lib/ddl/mysql_sql_generator.php

   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     /**
  92      * Reset a sequence to the id field of a table.
  93      *
  94      * @param xmldb_table|string $table name of table or the table object.
  95      * @return array of sql statements
  96      */
  97     public function getResetSequenceSQL($table) {
  98
  99         if ($table instanceof xmldb_table) {
 100             $tablename = $table->getName();
 101         } else {
 102             $tablename = $table;
 103         }
 104
 105         // From http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
 106         $value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'.$tablename.'}');
 107         $value++;
 108         return array("ALTER TABLE $this->prefix$tablename AUTO_INCREMENT = $value");
 109     }
 110
 111     /**
 112      * Given one correct xmldb_table, returns the SQL statements
 113      * to create it (inside one array).
 114      *
 115      * @param xmldb_table $xmldb_table An xmldb_table instance.
 116      * @return array An array of SQL statements, starting with the table creation SQL followed
 117      * by any of its comments, indexes and sequence creation SQL statements.
 118      */
 119     public function getCreateTableSQL($xmldb_table) {
 120         // First find out if want some special db engine.
 121         $engine = $this->mdb->get_dbengine();
 122         // Do we know collation?
 123         $collation = $this->mdb->get_dbcollation();
 124
 125         $sqlarr = parent::getCreateTableSQL($xmldb_table);
 126
 127         // Let's inject the extra MySQL tweaks.
 128         foreach ($sqlarr as $i=>$sql) {
 129             if (strpos($sql, 'CREATE TABLE ') === 0) {
 130                 if ($engine) {
 131                     $sqlarr[$i] .= " ENGINE = $engine";
 132                 }
 133                 if ($collation) {
 134                     if (strpos($collation, 'utf8_') === 0) {
 135                         $sqlarr[$i] .= " DEFAULT CHARACTER SET utf8";
 136                     }
 137                     $sqlarr[$i] .= " DEFAULT COLLATE = $collation";
 138                 }
 139             }
 140         }
 141
 142         return $sqlarr;
 143     }
 144
 145     /**
 146      * Given one correct xmldb_table, returns the SQL statements
 147      * to create temporary table (inside one array).
 148      *
 149      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 150      * @return array of sql statements
 151      */
 152     public function getCreateTempTableSQL($xmldb_table) {
 153         // Do we know collation?
 154         $collation = $this->mdb->get_dbcollation();
 155         $this->temptables->add_temptable($xmldb_table->getName());
 156
 157         $sqlarr = parent::getCreateTableSQL($xmldb_table);
 158
 159         // Let's inject the extra MySQL tweaks.
 160         foreach ($sqlarr as $i=>$sql) {
 161             if (strpos($sql, 'CREATE TABLE ') === 0) {
 162                 // We do not want the engine hack included in create table SQL.
 163                 $sqlarr[$i] = preg_replace('/^CREATE TABLE (.*)/s', 'CREATE TEMPORARY TABLE $1', $sql);
 164                 if ($collation) {
 165                     if (strpos($collation, 'utf8_') === 0) {
 166                         $sqlarr[$i] .= " DEFAULT CHARACTER SET utf8";
 167                     }
 168                     $sqlarr[$i] .= " DEFAULT COLLATE $collation";
 169                 }
 170             }
 171         }
 172
 173         return $sqlarr;
 174     }
 175
 176     /**
 177      * Given one correct xmldb_table, returns the SQL statements
 178      * to drop it (inside one array).
 179      *
 180      * @param xmldb_table $xmldb_table The table to drop.
 181      * @return array SQL statement(s) for dropping the specified table.
 182      */
 183     public function getDropTableSQL($xmldb_table) {
 184         $sqlarr = parent::getDropTableSQL($xmldb_table);
 185         if ($this->temptables->is_temptable($xmldb_table->getName())) {
 186             $sqlarr = preg_replace('/^DROP TABLE/', "DROP TEMPORARY TABLE", $sqlarr);
 187             $this->temptables->delete_temptable($xmldb_table->getName());
 188         }
 189         return $sqlarr;
 190     }
 191
 192     /**
 193      * Given one XMLDB Type, length and decimals, returns the DB proper SQL type.
 194      *
 195      * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants.
 196      * @param int $xmldb_length The length of that data type.
 197      * @param int $xmldb_decimals The decimal places of precision of the data type.
 198      * @return string The DB defined data type.
 199      */
 200     public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
 201
 202         switch ($xmldb_type) {
 203             case XMLDB_TYPE_INTEGER:    // From http://mysql.com/doc/refman/5.0/en/numeric-types.html!
 204                 if (empty($xmldb_length)) {
 205                     $xmldb_length = 10;
 206                 }
 207                 if ($xmldb_length > 9) {
 208                     $dbtype = 'BIGINT';
 209                 } else if ($xmldb_length > 6) {
 210                     $dbtype = 'INT';
 211                 } else if ($xmldb_length > 4) {
 212                     $dbtype = 'MEDIUMINT';
 213                 } else if ($xmldb_length > 2) {
 214                     $dbtype = 'SMALLINT';
 215                 } else {
 216                     $dbtype = 'TINYINT';
 217                 }
 218                 $dbtype .= '(' . $xmldb_length . ')';
 219                 break;
 220             case XMLDB_TYPE_NUMBER:
 221                 $dbtype = $this->number_type;
 222                 if (!empty($xmldb_length)) {
 223                     $dbtype .= '(' . $xmldb_length;
 224                     if (!empty($xmldb_decimals)) {
 225                         $dbtype .= ',' . $xmldb_decimals;
 226                     }
 227                     $dbtype .= ')';
 228                 }
 229                 break;
 230             case XMLDB_TYPE_FLOAT:
 231                 $dbtype = 'DOUBLE';
 232                 if (!empty($xmldb_decimals)) {
 233                     if ($xmldb_decimals < 6) {
 234                         $dbtype = 'FLOAT';
 235                     }
 236                 }
 237                 if (!empty($xmldb_length)) {
 238                     $dbtype .= '(' . $xmldb_length;
 239                     if (!empty($xmldb_decimals)) {
 240                         $dbtype .= ',' . $xmldb_decimals;
 241                     } else {
 242                         $dbtype .= ', 0'; // In MySQL, if length is specified, decimals are mandatory for FLOATs
 243                     }
 244                     $dbtype .= ')';
 245                 }
 246                 break;
 247             case XMLDB_TYPE_CHAR:
 248                 $dbtype = 'VARCHAR';
 249                 if (empty($xmldb_length)) {
 250                     $xmldb_length='255';
 251                 }
 252                 $dbtype .= '(' . $xmldb_length . ')';
 253                 if ($collation = $this->mdb->get_dbcollation()) {
 254                     if (strpos($collation, 'utf8_') === 0) {
 255                         $dbtype .= " CHARACTER SET utf8";
 256                     }
 257                     $dbtype .= " COLLATE $collation";
 258                 }
 259                 break;
 260             case XMLDB_TYPE_TEXT:
 261                 $dbtype = 'LONGTEXT';
 262                 if ($collation = $this->mdb->get_dbcollation()) {
 263                     if (strpos($collation, 'utf8_') === 0) {
 264                         $dbtype .= " CHARACTER SET utf8";
 265                     }
 266                     $dbtype .= " COLLATE $collation";
 267                 }
 268                 break;
 269             case XMLDB_TYPE_BINARY:
 270                 $dbtype = 'LONGBLOB';
 271                 break;
 272             case XMLDB_TYPE_DATETIME:
 273                 $dbtype = 'DATETIME';
 274         }
 275         return $dbtype;
 276     }
 277
 278     /**
 279      * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default
 280      * (usually invoked from getModifyDefaultSQL()
 281      *
 282      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 283      * @param xmldb_field $xmldb_field The xmldb_field object instance.
 284      * @return array Array of SQL statements to create a field's default.
 285      */
 286     public function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
 287         // Just a wrapper over the getAlterFieldSQL() function for MySQL that
 288         // is capable of handling defaults
 289         return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 290     }
 291
 292     /**
 293      * Given one correct xmldb_field and the new name, returns the SQL statements
 294      * to rename it (inside one array).
 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 get the renamed field from.
 298      * @param string $newname The new name to rename the field to.
 299      * @return array The SQL statements for renaming the field.
 300      */
 301     public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) {
 302         // NOTE: MySQL is pretty different from the standard to justify this overloading.
 303
 304         // Need a clone of xmldb_field to perform the change leaving original unmodified
 305         $xmldb_field_clone = clone($xmldb_field);
 306
 307         // Change the name of the field to perform the change
 308         $xmldb_field_clone->setName($newname);
 309
 310         $fieldsql = $this->getFieldSQL($xmldb_table, $xmldb_field_clone);
 311
 312         $sql = 'ALTER TABLE ' . $this->getTableName($xmldb_table) . ' CHANGE ' .
 313                $xmldb_field->getName() . ' ' . $fieldsql;
 314
 315         return array($sql);
 316     }
 317
 318     /**
 319      * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default
 320      * (usually invoked from getModifyDefaultSQL()
 321      *
 322      * Note that this method may be dropped in future.
 323      *
 324      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 325      * @param xmldb_field $xmldb_field The xmldb_field object instance.
 326      * @return array Array of SQL statements to create a field's default.
 327      *
 328      * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL()
 329      */
 330     public function getDropDefaultSQL($xmldb_table, $xmldb_field) {
 331         // Just a wrapper over the getAlterFieldSQL() function for MySQL that
 332         // is capable of handling defaults
 333         return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 334     }
 335
 336     /**
 337      * Returns the code (array of statements) needed to add one comment to the table.
 338      *
 339      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 340      * @return array Array of SQL statements to add one comment to the table.
 341      */
 342     function getCommentSQL ($xmldb_table) {
 343         $comment = '';
 344
 345         if ($xmldb_table->getComment()) {
 346             $comment .= 'ALTER TABLE ' . $this->getTableName($xmldb_table);
 347             $comment .= " COMMENT='" . $this->addslashes(substr($xmldb_table->getComment(), 0, 60)) . "'";
 348         }
 349         return array($comment);
 350     }
 351
 352     /**
 353      * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg).
 354      *
 355      * (MySQL requires the whole xmldb_table object to be specified, so we add it always)
 356      *
 357      * This is invoked from getNameForObject().
 358      * Only some DB have this implemented.
 359      *
 360      * @param string $object_name The object's name to check for.
 361      * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg).
 362      * @param string $table_name The table's name to check in
 363      * @return bool If such name is currently in use (true) or no (false)
 364      */
 365     public function isNameInUse($object_name, $type, $table_name) {
 366
 367         switch($type) {
 368             case 'ix':
 369             case 'uix':
 370                 // First of all, check table exists
 371                 $metatables = $this->mdb->get_tables();
 372                 if (isset($metatables[$table_name])) {
 373                     // Fetch all the indexes in the table
 374                     if ($indexes = $this->mdb->get_indexes($table_name)) {
 375                         // Look for existing index in array
 376                         if (isset($indexes[$object_name])) {
 377                             return true;
 378                         }
 379                     }
 380                 }
 381                 break;
 382         }
 383         return false; //No name in use found
 384     }
 385
 386
 387     /**
 388      * Returns an array of reserved words (lowercase) for this DB
 389      * @return array An array of database specific reserved words
 390      */
 391     public static function getReservedWords() {
 392         // This file contains the reserved words for MySQL databases
 393         // from http://dev.mysql.com/doc/refman/6.0/en/reserved-words.html
 394         $reserved_words = array (
 395             'accessible', 'add', 'all', 'alter', 'analyze', 'and', 'as', 'asc',
 396             'asensitive', 'before', 'between', 'bigint', 'binary',
 397             'blob', 'both', 'by', 'call', 'cascade', 'case', 'change',
 398             'char', 'character', 'check', 'collate', 'column',
 399             'condition', 'connection', 'constraint', 'continue',
 400             'convert', 'create', 'cross', 'current_date', 'current_time',
 401             'current_timestamp', 'current_user', 'cursor', 'database',
 402             'databases', 'day_hour', 'day_microsecond',
 403             'day_minute', 'day_second', 'dec', 'decimal', 'declare',
 404             'default', 'delayed', 'delete', 'desc', 'describe',
 405             'deterministic', 'distinct', 'distinctrow', 'div', 'double',
 406             'drop', 'dual', 'each', 'else', 'elseif', 'enclosed', 'escaped',
 407             'exists', 'exit', 'explain', 'false', 'fetch', 'float', 'float4',
 408             'float8', 'for', 'force', 'foreign', 'from', 'fulltext', 'grant',
 409             'group', 'having', 'high_priority', 'hour_microsecond',
 410             'hour_minute', 'hour_second', 'if', 'ignore', 'in', 'index',
 411             'infile', 'inner', 'inout', 'insensitive', 'insert', 'int', 'int1',
 412             'int2', 'int3', 'int4', 'int8', 'integer', 'interval', 'into', 'is',
 413             'iterate', 'join', 'key', 'keys', 'kill', 'leading', 'leave', 'left',
 414             'like', 'limit', 'linear', 'lines', 'load', 'localtime', 'localtimestamp',
 415             'lock', 'long', 'longblob', 'longtext', 'loop', 'low_priority', 'master_heartbeat_period',
 416             'master_ssl_verify_server_cert', 'match', 'mediumblob', 'mediumint', 'mediumtext',
 417             'middleint', 'minute_microsecond', 'minute_second',
 418             'mod', 'modifies', 'natural', 'not', 'no_write_to_binlog',
 419             'null', 'numeric', 'on', 'optimize', 'option', 'optionally',
 420             'or', 'order', 'out', 'outer', 'outfile', 'overwrite', 'precision', 'primary',
 421             'procedure', 'purge', 'raid0', 'range', 'read', 'read_only', 'read_write', 'reads', 'real',
 422             'references', 'regexp', 'release', 'rename', 'repeat', 'replace',
 423             'require', 'restrict', 'return', 'revoke', 'right', 'rlike', 'schema',
 424             'schemas', 'second_microsecond', 'select', 'sensitive',
 425             'separator', 'set', 'show', 'smallint', 'soname', 'spatial',
 426             'specific', 'sql', 'sqlexception', 'sqlstate', 'sqlwarning',
 427             'sql_big_result', 'sql_calc_found_rows', 'sql_small_result',
 428             'ssl', 'starting', 'straight_join', 'table', 'terminated', 'then',
 429             'tinyblob', 'tinyint', 'tinytext', 'to', 'trailing', 'trigger', 'true',
 430             'undo', 'union', 'unique', 'unlock', 'unsigned', 'update',
 431             'upgrade', 'usage', 'use', 'using', 'utc_date', 'utc_time',
 432             'utc_timestamp', 'values', 'varbinary', 'varchar', 'varcharacter',
 433             'varying', 'when', 'where', 'while', 'with', 'write', 'x509',
 434             'xor', 'year_month', 'zerofill'
 435         );
 436         return $reserved_words;
 437     }
 438 }