Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.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   * MSSQL 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 MSSQL
  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 mssql_sql_generator extends sql_generator {
  41  
  42      // Only set values that are different from the defaults present in XMLDBgenerator
  43  
  44      /** @var string To be automatically added at the end of each statement. */
  45      public $statement_end = "\ngo";
  46  
  47      /** @var string Proper type for NUMBER(x) in this DB. */
  48      public $number_type = 'DECIMAL';
  49  
  50      /** @var string To define the default to set for NOT NULLs CHARs without default (null=do nothing).*/
  51      public $default_for_char = '';
  52  
  53      /**
  54       * @var bool To force the generator if NULL clauses must be specified. It shouldn't be necessary.
  55       * note: some mssql drivers require them or everything is created as NOT NULL :-(
  56       */
  57      public $specify_nulls = true;
  58  
  59      /** @var bool True if the generator needs to add extra code to generate the sequence fields.*/
  60      public $sequence_extra_code = false;
  61  
  62      /** @var string The particular name for inline sequences in this generator.*/
  63      public $sequence_name = 'IDENTITY(1,1)';
  64  
  65      /** @var bool To avoid outputting the rest of the field specs, leaving only the name and the sequence_name returned.*/
  66      public $sequence_only = false;
  67  
  68      /** @var bool True if the generator needs to add code for table comments.*/
  69      public $add_table_comments = false;
  70  
  71      /** @var string Characters to be used as concatenation operator.*/
  72      public $concat_character = '+';
  73  
  74      /** @var string SQL sentence to rename one table, both 'OLDNAME' and 'NEWNAME' keywords are dynamically replaced.*/
  75      public $rename_table_sql = "sp_rename 'OLDNAME', 'NEWNAME'";
  76  
  77      /** @var string SQL sentence to rename one column where 'TABLENAME', 'OLDFIELDNAME' and 'NEWFIELDNAME' keywords are dynamically replaced.*/
  78      public $rename_column_sql = "sp_rename 'TABLENAME.OLDFIELDNAME', 'NEWFIELDNAME', 'COLUMN'";
  79  
  80      /** @var string SQL sentence to drop one index where 'TABLENAME', 'INDEXNAME' keywords are dynamically replaced.*/
  81      public $drop_index_sql = 'DROP INDEX TABLENAME.INDEXNAME';
  82  
  83      /** @var string SQL sentence to rename one index where 'TABLENAME', 'OLDINDEXNAME' and 'NEWINDEXNAME' are dynamically replaced.*/
  84      public $rename_index_sql = "sp_rename 'TABLENAME.OLDINDEXNAME', 'NEWINDEXNAME', 'INDEX'";
  85  
  86      /** @var string SQL sentence to rename one key 'TABLENAME', 'OLDKEYNAME' and 'NEWKEYNAME' are dynamically replaced.*/
  87      public $rename_key_sql = null;
  88  
  89      /**
  90       * Reset a sequence to the id field of a table.
  91       *
  92       * @param xmldb_table|string $table name of table or the table object.
  93       * @return array of sql statements
  94       */
  95      public function getResetSequenceSQL($table) {
  96  
  97          if (is_string($table)) {
  98              $table = new xmldb_table($table);
  99          }
 100  
 101          $value = (int)$this->mdb->get_field_sql('SELECT MAX(id) FROM {'. $table->getName() . '}');
 102          $sqls = array();
 103  
 104          // MSSQL has one non-consistent behavior to create the first identity value, depending
 105          // if the table has been truncated or no. If you are really interested, you can find the
 106          // whole description of the problem at:
 107          //     http://www.justinneff.com/archive/tag/dbcc-checkident
 108          if ($value == 0) {
 109              // truncate to get consistent result from reseed
 110              $sqls[] = "TRUNCATE TABLE " . $this->getTableName($table);
 111              $value = 1;
 112          }
 113  
 114          // From http://msdn.microsoft.com/en-us/library/ms176057.aspx
 115          $sqls[] = "DBCC CHECKIDENT ('" . $this->getTableName($table) . "', RESEED, $value)";
 116          return $sqls;
 117      }
 118  
 119      /**
 120       * Given one xmldb_table, returns it's correct name, depending of all the parametrization
 121       * Overridden to allow change of names in temp tables
 122       *
 123       * @param xmldb_table table whose name we want
 124       * @param boolean to specify if the name must be quoted (if reserved word, only!)
 125       * @return string the correct name of the table
 126       */
 127      public function getTableName(xmldb_table $xmldb_table, $quoted=true) {
 128          // Get the name, supporting special mssql names for temp tables
 129          if ($this->temptables->is_temptable($xmldb_table->getName())) {
 130              $tablename = $this->temptables->get_correct_name($xmldb_table->getName());
 131          } else {
 132              $tablename = $this->prefix . $xmldb_table->getName();
 133          }
 134  
 135          // Apply quotes optionally
 136          if ($quoted) {
 137              $tablename = $this->getEncQuoted($tablename);
 138          }
 139  
 140          return $tablename;
 141      }
 142  
 143      public function getCreateIndexSQL($xmldb_table, $xmldb_index) {
 144          list($indexsql) = parent::getCreateIndexSQL($xmldb_table, $xmldb_index);
 145  
 146          // Unique indexes need to work-around non-standard SQL server behaviour.
 147          if ($xmldb_index->getUnique()) {
 148              // Find any nullable columns. We need to add a
 149              // WHERE field IS NOT NULL to the index definition for each one.
 150              //
 151              // For example if you have a unique index on the three columns
 152              // (required, option1, option2) where the first one is non-null,
 153              // and the others nullable, then the SQL will end up as
 154              //
 155              // CREATE UNIQUE INDEX index_name ON table_name (required, option1, option2)
 156              // WHERE option1 IS NOT NULL AND option2 IS NOT NULL
 157              //
 158              // The first line comes from parent calls above. The WHERE is added below.
 159              $extraconditions = [];
 160              foreach ($this->get_nullable_fields_in_index($xmldb_table, $xmldb_index) as $fieldname) {
 161                  $extraconditions[] = $this->getEncQuoted($fieldname) .
 162                          ' IS NOT NULL';
 163              }
 164  
 165              if ($extraconditions) {
 166                  $indexsql .= ' WHERE ' . implode(' AND ', $extraconditions);
 167              }
 168          }
 169  
 170          return [$indexsql];
 171      }
 172  
 173      /**
 174       * Given one correct xmldb_table, returns the SQL statements
 175       * to create temporary table (inside one array).
 176       *
 177       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 178       * @return array of sql statements
 179       */
 180      public function getCreateTempTableSQL($xmldb_table) {
 181          $this->temptables->add_temptable($xmldb_table->getName());
 182          $sqlarr = $this->getCreateTableSQL($xmldb_table);
 183          return $sqlarr;
 184      }
 185  
 186      /**
 187       * Given one XMLDB Type, length and decimals, returns the DB proper SQL type.
 188       *
 189       * @param int $xmldb_type The xmldb_type defined constant. XMLDB_TYPE_INTEGER and other XMLDB_TYPE_* constants.
 190       * @param int $xmldb_length The length of that data type.
 191       * @param int $xmldb_decimals The decimal places of precision of the data type.
 192       * @return string The DB defined data type.
 193       */
 194      public function getTypeSQL($xmldb_type, $xmldb_length=null, $xmldb_decimals=null) {
 195  
 196          switch ($xmldb_type) {
 197              case XMLDB_TYPE_INTEGER:    // From http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_7msw.asp?frame=true
 198                  if (empty($xmldb_length)) {
 199                      $xmldb_length = 10;
 200                  }
 201                  if ($xmldb_length > 9) {
 202                      $dbtype = 'BIGINT';
 203                  } else if ($xmldb_length > 4) {
 204                      $dbtype = 'INTEGER';
 205                  } else {
 206                      $dbtype = 'SMALLINT';
 207                  }
 208                  break;
 209              case XMLDB_TYPE_NUMBER:
 210                  $dbtype = $this->number_type;
 211                  if (!empty($xmldb_length)) {
 212                      $dbtype .= '(' . $xmldb_length;
 213                      if (!empty($xmldb_decimals)) {
 214                          $dbtype .= ',' . $xmldb_decimals;
 215                      }
 216                      $dbtype .= ')';
 217                  }
 218                  break;
 219              case XMLDB_TYPE_FLOAT:
 220                  $dbtype = 'FLOAT';
 221                  if (!empty($xmldb_decimals)) {
 222                      if ($xmldb_decimals < 6) {
 223                          $dbtype = 'REAL';
 224                      }
 225                  }
 226                  break;
 227              case XMLDB_TYPE_CHAR:
 228                  $dbtype = 'NVARCHAR';
 229                  if (empty($xmldb_length)) {
 230                      $xmldb_length='255';
 231                  }
 232                  $dbtype .= '(' . $xmldb_length . ') COLLATE database_default';
 233                  break;
 234              case XMLDB_TYPE_TEXT:
 235                  $dbtype = 'NVARCHAR(MAX) COLLATE database_default';
 236                  break;
 237              case XMLDB_TYPE_BINARY:
 238                  $dbtype = 'VARBINARY(MAX)';
 239                  break;
 240              case XMLDB_TYPE_DATETIME:
 241                  $dbtype = 'DATETIME';
 242                  break;
 243          }
 244          return $dbtype;
 245      }
 246  
 247      /**
 248       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop the field from the table.
 249       * MSSQL overwrites the standard sentence because it needs to do some extra work dropping the default and
 250       * check constraints
 251       *
 252       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 253       * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
 254       * @return array The SQL statement for dropping a field from the table.
 255       */
 256      public function getDropFieldSQL($xmldb_table, $xmldb_field) {
 257          $results = array();
 258  
 259          // Get the quoted name of the table and field
 260          $tablename = $this->getTableName($xmldb_table);
 261          $fieldname = $this->getEncQuoted($xmldb_field->getName());
 262  
 263          // Look for any default constraint in this field and drop it
 264          if ($defaultname = $this->getDefaultConstraintName($xmldb_table, $xmldb_field)) {
 265              $results[] = 'ALTER TABLE ' . $tablename . ' DROP CONSTRAINT ' . $defaultname;
 266          }
 267  
 268          // Build the standard alter table drop column
 269          $results[] = 'ALTER TABLE ' . $tablename . ' DROP COLUMN ' . $fieldname;
 270  
 271          return $results;
 272      }
 273  
 274      /**
 275       * Given one correct xmldb_field and the new name, returns the SQL statements
 276       * to rename it (inside one array).
 277       *
 278       * MSSQL is special, so we overload the function here. It needs to
 279       * drop the constraints BEFORE renaming the field
 280       *
 281       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 282       * @param xmldb_field $xmldb_field The instance of xmldb_field to get the renamed field from.
 283       * @param string $newname The new name to rename the field to.
 284       * @return array The SQL statements for renaming the field.
 285       */
 286      public function getRenameFieldSQL($xmldb_table, $xmldb_field, $newname) {
 287  
 288          $results = array();  //Array where all the sentences will be stored
 289  
 290          // Although this is checked in database_manager::rename_field() - double check
 291          // that we aren't trying to rename one "id" field. Although it could be
 292          // implemented (if adding the necessary code to rename sequences, defaults,
 293          // triggers... and so on under each getRenameFieldExtraSQL() function, it's
 294          // better to forbid it, mainly because this field is the default PK and
 295          // in the future, a lot of FKs can be pointing here. So, this field, more
 296          // or less, must be considered immutable!
 297          if ($xmldb_field->getName() == 'id') {
 298              return array();
 299          }
 300  
 301          // We can't call to standard (parent) getRenameFieldSQL() function since it would enclose the field name
 302          // with improper quotes in MSSQL: here, we use a stored procedure to rename the field i.e. a column object;
 303          // we need to take care about how this stored procedure expects parameters to be "qualified".
 304          $rename = str_replace('TABLENAME', $this->getTableName($xmldb_table), $this->rename_column_sql);
 305          // Qualifying the column object could require brackets: use them, regardless the column name not being a reserved word.
 306          $rename = str_replace('OLDFIELDNAME', '[' . $xmldb_field->getName() . ']', $rename);
 307          // The new field name should be passed as the actual name, w/o any quote.
 308          $rename = str_replace('NEWFIELDNAME', $newname, $rename);
 309  
 310          $results[] = $rename;
 311  
 312          return $results;
 313      }
 314  
 315      /**
 316       * Returns the code (array of statements) needed to execute extra statements on table rename.
 317       *
 318       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 319       * @param string $newname The new name for the table.
 320       * @return array Array of extra SQL statements to rename a table.
 321       */
 322      public function getRenameTableExtraSQL($xmldb_table, $newname) {
 323  
 324          $results = array();
 325  
 326          return $results;
 327      }
 328  
 329      /**
 330       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to alter the field in the table.
 331       *
 332       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 333       * @param xmldb_field $xmldb_field The instance of xmldb_field to create the SQL from.
 334       * @param string $skip_type_clause The type clause on alter columns, NULL by default.
 335       * @param string $skip_default_clause The default clause on alter columns, NULL by default.
 336       * @param string $skip_notnull_clause The null/notnull clause on alter columns, NULL by default.
 337       * @return string The field altering SQL statement.
 338       */
 339      public function getAlterFieldSQL($xmldb_table, $xmldb_field, $skip_type_clause = NULL, $skip_default_clause = NULL, $skip_notnull_clause = NULL) {
 340  
 341          $results = array();     // To store all the needed SQL commands
 342  
 343          // Get the quoted name of the table and field
 344          $tablename = $xmldb_table->getName();
 345          $fieldname = $xmldb_field->getName();
 346  
 347          // Take a look to field metadata
 348          $meta = $this->mdb->get_columns($tablename);
 349          $metac = $meta[$fieldname];
 350          $oldmetatype = $metac->meta_type;
 351  
 352          $oldlength = $metac->max_length;
 353          $olddecimals = empty($metac->scale) ? null : $metac->scale;
 354          $oldnotnull = empty($metac->not_null) ? false : $metac->not_null;
 355          //$olddefault = empty($metac->has_default) ? null : strtok($metac->default_value, ':');
 356  
 357          $typechanged = true;  //By default, assume that the column type has changed
 358          $lengthchanged = true;  //By default, assume that the column length has changed
 359  
 360          // Detect if we are changing the type of the column
 361          if (($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') ||
 362              ($xmldb_field->getType() == XMLDB_TYPE_NUMBER  && $oldmetatype == 'N') ||
 363              ($xmldb_field->getType() == XMLDB_TYPE_FLOAT   && $oldmetatype == 'F') ||
 364              ($xmldb_field->getType() == XMLDB_TYPE_CHAR    && $oldmetatype == 'C') ||
 365              ($xmldb_field->getType() == XMLDB_TYPE_TEXT    && $oldmetatype == 'X') ||
 366              ($xmldb_field->getType() == XMLDB_TYPE_BINARY  && $oldmetatype == 'B')) {
 367              $typechanged = false;
 368          }
 369  
 370          // If the new field (and old) specs are for integer, let's be a bit more specific differentiating
 371          // types of integers. Else, some combinations can cause things like MDL-21868
 372          if ($xmldb_field->getType() == XMLDB_TYPE_INTEGER && $oldmetatype == 'I') {
 373              if ($xmldb_field->getLength() > 9) { // Convert our new lenghts to detailed meta types
 374                  $newmssqlinttype = 'I8';
 375              } else if ($xmldb_field->getLength() > 4) {
 376                  $newmssqlinttype = 'I';
 377              } else {
 378                  $newmssqlinttype = 'I2';
 379              }
 380              if ($metac->type == 'bigint') { // Convert current DB type to detailed meta type (our metatype is not enough!)
 381                  $oldmssqlinttype = 'I8';
 382              } else if ($metac->type == 'smallint') {
 383                  $oldmssqlinttype = 'I2';
 384              } else {
 385                  $oldmssqlinttype = 'I';
 386              }
 387              if ($newmssqlinttype != $oldmssqlinttype) { // Compare new and old meta types
 388                  $typechanged = true; // Change in meta type means change in type at all effects
 389              }
 390          }
 391  
 392          // Detect if we are changing the length of the column, not always necessary to drop defaults
 393          // if only the length changes, but it's safe to do it always
 394          if ($xmldb_field->getLength() == $oldlength) {
 395              $lengthchanged = false;
 396          }
 397  
 398          // If type or length have changed drop the default if exists
 399          if ($typechanged || $lengthchanged) {
 400              $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field);
 401          }
 402  
 403          // Some changes of type require multiple alter statements, because mssql lacks direct implicit cast between such types
 404          // Here it is the matrix: http://msdn.microsoft.com/en-us/library/ms187928(SQL.90).aspx
 405          // Going to store such intermediate alters in array of objects, storing all the info needed
 406          $multiple_alter_stmt = array();
 407          $targettype = $xmldb_field->getType();
 408  
 409          if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'I') { // integer to text
 410              $multiple_alter_stmt[0] = new stdClass;                  // needs conversion to varchar
 411              $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
 412              $multiple_alter_stmt[0]->length = 255;
 413  
 414          } else if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'N') { // decimal to text
 415              $multiple_alter_stmt[0] = new stdClass;                         // needs conversion to varchar
 416              $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
 417              $multiple_alter_stmt[0]->length = 255;
 418  
 419          } else if ($targettype == XMLDB_TYPE_TEXT && $oldmetatype == 'F') { // float to text
 420              $multiple_alter_stmt[0] = new stdClass;                         // needs conversion to varchar
 421              $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
 422              $multiple_alter_stmt[0]->length = 255;
 423  
 424          } else if ($targettype == XMLDB_TYPE_INTEGER && $oldmetatype == 'X') { // text to integer
 425              $multiple_alter_stmt[0] = new stdClass;                            // needs conversion to varchar
 426              $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
 427              $multiple_alter_stmt[0]->length = 255;
 428              $multiple_alter_stmt[1] = new stdClass;                            // and also needs conversion to decimal
 429              $multiple_alter_stmt[1]->type = XMLDB_TYPE_NUMBER;                 // without decimal positions
 430              $multiple_alter_stmt[1]->length = 10;
 431  
 432          } else if ($targettype == XMLDB_TYPE_NUMBER && $oldmetatype == 'X') { // text to decimal
 433              $multiple_alter_stmt[0] = new stdClass;                           // needs conversion to varchar
 434              $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
 435              $multiple_alter_stmt[0]->length = 255;
 436  
 437          } else if ($targettype ==  XMLDB_TYPE_FLOAT && $oldmetatype == 'X') { // text to float
 438              $multiple_alter_stmt[0] = new stdClass;                           // needs conversion to varchar
 439              $multiple_alter_stmt[0]->type = XMLDB_TYPE_CHAR;
 440              $multiple_alter_stmt[0]->length = 255;
 441          }
 442  
 443          // Just prevent default clauses in this type of sentences for mssql and launch the parent one
 444          if (empty($multiple_alter_stmt)) { // Direct implicit conversion allowed, launch it
 445              $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL));
 446  
 447          } else { // Direct implicit conversion forbidden, use the intermediate ones
 448              $final_type = $xmldb_field->getType(); // Save final type and length
 449              $final_length = $xmldb_field->getLength();
 450              foreach ($multiple_alter_stmt as $alter) {
 451                  $xmldb_field->setType($alter->type);  // Put our intermediate type and length and alter to it
 452                  $xmldb_field->setLength($alter->length);
 453                  $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL));
 454              }
 455              $xmldb_field->setType($final_type); // Set the final type and length and alter to it
 456              $xmldb_field->setLength($final_length);
 457              $results = array_merge($results, parent::getAlterFieldSQL($xmldb_table, $xmldb_field, NULL, true, NULL));
 458          }
 459  
 460          // Finally, process the default clause to add it back if necessary
 461          if ($typechanged || $lengthchanged) {
 462              $results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field));
 463          }
 464  
 465          // Return results
 466          return $results;
 467      }
 468  
 469      /**
 470       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to modify the default of the field in the table.
 471       *
 472       * @param xmldb_table $xmldb_table The table related to $xmldb_field.
 473       * @param xmldb_field $xmldb_field The instance of xmldb_field to get the modified default value from.
 474       * @return array The SQL statement for modifying the default value.
 475       */
 476      public function getModifyDefaultSQL($xmldb_table, $xmldb_field) {
 477          // MSSQL is a bit special with default constraints because it implements them as external constraints so
 478          // normal ALTER TABLE ALTER COLUMN don't work to change defaults. Because this, we have this method overloaded here
 479  
 480          $results = array();
 481  
 482          // Decide if we are going to create/modify or to drop the default
 483          if ($xmldb_field->getDefault() === null) {
 484              $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); //Drop but, under some circumstances, re-enable
 485              $default_clause = $this->getDefaultClause($xmldb_field);
 486              if ($default_clause) { //If getDefaultClause() it must have one default, create it
 487                  $results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field)); //Create/modify
 488              }
 489          } else {
 490              $results = $this->getDropDefaultSQL($xmldb_table, $xmldb_field); //Drop (only if exists)
 491              $results = array_merge($results, $this->getCreateDefaultSQL($xmldb_table, $xmldb_field)); //Create/modify
 492          }
 493  
 494          return $results;
 495      }
 496  
 497      /**
 498       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to add its default
 499       * (usually invoked from getModifyDefaultSQL()
 500       *
 501       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 502       * @param xmldb_field $xmldb_field The xmldb_field object instance.
 503       * @return array Array of SQL statements to create a field's default.
 504       */
 505      public function getCreateDefaultSQL($xmldb_table, $xmldb_field) {
 506          // MSSQL is a bit special and it requires the corresponding DEFAULT CONSTRAINT to be dropped
 507  
 508          $results = array();
 509  
 510          // Get the quoted name of the table and field
 511          $tablename = $this->getTableName($xmldb_table);
 512          $fieldname = $this->getEncQuoted($xmldb_field->getName());
 513  
 514          // Now, check if, with the current field attributes, we have to build one default
 515          $default_clause = $this->getDefaultClause($xmldb_field);
 516          if ($default_clause) {
 517              // We need to build the default (Moodle) default, so do it
 518              $sql = 'ALTER TABLE ' . $tablename . ' ADD' . $default_clause . ' FOR ' . $fieldname;
 519              $results[] = $sql;
 520          }
 521  
 522          return $results;
 523      }
 524  
 525      /**
 526       * Given one xmldb_table and one xmldb_field, return the SQL statements needed to drop its default
 527       * (usually invoked from getModifyDefaultSQL()
 528       *
 529       * Note that this method may be dropped in future.
 530       *
 531       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 532       * @param xmldb_field $xmldb_field The xmldb_field object instance.
 533       * @return array Array of SQL statements to create a field's default.
 534       *
 535       * @todo MDL-31147 Moodle 2.1 - Drop getDropDefaultSQL()
 536       */
 537      public function getDropDefaultSQL($xmldb_table, $xmldb_field) {
 538          // MSSQL is a bit special and it requires the corresponding DEFAULT CONSTRAINT to be dropped
 539  
 540          $results = array();
 541  
 542          // Get the quoted name of the table and field
 543          $tablename = $this->getTableName($xmldb_table);
 544          $fieldname = $this->getEncQuoted($xmldb_field->getName());
 545  
 546          // Look for the default contraint and, if found, drop it
 547          if ($defaultname = $this->getDefaultConstraintName($xmldb_table, $xmldb_field)) {
 548              $results[] = 'ALTER TABLE ' . $tablename . ' DROP CONSTRAINT ' . $defaultname;
 549          }
 550  
 551          return $results;
 552      }
 553  
 554      /**
 555       * Given one xmldb_table and one xmldb_field, returns the name of its default constraint in DB
 556       * or false if not found
 557       * This function should be considered internal and never used outside from generator
 558       *
 559       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 560       * @param xmldb_field $xmldb_field The xmldb_field object instance.
 561       * @return mixed
 562       */
 563      protected function getDefaultConstraintName($xmldb_table, $xmldb_field) {
 564  
 565          // Get the quoted name of the table and field
 566          $tablename = $this->getTableName($xmldb_table);
 567          $fieldname = $xmldb_field->getName();
 568  
 569          // Look for any default constraint in this field and drop it
 570          if ($default = $this->mdb->get_record_sql("SELECT object_id, object_name(default_object_id) AS defaultconstraint
 571                                                       FROM sys.columns
 572                                                      WHERE object_id = object_id(?)
 573                                                            AND name = ?", array($tablename, $fieldname))) {
 574              return $default->defaultconstraint;
 575          } else {
 576              return false;
 577          }
 578      }
 579  
 580      /**
 581       * Given three strings (table name, list of fields (comma separated) and suffix),
 582       * create the proper object name quoting it if necessary.
 583       *
 584       * IMPORTANT: This function must be used to CALCULATE NAMES of objects TO BE CREATED,
 585       *            NEVER TO GUESS NAMES of EXISTING objects!!!
 586       *
 587       * IMPORTANT: We are overriding this function for the MSSQL generator because objects
 588       * belonging to temporary tables aren't searchable in the catalog neither in information
 589       * schema tables. So, for temporary tables, we are going to add 4 randomly named "virtual"
 590       * fields, so the generated names won't cause concurrency problems. Really nasty hack,
 591       * but the alternative involves modifying all the creation table code to avoid naming
 592       * constraints for temp objects and that will dupe a lot of code.
 593       *
 594       * @param string $tablename The table name.
 595       * @param string $fields A list of comma separated fields.
 596       * @param string $suffix A suffix for the object name.
 597       * @return string Object's name.
 598       */
 599      public function getNameForObject($tablename, $fields, $suffix='') {
 600          if ($this->temptables->is_temptable($tablename)) { // Is temp table, inject random field names
 601              $random = strtolower(random_string(12)); // 12cc to be split in 4 parts
 602              $fields = $fields . ', ' . implode(', ', str_split($random, 3));
 603          }
 604          return parent::getNameForObject($tablename, $fields, $suffix); // Delegate to parent (common) algorithm
 605      }
 606  
 607      /**
 608       * Given one object name and it's type (pk, uk, fk, ck, ix, uix, seq, trg).
 609       *
 610       * (MySQL requires the whole xmldb_table object to be specified, so we add it always)
 611       *
 612       * This is invoked from getNameForObject().
 613       * Only some DB have this implemented.
 614       *
 615       * @param string $object_name The object's name to check for.
 616       * @param string $type The object's type (pk, uk, fk, ck, ix, uix, seq, trg).
 617       * @param string $table_name The table's name to check in
 618       * @return bool If such name is currently in use (true) or no (false)
 619       */
 620      public function isNameInUse($object_name, $type, $table_name) {
 621          switch($type) {
 622              case 'seq':
 623              case 'trg':
 624              case 'pk':
 625              case 'uk':
 626              case 'fk':
 627              case 'ck':
 628                  if ($check = $this->mdb->get_records_sql("SELECT name
 629                                                              FROM sys.objects
 630                                                             WHERE lower(name) = ?", array(strtolower($object_name)))) {
 631                      return true;
 632                  }
 633                  break;
 634              case 'ix':
 635              case 'uix':
 636                  if ($check = $this->mdb->get_records_sql("SELECT name
 637                                                              FROM sys.indexes
 638                                                             WHERE lower(name) = ?", array(strtolower($object_name)))) {
 639                      return true;
 640                  }
 641                  break;
 642          }
 643          return false; //No name in use found
 644      }
 645  
 646      /**
 647       * Returns the code (array of statements) needed to add one comment to the table.
 648       *
 649       * @param xmldb_table $xmldb_table The xmldb_table object instance.
 650       * @return array Array of SQL statements to add one comment to the table.
 651       */
 652      public function getCommentSQL($xmldb_table) {
 653          return array();
 654      }
 655  
 656      /**
 657       * Adds slashes to string.
 658       * @param string $s
 659       * @return string The escaped string.
 660       */
 661      public function addslashes($s) {
 662          // do not use php addslashes() because it depends on PHP quote settings!
 663          $s = str_replace("'",  "''", $s);
 664          return $s;
 665      }
 666  
 667      /**
 668       * Returns an array of reserved words (lowercase) for this DB
 669       * @return array An array of database specific reserved words
 670       */
 671      public static function getReservedWords() {
 672          // This file contains the reserved words for MSSQL databases
 673          // from http://msdn2.microsoft.com/en-us/library/ms189822.aspx
 674          // Should be identical to sqlsrv_native_moodle_database::$reservewords.
 675          $reserved_words = array (
 676              "add", "all", "alter", "and", "any", "as", "asc", "authorization", "avg", "backup", "begin", "between", "break",
 677              "browse", "bulk", "by", "cascade", "case", "check", "checkpoint", "close", "clustered", "coalesce", "collate", "column",
 678              "commit", "committed", "compute", "confirm", "constraint", "contains", "containstable", "continue", "controlrow",
 679              "convert", "count", "create", "cross", "current", "current_date", "current_time", "current_timestamp", "current_user",
 680              "cursor", "database", "dbcc", "deallocate", "declare", "default", "delete", "deny", "desc", "disk", "distinct",
 681              "distributed", "double", "drop", "dummy", "dump", "else", "end", "errlvl", "errorexit", "escape", "except", "exec",
 682              "execute", "exists", "exit", "external", "fetch", "file", "fillfactor", "floppy", "for", "foreign", "freetext",
 683              "freetexttable", "from", "full", "function", "goto", "grant", "group", "having", "holdlock", "identity",
 684              "identity_insert", "identitycol", "if", "in", "index", "inner", "insert", "intersect", "into", "is", "isolation",
 685              "join", "key", "kill", "left", "level", "like", "lineno", "load", "max", "merge", "min", "mirrorexit", "national",
 686              "nocheck", "nonclustered", "not", "null", "nullif", "of", "off", "offsets", "on", "once", "only", "open",
 687              "opendatasource", "openquery", "openrowset", "openxml", "option", "or", "order", "outer", "over", "percent", "perm",
 688              "permanent", "pipe", "pivot", "plan", "precision", "prepare", "primary", "print", "privileges", "proc", "procedure",
 689              "processexit", "public", "raiserror", "read", "readtext", "reconfigure", "references", "repeatable", "replication",
 690              "restore", "restrict", "return", "revert", "revoke", "right", "rollback", "rowcount", "rowguidcol", "rule", "save",
 691              "schema", "securityaudit", "select", "semantickeyphrasetable", "semanticsimilaritydetailstable",
 692              "semanticsimilaritytable", "serializable", "session_user", "set", "setuser", "shutdown", "some", "statistics", "sum",
 693              "system_user", "table", "tablesample", "tape", "temp", "temporary", "textsize", "then", "to", "top", "tran",
 694              "transaction", "trigger", "truncate", "try_convert", "tsequal", "uncommitted", "union", "unique", "unpivot", "update",
 695              "updatetext", "use", "user", "values", "varying", "view", "waitfor", "when", "where", "while", "with", "within group",
 696              "work", "writetext"
 697          );
 698          return $reserved_words;
 699      }
 700  }