Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
- Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
- PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.
/lib/ddl/ -> oracle_sql_generator.php (source)
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 * Oracle specific SQL code generator. 19 * 20 * @package core_ddl 21 * @copyright 1999 onwards Martin Dougiamas http://dougiamas.com 22 * 2001-3001 Eloy Lafuente (stronk7) http://contiento.com 23 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 24 */ 25 26 defined('MOODLE_INTERNAL') || die(); 27 28 require_once($CFG->libdir.'/ddl/sql_generator.php'); 29 30 /** 31 * This class generate SQL code to be used against Oracle 32 * It extends XMLDBgenerator so everything can be 33 * overridden as needed to generate correct SQL. 34 * 35 * @package core_ddl 36 * @copyright 1999 onwards Martin Dougiamas http://dougiamas.com 37 * 2001-3001 Eloy Lafuente (stronk7) http://contiento.com 38 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 39 */ 40 class oracle_sql_generator extends sql_generator { 41 42 // Only set values that are different from the defaults present in XMLDBgenerator 43 44 /** 45 * @var string To be automatically added at the end of each statement. 46 * note: Using "/" because the standard ";" isn't good for stored procedures (triggers) 47 */ 48 public $statement_end = "\n/"; 49 50 /** @var string Proper type for NUMBER(x) in this DB. */ 51 public $number_type = 'NUMBER'; 52 53 /** 54 * @var string To define the default to set for NOT NULLs CHARs without default (null=do nothing). 55 * note: Using this whitespace here because Oracle doesn't distinguish empty and null! :-( 56 */ 57 public $default_for_char = ' '; 58 59 /** @var bool To specify if the generator must use some DEFAULT clause to drop defaults.*/ 60 public $drop_default_value_required = true; 61 62 /** @var string The DEFAULT clause required to drop defaults.*/ 63 public $drop_default_value = null; 64 65 /** @var bool To decide if the default clause of each field must go after the null clause.*/ 66 public $default_after_null = false; 67 68 /** @var bool True if the generator needs to add extra code to generate the sequence fields.*/ 69 public $sequence_extra_code = true; 70 71 /** @var string The particular name for inline sequences in this generator.*/ 72 public $sequence_name = ''; 73 74 /** @var string The SQL template to alter columns where the 'TABLENAME' and 'COLUMNSPECS' keywords are dynamically replaced.*/ 75 public $alter_column_sql = 'ALTER TABLE TABLENAME MODIFY (COLUMNSPECS)'; 76 77 /** @var int var ugly Oracle hack - size of the sequences values cache (20 = Default)*/ 78 public $sequence_cache_size = 20; 79 80 /** 81 * Reset a sequence to the id field of a table. 82 * 83 * @param xmldb_table|string $table name of table or the table object. 84 * @return array of sql statements 85 */ 86 public function getResetSequenceSQL($table) { 87 88 if (is_string($table)) { 89 $tablename = $table; 90 $xmldb_table = new xmldb_table($tablename); 91 } else { 92 $tablename = $table->getName(); 93 $xmldb_table = $table; 94 } 95 // From http://www.acs.ilstu.edu/docs/oracle/server.101/b10759/statements_2011.htm 96 $value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'.$tablename.'}'); 97 $value++; 98 99 $seqname = $this->getSequenceFromDB($xmldb_table); 100 101 if (!$seqname) { 102 // Fallback, seqname not found, something is wrong. Inform and use the alternative getNameForObject() method 103 $seqname = $this->getNameForObject($table, 'id', 'seq'); 104 } 105 106 return array ("DROP SEQUENCE $seqname", 107 "CREATE SEQUENCE $seqname START WITH $value INCREMENT BY 1 NOMAXVALUE CACHE $this->sequence_cache_size"); 108 } 109 110 /** 111 * Given one xmldb_table, returns it's correct name, depending of all the parametrization 112 * Overridden to allow change of names in temp tables 113 * 114 * @param xmldb_table table whose name we want 115 * @param boolean to specify if the name must be quoted (if reserved word, only!) 116 * @return string the correct name of the table 117 */ 118 public function getTableName(xmldb_table $xmldb_table, $quoted=true) { 119 // Get the name, supporting special oci names for temp tables 120 if ($this->temptables->is_temptable($xmldb_table->getName())) { 121 $tablename = $this->temptables->get_correct_name($xmldb_table->getName()); 122 } else { 123 $tablename = $this->prefix . $xmldb_table->getName(); 124 } 125 126 // Apply quotes optionally 127 if ($quoted) { 128 $tablename = $this->getEncQuoted($tablename); 129 } 130 131 return $tablename; 132 } 133 134 public function getCreateIndexSQL($xmldb_table, $xmldb_index) { 135 if ($error = $xmldb_index->validateDefinition($xmldb_table)) { 136 throw new coding_exception($error); 137 } 138 139 $indexfields = $this->getEncQuoted($xmldb_index->getFields()); 140 141 $unique = ''; 142 $suffix = 'ix'; 143 if ($xmldb_index->getUnique()) { 144 $unique = ' UNIQUE'; 145 $suffix = 'uix'; 146 147 $nullablefields = $this->get_nullable_fields_in_index($xmldb_table, $xmldb_index); 148 if ($nullablefields) { 149 // If this is a unique index with nullable fields, then we have to 150 // apply the work-around from https://community.oracle.com/message/9518046#9518046. 151 // 152 // For example if you have a unique index on the three columns 153 // (required, option1, option2) where the first one is non-null, 154 // and the others nullable, then the SQL will end up as 155 // 156 // CREATE UNIQUE INDEX index_name ON table_name ( 157 // CASE WHEN option1 IS NOT NULL AND option2 IS NOT NULL THEN required ELSE NULL END, 158 // CASE WHEN option1 IS NOT NULL AND option2 IS NOT NULL THEN option1 ELSE NULL END, 159 // CASE WHEN option1 IS NOT NULL AND option2 IS NOT NULL THEN option2 ELSE NULL END) 160 // 161 // Basically Oracle behaves according to the standard if either 162 // none of the columns are NULL or all columns contain NULL. Therefore, 163 // if any column is NULL, we treat them all as NULL for the index. 164 $conditions = []; 165 foreach ($nullablefields as $fieldname) { 166 $conditions[] = $this->getEncQuoted($fieldname) . 167 ' IS NOT NULL'; 168 } 169 $condition = implode(' AND ', $conditions); 170 171 $updatedindexfields = []; 172 foreach ($indexfields as $fieldname) { 173 $updatedindexfields[] = 'CASE WHEN ' . $condition . ' THEN ' . 174 $fieldname . ' ELSE NULL END'; 175 } 176 $indexfields = $updatedindexfields; 177 } 178 179 } 180 181 $index = 'CREATE' . $unique . ' INDEX '; 182 $index .= $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_index->getFields()), $suffix); 183 $index .= ' ON ' . $this->getTableName($xmldb_table); 184 $index .= ' (' . implode(', ', $indexfields) . ')'; 185 186 return array($index); 187 } 188 189 /** 190 * Given one correct xmldb_table, returns the SQL statements 191 * to create temporary table (inside one array). 192 * 193 * @param xmldb_table $xmldb_table The xmldb_table object instance. 194 * @return array of sql statements 195 */ 196 public function getCreateTempTableSQL($xmldb_table) { 197 $this->temptables->add_temptable($xmldb_table->getName()); 198 $sqlarr = $this->getCreateTableSQL($xmldb_table); 199 $sqlarr = preg_replace('/^CREATE TABLE (.*)/s', 'CREATE GLOBAL TEMPORARY TABLE $1 ON COMMIT PRESERVE ROWS', $sqlarr); 200 return $sqlarr; 201 } 202 203 /** 204 * Given one correct xmldb_table, returns the SQL statements 205 * to drop it (inside one array). 206 * 207 * @param xmldb_table $xmldb_table The table to drop. 208 * @return array SQL statement(s) for dropping the specified table. 209 */ 210 public function getDropTableSQL($xmldb_table) { 211 $sqlarr = parent::getDropTableSQL($xmldb_table); 212 if ($this->temptables->is_temptable($xmldb_table->getName())) { 213 array_unshift($sqlarr, "TRUNCATE TABLE ". $this->getTableName($xmldb_table)); // oracle requires truncate before being able to drop a temp table 214 } 215 return $sqlarr; 216 } 217 218 /** 219 * Given one XMLDB Type, length and decimals, returns the DB proper SQL type. 220 * 221 * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants. 222 * @param int $xmldb_length The length of that data type. 223 * @param int $xmldb_decimals The decimal places of precision of the data type. 224 * @return string The DB defined data type. 225 */ 226 public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) { 227 228 switch ($xmldb_type) { 229 case XMLDB_TYPE_INTEGER: // See http://www.acs.ilstu.edu/docs/oracle/server.101/b10759/sql_elements001.htm#sthref86. 230 if (empty($xmldb_length)) { 231 $xmldb_length = 10; 232 } 233 $dbtype = 'NUMBER(' . $xmldb_length . ')'; 234 break; 235 case XMLDB_TYPE_FLOAT: 236 case XMLDB_TYPE_NUMBER: 237 $dbtype = $this->number_type; 238 if (!empty($xmldb_length)) { 239 $dbtype .= '(' . $xmldb_length; 240 if (!empty($xmldb_decimals)) { 241 $dbtype .= ',' . $xmldb_decimals; 242 } 243 $dbtype .= ')'; 244 } 245 break; 246 case XMLDB_TYPE_CHAR: 247 // Do not use NVARCHAR2 here because it has hardcoded 1333 char limit, 248 // VARCHAR2 allows us to create larger fields that error out later during runtime 249 // only when too many non-ascii utf-8 chars present. 250 $dbtype = 'VARCHAR2'; 251 if (empty($xmldb_length)) { 252 $xmldb_length='255'; 253 } 254 $dbtype .= '(' . $xmldb_length . ' CHAR)'; // CHAR is required because BYTE is the default 255 break; 256 case XMLDB_TYPE_TEXT: 257 $dbtype = 'CLOB'; 258 break; 259 case XMLDB_TYPE_BINARY: 260 $dbtype = 'BLOB'; 261 break; 262 case XMLDB_TYPE_DATETIME: 263 $dbtype = 'DATE'; 264 break; 265 } 266 return $dbtype; 267 } 268 269 /** 270 * Returns the code (array of statements) needed 271 * to create one sequence for the xmldb_table and xmldb_field passed in. 272 * 273 * @param xmldb_table $xmldb_table The xmldb_table object instance. 274 * @param xmldb_field $xmldb_field The xmldb_field object instance. 275 * @return array Array of SQL statements to create the sequence. 276 */ 277 public function getCreateSequenceSQL($xmldb_table, $xmldb_field) { 278 279 $results = array(); 280 281 $sequence_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'seq'); 282 283 $sequence = "CREATE SEQUENCE $sequence_name START WITH 1 INCREMENT BY 1 NOMAXVALUE CACHE $this->sequence_cache_size"; 284 285 $results[] = $sequence; 286 287 $results = array_merge($results, $this->getCreateTriggerSQL ($xmldb_table, $xmldb_field, $sequence_name)); 288 289 return $results; 290 } 291 292 /** 293 * Returns the code needed to create one trigger for the xmldb_table and xmldb_field passed 294 * 295 * @param xmldb_table $xmldb_table The xmldb_table object instance. 296 * @param xmldb_field $xmldb_field The xmldb_field object instance. 297 * @param string $sequence_name 298 * @return array Array of SQL statements to create the sequence. 299 */ 300 public function getCreateTriggerSQL($xmldb_table, $xmldb_field, $sequence_name) { 301 302 $trigger_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'trg'); 303 304 $trigger = "CREATE TRIGGER " . $trigger_name; 305 $trigger.= "\n BEFORE INSERT"; 306 $trigger.= "\nON " . $this->getTableName($xmldb_table); 307 $trigger.= "\n FOR EACH ROW"; 308 $trigger.= "\nBEGIN"; 309 $trigger.= "\n IF :new." . $this->getEncQuoted($xmldb_field->getName()) . ' IS NULL THEN'; 310 $trigger.= "\n SELECT " . $sequence_name . '.nextval INTO :new.' . $this->getEncQuoted($xmldb_field->getName()) . " FROM dual;"; 311 $trigger.= "\n END IF;"; 312 $trigger.= "\nEND;"; 313 314 return array($trigger); 315 } 316 317 /** 318 * Returns the code needed to drop one sequence for the xmldb_table and xmldb_field passed 319 * Can, optionally, specify if the underlying trigger will be also dropped 320 * 321 * @param xmldb_table $xmldb_table The xmldb_table object instance. 322 * @param xmldb_field $xmldb_field The xmldb_field object instance. 323 * @param bool $include_trigger 324 * @return array Array of SQL statements to create the sequence. 325 */ 326 public function getDropSequenceSQL($xmldb_table, $xmldb_field, $include_trigger=false) { 327 328 $result = array(); 329 330 if ($sequence_name = $this->getSequenceFromDB($xmldb_table)) { 331 $result[] = "DROP SEQUENCE " . $sequence_name; 332 } 333 334 if ($trigger_name = $this->getTriggerFromDB($xmldb_table) && $include_trigger) { 335 $result[] = "DROP TRIGGER " . $trigger_name; 336 } 337 338 return $result; 339 } 340 341 /** 342 * Returns the code (array of statements) needed to add one comment to the table. 343 * 344 * @param xmldb_table $xmldb_table The xmldb_table object instance. 345 * @return array Array of SQL statements to add one comment to the table. 346 */ 347 function getCommentSQL ($xmldb_table) { 348 349 $comment = "COMMENT ON TABLE " . $this->getTableName($xmldb_table); 350 $comment.= " IS '" . $this->addslashes(substr($xmldb_table->getComment(), 0, 250)) . "'"; 351 352 return array($comment); 353 } 354 355 /** 356 * Returns the code (array of statements) needed to execute extra statements on table drop 357 * 358 * @param xmldb_table $xmldb_table The xmldb_table object instance. 359 * @return array Array of extra SQL statements to drop a table. 360 */ 361 public function getDropTableExtraSQL($xmldb_table) { 362 $xmldb_field = new xmldb_field('id'); // Fields having sequences should be exclusively, id. 363 return $this->getDropSequenceSQL($xmldb_table, $xmldb_field, false); 364 } 365 366 /** 367 * Returns the code (array of statements) needed to execute extra statements on table rename. 368 * 369 * @param xmldb_table $xmldb_table The xmldb_table object instance. 370 * @param string $newname The new name for the table. 371 * @return array Array of extra SQL statements to rename a table. 372 */ 373 public function getRenameTableExtraSQL($xmldb_table, $newname) { 374 375 $results = array(); 376 377 $xmldb_field = new xmldb_field('id'); // Fields having sequences should be exclusively, id. 378 379 $oldseqname = $this->getSequenceFromDB($xmldb_table); 380 $newseqname = $this->getNameForObject($newname, $xmldb_field->getName(), 'seq'); 381 382 $oldtriggername = $this->getTriggerFromDB($xmldb_table); 383 $newtriggername = $this->getNameForObject($newname, $xmldb_field->getName(), 'trg'); 384 385 // Drop old trigger (first of all) 386 $results[] = "DROP TRIGGER " . $oldtriggername; 387 388 // Rename the sequence, disablig CACHE before and enablig it later 389 // to avoid consuming of values on rename 390 $results[] = 'ALTER SEQUENCE ' . $oldseqname . ' NOCACHE'; 391 $results[] = 'RENAME ' . $oldseqname . ' TO ' . $newseqname; 392 $results[] = 'ALTER SEQUENCE ' . $newseqname . ' CACHE ' . $this->sequence_cache_size; 393 394 // Create new trigger 395 $newt = new xmldb_table($newname); // Temp table for trigger code generation 396 $results = array_merge($results, $this->getCreateTriggerSQL($newt, $xmldb_field, $newseqname)); 397 398 return $results; 399 } 400 401 /** 402 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table. 403 * 404 * Oracle has some severe limits: 405 * - clob and blob fields doesn't allow type to be specified 406 * - error is dropped if the null/not null clause is specified and hasn't changed 407 * - changes in precision/decimals of numeric fields drop an ORA-1440 error 408 * 409 * @param xmldb_table $xmldb_table The table related to $xmldb_field. 410 * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from. 411 * @param string $skip_type_clause The type clause on alter columns, NULL by default. 412 * @param string $skip_default_clause The default clause on alter columns, NULL by default. 413 * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default. 414 * @return string The field altering SQL statement. 415 */ 416 public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) { 417 418 $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause; 419 $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause; 420 $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause; 421 422 $results = array(); // To store all the needed SQL commands 423 424 // Get the quoted name of the table and field 425 $tablename = $this->getTableName($xmldb_table); 426 $fieldname = $xmldb_field->getName(); 427 428 // Take a look to field metadata 429 $meta = $this->mdb->get_columns($xmldb_table->getName()); 430 $metac = $meta[$fieldname]; 431 $oldmetatype = $metac->meta_type; 432 433 $oldlength = $metac->max_length; 434 // To calculate the oldlength if the field is numeric, we need to perform one extra query 435 // because ADOdb has one bug here. http://phplens.com/lens/lensforum/msgs.php?id=15883 436 if ($oldmetatype == 'N') { 437 $uppertablename = strtoupper($tablename); 438 $upperfieldname = strtoupper($fieldname); 439 if ($col = $this->mdb->get_record_sql("SELECT cname, precision 440 FROM col 441 WHERE tname = ? AND cname = ?", 442 array($uppertablename, $upperfieldname))) { 443 $oldlength = $col->precision; 444 } 445 } 446 $olddecimals = empty($metac->scale) ? null : $metac->scale; 447 $oldnotnull = empty($metac->not_null) ? false : $metac->not_null; 448 $olddefault = empty($metac->default_value) || strtoupper($metac->default_value) == 'NULL' ? null : $metac->default_value; 449 450 $typechanged = true; //By default, assume that the column type has changed 451 $precisionchanged = true; //By default, assume that the column precision has changed 452 $decimalchanged = true; //By default, assume that the column decimal has changed 453 $defaultchanged = true; //By default, assume that the column default has changed 454 $notnullchanged = true; //By default, assume that the column notnull has changed 455 456 $from_temp_fields = false; //By default don't assume we are going to use temporal fields 457 458 // Detect if we are changing the type of the column 459 if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') || 460 ($xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N') || 461 ($xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F') || 462 ($xmldb_field->getType() == XMLDB_TYPE_CHAR && $oldmetatype == 'C') || 463 ($xmldb_field->getType() == XMLDB_TYPE_TEXT && $oldmetatype == 'X') || 464 ($xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B')) { 465 $typechanged = false; 466 } 467 // Detect if precision has changed 468 if (($xmldb_field->getType() == XMLDB_TYPE_TEXT) || 469 ($xmldb_field->getType() == XMLDB_TYPE_BINARY) || 470 ($oldlength == -1) || 471 ($xmldb_field->getLength() == $oldlength)) { 472 $precisionchanged = false; 473 } 474 // Detect if decimal has changed 475 if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER) || 476 ($xmldb_field->getType() == XMLDB_TYPE_CHAR) || 477 ($xmldb_field->getType() == XMLDB_TYPE_TEXT) || 478 ($xmldb_field->getType() == XMLDB_TYPE_BINARY) || 479 (!$xmldb_field->getDecimals()) || 480 (!$olddecimals) || 481 ($xmldb_field->getDecimals() == $olddecimals)) { 482 $decimalchanged = false; 483 } 484 // Detect if we are changing the default 485 if (($xmldb_field->getDefault() === null && $olddefault === null) || 486 ($xmldb_field->getDefault() === $olddefault) || //Check both equality and 487 ("'" . $xmldb_field->getDefault() . "'" === $olddefault)) { //Equality with quotes because ADOdb returns the default with quotes 488 $defaultchanged = false; 489 } 490 491 // Detect if we are changing the nullability 492 if (($xmldb_field->getNotnull() === $oldnotnull)) { 493 $notnullchanged = false; 494 } 495 496 // If type has changed or precision or decimal has changed and we are in one numeric field 497 // - create one temp column with the new specs 498 // - fill the new column with the values from the old one 499 // - drop the old column 500 // - rename the temp column to the original name 501 if (($typechanged) || (($oldmetatype == 'N' || $oldmetatype == 'I') && ($precisionchanged || $decimalchanged))) { 502 $tempcolname = $xmldb_field->getName() . '___tmp'; // Short tmp name, surely not conflicting ever 503 if (strlen($tempcolname) > 30) { // Safeguard we don't excess the 30cc limit 504 $tempcolname = 'ongoing_alter_column_tmp'; 505 } 506 // Prevent temp field to have both NULL/NOT NULL and DEFAULT constraints 507 $skip_notnull_clause = true; 508 $skip_default_clause = true; 509 $xmldb_field->setName($tempcolname); 510 // Drop the temp column, in case it exists (due to one previous failure in conversion) 511 // really ugly but we cannot enclose DDL into transaction :-( 512 if (isset($meta[$tempcolname])) { 513 $results = array_merge($results, $this->getDropFieldSQL($xmldb_table, $xmldb_field)); 514 } 515 // Create the temporal column 516 $results = array_merge($results, $this->getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_type_clause, $skip_notnull_clause)); 517 // Copy contents from original col to the temporal one 518 519 // From TEXT to integer/number we need explicit conversion 520 if ($oldmetatype == 'X' && $xmldb_field->GetType() == XMLDB_TYPE_INTEGER) { 521 $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = CAST(' . $this->mdb->sql_compare_text($fieldname) . ' AS INT)'; 522 } else if ($oldmetatype == 'X' && $xmldb_field->GetType() == XMLDB_TYPE_NUMBER) { 523 $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = CAST(' . $this->mdb->sql_compare_text($fieldname) . ' AS NUMBER)'; 524 525 // Normal cases, implicit conversion 526 } else { 527 $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = ' . $fieldname; 528 } 529 // Drop the old column 530 $xmldb_field->setName($fieldname); //Set back the original field name 531 $results = array_merge($results, $this->getDropFieldSQL($xmldb_table, $xmldb_field)); 532 // Rename the temp column to the original one 533 $results[] = 'ALTER TABLE ' . $tablename . ' RENAME COLUMN ' . $tempcolname . ' TO ' . $fieldname; 534 // Mark we have performed one change based in temp fields 535 $from_temp_fields = true; 536 // Re-enable the notnull and default sections so the general AlterFieldSQL can use it 537 $skip_notnull_clause = false; 538 $skip_default_clause = false; 539 // Disable the type section because we have done it with the temp field 540 $skip_type_clause = true; 541 // If new field is nullable, nullability hasn't changed 542 if (!$xmldb_field->getNotnull()) { 543 $notnullchanged = false; 544 } 545 // If new field hasn't default, default hasn't changed 546 if ($xmldb_field->getDefault() === null) { 547 $defaultchanged = false; 548 } 549 } 550 551 // If type and precision and decimals hasn't changed, prevent the type clause 552 if (!$typechanged && !$precisionchanged && !$decimalchanged) { 553 $skip_type_clause = true; 554 } 555 556 // If NULL/NOT NULL hasn't changed 557 // prevent null clause to be specified 558 if (!$notnullchanged) { 559 $skip_notnull_clause = true; // Initially, prevent the notnull clause 560 // But, if we have used the temp field and the new field is not null, then enforce the not null clause 561 if ($from_temp_fields && $xmldb_field->getNotnull()) { 562 $skip_notnull_clause = false; 563 } 564 } 565 // If default hasn't changed 566 // prevent default clause to be specified 567 if (!$defaultchanged) { 568 $skip_default_clause = true; // Initially, prevent the default clause 569 // But, if we have used the temp field and the new field has default clause, then enforce the default clause 570 if ($from_temp_fields) { 571 $default_clause = $this->getDefaultClause($xmldb_field); 572 if ($default_clause) { 573 $skip_notnull_clause = false; 574 } 575 } 576 } 577 578 // If arriving here, something is not being skipped (type, notnull, default), calculate the standard AlterFieldSQL 579 if (!$skip_type_clause || !$skip_notnull_clause || !$skip_default_clause) { 580 $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_default_clause, $skip_notnull_clause)); 581 return $results; 582 } 583 584 // Finally return results 585 return $results; 586 } 587 588 /** 589 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default 590 * (usually invoked from getModifyDefaultSQL() 591 * 592 * @param xmldb_table $xmldb_table The xmldb_table object instance. 593 * @param xmldb_field $xmldb_field The xmldb_field object instance. 594 * @return array Array of SQL statements to create a field's default. 595 */ 596 public function getCreateDefaultSQL($xmldb_table, $xmldb_field) { 597 // Just a wrapper over the getAlterFieldSQL() function for Oracle that 598 // is capable of handling defaults 599 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field); 600 } 601 602 /** 603 * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default 604 * (usually invoked from getModifyDefaultSQL() 605 * 606 * Note that this method may be dropped in future. 607 * 608 * @param xmldb_table $xmldb_table The xmldb_table object instance. 609 * @param xmldb_field $xmldb_field The xmldb_field object instance. 610 * @return array Array of SQL statements to create a field's default. 611 * 612 * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL() 613 */ 614 public function getDropDefaultSQL($xmldb_table, $xmldb_field) { 615 // Just a wrapper over the getAlterFieldSQL() function for Oracle that 616 // is capable of handling defaults 617 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field); 618 } 619 620 /** 621 * Given one xmldb_table returns one string with the sequence of the table 622 * in the table (fetched from DB) 623 * The sequence name for oracle is calculated by looking the corresponding 624 * trigger and retrieving the sequence name from it (because sequences are 625 * independent elements) 626 * @param xmldb_table $xmldb_table The xmldb_table object instance. 627 * @return string|bool If no sequence is found, returns false 628 */ 629 public function getSequenceFromDB($xmldb_table) { 630 631 $tablename = strtoupper($this->getTableName($xmldb_table)); 632 $prefixupper = strtoupper($this->prefix); 633 $sequencename = false; 634 635 if ($trigger = $this->mdb->get_record_sql("SELECT trigger_name, trigger_body 636 FROM user_triggers 637 WHERE table_name = ? AND trigger_name LIKE ?", 638 array($tablename, "{$prefixupper}%_ID%_TRG"))) { 639 // If trigger found, regexp it looking for the sequence name 640 preg_match('/.*SELECT (.*)\.nextval/i', $trigger->trigger_body, $matches); 641 if (isset($matches[1])) { 642 $sequencename = $matches[1]; 643 } 644 } 645 646 return $sequencename; 647 } 648 649 /** 650 * Given one xmldb_table returns one string with the trigger 651 * in the table (fetched from DB) 652 * 653 * @param xmldb_table $xmldb_table The xmldb_table object instance. 654 * @return string|bool If no trigger is found, returns false 655 */ 656 public function getTriggerFromDB($xmldb_table) { 657 658 $tablename = strtoupper($this->getTableName($xmldb_table)); 659 $prefixupper = strtoupper($this->prefix); 660 $triggername = false; 661 662 if ($trigger = $this->mdb->get_record_sql("SELECT trigger_name, trigger_body 663 FROM user_triggers 664 WHERE table_name = ? AND trigger_name LIKE ?", 665 array($tablename, "{$prefixupper}%_ID%_TRG"))) { 666 $triggername = $trigger->trigger_name; 667 } 668 669 return $triggername; 670 } 671 672 /** 673 * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg). 674 * 675 * (MySQL requires the whole xmldb_table object to be specified, so we add it always) 676 * 677 * This is invoked from getNameForObject(). 678 * Only some DB have this implemented. 679 * 680 * @param string $object_name The object's name to check for. 681 * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg). 682 * @param string $table_name The table's name to check in 683 * @return bool If such name is currently in use (true) or no (false) 684 */ 685 public function isNameInUse($object_name, $type, $table_name) { 686 switch($type) { 687 case 'ix': 688 case 'uix': 689 case 'seq': 690 case 'trg': 691 if ($check = $this->mdb->get_records_sql("SELECT object_name 692 FROM user_objects 693 WHERE lower(object_name) = ?", array(strtolower($object_name)))) { 694 return true; 695 } 696 break; 697 case 'pk': 698 case 'uk': 699 case 'fk': 700 case 'ck': 701 if ($check = $this->mdb->get_records_sql("SELECT constraint_name 702 FROM user_constraints 703 WHERE lower(constraint_name) = ?", array(strtolower($object_name)))) { 704 return true; 705 } 706 break; 707 } 708 return false; //No name in use found 709 } 710 711 /** 712 * Adds slashes to string. 713 * @param string $s 714 * @return string The escaped string. 715 */ 716 public function addslashes($s) { 717 // do not use php addslashes() because it depends on PHP quote settings! 718 $s = str_replace("'", "''", $s); 719 return $s; 720 } 721 722 /** 723 * Returns an array of reserved words (lowercase) for this DB 724 * @return array An array of database specific reserved words 725 */ 726 public static function getReservedWords() { 727 // This file contains the reserved words for Oracle databases 728 // from http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/ap_keywd.htm 729 $reserved_words = array ( 730 'access', 'add', 'all', 'alter', 'and', 'any', 731 'as', 'asc', 'audit', 'between', 'by', 'char', 732 'check', 'cluster', 'column', 'comment', 733 'compress', 'connect', 'create', 'current', 734 'date', 'decimal', 'default', 'delete', 'desc', 735 'distinct', 'drop', 'else', 'exclusive', 'exists', 736 'file', 'float', 'for', 'from', 'grant', 'group', 737 'having', 'identified', 'immediate', 'in', 738 'increment', 'index', 'initial', 'insert', 739 'integer', 'intersect', 'into', 'is', 'level', 740 'like', 'lock', 'long', 'maxextents', 'minus', 741 'mlslabel', 'mode', 'modify', 'nchar', 'nclob', 'noaudit', 742 'nocompress', 'not', 'nowait', 'null', 'number', 'nvarchar2', 743 'of', 'offline', 'on', 'online', 'option', 'or', 744 'order', 'pctfree', 'prior', 'privileges', 745 'public', 'raw', 'rename', 'resource', 'revoke', 746 'row', 'rowid', 'rownum', 'rows', 'select', 747 'session', 'set', 'share', 'size', 'smallint', 748 'start', 'successful', 'synonym', 'sysdate', 749 'table', 'then', 'to', 'trigger', 'uid', 'union', 750 'unique', 'update', 'user', 'validate', 'values', 751 'varchar', 'varchar2', 'view', 'whenever', 752 'where', 'with' 753 ); 754 return $reserved_words; 755 } 756 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
