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