[Summary view] [Print] [Text view]

   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       * @param xmldb_field[]|database_column_info[] $columns
 122       * @return int approximate row size in bytes
 123       */
 124      public function guess_antolope_row_size(array $columns) {
 125          if (empty($columns)) {
 126              return 0;
 127          }
 128  
 129          $size = 0;
 130          $first = reset($columns);
 131  
 132          if (count($columns) > 1) {
 133              // Do not start with zero because we need to cover changes of field types and
 134              // this calculation is most probably not be accurate.
 135              $size += 1000;
 136          }
 137  
 138          if ($first instanceof xmldb_field) {
 139              foreach ($columns as $field) {
 140                  switch ($field->getType()) {
 141                      case XMLDB_TYPE_TEXT:
 142                          $size += 768;
 143                          break;
 144                      case XMLDB_TYPE_BINARY:
 145                          $size += 768;
 146                          break;
 147                      case XMLDB_TYPE_CHAR:
 148                          $bytes = $field->getLength() * 3;
 149                          if ($bytes > 768) {
 150                              $bytes = 768;
 151                          }
 152                          $size += $bytes;
 153                          break;
 154                      default:
 155                          // Anything else is usually maximum 8 bytes.
 156                          $size += 8;
 157                  }
 158              }
 159  
 160          } else if ($first instanceof database_column_info) {
 161              foreach ($columns as $column) {
 162                  switch ($column->meta_type) {
 163                      case 'X':
 164                          $size += 768;
 165                          break;
 166                      case 'B':
 167                          $size += 768;
 168                          break;
 169                      case 'C':
 170                          $bytes = $column->max_length * 3;
 171                          if ($bytes > 768) {
 172                              $bytes = 768;
 173                          }
 174                          $size += $bytes;
 175                          break;
 176                      default:
 177                          // Anything else is usually maximum 8 bytes.
 178                          $size += 8;
 179                  }
 180              }
 181          }
 182  
 183          return $size;
 184      }
 185  
 186      /**
 187       * Given one correct xmldb_table, returns the SQL statements
 188       * to create it (inside one array).
 189       *
 190       * @param xmldb_table $xmldb_table An xmldb_table instance.
 191       * @return array An array of SQL statements, starting with the table creation SQL followed
 192       * by any of its comments, indexes and sequence creation SQL statements.
 193       */
 194      public function getCreateTableSQL($xmldb_table) {
 195          // First find out if want some special db engine.
 196          $engine = $this->mdb->get_dbengine();
 197          // Do we know collation?
 198          $collation = $this->mdb->get_dbcollation();
 199  
 200          // Do we need to use compressed format for rows?
 201          $rowformat = "";
 202          $size = $this->guess_antolope_row_size($xmldb_table->getFields());
 203          if ($size > self::ANTELOPE_MAX_ROW_SIZE) {
 204              if ($this->mdb->is_compressed_row_format_supported()) {
 205                  $rowformat = "\n ROW_FORMAT=Compressed";
 206              }
 207          }
 208  
 209          $sqlarr = parent::getCreateTableSQL($xmldb_table);
 210  
 211          // This is a very nasty hack that tries to use just one query per created table
 212          // because MySQL is stupidly slow when modifying empty tables.
 213          // Note: it is safer to inject everything on new lines because there might be some trailing -- comments.
 214          $sqls = array();
 215          $prevcreate = null;
 216          $matches = null;
 217          foreach ($sqlarr as $sql) {
 218              if (preg_match('/^CREATE TABLE ([^ ]+)/', $sql, $matches)) {
 219                  $prevcreate = $matches[1];
 220                  $sql = preg_replace('/\s*\)\s*$/s', '/*keyblock*/)', $sql);
 221                  // Let's inject the extra MySQL tweaks here.
 222                  if ($engine) {
 223                      $sql .= "\n ENGINE = $engine";
 224                  }
 225                  if ($collation) {
 226                      if (strpos($collation, 'utf8_') === 0) {
 227                          $sql .= "\n DEFAULT CHARACTER SET utf8";
 228                      }
 229                      $sql .= "\n DEFAULT COLLATE = $collation";
 230                  }
 231                  if ($rowformat) {
 232                      $sql .= $rowformat;
 233                  }
 234                  $sqls[] = $sql;
 235                  continue;
 236              }
 237              if ($prevcreate) {
 238                  if (preg_match('/^ALTER TABLE '.$prevcreate.' COMMENT=(.*)$/s', $sql, $matches)) {
 239                      $prev = array_pop($sqls);
 240                      $prev .= "\n COMMENT=$matches[1]";
 241                      $sqls[] = $prev;
 242                      continue;
 243                  }
 244                  if (preg_match('/^CREATE INDEX ([^ ]+) ON '.$prevcreate.' (.*)$/s', $sql, $matches)) {
 245                      $prev = array_pop($sqls);
 246                      if (strpos($prev, '/*keyblock*/')) {
 247                          $prev = str_replace('/*keyblock*/', "\n, KEY $matches[1] $matches[2]/*keyblock*/", $prev);
 248                          $sqls[] = $prev;
 249                          continue;
 250                      } else {
 251                          $sqls[] = $prev;
 252                      }
 253                  }
 254                  if (preg_match('/^CREATE UNIQUE INDEX ([^ ]+) ON '.$prevcreate.' (.*)$/s', $sql, $matches)) {
 255                      $prev = array_pop($sqls);
 256                      if (strpos($prev, '/*keyblock*/')) {
 257                          $prev = str_replace('/*keyblock*/', "\n, UNIQUE KEY $matches[1] $matches[2]/*keyblock*/", $prev);
 258                          $sqls[] = $prev;
 259                          continue;
 260                      } else {
 261                          $sqls[] = $prev;
 262                      }
 263                  }
 264              }
 265              $prevcreate = null;
 266              $sqls[] = $sql;
 267          }
 268  
 269          foreach ($sqls as $key => $sql) {
 270              $sqls[$key] = str_replace('/*keyblock*/', "\n", $sql);
 271          }
 272  
 273          return $sqls;
 274      }
 275  
 276      /**
 277       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add the field to the table.
 278       *
 279       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 280       * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
 281       * @param string $skip_type_clause The type clause on alter columns, NULL by default.
 282       * @param string $skip_default_clause The default clause on alter columns, NULL by default.
 283       * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
 284       * @return array The SQL statement for adding a field to the table.
 285       */
 286      public function getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) {
 287          $sqls = parent::getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_default_clause, $skip_notnull_clause);
 288  
 289          if ($this->table_exists($xmldb_table)) {
 290              $tablename = $xmldb_table->getName();
 291  
 292              $size = $this->guess_antolope_row_size($this->mdb->get_columns($tablename));
 293              $size += $this->guess_antolope_row_size(array($xmldb_field));
 294  
 295              if ($size > self::ANTELOPE_MAX_ROW_SIZE) {
 296                  if ($this->mdb->is_compressed_row_format_supported()) {
 297                      $format = strtolower($this->mdb->get_row_format($tablename));
 298                      if ($format === 'compact' or $format === 'redundant') {
 299                          // Change the format before conversion so that we do not run out of space.
 300                          array_unshift($sqls, "ALTER TABLE {$this->prefix}$tablename ROW_FORMAT=Compressed");
 301                      }
 302                  }
 303              }
 304          }
 305  
 306          return $sqls;
 307      }
 308  
 309      /**
 310       * Given one correct xmldb_table, returns the SQL statements
 311       * to create temporary table (inside one array).
 312       *
 313       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 314       * @return array of sql statements
 315       */
 316      public function getCreateTempTableSQL($xmldb_table) {
 317          // Do we know collation?
 318          $collation = $this->mdb->get_dbcollation();
 319          $this->temptables->add_temptable($xmldb_table->getName());
 320  
 321          $sqlarr = parent::getCreateTableSQL($xmldb_table);
 322  
 323          // Let's inject the extra MySQL tweaks.
 324          foreach ($sqlarr as $i=>$sql) {
 325              if (strpos($sql, 'CREATE TABLE ') === 0) {
 326                  // We do not want the engine hack included in create table SQL.
 327                  $sqlarr[$i] = preg_replace('/^CREATE TABLE (.*)/s', 'CREATE TEMPORARY TABLE $1', $sql);
 328                  if ($collation) {
 329                      if (strpos($collation, 'utf8_') === 0) {
 330                          $sqlarr[$i] .= " DEFAULT CHARACTER SET utf8";
 331                      }
 332                      $sqlarr[$i] .= " DEFAULT COLLATE $collation";
 333                  }
 334              }
 335          }
 336  
 337          return $sqlarr;
 338      }
 339  
 340      /**
 341       * Given one correct xmldb_table, returns the SQL statements
 342       * to drop it (inside one array).
 343       *
 344       * @param xmldb_table $xmldb_table The table to drop.
 345       * @return array SQL statement(s) for dropping the specified table.
 346       */
 347      public function getDropTableSQL($xmldb_table) {
 348          $sqlarr = parent::getDropTableSQL($xmldb_table);
 349          if ($this->temptables->is_temptable($xmldb_table->getName())) {
 350              $sqlarr = preg_replace('/^DROP TABLE/', "DROP TEMPORARY TABLE", $sqlarr);
 351              $this->temptables->delete_temptable($xmldb_table->getName());
 352          }
 353          return $sqlarr;
 354      }
 355  
 356      /**
 357       * Given one XMLDB Type, length and decimals, returns the DB proper SQL type.
 358       *
 359       * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants.
 360       * @param int $xmldb_length The length of that data type.
 361       * @param int $xmldb_decimals The decimal places of precision of the data type.
 362       * @return string The DB defined data type.
 363       */
 364      public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
 365  
 366          switch ($xmldb_type) {
 367              case XMLDB_TYPE_INTEGER:    // From http://mysql.com/doc/refman/5.0/en/numeric-types.html!
 368                  if (empty($xmldb_length)) {
 369                      $xmldb_length = 10;
 370                  }
 371                  if ($xmldb_length > 9) {
 372                      $dbtype = 'BIGINT';
 373                  } else if ($xmldb_length > 6) {
 374                      $dbtype = 'INT';
 375                  } else if ($xmldb_length > 4) {
 376                      $dbtype = 'MEDIUMINT';
 377                  } else if ($xmldb_length > 2) {
 378                      $dbtype = 'SMALLINT';
 379                  } else {
 380                      $dbtype = 'TINYINT';
 381                  }
 382                  $dbtype .= '(' . $xmldb_length . ')';
 383                  break;
 384              case XMLDB_TYPE_NUMBER:
 385                  $dbtype = $this->number_type;
 386                  if (!empty($xmldb_length)) {
 387                      $dbtype .= '(' . $xmldb_length;
 388                      if (!empty($xmldb_decimals)) {
 389                          $dbtype .= ',' . $xmldb_decimals;
 390                      }
 391                      $dbtype .= ')';
 392                  }
 393                  break;
 394              case XMLDB_TYPE_FLOAT:
 395                  $dbtype = 'DOUBLE';
 396                  if (!empty($xmldb_decimals)) {
 397                      if ($xmldb_decimals < 6) {
 398                          $dbtype = 'FLOAT';
 399                      }
 400                  }
 401                  if (!empty($xmldb_length)) {
 402                      $dbtype .= '(' . $xmldb_length;
 403                      if (!empty($xmldb_decimals)) {
 404                          $dbtype .= ',' . $xmldb_decimals;
 405                      } else {
 406                          $dbtype .= ', 0'; // In MySQL, if length is specified, decimals are mandatory for FLOATs
 407                      }
 408                      $dbtype .= ')';
 409                  }
 410                  break;
 411              case XMLDB_TYPE_CHAR:
 412                  $dbtype = 'VARCHAR';
 413                  if (empty($xmldb_length)) {
 414                      $xmldb_length='255';
 415                  }
 416                  $dbtype .= '(' . $xmldb_length . ')';
 417                  if ($collation = $this->mdb->get_dbcollation()) {
 418                      if (strpos($collation, 'utf8_') === 0) {
 419                          $dbtype .= " CHARACTER SET utf8";
 420                      }
 421                      $dbtype .= " COLLATE $collation";
 422                  }
 423                  break;
 424              case XMLDB_TYPE_TEXT:
 425                  $dbtype = 'LONGTEXT';
 426                  if ($collation = $this->mdb->get_dbcollation()) {
 427                      if (strpos($collation, 'utf8_') === 0) {
 428                          $dbtype .= " CHARACTER SET utf8";
 429                      }
 430                      $dbtype .= " COLLATE $collation";
 431                  }
 432                  break;
 433              case XMLDB_TYPE_BINARY:
 434                  $dbtype = 'LONGBLOB';
 435                  break;
 436              case XMLDB_TYPE_DATETIME:
 437                  $dbtype = 'DATETIME';
 438          }
 439          return $dbtype;
 440      }
 441  
 442      /**
 443       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default
 444       * (usually invoked from getModifyDefaultSQL()
 445       *
 446       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 447       * @param xmldb_field $xmldb_field The xmldb_field object instance.
 448       * @return array Array of SQL statements to create a field's default.
 449       */
 450      public function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
 451          // Just a wrapper over the getAlterFieldSQL() function for MySQL that
 452          // is capable of handling defaults
 453          return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 454      }
 455  
 456      /**
 457       * Given one correct xmldb_field and the new name, returns the SQL statements
 458       * to rename it (inside one array).
 459       *
 460       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 461       * @param xmldb_field $xmldb_field The instance of xmldb_field to get the renamed field from.
 462       * @param string $newname The new name to rename the field to.
 463       * @return array The SQL statements for renaming the field.
 464       */
 465      public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) {
 466          // NOTE: MySQL is pretty different from the standard to justify this overloading.
 467  
 468          // Need a clone of xmldb_field to perform the change leaving original unmodified
 469          $xmldb_field_clone = clone($xmldb_field);
 470  
 471          // Change the name of the field to perform the change
 472          $xmldb_field_clone->setName($newname);
 473  
 474          $fieldsql = $this->getFieldSQL($xmldb_table, $xmldb_field_clone);
 475  
 476          $sql = 'ALTER TABLE ' . $this->getTableName($xmldb_table) . ' CHANGE ' .
 477                 $xmldb_field->getName() . ' ' . $fieldsql;
 478  
 479          return array($sql);
 480      }
 481  
 482      /**
 483       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default
 484       * (usually invoked from getModifyDefaultSQL()
 485       *
 486       * Note that this method may be dropped in future.
 487       *
 488       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 489       * @param xmldb_field $xmldb_field The xmldb_field object instance.
 490       * @return array Array of SQL statements to create a field's default.
 491       *
 492       * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL()
 493       */
 494      public function getDropDefaultSQL($xmldb_table, $xmldb_field) {
 495          // Just a wrapper over the getAlterFieldSQL() function for MySQL that
 496          // is capable of handling defaults
 497          return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 498      }
 499  
 500      /**
 501       * Returns the code (array of statements) needed to add one comment to the table.
 502       *
 503       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 504       * @return array Array of SQL statements to add one comment to the table.
 505       */
 506      function getCommentSQL ($xmldb_table) {
 507          $comment = '';
 508  
 509          if ($xmldb_table->getComment()) {
 510              $comment .= 'ALTER TABLE ' . $this->getTableName($xmldb_table);
 511              $comment .= " COMMENT='" . $this->addslashes(substr($xmldb_table->getComment(), 0, 60)) . "'";
 512          }
 513          return array($comment);
 514      }
 515  
 516      /**
 517       * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg).
 518       *
 519       * (MySQL requires the whole xmldb_table object to be specified, so we add it always)
 520       *
 521       * This is invoked from getNameForObject().
 522       * Only some DB have this implemented.
 523       *
 524       * @param string $object_name The object's name to check for.
 525       * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg).
 526       * @param string $table_name The table's name to check in
 527       * @return bool If such name is currently in use (true) or no (false)
 528       */
 529      public function isNameInUse($object_name, $type, $table_name) {
 530  
 531          switch($type) {
 532              case 'ix':
 533              case 'uix':
 534                  // First of all, check table exists
 535                  $metatables = $this->mdb->get_tables();
 536                  if (isset($metatables[$table_name])) {
 537                      // Fetch all the indexes in the table
 538                      if ($indexes = $this->mdb->get_indexes($table_name)) {
 539                          // Look for existing index in array
 540                          if (isset($indexes[$object_name])) {
 541                              return true;
 542                          }
 543                      }
 544                  }
 545                  break;
 546          }
 547          return false; //No name in use found
 548      }
 549  
 550  
 551      /**
 552       * Returns an array of reserved words (lowercase) for this DB
 553       * @return array An array of database specific reserved words
 554       */
 555      public static function getReservedWords() {
 556          // This file contains the reserved words for MySQL databases
 557          // from http://dev.mysql.com/doc/refman/6.0/en/reserved-words.html
 558          $reserved_words = array (
 559              'accessible', 'add', 'all', 'alter', 'analyze', 'and', 'as', 'asc',
 560              'asensitive', 'before', 'between', 'bigint', 'binary',
 561              'blob', 'both', 'by', 'call', 'cascade', 'case', 'change',
 562              'char', 'character', 'check', 'collate', 'column',
 563              'condition', 'connection', 'constraint', 'continue',
 564              'convert', 'create', 'cross', 'current_date', 'current_time',
 565              'current_timestamp', 'current_user', 'cursor', 'database',
 566              'databases', 'day_hour', 'day_microsecond',
 567              'day_minute', 'day_second', 'dec', 'decimal', 'declare',
 568              'default', 'delayed', 'delete', 'desc', 'describe',
 569              'deterministic', 'distinct', 'distinctrow', 'div', 'double',
 570              'drop', 'dual', 'each', 'else', 'elseif', 'enclosed', 'escaped',
 571              'exists', 'exit', 'explain', 'false', 'fetch', 'float', 'float4',
 572              'float8', 'for', 'force', 'foreign', 'from', 'fulltext', 'grant',
 573              'group', 'having', 'high_priority', 'hour_microsecond',
 574              'hour_minute', 'hour_second', 'if', 'ignore', 'in', 'index',
 575              'infile', 'inner', 'inout', 'insensitive', 'insert', 'int', 'int1',
 576              'int2', 'int3', 'int4', 'int8', 'integer', 'interval', 'into', 'is',
 577              'iterate', 'join', 'key', 'keys', 'kill', 'leading', 'leave', 'left',
 578              'like', 'limit', 'linear', 'lines', 'load', 'localtime', 'localtimestamp',
 579              'lock', 'long', 'longblob', 'longtext', 'loop', 'low_priority', 'master_heartbeat_period',
 580              'master_ssl_verify_server_cert', 'match', 'mediumblob', 'mediumint', 'mediumtext',
 581              'middleint', 'minute_microsecond', 'minute_second',
 582              'mod', 'modifies', 'natural', 'not', 'no_write_to_binlog',
 583              'null', 'numeric', 'on', 'optimize', 'option', 'optionally',
 584              'or', 'order', 'out', 'outer', 'outfile', 'overwrite', 'precision', 'primary',
 585              'procedure', 'purge', 'raid0', 'range', 'read', 'read_only', 'read_write', 'reads', 'real',
 586              'references', 'regexp', 'release', 'rename', 'repeat', 'replace',
 587              'require', 'restrict', 'return', 'revoke', 'right', 'rlike', 'schema',
 588              'schemas', 'second_microsecond', 'select', 'sensitive',
 589              'separator', 'set', 'show', 'smallint', 'soname', 'spatial',
 590              'specific', 'sql', 'sqlexception', 'sqlstate', 'sqlwarning',
 591              'sql_big_result', 'sql_calc_found_rows', 'sql_small_result',
 592              'ssl', 'starting', 'straight_join', 'table', 'terminated', 'then',
 593              'tinyblob', 'tinyint', 'tinytext', 'to', 'trailing', 'trigger', 'true',
 594              'undo', 'union', 'unique', 'unlock', 'unsigned', 'update',
 595              'upgrade', 'usage', 'use', 'using', 'utc_date', 'utc_time',
 596              'utc_timestamp', 'values', 'varbinary', 'varchar', 'varcharacter',
 597              'varying', 'when', 'where', 'while', 'with', 'write', 'x509',
 598              'xor', 'year_month', 'zerofill'
 599          );
 600          return $reserved_words;
 601      }
 602  }