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

   1 <?php
   2 namespace wcf\system\database\editor;
   3 use wcf\system\database\exception\DatabaseQueryExecutionException;
   4 use wcf\system\database\util\PreparedStatementConditionBuilder;
   5 use wcf\system\Regex;
   6
   7 /**
   8  * Database editor implementation for MySQL4.1 or higher.
   9  *
  10  * @author      Marcel Werk
  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
  14  */
  15 class MySQLDatabaseEditor extends DatabaseEditor {
  16         /**
  17          * @inheritDoc
  18          */
  19         public function getTableNames() {
  20                 $existingTables = [];
  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         /**
  31          * @inheritDoc
  32          */
  33         public function getColumns($tableName) {
  34                 $columns = [];
  35                 $regex = new Regex('([a-z]+)\((.+)\)', Regex::CASE_INSENSITIVE);
  36
  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();
  43
  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
  81                         $columns[] = ['name' => $row['Field'], 'data' => [
  82                                 'type' => $type,
  83                                 'length' => $length,
  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
  90                         ]];
  91                 }
  92
  93                 return $columns;
  94         }
  95
  96         /**
  97          * @inheritDoc
  98          */
  99         public function getForeignKeys($tableName) {
 100                 $sql = "SELECT  CONSTRAINT_NAME, DELETE_RULE, UPDATE_RULE
 101                         FROM    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
 102                         WHERE   CONSTRAINT_SCHEMA = ?
 103                                 AND TABLE_NAME = ?";
 104                 $statement = $this->dbObj->prepareStatement($sql);
 105                 $statement->execute([
 106                         $this->dbObj->getDatabaseName(),
 107                         $tableName
 108                 ]);
 109                 $referentialConstraints = $statement->fetchAll(\PDO::FETCH_ASSOC);
 110
 111                 $validActions = ['CASCADE', 'SET NULL', 'NO ACTION'];
 112
 113                 $foreignKeys = [];
 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'];
 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
 190         /**
 191          * @inheritDoc
 192          */
 193         public function getIndices($tableName) {
 194                 $indices = [];
 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'];
 200                 }
 201
 202                 return array_unique($indices);
 203         }
 204
 205         /**
 206          * @inheritDoc
 207          */
 208         public function createTable($tableName, $columns, $indices = []) {
 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
 218                 foreach ($indices as $index) {
 219                         if (!empty($indexDefinition)) $indexDefinition .= ',';
 220                         $indexDefinition .= $this->buildIndexDefinition($index['name'], $index['data']);
 221                 }
 222
 223                 // create table
 224                 $sql = "CREATE TABLE `".$tableName."` (
 225                                 ".$columnDefinition."
 226                                 ".(!empty($indexDefinition) ? ',' : '')."
 227                                 ".$indexDefinition."
 228                         ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
 229                 $statement = $this->dbObj->prepareStatement($sql);
 230                 $statement->execute();
 231         }
 232
 233         /**
 234          * @inheritDoc
 235          */
 236         public function dropTable($tableName) {
 237                 $sql = "DROP TABLE IF EXISTS `".$tableName."`";
 238                 $statement = $this->dbObj->prepareStatement($sql);
 239                 $statement->execute();
 240         }
 241
 242         /**
 243          * @inheritDoc
 244          */
 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();
 249         }
 250
 251         /**
 252          * @inheritDoc
 253          */
 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();
 258         }
 259
 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':
 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'])},";
 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
 296         /**
 297          * @inheritDoc
 298          */
 299         public function dropColumn($tableName, $columnName) {
 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                 }
 313         }
 314
 315         /**
 316          * @inheritDoc
 317          */
 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();
 322         }
 323
 324         /**
 325          * @inheritDoc
 326          */
 327         public function addForeignKey($tableName, $indexName, $indexData) {
 328                 $sql = "ALTER TABLE `".$tableName."` ADD";
 329
 330                 // add index name
 331                 if (!empty($indexName)) $sql .= " CONSTRAINT `".$indexName."`";
 332
 333                 // add columns
 334                 $sql .= " FOREIGN KEY (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)";
 335
 336                 // add referenced table name
 337                 $sql .= " REFERENCES `".$indexData['referencedTable']."`";
 338
 339                 // add referenced columns
 340                 $sql .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['referencedColumns']))."`)";
 341
 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'];
 346
 347                 $statement = $this->dbObj->prepareStatement($sql);
 348                 $statement->execute();
 349         }
 350
 351         /**
 352          * @inheritDoc
 353          */
 354         public function dropIndex($tableName, $indexName) {
 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                 }
 368         }
 369
 370         /**
 371          * @inheritDoc
 372          */
 373         public function dropPrimaryKey($tableName) {
 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                 }
 387         }
 388
 389         /**
 390          * @inheritDoc
 391          */
 392         public function dropForeignKey($tableName, $indexName) {
 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                 }
 406         }
 407
 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
 413          * @return      string
 414          */
 415         protected function buildColumnDefinition($columnName, $columnData) {
 416                 // column name
 417                 $definition = "`".$columnName."`";
 418                 // column type
 419                 $definition .= " ".$columnData['type'];
 420
 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
 446          * @return      string
 447          */
 448         protected function buildIndexDefinition($indexName, $indexData) {
 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
 456                 if (!empty($indexName)) $definition .= " `".$indexName."`";
 457                 // columns
 458                 $definition .= " (`".str_replace(',', '`,`', preg_replace('/\s+/', '', $indexData['columns']))."`)";
 459
 460                 return $definition;
 461         }
 462 }