Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 3.9.x will end* 10 May 2021 (12 months).
  • Bug fixes for security issues in 3.9.x will end* 8 May 2023 (36 months).
  • PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.

Differences Between: [Versions 39 and 310] [Versions 39 and 311] [Versions 39 and 400] [Versions 39 and 401] [Versions 39 and 402] [Versions 39 and 403]

   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 2 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   * Native sqlsrv class representing moodle database interface.
  19   *
  20   * @package    core_dml
  21   * @copyright  2009 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com}
  22   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v2 or later
  23   */
  24  
  25  defined('MOODLE_INTERNAL') || die();
  26  
  27  require_once (__DIR__.'/moodle_database.php');
  28  require_once (__DIR__.'/sqlsrv_native_moodle_recordset.php');
  29  require_once (__DIR__.'/sqlsrv_native_moodle_temptables.php');
  30  
  31  /**
  32   * Native sqlsrv class representing moodle database interface.
  33   *
  34   * @package    core_dml
  35   * @copyright  2009 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com}
  36   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v2 or later
  37   */
  38  class sqlsrv_native_moodle_database extends moodle_database {
  39  
  40      protected $sqlsrv = null;
  41      protected $last_error_reporting; // To handle SQL*Server-Native driver default verbosity
  42      protected $temptables; // Control existing temptables (sqlsrv_moodle_temptables object)
  43      protected $collation;  // current DB collation cache
  44      /**
  45       * Does the used db version support ANSI way of limiting (2012 and higher)
  46       * @var bool
  47       */
  48      protected $supportsoffsetfetch;
  49  
  50      /** @var array list of open recordsets */
  51      protected $recordsets = array();
  52  
  53      /** @var array list of reserve words in MSSQL / Transact from http://msdn2.microsoft.com/en-us/library/ms189822.aspx */
  54      protected $reservewords = [
  55          "add", "all", "alter", "and", "any", "as", "asc", "authorization", "avg", "backup", "begin", "between", "break",
  56          "browse", "bulk", "by", "cascade", "case", "check", "checkpoint", "close", "clustered", "coalesce", "collate", "column",
  57          "commit", "committed", "compute", "confirm", "constraint", "contains", "containstable", "continue", "controlrow",
  58          "convert", "count", "create", "cross", "current", "current_date", "current_time", "current_timestamp", "current_user",
  59          "cursor", "database", "dbcc", "deallocate", "declare", "default", "delete", "deny", "desc", "disk", "distinct",
  60          "distributed", "double", "drop", "dummy", "dump", "else", "end", "errlvl", "errorexit", "escape", "except", "exec",
  61          "execute", "exists", "exit", "external", "fetch", "file", "fillfactor", "floppy", "for", "foreign", "freetext",
  62          "freetexttable", "from", "full", "function", "goto", "grant", "group", "having", "holdlock", "identity",
  63          "identity_insert", "identitycol", "if", "in", "index", "inner", "insert", "intersect", "into", "is", "isolation",
  64          "join", "key", "kill", "left", "level", "like", "lineno", "load", "max", "merge", "min", "mirrorexit", "national",
  65          "nocheck", "nonclustered", "not", "null", "nullif", "of", "off", "offsets", "on", "once", "only", "open",
  66          "opendatasource", "openquery", "openrowset", "openxml", "option", "or", "order", "outer", "over", "percent", "perm",
  67          "permanent", "pipe", "pivot", "plan", "precision", "prepare", "primary", "print", "privileges", "proc", "procedure",
  68          "processexit", "public", "raiserror", "read", "readtext", "reconfigure", "references", "repeatable", "replication",
  69          "restore", "restrict", "return", "revert", "revoke", "right", "rollback", "rowcount", "rowguidcol", "rule", "save",
  70          "schema", "securityaudit", "select", "semantickeyphrasetable", "semanticsimilaritydetailstable",
  71          "semanticsimilaritytable", "serializable", "session_user", "set", "setuser", "shutdown", "some", "statistics", "sum",
  72          "system_user", "table", "tablesample", "tape", "temp", "temporary", "textsize", "then", "to", "top", "tran",
  73          "transaction", "trigger", "truncate", "try_convert", "tsequal", "uncommitted", "union", "unique", "unpivot", "update",
  74          "updatetext", "use", "user", "values", "varying", "view", "waitfor", "when", "where", "while", "with", "within group",
  75          "work", "writetext"
  76      ];
  77  
  78      /**
  79       * Constructor - instantiates the database, specifying if it's external (connect to other systems) or no (Moodle DB)
  80       *              note this has effect to decide if prefix checks must be performed or no
  81       * @param bool true means external database used
  82       */
  83      public function __construct($external=false) {
  84          parent::__construct($external);
  85      }
  86  
  87      /**
  88       * Detects if all needed PHP stuff installed.
  89       * Note: can be used before connect()
  90       * @return mixed true if ok, string if something
  91       */
  92      public function driver_installed() {
  93          // use 'function_exists()' rather than 'extension_loaded()' because
  94          // the name used by 'extension_loaded()' is case specific! The extension
  95          // therefore *could be* mixed case and hence not found.
  96          if (!function_exists('sqlsrv_num_rows')) {
  97              return get_string('nativesqlsrvnodriver', 'install');
  98          }
  99          return true;
 100      }
 101  
 102      /**
 103       * Returns database family type - describes SQL dialect
 104       * Note: can be used before connect()
 105       * @return string db family name (mysql, postgres, mssql, sqlsrv, oracle, etc.)
 106       */
 107      public function get_dbfamily() {
 108          return 'mssql';
 109      }
 110  
 111      /**
 112       * Returns more specific database driver type
 113       * Note: can be used before connect()
 114       * @return string db type mysqli, pgsql, oci, mssql, sqlsrv
 115       */
 116      protected function get_dbtype() {
 117          return 'sqlsrv';
 118      }
 119  
 120      /**
 121       * Returns general database library name
 122       * Note: can be used before connect()
 123       * @return string db type pdo, native
 124       */
 125      protected function get_dblibrary() {
 126          return 'native';
 127      }
 128  
 129      /**
 130       * Returns localised database type name
 131       * Note: can be used before connect()
 132       * @return string
 133       */
 134      public function get_name() {
 135          return get_string('nativesqlsrv', 'install');
 136      }
 137  
 138      /**
 139       * Returns localised database configuration help.
 140       * Note: can be used before connect()
 141       * @return string
 142       */
 143      public function get_configuration_help() {
 144          return get_string('nativesqlsrvhelp', 'install');
 145      }
 146  
 147      /**
 148       * Diagnose database and tables, this function is used
 149       * to verify database and driver settings, db engine types, etc.
 150       *
 151       * @return string null means everything ok, string means problem found.
 152       */
 153      public function diagnose() {
 154          // Verify the database is running with READ_COMMITTED_SNAPSHOT enabled.
 155          // (that's required to get snapshots/row versioning on READ_COMMITED mode).
 156          $correctrcsmode = false;
 157          $sql = "SELECT is_read_committed_snapshot_on
 158                    FROM sys.databases
 159                   WHERE name = '{$this->dbname}'";
 160          $this->query_start($sql, null, SQL_QUERY_AUX);
 161          $result = sqlsrv_query($this->sqlsrv, $sql);
 162          $this->query_end($result);
 163          if ($result) {
 164              if ($row = sqlsrv_fetch_array($result)) {
 165                  $correctrcsmode = (bool)reset($row);
 166              }
 167          }
 168          $this->free_result($result);
 169  
 170          if (!$correctrcsmode) {
 171              return get_string('mssqlrcsmodemissing', 'error');
 172          }
 173  
 174          // Arrived here, all right.
 175          return null;
 176      }
 177  
 178      /**
 179       * Connect to db
 180       * Must be called before most other methods. (you can call methods that return connection configuration parameters)
 181       * @param string $dbhost The database host.
 182       * @param string $dbuser The database username.
 183       * @param string $dbpass The database username's password.
 184       * @param string $dbname The name of the database being connected to.
 185       * @param mixed $prefix string|bool The moodle db table name's prefix. false is used for external databases where prefix not used
 186       * @param array $dboptions driver specific options
 187       * @return bool true
 188       * @throws dml_connection_exception if error
 189       */
 190      public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {
 191          if ($prefix == '' and !$this->external) {
 192              // Enforce prefixes for everybody but mysql.
 193              throw new dml_exception('prefixcannotbeempty', $this->get_dbfamily());
 194          }
 195  
 196          $driverstatus = $this->driver_installed();
 197  
 198          if ($driverstatus !== true) {
 199              throw new dml_exception('dbdriverproblem', $driverstatus);
 200          }
 201  
 202          /*
 203           * Log all Errors.
 204           */
 205          sqlsrv_configure("WarningsReturnAsErrors", FALSE);
 206          sqlsrv_configure("LogSubsystems", SQLSRV_LOG_SYSTEM_OFF);
 207          sqlsrv_configure("LogSeverity", SQLSRV_LOG_SEVERITY_ERROR);
 208  
 209          $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
 210  
 211          $options = [
 212              'UID' => $this->dbuser,
 213              'PWD' => $this->dbpass,
 214              'Database' => $this->dbname,
 215              'CharacterSet' => 'UTF-8',
 216              'MultipleActiveResultSets' => true,
 217              'ConnectionPooling' => !empty($this->dboptions['dbpersist']),
 218              'ReturnDatesAsStrings' => true,
 219          ];
 220  
 221          $dbhost = $this->dbhost;
 222          if (!empty($dboptions['dbport'])) {
 223              $dbhost .= ',' . $dboptions['dbport'];
 224          }
 225  
 226          // The sqlsrv_connect() has a lot of connection options to be used.
 227          // Users can add any supported options with the 'extrainfo' key in the dboptions.
 228          if (isset($this->dboptions['extrainfo'])) {
 229              $options = array_merge($options, $this->dboptions['extrainfo']);
 230          }
 231  
 232          $this->sqlsrv = sqlsrv_connect($dbhost, $options);
 233  
 234          if ($this->sqlsrv === false) {
 235              $this->sqlsrv = null;
 236              $dberr = $this->get_last_error();
 237  
 238              throw new dml_connection_exception($dberr);
 239          }
 240  
 241          // Disable logging until we are fully setup.
 242          $this->query_log_prevent();
 243  
 244          // Allow quoted identifiers
 245          $sql = "SET QUOTED_IDENTIFIER ON";
 246          $this->query_start($sql, null, SQL_QUERY_AUX);
 247          $result = sqlsrv_query($this->sqlsrv, $sql);
 248          $this->query_end($result);
 249  
 250          $this->free_result($result);
 251  
 252          // Force ANSI nulls so the NULL check was done by IS NULL and NOT IS NULL
 253          // instead of equal(=) and distinct(<>) symbols
 254          $sql = "SET ANSI_NULLS ON";
 255          $this->query_start($sql, null, SQL_QUERY_AUX);
 256          $result = sqlsrv_query($this->sqlsrv, $sql);
 257          $this->query_end($result);
 258  
 259          $this->free_result($result);
 260  
 261          // Force ANSI warnings so arithmetic/string overflows will be
 262          // returning error instead of transparently truncating data
 263          $sql = "SET ANSI_WARNINGS ON";
 264          $this->query_start($sql, null, SQL_QUERY_AUX);
 265          $result = sqlsrv_query($this->sqlsrv, $sql);
 266          $this->query_end($result);
 267  
 268          // Concatenating null with anything MUST return NULL
 269          $sql = "SET CONCAT_NULL_YIELDS_NULL  ON";
 270          $this->query_start($sql, null, SQL_QUERY_AUX);
 271          $result = sqlsrv_query($this->sqlsrv, $sql);
 272          $this->query_end($result);
 273  
 274          $this->free_result($result);
 275  
 276          // Set transactions isolation level to READ_COMMITTED
 277          // prevents dirty reads when using transactions +
 278          // is the default isolation level of sqlsrv
 279          $sql = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED";
 280          $this->query_start($sql, NULL, SQL_QUERY_AUX);
 281          $result = sqlsrv_query($this->sqlsrv, $sql);
 282          $this->query_end($result);
 283  
 284          $this->free_result($result);
 285  
 286          $serverinfo = $this->get_server_info();
 287          // Fetch/offset is supported staring from SQL Server 2012.
 288          $this->supportsoffsetfetch = $serverinfo['version'] > '11';
 289  
 290          // We can enable logging now.
 291          $this->query_log_allow();
 292  
 293          // Connection established and configured, going to instantiate the temptables controller
 294          $this->temptables = new sqlsrv_native_moodle_temptables($this);
 295  
 296          return true;
 297      }
 298  
 299      /**
 300       * Close database connection and release all resources
 301       * and memory (especially circular memory references).
 302       * Do NOT use connect() again, create a new instance if needed.
 303       */
 304      public function dispose() {
 305          parent::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection
 306  
 307          if ($this->sqlsrv) {
 308              sqlsrv_close($this->sqlsrv);
 309              $this->sqlsrv = null;
 310          }
 311      }
 312  
 313      /**
 314       * Called before each db query.
 315       * @param string $sql
 316       * @param array $params array of parameters
 317       * @param int $type type of query
 318       * @param mixed $extrainfo driver specific extra information
 319       * @return void
 320       */
 321      protected function query_start($sql, array $params = null, $type, $extrainfo = null) {
 322          parent::query_start($sql, $params, $type, $extrainfo);
 323      }
 324  
 325      /**
 326       * Called immediately after each db query.
 327       * @param mixed db specific result
 328       * @return void
 329       */
 330      protected function query_end($result) {
 331          parent::query_end($result);
 332      }
 333  
 334      /**
 335       * Returns database server info array
 336       * @return array Array containing 'description', 'version' and 'database' (current db) info
 337       */
 338      public function get_server_info() {
 339          static $info;
 340  
 341          if (!$info) {
 342              $server_info = sqlsrv_server_info($this->sqlsrv);
 343  
 344              if ($server_info) {
 345                  $info['description'] = $server_info['SQLServerName'];
 346                  $info['version'] = $server_info['SQLServerVersion'];
 347                  $info['database'] = $server_info['CurrentDatabase'];
 348              }
 349          }
 350          return $info;
 351      }
 352  
 353      /**
 354       * Override: Converts short table name {tablename} to real table name
 355       * supporting temp tables (#) if detected
 356       *
 357       * @param string sql
 358       * @return string sql
 359       */
 360      protected function fix_table_names($sql) {
 361          if (preg_match_all('/\{([a-z][a-z0-9_]*)\}/i', $sql, $matches)) {
 362              foreach ($matches[0] as $key => $match) {
 363                  $name = $matches[1][$key];
 364  
 365                  if ($this->temptables->is_temptable($name)) {
 366                      $sql = str_replace($match, $this->temptables->get_correct_name($name), $sql);
 367                  } else {
 368                      $sql = str_replace($match, $this->prefix.$name, $sql);
 369                  }
 370              }
 371          }
 372          return $sql;
 373      }
 374  
 375      /**
 376       * Returns supported query parameter types
 377       * @return int bitmask
 378       */
 379      protected function allowed_param_types() {
 380          return SQL_PARAMS_QM;  // sqlsrv 1.1 can bind
 381      }
 382  
 383      /**
 384       * Returns last error reported by database engine.
 385       * @return string error message
 386       */
 387      public function get_last_error() {
 388          $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL);
 389          $errorMessage = 'No errors found';
 390  
 391          if ($retErrors != null) {
 392              $errorMessage = '';
 393  
 394              foreach ($retErrors as $arrError) {
 395                  $errorMessage .= "SQLState: ".$arrError['SQLSTATE']."<br>\n";
 396                  $errorMessage .= "Error Code: ".$arrError['code']."<br>\n";
 397                  $errorMessage .= "Message: ".$arrError['message']."<br>\n";
 398              }
 399          }
 400  
 401          return $errorMessage;
 402      }
 403  
 404      /**
 405       * Prepare the query binding and do the actual query.
 406       *
 407       * @param string $sql The sql statement
 408       * @param array $params array of params for binding. If NULL, they are ignored.
 409       * @param int $sql_query_type - Type of operation
 410       * @param bool $free_result - Default true, transaction query will be freed.
 411       * @param bool $scrollable - Default false, to use for quickly seeking to target records
 412       * @return resource|bool result
 413       */
 414      private function do_query($sql, $params, $sql_query_type, $free_result = true, $scrollable = false) {
 415          list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
 416  
 417          /*
 418           * Bound variables *are* supported. Until I can get it to work, emulate the bindings
 419           * The challenge/problem/bug is that although they work, doing a SELECT SCOPE_IDENTITY()
 420           * doesn't return a value (no result set)
 421           *
 422           * -- somebody from MS
 423           */
 424  
 425          $sql = $this->emulate_bound_params($sql, $params);
 426          $this->query_start($sql, $params, $sql_query_type);
 427          if (!$scrollable) { // Only supporting next row
 428              $result = sqlsrv_query($this->sqlsrv, $sql);
 429          } else { // Supporting absolute/relative rows
 430              $result = sqlsrv_query($this->sqlsrv, $sql, array(), array('Scrollable' => SQLSRV_CURSOR_STATIC));
 431          }
 432  
 433          if ($result === false) {
 434              // TODO do something with error or just use if DEV or DEBUG?
 435              $dberr = $this->get_last_error();
 436          }
 437  
 438          $this->query_end($result);
 439  
 440          if ($free_result) {
 441              $this->free_result($result);
 442              return true;
 443          }
 444          return $result;
 445      }
 446  
 447      /**
 448       * Return tables in database WITHOUT current prefix.
 449       * @param bool $usecache if true, returns list of cached tables.
 450       * @return array of table names in lowercase and without prefix
 451       */
 452      public function get_tables($usecache = true) {
 453          if ($usecache and $this->tables !== null) {
 454              return $this->tables;
 455          }
 456          $this->tables = array ();
 457          $prefix = str_replace('_', '\\_', $this->prefix);
 458          $sql = "SELECT table_name
 459                    FROM INFORMATION_SCHEMA.TABLES
 460                   WHERE table_name LIKE '$prefix%' ESCAPE '\\' AND table_type = 'BASE TABLE'";
 461  
 462          $this->query_start($sql, null, SQL_QUERY_AUX);
 463          $result = sqlsrv_query($this->sqlsrv, $sql);
 464          $this->query_end($result);
 465  
 466          if ($result) {
 467              while ($row = sqlsrv_fetch_array($result)) {
 468                  $tablename = reset($row);
 469                  if ($this->prefix !== false && $this->prefix !== '') {
 470                      if (strpos($tablename, $this->prefix) !== 0) {
 471                          continue;
 472                      }
 473                      $tablename = substr($tablename, strlen($this->prefix));
 474                  }
 475                  $this->tables[$tablename] = $tablename;
 476              }
 477              $this->free_result($result);
 478          }
 479  
 480          // Add the currently available temptables
 481          $this->tables = array_merge($this->tables, $this->temptables->get_temptables());
 482          return $this->tables;
 483      }
 484  
 485      /**
 486       * Return table indexes - everything lowercased.
 487       * @param string $table The table we want to get indexes from.
 488       * @return array of arrays
 489       */
 490      public function get_indexes($table) {
 491          $indexes = array ();
 492          $tablename = $this->prefix.$table;
 493  
 494          // Indexes aren't covered by information_schema metatables, so we need to
 495          // go to sys ones. Skipping primary key indexes on purpose.
 496          $sql = "SELECT i.name AS index_name, i.is_unique, ic.index_column_id, c.name AS column_name
 497                    FROM sys.indexes i
 498                    JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
 499                    JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
 500                    JOIN sys.tables t ON i.object_id = t.object_id
 501                   WHERE t.name = '$tablename' AND i.is_primary_key = 0
 502                ORDER BY i.name, i.index_id, ic.index_column_id";
 503  
 504          $this->query_start($sql, null, SQL_QUERY_AUX);
 505          $result = sqlsrv_query($this->sqlsrv, $sql);
 506          $this->query_end($result);
 507  
 508          if ($result) {
 509              $lastindex = '';
 510              $unique = false;
 511              $columns = array ();
 512  
 513              while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
 514                  if ($lastindex and $lastindex != $row['index_name'])
 515                      { // Save lastindex to $indexes and reset info
 516                      $indexes[$lastindex] = array
 517                       (
 518                        'unique' => $unique,
 519                        'columns' => $columns
 520                       );
 521  
 522                      $unique = false;
 523                      $columns = array ();
 524                  }
 525                  $lastindex = $row['index_name'];
 526                  $unique = empty($row['is_unique']) ? false : true;
 527                  $columns[] = $row['column_name'];
 528              }
 529  
 530              if ($lastindex) { // Add the last one if exists
 531                  $indexes[$lastindex] = array
 532                   (
 533                    'unique' => $unique,
 534                    'columns' => $columns
 535                   );
 536              }
 537  
 538              $this->free_result($result);
 539          }
 540          return $indexes;
 541      }
 542  
 543      /**
 544       * Returns detailed information about columns in table.
 545       *
 546       * @param string $table name
 547       * @return array array of database_column_info objects indexed with column names
 548       */
 549      protected function fetch_columns(string $table): array {
 550          $structure = array();
 551  
 552          if (!$this->temptables->is_temptable($table)) { // normal table, get metadata from own schema
 553              $sql = "SELECT column_name AS name,
 554                             data_type AS type,
 555                             numeric_precision AS max_length,
 556                             character_maximum_length AS char_max_length,
 557                             numeric_scale AS scale,
 558                             is_nullable AS is_nullable,
 559                             columnproperty(object_id(quotename(table_schema) + '.' + quotename(table_name)), column_name, 'IsIdentity') AS auto_increment,
 560                             column_default AS default_value
 561                        FROM INFORMATION_SCHEMA.COLUMNS
 562                       WHERE table_name = '{".$table."}'
 563                    ORDER BY ordinal_position";
 564          } else { // temp table, get metadata from tempdb schema
 565              $sql = "SELECT column_name AS name,
 566                             data_type AS type,
 567                             numeric_precision AS max_length,
 568                             character_maximum_length AS char_max_length,
 569                             numeric_scale AS scale,
 570                             is_nullable AS is_nullable,
 571                             columnproperty(object_id(quotename(table_schema) + '.' + quotename(table_name)), column_name, 'IsIdentity') AS auto_increment,
 572                             column_default AS default_value
 573                        FROM tempdb.INFORMATION_SCHEMA.COLUMNS ".
 574              // check this statement
 575              // JOIN tempdb..sysobjects ON name = table_name
 576              // WHERE id = object_id('tempdb..{".$table."}')
 577                      "WHERE table_name LIKE '{".$table."}__________%'
 578                    ORDER BY ordinal_position";
 579          }
 580  
 581          list($sql, $params, $type) = $this->fix_sql_params($sql, null);
 582  
 583          $this->query_start($sql, null, SQL_QUERY_AUX);
 584          $result = sqlsrv_query($this->sqlsrv, $sql);
 585          $this->query_end($result);
 586  
 587          if (!$result) {
 588              return array ();
 589          }
 590  
 591          while ($rawcolumn = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
 592  
 593              $rawcolumn = (object)$rawcolumn;
 594  
 595              $info = new stdClass();
 596              $info->name = $rawcolumn->name;
 597              $info->type = $rawcolumn->type;
 598              $info->meta_type = $this->sqlsrvtype2moodletype($info->type);
 599  
 600              // Prepare auto_increment info
 601              $info->auto_increment = $rawcolumn->auto_increment ? true : false;
 602  
 603              // Define type for auto_increment columns
 604              $info->meta_type = ($info->auto_increment && $info->meta_type == 'I') ? 'R' : $info->meta_type;
 605  
 606              // id columns being auto_incremnt are PK by definition
 607              $info->primary_key = ($info->name == 'id' && $info->meta_type == 'R' && $info->auto_increment);
 608  
 609              if ($info->meta_type === 'C' and $rawcolumn->char_max_length == -1) {
 610                  // This is NVARCHAR(MAX), not a normal NVARCHAR.
 611                  $info->max_length = -1;
 612                  $info->meta_type = 'X';
 613              } else {
 614                  // Put correct length for character and LOB types
 615                  $info->max_length = $info->meta_type == 'C' ? $rawcolumn->char_max_length : $rawcolumn->max_length;
 616                  $info->max_length = ($info->meta_type == 'X' || $info->meta_type == 'B') ? -1 : $info->max_length;
 617              }
 618  
 619              // Scale
 620              $info->scale = $rawcolumn->scale;
 621  
 622              // Prepare not_null info
 623              $info->not_null = $rawcolumn->is_nullable == 'NO' ? true : false;
 624  
 625              // Process defaults
 626              $info->has_default = !empty($rawcolumn->default_value);
 627              if ($rawcolumn->default_value === NULL) {
 628                  $info->default_value = NULL;
 629              } else {
 630                  $info->default_value = preg_replace("/^[\(N]+[']?(.*?)[']?[\)]+$/", '\\1', $rawcolumn->default_value);
 631              }
 632  
 633              // Process binary
 634              $info->binary = $info->meta_type == 'B' ? true : false;
 635  
 636              $structure[$info->name] = new database_column_info($info);
 637          }
 638          $this->free_result($result);
 639  
 640          return $structure;
 641      }
 642  
 643      /**
 644       * Normalise values based in RDBMS dependencies (booleans, LOBs...)
 645       *
 646       * @param database_column_info $column column metadata corresponding with the value we are going to normalise
 647       * @param mixed $value value we are going to normalise
 648       * @return mixed the normalised value
 649       */
 650      protected function normalise_value($column, $value) {
 651          $this->detect_objects($value);
 652  
 653          if (is_bool($value)) {                               // Always, convert boolean to int
 654              $value = (int)$value;
 655          }                                                    // And continue processing because text columns with numeric info need special handling below
 656  
 657          if ($column->meta_type == 'B')
 658              { // BLOBs need to be properly "packed", but can be inserted directly if so.
 659              if (!is_null($value)) {               // If value not null, unpack it to unquoted hexadecimal byte-string format
 660                  $value = unpack('H*hex', $value); // we leave it as array, so emulate_bound_params() can detect it
 661              }                                                // easily and "bind" the param ok.
 662  
 663          } else if ($column->meta_type == 'X') {              // sqlsrv doesn't cast from int to text, so if text column
 664              if (is_numeric($value)) { // and is numeric value then cast to string
 665                  $value = array('numstr' => (string)$value);  // and put into array, so emulate_bound_params() will know how
 666              }                                                // to "bind" the param ok, avoiding reverse conversion to number
 667          } else if ($value === '') {
 668  
 669              if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') {
 670                  $value = 0; // prevent '' problems in numeric fields
 671              }
 672          }
 673          return $value;
 674      }
 675  
 676      /**
 677       * Selectively call sqlsrv_free_stmt(), avoiding some warnings without using the horrible @
 678       *
 679       * @param sqlsrv_resource $resource resource to be freed if possible
 680       * @return bool
 681       */
 682      private function free_result($resource) {
 683          if (!is_bool($resource)) { // true/false resources cannot be freed
 684              return sqlsrv_free_stmt($resource);
 685          }
 686      }
 687  
 688      /**
 689       * Provides mapping between sqlsrv native data types and moodle_database - database_column_info - ones)
 690       *
 691       * @param string $sqlsrv_type native sqlsrv data type
 692       * @return string 1-char database_column_info data type
 693       */
 694      private function sqlsrvtype2moodletype($sqlsrv_type) {
 695          $type = null;
 696  
 697          switch (strtoupper($sqlsrv_type)) {
 698            case 'BIT':
 699             $type = 'L';
 700             break;
 701  
 702            case 'INT':
 703            case 'SMALLINT':
 704            case 'INTEGER':
 705            case 'BIGINT':
 706             $type = 'I';
 707             break;
 708  
 709            case 'DECIMAL':
 710            case 'REAL':
 711            case 'FLOAT':
 712             $type = 'N';
 713             break;
 714  
 715            case 'VARCHAR':
 716            case 'NVARCHAR':
 717             $type = 'C';
 718             break;
 719  
 720            case 'TEXT':
 721            case 'NTEXT':
 722            case 'VARCHAR(MAX)':
 723            case 'NVARCHAR(MAX)':
 724             $type = 'X';
 725             break;
 726  
 727            case 'IMAGE':
 728            case 'VARBINARY':
 729            case 'VARBINARY(MAX)':
 730             $type = 'B';
 731             break;
 732  
 733            case 'DATETIME':
 734             $type = 'D';
 735             break;
 736           }
 737  
 738          if (!$type) {
 739              throw new dml_exception('invalidsqlsrvnativetype', $sqlsrv_type);
 740          }
 741          return $type;
 742      }
 743  
 744      /**
 745       * Do NOT use in code, to be used by database_manager only!
 746       * @param string|array $sql query
 747       * @param array|null $tablenames an array of xmldb table names affected by this request.
 748       * @return bool true
 749       * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors.
 750       */
 751      public function change_database_structure($sql, $tablenames = null) {
 752          $this->get_manager(); // Includes DDL exceptions classes ;-)
 753          $sqls = (array)$sql;
 754  
 755          try {
 756              foreach ($sqls as $sql) {
 757                  $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
 758                  $result = sqlsrv_query($this->sqlsrv, $sql);
 759                  $this->query_end($result);
 760              }
 761          } catch (ddl_change_structure_exception $e) {
 762              $this->reset_caches($tablenames);
 763              throw $e;
 764          }
 765  
 766          $this->reset_caches($tablenames);
 767          return true;
 768      }
 769  
 770      /**
 771       * Prepare the array of params for native binding
 772       */
 773      protected function build_native_bound_params(array $params = null) {
 774  
 775          return null;
 776      }
 777  
 778      /**
 779       * Workaround for SQL*Server Native driver similar to MSSQL driver for
 780       * consistent behavior.
 781       */
 782      protected function emulate_bound_params($sql, array $params = null) {
 783  
 784          if (empty($params)) {
 785              return $sql;
 786          }
 787          // ok, we have verified sql statement with ? and correct number of params
 788          $parts = array_reverse(explode('?', $sql));
 789          $return = array_pop($parts);
 790          foreach ($params as $param) {
 791              if (is_bool($param)) {
 792                  $return .= (int)$param;
 793              } else if (is_array($param) && isset($param['hex'])) { // detect hex binary, bind it specially
 794                  $return .= '0x'.$param['hex'];
 795              } else if (is_array($param) && isset($param['numstr'])) { // detect numerical strings that *must not*
 796                  $return .= "N'{$param['numstr']}'";                   // be converted back to number params, but bound as strings
 797              } else if (is_null($param)) {
 798                  $return .= 'NULL';
 799  
 800              } else if (is_number($param)) { // we can not use is_numeric() because it eats leading zeros from strings like 0045646
 801                  $return .= "'$param'"; // this is a hack for MDL-23997, we intentionally use string because it is compatible with both nvarchar and int types
 802              } else if (is_float($param)) {
 803                  $return .= $param;
 804              } else {
 805                  $param = str_replace("'", "''", $param);
 806                  $param = str_replace("\0", "", $param);
 807                  $return .= "N'$param'";
 808              }
 809  
 810              $return .= array_pop($parts);
 811          }
 812          return $return;
 813      }
 814  
 815      /**
 816       * Execute general sql query. Should be used only when no other method suitable.
 817       * Do NOT use this to make changes in db structure, use database_manager methods instead!
 818       * @param string $sql query
 819       * @param array $params query parameters
 820       * @return bool true
 821       * @throws dml_exception A DML specific exception is thrown for any errors.
 822       */
 823      public function execute($sql, array $params = null) {
 824          if (strpos($sql, ';') !== false) {
 825              throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
 826          }
 827          $this->do_query($sql, $params, SQL_QUERY_UPDATE);
 828          return true;
 829      }
 830  
 831      /**
 832       * Whether the given SQL statement has the ORDER BY clause in the main query.
 833       *
 834       * @param string $sql the SQL statement
 835       * @return bool true if the main query has the ORDER BY clause; otherwise, false.
 836       */
 837      protected static function has_query_order_by(string $sql) {
 838          $sqltoupper = strtoupper($sql);
 839          // Fail fast if there is no ORDER BY clause in the original query.
 840          if (strpos($sqltoupper, 'ORDER BY') === false) {
 841              return false;
 842          }
 843  
 844          // Search for an ORDER BY clause in the main query, not in any subquery (not always allowed in MSSQL)
 845          // or in clauses like OVER with a window function e.g. ROW_NUMBER() OVER (ORDER BY ...) or RANK() OVER (ORDER BY ...):
 846          // use PHP PCRE recursive patterns to remove everything found within round brackets.
 847          $mainquery = preg_replace('/\(((?>[^()]+)|(?R))*\)/', '()', $sqltoupper);
 848          if (strpos($mainquery, 'ORDER BY') !== false) {
 849              return true;
 850          }
 851  
 852          return false;
 853      }
 854  
 855      /**
 856       * Get a number of records as a moodle_recordset using a SQL statement.
 857       *
 858       * Since this method is a little less readable, use of it should be restricted to
 859       * code where it's possible there might be large datasets being returned.  For known
 860       * small datasets use get_records_sql - it leads to simpler code.
 861       *
 862       * The return type is like:
 863       * @see function get_recordset.
 864       *
 865       * @param string $sql the SQL select query to execute.
 866       * @param array $params array of sql parameters
 867       * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 868       * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 869       * @return moodle_recordset instance
 870       * @throws dml_exception A DML specific exception is thrown for any errors.
 871       */
 872      public function get_recordset_sql($sql, array $params = null, $limitfrom = 0, $limitnum = 0) {
 873  
 874          list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum);
 875          $needscrollable = (bool)$limitfrom; // To determine if we'll need to perform scroll to $limitfrom.
 876  
 877          if ($limitfrom or $limitnum) {
 878              if (!$this->supportsoffsetfetch) {
 879                  if ($limitnum >= 1) { // Only apply TOP clause if we have any limitnum (limitfrom offset is handled later).
 880                      $fetch = $limitfrom + $limitnum;
 881                      if (PHP_INT_MAX - $limitnum < $limitfrom) { // Check PHP_INT_MAX overflow.
 882                          $fetch = PHP_INT_MAX;
 883                      }
 884                      $sql = preg_replace('/^([\s(])*SELECT([\s]+(DISTINCT|ALL))?(?!\s*TOP\s*\()/i',
 885                                          "\\1SELECT\\2 TOP $fetch", $sql);
 886                  }
 887              } else {
 888                  $needscrollable = false; // Using supported fetch/offset, no need to scroll anymore.
 889                  $sql = (substr($sql, -1) === ';') ? substr($sql, 0, -1) : $sql;
 890                  // We need ORDER BY to use FETCH/OFFSET.
 891                  // Ordering by first column shouldn't break anything if there was no order in the first place.
 892                  if (!self::has_query_order_by($sql)) {
 893                      $sql .= " ORDER BY 1";
 894                  }
 895  
 896                  $sql .= " OFFSET ".$limitfrom." ROWS ";
 897  
 898                  if ($limitnum > 0) {
 899                      $sql .= " FETCH NEXT ".$limitnum." ROWS ONLY";
 900                  }
 901              }
 902          }
 903  
 904          // Add WITH (NOLOCK) to any temp tables.
 905          $sql = $this->add_no_lock_to_temp_tables($sql);
 906  
 907          $result = $this->do_query($sql, $params, SQL_QUERY_SELECT, false, $needscrollable);
 908  
 909          if ($needscrollable) { // Skip $limitfrom records.
 910              sqlsrv_fetch($result, SQLSRV_SCROLL_ABSOLUTE, $limitfrom - 1);
 911          }
 912          return $this->create_recordset($result);
 913      }
 914  
 915      /**
 916       * Use NOLOCK on any temp tables. Since it's a temp table and uncommitted reads are low risk anyway.
 917       *
 918       * @param string $sql the SQL select query to execute.
 919       * @return string The SQL, with WITH (NOLOCK) added to all temp tables
 920       */
 921      protected function add_no_lock_to_temp_tables($sql) {
 922          return preg_replace_callback('/(\{([a-z][a-z0-9_]*)\})(\s+(\w+))?/', function($matches) {
 923              $table = $matches[1]; // With the braces, so we can put it back in the query.
 924              $name = $matches[2]; // Without the braces, so we can check if it's a temptable.
 925              $tail = isset($matches[3]) ? $matches[3] : ''; // Catch the next word afterwards so that we can check if it's an alias.
 926              $replacement = $matches[0]; // The table and the word following it, so we can replace it back if no changes are needed.
 927  
 928              if ($this->temptables && $this->temptables->is_temptable($name)) {
 929                  if (!empty($tail)) {
 930                      if (in_array(strtolower(trim($tail)), $this->reservewords)) {
 931                          // If the table is followed by a reserve word, it's not an alias so put the WITH (NOLOCK) in between.
 932                          return $table . ' WITH (NOLOCK)' . $tail;
 933                      }
 934                  }
 935                  // If the table is not followed by a reserve word, put the WITH (NOLOCK) after the whole match.
 936                  return $replacement . ' WITH (NOLOCK)';
 937              } else {
 938                  return $replacement;
 939              }
 940          }, $sql);
 941      }
 942  
 943      /**
 944       * Create a record set and initialize with first row
 945       *
 946       * @param mixed $result
 947       * @return sqlsrv_native_moodle_recordset
 948       */
 949      protected function create_recordset($result) {
 950          $rs = new sqlsrv_native_moodle_recordset($result, $this);
 951          $this->recordsets[] = $rs;
 952          return $rs;
 953      }
 954  
 955      /**
 956       * Do not use outside of recordset class.
 957       * @internal
 958       * @param sqlsrv_native_moodle_recordset $rs
 959       */
 960      public function recordset_closed(sqlsrv_native_moodle_recordset $rs) {
 961          if ($key = array_search($rs, $this->recordsets, true)) {
 962              unset($this->recordsets[$key]);
 963          }
 964      }
 965  
 966      /**
 967       * Get a number of records as an array of objects using a SQL statement.
 968       *
 969       * Return value is like:
 970       * @see function get_records.
 971       *
 972       * @param string $sql the SQL select query to execute. The first column of this SELECT statement
 973       *   must be a unique value (usually the 'id' field), as it will be used as the key of the
 974       *   returned array.
 975       * @param array $params array of sql parameters
 976       * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 977       * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 978       * @return array of objects, or empty array if no records were found
 979       * @throws dml_exception A DML specific exception is thrown for any errors.
 980       */
 981      public function get_records_sql($sql, array $params = null, $limitfrom = 0, $limitnum = 0) {
 982  
 983          $rs = $this->get_recordset_sql($sql, $params, $limitfrom, $limitnum);
 984  
 985          $results = array();
 986  
 987          foreach ($rs as $row) {
 988              $id = reset($row);
 989  
 990              if (isset($results[$id])) {
 991                  $colname = key($row);
 992                  debugging("Did you remember to make the first column something unique in your call to get_records? Duplicate value '$id' found in column '$colname'.", DEBUG_DEVELOPER);
 993              }
 994              $results[$id] = (object)$row;
 995          }
 996          $rs->close();
 997  
 998          return $results;
 999      }
