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