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