MDL-34211 Use the $table_name argument to check against $metatables key values
[moodle.git] / lib / ddl / mysql_sql_generator.php
CommitLineData
94b63295 1<?php
94b63295 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
94b63295 17/**
18 * Mysql specific SQL code generator.
19 *
5a070f04 20 * @package core_ddl
94b63295 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 */
8165877a 25
c86f5381
PS
26defined('MOODLE_INTERNAL') || die();
27
f33e1ed4 28require_once($CFG->libdir.'/ddl/sql_generator.php');
29
5a070f04
PS
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 */
f33e1ed4 40class mysql_sql_generator extends sql_generator {
8165877a 41
5a070f04 42 // Only set values that are different from the defaults present in XMLDBgenerator
8165877a 43
5a070f04
PS
44 /** @var string Used to quote names. */
45 public $quote_string = '`';
edc8779b 46
5a070f04
PS
47 /** @var string To define the default to set for NOT NULLs CHARs without default (null=do nothing).*/
48 public $default_for_char = '';
8165877a 49
5a070f04
PS
50 /** @var bool To specify if the generator must use some DEFAULT clause to drop defaults.*/
51 public $drop_default_value_required = true;
812e363a 52
5a070f04
PS
53 /** @var string The DEFAULT clause required to drop defaults.*/
54 public $drop_default_value = null;
8165877a 55
5a070f04
PS
56 /** @var string To force primary key names to one string (null=no force).*/
57 public $primary_key_name = '';
662244cb 58
5a070f04
PS
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';
662244cb 61
5a070f04
PS
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';
82b7ceb5 64
5a070f04
PS
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';
e54ae526 73
f33e1ed4 74 public $add_after_clause = true; // Does the generator need to add the after clause for fields
f0b50bbe 75
5a070f04
PS
76 /** @var string Characters to be used as concatenation operator.*/
77 public $concat_character = null;
9dbc13db 78
5a070f04
PS
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';
f8c485b0 81
5a070f04
PS
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';
618a982e 84
5a070f04
PS
85 /** @var string SQL sentence to rename one index where 'TABLENAME', 'OLDINDEXNAME' and 'NEWINDEXNAME' are dynamically replaced.*/
86 public $rename_index_sql = null;
1c86ef5b 87
5a070f04
PS
88 /** @var string SQL sentence to rename one key 'TABLENAME', 'OLDKEYNAME' and 'NEWKEYNAME' are dynamically replaced.*/
89 public $rename_key_sql = null;
e77fd021 90
be415e95 91 /**
92 * Reset a sequence to the id field of a table.
5a070f04
PS
93 *
94 * @param xmldb_table|string $table name of table or the table object.
95 * @return array of sql statements
be415e95 96 */
b1ca1387 97 public function getResetSequenceSQL($table) {
98
99 if ($table instanceof xmldb_table) {
be415e95 100 $tablename = $table->getName();
b1ca1387 101 } else {
102 $tablename = $table;
be415e95 103 }
b1ca1387 104
be415e95 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++;
b1ca1387 108 return array("ALTER TABLE $this->prefix$tablename AUTO_INCREMENT = $value");
be415e95 109 }
110
d35ece6c
PS
111 /**
112 * Given one correct xmldb_table, returns the SQL statements
5a070f04
PS
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.
d35ece6c
PS
118 */
119 public function getCreateTableSQL($xmldb_table) {
120 // first find out if want some special db engine
121 $engine = null;
122 if (method_exists($this->mdb, 'get_dbengine')) {
123 $engine = $this->mdb->get_dbengine();
124 }
125
126 $sqlarr = parent::getCreateTableSQL($xmldb_table);
127
128 if (!$engine) {
129 // we rely on database defaults
130 return $sqlarr;
131 }
132
133 // let's inject the engine into SQL
134 foreach ($sqlarr as $i=>$sql) {
135 if (strpos($sql, 'CREATE TABLE ') === 0) {
136 $sqlarr[$i] .= " ENGINE = $engine";
137 }
138 }
139
140 return $sqlarr;
141 }
df997f84 142
21a7e260 143 /**
144 * Given one correct xmldb_table, returns the SQL statements
5a070f04
PS
145 * to create temporary table (inside one array).
146 *
147 * @param xmldb_table $xmldb_table The xmldb_table object instance.
148 * @return array of sql statements
21a7e260 149 */
150 public function getCreateTempTableSQL($xmldb_table) {
4ff402d6 151 $this->temptables->add_temptable($xmldb_table->getName());
d35ece6c 152 $sqlarr = parent::getCreateTableSQL($xmldb_table); // we do not want the engine hack included in create table SQL
4d163d02 153 $sqlarr = preg_replace('/^CREATE TABLE (.*)/s', 'CREATE TEMPORARY TABLE $1', $sqlarr);
21a7e260 154 return $sqlarr;
155 }
156
b922e86b 157 /**
9b3323b8
PS
158 * Given one correct xmldb_table, returns the SQL statements
159 * to drop it (inside one array).
160 *
161 * @param xmldb_table $xmldb_table The table to drop.
162 * @return array SQL statement(s) for dropping the specified table.
163 */
164 public function getDropTableSQL($xmldb_table) {
165 $sqlarr = parent::getDropTableSQL($xmldb_table);
166 if ($this->temptables->is_temptable($xmldb_table->getName())) {
167 $sqlarr = preg_replace('/^DROP TABLE/', "DROP TEMPORARY TABLE", $sqlarr);
168 $this->temptables->delete_temptable($xmldb_table->getName());
169 }
170 return $sqlarr;
171 }
172
8165877a 173 /**
5a070f04
PS
174 * Given one XMLDB Type, length and decimals, returns the DB proper SQL type.
175 *
176 * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants.
177 * @param int $xmldb_length The length of that data type.
178 * @param int $xmldb_decimals The decimal places of precision of the data type.
179 * @return string The DB defined data type.
8165877a 180 */
f33e1ed4 181 public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
8165877a 182
183 switch ($xmldb_type) {
184 case XMLDB_TYPE_INTEGER: // From http://mysql.com/doc/refman/5.0/en/numeric-types.html!
185 if (empty($xmldb_length)) {
186 $xmldb_length = 10;
187 }
188 if ($xmldb_length > 9) {
189 $dbtype = 'BIGINT';
8165877a 190 } else if ($xmldb_length > 6) {
191 $dbtype = 'INT';
192 } else if ($xmldb_length > 4) {
193 $dbtype = 'MEDIUMINT';
194 } else if ($xmldb_length > 2) {
195 $dbtype = 'SMALLINT';
196 } else {
e54ae526 197 $dbtype = 'TINYINT';
8165877a 198 }
199 $dbtype .= '(' . $xmldb_length . ')';
200 break;
201 case XMLDB_TYPE_NUMBER:
edc8779b 202 $dbtype = $this->number_type;
8165877a 203 if (!empty($xmldb_length)) {
204 $dbtype .= '(' . $xmldb_length;
205 if (!empty($xmldb_decimals)) {
206 $dbtype .= ',' . $xmldb_decimals;
207 }
208 $dbtype .= ')';
209 }
210 break;
211 case XMLDB_TYPE_FLOAT:
860b5546 212 $dbtype = 'DOUBLE';
213 if (!empty($xmldb_decimals)) {
214 if ($xmldb_decimals < 6) {
215 $dbtype = 'FLOAT';
216 }
217 }
8165877a 218 if (!empty($xmldb_length)) {
219 $dbtype .= '(' . $xmldb_length;
220 if (!empty($xmldb_decimals)) {
221 $dbtype .= ',' . $xmldb_decimals;
d09af2e1 222 } else {
223 $dbtype .= ', 0'; // In MySQL, if length is specified, decimals are mandatory for FLOATs
8165877a 224 }
225 $dbtype .= ')';
226 }
227 break;
228 case XMLDB_TYPE_CHAR:
229 $dbtype = 'VARCHAR';
866c0747 230 if (empty($xmldb_length)) {
8165877a 231 $xmldb_length='255';
232 }
233 $dbtype .= '(' . $xmldb_length . ')';
234 break;
235 case XMLDB_TYPE_TEXT:
f13489df 236 $dbtype = 'LONGTEXT';
8165877a 237 break;
238 case XMLDB_TYPE_BINARY:
f13489df 239 $dbtype = 'LONGBLOB';
8165877a 240 break;
241 case XMLDB_TYPE_DATETIME:
242 $dbtype = 'DATETIME';
243 }
244 return $dbtype;
245 }
246
812e363a 247 /**
5a070f04 248 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default
812e363a 249 * (usually invoked from getModifyDefaultSQL()
5a070f04
PS
250 *
251 * @param xmldb_table $xmldb_table The xmldb_table object instance.
252 * @param xmldb_field $xmldb_field The xmldb_field object instance.
253 * @return array Array of SQL statements to create a field's default.
812e363a 254 */
f33e1ed4 255 public function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
5a070f04
PS
256 // Just a wrapper over the getAlterFieldSQL() function for MySQL that
257 // is capable of handling defaults
812e363a 258 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
259 }
260
b5d61bfd 261 /**
a8cb94f6 262 * Given one correct xmldb_field and the new name, returns the SQL statements
5a070f04
PS
263 * to rename it (inside one array).
264 *
265 * @param xmldb_table $xmldb_table The table related to $xmldb_field.
266 * @param xmldb_field $xmldb_field The instance of xmldb_field to get the renamed field from.
267 * @param string $newname The new name to rename the field to.
268 * @return array The SQL statements for renaming the field.
b5d61bfd 269 */
f33e1ed4 270 public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) {
5a070f04 271 // NOTE: MySQL is pretty different from the standard to justify this overloading.
b5d61bfd 272
5a070f04 273 // Need a clone of xmldb_field to perform the change leaving original unmodified
c7a311b5 274 $xmldb_field_clone = clone($xmldb_field);
275
5a070f04 276 // Change the name of the field to perform the change
9ee910e6 277 $xmldb_field_clone->setName($newname);
b5d61bfd 278
257ad88f 279 $fieldsql = $this->getFieldSQL($xmldb_table, $xmldb_field_clone);
f33e1ed4 280
9ee910e6
EL
281 $sql = 'ALTER TABLE ' . $this->getTableName($xmldb_table) . ' CHANGE ' .
282 $xmldb_field->getName() . ' ' . $fieldsql;
b5d61bfd 283
f33e1ed4 284 return array($sql);
b5d61bfd 285 }
286
812e363a 287 /**
72c45dcc 288 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default
812e363a 289 * (usually invoked from getModifyDefaultSQL()
5a070f04
PS
290 *
291 * Note that this method may be dropped in future.
292 *
293 * @param xmldb_table $xmldb_table The xmldb_table object instance.
294 * @param xmldb_field $xmldb_field The xmldb_field object instance.
295 * @return array Array of SQL statements to create a field's default.
296 *
297 * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL()
812e363a 298 */
f33e1ed4 299 public function getDropDefaultSQL($xmldb_table, $xmldb_field) {
5a070f04
PS
300 // Just a wrapper over the getAlterFieldSQL() function for MySQL that
301 // is capable of handling defaults
812e363a 302 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
303 }
304
8165877a 305 /**
5a070f04
PS
306 * Returns the code (array of statements) needed to add one comment to the table.
307 *
308 * @param xmldb_table $xmldb_table The xmldb_table object instance.
309 * @return array Array of SQL statements to add one comment to the table.
8165877a 310 */
e54ae526 311 function getCommentSQL ($xmldb_table) {
e54ae526 312 $comment = '';
eef868d1 313
e54ae526 314 if ($xmldb_table->getComment()) {
9af19c72 315 $comment .= 'ALTER TABLE ' . $this->getTableName($xmldb_table);
f33e1ed4 316 $comment .= " COMMENT='" . $this->addslashes(substr($xmldb_table->getComment(), 0, 60)) . "'";
82b7ceb5 317 }
9dcc6300 318 return array($comment);
8165877a 319 }
320
9770914d 321 /**
5a070f04
PS
322 * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg).
323 *
324 * (MySQL requires the whole xmldb_table object to be specified, so we add it always)
325 *
326 * This is invoked from getNameForObject().
327 * Only some DB have this implemented.
328 *
329 * @param string $object_name The object's name to check for.
330 * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg).
331 * @param string $table_name The table's name to check in
332 * @return bool If such name is currently in use (true) or no (false)
9770914d 333 */
f33e1ed4 334 public function isNameInUse($object_name, $type, $table_name) {
84a13375 335
9770914d 336 switch($type) {
337 case 'ix':
338 case 'uix':
5a070f04 339 // First of all, check table exists
f33e1ed4 340 $metatables = $this->mdb->get_tables();
eab8660e 341 if (isset($metatables[$table_name])) {
5a070f04 342 // Fetch all the indexes in the table
eab8660e 343 if ($indexes = $this->mdb->get_indexes($table_name)) {
5a070f04 344 // Look for existing index in array
f33e1ed4 345 if (isset($indexes[$object_name])) {
9770914d 346 return true;
347 }
348 }
349 }
350 break;
351 }
352 return false; //No name in use found
353 }
354
355
82b7ceb5 356 /**
357 * Returns an array of reserved words (lowercase) for this DB
5a070f04 358 * @return array An array of database specific reserved words
82b7ceb5 359 */
f33e1ed4 360 public static function getReservedWords() {
5a070f04
PS
361 // This file contains the reserved words for MySQL databases
362 // from http://dev.mysql.com/doc/refman/6.0/en/reserved-words.html
82b7ceb5 363 $reserved_words = array (
09717fa5 364 'accessible', 'add', 'all', 'alter', 'analyze', 'and', 'as', 'asc',
82b7ceb5 365 'asensitive', 'before', 'between', 'bigint', 'binary',
366 'blob', 'both', 'by', 'call', 'cascade', 'case', 'change',
367 'char', 'character', 'check', 'collate', 'column',
368 'condition', 'connection', 'constraint', 'continue',
369 'convert', 'create', 'cross', 'current_date', 'current_time',
370 'current_timestamp', 'current_user', 'cursor', 'database',
371 'databases', 'day_hour', 'day_microsecond',
372 'day_minute', 'day_second', 'dec', 'decimal', 'declare',
373 'default', 'delayed', 'delete', 'desc', 'describe',
374 'deterministic', 'distinct', 'distinctrow', 'div', 'double',
375 'drop', 'dual', 'each', 'else', 'elseif', 'enclosed', 'escaped',
376 'exists', 'exit', 'explain', 'false', 'fetch', 'float', 'float4',
377 'float8', 'for', 'force', 'foreign', 'from', 'fulltext', 'grant',
378 'group', 'having', 'high_priority', 'hour_microsecond',
379 'hour_minute', 'hour_second', 'if', 'ignore', 'in', 'index',
380 'infile', 'inner', 'inout', 'insensitive', 'insert', 'int', 'int1',
381 'int2', 'int3', 'int4', 'int8', 'integer', 'interval', 'into', 'is',
382 'iterate', 'join', 'key', 'keys', 'kill', 'leading', 'leave', 'left',
09717fa5 383 'like', 'limit', 'linear', 'lines', 'load', 'localtime', 'localtimestamp',
384 'lock', 'long', 'longblob', 'longtext', 'loop', 'low_priority', 'master_heartbeat_period',
385 'master_ssl_verify_server_cert', 'match', 'mediumblob', 'mediumint', 'mediumtext',
82b7ceb5 386 'middleint', 'minute_microsecond', 'minute_second',
387 'mod', 'modifies', 'natural', 'not', 'no_write_to_binlog',
388 'null', 'numeric', 'on', 'optimize', 'option', 'optionally',
09717fa5 389 'or', 'order', 'out', 'outer', 'outfile', 'overwrite', 'precision', 'primary',
390 'procedure', 'purge', 'raid0', 'range', 'read', 'read_only', 'read_write', 'reads', 'real',
82b7ceb5 391 'references', 'regexp', 'release', 'rename', 'repeat', 'replace',
392 'require', 'restrict', 'return', 'revoke', 'right', 'rlike', 'schema',
393 'schemas', 'second_microsecond', 'select', 'sensitive',
394 'separator', 'set', 'show', 'smallint', 'soname', 'spatial',
395 'specific', 'sql', 'sqlexception', 'sqlstate', 'sqlwarning',
396 'sql_big_result', 'sql_calc_found_rows', 'sql_small_result',
397 'ssl', 'starting', 'straight_join', 'table', 'terminated', 'then',
398 'tinyblob', 'tinyint', 'tinytext', 'to', 'trailing', 'trigger', 'true',
399 'undo', 'union', 'unique', 'unlock', 'unsigned', 'update',
400 'upgrade', 'usage', 'use', 'using', 'utc_date', 'utc_time',
401 'utc_timestamp', 'values', 'varbinary', 'varchar', 'varcharacter',
402 'varying', 'when', 'where', 'while', 'with', 'write', 'x509',
403 'xor', 'year_month', 'zerofill'
eef868d1 404 );
82b7ceb5 405 return $reserved_words;
406 }
8165877a 407}