| [ Index ] |
PHP Cross Reference of Unnamed Project |
[Summary view] [Print] [Text view]
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 and the new name, 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 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add the field to the table. 679 * 680 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 681 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 682 * @param string $skip_type_clause The type clause on alter columns, NULL by default. 683 * @param string $skip_default_clause The default clause on alter columns, NULL by default. 684 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default. 685 * @return array The SQL statement for adding a field to the table. 686 */ 687 public function getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) { 688 689 $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause; 690 $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause; 691 $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause; 692 693 $results = array(); 694 695 // Get the quoted name of the table and field 696 $tablename = $this->getTableName($xmldb_table); 697 698 // Build the standard alter table add 699 $sql = $this->getFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, 700 $skip_default_clause, 701 $skip_notnull_clause); 702 $altertable = 'ALTER TABLE ' . $tablename . ' ADD ' . $sql; 703 // Add the after clause if necessary 704 if ($this->add_after_clause && $xmldb_field->getPrevious()) { 705 $altertable .= ' AFTER ' . $this->getEncQuoted($xmldb_field->getPrevious()); 706 } 707 $results[] = $altertable; 708 709 return $results; 710 } 711 712 /** 713 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop the field from the table. 714 * 715 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 716 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 717 * @return array The SQL statement for dropping a field from the table. 718 */ 719 public function getDropFieldSQL($xmldb_table, $xmldb_field) { 720 721 $results = array(); 722 723 // Get the quoted name of the table and field 724 $tablename = $this->getTableName($xmldb_table); 725 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 726 727 // Build the standard alter table drop 728 $results[] = 'ALTER TABLE ' . $tablename . ' DROP COLUMN ' . $fieldname; 729 730 return $results; 731 } 732 733 /** 734 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table. 735 * 736 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 737 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 738 * @param string $skip_type_clause The type clause on alter columns, NULL by default. 739 * @param string $skip_default_clause The default clause on alter columns, NULL by default. 740 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default. 741 * @return string The field altering SQL statement. 742 */ 743 public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) { 744 745 $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause; 746 $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause; 747 $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause; 748 749 $results = array(); 750 751 // Get the quoted name of the table and field 752 $tablename = $this->getTableName($xmldb_table); 753 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 754 755 // Build de alter sentence using the alter_column_sql template 756 $alter = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->alter_column_sql); 757 $colspec = $this->getFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, 758 $skip_default_clause, 759 $skip_notnull_clause, 760 true); 761 $alter = str_replace('COLUMNSPECS', $colspec, $alter); 762 763 // Add the after clause if necessary 764 if ($this->add_after_clause && $xmldb_field->getPrevious()) { 765 $alter .= ' after ' . $this->getEncQuoted($xmldb_field->getPrevious()); 766 } 767 768 // Build the standard alter table modify 769 $results[] = $alter; 770 771 return $results; 772 } 773 774 /** 775 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to modify the default of the field in the table. 776 * 777 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 778 * @param xmldb_field $xmldb_field The instance of xmldb_field to get the modified default value from. 779 * @return array The SQL statement for modifying the default value. 780 */ 781 public function getModifyDefaultSQL($xmldb_table, $xmldb_field) { 782 783 $results = array(); 784 785 // Get the quoted name of the table and field 786 $tablename = $this->getTableName($xmldb_table); 787 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 788 789 // Decide if we are going to create/modify or to drop the default 790 if ($xmldb_field->getDefault() === null) { 791 $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); //Drop 792 } else { 793 $results = $this->getCreateDefaultSQL($xmldb_table, $xmldb_field); //Create/modify 794 } 795 796 return $results; 797 } 798 799 /** 800 * Given one correct xmldb_field and the new name, returns the SQL statements 801 * to rename it (inside one array). 802 * 803 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 804 * @param xmldb_field $xmldb_field The instance of xmldb_field to get the renamed field from. 805 * @param string $newname The new name to rename the field to. 806 * @return array The SQL statements for renaming the field. 807 */ 808 public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) { 809 810 $results = array(); //Array where all the sentences will be stored 811 812 // Although this is checked in database_manager::rename_field() - double check 813 // that we aren't trying to rename one "id" field. Although it could be 814 // implemented (if adding the necessary code to rename sequences, defaults, 815 // triggers... and so on under each getRenameFieldExtraSQL() function, it's 816 // better to forbid it, mainly because this field is the default PK and 817 // in the future, a lot of FKs can be pointing here. So, this field, more 818 // or less, must be considered immutable! 819 if ($xmldb_field->getName() == 'id') { 820 return array(); 821 } 822 823 $rename = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_column_sql); 824 $rename = str_replace('OLDFIELDNAME', $this->getEncQuoted($xmldb_field->getName()), $rename); 825 $rename = str_replace('NEWFIELDNAME', $this->getEncQuoted($newname), $rename); 826 827 $results[] = $rename; 828 829 // Call to getRenameFieldExtraSQL(), override if needed 830 $extra_sentences = $this->getRenameFieldExtraSQL($xmldb_table, $xmldb_field, $newname); 831 $results = array_merge($results, $extra_sentences); 832 833 return $results; 834 } 835 836 /** 837 * Given one xmldb_table and one xmldb_key, return the SQL statements needed to add the key to the table 838 * note that undelying indexes will be added as parametrised by $xxxx_keys and $xxxx_index parameters. 839 * 840 * @param xmldb_table $xmldb_table The table related to $xmldb_key. 841 * @param xmldb_key $xmldb_key The xmldb_key to add. 842 * @return array SQL statement to add the xmldb_key. 843 */ 844 public function getAddKeySQL($xmldb_table, $xmldb_key) { 845 846 $results = array(); 847 848 // Just use the CreateKeySQL function 849 if ($keyclause = $this->getKeySQL($xmldb_table, $xmldb_key)) { 850 $key = 'ALTER TABLE ' . $this->getTableName($xmldb_table) . 851 ' ADD CONSTRAINT ' . $keyclause; 852 $results[] = $key; 853 } 854 855 // If we aren't creating the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated 856 // automatically by the RDBMS) create the underlying (created by us) index (if doesn't exists) 857 if (!$keyclause || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) { 858 // Only if they don't exist 859 if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN) { //Calculate type of index based on type ok key 860 $indextype = XMLDB_INDEX_NOTUNIQUE; 861 } else { 862 $indextype = XMLDB_INDEX_UNIQUE; 863 } 864 $xmldb_index = new xmldb_index('anyname', $indextype, $xmldb_key->getFields()); 865 if (!$this->mdb->get_manager()->index_exists($xmldb_table, $xmldb_index)) { 866 $results = array_merge($results, $this->getAddIndexSQL($xmldb_table, $xmldb_index)); 867 } 868 } 869 870 // If the key is XMLDB_KEY_FOREIGN_UNIQUE, create it as UNIQUE too 871 if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && $this->unique_keys) { 872 //Duplicate the key 873 $xmldb_key->setType(XMLDB_KEY_UNIQUE); 874 $results = array_merge($results, $this->getAddKeySQL($xmldb_table, $xmldb_key)); 875 } 876 877 // Return results 878 return $results; 879 } 880 881 /** 882 * Given one xmldb_table and one xmldb_index, return the SQL statements needed to drop the index from the table. 883 * 884 * @param xmldb_table $xmldb_table The table related to $xmldb_key. 885 * @param xmldb_key $xmldb_key The xmldb_key to drop. 886 * @return array SQL statement to drop the xmldb_key. 887 */ 888 public function getDropKeySQL($xmldb_table, $xmldb_key) { 889 890 $results = array(); 891 892 // Get the key name (note that this doesn't introspect DB, so could cause some problems sometimes!) 893 // TODO: We'll need to overwrite the whole getDropKeySQL() method inside each DB to do the proper queries 894 // against the dictionary or require ADOdb to support it or change the find_key_name() method to 895 // perform DB introspection directly. But, for now, as we aren't going to enable referential integrity 896 // it won't be a problem at all 897 $dbkeyname = $this->mdb->get_manager()->find_key_name($xmldb_table, $xmldb_key); 898 899 // Only if such type of key generation is enabled 900 $dropkey = false; 901 switch ($xmldb_key->getType()) { 902 case XMLDB_KEY_PRIMARY: 903 if ($this->primary_keys) { 904 $template = $this->drop_primary_key; 905 $dropkey = true; 906 } 907 break; 908 case XMLDB_KEY_UNIQUE: 909 if ($this->unique_keys) { 910 $template = $this->drop_unique_key; 911 $dropkey = true; 912 } 913 break; 914 case XMLDB_KEY_FOREIGN_UNIQUE: 915 case XMLDB_KEY_FOREIGN: 916 if ($this->foreign_keys) { 917 $template = $this->drop_foreign_key; 918 $dropkey = true; 919 } 920 break; 921 } 922 // If we have decided to drop the key, let's do it 923 if ($dropkey) { 924 // Replace TABLENAME, CONSTRAINTTYPE and KEYNAME as needed 925 $dropsql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $template); 926 $dropsql = str_replace('KEYNAME', $dbkeyname, $dropsql); 927 928 $results[] = $dropsql; 929 } 930 931 // If we aren't dropping the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated 932 // automatically by the RDBMS) drop the underlying (created by us) index (if exists) 933 if (!$dropkey || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) { 934 // Only if they exist 935 $xmldb_index = new xmldb_index('anyname', XMLDB_INDEX_UNIQUE, $xmldb_key->getFields()); 936 if ($this->mdb->get_manager()->index_exists($xmldb_table, $xmldb_index)) { 937 $results = array_merge($results, $this->getDropIndexSQL($xmldb_table, $xmldb_index)); 938 } 939 } 940 941 // If the key is XMLDB_KEY_FOREIGN_UNIQUE, drop the UNIQUE too 942 if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && $this->unique_keys) { 943 //Duplicate the key 944 $xmldb_key->setType(XMLDB_KEY_UNIQUE); 945 $results = array_merge($results, $this->getDropKeySQL($xmldb_table, $xmldb_key)); 946 } 947 948 // Return results 949 return $results; 950 } 951 952 /** 953 * Given one xmldb_table and one xmldb_key, return the SQL statements needed to rename the key in the table 954 * Experimental! Shouldn't be used at all! 955 * 956 * @param xmldb_table $xmldb_table The table related to $xmldb_key. 957 * @param xmldb_key $xmldb_key The xmldb_key to rename. 958 * @param string $newname The xmldb_key's new name. 959 * @return array SQL statement to rename the xmldb_key. 960 */ 961 public function getRenameKeySQL($xmldb_table, $xmldb_key, $newname) { 962 963 $results = array(); 964 965 // Get the real key name 966 $dbkeyname = $this->mdb->get_manager()->find_key_name($xmldb_table, $xmldb_key); 967 968 // Check we are really generating this type of keys 969 if (($xmldb_key->getType() == XMLDB_KEY_PRIMARY && !$this->primary_keys) || 970 ($xmldb_key->getType() == XMLDB_KEY_UNIQUE && !$this->unique_keys) || 971 ($xmldb_key->getType() == XMLDB_KEY_FOREIGN && !$this->foreign_keys) || 972 ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && !$this->unique_keys && !$this->foreign_keys)) { 973 // We aren't generating this type of keys, delegate to child indexes 974 $xmldb_index = new xmldb_index($xmldb_key->getName()); 975 $xmldb_index->setFields($xmldb_key->getFields()); 976 return $this->getRenameIndexSQL($xmldb_table, $xmldb_index, $newname); 977 } 978 979 // Arrived here so we are working with keys, lets rename them 980 // Replace TABLENAME and KEYNAME as needed 981 $renamesql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_key_sql); 982 $renamesql = str_replace('OLDKEYNAME', $dbkeyname, $renamesql); 983 $renamesql = str_replace('NEWKEYNAME', $newname, $renamesql); 984 985 // Some DB doesn't support key renaming so this can be empty 986 if ($renamesql) { 987 $results[] = $renamesql; 988 } 989 990 return $results; 991 } 992 993 /** 994 * Given one xmldb_table and one xmldb_index, return the SQL statements needed to add the index to the table. 995 * 996 * @param xmldb_table $xmldb_table The xmldb_table instance to add the index on. 997 * @param xmldb_index $xmldb_index The xmldb_index to add. 998 * @return array An array of SQL statements to add the index. 999 */ 1000 public function getAddIndexSQL($xmldb_table, $xmldb_index) { 1001 1002 // Just use the CreateIndexSQL function 1003 return $this->getCreateIndexSQL($xmldb_table, $xmldb_index); 1004 } 1005 1006 /** 1007 * Given one xmldb_table and one xmldb_index, return the SQL statements needed to drop the index from the table. 1008 * 1009 * @param xmldb_table $xmldb_table The xmldb_table instance to drop the index on. 1010 * @param xmldb_index $xmldb_index The xmldb_index to drop. 1011 * @return array An array of SQL statements to drop the index. 1012 */ 1013 public function getDropIndexSQL($xmldb_table, $xmldb_index) { 1014 1015 $results = array(); 1016 1017 // Get the real index name 1018 $dbindexnames = $this->mdb->get_manager()->find_index_name($xmldb_table, $xmldb_index, true); 1019 1020 // Replace TABLENAME and INDEXNAME as needed 1021 if ($dbindexnames) { 1022 foreach ($dbindexnames as $dbindexname) { 1023 $dropsql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->drop_index_sql); 1024 $dropsql = str_replace('INDEXNAME', $this->getEncQuoted($dbindexname), $dropsql); 1025 $results[] = $dropsql; 1026 } 1027 } 1028 1029 return $results; 1030 } 1031 1032 /** 1033 * Given one xmldb_table and one xmldb_index, return the SQL statements needed to rename the index in the table 1034 * Experimental! Shouldn't be used at all! 1035 * 1036 * @param xmldb_table $xmldb_table The xmldb_table instance to rename the index on. 1037 * @param xmldb_index $xmldb_index The xmldb_index to rename. 1038 * @param string $newname The xmldb_index's new name. 1039 * @return array An array of SQL statements to rename the index. 1040 */ 1041 function getRenameIndexSQL($xmldb_table, $xmldb_index, $newname) { 1042 // Some DB doesn't support index renaming (MySQL) so this can be empty 1043 if (empty($this->rename_index_sql)) { 1044 return array(); 1045 } 1046 1047 // Get the real index name 1048 $dbindexname = $this->mdb->get_manager()->find_index_name($xmldb_table, $xmldb_index); 1049 // Replace TABLENAME and INDEXNAME as needed 1050 $renamesql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_index_sql); 1051 $renamesql = str_replace('OLDINDEXNAME', $this->getEncQuoted($dbindexname), $renamesql); 1052 $renamesql = str_replace('NEWINDEXNAME', $this->getEncQuoted($newname), $renamesql); 1053 1054 return array($renamesql); 1055 } 1056 1057 /** 1058 * Given three strings (table name, list of fields (comma separated) and suffix), 1059 * create the proper object name quoting it if necessary. 1060 * 1061 * IMPORTANT: This function must be used to CALCULATE NAMES of objects TO BE CREATED, 1062 * NEVER TO GUESS NAMES of EXISTING objects!!! 1063 * 1064 * @param string $tablename The table name. 1065 * @param string $fields A list of comma separated fields. 1066 * @param string $suffix A suffix for the object name. 1067 * @return string Object's name. 1068 */ 1069 public function getNameForObject($tablename, $fields, $suffix='') { 1070 1071 $name = ''; 1072 1073 // Implement one basic cache to avoid object name duplication 1074 // along all the request life, but never to return cached results 1075 // We need this because sql statements are created before executing 1076 // them, hence names doesn't exist "physically" yet in DB, so we need 1077 // to known which ones have been used 1078 static $used_names = array(); 1079 1080 // Use standard naming. See http://docs.moodle.org/en/XMLDB_key_and_index_naming 1081 $tablearr = explode ('_', $tablename); 1082 foreach ($tablearr as $table) { 1083 $name .= substr(trim($table),0,4); 1084 } 1085 $name .= '_'; 1086 $fieldsarr = explode (',', $fields); 1087 foreach ($fieldsarr as $field) { 1088 $name .= substr(trim($field),0,3); 1089 } 1090 // Prepend the prefix 1091 $name = trim($this->prefix . $name); 1092 1093 // Make sure name does not exceed the maximum name length and add suffix. 1094 $maxlengthwithoutsuffix = $this->names_max_length - strlen($suffix) - ($suffix ? 1 : 0); 1095 $namewithsuffix = substr($name, 0, $maxlengthwithoutsuffix) . ($suffix ? ('_' . $suffix) : ''); 1096 1097 // If the calculated name is in the cache, or if we detect it by introspecting the DB let's modify if 1098 $counter = 1; 1099 while (in_array($namewithsuffix, $used_names) || $this->isNameInUse($namewithsuffix, $suffix, $tablename)) { 1100 // Now iterate until not used name is found, incrementing the counter 1101 $counter++; 1102 $namewithsuffix = substr($name, 0, $maxlengthwithoutsuffix - strlen($counter)) . 1103 $counter . ($suffix ? ('_' . $suffix) : ''); 1104 } 1105 1106 // Add the name to the cache 1107 $used_names[] = $namewithsuffix; 1108 1109 // Quote it if necessary (reserved words) 1110 $namewithsuffix = $this->getEncQuoted($namewithsuffix); 1111 1112 return $namewithsuffix; 1113 } 1114 1115 /** 1116 * Given any string (or one array), enclose it by the proper quotes 1117 * if it's a reserved word 1118 * 1119 * @param string|array $input String to quote. 1120 * @return string Quoted string. 1121 */ 1122 public function getEncQuoted($input) { 1123 1124 if (is_array($input)) { 1125 foreach ($input as $key=>$content) { 1126 $input[$key] = $this->getEncQuoted($content); 1127 } 1128 return $input; 1129 } else { 1130 // Always lowercase 1131 $input = strtolower($input); 1132 // if reserved or quote_all or has hyphens, quote it 1133 if ($this->quote_all || in_array($input, $this->reserved_words) || strpos($input, '-') !== false) { 1134 $input = $this->quote_string . $input . $this->quote_string; 1135 } 1136 return $input; 1137 } 1138 } 1139 1140 /** 1141 * Given one XMLDB Statement, build the needed SQL insert sentences to execute it. 1142 * 1143 * @param string $statement SQL statement. 1144 * @return array Array of sentences in the SQL statement. 1145 */ 1146 function getExecuteInsertSQL($statement) { 1147 1148 $results = array(); //Array where all the sentences will be stored 1149 1150 if ($sentences = $statement->getSentences()) { 1151 foreach ($sentences as $sentence) { 1152 // Get the list of fields 1153 $fields = $statement->getFieldsFromInsertSentence($sentence); 1154 // Get the values of fields 1155 $values = $statement->getValuesFromInsertSentence($sentence); 1156 // Look if we have some CONCAT value and transform it dynamically 1157 foreach($values as $key => $value) { 1158 // Trim single quotes 1159 $value = trim($value,"'"); 1160 if (stristr($value, 'CONCAT') !== false){ 1161 // Look for data between parenthesis 1162 preg_match("/CONCAT\s*\((.*)\)$/is", trim($value), $matches); 1163 if (isset($matches[1])) { 1164 $part = $matches[1]; 1165 // Convert the comma separated string to an array 1166 $arr = xmldb_object::comma2array($part); 1167 if ($arr) { 1168 $value = $this->getConcatSQL($arr); 1169 } 1170 } 1171 } 1172 // Values to be sent to DB must be properly escaped 1173 $value = $this->addslashes($value); 1174 // Back trimmed quotes 1175 $value = "'" . $value . "'"; 1176 // Back to the array 1177 $values[$key] = $value; 1178 } 1179 1180 // Iterate over fields, escaping them if necessary 1181 foreach($fields as $key => $field) { 1182 $fields[$key] = $this->getEncQuoted($field); 1183 } 1184 // Build the final SQL sentence and add it to the array of results 1185 $sql = 'INSERT INTO ' . $this->getEncQuoted($this->prefix . $statement->getTable()) . 1186 '(' . implode(', ', $fields) . ') ' . 1187 'VALUES (' . implode(', ', $values) . ')'; 1188 $results[] = $sql; 1189 } 1190 1191 } 1192 return $results; 1193 } 1194 1195 /** 1196 * Given one array of elements, build the proper CONCAT expression, based 1197 * in the $concat_character setting. If such setting is empty, then 1198 * MySQL's CONCAT function will be used instead. 1199 * 1200 * @param array $elements An array of elements to concatenate. 1201 * @return mixed Returns the result of moodle_database::sql_concat() or false. 1202 * @uses moodle_database::sql_concat() 1203 * @uses call_user_func_array() 1204 */ 1205 public function getConcatSQL($elements) { 1206 1207 // Replace double quoted elements by single quotes 1208 foreach($elements as $key => $element) { 1209 $element = trim($element); 1210 if (substr($element, 0, 1) == '"' && 1211 substr($element, -1, 1) == '"') { 1212 $elements[$key] = "'" . trim($element, '"') . "'"; 1213 } 1214 } 1215 1216 // Now call the standard $DB->sql_concat() DML function 1217 return call_user_func_array(array($this->mdb, 'sql_concat'), $elements); 1218 } 1219 1220 /** 1221 * Returns the name (string) of the sequence used in the table for the autonumeric pk 1222 * Only some DB have this implemented. 1223 * 1224 * @param xmldb_table $xmldb_table The xmldb_table instance. 1225 * @return bool Returns the sequence from the DB or false. 1226 */ 1227 public function getSequenceFromDB($xmldb_table) { 1228 return false; 1229 } 1230 1231 /** 1232 * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg). 1233 * 1234 * (MySQL requires the whole xmldb_table object to be specified, so we add it always) 1235 * 1236 * This is invoked from getNameForObject(). 1237 * Only some DB have this implemented. 1238 * 1239 * @param string $object_name The object's name to check for. 1240 * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg). 1241 * @param string $table_name The table's name to check in 1242 * @return bool If such name is currently in use (true) or no (false) 1243 */ 1244 public function isNameInUse($object_name, $type, $table_name) { 1245 return false; //For generators not implementing introspection, 1246 //we always return with the name being free to be used 1247 } 1248 1249 1250 // ====== FOLLOWING FUNCTION MUST BE CUSTOMISED BY ALL THE XMLDGenerator classes ======== 1251 1252 /** 1253 * Reset a sequence to the id field of a table. 1254 * 1255 * @param xmldb_table|string $table name of table or the table object. 1256 * @return array of sql statements 1257 */ 1258 public abstract function getResetSequenceSQL($table); 1259 1260 /** 1261 * Given one correct xmldb_table, returns the SQL statements 1262 * to create temporary table (inside one array). 1263 * 1264 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1265 * @return array of sql statements 1266 */ 1267 abstract public function getCreateTempTableSQL($xmldb_table); 1268 1269 /** 1270 * Given one XMLDB Type, length and decimals, returns the DB proper SQL type. 1271 * 1272 * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants. 1273 * @param int $xmldb_length The length of that data type. 1274 * @param int $xmldb_decimals The decimal places of precision of the data type. 1275 * @return string The DB defined data type. 1276 */ 1277 public abstract function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null); 1278 1279 /** 1280 * Returns the code (array of statements) needed to execute extra statements on field rename. 1281 * 1282 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1283 * @param xmldb_field $xmldb_field The xmldb_field object instance. 1284 * @return array Array of extra SQL statements to run with a field being renamed. 1285 */ 1286 public function getRenameFieldExtraSQL($xmldb_table, $xmldb_field) { 1287 return array(); 1288 } 1289 1290 /** 1291 * Returns the code (array of statements) needed 1292 * to create one sequence for the xmldb_table and xmldb_field passed in. 1293 * 1294 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1295 * @param xmldb_field $xmldb_field The xmldb_field object instance. 1296 * @return array Array of SQL statements to create the sequence. 1297 */ 1298 public function getCreateSequenceSQL($xmldb_table, $xmldb_field) { 1299 return array(); 1300 } 1301 1302 /** 1303 * Returns the code (array of statements) needed to add one comment to the table. 1304 * 1305 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1306 * @return array Array of SQL statements to add one comment to the table. 1307 */ 1308 public abstract function getCommentSQL($xmldb_table); 1309 1310 /** 1311 * Returns the code (array of statements) needed to execute extra statements on table rename. 1312 * 1313 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1314 * @param string $newname The new name for the table. 1315 * @return array Array of extra SQL statements to rename a table. 1316 */ 1317 public function getRenameTableExtraSQL($xmldb_table, $newname) { 1318 return array(); 1319 } 1320 1321 /** 1322 * Returns the code (array of statements) needed to execute extra statements on table drop 1323 * 1324 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1325 * @return array Array of extra SQL statements to drop a table. 1326 */ 1327 public function getDropTableExtraSQL($xmldb_table) { 1328 return array(); 1329 } 1330 1331 /** 1332 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default 1333 * (usually invoked from getModifyDefaultSQL() 1334 * 1335 * Note that this method may be dropped in future. 1336 * 1337 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1338 * @param xmldb_field $xmldb_field The xmldb_field object instance. 1339 * @return array Array of SQL statements to create a field's default. 1340 * 1341 * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL() 1342 */ 1343 public abstract function getDropDefaultSQL($xmldb_table, $xmldb_field); 1344 1345 /** 1346 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default 1347 * (usually invoked from getModifyDefaultSQL() 1348 * 1349 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1350 * @param xmldb_field $xmldb_field The xmldb_field object instance. 1351 * @return array Array of SQL statements to create a field's default. 1352 */ 1353 public abstract function getCreateDefaultSQL($xmldb_table, $xmldb_field); 1354 1355 /** 1356 * Returns an array of reserved words (lowercase) for this DB 1357 * You MUST provide the real list for each DB inside every XMLDB class. 1358 * @return array An array of database specific reserved words. 1359 * @throws coding_exception Thrown if not implemented for the specific DB. 1360 */ 1361 public static function getReservedWords() { 1362 throw new coding_exception('getReservedWords() method needs to be overridden in each subclass of sql_generator'); 1363 } 1364 1365 /** 1366 * Returns all reserved words in supported databases. 1367 * Reserved words should be lowercase. 1368 * @return array ('word'=>array(databases)) 1369 */ 1370 public static function getAllReservedWords() { 1371 global $CFG; 1372 1373 $generators = array('mysql', 'postgres', 'oracle', 'mssql'); 1374 $reserved_words = array(); 1375 1376 foreach($generators as $generator) { 1377 $class = $generator . '_sql_generator'; 1378 require_once("$CFG->libdir/ddl/$class.php"); 1379 foreach (call_user_func(array($class, 'getReservedWords')) as $word) { 1380 $reserved_words[$word][] = $generator; 1381 } 1382 } 1383 ksort($reserved_words); 1384 return $reserved_words; 1385 } 1386 1387 /** 1388 * Adds slashes to string. 1389 * @param string $s 1390 * @return string The escaped string. 1391 */ 1392 public function addslashes($s) { 1393 // do not use php addslashes() because it depends on PHP quote settings! 1394 $s = str_replace('\\','\\\\',$s); 1395 $s = str_replace("\0","\\\0", $s); 1396 $s = str_replace("'", "\\'", $s); 1397 return $s; 1398 } 1399 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Thu Aug 11 10:00:09 2016 | Cross-referenced by PHPXref 0.7.1 |