Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 4.3.x will end 7 October 2024 (12 months).
- Bug fixes for security issues in 4.3.x will end 21 April 2025 (18 months).
- PHP version: minimum PHP 8.0.0 Note: minimum PHP version has increased since Moodle 4.1. PHP 8.2.x is supported too.
/lib/dml/ -> sqlsrv_native_moodle_database.php (source)
Differences Between: [Versions 310 and 403] [Versions 311 and 403] [Versions 39 and 403] [Versions 400 and 403] [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 stdClass|array $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 stdClass|array $dataobject An object with contents equal to fieldname=>fieldvalue. 1235 * Must have an entry for 'id' to map to the table specified. 1236 * @param bool true means repeated updates expected 1237 * @return bool true 1238 * @throws dml_exception A DML specific exception is thrown for any errors. 1239 */ 1240 public function update_record($table, $dataobject, $bulk = false) { 1241 $dataobject = (array)$dataobject; 1242 1243 $columns = $this->get_columns($table); 1244 $cleaned = array (); 1245 1246 foreach ($dataobject as $field => $value) { 1247 if (!isset($columns[$field])) { 1248 continue; 1249 } 1250 $column = $columns[$field]; 1251 $cleaned[$field] = $this->normalise_value($column, $value); 1252 } 1253 1254 return $this->update_record_raw($table, $cleaned, $bulk); 1255 } 1256 1257 /** 1258 * Set a single field in every table record which match a particular WHERE clause. 1259 * 1260 * @param string $table The database table to be checked against. 1261 * @param string $newfield the field to set. 1262 * @param string $newvalue the value to set the field to. 1263 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1264 * @param array $params array of sql parameters 1265 * @return bool true 1266 * @throws dml_exception A DML specific exception is thrown for any errors. 1267 */ 1268 public function set_field_select($table, $newfield, $newvalue, $select, array $params = null) { 1269 if ($select) { 1270 $select = "WHERE $select"; 1271 } 1272 1273 if (is_null($params)) { 1274 $params = array (); 1275 } 1276 1277 // convert params to ? types 1278 list($select, $params, $type) = $this->fix_sql_params($select, $params); 1279 1280 // Get column metadata 1281 $columns = $this->get_columns($table); 1282 $column = $columns[$newfield]; 1283 1284 $newvalue = $this->normalise_value($column, $newvalue); 1285 1286 if (is_null($newvalue)) { 1287 $newfield = "$newfield = NULL"; 1288 } else { 1289 $newfield = "$newfield = ?"; 1290 array_unshift($params, $newvalue); 1291 } 1292 $sql = "UPDATE {".$table."} SET $newfield $select"; 1293 1294 $this->do_query($sql, $params, SQL_QUERY_UPDATE); 1295 1296 return true; 1297 } 1298 1299 /** 1300 * Delete one or more records from a table which match a particular WHERE clause. 1301 * 1302 * @param string $table The database table to be checked against. 1303 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria). 1304 * @param array $params array of sql parameters 1305 * @return bool true 1306 * @throws dml_exception A DML specific exception is thrown for any errors. 1307 */ 1308 public function delete_records_select($table, $select, array $params = null) { 1309 if ($select) { 1310 $select = "WHERE $select"; 1311 } 1312 1313 $sql = "DELETE FROM {".$table."} $select"; 1314 1315 // we use SQL_QUERY_UPDATE because we do not know what is in general SQL, delete constant would not be accurate 1316 $this->do_query($sql, $params, SQL_QUERY_UPDATE); 1317 1318 return true; 1319 } 1320 1321 /** 1322 * Return SQL for casting to char of given field/expression 1323 * 1324 * @param string $field Table field or SQL expression to be cast 1325 * @return string 1326 */ 1327 public function sql_cast_to_char(string $field): string { 1328 return "CAST({$field} AS NVARCHAR(MAX))"; 1329 } 1330 1331 1332 public function sql_cast_char2int($fieldname, $text = false) { 1333 if (!$text) { 1334 return ' CAST(' . $fieldname . ' AS INT) '; 1335 } else { 1336 return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS INT) '; 1337 } 1338 } 1339 1340 public function sql_cast_char2real($fieldname, $text=false) { 1341 if (!$text) { 1342 return ' CAST(' . $fieldname . ' AS REAL) '; 1343 } else { 1344 return ' CAST(' . $this->sql_compare_text($fieldname) . ' AS REAL) '; 1345 } 1346 } 1347 1348 public function sql_ceil($fieldname) { 1349 return ' CEILING('.$fieldname.')'; 1350 } 1351 1352 protected function get_collation() { 1353 if (isset($this->collation)) { 1354 return $this->collation; 1355 } 1356 if (!empty($this->dboptions['dbcollation'])) { 1357 // perf speedup 1358 $this->collation = $this->dboptions['dbcollation']; 1359 return $this->collation; 1360 } 1361 1362 // make some default 1363 $this->collation = 'Latin1_General_CI_AI'; 1364 1365 $sql = "SELECT CAST(DATABASEPROPERTYEX('$this->dbname', 'Collation') AS varchar(255)) AS SQLCollation"; 1366 $this->query_start($sql, null, SQL_QUERY_AUX); 1367 $result = sqlsrv_query($this->sqlsrv, $sql); 1368 $this->query_end($result); 1369 1370 if ($result) { 1371 if ($rawcolumn = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) { 1372 $this->collation = reset($rawcolumn); 1373 } 1374 $this->free_result($result); 1375 } 1376 1377 return $this->collation; 1378 } 1379 1380 public function sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false) { 1381 $equalop = $notequal ? '<>' : '='; 1382 $collation = $this->get_collation(); 1383 1384 if ($casesensitive) { 1385 $collation = str_replace('_CI', '_CS', $collation); 1386 } else { 1387 $collation = str_replace('_CS', '_CI', $collation); 1388 } 1389 if ($accentsensitive) { 1390 $collation = str_replace('_AI', '_AS', $collation); 1391 } else { 1392 $collation = str_replace('_AS', '_AI', $collation); 1393 } 1394 1395 return "$fieldname COLLATE $collation $equalop $param"; 1396 } 1397 1398 /** 1399 * Returns 'LIKE' part of a query. 1400 * 1401 * @param string $fieldname usually name of the table column 1402 * @param string $param usually bound query parameter (?, :named) 1403 * @param bool $casesensitive use case sensitive search 1404 * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive) 1405 * @param bool $notlike true means "NOT LIKE" 1406 * @param string $escapechar escape char for '%' and '_' 1407 * @return string SQL code fragment 1408 */ 1409 public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') { 1410 if (strpos($param, '%') !== false) { 1411 debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)'); 1412 } 1413 1414 $collation = $this->get_collation(); 1415 $LIKE = $notlike ? 'NOT LIKE' : 'LIKE'; 1416 1417 if ($casesensitive) { 1418 $collation = str_replace('_CI', '_CS', $collation); 1419 } else { 1420 $collation = str_replace('_CS', '_CI', $collation); 1421 } 1422 if ($accentsensitive) { 1423 $collation = str_replace('_AI', '_AS', $collation); 1424 } else { 1425 $collation = str_replace('_AS', '_AI', $collation); 1426 } 1427 1428 return "$fieldname COLLATE $collation $LIKE $param ESCAPE '$escapechar'"; 1429 } 1430 1431 /** 1432 * Escape common SQL LIKE special characters like '_' or '%', plus '[' & ']' which are also supported in SQL Server 1433 * 1434 * Note that '^' and '-' also have meaning within a LIKE, but only when enclosed within square brackets. As this syntax 1435 * is not supported on all databases and the brackets are always escaped, we don't need special handling of them 1436 * 1437 * @param string $text 1438 * @param string $escapechar 1439 * @return string 1440 */ 1441 public function sql_like_escape($text, $escapechar = '\\') { 1442 $text = parent::sql_like_escape($text, $escapechar); 1443 1444 $text = str_replace('[', $escapechar . '[', $text); 1445 $text = str_replace(']', $escapechar . ']', $text); 1446 1447 return $text; 1448 } 1449 1450 public function sql_concat() { 1451 $arr = func_get_args(); 1452 1453 foreach ($arr as $key => $ele) { 1454 $arr[$key] = $this->sql_cast_to_char($ele); 1455 } 1456 $s = implode(' + ', $arr); 1457 1458 if ($s === '') { 1459 return " '' "; 1460 } 1461 return " $s "; 1462 } 1463 1464 public function sql_concat_join($separator = "' '", $elements = array ()) { 1465 for ($n = count($elements) - 1; $n > 0; $n--) { 1466 array_splice($elements, $n, 0, $separator); 1467 } 1468 return call_user_func_array(array($this, 'sql_concat'), array_values($elements)); 1469 } 1470 1471 /** 1472 * Return SQL for performing group concatenation on given field/expression 1473 * 1474 * @param string $field 1475 * @param string $separator 1476 * @param string $sort 1477 * @return string 1478 */ 1479 public function sql_group_concat(string $field, string $separator = ', ', string $sort = ''): string { 1480 $fieldsort = $sort ? "WITHIN GROUP (ORDER BY {$sort})" : ''; 1481 return "STRING_AGG({$field}, '{$separator}') {$fieldsort}"; 1482 } 1483 1484 public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) { 1485 if ($textfield) { 1486 return ' ('.$this->sql_compare_text($fieldname)." = '') "; 1487 } else { 1488 return " ($fieldname = '') "; 1489 } 1490 } 1491 1492 /** 1493 * Returns the SQL text to be used to calculate the length in characters of one expression. 1494 * @param string fieldname or expression to calculate its length in characters. 1495 * @return string the piece of SQL code to be used in the statement. 1496 */ 1497 public function sql_length($fieldname) { 1498 return ' LEN('.$fieldname.')'; 1499 } 1500 1501 public function sql_order_by_text($fieldname, $numchars = 32) { 1502 return " CONVERT(varchar({$numchars}), {$fieldname})"; 1503 } 1504 1505 /** 1506 * Returns the SQL for returning searching one string for the location of another. 1507 */ 1508 public function sql_position($needle, $haystack) { 1509 return "CHARINDEX(($needle), ($haystack))"; 1510 } 1511 1512 /** 1513 * Returns the proper substr() SQL text used to extract substrings from DB 1514 * NOTE: this was originally returning only function name 1515 * 1516 * @param string $expr some string field, no aggregates 1517 * @param mixed $start integer or expression evaluating to int 1518 * @param mixed $length optional integer or expression evaluating to int 1519 * @return string sql fragment 1520 */ 1521 public function sql_substr($expr, $start, $length = false) { 1522 if (count(func_get_args()) < 2) { 1523 throw new coding_exception('moodle_database::sql_substr() requires at least two parameters', 1524 'Originally this function was only returning name of SQL substring function, it now requires all parameters.'); 1525 } 1526 1527 if ($length === false) { 1528 return "SUBSTRING($expr, " . $this->sql_cast_char2int($start) . ", 2^31-1)"; 1529 } else { 1530 return "SUBSTRING($expr, " . $this->sql_cast_char2int($start) . ", " . $this->sql_cast_char2int($length) . ")"; 1531 } 1532 } 1533 1534 /** 1535 * Does this driver support tool_replace? 1536 * 1537 * @since Moodle 2.6.1 1538 * @return bool 1539 */ 1540 public function replace_all_text_supported() { 1541 return true; 1542 } 1543 1544 public function session_lock_supported() { 1545 return true; 1546 } 1547 1548 /** 1549 * Obtain session lock 1550 * @param int $rowid id of the row with session record 1551 * @param int $timeout max allowed time to wait for the lock in seconds 1552 * @return void 1553 */ 1554 public function get_session_lock($rowid, $timeout) { 1555 if (!$this->session_lock_supported()) { 1556 return; 1557 } 1558 parent::get_session_lock($rowid, $timeout); 1559 1560 $timeoutmilli = $timeout * 1000; 1561 1562 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; 1563 // While this may work using proper {call sp_...} calls + binding + 1564 // executing + consuming recordsets, the solution used for the mssql 1565 // driver is working perfectly, so 100% mimic-ing that code. 1566 // $sql = "sp_getapplock '$fullname', 'Exclusive', 'Session', $timeoutmilli"; 1567 $sql = "BEGIN 1568 DECLARE @result INT 1569 EXECUTE @result = sp_getapplock @Resource='$fullname', 1570 @LockMode='Exclusive', 1571 @LockOwner='Session', 1572 @LockTimeout='$timeoutmilli' 1573 SELECT @result 1574 END"; 1575 $this->query_start($sql, null, SQL_QUERY_AUX); 1576 $result = sqlsrv_query($this->sqlsrv, $sql); 1577 $this->query_end($result); 1578 1579 if ($result) { 1580 $row = sqlsrv_fetch_array($result); 1581 if ($row[0] < 0) { 1582 throw new dml_sessionwait_exception(); 1583 } 1584 } 1585 1586 $this->free_result($result); 1587 } 1588 1589 public function release_session_lock($rowid) { 1590 if (!$this->session_lock_supported()) { 1591 return; 1592 } 1593 if (!$this->used_for_db_sessions) { 1594 return; 1595 } 1596 1597 parent::release_session_lock($rowid); 1598 1599 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; 1600 $sql = "sp_releaseapplock '$fullname', 'Session'"; 1601 $this->query_start($sql, null, SQL_QUERY_AUX); 1602 $result = sqlsrv_query($this->sqlsrv, $sql); 1603 $this->query_end($result); 1604 $this->free_result($result); 1605 } 1606 1607 /** 1608 * Driver specific start of real database transaction, 1609 * this can not be used directly in code. 1610 * @return void 1611 */ 1612 protected function begin_transaction() { 1613 // Recordsets do not work well with transactions in SQL Server, 1614 // let's prefetch the recordsets to memory to work around these problems. 1615 foreach ($this->recordsets as $rs) { 1616 $rs->transaction_starts(); 1617 } 1618 1619 $this->query_start('native sqlsrv_begin_transaction', NULL, SQL_QUERY_AUX); 1620 $result = sqlsrv_begin_transaction($this->sqlsrv); 1621 $this->query_end($result); 1622 } 1623 1624 /** 1625 * Driver specific commit of real database transaction, 1626 * this can not be used directly in code. 1627 * @return void 1628 */ 1629 protected function commit_transaction() { 1630 $this->query_start('native sqlsrv_commit', NULL, SQL_QUERY_AUX); 1631 $result = sqlsrv_commit($this->sqlsrv); 1632 $this->query_end($result); 1633 } 1634 1635 /** 1636 * Driver specific abort of real database transaction, 1637 * this can not be used directly in code. 1638 * @return void 1639 */ 1640 protected function rollback_transaction() { 1641 $this->query_start('native sqlsrv_rollback', NULL, SQL_QUERY_AUX); 1642 $result = sqlsrv_rollback($this->sqlsrv); 1643 $this->query_end($result); 1644 } 1645 1646 /** 1647 * Is fulltext search enabled?. 1648 * 1649 * @return bool 1650 */ 1651 public function is_fulltext_search_supported() { 1652 global $CFG; 1653 1654 $sql = "SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')"; 1655 $this->query_start($sql, null, SQL_QUERY_AUX); 1656 $result = sqlsrv_query($this->sqlsrv, $sql); 1657 $this->query_end($result); 1658 if ($result) { 1659 if ($row = sqlsrv_fetch_array($result)) { 1660 $property = (bool)reset($row); 1661 } 1662 } 1663 $this->free_result($result); 1664 1665 return !empty($property); 1666 } 1667 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
