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