Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

Differences Between: [Versions 311 and 402] [Versions 311 and 403]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * Mysql specific SQL code generator.
  19   *
  20   * @package    core_ddl
  21   * @copyright  1999 onwards Martin Dougiamas     http://dougiamas.com
  22   *             2001-3001 Eloy Lafuente (stronk7) http://contiento.com
  23   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  24   */
  25  
  26  defined('MOODLE_INTERNAL') || die();
  27  
  28  require_once($CFG->libdir.'/ddl/sql_generator.php');
  29  
  30  /**
  31   * This class generate SQL code to be used against MySQL
  32   * It extends XMLDBgenerator so everything can be
  33   * overridden as needed to generate correct SQL.
  34   *
  35   * @package    core_ddl
  36   * @copyright  1999 onwards Martin Dougiamas     http://dougiamas.com
  37   *             2001-3001 Eloy Lafuente (stronk7) http://contiento.com
  38   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  39   */
  40  class mysql_sql_generator extends sql_generator {
  41  
  42      // Only set values that are different from the defaults present in XMLDBgenerator
  43  
  44      /** @var string Used to quote names. */
  45      public $quote_string = '`';
  46  
  47      /** @var string To define the default to set for NOT NULLs CHARs without default (null=do nothing).*/
  48      public $default_for_char = '';
  49  
  50      /** @var bool To specify if the generator must use some DEFAULT clause to drop defaults.*/
  51      public $drop_default_value_required = true;
  52  
  53      /** @var string The DEFAULT clause required to drop defaults.*/
  54      public $drop_default_value = null;
  55  
  56      /** @var string To force primary key names to one string (null=no force).*/
  57      public $primary_key_name = '';
  58  
  59      /** @var string Template to drop PKs. 'TABLENAME' and 'KEYNAME' will be replaced from this template.*/
  60      public $drop_primary_key = 'ALTER TABLE TABLENAME DROP PRIMARY KEY';
  61  
  62      /** @var string Template to drop UKs. 'TABLENAME' and 'KEYNAME' will be replaced from this template.*/
  63      public $drop_unique_key = 'ALTER TABLE TABLENAME DROP KEY KEYNAME';
  64  
  65      /** @var string Template to drop FKs. 'TABLENAME' and 'KEYNAME' will be replaced from this template.*/
  66      public $drop_foreign_key = 'ALTER TABLE TABLENAME DROP FOREIGN KEY KEYNAME';
  67  
  68      /** @var bool True if the generator needs to add extra code to generate the sequence fields.*/
  69      public $sequence_extra_code = false;
  70  
  71      /** @var string The particular name for inline sequences in this generator.*/
  72      public $sequence_name = 'auto_increment';
  73  
  74      public $add_after_clause = true; // Does the generator need to add the after clause for fields
  75  
  76      /** @var string Characters to be used as concatenation operator.*/
  77      public $concat_character = null;
  78  
  79      /** @var string The SQL template to alter columns where the 'TABLENAME' and 'COLUMNSPECS' keywords are dynamically replaced.*/
  80      public $alter_column_sql = 'ALTER TABLE TABLENAME MODIFY COLUMN COLUMNSPECS';
  81  
  82      /** @var string SQL sentence to drop one index where 'TABLENAME', 'INDEXNAME' keywords are dynamically replaced.*/
  83      public $drop_index_sql = 'ALTER TABLE TABLENAME DROP INDEX INDEXNAME';
  84  
  85      /** @var string SQL sentence to rename one index where 'TABLENAME', 'OLDINDEXNAME' and 'NEWINDEXNAME' are dynamically replaced.*/
  86      public $rename_index_sql = null;
  87  
  88      /** @var string SQL sentence to rename one key 'TABLENAME', 'OLDKEYNAME' and 'NEWKEYNAME' are dynamically replaced.*/
  89      public $rename_key_sql = null;
  90  
  91      /** Maximum size of InnoDB row in Antelope file format */
  92      const ANTELOPE_MAX_ROW_SIZE = 8126;
  93  
  94      /**
  95       * Reset a sequence to the id field of a table.
  96       *
  97       * @param xmldb_table|string $table name of table or the table object.
  98       * @return array of sql statements
  99       */
 100      public function getResetSequenceSQL($table) {
 101  
 102          if ($table instanceof xmldb_table) {
 103              $tablename = $table->getName();
 104          } else {
 105              $tablename = $table;
 106          }
 107  
 108          // From http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
 109          $value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'.$tablename.'}');
 110          $value++;
 111          return array("ALTER TABLE $this->prefix$tablename AUTO_INCREMENT = $value");
 112      }
 113  
 114      /**
 115       * Calculate proximate row size when using InnoDB
 116       * tables in Antelope row format.
 117       *
 118       * Note: the returned value is a bit higher to compensate for
 119       *       errors and changes of column data types.
 120       *
 121       * @deprecated since Moodle 2.9 MDL-49723 - please do not use this function any more.
 122       */
 123      public function guess_antolope_row_size(array $columns) {
 124          throw new coding_exception('guess_antolope_row_size() can not be used any more, please use guess_antelope_row_size() instead.');
 125      }
 126  
 127      /**
 128       * Calculate proximate row size when using InnoDB tables in Antelope row format.
 129       *
 130       * Note: the returned value is a bit higher to compensate for errors and changes of column data types.
 131       *
 132       * @param xmldb_field[]|database_column_info[] $columns
 133       * @return int approximate row size in bytes
 134       */
 135      public function guess_antelope_row_size(array $columns) {
 136  
 137          if (empty($columns)) {
 138              return 0;
 139          }
 140  
 141          $size = 0;
 142          $first = reset($columns);
 143  
 144          if (count($columns) > 1) {
 145              // Do not start with zero because we need to cover changes of field types and
 146              // this calculation is most probably not be accurate.
 147              $size += 1000;
 148          }
 149  
 150          if ($first instanceof xmldb_field) {
 151              foreach ($columns as $field) {
 152                  switch ($field->getType()) {
 153                      case XMLDB_TYPE_TEXT:
 154                          $size += 768;
 155                          break;
 156                      case XMLDB_TYPE_BINARY:
 157                          $size += 768;
 158                          break;
 159                      case XMLDB_TYPE_CHAR:
 160                          $bytes = $field->getLength() * 3;
 161                          if ($bytes > 768) {
 162                              $bytes = 768;
 163                          }
 164                          $size += $bytes;
 165                          break;
 166                      default:
 167                          // Anything else is usually maximum 8 bytes.
 168                          $size += 8;
 169                  }
 170              }
 171  
 172          } else if ($first instanceof database_column_info) {
 173              foreach ($columns as $column) {
 174                  switch ($column->meta_type) {
 175                      case 'X':
 176                          $size += 768;
 177                          break;
 178                      case 'B':
 179                          $size += 768;
 180                          break;
 181                      case 'C':
 182                          $bytes = $column->max_length * 3;
 183                          if ($bytes > 768) {
 184                              $bytes = 768;
 185                          }
 186                          $size += $bytes;
 187                          break;
 188                      default:
 189                          // Anything else is usually maximum 8 bytes.
 190                          $size += 8;
 191                  }
 192              }
 193          }
 194  
 195          return $size;
 196      }
 197  
 198      /**
 199       * Given one correct xmldb_table, returns the SQL statements
 200       * to create it (inside one array).
 201       *
 202       * @param xmldb_table $xmldb_table An xmldb_table instance.
 203       * @return array An array of SQL statements, starting with the table creation SQL followed
 204       * by any of its comments, indexes and sequence creation SQL statements.
 205       */
 206      public function getCreateTableSQL($xmldb_table) {
 207          // First find out if want some special db engine.
 208          $engine = $this->mdb->get_dbengine();
 209          // Do we know collation?
 210          $collation = $this->mdb->get_dbcollation();
 211  
 212          // Do we need to use compressed format for rows?
 213          $rowformat = "";
 214          $size = $this->guess_antelope_row_size($xmldb_table->getFields());
 215          if ($size > self::ANTELOPE_MAX_ROW_SIZE) {
 216              if ($this->mdb->is_compressed_row_format_supported()) {
 217                  $rowformat = "\n ROW_FORMAT=Compressed";
 218              }
 219          }
 220  
 221          $utf8mb4rowformat = $this->mdb->get_row_format_sql($engine, $collation);
 222          $rowformat = ($utf8mb4rowformat == '') ? $rowformat : $utf8mb4rowformat;
 223  
 224          $sqlarr = parent::getCreateTableSQL($xmldb_table);
 225  
 226          // This is a very nasty hack that tries to use just one query per created table
 227          // because MySQL is stupidly slow when modifying empty tables.
 228          // Note: it is safer to inject everything on new lines because there might be some trailing -- comments.
 229          $sqls = array();
 230          $prevcreate = null;
 231          $matches = null;
 232          foreach ($sqlarr as $sql) {
 233              if (preg_match('/^CREATE TABLE ([^ ]+)/', $sql, $matches)) {
 234                  $prevcreate = $matches[1];
 235                  $sql = preg_replace('/\s*\)\s*$/s', '/*keyblock*/)', $sql);
 236                  // Let's inject the extra MySQL tweaks here.
 237                  if ($engine) {
 238                      $sql .= "\n ENGINE = $engine";
 239                  }
 240                  if ($collation) {
 241                      if (strpos($collation, 'utf8_') === 0) {
 242                          $sql .= "\n DEFAULT CHARACTER SET utf8";
 243                      }
 244                      $sql .= "\n DEFAULT COLLATE = $collation ";
 245                  }
 246                  if ($rowformat) {
 247                      $sql .= $rowformat;
 248                  }
 249                  $sqls[] = $sql;
 250                  continue;
 251              }
 252              if ($prevcreate) {
 253                  if (preg_match('/^ALTER TABLE '.$prevcreate.' COMMENT=(.*)$/s', $sql, $matches)) {
 254                      $prev = array_pop($sqls);
 255                      $prev .= "\n COMMENT=$matches[1]";
 256                      $sqls[] = $prev;
 257                      continue;
 258                  }
 259                  if (preg_match('/^CREATE INDEX ([^ ]+) ON '.$prevcreate.' (.*)$/s', $sql, $matches)) {
 260                      $prev = array_pop($sqls);
 261                      if (strpos($prev, '/*keyblock*/')) {
 262                          $prev = str_replace('/*keyblock*/', "\n, KEY $matches[1] $matches[2]/*keyblock*/", $prev);
 263                          $sqls[] = $prev;
 264                          continue;
 265                      } else {
 266                          $sqls[] = $prev;
 267                      }
 268                  }
 269                  if (preg_match('/^CREATE UNIQUE INDEX ([^ ]+) ON '.$prevcreate.' (.*)$/s', $sql, $matches)) {
 270                      $prev = array_pop($sqls);
 271                      if (strpos($prev, '/*keyblock*/')) {
 272                          $prev = str_replace('/*keyblock*/', "\n, UNIQUE KEY $matches[1] $matches[2]/*keyblock*/", $prev);
 273                          $sqls[] = $prev;
 274                          continue;
 275                      } else {
 276                          $sqls[] = $prev;
 277                      }
 278                  }
 279              }
 280              $prevcreate = null;
 281              $sqls[] = $sql;
 282          }
 283  
 284          foreach ($sqls as $key => $sql) {
 285              $sqls[$key] = str_replace('/*keyblock*/', "\n", $sql);
 286          }
 287  
 288          return $sqls;
 289      }
 290  
 291      /**
 292       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add the field to the table.
 293       *
 294       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 295       * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
 296       * @param string $skip_type_clause The type clause on alter columns, NULL by default.
 297       * @param string $skip_default_clause The default clause on alter columns, NULL by default.
 298       * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
 299       * @return array The SQL statement for adding a field to the table.
 300       */
 301      public function getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) {
 302          $sqls = parent::getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_default_clause, $skip_notnull_clause);
 303  
 304          if ($this->table_exists($xmldb_table)) {
 305              $tablename = $xmldb_table->getName();
 306  
 307              $size = $this->guess_antelope_row_size($this->mdb->get_columns($tablename));
 308              $size += $this->guess_antelope_row_size(array($xmldb_field));
 309  
 310              if ($size > self::ANTELOPE_MAX_ROW_SIZE) {
 311                  if ($this->mdb->is_compressed_row_format_supported()) {
 312                      $format = strtolower($this->mdb->get_row_format($tablename));
 313                      if ($format === 'compact' or $format === 'redundant') {
 314                          // Change the format before conversion so that we do not run out of space.
 315                          array_unshift($sqls, "ALTER TABLE {$this->prefix}$tablename ROW_FORMAT=Compressed");
 316                      }
 317                  }
 318              }
 319          }
 320  
 321          return $sqls;
 322      }
 323  
 324      public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL)
 325      {
 326          $tablename = $xmldb_table->getName();
 327          $dbcolumnsinfo = $this->mdb->get_columns($tablename);
 328  
 329          if (($this->mdb->has_breaking_change_sqlmode()) &&
 330              ($dbcolumnsinfo[$xmldb_field->getName()]->meta_type == 'X') &&
 331              ($xmldb_field->getType() == XMLDB_TYPE_INTEGER)) {
 332              // Ignore 1292 ER_TRUNCATED_WRONG_VALUE Truncated incorrect INTEGER value: '%s'.
 333              $altercolumnsqlorig = $this->alter_column_sql;
 334              $this->alter_column_sql = str_replace('ALTER TABLE', 'ALTER IGNORE TABLE', $this->alter_column_sql);
 335              $result = parent::getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_default_clause, $skip_notnull_clause);
 336              // Restore the original ALTER SQL statement pattern.
 337              $this->alter_column_sql = $altercolumnsqlorig;
 338  
 339              return $result;
 340          }
 341  
 342          return parent::getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_default_clause, $skip_notnull_clause);
 343      }
 344  
 345      /**
 346       * Given one correct xmldb_table, returns the SQL statements
 347       * to create temporary table (inside one array).
 348       *
 349       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 350       * @return array of sql statements
 351       */
 352      public function getCreateTempTableSQL($xmldb_table) {
 353          // Do we know collation?
 354          $collation = $this->mdb->get_dbcollation();
 355          $this->temptables->add_temptable($xmldb_table->getName());
 356  
 357          $sqlarr = parent::getCreateTableSQL($xmldb_table);
 358  
 359          // Let's inject the extra MySQL tweaks.
 360          foreach ($sqlarr as $i=>$sql) {
 361              if (strpos($sql, 'CREATE TABLE ') === 0) {
 362                  // We do not want the engine hack included in create table SQL.
 363                  $sqlarr[$i] = preg_replace('/^CREATE TABLE (.*)/s', 'CREATE TEMPORARY TABLE $1', $sql);
 364                  if ($collation) {
 365                      if (strpos($collation, 'utf8_') === 0) {
 366                          $sqlarr[$i] .= " DEFAULT CHARACTER SET utf8";
 367                      }
 368                      $sqlarr[$i] .= " DEFAULT COLLATE $collation ROW_FORMAT=DYNAMIC";
 369                  }
 370              }
 371          }
 372  
 373          return $sqlarr;
 374      }
 375  
 376      /**
 377       * Given one correct xmldb_table, returns the SQL statements
 378       * to drop it (inside one array).
 379       *
 380       * @param xmldb_table $xmldb_table The table to drop.
 381       * @return array SQL statement(s) for dropping the specified table.
 382       */
 383      public function getDropTableSQL($xmldb_table) {
 384          $sqlarr = parent::getDropTableSQL($xmldb_table);
 385          if ($this->temptables->is_temptable($xmldb_table->getName())) {
 386              $sqlarr = preg_replace('/^DROP TABLE/', "DROP TEMPORARY TABLE", $sqlarr);
 387          }
 388          return $sqlarr;
 389      }
 390  
 391      /**
 392       * Given one XMLDB Type, length and decimals, returns the DB proper SQL type.
 393       *
 394       * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants.
 395       * @param int $xmldb_length The length of that data type.
 396       * @param int $xmldb_decimals The decimal places of precision of the data type.
 397       * @return string The DB defined data type.
 398       */
 399      public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
 400  
 401          switch ($xmldb_type) {
 402              case XMLDB_TYPE_INTEGER:    // From http://mysql.com/doc/refman/5.0/en/numeric-types.html!
 403                  if (empty($xmldb_length)) {
 404                      $xmldb_length = 10;
 405                  }
 406                  if ($xmldb_length > 9) {
 407                      $dbtype = 'BIGINT';
 408                  } else if ($xmldb_length > 6) {
 409                      $dbtype = 'INT';
 410                  } else if ($xmldb_length > 4) {
 411                      $dbtype = 'MEDIUMINT';
 412                  } else if ($xmldb_length > 2) {
 413                      $dbtype = 'SMALLINT';
 414                  } else {
 415                      $dbtype = 'TINYINT';
 416                  }
 417                  $dbtype .= '(' . $xmldb_length . ')';
 418                  break;
 419              case XMLDB_TYPE_NUMBER:
 420                  $dbtype = $this->number_type;
 421                  if (!empty($xmldb_length)) {
 422                      $dbtype .= '(' . $xmldb_length;
 423                      if (!empty($xmldb_decimals)) {
 424                          $dbtype .= ',' . $xmldb_decimals;
 425                      }
 426                      $dbtype .= ')';
 427                  }
 428                  break;
 429              case XMLDB_TYPE_FLOAT:
 430                  $dbtype = 'DOUBLE';
 431                  if (!empty($xmldb_decimals)) {
 432                      if ($xmldb_decimals < 6) {
 433                          $dbtype = 'FLOAT';
 434                      }
 435                  }
 436                  if (!empty($xmldb_length)) {
 437                      $dbtype .= '(' . $xmldb_length;
 438                      if (!empty($xmldb_decimals)) {
 439                          $dbtype .= ',' . $xmldb_decimals;
 440                      } else {
 441                          $dbtype .= ', 0'; // In MySQL, if length is specified, decimals are mandatory for FLOATs
 442                      }
 443                      $dbtype .= ')';
 444                  }
 445                  break;
 446              case XMLDB_TYPE_CHAR:
 447                  $dbtype = 'VARCHAR';
 448                  if (empty($xmldb_length)) {
 449                      $xmldb_length='255';
 450                  }
 451                  $dbtype .= '(' . $xmldb_length . ')';
 452                  if ($collation = $this->mdb->get_dbcollation()) {
 453                      if (strpos($collation, 'utf8_') === 0) {
 454                          $dbtype .= " CHARACTER SET utf8";
 455                      }
 456                      $dbtype .= " COLLATE $collation";
 457                  }
 458                  break;
 459              case XMLDB_TYPE_TEXT:
 460                  $dbtype = 'LONGTEXT';
 461                  if ($collation = $this->mdb->get_dbcollation()) {
 462                      if (strpos($collation, 'utf8_') === 0) {
 463                          $dbtype .= " CHARACTER SET utf8";
 464                      }
 465                      $dbtype .= " COLLATE $collation";
 466                  }
 467                  break;
 468              case XMLDB_TYPE_BINARY:
 469                  $dbtype = 'LONGBLOB';
 470                  break;
 471              case XMLDB_TYPE_DATETIME:
 472                  $dbtype = 'DATETIME';
 473          }
 474          return $dbtype;
 475      }
 476  
 477      /**
 478       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default
 479       * (usually invoked from getModifyDefaultSQL()
 480       *
 481       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 482       * @param xmldb_field $xmldb_field The xmldb_field object instance.
 483       * @return array Array of SQL statements to create a field's default.
 484       */
 485      public function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
 486          // Just a wrapper over the getAlterFieldSQL() function for MySQL that
 487          // is capable of handling defaults
 488          return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 489      }
 490  
 491      /**
 492       * Given one correct xmldb_field and the new name, returns the SQL statements
 493       * to rename it (inside one array).
 494       *
 495       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 496       * @param xmldb_field $xmldb_field The instance of xmldb_field to get the renamed field from.
 497       * @param string $newname The new name to rename the field to.
 498       * @return array The SQL statements for renaming the field.
 499       */
 500      public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) {
 501          // NOTE: MySQL is pretty different from the standard to justify this overloading.
 502  
 503          // Need a clone of xmldb_field to perform the change leaving original unmodified
 504          $xmldb_field_clone = clone($xmldb_field);
 505  
 506          // Change the name of the field to perform the change
 507          $xmldb_field_clone->setName($newname);
 508  
 509          $fieldsql = $this->getFieldSQL($xmldb_table, $xmldb_field_clone);
 510  
 511          $sql = 'ALTER TABLE ' . $this->getTableName($xmldb_table) . ' CHANGE ' .
 512                 $this->getEncQuoted($xmldb_field->getName()) . ' ' . $fieldsql;
 513  
 514          return array($sql);
 515      }
 516  
 517      /**
 518       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default
 519       * (usually invoked from getModifyDefaultSQL()
 520       *
 521       * Note that this method may be dropped in future.
 522       *
 523       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 524       * @param xmldb_field $xmldb_field The xmldb_field object instance.
 525       * @return array Array of SQL statements to create a field's default.
 526       *
 527       * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL()
 528       */
 529      public function getDropDefaultSQL($xmldb_table, $xmldb_field) {
 530          // Just a wrapper over the getAlterFieldSQL() function for MySQL that
 531          // is capable of handling defaults
 532          return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 533      }
 534  
 535      /**
 536       * Returns the code (array of statements) needed to add one comment to the table.
 537       *
 538       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 539       * @return array Array of SQL statements to add one comment to the table.
 540       */
 541      function getCommentSQL ($xmldb_table) {
 542          $comment = '';
 543  
 544          if ($xmldb_table->getComment()) {
 545              $comment .= 'ALTER TABLE ' . $this->getTableName($xmldb_table);
 546              $comment .= " COMMENT='" . $this->addslashes(substr($xmldb_table->getComment(), 0, 60)) . "'";
 547          }
 548          return array($comment);
 549      }
 550  
 551      /**
 552       * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg).
 553       *
 554       * (MySQL requires the whole xmldb_table object to be specified, so we add it always)
 555       *
 556       * This is invoked from getNameForObject().
 557       * Only some DB have this implemented.
 558       *
 559       * @param string $object_name The object's name to check for.
 560       * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg).
 561       * @param string $table_name The table's name to check in
 562       * @return bool If such name is currently in use (true) or no (false)
 563       */
 564      public function isNameInUse($object_name, $type, $table_name) {
 565  
 566          switch($type) {
 567              case 'ix':
 568              case 'uix':
 569                  // First of all, check table exists
 570                  $metatables = $this->mdb->get_tables();
 571                  if (isset($metatables[$table_name])) {
 572                      // Fetch all the indexes in the table
 573                      if ($indexes = $this->mdb->get_indexes($table_name)) {
 574                          // Look for existing index in array
 575                          if (isset($indexes[$object_name])) {
 576                              return true;
 577                          }
 578                      }
 579                  }
 580                  break;
 581          }
 582          return false; //No name in use found
 583      }
 584  
 585  
 586      /**
 587       * Returns an array of reserved words (lowercase) for this DB
 588       * @return array An array of database specific reserved words
 589       */
 590      public static function getReservedWords() {
 591          // This file contains the reserved words for MySQL databases.
 592          $reserved_words = array (
 593              // From http://dev.mysql.com/doc/refman/6.0/en/reserved-words.html.
 594              'accessible', 'add', 'all', 'alter', 'analyze', 'and', 'as', 'asc',
 595              'asensitive', 'before', 'between', 'bigint', 'binary',
 596              'blob', 'both', 'by', 'call', 'cascade', 'case', 'change',
 597              'char', 'character', 'check', 'collate', 'column',
 598              'condition', 'connection', 'constraint', 'continue',
 599              'convert', 'create', 'cross', 'current_date', 'current_time',
 600              'current_timestamp', 'current_user', 'cursor', 'database',
 601              'databases', 'day_hour', 'day_microsecond',
 602              'day_minute', 'day_second', 'dec', 'decimal', 'declare',
 603              'default', 'delayed', 'delete', 'desc', 'describe',
 604              'deterministic', 'distinct', 'distinctrow', 'div', 'double',
 605              'drop', 'dual', 'each', 'else', 'elseif', 'enclosed', 'escaped',
 606              'exists', 'exit', 'explain', 'false', 'fetch', 'float', 'float4',
 607              'float8', 'for', 'force', 'foreign', 'from', 'fulltext', 'grant',
 608              'group', 'having', 'high_priority', 'hour_microsecond',
 609              'hour_minute', 'hour_second', 'if', 'ignore', 'in', 'index',
 610              'infile', 'inner', 'inout', 'insensitive', 'insert', 'int', 'int1',
 611              'int2', 'int3', 'int4', 'int8', 'integer', 'interval', 'into', 'is',
 612              'iterate', 'join', 'key', 'keys', 'kill', 'leading', 'leave', 'left',
 613              'like', 'limit', 'linear', 'lines', 'load', 'localtime', 'localtimestamp',
 614              'lock', 'long', 'longblob', 'longtext', 'loop', 'low_priority', 'master_heartbeat_period',
 615              'master_ssl_verify_server_cert', 'match', 'mediumblob', 'mediumint', 'mediumtext',
 616              'middleint', 'minute_microsecond', 'minute_second',
 617              'mod', 'modifies', 'natural', 'not', 'no_write_to_binlog',
 618              'null', 'numeric', 'on', 'optimize', 'option', 'optionally',
 619              'or', 'order', 'out', 'outer', 'outfile', 'overwrite', 'precision', 'primary',
 620              'procedure', 'purge', 'raid0', 'range', 'read', 'read_only', 'read_write', 'reads', 'real',
 621              'references', 'regexp', 'release', 'rename', 'repeat', 'replace',
 622              'require', 'restrict', 'return', 'revoke', 'right', 'rlike', 'schema',
 623              'schemas', 'second_microsecond', 'select', 'sensitive',
 624              'separator', 'set', 'show', 'smallint', 'soname', 'spatial',
 625              'specific', 'sql', 'sqlexception', 'sqlstate', 'sqlwarning',
 626              'sql_big_result', 'sql_calc_found_rows', 'sql_small_result',
 627              'ssl', 'starting', 'straight_join', 'table', 'terminated', 'then',
 628              'tinyblob', 'tinyint', 'tinytext', 'to', 'trailing', 'trigger', 'true',
 629              'undo', 'union', 'unique', 'unlock', 'unsigned', 'update',
 630              'upgrade', 'usage', 'use', 'using', 'utc_date', 'utc_time',
 631              'utc_timestamp', 'values', 'varbinary', 'varchar', 'varcharacter',
 632              'varying', 'when', 'where', 'while', 'with', 'write', 'x509',
 633              'xor', 'year_month', 'zerofill',
 634              // Added in MySQL 8.0, compared to MySQL 5.7:
 635              // https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-new-in-current-series.
 636              '_filename', 'admin', 'cume_dist', 'dense_rank', 'empty', 'except', 'first_value', 'grouping', 'groups',
 637              'json_table', 'lag', 'last_value', 'lead', 'nth_value', 'ntile',
 638              'of', 'over', 'percent_rank', 'persist', 'persist_only', 'rank', 'recursive', 'row_number',
 639              'system', 'window'
 640          );
 641          return $reserved_words;
 642      }
 643  }