wcfsetup/install/files/lib/system/database/editor/MySQLDatabaseEditor.class.php

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