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

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

/**
 * Database editor implementation for MySQL4.1 or higher.
 * 
 * @author	Marcel Werk
 * @copyright	2001-2019 WoltLab GmbH
 * @license	GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php>
 * @package	WoltLabSuite\Core\System\Database\Editor
 */
class MySQLDatabaseEditor extends DatabaseEditor {
	/**
	 * @inheritDoc
	 */
	public function getTableNames() {
		$existingTables = [];
		$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;
	}
	
	/**
	 * @inheritDoc
	 */
	public function getColumns($tableName) {
		$columns = [];
		$regex = new Regex('([a-z]+)\((.+)\)', Regex::CASE_INSENSITIVE);
		
		$sql = "SHOW COLUMNS FROM `".$tableName."`";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
		while ($row = $statement->fetchArray()) {
			$regex->match($row['Type']);
			$typeMatches = $regex->getMatches();
			
			$type = $row['Type'];
			$length = '';
			$decimals = '';
			$enumValues = '';
			if (!empty($typeMatches)) {
				$type = $typeMatches[1];
				
				switch ($type) {
					case 'enum':
					case 'set':
						$enumValues = $typeMatches[2];
						break;
						
					case 'decimal':
					case 'double':
					case 'float':
						$pieces = explode(',', $typeMatches[2]);
						switch (count($pieces)) {
							case 1:
								$length = $pieces[0];
								break;
								
							case 2:
								list($length, $decimals) = $pieces;
								break;
						}
						
						break;
						
					default:
						if ($typeMatches[2] == (int)$typeMatches[2]) {
							$length = $typeMatches[2];
						}
						break;
				}
			}
			
			$columns[] = ['name' => $row['Field'], 'data' => [
				'type' => $type,
				'length' => $length,
				'notNull' => $row['Null'] == 'YES' ? false : true,
				'key' => ($row['Key'] == 'PRI') ? 'PRIMARY' : (($row['Key'] == 'UNI') ? 'UNIQUE' : ''),
				'default' => $row['Default'],
				'autoIncrement' => $row['Extra'] == 'auto_increment' ? true : false,
				'enumValues' => $enumValues,
				'decimals' => $decimals
			]];
		}
		
		return $columns;
	}
	
