Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 4.1.x will end 13 November 2023 (12 months).
  • Bug fixes for security issues in 4.1.x will end 10 November 2025 (36 months).
  • PHP version: minimum PHP 7.4.0 Note: minimum PHP version has increased since Moodle 4.0. PHP 8.0.x is supported too.

Differences Between: [Versions 310 and 401] [Versions 311 and 401] [Versions 39 and 401] [Versions 400 and 401] [Versions 401 and 402] [Versions 401 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|null $params An 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, $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              $rowarray = (array)$row;
 989              $id = reset($rowarray);
 990  
 991              if (isset($results[$id])) {
 992                  $colname = key($rowarray);
 993                  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);
 994              }
 995              $results[$id] = (object)$row;
 996          }
 997          $rs->close();
 998  
 999          return $results;
1000      }
1001  
1002      /**
1003       * Selects records and return values (first field) as an array using a SQL statement.
1004       *
1005       * @param string $sql The SQL query
1006       * @param array $params array of sql parameters
1007       * @return array of values
1008       * @throws dml_exception A DML specific exception is thrown for any errors.
1009       */
1010      public function get_fieldset_sql($sql, array $params = null) {
1011  
1012          $rs = $this->get_recordset_sql($sql, $params);
1013  
1014          $results = array ();
1015  
1016          foreach ($rs as $row) {
1017              $rowarray = (array)$row;
1018              $results[] = reset($rowarray);
1019          }
1020          $rs->close();
1021  
1022          return $results;
1023      }
1024  
1025      /**
1026       * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
1027       * @param string $table name
1028       * @param mixed $params data record as object or array
1029       * @param bool $returnit return it of inserted record
1030       * @param bool $bulk true means repeated inserts expected
1031       * @param bool $customsequence true if 'id' included in $params, disables $returnid
1032       * @return bool|int true or new id
1033       * @throws dml_exception A DML specific exception is thrown for any errors.
1034       */
1035      public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
1036          if (!is_array($params)) {
1037              $params = (array)$params;
1038          }
1039  
1040          $isidentity = false;
1041  
1042          if ($customsequence) {
1043              if (!isset($params['id'])) {
1044                  throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
1045              }
1046  
1047              $returnid = false;
1048              $columns = $this->get_columns($table);
1049              if (isset($columns['id']) and $columns['id']->auto_increment) {
1050                  $isidentity = true;
1051              }
1052  
1053              // Disable IDENTITY column before inserting record with id, only if the
1054              // column is identity, from meta information.
1055              if ($isidentity) {
1056                  $sql = 'SET IDENTITY_INSERT {'.$table.'} ON'; // Yes, it' ON!!
1057                  $this->do_query($sql, null, SQL_QUERY_AUX);
1058              }
1059  
1060          } else {
1061              unset($params['id']);
1062          }
1063  
1064          if (empty($params)) {
1065              throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
1066          }
1067          $fields = implode(',', array_keys($params));
1068          $qms = array_fill(0, count($params), '?');
1069          $qms = implode(',', $qms);
1070          $sql = "INSERT INTO {" . $table . "} ($fields) VALUES($qms)";
1071          $query_id = $this->do_query($sql, $params, SQL_QUERY_INSERT);
1072  
1073          if ($customsequence) {
1074              // Enable IDENTITY column after inserting record with id, only if the
1075              // column is identity, from meta information.
1076              if ($isidentity) {
1077                  $sql = 'SET IDENTITY_INSERT {'.$table.'} OFF'; // Yes, it' OFF!!
1078                  $this->do_query($sql, null, SQL_QUERY_AUX);
1079              }
1080          }
1081  
1082          if ($returnid) {
1083              $id = $this->sqlsrv_fetch_id();
1084              return $id;
1085          } else {
1086              return true;
1087          }
1088      }
1089  
1090      /**
1091       * Get the ID of the current action
1092       *
1093       * @return mixed ID
1094       */
1095      private function sqlsrv_fetch_id() {
1096          $query_id = sqlsrv_query($this->sqlsrv, 'SELECT SCOPE_IDENTITY()');
1097          if ($query_id === false) {
1098              $dberr = $this->get_last_error();
1099              return false;
1100          }
1101          $row = $this->sqlsrv_fetchrow($query_id);
1102          return (int)$row[0];
1103      }
1104  
1105      /**
1106       * Fetch a single row into an numbered array
1107       *
1108       * @param mixed $query_id
1109       */
1110      private function sqlsrv_fetchrow($query_id) {
1111          $row = sqlsrv_fetch_array($query_id, SQLSRV_FETCH_NUMERIC);
1112          if ($row === false) {
1113              $dberr = $this->get_last_error();
1114              return false;
1115          }
1116  
1117          foreach ($row as $key => $value) {
1118              $row[$key] = ($value === ' ' || $value === NULL) ? '' : $value;
1119          }
1120          return $row;
1121      }
1122  
1123      /**
1124       * Insert a record into a table and return the "id" field if required.
1125       *
1126       * Some conversions and safety checks are carried out. Lobs are supported.
1127       * If the return ID isn't required, then this just reports success as true/false.
1128       * $data is an object containing needed data
1129       * @param string $table The database table to be inserted into
1130       * @param object|array $dataobject A data object with values for one or more fields in the record
1131       * @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.
1132       * @return bool|int true or new id
1133       * @throws dml_exception A DML specific exception is thrown for any errors.
1134       */
1135      public function insert_record($table, $dataobject, $returnid = true, $bulk = false) {
1136          $dataobject = (array)$dataobject;
1137  
1138          $columns = $this->get_columns($table);
1139          if (empty($columns)) {
1140              throw new dml_exception('ddltablenotexist', $table);
1141          }
1142  
1143          $cleaned = array ();
1144  
1145          foreach ($dataobject as $field => $value) {
1146              if ($field === 'id') {
1147                  continue;
1148              }
1149              if (!isset($columns[$field])) {
1150                  continue;
1151              }
1152              $column = $columns[$field];
1153              $cleaned[$field] = $this->normalise_value($column, $value);
1154          }
1155  
1156          return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
1157      }
1158  
1159      /**
1160       * Import a record into a table, id field is required.
1161       * Safety checks are NOT carried out. Lobs are supported.
1162       *
1163       * @param string $table name of database table to be inserted into
1164       * @param object $dataobject A data object with values for one or more fields in the record
1165       * @return bool true
1166       * @throws dml_exception A DML specific exception is thrown for any errors.
1167       */
1168      public function import_record($table, $dataobject) {
1169          if (!is_object($dataobject)) {
1170              $dataobject = (object)$dataobject;
1171          }
1172  
1173          $columns = $this->get_columns($table);
1174          $cleaned = array ();
1175  
1176          foreach ($dataobject as $field => $value) {
1177              if (!isset($columns[$field])) {
1178                  continue;
1179              }
1180              $column = $columns[$field];
1181              $cleaned[$field] = $this->normalise_value($column, $value);
1182          }
1183  
1184          $this->insert_record_raw($table, $cleaned, false, false, true);
1185  
1186          return true;
1187      }
1188  
1189      /**
1190       * Update record in database, as fast as possible, no safety checks, lobs not supported.
1191       * @param string $table name
1192       * @param mixed $params data record as object or array
1193       * @param bool true means repeated updates expected
1194       * @return bool true
1195       * @throws dml_exception A DML specific exception is thrown for any errors.
1196       */
1197      public function update_record_raw($table, $params, $bulk = false) {
1198          $params = (array)$params;
1199  
1200          if (!isset($params['id'])) {
1201              throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
1202          }
1203          $id = $params['id'];
1204          unset($params['id']);
1205  
1206          if (empty($params)) {
1207              throw new coding_exception('moodle_database::update_record_raw() no fields found.');
1208          }
1209  
1210          $sets = array ();
1211  
1212          foreach ($params as $field => $value) {
1213              $sets[] = "$field = ?";
1214          }
1215  
1216          $params[] = $id; // last ? in WHERE condition
1217  
1218          $sets = implode(',', $sets);
1219          $sql = "UPDATE {".$table."} SET $sets WHERE id = ?";
1220  
1221          $this->do_query($sql, $params, SQL_QUERY_UPDATE);
1222  
1223          return true;
1224      }
1225  
1226      /**
1227       * Update a record in a table
1228       *
1229       * $dataobject is an object containing needed data
1230       * Relies on $dataobject having a variable "id" to
1231       * specify the record to update
1232       *
1233       * @param string $table The database table to be checked against.
1234       * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
1235       * @param bool true means repeated updates expected
1236       * @return bool true
1237       * @throws dml_exception A DML specific exception is thrown for any errors.
1238       */
1239      public function update_record($table, $dataobject, $bulk = false) {
1240          $dataobject = (array)$dataobject;
1241  
1242          $columns = $this->get_columns($table);
1243          $cleaned = array ();
1244  
1245          foreach ($dataobject as $field => $value) {
1246              if (!isset($columns[$field])) {
1247                  continue;
1248              }
1249              $column = $columns[$field];
1250              $cleaned[$field] = $this->normalise_value($column, $value);
1251          }
1252  
1253          return $this->update_record_raw($table, $cleaned, $bulk);
1254      }
1255  
1256      /**
1257       * Set a single field in every table record which match a particular WHERE clause.
1258       *
1259       * @param string $table The database table to be checked against.
1260       * @param string $newfield the field to set.
1261       * @param string $newvalue the value to set the field to.
1262       * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1263       * @param array $params array of sql parameters
1264       * @return bool true
1265       * @throws dml_exception A DML specific exception is thrown for any errors.
1266       */
1267      public function set_field_select($table, $newfield, $newvalue, $select, array $params = null) {
1268          if ($select) {
1269              $select = "WHERE $select";
1270          }
1271  
1272          if (is_null($params)) {
1273              $params = array ();
1274          }
1275  
1276          // convert params to ? types
1277          list($select, $params, $type) = $this->fix_sql_params($select, $params);
1278  
1279          // Get column metadata
1280          $columns = $this->get_columns($table);
1281          $column = $columns[$newfield];
1282  
1283          $newvalue = $this->normalise_value($column, $newvalue);
1284  
1285          if (is_null($newvalue)) {
1286              $newfield = "$newfield = NULL";
1287          } else {
1288              $newfield = "$newfield = ?";
1289              array_unshift($params, $newvalue);
1290          }
1291          $sql = "UPDATE {".$table."} SET $newfield $select";
1292  
1293          $this->do_query($sql, $params, SQL_QUERY_UPDATE);
1294  
1295          return true;
1296      }
1297  
1298      /**
1299       * Delete one or more records from a table which match a particular WHERE clause.
1300       *
1301       * @param string $table The database table to be checked against.
1302       * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1303       * @param array $params array of sql parameters
1304       * @return bool true
1305       * @throws dml_exception A DML specific exception is thrown for any errors.
1306       */
1307      public function delete_records_select($table, $select, array $params = null) {
1308          if ($select) {
1309              $select = "WHERE $select";
1310          }
1311  
1312          $sql = "DELETE FROM {".$table."} $select";
1313  
1314          // we use SQL_QUERY_UPDATE because we do not know what is in general SQL, delete constant would not be accurate
1315          $this->do_query($sql, $params, SQL_QUERY_UPDATE);
1316  
1317          return true;
1318      }
1319  
1320      /**
1321       * Return SQL for casting to char of given field/expression
1322       *
1323       * @param string $field Table field or SQL expression to be cast
1324       * @return string
1325       */
1326      public function sql_cast_to_char(string $field): string {
1327          return "CAST({$field} AS NVARCHAR(MAX))";
1328      }
1329  
1330  
1331      public function sql_cast_char2int($fieldname, $text = false) {
1332          if (!$text) {
1333              return ' CAST(' . $fieldname . ' AS INT) ';
1334          } else {
1335              return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS INT) ';
1336          }
1337      }
1338  
1339      public function sql_cast_char2real($fieldname, $text=false) {
1340          if (!$text) {
1341              return ' CAST(' . $fieldname . ' AS REAL) ';
1342          } else {
1343              return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS REAL) ';
1344          }
1345      }
1346  
1347      public function sql_ceil($fieldname) {
1348          return ' CEILING('.$fieldname.')';
1349      }
1350  
1351      protected function get_collation() {
1352          if (isset($this->collation)) {
1353              return $this->collation;
1354          }
1355          if (!empty($this->dboptions['dbcollation'])) {
1356              // perf speedup
1357              $this->collation = $this->dboptions['dbcollation'];
1358              return $this->collation;
1359          }
1360  
1361          // make some default
1362          $this->collation = 'Latin1_General_CI_AI';
1363  
1364          $sql = "SELECT CAST(DATABASEPROPERTYEX('$this->dbname', 'Collation') AS varchar(255)) AS SQLCollation";
1365          $this->query_start($sql, null, SQL_QUERY_AUX);
1366          $result = sqlsrv_query($this->sqlsrv, $sql);
1367          $this->query_end($result);
1368  
1369          if ($result) {
1370              if ($rawcolumn = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
1371                  $this->collation = reset($rawcolumn);
1372              }
1373              $this->free_result($result);
1374          }
1375  
1376          return $this->collation;
1377      }
1378  
1379      public function sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false) {
1380          $equalop = $notequal ? '<>' : '=';
1381          $collation = $this->get_collation();
1382  
1383          if ($casesensitive) {
1384              $collation = str_replace('_CI', '_CS', $collation);
1385          } else {
1386              $collation = str_replace('_CS', '_CI', $collation);
1387          }
1388          if ($accentsensitive) {
1389              $collation = str_replace('_AI', '_AS', $collation);
1390          } else {
1391              $collation = str_replace('_AS', '_AI', $collation);
1392          }
1393  
1394          return "$fieldname COLLATE $collation $equalop $param";
1395      }
1396  
1397      /**
1398       * Returns 'LIKE' part of a query.
1399       *
1400       * @param string $fieldname usually name of the table column
1401       * @param string $param usually bound query parameter (?, :named)
1402       * @param bool $casesensitive use case sensitive search
1403       * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
1404       * @param bool $notlike true means "NOT LIKE"
1405       * @param string $escapechar escape char for '%' and '_'
1406       * @return string SQL code fragment
1407       */
1408      public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
1409          if (strpos($param, '%') !== false) {
1410              debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)');
1411          }
1412  
1413          $collation = $this->get_collation();
1414          $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
1415  
1416          if ($casesensitive) {
1417              $collation = str_replace('_CI', '_CS', $collation);
1418          } else {
1419              $collation = str_replace('_CS', '_CI', $collation);
1420          }
1421          if ($accentsensitive) {
1422              $collation = str_replace('_AI', '_AS', $collation);
1423          } else {
1424              $collation = str_replace('_AS', '_AI', $collation);
1425          }
1426  
1427          return "$fieldname COLLATE $collation $LIKE $param ESCAPE '$escapechar'";
1428      }
1429  
1430      /**
1431       * Escape common SQL LIKE special characters like '_' or '%', plus '[' & ']' which are also supported in SQL Server
1432       *
1433       * Note that '^' and '-' also have meaning within a LIKE, but only when enclosed within square brackets. As this syntax
1434       * is not supported on all databases and the brackets are always escaped, we don't need special handling of them
1435       *
1436       * @param string $text
1437       * @param string $escapechar
1438       * @return string
1439       */
1440      public function sql_like_escape($text, $escapechar = '\\') {
1441          $text = parent::sql_like_escape($text, $escapechar);
1442  
1443          $text = str_replace('[', $escapechar . '[', $text);
1444          $text = str_replace(']', $escapechar . ']', $text);
1445  
1446          return $text;
1447      }
1448  
1449      public function sql_concat() {
1450          $arr = func_get_args();
1451  
1452          foreach ($arr as $key => $ele) {
1453              $arr[$key] = $this->sql_cast_to_char($ele);
1454          }
1455          $s = implode(' + ', $arr);
1456  
1457          if ($s === '') {
1458              return " '' ";
1459          }
1460          return " $s ";
1461      }
1462  
1463      public function sql_concat_join($separator = "' '", $elements = array ()) {
1464          for ($n = count($elements) - 1; $n > 0; $n--) {
1465              array_splice($elements, $n, 0, $separator);
1466          }
1467          return call_user_func_array(array($this, 'sql_concat'), array_values($elements));
1468      }
1469  
1470      /**
1471       * Return SQL for performing group concatenation on given field/expression
1472       *
1473       * @param string $field
1474       * @param string $separator
1475       * @param string $sort
1476       * @return string
1477       */
1478      public function sql_group_concat(string $field, string $separator = ', ', string $sort = ''): string {
1479          $fieldsort = $sort ? "WITHIN GROUP (ORDER BY {$sort})" : '';
1480          return "STRING_AGG({$field}, '{$separator}') {$fieldsort}";
1481      }
1482  
1483      public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) {
1484          if ($textfield) {
1485              return ' ('.$this->sql_compare_text($fieldname)." = '') ";
1486          } else {
1487              return " ($fieldname = '') ";
1488          }
1489      }
1490  
1491      /**
1492       * Returns the SQL text to be used to calculate the length in characters of one expression.
1493       * @param string fieldname or expression to calculate its length in characters.
1494       * @return string the piece of SQL code to be used in the statement.
1495       */
1496      public function sql_length($fieldname) {
1497          return ' LEN('.$fieldname.')';
1498      }
1499  
1500      public function sql_order_by_text($fieldname, $numchars = 32) {
1501          return " CONVERT(varchar({$numchars}), {$fieldname})";
1502      }
1503  
1504      /**
1505       * Returns the SQL for returning searching one string for the location of another.
1506       */
1507      public function sql_position($needle, $haystack) {
1508          return "CHARINDEX(($needle), ($haystack))";
1509      }
1510  
1511      /**
1512       * Returns the proper substr() SQL text used to extract substrings from DB
1513       * NOTE: this was originally returning only function name
1514       *
1515       * @param string $expr some string field, no aggregates
1516       * @param mixed $start integer or expression evaluating to int
1517       * @param mixed $length optional integer or expression evaluating to int
1518       * @return string sql fragment
1519       */
1520      public function sql_substr($expr, $start, $length = false) {
1521          if (count(func_get_args()) < 2) {
1522              throw new coding_exception('moodle_database::sql_substr() requires at least two parameters',
1523                  'Originally this function was only returning name of SQL substring function, it now requires all parameters.');
1524          }
1525  
1526          if ($length === false) {
1527              return "SUBSTRING($expr, " . $this->sql_cast_char2int($start) . ", 2^31-1)";
1528          } else {
1529              return "SUBSTRING($expr, " . $this->sql_cast_char2int($start) . ", " . $this->sql_cast_char2int($length) . ")";
1530          }
1531      }
1532  
1533      /**
1534       * Does this driver support tool_replace?
1535       *
1536       * @since Moodle 2.6.1
1537       * @return bool
1538       */
1539      public function replace_all_text_supported() {
1540          return true;
1541      }
1542  
1543      public function session_lock_supported() {
1544          return true;
1545      }
1546  
1547      /**
1548       * Obtain session lock
1549       * @param int $rowid id of the row with session record
1550       * @param int $timeout max allowed time to wait for the lock in seconds
1551       * @return void
1552       */
1553      public function get_session_lock($rowid, $timeout) {
1554          if (!$this->session_lock_supported()) {
1555              return;
1556          }
1557          parent::get_session_lock($rowid, $timeout);
1558  
1559          $timeoutmilli = $timeout * 1000;
1560  
1561          $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1562          // While this may work using proper {call sp_...} calls + binding +
1563          // executing + consuming recordsets, the solution used for the mssql
1564          // driver is working perfectly, so 100% mimic-ing that code.
1565          // $sql = "sp_getapplock '$fullname', 'Exclusive', 'Session',  $timeoutmilli";
1566          $sql = "BEGIN
1567                      DECLARE @result INT
1568                      EXECUTE @result = sp_getapplock @Resource='$fullname',
1569                                                      @LockMode='Exclusive',
1570                                                      @LockOwner='Session',
1571                                                      @LockTimeout='$timeoutmilli'
1572                      SELECT @result
1573                  END";
1574          $this->query_start($sql, null, SQL_QUERY_AUX);
1575          $result = sqlsrv_query($this->sqlsrv, $sql);
1576          $this->query_end($result);
1577  
1578          if ($result) {
1579              $row = sqlsrv_fetch_array($result);
1580              if ($row[0] < 0) {
1581                  throw new dml_sessionwait_exception();
1582              }
1583          }
1584  
1585          $this->free_result($result);
1586      }
1587  
1588      public function release_session_lock($rowid) {
1589          if (!$this->session_lock_supported()) {
1590              return;
1591          }
1592          if (!$this->used_for_db_sessions) {
1593              return;
1594          }
1595  
1596          parent::release_session_lock($rowid);
1597  
1598          $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1599          $sql = "sp_releaseapplock '$fullname', 'Session'";
1600          $this->query_start($sql, null, SQL_QUERY_AUX);
1601          $result = sqlsrv_query($this->sqlsrv, $sql);
1602          $this->query_end($result);
1603          $this->free_result($result);
1604      }
1605  
1606      /**
1607       * Driver specific start of real database transaction,
1608       * this can not be used directly in code.
1609       * @return void
1610       */
1611      protected function begin_transaction() {
1612          // Recordsets do not work well with transactions in SQL Server,
1613          // let's prefetch the recordsets to memory to work around these problems.
1614          foreach ($this->recordsets as $rs) {
1615              $rs->transaction_starts();
1616          }
1617  
1618          $this->query_start('native sqlsrv_begin_transaction', NULL, SQL_QUERY_AUX);
1619          $result = sqlsrv_begin_transaction($this->sqlsrv);
1620          $this->query_end($result);
1621      }
1622  
1623      /**
1624       * Driver specific commit of real database transaction,
1625       * this can not be used directly in code.
1626       * @return void
1627       */
1628      protected function commit_transaction() {
1629          $this->query_start('native sqlsrv_commit', NULL, SQL_QUERY_AUX);
1630          $result = sqlsrv_commit($this->sqlsrv);
1631          $this->query_end($result);
1632      }
1633  
1634      /**
1635       * Driver specific abort of real database transaction,
1636       * this can not be used directly in code.
1637       * @return void
1638       */
1639      protected function rollback_transaction() {
1640          $this->query_start('native sqlsrv_rollback', NULL, SQL_QUERY_AUX);
1641          $result = sqlsrv_rollback($this->sqlsrv);
1642          $this->query_end($result);
1643      }
1644  
1645      /**
1646       * Is fulltext search enabled?.
1647       *
1648       * @return bool
1649       */
1650      public function is_fulltext_search_supported() {
1651          global $CFG;
1652  
1653          $sql = "SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')";
1654          $this->query_start($sql, null, SQL_QUERY_AUX);
1655          $result = sqlsrv_query($this->sqlsrv, $sql);
1656          $this->query_end($result);
1657          if ($result) {
1658              if ($row = sqlsrv_fetch_array($result)) {
1659                  $property = (bool)reset($row);
1660              }
1661          }
1662          $this->free_result($result);
1663  
1664          return !empty($property);
1665      }
1666  }