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

<?php
namespace wcf\system\database\editor;
use wcf\system\database\util\PreparedStatementConditionBuilder;

/**
 * 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[] = $row['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;
	}
	
	/**
	 * @see	wcf\system\database\editor\DatabaseEditor::dropConflictedTables()
	 */
	public function dropConflictedTables(array $conflictedTables) {
		$tables = array();
		foreach ($conflictedTables as $tableName) {
			$tables[$tableName] = array();
		}
		
		// get current database
		$sql = "SELECT	DATABASE() AS currentDB";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
		$row = $statement->fetchArray();
		$currentDB = $row['currentDB'];
		
		// get constraints
		$conditions = new PreparedStatementConditionBuilder();
		$conditions->add("TABLE_SCHEMA = ?", array($currentDB));
		$conditions->add("TABLE_NAME IN (?)", array($conflictedTables));
		$conditions->add("REFERENCED_TABLE_NAME IS NOT NULL");
		$conditions->add("CONSTRAINT_NAME LIKE ?", array('%_fk'));
		
		$sql = "SELECT	CONSTRAINT_NAME, TABLE_NAME
			FROM	information_schema.KEY_COLUMN_USAGE
			".$conditions;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute($conditions->getParameters());
		while ($row = $statement->fetchArray()) {
			$this->tables[$row['TABLE_NAME']][] = $row['CONSTRAINT_NAME'];
		}
		
		// handle foreign keys from 3rd party tables
		$conditions = new PreparedStatementConditionBuilder();
		$conditions->add("TABLE_SCHEMA = ?", array($currentDB));
		$conditions->add("REFERENCED_TABLE_NAME IN (?)", array($conflictedTables));
		$conditions->add("CONSTRAINT_NAME LIKE ?", array('%_fk'));
		
		$sql = "SELECT	CONSTRAINT_NAME, TABLE_NAME
			FROM	information_schema.KEY_COLUMN_USAGE
			".$conditions;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute($conditions->getParameters());
		$foreignConstraints = array();
		while ($row = $statement->fetchArray()) {
			if (!isset($foreignConstraints[$row['TABLE_NAME']])) {
				$foreignConstraints[$row['TABLE_NAME']] = array();
			}
			
			$foreignConstraints[$row['TABLE_NAME']][] = $row['CONSTRAINT_NAME'];
		}
		
		// drop foreign keys from 3rd party tables
		foreach ($foreignConstraints as $tableName => $foreignKeys) {
			foreach ($foreignKeys as $fk) {
				$this->dropForeignKey($tableName, $fk);
			}
		}
		
		// drop foreign keys
		foreach ($this->tables as $tableName => $foreignKeys) {
			foreach ($foreignKeys as $fk) {
				$this->dropForeignKey($tableName, $fk);
			}
		}
		
		// drop tables
		foreach (array_keys($this->tables) as $tableName) {
			$this->dropTable($tableName);
		}
	}
}
Commit	Line	Data
11ade432 AE	1	<?php
11ade432 AE	2	namespace wcf\system\database\editor;
76d74665	3	use wcf\system\database\util\PreparedStatementConditionBuilder;
11ade432 AE	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()) {
6286572b	53	$indices[] = $row['Key_name'];
11ade432 AE	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
96017679	141	if (!empty($indexName)) $sql .= " CONSTRAINT `".$indexName."`";
11ade432 AE	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	*/
b6ae7d04 MW	171	public function dropForeignKey($tableName, $indexName) {
96017679	172	$sql = "ALTER TABLE ".$tableName." DROP FOREIGN KEY `".$indexName."`";
b6ae7d04 MW	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	}
76d74665 AE	231
	232	/**
	233	* @see wcf\system\database\editor\DatabaseEditor::dropConflictedTables()
	234	*/
	235	public function dropConflictedTables(array $conflictedTables) {
	236	$tables = array();
	237	foreach ($conflictedTables as $tableName) {
	238	$tables[$tableName] = array();
	239	}
	240
	241	// get current database
	242	$sql = "SELECT DATABASE() AS currentDB";
	243	$statement = $this->dbObj->prepareStatement($sql);
	244	$statement->execute();
	245	$row = $statement->fetchArray();
	246	$currentDB = $row['currentDB'];
	247
	248	// get constraints
	249	$conditions = new PreparedStatementConditionBuilder();
	250	$conditions->add("TABLE_SCHEMA = ?", array($currentDB));
	251	$conditions->add("TABLE_NAME IN (?)", array($conflictedTables));
	252	$conditions->add("REFERENCED_TABLE_NAME IS NOT NULL");
	253	$conditions->add("CONSTRAINT_NAME LIKE ?", array('%_fk'));
	254
	255	$sql = "SELECT CONSTRAINT_NAME, TABLE_NAME
	256	FROM information_schema.KEY_COLUMN_USAGE
	257	".$conditions;
	258	$statement = $this->dbObj->prepareStatement($sql);
	259	$statement->execute($conditions->getParameters());
	260	while ($row = $statement->fetchArray()) {
	261	$this->tables[$row['TABLE_NAME']][] = $row['CONSTRAINT_NAME'];
	262	}
	263
a1517977 AE	264	// handle foreign keys from 3rd party tables
	265	$conditions = new PreparedStatementConditionBuilder();
	266	$conditions->add("TABLE_SCHEMA = ?", array($currentDB));
	267	$conditions->add("REFERENCED_TABLE_NAME IN (?)", array($conflictedTables));
	268	$conditions->add("CONSTRAINT_NAME LIKE ?", array('%_fk'));
	269
	270	$sql = "SELECT CONSTRAINT_NAME, TABLE_NAME
	271	FROM information_schema.KEY_COLUMN_USAGE
	272	".$conditions;
	273	$statement = $this->dbObj->prepareStatement($sql);
	274	$statement->execute($conditions->getParameters());
	275	$foreignConstraints = array();
	276	while ($row = $statement->fetchArray()) {
	277	if (!isset($foreignConstraints[$row['TABLE_NAME']])) {
	278	$foreignConstraints[$row['TABLE_NAME']] = array();
	279	}
	280
	281	$foreignConstraints[$row['TABLE_NAME']][] = $row['CONSTRAINT_NAME'];
	282	}
	283
	284	// drop foreign keys from 3rd party tables
	285	foreach ($foreignConstraints as $tableName => $foreignKeys) {
	286	foreach ($foreignKeys as $fk) {
	287	$this->dropForeignKey($tableName, $fk);
	288	}
	289	}
	290
76d74665 AE	291	// drop foreign keys
	292	foreach ($this->tables as $tableName => $foreignKeys) {
	293	foreach ($foreignKeys as $fk) {
	294	$this->dropForeignKey($tableName, $fk);
	295	}
	296	}
	297
	298	// drop tables
	299	foreach (array_keys($this->tables) as $tableName) {
	300	$this->dropTable($tableName);
	301	}
	302	}
11ade432	303	}