1000  
1001      /**
1002       * Selects records and return values (first field) as an array using a SQL statement.
1003       *
1004       * @param string $sql The SQL query
1005       * @param array $params array of sql parameters
1006       * @return array of values
1007       * @throws dml_exception A DML specific exception is thrown for any errors.
1008       */
1009      public function get_fieldset_sql($sql, array $params = null) {
1010  
1011          $rs = $this->get_recordset_sql($sql, $params);
1012  
1013          $results = array ();
1014  
1015          foreach ($rs as $row) {
1016              $results[] = reset($row);
1017          }
1018          $rs->close();
1019  
1020          return $results;
1021      }
1022  
1023      /**
1024       * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
1025       * @param string $table name
1026       * @param mixed $params data record as object or array
1027       * @param bool $returnit return it of inserted record
1028       * @param bool $bulk true means repeated inserts expected
1029       * @param bool $customsequence true if 'id' included in $params, disables $returnid
1030       * @return bool|int true or new id
1031       * @throws dml_exception A DML specific exception is thrown for any errors.
1032       */
1033      public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
1034          if (!is_array($params)) {
1035              $params = (array)$params;
1036          }
1037  
1038          $isidentity = false;
1039  
1040          if ($customsequence) {
1041              if (!isset($params['id'])) {
1042                  throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
1043              }
1044  
1045              $returnid = false;
1046              $columns = $this->get_columns($table);
1047              if (isset($columns['id']) and $columns['id']->auto_increment) {
1048                  $isidentity = true;
1049              }
1050  
1051              // Disable IDENTITY column before inserting record with id, only if the
1052              // column is identity, from meta information.
1053              if ($isidentity) {
1054                  $sql = 'SET IDENTITY_INSERT {'.$table.'} ON'; // Yes, it' ON!!
1055                  $this->do_query($sql, null, SQL_QUERY_AUX);
1056              }
1057  
1058          } else {
1059              unset($params['id']);
1060          }
1061  
1062          if (empty($params)) {
1063              throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
1064          }
1065          $fields = implode(',', array_keys($params));
1066          $qms = array_fill(0, count($params), '?');
1067          $qms = implode(',', $qms);
1068          $sql = "INSERT INTO {" . $table . "} ($fields) VALUES($qms)";
1069          $query_id = $this->do_query($sql, $params, SQL_QUERY_INSERT);
1070  
1071          if ($customsequence) {
1072              // Enable IDENTITY column after inserting record with id, only if the
1073              // column is identity, from meta information.
1074              if ($isidentity) {
1075                  $sql = 'SET IDENTITY_INSERT {'.$table.'} OFF'; // Yes, it' OFF!!
1076                  $this->do_query($sql, null, SQL_QUERY_AUX);
1077              }
1078          }
1079  
1080          if ($returnid) {
1081              $id = $this->sqlsrv_fetch_id();
1082              return $id;
1083          } else {
1084              return true;
1085          }
1086      }
1087  
1088      /**
1089       * Get the ID of the current action
1090       *
1091       * @return mixed ID
1092       */
1093      private function sqlsrv_fetch_id() {
1094          $query_id = sqlsrv_query($this->sqlsrv, 'SELECT SCOPE_IDENTITY()');
1095          if ($query_id === false) {
1096              $dberr = $this->get_last_error();
1097              return false;
1098          }
1099          $row = $this->sqlsrv_fetchrow($query_id);
1100          return (int)$row[0];
1101      }
1102  
1103      /**
1104       * Fetch a single row into an numbered array
1105       *
1106       * @param mixed $query_id
1107       */
1108      private function sqlsrv_fetchrow($query_id) {
1109          $row = sqlsrv_fetch_array($query_id, SQLSRV_FETCH_NUMERIC);
1110          if ($row === false) {
1111              $dberr = $this->get_last_error();
1112              return false;
1113          }
1114  
1115          foreach ($row as $key => $value) {
1116              $row[$key] = ($value === ' ' || $value === NULL) ? '' : $value;
1117          }
1118          return $row;
1119      }
1120  
1121      /**
1122       * Insert a record into a table and return the "id" field if required.
1123       *
1124       * Some conversions and safety checks are carried out. Lobs are supported.
1125       * If the return ID isn't required, then this just reports success as true/false.
1126       * $data is an object containing needed data
1127       * @param string $table The database table to be inserted into
1128       * @param object|array $dataobject A data object with values for one or more fields in the record
1129       * @param bool $returnid Should the id of the newly created record entry be returned? If this option is not requested then true/false is returned.
1130       * @return bool|int true or new id
1131       * @throws dml_exception A DML specific exception is thrown for any errors.
1132       */
1133      public function insert_record($table, $dataobject, $returnid = true, $bulk = false) {
1134          $dataobject = (array)$dataobject;
1135  
1136          $columns = $this->get_columns($table);
1137          if (empty($columns)) {
1138              throw new dml_exception('ddltablenotexist', $table);
1139          }
1140  
1141          $cleaned = array ();
1142  
1143          foreach ($dataobject as $field => $value) {
1144              if ($field === 'id') {
1145                  continue;
1146              }
1147              if (!isset($columns[$field])) {
1148                  continue;
1149              }
1150              $column = $columns[$field];
1151              $cleaned[$field] = $this->normalise_value($column, $value);
1152          }
1153  
1154          return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
1155      }
1156  
1157      /**
1158       * Import a record into a table, id field is required.
1159       * Safety checks are NOT carried out. Lobs are supported.
1160       *
1161       * @param string $table name of database table to be inserted into
1162       * @param object $dataobject A data object with values for one or more fields in the record
1163       * @return bool true
1164       * @throws dml_exception A DML specific exception is thrown for any errors.
1165       */
1166      public function import_record($table, $dataobject) {
1167          if (!is_object($dataobject)) {
1168              $dataobject = (object)$dataobject;
1169          }
1170  
1171          $columns = $this->get_columns($table);
1172          $cleaned = array ();
1173  
1174          foreach ($dataobject as $field => $value) {
1175              if (!isset($columns[$field])) {
1176                  continue;
1177              }
1178              $column = $columns[$field];
1179              $cleaned[$field] = $this->normalise_value($column, $value);
1180          }
1181  
1182          $this->insert_record_raw($table, $cleaned, false, false, true);
1183  
1184          return true;
1185      }
1186  
1187      /**
1188       * Update record in database, as fast as possible, no safety checks, lobs not supported.
1189       * @param string $table name
1190       * @param mixed $params data record as object or array
1191       * @param bool true means repeated updates expected
1192       * @return bool true
1193       * @throws dml_exception A DML specific exception is thrown for any errors.
1194       */
1195      public function update_record_raw($table, $params, $bulk = false) {
1196          $params = (array)$params;
1197  
1198          if (!isset($params['id'])) {
1199              throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
1200          }
1201          $id = $params['id'];
1202          unset($params['id']);
1203  
1204          if (empty($params)) {
1205              throw new coding_exception('moodle_database::update_record_raw() no fields found.');
1206          }
1207  
1208          $sets = array ();
1209  
1210          foreach ($params as $field => $value) {
1211              $sets[] = "$field = ?";
1212          }
1213  
1214          $params[] = $id; // last ? in WHERE condition
1215  
1216          $sets = implode(',', $sets);
1217          $sql = "UPDATE {".$table."} SET $sets WHERE id = ?";
1218  
1219          $this->do_query($sql, $params, SQL_QUERY_UPDATE);
1220  
1221          return true;
1222      }
1223  
1224      /**
1225       * Update a record in a table
1226       *
1227       * $dataobject is an object containing needed data
1228       * Relies on $dataobject having a variable "id" to
1229       * specify the record to update
1230       *
1231       * @param string $table The database table to be checked against.
1232       * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
1233       * @param bool true means repeated updates expected
1234       * @return bool true
1235       * @throws dml_exception A DML specific exception is thrown for any errors.
1236       */
1237      public function update_record($table, $dataobject, $bulk = false) {
1238          $dataobject = (array)$dataobject;
1239  
1240          $columns = $this->get_columns($table);
1241          $cleaned = array ();
1242  
1243          foreach ($dataobject as $field => $value) {
1244              if (!isset($columns[$field])) {
1245                  continue;
1246              }
1247              $column = $columns[$field];
1248              $cleaned[$field] = $this->normalise_value($column, $value);
1249          }
1250  
1251          return $this->update_record_raw($table, $cleaned, $bulk);
1252      }
1253  
1254      /**
1255       * Set a single field in every table record which match a particular WHERE clause.
1256       *
1257       * @param string $table The database table to be checked against.
1258       * @param string $newfield the field to set.
1259       * @param string $newvalue the value to set the field to.
1260       * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1261       * @param array $params array of sql parameters
1262       * @return bool true
1263       * @throws dml_exception A DML specific exception is thrown for any errors.
1264       */
1265      public function set_field_select($table, $newfield, $newvalue, $select, array $params = null) {
1266          if ($select) {
1267              $select = "WHERE $select";
1268          }
1269  
1270          if (is_null($params)) {
1271              $params = array ();
1272          }
1273  
1274          // convert params to ? types
1275          list($select, $params, $type) = $this->fix_sql_params($select, $params);
1276  
1277          // Get column metadata
1278          $columns = $this->get_columns($table);
1279          $column = $columns[$newfield];
1280  
1281          $newvalue = $this->normalise_value($column, $newvalue);
1282  
1283          if (is_null($newvalue)) {
1284              $newfield = "$newfield = NULL";
1285          } else {
1286              $newfield = "$newfield = ?";
1287              array_unshift($params, $newvalue);
1288          }
1289          $sql = "UPDATE {".$table."} SET $newfield $select";
1290  
1291          $this->do_query($sql, $params, SQL_QUERY_UPDATE);
1292  
1293          return true;
1294      }
1295  
1296      /**
1297       * Delete one or more records from a table which match a particular WHERE clause.
1298       *
1299       * @param string $table The database table to be checked against.
1300       * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1301       * @param array $params array of sql parameters
1302       * @return bool true
1303       * @throws dml_exception A DML specific exception is thrown for any errors.
1304       */
1305      public function delete_records_select($table, $select, array $params = null) {
1306          if ($select) {
1307              $select = "WHERE $select";
1308          }
1309  
1310          $sql = "DELETE FROM {".$table."} $select";
1311  
1312          // we use SQL_QUERY_UPDATE because we do not know what is in general SQL, delete constant would not be accurate
1313          $this->do_query($sql, $params, SQL_QUERY_UPDATE);
1314  
1315          return true;
1316      }
1317  
1318  
1319      public function sql_cast_char2int($fieldname, $text = false) {
1320          if (!$text) {
1321              return ' CAST(' . $fieldname . ' AS INT) ';
1322          } else {
1323              return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS INT) ';
1324          }
1325      }
1326  
1327      public function sql_cast_char2real($fieldname, $text=false) {
1328          if (!$text) {
1329              return ' CAST(' . $fieldname . ' AS REAL) ';
1330          } else {
1331              return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS REAL) ';
1332          }
1333      }
1334  
1335      public function sql_ceil($fieldname) {
1336          return ' CEILING('.$fieldname.')';
1337      }
1338  
1339      protected function get_collation() {
1340          if (isset($this->collation)) {
1341              return $this->collation;
1342          }
1343          if (!empty($this->dboptions['dbcollation'])) {
1344              // perf speedup
1345              $this->collation = $this->dboptions['dbcollation'];
1346              return $this->collation;
1347          }
1348  
1349          // make some default
1350          $this->collation = 'Latin1_General_CI_AI';
1351  
1352          $sql = "SELECT CAST(DATABASEPROPERTYEX('$this->dbname', 'Collation') AS varchar(255)) AS SQLCollation";
1353          $this->query_start($sql, null, SQL_QUERY_AUX);
1354          $result = sqlsrv_query($this->sqlsrv, $sql);
1355          $this->query_end($result);
1356  
1357          if ($result) {
1358              if ($rawcolumn = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
1359                  $this->collation = reset($rawcolumn);
1360              }
1361              $this->free_result($result);
1362          }
1363  
1364          return $this->collation;
1365      }
1366  
1367      public function sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false) {
1368          $equalop = $notequal ? '<>' : '=';
1369          $collation = $this->get_collation();
1370  
1371          if ($casesensitive) {
1372              $collation = str_replace('_CI', '_CS', $collation);
1373          } else {
1374              $collation = str_replace('_CS', '_CI', $collation);
1375          }
1376          if ($accentsensitive) {
1377              $collation = str_replace('_AI', '_AS', $collation);
1378          } else {
1379              $collation = str_replace('_AS', '_AI', $collation);
1380          }
1381  
1382          return "$fieldname COLLATE $collation $equalop $param";
1383      }
1384  
1385      /**
1386       * Returns 'LIKE' part of a query.
1387       *
1388       * @param string $fieldname usually name of the table column
1389       * @param string $param usually bound query parameter (?, :named)
1390       * @param bool $casesensitive use case sensitive search
1391       * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
1392       * @param bool $notlike true means "NOT LIKE"
1393       * @param string $escapechar escape char for '%' and '_'
1394       * @return string SQL code fragment
1395       */
1396      public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
1397          if (strpos($param, '%') !== false) {
1398              debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)');
1399          }
1400  
1401          $collation = $this->get_collation();
1402          $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
1403  
1404          if ($casesensitive) {
1405              $collation = str_replace('_CI', '_CS', $collation);
1406          } else {
1407              $collation = str_replace('_CS', '_CI', $collation);
1408          }
1409          if ($accentsensitive) {
1410              $collation = str_replace('_AI', '_AS', $collation);
1411          } else {
1412              $collation = str_replace('_AS', '_AI', $collation);
1413          }
1414  
1415          return "$fieldname COLLATE $collation $LIKE $param ESCAPE '$escapechar'";
1416      }
1417  
1418      /**
1419       * Escape common SQL LIKE special characters like '_' or '%', plus '[' & ']' which are also supported in SQL Server
1420       *
1421       * Note that '^' and '-' also have meaning within a LIKE, but only when enclosed within square brackets. As this syntax
1422       * is not supported on all databases and the brackets are always escaped, we don't need special handling of them
1423       *
1424       * @param string $text
1425       * @param string $escapechar
1426       * @return string
1427       */
1428      public function sql_like_escape($text, $escapechar = '\\') {
1429          $text = parent::sql_like_escape($text, $escapechar);
1430  
1431          $text = str_replace('[', $escapechar . '[', $text);
1432          $text = str_replace(']', $escapechar . ']', $text);
1433  
1434          return $text;
1435      }
1436  
1437      public function sql_concat() {
1438          $arr = func_get_args();
1439  
1440          foreach ($arr as $key => $ele) {
1441              $arr[$key] = ' CAST('.$ele.' AS NVARCHAR(255)) ';
1442          }
1443          $s = implode(' + ', $arr);
1444  
1445          if ($s === '') {
1446              return " '' ";
1447          }
1448          return " $s ";
1449      }
1450  
1451      public function sql_concat_join($separator = "' '", $elements = array ()) {
1452          for ($n = count($elements) - 1; $n > 0; $n--) {
1453              array_splice($elements, $n, 0, $separator);
1454          }
1455          return call_user_func_array(array($this, 'sql_concat'), $elements);
1456      }
1457  
1458      public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) {
1459          if ($textfield) {
1460              return ' ('.$this->sql_compare_text($fieldname)." = '') ";
1461          } else {
1462              return " ($fieldname = '') ";
1463          }
1464      }
1465  
1466      /**
1467       * Returns the SQL text to be used to calculate the length in characters of one expression.
1468       * @param string fieldname or expression to calculate its length in characters.
1469       * @return string the piece of SQL code to be used in the statement.
1470       */
1471      public function sql_length($fieldname) {
1472          return ' LEN('.$fieldname.')';
1473      }
1474  
1475      public function sql_order_by_text($fieldname, $numchars = 32) {
1476          return " CONVERT(varchar({$numchars}), {$fieldname})";
1477      }
1478  
1479      /**
1480       * Returns the SQL for returning searching one string for the location of another.
1481       */
1482      public function sql_position($needle, $haystack) {
1483          return "CHARINDEX(($needle), ($haystack))";
1484      }
1485  
1486      /**
1487       * Returns the proper substr() SQL text used to extract substrings from DB
1488       * NOTE: this was originally returning only function name
1489       *
1490       * @param string $expr some string field, no aggregates
1491       * @param mixed $start integer or expression evaluating to int
1492       * @param mixed $length optional integer or expression evaluating to int
1493       * @return string sql fragment
1494       */
1495      public function sql_substr($expr, $start, $length = false) {
1496          if (count(func_get_args()) < 2) {
1497              throw new coding_exception('moodle_database::sql_substr() requires at least two parameters',
1498                  'Originally this function was only returning name of SQL substring function, it now requires all parameters.');
1499          }
1500  
1501          if ($length === false) {
1502              return "SUBSTRING($expr, " . $this->sql_cast_char2int($start) . ", 2^31-1)";
1503          } else {
1504              return "SUBSTRING($expr, " . $this->sql_cast_char2int($start) . ", " . $this->sql_cast_char2int($length) . ")";
1505          }
1506      }
1507  
1508      /**
1509       * Does this driver support tool_replace?
1510       *
1511       * @since Moodle 2.6.1
1512       * @return bool
1513       */
1514      public function replace_all_text_supported() {
1515          return true;
1516      }
1517  
1518      public function session_lock_supported() {
1519          return true;
1520      }
1521  
1522      /**
1523       * Obtain session lock
1524       * @param int $rowid id of the row with session record
1525       * @param int $timeout max allowed time to wait for the lock in seconds
1526       * @return void
1527       */
1528      public function get_session_lock($rowid, $timeout) {
1529          if (!$this->session_lock_supported()) {
1530              return;
1531          }
1532          parent::get_session_lock($rowid, $timeout);
1533  
1534          $timeoutmilli = $timeout * 1000;
1535  
1536          $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1537          // While this may work using proper {call sp_...} calls + binding +
1538          // executing + consuming recordsets, the solution used for the mssql
1539          // driver is working perfectly, so 100% mimic-ing that code.
1540          // $sql = "sp_getapplock '$fullname', 'Exclusive', 'Session',  $timeoutmilli";
1541          $sql = "BEGIN
1542                      DECLARE @result INT
1543                      EXECUTE @result = sp_getapplock @Resource='$fullname',
1544                                                      @LockMode='Exclusive',
1545                                                      @LockOwner='Session',
1546                                                      @LockTimeout='$timeoutmilli'
1547                      SELECT @result
1548                  END";
1549          $this->query_start($sql, null, SQL_QUERY_AUX);
1550          $result = sqlsrv_query($this->sqlsrv, $sql);
1551          $this->query_end($result);
1552  
1553          if ($result) {
1554              $row = sqlsrv_fetch_array($result);
1555              if ($row[0] < 0) {
1556                  throw new dml_sessionwait_exception();
1557              }
1558          }
1559  
1560          $this->free_result($result);
1561      }
1562  
1563      public function release_session_lock($rowid) {
1564          if (!$this->session_lock_supported()) {
1565              return;
1566          }
1567          if (!$this->used_for_db_sessions) {
1568              return;
1569          }
1570  
1571          parent::release_session_lock($rowid);
1572  
1573          $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1574          $sql = "sp_releaseapplock '$fullname', 'Session'";
1575          $this->query_start($sql, null, SQL_QUERY_AUX);
1576          $result = sqlsrv_query($this->sqlsrv, $sql);
1577          $this->query_end($result);
1578          $this->free_result($result);
1579      }
1580  
1581      /**
1582       * Driver specific start of real database transaction,
1583       * this can not be used directly in code.
1584       * @return void
1585       */
1586      protected function begin_transaction() {
1587          // Recordsets do not work well with transactions in SQL Server,
1588          // let's prefetch the recordsets to memory to work around these problems.
1589          foreach ($this->recordsets as $rs) {
1590              $rs->transaction_starts();
1591          }
1592  
1593          $this->query_start('native sqlsrv_begin_transaction', NULL, SQL_QUERY_AUX);
1594          $result = sqlsrv_begin_transaction($this->sqlsrv);
1595          $this->query_end($result);
1596      }
1597  
1598      /**
1599       * Driver specific commit of real database transaction,
1600       * this can not be used directly in code.
1601       * @return void
1602       */
1603      protected function commit_transaction() {
1604          $this->query_start('native sqlsrv_commit', NULL, SQL_QUERY_AUX);
1605          $result = sqlsrv_commit($this->sqlsrv);
1606          $this->query_end($result);
1607      }
1608  
1609      /**
1610       * Driver specific abort of real database transaction,
1611       * this can not be used directly in code.
1612       * @return void
1613       */
1614      protected function rollback_transaction() {
1615          $this->query_start('native sqlsrv_rollback', NULL, SQL_QUERY_AUX);
1616          $result = sqlsrv_rollback($this->sqlsrv);
1617          $this->query_end($result);
1618      }
1619  
1620      /**
1621       * Is fulltext search enabled?.
1622       *
1623       * @return bool
1624       */
1625      public function is_fulltext_search_supported() {
1626          global $CFG;
1627  
1628          $sql = "SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')";
1629          $this->query_start($sql, null, SQL_QUERY_AUX);
1630          $result = sqlsrv_query($this->sqlsrv, $sql);
1631          $this->query_end($result);
1632          if ($result) {
1633              if ($row = sqlsrv_fetch_array($result)) {
1634                  $property = (bool)reset($row);
1635              }
1636          }
1637          $this->free_result($result);
1638  
1639          return !empty($property);
1640      }
1641  }