Merge branch 'MDL-34211' of git://github.com/appalachianstate/moodle into MOODLE_23_S...
[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) {
7ec63b27
PS
120 // First find out if want some special db engine.
121 $engine = $this->mdb->get_dbengine();
41e8883e
PS
122 // Do we know collation?
123 $collation = $this->mdb->get_dbcollation();
124
d35ece6c
PS
125 $sqlarr = parent::getCreateTableSQL($xmldb_table);
126
7ec63b27 127 // Let's inject the extra MySQL tweaks.
d35ece6c
PS
128 foreach ($sqlarr as $i=>$sql) {
129 if (strpos($sql, 'CREATE TABLE ') === 0) {
7ec63b27
PS
130 if ($engine) {
131 $sqlarr[$i] .= " ENGINE = $engine";
132 }
41e8883e
PS
133 if ($collation) {
134 if (strpos($collation, 'utf8_') === 0) {
135 $sqlarr[$i] .= " DEFAULT CHARACTER SET utf8";
136 }
137 $sqlarr[$i] .= " DEFAULT COLLATE = $collation";
138 }
d35ece6c
PS
139 }
140 }
141
142 return $sqlarr;
143 }
df997f84 144
21a7e260 145 /**
146 * Given one correct xmldb_table, returns the SQL statements
5a070f04
PS
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
21a7e260 151 */
152 public function getCreateTempTableSQL($xmldb_table) {
41e8883e
PS
153 // Do we know collation?
154 $collation = $this->mdb->get_dbcollation();
4ff402d6 155 $this->temptables->add_temptable($xmldb_table->getName());
41e8883e
PS
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
21a7e260 173 return $sqlarr;
174 }
175
b922e86b 176 /**
9b3323b8
PS
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
8165877a 192 /**
5a070f04
PS
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.
8165877a 199 */
f33e1ed4 200 public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
8165877a 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';
8165877a 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 {
e54ae526 216 $dbtype = 'TINYINT';
8165877a 217 }
218 $dbtype .= '(' . $xmldb_length . ')';
219 break;
220 case XMLDB_TYPE_NUMBER:
edc8779b 221 $dbtype = $this->number_type;
8165877a 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:
860b5546 231 $dbtype = 'DOUBLE';
232 if (!empty($xmldb_decimals)) {
233 if ($xmldb_decimals < 6) {
234 $dbtype = 'FLOAT';
235 }
236 }
8165877a 237 if (!empty($xmldb_length)) {
238 $dbtype .= '(' . $xmldb_length;
239 if (!empty($xmldb_decimals)) {
240 $dbtype .= ',' . $xmldb_decimals;
d09af2e1 241 } else {
242 $dbtype .= ', 0'; // In MySQL, if length is specified, decimals are mandatory for FLOATs
8165877a 243 }
244 $dbtype .= ')';
245 }
246 break;
247 case XMLDB_TYPE_CHAR:
248 $dbtype = 'VARCHAR';
866c0747 249 if (empty($xmldb_length)) {
8165877a 250 $xmldb_length='255';
251 }
252 $dbtype .= '(' . $xmldb_length . ')';
41e8883e
PS
253 if ($collation = $this->mdb->get_dbcollation()) {
254 if (strpos($collation, 'utf8_') === 0) {
255 $dbtype .= " CHARACTER SET utf8";
256 }
257 $dbtype .= " COLLATE $collation";
258 }
8165877a 259 break;
260 case XMLDB_TYPE_TEXT:
f13489df 261 $dbtype = 'LONGTEXT';
41e8883e
PS
262 if ($collation = $this->mdb->get_dbcollation()) {
263 if (strpos($collation, 'utf8_') === 0) {
264 $dbtype .= " CHARACTER SET utf8";
265 }
266 $dbtype .= " COLLATE $collation";
267 }
8165877a 268 break;
269 case XMLDB_TYPE_BINARY:
f13489df 270 $dbtype = 'LONGBLOB';
8165877a 271 break;
272 case XMLDB_TYPE_DATETIME:
273 $dbtype = 'DATETIME';
274 }
275 return $dbtype;
276 }
277
812e363a 278 /**
5a070f04 279 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default
812e363a 280 * (usually invoked from getModifyDefaultSQL()
5a070f04
PS
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.
812e363a 285 */
f33e1ed4 286 public function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
5a070f04
PS
287 // Just a wrapper over the getAlterFieldSQL() function for MySQL that
288 // is capable of handling defaults
812e363a 289 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
290 }
291
b5d61bfd 292 /**
a8cb94f6 293 * Given one correct xmldb_field and the new name, returns the SQL statements
5a070f04
PS
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.
b5d61bfd 300 */
f33e1ed4 301 public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) {
5a070f04 302 // NOTE: MySQL is pretty different from the standard to justify this overloading.
b5d61bfd 303
5a070f04 304 // Need a clone of xmldb_field to perform the change leaving original unmodified
c7a311b5 305 $xmldb_field_clone = clone($xmldb_field);
306
5a070f04 307 // Change the name of the field to perform the change
9ee910e6 308 $xmldb_field_clone->setName($newname);
b5d61bfd 309
257ad88f 310 $fieldsql = $this->getFieldSQL($xmldb_table, $xmldb_field_clone);
f33e1ed4 311
9ee910e6
EL
312 $sql = 'ALTER TABLE ' . $this->getTableName($xmldb_table) . ' CHANGE ' .
313 $xmldb_field->getName() . ' ' . $fieldsql;
b5d61bfd 314
f33e1ed4 315 return array($sql);
b5d61bfd 316 }
317
812e363a 318 /**
72c45dcc 319 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default
812e363a 320 * (usually invoked from getModifyDefaultSQL()
5a070f04
PS
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()
812e363a 329 */
f33e1ed4 330 public function getDropDefaultSQL($xmldb_table, $xmldb_field) {
5a070f04
PS
331 // Just a wrapper over the getAlterFieldSQL() function for MySQL that
332 // is capable of handling defaults
812e363a 333 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
334 }
335
8165877a 336 /**
5a070f04
PS
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.
8165877a 341 */
e54ae526 342 function getCommentSQL ($xmldb_table) {
e54ae526 343 $comment = '';
eef868d1 344
e54ae526 345 if ($xmldb_table->getComment()) {
9af19c72 346 $comment .= 'ALTER TABLE ' . $this->getTableName($xmldb_table);
f33e1ed4 347 $comment .= " COMMENT='" . $this->addslashes(substr($xmldb_table->getComment(), 0, 60)) . "'";
82b7ceb5 348 }
9dcc6300 349 return array($comment);
8165877a 350 }
351
9770914d 352 /**
5a070f04
PS
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)
9770914d 364 */
f33e1ed4 365 public function isNameInUse($object_name, $type, $table_name) {
84a13375 366
9770914d 367 switch($type) {
368 case 'ix':
369 case 'uix':
5a070f04 370 // First of all, check table exists
f33e1ed4 371 $metatables = $this->mdb->get_tables();
eab8660e 372 if (isset($metatables[$table_name])) {
5a070f04 373 // Fetch all the indexes in the table
eab8660e 374 if ($indexes = $this->mdb->get_indexes($table_name)) {
5a070f04 375 // Look for existing index in array
f33e1ed4 376 if (isset($indexes[$object_name])) {
9770914d 377 return true;
378 }
379 }
380 }
381 break;
382 }
383 return false; //No name in use found
384 }
385
386
82b7ceb5 387 /**
388 * Returns an array of reserved words (lowercase) for this DB
5a070f04 389 * @return array An array of database specific reserved words
82b7ceb5 390 */
f33e1ed4 391 public static function getReservedWords() {
5a070f04
PS
392 // This file contains the reserved words for MySQL databases
393 // from http://dev.mysql.com/doc/refman/6.0/en/reserved-words.html
82b7ceb5 394 $reserved_words = array (
09717fa5 395 'accessible', 'add', 'all', 'alter', 'analyze', 'and', 'as', 'asc',
82b7ceb5 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',
09717fa5 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',
82b7ceb5 417 'middleint', 'minute_microsecond', 'minute_second',
418 'mod', 'modifies', 'natural', 'not', 'no_write_to_binlog',
419 'null', 'numeric', 'on', 'optimize', 'option', 'optionally',
09717fa5 420 'or', 'order', 'out', 'outer', 'outfile', 'overwrite', 'precision', 'primary',
421 'procedure', 'purge', 'raid0', 'range', 'read', 'read_only', 'read_write', 'reads', 'real',
82b7ceb5 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'
eef868d1 435 );
82b7ceb5 436 return $reserved_words;
437 }
8165877a 438}