2 namespace wcf\system\database\editor
;
3 use wcf\system\database\exception\DatabaseQueryExecutionException
;
4 use wcf\system\database\util\PreparedStatementConditionBuilder
;
8 * Database editor implementation for MySQL4.1 or higher.
11 * @copyright 2001-2019 WoltLab GmbH
12 * @license GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php>
13 * @package WoltLabSuite\Core\System\Database\Editor
15 class MySQLDatabaseEditor
extends DatabaseEditor
{
19 public function getTableNames() {
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];
27 return $existingTables;
33 public function getColumns($tableName) {
35 $regex = new Regex('([a-z]+)\((.+)\)', Regex
::CASE_INSENSITIVE
);
37 $sql = "SHOW COLUMNS FROM `".$tableName."`";
38 $statement = $this->dbObj
->prepareStatement($sql);
39 $statement->execute();
40 while ($row = $statement->fetchArray()) {
41 $regex->match($row['Type']);
42 $typeMatches = $regex->getMatches();
48 if (!empty($typeMatches)) {
49 $type = $typeMatches[1];
54 $enumValues = $typeMatches[2];
60 $pieces = explode(',', $typeMatches[2]);
61 switch (count($pieces)) {
67 list($length, $decimals) = $pieces;
74 if ($typeMatches[2] == (int)$typeMatches[2]) {
75 $length = $typeMatches[2];
81 $columns[] = ['name' => $row['Field'], 'data' => [
84 'notNull' => $row['Null'] == 'YES' ?
false : true,
85 'key' => ($row['Key'] == 'PRI') ?
'PRIMARY' : (($row['Key'] == 'UNI') ?
'UNIQUE' : ''),
86 'default' => $row['Default'],
87 'autoIncrement' => $row['Extra'] == 'auto_increment' ?
true : false,
88 'enumValues' => $enumValues,
89 'decimals' => $decimals
99 public function getForeignKeys($tableName) {
100 $sql = "SELECT CONSTRAINT_NAME, DELETE_RULE, UPDATE_RULE
101 FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
102 WHERE CONSTRAINT_SCHEMA = ?
104 $statement = $this->dbObj
->prepareStatement($sql);
105 $statement->execute([
106 $this->dbObj
->getDatabaseName(),
109 $referentialConstraints = $statement->fetchAll(\PDO
::FETCH_ASSOC
);
111 $validActions = ['CASCADE', 'SET NULL', 'NO ACTION'];
114 foreach ($referentialConstraints as $information) {
115 $foreignKeys[$information['CONSTRAINT_NAME']] = [
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
123 if (empty($foreignKeys)) {
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]);
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
);
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'];
145 foreach ($foreignKeys as $keyName => $keyData) {
146 $foreignKeys[$keyName]['columns'] = array_unique($foreignKeys[$keyName]['columns']);
147 $foreignKeys[$keyName]['referencedColumns'] = array_unique($foreignKeys[$keyName]['referencedColumns']);
156 public function getIndexInformation($tableName) {
158 FROM `".$tableName."`";
159 $statement = $this->dbObj
->prepareStatement($sql);
160 $statement->execute();
161 $indices = $statement->fetchAll(\PDO
::FETCH_ASSOC
);
163 $indexInformation = [];
164 foreach ($indices as $index) {
165 if (!isset($indexInformation[$index['Key_name']])) {
167 if ($index['Index_type'] === 'FULLTEXT') {
170 else if ($index['Key_name'] === 'PRIMARY') {
173 else if ($index['Non_unique'] == 0) {
177 $indexInformation[$index['Key_name']] = [
178 'columns' => [$index['Column_name']],
183 $indexInformation[$index['Key_name']]['columns'][] = $index['Column_name'];
187 return $indexInformation;
193 public function getIndices($tableName) {
195 $sql = "SHOW INDEX FROM `".$tableName."`";
196 $statement = $this->dbObj
->prepareStatement($sql);
197 $statement->execute();
198 while ($row = $statement->fetchArray()) {
199 $indices[] = $row['Key_name'];
202 return array_unique($indices);
208 public function createTable($tableName, $columns, $indices = []) {
209 $columnDefinition = $indexDefinition = '';
211 // build column definition
212 foreach ($columns as $column) {
213 if (!empty($columnDefinition)) $columnDefinition .= ',';
214 $columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']);
217 // build index definition
218 foreach ($indices as $index) {
219 if (!empty($indexDefinition)) $indexDefinition .= ',';
220 $indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']);
224 $sql = "CREATE TABLE `".$tableName."` (
225 ".$columnDefinition."
226 ".(!empty($indexDefinition) ?
',' : '')."
228 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
229 $statement = $this->dbObj
->prepareStatement($sql);
230 $statement->execute();
236 public function dropTable($tableName) {
237 $sql = "DROP TABLE IF EXISTS `".$tableName."`";
238 $statement = $this->dbObj
->prepareStatement($sql);
239 $statement->execute();
245 public function addColumn($tableName, $columnName, $columnData) {
246 $sql = "ALTER TABLE `".$tableName."` ADD COLUMN ".$this->buildColumnDefinition($columnName, $columnData);
247 $statement = $this->dbObj
->prepareStatement($sql);
248 $statement->execute();
254 public function alterColumn($tableName, $oldColumnName, $newColumnName, $newColumnData) {
255 $sql = "ALTER TABLE `".$tableName."` CHANGE COLUMN `".$oldColumnName."` ".$this->buildColumnDefinition($newColumnName, $newColumnData);
256 $statement = $this->dbObj
->prepareStatement($sql);
257 $statement->execute();
263 public function alterColumns($tableName, $alterData) {
265 foreach ($alterData as $columnName => $data) {
266 switch ($data['action']) {
268 $queries .= "ADD COLUMN {$this->buildColumnDefinition($columnName, $data['data'])},";
272 $newColumnName = $columnName;
273 if (isset($data['oldColumnName'])) {
275 * @deprecated 5.4 `oldColumnName` was an incorrect name for the index
276 * that is kept for backwards compatibility for now
278 $newColumnName = $data['oldColumnName'];
280 else if (isset($data['newColumnName'])) {
281 $newColumnName = $data['newColumnName'];
284 $queries .= "CHANGE COLUMN `{$columnName}` {$this->buildColumnDefinition($newColumnName, $data['data'])},";
288 $queries .= "DROP COLUMN `{$columnName}`,";
293 $this->dbObj
->prepareStatement("ALTER TABLE `{$tableName}` " . rtrim($queries, ','))->execute();
299 public function dropColumn($tableName, $columnName) {
301 $sql = "ALTER TABLE `".$tableName."` DROP COLUMN `".$columnName."`";
302 $statement = $this->dbObj
->prepareStatement($sql);
303 $statement->execute();
305 catch (DatabaseQueryExecutionException
$e) {
306 if ($e->getCode() != '42000') {
309 if (in_array($columnName, array_column($this->getColumns($tableName), 'name'))) {
318 public function addIndex($tableName, $indexName, $indexData) {
319 $sql = "ALTER TABLE `".$tableName."` ADD ".$this->buildIndexDefinition($indexName, $indexData);
320 $statement = $this->dbObj
->prepareStatement($sql);
321 $statement->execute();
327 public function addForeignKey($tableName, $indexName, $indexData) {
328 $sql = "ALTER TABLE `".$tableName."` ADD";
331 if (!empty($indexName)) $sql .= " CONSTRAINT `".$indexName."`";
334 $sql .= " FOREIGN KEY (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)";
336 // add referenced table name
337 $sql .= " REFERENCES `".$indexData['referencedTable']."`";
339 // add referenced columns
340 $sql .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['referencedColumns']))."`)";
342 // add operation and action
343 if (!empty($indexData['operation'])) $sql .= " ON ".$indexData['operation']." ".$indexData['action'];
344 if (!empty($indexData['ON DELETE'])) $sql .= " ON DELETE ".$indexData['ON DELETE'];
345 if (!empty($indexData['ON UPDATE'])) $sql .= " ON UPDATE ".$indexData['ON UPDATE'];
347 $statement = $this->dbObj
->prepareStatement($sql);
348 $statement->execute();
354 public function dropIndex($tableName, $indexName) {
356 $sql = "ALTER TABLE `".$tableName."` DROP INDEX `".$indexName."`";
357 $statement = $this->dbObj
->prepareStatement($sql);
358 $statement->execute();
360 catch (DatabaseQueryExecutionException
$e) {
361 if ($e->getCode() != '42000') {
364 if (in_array($indexName, $this->getIndices($tableName))) {
373 public function dropPrimaryKey($tableName) {
375 $sql = "ALTER TABLE ".$tableName." DROP PRIMARY KEY";
376 $statement = $this->dbObj
->prepareStatement($sql);
377 $statement->execute();
379 catch (DatabaseQueryExecutionException
$e) {
380 if ($e->getCode() != '42000') {
383 if (in_array("PRIMARY", $this->getIndices($tableName))) {
392 public function dropForeignKey($tableName, $indexName) {
394 $sql = "ALTER TABLE `".$tableName."` DROP FOREIGN KEY `".$indexName."`";
395 $statement = $this->dbObj
->prepareStatement($sql);
396 $statement->execute();
398 catch (DatabaseQueryExecutionException
$e) {
399 if ($e->getCode() != '42000') {
402 if (in_array($indexName, array_keys($this->getForeignKeys($tableName)))) {
409 * Builds a column definition for execution in a create table or alter table statement.
411 * @param string $columnName
412 * @param array $columnData
415 protected function buildColumnDefinition($columnName, $columnData) {
417 $definition = "`".$columnName."`";
419 $definition .= " ".$columnData['type'];
421 // column length and decimals
422 if (!empty($columnData['length'])) {
423 $definition .= "(".$columnData['length'].(!empty($columnData['decimals']) ?
",".$columnData['decimals'] : "").")";
426 if ($columnData['type'] == 'enum' && !empty($columnData['values'])) {
427 $definition .= "(".$columnData['values'].")";
430 if (!empty($columnData['notNull'])) $definition .= " NOT NULL";
432 if (isset($columnData['default']) && $columnData['default'] !== '') $definition .= " DEFAULT ".$columnData['default'];
434 if (!empty($columnData['autoIncrement'])) $definition .= " AUTO_INCREMENT";
436 if (!empty($columnData['key'])) $definition .= " ".$columnData['key']." KEY";
442 * Builds a index definition for execution in a create table or alter table statement.
444 * @param string $indexName
445 * @param array $indexData
448 protected function buildIndexDefinition($indexName, $indexData) {
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";
456 if (!empty($indexName)) $definition .= " `".$indexName."`";
458 $definition .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)";