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     public function getCreateIndexSQL($xmldb_table, $xmldb_index) {
 135         if ($error = $xmldb_index->validateDefinition($xmldb_table)) {
 136             throw new coding_exception($error);
 137         }
 138
 139         $indexfields = $this->getEncQuoted($xmldb_index->getFields());
 140
 141         $unique = '';
 142         $suffix = 'ix';
 143         if ($xmldb_index->getUnique()) {
 144             $unique = ' UNIQUE';
 145             $suffix = 'uix';
 146
 147             $nullablefields = $this->get_nullable_fields_in_index($xmldb_table, $xmldb_index);
 148             if ($nullablefields) {
 149                 // If this is a unique index with nullable fields, then we have to
 150                 // apply the work-around from https://community.oracle.com/message/9518046#9518046.
 151                 //
 152                 // For example if you have a unique index on the three columns
 153                 // (required, option1, option2) where the first one is non-null,
 154                 // and the others nullable, then the SQL will end up as
 155                 //
 156                 // CREATE UNIQUE INDEX index_name ON table_name (
 157                 // CASE WHEN option1 IS NOT NULL AND option2 IS NOT NULL THEN required ELSE NULL END,
 158                 // CASE WHEN option1 IS NOT NULL AND option2 IS NOT NULL THEN option1 ELSE NULL END,
 159                 // CASE WHEN option1 IS NOT NULL AND option2 IS NOT NULL THEN option2 ELSE NULL END)
 160                 //
 161                 // Basically Oracle behaves according to the standard if either
 162                 // none of the columns are NULL or all columns contain NULL. Therefore,
 163                 // if any column is NULL, we treat them all as NULL for the index.
 164                 $conditions = [];
 165                 foreach ($nullablefields as $fieldname) {
 166                     $conditions[] = $this->getEncQuoted($fieldname) .
 167                             ' IS NOT NULL';
 168                 }
 169                 $condition = implode(' AND ', $conditions);
 170
 171                 $updatedindexfields = [];
 172                 foreach ($indexfields as $fieldname) {
 173                     $updatedindexfields[] = 'CASE WHEN ' . $condition . ' THEN ' .
 174                             $fieldname . ' ELSE NULL END';
 175                 }
 176                 $indexfields = $updatedindexfields;
 177             }
 178
 179         }
 180
 181         $index = 'CREATE' . $unique . ' INDEX ';
 182         $index .= $this->getNameForObject($xmldb_table->getName(), implode(', ', $xmldb_index->getFields()), $suffix);
 183         $index .= ' ON ' . $this->getTableName($xmldb_table);
 184         $index .= ' (' . implode(', ', $indexfields) . ')';
 185
 186         return array($index);
 187     }
 188
 189     /**
 190      * Given one correct xmldb_table, returns the SQL statements
 191      * to create temporary table (inside one array).
 192      *
 193      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 194      * @return array of sql statements
 195      */
 196     public function getCreateTempTableSQL($xmldb_table) {
 197         $this->temptables->add_temptable($xmldb_table->getName());
 198         $sqlarr = $this->getCreateTableSQL($xmldb_table);
 199         $sqlarr = preg_replace('/^CREATE TABLE (.*)/s', 'CREATE GLOBAL TEMPORARY TABLE $1 ON COMMIT PRESERVE ROWS', $sqlarr);
 200         return $sqlarr;
 201     }
 202
 203     /**
 204      * Given one correct xmldb_table, returns the SQL statements
 205      * to drop it (inside one array).
 206      *
 207      * @param xmldb_table $xmldb_table The table to drop.
 208      * @return array SQL statement(s) for dropping the specified table.
 209      */
 210     public function getDropTableSQL($xmldb_table) {
 211         $sqlarr = parent::getDropTableSQL($xmldb_table);
 212         if ($this->temptables->is_temptable($xmldb_table->getName())) {
 213             array_unshift($sqlarr, "TRUNCATE TABLE ". $this->getTableName($xmldb_table)); // oracle requires truncate before being able to drop a temp table
 214             $this->temptables->delete_temptable($xmldb_table->getName());
 215         }
 216         return $sqlarr;
 217     }
 218
 219     /**
 220      * Given one XMLDB Type, length and decimals, returns the DB proper SQL type.
 221      *
 222      * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants.
 223      * @param int $xmldb_length The length of that data type.
 224      * @param int $xmldb_decimals The decimal places of precision of the data type.
 225      * @return string The DB defined data type.
 226      */
 227     public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
 228
 229         switch ($xmldb_type) {
 230             case XMLDB_TYPE_INTEGER:    // See http://www.acs.ilstu.edu/docs/oracle/server.101/b10759/sql_elements001.htm#sthref86.
 231                 if (empty($xmldb_length)) {
 232                     $xmldb_length = 10;
 233                 }
 234                 $dbtype = 'NUMBER(' .  $xmldb_length . ')';
 235                 break;
 236             case XMLDB_TYPE_FLOAT:
 237             case XMLDB_TYPE_NUMBER:
 238                 $dbtype = $this->number_type;
 239                 if (!empty($xmldb_length)) {
 240                     $dbtype .= '(' . $xmldb_length;
 241                     if (!empty($xmldb_decimals)) {
 242                         $dbtype .= ',' . $xmldb_decimals;
 243                     }
 244                     $dbtype .= ')';
 245                 }
 246                 break;
 247             case XMLDB_TYPE_CHAR:
 248                 // Do not use NVARCHAR2 here because it has hardcoded 1333 char limit,
 249                 // VARCHAR2 allows us to create larger fields that error out later during runtime
 250                 // only when too many non-ascii utf-8 chars present.
 251                 $dbtype = 'VARCHAR2';
 252                 if (empty($xmldb_length)) {
 253                     $xmldb_length='255';
 254                 }
 255                 $dbtype .= '(' . $xmldb_length . ' CHAR)'; // CHAR is required because BYTE is the default
 256                 break;
 257             case XMLDB_TYPE_TEXT:
 258                 $dbtype = 'CLOB';
 259                 break;
 260             case XMLDB_TYPE_BINARY:
 261                 $dbtype = 'BLOB';
 262                 break;
 263             case XMLDB_TYPE_DATETIME:
 264                 $dbtype = 'DATE';
 265                 break;
 266         }
 267         return $dbtype;
 268     }
 269
 270     /**
 271      * Returns the code (array of statements) needed
 272      * to create one sequence for the xmldb_table and xmldb_field passed in.
 273      *
 274      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 275      * @param xmldb_field $xmldb_field The xmldb_field object instance.
 276      * @return array Array of SQL statements to create the sequence.
 277      */
 278     public function getCreateSequenceSQL($xmldb_table, $xmldb_field) {
 279
 280         $results = array();
 281
 282         $sequence_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'seq');
 283
 284         $sequence = "CREATE SEQUENCE $sequence_name START WITH 1 INCREMENT BY 1 NOMAXVALUE CACHE $this->sequence_cache_size";
 285
 286         $results[] = $sequence;
 287
 288         $results = array_merge($results, $this->getCreateTriggerSQL ($xmldb_table, $xmldb_field, $sequence_name));
 289
 290         return $results;
 291     }
 292
 293     /**
 294      * Returns the code needed to create one trigger for the xmldb_table and xmldb_field passed
 295      *
 296      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 297      * @param xmldb_field $xmldb_field The xmldb_field object instance.
 298      * @param string $sequence_name
 299      * @return array Array of SQL statements to create the sequence.
 300      */
 301     public function getCreateTriggerSQL($xmldb_table, $xmldb_field, $sequence_name) {
 302
 303         $trigger_name = $this->getNameForObject($xmldb_table->getName(), $xmldb_field->getName(), 'trg');
 304
 305         $trigger = "CREATE TRIGGER " . $trigger_name;
 306         $trigger.= "\n    BEFORE INSERT";
 307         $trigger.= "\nON " . $this->getTableName($xmldb_table);
 308         $trigger.= "\n    FOR EACH ROW";
 309         $trigger.= "\nBEGIN";
 310         $trigger.= "\n    IF :new." . $this->getEncQuoted($xmldb_field->getName()) . ' IS NULL THEN';
 311         $trigger.= "\n        SELECT " . $sequence_name . '.nextval INTO :new.' . $this->getEncQuoted($xmldb_field->getName()) . " FROM dual;";
 312         $trigger.= "\n    END IF;";
 313         $trigger.= "\nEND;";
 314
 315         return array($trigger);
 316     }
 317
 318     /**
 319      * Returns the code needed to drop one sequence for the xmldb_table and xmldb_field passed
 320      * Can, optionally, specify if the underlying trigger will be also dropped
 321      *
 322      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 323      * @param xmldb_field $xmldb_field The xmldb_field object instance.
 324      * @param bool $include_trigger
 325      * @return array Array of SQL statements to create the sequence.
 326      */
 327     public function getDropSequenceSQL($xmldb_table, $xmldb_field, $include_trigger=false) {
 328
 329         $result = array();
 330
 331         if ($sequence_name = $this->getSequenceFromDB($xmldb_table)) {
 332             $result[] = "DROP SEQUENCE " . $sequence_name;
 333         }
 334
 335         if ($trigger_name = $this->getTriggerFromDB($xmldb_table) && $include_trigger) {
 336             $result[] = "DROP TRIGGER " . $trigger_name;
 337         }
 338
 339         return $result;
 340     }
 341
 342     /**
 343      * Returns the code (array of statements) needed to add one comment to the table.
 344      *
 345      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 346      * @return array Array of SQL statements to add one comment to the table.
 347      */
 348     function getCommentSQL ($xmldb_table) {
 349
 350         $comment = "COMMENT ON TABLE " . $this->getTableName($xmldb_table);
 351         $comment.= " IS '" . $this->addslashes(substr($xmldb_table->getComment(), 0, 250)) . "'";
 352
 353         return array($comment);
 354     }
 355
 356     /**
 357      * Returns the code (array of statements) needed to execute extra statements on table drop
 358      *
 359      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 360      * @return array Array of extra SQL statements to drop a table.
 361      */
 362     public function getDropTableExtraSQL($xmldb_table) {
 363         $xmldb_field = new xmldb_field('id'); // Fields having sequences should be exclusively, id.
 364         return $this->getDropSequenceSQL($xmldb_table, $xmldb_field, false);
 365     }
 366
 367     /**
 368      * Returns the code (array of statements) needed to execute extra statements on table rename.
 369      *
 370      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 371      * @param string $newname The new name for the table.
 372      * @return array Array of extra SQL statements to rename a table.
 373      */
 374     public function getRenameTableExtraSQL($xmldb_table, $newname) {
 375
 376         $results = array();
 377
 378         $xmldb_field = new xmldb_field('id'); // Fields having sequences should be exclusively, id.
 379
 380         $oldseqname = $this->getSequenceFromDB($xmldb_table);
 381         $newseqname = $this->getNameForObject($newname, $xmldb_field->getName(), 'seq');
 382
 383         $oldtriggername = $this->getTriggerFromDB($xmldb_table);
 384         $newtriggername = $this->getNameForObject($newname, $xmldb_field->getName(), 'trg');
 385
 386         // Drop old trigger (first of all)
 387         $results[] = "DROP TRIGGER " . $oldtriggername;
 388
 389         // Rename the sequence, disablig CACHE before and enablig it later
 390         // to avoid consuming of values on rename
 391         $results[] = 'ALTER SEQUENCE ' . $oldseqname . ' NOCACHE';
 392         $results[] = 'RENAME ' . $oldseqname . ' TO ' . $newseqname;
 393         $results[] = 'ALTER SEQUENCE ' . $newseqname . ' CACHE ' . $this->sequence_cache_size;
 394
 395         // Create new trigger
 396         $newt = new xmldb_table($newname);     // Temp table for trigger code generation
 397         $results = array_merge($results, $this->getCreateTriggerSQL($newt, $xmldb_field, $newseqname));
 398
 399         return $results;
 400     }
 401
 402     /**
 403      * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table.
 404      *
 405      * Oracle has some severe limits:
 406      *     - clob and blob fields doesn't allow type to be specified
 407      *     - error is dropped if the null/not null clause is specified and hasn't changed
 408      *     - changes in precision/decimals of numeric fields drop an ORA-1440 error
 409      *
 410      * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 411      * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
 412      * @param string $skip_type_clause The type clause on alter columns, NULL by default.
 413      * @param string $skip_default_clause The default clause on alter columns, NULL by default.
 414      * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
 415      * @return string The field altering SQL statement.
 416      */
 417     public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) {
 418
 419         $skip_type_clause = is_null($skip_type_clause) ? $this->alter_column_skip_type : $skip_type_clause;
 420         $skip_default_clause = is_null($skip_default_clause) ? $this->alter_column_skip_default : $skip_default_clause;
 421         $skip_notnull_clause = is_null($skip_notnull_clause) ? $this->alter_column_skip_notnull : $skip_notnull_clause;
 422
 423         $results = array();     // To store all the needed SQL commands
 424
 425         // Get the quoted name of the table and field
 426         $tablename = $this->getTableName($xmldb_table);
 427         $fieldname = $xmldb_field->getName();
 428
 429         // Take a look to field metadata
 430         $meta = $this->mdb->get_columns($xmldb_table->getName());
 431         $metac = $meta[$fieldname];
 432         $oldmetatype = $metac->meta_type;
 433
 434         $oldlength = $metac->max_length;
 435         // To calculate the oldlength if the field is numeric, we need to perform one extra query
 436         // because ADOdb has one bug here. http://phplens.com/lens/lensforum/msgs.php?id=15883
 437         if ($oldmetatype == 'N') {
 438             $uppertablename = strtoupper($tablename);
 439             $upperfieldname = strtoupper($fieldname);
 440             if ($col = $this->mdb->get_record_sql("SELECT cname, precision
 441                                                      FROM col
 442                                                      WHERE tname = ? AND cname = ?",
 443                                                   array($uppertablename, $upperfieldname))) {
 444                 $oldlength = $col->precision;
 445             }
 446         }
 447         $olddecimals = empty($metac->scale) ? null : $metac->scale;
 448         $oldnotnull = empty($metac->not_null) ? false : $metac->not_null;
 449         $olddefault = empty($metac->default_value) || strtoupper($metac->default_value) == 'NULL' ? null : $metac->default_value;
 450
 451         $typechanged = true;  //By default, assume that the column type has changed
 452         $precisionchanged = true;  //By default, assume that the column precision has changed
 453         $decimalchanged = true;  //By default, assume that the column decimal has changed
 454         $defaultchanged = true;  //By default, assume that the column default has changed
 455         $notnullchanged = true;  //By default, assume that the column notnull has changed
 456
 457         $from_temp_fields = false; //By default don't assume we are going to use temporal fields
 458
 459         // Detect if we are changing the type of the column
 460         if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') ||
 461             ($xmldb_field->getType() == XMLDB_TYPE_NUMBER  && $oldmetatype == 'N') ||
 462             ($xmldb_field->getType() == XMLDB_TYPE_FLOAT   && $oldmetatype == 'F') ||
 463             ($xmldb_field->getType() == XMLDB_TYPE_CHAR    && $oldmetatype == 'C') ||
 464             ($xmldb_field->getType() == XMLDB_TYPE_TEXT    && $oldmetatype == 'X') ||
 465             ($xmldb_field->getType() == XMLDB_TYPE_BINARY  && $oldmetatype == 'B')) {
 466             $typechanged = false;
 467         }
 468         // Detect if precision has changed
 469         if (($xmldb_field->getType() == XMLDB_TYPE_TEXT) ||
 470             ($xmldb_field->getType() == XMLDB_TYPE_BINARY) ||
 471             ($oldlength == -1) ||
 472             ($xmldb_field->getLength() == $oldlength)) {
 473             $precisionchanged = false;
 474         }
 475         // Detect if decimal has changed
 476         if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER) ||
 477             ($xmldb_field->getType() == XMLDB_TYPE_CHAR) ||
 478             ($xmldb_field->getType() == XMLDB_TYPE_TEXT) ||
 479             ($xmldb_field->getType() == XMLDB_TYPE_BINARY) ||
 480             (!$xmldb_field->getDecimals()) ||
 481             (!$olddecimals) ||
 482             ($xmldb_field->getDecimals() == $olddecimals)) {
 483             $decimalchanged = false;
 484         }
 485         // Detect if we are changing the default
 486         if (($xmldb_field->getDefault() === null && $olddefault === null) ||
 487             ($xmldb_field->getDefault() === $olddefault) ||             //Check both equality and
 488             ("'" . $xmldb_field->getDefault() . "'" === $olddefault)) {  //Equality with quotes because ADOdb returns the default with quotes
 489             $defaultchanged = false;
 490         }
 491
 492         // Detect if we are changing the nullability
 493         if (($xmldb_field->getNotnull() === $oldnotnull)) {
 494             $notnullchanged = false;
 495         }
 496
 497         // If type has changed or precision or decimal has changed and we are in one numeric field
 498         //     - create one temp column with the new specs
 499         //     - fill the new column with the values from the old one
 500         //     - drop the old column
 501         //     - rename the temp column to the original name
 502         if (($typechanged) || (($oldmetatype == 'N' || $oldmetatype == 'I')  && ($precisionchanged || $decimalchanged))) {
 503             $tempcolname = $xmldb_field->getName() . '___tmp'; // Short tmp name, surely not conflicting ever
 504             if (strlen($tempcolname) > 30) { // Safeguard we don't excess the 30cc limit
 505                 $tempcolname = 'ongoing_alter_column_tmp';
 506             }
 507             // Prevent temp field to have both NULL/NOT NULL and DEFAULT constraints
 508             $skip_notnull_clause = true;
 509             $skip_default_clause = true;
 510             $xmldb_field->setName($tempcolname);
 511             // Drop the temp column, in case it exists (due to one previous failure in conversion)
 512             // really ugly but we cannot enclose DDL into transaction :-(
 513             if (isset($meta[$tempcolname])) {
 514                 $results = array_merge($results, $this->getDropFieldSQL($xmldb_table, $xmldb_field));
 515             }
 516             // Create the temporal column
 517             $results = array_merge($results, $this->getAddFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_type_clause, $skip_notnull_clause));
 518             // Copy contents from original col to the temporal one
 519
 520             // From TEXT to integer/number we need explicit conversion
 521             if ($oldmetatype == 'X' && $xmldb_field->GetType() == XMLDB_TYPE_INTEGER) {
 522                 $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = CAST(' . $this->mdb->sql_compare_text($fieldname) . ' AS INT)';
 523             } else if ($oldmetatype == 'X' && $xmldb_field->GetType() == XMLDB_TYPE_NUMBER) {
 524                 $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = CAST(' . $this->mdb->sql_compare_text($fieldname) . ' AS NUMBER)';
 525
 526             // Normal cases, implicit conversion
 527             } else {
 528                 $results[] = 'UPDATE ' . $tablename . ' SET ' . $tempcolname . ' = ' . $fieldname;
 529             }
 530             // Drop the old column
 531             $xmldb_field->setName($fieldname); //Set back the original field name
 532             $results = array_merge($results, $this->getDropFieldSQL($xmldb_table, $xmldb_field));
 533             // Rename the temp column to the original one
 534             $results[] = 'ALTER TABLE ' . $tablename . ' RENAME COLUMN ' . $tempcolname . ' TO ' . $fieldname;
 535             // Mark we have performed one change based in temp fields
 536             $from_temp_fields = true;
 537             // Re-enable the notnull and default sections so the general AlterFieldSQL can use it
 538             $skip_notnull_clause = false;
 539             $skip_default_clause = false;
 540             // Disable the type section because we have done it with the temp field
 541             $skip_type_clause = true;
 542             // If new field is nullable, nullability hasn't changed
 543             if (!$xmldb_field->getNotnull()) {
 544                 $notnullchanged = false;
 545             }
 546             // If new field hasn't default, default hasn't changed
 547             if ($xmldb_field->getDefault() === null) {
 548                 $defaultchanged = false;
 549             }
 550         }
 551
 552         // If type and precision and decimals hasn't changed, prevent the type clause
 553         if (!$typechanged && !$precisionchanged && !$decimalchanged) {
 554             $skip_type_clause = true;
 555         }
 556
 557         // If NULL/NOT NULL hasn't changed
 558         // prevent null clause to be specified
 559         if (!$notnullchanged) {
 560             $skip_notnull_clause = true;     // Initially, prevent the notnull clause
 561             // But, if we have used the temp field and the new field is not null, then enforce the not null clause
 562             if ($from_temp_fields &&  $xmldb_field->getNotnull()) {
 563                 $skip_notnull_clause = false;
 564             }
 565         }
 566         // If default hasn't changed
 567         // prevent default clause to be specified
 568         if (!$defaultchanged) {
 569             $skip_default_clause = true;     // Initially, prevent the default clause
 570             // But, if we have used the temp field and the new field has default clause, then enforce the default clause
 571             if ($from_temp_fields) {
 572                 $default_clause = $this->getDefaultClause($xmldb_field);
 573                 if ($default_clause) {
 574                     $skip_notnull_clause = false;
 575                 }
 576             }
 577         }
 578
 579         // If arriving here, something is not being skipped (type, notnull, default), calculate the standard AlterFieldSQL
 580         if (!$skip_type_clause || !$skip_notnull_clause || !$skip_default_clause) {
 581             $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause, $skip_default_clause, $skip_notnull_clause));
 582             return $results;
 583         }
 584
 585         // Finally return results
 586         return $results;
 587     }
 588
 589     /**
 590      * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default
 591      * (usually invoked from getModifyDefaultSQL()
 592      *
 593      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 594      * @param xmldb_field $xmldb_field The xmldb_field object instance.
 595      * @return array Array of SQL statements to create a field's default.
 596      */
 597     public function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
 598         // Just a wrapper over the getAlterFieldSQL() function for Oracle that
 599         // is capable of handling defaults
 600         return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 601     }
 602
 603     /**
 604      * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default
 605      * (usually invoked from getModifyDefaultSQL()
 606      *
 607      * Note that this method may be dropped in future.
 608      *
 609      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 610      * @param xmldb_field $xmldb_field The xmldb_field object instance.
 611      * @return array Array of SQL statements to create a field's default.
 612      *
 613      * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL()
 614      */
 615     public function getDropDefaultSQL($xmldb_table, $xmldb_field) {
 616         // Just a wrapper over the getAlterFieldSQL() function for Oracle that
 617         // is capable of handling defaults
 618         return $this->getAlterFieldSQL($xmldb_table, $xmldb_field);
 619     }
 620
 621     /**
 622      * Given one xmldb_table returns one string with the sequence of the table
 623      * in the table (fetched from DB)
 624      * The sequence name for oracle is calculated by looking the corresponding
 625      * trigger and retrieving the sequence name from it (because sequences are
 626      * independent elements)
 627      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 628      * @return string|bool If no sequence is found, returns false
 629      */
 630     public function getSequenceFromDB($xmldb_table) {
 631
 632          $tablename    = strtoupper($this->getTableName($xmldb_table));
 633          $prefixupper  = strtoupper($this->prefix);
 634          $sequencename = false;
 635
 636         if ($trigger = $this->mdb->get_record_sql("SELECT trigger_name, trigger_body
 637                                                      FROM user_triggers
 638                                                     WHERE table_name = ? AND trigger_name LIKE ?",
 639                                                   array($tablename, "{$prefixupper}%_ID%_TRG"))) {
 640             // If trigger found, regexp it looking for the sequence name
 641             preg_match('/.*SELECT (.*)\.nextval/i', $trigger->trigger_body, $matches);
 642             if (isset($matches[1])) {
 643                 $sequencename = $matches[1];
 644             }
 645         }
 646
 647         return $sequencename;
 648     }
 649
 650     /**
 651      * Given one xmldb_table returns one string with the trigger
 652      * in the table (fetched from DB)
 653      *
 654      * @param xmldb_table $xmldb_table The xmldb_table object instance.
 655      * @return string|bool If no trigger is found, returns false
 656      */
 657     public function getTriggerFromDB($xmldb_table) {
 658
 659         $tablename   = strtoupper($this->getTableName($xmldb_table));
 660         $prefixupper = strtoupper($this->prefix);
 661         $triggername = false;
 662
 663         if ($trigger = $this->mdb->get_record_sql("SELECT trigger_name, trigger_body
 664                                                      FROM user_triggers
 665                                                     WHERE table_name = ? AND trigger_name LIKE ?",
 666                                                   array($tablename, "{$prefixupper}%_ID%_TRG"))) {
 667             $triggername = $trigger->trigger_name;
 668         }
 669
 670         return $triggername;
 671     }
 672
 673     /**
 674      * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg).
 675      *
 676      * (MySQL requires the whole xmldb_table object to be specified, so we add it always)
 677      *
 678      * This is invoked from getNameForObject().
 679      * Only some DB have this implemented.
 680      *
 681      * @param string $object_name The object's name to check for.
 682      * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg).
 683      * @param string $table_name The table's name to check in
 684      * @return bool If such name is currently in use (true) or no (false)
 685      */
 686     public function isNameInUse($object_name, $type, $table_name) {
 687         switch($type) {
 688             case 'ix':
 689             case 'uix':
 690             case 'seq':
 691             case 'trg':
 692                 if ($check = $this->mdb->get_records_sql("SELECT object_name
 693                                                             FROM user_objects
 694                                                            WHERE lower(object_name) = ?", array(strtolower($object_name)))) {
 695                     return true;
 696                 }
 697                 break;
 698             case 'pk':
 699             case 'uk':
 700             case 'fk':
 701             case 'ck':
 702                 if ($check = $this->mdb->get_records_sql("SELECT constraint_name
 703                                                             FROM user_constraints
 704                                                            WHERE lower(constraint_name) = ?", array(strtolower($object_name)))) {
 705                     return true;
 706                 }
 707                 break;
 708         }
 709         return false; //No name in use found
 710     }
 711
 712     /**
 713      * Adds slashes to string.
 714      * @param string $s
 715      * @return string The escaped string.
 716      */
 717     public function addslashes($s) {
 718         // do not use php addslashes() because it depends on PHP quote settings!
 719         $s = str_replace("'",  "''", $s);
 720         return $s;
 721     }
 722
 723     /**
 724      * Returns an array of reserved words (lowercase) for this DB
 725      * @return array An array of database specific reserved words
 726      */
 727     public static function getReservedWords() {
 728         // This file contains the reserved words for Oracle databases
 729         // from http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/ap_keywd.htm
 730         $reserved_words = array (
 731             'access', 'add', 'all', 'alter', 'and', 'any',
 732             'as', 'asc', 'audit', 'between', 'by', 'char',
 733             'check', 'cluster', 'column', 'comment',
 734             'compress', 'connect', 'create', 'current',
 735             'date', 'decimal', 'default', 'delete', 'desc',
 736             'distinct', 'drop', 'else', 'exclusive', 'exists',
 737             'file', 'float', 'for', 'from', 'grant', 'group',
 738             'having', 'identified', 'immediate', 'in',
 739             'increment', 'index', 'initial', 'insert',
 740             'integer', 'intersect', 'into', 'is', 'level',
 741             'like', 'lock', 'long', 'maxextents', 'minus',
 742             'mlslabel', 'mode', 'modify', 'nchar', 'nclob', 'noaudit',
 743             'nocompress', 'not', 'nowait', 'null', 'number', 'nvarchar2',
 744             'of', 'offline', 'on', 'online', 'option', 'or',
 745             'order', 'pctfree', 'prior', 'privileges',
 746             'public', 'raw', 'rename', 'resource', 'revoke',
 747             'row', 'rowid', 'rownum', 'rows', 'select',
 748             'session', 'set', 'share', 'size', 'smallint',
 749             'start', 'successful', 'synonym', 'sysdate',
 750             'table', 'then', 'to', 'trigger', 'uid', 'union',
 751             'unique', 'update', 'user', 'validate', 'values',
 752             'varchar', 'varchar2', 'view', 'whenever',
 753             'where', 'with'
 754         );
 755         return $reserved_words;
 756     }
 757 }