[GitHub/WoltLab/WCF.git] / wcfsetup / install / files / lib / system / database / editor / MySQLDatabaseEditor.class.php

<?php
namespace wcf\system\database\editor;
use wcf\system\database\Database;

/**
 * This is the database editor implementation for MySQL4.1 or higher.
 *
 * @author	Marcel Werk
 * @copyright	2001-2011 WoltLab GmbH
 * @license	GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php>
 * @package	com.woltlab.wcf
 * @subpackage	system.database.editor
 * @category 	Community Framework
 */
class MySQLDatabaseEditor extends DatabaseEditor {
	/**
	 * @see wcf\system\database\editor\DatabaseEditor::getTableNames()
	 */
	public function getTableNames() {
		$existingTables = array();
		$sql = "SHOW TABLES FROM `".$this->dbObj->getDatabaseName()."`";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
		while ($row = $statement->fetchArray(\PDO::FETCH_NUM)) {
			$existingTables[] = $row[0];
		}
		return $existingTables;
	}
	
	/**
	 * @see wcf\system\database\editor\DatabaseEditor::getColumns()
	 */
	public function getColumns($tableName) {
		$columns = array();
		$sql = "SHOW COLUMNS FROM ".$tableName;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
		while ($row = $statement->fetchArray()) {
      	 		$columns[] = $row['Field'];
   		}
   		return $columns;
	}
	
	/**
	 * @see wcf\system\database\editor\DatabaseEditor::getIndices()
	 */
	public function getIndices($tableName) {
		$indices = array();
		$sql = "SHOW INDEX FROM ".$tableName;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
		while ($row = $statement->fetchArray()) {
      	 		$indices[] = $index['Key_name'];
   		}
   		
   		return $indices;
	}
	
