[Summary view]

Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402] [Versions 400 and 402] [Versions 401 and 402]

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