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