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