MDL-31251 documentation : whitespace fixes
[moodle.git] / lib / ddl / 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/>.
91496d15 16
91496d15 17
94b63295 18/**
83a4ee1e 19 * This class represent the base generator class where all the needed functions to generate proper SQL are defined.
94b63295 20 *
21 * The rest of classes will inherit, by default, the same logic.
a3790ed5 22 * Functions will be overridden as needed to generate correct SQL.
94b63295 23 *
c86f5381 24 * @package core
83a4ee1e 25 * @category ddl
c86f5381 26 * @subpackage ddl
94b63295 27 * @copyright 1999 onwards Martin Dougiamas http://dougiamas.com
28 * 2001-3001 Eloy Lafuente (stronk7) http://contiento.com
29 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
30 */
91496d15 31
c86f5381
PS
32defined('MOODLE_INTERNAL') || die();
33
94b63295 34/**
a3790ed5 35 * Abstract sql generator class, base for all db specific implementations.
83a4ee1e
AB
36 *
37 * @package core
38 * @category ddl
39 * @subpackage ddl
40 * @copyright 1999 onwards Martin Dougiamas http://dougiamas.com
41 * 2001-3001 Eloy Lafuente (stronk7) http://contiento.com
42 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
94b63295 43 */
f33e1ed4 44abstract class sql_generator {
91496d15 45
eef868d1 46/// Please, avoid editing this defaults in this base class!
91496d15 47/// It could change the behaviour of the rest of generators
48/// that, by default, inherit this configuration.
49/// To change any of them, do it in extended classes instead.
50
83a4ee1e
AB
51 /** @var string Used to quote names. */
52 public $quote_string = '"';
716332e6 53
83a4ee1e
AB
54 /** @var string To be automatically added at the end of each statement. */
55 public $statement_end = ';';
91496d15 56
83a4ee1e
AB
57 /** @var bool To decide if we want to quote all the names or only the reserved ones. */
58 public $quote_all = false;
9dcc6300 59
83a4ee1e
AB
60 /** @var bool To create all the integers as NUMBER(x) (also called DECIMAL, NUMERIC...). */
61 public $integer_to_number = false;
62 /** @var bool To create all the floats as NUMBER(x) (also called DECIMAL, NUMERIC...). */
63 public $float_to_number = false;
91496d15 64
83a4ee1e
AB
65 /** @var string Proper type for NUMBER(x) in this DB. */
66 public $number_type = 'NUMERIC';
91496d15 67
83a4ee1e
AB
68 /** @var bool To define in the generator must handle unsigned information.*/
69 public $unsigned_allowed = true;
70 /** @var string To define the default to set for NOT NULLs CHARs without default (null=do nothing).*/
71 public $default_for_char = null;
91496d15 72
83a4ee1e
AB
73 /** @var bool To specify if the generator must use some DEFAULT clause to drop defaults.*/
74 public $drop_default_value_required = false;
75 /** @var string The DEFAULT clause required to drop defaults.*/
76 public $drop_default_value = '';
812e363a 77
83a4ee1e
AB
78 /** @var bool To decide if the default clause of each field must go after the null clause.*/
79 public $default_after_null = true;
f075bac4 80
83a4ee1e
AB
81 /**
82 * @var bool To force the generator if NULL clauses must be specified. It shouldn't be necessary.
83 * note: some mssql drivers require them or everything is created as NOT NULL :-(
84 */
85 public $specify_nulls = false;
91496d15 86
83a4ee1e
AB
87 /** @var string To force primary key names to one string (null=no force).*/
88 public $primary_key_name = null;
91496d15 89
83a4ee1e
AB
90 /** @var bool True if the generator builds primary keys.*/
91 public $primary_keys = true;
92 /** @var bool True if the generator builds unique keys.*/
93 public $unique_keys = false;
94 /** @var bool True if the generator builds foreign keys.*/
95 public $foreign_keys = false;
0852990f 96
83a4ee1e
AB
97 /**
98 * @var string Template to drop PKs.
99 * 'TABLENAME' and 'KEYNAME' will be replaced from this template.
100 */
101 public $drop_primary_key = 'ALTER TABLE TABLENAME DROP CONSTRAINT KEYNAME';
0852990f 102
83a4ee1e
AB
103 /**
104 * @var string Template to drop UKs.
105 * 'TABLENAME' and 'KEYNAME' will be replaced from this template.
106 */
107 public $drop_unique_key = 'ALTER TABLE TABLENAME DROP CONSTRAINT KEYNAME';
91496d15 108
83a4ee1e
AB
109 /** @var string Template to drop FKs.
110 * 'TABLENAME' and 'KEYNAME' will be replaced from this template.
111 */
112 public $drop_foreign_key = 'ALTER TABLE TABLENAME DROP CONSTRAINT KEYNAME';
113
114 /** @var bool True if the generator needs to add extra code to generate the sequence fields.*/
115 public $sequence_extra_code = true;
116 /** @var string The particular name for inline sequences in this generator.*/
117 public $sequence_name = 'auto_increment';
118 /** @var string|bool Different name for small (4byte) sequences or false if same.*/
119 public $sequence_name_small = false;
120 /**
29b6a3e8 121 * @var bool To avoid outputting the rest of the field specs, leaving only the name and the sequence_name returned.
83a4ee1e
AB
122 * @see getFieldSQL()
123 */
124 public $sequence_only = false;
91496d15 125
83a4ee1e
AB
126 /** @var bool True if the generator needs to add code for table comments.*/
127 public $add_table_comments = true;
91496d15 128
83a4ee1e
AB
129 /** @var bool True if the generator needs to add the after clause for fields.*/
130 public $add_after_clause = false;
f0b50bbe 131
83a4ee1e
AB
132 /**
133 * @var bool True if the generator needs to prepend the prefix to all the key/index/sequence/trigger/check names.
134 * @see $prefix
135 */
136 public $prefix_on_names = true;
6caad517 137
83a4ee1e
AB
138 /** @var int Maximum length for key/index/sequence/trigger/check names (keep 30 for all!).*/
139 public $names_max_length = 30;
91496d15 140
83a4ee1e
AB
141 /** @var string Characters to be used as concatenation operator.
142 * If not defined, MySQL CONCAT function will be used.
143 */
144 public $concat_character = '||';
a59f3a34 145
83a4ee1e
AB
146 /** @var string SQL sentence to rename one table, both 'OLDNAME' and 'NEWNAME' keywords are dynamically replaced.*/
147 public $rename_table_sql = 'ALTER TABLE OLDNAME RENAME TO NEWNAME';
d05babe9 148
83a4ee1e
AB
149 /** @var string SQL sentence to drop one table where the 'TABLENAME' keyword is dynamically replaced.*/
150 public $drop_table_sql = 'DROP TABLE TABLENAME';
8830218f 151
83a4ee1e
AB
152 /** @var string The SQL template to alter columns where the 'TABLENAME' and 'COLUMNSPECS' keywords are dynamically replaced.*/
153 public $alter_column_sql = 'ALTER TABLE TABLENAME ALTER COLUMN COLUMNSPECS';
0e9e0b3b 154
83a4ee1e
AB
155 /** @var bool The generator will skip the default clause on alter columns.*/
156 public $alter_column_skip_default = false;
0e9e0b3b 157
83a4ee1e
AB
158 /** @var bool The generator will skip the type clause on alter columns.*/
159 public $alter_column_skip_type = false;
19c8321e 160
83a4ee1e
AB
161 /** @var bool The generator will skip the null/notnull clause on alter columns.*/
162 public $alter_column_skip_notnull = false;
19c8321e 163
83a4ee1e 164 /** @var string SQL sentence to rename one column where 'TABLENAME', 'OLDFIELDNAME' and 'NEWFIELDNAME' keywords are dynamically replaced.*/
f33e1ed4 165 public $rename_column_sql = 'ALTER TABLE TABLENAME RENAME COLUMN OLDFIELDNAME TO NEWFIELDNAME';
7c07a932 166
83a4ee1e
AB
167 /** @var string SQL sentence to drop one index where 'TABLENAME', 'INDEXNAME' keywords are dynamically replaced.*/
168 public $drop_index_sql = 'DROP INDEX INDEXNAME';
618a982e 169
83a4ee1e
AB
170 /** @var string SQL sentence to rename one index where 'TABLENAME', 'OLDINDEXNAME' and 'NEWINDEXNAME' are dynamically replaced.*/
171 public $rename_index_sql = 'ALTER INDEX OLDINDEXNAME RENAME TO NEWINDEXNAME';
1c86ef5b 172
83a4ee1e
AB
173 /** @var string SQL sentence to rename one key 'TABLENAME', 'OLDKEYNAME' and 'NEWKEYNAME' are dynamically replaced.*/
174 public $rename_key_sql = 'ALTER TABLE TABLENAME CONSTRAINT OLDKEYNAME RENAME TO NEWKEYNAME';
e77fd021 175
83a4ee1e
AB
176 /** @var string The prefix to be used for all the DB objects.*/
177 public $prefix;
f33e1ed4 178
83a4ee1e
AB
179 /** @var string List of reserved words (in order to quote them properly).*/
180 public $reserved_words;
91496d15 181
83a4ee1e 182 /** @var moodle_database The moodle_database instance.*/
f33e1ed4 183 public $mdb;
83a4ee1e
AB
184 /** @var Control existing temptables.*/
185 protected $temptables;
10f375aa 186
91496d15 187 /**
83a4ee1e
AB
188 * Creates a new sql_generator.
189 * @param moodle_database $mdb The moodle_database object instance.
190 * @param moodle_temptables $temptables The optional moodle_temptables instance, null by default.
91496d15 191 */
10f375aa 192 public function __construct($mdb, $temptables = null) {
f33e1ed4 193 $this->prefix = $mdb->get_prefix();
91496d15 194 $this->reserved_words = $this->getReservedWords();
f33e1ed4 195 $this->mdb = $mdb; // this creates circular reference - the other link must be unset when closing db
10f375aa 196 $this->temptables = $temptables;
91496d15 197 }
198
25d854c6 199 /**
83a4ee1e 200 * Releases all resources.
25d854c6 201 */
202 public function dispose() {
203 $this->mdb = null;
204 }
205
f33e1ed4 206 /**
83a4ee1e 207 * Given one string (or one array), ends it with $statement_end .
29b6a3e8 208 *
83a4ee1e
AB
209 * @see $statement_end
210 *
211 * @param array|string $input SQL statement(s).
f33e1ed4 212 */
213 public function getEndedStatements($input) {
214
215 if (is_array($input)) {
216 foreach ($input as $key=>$content) {
217 $input[$key] = $this->getEndedStatements($content);
218 }
219 return $input;
220 } else {
221 $input = trim($input).$this->statement_end;
222 return $input;
223 }
224 }
91496d15 225
b922e86b 226 /**
83a4ee1e 227 * Given one xmldb_table, checks if it exists in DB (true/false).
b922e86b 228 *
83a4ee1e 229 * @param mixed $table The table to be searched (string name or xmldb_table instance).
b922e86b 230 * @return boolean true/false
231 */
4ff402d6 232 public function table_exists($table) {
b922e86b 233 if (is_string($table)) {
234 $tablename = $table;
235 } else {
236 /// Calculate the name of the table
237 $tablename = $table->getName();
238 }
239
240 /// get all tables in moodle database
241 $tables = $this->mdb->get_tables();
242 $exists = in_array($tablename, $tables);
21a7e260 243
b922e86b 244 return $exists;
245 }
246
91496d15 247 /**
83a4ee1e
AB
248 * This function will return the SQL code needed to create db tables and statements.
249 *
250 * @param xmldb_structure $xmldb_structure An xmldb_structure instance.
29b6a3e8 251 *
83a4ee1e 252 * @see xmldb_structure
91496d15 253 */
f33e1ed4 254 public function getCreateStructureSQL($xmldb_structure) {
255 $results = array();
256
257 if ($tables = $xmldb_structure->getTables()) {
258 foreach ($tables as $table) {
259 $results = array_merge($results, $this->getCreateTableSQL($table));
260 }
261 }
262
f33e1ed4 263 return $results;
91496d15 264 }
265
266 /**
83a4ee1e
AB
267 * Given one xmldb_table, this returns it's correct name, depending of all the parameterization.
268 * eg: This appends $prefix to the table name.
29b6a3e8 269 *
83a4ee1e 270 * @see $prefix
97b91784 271 *
83a4ee1e
AB
272 * @param xmldb_table $xmldb_table The table whose name we want.
273 * @param boolean $quoted To specify if the name must be quoted (if reserved word, only!).
274 * @return string The correct name of the table.
91496d15 275 */
eb099f32 276 public function getTableName(xmldb_table $xmldb_table, $quoted=true) {
97b91784 277 /// Get the name
f33e1ed4 278 $tablename = $this->prefix.$xmldb_table->getName();
279
280 /// Apply quotes optionally
97b91784 281 if ($quoted) {
282 $tablename = $this->getEncQuoted($tablename);
283 }
284
285 return $tablename;
286 }
287
288 /**
a8cb94f6 289 * Given one correct xmldb_table, returns the SQL statements
83a4ee1e
AB
290 * to create it (inside one array).
291 *
292 * @param xmldb_table $xmldb_table An xmldb_table instance.
293 * @return array An array of SQL statements, starting with the table creation SQL followed
294 * by any of its comments, indexes and sequence creation SQL statements.
97b91784 295 */
f33e1ed4 296 public function getCreateTableSQL($xmldb_table) {
2e2a396c 297
9dcc6300 298 $results = array(); //Array where all the sentences will be stored
299
91496d15 300 /// Table header
97b91784 301 $table = 'CREATE TABLE ' . $this->getTableName($xmldb_table) . ' (';
91496d15 302
303 if (!$xmldb_fields = $xmldb_table->getFields()) {
f6ebc341 304 return $results;
91496d15 305 }
4103c354 306
41a50aa2
PS
307 $sequencefield = null;
308
91496d15 309 /// Add the fields, separated by commas
310 foreach ($xmldb_fields as $xmldb_field) {
41a50aa2
PS
311 if ($xmldb_field->getSequence()) {
312 $sequencefield = $xmldb_field->getName();
313 }
257ad88f 314 $table .= "\n " . $this->getFieldSQL($xmldb_table, $xmldb_field);
f0b50bbe 315 $table .= ',';
91496d15 316 }
317 /// Add the keys, separated by commas
318 if ($xmldb_keys = $xmldb_table->getKeys()) {
319 foreach ($xmldb_keys as $xmldb_key) {
320 if ($keytext = $this->getKeySQL($xmldb_table, $xmldb_key)) {
321 $table .= "\nCONSTRAINT " . $keytext . ',';
322 }
323 /// If the key is XMLDB_KEY_FOREIGN_UNIQUE, create it as UNIQUE too
324 if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE) {
325 ///Duplicate the key
326 $xmldb_key->setType(XMLDB_KEY_UNIQUE);
327 if ($keytext = $this->getKeySQL($xmldb_table, $xmldb_key)) {
328 $table .= "\nCONSTRAINT " . $keytext . ',';
329 }
330 }
41a50aa2
PS
331 /// make sure sequence field is unique
332 if ($sequencefield and $xmldb_key->getType() == XMLDB_KEY_PRIMARY) {
333 $field = reset($xmldb_key->getFields());
334 if ($sequencefield === $field) {
335 $sequencefield = null;
336 }
337 }
91496d15 338 }
339 }
41a50aa2
PS
340 /// throw error if sequence field does not have unique key defined
341 if ($sequencefield) {
342 throw new ddl_exception('ddsequenceerror', $xmldb_table->getName());
343 }
344
91496d15 345 /// Table footer, trim the latest comma
346 $table = trim($table,',');
347 $table .= "\n)";
348
9dcc6300 349 /// Add the CREATE TABLE to results
350 $results[] = $table;
351
2ebd5967 352 /// Add comments if specified and it exists
353 if ($this->add_table_comments && $xmldb_table->getComment()) {
f33e1ed4 354 $comment = $this->getCommentSQL($xmldb_table);
9dcc6300 355 /// Add the COMMENT to results
356 $results = array_merge($results, $comment);
91496d15 357 }
9dcc6300 358
91496d15 359 /// Add the indexes (each one, one statement)
91496d15 360 if ($xmldb_indexes = $xmldb_table->getIndexes()) {
361 foreach ($xmldb_indexes as $xmldb_index) {
eb099f32 362 ///tables do not exist yet, which means indexed can not exist yet
363 if ($indextext = $this->getCreateIndexSQL($xmldb_table, $xmldb_index)) {
364 $results = array_merge($results, $indextext);
91496d15 365 }
91496d15 366 }
367 }
9dcc6300 368
91496d15 369 /// Also, add the indexes needed from keys, based on configuration (each one, one statement)
370 if ($xmldb_keys = $xmldb_table->getKeys()) {
371 foreach ($xmldb_keys as $xmldb_key) {
f33e1ed4 372 /// If we aren't creating the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated
4690f076 373 /// automatically by the RDBMS) create the underlying (created by us) index (if doesn't exists)
374 if (!$this->getKeySQL($xmldb_table, $xmldb_key) || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) {
f33e1ed4 375 /// Create the interim index
a8cb94f6 376 $index = new xmldb_index('anyname');
4690f076 377 $index->setFields($xmldb_key->getFields());
eb099f32 378 ///tables do not exist yet, which means indexed can not exist yet
379 $createindex = false; //By default
380 switch ($xmldb_key->getType()) {
381 case XMLDB_KEY_UNIQUE:
382 case XMLDB_KEY_FOREIGN_UNIQUE:
383 $index->setUnique(true);
384 $createindex = true;
385 break;
386 case XMLDB_KEY_FOREIGN:
387 $index->setUnique(false);
388 $createindex = true;
389 break;
390 }
391 if ($createindex) {
392 if ($indextext = $this->getCreateIndexSQL($xmldb_table, $index)) {
393 /// Add the INDEX to the array
394 $results = array_merge($results, $indextext);
91496d15 395 }
396 }
91496d15 397 }
398 }
399 }
400
401 /// Add sequence extra code if needed
402 if ($this->sequence_extra_code) {
403 /// Iterate over fields looking for sequences
404 foreach ($xmldb_fields as $xmldb_field) {
405 if ($xmldb_field->getSequence()) {
9dcc6300 406 /// returns an array of statements needed to create one sequence
407 $sequence_sentences = $this->getCreateSequenceSQL($xmldb_table, $xmldb_field);
408 /// Add the SEQUENCE to the array
409 $results = array_merge($results, $sequence_sentences);
91496d15 410 }
411 }
412 }
413
9dcc6300 414 return $results;
91496d15 415 }
416
417 /**
a8cb94f6 418 * Given one correct xmldb_index, returns the SQL statements
83a4ee1e
AB
419 * needed to create it (in array).
420 *
421 * @param xmldb_table $xmldb_table The xmldb_table instance to create the index on.
422 * @param xmldb_index $xmldb_index The xmldb_index to create.
423 * @return array An array of SQL statements to create the index.
424 * @throws coding_exception Thrown if the xmldb_index does not validate with the xmldb_table.
91496d15 425 */
f33e1ed4 426 public function getCreateIndexSQL($xmldb_table, $xmldb_index) {
b2cfdcf6
PS
427 if ($error = $xmldb_index->validateDefinition($xmldb_table)) {
428 throw new coding_exception($error);
429 }
91496d15 430
431 $unique = '';
432 $suffix = 'ix';
433 if ($xmldb_index->getUnique()) {
434 $unique = ' UNIQUE';
435 $suffix = 'uix';
436 }
437
438 $index = 'CREATE' . $unique . ' INDEX ';
439 $index .= $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_index->getFields()), $suffix);
97b91784 440 $index .= ' ON ' . $this->getTableName($xmldb_table);
9dcc6300 441 $index .= ' (' . implode(', ', $this->getEncQuoted($xmldb_index->getFields())) . ')';
91496d15 442
9dcc6300 443 return array($index);
91496d15 444 }
445
446 /**
83a4ee1e
AB
447 * Given one correct xmldb_field, returns the complete SQL line to create it.
448 *
449 * @param xmldb_table $xmldb_table The table related to $xmldb_field.
450 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
451 * @param string $skip_type_clause The type clause on alter columns, NULL by default.
452 * @param string $skip_default_clause The default clause on alter columns, NULL by default.
453 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
454 * @param string $specify_nulls_clause To force a specific null clause, NULL by default.
455 * @param bool $specify_field_name Flag to specify fieldname in return.
456 * @return string The field generating SQL statement.
457 * @throws coding_exception Thrown when xmldb_field doesn't validate with the xmldb_table.
91496d15 458 */
257ad88f
PS
459 public function getFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL, $specify_nulls_clause = NULL, $specify_field_name = true) {
460 if ($error = $xmldb_field->validateDefinition($xmldb_table)) {
461 throw new coding_exception($error);
462 }
ed55f668 463
464 $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause;
465 $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause;
466 $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause;
467 $specify_nulls_clause = is_null($specify_nulls_clause) ? $this->specify_nulls : $specify_nulls_clause;
91496d15 468
469 /// First of all, convert integers to numbers if defined
470 if ($this->integer_to_number) {
471 if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER) {
472 $xmldb_field->setType(XMLDB_TYPE_NUMBER);
473 }
474 }
475 /// Same for floats
476 if ($this->float_to_number) {
477 if ($xmldb_field->getType() == XMLDB_TYPE_FLOAT) {
478 $xmldb_field->setType(XMLDB_TYPE_NUMBER);
479 }
480 }
481
4411f0d5 482 $field = ''; // Let's accumulate the whole expression based on params and settings
91496d15 483 /// The name
4411f0d5 484 if ($specify_field_name) {
485 $field .= $this->getEncQuoted($xmldb_field->getName());
486 }
19c8321e 487 /// The type and length only if we don't want to skip it
488 if (!$skip_type_clause) {
2a88f626 489 /// The type and length
490 $field .= ' ' . $this->getTypeSQL($xmldb_field->getType(), $xmldb_field->getLength(), $xmldb_field->getDecimals());
91496d15 491 }
492 /// The unsigned if supported
493 if ($this->unsigned_allowed && ($xmldb_field->getType() == XMLDB_TYPE_INTEGER ||
494 $xmldb_field->getType() == XMLDB_TYPE_NUMBER ||
495 $xmldb_field->getType() == XMLDB_TYPE_FLOAT)) {
496 if ($xmldb_field->getUnsigned()) {
497 $field .= ' unsigned';
498 }
499 }
f075bac4 500 /// Calculate the not null clause
848caec8 501 $notnull = '';
19c8321e 502 /// Only if we don't want to skip it
503 if (!$skip_notnull_clause) {
504 if ($xmldb_field->getNotNull()) {
505 $notnull = ' NOT NULL';
506 } else {
ed55f668 507 if ($specify_nulls_clause) {
19c8321e 508 $notnull = ' NULL';
509 }
7f5f956c 510 }
f075bac4 511 }
512 /// Calculate the default clause
f33e1ed4 513 $default_clause = '';
0e9e0b3b 514 if (!$skip_default_clause) { //Only if we don't want to skip it
f33e1ed4 515 $default_clause = $this->getDefaultClause($xmldb_field);
0e9e0b3b 516 }
f075bac4 517 /// Based on default_after_null, set both clauses properly
518 if ($this->default_after_null) {
f33e1ed4 519 $field .= $notnull . $default_clause;
f075bac4 520 } else {
f33e1ed4 521 $field .= $default_clause . $notnull;
91496d15 522 }
523 /// The sequence
524 if ($xmldb_field->getSequence()) {
8101dccb 525 if($xmldb_field->getLength()<=9 && $this->sequence_name_small) {
526 $sequencename=$this->sequence_name_small;
527 } else {
528 $sequencename=$this->sequence_name;
529 }
530 $field .= ' ' . $sequencename;
91496d15 531 if ($this->sequence_only) {
532 /// We only want the field name and sequence name to be printed
533 /// so, calculate it and return
f33e1ed4 534 $sql = $this->getEncQuoted($xmldb_field->getName()) . ' ' . $sequencename;
535 return $sql;
91496d15 536 }
537 }
91496d15 538 return $field;
539 }
540
541 /**
83a4ee1e
AB
542 * Given one correct xmldb_key, returns its specs.
543 *
544 * @param xmldb_table $xmldb_table The table related to $xmldb_key.
545 * @param xmldb_key $xmldb_key The xmldb_key's specifications requested.
546 * @return string SQL statement about the xmldb_key.
91496d15 547 */
f33e1ed4 548 public function getKeySQL($xmldb_table, $xmldb_key) {
91496d15 549
550 $key = '';
551
552 switch ($xmldb_key->getType()) {
553 case XMLDB_KEY_PRIMARY:
554 if ($this->primary_keys) {
555 if ($this->primary_key_name !== null) {
c4122781 556 $key = $this->getEncQuoted($this->primary_key_name);
91496d15 557 } else {
558 $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'pk');
559 }
560 $key .= ' PRIMARY KEY (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
561 }
562 break;
563 case XMLDB_KEY_UNIQUE:
564 if ($this->unique_keys) {
565 $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'uk');
6c11ad54 566 $key .= ' UNIQUE (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
91496d15 567 }
568 break;
569 case XMLDB_KEY_FOREIGN:
570 case XMLDB_KEY_FOREIGN_UNIQUE:
571 if ($this->foreign_keys) {
572 $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'fk');
573 $key .= ' FOREIGN KEY (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')';
f075bac4 574 $key .= ' REFERENCES ' . $this->getEncQuoted($this->prefix . $xmldb_key->getRefTable());
91496d15 575 $key .= ' (' . implode(', ', $this->getEncQuoted($xmldb_key->getRefFields())) . ')';
576 }
577 break;
578 }
579
580 return $key;
581 }
582
f075bac4 583 /**
a8cb94f6 584 * Give one xmldb_field, returns the correct "default value" for the current configuration
83a4ee1e
AB
585 *
586 * @param xmldb_field $xmldb_field The field.
587 * @return The default value of the field.
f075bac4 588 */
f33e1ed4 589 public function getDefaultValue($xmldb_field) {
f075bac4 590
2cfea485 591 $default = null;
f075bac4 592
04c9f8dc 593 if ($xmldb_field->getDefault() !== NULL) {
f075bac4 594 if ($xmldb_field->getType() == XMLDB_TYPE_CHAR ||
595 $xmldb_field->getType() == XMLDB_TYPE_TEXT) {
692ae912 596 if ($xmldb_field->getDefault() === '') { // If passing empty default, use the $default_for_char one instead
597 $default = "'" . $this->default_for_char . "'";
598 } else {
599 $default = "'" . $this->addslashes($xmldb_field->getDefault()) . "'";
600 }
f075bac4 601 } else {
2cfea485 602 $default = $xmldb_field->getDefault();
f075bac4 603 }
604 } else {
605 /// We force default '' for not null char columns without proper default
606 /// some day this should be out!
eef868d1 607 if ($this->default_for_char !== NULL &&
f075bac4 608 $xmldb_field->getType() == XMLDB_TYPE_CHAR &&
609 $xmldb_field->getNotNull()) {
2cfea485 610 $default = "'" . $this->default_for_char . "'";
812e363a 611 } else {
612 /// If the DB requires to explicity define some clause to drop one default, do it here
613 /// never applying defaults to TEXT and BINARY fields
f33e1ed4 614 if ($this->drop_default_value_required &&
812e363a 615 $xmldb_field->getType() != XMLDB_TYPE_TEXT &&
0db2d6bb 616 $xmldb_field->getType() != XMLDB_TYPE_BINARY && !$xmldb_field->getNotNull()) {
f33e1ed4 617 $default = $this->drop_default_value;
812e363a 618 }
f075bac4 619 }
620 }
621 return $default;
622 }
623
2cfea485 624 /**
83a4ee1e
AB
625 * Given one xmldb_field, returns the correct "default clause" for the current configuration.
626 *
627 * @param xmldb_field $xmldb_field The xmldb_field.
628 * @return The SQL clause for generating the default value as in $xmldb_field.
2cfea485 629 */
f33e1ed4 630 public function getDefaultClause($xmldb_field) {
2cfea485 631
632 $defaultvalue = $this->getDefaultValue ($xmldb_field);
633
634 if ($defaultvalue !== null) {
635 return ' DEFAULT ' . $defaultvalue;
636 } else {
637 return null;
638 }
639 }
640
d05babe9 641 /**
a8cb94f6 642 * Given one correct xmldb_table and the new name, returns the SQL statements
83a4ee1e
AB
643 * to rename it (inside one array).
644 *
645 * @param xmldb_table $xmldb_table The table to rename.
646 * @param string $newname The new name to rename the table to.
647 * @return array SQL statement(s) to rename the table.
eef868d1 648 */
f33e1ed4 649 public function getRenameTableSQL($xmldb_table, $newname) {
d05babe9 650
651 $results = array(); //Array where all the sentences will be stored
652
a8cb94f6 653 $newt = new xmldb_table($newname); //Temporal table for name calculations
97b91784 654
97b91784 655 $rename = str_replace('OLDNAME', $this->getTableName($xmldb_table), $this->rename_table_sql);
656 $rename = str_replace('NEWNAME', $this->getTableName($newt), $rename);
d05babe9 657
658 $results[] = $rename;
659
f33e1ed4 660 /// Call to getRenameTableExtraSQL() override if needed
661 $extra_sentences = $this->getRenameTableExtraSQL($xmldb_table, $newname);
662 $results = array_merge($results, $extra_sentences);
8830218f 663
664 return $results;
665 }
666
667 /**
a8cb94f6 668 * Given one correct xmldb_table and the new name, returns the SQL statements
83a4ee1e
AB
669 * to drop it (inside one array).
670 *
671 * @param xmldb_table $xmldb_table The table to drop.
672 * @return array SQL statement(s) for dropping the specified table.
eef868d1 673 */
f33e1ed4 674 public function getDropTableSQL($xmldb_table) {
8830218f 675
676 $results = array(); //Array where all the sentences will be stored
677
97b91784 678 $drop = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->drop_table_sql);
8830218f 679
3a8c55c3 680 $results[] = $drop;
8830218f 681
f33e1ed4 682 /// call to getDropTableExtraSQL(), override if needed
683 $extra_sentences = $this->getDropTableExtraSQL($xmldb_table);
684 $results = array_merge($results, $extra_sentences);
d05babe9 685
686 return $results;
687 }
688
3ab26100 689 /**
83a4ee1e
AB
690 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add the field to the table.
691 *
692 * @param xmldb_table $xmldb_table The table related to $xmldb_field.
693 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
694 * @param string $skip_type_clause The type clause on alter columns, NULL by default.
695 * @param string $skip_default_clause The default clause on alter columns, NULL by default.
696 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
697 * @return array The SQL statement for adding a field to the table.
3ab26100 698 */
ed55f668 699 public function getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) {
700
701 $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause;
702 $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause;
703 $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause;
3ab26100 704
705 $results = array();
706
707 /// Get the quoted name of the table and field
97b91784 708 $tablename = $this->getTableName($xmldb_table);
3ab26100 709
710 /// Build the standard alter table add
257ad88f 711 $sql = $this->getFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause,
ed55f668 712 $skip_default_clause,
713 $skip_notnull_clause);
f33e1ed4 714 $altertable = 'ALTER TABLE ' . $tablename . ' ADD ' . $sql;
f0b50bbe 715 /// Add the after clause if necesary
716 if ($this->add_after_clause && $xmldb_field->getPrevious()) {
f33e1ed4 717 $altertable .= ' AFTER ' . $this->getEncQuoted($xmldb_field->getPrevious());
f0b50bbe 718 }
719 $results[] = $altertable;
3ab26100 720
3ab26100 721 return $results;
722 }
723
724 /**
83a4ee1e
AB
725 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop the field from the table.
726 *
727 * @param xmldb_table $xmldb_table The table related to $xmldb_field.
728 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
729 * @return array The SQL statement for dropping a field from the table.
3ab26100 730 */
f33e1ed4 731 public function getDropFieldSQL($xmldb_table, $xmldb_field) {
3ab26100 732
733 $results = array();
734
735 /// Get the quoted name of the table and field
97b91784 736 $tablename = $this->getTableName($xmldb_table);
3ab26100 737 $fieldname = $this->getEncQuoted($xmldb_field->getName());
738
739 /// Build the standard alter table drop
740 $results[] = 'ALTER TABLE ' . $tablename . ' DROP COLUMN ' . $fieldname;
741
742 return $results;
743 }
744
0e9e0b3b 745 /**
83a4ee1e
AB
746 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table.
747 *
748 * @param xmldb_table $xmldb_table The table related to $xmldb_field.
749 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
750 * @param string $skip_type_clause The type clause on alter columns, NULL by default.
751 * @param string $skip_default_clause The default clause on alter columns, NULL by default.
752 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
753 * @return string The field altering SQL statement.
0e9e0b3b 754 */
ed55f668 755 public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) {
0e9e0b3b 756
ed55f668 757 $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause;
758 $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause;
759 $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause;
0e9e0b3b 760
ed55f668 761 $results = array();
19c8321e 762
0e9e0b3b 763 /// Get the quoted name of the table and field
97b91784 764 $tablename = $this->getTableName($xmldb_table);
0e9e0b3b 765 $fieldname = $this->getEncQuoted($xmldb_field->getName());
766
767 /// Build de alter sentence using the alter_column_sql template
512a237f 768 $alter = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->alter_column_sql);
257ad88f 769 $colspec = $this->getFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause,
ed55f668 770 $skip_default_clause,
771 $skip_notnull_clause,
772 true);
f33e1ed4 773 $alter = str_replace('COLUMNSPECS', $colspec, $alter);
0e9e0b3b 774
0e9e0b3b 775 /// Add the after clause if necesary
776 if ($this->add_after_clause && $xmldb_field->getPrevious()) {
b899d9bf 777 $alter .= ' after ' . $this->getEncQuoted($xmldb_field->getPrevious());
778 }
779
f6ebc341 780 /// Build the standard alter table modify
781 $results[] = $alter;
782
b899d9bf 783 return $results;
784 }
785
0e9e0b3b 786 /**
83a4ee1e
AB
787 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to modify the default of the field in the table.
788 *
789 * @param xmldb_table $xmldb_table The table related to $xmldb_field.
790 * @param xmldb_field $xmldb_field The instance of xmldb_field to get the modified default value from.
791 * @return array The SQL statement for modifying the default value.
0e9e0b3b 792 */
f33e1ed4 793 public function getModifyDefaultSQL($xmldb_table, $xmldb_field) {
0e9e0b3b 794
795 $results = array();
796
797 /// Get the quoted name of the table and field
97b91784 798 $tablename = $this->getTableName($xmldb_table);
0e9e0b3b 799 $fieldname = $this->getEncQuoted($xmldb_field->getName());
800
801 /// Decide if we are going to create/modify or to drop the default
802 if ($xmldb_field->getDefault() === null) {
6e152cc6 803 $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); //Drop
0e9e0b3b 804 } else {
6e152cc6 805 $results = $this->getCreateDefaultSQL($xmldb_table, $xmldb_field); //Create/modify
0e9e0b3b 806 }
6e152cc6 807
808 return $results;
0e9e0b3b 809 }
810
7c07a932 811 /**
a8cb94f6 812 * Given one correct xmldb_field and the new name, returns the SQL statements
83a4ee1e
AB
813 * to rename it (inside one array).
814 *
815 * @param xmldb_table $xmldb_table The table related to $xmldb_field.
816 * @param xmldb_field $xmldb_field The instance of xmldb_field to get the renamed field from.
817 * @param string $newname The new name to rename the field to.
818 * @return array The SQL statements for renaming the field.
7c07a932 819 */
f33e1ed4 820 public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) {
7c07a932 821
822 $results = array(); //Array where all the sentences will be stored
823
b1f93b15 824 /// Although this is checked in database_manager::rename_field() - double check
f14080b0 825 /// that we aren't trying to rename one "id" field. Although it could be
826 /// implemented (if adding the necessary code to rename sequences, defaults,
827 /// triggers... and so on under each getRenameFieldExtraSQL() function, it's
a3790ed5 828 /// better to forbid it, mainly because this field is the default PK and
f14080b0 829 /// in the future, a lot of FKs can be pointing here. So, this field, more
a3790ed5 830 /// or less, must be considered immutable!
f14080b0 831 if ($xmldb_field->getName() == 'id') {
832 return array();
833 }
834
7c07a932 835 $rename = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_column_sql);
fec5fe88 836 $rename = str_replace('OLDFIELDNAME', $this->getEncQuoted($xmldb_field->getName()), $rename);
2a82cacf 837 $rename = str_replace('NEWFIELDNAME', $this->getEncQuoted($newname), $rename);
7c07a932 838
839 $results[] = $rename;
840
f33e1ed4 841 /// Call to getRenameFieldExtraSQL(), override if needed
842 $extra_sentences = $this->getRenameFieldExtraSQL($xmldb_table, $xmldb_field, $newname);
843 $results = array_merge($results, $extra_sentences);
7c07a932 844
845 return $results;
846 }
847
0852990f 848 /**
a3790ed5 849 * Given one xmldb_table and one xmldb_key, return the SQL statements needed to add the key to the table
83a4ee1e
AB
850 * note that undelying indexes will be added as parametrised by $xxxx_keys and $xxxx_index parameters.
851 *
852 * @param xmldb_table $xmldb_table The table related to $xmldb_key.
853 * @param xmldb_key $xmldb_key The xmldb_key to add.
854 * @return array SQL statement to add the xmldb_key.
0852990f 855 */
f33e1ed4 856 public function getAddKeySQL($xmldb_table, $xmldb_key) {
0852990f 857
858 $results = array();
859
860 /// Just use the CreateKeySQL function
861 if ($keyclause = $this->getKeySQL($xmldb_table, $xmldb_key)) {
97b91784 862 $key = 'ALTER TABLE ' . $this->getTableName($xmldb_table) .
0852990f 863 ' ADD CONSTRAINT ' . $keyclause;
864 $results[] = $key;
865 }
4690f076 866
f33e1ed4 867 /// If we aren't creating the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated
4690f076 868 /// automatically by the RDBMS) create the underlying (created by us) index (if doesn't exists)
869 if (!$keyclause || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) {
870 /// Only if they don't exist
871 if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN) { ///Calculate type of index based on type ok key
ac5ff0e7 872 $indextype = XMLDB_INDEX_NOTUNIQUE;
4690f076 873 } else {
874 $indextype = XMLDB_INDEX_UNIQUE;
875 }
69b80cc2 876 $xmldb_index = new xmldb_index('anyname', $indextype, $xmldb_key->getFields());
f33e1ed4 877 if (!$this->mdb->get_manager()->index_exists($xmldb_table, $xmldb_index)) {
4690f076 878 $results = array_merge($results, $this->getAddIndexSQL($xmldb_table, $xmldb_index));
879 }
880 }
881
0852990f 882 /// If the key is XMLDB_KEY_FOREIGN_UNIQUE, create it as UNIQUE too
883 if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && $this->unique_keys) {
884 ///Duplicate the key
885 $xmldb_key->setType(XMLDB_KEY_UNIQUE);
886 $results = array_merge($results, $this->getAddKeySQL($xmldb_table, $xmldb_key));
887 }
f33e1ed4 888
0852990f 889 /// Return results
890 return $results;
891 }
892
893 /**
83a4ee1e
AB
894 * Given one xmldb_table and one xmldb_index, return the SQL statements needed to drop the index from the table.
895 *
896 * @param xmldb_table $xmldb_table The table related to $xmldb_key.
897 * @param xmldb_key $xmldb_key The xmldb_key to drop.
898 * @return array SQL statement to drop the xmldb_key.
0852990f 899 */
f33e1ed4 900 public function getDropKeySQL($xmldb_table, $xmldb_key) {
0852990f 901
902 $results = array();
903
904 /// Get the key name (note that this doesn't introspect DB, so could cause some problems sometimes!)
905 /// TODO: We'll need to overwrite the whole getDropKeySQL() method inside each DB to do the proper queries
906 /// against the dictionary or require ADOdb to support it or change the find_key_name() method to
907 /// perform DB introspection directly. But, for now, as we aren't going to enable referential integrity
908 /// it won't be a problem at all
f33e1ed4 909 $dbkeyname = $this->mdb->get_manager()->find_key_name($xmldb_table, $xmldb_key);
0852990f 910
911 /// Only if such type of key generation is enabled
912 $dropkey = false;
913 switch ($xmldb_key->getType()) {
914 case XMLDB_KEY_PRIMARY:
915 if ($this->primary_keys) {
916 $template = $this->drop_primary_key;
0852990f 917 $dropkey = true;
918 }
919 break;
920 case XMLDB_KEY_UNIQUE:
921 if ($this->unique_keys) {
922 $template = $this->drop_unique_key;
0852990f 923 $dropkey = true;
924 }
925 break;
926 case XMLDB_KEY_FOREIGN_UNIQUE:
927 case XMLDB_KEY_FOREIGN:
928 if ($this->foreign_keys) {
929 $template = $this->drop_foreign_key;
0852990f 930 $dropkey = true;
931 }
932 break;
933 }
934 /// If we have decided to drop the key, let's do it
935 if ($dropkey) {
936 /// Replace TABLENAME, CONSTRAINTTYPE and KEYNAME as needed
97b91784 937 $dropsql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $template);
0852990f 938 $dropsql = str_replace('KEYNAME', $dbkeyname, $dropsql);
939
940 $results[] = $dropsql;
941 }
942
f33e1ed4 943 /// If we aren't dropping the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated
4690f076 944 /// automatically by the RDBMS) drop the underlying (created by us) index (if exists)
945 if (!$dropkey || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) {
946 /// Only if they exist
69b80cc2 947 $xmldb_index = new xmldb_index('anyname', XMLDB_INDEX_UNIQUE, $xmldb_key->getFields());
f33e1ed4 948 if ($this->mdb->get_manager()->index_exists($xmldb_table, $xmldb_index)) {
4690f076 949 $results = array_merge($results, $this->getDropIndexSQL($xmldb_table, $xmldb_index));
950 }
951 }
952
0852990f 953 /// If the key is XMLDB_KEY_FOREIGN_UNIQUE, drop the UNIQUE too
954 if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && $this->unique_keys) {
955 ///Duplicate the key
956 $xmldb_key->setType(XMLDB_KEY_UNIQUE);
957 $results = array_merge($results, $this->getDropKeySQL($xmldb_table, $xmldb_key));
958 }
f33e1ed4 959
0852990f 960 /// Return results
961 return $results;
962 }
963
e77fd021 964 /**
a3790ed5 965 * Given one xmldb_table and one xmldb_key, return the SQL statements needed to rename the key in the table
e77fd021 966 * Experimental! Shouldn't be used at all!
83a4ee1e
AB
967 *
968 * @param xmldb_table $xmldb_table The table related to $xmldb_key.
969 * @param xmldb_key $xmldb_key The xmldb_key to rename.
970 * @param string $newname The xmldb_key's new name.
971 * @return array SQL statement to rename the xmldb_key.
e77fd021 972 */
f33e1ed4 973 public function getRenameKeySQL($xmldb_table, $xmldb_key, $newname) {
e77fd021 974
975 $results = array();
976
977 /// Get the real key name
f33e1ed4 978 $dbkeyname = $this->mdb->get_manager()->find_key_name($xmldb_table, $xmldb_key);
e77fd021 979
980 /// Check we are really generating this type of keys
981 if (($xmldb_key->getType() == XMLDB_KEY_PRIMARY && !$this->primary_keys) ||
982 ($xmldb_key->getType() == XMLDB_KEY_UNIQUE && !$this->unique_keys) ||
983 ($xmldb_key->getType() == XMLDB_KEY_FOREIGN && !$this->foreign_keys) ||
984 ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && !$this->unique_keys && !$this->foreign_keys)) {
985 /// We aren't generating this type of keys, delegate to child indexes
a8cb94f6 986 $xmldb_index = new xmldb_index($xmldb_key->getName());
e77fd021 987 $xmldb_index->setFields($xmldb_key->getFields());
4702d62e 988 return $this->getRenameIndexSQL($xmldb_table, $xmldb_index, $newname);
e77fd021 989 }
990
991 /// Arrived here so we are working with keys, lets rename them
992 /// Replace TABLENAME and KEYNAME as needed
993 $renamesql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_key_sql);
994 $renamesql = str_replace('OLDKEYNAME', $dbkeyname, $renamesql);
4702d62e 995 $renamesql = str_replace('NEWKEYNAME', $newname, $renamesql);
e77fd021 996
997 /// Some DB doesn't support key renaming so this can be empty
998 if ($renamesql) {
999 $results[] = $renamesql;
1000 }
1001
1002 return $results;
1003 }
1004
618a982e 1005 /**
83a4ee1e
AB
1006 * Given one xmldb_table and one xmldb_index, return the SQL statements needed to add the index to the table.
1007 *
1008 * @param xmldb_table $xmldb_table The xmldb_table instance to add the index on.
1009 * @param xmldb_index $xmldb_index The xmldb_index to add.
1010 * @return array An array of SQL statements to add the index.
618a982e 1011 */
f33e1ed4 1012 public function getAddIndexSQL($xmldb_table, $xmldb_index) {
618a982e 1013
1014 /// Just use the CreateIndexSQL function
1015 return $this->getCreateIndexSQL($xmldb_table, $xmldb_index);
1016 }
1017
1018 /**
83a4ee1e
AB
1019 * Given one xmldb_table and one xmldb_index, return the SQL statements needed to drop the index from the table.
1020 *
1021 * @param xmldb_table $xmldb_table The xmldb_table instance to drop the index on.
1022 * @param xmldb_index $xmldb_index The xmldb_index to drop.
1023 * @return array An array of SQL statements to drop the index.
618a982e 1024 */
f33e1ed4 1025 public function getDropIndexSQL($xmldb_table, $xmldb_index) {
618a982e 1026
1027 $results = array();
1028
1029 /// Get the real index name
f33e1ed4 1030 $dbindexname = $this->mdb->get_manager()->find_index_name($xmldb_table, $xmldb_index);
618a982e 1031
1032 /// Replace TABLENAME and INDEXNAME as needed
97b91784 1033 $dropsql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->drop_index_sql);
67a2f1c1 1034 $dropsql = str_replace('INDEXNAME', $this->getEncQuoted($dbindexname), $dropsql);
618a982e 1035
1036 $results[] = $dropsql;
1037
1038 return $results;
1039 }
1040
1c86ef5b 1041 /**
a3790ed5 1042 * Given one xmldb_table and one xmldb_index, return the SQL statements needed to rename the index in the table
e77fd021 1043 * Experimental! Shouldn't be used at all!
83a4ee1e
AB
1044 *
1045 * @param xmldb_table $xmldb_table The xmldb_table instance to rename the index on.
1046 * @param xmldb_index $xmldb_index The xmldb_index to rename.
1047 * @param string $newname The xmldb_index's new name.
1048 * @return array An array of SQL statements to rename the index.
1c86ef5b 1049 */
4702d62e 1050 function getRenameIndexSQL($xmldb_table, $xmldb_index, $newname) {
f33e1ed4 1051 /// Some DB doesn't support index renaming (MySQL) so this can be empty
1052 if (empty($this->rename_index_sql)) {
1053 return array();
1054 }
1c86ef5b 1055
1056 /// Get the real index name
f33e1ed4 1057 $dbindexname = $this->mdb->get_manager()->find_index_name($xmldb_table, $xmldb_index);
1c86ef5b 1058 /// Replace TABLENAME and INDEXNAME as needed
1059 $renamesql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_index_sql);
67a2f1c1
EL
1060 $renamesql = str_replace('OLDINDEXNAME', $this->getEncQuoted($dbindexname), $renamesql);
1061 $renamesql = str_replace('NEWINDEXNAME', $this->getEncQuoted($newname), $renamesql);
1c86ef5b 1062
f33e1ed4 1063 return array($renamesql);
1c86ef5b 1064 }
812e363a 1065
91496d15 1066 /**
da40b0be 1067 * Given three strings (table name, list of fields (comma separated) and suffix),
1068 * create the proper object name quoting it if necessary.
1069 *
1070 * IMPORTANT: This function must be used to CALCULATE NAMES of objects TO BE CREATED,
1071 * NEVER TO GUESS NAMES of EXISTING objects!!!
83a4ee1e
AB
1072 *
1073 * @param string $tablename The table name.
1074 * @param string $fields A list of comma separated fields.
1075 * @param string $suffix A suffix for the object name.
1076 * @return string Object's name.
91496d15 1077 */
f33e1ed4 1078 public function getNameForObject($tablename, $fields, $suffix='') {
da40b0be 1079
91496d15 1080 $name = '';
1081
38af117a
EL
1082 /// Implement one basic cache to avoid object name duplication
1083 /// along all the request life, but never to return cached results
90d07fa4 1084 /// We need this because sql statements are created before executing
a1656ddf 1085 /// them, hence names doesn't exist "physically" yet in DB, so we need
90d07fa4 1086 /// to known which ones have been used
524ad881 1087 static $used_names = array();
38af117a 1088
91496d15 1089 /// Use standard naming. See http://docs.moodle.org/en/XMLDB_key_and_index_naming
1090 $tablearr = explode ('_', $tablename);
1091 foreach ($tablearr as $table) {
93d2c55f 1092 $name .= substr(trim($table),0,4);
91496d15 1093 }
1094 $name .= '_';
1095 $fieldsarr = explode (',', $fields);
1096 foreach ($fieldsarr as $field) {
1097 $name .= substr(trim($field),0,3);
1098 }
716332e6 1099 /// Prepend the prefix
1100 $name = $this->prefix . $name;
1101
91496d15 1102 $name = substr(trim($name), 0, $this->names_max_length - 1 - strlen($suffix)); //Max names_max_length
1103
1104 /// Add the suffix
0e204a10 1105 $namewithsuffix = $name;
1106 if ($suffix) {
1107 $namewithsuffix = $namewithsuffix . '_' . $suffix;
1108 }
91496d15 1109
f2daac4e 1110 /// If the calculated name is in the cache, or if we detect it by introspecting the DB let's modify if
38af117a 1111 if (in_array($namewithsuffix, $used_names) || $this->isNameInUse($namewithsuffix, $suffix, $tablename)) {
91496d15 1112 $counter = 2;
1113 /// If have free space, we add 2
716332e6 1114 if (strlen($namewithsuffix) < $this->names_max_length) {
91496d15 1115 $newname = $name . $counter;
1116 /// Else replace the last char by 2
1117 } else {
1118 $newname = substr($name, 0, strlen($name)-1) . $counter;
1119 }
0e204a10 1120 $newnamewithsuffix = $newname;
1121 if ($suffix) {
1122 $newnamewithsuffix = $newnamewithsuffix . '_' . $suffix;
1123 }
91496d15 1124 /// Now iterate until not used name is found, incrementing the counter
38af117a 1125 while (in_array($newnamewithsuffix, $used_names) || $this->isNameInUse($newnamewithsuffix, $suffix, $tablename)) {
d21a42d7 1126 $counter++;
91496d15 1127 $newname = substr($name, 0, strlen($newname)-1) . $counter;
0e204a10 1128 $newnamewithsuffix = $newname;
1129 if ($suffix) {
1130 $newnamewithsuffix = $newnamewithsuffix . '_' . $suffix;
1131 }
91496d15 1132 }
1133 $namewithsuffix = $newnamewithsuffix;
1134 }
1135
38af117a 1136 /// Add the name to the cache
90d07fa4 1137 $used_names[] = $namewithsuffix;
38af117a 1138
c4122781 1139 /// Quote it if necessary (reserved words)
1140 $namewithsuffix = $this->getEncQuoted($namewithsuffix);
1141
91496d15 1142 return $namewithsuffix;
1143 }
1144
1145 /**
1146 * Given any string (or one array), enclose it by the proper quotes
716332e6 1147 * if it's a reserved word
83a4ee1e
AB
1148 *
1149 * @param string|array $input String to quote.
1150 * @return Quoted string.
91496d15 1151 */
f33e1ed4 1152 public function getEncQuoted($input) {
91496d15 1153
1154 if (is_array($input)) {
1155 foreach ($input as $key=>$content) {
1156 $input[$key] = $this->getEncQuoted($content);
1157 }
1158 return $input;
1159 } else {
1160 /// Always lowercase
1161 $input = strtolower($input);
67a2f1c1
EL
1162 /// if reserved or quote_all or has hyphens, quote it
1163 if ($this->quote_all || in_array($input, $this->reserved_words) || strpos($input, '-') !== false) {
91496d15 1164 $input = $this->quote_string . $input . $this->quote_string;
1165 }
1166 return $input;
1167 }
1168 }
1169
716332e6 1170 /**
83a4ee1e
AB
1171 * Given one XMLDB Statement, build the needed SQL insert sentences to execute it.
1172 *
1173 * @param string $statement SQL statement.
1174 * @return array Array of sentences in the SQL statement.
716332e6 1175 */
1176 function getExecuteInsertSQL($statement) {
1177
1178 $results = array(); //Array where all the sentences will be stored
1179
1180 if ($sentences = $statement->getSentences()) {
1181 foreach ($sentences as $sentence) {
1182 /// Get the list of fields
1183 $fields = $statement->getFieldsFromInsertSentence($sentence);
1184 /// Get the values of fields
1185 $values = $statement->getValuesFromInsertSentence($sentence);
a3790ed5 1186 /// Look if we have some CONCAT value and transform it dynamically
716332e6 1187 foreach($values as $key => $value) {
1188 /// Trim single quotes
1189 $value = trim($value,"'");
eef868d1 1190 if (stristr($value, 'CONCAT') !== false){
a3790ed5 1191 /// Look for data between parenthesis
716332e6 1192 preg_match("/CONCAT\s*\((.*)\)$/is", trim($value), $matches);
1193 if (isset($matches[1])) {
1194 $part = $matches[1];
1195 /// Convert the comma separated string to an array
46293bd7 1196 $arr = xmldb_object::comma2array($part);
716332e6 1197 if ($arr) {
1198 $value = $this->getConcatSQL($arr);
1199 }
1200 }
1201 }
1202 /// Values to be sent to DB must be properly escaped
f33e1ed4 1203 $value = $this->addslashes($value);
716332e6 1204 /// Back trimmed quotes
1205 $value = "'" . $value . "'";
1206 /// Back to the array
1207 $values[$key] = $value;
1208 }
1209
1210 /// Iterate over fields, escaping them if necessary
1211 foreach($fields as $key => $field) {
1212 $fields[$key] = $this->getEncQuoted($field);
1213 }
1214 /// Build the final SQL sentence and add it to the array of results
1215 $sql = 'INSERT INTO ' . $this->getEncQuoted($this->prefix . $statement->getTable()) .
1216 '(' . implode(', ', $fields) . ') ' .
1217 'VALUES (' . implode(', ', $values) . ')';
1218 $results[] = $sql;
1219 }
1220
1221 }
1222 return $results;
1223 }
1224
1225 /**
83a4ee1e 1226 * Given one array of elements, build the proper CONCAT expression, based
716332e6 1227 * in the $concat_character setting. If such setting is empty, then
83a4ee1e
AB
1228 * MySQL's CONCAT function will be used instead.
1229 *
1230 * @param array $elements An array of elements to concatenate.
1231 * @return mixed Returns the result of moodle_database::sql_concat() or false.
1232 * @uses moodle_database::sql_concat()
1233 * @uses call_user_func_array()
716332e6 1234 */
f33e1ed4 1235 public function getConcatSQL($elements) {
716332e6 1236
1237 /// Replace double quoted elements by single quotes
1238 foreach($elements as $key => $element) {
1239 $element = trim($element);
1240 if (substr($element, 0, 1) == '"' &&
1241 substr($element, -1, 1) == '"') {
1242 $elements[$key] = "'" . trim($element, '"') . "'";
1243 }
1244 }
1245
245ac557 1246 /// Now call the standard $DB->sql_concat() DML function
f33e1ed4 1247 return call_user_func_array(array($this->mdb, 'sql_concat'), $elements);
9dcc6300 1248 }
1249
abbd460f 1250 /**
1251 * Returns the name (string) of the sequence used in the table for the autonumeric pk
83a4ee1e
AB
1252 * Only some DB have this implemented.
1253 *
1254 * @param xmldb_table $xmldb_table The xmldb_table instance.
1255 * @return bool Returns the sequence from the DB or false.
abbd460f 1256 */
f33e1ed4 1257 public function getSequenceFromDB($xmldb_table) {
abbd460f 1258 return false;
1259 }
1260
f2daac4e 1261 /**
83a4ee1e
AB
1262 * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg).
1263 *
a8cb94f6 1264 * (MySQL requires the whole xmldb_table object to be specified, so we add it always)
83a4ee1e
AB
1265 *
1266 * This is invoked from getNameForObject().
1267 * Only some DB have this implemented.
1268 *
1269 * @param string $object_name The object's name to check for.
1270 * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg).
1271 * @param string $table_name The table's name to check in
1272 * @return bool If such name is currently in use (true) or no (false)
f2daac4e 1273 */
f33e1ed4 1274 public function isNameInUse($object_name, $type, $table_name) {
a3790ed5 1275 return false; //For generators not implementing introspection,
f2daac4e 1276 //we always return with the name being free to be used
1277 }
1278
abbd460f 1279
91496d15 1280/// ALL THESE FUNCTION MUST BE CUSTOMISED BY ALL THE XMLDGenerator classes
1281
10f375aa
EL
1282 /**
1283 * Reset a sequence to the id field of a table.
83a4ee1e
AB
1284 *
1285 * @param string $tablename name of table.
10f375aa
EL
1286 * @return success
1287 */
1288 public abstract function getResetSequenceSQL($tablename);
1289
1290 /**
1291 * Given one correct xmldb_table, returns the SQL statements
83a4ee1e
AB
1292 * to create temporary table (inside one array).
1293 *
1294 * @param xmldb_table $xmldb_table The xmldb_table object instance.
1295 * @return array SQL statements.
10f375aa
EL
1296 */
1297 abstract public function getCreateTempTableSQL($xmldb_table);
1298
1299 /**
83a4ee1e
AB
1300 * Given one correct xmldb_table and the new name, returns the SQL statements.
1301 * to drop it (inside one array).
1302 *
1303 * @param xmldb_table $xmldb_table The xmldb_table object instance.
1304 * @return array SQL statements.
10f375aa
EL
1305 */
1306 abstract public function getDropTempTableSQL($xmldb_table);
1307
91496d15 1308 /**
83a4ee1e
AB
1309 * Given one XMLDB Type, length and decimals, returns the DB proper SQL type.
1310 *
1311 * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants.
1312 * @param int $xmldb_length The length of that data type.
1313 * @param int $xmldb_decimals The decimal places of precision of the data type.
1314 * @return string The DB defined data type.
91496d15 1315 */
f33e1ed4 1316 public abstract function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null);
91496d15 1317
7c07a932 1318 /**
83a4ee1e
AB
1319 * Returns the code (array of statements) needed to execute extra statements on field rename.
1320 *
1321 * @param xmldb_table $xmldb_table The xmldb_table object instance.
1322 * @param xmldb_field $xmldb_field The xmldb_field object instance.
1323 * @return array Array of extra SQL statements to run with a field being renamed.
7c07a932 1324 */
f33e1ed4 1325 public function getRenameFieldExtraSQL($xmldb_table, $xmldb_field) {
1326 return array();
7c07a932 1327 }
1328
91496d15 1329 /**
eef868d1 1330 * Returns the code (array of statements) needed
83a4ee1e
AB
1331 * to create one sequence for the xmldb_table and xmldb_field passed in.
1332 *
1333 * @param xmldb_table $xmldb_table The xmldb_table object instance.
1334 * @param xmldb_field $xmldb_field The xmldb_field object instance.
1335 * @return array Array of SQL statements to create the sequence.
91496d15 1336 */
f33e1ed4 1337 public function getCreateSequenceSQL($xmldb_table, $xmldb_field) {
1338 return array();
91496d15 1339 }
1340
1341 /**
83a4ee1e
AB
1342 * Returns the code (array of statements) needed to add one comment to the table.
1343 *
1344 * @param xmldb_table $xmldb_table The xmldb_table object instance.
1345 * @return array Array of SQL statements to add one comment to the table.
91496d15 1346 */
f33e1ed4 1347 public abstract function getCommentSQL($xmldb_table);
91496d15 1348
d05babe9 1349 /**
83a4ee1e
AB
1350 * Returns the code (array of statements) needed to execute extra statements on table rename.
1351 *
1352 * @param xmldb_table $xmldb_table The xmldb_table object instance.
1353 * @param string $newname The new name for the table.
1354 * @return array Array of extra SQL statements to rename a table.
d05babe9 1355 */
00570de5 1356 public function getRenameTableExtraSQL($xmldb_table, $newname) {
f33e1ed4 1357 return array();
8830218f 1358 }
1359
1360 /**
1361 * Returns the code (array of statements) needed to execute extra statements on table drop
83a4ee1e
AB
1362 *
1363 * @param xmldb_table $xmldb_table The xmldb_table object instance.
1364 * @return array Array of extra SQL statements to drop a table.
8830218f 1365 */
f33e1ed4 1366 public function getDropTableExtraSQL($xmldb_table) {
1367 return array();
d05babe9 1368 }
1369
b899d9bf 1370 /**
a3790ed5 1371 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its enum
b899d9bf 1372 * (usually invoked from getModifyEnumSQL()
2a88f626 1373 *
83a4ee1e
AB
1374 * Note that this method may be dropped in future.
1375 *
1376 * @param xmldb_table $xmldb_table The xmldb_table object instance.
1377 * @param xmldb_field $xmldb_field The xmldb_field object instance.
1378 *
1379 * @todo MDL-31147 Moodle 2.1 - Drop getDropEnumSQL()
b899d9bf 1380 */
f33e1ed4 1381 public abstract function getDropEnumSQL($xmldb_table, $xmldb_field);
b899d9bf 1382
812e363a 1383 /**
a3790ed5 1384 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default
812e363a 1385 * (usually invoked from getModifyDefaultSQL()
2d2d79ef 1386 *
83a4ee1e
AB
1387 * Note that this method may be dropped in future.
1388 *
1389 * @param xmldb_table $xmldb_table The xmldb_table object instance.
1390 * @param xmldb_field $xmldb_field The xmldb_field object instance.
1391 *
1392 * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL()
812e363a 1393 */
f33e1ed4 1394 public abstract function getDropDefaultSQL($xmldb_table, $xmldb_field);
812e363a 1395
4215e41e 1396 /**
a8cb94f6 1397 * Given one xmldb_table and one optional xmldb_field, return one array with all the check
4215e41e 1398 * constrainst found for that table (or field). Must exist for each DB supported.
1399 * (usually invoked from find_check_constraint_name)
2d2d79ef 1400 *
83a4ee1e
AB
1401 * Note that this method may be dropped in future.
1402 *
1403 * @param xmldb_table $xmldb_table The xmldb_table object instance.
1404 * @param xmldb_field $xmldb_field The xmldb_field object instance.
1405 *
1406 * @todo MDL-31147 Moodle 2.1 - Drop getCheckConstraintsFromDB
4215e41e 1407 */
f33e1ed4 1408 public abstract function getCheckConstraintsFromDB($xmldb_table, $xmldb_field=null);
4215e41e 1409
812e363a 1410 /**
a3790ed5 1411 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default
812e363a 1412 * (usually invoked from getModifyDefaultSQL()
83a4ee1e
AB
1413 *
1414 * @param xmldb_table $xmldb_table The xmldb_table object instance.
1415 * @param xmldb_field $xmldb_field The xmldb_field object instance.
1416 * @return array Array of SQL statements to create a field's default.
812e363a 1417 */
f33e1ed4 1418 public abstract function getCreateDefaultSQL($xmldb_table, $xmldb_field);
812e363a 1419
91496d15 1420 /**
1421 * Returns an array of reserved words (lowercase) for this DB
83a4ee1e
AB
1422 * You MUST provide the real list for each DB inside every XMLDB class.
1423 * @return array An array of database specific reserved words.
1424 * @throws coding_exception Thrown if not implemented for the specific DB.
91496d15 1425 */
209e0018 1426 public static function getReservedWords() {
7c109ea3 1427 throw new coding_exception('getReservedWords() method needs to be overridden in each subclass of sql_generator');
209e0018 1428 }
2e2a396c 1429
1430 /**
83a4ee1e 1431 * Returns all reserved words in supported databases.
28a40b5c 1432 * Reserved words should be lowercase.
f33e1ed4 1433 * @return array ('word'=>array(databases))
2e2a396c 1434 */
f33e1ed4 1435 public static function getAllReservedWords() {
1436 global $CFG;
1437
0a03123f 1438 $generators = array('mysql', 'postgres', 'oracle', 'mssql');
f33e1ed4 1439 $reserved_words = array();
1440
28a40b5c 1441 foreach($generators as $generator) {
1442 $class = $generator . '_sql_generator';
1443 require_once("$CFG->libdir/ddl/$class.php");
1444 foreach (call_user_func(array($class, 'getReservedWords')) as $word) {
1445 $reserved_words[$word][] = $generator;
f33e1ed4 1446 }
f33e1ed4 1447 }
1448 ksort($reserved_words);
1449 return $reserved_words;
1450 }
1451
83a4ee1e
AB
1452 /**
1453 * Adds slashes to string.
1454 * @param string $s
1455 * @return string The escaped string.
1456 */
f33e1ed4 1457 public function addslashes($s) {
1458 // do not use php addslashes() because it depends on PHP quote settings!
1459 $s = str_replace('\\','\\\\',$s);
1460 $s = str_replace("\0","\\\0", $s);
1461 $s = str_replace("'", "\\'", $s);
1462 return $s;
2e2a396c 1463 }
91496d15 1464}