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