	/**
	 * @see wcf\system\database\editor\DatabaseEditor::createTable()
	 */
	public function createTable($tableName, $columns, $indices = array()) {
		$columnDefinition = $indexDefinition = '';
		
		// build column definition
		foreach ($columns as $column) {
			if (!empty($columnDefinition)) $columnDefinition .= ',';
			$columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']);
		}
		
		// build index definition
		$hasFulltextIndex = false;
		foreach ($indices as $index) {
			if (!empty($indexDefinition)) $indexDefinition .= ',';
			$indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']);
			if ($index['data']['type'] == 'FULLTEXT') $hasFulltextIndex = true;
		}
		
		// create table
		$sql = "CREATE TABLE ".$tableName." (
				".$columnDefinition."
				".(!empty($indexDefinition) ? ',' : '')."
				".$indexDefinition."
			) ENGINE=".($hasFulltextIndex ? 'MyISAM' : 'InnoDB')." DEFAULT CHARSET=utf8";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see wcf\system\database\editor\DatabaseEditor::dropTable()
	 */
	public function dropTable($tableName) {
		$sql = "DROP TABLE IF EXISTS ".$tableName;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see wcf\system\database\editor\DatabaseEditor::addColumn()
	 */
	public function addColumn($tableName, $columnName, $columnData) {
		$sql = "ALTER TABLE ".$tableName." ADD COLUMN ".$this->buildColumnDefinition($columnName, $columnData);
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see wcf\system\database\editor\DatabaseEditor::alterColumn()
	 */
	public function alterColumn($tableName, $oldColumnName, $newColumnName, $newColumnData) {
		$sql = "ALTER TABLE ".$tableName." CHANGE COLUMN ".$oldColumnName." ".$this->buildColumnDefinition($newColumnName, $newColumnData);
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see wcf\system\database\editor\DatabaseEditor::dropColumn()
	 */
	public function dropColumn($tableName, $columnName) {
		$sql = "ALTER TABLE ".$tableName." DROP COLUMN ".$columnName;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see wcf\system\database\editor\DatabaseEditor::addIndex()
	 */
	public function addIndex($tableName, $indexName, $indexData) {
		$sql = "ALTER TABLE ".$tableName." ADD ".$this->buildIndexDefinition($indexName, $indexData);
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see wcf\system\database\editor\DatabaseEditor::addIndex()
	 */
	public function addForeignKey($tableName, $indexName, $indexData) {
		$sql = "ALTER TABLE ".$tableName." ADD";
		
		// add index name
		if (!empty($indexName)) $sql .= " CONSTRAINT ".$indexName;
		
		// add columns
		$sql .= " FOREIGN KEY (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['columns'])).")";
		
		// add referenced table name
		$sql .= " REFERENCES ".$indexData['referencedTable'];
		
		// add referenced columns
		$sql .= " (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['referencedColumns'])).")";
		
		// add operation and action
		if (!empty($indexData['operation'])) $sql .= " ON ".$indexData['operation']." ".$indexData['action'];
		
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see wcf\system\database\editor\DatabaseEditor::dropIndex()
	 */
	public function dropIndex($tableName, $indexName) {
		$sql = "ALTER TABLE ".$tableName." DROP INDEX ".$indexName;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @see wcf\system\database\editor\DatabaseEditor::dropForeignKey()
	 */
	public function dropForeignKey($tableName, $indexName) {
		$sql = "ALTER TABLE ".$tableName." DROP FOREIGN KEY ".$indexName;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * Builds a column definition for execution in a create table or alter table statement.
	 * 
	 * @param	string		$columnName
	 * @param	array		$columnData
	 * @param	string
	 */
	protected function buildColumnDefinition($columnName, $columnData) {
		// column name
		$definition = $columnName;
		// column type
		$definition .= " ".$columnData['type'];
		// column length and decimals
		if (!empty($columnData['length'])) {
			$definition .= "(".$columnData['length'].(!empty($columnData['decimals']) ? ",".$columnData['decimals'] : "").")";
		}
		// enum / set
		if ($columnData['type'] == 'enum' && !empty($columnData['values'])) {
			$definition .= "(".$columnData['values'].")";
		}
		// not null / null
		if (!empty($columnData['notNull'])) $definition .= " NOT NULL";
		// default
		if (isset($columnData['default']) && $columnData['default'] !== '') $definition .= " DEFAULT ".$columnData['default'];
		// auto_increment
		if (!empty($columnData['autoIncrement'])) $definition .= " AUTO_INCREMENT";
		// key
		if (!empty($columnData['key'])) $definition .= " ".$columnData['key']." KEY";
		
		return $definition;
	}
	
	/**
	 * Builds a index definition for execution in a create table or alter table statement.
	 * 
	 * @param	string		$indexName
	 * @param	array		$indexData
	 * @param	string
	 */
	protected function buildIndexDefinition($indexName, $indexData) {
		$definition = "";
		// index type
		if ($indexData['type'] == 'PRIMARY') $definition = "PRIMARY KEY";
		else if ($indexData['type'] == 'UNIQUE') $definition = "UNIQUE KEY";
		else if ($indexData['type'] == 'FULLTEXT') $definition = "FULLTEXT KEY";
		else $definition = "KEY";
		
		// index name
		if (!empty($indexName)) $definition .= " ".$indexName."";
		// columns
		$definition .= " (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['columns'])).")";
		
		return $definition;
	}
}
Commit	Line	Data
11ade432 AE	1	<?php
	2	namespace wcf\system\database\editor;
	3	use wcf\system\database\Database;
	4
	5	/**
	6	* This is the database editor implementation for MySQL4.1 or higher.
	7	*
	8	* @author Marcel Werk
	9	* @copyright 2001-2011 WoltLab GmbH
	10	* @license GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php>
	11	* @package com.woltlab.wcf
	12	* @subpackage system.database.editor
	13	* @category Community Framework
	14	*/
	15	class MySQLDatabaseEditor extends DatabaseEditor {
	16	/**
78587b79	17	* @see wcf\system\database\editor\DatabaseEditor::getTableNames()
11ade432 AE	18	*/
	19	public function getTableNames() {
	20	$existingTables = array();
	21	$sql = "SHOW TABLES FROM `".$this->dbObj->getDatabaseName()."`";
	22	$statement = $this->dbObj->prepareStatement($sql);
	23	$statement->execute();
	24	while ($row = $statement->fetchArray(\PDO::FETCH_NUM)) {
	25	$existingTables[] = $row[0];
	26	}
	27	return $existingTables;
	28	}
	29
	30	/**
78587b79	31	* @see wcf\system\database\editor\DatabaseEditor::getColumns()
11ade432 AE	32	*/
	33	public function getColumns($tableName) {
	34	$columns = array();
	35	$sql = "SHOW COLUMNS FROM ".$tableName;
	36	$statement = $this->dbObj->prepareStatement($sql);
	37	$statement->execute();
	38	while ($row = $statement->fetchArray()) {
	39	$columns[] = $row['Field'];
	40	}
	41	return $columns;
	42	}
	43
	44	/**
78587b79	45	* @see wcf\system\database\editor\DatabaseEditor::getIndices()
11ade432 AE	46	*/
	47	public function getIndices($tableName) {
	48	$indices = array();
	49	$sql = "SHOW INDEX FROM ".$tableName;
	50	$statement = $this->dbObj->prepareStatement($sql);
	51	$statement->execute();
	52	while ($row = $statement->fetchArray()) {
	53	$indices[] = $index['Key_name'];
	54	}
	55
	56	return $indices;
	57	}
	58
	59	/**
78587b79	60	* @see wcf\system\database\editor\DatabaseEditor::createTable()
11ade432 AE	61	*/
	62	public function createTable($tableName, $columns, $indices = array()) {
	63	$columnDefinition = $indexDefinition = '';
	64
	65	// build column definition
	66	foreach ($columns as $column) {
	67	if (!empty($columnDefinition)) $columnDefinition .= ',';
	68	$columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']);
	69	}
	70
	71	// build index definition
	72	$hasFulltextIndex = false;
	73	foreach ($indices as $index) {
	74	if (!empty($indexDefinition)) $indexDefinition .= ',';
	75	$indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']);
	76	if ($index['data']['type'] == 'FULLTEXT') $hasFulltextIndex = true;
	77	}
	78
	79	// create table
	80	$sql = "CREATE TABLE ".$tableName." (
	81	".$columnDefinition."
	82	".(!empty($indexDefinition) ? ',' : '')."
	83	".$indexDefinition."
	84	) ENGINE=".($hasFulltextIndex ? 'MyISAM' : 'InnoDB')." DEFAULT CHARSET=utf8";
	85	$statement = $this->dbObj->prepareStatement($sql);
	86	$statement->execute();
	87	}
	88
	89	/**
78587b79	90	* @see wcf\system\database\editor\DatabaseEditor::dropTable()
11ade432 AE	91	*/
	92	public function dropTable($tableName) {
	93	$sql = "DROP TABLE IF EXISTS ".$tableName;
	94	$statement = $this->dbObj->prepareStatement($sql);
	95	$statement->execute();
	96	}
	97
	98	/**
78587b79	99	* @see wcf\system\database\editor\DatabaseEditor::addColumn()
11ade432 AE	100	*/
	101	public function addColumn($tableName, $columnName, $columnData) {
	102	$sql = "ALTER TABLE ".$tableName." ADD COLUMN ".$this->buildColumnDefinition($columnName, $columnData);
	103	$statement = $this->dbObj->prepareStatement($sql);
	104	$statement->execute();
	105	}
	106
	107	/**
78587b79	108	* @see wcf\system\database\editor\DatabaseEditor::alterColumn()
11ade432 AE	109	*/
	110	public function alterColumn($tableName, $oldColumnName, $newColumnName, $newColumnData) {
	111	$sql = "ALTER TABLE ".$tableName." CHANGE COLUMN ".$oldColumnName." ".$this->buildColumnDefinition($newColumnName, $newColumnData);
	112	$statement = $this->dbObj->prepareStatement($sql);
	113	$statement->execute();
	114	}
	115
	116	/**
78587b79	117	* @see wcf\system\database\editor\DatabaseEditor::dropColumn()
11ade432 AE	118	*/
	119	public function dropColumn($tableName, $columnName) {
	120	$sql = "ALTER TABLE ".$tableName." DROP COLUMN ".$columnName;
	121	$statement = $this->dbObj->prepareStatement($sql);
	122	$statement->execute();
	123	}
	124
	125	/**
78587b79	126	* @see wcf\system\database\editor\DatabaseEditor::addIndex()
11ade432 AE	127	*/
	128	public function addIndex($tableName, $indexName, $indexData) {
	129	$sql = "ALTER TABLE ".$tableName." ADD ".$this->buildIndexDefinition($indexName, $indexData);
	130	$statement = $this->dbObj->prepareStatement($sql);
	131	$statement->execute();
	132	}
	133
	134	/**
78587b79	135	* @see wcf\system\database\editor\DatabaseEditor::addIndex()
11ade432 AE	136	*/
	137	public function addForeignKey($tableName, $indexName, $indexData) {
	138	$sql = "ALTER TABLE ".$tableName." ADD";
	139
	140	// add index name
	141	if (!empty($indexName)) $sql .= " CONSTRAINT ".$indexName;
	142
	143	// add columns
	144	$sql .= " FOREIGN KEY (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['columns'])).")";
	145
	146	// add referenced table name
	147	$sql .= " REFERENCES ".$indexData['referencedTable'];
	148
	149	// add referenced columns
	150	$sql .= " (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['referencedColumns'])).")";
	151
	152	// add operation and action
	153	if (!empty($indexData['operation'])) $sql .= " ON ".$indexData['operation']." ".$indexData['action'];
	154
	155	$statement = $this->dbObj->prepareStatement($sql);
	156	$statement->execute();
	157	}
	158
	159	/**
78587b79	160	* @see wcf\system\database\editor\DatabaseEditor::dropIndex()
11ade432 AE	161	*/
	162	public function dropIndex($tableName, $indexName) {
	163	$sql = "ALTER TABLE ".$tableName." DROP INDEX ".$indexName;
	164	$statement = $this->dbObj->prepareStatement($sql);
	165	$statement->execute();
	166	}
	167
b6ae7d04	168	/**
78587b79	169	* @see wcf\system\database\editor\DatabaseEditor::dropForeignKey()
b6ae7d04 MW	170	*/
	171	public function dropForeignKey($tableName, $indexName) {
	172	$sql = "ALTER TABLE ".$tableName." DROP FOREIGN KEY ".$indexName;
	173	$statement = $this->dbObj->prepareStatement($sql);
	174	$statement->execute();
	175	}
	176
11ade432 AE	177	/**
	178	* Builds a column definition for execution in a create table or alter table statement.
	179	*
	180	* @param string $columnName
	181	* @param array $columnData
	182	* @param string
	183	*/
	184	protected function buildColumnDefinition($columnName, $columnData) {
	185	// column name
	186	$definition = $columnName;
	187	// column type
	188	$definition .= " ".$columnData['type'];
	189	// column length and decimals
	190	if (!empty($columnData['length'])) {
	191	$definition .= "(".$columnData['length'].(!empty($columnData['decimals']) ? ",".$columnData['decimals'] : "").")";
	192	}
	193	// enum / set
	194	if ($columnData['type'] == 'enum' && !empty($columnData['values'])) {
	195	$definition .= "(".$columnData['values'].")";
	196	}
	197	// not null / null
	198	if (!empty($columnData['notNull'])) $definition .= " NOT NULL";
	199	// default
	200	if (isset($columnData['default']) && $columnData['default'] !== '') $definition .= " DEFAULT ".$columnData['default'];
	201	// auto_increment
	202	if (!empty($columnData['autoIncrement'])) $definition .= " AUTO_INCREMENT";
	203	// key
	204	if (!empty($columnData['key'])) $definition .= " ".$columnData['key']." KEY";
	205
	206	return $definition;
	207	}
	208
	209	/**
	210	* Builds a index definition for execution in a create table or alter table statement.
	211	*
	212	* @param string $indexName
	213	* @param array $indexData
	214	* @param string
	215	*/
	216	protected function buildIndexDefinition($indexName, $indexData) {
	217	$definition = "";
	218	// index type
	219	if ($indexData['type'] == 'PRIMARY') $definition = "PRIMARY KEY";
	220	else if ($indexData['type'] == 'UNIQUE') $definition = "UNIQUE KEY";
	221	else if ($indexData['type'] == 'FULLTEXT') $definition = "FULLTEXT KEY";
	222	else $definition = "KEY";
	223
	224	// index name
	225	if (!empty($indexName)) $definition .= " ".$indexName."";
	226	// columns
	227	$definition .= " (".str_replace(',', ',', preg_replace('/\s+/', '', $indexData['columns'])).")";
	228
	229	return $definition;
	230	}
	231	}