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