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