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