| [ 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 * Abstract database driver class. 19 * 20 * @package core_dml 21 * @copyright 2008 Petr Skoda (http://skodak.org) 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__.'/database_column_info.php'); 28 require_once (__DIR__.'/moodle_recordset.php'); 29 require_once (__DIR__.'/moodle_transaction.php'); 30 31 /** SQL_PARAMS_NAMED - Bitmask, indicates :name type parameters are supported by db backend. */ 32 define('SQL_PARAMS_NAMED', 1); 33 34 /** SQL_PARAMS_QM - Bitmask, indicates ? type parameters are supported by db backend. */ 35 define('SQL_PARAMS_QM', 2); 36 37 /** SQL_PARAMS_DOLLAR - Bitmask, indicates $1, $2, ... type parameters are supported by db backend. */ 38 define('SQL_PARAMS_DOLLAR', 4); 39 40 /** SQL_QUERY_SELECT - Normal select query, reading only. */ 41 define('SQL_QUERY_SELECT', 1); 42 43 /** SQL_QUERY_INSERT - Insert select query, writing. */ 44 define('SQL_QUERY_INSERT', 2); 45 46 /** SQL_QUERY_UPDATE - Update select query, writing. */ 47 define('SQL_QUERY_UPDATE', 3); 48 49 /** SQL_QUERY_STRUCTURE - Query changing db structure, writing. */ 50 define('SQL_QUERY_STRUCTURE', 4); 51 52 /** SQL_QUERY_AUX - Auxiliary query done by driver, setting connection config, getting table info, etc. */ 53 define('SQL_QUERY_AUX', 5); 54 55 /** 56 * Abstract class representing moodle database interface. 57 * @link http://docs.moodle.org/dev/DML_functions 58 * 59 * @package core_dml 60 * @copyright 2008 Petr Skoda (http://skodak.org) 61 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 62 */ 63 abstract class moodle_database { 64 65 /** @var database_manager db manager which allows db structure modifications. */ 66 protected $database_manager; 67 /** @var moodle_temptables temptables manager to provide cross-db support for temp tables. */ 68 protected $temptables; 69 /** @var array Cache of table info. */ 70 protected $tables = null; 71 72 // db connection options 73 /** @var string db host name. */ 74 protected $dbhost; 75 /** @var string db host user. */ 76 protected $dbuser; 77 /** @var string db host password. */ 78 protected $dbpass; 79 /** @var string db name. */ 80 protected $dbname; 81 /** @var string Prefix added to table names. */ 82 protected $prefix; 83 84 /** @var array Database or driver specific options, such as sockets or TCP/IP db connections. */ 85 protected $dboptions; 86 87 /** @var bool True means non-moodle external database used.*/ 88 protected $external; 89 90 /** @var int The database reads (performance counter).*/ 91 protected $reads = 0; 92 /** @var int The database writes (performance counter).*/ 93 protected $writes = 0; 94 /** @var float Time queries took to finish, seconds with microseconds.*/ 95 protected $queriestime = 0; 96 97 /** @var int Debug level. */ 98 protected $debug = 0; 99 100 /** @var string Last used query sql. */ 101 protected $last_sql; 102 /** @var array Last query parameters. */ 103 protected $last_params; 104 /** @var int Last query type. */ 105 protected $last_type; 106 /** @var string Last extra info. */ 107 protected $last_extrainfo; 108 /** @var float Last time in seconds with millisecond precision. */ 109 protected $last_time; 110 /** @var bool Flag indicating logging of query in progress. This helps prevent infinite loops. */ 111 private $loggingquery = false; 112 113 /** @var bool True if the db is used for db sessions. */ 114 protected $used_for_db_sessions = false; 115 116 /** @var array Array containing open transactions. */ 117 private $transactions = array(); 118 /** @var bool Flag used to force rollback of all current transactions. */ 119 private $force_rollback = false; 120 121 /** @var string MD5 of settings used for connection. Used by MUC as an identifier. */ 122 private $settingshash; 123 124 /** @var cache_application for column info */ 125 protected $metacache; 126 127 /** @var bool flag marking database instance as disposed */ 128 protected $disposed; 129 130 /** 131 * @var int internal temporary variable used to fix params. Its used by {@link _fix_sql_params_dollar_callback()}. 132 */ 133 private $fix_sql_params_i; 134 /** 135 * @var int internal temporary variable used to guarantee unique parameters in each request. Its used by {@link get_in_or_equal()}. 136 */ 137 private $inorequaluniqueindex = 1; 138 139 /** 140 * Constructor - Instantiates the database, specifying if it's external (connect to other systems) or not (Moodle DB). 141 * Note that this affects the decision of whether prefix checks must be performed or not. 142 * @param bool $external True means that an external database is used. 143 */ 144 public function __construct($external=false) { 145 $this->external = $external; 146 } 147 148 /** 149 * Destructor - cleans up and flushes everything needed. 150 */ 151 public function __destruct() { 152 $this->dispose(); 153 } 154 155 /** 156 * Detects if all needed PHP stuff are installed for DB connectivity. 157 * Note: can be used before connect() 158 * @return mixed True if requirements are met, otherwise a string if something isn't installed. 159 */ 160 public abstract function driver_installed(); 161 162 /** 163 * Returns database table prefix 164 * Note: can be used before connect() 165 * @return string The prefix used in the database. 166 */ 167 public function get_prefix() { 168 return $this->prefix; 169 } 170 171 /** 172 * Loads and returns a database instance with the specified type and library. 173 * 174 * The loaded class is within lib/dml directory and of the form: $type.'_'.$library.'_moodle_database' 175 * 176 * @param string $type Database driver's type. (eg: mysqli, pgsql, mssql, sqldrv, oci, etc.) 177 * @param string $library Database driver's library (native, pdo, etc.) 178 * @param bool $external True if this is an external database. 179 * @return moodle_database driver object or null if error, for example of driver object see {@link mysqli_native_moodle_database} 180 */ 181 public static function get_driver_instance($type, $library, $external = false) { 182 global $CFG; 183 184 $classname = $type.'_'.$library.'_moodle_database'; 185 $libfile = "$CFG->libdir/dml/$classname.php"; 186 187 if (!file_exists($libfile)) { 188 return null; 189 } 190 191 require_once($libfile); 192 return new $classname($external); 193 } 194 195 /** 196 * Returns the database vendor. 197 * Note: can be used before connect() 198 * @return string The db vendor name, usually the same as db family name. 199 */ 200 public function get_dbvendor() { 201 return $this->get_dbfamily(); 202 } 203 204 /** 205 * Returns the database family type. (This sort of describes the SQL 'dialect') 206 * Note: can be used before connect() 207 * @return string The db family name (mysql, postgres, mssql, oracle, etc.) 208 */ 209 public abstract function get_dbfamily(); 210 211 /** 212 * Returns a more specific database driver type 213 * Note: can be used before connect() 214 * @return string The db type mysqli, pgsql, oci, mssql, sqlsrv 215 */ 216 protected abstract function get_dbtype(); 217 218 /** 219 * Returns the general database library name 220 * Note: can be used before connect() 221 * @return string The db library type - pdo, native etc. 222 */ 223 protected abstract function get_dblibrary(); 224 225 /** 226 * Returns the localised database type name 227 * Note: can be used before connect() 228 * @return string 229 */ 230 public abstract function get_name(); 231 232 /** 233 * Returns the localised database configuration help. 234 * Note: can be used before connect() 235 * @return string 236 */ 237 public abstract function get_configuration_help(); 238 239 /** 240 * Returns the localised database description 241 * Note: can be used before connect() 242 * @deprecated since 2.6 243 * @return string 244 */ 245 public function get_configuration_hints() { 246 debugging('$DB->get_configuration_hints() method is deprecated, use $DB->get_configuration_help() instead'); 247 return $this->get_configuration_help(); 248 } 249 250 /** 251 * Returns the db related part of config.php 252 * @return stdClass 253 */ 254 public function export_dbconfig() { 255 $cfg = new stdClass(); 256 $cfg->dbtype = $this->get_dbtype(); 257 $cfg->dblibrary = $this->get_dblibrary(); 258 $cfg->dbhost = $this->dbhost; 259 $cfg->dbname = $this->dbname; 260 $cfg->dbuser = $this->dbuser; 261 $cfg->dbpass = $this->dbpass; 262 $cfg->prefix = $this->prefix; 263 if ($this->dboptions) { 264 $cfg->dboptions = $this->dboptions; 265 } 266 267 return $cfg; 268 } 269 270 /** 271 * Diagnose database and tables, this function is used 272 * to verify database and driver settings, db engine types, etc. 273 * 274 * @return string null means everything ok, string means problem found. 275 */ 276 public function diagnose() { 277 return null; 278 } 279 280 /** 281 * Connects to the database. 282 * Must be called before other methods. 283 * @param string $dbhost The database host. 284 * @param string $dbuser The database user to connect as. 285 * @param string $dbpass The password to use when connecting to the database. 286 * @param string $dbname The name of the database being connected to. 287 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used 288 * @param array $dboptions driver specific options 289 * @return bool true 290 * @throws dml_connection_exception if error 291 */ 292 public abstract function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null); 293 294 /** 295 * Store various database settings 296 * @param string $dbhost The database host. 297 * @param string $dbuser The database user to connect as. 298 * @param string $dbpass The password to use when connecting to the database. 299 * @param string $dbname The name of the database being connected to. 300 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used 301 * @param array $dboptions driver specific options 302 * @return void 303 */ 304 protected function store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) { 305 $this->dbhost = $dbhost; 306 $this->dbuser = $dbuser; 307 $this->dbpass = $dbpass; 308 $this->dbname = $dbname; 309 $this->prefix = $prefix; 310 $this->dboptions = (array)$dboptions; 311 } 312 313 /** 314 * Returns a hash for the settings used during connection. 315 * 316 * If not already requested it is generated and stored in a private property. 317 * 318 * @return string 319 */ 320 protected function get_settings_hash() { 321 if (empty($this->settingshash)) { 322 $this->settingshash = md5($this->dbhost . $this->dbuser . $this->dbname . $this->prefix); 323 } 324 return $this->settingshash; 325 } 326 327 /** 328 * Attempt to create the database 329 * @param string $dbhost The database host. 330 * @param string $dbuser The database user to connect as. 331 * @param string $dbpass The password to use when connecting to the database. 332 * @param string $dbname The name of the database being connected to. 333 * @param array $dboptions An array of optional database options (eg: dbport) 334 * 335 * @return bool success True for successful connection. False otherwise. 336 */ 337 public function create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null) { 338 return false; 339 } 340 341 /** 342 * Returns transaction trace for debugging purposes. 343 * @private to be used by core only 344 * @return array or null if not in transaction. 345 */ 346 public function get_transaction_start_backtrace() { 347 if (!$this->transactions) { 348 return null; 349 } 350 $lowesttransaction = end($this->transactions); 351 return $lowesttransaction->get_backtrace(); 352 } 353 354 /** 355 * Closes the database connection and releases all resources 356 * and memory (especially circular memory references). 357 * Do NOT use connect() again, create a new instance if needed. 358 * @return void 359 */ 360 public function dispose() { 361 if ($this->disposed) { 362 return; 363 } 364 $this->disposed = true; 365 if ($this->transactions) { 366 $this->force_transaction_rollback(); 367 } 368 369 if ($this->temptables) { 370 $this->temptables->dispose(); 371 $this->temptables = null; 372 } 373 if ($this->database_manager) { 374 $this->database_manager->dispose(); 375 $this->database_manager = null; 376 } 377 $this->tables = null; 378 } 379 380 /** 381 * This should be called before each db query. 382 * @param string $sql The query string. 383 * @param array $params An array of parameters. 384 * @param int $type The type of query. ( SQL_QUERY_SELECT | SQL_QUERY_AUX | SQL_QUERY_INSERT | SQL_QUERY_UPDATE | SQL_QUERY_STRUCTURE ) 385 * @param mixed $extrainfo This is here for any driver specific extra information. 386 * @return void 387 */ 388 protected function query_start($sql, array $params=null, $type, $extrainfo=null) { 389 if ($this->loggingquery) { 390 return; 391 } 392 $this->last_sql = $sql; 393 $this->last_params = $params; 394 $this->last_type = $type; 395 $this->last_extrainfo = $extrainfo; 396 $this->last_time = microtime(true); 397 398 switch ($type) { 399 case SQL_QUERY_SELECT: 400 case SQL_QUERY_AUX: 401 $this->reads++; 402 break; 403 case SQL_QUERY_INSERT: 404 case SQL_QUERY_UPDATE: 405 case SQL_QUERY_STRUCTURE: 406 $this->writes++; 407 } 408 409 $this->print_debug($sql, $params); 410 } 411 412 /** 413 * This should be called immediately after each db query. It does a clean up of resources. 414 * It also throws exceptions if the sql that ran produced errors. 415 * @param mixed $result The db specific result obtained from running a query. 416 * @throws dml_read_exception | dml_write_exception | ddl_change_structure_exception 417 * @return void 418 */ 419 protected function query_end($result) { 420 if ($this->loggingquery) { 421 return; 422 } 423 if ($result !== false) { 424 $this->query_log(); 425 // free memory 426 $this->last_sql = null; 427 $this->last_params = null; 428 $this->print_debug_time(); 429 return; 430 } 431 432 // remember current info, log queries may alter it 433 $type = $this->last_type; 434 $sql = $this->last_sql; 435 $params = $this->last_params; 436 $error = $this->get_last_error(); 437 438 $this->query_log($error); 439 440 switch ($type) { 441 case SQL_QUERY_SELECT: 442 case SQL_QUERY_AUX: 443 throw new dml_read_exception($error, $sql, $params); 444 case SQL_QUERY_INSERT: 445 case SQL_QUERY_UPDATE: 446 throw new dml_write_exception($error, $sql, $params); 447 case SQL_QUERY_STRUCTURE: 448 $this->get_manager(); // includes ddl exceptions classes ;-) 449 throw new ddl_change_structure_exception($error, $sql); 450 } 451 } 452 453 /** 454 * This logs the last query based on 'logall', 'logslow' and 'logerrors' options configured via $CFG->dboptions . 455 * @param string|bool $error or false if not error 456 * @return void 457 */ 458 public function query_log($error=false) { 459 $logall = !empty($this->dboptions['logall']); 460 $logslow = !empty($this->dboptions['logslow']) ? $this->dboptions['logslow'] : false; 461 $logerrors = !empty($this->dboptions['logerrors']); 462 $iserror = ($error !== false); 463 464 $time = $this->query_time(); 465 466 // Will be shown or not depending on MDL_PERF values rather than in dboptions['log*]. 467 $this->queriestime = $this->queriestime + $time; 468 469 if ($logall or ($logslow and ($logslow < ($time+0.00001))) or ($iserror and $logerrors)) { 470 $this->loggingquery = true; 471 try { 472 $backtrace = debug_backtrace(); 473 if ($backtrace) { 474 //remove query_log() 475 array_shift($backtrace); 476 } 477 if ($backtrace) { 478 //remove query_end() 479 array_shift($backtrace); 480 } 481 $log = new stdClass(); 482 $log->qtype = $this->last_type; 483 $log->sqltext = $this->last_sql; 484 $log->sqlparams = var_export((array)$this->last_params, true); 485 $log->error = (int)$iserror; 486 $log->info = $iserror ? $error : null; 487 $log->backtrace = format_backtrace($backtrace, true); 488 $log->exectime = $time; 489 $log->timelogged = time(); 490 $this->insert_record('log_queries', $log); 491 } catch (Exception $ignored) { 492 } 493 $this->loggingquery = false; 494 } 495 } 496 497 /** 498 * Returns the time elapsed since the query started. 499 * @return float Seconds with microseconds 500 */ 501 protected function query_time() { 502 return microtime(true) - $this->last_time; 503 } 504 505 /** 506 * Returns database server info array 507 * @return array Array containing 'description' and 'version' at least. 508 */ 509 public abstract function get_server_info(); 510 511 /** 512 * Returns supported query parameter types 513 * @return int bitmask of accepted SQL_PARAMS_* 514 */ 515 protected abstract function allowed_param_types(); 516 517 /** 518 * Returns the last error reported by the database engine. 519 * @return string The error message. 520 */ 521 public abstract function get_last_error(); 522 523 /** 524 * Prints sql debug info 525 * @param string $sql The query which is being debugged. 526 * @param array $params The query parameters. (optional) 527 * @param mixed $obj The library specific object. (optional) 528 * @return void 529 */ 530 protected function print_debug($sql, array $params=null, $obj=null) { 531 if (!$this->get_debug()) { 532 return; 533 } 534 if (CLI_SCRIPT) { 535 echo "--------------------------------\n"; 536 echo $sql."\n"; 537 if (!is_null($params)) { 538 echo "[".var_export($params, true)."]\n"; 539 } 540 echo "--------------------------------\n"; 541 } else { 542 echo "<hr />\n"; 543 echo s($sql)."\n"; 544 if (!is_null($params)) { 545 echo "[".s(var_export($params, true))."]\n"; 546 } 547 echo "<hr />\n"; 548 } 549 } 550 551 /** 552 * Prints the time a query took to run. 553 * @return void 554 */ 555 protected function print_debug_time() { 556 if (!$this->get_debug()) { 557 return; 558 } 559 $time = $this->query_time(); 560 $message = "Query took: {$time} seconds.\n"; 561 if (CLI_SCRIPT) { 562 echo $message; 563 echo "--------------------------------\n"; 564 } else { 565 echo s($message); 566 echo "<hr />\n"; 567 } 568 } 569 570 /** 571 * Returns the SQL WHERE conditions. 572 * @param string $table The table name that these conditions will be validated against. 573 * @param array $conditions The conditions to build the where clause. (must not contain numeric indexes) 574 * @throws dml_exception 575 * @return array An array list containing sql 'where' part and 'params'. 576 */ 577 protected function where_clause($table, array $conditions=null) { 578 // We accept nulls in conditions 579 $conditions = is_null($conditions) ? array() : $conditions; 580 // Some checks performed under debugging only 581 if (debugging()) { 582 $columns = $this->get_columns($table); 583 if (empty($columns)) { 584 // no supported columns means most probably table does not exist 585 throw new dml_exception('ddltablenotexist', $table); 586 } 587 foreach ($conditions as $key=>$value) { 588 if (!isset($columns[$key])) { 589 $a = new stdClass(); 590 $a->fieldname = $key; 591 $a->tablename = $table; 592 throw new dml_exception('ddlfieldnotexist', $a); 593 } 594 $column = $columns[$key]; 595 if ($column->meta_type == 'X') { 596 //ok so the column is a text column. sorry no text columns in the where clause conditions 597 throw new dml_exception('textconditionsnotallowed', $conditions); 598 } 599 } 600 } 601 602 $allowed_types = $this->allowed_param_types(); 603 if (empty($conditions)) { 604 return array('', array()); 605 } 606 $where = array(); 607 $params = array(); 608 609 foreach ($conditions as $key=>$value) { 610 if (is_int($key)) { 611 throw new dml_exception('invalidnumkey'); 612 } 613 if (is_null($value)) { 614 $where[] = "$key IS NULL"; 615 } else { 616 if ($allowed_types & SQL_PARAMS_NAMED) { 617 // Need to verify key names because they can contain, originally, 618 // spaces and other forbidden chars when using sql_xxx() functions and friends. 619 $normkey = trim(preg_replace('/[^a-zA-Z0-9_-]/', '_', $key), '-_'); 620 if ($normkey !== $key) { 621 debugging('Invalid key found in the conditions array.'); 622 } 623 $where[] = "$key = :$normkey"; 624 $params[$normkey] = $value; 625 } else { 626 $where[] = "$key = ?"; 627 $params[] = $value; 628 } 629 } 630 } 631 $where = implode(" AND ", $where); 632 return array($where, $params); 633 } 634 635 /** 636 * Returns SQL WHERE conditions for the ..._list group of methods. 637 * 638 * @param string $field the name of a field. 639 * @param array $values the values field might take. 640 * @return array An array containing sql 'where' part and 'params' 641 */ 642 protected function where_clause_list($field, array $values) { 643 if (empty($values)) { 644 return array("1 = 2", array()); // Fake condition, won't return rows ever. MDL-17645 645 } 646 647 // Note: Do not use get_in_or_equal() because it can not deal with bools and nulls. 648 649 $params = array(); 650 $select = ""; 651 $values = (array)$values; 652 foreach ($values as $value) { 653 if (is_bool($value)) { 654 $value = (int)$value; 655 } 656 if (is_null($value)) { 657 $select = "$field IS NULL"; 658 } else { 659 $params[] = $value; 660 } 661 } 662 if ($params) { 663 if ($select !== "") { 664 $select = "$select OR "; 665 } 666 $count = count($params); 667 if ($count == 1) { 668 $select = $select."$field = ?"; 669 } else { 670 $qs = str_repeat(',?', $count); 671 $qs = ltrim($qs, ','); 672 $select = $select."$field IN ($qs)"; 673 } 674 } 675 return array($select, $params); 676 } 677 678 /** 679 * Constructs 'IN()' or '=' sql fragment 680 * @param mixed $items A single value or array of values for the expression. 681 * @param int $type Parameter bounding type : SQL_PARAMS_QM or SQL_PARAMS_NAMED. 682 * @param string $prefix Named parameter placeholder prefix (a unique counter value is appended to each parameter name). 683 * @param bool $equal True means we want to equate to the constructed expression, false means we don't want to equate to it. 684 * @param mixed $onemptyitems This defines the behavior when the array of items provided is empty. Defaults to false, 685 * meaning throw exceptions. Other values will become part of the returned SQL fragment. 686 * @throws coding_exception | dml_exception 687 * @return array A list containing the constructed sql fragment and an array of parameters. 688 */ 689 public function get_in_or_equal($items, $type=SQL_PARAMS_QM, $prefix='param', $equal=true, $onemptyitems=false) { 690 691 // default behavior, throw exception on empty array 692 if (is_array($items) and empty($items) and $onemptyitems === false) { 693 throw new coding_exception('moodle_database::get_in_or_equal() does not accept empty arrays'); 694 } 695 // handle $onemptyitems on empty array of items 696 if (is_array($items) and empty($items)) { 697 if (is_null($onemptyitems)) { // Special case, NULL value 698 $sql = $equal ? ' IS NULL' : ' IS NOT NULL'; 699 return (array($sql, array())); 700 } else { 701 $items = array($onemptyitems); // Rest of cases, prepare $items for std processing 702 } 703 } 704 705 if ($type == SQL_PARAMS_QM) { 706 if (!is_array($items) or count($items) == 1) { 707 $sql = $equal ? '= ?' : '<> ?'; 708 $items = (array)$items; 709 $params = array_values($items); 710 } else { 711 if ($equal) { 712 $sql = 'IN ('.implode(',', array_fill(0, count($items), '?')).')'; 713 } else { 714 $sql = 'NOT IN ('.implode(',', array_fill(0, count($items), '?')).')'; 715 } 716 $params = array_values($items); 717 } 718 719 } else if ($type == SQL_PARAMS_NAMED) { 720 if (empty($prefix)) { 721 $prefix = 'param'; 722 } 723 724 if (!is_array($items)){ 725 $param = $prefix.$this->inorequaluniqueindex++; 726 $sql = $equal ? "= :$param" : "<> :$param"; 727 $params = array($param=>$items); 728 } else if (count($items) == 1) { 729 $param = $prefix.$this->inorequaluniqueindex++; 730 $sql = $equal ? "= :$param" : "<> :$param"; 731 $item = reset($items); 732 $params = array($param=>$item); 733 } else { 734 $params = array(); 735 $sql = array(); 736 foreach ($items as $item) { 737 $param = $prefix.$this->inorequaluniqueindex++; 738 $params[$param] = $item; 739 $sql[] = ':'.$param; 740 } 741 if ($equal) { 742 $sql = 'IN ('.implode(',', $sql).')'; 743 } else { 744 $sql = 'NOT IN ('.implode(',', $sql).')'; 745 } 746 } 747 748 } else { 749 throw new dml_exception('typenotimplement'); 750 } 751 return array($sql, $params); 752 } 753 754 /** 755 * Converts short table name {tablename} to the real prefixed table name in given sql. 756 * @param string $sql The sql to be operated on. 757 * @return string The sql with tablenames being prefixed with $CFG->prefix 758 */ 759 protected function fix_table_names($sql) { 760 return preg_replace('/\{([a-z][a-z0-9_]*)\}/', $this->prefix.'$1', $sql); 761 } 762 763 /** 764 * Internal private utitlity function used to fix parameters. 765 * Used with {@link preg_replace_callback()} 766 * @param array $match Refer to preg_replace_callback usage for description. 767 * @return string 768 */ 769 private function _fix_sql_params_dollar_callback($match) { 770 $this->fix_sql_params_i++; 771 return "\$".$this->fix_sql_params_i; 772 } 773 774 /** 775 * Detects object parameters and throws exception if found 776 * @param mixed $value 777 * @return void 778 * @throws coding_exception if object detected 779 */ 780 protected function detect_objects($value) { 781 if (is_object($value)) { 782 throw new coding_exception('Invalid database query parameter value', 'Objects are are not allowed: '.get_class($value)); 783 } 784 } 785 786 /** 787 * Normalizes sql query parameters and verifies parameters. 788 * @param string $sql The query or part of it. 789 * @param array $params The query parameters. 790 * @return array (sql, params, type of params) 791 */ 792 public function fix_sql_params($sql, array $params=null) { 793 $params = (array)$params; // mke null array if needed 794 $allowed_types = $this->allowed_param_types(); 795 796 // convert table names 797 $sql = $this->fix_table_names($sql); 798 799 // cast booleans to 1/0 int and detect forbidden objects 800 foreach ($params as $key => $value) { 801 $this->detect_objects($value); 802 $params[$key] = is_bool($value) ? (int)$value : $value; 803 } 804 805 // NICOLAS C: Fixed regexp for negative backwards look-ahead of double colons. Thanks for Sam Marshall's help 806 $named_count = preg_match_all('/(?<!:):[a-z][a-z0-9_]*/', $sql, $named_matches); // :: used in pgsql casts 807 $dollar_count = preg_match_all('/\$[1-9][0-9]*/', $sql, $dollar_matches); 808 $q_count = substr_count($sql, '?'); 809 810 $count = 0; 811 812 if ($named_count) { 813 $type = SQL_PARAMS_NAMED; 814 $count = $named_count; 815 816 } 817 if ($dollar_count) { 818 if ($count) { 819 throw new dml_exception('mixedtypesqlparam'); 820 } 821 $type = SQL_PARAMS_DOLLAR; 822 $count = $dollar_count; 823 824 } 825 if ($q_count) { 826 if ($count) { 827 throw new dml_exception('mixedtypesqlparam'); 828 } 829 $type = SQL_PARAMS_QM; 830 $count = $q_count; 831 832 } 833 834 if (!$count) { 835 // ignore params 836 if ($allowed_types & SQL_PARAMS_NAMED) { 837 return array($sql, array(), SQL_PARAMS_NAMED); 838 } else if ($allowed_types & SQL_PARAMS_QM) { 839 return array($sql, array(), SQL_PARAMS_QM); 840 } else { 841 return array($sql, array(), SQL_PARAMS_DOLLAR); 842 } 843 } 844 845 if ($count > count($params)) { 846 $a = new stdClass; 847 $a->expected = $count; 848 $a->actual = count($params); 849 throw new dml_exception('invalidqueryparam', $a); 850 } 851 852 $target_type = $allowed_types; 853 854 if ($type & $allowed_types) { // bitwise AND 855 if ($count == count($params)) { 856 if ($type == SQL_PARAMS_QM) { 857 return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based array required 858 } else { 859 //better do the validation of names below 860 } 861 } 862 // needs some fixing or validation - there might be more params than needed 863 $target_type = $type; 864 } 865 866 if ($type == SQL_PARAMS_NAMED) { 867 $finalparams = array(); 868 foreach ($named_matches[0] as $key) { 869 $key = trim($key, ':'); 870 if (!array_key_exists($key, $params)) { 871 throw new dml_exception('missingkeyinsql', $key, ''); 872 } 873 if (strlen($key) > 30) { 874 throw new coding_exception( 875 "Placeholder names must be 30 characters or shorter. '" . 876 $key . "' is too long.", $sql); 877 } 878 $finalparams[$key] = $params[$key]; 879 } 880 if ($count != count($finalparams)) { 881 throw new dml_exception('duplicateparaminsql'); 882 } 883 884 if ($target_type & SQL_PARAMS_QM) { 885 $sql = preg_replace('/(?<!:):[a-z][a-z0-9_]*/', '?', $sql); 886 return array($sql, array_values($finalparams), SQL_PARAMS_QM); // 0-based required 887 } else if ($target_type & SQL_PARAMS_NAMED) { 888 return array($sql, $finalparams, SQL_PARAMS_NAMED); 889 } else { // $type & SQL_PARAMS_DOLLAR 890 //lambda-style functions eat memory - we use globals instead :-( 891 $this->fix_sql_params_i = 0; 892 $sql = preg_replace_callback('/(?<!:):[a-z][a-z0-9_]*/', array($this, '_fix_sql_params_dollar_callback'), $sql); 893 return array($sql, array_values($finalparams), SQL_PARAMS_DOLLAR); // 0-based required 894 } 895 896 } else if ($type == SQL_PARAMS_DOLLAR) { 897 if ($target_type & SQL_PARAMS_DOLLAR) { 898 return array($sql, array_values($params), SQL_PARAMS_DOLLAR); // 0-based required 899 } else if ($target_type & SQL_PARAMS_QM) { 900 $sql = preg_replace('/\$[0-9]+/', '?', $sql); 901 return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based required 902 } else { //$target_type & SQL_PARAMS_NAMED 903 $sql = preg_replace('/\$([0-9]+)/', ':param\\1', $sql); 904 $finalparams = array(); 905 foreach ($params as $key=>$param) { 906 $key++; 907 $finalparams['param'.$key] = $param; 908 } 909 return array($sql, $finalparams, SQL_PARAMS_NAMED); 910 } 911 912 } else { // $type == SQL_PARAMS_QM 913 if (count($params) != $count) { 914 $params = array_slice($params, 0, $count); 915 } 916 917 if ($target_type & SQL_PARAMS_QM) { 918 return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based required 919 } else if ($target_type & SQL_PARAMS_NAMED) { 920 $finalparams = array(); 921 $pname = 'param0'; 922 $parts = explode('?', $sql); 923 $sql = array_shift($parts); 924 foreach ($parts as $part) { 925 $param = array_shift($params); 926 $pname++; 927 $sql .= ':'.$pname.$part; 928 $finalparams[$pname] = $param; 929 } 930 return array($sql, $finalparams, SQL_PARAMS_NAMED); 931 } else { // $type & SQL_PARAMS_DOLLAR 932 //lambda-style functions eat memory - we use globals instead :-( 933 $this->fix_sql_params_i = 0; 934 $sql = preg_replace_callback('/\?/', array($this, '_fix_sql_params_dollar_callback'), $sql); 935 return array($sql, array_values($params), SQL_PARAMS_DOLLAR); // 0-based required 936 } 937 } 938 } 939 940 /** 941 * Ensures that limit params are numeric and positive integers, to be passed to the database. 942 * We explicitly treat null, '' and -1 as 0 in order to provide compatibility with how limit 943 * values have been passed historically. 944 * 945 * @param int $limitfrom Where to start results from 946 * @param int $limitnum How many results to return 947 * @return array Normalised limit params in array($limitfrom, $limitnum) 948 */ 949 protected function normalise_limit_from_num($limitfrom, $limitnum) { 950 global $CFG; 951 952 // We explicilty treat these cases as 0. 953 if ($limitfrom === null || $limitfrom === '' || $limitfrom === -1) { 954 $limitfrom = 0; 955 } 956 if ($limitnum === null || $limitnum === '' || $limitnum === -1) { 957 $limitnum = 0; 958 } 959 960 if ($CFG->debugdeveloper) { 961 if (!is_numeric($limitfrom)) { 962 $strvalue = var_export($limitfrom, true); 963 debugging("Non-numeric limitfrom parameter detected: $strvalue, did you pass the correct arguments?", 964 DEBUG_DEVELOPER); 965 } else if ($limitfrom < 0) { 966 debugging("Negative limitfrom parameter detected: $limitfrom, did you pass the correct arguments?", 967 DEBUG_DEVELOPER); 968 } 969 970 if (!is_numeric($limitnum)) { 971 $strvalue = var_export($limitnum, true); 972 debugging("Non-numeric limitnum parameter detected: $strvalue, did you pass the correct arguments?", 973 DEBUG_DEVELOPER); 974 } else if ($limitnum < 0) { 975 debugging("Negative limitnum parameter detected: $limitnum, did you pass the correct arguments?", 976 DEBUG_DEVELOPER); 977 } 978 } 979 980 $limitfrom = (int)$limitfrom; 981 $limitnum = (int)$limitnum; 982 $limitfrom = max(0, $limitfrom); 983 $limitnum = max(0, $limitnum); 984 985 return array($limitfrom, $limitnum); 986 } 987 988 /** 989 * Return tables in database WITHOUT current prefix. 990 * @param bool $usecache if true, returns list of cached tables. 991 * @return array of table names in lowercase and without prefix 992 */ 993 public abstract function get_tables($usecache=true); 994 995 /** 996 * Return table indexes - everything lowercased. 997 * @param string $table The table we want to get indexes from. 998 * @return array An associative array of indexes containing 'unique' flag and 'columns' being indexed 999 */ 1000 public abstract function get_indexes($table); 1001 1002 /** 1003 * Returns detailed information about columns in table. This information is cached internally. 1004 * @param string $table The table's name. 1005 * @param bool $usecache Flag to use internal cacheing. The default is true. 1006 * @return array of database_column_info objects indexed with column names 1007 */ 1008 public abstract function get_columns($table, $usecache=true); 1009 1010 /** 1011 * Normalise values based on varying RDBMS's dependencies (booleans, LOBs...) 1012 * 1013 * @param database_column_info $column column metadata corresponding with the value we are going to normalise 1014 * @param mixed $value value we are going to normalise 1015 * @return mixed the normalised value 1016 */ 1017 protected abstract function normalise_value($column, $value); 1018 1019 /** 1020 * Resets the internal column details cache 1021 * @return void 1022 */ 1023 public function reset_caches() { 1024 $this->tables = null; 1025 // Purge MUC as well 1026 $identifiers = array('dbfamily' => $this->get_dbfamily(), 'settings' => $this->get_settings_hash()); 1027 cache_helper::purge_by_definition('core', 'databasemeta', $identifiers); 1028 } 1029 1030 /** 1031 * Returns the sql generator used for db manipulation. 1032 * Used mostly in upgrade.php scripts. 1033 * @return database_manager The instance used to perform ddl operations. 1034 * @see lib/ddl/database_manager.php 1035 */ 1036 public function get_manager() { 1037 global $CFG; 1038 1039 if (!$this->database_manager) { 1040 require_once($CFG->libdir.'/ddllib.php'); 1041 1042 $classname = $this->get_dbfamily().'_sql_generator'; 1043 require_once("$CFG->libdir/ddl/$classname.php"); 1044 $generator = new $classname($this, $this->temptables); 1045 1046 $this->database_manager = new database_manager($this, $generator); 1047 } 1048 return $this->database_manager; 1049 } 1050 1051 /** 1052 * Attempts to change db encoding to UTF-8 encoding if possible. 1053 * @return bool True is successful. 1054 */ 1055 public function change_db_encoding() { 1056 return false; 1057 } 1058 1059 /** 1060 * Checks to see if the database is in unicode mode? 1061 * @return bool 1062 */ 1063 public function setup_is_unicodedb() { 1064 return true; 1065 } 1066 1067 /** 1068 * Enable/disable very detailed debugging. 1069 * @param bool $state 1070 * @return void 1071 */ 1072 public function set_debug($state) { 1073 $this->debug = $state; 1074 } 1075 1076 /** 1077 * Returns debug status 1078 * @return bool $state 1079 */ 1080 public function get_debug() { 1081 return $this->debug; 1082 } 1083 1084 /** 1085 * Enable/disable detailed sql logging 1086 * @param bool $state 1087 */ 1088 public function set_logging($state) { 1089 // adodb sql logging shares one table without prefix per db - this is no longer acceptable :-( 1090 // we must create one table shared by all drivers 1091 } 1092 1093 /** 1094 * Do NOT use in code, this is for use by database_manager only! 1095 * @param string|array $sql query or array of queries 1096 * @return bool true 1097 * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors. 1098 */ 1099 public abstract function change_database_structure($sql); 1100 1101 /** 1102 * Executes a general sql query. Should be used only when no other method suitable. 1103 * Do NOT use this to make changes in db structure, use database_manager methods instead! 1104 * @param string $sql query 1105 * @param array $params query parameters 1106 * @return bool true 1107 * @throws dml_exception A DML specific exception is thrown for any errors. 1108 */ 1109 public abstract function execute($sql, array $params=null); 1110 1111 /** 1112 * Get a number of records as a moodle_recordset where all the given conditions met. 1113 * 1114 * Selects records from the table $table. 1115 * 1116 * If specified, only records meeting $conditions. 1117 * 1118 * If specified, the results will be sorted as specified by $sort. This 1119 * is added to the SQL as "ORDER BY $sort". Example values of $sort 1120 * might be "time ASC" or "time DESC". 1121 * 1122 * If $fields is specified, only those fields are returned. 1123 * 1124 * Since this method is a little less readable, use of it should be restricted to 1125 * code where it's possible there might be large datasets being returned. For known 1126 * small datasets use get_records - it leads to simpler code. 1127 * 1128 * If you only want some of the records, specify $limitfrom and $limitnum. 1129 * The query will skip the first $limitfrom records (according to the sort 1130 * order) and then return the next $limitnum records. If either of $limitfrom 1131 * or $limitnum is specified, both must be present. 1132 * 1133 * The return value is a moodle_recordset 1134 * if the query succeeds. If an error occurs, false is returned. 1135 * 1136 * @param string $table the table to query. 1137 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1138 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). 1139 * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned). 1140 * @param int $limitfrom return a subset of records, starting at this point (optional). 1141 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1142 * @return moodle_recordset A moodle_recordset instance 1143 * @throws dml_exception A DML specific exception is thrown for any errors. 1144 */ 1145 public function get_recordset($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1146 list($select, $params) = $this->where_clause($table, $conditions); 1147 return $this->get_recordset_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum); 1148 } 1149 1150 /** 1151 * Get a number of records as a moodle_recordset where one field match one list of values. 1152 * 1153 * Only records where $field takes one of the values $values are returned. 1154 * $values must be an array of values. 1155 * 1156 * Other arguments and the return type are like {@link function get_recordset}. 1157 * 1158 * @param string $table the table to query. 1159 * @param string $field a field to check (optional). 1160 * @param array $values array of values the field must have 1161 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). 1162 * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned). 1163 * @param int $limitfrom return a subset of records, starting at this point (optional). 1164 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1165 * @return moodle_recordset A moodle_recordset instance. 1166 * @throws dml_exception A DML specific exception is thrown for any errors. 1167 */ 1168 public function get_recordset_list($table, $field, array $values, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1169 list($select, $params) = $this->where_clause_list($field, $values); 1170 return $this->get_recordset_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum); 1171 } 1172 1173 /** 1174 * Get a number of records as a moodle_recordset which match a particular WHERE clause. 1175 * 1176 * If given, $select is used as the SELECT parameter in the SQL query, 1177 * otherwise all records from the table are returned. 1178 * 1179 * Other arguments and the return type are like {@link function get_recordset}. 1180 * 1181 * @param string $table the table to query. 1182 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1183 * @param array $params array of sql parameters 1184 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). 1185 * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned). 1186 * @param int $limitfrom return a subset of records, starting at this point (optional). 1187 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1188 * @return moodle_recordset A moodle_recordset instance. 1189 * @throws dml_exception A DML specific exception is thrown for any errors. 1190 */ 1191 public function get_recordset_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1192 $sql = "SELECT $fields FROM {".$table."}"; 1193 if ($select) { 1194 $sql .= " WHERE $select"; 1195 } 1196 if ($sort) { 1197 $sql .= " ORDER BY $sort"; 1198 } 1199 return $this->get_recordset_sql($sql, $params, $limitfrom, $limitnum); 1200 } 1201 1202 /** 1203 * Get a number of records as a moodle_recordset using a SQL statement. 1204 * 1205 * Since this method is a little less readable, use of it should be restricted to 1206 * code where it's possible there might be large datasets being returned. For known 1207 * small datasets use get_records_sql - it leads to simpler code. 1208 * 1209 * The return type is like {@link function get_recordset}. 1210 * 1211 * @param string $sql the SQL select query to execute. 1212 * @param array $params array of sql parameters 1213 * @param int $limitfrom return a subset of records, starting at this point (optional). 1214 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1215 * @return moodle_recordset A moodle_recordset instance. 1216 * @throws dml_exception A DML specific exception is thrown for any errors. 1217 */ 1218 public abstract function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0); 1219 1220 /** 1221 * Get all records from a table. 1222 * 1223 * This method works around potential memory problems and may improve performance, 1224 * this method may block access to table until the recordset is closed. 1225 * 1226 * @param string $table Name of database table. 1227 * @return moodle_recordset A moodle_recordset instance {@link function get_recordset}. 1228 * @throws dml_exception A DML specific exception is thrown for any errors. 1229 */ 1230 public function export_table_recordset($table) { 1231 return $this->get_recordset($table, array()); 1232 } 1233 1234 /** 1235 * Get a number of records as an array of objects where all the given conditions met. 1236 * 1237 * If the query succeeds and returns at least one record, the 1238 * return value is an array of objects, one object for each 1239 * record found. The array key is the value from the first 1240 * column of the result set. The object associated with that key 1241 * has a member variable for each column of the results. 1242 * 1243 * @param string $table the table to query. 1244 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1245 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). 1246 * @param string $fields a comma separated list of fields to return (optional, by default 1247 * all fields are returned). The first field will be used as key for the 1248 * array so must be a unique field such as 'id'. 1249 * @param int $limitfrom return a subset of records, starting at this point (optional). 1250 * @param int $limitnum return a subset comprising this many records in total (optional, required if $limitfrom is set). 1251 * @return array An array of Objects indexed by first column. 1252 * @throws dml_exception A DML specific exception is thrown for any errors. 1253 */ 1254 public function get_records($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1255 list($select, $params) = $this->where_clause($table, $conditions); 1256 return $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum); 1257 } 1258 1259 /** 1260 * Get a number of records as an array of objects where one field match one list of values. 1261 * 1262 * Return value is like {@link function get_records}. 1263 * 1264 * @param string $table The database table to be checked against. 1265 * @param string $field The field to search 1266 * @param array $values An array of values 1267 * @param string $sort Sort order (as valid SQL sort parameter) 1268 * @param string $fields A comma separated list of fields to be returned from the chosen table. If specified, 1269 * the first field should be a unique one such as 'id' since it will be used as a key in the associative 1270 * array. 1271 * @param int $limitfrom return a subset of records, starting at this point (optional). 1272 * @param int $limitnum return a subset comprising this many records in total (optional). 1273 * @return array An array of objects indexed by first column 1274 * @throws dml_exception A DML specific exception is thrown for any errors. 1275 */ 1276 public function get_records_list($table, $field, array $values, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1277 list($select, $params) = $this->where_clause_list($field, $values); 1278 return $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum); 1279 } 1280 1281 /** 1282 * Get a number of records as an array of objects which match a particular WHERE clause. 1283 * 1284 * Return value is like {@link function get_records}. 1285 * 1286 * @param string $table The table to query. 1287 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1288 * @param array $params An array of sql parameters 1289 * @param string $sort An order to sort the results in (optional, a valid SQL ORDER BY parameter). 1290 * @param string $fields A comma separated list of fields to return 1291 * (optional, by default all fields are returned). The first field will be used as key for the 1292 * array so must be a unique field such as 'id'. 1293 * @param int $limitfrom return a subset of records, starting at this point (optional). 1294 * @param int $limitnum return a subset comprising this many records in total (optional, required if $limitfrom is set). 1295 * @return array of objects indexed by first column 1296 * @throws dml_exception A DML specific exception is thrown for any errors. 1297 */ 1298 public function get_records_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1299 if ($select) { 1300 $select = "WHERE $select"; 1301 } 1302 if ($sort) { 1303 $sort = " ORDER BY $sort"; 1304 } 1305 return $this->get_records_sql("SELECT $fields FROM {" . $table . "} $select $sort", $params, $limitfrom, $limitnum); 1306 } 1307 1308 /** 1309 * Get a number of records as an array of objects using a SQL statement. 1310 * 1311 * Return value is like {@link function get_records}. 1312 * 1313 * @param string $sql the SQL select query to execute. The first column of this SELECT statement 1314 * must be a unique value (usually the 'id' field), as it will be used as the key of the 1315 * returned array. 1316 * @param array $params array of sql parameters 1317 * @param int $limitfrom return a subset of records, starting at this point (optional). 1318 * @param int $limitnum return a subset comprising this many records in total (optional, required if $limitfrom is set). 1319 * @return array of objects indexed by first column 1320 * @throws dml_exception A DML specific exception is thrown for any errors. 1321 */ 1322 public abstract function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0); 1323 1324 /** 1325 * Get the first two columns from a number of records as an associative array where all the given conditions met. 1326 * 1327 * Arguments are like {@link function get_recordset}. 1328 * 1329 * If no errors occur the return value 1330 * is an associative whose keys come from the first field of each record, 1331 * and whose values are the corresponding second fields. 1332 * False is returned if an error occurs. 1333 * 1334 * @param string $table the table to query. 1335 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1336 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). 1337 * @param string $fields a comma separated list of fields to return - the number of fields should be 2! 1338 * @param int $limitfrom return a subset of records, starting at this point (optional). 1339 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1340 * @return array an associative array 1341 * @throws dml_exception A DML specific exception is thrown for any errors. 1342 */ 1343 public function get_records_menu($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1344 $menu = array(); 1345 if ($records = $this->get_records($table, $conditions, $sort, $fields, $limitfrom, $limitnum)) { 1346 foreach ($records as $record) { 1347 $record = (array)$record; 1348 $key = array_shift($record); 1349 $value = array_shift($record); 1350 $menu[$key] = $value; 1351 } 1352 } 1353 return $menu; 1354 } 1355 1356 /** 1357 * Get the first two columns from a number of records as an associative array which match a particular WHERE clause. 1358 * 1359 * Arguments are like {@link function get_recordset_select}. 1360 * Return value is like {@link function get_records_menu}. 1361 * 1362 * @param string $table The database table to be checked against. 1363 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1364 * @param array $params array of sql parameters 1365 * @param string $sort Sort order (optional) - a valid SQL order parameter 1366 * @param string $fields A comma separated list of fields to be returned from the chosen table - the number of fields should be 2! 1367 * @param int $limitfrom return a subset of records, starting at this point (optional). 1368 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1369 * @return array an associative array 1370 * @throws dml_exception A DML specific exception is thrown for any errors. 1371 */ 1372 public function get_records_select_menu($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1373 $menu = array(); 1374 if ($records = $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum)) { 1375 foreach ($records as $record) { 1376 $record = (array)$record; 1377 $key = array_shift($record); 1378 $value = array_shift($record); 1379 $menu[$key] = $value; 1380 } 1381 } 1382 return $menu; 1383 } 1384 1385 /** 1386 * Get the first two columns from a number of records as an associative array using a SQL statement. 1387 * 1388 * Arguments are like {@link function get_recordset_sql}. 1389 * Return value is like {@link function get_records_menu}. 1390 * 1391 * @param string $sql The SQL string you wish to be executed. 1392 * @param array $params array of sql parameters 1393 * @param int $limitfrom return a subset of records, starting at this point (optional). 1394 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1395 * @return array an associative array 1396 * @throws dml_exception A DML specific exception is thrown for any errors. 1397 */ 1398 public function get_records_sql_menu($sql, array $params=null, $limitfrom=0, $limitnum=0) { 1399 $menu = array(); 1400 if ($records = $this->get_records_sql($sql, $params, $limitfrom, $limitnum)) { 1401 foreach ($records as $record) { 1402 $record = (array)$record; 1403 $key = array_shift($record); 1404 $value = array_shift($record); 1405 $menu[$key] = $value; 1406 } 1407 } 1408 return $menu; 1409 } 1410 1411 /** 1412 * Get a single database record as an object where all the given conditions met. 1413 * 1414 * @param string $table The table to select from. 1415 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1416 * @param string $fields A comma separated list of fields to be returned from the chosen table. 1417 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1418 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1419 * MUST_EXIST means we will throw an exception if no record or multiple records found. 1420 * 1421 * @todo MDL-30407 MUST_EXIST option should not throw a dml_exception, it should throw a different exception as it's a requested check. 1422 * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode 1423 * @throws dml_exception A DML specific exception is thrown for any errors. 1424 */ 1425 public function get_record($table, array $conditions, $fields='*', $strictness=IGNORE_MISSING) { 1426 list($select, $params) = $this->where_clause($table, $conditions); 1427 return $this->get_record_select($table, $select, $params, $fields, $strictness); 1428 } 1429 1430 /** 1431 * Get a single database record as an object which match a particular WHERE clause. 1432 * 1433 * @param string $table The database table to be checked against. 1434 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1435 * @param array $params array of sql parameters 1436 * @param string $fields A comma separated list of fields to be returned from the chosen table. 1437 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1438 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1439 * MUST_EXIST means throw exception if no record or multiple records found 1440 * @return stdClass|false a fieldset object containing the first matching record, false or exception if error not found depending on mode 1441 * @throws dml_exception A DML specific exception is thrown for any errors. 1442 */ 1443 public function get_record_select($table, $select, array $params=null, $fields='*', $strictness=IGNORE_MISSING) { 1444 if ($select) { 1445 $select = "WHERE $select"; 1446 } 1447 try { 1448 return $this->get_record_sql("SELECT $fields FROM {" . $table . "} $select", $params, $strictness); 1449 } catch (dml_missing_record_exception $e) { 1450 // create new exception which will contain correct table name 1451 throw new dml_missing_record_exception($table, $e->sql, $e->params); 1452 } 1453 } 1454 1455 /** 1456 * Get a single database record as an object using a SQL statement. 1457 * 1458 * The SQL statement should normally only return one record. 1459 * It is recommended to use get_records_sql() if more matches possible! 1460 * 1461 * @param string $sql The SQL string you wish to be executed, should normally only return one record. 1462 * @param array $params array of sql parameters 1463 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1464 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1465 * MUST_EXIST means throw exception if no record or multiple records found 1466 * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode 1467 * @throws dml_exception A DML specific exception is thrown for any errors. 1468 */ 1469 public function get_record_sql($sql, array $params=null, $strictness=IGNORE_MISSING) { 1470 $strictness = (int)$strictness; // we support true/false for BC reasons too 1471 if ($strictness == IGNORE_MULTIPLE) { 1472 $count = 1; 1473 } else { 1474 $count = 0; 1475 } 1476 if (!$records = $this->get_records_sql($sql, $params, 0, $count)) { 1477 // not found 1478 if ($strictness == MUST_EXIST) { 1479 throw new dml_missing_record_exception('', $sql, $params); 1480 } 1481 return false; 1482 } 1483 1484 if (count($records) > 1) { 1485 if ($strictness == MUST_EXIST) { 1486 throw new dml_multiple_records_exception($sql, $params); 1487 } 1488 debugging('Error: mdb->get_record() found more than one record!'); 1489 } 1490 1491 $return = reset($records); 1492 return $return; 1493 } 1494 1495 /** 1496 * Get a single field value from a table record where all the given conditions met. 1497 * 1498 * @param string $table the table to query. 1499 * @param string $return the field to return the value of. 1500 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1501 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1502 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1503 * MUST_EXIST means throw exception if no record or multiple records found 1504 * @return mixed the specified value false if not found 1505 * @throws dml_exception A DML specific exception is thrown for any errors. 1506 */ 1507 public function get_field($table, $return, array $conditions, $strictness=IGNORE_MISSING) { 1508 list($select, $params) = $this->where_clause($table, $conditions); 1509 return $this->get_field_select($table, $return, $select, $params, $strictness); 1510 } 1511 1512 /** 1513 * Get a single field value from a table record which match a particular WHERE clause. 1514 * 1515 * @param string $table the table to query. 1516 * @param string $return the field to return the value of. 1517 * @param string $select A fragment of SQL to be used in a where clause returning one row with one column 1518 * @param array $params array of sql parameters 1519 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1520 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1521 * MUST_EXIST means throw exception if no record or multiple records found 1522 * @return mixed the specified value false if not found 1523 * @throws dml_exception A DML specific exception is thrown for any errors. 1524 */ 1525 public function get_field_select($table, $return, $select, array $params=null, $strictness=IGNORE_MISSING) { 1526 if ($select) { 1527 $select = "WHERE $select"; 1528 } 1529 try { 1530 return $this->get_field_sql("SELECT $return FROM {" . $table . "} $select", $params, $strictness); 1531 } catch (dml_missing_record_exception $e) { 1532 // create new exception which will contain correct table name 1533 throw new dml_missing_record_exception($table, $e->sql, $e->params); 1534 } 1535 } 1536 1537 /** 1538 * Get a single field value (first field) using a SQL statement. 1539 * 1540 * @param string $sql The SQL query returning one row with one column 1541 * @param array $params array of sql parameters 1542 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1543 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1544 * MUST_EXIST means throw exception if no record or multiple records found 1545 * @return mixed the specified value false if not found 1546 * @throws dml_exception A DML specific exception is thrown for any errors. 1547 */ 1548 public function get_field_sql($sql, array $params=null, $strictness=IGNORE_MISSING) { 1549 if (!$record = $this->get_record_sql($sql, $params, $strictness)) { 1550 return false; 1551 } 1552 1553 $record = (array)$record; 1554 return reset($record); // first column 1555 } 1556 1557 /** 1558 * Selects records and return values of chosen field as an array which match a particular WHERE clause. 1559 * 1560 * @param string $table the table to query. 1561 * @param string $return the field we are intered in 1562 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1563 * @param array $params array of sql parameters 1564 * @return array of values 1565 * @throws dml_exception A DML specific exception is thrown for any errors. 1566 */ 1567 public function get_fieldset_select($table, $return, $select, array $params=null) { 1568 if ($select) { 1569 $select = "WHERE $select"; 1570 } 1571 return $this->get_fieldset_sql("SELECT $return FROM {" . $table . "} $select", $params); 1572 } 1573 1574 /** 1575 * Selects records and return values (first field) as an array using a SQL statement. 1576 * 1577 * @param string $sql The SQL query 1578 * @param array $params array of sql parameters 1579 * @return array of values 1580 * @throws dml_exception A DML specific exception is thrown for any errors. 1581 */ 1582 public abstract function get_fieldset_sql($sql, array $params=null); 1583 1584 /** 1585 * Insert new record into database, as fast as possible, no safety checks, lobs not supported. 1586 * @param string $table name 1587 * @param mixed $params data record as object or array 1588 * @param bool $returnid Returns id of inserted record. 1589 * @param bool $bulk true means repeated inserts expected 1590 * @param bool $customsequence true if 'id' included in $params, disables $returnid 1591 * @return bool|int true or new id 1592 * @throws dml_exception A DML specific exception is thrown for any errors. 1593 */ 1594 public abstract function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false); 1595 1596 /** 1597 * Insert a record into a table and return the "id" field if required. 1598 * 1599 * Some conversions and safety checks are carried out. Lobs are supported. 1600 * If the return ID isn't required, then this just reports success as true/false. 1601 * $data is an object containing needed data 1602 * @param string $table The database table to be inserted into 1603 * @param object $dataobject A data object with values for one or more fields in the record 1604 * @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. 1605 * @param bool $bulk Set to true is multiple inserts are expected 1606 * @return bool|int true or new id 1607 * @throws dml_exception A DML specific exception is thrown for any errors. 1608 */ 1609 public abstract function insert_record($table, $dataobject, $returnid=true, $bulk=false); 1610 1611 /** 1612 * Insert multiple records into database as fast as possible. 1613 * 1614 * Order of inserts is maintained, but the operation is not atomic, 1615 * use transactions if necessary. 1616 * 1617 * This method is intended for inserting of large number of small objects, 1618 * do not use for huge objects with text or binary fields. 1619 * 1620 * @since Moodle 2.7 1621 * 1622 * @param string $table The database table to be inserted into 1623 * @param array|Traversable $dataobjects list of objects to be inserted, must be compatible with foreach 1624 * @return void does not return new record ids 1625 * 1626 * @throws coding_exception if data objects have different structure 1627 * @throws dml_exception A DML specific exception is thrown for any errors. 1628 */ 1629 public function insert_records($table, $dataobjects) { 1630 if (!is_array($dataobjects) and !($dataobjects instanceof Traversable)) { 1631 throw new coding_exception('insert_records() passed non-traversable object'); 1632 } 1633 1634 $fields = null; 1635 // Note: override in driver if there is a faster way. 1636 foreach ($dataobjects as $dataobject) { 1637 if (!is_array($dataobject) and !is_object($dataobject)) { 1638 throw new coding_exception('insert_records() passed invalid record object'); 1639 } 1640 $dataobject = (array)$dataobject; 1641 if ($fields === null) { 1642 $fields = array_keys($dataobject); 1643 } else if ($fields !== array_keys($dataobject)) { 1644 throw new coding_exception('All dataobjects in insert_records() must have the same structure!'); 1645 } 1646 $this->insert_record($table, $dataobject, false); 1647 } 1648 } 1649 1650 /** 1651 * Import a record into a table, id field is required. 1652 * Safety checks are NOT carried out. Lobs are supported. 1653 * 1654 * @param string $table name of database table to be inserted into 1655 * @param object $dataobject A data object with values for one or more fields in the record 1656 * @return bool true 1657 * @throws dml_exception A DML specific exception is thrown for any errors. 1658 */ 1659 public abstract function import_record($table, $dataobject); 1660 1661 /** 1662 * Update record in database, as fast as possible, no safety checks, lobs not supported. 1663 * @param string $table name 1664 * @param mixed $params data record as object or array 1665 * @param bool $bulk True means repeated updates expected. 1666 * @return bool true 1667 * @throws dml_exception A DML specific exception is thrown for any errors. 1668 */ 1669 public abstract function update_record_raw($table, $params, $bulk=false); 1670 1671 /** 1672 * Update a record in a table 1673 * 1674 * $dataobject is an object containing needed data 1675 * Relies on $dataobject having a variable "id" to 1676 * specify the record to update 1677 * 1678 * @param string $table The database table to be checked against. 1679 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified. 1680 * @param bool $bulk True means repeated updates expected. 1681 * @return bool true 1682 * @throws dml_exception A DML specific exception is thrown for any errors. 1683 */ 1684 public abstract function update_record($table, $dataobject, $bulk=false); 1685 1686 /** 1687 * Set a single field in every table record where all the given conditions met. 1688 * 1689 * @param string $table The database table to be checked against. 1690 * @param string $newfield the field to set. 1691 * @param string $newvalue the value to set the field to. 1692 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1693 * @return bool true 1694 * @throws dml_exception A DML specific exception is thrown for any errors. 1695 */ 1696 public function set_field($table, $newfield, $newvalue, array $conditions=null) { 1697 list($select, $params) = $this->where_clause($table, $conditions); 1698 return $this->set_field_select($table, $newfield, $newvalue, $select, $params); 1699 } 1700 1701 /** 1702 * Set a single field in every table record which match a particular WHERE clause. 1703 * 1704 * @param string $table The database table to be checked against. 1705 * @param string $newfield the field to set. 1706 * @param string $newvalue the value to set the field to. 1707 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1708 * @param array $params array of sql parameters 1709 * @return bool true 1710 * @throws dml_exception A DML specific exception is thrown for any errors. 1711 */ 1712 public abstract function set_field_select($table, $newfield, $newvalue, $select, array $params=null); 1713 1714 1715 /** 1716 * Count the records in a table where all the given conditions met. 1717 * 1718 * @param string $table The table to query. 1719 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1720 * @return int The count of records returned from the specified criteria. 1721 * @throws dml_exception A DML specific exception is thrown for any errors. 1722 */ 1723 public function count_records($table, array $conditions=null) { 1724 list($select, $params) = $this->where_clause($table, $conditions); 1725 return $this->count_records_select($table, $select, $params); 1726 } 1727 1728 /** 1729 * Count the records in a table which match a particular WHERE clause. 1730 * 1731 * @param string $table The database table to be checked against. 1732 * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call. 1733 * @param array $params array of sql parameters 1734 * @param string $countitem The count string to be used in the SQL call. Default is COUNT('x'). 1735 * @return int The count of records returned from the specified criteria. 1736 * @throws dml_exception A DML specific exception is thrown for any errors. 1737 */ 1738 public function count_records_select($table, $select, array $params=null, $countitem="COUNT('x')") { 1739 if ($select) { 1740 $select = "WHERE $select"; 1741 } 1742 return $this->count_records_sql("SELECT $countitem FROM {" . $table . "} $select", $params); 1743 } 1744 1745 /** 1746 * Get the result of a SQL SELECT COUNT(...) query. 1747 * 1748 * Given a query that counts rows, return that count. (In fact, 1749 * given any query, return the first field of the first record 1750 * returned. However, this method should only be used for the 1751 * intended purpose.) If an error occurs, 0 is returned. 1752 * 1753 * @param string $sql The SQL string you wish to be executed. 1754 * @param array $params array of sql parameters 1755 * @return int the count 1756 * @throws dml_exception A DML specific exception is thrown for any errors. 1757 */ 1758 public function count_records_sql($sql, array $params=null) { 1759 $count = $this->get_field_sql($sql, $params); 1760 if ($count === false or !is_number($count) or $count < 0) { 1761 throw new coding_exception("count_records_sql() expects the first field to contain non-negative number from COUNT(), '$count' found instead."); 1762 } 1763 return (int)$count; 1764 } 1765 1766 /** 1767 * Test whether a record exists in a table where all the given conditions met. 1768 * 1769 * @param string $table The table to check. 1770 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1771 * @return bool true if a matching record exists, else false. 1772 * @throws dml_exception A DML specific exception is thrown for any errors. 1773 */ 1774 public function record_exists($table, array $conditions) { 1775 list($select, $params) = $this->where_clause($table, $conditions); 1776 return $this->record_exists_select($table, $select, $params); 1777 } 1778 1779 /** 1780 * Test whether any records exists in a table which match a particular WHERE clause. 1781 * 1782 * @param string $table The database table to be checked against. 1783 * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call. 1784 * @param array $params array of sql parameters 1785 * @return bool true if a matching record exists, else false. 1786 * @throws dml_exception A DML specific exception is thrown for any errors. 1787 */ 1788 public function record_exists_select($table, $select, array $params=null) { 1789 if ($select) { 1790 $select = "WHERE $select"; 1791 } 1792 return $this->record_exists_sql("SELECT 'x' FROM {" . $table . "} $select", $params); 1793 } 1794 1795 /** 1796 * Test whether a SQL SELECT statement returns any records. 1797 * 1798 * This function returns true if the SQL statement executes 1799 * without any errors and returns at least one record. 1800 * 1801 * @param string $sql The SQL statement to execute. 1802 * @param array $params array of sql parameters 1803 * @return bool true if the SQL executes without errors and returns at least one record. 1804 * @throws dml_exception A DML specific exception is thrown for any errors. 1805 */ 1806 public function record_exists_sql($sql, array $params=null) { 1807 $mrs = $this->get_recordset_sql($sql, $params, 0, 1); 1808 $return = $mrs->valid(); 1809 $mrs->close(); 1810 return $return; 1811 } 1812 1813 /** 1814 * Delete the records from a table where all the given conditions met. 1815 * If conditions not specified, table is truncated. 1816 * 1817 * @param string $table the table to delete from. 1818 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1819 * @return bool true. 1820 * @throws dml_exception A DML specific exception is thrown for any errors. 1821 */ 1822 public function delete_records($table, array $conditions=null) { 1823 // truncate is drop/create (DDL), not transactional safe, 1824 // so we don't use the shortcut within them. MDL-29198 1825 if (is_null($conditions) && empty($this->transactions)) { 1826 return $this->execute("TRUNCATE TABLE {".$table."}"); 1827 } 1828 list($select, $params) = $this->where_clause($table, $conditions); 1829 return $this->delete_records_select($table, $select, $params); 1830 } 1831 1832 /** 1833 * Delete the records from a table where one field match one list of values. 1834 * 1835 * @param string $table the table to delete from. 1836 * @param string $field The field to search 1837 * @param array $values array of values 1838 * @return bool true. 1839 * @throws dml_exception A DML specific exception is thrown for any errors. 1840 */ 1841 public function delete_records_list($table, $field, array $values) { 1842 list($select, $params) = $this->where_clause_list($field, $values); 1843 return $this->delete_records_select($table, $select, $params); 1844 } 1845 1846 /** 1847 * Delete one or more records from a table which match a particular WHERE clause. 1848 * 1849 * @param string $table The database table to be checked against. 1850 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria). 1851 * @param array $params array of sql parameters 1852 * @return bool true. 1853 * @throws dml_exception A DML specific exception is thrown for any errors. 1854 */ 1855 public abstract function delete_records_select($table, $select, array $params=null); 1856 1857 /** 1858 * Returns the FROM clause required by some DBs in all SELECT statements. 1859 * 1860 * To be used in queries not having FROM clause to provide cross_db 1861 * Most DBs don't need it, hence the default is '' 1862 * @return string 1863 */ 1864 public function sql_null_from_clause() { 1865 return ''; 1866 } 1867 1868 /** 1869 * Returns the SQL text to be used in order to perform one bitwise AND operation 1870 * between 2 integers. 1871 * 1872 * NOTE: The SQL result is a number and can not be used directly in 1873 * SQL condition, please compare it to some number to get a bool!! 1874 * 1875 * @param int $int1 First integer in the operation. 1876 * @param int $int2 Second integer in the operation. 1877 * @return string The piece of SQL code to be used in your statement. 1878 */ 1879 public function sql_bitand($int1, $int2) { 1880 return '((' . $int1 . ') & (' . $int2 . '))'; 1881 } 1882 1883 /** 1884 * Returns the SQL text to be used in order to perform one bitwise NOT operation 1885 * with 1 integer. 1886 * 1887 * @param int $int1 The operand integer in the operation. 1888 * @return string The piece of SQL code to be used in your statement. 1889 */ 1890 public function sql_bitnot($int1) { 1891 return '(~(' . $int1 . '))'; 1892 } 1893 1894 /** 1895 * Returns the SQL text to be used in order to perform one bitwise OR operation 1896 * between 2 integers. 1897 * 1898 * NOTE: The SQL result is a number and can not be used directly in 1899 * SQL condition, please compare it to some number to get a bool!! 1900 * 1901 * @param int $int1 The first operand integer in the operation. 1902 * @param int $int2 The second operand integer in the operation. 1903 * @return string The piece of SQL code to be used in your statement. 1904 */ 1905 public function sql_bitor($int1, $int2) { 1906 return '((' . $int1 . ') | (' . $int2 . '))'; 1907 } 1908 1909 /** 1910 * Returns the SQL text to be used in order to perform one bitwise XOR operation 1911 * between 2 integers. 1912 * 1913 * NOTE: The SQL result is a number and can not be used directly in 1914 * SQL condition, please compare it to some number to get a bool!! 1915 * 1916 * @param int $int1 The first operand integer in the operation. 1917 * @param int $int2 The second operand integer in the operation. 1918 * @return string The piece of SQL code to be used in your statement. 1919 */ 1920 public function sql_bitxor($int1, $int2) { 1921 return '((' . $int1 . ') ^ (' . $int2 . '))'; 1922 } 1923 1924 /** 1925 * Returns the SQL text to be used in order to perform module '%' 1926 * operation - remainder after division 1927 * 1928 * @param int $int1 The first operand integer in the operation. 1929 * @param int $int2 The second operand integer in the operation. 1930 * @return string The piece of SQL code to be used in your statement. 1931 */ 1932 public function sql_modulo($int1, $int2) { 1933 return '((' . $int1 . ') % (' . $int2 . '))'; 1934 } 1935 1936 /** 1937 * Returns the cross db correct CEIL (ceiling) expression applied to fieldname. 1938 * note: Most DBs use CEIL(), hence it's the default here. 1939 * 1940 * @param string $fieldname The field (or expression) we are going to ceil. 1941 * @return string The piece of SQL code to be used in your ceiling statement. 1942 */ 1943 public function sql_ceil($fieldname) { 1944 return ' CEIL(' . $fieldname . ')'; 1945 } 1946 1947 /** 1948 * Returns the SQL to be used in order to CAST one CHAR column to INTEGER. 1949 * 1950 * Be aware that the CHAR column you're trying to cast contains really 1951 * int values or the RDBMS will throw an error! 1952 * 1953 * @param string $fieldname The name of the field to be casted. 1954 * @param bool $text Specifies if the original column is one TEXT (CLOB) column (true). Defaults to false. 1955 * @return string The piece of SQL code to be used in your statement. 1956 */ 1957 public function sql_cast_char2int($fieldname, $text=false) { 1958 return ' ' . $fieldname . ' '; 1959 } 1960 1961 /** 1962 * Returns the SQL to be used in order to CAST one CHAR column to REAL number. 1963 * 1964 * Be aware that the CHAR column you're trying to cast contains really 1965 * numbers or the RDBMS will throw an error! 1966 * 1967 * @param string $fieldname The name of the field to be casted. 1968 * @param bool $text Specifies if the original column is one TEXT (CLOB) column (true). Defaults to false. 1969 * @return string The piece of SQL code to be used in your statement. 1970 */ 1971 public function sql_cast_char2real($fieldname, $text=false) { 1972 return ' ' . $fieldname . ' '; 1973 } 1974 1975 /** 1976 * Returns the SQL to be used in order to an UNSIGNED INTEGER column to SIGNED. 1977 * 1978 * (Only MySQL needs this. MySQL things that 1 * -1 = 18446744073709551615 1979 * if the 1 comes from an unsigned column). 1980 * 1981 * @deprecated since 2.3 1982 * @param string $fieldname The name of the field to be cast 1983 * @return string The piece of SQL code to be used in your statement. 1984 */ 1985 public function sql_cast_2signed($fieldname) { 1986 return ' ' . $fieldname . ' '; 1987 } 1988 1989 /** 1990 * Returns the SQL text to be used to compare one TEXT (clob) column with 1991 * one varchar column, because some RDBMS doesn't support such direct 1992 * comparisons. 1993 * 1994 * @param string $fieldname The name of the TEXT field we need to order by 1995 * @param int $numchars Number of chars to use for the ordering (defaults to 32). 1996 * @return string The piece of SQL code to be used in your statement. 1997 */ 1998 public function sql_compare_text($fieldname, $numchars=32) { 1999 return $this->sql_order_by_text($fieldname, $numchars); 2000 } 2001 2002 /** 2003 * Returns 'LIKE' part of a query. 2004 * 2005 * @param string $fieldname Usually the name of the table column. 2006 * @param string $param Usually the bound query parameter (?, :named). 2007 * @param bool $casesensitive Use case sensitive search when set to true (default). 2008 * @param bool $accentsensitive Use accent sensitive search when set to true (default). (not all databases support accent insensitive) 2009 * @param bool $notlike True means "NOT LIKE". 2010 * @param string $escapechar The escape char for '%' and '_'. 2011 * @return string The SQL code fragment. 2012 */ 2013 public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') { 2014 if (strpos($param, '%') !== false) { 2015 debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)'); 2016 } 2017 $LIKE = $notlike ? 'NOT LIKE' : 'LIKE'; 2018 // by default ignore any sensitiveness - each database does it in a different way 2019 return "$fieldname $LIKE $param ESCAPE '$escapechar'"; 2020 } 2021 2022 /** 2023 * Escape sql LIKE special characters like '_' or '%'. 2024 * @param string $text The string containing characters needing escaping. 2025 * @param string $escapechar The desired escape character, defaults to '\\'. 2026 * @return string The escaped sql LIKE string. 2027 */ 2028 public function sql_like_escape($text, $escapechar = '\\') { 2029 $text = str_replace('_', $escapechar.'_', $text); 2030 $text = str_replace('%', $escapechar.'%', $text); 2031 return $text; 2032 } 2033 2034 /** 2035 * Returns the proper SQL to do CONCAT between the elements(fieldnames) passed. 2036 * 2037 * This function accepts variable number of string parameters. 2038 * All strings/fieldnames will used in the SQL concatenate statement generated. 2039 * 2040 * @return string The SQL to concatenate strings passed in. 2041 * @uses func_get_args() and thus parameters are unlimited OPTIONAL number of additional field names. 2042 */ 2043 public abstract function sql_concat(); 2044 2045 /** 2046 * Returns the proper SQL to do CONCAT between the elements passed 2047 * with a given separator 2048 * 2049 * @param string $separator The separator desired for the SQL concatenating $elements. 2050 * @param array $elements The array of strings to be concatenated. 2051 * @return string The SQL to concatenate the strings. 2052 */ 2053 public abstract function sql_concat_join($separator="' '", $elements=array()); 2054 2055 /** 2056 * Returns the proper SQL (for the dbms in use) to concatenate $firstname and $lastname 2057 * 2058 * @todo MDL-31233 This may not be needed here. 2059 * 2060 * @param string $first User's first name (default:'firstname'). 2061 * @param string $last User's last name (default:'lastname'). 2062 * @return string The SQL to concatenate strings. 2063 */ 2064 function sql_fullname($first='firstname', $last='lastname') { 2065 return $this->sql_concat($first, "' '", $last); 2066 } 2067 2068 /** 2069 * Returns the SQL text to be used to order by one TEXT (clob) column, because 2070 * some RDBMS doesn't support direct ordering of such fields. 2071 * 2072 * Note that the use or queries being ordered by TEXT columns must be minimised, 2073 * because it's really slooooooow. 2074 * 2075 * @param string $fieldname The name of the TEXT field we need to order by. 2076 * @param int $numchars The number of chars to use for the ordering (defaults to 32). 2077 * @return string The piece of SQL code to be used in your statement. 2078 */ 2079 public function sql_order_by_text($fieldname, $numchars=32) { 2080 return $fieldname; 2081 } 2082 2083 /** 2084 * Returns the SQL text to be used to calculate the length in characters of one expression. 2085 * @param string $fieldname The fieldname/expression to calculate its length in characters. 2086 * @return string the piece of SQL code to be used in the statement. 2087 */ 2088 public function sql_length($fieldname) { 2089 return ' LENGTH(' . $fieldname . ')'; 2090 } 2091 2092 /** 2093 * Returns the proper substr() SQL text used to extract substrings from DB 2094 * NOTE: this was originally returning only function name 2095 * 2096 * @param string $expr Some string field, no aggregates. 2097 * @param mixed $start Integer or expression evaluating to integer (1 based value; first char has index 1) 2098 * @param mixed $length Optional integer or expression evaluating to integer. 2099 * @return string The sql substring extraction fragment. 2100 */ 2101 public function sql_substr($expr, $start, $length=false) { 2102 if (count(func_get_args()) < 2) { 2103 throw new coding_exception('moodle_database::sql_substr() requires at least two parameters', 'Originally this function was only returning name of SQL substring function, it now requires all parameters.'); 2104 } 2105 if ($length === false) { 2106 return "SUBSTR($expr, $start)"; 2107 } else { 2108 return "SUBSTR($expr, $start, $length)"; 2109 } 2110 } 2111 2112 /** 2113 * Returns the SQL for returning searching one string for the location of another. 2114 * 2115 * Note, there is no guarantee which order $needle, $haystack will be in 2116 * the resulting SQL so when using this method, and both arguments contain 2117 * placeholders, you should use named placeholders. 2118 * 2119 * @param string $needle the SQL expression that will be searched for. 2120 * @param string $haystack the SQL expression that will be searched in. 2121 * @return string The required searching SQL part. 2122 */ 2123 public function sql_position($needle, $haystack) { 2124 // Implementation using standard SQL. 2125 return "POSITION(($needle) IN ($haystack))"; 2126 } 2127 2128 /** 2129 * This used to return empty string replacement character. 2130 * 2131 * @deprecated use bound parameter with empty string instead 2132 * 2133 * @return string An empty string. 2134 */ 2135 function sql_empty() { 2136 debugging("sql_empty() is deprecated, please use empty string '' as sql parameter value instead", DEBUG_DEVELOPER); 2137 return ''; 2138 } 2139 2140 /** 2141 * Returns the proper SQL to know if one field is empty. 2142 * 2143 * Note that the function behavior strongly relies on the 2144 * parameters passed describing the field so, please, be accurate 2145 * when specifying them. 2146 * 2147 * Also, note that this function is not suitable to look for 2148 * fields having NULL contents at all. It's all for empty values! 2149 * 2150 * This function should be applied in all the places where conditions of 2151 * the type: 2152 * 2153 * ... AND fieldname = ''; 2154 * 2155 * are being used. Final result for text fields should be: 2156 * 2157 * ... AND ' . sql_isempty('tablename', 'fieldname', true/false, true); 2158 * 2159 * and for varchar fields result should be: 2160 * 2161 * ... AND fieldname = :empty; "; $params['empty'] = ''; 2162 * 2163 * (see parameters description below) 2164 * 2165 * @param string $tablename Name of the table (without prefix). Not used for now but can be 2166 * necessary in the future if we want to use some introspection using 2167 * meta information against the DB. /// TODO /// 2168 * @param string $fieldname Name of the field we are going to check 2169 * @param bool $nullablefield For specifying if the field is nullable (true) or no (false) in the DB. 2170 * @param bool $textfield For specifying if it is a text (also called clob) field (true) or a varchar one (false) 2171 * @return string the sql code to be added to check for empty values 2172 */ 2173 public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) { 2174 return " ($fieldname = '') "; 2175 } 2176 2177 /** 2178 * Returns the proper SQL to know if one field is not empty. 2179 * 2180 * Note that the function behavior strongly relies on the 2181 * parameters passed describing the field so, please, be accurate 2182 * when specifying them. 2183 * 2184 * This function should be applied in all the places where conditions of 2185 * the type: 2186 * 2187 * ... AND fieldname != ''; 2188 * 2189 * are being used. Final result for text fields should be: 2190 * 2191 * ... AND ' . sql_isnotempty('tablename', 'fieldname', true/false, true/false); 2192 * 2193 * and for varchar fields result should be: 2194 * 2195 * ... AND fieldname != :empty; "; $params['empty'] = ''; 2196 * 2197 * (see parameters description below) 2198 * 2199 * @param string $tablename Name of the table (without prefix). This is not used for now but can be 2200 * necessary in the future if we want to use some introspection using 2201 * meta information against the DB. 2202 * @param string $fieldname The name of the field we are going to check. 2203 * @param bool $nullablefield Specifies if the field is nullable (true) or not (false) in the DB. 2204 * @param bool $textfield Specifies if it is a text (also called clob) field (true) or a varchar one (false). 2205 * @return string The sql code to be added to check for non empty values. 2206 */ 2207 public function sql_isnotempty($tablename, $fieldname, $nullablefield, $textfield) { 2208 return ' ( NOT ' . $this->sql_isempty($tablename, $fieldname, $nullablefield, $textfield) . ') '; 2209 } 2210 2211 /** 2212 * Returns true if this database driver supports regex syntax when searching. 2213 * @return bool True if supported. 2214 */ 2215 public function sql_regex_supported() { 2216 return false; 2217 } 2218 2219 /** 2220 * Returns the driver specific syntax (SQL part) for matching regex positively or negatively (inverted matching). 2221 * Eg: 'REGEXP':'NOT REGEXP' or '~*' : '!~*' 2222 * @param bool $positivematch 2223 * @return string or empty if not supported 2224 */ 2225 public function sql_regex($positivematch=true) { 2226 return ''; 2227 } 2228 2229 /** 2230 * Returns the SQL that allows to find intersection of two or more queries 2231 * 2232 * @since Moodle 2.8 2233 * 2234 * @param array $selects array of SQL select queries, each of them only returns fields with the names from $fields 2235 * @param string $fields comma-separated list of fields (used only by some DB engines) 2236 * @return string SQL query that will return only values that are present in each of selects 2237 */ 2238 public function sql_intersect($selects, $fields) { 2239 if (!count($selects)) { 2240 throw new coding_exception('sql_intersect() requires at least one element in $selects'); 2241 } else if (count($selects) == 1) { 2242 return $selects[0]; 2243 } 2244 static $aliascnt = 0; 2245 $rv = '('.$selects[0].')'; 2246 for ($i = 1; $i < count($selects); $i++) { 2247 $rv .= " INTERSECT (".$selects[$i].')'; 2248 } 2249 return $rv; 2250 } 2251 2252 /** 2253 * Does this driver support tool_replace? 2254 * 2255 * @since Moodle 2.6.1 2256 * @return bool 2257 */ 2258 public function replace_all_text_supported() { 2259 return false; 2260 } 2261 2262 /** 2263 * Replace given text in all rows of column. 2264 * 2265 * @since Moodle 2.6.1 2266 * @param string $table name of the table 2267 * @param database_column_info $column 2268 * @param string $search 2269 * @param string $replace 2270 */ 2271 public function replace_all_text($table, database_column_info $column, $search, $replace) { 2272 if (!$this->replace_all_text_supported()) { 2273 return; 2274 } 2275 2276 // NOTE: override this methods if following standard compliant SQL 2277 // does not work for your driver. 2278 2279 $columnname = $column->name; 2280 $sql = "UPDATE {".$table."} 2281 SET $columnname = REPLACE($columnname, ?, ?) 2282 WHERE $columnname IS NOT NULL"; 2283 2284 if ($column->meta_type === 'X') { 2285 $this->execute($sql, array($search, $replace)); 2286 2287 } else if ($column->meta_type === 'C') { 2288 if (core_text::strlen($search) < core_text::strlen($replace)) { 2289 $colsize = $column->max_length; 2290 $sql = "UPDATE {".$table."} 2291 SET $columnname = " . $this->sql_substr("REPLACE(" . $columnname . ", ?, ?)", 1, $colsize) . " 2292 WHERE $columnname IS NOT NULL"; 2293 } 2294 $this->execute($sql, array($search, $replace)); 2295 } 2296 } 2297 2298 /** 2299 * Analyze the data in temporary tables to force statistics collection after bulk data loads. 2300 * 2301 * @return void 2302 */ 2303 public function update_temp_table_stats() { 2304 $this->temptables->update_stats(); 2305 } 2306 2307 /** 2308 * Checks and returns true if transactions are supported. 2309 * 2310 * It is not responsible to run productions servers 2311 * on databases without transaction support ;-) 2312 * 2313 * Override in driver if needed. 2314 * 2315 * @return bool 2316 */ 2317 protected function transactions_supported() { 2318 // protected for now, this might be changed to public if really necessary 2319 return true; 2320 } 2321 2322 /** 2323 * Returns true if a transaction is in progress. 2324 * @return bool 2325 */ 2326 public function is_transaction_started() { 2327 return !empty($this->transactions); 2328 } 2329 2330 /** 2331 * This is a test that throws an exception if transaction in progress. 2332 * This test does not force rollback of active transactions. 2333 * @return void 2334 * @throws dml_transaction_exception if stansaction active 2335 */ 2336 public function transactions_forbidden() { 2337 if ($this->is_transaction_started()) { 2338 throw new dml_transaction_exception('This code can not be excecuted in transaction'); 2339 } 2340 } 2341 2342 /** 2343 * On DBs that support it, switch to transaction mode and begin a transaction 2344 * you'll need to ensure you call allow_commit() on the returned object 2345 * or your changes *will* be lost. 2346 * 2347 * this is _very_ useful for massive updates 2348 * 2349 * Delegated database transactions can be nested, but only one actual database 2350 * transaction is used for the outer-most delegated transaction. This method 2351 * returns a transaction object which you should keep until the end of the 2352 * delegated transaction. The actual database transaction will 2353 * only be committed if all the nested delegated transactions commit 2354 * successfully. If any part of the transaction rolls back then the whole 2355 * thing is rolled back. 2356 * 2357 * @return moodle_transaction 2358 */ 2359 public function start_delegated_transaction() { 2360 $transaction = new moodle_transaction($this); 2361 $this->transactions[] = $transaction; 2362 if (count($this->transactions) == 1) { 2363 $this->begin_transaction(); 2364 } 2365 return $transaction; 2366 } 2367 2368 /** 2369 * Driver specific start of real database transaction, 2370 * this can not be used directly in code. 2371 * @return void 2372 */ 2373 protected abstract function begin_transaction(); 2374 2375 /** 2376 * Indicates delegated transaction finished successfully. 2377 * The real database transaction is committed only if 2378 * all delegated transactions committed. 2379 * @param moodle_transaction $transaction The transaction to commit 2380 * @return void 2381 * @throws dml_transaction_exception Creates and throws transaction related exceptions. 2382 */ 2383 public function commit_delegated_transaction(moodle_transaction $transaction) { 2384 if ($transaction->is_disposed()) { 2385 throw new dml_transaction_exception('Transactions already disposed', $transaction); 2386 } 2387 // mark as disposed so that it can not be used again 2388 $transaction->dispose(); 2389 2390 if (empty($this->transactions)) { 2391 throw new dml_transaction_exception('Transaction not started', $transaction); 2392 } 2393 2394 if ($this->force_rollback) { 2395 throw new dml_transaction_exception('Tried to commit transaction after lower level rollback', $transaction); 2396 } 2397 2398 if ($transaction !== $this->transactions[count($this->transactions) - 1]) { 2399 // one incorrect commit at any level rollbacks everything 2400 $this->force_rollback = true; 2401 throw new dml_transaction_exception('Invalid transaction commit attempt', $transaction); 2402 } 2403 2404 if (count($this->transactions) == 1) { 2405 // only commit the top most level 2406 $this->commit_transaction(); 2407 } 2408 array_pop($this->transactions); 2409 2410 if (empty($this->transactions)) { 2411 \core\event\manager::database_transaction_commited(); 2412 \core\message\manager::database_transaction_commited(); 2413 } 2414 } 2415 2416 /** 2417 * Driver specific commit of real database transaction, 2418 * this can not be used directly in code. 2419 * @return void 2420 */ 2421 protected abstract function commit_transaction(); 2422 2423 /** 2424 * Call when delegated transaction failed, this rolls back 2425 * all delegated transactions up to the top most level. 2426 * 2427 * In many cases you do not need to call this method manually, 2428 * because all open delegated transactions are rolled back 2429 * automatically if exceptions not caught. 2430 * 2431 * @param moodle_transaction $transaction An instance of a moodle_transaction. 2432 * @param Exception $e The related exception to this transaction rollback. 2433 * @return void This does not return, instead the exception passed in will be rethrown. 2434 */ 2435 public function rollback_delegated_transaction(moodle_transaction $transaction, Exception $e) { 2436 if ($transaction->is_disposed()) { 2437 throw new dml_transaction_exception('Transactions already disposed', $transaction); 2438 } 2439 // mark as disposed so that it can not be used again 2440 $transaction->dispose(); 2441 2442 // one rollback at any level rollbacks everything 2443 $this->force_rollback = true; 2444 2445 if (empty($this->transactions) or $transaction !== $this->transactions[count($this->transactions) - 1]) { 2446 // this may or may not be a coding problem, better just rethrow the exception, 2447 // because we do not want to loose the original $e 2448 throw $e; 2449 } 2450 2451 if (count($this->transactions) == 1) { 2452 // only rollback the top most level 2453 $this->rollback_transaction(); 2454 } 2455 array_pop($this->transactions); 2456 if (empty($this->transactions)) { 2457 // finally top most level rolled back 2458 $this->force_rollback = false; 2459 \core\event\manager::database_transaction_rolledback(); 2460 \core\message\manager::database_transaction_rolledback(); 2461 } 2462 throw $e; 2463 } 2464 2465 /** 2466 * Driver specific abort of real database transaction, 2467 * this can not be used directly in code. 2468 * @return void 2469 */ 2470 protected abstract function rollback_transaction(); 2471 2472 /** 2473 * Force rollback of all delegated transaction. 2474 * Does not throw any exceptions and does not log anything. 2475 * 2476 * This method should be used only from default exception handlers and other 2477 * core code. 2478 * 2479 * @return void 2480 */ 2481 public function force_transaction_rollback() { 2482 if ($this->transactions) { 2483 try { 2484 $this->rollback_transaction(); 2485 } catch (dml_exception $e) { 2486 // ignore any sql errors here, the connection might be broken 2487 } 2488 } 2489 2490 // now enable transactions again 2491 $this->transactions = array(); 2492 $this->force_rollback = false; 2493 2494 \core\event\manager::database_transaction_rolledback(); 2495 \core\message\manager::database_transaction_rolledback(); 2496 } 2497 2498 /** 2499 * Is session lock supported in this driver? 2500 * @return bool 2501 */ 2502 public function session_lock_supported() { 2503 return false; 2504 } 2505 2506 /** 2507 * Obtains the session lock. 2508 * @param int $rowid The id of the row with session record. 2509 * @param int $timeout The maximum allowed time to wait for the lock in seconds. 2510 * @return void 2511 * @throws dml_exception A DML specific exception is thrown for any errors. 2512 */ 2513 public function get_session_lock($rowid, $timeout) { 2514 $this->used_for_db_sessions = true; 2515 } 2516 2517 /** 2518 * Releases the session lock. 2519 * @param int $rowid The id of the row with session record. 2520 * @return void 2521 * @throws dml_exception A DML specific exception is thrown for any errors. 2522 */ 2523 public function release_session_lock($rowid) { 2524 } 2525 2526 /** 2527 * Returns the number of reads done by this database. 2528 * @return int Number of reads. 2529 */ 2530 public function perf_get_reads() { 2531 return $this->reads; 2532 } 2533 2534 /** 2535 * Returns the number of writes done by this database. 2536 * @return int Number of writes. 2537 */ 2538 public function perf_get_writes() { 2539 return $this->writes; 2540 } 2541 2542 /** 2543 * Returns the number of queries done by this database. 2544 * @return int Number of queries. 2545 */ 2546 public function perf_get_queries() { 2547 return $this->writes + $this->reads; 2548 } 2549 2550 /** 2551 * Time waiting for the database engine to finish running all queries. 2552 * @return float Number of seconds with microseconds 2553 */ 2554 public function perf_get_queries_time() { 2555 return $this->queriestime; 2556 } 2557 }
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 |