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