3 namespace wcf\system\database\editor
;
5 use wcf\system\database\exception\DatabaseQueryExecutionException
;
6 use wcf\system\database\util\PreparedStatementConditionBuilder
;
10 * Database editor implementation for MySQL4.1 or higher.
13 * @copyright 2001-2019 WoltLab GmbH
14 * @license GNU Lesser General Public License <http://opensource.org/licenses/lgpl-license.php>
16 class MySQLDatabaseEditor
extends DatabaseEditor
21 public function getTableNames()
24 $sql = "SHOW TABLES FROM `" . $this->dbObj
->getDatabaseName() . "`";
25 $statement = $this->dbObj
->prepareStatement($sql);
26 $statement->execute();
27 while ($row = $statement->fetchArray(\PDO
::FETCH_NUM
)) {
28 $existingTables[] = $row[0];
31 return $existingTables;
37 public function getColumns($tableName)
40 $regex = new Regex('([a-z]+)\((.+)\)', Regex
::CASE_INSENSITIVE
);
42 $sql = "SHOW COLUMNS FROM `" . $tableName . "`";
43 $statement = $this->dbObj
->prepareStatement($sql);
44 $statement->execute();
45 while ($row = $statement->fetchArray()) {
46 $regex->match($row['Type']);
47 $typeMatches = $regex->getMatches();
53 if (!empty($typeMatches)) {
54 $type = $typeMatches[1];
59 $enumValues = $typeMatches[2];
65 $pieces = \
explode(',', $typeMatches[2]);
66 switch (\
count($pieces)) {
72 [$length, $decimals] = $pieces;
79 if ($typeMatches[2] == (int)$typeMatches[2]) {
80 $length = $typeMatches[2];
87 'name' => $row['Field'],
91 'notNull' => $row['Null'] == 'YES' ?
false : true,
92 'key' => ($row['Key'] == 'PRI') ?
'PRIMARY' : (($row['Key'] == 'UNI') ?
'UNIQUE' : ''),
93 'default' => $row['Default'],
94 'autoIncrement' => $row['Extra'] == 'auto_increment' ?
true : false,
95 'enumValues' => $enumValues,
96 'decimals' => $decimals,
107 public function getForeignKeys($tableName)
109 $sql = "SELECT CONSTRAINT_NAME, DELETE_RULE, UPDATE_RULE
110 FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
111 WHERE CONSTRAINT_SCHEMA = ?
113 $statement = $this->dbObj
->prepareStatement($sql);
114 $statement->execute([
115 $this->dbObj
->getDatabaseName(),
118 $referentialConstraints = $statement->fetchAll(\PDO
::FETCH_ASSOC
);
120 $validActions = ['CASCADE', 'SET NULL', 'NO ACTION'];
123 foreach ($referentialConstraints as $information) {
124 $foreignKeys[$information['CONSTRAINT_NAME']] = [
126 'referencedColumns' => [],
127 'ON DELETE' => \
in_array(
128 $information['DELETE_RULE'],
130 ) ?
$information['DELETE_RULE'] : null,
131 'ON UPDATE' => \
in_array(
132 $information['UPDATE_RULE'],
134 ) ?
$information['UPDATE_RULE'] : null,
138 if (empty($foreignKeys)) {
142 $conditionBuilder = new PreparedStatementConditionBuilder();
143 $conditionBuilder->add('CONSTRAINT_NAME IN (?)', [\array_keys
($foreignKeys)]);
144 $conditionBuilder->add('TABLE_SCHEMA = ?', [$this->dbObj
->getDatabaseName()]);
145 $conditionBuilder->add('TABLE_NAME = ?', [$tableName]);
147 $sql = "SELECT CONSTRAINT_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
148 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
149 " . $conditionBuilder;
150 $statement = $this->dbObj
->prepareStatement($sql);
151 $statement->execute($conditionBuilder->getParameters());
152 $keyColumnUsage = $statement->fetchAll(\PDO
::FETCH_ASSOC
);
154 foreach ($keyColumnUsage as $information) {
155 $foreignKeys[$information['CONSTRAINT_NAME']]['columns'][] = $information['COLUMN_NAME'];
156 $foreignKeys[$information['CONSTRAINT_NAME']]['referencedColumns'][] = $information['REFERENCED_COLUMN_NAME'];
157 $foreignKeys[$information['CONSTRAINT_NAME']]['referencedTable'] = $information['REFERENCED_TABLE_NAME'];
160 foreach ($foreignKeys as $keyName => $keyData) {
161 $foreignKeys[$keyName]['columns'] = \array_unique
($foreignKeys[$keyName]['columns']);
162 $foreignKeys[$keyName]['referencedColumns'] = \array_unique
($foreignKeys[$keyName]['referencedColumns']);
171 public function getIndexInformation($tableName)
174 FROM `" . $tableName . "`";
175 $statement = $this->dbObj
->prepareStatement($sql);
176 $statement->execute();
177 $indices = $statement->fetchAll(\PDO
::FETCH_ASSOC
);
179 $indexInformation = [];
180 foreach ($indices as $index) {
181 if (!isset($indexInformation[$index['Key_name']])) {
183 if ($index['Index_type'] === 'FULLTEXT') {
185 } elseif ($index['Key_name'] === 'PRIMARY') {
187 } elseif ($index['Non_unique'] == 0) {
191 $indexInformation[$index['Key_name']] = [
192 'columns' => [$index['Column_name']],
196 $indexInformation[$index['Key_name']]['columns'][] = $index['Column_name'];
200 return $indexInformation;
206 public function getIndices($tableName)
209 $sql = "SHOW INDEX FROM `" . $tableName . "`";
210 $statement = $this->dbObj
->prepareStatement($sql);
211 $statement->execute();
212 while ($row = $statement->fetchArray()) {
213 $indices[] = $row['Key_name'];
216 return \array_unique
($indices);
222 public function createTable($tableName, $columns, $indices = [])
224 $columnDefinition = $indexDefinition = '';
226 // build column definition
227 foreach ($columns as $column) {
228 if (!empty($columnDefinition)) {
229 $columnDefinition .= ',';
231 $columnDefinition .= $this->buildColumnDefinition($column['name'], $column['data']);
234 // build index definition
235 foreach ($indices as $index) {
236 if (!empty($indexDefinition)) {
237 $indexDefinition .= ',';
239 $indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']);
243 $sql = "CREATE TABLE `" . $tableName . "` (
244 " . $columnDefinition . "
245 " . (!empty($indexDefinition) ?
',' : '') . "
246 " . $indexDefinition . "
247 ) ENGINE=InnoDB ROW_FORMAT=dynamic DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
248 $statement = $this->dbObj
->prepareStatement($sql);
249 $statement->execute();
255 public function dropTable($tableName)
257 $sql = "DROP TABLE IF EXISTS `" . $tableName . "`";
258 $statement = $this->dbObj
->prepareStatement($sql);
259 $statement->execute();
265 public function addColumn($tableName, $columnName, $columnData)
267 $sql = "ALTER TABLE `" . $tableName . "` ADD COLUMN " . $this->buildColumnDefinition($columnName, $columnData);
268 $statement = $this->dbObj
->prepareStatement($sql);
269 $statement->execute();
275 public function alterColumn($tableName, $oldColumnName, $newColumnName, $newColumnData)
277 $sql = "ALTER TABLE `" . $tableName . "` CHANGE COLUMN `" . $oldColumnName . "` " . $this->buildColumnDefinition(
281 $statement = $this->dbObj
->prepareStatement($sql);
282 $statement->execute();
288 public function alterColumns($tableName, $alterData)
291 foreach ($alterData as $columnName => $data) {
292 switch ($data['action']) {
294 $queries .= "ADD COLUMN {$this->buildColumnDefinition($columnName, $data['data'])},";
298 $newColumnName = $columnName;
299 if (isset($data['oldColumnName'])) {
301 * @deprecated 5.4 `oldColumnName` was an incorrect name for the index
302 * that is kept for backwards compatibility for now
304 $newColumnName = $data['oldColumnName'];
305 } elseif (isset($data['newColumnName'])) {
306 $newColumnName = $data['newColumnName'];
309 $queries .= "CHANGE COLUMN `{$columnName}` {$this->buildColumnDefinition($newColumnName, $data['data'])},";
313 $queries .= "DROP COLUMN `{$columnName}`,";
318 $this->dbObj
->prepareStatement("ALTER TABLE `{$tableName}` " . \rtrim
($queries, ','))->execute();
324 public function dropColumn($tableName, $columnName)
327 $sql = "ALTER TABLE `" . $tableName . "` DROP COLUMN `" . $columnName . "`";
328 $statement = $this->dbObj
->prepareStatement($sql);
329 $statement->execute();
330 } catch (DatabaseQueryExecutionException
$e) {
331 if ($e->getCode() != '42000') {
334 if (\
in_array($columnName, \array_column
($this->getColumns($tableName), 'name'))) {
343 public function addIndex($tableName, $indexName, $indexData)
345 $sql = "ALTER TABLE `" . $tableName . "` ADD " . $this->buildIndexDefinition($indexName, $indexData);
346 $statement = $this->dbObj
->prepareStatement($sql);
347 $statement->execute();
353 public function addForeignKey($tableName, $indexName, $indexData)
355 $sql = "ALTER TABLE `" . $tableName . "` ADD";
358 if (!empty($indexName)) {
359 $sql .= " CONSTRAINT `" . $indexName . "`";
363 $sql .= " FOREIGN KEY (`" . \
str_replace(',', '`,`', \
preg_replace('/\s+/', '', $indexData['columns'])) . "`)";
365 // add referenced table name
366 $sql .= " REFERENCES `" . $indexData['referencedTable'] . "`";
368 // add referenced columns
369 $sql .= " (`" . \
str_replace(',', '`,`', \
preg_replace('/\s+/', '', $indexData['referencedColumns'])) . "`)";
371 // add operation and action
372 if (!empty($indexData['operation'])) {
373 $sql .= " ON " . $indexData['operation'] . " " . $indexData['action'];
375 if (!empty($indexData['ON DELETE'])) {
376 $sql .= " ON DELETE " . $indexData['ON DELETE'];
378 if (!empty($indexData['ON UPDATE'])) {
379 $sql .= " ON UPDATE " . $indexData['ON UPDATE'];
382 $statement = $this->dbObj
->prepareStatement($sql);
383 $statement->execute();
389 public function dropIndex($tableName, $indexName)
392 $sql = "ALTER TABLE `" . $tableName . "` DROP INDEX `" . $indexName . "`";
393 $statement = $this->dbObj
->prepareStatement($sql);
394 $statement->execute();
395 } catch (DatabaseQueryExecutionException
$e) {
396 if ($e->getCode() != '42000') {
399 if (\
in_array($indexName, $this->getIndices($tableName))) {
408 public function dropPrimaryKey($tableName)
411 $sql = "ALTER TABLE " . $tableName . " DROP PRIMARY KEY";
412 $statement = $this->dbObj
->prepareStatement($sql);
413 $statement->execute();
414 } catch (DatabaseQueryExecutionException
$e) {
415 if ($e->getCode() != '42000') {
418 if (\
in_array("PRIMARY", $this->getIndices($tableName))) {
427 public function dropForeignKey($tableName, $indexName)
430 $sql = "ALTER TABLE `" . $tableName . "` DROP FOREIGN KEY `" . $indexName . "`";
431 $statement = $this->dbObj
->prepareStatement($sql);
432 $statement->execute();
433 } catch (DatabaseQueryExecutionException
$e) {
434 if ($e->getCode() != '42000') {
437 if (\
in_array($indexName, \array_keys
($this->getForeignKeys($tableName)))) {
444 * Builds a column definition for execution in a create table or alter table statement.
446 * @param string $columnName
447 * @param array $columnData
450 protected function buildColumnDefinition($columnName, $columnData)
453 $definition = "`" . $columnName . "`";
455 $definition .= " " . $columnData['type'];
457 // column length and decimals
458 if (!empty($columnData['length'])) {
459 $definition .= "(" . $columnData['length'] . (!empty($columnData['decimals']) ?
"," . $columnData['decimals'] : "") . ")";
462 if ($columnData['type'] == 'enum' && !empty($columnData['values'])) {
463 $definition .= "(" . $columnData['values'] . ")";
466 if (!empty($columnData['notNull'])) {
467 $definition .= " NOT NULL";
469 $definition .= " NULL";
472 if (isset($columnData['default']) && $columnData['default'] !== '') {
473 $definition .= " DEFAULT " . $columnData['default'];
476 if (!empty($columnData['autoIncrement'])) {
477 $definition .= " AUTO_INCREMENT";
480 if (!empty($columnData['key'])) {
481 $definition .= " " . $columnData['key'] . " KEY";
488 * Builds a index definition for execution in a create table or alter table statement.
490 * @param string $indexName
491 * @param array $indexData
494 protected function buildIndexDefinition($indexName, $indexData)
497 if ($indexData['type'] == 'PRIMARY') {
498 $definition = "PRIMARY KEY";
499 } elseif ($indexData['type'] == 'UNIQUE') {
500 $definition = "UNIQUE KEY";
501 } elseif ($indexData['type'] == 'FULLTEXT') {
502 $definition = "FULLTEXT KEY";
508 if (!empty($indexName) && $indexData['type'] !== 'PRIMARY') {
509 $definition .= " `" . $indexName . "`";
512 $definition .= " (`" . \
str_replace(',', '`,`', \
preg_replace('/\s+/', '', $indexData['columns'])) . "`)";