MDL-20734 normalise_value() - moving from private to protected everywhere and abstracting
[moodle.git] / lib / dml / mssql_native_moodle_database.php
CommitLineData
520b730c 1<?php
2
3// This file is part of Moodle - http://moodle.org/
4//
5// Moodle is free software: you can redistribute it and/or modify
6// it under the terms of the GNU General Public License as published by
7// the Free Software Foundation, either version 3 of the License, or
8// (at your option) any later version.
9//
10// Moodle is distributed in the hope that it will be useful,
11// but WITHOUT ANY WARRANTY; without even the implied warranty of
12// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13// GNU General Public License for more details.
14//
15// You should have received a copy of the GNU General Public License
16// along with Moodle. If not, see <http://www.gnu.org/licenses/>.
17
18
19/**
20 * Native mssql class representing moodle database interface.
21 *
22 * @package moodlecore
23 * @subpackage DML
24 * @copyright 2009 onwards Eloy Lafuente (stronk7) {@link http://stronk7.com}
25 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
26 */
27
28require_once($CFG->libdir.'/dml/moodle_database.php');
29require_once($CFG->libdir.'/dml/mssql_native_moodle_recordset.php');
30require_once($CFG->libdir.'/dml/mssql_native_moodle_temptables.php');
31
32/**
33 * Native mssql class representing moodle database interface.
34 */
35class mssql_native_moodle_database extends moodle_database {
36
37 protected $mssql = null;
38 private $temptables; // Control existing temptables (mssql_moodle_temptables object)
39
5c08b24b 40 protected $last_error_reporting; // To handle mssql driver default verbosity
41
520b730c 42 /**
43 * Detects if all needed PHP stuff installed.
44 * Note: can be used before connect()
45 * @return mixed true if ok, string if something
46 */
47 public function driver_installed() {
48 if (!function_exists('mssql_connect')) {
49 return get_string('mssqlextensionisnotpresentinphp', 'install');
50 }
51 return true;
52 }
53
54 /**
55 * Returns database family type - describes SQL dialect
56 * Note: can be used before connect()
57 * @return string db family name (mysql, postgres, mssql, oracle, etc.)
58 */
59 public function get_dbfamily() {
60 return 'mssql';
61 }
62
63 /**
64 * Returns more specific database driver type
65 * Note: can be used before connect()
66 * @return string db type mysql, pgsql, postgres7
67 */
68 protected function get_dbtype() {
69 return 'mssql';
70 }
71
72 /**
73 * Returns general database library name
74 * Note: can be used before connect()
eb123deb 75 * @return string db type pdo, native
520b730c 76 */
77 protected function get_dblibrary() {
78 return 'native';
79 }
80
81 /**
82 * Returns localised database type name
83 * Note: can be used before connect()
84 * @return string
85 */
86 public function get_name() {
87 return get_string('nativemssql', 'install');
88 }
89
90 /**
91 * Returns sql generator used for db manipulation.
92 * Used mostly in upgrade.php scripts. mssql overrides it
3f33c9e2 93 * in order to share the mssql_native_moodle_temptables
520b730c 94 * between the driver and the generator
95 *
96 * @return object database_manager instance
97 */
98 public function get_manager() {
99 global $CFG;
100
101 if (!$this->database_manager) {
102 require_once($CFG->libdir.'/ddllib.php');
103
104 $classname = $this->get_dbfamily().'_sql_generator';
105 require_once("$CFG->libdir/ddl/$classname.php");
106 $generator = new $classname($this, $this->temptables);
107
108 $this->database_manager = new database_manager($this, $generator);
109 }
110 return $this->database_manager;
111 }
112
113 /**
114 * Returns localised database configuration help.
115 * Note: can be used before connect()
116 * @return string
117 */
118 public function get_configuration_help() {
119 return get_string('nativemssqlhelp', 'install');
120 }
121
122 /**
123 * Returns localised database description
124 * Note: can be used before connect()
125 * @return string
126 */
127 public function get_configuration_hints() {
128 $str = get_string('databasesettingssub_mssql', 'install');
129 $str .= "<p style='text-align:right'><a href=\"javascript:void(0)\" ";
130 $str .= "onclick=\"return window.open('http://docs.moodle.org/en/Installing_MSSQL_for_PHP')\"";
131 $str .= ">";
132 $str .= '<img src="pix/docs.gif' . '" alt="Docs" class="iconhelp" />';
133 $str .= get_string('moodledocslink', 'install') . '</a></p>';
134 return $str;
135 }
136
137 /**
138 * Connect to db
139 * Must be called before other methods.
140 * @param string $dbhost
141 * @param string $dbuser
142 * @param string $dbpass
143 * @param string $dbname
144 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
145 * @param array $dboptions driver specific options
146 * @return bool true
147 * @throws dml_connection_exception if error
148 */
149 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {
150 if ($prefix == '' and !$this->external) {
151 //Enforce prefixes for everybody but mysql
152 throw new dml_exception('prefixcannotbeempty', $this->get_dbfamily());
153 }
154
155 $driverstatus = $this->driver_installed();
156
157 if ($driverstatus !== true) {
158 throw new dml_exception('dbdriverproblem', $driverstatus);
159 }
160
161 $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
162
163 ob_start();
164 if (!empty($this->dboptions['dbpersit'])) { // persistent connection
165 $this->mssql = mssql_pconnect($this->dbhost, $this->dbuser, $this->dbpass, true);
166 } else {
167 $this->mssql = mssql_connect($this->dbhost, $this->dbuser, $this->dbpass, true);
168 }
169 $dberr = ob_get_contents();
170 ob_end_clean();
171
172 if ($this->mssql === false) {
173 $this->mssql = null;
174 throw new dml_connection_exception($dberr);
175 }
176
177 // already connected, select database and set some env. variables
178 $this->query_start("--mssql_select_db", null, SQL_QUERY_AUX);
179 $result = mssql_select_db($this->dbname, $this->mssql);
180 $this->query_end($result);
181
182 // No need to set charset. It's UTF8, with transparent conversions
183 // back and forth performed both by FreeTDS or ODBTP
184
185 // Allow quoted identifiers
186 $sql = "SET QUOTED_IDENTIFIER ON";
187 $this->query_start($sql, null, SQL_QUERY_AUX);
188 $result = mssql_query($sql, $this->mssql);
189 $this->query_end($result);
190
191 $this->free_result($result);
192
193 // Force ANSI nulls so the NULL check was done by IS NULL and NOT IS NULL
194 // instead of equal(=) and distinct(<>) simbols
195 $sql = "SET ANSI_NULLS ON";
196 $this->query_start($sql, null, SQL_QUERY_AUX);
197 $result = mssql_query($sql, $this->mssql);
198 $this->query_end($result);
199
200 $this->free_result($result);
201
202 // Force ANSI warnings so arithmetic/string overflows will be
203 // returning error instead of transparently truncating data
204 $sql = "SET ANSI_WARNINGS ON";
205 $this->query_start($sql, null, SQL_QUERY_AUX);
206 $result = mssql_query($sql, $this->mssql);
207 $this->query_end($result);
208
209 // Concatenating null with anything MUST return NULL
210 $sql = "SET CONCAT_NULL_YIELDS_NULL ON";
211 $this->query_start($sql, null, SQL_QUERY_AUX);
212 $result = mssql_query($sql, $this->mssql);
213 $this->query_end($result);
214
215 $this->free_result($result);
216
217 // Set transactions isolation level to READ_COMMITTED
218 // prevents dirty reads when using transactions +
219 // is the default isolation level of MSSQL
220 $sql = "SET TRANSACTION ISOLATION LEVEL READ COMMITTED";
221 $this->query_start($sql, NULL, SQL_QUERY_AUX);
222 $result = mssql_query($sql, $this->mssql);
223 $this->query_end($result);
224
225 $this->free_result($result);
226
227 // Connection stabilished and configured, going to instantiate the temptables controller
228 $this->temptables = new mssql_native_moodle_temptables($this);
229
230 return true;
231 }
232
233 /**
234 * Close database connection and release all resources
235 * and memory (especially circular memory references).
236 * Do NOT use connect() again, create a new instance if needed.
237 */
238 public function dispose() {
239 parent::dispose(); // Call parent dispose to write/close session and other common stuff before clossing conn
240 if ($this->mssql) {
241 mssql_close($this->mssql);
242 $this->mssql = null;
243 }
244 }
245
246 /**
247 * Called before each db query.
248 * @param string $sql
249 * @param array array of parameters
250 * @param int $type type of query
251 * @param mixed $extrainfo driver specific extra information
252 * @return void
253 */
254 protected function query_start($sql, array $params=null, $type, $extrainfo=null) {
255 parent::query_start($sql, $params, $type, $extrainfo);
5c08b24b 256 // mssql driver tends to send debug to output, we do not need that ;-)
257 $this->last_error_reporting = error_reporting(0);
520b730c 258 }
259
260 /**
261 * Called immediately after each db query.
262 * @param mixed db specific result
263 * @return void
264 */
265 protected function query_end($result) {
5c08b24b 266 // reset original debug level
267 error_reporting($this->last_error_reporting);
520b730c 268 parent::query_end($result);
269 }
270
271 /**
272 * Returns database server info array
273 * @return array
274 */
275 public function get_server_info() {
276 static $info;
277 if (!$info) {
278 $info = array();
279 $sql = 'sp_server_info 2';
280 $this->query_start($sql, null, SQL_QUERY_AUX);
281 $result = mssql_query($sql, $this->mssql);
282 $this->query_end($result);
283 $row = mssql_fetch_row($result);
284 $info['server'] = $row[2];
285 $this->free_result($result);
286
287 $sql = 'sp_server_info 500';
288 $this->query_start($sql, null, SQL_QUERY_AUX);
289 $result = mssql_query($sql, $this->mssql);
290 $this->query_end($result);
291 $row = mssql_fetch_row($result);
292 $info['version'] = $row[2];
293 $this->free_result($result);
294 }
295 return $info;
296 }
297
298 protected function is_min_version($version) {
299 $server = $this->get_server_info();
300 $server = $server['version'];
301 return version_compare($server, $version, '>=');
302 }
303
304 /**
305 * Converts short table name {tablename} to real table name
306 * supporting temp tables (#) if detected
307 *
308 * @param string sql
309 * @return string sql
310 */
311 protected function fix_table_names($sql) {
312 if (preg_match_all('/\{([a-z][a-z0-9_]*)\}/', $sql, $matches)) {
313 foreach($matches[0] as $key=>$match) {
314 $name = $matches[1][$key];
4b1ee2b3 315 if ($this->temptables->is_temptable($name)) {
316 $sql = str_replace($match, $this->temptables->get_correct_name($name), $sql);
317 } else {
318 $sql = str_replace($match, $this->prefix.$name, $sql);
319 }
520b730c 320 }
321 }
322 return $sql;
323 }
324
325 /**
326 * Returns supported query parameter types
327 * @return bitmask
328 */
329 protected function allowed_param_types() {
330 return SQL_PARAMS_QM; // Not really, but emulated, see emulate_bound_params()
331 }
332
333 /**
334 * Returns last error reported by database engine.
335 */
336 public function get_last_error() {
337 return mssql_get_last_message();
338 }
339
340 /**
341 * Return tables in database WITHOUT current prefix
342 * @return array of table names in lowercase and without prefix
343 */
344 public function get_tables($usecache=true) {
345 if ($usecache and $this->tables !== null) {
346 return $this->tables;
347 }
348 $this->tables = array();
349 $sql = "SELECT table_name
350 FROM information_schema.tables
351 WHERE table_name LIKE '$this->prefix%'
352 AND table_type = 'BASE TABLE'";
353 $this->query_start($sql, null, SQL_QUERY_AUX);
354 $result = mssql_query($sql, $this->mssql);
355 $this->query_end($result);
356
357 if ($result) {
358 while ($row = mssql_fetch_row($result)) {
359 $tablename = reset($row);
360 if (strpos($tablename, $this->prefix) !== 0) {
361 continue;
362 }
363 $tablename = substr($tablename, strlen($this->prefix));
364 $this->tables[$tablename] = $tablename;
365 }
366 $this->free_result($result);
367 }
368
369 // Add the currently available temptables
370 $this->tables = array_merge($this->tables, $this->temptables->get_temptables());
371 return $this->tables;
372 }
373
374 /**
375 * Return table indexes - everything lowercased
376 * @return array of arrays
377 */
378 public function get_indexes($table) {
379 $indexes = array();
380 $tablename = $this->prefix.$table;
381
382 // Indexes aren't covered by information_schema metatables, so we need to
383 // go to sys ones. Skipping primary key indexes on purpose.
384 $sql = "SELECT i.name AS index_name, i.is_unique, ic.index_column_id, c.name AS column_name
385 FROM sys.indexes i
386 JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
387 JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
388 JOIN sys.tables t ON i.object_id = t.object_id
389 WHERE t.name = '$tablename'
390 AND i.is_primary_key = 0
391 ORDER BY i.name, i.index_id, ic.index_column_id";
392
393 $this->query_start($sql, null, SQL_QUERY_AUX);
394 $result = mssql_query($sql, $this->mssql);
395 $this->query_end($result);
396
397 if ($result) {
398 $lastindex = '';
399 $unique = false;
400 $columns = array();
401 while ($row = mssql_fetch_assoc($result)) {
402 if ($lastindex and $lastindex != $row['index_name']) { // Save lastindex to $indexes and reset info
403 $indexes[$lastindex] = array('unique' => $unique, 'columns' => $columns);
404 $unique = false;
405 $columns = array();
406 }
407 $lastindex = $row['index_name'];
408 $unique = empty($row['is_unique']) ? false : true;
409 $columns[] = $row['column_name'];
410 }
411 if ($lastindex ) { // Add the last one if exists
412 $indexes[$lastindex] = array('unique' => $unique, 'columns' => $columns);
413 }
414 $this->free_result($result);
415 }
416 return $indexes;
417 }
418
419 /**
420 * Returns datailed information about columns in table. This information is cached internally.
421 * @param string $table name
422 * @param bool $usecache
423 * @return array array of database_column_info objects indexed with column names
424 */
425 public function get_columns($table, $usecache=true) {
426 if ($usecache and isset($this->columns[$table])) {
427 return $this->columns[$table];
428 }
429
430 $this->columns[$table] = array();
431
520b730c 432 if (!$this->temptables->is_temptable($table)) { // normal table, get metadata from own schema
433 $sql = "SELECT column_name AS name,
434 data_type AS type,
435 numeric_precision AS max_length,
436 character_maximum_length AS char_max_length,
437 numeric_scale AS scale,
438 is_nullable AS is_nullable,
439 columnproperty(object_id(quotename(table_schema) + '.' +
440 quotename(table_name)), column_name, 'IsIdentity') AS auto_increment,
441 column_default AS default_value
442 FROM information_schema.columns
896a2a91 443 WHERE table_name = '{" . $table . "}'
520b730c 444 ORDER BY ordinal_position";
445 } else { // temp table, get metadata from tempdb schema
446 $sql = "SELECT column_name AS name,
447 data_type AS type,
448 numeric_precision AS max_length,
449 character_maximum_length AS char_max_length,
450 numeric_scale AS scale,
451 is_nullable AS is_nullable,
452 columnproperty(object_id(quotename(table_schema) + '.' +
453 quotename(table_name)), column_name, 'IsIdentity') AS auto_increment,
454 column_default AS default_value
455 FROM tempdb.information_schema.columns
3c41bba0 456 JOIN tempdb..sysobjects ON name = table_name
457 WHERE id = object_id('tempdb..{" . $table . "}')
520b730c 458 ORDER BY ordinal_position";
459 }
460
896a2a91 461 list($sql, $params, $type) = $this->fix_sql_params($sql, null);
462
520b730c 463 $this->query_start($sql, null, SQL_QUERY_AUX);
464 $result = mssql_query($sql, $this->mssql);
465 $this->query_end($result);
466
467 if (!$result) {
468 return array();
469 }
470
471 while ($rawcolumn = mssql_fetch_assoc($result)) {
472
473 $rawcolumn = (object)$rawcolumn;
474
475 $info = new object();
476 $info->name = $rawcolumn->name;
477 $info->type = $rawcolumn->type;
478 $info->meta_type = $this->mssqltype2moodletype($info->type);
479
480 // Prepare auto_increment info
481 $info->auto_increment = $rawcolumn->auto_increment ? true : false;
482
483 // Define type for auto_increment columns
484 $info->meta_type = ($info->auto_increment && $info->meta_type == 'I') ? 'R' : $info->meta_type;
485
486 // id columns being auto_incremnt are PK by definition
487 $info->primary_key = ($info->name == 'id' && $info->meta_type == 'R' && $info->auto_increment);
488
489 // Put correct length for character and LOB types
490 $info->max_length = $info->meta_type == 'C' ? $rawcolumn->char_max_length : $rawcolumn->max_length;
491 $info->max_length = ($info->meta_type == 'X' || $info->meta_type == 'B') ? -1 : $info->max_length;
492
493 // Scale
494 $info->scale = $rawcolumn->scale ? $rawcolumn->scale : false;
495
496 // Prepare not_null info
497 $info->not_null = $rawcolumn->is_nullable == 'NO' ? true : false;
498
499 // Process defaults
500 $info->has_default = !empty($rawcolumn->default_value);
501 $info->default_value = preg_replace("/^[\(N]+[']?(.*?)[']?[\)]+$/", '\\1', $rawcolumn->default_value);
502
503 // Process binary
504 $info->binary = $info->meta_type == 'B' ? true : false;
505
506 $this->columns[$table][$info->name] = new database_column_info($info);
507 }
508 $this->free_result($result);
509
510 return $this->columns[$table];
511 }
512
513 /**
514 * Normalise values based in RDBMS dependencies (booleans, LOBs...)
515 *
516 * @param database_column_info $column column metadata corresponding with the value we are going to normalise
517 * @param mixed $value value we are going to normalise
518 * @return mixed the normalised value
519 */
e3acc8af 520 protected function normalise_value($column, $value) {
520b730c 521 if (is_bool($value)) { /// Always, convert boolean to int
522 $value = (int)$value;
8a89be3c 523 } // And continue processing because text columns with numeric info need special handling below
520b730c 524
8a89be3c 525 if ($column->meta_type == 'B') { // BLOBs need to be properly "packed", but can be inserted directly if so.
520b730c 526 if (!is_null($value)) { // If value not null, unpack it to unquoted hexadecimal byte-string format
527 $value = unpack('H*hex', $value); // we leave it as array, so emulate_bound_params() can detect it
528 } // easily and "bind" the param ok.
529
530 } else if ($column->meta_type == 'X') { // MSSQL doesn't cast from int to text, so if text column
531 if (is_numeric($value)) { // and is numeric value then cast to string
532 $value = array('numstr' => (string)$value); // and put into array, so emulate_bound_params() will know how
533 } // to "bind" the param ok, avoiding reverse conversion to number
534
535 } else if ($value === '') {
536 if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') {
537 $value = 0; // prevent '' problems in numeric fields
538 }
539 }
540 return $value;
541 }
542
543 /**
544 * Selectively call mssql_free_result(), avoiding some warnings without using the horrible @
545 *
546 * @param mssql_resource $resource resource to be freed if possible
547 */
548 private function free_result($resource) {
549 if (!is_bool($resource)) { // true/false resources cannot be freed
550 mssql_free_result($resource);
551 }
552 }
553
554 /**
555 * Provides mapping between mssql native data types and moodle_database - database_column_info - ones)
556 *
557 * @param string $mssql_type native mssql data type
558 * @return string 1-char database_column_info data type
559 */
560 private function mssqltype2moodletype($mssql_type) {
561 $type = null;
562 switch (strtoupper($mssql_type)) {
563 case 'BIT':
564 $type = 'L';
565 break;
566 case 'INT':
567 case 'SMALLINT':
568 case 'INTEGER':
569 case 'BIGINT':
570 $type = 'I';
571 break;
572 case 'DECIMAL':
573 case 'REAL':
574 case 'FLOAT':
575 $type = 'N';
576 break;
577 case 'VARCHAR':
578 case 'NVARCHAR':
579 $type = 'C';
580 break;
581 case 'TEXT':
582 case 'NTEXT':
583 case 'VARCHAR(MAX)':
584 case 'NVARCHAR(MAX)':
585 $type = 'X';
586 break;
587 case 'IMAGE':
588 case 'VARBINARY(MAX)':
589 $type = 'B';
590 break;
591 case 'DATETIME':
592 $type = 'D';
593 break;
594 }
595 if (!$type) {
596 throw new dml_exception('invalidmssqlnativetype', $mssql_type);
597 }
598 return $type;
599 }
600
601 /**
602 * Do NOT use in code, to be used by database_manager only!
603 * @param string $sql query
604 * @return bool true
605 * @throws dml_exception if error
606 */
607 public function change_database_structure($sql) {
608 $this->reset_caches();
609
610 $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
5c08b24b 611 $result = mssql_query($sql, $this->mssql);
520b730c 612 $this->query_end($result);
613
614 return true;
615 }
616
617 /**
618 * Very ugly hack which emulates bound parameters in queries
619 * because the mssql driver doesn't support placeholders natively at all
620 */
621 protected function emulate_bound_params($sql, array $params=null) {
622 if (empty($params)) {
623 return $sql;
624 }
625 /// ok, we have verified sql statement with ? and correct number of params
626 $return = strtok($sql, '?');
627 foreach ($params as $param) {
628 if (is_bool($param)) {
629 $return .= (int)$param;
630
631 } else if (is_array($param) && isset($param['hex'])) { // detect hex binary, bind it specially
632 $return .= '0x' . $param['hex'];
633
634 } else if (is_array($param) && isset($param['numstr'])) { // detect numerical strings that *must not*
635 $return .= "N'{$param['numstr']}'"; // be converted back to number params, but bound as strings
636
637 } else if (is_null($param)) {
638 $return .= 'NULL';
639
640 } else if (is_number($param)) { // we can not use is_numeric() because it eats leading zeros from strings like 0045646
641 $return .= $param;
642
643 } else if (is_float($param)) {
644 $return .= $param;
645
646 } else {
647 $param = str_replace("'", "''", $param);
648 $return .= "N'$param'";
649 }
650
651 $return .= strtok('?');
652 }
653 return $return;
654 }
655
656 /**
657 * Execute general sql query. Should be used only when no other method suitable.
658 * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead!
659 * @param string $sql query
660 * @param array $params query parameters
661 * @return bool true
662 * @throws dml_exception if error
663 */
664 public function execute($sql, array $params=null) {
665
666 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
667 $rawsql = $this->emulate_bound_params($sql, $params);
668
669 if (strpos($sql, ';') !== false) {
670 throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
671 }
672
673 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
5c08b24b 674 $result = mssql_query($rawsql, $this->mssql);
520b730c 675 $this->query_end($result);
676 $this->free_result($result);
677
678 return true;
679 }
680
681 /**
682 * Get a number of records as a moodle_recordset using a SQL statement.
683 *
684 * Since this method is a little less readable, use of it should be restricted to
685 * code where it's possible there might be large datasets being returned. For known
686 * small datasets use get_records_sql - it leads to simpler code.
687 *
688 * The return type is as for @see function get_recordset.
689 *
690 * @param string $sql the SQL select query to execute.
691 * @param array $params array of sql parameters
692 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
693 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
694 * @return mixed an moodle_recordset object
695 * @throws dml_exception if error
696 */
697 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
698 $limitfrom = (int)$limitfrom;
699 $limitnum = (int)$limitnum;
700 $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom;
701 $limitnum = ($limitnum < 0) ? 0 : $limitnum;
702 if ($limitfrom or $limitnum) {
72a3902f 703 if ($limitnum >= 1) { // Only apply TOP clause if we have any limitnum (limitfrom offset is hadled later)
704 $fetch = $limitfrom + $limitnum;
705 $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',
706 "\\1SELECT\\2 TOP $fetch", $sql);
520b730c 707 }
520b730c 708 }
709
710 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
711 $rawsql = $this->emulate_bound_params($sql, $params);
712
713 $this->query_start($sql, $params, SQL_QUERY_SELECT);
5c08b24b 714 $result = mssql_query($rawsql, $this->mssql);
520b730c 715 $this->query_end($result);
716
72a3902f 717 if ($limitfrom) { // Skip $limitfrom records
5c08b24b 718 mssql_data_seek($result, $limitfrom);
520b730c 719 }
720
721 return $this->create_recordset($result);
722 }
723
724 protected function create_recordset($result) {
725 return new mssql_native_moodle_recordset($result);
726 }
727
728 /**
729 * Get a number of records as an array of objects using a SQL statement.
730 *
731 * Return value as for @see function get_records.
732 *
733 * @param string $sql the SQL select query to execute. The first column of this SELECT statement
734 * must be a unique value (usually the 'id' field), as it will be used as the key of the
735 * returned array.
736 * @param array $params array of sql parameters
737 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
738 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
739 * @return mixed an array of objects, or empty array if no records were found
740 * @throws dml_exception if error
741 */
742 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
743
744 $rs = $this->get_recordset_sql($sql, $params, $limitfrom, $limitnum);
745
746 $results = array();
747
748 foreach ($rs as $row) {
df5f9165 749 $id = reset($row);
750 if (isset($results[$id])) {
520b730c 751 $colname = key($row);
752 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);
753 }
df5f9165 754 $results[$id] = $row;
520b730c 755 }
756 $rs->close();
757
758 return $results;
759 }
760
761 /**
762 * Selects records and return values (first field) as an array using a SQL statement.
763 *
764 * @param string $sql The SQL query
765 * @param array $params array of sql parameters
766 * @return mixed array of values
767 * @throws dml_exception if error
768 */
769 public function get_fieldset_sql($sql, array $params=null) {
770
771 $rs = $this->get_recordset_sql($sql, $params);
772
773 $results = array();
774
775 foreach ($rs as $row) {
776 $results[] = reset($row);
777 }
778 $rs->close();
779
780 return $results;
781 }
782
783 /**
784 * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
785 * @param string $table name
786 * @param mixed $params data record as object or array
787 * @param bool $returnit return it of inserted record
788 * @param bool $bulk true means repeated inserts expected
789 * @param bool $customsequence true if 'id' included in $params, disables $returnid
790 * @return true or new id
791 * @throws dml_exception if error
792 */
793 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
794 if (!is_array($params)) {
795 $params = (array)$params;
796 }
797
798 $returning = "";
799
800 if ($customsequence) {
801 if (!isset($params['id'])) {
802 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
803 }
804 $returnid = false;
805 } else {
806 unset($params['id']);
807 if ($returnid) {
808 $returning = "; SELECT SCOPE_IDENTITY()";
809 }
810 }
811
812 if (empty($params)) {
813 throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
814 }
815
816 $fields = implode(',', array_keys($params));
817 $qms = array_fill(0, count($params), '?');
818 $qms = implode(',', $qms);
819
820 $sql = "INSERT INTO {" . $table . "} ($fields) VALUES($qms) $returning";
821
822 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
823 $rawsql = $this->emulate_bound_params($sql, $params);
824
825 $this->query_start($sql, $params, SQL_QUERY_INSERT);
5c08b24b 826 $result = mssql_query($rawsql, $this->mssql);
520b730c 827 $this->query_end($result);
828
829 if ($returning !== "") {
830 $row = mssql_fetch_assoc($result);
831 $params['id'] = reset($row);
832 }
833 $this->free_result($result);
834
835 if (!$returnid) {
836 return true;
837 }
838
839 return (int)$params['id'];
840 }
841
842 /**
843 * Insert a record into a table and return the "id" field if required.
844 *
845 * Some conversions and safety checks are carried out. Lobs are supported.
846 * If the return ID isn't required, then this just reports success as true/false.
847 * $data is an object containing needed data
848 * @param string $table The database table to be inserted into
849 * @param object $data A data object with values for one or more fields in the record
850 * @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.
851 * @return true or new id
852 * @throws dml_exception if error
853 */
854 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
855 if (!is_object($dataobject)) {
856 $dataobject = (object)$dataobject;
857 }
858
859 unset($dataobject->id);
860
861 $columns = $this->get_columns($table);
862 $cleaned = array();
863
864 foreach ($dataobject as $field => $value) {
865 if (!isset($columns[$field])) {
866 continue;
867 }
868 $column = $columns[$field];
869 $cleaned[$field] = $this->normalise_value($column, $value);
870 }
871
872 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
873 }
874
875 /**
876 * Import a record into a table, id field is required.
877 * Safety checks are NOT carried out. Lobs are supported.
878 *
879 * @param string $table name of database table to be inserted into
880 * @param object $dataobject A data object with values for one or more fields in the record
881 * @return bool true
882 * @throws dml_exception if error
883 */
884 public function import_record($table, $dataobject) {
896a2a91 885 if (!is_object($dataobject)) {
886 $dataobject = (object)$dataobject;
887 }
520b730c 888
889 $columns = $this->get_columns($table);
890 $cleaned = array();
891
896a2a91 892 foreach ($dataobject as $field => $value) {
520b730c 893 if (!isset($columns[$field])) {
894 continue;
895 }
896a2a91 896 $column = $columns[$field];
897 $cleaned[$field] = $this->normalise_value($column, $value);
520b730c 898 }
899
896a2a91 900 // Disable IDENTITY column before inserting record with id
901 $sql = 'SET IDENTITY_INSERT {' . $table . '} ON'; // Yes, it' ON!!
902
903 list($sql, $params, $type) = $this->fix_sql_params($sql, null);
904
905 $this->query_start($sql, null, SQL_QUERY_AUX);
906 $result = mssql_query($sql, $this->mssql);
907 $this->query_end($result);
908
909 $this->free_result($result);
910
911 $insertresult = $this->insert_record_raw($table, $cleaned, false, false, true);
912
913 // Enable IDENTITY column after inserting record with id
914 $sql = 'SET IDENTITY_INSERT {' . $table . '} OFF'; // Yes, it' OFF!!
915
916 list($sql, $params, $type) = $this->fix_sql_params($sql, null);
917
918 $this->query_start($sql, null, SQL_QUERY_AUX);
919 $result = mssql_query($sql, $this->mssql);
920 $this->query_end($result);
921
922 $this->free_result($result);
923
924 return $insertresult;
520b730c 925 }
926
927 /**
928 * Update record in database, as fast as possible, no safety checks, lobs not supported.
929 * @param string $table name
930 * @param mixed $params data record as object or array
931 * @param bool true means repeated updates expected
932 * @return bool true
933 * @throws dml_exception if error
934 */
935 public function update_record_raw($table, $params, $bulk=false) {
936 if (!is_array($params)) {
937 $params = (array)$params;
938 }
939 if (!isset($params['id'])) {
940 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
941 }
942 $id = $params['id'];
943 unset($params['id']);
944
945 if (empty($params)) {
946 throw new coding_exception('moodle_database::update_record_raw() no fields found.');
947 }
948
949 $sets = array();
950 foreach ($params as $field=>$value) {
951 $sets[] = "$field = ?";
952 }
953
954 $params[] = $id; // last ? in WHERE condition
955
956 $sets = implode(',', $sets);
957 $sql = "UPDATE {" . $table . "} SET $sets WHERE id = ?";
958
959 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
960 $rawsql = $this->emulate_bound_params($sql, $params);
961
962 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
5c08b24b 963 $result = mssql_query($rawsql, $this->mssql);
520b730c 964 $this->query_end($result);
965
966 $this->free_result($result);
967 return true;
968 }
969
970 /**
971 * Update a record in a table
972 *
973 * $dataobject is an object containing needed data
974 * Relies on $dataobject having a variable "id" to
975 * specify the record to update
976 *
977 * @param string $table The database table to be checked against.
978 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
979 * @param bool true means repeated updates expected
980 * @return bool true
981 * @throws dml_exception if error
982 */
983 public function update_record($table, $dataobject, $bulk=false) {
984 if (!is_object($dataobject)) {
985 $dataobject = (object)$dataobject;
986 }
987
988 $columns = $this->get_columns($table);
989 $cleaned = array();
990
991 foreach ($dataobject as $field => $value) {
992 if (!isset($columns[$field])) {
993 continue;
994 }
995 $column = $columns[$field];
996 $cleaned[$field] = $this->normalise_value($column, $value);
997 }
998
999 return $this->update_record_raw($table, $cleaned, $bulk);
1000 }
1001
1002 /**
1003 * Set a single field in every table record which match a particular WHERE clause.
1004 *
1005 * @param string $table The database table to be checked against.
1006 * @param string $newfield the field to set.
1007 * @param string $newvalue the value to set the field to.
1008 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1009 * @param array $params array of sql parameters
1010 * @return bool true
1011 * @throws dml_exception if error
1012 */
1013 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) {
1014
1015 if ($select) {
1016 $select = "WHERE $select";
1017 }
1018 if (is_null($params)) {
1019 $params = array();
1020 }
1021
1022 /// Get column metadata
1023 $columns = $this->get_columns($table);
1024 $column = $columns[$newfield];
1025
1026 $newvalue = $this->normalise_value($column, $newvalue);
1027
1028 if (is_null($newvalue)) {
1029 $newfield = "$newfield = NULL";
1030 } else {
1031 $newfield = "$newfield = ?";
1032 array_unshift($params, $newvalue);
1033 }
1034 $sql = "UPDATE {" . $table . "} SET $newfield $select";
1035
1036 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1037 $rawsql = $this->emulate_bound_params($sql, $params);
1038
1039 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
5c08b24b 1040 $result = mssql_query($rawsql, $this->mssql);
520b730c 1041 $this->query_end($result);
1042
1043 $this->free_result($result);
1044
1045 return true;
1046 }
1047
1048 /**
1049 * Delete one or more records from a table which match a particular WHERE clause.
1050 *
1051 * @param string $table The database table to be checked against.
1052 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1053 * @param array $params array of sql parameters
1054 * @return bool true
1055 * @throws dml_exception if error
1056 */
1057 public function delete_records_select($table, $select, array $params=null) {
1058
1059 if ($select) {
1060 $select = "WHERE $select";
1061 }
1062
1063 $sql = "DELETE FROM {" . $table . "} $select";
1064
1065 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1066 $rawsql = $this->emulate_bound_params($sql, $params);
1067
1068 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
5c08b24b 1069 $result = mssql_query($rawsql, $this->mssql);
520b730c 1070 $this->query_end($result);
1071
1072 $this->free_result($result);
1073
1074 return true;
1075 }
1076
1077/// SQL helper functions
1078
1079 public function sql_bitxor($int1, $int2) {
1080 return '(' . $this->sql_bitor($int1, $int2) . ' - ' . $this->sql_bitand($int1, $int2) . ')';
1081 }
1082
1083 public function sql_cast_char2int($fieldname, $text=false) {
1084 return ' CAST(' . $fieldname . ' AS INT) ';
1085 }
1086
1087 public function sql_ceil($fieldname) {
1088 return ' CEILING(' . $fieldname . ')';
1089 }
1090
1091 public function sql_concat() {
1092 $arr = func_get_args();
1093 foreach ($arr as $key => $ele) {
1094 $arr[$key] = ' CAST(' . $ele . ' AS VARCHAR(255)) ';
1095 }
1096 $s = implode(' + ', $arr);
1097 if ($s === '') {
1098 return " '' ";
1099 }
1100 return " $s ";
1101 }
1102
1103 public function sql_concat_join($separator="' '", $elements=array()) {
1104 for ($n=count($elements)-1; $n > 0 ; $n--) {
1105 array_splice($elements, $n, 0, $separator);
1106 }
1107 $s = implode(' + ', $elements);
1108 if ($s === '') {
1109 return " '' ";
1110 }
1111 return " $s ";
1112 }
1113
1114 public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) {
1115 if ($textfield) {
1116 return $this->sql_compare_text($fieldname)." = '' ";
1117 } else {
1118 return " $fieldname = '' ";
1119 }
1120 }
1121
1122 /**
1123 * Returns the SQL text to be used to calculate the length in characters of one expression.
1124 * @param string fieldname or expression to calculate its length in characters.
1125 * @return string the piece of SQL code to be used in the statement.
1126 */
1127 public function sql_length($fieldname) {
1128 return ' LEN(' . $fieldname . ')';
1129 }
1130
1131 public function sql_order_by_text($fieldname, $numchars=32) {
1132 return ' CONVERT(varchar, ' . $fieldname . ', ' . $numchars . ')';
1133 }
1134
1135 /**
1136 * Returns the SQL for returning searching one string for the location of another.
1137 */
1138 public function sql_position($needle, $haystack) {
1139 return "CHARINDEX(($needle), ($haystack))";
1140 }
1141
1142 /**
ab62bd23 1143 * Returns the proper substr() SQL text used to extract substrings from DB
520b730c 1144 * NOTE: this was originally returning only function name
1145 *
1146 * @param string $expr some string field, no aggregates
1147 * @param mixed $start integer or expresion evaluating to int
1148 * @param mixed $length optional integer or expresion evaluating to int
1149 * @return string sql fragment
1150 */
1151 public function sql_substr($expr, $start, $length=false) {
1152 if (count(func_get_args()) < 2) {
1153 throw new coding_exception('moodle_database::sql_substr() requires at least two parameters', 'Originaly this function wa
1154s only returning name of SQL substring function, it now requires all parameters.');
1155 }
1156 if ($length === false) {
1157 return "SUBSTRING($expr, $start, (LEN($expr) - $start + 1))";
1158 } else {
1159 return "SUBSTRING($expr, $start, $length)";
1160 }
1161 }
1162
1163/// session locking
1164
1165 public function session_lock_supported() {
6d682bae 1166 return true;
520b730c 1167 }
1168
1169 public function get_session_lock($rowid) {
520b730c 1170 if (!$this->session_lock_supported()) {
1171 return;
1172 }
520b730c 1173 parent::get_session_lock($rowid);
6d682bae 1174
1175 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1176 $sql = "sp_getapplock '$fullname', 'Exclusive', 'Session', 120000";
520b730c 1177 $this->query_start($sql, null, SQL_QUERY_AUX);
6d682bae 1178 $result = mssql_query($sql, $this->mssql);
520b730c 1179 $this->query_end($result);
1180
6d682bae 1181 $this->free_result($result);
520b730c 1182 }
1183
1184 public function release_session_lock($rowid) {
1185 if (!$this->session_lock_supported()) {
1186 return;
1187 }
1188 parent::release_session_lock($rowid);
1189
6d682bae 1190 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1191 $sql = "sp_releaseapplock '$fullname', 'Exclusive', 'Session'";
520b730c 1192 $this->query_start($sql, null, SQL_QUERY_AUX);
6d682bae 1193 $result = mssql_query($sql, $this->mssql);
520b730c 1194 $this->query_end($result);
1195
6d682bae 1196 $this->free_result($result);
520b730c 1197 }
1198
1199/// transactions
1200
1201 /**
1202 * on DBs that support it, switch to transaction mode and begin a transaction
1203 * you'll need to ensure you call commit_sql() or your changes *will* be lost.
1204 *
1205 * this is _very_ useful for massive updates
1206 */
1207 public function begin_sql() {
1208 if (!parent::begin_sql()) {
1209 return false;
1210 }
1211 $sql = "BEGIN TRANSACTION"; // Will be using READ COMMITTED isolation
1212 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1213 $result = mssql_query($sql, $this->mssql);
1214 $this->query_end($result);
1215
1216 $this->free_result($result);
1217
1218 return true;
1219 }
1220
1221 /**
1222 * on DBs that support it, commit the transaction
1223 */
1224 public function commit_sql() {
1225 if (!parent::commit_sql()) {
1226 return false;
1227 }
1228 $sql = "COMMIT TRANSACTION";
1229 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1230 $result = mssql_query($sql, $this->mssql);
1231 $this->query_end($result);
1232
1233 $this->free_result($result);
1234
1235 return true;
1236 }
1237
1238 /**
1239 * on DBs that support it, rollback the transaction
1240 */
1241 public function rollback_sql() {
1242 if (!parent::rollback_sql()) {
1243 return false;
1244 }
1245 $sql = "ROLLBACK TRANSACTION";
1246 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1247 $result = mssql_query($sql, $this->mssql);
1248 $this->query_end($result);
1249
1250 $this->free_result($result);
1251
1252 return true;
1253 }
1254}