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