Merge branch 'MDL-38766/MOODLE_23_STABLE' of github.com:kemitix/moodle into MOODLE_23...
[moodle.git] / lib / dml / sqlsrv_native_moodle_database.php
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/>.
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  */
25 defined('MOODLE_INTERNAL') || die();
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');
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 {
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     /** @var array list of open recordsets */
45     protected $recordsets = array();
47     /**
48      * Constructor - instantiates the database, specifying if it's external (connect to other systems) or no (Moodle DB)
49      *              note this has effect to decide if prefix checks must be performed or no
50      * @param bool true means external database used
51      */
52     public function __construct($external=false) {
53         parent::__construct($external);
54     }
56     /**
57      * Detects if all needed PHP stuff installed.
58      * Note: can be used before connect()
59      * @return mixed true if ok, string if something
60      */
61     public function driver_installed() {
62         // use 'function_exists()' rather than 'extension_loaded()' because
63         // the name used by 'extension_loaded()' is case specific! The extension
64         // therefore *could be* mixed case and hence not found.
65         if (!function_exists('sqlsrv_num_rows')) {
66             if (stripos(PHP_OS, 'win') === 0) {
67                 return get_string('nativesqlsrvnodriver', 'install');
68             } else {
69                 return get_string('nativesqlsrvnonwindows', 'install');
70             }
71         }
72         return true;
73     }
75     /**
76      * Returns database family type - describes SQL dialect
77      * Note: can be used before connect()
78      * @return string db family name (mysql, postgres, mssql, sqlsrv, oracle, etc.)
79      */
80     public function get_dbfamily() {
81         return 'mssql';
82     }
84    /**
85      * Returns more specific database driver type
86      * Note: can be used before connect()
87      * @return string db type mysqli, pgsql, oci, mssql, sqlsrv
88      */
89     protected function get_dbtype() {
90         return 'sqlsrv';
91     }
93    /**
94      * Returns general database library name
95      * Note: can be used before connect()
96      * @return string db type pdo, native
97      */
98     protected function get_dblibrary() {
99         return 'native';
100     }
102     /**
103      * Returns localised database type name
104      * Note: can be used before connect()
105      * @return string
106      */
107     public function get_name() {
108         return get_string('nativesqlsrv', 'install');
109     }
111     /**
112      * Returns localised database configuration help.
113      * Note: can be used before connect()
114      * @return string
115      */
116     public function get_configuration_help() {
117         return get_string('nativesqlsrvhelp', 'install');
118     }
120     /**
121      * Returns localised database description
122      * Note: can be used before connect()
123      * @return string
124      */
125     public function get_configuration_hints() {
126         $str = get_string('databasesettingssub_sqlsrv', 'install');
127         $str .= "<p style='text-align:right'><a href=\"javascript:void(0)\" ";
128         $str .= "onclick=\"return window.open('http://docs.moodle.org/en/Using_the_Microsoft_SQL_Server_Driver_for_PHP')\"";
129         $str .= ">";
130         $str .= '<img src="pix/docs.gif'.'" alt="Docs" class="iconhelp" />';
131         $str .= get_string('moodledocslink', 'install').'</a></p>';
132         return $str;
133     }
135     /**
136      * Connect to db
137      * Must be called before most other methods. (you can call methods that return connection configuration parameters)
138      * @param string $dbhost The database host.
139      * @param string $dbuser The database username.
140      * @param string $dbpass The database username's password.
141      * @param string $dbname The name of the database being connected to.
142      * @param mixed $prefix string|bool The moodle db table name's prefix. false is used for external databases where prefix not used
143      * @param array $dboptions driver specific options
144      * @return bool true
145      * @throws dml_connection_exception if error
146      */
147     public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {
148         $driverstatus = $this->driver_installed();
150         if ($driverstatus !== true) {
151             throw new dml_exception('dbdriverproblem', $driverstatus);
152         }
154         /*
155          * Log all Errors.
156          */
157         sqlsrv_configure("WarningsReturnAsErrors", FALSE);
158         sqlsrv_configure("LogSubsystems", SQLSRV_LOG_SYSTEM_OFF);
159         sqlsrv_configure("LogSeverity", SQLSRV_LOG_SEVERITY_ERROR);
161         $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
162         $this->sqlsrv = sqlsrv_connect($this->dbhost, array
163          (
164           'UID' => $this->dbuser,
165           'PWD' => $this->dbpass,
166           'Database' => $this->dbname,
167           'CharacterSet' => 'UTF-8',
168           'MultipleActiveResultSets' => true,
169           'ConnectionPooling' => !empty($this->dboptions['dbpersist']),
170           'ReturnDatesAsStrings' => true,
171          ));
173         if ($this->sqlsrv === false) {
174             $this->sqlsrv = null;
175             $dberr = $this->get_last_error();
177             throw new dml_connection_exception($dberr);
178         }
180         // Allow quoted identifiers
181         $sql = "SET QUOTED_IDENTIFIER ON";
182         $this->query_start($sql, null, SQL_QUERY_AUX);
183         $result = sqlsrv_query($this->sqlsrv, $sql);
184         $this->query_end($result);
186         $this->free_result($result);
188         // Force ANSI nulls so the NULL check was done by IS NULL and NOT IS NULL
189         // instead of equal(=) and distinct(<>) symbols
190         $sql = "SET ANSI_NULLS ON";
191         $this->query_start($sql, null, SQL_QUERY_AUX);
192         $result = sqlsrv_query($this->sqlsrv, $sql);
193         $this->query_end($result);
195         $this->free_result($result);
197         // Force ANSI warnings so arithmetic/string overflows will be
198         // returning error instead of transparently truncating data
199         $sql = "SET ANSI_WARNINGS ON";
200         $this->query_start($sql, null, SQL_QUERY_AUX);
201         $result = sqlsrv_query($this->sqlsrv, $sql);
202         $this->query_end($result);
204         // Concatenating null with anything MUST return NULL
205         $sql = "SET CONCAT_NULL_YIELDS_NULL  ON";
206         $this->query_start($sql, null, SQL_QUERY_AUX);
207         $result = sqlsrv_query($this->sqlsrv, $sql);
208         $this->query_end($result);
210         $this->free_result($result);
212         // Set transactions isolation level to READ_COMMITTED
213         // prevents dirty reads when using transactions +
214         // is the default isolation level of sqlsrv
215         $sql = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED";
216         $this->query_start($sql, NULL, SQL_QUERY_AUX);
217         $result = sqlsrv_query($this->sqlsrv, $sql);
218         $this->query_end($result);
220         $this->free_result($result);
222         // Connection established and configured, going to instantiate the temptables controller
223         $this->temptables = new sqlsrv_native_moodle_temptables($this);
225         return true;
226     }
228     /**
229      * Close database connection and release all resources
230      * and memory (especially circular memory references).
231      * Do NOT use connect() again, create a new instance if needed.
232      */
233     public function dispose() {
234         parent::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection
236         if ($this->sqlsrv) {
237             sqlsrv_close($this->sqlsrv);
238             $this->sqlsrv = null;
239         }
240     }
242     /**
243      * Called before each db query.
244      * @param string $sql
245      * @param array $params array of parameters
246      * @param int $type type of query
247      * @param mixed $extrainfo driver specific extra information
248      * @return void
249      */
250     protected function query_start($sql, array $params = null, $type, $extrainfo = null) {
251         parent::query_start($sql, $params, $type, $extrainfo);
252     }
254     /**
255      * Called immediately after each db query.
256      * @param mixed db specific result
257      * @return void
258      */
259     protected function query_end($result) {
260         parent::query_end($result);
261     }
263     /**
264      * Returns database server info array
265      * @return array Array containing 'description', 'version' and 'database' (current db) info
266      */
267     public function get_server_info() {
268         static $info;
270         if (!$info) {
271             $server_info = sqlsrv_server_info($this->sqlsrv);
273             if ($server_info) {
274                 $info['description'] = $server_info['SQLServerName'];
275                 $info['version'] = $server_info['SQLServerVersion'];
276                 $info['database'] = $server_info['CurrentDatabase'];
277             }
278         }
279         return $info;
280     }
282     /**
283      * Override: Converts short table name {tablename} to real table name
284      * supporting temp tables (#) if detected
285      *
286      * @param string sql
287      * @return string sql
288      */
289     protected function fix_table_names($sql) {
290         if (preg_match_all('/\{([a-z][a-z0-9_]*)\}/i', $sql, $matches)) {
291             foreach ($matches[0] as $key => $match) {
292                 $name = $matches[1][$key];
294                 if ($this->temptables->is_temptable($name)) {
295                     $sql = str_replace($match, $this->temptables->get_correct_name($name), $sql);
296                 } else {
297                     $sql = str_replace($match, $this->prefix.$name, $sql);
298                 }
299             }
300         }
301         return $sql;
302     }
304     /**
305      * Returns supported query parameter types
306      * @return int bitmask
307      */
308     protected function allowed_param_types() {
309         return SQL_PARAMS_QM;  // sqlsrv 1.1 can bind
310     }
312     /**
313      * Returns last error reported by database engine.
314      * @return string error message
315      */
316     public function get_last_error() {
317         $retErrors = sqlsrv_errors(SQLSRV_ERR_ALL);
318         $errorMessage = 'No errors found';
320         if ($retErrors != null) {
321             $errorMessage = '';
323             foreach ($retErrors as $arrError) {
324                 $errorMessage .= "SQLState: ".$arrError['SQLSTATE']."<br>\n";
325                 $errorMessage .= "Error Code: ".$arrError['code']."<br>\n";
326                 $errorMessage .= "Message: ".$arrError['message']."<br>\n";
327             }
328         }
330         return $errorMessage;
331     }
333     /**
334      * Prepare the query binding and do the actual query.
335      *
336      * @param string $sql The sql statement
337      * @param array $params array of params for binding. If NULL, they are ignored.
338      * @param int $sql_query_type - Type of operation
339      * @param bool $free_result - Default true, transaction query will be freed.
340      * @param bool $scrollable - Default false, to use for quickly seeking to target records
341      * @return resource|bool result
342      */
343     private function do_query($sql, $params, $sql_query_type, $free_result = true, $scrollable = false) {
344         list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
346         /*
347          * Bound variables *are* supported. Until I can get it to work, emulate the bindings
348          * The challenge/problem/bug is that although they work, doing a SELECT SCOPE_IDENTITY()
349          * doesn't return a value (no result set)
350          *
351          * -- somebody from MS
352          */
354         $sql = $this->emulate_bound_params($sql, $params);
355         $this->query_start($sql, $params, $sql_query_type);
356         if (!$scrollable) { // Only supporting next row
357             $result = sqlsrv_query($this->sqlsrv, $sql);
358         } else { // Supporting absolute/relative rows
359             $result = sqlsrv_query($this->sqlsrv, $sql, array(), array('Scrollable' => SQLSRV_CURSOR_STATIC));
360         }
362         if ($result === false) {
363             // TODO do something with error or just use if DEV or DEBUG?
364             $dberr = $this->get_last_error();
365         }
367         $this->query_end($result);
369         if ($free_result) {
370             $this->free_result($result);
371             return true;
372         }
373         return $result;
374     }
376     /**
377      * Return tables in database WITHOUT current prefix.
378      * @param bool $usecache if true, returns list of cached tables.
379      * @return array of table names in lowercase and without prefix
380      */
381     public function get_tables($usecache = true) {
382         if ($usecache and count($this->tables) > 0) {
383             return $this->tables;
384         }
385         $this->tables = array ();
386         $prefix = str_replace('_', '\\_', $this->prefix);
387         $sql = "SELECT table_name
388                   FROM INFORMATION_SCHEMA.TABLES
389                  WHERE table_name LIKE '$prefix%' ESCAPE '\\' AND table_type = 'BASE TABLE'";
391         $this->query_start($sql, null, SQL_QUERY_AUX);
392         $result = sqlsrv_query($this->sqlsrv, $sql);
393         $this->query_end($result);
395         if ($result) {
396             while ($row = sqlsrv_fetch_array($result)) {
397                 $tablename = reset($row);
398                 if ($this->prefix !== '') {
399                     if (strpos($tablename, $this->prefix) !== 0) {
400                         continue;
401                     }
402                     $tablename = substr($tablename, strlen($this->prefix));
403                 }
404                 $this->tables[$tablename] = $tablename;
405             }
406             $this->free_result($result);
407         }
409         // Add the currently available temptables
410         $this->tables = array_merge($this->tables, $this->temptables->get_temptables());
411         return $this->tables;
412     }
414     /**
415      * Return table indexes - everything lowercased.
416      * @param string $table The table we want to get indexes from.
417      * @return array of arrays
418      */
419     public function get_indexes($table) {
420         $indexes = array ();
421         $tablename = $this->prefix.$table;
423         // Indexes aren't covered by information_schema metatables, so we need to
424         // go to sys ones. Skipping primary key indexes on purpose.
425         $sql = "SELECT i.name AS index_name, i.is_unique, ic.index_column_id, c.name AS column_name
426                   FROM sys.indexes i
427                   JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
428                   JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
429                   JOIN sys.tables t ON i.object_id = t.object_id
430                  WHERE t.name = '$tablename' AND i.is_primary_key = 0
431               ORDER BY i.name, i.index_id, ic.index_column_id";
433         $this->query_start($sql, null, SQL_QUERY_AUX);
434         $result = sqlsrv_query($this->sqlsrv, $sql);
435         $this->query_end($result);
437         if ($result) {
438             $lastindex = '';
439             $unique = false;
440             $columns = array ();
442             while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
443                 if ($lastindex and $lastindex != $row['index_name'])
444                     { // Save lastindex to $indexes and reset info
445                     $indexes[$lastindex] = array
446                      (
447                       'unique' => $unique,
448                       'columns' => $columns
449                      );
451                     $unique = false;
452                     $columns = array ();
453                 }
454                 $lastindex = $row['index_name'];
455                 $unique = empty($row['is_unique']) ? false : true;
456                 $columns[] = $row['column_name'];
457             }
459             if ($lastindex) { // Add the last one if exists
460                 $indexes[$lastindex] = array
461                  (
462                   'unique' => $unique,
463                   'columns' => $columns
464                  );
465             }
467             $this->free_result($result);
468         }
469         return $indexes;
470     }
472     /**
473      * Returns detailed information about columns in table. This information is cached internally.
474      * @param string $table name
475      * @param bool $usecache
476      * @return array array of database_column_info objects indexed with column names
477      */
478     public function get_columns($table, $usecache = true) {
479         if ($usecache and isset($this->columns[$table])) {
480             return $this->columns[$table];
481         }
483         $this->columns[$table] = array ();
485         if (!$this->temptables->is_temptable($table)) { // normal table, get metadata from own schema
486             $sql = "SELECT column_name AS name,
487                            data_type AS type,
488                            numeric_precision AS max_length,
489                            character_maximum_length AS char_max_length,
490                            numeric_scale AS scale,
491                            is_nullable AS is_nullable,
492                            columnproperty(object_id(quotename(table_schema) + '.' + quotename(table_name)), column_name, 'IsIdentity') AS auto_increment,
493                            column_default AS default_value
494                       FROM INFORMATION_SCHEMA.COLUMNS
495                      WHERE table_name = '{".$table."}'
496                   ORDER BY ordinal_position";
497         } else { // temp table, get metadata from tempdb schema
498             $sql = "SELECT column_name AS name,
499                            data_type AS type,
500                            numeric_precision AS max_length,
501                            character_maximum_length AS char_max_length,
502                            numeric_scale AS scale,
503                            is_nullable AS is_nullable,
504                            columnproperty(object_id(quotename(table_schema) + '.' + quotename(table_name)), column_name, 'IsIdentity') AS auto_increment,
505                            column_default AS default_value
506                       FROM tempdb.INFORMATION_SCHEMA.COLUMNS ".
507             // check this statement
508             // JOIN tempdb..sysobjects ON name = table_name
509             // WHERE id = object_id('tempdb..{".$table."}')
510                     "WHERE table_name LIKE '{".$table."}__________%'
511                   ORDER BY ordinal_position";
512         }
514         list($sql, $params, $type) = $this->fix_sql_params($sql, null);
516         $this->query_start($sql, null, SQL_QUERY_AUX);
517         $result = sqlsrv_query($this->sqlsrv, $sql);
518         $this->query_end($result);
520         if (!$result) {
521             return array ();
522         }
524         while ($rawcolumn = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
526             $rawcolumn = (object)$rawcolumn;
528             $info = new stdClass();
529             $info->name = $rawcolumn->name;
530             $info->type = $rawcolumn->type;
531             $info->meta_type = $this->sqlsrvtype2moodletype($info->type);
533             // Prepare auto_increment info
534             $info->auto_increment = $rawcolumn->auto_increment ? true : false;
536             // Define type for auto_increment columns
537             $info->meta_type = ($info->auto_increment && $info->meta_type == 'I') ? 'R' : $info->meta_type;
539             // id columns being auto_incremnt are PK by definition
540             $info->primary_key = ($info->name == 'id' && $info->meta_type == 'R' && $info->auto_increment);
542             // Put correct length for character and LOB types
543             $info->max_length = $info->meta_type == 'C' ? $rawcolumn->char_max_length : $rawcolumn->max_length;
544             $info->max_length = ($info->meta_type == 'X' || $info->meta_type == 'B') ? -1 : $info->max_length;
546             // Scale
547             $info->scale = $rawcolumn->scale ? $rawcolumn->scale : false;
549             // Prepare not_null info
550             $info->not_null = $rawcolumn->is_nullable == 'NO' ? true : false;
552             // Process defaults
553             $info->has_default = !empty($rawcolumn->default_value);
554             if ($rawcolumn->default_value === NULL) {
555                 $info->default_value = NULL;
556             } else {
557                 $info->default_value = preg_replace("/^[\(N]+[']?(.*?)[']?[\)]+$/", '\\1', $rawcolumn->default_value);
558             }
560             // Process binary
561             $info->binary = $info->meta_type == 'B' ? true : false;
563             $this->columns[$table][$info->name] = new database_column_info($info);
564         }
565         $this->free_result($result);
567         return $this->columns[$table];
568     }
570     /**
571      * Normalise values based in RDBMS dependencies (booleans, LOBs...)
572      *
573      * @param database_column_info $column column metadata corresponding with the value we are going to normalise
574      * @param mixed $value value we are going to normalise
575      * @return mixed the normalised value
576      */
577     protected function normalise_value($column, $value) {
578         $this->detect_objects($value);
580         if (is_bool($value)) {                               // Always, convert boolean to int
581             $value = (int)$value;
582         }                                                    // And continue processing because text columns with numeric info need special handling below
584         if ($column->meta_type == 'B')
585             { // BLOBs need to be properly "packed", but can be inserted directly if so.
586             if (!is_null($value)) {               // If value not null, unpack it to unquoted hexadecimal byte-string format
587                 $value = unpack('H*hex', $value); // we leave it as array, so emulate_bound_params() can detect it
588             }                                                // easily and "bind" the param ok.
590         } else if ($column->meta_type == 'X') {              // sqlsrv doesn't cast from int to text, so if text column
591             if (is_numeric($value)) { // and is numeric value then cast to string
592                 $value = array('numstr' => (string)$value);  // and put into array, so emulate_bound_params() will know how
593             }                                                // to "bind" the param ok, avoiding reverse conversion to number
594         } else if ($value === '') {
596             if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') {
597                 $value = 0; // prevent '' problems in numeric fields
598             }
599         }
600         return $value;
601     }
603     /**
604      * Selectively call sqlsrv_free_stmt(), avoiding some warnings without using the horrible @
605      *
606      * @param sqlsrv_resource $resource resource to be freed if possible
607      * @return bool
608      */
609     private function free_result($resource) {
610         if (!is_bool($resource)) { // true/false resources cannot be freed
611             return sqlsrv_free_stmt($resource);
612         }
613     }
615     /**
616      * Provides mapping between sqlsrv native data types and moodle_database - database_column_info - ones)
617      *
618      * @param string $sqlsrv_type native sqlsrv data type
619      * @return string 1-char database_column_info data type
620      */
621     private function sqlsrvtype2moodletype($sqlsrv_type) {
622         $type = null;
624         switch (strtoupper($sqlsrv_type)) {
625           case 'BIT':
626            $type = 'L';
627            break;
629           case 'INT':
630           case 'SMALLINT':
631           case 'INTEGER':
632           case 'BIGINT':
633            $type = 'I';
634            break;
636           case 'DECIMAL':
637           case 'REAL':
638           case 'FLOAT':
639            $type = 'N';
640            break;
642           case 'VARCHAR':
643           case 'NVARCHAR':
644            $type = 'C';
645            break;
647           case 'TEXT':
648           case 'NTEXT':
649           case 'VARCHAR(MAX)':
650           case 'NVARCHAR(MAX)':
651            $type = 'X';
652            break;
654           case 'IMAGE':
655           case 'VARBINARY(MAX)':
656            $type = 'B';
657            break;
659           case 'DATETIME':
660            $type = 'D';
661            break;
662          }
664         if (!$type) {
665             throw new dml_exception('invalidsqlsrvnativetype', $sqlsrv_type);
666         }
667         return $type;
668     }
670     /**
671      * Do NOT use in code, to be used by database_manager only!
672      * @param string $sql query
673      * @return bool true
674      * @throws dml_exception A DML specific exception is thrown for any errors.
675      */
676     public function change_database_structure($sql) {
677         $this->reset_caches();
679         $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
680         $result = sqlsrv_query($this->sqlsrv, $sql);
681         $this->query_end($result);
683         return true;
684     }
686     /**
687      * Prepare the array of params for native binding
688      */
689     protected function build_native_bound_params(array $params = null) {
691         return null;
692     }
694     /**
695      * Workaround for SQL*Server Native driver similar to MSSQL driver for
696      * consistent behavior.
697      */
698     protected function emulate_bound_params($sql, array $params = null) {
700         if (empty($params)) {
701             return $sql;
702         }
703         // ok, we have verified sql statement with ? and correct number of params
704         $parts = explode('?', $sql);
705         $return = array_shift($parts);
706         foreach ($params as $param) {
707             if (is_bool($param)) {
708                 $return .= (int)$param;
709             } else if (is_array($param) && isset($param['hex'])) { // detect hex binary, bind it specially
710                 $return .= '0x'.$param['hex'];
711             } else if (is_array($param) && isset($param['numstr'])) { // detect numerical strings that *must not*
712                 $return .= "N'{$param['numstr']}'";                   // be converted back to number params, but bound as strings
713             } else if (is_null($param)) {
714                 $return .= 'NULL';
716             } else if (is_number($param)) { // we can not use is_numeric() because it eats leading zeros from strings like 0045646
717                 $return .= "'$param'"; // this is a hack for MDL-23997, we intentionally use string because it is compatible with both nvarchar and int types
718             } else if (is_float($param)) {
719                 $return .= $param;
720             } else {
721                 $param = str_replace("'", "''", $param);
722                 $return .= "N'$param'";
723             }
725             $return .= array_shift($parts);
726         }
727         return $return;
728     }
730     /**
731      * Execute general sql query. Should be used only when no other method suitable.
732      * Do NOT use this to make changes in db structure, use database_manager methods instead!
733      * @param string $sql query
734      * @param array $params query parameters
735      * @return bool true
736      * @throws dml_exception A DML specific exception is thrown for any errors.
737      */
738     public function execute($sql, array $params = null) {
739         if (strpos($sql, ';') !== false) {
740             throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
741         }
742         $this->do_query($sql, $params, SQL_QUERY_UPDATE);
743         return true;
744     }
746     /**
747      * Get a number of records as a moodle_recordset using a SQL statement.
748      *
749      * Since this method is a little less readable, use of it should be restricted to
750      * code where it's possible there might be large datasets being returned.  For known
751      * small datasets use get_records_sql - it leads to simpler code.
752      *
753      * The return type is like:
754      * @see function get_recordset.
755      *
756      * @param string $sql the SQL select query to execute.
757      * @param array $params array of sql parameters
758      * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
759      * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
760      * @return moodle_recordset instance
761      * @throws dml_exception A DML specific exception is thrown for any errors.
762      */
763     public function get_recordset_sql($sql, array $params = null, $limitfrom = 0, $limitnum = 0) {
764         $limitfrom = (int)$limitfrom;
765         $limitnum = (int)$limitnum;
766         $limitfrom = max(0, $limitfrom);
767         $limitnum = max(0, $limitnum);
769         if ($limitfrom or $limitnum) {
770             if ($limitnum >= 1) { // Only apply TOP clause if we have any limitnum (limitfrom offset is handled later)
771                 $fetch = $limitfrom + $limitnum;
772                 if (PHP_INT_MAX - $limitnum < $limitfrom) { // Check PHP_INT_MAX overflow
773                     $fetch = PHP_INT_MAX;
774                 }
775                 $sql = preg_replace('/^([\s(])*SELECT([\s]+(DISTINCT|ALL))?(?!\s*TOP\s*\()/i',
776                                     "\\1SELECT\\2 TOP $fetch", $sql);
777             }
778         }
779         $result = $this->do_query($sql, $params, SQL_QUERY_SELECT, false, (bool)$limitfrom);
781         if ($limitfrom) { // Skip $limitfrom records
782             sqlsrv_fetch($result, SQLSRV_SCROLL_ABSOLUTE, $limitfrom - 1);
783         }
784         return $this->create_recordset($result);
785     }
787     /**
788      * Create a record set and initialize with first row
789      *
790      * @param mixed $result
791      * @return sqlsrv_native_moodle_recordset
792      */
793     protected function create_recordset($result) {
794         $rs = new sqlsrv_native_moodle_recordset($result, $this);
795         $this->recordsets[] = $rs;
796         return $rs;
797     }
799     /**
800      * Do not use outside of recordset class.
801      * @internal
802      * @param sqlsrv_native_moodle_recordset $rs
803      */
804     public function recordset_closed(sqlsrv_native_moodle_recordset $rs) {
805         if ($key = array_search($rs, $this->recordsets, true)) {
806             unset($this->recordsets[$key]);
807         }
808     }
810     /**
811      * Get a number of records as an array of objects using a SQL statement.
812      *
813      * Return value is like:
814      * @see function get_records.
815      *
816      * @param string $sql the SQL select query to execute. The first column of this SELECT statement
817      *   must be a unique value (usually the 'id' field), as it will be used as the key of the
818      *   returned array.
819      * @param array $params array of sql parameters
820      * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
821      * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
822      * @return array of objects, or empty array if no records were found
823      * @throws dml_exception A DML specific exception is thrown for any errors.
824      */
825     public function get_records_sql($sql, array $params = null, $limitfrom = 0, $limitnum = 0) {
827         $rs = $this->get_recordset_sql($sql, $params, $limitfrom, $limitnum);
829         $results = array();
831         foreach ($rs as $row) {
832             $id = reset($row);
834             if (isset($results[$id])) {
835                 $colname = key($row);
836                 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);
837             }
838             $results[$id] = (object)$row;
839         }
840         $rs->close();
842         return $results;
843     }
845     /**
846      * Selects records and return values (first field) as an array using a SQL statement.
847      *
848      * @param string $sql The SQL query
849      * @param array $params array of sql parameters
850      * @return array of values
851      * @throws dml_exception A DML specific exception is thrown for any errors.
852      */
853     public function get_fieldset_sql($sql, array $params = null) {
855         $rs = $this->get_recordset_sql($sql, $params);
857         $results = array ();
859         foreach ($rs as $row) {
860             $results[] = reset($row);
861         }
862         $rs->close();
864         return $results;
865     }
867     /**
868      * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
869      * @param string $table name
870      * @param mixed $params data record as object or array
871      * @param bool $returnit return it of inserted record
872      * @param bool $bulk true means repeated inserts expected
873      * @param bool $customsequence true if 'id' included in $params, disables $returnid
874      * @return bool|int true or new id
875      * @throws dml_exception A DML specific exception is thrown for any errors.
876      */
877     public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
878         if (!is_array($params)) {
879             $params = (array)$params;
880         }
882         $isidentity = false;
884         if ($customsequence) {
885             if (!isset($params['id'])) {
886                 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
887             }
889             $returnid = false;
890             $columns = $this->get_columns($table);
891             if (isset($columns['id']) and $columns['id']->auto_increment) {
892                 $isidentity = true;
893             }
895             // Disable IDENTITY column before inserting record with id, only if the
896             // column is identity, from meta information.
897             if ($isidentity) {
898                 $sql = 'SET IDENTITY_INSERT {'.$table.'} ON'; // Yes, it' ON!!
899                 $this->do_query($sql, null, SQL_QUERY_AUX);
900             }
902         } else {
903             unset($params['id']);
904         }
906         if (empty($params)) {
907             throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
908         }
909         $fields = implode(',', array_keys($params));
910         $qms = array_fill(0, count($params), '?');
911         $qms = implode(',', $qms);
912         $sql = "INSERT INTO {" . $table . "} ($fields) VALUES($qms)";
913         $query_id = $this->do_query($sql, $params, SQL_QUERY_INSERT);
915         if ($customsequence) {
916             // Enable IDENTITY column after inserting record with id, only if the
917             // column is identity, from meta information.
918             if ($isidentity) {
919                 $sql = 'SET IDENTITY_INSERT {'.$table.'} OFF'; // Yes, it' OFF!!
920                 $this->do_query($sql, null, SQL_QUERY_AUX);
921             }
922         }
924         if ($returnid) {
925             $id = $this->sqlsrv_fetch_id();
926             return $id;
927         } else {
928             return true;
929         }
930     }
932     /**
933      * Get the ID of the current action
934      *
935      * @return mixed ID
936      */
937     private function sqlsrv_fetch_id() {
938         $query_id = sqlsrv_query($this->sqlsrv, 'SELECT SCOPE_IDENTITY()');
939         if ($query_id === false) {
940             $dberr = $this->get_last_error();
941             return false;
942         }
943         $row = $this->sqlsrv_fetchrow($query_id);
944         return (int)$row[0];
945     }
947     /**
948      * Fetch a single row into an numbered array
949      *
950      * @param mixed $query_id
951      */
952     private function sqlsrv_fetchrow($query_id) {
953         $row = sqlsrv_fetch_array($query_id, SQLSRV_FETCH_NUMERIC);
954         if ($row === false) {
955             $dberr = $this->get_last_error();
956             return false;
957         }
959         foreach ($row as $key => $value) {
960             $row[$key] = ($value === ' ' || $value === NULL) ? '' : $value;
961         }
962         return $row;
963     }
965     /**
966      * Insert a record into a table and return the "id" field if required.
967      *
968      * Some conversions and safety checks are carried out. Lobs are supported.
969      * If the return ID isn't required, then this just reports success as true/false.
970      * $data is an object containing needed data
971      * @param string $table The database table to be inserted into
972      * @param object $data A data object with values for one or more fields in the record
973      * @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.
974      * @return bool|int true or new id
975      * @throws dml_exception A DML specific exception is thrown for any errors.
976      */
977     public function insert_record($table, $dataobject, $returnid = true, $bulk = false) {
978         $dataobject = (array)$dataobject;
980         $columns = $this->get_columns($table);
981         $cleaned = array ();
983         foreach ($dataobject as $field => $value) {
984             if ($field === 'id') {
985                 continue;
986             }
987             if (!isset($columns[$field])) {
988                 continue;
989             }
990             $column = $columns[$field];
991             $cleaned[$field] = $this->normalise_value($column, $value);
992         }
994         return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
995     }
997     /**
998      * Import a record into a table, id field is required.
999      * Safety checks are NOT carried out. Lobs are supported.
1000      *
1001      * @param string $table name of database table to be inserted into
1002      * @param object $dataobject A data object with values for one or more fields in the record
1003      * @return bool true
1004      * @throws dml_exception A DML specific exception is thrown for any errors.
1005      */
1006     public function import_record($table, $dataobject) {
1007         if (!is_object($dataobject)) {
1008             $dataobject = (object)$dataobject;
1009         }
1011         $columns = $this->get_columns($table);
1012         $cleaned = array ();
1014         foreach ($dataobject as $field => $value) {
1015             if (!isset($columns[$field])) {
1016                 continue;
1017             }
1018             $column = $columns[$field];
1019             $cleaned[$field] = $this->normalise_value($column, $value);
1020         }
1022         $this->insert_record_raw($table, $cleaned, false, false, true);
1024         return true;
1025     }
1027     /**
1028      * Update record in database, as fast as possible, no safety checks, lobs not supported.
1029      * @param string $table name
1030      * @param mixed $params data record as object or array
1031      * @param bool true means repeated updates expected
1032      * @return bool true
1033      * @throws dml_exception A DML specific exception is thrown for any errors.
1034      */
1035     public function update_record_raw($table, $params, $bulk = false) {
1036         $params = (array)$params;
1038         if (!isset($params['id'])) {
1039             throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
1040         }
1041         $id = $params['id'];
1042         unset($params['id']);
1044         if (empty($params)) {
1045             throw new coding_exception('moodle_database::update_record_raw() no fields found.');
1046         }
1048         $sets = array ();
1050         foreach ($params as $field => $value) {
1051             $sets[] = "$field = ?";
1052         }
1054         $params[] = $id; // last ? in WHERE condition
1056         $sets = implode(',', $sets);
1057         $sql = "UPDATE {".$table."} SET $sets WHERE id = ?";
1059         $this->do_query($sql, $params, SQL_QUERY_UPDATE);
1061         return true;
1062     }
1064     /**
1065      * Update a record in a table
1066      *
1067      * $dataobject is an object containing needed data
1068      * Relies on $dataobject having a variable "id" to
1069      * specify the record to update
1070      *
1071      * @param string $table The database table to be checked against.
1072      * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
1073      * @param bool true means repeated updates expected
1074      * @return bool true
1075      * @throws dml_exception A DML specific exception is thrown for any errors.
1076      */
1077     public function update_record($table, $dataobject, $bulk = false) {
1078         $dataobject = (array)$dataobject;
1080         $columns = $this->get_columns($table);
1081         $cleaned = array ();
1083         foreach ($dataobject as $field => $value) {
1084             if (!isset($columns[$field])) {
1085                 continue;
1086             }
1087             $column = $columns[$field];
1088             $cleaned[$field] = $this->normalise_value($column, $value);
1089         }
1091         return $this->update_record_raw($table, $cleaned, $bulk);
1092     }
1094     /**
1095      * Set a single field in every table record which match a particular WHERE clause.
1096      *
1097      * @param string $table The database table to be checked against.
1098      * @param string $newfield the field to set.
1099      * @param string $newvalue the value to set the field to.
1100      * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1101      * @param array $params array of sql parameters
1102      * @return bool true
1103      * @throws dml_exception A DML specific exception is thrown for any errors.
1104      */
1105     public function set_field_select($table, $newfield, $newvalue, $select, array $params = null) {
1106         if ($select) {
1107             $select = "WHERE $select";
1108         }
1110         if (is_null($params)) {
1111             $params = array ();
1112         }
1114         // convert params to ? types
1115         list($select, $params, $type) = $this->fix_sql_params($select, $params);
1117         // Get column metadata
1118         $columns = $this->get_columns($table);
1119         $column = $columns[$newfield];
1121         $newvalue = $this->normalise_value($column, $newvalue);
1123         if (is_null($newvalue)) {
1124             $newfield = "$newfield = NULL";
1125         } else {
1126             $newfield = "$newfield = ?";
1127             array_unshift($params, $newvalue);
1128         }
1129         $sql = "UPDATE {".$table."} SET $newfield $select";
1131         $this->do_query($sql, $params, SQL_QUERY_UPDATE);
1133         return true;
1134     }
1136     /**
1137      * Delete one or more records from a table which match a particular WHERE clause.
1138      *
1139      * @param string $table The database table to be checked against.
1140      * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1141      * @param array $params array of sql parameters
1142      * @return bool true
1143      * @throws dml_exception A DML specific exception is thrown for any errors.
1144      */
1145     public function delete_records_select($table, $select, array $params = null) {
1146         if ($select) {
1147             $select = "WHERE $select";
1148         }
1150         $sql = "DELETE FROM {".$table."} $select";
1152         // we use SQL_QUERY_UPDATE because we do not know what is in general SQL, delete constant would not be accurate
1153         $this->do_query($sql, $params, SQL_QUERY_UPDATE);
1155         return true;
1156     }
1159     public function sql_cast_char2int($fieldname, $text = false) {
1160         if (!$text) {
1161             return ' CAST(' . $fieldname . ' AS INT) ';
1162         } else {
1163             return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS INT) ';
1164         }
1165     }
1167     public function sql_cast_char2real($fieldname, $text=false) {
1168         if (!$text) {
1169             return ' CAST(' . $fieldname . ' AS REAL) ';
1170         } else {
1171             return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS REAL) ';
1172         }
1173     }
1175     public function sql_ceil($fieldname) {
1176         return ' CEILING('.$fieldname.')';
1177     }
1179     protected function get_collation() {
1180         if (isset($this->collation)) {
1181             return $this->collation;
1182         }
1183         if (!empty($this->dboptions['dbcollation'])) {
1184             // perf speedup
1185             $this->collation = $this->dboptions['dbcollation'];
1186             return $this->collation;
1187         }
1189         // make some default
1190         $this->collation = 'Latin1_General_CI_AI';
1192         $sql = "SELECT CAST(DATABASEPROPERTYEX('$this->dbname', 'Collation') AS varchar(255)) AS SQLCollation";
1193         $this->query_start($sql, null, SQL_QUERY_AUX);
1194         $result = sqlsrv_query($this->sqlsrv, $sql);
1195         $this->query_end($result);
1197         if ($result) {
1198             if ($rawcolumn = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
1199                 $this->collation = reset($rawcolumn);
1200             }
1201             $this->free_result($result);
1202         }
1204         return $this->collation;
1205     }
1207     /**
1208      * Returns 'LIKE' part of a query.
1209      *
1210      * @param string $fieldname usually name of the table column
1211      * @param string $param usually bound query parameter (?, :named)
1212      * @param bool $casesensitive use case sensitive search
1213      * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
1214      * @param bool $notlike true means "NOT LIKE"
1215      * @param string $escapechar escape char for '%' and '_'
1216      * @return string SQL code fragment
1217      */
1218     public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
1219         if (strpos($param, '%') !== false) {
1220             debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)');
1221         }
1223         $collation = $this->get_collation();
1224         $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
1226         if ($casesensitive) {
1227             $collation = str_replace('_CI', '_CS', $collation);
1228         } else {
1229             $collation = str_replace('_CS', '_CI', $collation);
1230         }
1231         if ($accentsensitive) {
1232             $collation = str_replace('_AI', '_AS', $collation);
1233         } else {
1234             $collation = str_replace('_AS', '_AI', $collation);
1235         }
1237         return "$fieldname COLLATE $collation $LIKE $param ESCAPE '$escapechar'";
1238     }
1240     public function sql_concat() {
1241         $arr = func_get_args();
1243         foreach ($arr as $key => $ele) {
1244             $arr[$key] = ' CAST('.$ele.' AS NVARCHAR(255)) ';
1245         }
1246         $s = implode(' + ', $arr);
1248         if ($s === '') {
1249             return " '' ";
1250         }
1251         return " $s ";
1252     }
1254     public function sql_concat_join($separator = "' '", $elements = array ()) {
1255         for ($n = count($elements) - 1; $n > 0; $n--) {
1256             array_splice($elements, $n, 0, $separator);
1257         }
1258         $s = implode(' + ', $elements);
1260         if ($s === '') {
1261             return " '' ";
1262         }
1263         return " $s ";
1264     }
1266     public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) {
1267         if ($textfield) {
1268             return ' ('.$this->sql_compare_text($fieldname)." = '') ";
1269         } else {
1270             return " ($fieldname = '') ";
1271         }
1272     }
1274     /**
1275      * Returns the SQL text to be used to calculate the length in characters of one expression.
1276      * @param string fieldname or expression to calculate its length in characters.
1277      * @return string the piece of SQL code to be used in the statement.
1278      */
1279     public function sql_length($fieldname) {
1280         return ' LEN('.$fieldname.')';
1281     }
1283     public function sql_order_by_text($fieldname, $numchars = 32) {
1284         return ' CONVERT(varchar, '.$fieldname.', '.$numchars.')';
1285     }
1287     /**
1288      * Returns the SQL for returning searching one string for the location of another.
1289      */
1290     public function sql_position($needle, $haystack) {
1291         return "CHARINDEX(($needle), ($haystack))";
1292     }
1294     /**
1295      * Returns the proper substr() SQL text used to extract substrings from DB
1296      * NOTE: this was originally returning only function name
1297      *
1298      * @param string $expr some string field, no aggregates
1299      * @param mixed $start integer or expression evaluating to int
1300      * @param mixed $length optional integer or expression evaluating to int
1301      * @return string sql fragment
1302      */
1303     public function sql_substr($expr, $start, $length = false) {
1304         if (count(func_get_args()) < 2) {
1305             throw new coding_exception('moodle_database::sql_substr() requires at least two parameters',
1306                 'Originally this function was only returning name of SQL substring function, it now requires all parameters.');
1307         }
1309         if ($length === false) {
1310             return "SUBSTRING($expr, $start, (LEN($expr) - $start + 1))";
1311         } else {
1312             return "SUBSTRING($expr, $start, $length)";
1313         }
1314     }
1316     public function session_lock_supported() {
1317         return true;
1318     }
1320     /**
1321      * Obtain session lock
1322      * @param int $rowid id of the row with session record
1323      * @param int $timeout max allowed time to wait for the lock in seconds
1324      * @return void
1325      */
1326     public function get_session_lock($rowid, $timeout) {
1327         if (!$this->session_lock_supported()) {
1328             return;
1329         }
1330         parent::get_session_lock($rowid, $timeout);
1332         $timeoutmilli = $timeout * 1000;
1334         $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1335         // While this may work using proper {call sp_...} calls + binding +
1336         // executing + consuming recordsets, the solution used for the mssql
1337         // driver is working perfectly, so 100% mimic-ing that code.
1338         // $sql = "sp_getapplock '$fullname', 'Exclusive', 'Session',  $timeoutmilli";
1339         $sql = "BEGIN
1340                     DECLARE @result INT
1341                     EXECUTE @result = sp_getapplock @Resource='$fullname',
1342                                                     @LockMode='Exclusive',
1343                                                     @LockOwner='Session',
1344                                                     @LockTimeout='$timeoutmilli'
1345                     SELECT @result
1346                 END";
1347         $this->query_start($sql, null, SQL_QUERY_AUX);
1348         $result = sqlsrv_query($this->sqlsrv, $sql);
1349         $this->query_end($result);
1351         if ($result) {
1352             $row = sqlsrv_fetch_array($result);
1353             if ($row[0] < 0) {
1354                 throw new dml_sessionwait_exception();
1355             }
1356         }
1358         $this->free_result($result);
1359     }
1361     public function release_session_lock($rowid) {
1362         if (!$this->session_lock_supported()) {
1363             return;
1364         }
1365         parent::release_session_lock($rowid);
1367         $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1368         $sql = "sp_releaseapplock '$fullname', 'Session'";
1369         $this->query_start($sql, null, SQL_QUERY_AUX);
1370         $result = sqlsrv_query($this->sqlsrv, $sql);
1371         $this->query_end($result);
1372         $this->free_result($result);
1373     }
1375     /**
1376      * Driver specific start of real database transaction,
1377      * this can not be used directly in code.
1378      * @return void
1379      */
1380     protected function begin_transaction() {
1381         // Recordsets do not work well with transactions in SQL Server,
1382         // let's prefetch the recordsets to memory to work around these problems.
1383         foreach ($this->recordsets as $rs) {
1384             $rs->transaction_starts();
1385         }
1387         $this->query_start('native sqlsrv_begin_transaction', NULL, SQL_QUERY_AUX);
1388         $result = sqlsrv_begin_transaction($this->sqlsrv);
1389         $this->query_end($result);
1390     }
1392     /**
1393      * Driver specific commit of real database transaction,
1394      * this can not be used directly in code.
1395      * @return void
1396      */
1397     protected function commit_transaction() {
1398         $this->query_start('native sqlsrv_commit', NULL, SQL_QUERY_AUX);
1399         $result = sqlsrv_commit($this->sqlsrv);
1400         $this->query_end($result);
1401     }
1403     /**
1404      * Driver specific abort of real database transaction,
1405      * this can not be used directly in code.
1406      * @return void
1407      */
1408     protected function rollback_transaction() {
1409         $this->query_start('native sqlsrv_rollback', NULL, SQL_QUERY_AUX);
1410         $result = sqlsrv_rollback($this->sqlsrv);
1411         $this->query_end($result);
1412     }