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

<?php
namespace wcf\system\database\editor;
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]+)\(([0-9]+)\)', 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();
			
			$columns[] = ['name' => $row['Field'], 'data' => [
				'type' => empty($typeMatches) ? $row['Type'] : $typeMatches[1],
				'length' => empty($typeMatches) ? '' : $typeMatches[2],
				'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
			]];
		}
		
		return $columns;
	}
	
	/**
	 * @inheritDoc
	 */
	public function getForeignKeys($tableName) {
		$sql = "SELECT		key_column_usage.CONSTRAINT_NAME,
					key_column_usage.COLUMN_NAME,
					key_column_usage.REFERENCED_TABLE_NAME,
					key_column_usage.REFERENCED_COLUMN_NAME,
					referential_constraints.DELETE_RULE,
					referential_constraints.UPDATE_RULE
			FROM		INFORMATION_SCHEMA.KEY_COLUMN_USAGE key_column_usage
			INNER JOIN	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS referential_constraints
			ON 		(referential_constraints.CONSTRAINT_NAME = key_column_usage.CONSTRAINT_NAME
					AND referential_constraints.CONSTRAINT_SCHEMA = key_column_usage.TABLE_SCHEMA
					AND referential_constraints.TABLE_NAME = key_column_usage.TABLE_NAME)
			WHERE		key_column_usage.TABLE_SCHEMA = ?
					AND key_column_usage.TABLE_NAME = ?";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute([
			$this->dbObj->getDatabaseName(),
			$tableName
		]);
		$keyInformation = $statement->fetchAll(\PDO::FETCH_ASSOC);
		
		$validActions = ['CASCADE', 'SET NULL', 'NO ACTION'];
		
		$foreignKeys = [];
		foreach ($keyInformation as $information) {
			if (!isset($foreignKeys[$information['CONSTRAINT_NAME']])) {
				$foreignKeys[$information['CONSTRAINT_NAME']] = [
					'columns' => [$information['COLUMN_NAME']],
					'referencedColumns' => [$information['REFERENCED_COLUMN_NAME']],
					'referencedTable' => $information['REFERENCED_TABLE_NAME'],
					'ON DELETE' => in_array($information['DELETE_RULE'], $validActions) ? $information['DELETE_RULE'] : null,
					'ON UPDATE' => in_array($information['UPDATE_RULE'], $validActions) ? $information['UPDATE_RULE'] : null
				];
			}
			else {
				$foreignKeys[$information['CONSTRAINT_NAME']]['columns'][] = $information['COLUMN_NAME'];
				$foreignKeys[$information['CONSTRAINT_NAME']]['referencedColumns'][] = $information['REFERENCED_COLUMN_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 $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
		$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=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 dropColumn($tableName, $columnName) {
		$sql = "ALTER TABLE `".$tableName."` DROP COLUMN `".$columnName."`";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @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) {
		$sql = "ALTER TABLE `".$tableName."` DROP INDEX `".$indexName."`";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @inheritDoc
	 */
	public function dropPrimaryKey($tableName) {
		$sql = "ALTER TABLE ".$tableName." DROP PRIMARY KEY";
		$statement = $this->dbObj->prepareStatement($sql);
		$statement->execute();
	}
	
	/**
	 * @inheritDoc
	 */
	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
	 * @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;
82a7de7f	3	use wcf\system\Regex;
11ade432 AE	4
11ade432 AE	5	/**
a17de04e MS	6	* Database editor implementation for MySQL4.1 or higher.
a17de04e MS	7	*
11ade432	8	* @author Marcel Werk
7b7b9764	9	* @copyright 2001-2019 WoltLab GmbH
11ade432	10	* @license GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php>
e71525e4	11	* @package WoltLabSuite\Core\System\Database\Editor
11ade432 AE	12	*/
	13	class MySQLDatabaseEditor extends DatabaseEditor {
	14	/**
0fcfe5f6	15	* @inheritDoc
11ade432 AE	16	*/
11ade432 AE	17	public function getTableNames() {
058cbd6a	18	$existingTables = [];
11ade432 AE	19	$sql = "SHOW TABLES FROM `".$this->dbObj->getDatabaseName()."`";
	20	$statement = $this->dbObj->prepareStatement($sql);
	21	$statement->execute();
	22	while ($row = $statement->fetchArray(\PDO::FETCH_NUM)) {
	23	$existingTables[] = $row[0];
	24	}
	25	return $existingTables;
	26	}
	27
	28	/**
0fcfe5f6	29	* @inheritDoc
11ade432 AE	30	*/
11ade432 AE	31	public function getColumns($tableName) {
058cbd6a	32	$columns = [];
14d48464	33	$regex = new Regex('([a-z]+)\(([0-9]+)\)', Regex::CASE_INSENSITIVE);
77bb36ca	34
4f2c4eb8	35	$sql = "SHOW COLUMNS FROM `".$tableName."`";
11ade432 AE	36	$statement = $this->dbObj->prepareStatement($sql);
	37	$statement->execute();
	38	while ($row = $statement->fetchArray()) {
14d48464	39	$regex->match($row['Type']);
14d48464	40	$typeMatches = $regex->getMatches();
77bb36ca	41
058cbd6a	42	$columns[] = ['name' => $row['Field'], 'data' => [
63b9817b MS	43	'type' => empty($typeMatches) ? $row['Type'] : $typeMatches[1],
	44	'length' => empty($typeMatches) ? '' : $typeMatches[2],
	45	'notNull' => ($row['Null'] == 'YES') ? false : true,
	46	'key' => ($row['Key'] == 'PRI') ? 'PRIMARY' : (($row['Key'] == 'UNI') ? 'UNIQUE' : ''),
5fe135db	47	'default' => $row['Default'],
63b9817b	48	'autoIncrement' => $row['Extra'] == 'auto_increment' ? true : false
058cbd6a	49	]];
ebe8e825	50	}
bf27dc91	51
9f959ced	52	return $columns;
11ade432 AE	53	}
11ade432 AE	54
dc62342b MS	55	/**
	56	* @inheritDoc
	57	*/
	58	public function getForeignKeys($tableName) {
	59	$sql = "SELECT key_column_usage.CONSTRAINT_NAME,
	60	key_column_usage.COLUMN_NAME,
	61	key_column_usage.REFERENCED_TABLE_NAME,
	62	key_column_usage.REFERENCED_COLUMN_NAME,
	63	referential_constraints.DELETE_RULE,
	64	referential_constraints.UPDATE_RULE
	65	FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE key_column_usage
	66	INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS referential_constraints
	67	ON (referential_constraints.CONSTRAINT_NAME = key_column_usage.CONSTRAINT_NAME
	68	AND referential_constraints.CONSTRAINT_SCHEMA = key_column_usage.TABLE_SCHEMA
	69	AND referential_constraints.TABLE_NAME = key_column_usage.TABLE_NAME)
	70	WHERE key_column_usage.TABLE_SCHEMA = ?
	71	AND key_column_usage.TABLE_NAME = ?";
	72	$statement = $this->dbObj->prepareStatement($sql);
	73	$statement->execute([
	74	$this->dbObj->getDatabaseName(),
	75	$tableName
	76	]);
	77	$keyInformation = $statement->fetchAll(\PDO::FETCH_ASSOC);
	78
	79	$validActions = ['CASCADE', 'SET NULL', 'NO ACTION'];
	80
	81	$foreignKeys = [];
	82	foreach ($keyInformation as $information) {
	83	if (!isset($foreignKeys[$information['CONSTRAINT_NAME']])) {
	84	$foreignKeys[$information['CONSTRAINT_NAME']] = [
	85	'columns' => [$information['COLUMN_NAME']],
	86	'referencedColumns' => [$information['REFERENCED_COLUMN_NAME']],
	87	'referencedTable' => $information['REFERENCED_TABLE_NAME'],
	88	'ON DELETE' => in_array($information['DELETE_RULE'], $validActions) ? $information['DELETE_RULE'] : null,
	89	'ON UPDATE' => in_array($information['UPDATE_RULE'], $validActions) ? $information['UPDATE_RULE'] : null
	90	];
	91	}
	92	else {
	93	$foreignKeys[$information['CONSTRAINT_NAME']]['columns'][] = $information['COLUMN_NAME'];
	94	$foreignKeys[$information['CONSTRAINT_NAME']]['referencedColumns'][] = $information['REFERENCED_COLUMN_NAME'];
	95	}
	96	}
	97
	98	foreach ($foreignKeys as $keyName => $keyData) {
	99	$foreignKeys[$keyName]['columns'] = array_unique($foreignKeys[$keyName]['columns']);
	100	$foreignKeys[$keyName]['referencedColumns'] = array_unique($foreignKeys[$keyName]['referencedColumns']);
	101	}
	102
	103	return $foreignKeys;
	104	}
	105
	106	/**
	107	* @inheritDoc
	108	*/
	109	public function getIndexInformation($tableName) {
	110	$sql = "SHOW INDEX
	111	FROM `".$tableName."`";
	112	$statement = $this->dbObj->prepareStatement($sql);
	113	$statement->execute();
	114	$indices = $statement->fetchAll(\PDO::FETCH_ASSOC);
	115
	116	$indexInformation = [];
	117	foreach ($indices as $index) {
	118	if (!isset($indexInformation[$index['Key_name']])) {
119	$type = null;
120	if ($index['Index_type'] === 'FULLTEXT') {
121	$type = 'FULLTEXT';
122	}
123	else if ($index['Key_name'] === 'PRIMARY') {
124	$type = 'PRIMARY';
125	}
126	else if ($index['Non_unique'] == 0) {
127	$type = 'UNIQUE';
128	}
129
130	$indexInformation[$index['Key_name']] = [
131	'columns' => [$index['Column_name']],
132	'type' => $type
133	];
134	}
135	else {
136	$indexInformation[$index['Key_name']]['columns'][] = $index['Column_name'];
137	}
138	}
139
140	return $indexInformation;
141	}
142
11ade432	143	/**
0fcfe5f6	144	* @inheritDoc
11ade432 AE	145	*/
11ade432 AE	146	public function getIndices($tableName) {
058cbd6a	147	$indices = [];
4f2c4eb8	148	$sql = "SHOW INDEX FROM `".$tableName."`";
11ade432 AE	149	$statement = $this->dbObj->prepareStatement($sql);
	150	$statement->execute();
	151	while ($row = $statement->fetchArray()) {
9f959ced MS	152	$indices[] = $row['Key_name'];
	153	}
	154
	155	return $indices;
11ade432 AE	156	}
	157
	158	/**
0fcfe5f6	159	* @inheritDoc
11ade432	160	*/
058cbd6a	161	public function createTable($tableName, $columns, $indices = []) {
11ade432 AE	162	$columnDefinition = $indexDefinition = '';
	163
	164	// build column definition
	165	foreach ($columns as $column) {
	166	if (!empty($columnDefinition)) $columnDefinition .= ',';
	167	$columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']);
	168	}
	169
	170	// build index definition
	171	$hasFulltextIndex = false;
	172	foreach ($indices as $index) {
	173	if (!empty($indexDefinition)) $indexDefinition .= ',';
	174	$indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']);
	175	if ($index['data']['type'] == 'FULLTEXT') $hasFulltextIndex = true;
	176	}
	177
	178	// create table
4f2c4eb8	179	$sql = "CREATE TABLE `".$tableName."` (
11ade432 AE	180	".$columnDefinition."
	181	".(!empty($indexDefinition) ? ',' : '')."
	182	".$indexDefinition."
ac677ff6	183	) ENGINE=".($hasFulltextIndex ? 'MyISAM' : 'InnoDB')." DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
11ade432 AE	184	$statement = $this->dbObj->prepareStatement($sql);
	185	$statement->execute();
	186	}
	187
	188	/**
0fcfe5f6	189	* @inheritDoc
11ade432 AE	190	*/
11ade432 AE	191	public function dropTable($tableName) {
4f2c4eb8	192	$sql = "DROP TABLE IF EXISTS `".$tableName."`";
11ade432 AE	193	$statement = $this->dbObj->prepareStatement($sql);
	194	$statement->execute();
	195	}
	196
	197	/**
0fcfe5f6	198	* @inheritDoc
11ade432 AE	199	*/
11ade432 AE	200	public function addColumn($tableName, $columnName, $columnData) {
4f2c4eb8	201	$sql = "ALTER TABLE `".$tableName."` ADD COLUMN ".$this->buildColumnDefinition($columnName, $columnData);
11ade432 AE	202	$statement = $this->dbObj->prepareStatement($sql);
	203	$statement->execute();
	204	}
	205
	206	/**
0fcfe5f6	207	* @inheritDoc
11ade432 AE	208	*/
11ade432 AE	209	public function alterColumn($tableName, $oldColumnName, $newColumnName, $newColumnData) {
4f2c4eb8	210	$sql = "ALTER TABLE `".$tableName."` CHANGE COLUMN `".$oldColumnName."` ".$this->buildColumnDefinition($newColumnName, $newColumnData);
11ade432 AE	211	$statement = $this->dbObj->prepareStatement($sql);
	212	$statement->execute();
	213	}
	214
	215	/**
0fcfe5f6	216	* @inheritDoc
11ade432 AE	217	*/
11ade432 AE	218	public function dropColumn($tableName, $columnName) {
4f2c4eb8	219	$sql = "ALTER TABLE `".$tableName."` DROP COLUMN `".$columnName."`";
11ade432 AE	220	$statement = $this->dbObj->prepareStatement($sql);
	221	$statement->execute();
	222	}
	223
	224	/**
0fcfe5f6	225	* @inheritDoc
11ade432 AE	226	*/
11ade432 AE	227	public function addIndex($tableName, $indexName, $indexData) {
4f2c4eb8	228	$sql = "ALTER TABLE `".$tableName."` ADD ".$this->buildIndexDefinition($indexName, $indexData);
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 addForeignKey($tableName, $indexName, $indexData) {
4f2c4eb8	237	$sql = "ALTER TABLE `".$tableName."` ADD";
11ade432 AE	238
11ade432 AE	239	// add index name
96017679	240	if (!empty($indexName)) $sql .= " CONSTRAINT `".$indexName."`";
11ade432 AE	241
11ade432 AE	242	// add columns
4f2c4eb8	243	$sql .= " FOREIGN KEY (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)";
11ade432 AE	244
11ade432 AE	245	// add referenced table name
4f2c4eb8	246	$sql .= " REFERENCES `".$indexData['referencedTable']."`";
11ade432 AE	247
11ade432 AE	248	// add referenced columns
4f2c4eb8	249	$sql .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['referencedColumns']))."`)";
11ade432 AE	250
	251	// add operation and action
	252	if (!empty($indexData['operation'])) $sql .= " ON ".$indexData['operation']." ".$indexData['action'];
281ac362 TD	253	if (!empty($indexData['ON DELETE'])) $sql .= " ON DELETE ".$indexData['ON DELETE'];
281ac362 TD	254	if (!empty($indexData['ON UPDATE'])) $sql .= " ON UPDATE ".$indexData['ON UPDATE'];
11ade432 AE	255
	256	$statement = $this->dbObj->prepareStatement($sql);
	257	$statement->execute();
	258	}
	259
	260	/**
0fcfe5f6	261	* @inheritDoc
11ade432 AE	262	*/
11ade432 AE	263	public function dropIndex($tableName, $indexName) {
4f2c4eb8	264	$sql = "ALTER TABLE `".$tableName."` DROP INDEX `".$indexName."`";
11ade432 AE	265	$statement = $this->dbObj->prepareStatement($sql);
	266	$statement->execute();
	267	}
	268
42a1e71f	269	/**
6b7d6653	270	* @inheritDoc
42a1e71f	271	*/
	272	public function dropPrimaryKey($tableName) {
	273	$sql = "ALTER TABLE ".$tableName." DROP PRIMARY KEY";
	274	$statement = $this->dbObj->prepareStatement($sql);
	275	$statement->execute();
	276	}
	277
b6ae7d04	278	/**
0fcfe5f6	279	* @inheritDoc
b6ae7d04 MW	280	*/
b6ae7d04 MW	281	public function dropForeignKey($tableName, $indexName) {
4f2c4eb8	282	$sql = "ALTER TABLE `".$tableName."` DROP FOREIGN KEY `".$indexName."`";
b6ae7d04 MW	283	$statement = $this->dbObj->prepareStatement($sql);
	284	$statement->execute();
	285	}
	286
11ade432 AE	287	/**
	288	* Builds a column definition for execution in a create table or alter table statement.
	289	*
	290	* @param string $columnName
	291	* @param array $columnData
71952a87	292	* @return string
11ade432 AE	293	*/
	294	protected function buildColumnDefinition($columnName, $columnData) {
	295	// column name
4f2c4eb8	296	$definition = "`".$columnName."`";
11ade432 AE	297	// column type
	298	$definition .= " ".$columnData['type'];
	299	// column length and decimals
	300	if (!empty($columnData['length'])) {
	301	$definition .= "(".$columnData['length'].(!empty($columnData['decimals']) ? ",".$columnData['decimals'] : "").")";
	302	}
	303	// enum / set
	304	if ($columnData['type'] == 'enum' && !empty($columnData['values'])) {
	305	$definition .= "(".$columnData['values'].")";
	306	}
	307	// not null / null
	308	if (!empty($columnData['notNull'])) $definition .= " NOT NULL";
	309	// default
	310	if (isset($columnData['default']) && $columnData['default'] !== '') $definition .= " DEFAULT ".$columnData['default'];
	311	// auto_increment
	312	if (!empty($columnData['autoIncrement'])) $definition .= " AUTO_INCREMENT";
	313	// key
	314	if (!empty($columnData['key'])) $definition .= " ".$columnData['key']." KEY";
	315
	316	return $definition;
	317	}
	318
	319	/**
	320	* Builds a index definition for execution in a create table or alter table statement.
	321	*
	322	* @param string $indexName
	323	* @param array $indexData
71952a87	324	* @return string
11ade432 AE	325	*/
11ade432 AE	326	protected function buildIndexDefinition($indexName, $indexData) {
11ade432 AE	327	// index type
	328	if ($indexData['type'] == 'PRIMARY') $definition = "PRIMARY KEY";
	329	else if ($indexData['type'] == 'UNIQUE') $definition = "UNIQUE KEY";
	330	else if ($indexData['type'] == 'FULLTEXT') $definition = "FULLTEXT KEY";
	331	else $definition = "KEY";
	332
	333	// index name
4f2c4eb8	334	if (!empty($indexName)) $definition .= " `".$indexName."`";
11ade432	335	// columns
4f2c4eb8	336	$definition .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)";
11ade432 AE	337
	338	return $definition;
	339	}
	340	}