	/**
	 * @inheritDoc
	 */
	public function getForeignKeys($tableName) {
		$sql = "SELECT	CONSTRAINT_NAME, DELETE_RULE, UPDATE_RULE
			FROM	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
			WHERE	CONSTRAINT_SCHEMA = ?
				AND TABLE_NAME = ?";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute([
			$this->dbObj->getDatabaseName(),
			$tableName
		]);
		$referentialConstraints = $statement->fetchAll(\PDO::FETCH_ASSOC);
		
		$validActions = ['CASCADE', 'SET NULL', 'NO ACTION'];
		
		$foreignKeys = [];
		foreach ($referentialConstraints as $information) {
			$foreignKeys[$information['CONSTRAINT_NAME']] = [
				'columns' => [],
				'referencedColumns' => [],
				'ON DELETE' => in_array($information['DELETE_RULE'], $validActions) ? $information['DELETE_RULE'] : null,
				'ON UPDATE' => in_array($information['UPDATE_RULE'], $validActions) ? $information['UPDATE_RULE'] : null
			];
		}
		
		if (empty($foreignKeys)) {
			return [];
		}
		
		$conditionBuilder = new PreparedStatementConditionBuilder();
		$conditionBuilder->add('CONSTRAINT_NAME IN (?)', [array_keys($foreignKeys)]);
		$conditionBuilder->add('TABLE_SCHEMA = ?', [$this->dbObj->getDatabaseName()]);
		$conditionBuilder->add('TABLE_NAME = ?', [$tableName]);
		
		$sql = "SELECT	CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
			FROM	INFORMATION_SCHEMA.KEY_COLUMN_USAGE
			" . $conditionBuilder;
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute($conditionBuilder->getParameters());
		$keyColumnUsage = $statement->fetchAll(\PDO::FETCH_ASSOC);
		
		foreach ($keyColumnUsage as $information) {
			$foreignKeys[$information['CONSTRAINT_NAME']]['columns'][] = $information['COLUMN_NAME'];
			$foreignKeys[$information['CONSTRAINT_NAME']]['referencedColumns'][] = $information['REFERENCED_COLUMN_NAME'];
			$foreignKeys[$information['CONSTRAINT_NAME']]['referencedTable'] = $information['REFERENCED_TABLE_NAME'];
		}
		
		foreach ($foreignKeys as $keyName => $keyData) {
			$foreignKeys[$keyName]['columns'] = array_unique($foreignKeys[$keyName]['columns']);
			$foreignKeys[$keyName]['referencedColumns'] = array_unique($foreignKeys[$keyName]['referencedColumns']);
		}
		
		return $foreignKeys;
	}
	
	/**
	 * @inheritDoc
	 */
	public function getIndexInformation($tableName) {
		$sql = "SHOW	INDEX
			FROM	`".$tableName."`";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
		$indices = $statement->fetchAll(\PDO::FETCH_ASSOC);
		
		$indexInformation = [];
		foreach ($indices as $index) {
			if (!isset($indexInformation[$index['Key_name']])) {
				$type = null;
				if ($index['Index_type'] === 'FULLTEXT') {
					$type = 'FULLTEXT';
				}
				else if ($index['Key_name'] === 'PRIMARY') {
					$type = 'PRIMARY';
				}
				else if ($index['Non_unique'] == 0) {
					$type = 'UNIQUE';
				}
				
				$indexInformation[$index['Key_name']] = [
					'columns' => [$index['Column_name']],
					'type' => $type
				];
			}
			else {
				$indexInformation[$index['Key_name']]['columns'][] = $index['Column_name'];
			}
		}
		
		return $indexInformation;
	}
	
	/**
	 * @inheritDoc
	 */
	public function getIndices($tableName) {
		$indices = [];
		$sql = "SHOW INDEX FROM `".$tableName."`";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
		while ($row = $statement->fetchArray()) {
			$indices[] = $row['Key_name'];
		}
		
		return array_unique($indices);
	}
	
	/**
	 * @inheritDoc
	 */
	public function createTable($tableName, $columns, $indices = []) {
		$columnDefinition = $indexDefinition = '';
		
		// build column definition
		foreach ($columns as $column) {
			if (!empty($columnDefinition)) $columnDefinition .= ',';
			$columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']);
		}
		
		// build index definition
		foreach ($indices as $index) {
			if (!empty($indexDefinition)) $indexDefinition .= ',';
			$indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']);
		}
		
		// create table
		$sql = "CREATE TABLE `".$tableName."` (
				".$columnDefinition."
				".(!empty($indexDefinition) ? ',' : '')."
				".$indexDefinition."
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @inheritDoc
	 */
	public function dropTable($tableName) {
		$sql = "DROP TABLE IF EXISTS `".$tableName."`";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @inheritDoc
	 */
	public function addColumn($tableName, $columnName, $columnData) {
		$sql = "ALTER TABLE `".$tableName."` ADD COLUMN ".$this->buildColumnDefinition($columnName, $columnData);
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @inheritDoc
	 */
	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();
	}
	
	/**
	 * @inheritDoc
	 */
	public function alterColumns($tableName, $alterData) {
		$queries = "";
		foreach ($alterData as $columnName => $data) {
			switch ($data['action']) {
				case 'add':
					$queries .= "ADD COLUMN {$this->buildColumnDefinition($columnName, $data['data'])},";
					break;
					
				case 'alter':
					$newColumnName = $columnName;
					if (isset($data['oldColumnName'])) {
						/**
						 * @deprecated  5.4     `oldColumnName` was an incorrect name for the index
						 *                      that is kept for backwards compatibility for now
						 */
						$newColumnName = $data['oldColumnName'];
					}
					else if (isset($data['newColumnName'])) {
						$newColumnName = $data['newColumnName'];
					}
					
					$queries .= "CHANGE COLUMN `{$columnName}` {$this->buildColumnDefinition($newColumnName, $data['data'])},";
					break;
					
				case 'drop':
					$queries .= "DROP COLUMN `{$columnName}`,";
					break;
			}
		}
		
		$this->dbObj->prepareStatement("ALTER TABLE `{$tableName}` " . rtrim($queries, ','))->execute();
	}

	/**
	 * @inheritDoc
	 */
	public function dropColumn($tableName, $columnName) {
		try {
			$sql = "ALTER TABLE `".$tableName."` DROP COLUMN `".$columnName."`";
			$statement = $this->dbObj->prepareStatement($sql);
			$statement->execute();
		}
		catch (DatabaseQueryExecutionException $e) {
			if ($e->getCode() != '42000') {
				throw $e;
			}
			if (in_array($columnName, array_column($this->getColumns($tableName), 'name'))) {
				throw $e;
			}
		}
	}
	
	/**
	 * @inheritDoc
	 */
	public function addIndex($tableName, $indexName, $indexData) {
		$sql = "ALTER TABLE `".$tableName."` ADD ".$this->buildIndexDefinition($indexName, $indexData);
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @inheritDoc
	 */
	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'];
		if (!empty($indexData['ON DELETE'])) $sql .= " ON DELETE ".$indexData['ON DELETE'];
		if (!empty($indexData['ON UPDATE'])) $sql .= " ON UPDATE ".$indexData['ON UPDATE'];
		
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @inheritDoc
	 */
	public function dropIndex($tableName, $indexName) {
		try {
			$sql = "ALTER TABLE `".$tableName."` DROP INDEX `".$indexName."`";
			$statement = $this->dbObj->prepareStatement($sql);
			$statement->execute();
		}
		catch (DatabaseQueryExecutionException $e) {
			if ($e->getCode() != '42000') {
				throw $e;
			}
			if (in_array($indexName, $this->getIndices($tableName))) {
				throw $e;
			}
		}
	}
	
	/**
	 * @inheritDoc
	 */
	public function dropPrimaryKey($tableName) {
		try {
			$sql = "ALTER TABLE ".$tableName." DROP PRIMARY KEY";
			$statement = $this->dbObj->prepareStatement($sql);
			$statement->execute();
		}
		catch (DatabaseQueryExecutionException $e) {
			if ($e->getCode() != '42000') {
				throw $e;
			}
			if (in_array("PRIMARY", $this->getIndices($tableName))) {
				throw $e;
			}
		}
	}
	
	/**
	 * @inheritDoc
	 */
	public function dropForeignKey($tableName, $indexName) {
		try {
			$sql = "ALTER TABLE `".$tableName."` DROP FOREIGN KEY `".$indexName."`";
			$statement = $this->dbObj->prepareStatement($sql);
			$statement->execute();
		}
		catch (DatabaseQueryExecutionException $e) {
			if ($e->getCode() != '42000') {
				throw $e;
			}
			if (in_array($indexName, array_keys($this->getForeignKeys($tableName)))) {
				throw $e;
			}
		}
	}
	
	/**
	 * Builds a column definition for execution in a create table or alter table statement.
	 * 
	 * @param	string		$columnName
	 * @param	array		$columnData
	 * @return	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
	 * @return	string
	 */
	protected function buildIndexDefinition($indexName, $indexData) {
		// 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
11ade432 AE	2	namespace wcf\system\database\editor;
bf64fe54	3	use wcf\system\database\exception\DatabaseQueryExecutionException;
770f5de3	4	use wcf\system\database\util\PreparedStatementConditionBuilder;
82a7de7f	5	use wcf\system\Regex;
11ade432 AE	6
11ade432 AE	7	/**
a17de04e MS	8	* Database editor implementation for MySQL4.1 or higher.
a17de04e MS	9	*
11ade432	10	* @author Marcel Werk
7b7b9764	11	* @copyright 2001-2019 WoltLab GmbH
11ade432	12	* @license GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php>
e71525e4	13	* @package WoltLabSuite\Core\System\Database\Editor
11ade432 AE	14	*/
	15	class MySQLDatabaseEditor extends DatabaseEditor {
	16	/**
0fcfe5f6	17	* @inheritDoc
11ade432 AE	18	*/
11ade432 AE	19	public function getTableNames() {
058cbd6a	20	$existingTables = [];
11ade432 AE	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	/**
0fcfe5f6	31	* @inheritDoc
11ade432 AE	32	*/
11ade432 AE	33	public function getColumns($tableName) {
058cbd6a	34	$columns = [];
ef5d8ae6	35	$regex = new Regex('([a-z]+)\((.+)\)', Regex::CASE_INSENSITIVE);
77bb36ca	36
4f2c4eb8	37	$sql = "SHOW COLUMNS FROM `".$tableName."`";
11ade432 AE	38	$statement = $this->dbObj->prepareStatement($sql);
	39	$statement->execute();
	40	while ($row = $statement->fetchArray()) {
14d48464	41	$regex->match($row['Type']);
14d48464	42	$typeMatches = $regex->getMatches();
77bb36ca	43
4d2bd2ec MS	44	$type = $row['Type'];
	45	$length = '';
	46	$decimals = '';
	47	$enumValues = '';
	48	if (!empty($typeMatches)) {
	49	$type = $typeMatches[1];
	50
	51	switch ($type) {
	52	case 'enum':
	53	case 'set':
	54	$enumValues = $typeMatches[2];
	55	break;
	56
	57	case 'decimal':
	58	case 'double':
	59	case 'float':
	60	$pieces = explode(',', $typeMatches[2]);
	61	switch (count($pieces)) {
	62	case 1:
	63	$length = $pieces[0];
	64	break;
	65
	66	case 2:
	67	list($length, $decimals) = $pieces;
	68	break;
	69	}
	70
	71	break;
	72
	73	default:
	74	if ($typeMatches[2] == (int)$typeMatches[2]) {
	75	$length = $typeMatches[2];
	76	}
	77	break;
	78	}
	79	}
	80
058cbd6a	81	$columns[] = ['name' => $row['Field'], 'data' => [
4d2bd2ec MS	82	'type' => $type,
	83	'length' => $length,
	84	'notNull' => $row['Null'] == 'YES' ? false : true,
63b9817b	85	'key' => ($row['Key'] == 'PRI') ? 'PRIMARY' : (($row['Key'] == 'UNI') ? 'UNIQUE' : ''),
5fe135db	86	'default' => $row['Default'],
4d2bd2ec MS	87	'autoIncrement' => $row['Extra'] == 'auto_increment' ? true : false,
4d2bd2ec MS	88	'enumValues' => $enumValues,
ef5d8ae6	89	'decimals' => $decimals
058cbd6a	90	]];
ebe8e825	91	}
bf27dc91	92
9f959ced	93	return $columns;
11ade432 AE	94	}
11ade432 AE	95
dc62342b MS	96	/**
	97	* @inheritDoc
	98	*/
	99	public function getForeignKeys($tableName) {
770f5de3 MS	100	$sql = "SELECT CONSTRAINT_NAME, DELETE_RULE, UPDATE_RULE
	101	FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
	102	WHERE CONSTRAINT_SCHEMA = ?
	103	AND TABLE_NAME = ?";
dc62342b MS	104	$statement = $this->dbObj->prepareStatement($sql);
	105	$statement->execute([
	106	$this->dbObj->getDatabaseName(),
	107	$tableName
	108	]);
770f5de3	109	$referentialConstraints = $statement->fetchAll(\PDO::FETCH_ASSOC);
dc62342b MS	110
	111	$validActions = ['CASCADE', 'SET NULL', 'NO ACTION'];
	112
	113	$foreignKeys = [];
770f5de3 MS	114	foreach ($referentialConstraints as $information) {
	115	$foreignKeys[$information['CONSTRAINT_NAME']] = [
	116	'columns' => [],
	117	'referencedColumns' => [],
	118	'ON DELETE' => in_array($information['DELETE_RULE'], $validActions) ? $information['DELETE_RULE'] : null,
	119	'ON UPDATE' => in_array($information['UPDATE_RULE'], $validActions) ? $information['UPDATE_RULE'] : null
	120	];
	121	}
	122
	123	if (empty($foreignKeys)) {
	124	return [];
	125	}
	126
	127	$conditionBuilder = new PreparedStatementConditionBuilder();
	128	$conditionBuilder->add('CONSTRAINT_NAME IN (?)', [array_keys($foreignKeys)]);
	129	$conditionBuilder->add('TABLE_SCHEMA = ?', [$this->dbObj->getDatabaseName()]);
	130	$conditionBuilder->add('TABLE_NAME = ?', [$tableName]);
	131
	132	$sql = "SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
	133	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
	134	" . $conditionBuilder;
	135	$statement = $this->dbObj->prepareStatement($sql);
	136	$statement->execute($conditionBuilder->getParameters());
	137	$keyColumnUsage = $statement->fetchAll(\PDO::FETCH_ASSOC);
	138
	139	foreach ($keyColumnUsage as $information) {
	140	$foreignKeys[$information['CONSTRAINT_NAME']]['columns'][] = $information['COLUMN_NAME'];
	141	$foreignKeys[$information['CONSTRAINT_NAME']]['referencedColumns'][] = $information['REFERENCED_COLUMN_NAME'];
	142	$foreignKeys[$information['CONSTRAINT_NAME']]['referencedTable'] = $information['REFERENCED_TABLE_NAME'];
dc62342b MS	143	}
	144
	145	foreach ($foreignKeys as $keyName => $keyData) {
	146	$foreignKeys[$keyName]['columns'] = array_unique($foreignKeys[$keyName]['columns']);
	147	$foreignKeys[$keyName]['referencedColumns'] = array_unique($foreignKeys[$keyName]['referencedColumns']);
	148	}
	149
	150	return $foreignKeys;
	151	}
	152
	153	/**
	154	* @inheritDoc
	155	*/
	156	public function getIndexInformation($tableName) {
	157	$sql = "SHOW INDEX
	158	FROM `".$tableName."`";
	159	$statement = $this->dbObj->prepareStatement($sql);
	160	$statement->execute();
	161	$indices = $statement->fetchAll(\PDO::FETCH_ASSOC);
	162
	163	$indexInformation = [];
	164	foreach ($indices as $index) {
	165	if (!isset($indexInformation[$index['Key_name']])) {
	166	$type = null;
	167	if ($index['Index_type'] === 'FULLTEXT') {
	168	$type = 'FULLTEXT';
	169	}
	170	else if ($index['Key_name'] === 'PRIMARY') {
	171	$type = 'PRIMARY';
	172	}
	173	else if ($index['Non_unique'] == 0) {
	174	$type = 'UNIQUE';
	175	}
	176
	177	$indexInformation[$index['Key_name']] = [
	178	'columns' => [$index['Column_name']],
	179	'type' => $type
	180	];
	181	}
	182	else {
	183	$indexInformation[$index['Key_name']]['columns'][] = $index['Column_name'];
	184	}
	185	}
	186
	187	return $indexInformation;
	188	}
	189
11ade432	190	/**
0fcfe5f6	191	* @inheritDoc
11ade432 AE	192	*/
11ade432 AE	193	public function getIndices($tableName) {
058cbd6a	194	$indices = [];
4f2c4eb8	195	$sql = "SHOW INDEX FROM `".$tableName."`";
11ade432 AE	196	$statement = $this->dbObj->prepareStatement($sql);
	197	$statement->execute();
	198	while ($row = $statement->fetchArray()) {
9f959ced MS	199	$indices[] = $row['Key_name'];
	200	}
	201
f62c045d	202	return array_unique($indices);
11ade432 AE	203	}
	204
	205	/**
0fcfe5f6	206	* @inheritDoc
11ade432	207	*/
058cbd6a	208	public function createTable($tableName, $columns, $indices = []) {
11ade432 AE	209	$columnDefinition = $indexDefinition = '';
	210
	211	// build column definition
	212	foreach ($columns as $column) {
	213	if (!empty($columnDefinition)) $columnDefinition .= ',';
	214	$columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']);
	215	}
	216
	217	// build index definition
11ade432 AE	218	foreach ($indices as $index) {
	219	if (!empty($indexDefinition)) $indexDefinition .= ',';
	220	$indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']);
11ade432 AE	221	}
	222
	223	// create table
4f2c4eb8	224	$sql = "CREATE TABLE `".$tableName."` (
11ade432 AE	225	".$columnDefinition."
	226	".(!empty($indexDefinition) ? ',' : '')."
	227	".$indexDefinition."
ba16fd2b	228	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
11ade432 AE	229	$statement = $this->dbObj->prepareStatement($sql);
	230	$statement->execute();
	231	}
	232
	233	/**
0fcfe5f6	234	* @inheritDoc
11ade432 AE	235	*/
11ade432 AE	236	public function dropTable($tableName) {
4f2c4eb8	237	$sql = "DROP TABLE IF EXISTS `".$tableName."`";
11ade432 AE	238	$statement = $this->dbObj->prepareStatement($sql);
	239	$statement->execute();
	240	}
	241
	242	/**
0fcfe5f6	243	* @inheritDoc
11ade432 AE	244	*/
11ade432 AE	245	public function addColumn($tableName, $columnName, $columnData) {
4f2c4eb8	246	$sql = "ALTER TABLE `".$tableName."` ADD COLUMN ".$this->buildColumnDefinition($columnName, $columnData);
11ade432 AE	247	$statement = $this->dbObj->prepareStatement($sql);
	248	$statement->execute();
	249	}
	250
	251	/**
0fcfe5f6	252	* @inheritDoc
11ade432 AE	253	*/
11ade432 AE	254	public function alterColumn($tableName, $oldColumnName, $newColumnName, $newColumnData) {
4f2c4eb8	255	$sql = "ALTER TABLE `".$tableName."` CHANGE COLUMN `".$oldColumnName."` ".$this->buildColumnDefinition($newColumnName, $newColumnData);
11ade432 AE	256	$statement = $this->dbObj->prepareStatement($sql);
	257	$statement->execute();
	258	}
	259
f6e43f2f MS	260	/**
	261	* @inheritDoc
	262	*/
	263	public function alterColumns($tableName, $alterData) {
	264	$queries = "";
	265	foreach ($alterData as $columnName => $data) {
	266	switch ($data['action']) {
	267	case 'add':
	268	$queries .= "ADD COLUMN {$this->buildColumnDefinition($columnName, $data['data'])},";
	269	break;
	270
	271	case 'alter':
1a024c65 MS	272	$newColumnName = $columnName;
	273	if (isset($data['oldColumnName'])) {
	274	/**
	275	* @deprecated 5.4 `oldColumnName` was an incorrect name for the index
	276	* that is kept for backwards compatibility for now
	277	*/
	278	$newColumnName = $data['oldColumnName'];
	279	}
	280	else if (isset($data['newColumnName'])) {
	281	$newColumnName = $data['newColumnName'];
	282	}
	283
	284	$queries .= "CHANGE COLUMN `{$columnName}` {$this->buildColumnDefinition($newColumnName, $data['data'])},";
f6e43f2f MS	285	break;
	286
	287	case 'drop':
	288	$queries .= "DROP COLUMN `{$columnName}`,";
	289	break;
	290	}
	291	}
	292
	293	$this->dbObj->prepareStatement("ALTER TABLE `{$tableName}` " . rtrim($queries, ','))->execute();
	294	}
	295
11ade432	296	/**
0fcfe5f6	297	* @inheritDoc
11ade432 AE	298	*/
11ade432 AE	299	public function dropColumn($tableName, $columnName) {
bf64fe54 TD	300	try {
	301	$sql = "ALTER TABLE `".$tableName."` DROP COLUMN `".$columnName."`";
	302	$statement = $this->dbObj->prepareStatement($sql);
	303	$statement->execute();
	304	}
	305	catch (DatabaseQueryExecutionException $e) {
	306	if ($e->getCode() != '42000') {
	307	throw $e;
	308	}
	309	if (in_array($columnName, array_column($this->getColumns($tableName), 'name'))) {
	310	throw $e;
	311	}
	312	}
11ade432 AE	313	}
	314
	315	/**
0fcfe5f6	316	* @inheritDoc
11ade432 AE	317	*/
11ade432 AE	318	public function addIndex($tableName, $indexName, $indexData) {
4f2c4eb8	319	$sql = "ALTER TABLE `".$tableName."` ADD ".$this->buildIndexDefinition($indexName, $indexData);
11ade432 AE	320	$statement = $this->dbObj->prepareStatement($sql);
	321	$statement->execute();
	322	}
	323
	324	/**
0fcfe5f6	325	* @inheritDoc
11ade432 AE	326	*/
11ade432 AE	327	public function addForeignKey($tableName, $indexName, $indexData) {
4f2c4eb8	328	$sql = "ALTER TABLE `".$tableName."` ADD";
11ade432 AE	329
11ade432 AE	330	// add index name
96017679	331	if (!empty($indexName)) $sql .= " CONSTRAINT `".$indexName."`";
11ade432 AE	332
11ade432 AE	333	// add columns
4f2c4eb8	334	$sql .= " FOREIGN KEY (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)";
11ade432 AE	335
11ade432 AE	336	// add referenced table name
4f2c4eb8	337	$sql .= " REFERENCES `".$indexData['referencedTable']."`";
11ade432 AE	338
11ade432 AE	339	// add referenced columns
4f2c4eb8	340	$sql .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['referencedColumns']))."`)";
11ade432 AE	341
	342	// add operation and action
	343	if (!empty($indexData['operation'])) $sql .= " ON ".$indexData['operation']." ".$indexData['action'];
281ac362 TD	344	if (!empty($indexData['ON DELETE'])) $sql .= " ON DELETE ".$indexData['ON DELETE'];
281ac362 TD	345	if (!empty($indexData['ON UPDATE'])) $sql .= " ON UPDATE ".$indexData['ON UPDATE'];
11ade432 AE	346
	347	$statement = $this->dbObj->prepareStatement($sql);
	348	$statement->execute();
	349	}
	350
	351	/**
0fcfe5f6	352	* @inheritDoc
11ade432 AE	353	*/
11ade432 AE	354	public function dropIndex($tableName, $indexName) {
bf64fe54 TD	355	try {
	356	$sql = "ALTER TABLE `".$tableName."` DROP INDEX `".$indexName."`";
	357	$statement = $this->dbObj->prepareStatement($sql);
	358	$statement->execute();
	359	}
	360	catch (DatabaseQueryExecutionException $e) {
	361	if ($e->getCode() != '42000') {
	362	throw $e;
	363	}
	364	if (in_array($indexName, $this->getIndices($tableName))) {
	365	throw $e;
	366	}
	367	}
11ade432 AE	368	}
11ade432 AE	369
42a1e71f	370	/**
6b7d6653	371	* @inheritDoc
42a1e71f	372	*/
42a1e71f	373	public function dropPrimaryKey($tableName) {
bf64fe54 TD	374	try {
	375	$sql = "ALTER TABLE ".$tableName." DROP PRIMARY KEY";
	376	$statement = $this->dbObj->prepareStatement($sql);
	377	$statement->execute();
	378	}
	379	catch (DatabaseQueryExecutionException $e) {
	380	if ($e->getCode() != '42000') {
	381	throw $e;
	382	}
	383	if (in_array("PRIMARY", $this->getIndices($tableName))) {
	384	throw $e;
	385	}
	386	}
42a1e71f	387	}
42a1e71f	388
b6ae7d04	389	/**
0fcfe5f6	390	* @inheritDoc
b6ae7d04 MW	391	*/
b6ae7d04 MW	392	public function dropForeignKey($tableName, $indexName) {
bf64fe54 TD	393	try {
	394	$sql = "ALTER TABLE `".$tableName."` DROP FOREIGN KEY `".$indexName."`";
	395	$statement = $this->dbObj->prepareStatement($sql);
	396	$statement->execute();
	397	}
	398	catch (DatabaseQueryExecutionException $e) {
	399	if ($e->getCode() != '42000') {
	400	throw $e;
	401	}
	402	if (in_array($indexName, array_keys($this->getForeignKeys($tableName)))) {
	403	throw $e;
	404	}
	405	}
b6ae7d04 MW	406	}
b6ae7d04 MW	407
11ade432 AE	408	/**
	409	* Builds a column definition for execution in a create table or alter table statement.
	410	*
	411	* @param string $columnName
	412	* @param array $columnData
71952a87	413	* @return string
11ade432 AE	414	*/
	415	protected function buildColumnDefinition($columnName, $columnData) {
	416	// column name
4f2c4eb8	417	$definition = "`".$columnName."`";
11ade432 AE	418	// column type
11ade432 AE	419	$definition .= " ".$columnData['type'];
f6e43f2f	420
11ade432 AE	421	// column length and decimals
	422	if (!empty($columnData['length'])) {
	423	$definition .= "(".$columnData['length'].(!empty($columnData['decimals']) ? ",".$columnData['decimals'] : "").")";
	424	}
	425	// enum / set
	426	if ($columnData['type'] == 'enum' && !empty($columnData['values'])) {
	427	$definition .= "(".$columnData['values'].")";
	428	}
	429	// not null / null
	430	if (!empty($columnData['notNull'])) $definition .= " NOT NULL";
	431	// default
	432	if (isset($columnData['default']) && $columnData['default'] !== '') $definition .= " DEFAULT ".$columnData['default'];
	433	// auto_increment
	434	if (!empty($columnData['autoIncrement'])) $definition .= " AUTO_INCREMENT";
	435	// key
	436	if (!empty($columnData['key'])) $definition .= " ".$columnData['key']." KEY";
	437
	438	return $definition;
	439	}
	440
	441	/**
	442	* Builds a index definition for execution in a create table or alter table statement.
	443	*
	444	* @param string $indexName
	445	* @param array $indexData
71952a87	446	* @return string
11ade432 AE	447	*/
11ade432 AE	448	protected function buildIndexDefinition($indexName, $indexData) {
11ade432 AE	449	// index type
	450	if ($indexData['type'] == 'PRIMARY') $definition = "PRIMARY KEY";
	451	else if ($indexData['type'] == 'UNIQUE') $definition = "UNIQUE KEY";
	452	else if ($indexData['type'] == 'FULLTEXT') $definition = "FULLTEXT KEY";
	453	else $definition = "KEY";
	454
	455	// index name
4f2c4eb8	456	if (!empty($indexName)) $definition .= " `".$indexName."`";
11ade432	457	// columns
4f2c4eb8	458	$definition .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)";
11ade432 AE	459
	460	return $definition;
	461	}
	462	}