String should be printed using s() MDL-6779
[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
d7444bfc 56 var $enum_inline_code = false; //Does the generator need to add inline code in the column definition
57
19c8321e 58 var $alter_column_sql = 'ALTER TABLE TABLENAME MODIFY (COLUMNSPECS)'; //The SQL template to alter columns
59
d7444bfc 60 /**
20c559dd 61 * Creates one new XMLDBoci8po
d7444bfc 62 */
63 function XMLDBoci8po() {
64 parent::XMLDBgenerator();
65 $this->prefix = '';
66 $this->reserved_words = $this->getReservedWords();
67 }
68
69 /**
70 * Given one XMLDB Type, lenght and decimals, returns the DB proper SQL type
71 */
72 function getTypeSQL ($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
73
74 switch ($xmldb_type) {
75 case XMLDB_TYPE_INTEGER: // From http://www.postgresql.org/docs/7.4/interactive/datatype.html
76 if (empty($xmldb_length)) {
77 $xmldb_length = 10;
78 }
79 $dbtype = 'NUMBER(' . $xmldb_length . ')';
80 break;
81 case XMLDB_TYPE_NUMBER:
82 $dbtype = $this->number_type;
4782a1f8 83 /// 38 is the max allowed
84 if ($xmldb_length > 38) {
85 $xmldb_length = 38;
86 }
d7444bfc 87 if (!empty($xmldb_length)) {
88 $dbtype .= '(' . $xmldb_length;
89 if (!empty($xmldb_decimals)) {
90 $dbtype .= ',' . $xmldb_decimals;
91 }
92 $dbtype .= ')';
93 }
94 break;
95 case XMLDB_TYPE_FLOAT:
96 $dbtype = 'NUMBER';
97 break;
98 case XMLDB_TYPE_CHAR:
99 $dbtype = 'VARCHAR2';
100 if (empty($xmldb_length)) {
101 $xmldb_length='255';
102 }
103 $dbtype .= '(' . $xmldb_length . ')';
104 break;
105 case XMLDB_TYPE_TEXT:
106 $dbtype = 'CLOB';
107 break;
108 case XMLDB_TYPE_BINARY:
109 $dbtype = 'BLOB';
110 break;
111 case XMLDB_TYPE_DATETIME:
112 $dbtype = 'DATE';
113 break;
114 }
115 return $dbtype;
116 }
117
eef868d1 118 /**
d7444bfc 119 * Returns the code needed to create one enum for the xmldb_table and xmldb_field passes
eef868d1 120 */
d7444bfc 121 function getEnumExtraSQL ($xmldb_table, $xmldb_field) {
eef868d1 122
d7444bfc 123 $sql = 'CONSTRAINT ' . $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'ck');
0dd87cfa 124 $sql.= ' CHECK (' . $this->getEncQuoted($xmldb_field->getName()) . ' IN (' . implode(', ', $xmldb_field->getEnumValues()) . '))';
d7444bfc 125
126 return $sql;
127 }
128
129 /**
130 * Returns the code needed to create one sequence for the xmldb_table and xmldb_field passes
131 */
132 function getCreateSequenceSQL ($xmldb_table, $xmldb_field) {
133
20c559dd 134 $results = array();
135
465a8029 136 $sequence_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'seq');
eef868d1 137
465a8029 138 $sequence = "CREATE SEQUENCE " . $sequence_name;
d7444bfc 139 $sequence.= "\n START WITH 1";
140 $sequence.= "\n INCREMENT BY 1";
9dcc6300 141 $sequence.= "\n NOMAXVALUE";
d7444bfc 142
20c559dd 143 $results[] = $sequence;
144
145 $results = array_merge($results, $this->getCreateTriggerSQL ($xmldb_table, $xmldb_field));
146
147 return $results;
148 }
149
150 /**
151 * Returns the code needed to create one trigger for the xmldb_table and xmldb_field passed
152 */
153 function getCreateTriggerSQL ($xmldb_table, $xmldb_field) {
154
d7444bfc 155 $trigger_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'trg');
20c559dd 156 $sequence_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'seq');
eef868d1 157
1d5071a5 158 $trigger = "CREATE TRIGGER " . $trigger_name;
d7444bfc 159 $trigger.= "\n BEFORE INSERT";
9af19c72 160 $trigger.= "\nON " . $this->getTableName($xmldb_table);
d7444bfc 161 $trigger.= "\n FOR EACH ROW";
162 $trigger.= "\nBEGIN";
4782a1f8 163 $trigger.= "\n IF :new." . $this->getEncQuoted($xmldb_field->getName()) . ' IS NULL THEN';
b8851b80 164 $trigger.= "\n SELECT " . $sequence_name . '.nextval INTO :new.' . $this->getEncQuoted($xmldb_field->getName()) . " FROM dual;";
165 $trigger.= "\n END IF;";
0513f3bf 166 $trigger.= "\nEND;";
20c559dd 167
168 return array($trigger);
d7444bfc 169 }
170
3a8c55c3 171 /**
172 * Returns the code needed to drop one sequence for the xmldb_table and xmldb_field passed
173 * Can, optionally, specify if the underlying trigger will be also dropped
174 */
175 function getDropSequenceSQL ($xmldb_table, $xmldb_field, $include_trigger=false) {
176
177 $sequence_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'seq');
eef868d1 178
3a8c55c3 179 $sequence = "DROP SEQUENCE " . $sequence_name;
180
181 $trigger_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'trg');
eef868d1 182
3a8c55c3 183 $trigger = "DROP TRIGGER " . $trigger_name;
184
185 if ($include_trigger) {
186 $result = array($sequence, $trigger);
187 } else {
188 $result = array($sequence);
189 }
190 return $result;
191 }
192
193 /**
194 * Returns the code (in array) needed to add one comment to the table
195 */
196 function getCommentSQL ($xmldb_table) {
197
9af19c72 198 $comment = "COMMENT ON TABLE " . $this->getTableName($xmldb_table);
3a8c55c3 199 $comment.= " IS '" . substr($xmldb_table->getComment(), 0, 250) . "'";
d7444bfc 200
3a8c55c3 201 return array($comment);
202 }
d7444bfc 203
3a8c55c3 204 /**
205 * Returns the code (array of statements) needed to execute extra statements on table drop
206 */
207 function getDropTableExtraSQL ($xmldb_table) {
208 $xmldb_field = new XMLDBField('id'); // Fields having sequences should be exclusively, id.
209 return $this->getDropSequenceSQL($xmldb_table, $xmldb_field, false);
210 }
d7444bfc 211
20c559dd 212 /**
213 * Returns the code (array of statements) needed to execute extra statements on table rename
214 */
215 function getRenameTableExtraSQL ($xmldb_table, $newname) {
216
217 $results = array();
218
219 $xmldb_field = new XMLDBField('id'); // Fields having sequences should be exclusively, id.
220
221 $oldseqname = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'seq');
222 $newseqname = $this->getNameForObject($newname, $xmldb_field->getName(), 'seq');
223
224 /// Rename de sequence
225 $results[] = 'RENAME ' . $oldseqname . ' TO ' . $newseqname;
226
227 $oldtriggername = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'trg');
228 $newtriggername = $this->getNameForObject($newname, $xmldb_field->getName(), 'trg');
229
230 /// Drop old trigger
231 $results[] = "DROP TRIGGER " . $oldtriggername;
232
233 $new_xmldb_table = new XMLDBTable($newname); /// Temp table for trigger code generation
234
235 /// Create new trigger
236 $results = array_merge($results, $this->getCreateTriggerSQL($new_xmldb_table, $xmldb_field));
237
238 return $results;
239 }
240
19c8321e 241 /**
242 * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to alter the field in the table
243 * Oracle has some severe limits:
244 * - clob and blob fields doesn't allow type to be specified
245 * - error is dropped if the null/not null clause is specified and hasn't changed
11b75afe 246 * - changes in precision/decimals of numeric fields drop an ORA-1440 error
19c8321e 247 */
248 function getAlterFieldSQL($xmldb_table, $xmldb_field) {
249
11b75afe 250 global $db;
251
252 $results = array(); /// To store all the needed SQL commands
253
254 /// Get the quoted name of the table and field
9af19c72 255 $tablename = $this->getTableName($xmldb_table);
11b75afe 256 $fieldname = $this->getEncQuoted($xmldb_field->getName());
257
258 /// Take a look to field metadata
259 $meta = array_change_key_case($db->MetaColumns($tablename));
260 $metac = $meta[$fieldname];
261 $oldtype = strtolower($metac->type);
262 $oldmetatype = column_type($xmldb_table->getName(), $fieldname);
263 $oldlength = $metac->max_length;
264 /// To calculate the oldlength if the field is numeric, we need to perform one extra query
265 /// because ADOdb has one bug here. http://phplens.com/lens/lensforum/msgs.php?id=15883
266 if ($oldmetatype == 'N') {
267 $uppertablename = strtoupper($tablename);
268 $upperfieldname = strtoupper($fieldname);
269 if ($col = get_record_sql("SELECT cname, precision
270 FROM col
271 WHERE tname = '$uppertablename'
272 AND cname = '$upperfieldname'")) {
273 $oldlength = $col->precision;
274 }
275 }
276 $olddecimals = empty($metac->scale) ? null : $metac->scale;
277 $oldnotnull = empty($metac->not_null) ? false : $metac->not_null;
812e363a 278 $olddefault = empty($metac->default_value) || strtoupper($metac->default_value) == 'NULL' ? null : $metac->default_value;
11b75afe 279
280 $typechanged = true; //By default, assume that the column type has changed
281 $precisionchanged = true; //By default, assume that the column precision has changed
282 $decimalchanged = true; //By default, assume that the column decimal has changed
283 $defaultchanged = true; //By default, assume that the column default has changed
284 $notnullchanged = true; //By default, assume that the column notnull has changed
285
286 $from_temp_fields = false; //By default don't assume we are going to use temporal fields
287
288 /// Detect if we are changing the type of the column
289 if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER && substr($oldmetatype, 0, 1) == 'I') ||
290 ($xmldb_field->getType() == XMLDB_TYPE_NUMBER && $oldmetatype == 'N') ||
291 ($xmldb_field->getType() == XMLDB_TYPE_FLOAT && $oldmetatype == 'F') ||
292 ($xmldb_field->getType() == XMLDB_TYPE_CHAR && substr($oldmetatype, 0, 1) == 'C') ||
293 ($xmldb_field->getType() == XMLDB_TYPE_TEXT && substr($oldmetatype, 0, 1) == 'X') ||
294 ($xmldb_field->getType() == XMLDB_TYPE_BINARY && $oldmetatype == 'B')) {
295 $typechanged = false;
296 }
297 /// Detect if precision has changed
298 if (($xmldb_field->getType() == XMLDB_TYPE_TEXT) ||
299 ($xmldb_field->getType() == XMLDB_TYPE_BINARY) ||
300 ($oldlength == -1) ||
301 ($xmldb_field->getLength() == $oldlength)) {
302 $precisionchanged = false;
303 }
304 /// Detect if decimal has changed
305 if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER) ||
306 ($xmldb_field->getType() == XMLDB_TYPE_CHAR) ||
307 ($xmldb_field->getType() == XMLDB_TYPE_TEXT) ||
308 ($xmldb_field->getType() == XMLDB_TYPE_BINARY) ||
309 (!$xmldb_field->getDecimals()) ||
310 (!$olddecimals) ||
311 ($xmldb_field->getDecimals() == $olddecimals)) {
312 $decimalchanged = false;
313 }
314 /// Detect if we are changing the default
315 if (($xmldb_field->getDefault() === null && $olddefault === null) ||
316 ($xmldb_field->getDefault() === $olddefault) || //Check both equality and
317 ("'" . $xmldb_field->getDefault() . "'" === $olddefault)) { //Equality with quotes because ADOdb returns the default with quotes
318 $defaultchanged = false;
319 }
812e363a 320
11b75afe 321 /// Detect if we are changing the nullability
322 if (($xmldb_field->getNotnull() === $oldnotnull)) {
323 $notnullchanged = false;
324 }
325
326 /// If type has changed or precision or decimal has changed and we are in one numeric field
327 /// - create one temp column with the new specs
328 /// - fill the new column with the values from the old one
329 /// - drop the old column
330 /// - rename the temp column to the original name
331 if (($typechanged) || ($oldmetatype == 'N' && ($precisionchanged || $decimalchanged))) {
332 $tempcolname = $xmldb_field->getName() . '_alter_column_tmp';
333 /// Prevent temp field to have both NULL/NOT NULL and DEFAULT constraints
334 $this->alter_column_skip_notnull = true;
335 $this->alter_column_skip_default = true;
336 $xmldb_field->setName($tempcolname);
337 /// Create the temporal column
338 $results = array_merge($results, $this->getAddFieldSQL($xmldb_table, $xmldb_field));
339 /// Copy contents from original col to the temporal one
340 $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = ' . $fieldname;
341 /// Drop the old column
342 $xmldb_field->setName($fieldname); //Set back the original field name
343 $results = array_merge($results, $this->getDropFieldSQL($xmldb_table, $xmldb_field));
344 /// Rename the temp column to the original one
345 $results[] = 'ALTER TABLE ' . $tablename . ' RENAME COLUMN ' . $tempcolname . ' TO ' . $fieldname;
346 /// Mark we have performed one change based in temp fields
347 $from_temp_fields = true;
348 /// Re-enable the notnull and default sections so the general AlterFieldSQL can use it
349 $this->alter_column_skip_notnull = false;
350 $this->alter_column_skip_default = false;
812e363a 351 /// Dissable the type section because we have done it with the temp field
11b75afe 352 $this->alter_column_skip_type = true;
599caff8 353 /// If new field is nullable, nullability hasn't changed
354 if (!$xmldb_field->getNotnull()) {
355 $notnullchanged = false;
356 }
357 /// If new field hasn't default, default hasn't changed
358 if ($xmldb_field->getDefault() === null) {
359 $defaultchanged = false;
360 }
11b75afe 361 }
362
363 /// If type and precision and decimals hasn't changed, prevent the type clause
364 if (!$typechanged && !$precisionchanged && !$decimalchanged) {
365 $this->alter_column_skip_type = true;
366 }
367
368 /// If NULL/NOT NULL hasn't changed
369 /// prevent null clause to be specified
370 if (!$notnullchanged) {
371 $this->alter_column_skip_notnull = true; /// Initially, prevent the notnull clause
372 /// But, if we have used the temp field and the new field is not null, then enforce the not null clause
373 if ($from_temp_fields && $xmldb_field->getNotnull()) {
374 $this->alter_column_skip_notnull = false;
375 }
376 }
377 /// If default hasn't changed
378 /// prevent default clause to be specified
379 if (!$defaultchanged) {
380 $this->alter_column_skip_default = true; /// Initially, prevent the default clause
381 /// But, if we have used the temp field and the new field has default clause, then enforce the default clause
382 if ($from_temp_fields && $default_clause = $this->getDefaultClause($xmldb_field)) {
383 $this->alter_column_skip_default = false;
384 }
385 }
386
387 /// If arriving here, something is not being skiped (type, notnull, default), calculate the standar AlterFieldSQL
388 if (!$this->alter_column_skip_type || !$this->alter_column_skip_notnull || !$this->alter_column_skip_default) {
389 $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field));
390 return $results;
391 }
392
393 /// Finally return results
394 return $results;
19c8321e 395 }
396
b899d9bf 397 /**
398 * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to create its enum
399 * (usually invoked from getModifyEnumSQL()
400 */
401 function getCreateEnumSQL($xmldb_table, $xmldb_field) {
402 /// All we have to do is to create the check constraint
403 return array('ALTER TABLE ' . $this->getTableName($xmldb_table) .
404 ' ADD ' . $this->getEnumExtraSQL($xmldb_table, $xmldb_field));
405 }
406
407 /**
408 * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to drop its enum
409 * (usually invoked from getModifyEnumSQL()
410 */
411 function getDropEnumSQL($xmldb_table, $xmldb_field) {
412 /// All we have to do is to drop the check constraint
413 return array('ALTER TABLE ' . $this->getTableName($xmldb_table) .
414 ' DROP CONSTRAINT ' . $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'ck'));
415 }
416
812e363a 417 /**
418 * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to create its default
419 * (usually invoked from getModifyDefaultSQL()
420 */
421 function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
422 /// Just a wrapper over the getAlterFieldSQL() function for Oracle that
423 /// is capable of handling defaults
424 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
425 }
426
427 /**
428 * Given one XMLDBTable and one XMLDBField, return the SQL statements needded to drop its default
429 * (usually invoked from getModifyDefaultSQL()
430 */
431 function getDropDefaultSQL($xmldb_table, $xmldb_field) {
432 /// Just a wrapper over the getAlterFieldSQL() function for Oracle that
433 /// is capable of handling defaults
434 return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
435 }
436
d7444bfc 437 /**
438 * Returns an array of reserved words (lowercase) for this DB
439 */
440 function getReservedWords() {
6aa7885e 441 /// This file contains the reserved words for Oracle databases
442 /// from http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/ap_keywd.htm
d7444bfc 443 $reserved_words = array (
444 'access', 'add', 'all', 'alter', 'and', 'any',
445 'as', 'asc', 'audit', 'between', 'by', 'char',
446 'check', 'cluster', 'column', 'comment',
447 'compress', 'connect', 'create', 'current',
448 'date', 'decimal', 'default', 'delete', 'desc',
449 'distinct', 'drop', 'else', 'exclusive', 'exists',
450 'file', 'float', 'for', 'from', 'grant', 'group',
451 'having', 'identified', 'immediate', 'in',
452 'increment', 'index', 'initial', 'insert',
453 'integer', 'intersect', 'into', 'is', 'level',
454 'like', 'lock', 'long', 'maxextents', 'minus',
455 'mlslabel', 'mode', 'modify', 'noaudit',
456 'nocompress', 'not', 'nowait', 'null', 'number',
457 'of', 'offline', 'on', 'online', 'option', 'or',
458 'order', 'pctfree', 'prior', 'privileges',
459 'public', 'raw', 'rename', 'resource', 'revoke',
460 'row', 'rowid', 'rownum', 'rows', 'select',
461 'session', 'set', 'share', 'size', 'smallint',
462 'start', 'successful', 'synonym', 'sysdate',
463 'table', 'then', 'to', 'trigger', 'uid', 'union',
464 'unique', 'update', 'user', 'validate', 'values',
465 'varchar', 'varchar2', 'view', 'whenever',
466 'where', 'with'
eef868d1 467 );
d7444bfc 468 return $reserved_words;
469 }
470}
471
472?>