Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

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