[Summary view] [Print] [Text view]

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