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