lib/ddl/oracle_sql_generator.php

   1 <?php
   2 // This file is part of Moodle - http://moodle.org/
   3 //
   4 // Moodle is free software: you can redistribute it and/or modify
   5 // it under the terms of the GNU General Public License as published by
   6 // the Free Software Foundation, either version 3 of the License, or
   7 // (at your option) any later version.
   8 //
   9 // Moodle is distributed in the hope that it will be useful,
  10 // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12 // GNU General Public License for more details.
  13 //
  14 // You should have received a copy of the GNU General Public License
  15 // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16
  17 /**
  18  * Oracle specific SQL code generator.
  19  *
  20  * @package    core_ddl
  21  * @copyright  1999 onwards Martin Dougiamas     http://dougiamas.com
  22  *             2001-3001 Eloy Lafuente (stronk7) http://contiento.com
  23  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  24  */
  25
  26 defined('MOODLE_INTERNAL') || die();
  27
  28 require_once($CFG->libdir.'/ddl/sql_generator.php');
  29
  30 /**
  31  * This class generate SQL code to be used against Oracle
  32  * It extends XMLDBgenerator so everything can be
  33  * overridden as needed to generate correct SQL.
  34  *
  35  * @package    core_ddl
  36  * @copyright  1999 onwards Martin Dougiamas     http://dougiamas.com
  37  *             2001-3001 Eloy Lafuente (stronk7) http://contiento.com
  38  * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  39  */
  40 class oracle_sql_generator extends sql_generator {
  41
  42     // Only set values that are different from the defaults present in XMLDBgenerator
  43
  44     /**
  45      * @var string To be automatically added at the end of each statement.
  46      * note: Using "/" because the standard ";" isn't good for stored procedures (triggers)
  47      */
  48     public $statement_end = "\n/";
  49
  50     /** @var string Proper type for NUMBER(x) in this DB. */
  51     public $number_type = 'NUMBER';
  52
  53     /**
  54      * @var string To define the default to set for NOT NULLs CHARs without default (null=do nothing).
  55      * note: Using this whitespace here because Oracle doesn't distinguish empty and null! :-(
  56      */
  57     public $default_for_char = ' ';
  58
  59     /** @var bool To specify if the generator must use some DEFAULT clause to drop defaults.*/
  60     public $drop_default_value_required = true;
  61
  62     /** @var string The DEFAULT clause required to drop defaults.*/
  63     public $drop_default_value = null;
  64
  65     /** @var bool To decide if the default clause of each field must go after the null clause.*/
  66     public $default_after_null = false;
  67
  68     /** @var bool True if the generator needs to add extra code to generate the sequence fields.*/
  69     public $sequence_extra_code = true;
  70
  71     /** @var string The particular name for inline sequences in this generator.*/
  72     public $sequence_name = '';
  73
  74     /** @var string The SQL template to alter columns where the 'TABLENAME' and 'COLUMNSPECS' keywords are dynamically replaced.*/
  75     public $alter_column_sql = 'ALTER TABLE TABLENAME MODIFY (COLUMNSPECS)';
  76
  77     /** @var int var ugly Oracle hack - size of the sequences values cache (20 = Default)*/
  78     public $sequence_cache_size = 20;
  79
  80     /**
  81      * Reset a sequence to the id field of a table.
  82      *
  83      * @param xmldb_table|string $table name of table or the table object.
  84      * @return array of sql statements
  85      */
  86     public function getResetSequenceSQL($table) {
  87
  88         if (is_string($table)) {
  89             $tablename = $table;
  90             $xmldb_table = new xmldb_table($tablename);
  91         } else {
  92             $tablename = $table->getName();
  93             $xmldb_table = $table;
  94         }
  95         // From http://www.acs.ilstu.edu/docs/oracle/server.101/b10759/statements_2011.htm
  96         $value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'.$tablename.'}');
  97         $value++;
  98
  99         $seqname = $this->getSequenceFromDB($xmldb_table);
 100
 101         if (!$seqname) {
 102             // Fallback, seqname not found, something is wrong. Inform and use the alternative getNameForObject() method
 103             $seqname = $this->getNameForObject($table, 'id', 'seq');
 104         }
 105
 106         return array ("DROP SEQUENCE $seqname",
 107                       "CREATE SEQUENCE $seqname START WITH $value INCREMENT BY 1 NOMAXVALUE CACHE $this->sequence_cache_size");
 108     }
 109
 110     /**
 111      * Given one xmldb_table, returns it's correct name, depending of all the parametrization
 112      * Overridden to allow change of names in temp tables
 113      *
 114      * @param xmldb_table table whose name we want
 115      * @param boolean to specify if the name must be quoted (if reserved word, only!)
 116      * @return string the correct name of the table
 117      */
 118     public function getTableName(xmldb_table $xmldb_table, $quoted=true) {
 119         // Get the name, supporting special oci names for temp tables
 120         if ($this->temptables->is_temptable($xmldb_table->getName())) {
 121             $tablename = $this->temptables->get_correct_name($xmldb_table->getName());
 122         } else {
 123             $tablename = $this->prefix . $xmldb_table->getName();
 124         }
 125
 126         // Apply quotes optionally
 127         if ($quoted) {
 128             $tablename = $this->getEncQuoted($tablename);
 129         }
 130
 131         return $tablename;
 132     }
 133
 134     /**
 135      * Given one correct xmldb_table, returns the SQL statements
 136      * to create temporary table (inside one array).
 137      *
 138      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 139      * @return array of sql statements
 140      */
 141     public function getCreateTempTableSQL($xmldb_table) {
 142         $this->temptables->add_temptable($xmldb_table->getName());
 143         $sqlarr = $this->getCreateTableSQL($xmldb_table);
 144         $sqlarr = preg_replace('/^CREATE TABLE (.*)/s', 'CREATE GLOBAL TEMPORARY TABLE $1 ON COMMIT PRESERVE ROWS', $sqlarr);
 145         return $sqlarr;
 146     }
 147
 148     /**
 149      * Given one correct xmldb_table, returns the SQL statements
 150      * to drop it (inside one array).
 151      *
 152      * @param xmldb_table $xmldb_table The table to drop.
 153      * @return array SQL statement(s) for dropping the specified table.
 154      */
 155     public function getDropTableSQL($xmldb_table) {
 156         $sqlarr = parent::getDropTableSQL($xmldb_table);
 157         if ($this->temptables->is_temptable($xmldb_table->getName())) {
 158             array_unshift($sqlarr, "TRUNCATE TABLE ". $this->getTableName($xmldb_table)); // oracle requires truncate before being able to drop a temp table
 159             $this->temptables->delete_temptable($xmldb_table->getName());
 160         }
 161         return $sqlarr;
 162     }
 163
 164     /**
 165      * Given one XMLDB Type, length and decimals, returns the DB proper SQL type.
 166      *
 167      * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants.
 168      * @param int $xmldb_length The length of that data type.
 169      * @param int $xmldb_decimals The decimal places of precision of the data type.
 170      * @return string The DB defined data type.
 171      */
 172     public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
 173
 174         switch ($xmldb_type) {
 175             case XMLDB_TYPE_INTEGER:    // See http://www.acs.ilstu.edu/docs/oracle/server.101/b10759/sql_elements001.htm#sthref86.
 176                 if (empty($xmldb_length)) {
 177                     $xmldb_length = 10;
 178                 }
 179                 $dbtype = 'NUMBER(' .  $xmldb_length . ')';
 180                 break;
 181             case XMLDB_TYPE_FLOAT:
 182             case XMLDB_TYPE_NUMBER:
 183                 $dbtype = $this->number_type;
 184                 if (!empty($xmldb_length)) {
 185                     $dbtype .= '(' . $xmldb_length;
 186                     if (!empty($xmldb_decimals)) {
 187                         $dbtype .= ',' . $xmldb_decimals;
 188                     }
 189                     $dbtype .= ')';
 190                 }
 191                 break;
 192             case XMLDB_TYPE_CHAR:
 193                 // Do not use NVARCHAR2 here because it has hardcoded 1333 char limit,
 194                 // VARCHAR2 allows us to create larger fields that error out later during runtime
 195                 // only when too many non-ascii utf-8 chars present.
 196                 $dbtype = 'VARCHAR2';
 197                 if (empty($xmldb_length)) {
 198                     $xmldb_length='255';
 199                 }
 200                 $dbtype .= '(' . $xmldb_length . ' CHAR)'; // CHAR is required because BYTE is the default
 201                 break;
 202             case XMLDB_TYPE_TEXT:
 203                 $dbtype = 'CLOB';
 204                 break;
 205             case XMLDB_TYPE_BINARY:
 206                 $dbtype = 'BLOB';
 207                 break;
 208             case XMLDB_TYPE_DATETIME:
 209                 $dbtype = 'DATE';
 210                 break;
 211         }
 212         return $dbtype;
 213     }
 214
 215     /**
 216      * Returns the code (array of statements) needed
 217      * to create one sequence for the xmldb_table and xmldb_field passed in.
 218      *
 219      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 220      * @param xmldb_field $xmldb_field The xmldb_field object instance.
 221      * @return array Array of SQL statements to create the sequence.
 222      */
 223     public function getCreateSequenceSQL($xmldb_table, $xmldb_field) {
 224
 225         $results = array();
 226
 227         $sequence_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'seq');
 228
 229         $sequence = "CREATE SEQUENCE $sequence_name START WITH 1 INCREMENT BY 1 NOMAXVALUE CACHE $this->sequence_cache_size";
 230
 231         $results[] = $sequence;
 232
 233         $results = array_merge($results, $this->getCreateTriggerSQL ($xmldb_table, $xmldb_field, $sequence_name));
 234
 235         return $results;
 236     }
 237
 238     /**
 239      * Returns the code needed to create one trigger for the xmldb_table and xmldb_field passed
 240      *
 241      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 242      * @param xmldb_field $xmldb_field The xmldb_field object instance.
 243      * @param string $sequence_name
 244      * @return array Array of SQL statements to create the sequence.
 245      */
 246     public function getCreateTriggerSQL($xmldb_table, $xmldb_field, $sequence_name) {
 247
 248         $trigger_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'trg');
 249
 250         $trigger = "CREATE TRIGGER " . $trigger_name;
 251         $trigger.= "\n    BEFORE INSERT";
 252         $trigger.= "\nON " . $this->getTableName($xmldb_table);
 253         $trigger.= "\n    FOR EACH ROW";
 254         $trigger.= "\nBEGIN";
 255         $trigger.= "\n    IF :new." . $this->getEncQuoted($xmldb_field->getName()) . ' IS NULL THEN';
 256         $trigger.= "\n        SELECT " . $sequence_name . '.nextval INTO :new.' . $this->getEncQuoted($xmldb_field->getName()) . " FROM dual;";
 257         $trigger.= "\n    END IF;";
 258         $trigger.= "\nEND;";
 259
 260         return array($trigger);
 261     }
 262
 263     /**
 264      * Returns the code needed to drop one sequence for the xmldb_table and xmldb_field passed
 265      * Can, optionally, specify if the underlying trigger will be also dropped
 266      *
 267      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 268      * @param xmldb_field $xmldb_field The xmldb_field object instance.
 269      * @param bool $include_trigger
 270      * @return array Array of SQL statements to create the sequence.
 271      */
 272     public function getDropSequenceSQL($xmldb_table, $xmldb_field, $include_trigger=false) {
 273
 274         $result = array();
 275
 276         if ($sequence_name = $this->getSequenceFromDB($xmldb_table)) {
 277             $result[] = "DROP SEQUENCE " . $sequence_name;
 278         }
 279
 280         if ($trigger_name = $this->getTriggerFromDB($xmldb_table) && $include_trigger) {
 281             $result[] = "DROP TRIGGER " . $trigger_name;
 282         }
 283
 284         return $result;
 285     }
 286
 287     /**
 288      * Returns the code (array of statements) needed to add one comment to the table.
 289      *
 290      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 291      * @return array Array of SQL statements to add one comment to the table.
 292      */
 293     function getCommentSQL ($xmldb_table) {
 294
 295         $comment = "COMMENT ON TABLE " . $this->getTableName($xmldb_table);
 296         $comment.= " IS '" . $this->addslashes(substr($xmldb_table->getComment(), 0, 250)) . "'";
 297
 298         return array($comment);
 299     }
 300
 301     /**
 302      * Returns the code (array of statements) needed to execute extra statements on table drop
 303      *
 304      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 305      * @return array Array of extra SQL statements to drop a table.
 306      */
 307     public function getDropTableExtraSQL($xmldb_table) {
 308         $xmldb_field = new xmldb_field('id'); // Fields having sequences should be exclusively, id.
 309         return $this->getDropSequenceSQL($xmldb_table, $xmldb_field, false);
 310     }
 311
 312     /**
 313      * Returns the code (array of statements) needed to execute extra statements on table rename.
 314      *
 315      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 316      * @param string $newname The new name for the table.
 317      * @return array Array of extra SQL statements to rename a table.
 318      */
 319     public function getRenameTableExtraSQL($xmldb_table, $newname) {
 320
 321         $results = array();
 322
 323         $xmldb_field = new xmldb_field('id'); // Fields having sequences should be exclusively, id.
 324
 325         $oldseqname = $this->getSequenceFromDB($xmldb_table);
 326         $newseqname = $this->getNameForObject($newname, $xmldb_field->getName(), 'seq');
 327
 328         $oldtriggername = $this->getTriggerFromDB($xmldb_table);
 329         $newtriggername = $this->getNameForObject($newname, $xmldb_field->getName(), 'trg');
 330
 331         // Drop old trigger (first of all)
 332         $results[] = "DROP TRIGGER " . $oldtriggername;
 333
 334         // Rename the sequence, disablig CACHE before and enablig it later
 335         // to avoid consuming of values on rename
 336         $results[] = 'ALTER SEQUENCE ' . $oldseqname . ' NOCACHE';
 337         $results[] = 'RENAME ' . $oldseqname . ' TO ' . $newseqname;
 338         $results[] = 'ALTER SEQUENCE ' . $newseqname . ' CACHE ' . $this->sequence_cache_size;
 339
 340         // Create new trigger
 341         $newt = new xmldb_table($newname);     // Temp table for trigger code generation
 342         $results = array_merge($results, $this->getCreateTriggerSQL($newt, $xmldb_field, $newseqname));
 343
 344         return $results;
 345     }
 346
 347     /**
 348      * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table.
 349      *
 350      * Oracle has some severe limits:
 351      *     - clob and blob fields doesn't allow type to be specified
 352      *     - error is dropped if the null/not null clause is specified and hasn't changed
 353      *     - changes in precision/decimals of numeric fields drop an ORA-1440 error
 354      *
 355      * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 356      * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
 357      * @param string $skip_type_clause The type clause on alter columns, NULL by default.
 358      * @param string $skip_default_clause The default clause on alter columns, NULL by default.
 359      * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
 360      * @return string The field altering SQL statement.
 361      */
 362     public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) {
 363
 364         $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause;
 365         $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause;
 366         $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause;
 367
 368         $results = array();     // To store all the needed SQL commands
 369
 370         // Get the quoted name of the table and field
 371         $tablename = $this->getTableName($xmldb_table);
 372         $fieldname = $xmldb_field->getName();
 373
 374         // Take a look to field metadata
 375         $meta = $this->mdb->get_columns($xmldb_table->getName());
 376         $metac = $meta[$fieldname];
 377         $oldmetatype = $metac->meta_type;
 378
 379         $oldlength = $metac->max_length;
 380         // To calculate the oldlength if the field is numeric, we need to perform one extra query
 381         // because ADOdb has one bug here. http://phplens.com/lens/lensforum/msgs.php?id=15883
 382         if ($oldmetatype == 'N') {
 383             $uppertablename = strtoupper($tablename);
 384             $upperfieldname = strtoupper($fieldname);
 385             if ($col = $this->mdb->get_record_sql("SELECT cname, precision
 386                                                      FROM col
 387                                                      WHERE tname = ? AND cname = ?",
 388                                                   array($uppertablename, $upperfieldname))) {
 389                 $oldlength = $col->precision;
 390             }
 391         }
 392         $olddecimals = empty($metac->scale) ? null : $metac->scale;
 393         $oldnotnull = empty($metac->not_null) ? false : $metac->not_null;
 394         $olddefault = empty($metac->default_value) || strtoupper($metac->default_value) == 'NULL' ? null : $metac->default_value;
 395
 396         $typechanged = true;  //By default, assume that the column type has changed
 397         $precisionchanged = true;  //By default, assume that the column precision has changed
 398         $decimalchanged = true;  //By default, assume that the column decimal has changed
 399         $defaultchanged = true;  //By default, assume that the column default has changed
 400         $notnullchanged = true;  //By default, assume that the column notnull has changed
 401
 402         $from_temp_fields = false; //By default don't assume we are going to use temporal fields
 403
 404         // Detect if we are changing the type of the column
 405         if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') ||
 406             ($xmldb_field->getType() == XMLDB_TYPE_NUMBER  && $oldmetatype == 'N') ||
 407             ($xmldb_field->getType() == XMLDB_TYPE_FLOAT   && $oldmetatype == 'F') ||
 408             ($xmldb_field->getType() == XMLDB_TYPE_CHAR    && $oldmetatype == 'C') ||
 409             ($xmldb_field->getType() == XMLDB_TYPE_TEXT    && $oldmetatype == 'X') ||
 410             ($xmldb_field->getType() == XMLDB_TYPE_BINARY  && $oldmetatype == 'B')) {
 411             $typechanged = false;
 412         }
 413         // Detect if precision has changed
 414         if (($xmldb_field->getType() == XMLDB_TYPE_TEXT) ||
 415             ($xmldb_field->getType() == XMLDB_TYPE_BINARY) ||
 416             ($oldlength == -1) ||
 417             ($xmldb_field->getLength() == $oldlength)) {
 418             $precisionchanged = false;
 419         }
 420         // Detect if decimal has changed
 421         if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER) ||
 422             ($xmldb_field->getType() == XMLDB_TYPE_CHAR) ||
 423             ($xmldb_field->getType() == XMLDB_TYPE_TEXT) ||
 424             ($xmldb_field->getType() == XMLDB_TYPE_BINARY) ||
 425             (!$xmldb_field->getDecimals()) ||
 426             (!$olddecimals) ||
 427             ($xmldb_field->getDecimals() == $olddecimals)) {
 428             $decimalchanged = false;
 429         }
 430         // Detect if we are changing the default
 431         if (($xmldb_field->getDefault() === null && $olddefault === null) ||
 432             ($xmldb_field->getDefault() === $olddefault) ||             //Check both equality and
 433             ("'" . $xmldb_field->getDefault() . "'" === $olddefault)) {  //Equality with quotes because ADOdb returns the default with quotes
 434             $defaultchanged = false;
 435         }
 436
 437         // Detect if we are changing the nullability
 438         if (($xmldb_field->getNotnull() === $oldnotnull)) {
 439             $notnullchanged = false;
 440         }
 441
 442         // If type has changed or precision or decimal has changed and we are in one numeric field
 443         //     - create one temp column with the new specs
 444         //     - fill the new column with the values from the old one
 445         //     - drop the old column
 446         //     - rename the temp column to the original name
 447         if (($typechanged) || (($oldmetatype == 'N' || $oldmetatype == 'I')  && ($precisionchanged || $decimalchanged))) {
 448             $tempcolname = $xmldb_field->getName() . '___tmp'; // Short tmp name, surely not conflicting ever
 449             if (strlen($tempcolname) > 30) { // Safeguard we don't excess the 30cc limit
 450                 $tempcolname = 'ongoing_alter_column_tmp';
 451             }
 452             // Prevent temp field to have both NULL/NOT NULL and DEFAULT constraints
 453             $skip_notnull_clause = true;
 454             $skip_default_clause = true;
 455             $xmldb_field->setName($tempcolname);
 456             // Drop the temp column, in case it exists (due to one previous failure in conversion)
 457             // really ugly but we cannot enclose DDL into transaction :-(
 458             if (isset($meta[$tempcolname])) {
 459                 $results = array_merge($results, $this->getDropFieldSQL($xmldb_table, $xmldb_field));
 460             }
 461             // Create the temporal column
 462             $results = array_merge($results, $this->getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_type_clause, $skip_notnull_clause));
 463             // Copy contents from original col to the temporal one
 464
 465             // From TEXT to integer/number we need explicit conversion
 466             if ($oldmetatype == 'X' && $xmldb_field->GetType() == XMLDB_TYPE_INTEGER) {
 467                 $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = CAST(' . $this->mdb->sql_compare_text($fieldname) . ' AS INT)';
 468             } else if ($oldmetatype == 'X' && $xmldb_field->GetType() == XMLDB_TYPE_NUMBER) {
 469                 $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = CAST(' . $this->mdb->sql_compare_text($fieldname) . ' AS NUMBER)';
 470
 471             // Normal cases, implicit conversion
 472             } else {
 473                 $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = ' . $fieldname;
 474             }
 475             // Drop the old column
 476             $xmldb_field->setName($fieldname); //Set back the original field name
 477             $results = array_merge($results, $this->getDropFieldSQL($xmldb_table, $xmldb_field));
 478             // Rename the temp column to the original one
 479             $results[] = 'ALTER TABLE ' . $tablename . ' RENAME COLUMN ' . $tempcolname . ' TO ' . $fieldname;
 480             // Mark we have performed one change based in temp fields
 481             $from_temp_fields = true;
 482             // Re-enable the notnull and default sections so the general AlterFieldSQL can use it
 483             $skip_notnull_clause = false;
 484             $skip_default_clause = false;
 485             // Disable the type section because we have done it with the temp field
 486             $skip_type_clause = true;
 487             // If new field is nullable, nullability hasn't changed
 488             if (!$xmldb_field->getNotnull()) {
 489                 $notnullchanged = false;
 490             }
 491             // If new field hasn't default, default hasn't changed
 492             if ($xmldb_field->getDefault() === null) {
 493                 $defaultchanged = false;
 494             }
 495         }
 496
 497         // If type and precision and decimals hasn't changed, prevent the type clause
 498         if (!$typechanged && !$precisionchanged && !$decimalchanged) {
 499             $skip_type_clause = true;
 500         }
 501
 502         // If NULL/NOT NULL hasn't changed
 503         // prevent null clause to be specified
 504         if (!$notnullchanged) {
 505             $skip_notnull_clause = true;     // Initially, prevent the notnull clause
 506             // But, if we have used the temp field and the new field is not null, then enforce the not null clause
 507             if ($from_temp_fields &&  $xmldb_field->getNotnull()) {
 508                 $skip_notnull_clause = false;
 509             }
 510         }
 511         // If default hasn't changed
 512         // prevent default clause to be specified
 513         if (!$defaultchanged) {
 514             $skip_default_clause = true;     // Initially, prevent the default clause
 515             // But, if we have used the temp field and the new field has default clause, then enforce the default clause
 516             if ($from_temp_fields) {
 517                 $default_clause = $this->getDefaultClause($xmldb_field);
 518                 if ($default_clause) {
 519                     $skip_notnull_clause = false;
 520                 }
 521             }
 522         }
 523
 524         // If arriving here, something is not being skipped (type, notnull, default), calculate the standard AlterFieldSQL
 525         if (!$skip_type_clause || !$skip_notnull_clause || !$skip_default_clause) {
 526             $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_default_clause, $skip_notnull_clause));
 527             return $results;
 528         }
 529
 530         // Finally return results
 531         return $results;
 532     }
 533
 534     /**
 535      * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default
 536      * (usually invoked from getModifyDefaultSQL()
 537      *
 538      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 539      * @param xmldb_field $xmldb_field The xmldb_field object instance.
 540      * @return array Array of SQL statements to create a field's default.
 541      */
 542     public function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
 543         // Just a wrapper over the getAlterFieldSQL() function for Oracle that
 544         // is capable of handling defaults
 545         return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 546     }
 547
 548     /**
 549      * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default
 550      * (usually invoked from getModifyDefaultSQL()
 551      *
 552      * Note that this method may be dropped in future.
 553      *
 554      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 555      * @param xmldb_field $xmldb_field The xmldb_field object instance.
 556      * @return array Array of SQL statements to create a field's default.
 557      *
 558      * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL()
 559      */
 560     public function getDropDefaultSQL($xmldb_table, $xmldb_field) {
 561         // Just a wrapper over the getAlterFieldSQL() function for Oracle that
 562         // is capable of handling defaults
 563         return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 564     }
 565
 566     /**
 567      * Given one xmldb_table returns one string with the sequence of the table
 568      * in the table (fetched from DB)
 569      * The sequence name for oracle is calculated by looking the corresponding
 570      * trigger and retrieving the sequence name from it (because sequences are
 571      * independent elements)
 572      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 573      * @return string|bool If no sequence is found, returns false
 574      */
 575     public function getSequenceFromDB($xmldb_table) {
 576
 577          $tablename    = strtoupper($this->getTableName($xmldb_table));
 578          $prefixupper  = strtoupper($this->prefix);
 579          $sequencename = false;
 580
 581         if ($trigger = $this->mdb->get_record_sql("SELECT trigger_name, trigger_body
 582                                                      FROM user_triggers
 583                                                     WHERE table_name = ? AND trigger_name LIKE ?",
 584                                                   array($tablename, "{$prefixupper}%_ID%_TRG"))) {
 585             // If trigger found, regexp it looking for the sequence name
 586             preg_match('/.*SELECT (.*)\.nextval/i', $trigger->trigger_body, $matches);
 587             if (isset($matches[1])) {
 588                 $sequencename = $matches[1];
 589             }
 590         }
 591
 592         return $sequencename;
 593     }
 594
 595     /**
 596      * Given one xmldb_table returns one string with the trigger
 597      * in the table (fetched from DB)
 598      *
 599      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 600      * @return string|bool If no trigger is found, returns false
 601      */
 602     public function getTriggerFromDB($xmldb_table) {
 603
 604         $tablename   = strtoupper($this->getTableName($xmldb_table));
 605         $prefixupper = strtoupper($this->prefix);
 606         $triggername = false;
 607
 608         if ($trigger = $this->mdb->get_record_sql("SELECT trigger_name, trigger_body
 609                                                      FROM user_triggers
 610                                                     WHERE table_name = ? AND trigger_name LIKE ?",
 611                                                   array($tablename, "{$prefixupper}%_ID%_TRG"))) {
 612             $triggername = $trigger->trigger_name;
 613         }
 614
 615         return $triggername;
 616     }
 617
 618     /**
 619      * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg).
 620      *
 621      * (MySQL requires the whole xmldb_table object to be specified, so we add it always)
 622      *
 623      * This is invoked from getNameForObject().
 624      * Only some DB have this implemented.
 625      *
 626      * @param string $object_name The object's name to check for.
 627      * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg).
 628      * @param string $table_name The table's name to check in
 629      * @return bool If such name is currently in use (true) or no (false)
 630      */
 631     public function isNameInUse($object_name, $type, $table_name) {
 632         switch($type) {
 633             case 'ix':
 634             case 'uix':
 635             case 'seq':
 636             case 'trg':
 637                 if ($check = $this->mdb->get_records_sql("SELECT object_name
 638                                                             FROM user_objects
 639                                                            WHERE lower(object_name) = ?", array(strtolower($object_name)))) {
 640                     return true;
 641                 }
 642                 break;
 643             case 'pk':
 644             case 'uk':
 645             case 'fk':
 646             case 'ck':
 647                 if ($check = $this->mdb->get_records_sql("SELECT constraint_name
 648                                                             FROM user_constraints
 649                                                            WHERE lower(constraint_name) = ?", array(strtolower($object_name)))) {
 650                     return true;
 651                 }
 652                 break;
 653         }
 654         return false; //No name in use found
 655     }
 656
 657     /**
 658      * Adds slashes to string.
 659      * @param string $s
 660      * @return string The escaped string.
 661      */
 662     public function addslashes($s) {
 663         // do not use php addslashes() because it depends on PHP quote settings!
 664         $s = str_replace("'",  "''", $s);
 665         return $s;
 666     }
 667
 668     /**
 669      * Returns an array of reserved words (lowercase) for this DB
 670      * @return array An array of database specific reserved words
 671      */
 672     public static function getReservedWords() {
 673         // This file contains the reserved words for Oracle databases
 674         // from http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/ap_keywd.htm
 675         $reserved_words = array (
 676             'access', 'add', 'all', 'alter', 'and', 'any',
 677             'as', 'asc', 'audit', 'between', 'by', 'char',
 678             'check', 'cluster', 'column', 'comment',
 679             'compress', 'connect', 'create', 'current',
 680             'date', 'decimal', 'default', 'delete', 'desc',
 681             'distinct', 'drop', 'else', 'exclusive', 'exists',
 682             'file', 'float', 'for', 'from', 'grant', 'group',
 683             'having', 'identified', 'immediate', 'in',
 684             'increment', 'index', 'initial', 'insert',
 685             'integer', 'intersect', 'into', 'is', 'level',
 686             'like', 'lock', 'long', 'maxextents', 'minus',
 687             'mlslabel', 'mode', 'modify', 'nchar', 'nclob', 'noaudit',
 688             'nocompress', 'not', 'nowait', 'null', 'number', 'nvarchar2',
 689             'of', 'offline', 'on', 'online', 'option', 'or',
 690             'order', 'pctfree', 'prior', 'privileges',
 691             'public', 'raw', 'rename', 'resource', 'revoke',
 692             'row', 'rowid', 'rownum', 'rows', 'select',
 693             'session', 'set', 'share', 'size', 'smallint',
 694             'start', 'successful', 'synonym', 'sysdate',
 695             'table', 'then', 'to', 'trigger', 'uid', 'union',
 696             'unique', 'update', 'user', 'validate', 'values',
 697             'varchar', 'varchar2', 'view', 'whenever',
 698             'where', 'with'
 699         );
 700         return $reserved_words;
 701     }
 702 }