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