| [ Index ] |
PHP Cross Reference of moodle-2.8 |
[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 // get all tables in moodle database 230 $tables = $this->mdb->get_tables(); 231 $exists = in_array($tablename, $tables); 232 233 return $exists; 234 } 235 236 /** 237 * This function will return the SQL code needed to create db tables and statements. 238 * @see xmldb_structure 239 * 240 * @param xmldb_structure $xmldb_structure An xmldb_structure instance. 241 * @return array 242 */ 243 public function getCreateStructureSQL($xmldb_structure) { 244 $results = array(); 245 246 if ($tables = $xmldb_structure->getTables()) { 247 foreach ($tables as $table) { 248 $results = array_merge($results, $this->getCreateTableSQL($table)); 249 } 250 } 251 252 return $results; 253 } 254 255 /** 256 * Given one xmldb_table, this returns it's correct name, depending of all the parameterization. 257 * eg: This appends $prefix to the table name. 258 * 259 * @see $prefix 260 * 261 * @param xmldb_table $xmldb_table The table whose name we want. 262 * @param boolean $quoted To specify if the name must be quoted (if reserved word, only!). 263 * @return string The correct name of the table. 264 */ 265 public function getTableName(xmldb_table $xmldb_table, $quoted=true) { 266 // Get the name 267 $tablename = $this->prefix.$xmldb_table->getName(); 268 269 // Apply quotes optionally 270 if ($quoted) { 271 $tablename = $this->getEncQuoted($tablename); 272 } 273 274 return $tablename; 275 } 276 277 /** 278 * Given one correct xmldb_table, returns the SQL statements 279 * to create it (inside one array). 280 * 281 * @param xmldb_table $xmldb_table An xmldb_table instance. 282 * @return array An array of SQL statements, starting with the table creation SQL followed 283 * by any of its comments, indexes and sequence creation SQL statements. 284 */ 285 public function getCreateTableSQL($xmldb_table) { 286 if ($error = $xmldb_table->validateDefinition()) { 287 throw new coding_exception($error); 288 } 289 290 $results = array(); //Array where all the sentences will be stored 291 292 // Table header 293 $table = 'CREATE TABLE ' . $this->getTableName($xmldb_table) . ' ('; 294 295 if (!$xmldb_fields = $xmldb_table->getFields()) { 296 return $results; 297 } 298 299 $sequencefield = null; 300 301 // Add the fields, separated by commas 302 foreach ($xmldb_fields as $xmldb_field) { 303 if ($xmldb_field->getSequence()) { 304 $sequencefield = $xmldb_field->getName(); 305 } 306 $table .= "\n " . $this->getFieldSQL($xmldb_table, $xmldb_field); 307 $table .= ','; 308 } 309 // Add the keys, separated by commas 310 if ($xmldb_keys = $xmldb_table->getKeys()) { 311 foreach ($xmldb_keys as $xmldb_key) { 312 if ($keytext = $this->getKeySQL($xmldb_table, $xmldb_key)) { 313 $table .= "\nCONSTRAINT " . $keytext . ','; 314 } 315 // If the key is XMLDB_KEY_FOREIGN_UNIQUE, create it as UNIQUE too 316 if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE) { 317 //Duplicate the key 318 $xmldb_key->setType(XMLDB_KEY_UNIQUE); 319 if ($keytext = $this->getKeySQL($xmldb_table, $xmldb_key)) { 320 $table .= "\nCONSTRAINT " . $keytext . ','; 321 } 322 } 323 // make sure sequence field is unique 324 if ($sequencefield and $xmldb_key->getType() == XMLDB_KEY_PRIMARY) { 325 $fields = $xmldb_key->getFields(); 326 $field = reset($fields); 327 if ($sequencefield === $field) { 328 $sequencefield = null; 329 } 330 } 331 } 332 } 333 // throw error if sequence field does not have unique key defined 334 if ($sequencefield) { 335 throw new ddl_exception('ddsequenceerror', $xmldb_table->getName()); 336 } 337 338 // Table footer, trim the latest comma 339 $table = trim($table,','); 340 $table .= "\n)"; 341 342 // Add the CREATE TABLE to results 343 $results[] = $table; 344 345 // Add comments if specified and it exists 346 if ($this->add_table_comments && $xmldb_table->getComment()) { 347 $comment = $this->getCommentSQL($xmldb_table); 348 // Add the COMMENT to results 349 $results = array_merge($results, $comment); 350 } 351 352 // Add the indexes (each one, one statement) 353 if ($xmldb_indexes = $xmldb_table->getIndexes()) { 354 foreach ($xmldb_indexes as $xmldb_index) { 355 //tables do not exist yet, which means indexed can not exist yet 356 if ($indextext = $this->getCreateIndexSQL($xmldb_table, $xmldb_index)) { 357 $results = array_merge($results, $indextext); 358 } 359 } 360 } 361 362 // Also, add the indexes needed from keys, based on configuration (each one, one statement) 363 if ($xmldb_keys = $xmldb_table->getKeys()) { 364 foreach ($xmldb_keys as $xmldb_key) { 365 // If we aren't creating the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated 366 // automatically by the RDBMS) create the underlying (created by us) index (if doesn't exists) 367 if (!$this->getKeySQL($xmldb_table, $xmldb_key) || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) { 368 // Create the interim index 369 $index = new xmldb_index('anyname'); 370 $index->setFields($xmldb_key->getFields()); 371 //tables do not exist yet, which means indexed can not exist yet 372 $createindex = false; //By default 373 switch ($xmldb_key->getType()) { 374 case XMLDB_KEY_UNIQUE: 375 case XMLDB_KEY_FOREIGN_UNIQUE: 376 $index->setUnique(true); 377 $createindex = true; 378 break; 379 case XMLDB_KEY_FOREIGN: 380 $index->setUnique(false); 381 $createindex = true; 382 break; 383 } 384 if ($createindex) { 385 if ($indextext = $this->getCreateIndexSQL($xmldb_table, $index)) { 386 // Add the INDEX to the array 387 $results = array_merge($results, $indextext); 388 } 389 } 390 } 391 } 392 } 393 394 // Add sequence extra code if needed 395 if ($this->sequence_extra_code) { 396 // Iterate over fields looking for sequences 397 foreach ($xmldb_fields as $xmldb_field) { 398 if ($xmldb_field->getSequence()) { 399 // returns an array of statements needed to create one sequence 400 $sequence_sentences = $this->getCreateSequenceSQL($xmldb_table, $xmldb_field); 401 // Add the SEQUENCE to the array 402 $results = array_merge($results, $sequence_sentences); 403 } 404 } 405 } 406 407 return $results; 408 } 409 410 /** 411 * Given one correct xmldb_index, returns the SQL statements 412 * needed to create it (in array). 413 * 414 * @param xmldb_table $xmldb_table The xmldb_table instance to create the index on. 415 * @param xmldb_index $xmldb_index The xmldb_index to create. 416 * @return array An array of SQL statements to create the index. 417 * @throws coding_exception Thrown if the xmldb_index does not validate with the xmldb_table. 418 */ 419 public function getCreateIndexSQL($xmldb_table, $xmldb_index) { 420 if ($error = $xmldb_index->validateDefinition($xmldb_table)) { 421 throw new coding_exception($error); 422 } 423 424 $unique = ''; 425 $suffix = 'ix'; 426 if ($xmldb_index->getUnique()) { 427 $unique = ' UNIQUE'; 428 $suffix = 'uix'; 429 } 430 431 $index = 'CREATE' . $unique . ' INDEX '; 432 $index .= $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_index->getFields()), $suffix); 433 $index .= ' ON ' . $this->getTableName($xmldb_table); 434 $index .= ' (' . implode(', ', $this->getEncQuoted($xmldb_index->getFields())) . ')'; 435 436 return array($index); 437 } 438 439 /** 440 * Given one correct xmldb_field, returns the complete SQL line to create it. 441 * 442 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 443 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 444 * @param string $skip_type_clause The type clause on alter columns, NULL by default. 445 * @param string $skip_default_clause The default clause on alter columns, NULL by default. 446 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default. 447 * @param string $specify_nulls_clause To force a specific null clause, NULL by default. 448 * @param bool $specify_field_name Flag to specify fieldname in return. 449 * @return string The field generating SQL statement. 450 * @throws coding_exception Thrown when xmldb_field doesn't validate with the xmldb_table. 451 */ 452 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) { 453 if ($error = $xmldb_field->validateDefinition($xmldb_table)) { 454 throw new coding_exception($error); 455 } 456 457 $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause; 458 $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause; 459 $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause; 460 $specify_nulls_clause = is_null($specify_nulls_clause) ? $this->specify_nulls : $specify_nulls_clause; 461 462 // First of all, convert integers to numbers if defined 463 if ($this->integer_to_number) { 464 if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER) { 465 $xmldb_field->setType(XMLDB_TYPE_NUMBER); 466 } 467 } 468 // Same for floats 469 if ($this->float_to_number) { 470 if ($xmldb_field->getType() == XMLDB_TYPE_FLOAT) { 471 $xmldb_field->setType(XMLDB_TYPE_NUMBER); 472 } 473 } 474 475 $field = ''; // Let's accumulate the whole expression based on params and settings 476 // The name 477 if ($specify_field_name) { 478 $field .= $this->getEncQuoted($xmldb_field->getName()); 479 } 480 // The type and length only if we don't want to skip it 481 if (!$skip_type_clause) { 482 // The type and length 483 $field .= ' ' . $this->getTypeSQL($xmldb_field->getType(), $xmldb_field->getLength(), $xmldb_field->getDecimals()); 484 } 485 // note: unsigned is not supported any more since moodle 2.3, all numbers are signed 486 // Calculate the not null clause 487 $notnull = ''; 488 // Only if we don't want to skip it 489 if (!$skip_notnull_clause) { 490 if ($xmldb_field->getNotNull()) { 491 $notnull = ' NOT NULL'; 492 } else { 493 if ($specify_nulls_clause) { 494 $notnull = ' NULL'; 495 } 496 } 497 } 498 // Calculate the default clause 499 $default_clause = ''; 500 if (!$skip_default_clause) { //Only if we don't want to skip it 501 $default_clause = $this->getDefaultClause($xmldb_field); 502 } 503 // Based on default_after_null, set both clauses properly 504 if ($this->default_after_null) { 505 $field .= $notnull . $default_clause; 506 } else { 507 $field .= $default_clause . $notnull; 508 } 509 // The sequence 510 if ($xmldb_field->getSequence()) { 511 if($xmldb_field->getLength()<=9 && $this->sequence_name_small) { 512 $sequencename=$this->sequence_name_small; 513 } else { 514 $sequencename=$this->sequence_name; 515 } 516 $field .= ' ' . $sequencename; 517 if ($this->sequence_only) { 518 // We only want the field name and sequence name to be printed 519 // so, calculate it and return 520 $sql = $this->getEncQuoted($xmldb_field->getName()) . ' ' . $sequencename; 521 return $sql; 522 } 523 } 524 return $field; 525 } 526 527 /** 528 * Given one correct xmldb_key, returns its specs. 529 * 530 * @param xmldb_table $xmldb_table The table related to $xmldb_key. 531 * @param xmldb_key $xmldb_key The xmldb_key's specifications requested. 532 * @return string SQL statement about the xmldb_key. 533 */ 534 public function getKeySQL($xmldb_table, $xmldb_key) { 535 536 $key = ''; 537 538 switch ($xmldb_key->getType()) { 539 case XMLDB_KEY_PRIMARY: 540 if ($this->primary_keys) { 541 if ($this->primary_key_name !== null) { 542 $key = $this->getEncQuoted($this->primary_key_name); 543 } else { 544 $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'pk'); 545 } 546 $key .= ' PRIMARY KEY (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')'; 547 } 548 break; 549 case XMLDB_KEY_UNIQUE: 550 if ($this->unique_keys) { 551 $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'uk'); 552 $key .= ' UNIQUE (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')'; 553 } 554 break; 555 case XMLDB_KEY_FOREIGN: 556 case XMLDB_KEY_FOREIGN_UNIQUE: 557 if ($this->foreign_keys) { 558 $key = $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_key->getFields()), 'fk'); 559 $key .= ' FOREIGN KEY (' . implode(', ', $this->getEncQuoted($xmldb_key->getFields())) . ')'; 560 $key .= ' REFERENCES ' . $this->getEncQuoted($this->prefix . $xmldb_key->getRefTable()); 561 $key .= ' (' . implode(', ', $this->getEncQuoted($xmldb_key->getRefFields())) . ')'; 562 } 563 break; 564 } 565 566 return $key; 567 } 568 569 /** 570 * Give one xmldb_field, returns the correct "default value" for the current configuration 571 * 572 * @param xmldb_field $xmldb_field The field. 573 * @return The default value of the field. 574 */ 575 public function getDefaultValue($xmldb_field) { 576 577 $default = null; 578 579 if ($xmldb_field->getDefault() !== NULL) { 580 if ($xmldb_field->getType() == XMLDB_TYPE_CHAR || 581 $xmldb_field->getType() == XMLDB_TYPE_TEXT) { 582 if ($xmldb_field->getDefault() === '') { // If passing empty default, use the $default_for_char one instead 583 $default = "'" . $this->default_for_char . "'"; 584 } else { 585 $default = "'" . $this->addslashes($xmldb_field->getDefault()) . "'"; 586 } 587 } else { 588 $default = $xmldb_field->getDefault(); 589 } 590 } else { 591 // We force default '' for not null char columns without proper default 592 // some day this should be out! 593 if ($this->default_for_char !== NULL && 594 $xmldb_field->getType() == XMLDB_TYPE_CHAR && 595 $xmldb_field->getNotNull()) { 596 $default = "'" . $this->default_for_char . "'"; 597 } else { 598 // If the DB requires to explicity define some clause to drop one default, do it here 599 // never applying defaults to TEXT and BINARY fields 600 if ($this->drop_default_value_required && 601 $xmldb_field->getType() != XMLDB_TYPE_TEXT && 602 $xmldb_field->getType() != XMLDB_TYPE_BINARY && !$xmldb_field->getNotNull()) { 603 $default = $this->drop_default_value; 604 } 605 } 606 } 607 return $default; 608 } 609 610 /** 611 * Given one xmldb_field, returns the correct "default clause" for the current configuration. 612 * 613 * @param xmldb_field $xmldb_field The xmldb_field. 614 * @return The SQL clause for generating the default value as in $xmldb_field. 615 */ 616 public function getDefaultClause($xmldb_field) { 617 618 $defaultvalue = $this->getDefaultValue ($xmldb_field); 619 620 if ($defaultvalue !== null) { 621 return ' DEFAULT ' . $defaultvalue; 622 } else { 623 return null; 624 } 625 } 626 627 /** 628 * Given one correct xmldb_table and the new name, returns the SQL statements 629 * to rename it (inside one array). 630 * 631 * @param xmldb_table $xmldb_table The table to rename. 632 * @param string $newname The new name to rename the table to. 633 * @return array SQL statement(s) to rename the table. 634 */ 635 public function getRenameTableSQL($xmldb_table, $newname) { 636 637 $results = array(); //Array where all the sentences will be stored 638 639 $newt = new xmldb_table($newname); //Temporal table for name calculations 640 641 $rename = str_replace('OLDNAME', $this->getTableName($xmldb_table), $this->rename_table_sql); 642 $rename = str_replace('NEWNAME', $this->getTableName($newt), $rename); 643 644 $results[] = $rename; 645 646 // Call to getRenameTableExtraSQL() override if needed 647 $extra_sentences = $this->getRenameTableExtraSQL($xmldb_table, $newname); 648 $results = array_merge($results, $extra_sentences); 649 650 return $results; 651 } 652 653 /** 654 * Given one correct xmldb_table and the new name, returns the SQL statements 655 * to drop it (inside one array). Works also for temporary tables. 656 * 657 * @param xmldb_table $xmldb_table The table to drop. 658 * @return array SQL statement(s) for dropping the specified table. 659 */ 660 public function getDropTableSQL($xmldb_table) { 661 662 $results = array(); //Array where all the sentences will be stored 663 664 $drop = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->drop_table_sql); 665 666 $results[] = $drop; 667 668 // call to getDropTableExtraSQL(), override if needed 669 $extra_sentences = $this->getDropTableExtraSQL($xmldb_table); 670 $results = array_merge($results, $extra_sentences); 671 672 return $results; 673 } 674 675 /** 676 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add the field to the table. 677 * 678 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 679 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 680 * @param string $skip_type_clause The type clause on alter columns, NULL by default. 681 * @param string $skip_default_clause The default clause on alter columns, NULL by default. 682 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default. 683 * @return array The SQL statement for adding a field to the table. 684 */ 685 public function getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) { 686 687 $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause; 688 $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause; 689 $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause; 690 691 $results = array(); 692 693 // Get the quoted name of the table and field 694 $tablename = $this->getTableName($xmldb_table); 695 696 // Build the standard alter table add 697 $sql = $this->getFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, 698 $skip_default_clause, 699 $skip_notnull_clause); 700 $altertable = 'ALTER TABLE ' . $tablename . ' ADD ' . $sql; 701 // Add the after clause if necessary 702 if ($this->add_after_clause && $xmldb_field->getPrevious()) { 703 $altertable .= ' AFTER ' . $this->getEncQuoted($xmldb_field->getPrevious()); 704 } 705 $results[] = $altertable; 706 707 return $results; 708 } 709 710 /** 711 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop the field from the table. 712 * 713 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 714 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 715 * @return array The SQL statement for dropping a field from the table. 716 */ 717 public function getDropFieldSQL($xmldb_table, $xmldb_field) { 718 719 $results = array(); 720 721 // Get the quoted name of the table and field 722 $tablename = $this->getTableName($xmldb_table); 723 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 724 725 // Build the standard alter table drop 726 $results[] = 'ALTER TABLE ' . $tablename . ' DROP COLUMN ' . $fieldname; 727 728 return $results; 729 } 730 731 /** 732 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table. 733 * 734 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 735 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 736 * @param string $skip_type_clause The type clause on alter columns, NULL by default. 737 * @param string $skip_default_clause The default clause on alter columns, NULL by default. 738 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default. 739 * @return string The field altering SQL statement. 740 */ 741 public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) { 742 743 $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause; 744 $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause; 745 $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause; 746 747 $results = array(); 748 749 // Get the quoted name of the table and field 750 $tablename = $this->getTableName($xmldb_table); 751 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 752 753 // Build de alter sentence using the alter_column_sql template 754 $alter = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->alter_column_sql); 755 $colspec = $this->getFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, 756 $skip_default_clause, 757 $skip_notnull_clause, 758 true); 759 $alter = str_replace('COLUMNSPECS', $colspec, $alter); 760 761 // Add the after clause if necessary 762 if ($this->add_after_clause && $xmldb_field->getPrevious()) { 763 $alter .= ' after ' . $this->getEncQuoted($xmldb_field->getPrevious()); 764 } 765 766 // Build the standard alter table modify 767 $results[] = $alter; 768 769 return $results; 770 } 771 772 /** 773 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to modify the default of the field in the table. 774 * 775 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 776 * @param xmldb_field $xmldb_field The instance of xmldb_field to get the modified default value from. 777 * @return array The SQL statement for modifying the default value. 778 */ 779 public function getModifyDefaultSQL($xmldb_table, $xmldb_field) { 780 781 $results = array(); 782 783 // Get the quoted name of the table and field 784 $tablename = $this->getTableName($xmldb_table); 785 $fieldname = $this->getEncQuoted($xmldb_field->getName()); 786 787 // Decide if we are going to create/modify or to drop the default 788 if ($xmldb_field->getDefault() === null) { 789 $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); //Drop 790 } else { 791 $results = $this->getCreateDefaultSQL($xmldb_table, $xmldb_field); //Create/modify 792 } 793 794 return $results; 795 } 796 797 /** 798 * Given one correct xmldb_field and the new name, returns the SQL statements 799 * to rename it (inside one array). 800 * 801 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 802 * @param xmldb_field $xmldb_field The instance of xmldb_field to get the renamed field from. 803 * @param string $newname The new name to rename the field to. 804 * @return array The SQL statements for renaming the field. 805 */ 806 public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) { 807 808 $results = array(); //Array where all the sentences will be stored 809 810 // Although this is checked in database_manager::rename_field() - double check 811 // that we aren't trying to rename one "id" field. Although it could be 812 // implemented (if adding the necessary code to rename sequences, defaults, 813 // triggers... and so on under each getRenameFieldExtraSQL() function, it's 814 // better to forbid it, mainly because this field is the default PK and 815 // in the future, a lot of FKs can be pointing here. So, this field, more 816 // or less, must be considered immutable! 817 if ($xmldb_field->getName() == 'id') { 818 return array(); 819 } 820 821 $rename = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_column_sql); 822 $rename = str_replace('OLDFIELDNAME', $this->getEncQuoted($xmldb_field->getName()), $rename); 823 $rename = str_replace('NEWFIELDNAME', $this->getEncQuoted($newname), $rename); 824 825 $results[] = $rename; 826 827 // Call to getRenameFieldExtraSQL(), override if needed 828 $extra_sentences = $this->getRenameFieldExtraSQL($xmldb_table, $xmldb_field, $newname); 829 $results = array_merge($results, $extra_sentences); 830 831 return $results; 832 } 833 834 /** 835 * Given one xmldb_table and one xmldb_key, return the SQL statements needed to add the key to the table 836 * note that undelying indexes will be added as parametrised by $xxxx_keys and $xxxx_index parameters. 837 * 838 * @param xmldb_table $xmldb_table The table related to $xmldb_key. 839 * @param xmldb_key $xmldb_key The xmldb_key to add. 840 * @return array SQL statement to add the xmldb_key. 841 */ 842 public function getAddKeySQL($xmldb_table, $xmldb_key) { 843 844 $results = array(); 845 846 // Just use the CreateKeySQL function 847 if ($keyclause = $this->getKeySQL($xmldb_table, $xmldb_key)) { 848 $key = 'ALTER TABLE ' . $this->getTableName($xmldb_table) . 849 ' ADD CONSTRAINT ' . $keyclause; 850 $results[] = $key; 851 } 852 853 // If we aren't creating the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated 854 // automatically by the RDBMS) create the underlying (created by us) index (if doesn't exists) 855 if (!$keyclause || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) { 856 // Only if they don't exist 857 if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN) { //Calculate type of index based on type ok key 858 $indextype = XMLDB_INDEX_NOTUNIQUE; 859 } else { 860 $indextype = XMLDB_INDEX_UNIQUE; 861 } 862 $xmldb_index = new xmldb_index('anyname', $indextype, $xmldb_key->getFields()); 863 if (!$this->mdb->get_manager()->index_exists($xmldb_table, $xmldb_index)) { 864 $results = array_merge($results, $this->getAddIndexSQL($xmldb_table, $xmldb_index)); 865 } 866 } 867 868 // If the key is XMLDB_KEY_FOREIGN_UNIQUE, create it as UNIQUE too 869 if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && $this->unique_keys) { 870 //Duplicate the key 871 $xmldb_key->setType(XMLDB_KEY_UNIQUE); 872 $results = array_merge($results, $this->getAddKeySQL($xmldb_table, $xmldb_key)); 873 } 874 875 // Return results 876 return $results; 877 } 878 879 /** 880 * Given one xmldb_table and one xmldb_index, return the SQL statements needed to drop the index from the table. 881 * 882 * @param xmldb_table $xmldb_table The table related to $xmldb_key. 883 * @param xmldb_key $xmldb_key The xmldb_key to drop. 884 * @return array SQL statement to drop the xmldb_key. 885 */ 886 public function getDropKeySQL($xmldb_table, $xmldb_key) { 887 888 $results = array(); 889 890 // Get the key name (note that this doesn't introspect DB, so could cause some problems sometimes!) 891 // TODO: We'll need to overwrite the whole getDropKeySQL() method inside each DB to do the proper queries 892 // against the dictionary or require ADOdb to support it or change the find_key_name() method to 893 // perform DB introspection directly. But, for now, as we aren't going to enable referential integrity 894 // it won't be a problem at all 895 $dbkeyname = $this->mdb->get_manager()->find_key_name($xmldb_table, $xmldb_key); 896 897 // Only if such type of key generation is enabled 898 $dropkey = false; 899 switch ($xmldb_key->getType()) { 900 case XMLDB_KEY_PRIMARY: 901 if ($this->primary_keys) { 902 $template = $this->drop_primary_key; 903 $dropkey = true; 904 } 905 break; 906 case XMLDB_KEY_UNIQUE: 907 if ($this->unique_keys) { 908 $template = $this->drop_unique_key; 909 $dropkey = true; 910 } 911 break; 912 case XMLDB_KEY_FOREIGN_UNIQUE: 913 case XMLDB_KEY_FOREIGN: 914 if ($this->foreign_keys) { 915 $template = $this->drop_foreign_key; 916 $dropkey = true; 917 } 918 break; 919 } 920 // If we have decided to drop the key, let's do it 921 if ($dropkey) { 922 // Replace TABLENAME, CONSTRAINTTYPE and KEYNAME as needed 923 $dropsql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $template); 924 $dropsql = str_replace('KEYNAME', $dbkeyname, $dropsql); 925 926 $results[] = $dropsql; 927 } 928 929 // If we aren't dropping the keys OR if the key is XMLDB_KEY_FOREIGN (not underlying index generated 930 // automatically by the RDBMS) drop the underlying (created by us) index (if exists) 931 if (!$dropkey || $xmldb_key->getType() == XMLDB_KEY_FOREIGN) { 932 // Only if they exist 933 $xmldb_index = new xmldb_index('anyname', XMLDB_INDEX_UNIQUE, $xmldb_key->getFields()); 934 if ($this->mdb->get_manager()->index_exists($xmldb_table, $xmldb_index)) { 935 $results = array_merge($results, $this->getDropIndexSQL($xmldb_table, $xmldb_index)); 936 } 937 } 938 939 // If the key is XMLDB_KEY_FOREIGN_UNIQUE, drop the UNIQUE too 940 if ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && $this->unique_keys) { 941 //Duplicate the key 942 $xmldb_key->setType(XMLDB_KEY_UNIQUE); 943 $results = array_merge($results, $this->getDropKeySQL($xmldb_table, $xmldb_key)); 944 } 945 946 // Return results 947 return $results; 948 } 949 950 /** 951 * Given one xmldb_table and one xmldb_key, return the SQL statements needed to rename the key in the table 952 * Experimental! Shouldn't be used at all! 953 * 954 * @param xmldb_table $xmldb_table The table related to $xmldb_key. 955 * @param xmldb_key $xmldb_key The xmldb_key to rename. 956 * @param string $newname The xmldb_key's new name. 957 * @return array SQL statement to rename the xmldb_key. 958 */ 959 public function getRenameKeySQL($xmldb_table, $xmldb_key, $newname) { 960 961 $results = array(); 962 963 // Get the real key name 964 $dbkeyname = $this->mdb->get_manager()->find_key_name($xmldb_table, $xmldb_key); 965 966 // Check we are really generating this type of keys 967 if (($xmldb_key->getType() == XMLDB_KEY_PRIMARY && !$this->primary_keys) || 968 ($xmldb_key->getType() == XMLDB_KEY_UNIQUE && !$this->unique_keys) || 969 ($xmldb_key->getType() == XMLDB_KEY_FOREIGN && !$this->foreign_keys) || 970 ($xmldb_key->getType() == XMLDB_KEY_FOREIGN_UNIQUE && !$this->unique_keys && !$this->foreign_keys)) { 971 // We aren't generating this type of keys, delegate to child indexes 972 $xmldb_index = new xmldb_index($xmldb_key->getName()); 973 $xmldb_index->setFields($xmldb_key->getFields()); 974 return $this->getRenameIndexSQL($xmldb_table, $xmldb_index, $newname); 975 } 976 977 // Arrived here so we are working with keys, lets rename them 978 // Replace TABLENAME and KEYNAME as needed 979 $renamesql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_key_sql); 980 $renamesql = str_replace('OLDKEYNAME', $dbkeyname, $renamesql); 981 $renamesql = str_replace('NEWKEYNAME', $newname, $renamesql); 982 983 // Some DB doesn't support key renaming so this can be empty 984 if ($renamesql) { 985 $results[] = $renamesql; 986 } 987 988 return $results; 989 } 990 991 /** 992 * Given one xmldb_table and one xmldb_index, return the SQL statements needed to add the index to the table. 993 * 994 * @param xmldb_table $xmldb_table The xmldb_table instance to add the index on. 995 * @param xmldb_index $xmldb_index The xmldb_index to add. 996 * @return array An array of SQL statements to add the index. 997 */ 998 public function getAddIndexSQL($xmldb_table, $xmldb_index) { 999 1000 // Just use the CreateIndexSQL function 1001 return $this->getCreateIndexSQL($xmldb_table, $xmldb_index); 1002 } 1003 1004 /** 1005 * Given one xmldb_table and one xmldb_index, return the SQL statements needed to drop the index from the table. 1006 * 1007 * @param xmldb_table $xmldb_table The xmldb_table instance to drop the index on. 1008 * @param xmldb_index $xmldb_index The xmldb_index to drop. 1009 * @return array An array of SQL statements to drop the index. 1010 */ 1011 public function getDropIndexSQL($xmldb_table, $xmldb_index) { 1012 1013 $results = array(); 1014 1015 // Get the real index name 1016 $dbindexnames = $this->mdb->get_manager()->find_index_name($xmldb_table, $xmldb_index, true); 1017 1018 // Replace TABLENAME and INDEXNAME as needed 1019 if ($dbindexnames) { 1020 foreach ($dbindexnames as $dbindexname) { 1021 $dropsql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->drop_index_sql); 1022 $dropsql = str_replace('INDEXNAME', $this->getEncQuoted($dbindexname), $dropsql); 1023 $results[] = $dropsql; 1024 } 1025 } 1026 1027 return $results; 1028 } 1029 1030 /** 1031 * Given one xmldb_table and one xmldb_index, return the SQL statements needed to rename the index in the table 1032 * Experimental! Shouldn't be used at all! 1033 * 1034 * @param xmldb_table $xmldb_table The xmldb_table instance to rename the index on. 1035 * @param xmldb_index $xmldb_index The xmldb_index to rename. 1036 * @param string $newname The xmldb_index's new name. 1037 * @return array An array of SQL statements to rename the index. 1038 */ 1039 function getRenameIndexSQL($xmldb_table, $xmldb_index, $newname) { 1040 // Some DB doesn't support index renaming (MySQL) so this can be empty 1041 if (empty($this->rename_index_sql)) { 1042 return array(); 1043 } 1044 1045 // Get the real index name 1046 $dbindexname = $this->mdb->get_manager()->find_index_name($xmldb_table, $xmldb_index); 1047 // Replace TABLENAME and INDEXNAME as needed 1048 $renamesql = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_index_sql); 1049 $renamesql = str_replace('OLDINDEXNAME', $this->getEncQuoted($dbindexname), $renamesql); 1050 $renamesql = str_replace('NEWINDEXNAME', $this->getEncQuoted($newname), $renamesql); 1051 1052 return array($renamesql); 1053 } 1054 1055 /** 1056 * Given three strings (table name, list of fields (comma separated) and suffix), 1057 * create the proper object name quoting it if necessary. 1058 * 1059 * IMPORTANT: This function must be used to CALCULATE NAMES of objects TO BE CREATED, 1060 * NEVER TO GUESS NAMES of EXISTING objects!!! 1061 * 1062 * @param string $tablename The table name. 1063 * @param string $fields A list of comma separated fields. 1064 * @param string $suffix A suffix for the object name. 1065 * @return string Object's name. 1066 */ 1067 public function getNameForObject($tablename, $fields, $suffix='') { 1068 1069 $name = ''; 1070 1071 // Implement one basic cache to avoid object name duplication 1072 // along all the request life, but never to return cached results 1073 // We need this because sql statements are created before executing 1074 // them, hence names doesn't exist "physically" yet in DB, so we need 1075 // to known which ones have been used 1076 static $used_names = array(); 1077 1078 // Use standard naming. See http://docs.moodle.org/en/XMLDB_key_and_index_naming 1079 $tablearr = explode ('_', $tablename); 1080 foreach ($tablearr as $table) { 1081 $name .= substr(trim($table),0,4); 1082 } 1083 $name .= '_'; 1084 $fieldsarr = explode (',', $fields); 1085 foreach ($fieldsarr as $field) { 1086 $name .= substr(trim($field),0,3); 1087 } 1088 // Prepend the prefix 1089 $name = trim($this->prefix . $name); 1090 1091 // Make sure name does not exceed the maximum name length and add suffix. 1092 $maxlengthwithoutsuffix = $this->names_max_length - strlen($suffix) - ($suffix ? 1 : 0); 1093 $namewithsuffix = substr($name, 0, $maxlengthwithoutsuffix) . ($suffix ? ('_' . $suffix) : ''); 1094 1095 // If the calculated name is in the cache, or if we detect it by introspecting the DB let's modify if 1096 $counter = 1; 1097 while (in_array($namewithsuffix, $used_names) || $this->isNameInUse($namewithsuffix, $suffix, $tablename)) { 1098 // Now iterate until not used name is found, incrementing the counter 1099 $counter++; 1100 $namewithsuffix = substr($name, 0, $maxlengthwithoutsuffix - strlen($counter)) . 1101 $counter . ($suffix ? ('_' . $suffix) : ''); 1102 } 1103 1104 // Add the name to the cache 1105 $used_names[] = $namewithsuffix; 1106 1107 // Quote it if necessary (reserved words) 1108 $namewithsuffix = $this->getEncQuoted($namewithsuffix); 1109 1110 return $namewithsuffix; 1111 } 1112 1113 /** 1114 * Given any string (or one array), enclose it by the proper quotes 1115 * if it's a reserved word 1116 * 1117 * @param string|array $input String to quote. 1118 * @return string Quoted string. 1119 */ 1120 public function getEncQuoted($input) { 1121 1122 if (is_array($input)) { 1123 foreach ($input as $key=>$content) { 1124 $input[$key] = $this->getEncQuoted($content); 1125 } 1126 return $input; 1127 } else { 1128 // Always lowercase 1129 $input = strtolower($input); 1130 // if reserved or quote_all or has hyphens, quote it 1131 if ($this->quote_all || in_array($input, $this->reserved_words) || strpos($input, '-') !== false) { 1132 $input = $this->quote_string . $input . $this->quote_string; 1133 } 1134 return $input; 1135 } 1136 } 1137 1138 /** 1139 * Given one XMLDB Statement, build the needed SQL insert sentences to execute it. 1140 * 1141 * @param string $statement SQL statement. 1142 * @return array Array of sentences in the SQL statement. 1143 */ 1144 function getExecuteInsertSQL($statement) { 1145 1146 $results = array(); //Array where all the sentences will be stored 1147 1148 if ($sentences = $statement->getSentences()) { 1149 foreach ($sentences as $sentence) { 1150 // Get the list of fields 1151 $fields = $statement->getFieldsFromInsertSentence($sentence); 1152 // Get the values of fields 1153 $values = $statement->getValuesFromInsertSentence($sentence); 1154 // Look if we have some CONCAT value and transform it dynamically 1155 foreach($values as $key => $value) { 1156 // Trim single quotes 1157 $value = trim($value,"'"); 1158 if (stristr($value, 'CONCAT') !== false){ 1159 // Look for data between parenthesis 1160 preg_match("/CONCAT\s*\((.*)\)$/is", trim($value), $matches); 1161 if (isset($matches[1])) { 1162 $part = $matches[1]; 1163 // Convert the comma separated string to an array 1164 $arr = xmldb_object::comma2array($part); 1165 if ($arr) { 1166 $value = $this->getConcatSQL($arr); 1167 } 1168 } 1169 } 1170 // Values to be sent to DB must be properly escaped 1171 $value = $this->addslashes($value); 1172 // Back trimmed quotes 1173 $value = "'" . $value . "'"; 1174 // Back to the array 1175 $values[$key] = $value; 1176 } 1177 1178 // Iterate over fields, escaping them if necessary 1179 foreach($fields as $key => $field) { 1180 $fields[$key] = $this->getEncQuoted($field); 1181 } 1182 // Build the final SQL sentence and add it to the array of results 1183 $sql = 'INSERT INTO ' . $this->getEncQuoted($this->prefix . $statement->getTable()) . 1184 '(' . implode(', ', $fields) . ') ' . 1185 'VALUES (' . implode(', ', $values) . ')'; 1186 $results[] = $sql; 1187 } 1188 1189 } 1190 return $results; 1191 } 1192 1193 /** 1194 * Given one array of elements, build the proper CONCAT expression, based 1195 * in the $concat_character setting. If such setting is empty, then 1196 * MySQL's CONCAT function will be used instead. 1197 * 1198 * @param array $elements An array of elements to concatenate. 1199 * @return mixed Returns the result of moodle_database::sql_concat() or false. 1200 * @uses moodle_database::sql_concat() 1201 * @uses call_user_func_array() 1202 */ 1203 public function getConcatSQL($elements) { 1204 1205 // Replace double quoted elements by single quotes 1206 foreach($elements as $key => $element) { 1207 $element = trim($element); 1208 if (substr($element, 0, 1) == '"' && 1209 substr($element, -1, 1) == '"') { 1210 $elements[$key] = "'" . trim($element, '"') . "'"; 1211 } 1212 } 1213 1214 // Now call the standard $DB->sql_concat() DML function 1215 return call_user_func_array(array($this->mdb, 'sql_concat'), $elements); 1216 } 1217 1218 /** 1219 * Returns the name (string) of the sequence used in the table for the autonumeric pk 1220 * Only some DB have this implemented. 1221 * 1222 * @param xmldb_table $xmldb_table The xmldb_table instance. 1223 * @return bool Returns the sequence from the DB or false. 1224 */ 1225 public function getSequenceFromDB($xmldb_table) { 1226 return false; 1227 } 1228 1229 /** 1230 * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg). 1231 * 1232 * (MySQL requires the whole xmldb_table object to be specified, so we add it always) 1233 * 1234 * This is invoked from getNameForObject(). 1235 * Only some DB have this implemented. 1236 * 1237 * @param string $object_name The object's name to check for. 1238 * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg). 1239 * @param string $table_name The table's name to check in 1240 * @return bool If such name is currently in use (true) or no (false) 1241 */ 1242 public function isNameInUse($object_name, $type, $table_name) { 1243 return false; //For generators not implementing introspection, 1244 //we always return with the name being free to be used 1245 } 1246 1247 1248 // ====== FOLLOWING FUNCTION MUST BE CUSTOMISED BY ALL THE XMLDGenerator classes ======== 1249 1250 /** 1251 * Reset a sequence to the id field of a table. 1252 * 1253 * @param xmldb_table|string $table name of table or the table object. 1254 * @return array of sql statements 1255 */ 1256 public abstract function getResetSequenceSQL($table); 1257 1258 /** 1259 * Given one correct xmldb_table, returns the SQL statements 1260 * to create temporary table (inside one array). 1261 * 1262 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1263 * @return array of sql statements 1264 */ 1265 abstract public function getCreateTempTableSQL($xmldb_table); 1266 1267 /** 1268 * Given one XMLDB Type, length and decimals, returns the DB proper SQL type. 1269 * 1270 * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants. 1271 * @param int $xmldb_length The length of that data type. 1272 * @param int $xmldb_decimals The decimal places of precision of the data type. 1273 * @return string The DB defined data type. 1274 */ 1275 public abstract function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null); 1276 1277 /** 1278 * Returns the code (array of statements) needed to execute extra statements on field rename. 1279 * 1280 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1281 * @param xmldb_field $xmldb_field The xmldb_field object instance. 1282 * @return array Array of extra SQL statements to run with a field being renamed. 1283 */ 1284 public function getRenameFieldExtraSQL($xmldb_table, $xmldb_field) { 1285 return array(); 1286 } 1287 1288 /** 1289 * Returns the code (array of statements) needed 1290 * to create one sequence for the xmldb_table and xmldb_field passed in. 1291 * 1292 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1293 * @param xmldb_field $xmldb_field The xmldb_field object instance. 1294 * @return array Array of SQL statements to create the sequence. 1295 */ 1296 public function getCreateSequenceSQL($xmldb_table, $xmldb_field) { 1297 return array(); 1298 } 1299 1300 /** 1301 * Returns the code (array of statements) needed to add one comment to the table. 1302 * 1303 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1304 * @return array Array of SQL statements to add one comment to the table. 1305 */ 1306 public abstract function getCommentSQL($xmldb_table); 1307 1308 /** 1309 * Returns the code (array of statements) needed to execute extra statements on table rename. 1310 * 1311 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1312 * @param string $newname The new name for the table. 1313 * @return array Array of extra SQL statements to rename a table. 1314 */ 1315 public function getRenameTableExtraSQL($xmldb_table, $newname) { 1316 return array(); 1317 } 1318 1319 /** 1320 * Returns the code (array of statements) needed to execute extra statements on table drop 1321 * 1322 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1323 * @return array Array of extra SQL statements to drop a table. 1324 */ 1325 public function getDropTableExtraSQL($xmldb_table) { 1326 return array(); 1327 } 1328 1329 /** 1330 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default 1331 * (usually invoked from getModifyDefaultSQL() 1332 * 1333 * Note that this method may be dropped in future. 1334 * 1335 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1336 * @param xmldb_field $xmldb_field The xmldb_field object instance. 1337 * @return array Array of SQL statements to create a field's default. 1338 * 1339 * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL() 1340 */ 1341 public abstract function getDropDefaultSQL($xmldb_table, $xmldb_field); 1342 1343 /** 1344 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default 1345 * (usually invoked from getModifyDefaultSQL() 1346 * 1347 * @param xmldb_table $xmldb_table The xmldb_table object instance. 1348 * @param xmldb_field $xmldb_field The xmldb_field object instance. 1349 * @return array Array of SQL statements to create a field's default. 1350 */ 1351 public abstract function getCreateDefaultSQL($xmldb_table, $xmldb_field); 1352 1353 /** 1354 * Returns an array of reserved words (lowercase) for this DB 1355 * You MUST provide the real list for each DB inside every XMLDB class. 1356 * @return array An array of database specific reserved words. 1357 * @throws coding_exception Thrown if not implemented for the specific DB. 1358 */ 1359 public static function getReservedWords() { 1360 throw new coding_exception('getReservedWords() method needs to be overridden in each subclass of sql_generator'); 1361 } 1362 1363 /** 1364 * Returns all reserved words in supported databases. 1365 * Reserved words should be lowercase. 1366 * @return array ('word'=>array(databases)) 1367 */ 1368 public static function getAllReservedWords() { 1369 global $CFG; 1370 1371 $generators = array('mysql', 'postgres', 'oracle', 'mssql'); 1372 $reserved_words = array(); 1373 1374 foreach($generators as $generator) { 1375 $class = $generator . '_sql_generator'; 1376 require_once("$CFG->libdir/ddl/$class.php"); 1377 foreach (call_user_func(array($class, 'getReservedWords')) as $word) { 1378 $reserved_words[$word][] = $generator; 1379 } 1380 } 1381 ksort($reserved_words); 1382 return $reserved_words; 1383 } 1384 1385 /** 1386 * Adds slashes to string. 1387 * @param string $s 1388 * @return string The escaped string. 1389 */ 1390 public function addslashes($s) { 1391 // do not use php addslashes() because it depends on PHP quote settings! 1392 $s = str_replace('\\','\\\\',$s); 1393 $s = str_replace("\0","\\\0", $s); 1394 $s = str_replace("'", "\\'", $s); 1395 return $s; 1396 } 1397 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
| Generated: Fri Nov 28 20:29:05 2014 | Cross-referenced by PHPXref 0.7.1 |