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