Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 4.2.x will end 22 April 2024 (12 months).
- Bug fixes for security issues in 4.2.x will end 7 October 2024 (18 months).
- PHP version: minimum PHP 8.0.0 Note: minimum PHP version has increased since Moodle 4.1. PHP 8.1.x is supported too.
/lib/dml/ -> moodle_database.php (source)
Differences Between: [Versions 310 and 402] [Versions 311 and 402] [Versions 39 and 402] [Versions 400 and 402] [Versions 401 and 402] [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 $params = (array)$params; // mke null array if needed 895 $allowed_types = $this->allowed_param_types(); 896 897 // convert table names 898 $sql = $this->fix_table_names($sql); 899 900 // cast booleans to 1/0 int and detect forbidden objects 901 foreach ($params as $key => $value) { 902 $this->detect_objects($value); 903 $params[$key] = is_bool($value) ? (int)$value : $value; 904 } 905 906 // NICOLAS C: Fixed regexp for negative backwards look-ahead of double colons. Thanks for Sam Marshall's help 907 $named_count = preg_match_all('/(?<!:):[a-z][a-z0-9_]*/', $sql, $named_matches); // :: used in pgsql casts 908 $dollar_count = preg_match_all('/\$[1-9][0-9]*/', $sql, $dollar_matches); 909 $q_count = substr_count($sql, '?'); 910 911 // Optionally add debug trace to sql as a comment. 912 $sql = $this->add_sql_debugging($sql); 913 914 $count = 0; 915 916 if ($named_count) { 917 $type = SQL_PARAMS_NAMED; 918 $count = $named_count; 919 920 } 921 if ($dollar_count) { 922 if ($count) { 923 throw new dml_exception('mixedtypesqlparam'); 924 } 925 $type = SQL_PARAMS_DOLLAR; 926 $count = $dollar_count; 927 928 } 929 if ($q_count) { 930 if ($count) { 931 throw new dml_exception('mixedtypesqlparam'); 932 } 933 $type = SQL_PARAMS_QM; 934 $count = $q_count; 935 936 } 937 938 if (!$count) { 939 // ignore params 940 if ($allowed_types & SQL_PARAMS_NAMED) { 941 return array($sql, array(), SQL_PARAMS_NAMED); 942 } else if ($allowed_types & SQL_PARAMS_QM) { 943 return array($sql, array(), SQL_PARAMS_QM); 944 } else { 945 return array($sql, array(), SQL_PARAMS_DOLLAR); 946 } 947 } 948 949 if ($count > count($params)) { 950 $a = new stdClass; 951 $a->expected = $count; 952 $a->actual = count($params); 953 throw new dml_exception('invalidqueryparam', $a); 954 } 955 956 $target_type = $allowed_types; 957 958 if ($type & $allowed_types) { // bitwise AND 959 if ($count == count($params)) { 960 if ($type == SQL_PARAMS_QM) { 961 return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based array required 962 } else { 963 //better do the validation of names below 964 } 965 } 966 // needs some fixing or validation - there might be more params than needed 967 $target_type = $type; 968 } 969 970 if ($type == SQL_PARAMS_NAMED) { 971 $finalparams = array(); 972 foreach ($named_matches[0] as $key) { 973 $key = trim($key, ':'); 974 if (!array_key_exists($key, $params)) { 975 throw new dml_exception('missingkeyinsql', $key, ''); 976 } 977 if (strlen($key) > 30) { 978 throw new coding_exception( 979 "Placeholder names must be 30 characters or shorter. '" . 980 $key . "' is too long.", $sql); 981 } 982 $finalparams[$key] = $params[$key]; 983 } 984 if ($count != count($finalparams)) { 985 throw new dml_exception('duplicateparaminsql'); 986 } 987 988 if ($target_type & SQL_PARAMS_QM) { 989 $sql = preg_replace('/(?<!:):[a-z][a-z0-9_]*/', '?', $sql); 990 return array($sql, array_values($finalparams), SQL_PARAMS_QM); // 0-based required 991 } else if ($target_type & SQL_PARAMS_NAMED) { 992 return array($sql, $finalparams, SQL_PARAMS_NAMED); 993 } else { // $type & SQL_PARAMS_DOLLAR 994 //lambda-style functions eat memory - we use globals instead :-( 995 $this->fix_sql_params_i = 0; 996 $sql = preg_replace_callback('/(?<!:):[a-z][a-z0-9_]*/', array($this, '_fix_sql_params_dollar_callback'), $sql); 997 return array($sql, array_values($finalparams), SQL_PARAMS_DOLLAR); // 0-based required 998 } 999 1000 } else if ($type == SQL_PARAMS_DOLLAR) { 1001 if ($target_type & SQL_PARAMS_DOLLAR) { 1002 return array($sql, array_values($params), SQL_PARAMS_DOLLAR); // 0-based required 1003 } else if ($target_type & SQL_PARAMS_QM) { 1004 $sql = preg_replace('/\$[0-9]+/', '?', $sql); 1005 return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based required 1006 } else { //$target_type & SQL_PARAMS_NAMED 1007 $sql = preg_replace('/\$([0-9]+)/', ':param\\1', $sql); 1008 $finalparams = array(); 1009 foreach ($params as $key=>$param) { 1010 $key++; 1011 $finalparams['param'.$key] = $param; 1012 } 1013 return array($sql, $finalparams, SQL_PARAMS_NAMED); 1014 } 1015 1016 } else { // $type == SQL_PARAMS_QM 1017 if (count($params) != $count) { 1018 $params = array_slice($params, 0, $count); 1019 } 1020 1021 if ($target_type & SQL_PARAMS_QM) { 1022 return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based required 1023 } else if ($target_type & SQL_PARAMS_NAMED) { 1024 $finalparams = array(); 1025 $pname = 'param0'; 1026 $parts = explode('?', $sql); 1027 $sql = array_shift($parts); 1028 foreach ($parts as $part) { 1029 $param = array_shift($params); 1030 $pname++; 1031 $sql .= ':'.$pname.$part; 1032 $finalparams[$pname] = $param; 1033 } 1034 return array($sql, $finalparams, SQL_PARAMS_NAMED); 1035 } else { // $type & SQL_PARAMS_DOLLAR 1036 //lambda-style functions eat memory - we use globals instead :-( 1037 $this->fix_sql_params_i = 0; 1038 $sql = preg_replace_callback('/\?/', array($this, '_fix_sql_params_dollar_callback'), $sql); 1039 return array($sql, array_values($params), SQL_PARAMS_DOLLAR); // 0-based required 1040 } 1041 } 1042 } 1043 1044 /** 1045 * Add an SQL comment to trace all sql calls back to the calling php code 1046 * @param string $sql Original sql 1047 * @return string Instrumented sql 1048 */ 1049 protected function add_sql_debugging(string $sql): string { 1050 global $CFG; 1051 1052 if (!property_exists($CFG, 'debugsqltrace')) { 1053 return $sql; 1054 } 1055 1056 $level = $CFG->debugsqltrace; 1057 1058 if (empty($level)) { 1059 return $sql; 1060 } 1061 1062 $callers = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS); 1063 1064 // Ignore moodle_database internals. 1065 $callers = array_filter($callers, function($caller) { 1066 return empty($caller['class']) || $caller['class'] != 'moodle_database'; 1067 }); 1068 1069 $callers = array_slice($callers, 0, $level); 1070 1071 $text = trim(format_backtrace($callers, true)); 1072 1073 // Convert all linebreaks to SQL comments, optionally 1074 // also eating any * formatting. 1075 $text = preg_replace("/(^|\n)\*?\s*/", "\n-- ", $text); 1076 1077 // Convert all ? to 'unknown' in the sql coment so these don't get 1078 // caught by fix_sql_params(). 1079 $text = str_replace('?', 'unknown', $text); 1080 1081 // Convert tokens like :test to ::test for the same reason. 1082 $text = preg_replace('/(?<!:):[a-z][a-z0-9_]*/', ':\0', $text); 1083 1084 return $sql . $text; 1085 } 1086 1087 1088 /** 1089 * Ensures that limit params are numeric and positive integers, to be passed to the database. 1090 * We explicitly treat null, '' and -1 as 0 in order to provide compatibility with how limit 1091 * values have been passed historically. 1092 * 1093 * @param int $limitfrom Where to start results from 1094 * @param int $limitnum How many results to return 1095 * @return array Normalised limit params in array($limitfrom, $limitnum) 1096 */ 1097 protected function normalise_limit_from_num($limitfrom, $limitnum) { 1098 global $CFG; 1099 1100 // We explicilty treat these cases as 0. 1101 if ($limitfrom === null || $limitfrom === '' || $limitfrom === -1) { 1102 $limitfrom = 0; 1103 } 1104 if ($limitnum === null || $limitnum === '' || $limitnum === -1) { 1105 $limitnum = 0; 1106 } 1107 1108 if ($CFG->debugdeveloper) { 1109 if (!is_numeric($limitfrom)) { 1110 $strvalue = var_export($limitfrom, true); 1111 debugging("Non-numeric limitfrom parameter detected: $strvalue, did you pass the correct arguments?", 1112 DEBUG_DEVELOPER); 1113 } else if ($limitfrom < 0) { 1114 debugging("Negative limitfrom parameter detected: $limitfrom, did you pass the correct arguments?", 1115 DEBUG_DEVELOPER); 1116 } 1117 1118 if (!is_numeric($limitnum)) { 1119 $strvalue = var_export($limitnum, true); 1120 debugging("Non-numeric limitnum parameter detected: $strvalue, did you pass the correct arguments?", 1121 DEBUG_DEVELOPER); 1122 } else if ($limitnum < 0) { 1123 debugging("Negative limitnum parameter detected: $limitnum, did you pass the correct arguments?", 1124 DEBUG_DEVELOPER); 1125 } 1126 } 1127 1128 $limitfrom = (int)$limitfrom; 1129 $limitnum = (int)$limitnum; 1130 $limitfrom = max(0, $limitfrom); 1131 $limitnum = max(0, $limitnum); 1132 1133 return array($limitfrom, $limitnum); 1134 } 1135 1136 /** 1137 * Return tables in database WITHOUT current prefix. 1138 * @param bool $usecache if true, returns list of cached tables. 1139 * @return array of table names in lowercase and without prefix 1140 */ 1141 public abstract function get_tables($usecache=true); 1142 1143 /** 1144 * Return table indexes - everything lowercased. 1145 * @param string $table The table we want to get indexes from. 1146 * @return array An associative array of indexes containing 'unique' flag and 'columns' being indexed 1147 */ 1148 public abstract function get_indexes($table); 1149 1150 /** 1151 * Returns detailed information about columns in table. This information is cached internally. 1152 * 1153 * @param string $table The table's name. 1154 * @param bool $usecache Flag to use internal cacheing. The default is true. 1155 * @return database_column_info[] of database_column_info objects indexed with column names 1156 */ 1157 public function get_columns($table, $usecache = true): array { 1158 if (!$table) { // Table not specified, return empty array directly. 1159 return []; 1160 } 1161 1162 if ($usecache) { 1163 if ($this->temptables->is_temptable($table)) { 1164 if ($data = $this->get_temp_tables_cache()->get($table)) { 1165 return $data; 1166 } 1167 } else { 1168 if ($data = $this->get_metacache()->get($table)) { 1169 return $data; 1170 } 1171 } 1172 } 1173 1174 $structure = $this->fetch_columns($table); 1175 1176 if ($usecache) { 1177 if ($this->temptables->is_temptable($table)) { 1178 $this->get_temp_tables_cache()->set($table, $structure); 1179 } else { 1180 $this->get_metacache()->set($table, $structure); 1181 } 1182 } 1183 1184 return $structure; 1185 } 1186 1187 /** 1188 * Returns detailed information about columns in table. This information is cached internally. 1189 * 1190 * @param string $table The table's name. 1191 * @return database_column_info[] of database_column_info objects indexed with column names 1192 */ 1193 protected abstract function fetch_columns(string $table): array; 1194 1195 /** 1196 * Normalise values based on varying RDBMS's dependencies (booleans, LOBs...) 1197 * 1198 * @param database_column_info $column column metadata corresponding with the value we are going to normalise 1199 * @param mixed $value value we are going to normalise 1200 * @return mixed the normalised value 1201 */ 1202 protected abstract function normalise_value($column, $value); 1203 1204 /** 1205 * Resets the internal column details cache 1206 * 1207 * @param array|null $tablenames an array of xmldb table names affected by this request. 1208 * @return void 1209 */ 1210 public function reset_caches($tablenames = null) { 1211 if (!empty($tablenames)) { 1212 $dbmetapurged = false; 1213 foreach ($tablenames as $tablename) { 1214 if ($this->temptables->is_temptable($tablename)) { 1215 $this->get_temp_tables_cache()->delete($tablename); 1216 } else if ($dbmetapurged === false) { 1217 $this->tables = null; 1218 $this->get_metacache()->purge(); 1219 $this->metacache = null; 1220 $dbmetapurged = true; 1221 } 1222 } 1223 } else { 1224 $this->get_temp_tables_cache()->purge(); 1225 $this->tables = null; 1226 // Purge MUC as well. 1227 $this->get_metacache()->purge(); 1228 $this->metacache = null; 1229 } 1230 } 1231 1232 /** 1233 * Returns the sql generator used for db manipulation. 1234 * Used mostly in upgrade.php scripts. 1235 * @return database_manager The instance used to perform ddl operations. 1236 * @see lib/ddl/database_manager.php 1237 */ 1238 public function get_manager() { 1239 global $CFG; 1240 1241 if (!$this->database_manager) { 1242 require_once($CFG->libdir.'/ddllib.php'); 1243 1244 $classname = $this->get_dbfamily().'_sql_generator'; 1245 require_once("$CFG->libdir/ddl/$classname.php"); 1246 $generator = new $classname($this, $this->temptables); 1247 1248 $this->database_manager = new database_manager($this, $generator); 1249 } 1250 return $this->database_manager; 1251 } 1252 1253 /** 1254 * Attempts to change db encoding to UTF-8 encoding if possible. 1255 * @return bool True is successful. 1256 */ 1257 public function change_db_encoding() { 1258 return false; 1259 } 1260 1261 /** 1262 * Checks to see if the database is in unicode mode? 1263 * @return bool 1264 */ 1265 public function setup_is_unicodedb() { 1266 return true; 1267 } 1268 1269 /** 1270 * Enable/disable very detailed debugging. 1271 * @param bool $state 1272 * @return void 1273 */ 1274 public function set_debug($state) { 1275 $this->debug = $state; 1276 } 1277 1278 /** 1279 * Returns debug status 1280 * @return bool $state 1281 */ 1282 public function get_debug() { 1283 return $this->debug; 1284 } 1285 1286 /** 1287 * Enable/disable detailed sql logging 1288 * 1289 * @deprecated since Moodle 2.9 1290 */ 1291 public function set_logging($state) { 1292 throw new coding_exception('set_logging() can not be used any more.'); 1293 } 1294 1295 /** 1296 * Do NOT use in code, this is for use by database_manager only! 1297 * @param string|array $sql query or array of queries 1298 * @param array|null $tablenames an array of xmldb table names affected by this request. 1299 * @return bool true 1300 * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors. 1301 */ 1302 public abstract function change_database_structure($sql, $tablenames = null); 1303 1304 /** 1305 * Executes a general sql query. Should be used only when no other method suitable. 1306 * Do NOT use this to make changes in db structure, use database_manager methods instead! 1307 * @param string $sql query 1308 * @param array $params query parameters 1309 * @return bool true 1310 * @throws dml_exception A DML specific exception is thrown for any errors. 1311 */ 1312 public abstract function execute($sql, array $params=null); 1313 1314 /** 1315 * Get a number of records as a moodle_recordset where all the given conditions met. 1316 * 1317 * Selects records from the table $table. 1318 * 1319 * If specified, only records meeting $conditions. 1320 * 1321 * If specified, the results will be sorted as specified by $sort. This 1322 * is added to the SQL as "ORDER BY $sort". Example values of $sort 1323 * might be "time ASC" or "time DESC". 1324 * 1325 * If $fields is specified, only those fields are returned. 1326 * 1327 * Since this method is a little less readable, use of it should be restricted to 1328 * code where it's possible there might be large datasets being returned. For known 1329 * small datasets use get_records - it leads to simpler code. 1330 * 1331 * If you only want some of the records, specify $limitfrom and $limitnum. 1332 * The query will skip the first $limitfrom records (according to the sort 1333 * order) and then return the next $limitnum records. If either of $limitfrom 1334 * or $limitnum is specified, both must be present. 1335 * 1336 * The return value is a moodle_recordset 1337 * if the query succeeds. If an error occurs, false is returned. 1338 * 1339 * @param string $table the table to query. 1340 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1341 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). 1342 * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned). 1343 * @param int $limitfrom return a subset of records, starting at this point (optional). 1344 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1345 * @return moodle_recordset A moodle_recordset instance 1346 * @throws dml_exception A DML specific exception is thrown for any errors. 1347 */ 1348 public function get_recordset($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1349 list($select, $params) = $this->where_clause($table, $conditions); 1350 return $this->get_recordset_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum); 1351 } 1352 1353 /** 1354 * Get a number of records as a moodle_recordset where one field match one list of values. 1355 * 1356 * Only records where $field takes one of the values $values are returned. 1357 * $values must be an array of values. 1358 * 1359 * Other arguments and the return type are like {@link function get_recordset}. 1360 * 1361 * @param string $table the table to query. 1362 * @param string $field a field to check (optional). 1363 * @param array $values array of values the field must have 1364 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). 1365 * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned). 1366 * @param int $limitfrom return a subset of records, starting at this point (optional). 1367 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1368 * @return moodle_recordset A moodle_recordset instance. 1369 * @throws dml_exception A DML specific exception is thrown for any errors. 1370 */ 1371 public function get_recordset_list($table, $field, array $values, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1372 list($select, $params) = $this->where_clause_list($field, $values); 1373 return $this->get_recordset_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum); 1374 } 1375 1376 /** 1377 * Get a number of records as a moodle_recordset which match a particular WHERE clause. 1378 * 1379 * If given, $select is used as the SELECT parameter in the SQL query, 1380 * otherwise all records from the table are returned. 1381 * 1382 * Other arguments and the return type are like {@link function get_recordset}. 1383 * 1384 * @param string $table the table to query. 1385 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1386 * @param array $params array of sql parameters 1387 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). 1388 * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned). 1389 * @param int $limitfrom return a subset of records, starting at this point (optional). 1390 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1391 * @return moodle_recordset A moodle_recordset instance. 1392 * @throws dml_exception A DML specific exception is thrown for any errors. 1393 */ 1394 public function get_recordset_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1395 $sql = "SELECT $fields FROM {".$table."}"; 1396 if ($select) { 1397 $sql .= " WHERE $select"; 1398 } 1399 if ($sort) { 1400 $sql .= " ORDER BY $sort"; 1401 } 1402 return $this->get_recordset_sql($sql, $params, $limitfrom, $limitnum); 1403 } 1404 1405 /** 1406 * Get a number of records as a moodle_recordset using a SQL statement. 1407 * 1408 * Since this method is a little less readable, use of it should be restricted to 1409 * code where it's possible there might be large datasets being returned. For known 1410 * small datasets use get_records_sql - it leads to simpler code. 1411 * 1412 * The return type is like {@link function get_recordset}. 1413 * 1414 * @param string $sql the SQL select query to execute. 1415 * @param array $params array of sql parameters 1416 * @param int $limitfrom return a subset of records, starting at this point (optional). 1417 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1418 * @return moodle_recordset A moodle_recordset instance. 1419 * @throws dml_exception A DML specific exception is thrown for any errors. 1420 */ 1421 public abstract function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0); 1422 1423 /** 1424 * Get all records from a table. 1425 * 1426 * This method works around potential memory problems and may improve performance, 1427 * this method may block access to table until the recordset is closed. 1428 * 1429 * @param string $table Name of database table. 1430 * @return moodle_recordset A moodle_recordset instance {@link function get_recordset}. 1431 * @throws dml_exception A DML specific exception is thrown for any errors. 1432 */ 1433 public function export_table_recordset($table) { 1434 return $this->get_recordset($table, array()); 1435 } 1436 1437 /** 1438 * Get a number of records as an array of objects where all the given conditions met. 1439 * 1440 * If the query succeeds and returns at least one record, the 1441 * return value is an array of objects, one object for each 1442 * record found. The array key is the value from the first 1443 * column of the result set. The object associated with that key 1444 * has a member variable for each column of the results. 1445 * 1446 * @param string $table the table to query. 1447 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1448 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). 1449 * @param string $fields a comma separated list of fields to return (optional, by default 1450 * all fields are returned). The first field will be used as key for the 1451 * array so must be a unique field such as 'id'. 1452 * @param int $limitfrom return a subset of records, starting at this point (optional). 1453 * @param int $limitnum return a subset comprising this many records in total (optional, required if $limitfrom is set). 1454 * @return array An array of Objects indexed by first column. 1455 * @throws dml_exception A DML specific exception is thrown for any errors. 1456 */ 1457 public function get_records($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1458 list($select, $params) = $this->where_clause($table, $conditions); 1459 return $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum); 1460 } 1461 1462 /** 1463 * Get a number of records as an array of objects where one field match one list of values. 1464 * 1465 * Return value is like {@link function get_records}. 1466 * 1467 * @param string $table The database table to be checked against. 1468 * @param string $field The field to search 1469 * @param array $values An array of values 1470 * @param string $sort Sort order (as valid SQL sort parameter) 1471 * @param string $fields A comma separated list of fields to be returned from the chosen table. If specified, 1472 * the first field should be a unique one such as 'id' since it will be used as a key in the associative 1473 * array. 1474 * @param int $limitfrom return a subset of records, starting at this point (optional). 1475 * @param int $limitnum return a subset comprising this many records in total (optional). 1476 * @return array An array of objects indexed by first column 1477 * @throws dml_exception A DML specific exception is thrown for any errors. 1478 */ 1479 public function get_records_list($table, $field, array $values, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1480 list($select, $params) = $this->where_clause_list($field, $values); 1481 return $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum); 1482 } 1483 1484 /** 1485 * Get a number of records as an array of objects which match a particular WHERE clause. 1486 * 1487 * Return value is like {@link function get_records}. 1488 * 1489 * @param string $table The table to query. 1490 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1491 * @param array $params An array of sql parameters 1492 * @param string $sort An order to sort the results in (optional, a valid SQL ORDER BY parameter). 1493 * @param string $fields A comma separated list of fields to return 1494 * (optional, by default all fields are returned). The first field will be used as key for the 1495 * array so must be a unique field such as 'id'. 1496 * @param int $limitfrom return a subset of records, starting at this point (optional). 1497 * @param int $limitnum return a subset comprising this many records in total (optional, required if $limitfrom is set). 1498 * @return array of objects indexed by first column 1499 * @throws dml_exception A DML specific exception is thrown for any errors. 1500 */ 1501 public function get_records_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1502 if ($select) { 1503 $select = "WHERE $select"; 1504 } 1505 if ($sort) { 1506 $sort = " ORDER BY $sort"; 1507 } 1508 return $this->get_records_sql("SELECT $fields FROM {" . $table . "} $select $sort", $params, $limitfrom, $limitnum); 1509 } 1510 1511 /** 1512 * Get a number of records as an array of objects using a SQL statement. 1513 * 1514 * Return value is like {@link function get_records}. 1515 * 1516 * @param string $sql the SQL select query to execute. The first column of this SELECT statement 1517 * must be a unique value (usually the 'id' field), as it will be used as the key of the 1518 * returned array. 1519 * @param array $params array of sql parameters 1520 * @param int $limitfrom return a subset of records, starting at this point (optional). 1521 * @param int $limitnum return a subset comprising this many records in total (optional, required if $limitfrom is set). 1522 * @return array of objects indexed by first column 1523 * @throws dml_exception A DML specific exception is thrown for any errors. 1524 */ 1525 public abstract function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0); 1526 1527 /** 1528 * Get the first two columns from a number of records as an associative array where all the given conditions met. 1529 * 1530 * Arguments are like {@link function get_recordset}. 1531 * 1532 * If no errors occur the return value 1533 * is an associative whose keys come from the first field of each record, 1534 * and whose values are the corresponding second fields. 1535 * False is returned if an error occurs. 1536 * 1537 * @param string $table the table to query. 1538 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1539 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter). 1540 * @param string $fields a comma separated list of fields to return - the number of fields should be 2! 1541 * @param int $limitfrom return a subset of records, starting at this point (optional). 1542 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1543 * @return array an associative array 1544 * @throws dml_exception A DML specific exception is thrown for any errors. 1545 */ 1546 public function get_records_menu($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1547 $menu = array(); 1548 if ($records = $this->get_records($table, $conditions, $sort, $fields, $limitfrom, $limitnum)) { 1549 foreach ($records as $record) { 1550 $record = (array)$record; 1551 $key = array_shift($record); 1552 $value = array_shift($record); 1553 $menu[$key] = $value; 1554 } 1555 } 1556 return $menu; 1557 } 1558 1559 /** 1560 * Get the first two columns from a number of records as an associative array which match a particular WHERE clause. 1561 * 1562 * Arguments are like {@link function get_recordset_select}. 1563 * Return value is like {@link function get_records_menu}. 1564 * 1565 * @param string $table The database table to be checked against. 1566 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1567 * @param array $params array of sql parameters 1568 * @param string $sort Sort order (optional) - a valid SQL order parameter 1569 * @param string $fields A comma separated list of fields to be returned from the chosen table - the number of fields should be 2! 1570 * @param int $limitfrom return a subset of records, starting at this point (optional). 1571 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1572 * @return array an associative array 1573 * @throws dml_exception A DML specific exception is thrown for any errors. 1574 */ 1575 public function get_records_select_menu($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) { 1576 $menu = array(); 1577 if ($records = $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum)) { 1578 foreach ($records as $record) { 1579 $record = (array)$record; 1580 $key = array_shift($record); 1581 $value = array_shift($record); 1582 $menu[$key] = $value; 1583 } 1584 } 1585 return $menu; 1586 } 1587 1588 /** 1589 * Get the first two columns from a number of records as an associative array using a SQL statement. 1590 * 1591 * Arguments are like {@link function get_recordset_sql}. 1592 * Return value is like {@link function get_records_menu}. 1593 * 1594 * @param string $sql The SQL string you wish to be executed. 1595 * @param array $params array of sql parameters 1596 * @param int $limitfrom return a subset of records, starting at this point (optional). 1597 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1598 * @return array an associative array 1599 * @throws dml_exception A DML specific exception is thrown for any errors. 1600 */ 1601 public function get_records_sql_menu($sql, array $params=null, $limitfrom=0, $limitnum=0) { 1602 $menu = array(); 1603 if ($records = $this->get_records_sql($sql, $params, $limitfrom, $limitnum)) { 1604 foreach ($records as $record) { 1605 $record = (array)$record; 1606 $key = array_shift($record); 1607 $value = array_shift($record); 1608 $menu[$key] = $value; 1609 } 1610 } 1611 return $menu; 1612 } 1613 1614 /** 1615 * Get a single database record as an object where all the given conditions met. 1616 * 1617 * @param string $table The table to select from. 1618 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1619 * @param string $fields A comma separated list of fields to be returned from the chosen table. 1620 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1621 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1622 * MUST_EXIST means we will throw an exception if no record or multiple records found. 1623 * 1624 * @todo MDL-30407 MUST_EXIST option should not throw a dml_exception, it should throw a different exception as it's a requested check. 1625 * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode 1626 * @throws dml_exception A DML specific exception is thrown for any errors. 1627 */ 1628 public function get_record($table, array $conditions, $fields='*', $strictness=IGNORE_MISSING) { 1629 list($select, $params) = $this->where_clause($table, $conditions); 1630 return $this->get_record_select($table, $select, $params, $fields, $strictness); 1631 } 1632 1633 /** 1634 * Get a single database record as an object which match a particular WHERE clause. 1635 * 1636 * @param string $table The database table to be checked against. 1637 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1638 * @param array $params array of sql parameters 1639 * @param string $fields A comma separated list of fields to be returned from the chosen table. 1640 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1641 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1642 * MUST_EXIST means throw exception if no record or multiple records found 1643 * @return stdClass|false a fieldset object containing the first matching record, false or exception if error not found depending on mode 1644 * @throws dml_exception A DML specific exception is thrown for any errors. 1645 */ 1646 public function get_record_select($table, $select, array $params=null, $fields='*', $strictness=IGNORE_MISSING) { 1647 if ($select) { 1648 $select = "WHERE $select"; 1649 } 1650 try { 1651 return $this->get_record_sql("SELECT $fields FROM {" . $table . "} $select", $params, $strictness); 1652 } catch (dml_missing_record_exception $e) { 1653 // create new exception which will contain correct table name 1654 throw new dml_missing_record_exception($table, $e->sql, $e->params); 1655 } 1656 } 1657 1658 /** 1659 * Get a single database record as an object using a SQL statement. 1660 * 1661 * The SQL statement should normally only return one record. 1662 * It is recommended to use get_records_sql() if more matches possible! 1663 * 1664 * @param string $sql The SQL string you wish to be executed, should normally only return one record. 1665 * @param array $params array of sql parameters 1666 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1667 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1668 * MUST_EXIST means throw exception if no record or multiple records found 1669 * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode 1670 * @throws dml_exception A DML specific exception is thrown for any errors. 1671 */ 1672 public function get_record_sql($sql, array $params=null, $strictness=IGNORE_MISSING) { 1673 $strictness = (int)$strictness; // we support true/false for BC reasons too 1674 if ($strictness == IGNORE_MULTIPLE) { 1675 $count = 1; 1676 } else { 1677 $count = 0; 1678 } 1679 if (!$records = $this->get_records_sql($sql, $params, 0, $count)) { 1680 // not found 1681 if ($strictness == MUST_EXIST) { 1682 throw new dml_missing_record_exception('', $sql, $params); 1683 } 1684 return false; 1685 } 1686 1687 if (count($records) > 1) { 1688 if ($strictness == MUST_EXIST) { 1689 throw new dml_multiple_records_exception($sql, $params); 1690 } 1691 debugging('Error: mdb->get_record() found more than one record!'); 1692 } 1693 1694 $return = reset($records); 1695 return $return; 1696 } 1697 1698 /** 1699 * Get a single field value from a table record where all the given conditions met. 1700 * 1701 * @param string $table the table to query. 1702 * @param string $return the field to return the value of. 1703 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1704 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1705 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1706 * MUST_EXIST means throw exception if no record or multiple records found 1707 * @return mixed the specified value false if not found 1708 * @throws dml_exception A DML specific exception is thrown for any errors. 1709 */ 1710 public function get_field($table, $return, array $conditions, $strictness=IGNORE_MISSING) { 1711 list($select, $params) = $this->where_clause($table, $conditions); 1712 return $this->get_field_select($table, $return, $select, $params, $strictness); 1713 } 1714 1715 /** 1716 * Get a single field value from a table record which match a particular WHERE clause. 1717 * 1718 * @param string $table the table to query. 1719 * @param string $return the field to return the value of. 1720 * @param string $select A fragment of SQL to be used in a where clause returning one row with one column 1721 * @param array $params array of sql parameters 1722 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1723 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1724 * MUST_EXIST means throw exception if no record or multiple records found 1725 * @return mixed the specified value false if not found 1726 * @throws dml_exception A DML specific exception is thrown for any errors. 1727 */ 1728 public function get_field_select($table, $return, $select, array $params=null, $strictness=IGNORE_MISSING) { 1729 if ($select) { 1730 $select = "WHERE $select"; 1731 } 1732 try { 1733 return $this->get_field_sql("SELECT $return FROM {" . $table . "} $select", $params, $strictness); 1734 } catch (dml_missing_record_exception $e) { 1735 // create new exception which will contain correct table name 1736 throw new dml_missing_record_exception($table, $e->sql, $e->params); 1737 } 1738 } 1739 1740 /** 1741 * Get a single field value (first field) using a SQL statement. 1742 * 1743 * @param string $sql The SQL query returning one row with one column 1744 * @param array $params array of sql parameters 1745 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found; 1746 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended); 1747 * MUST_EXIST means throw exception if no record or multiple records found 1748 * @return mixed the specified value false if not found 1749 * @throws dml_exception A DML specific exception is thrown for any errors. 1750 */ 1751 public function get_field_sql($sql, array $params=null, $strictness=IGNORE_MISSING) { 1752 if (!$record = $this->get_record_sql($sql, $params, $strictness)) { 1753 return false; 1754 } 1755 1756 $record = (array)$record; 1757 return reset($record); // first column 1758 } 1759 1760 /** 1761 * Selects records and return values of chosen field as an array which match a particular WHERE clause. 1762 * 1763 * @param string $table the table to query. 1764 * @param string $return the field we are intered in 1765 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1766 * @param array $params array of sql parameters 1767 * @return array of values 1768 * @throws dml_exception A DML specific exception is thrown for any errors. 1769 */ 1770 public function get_fieldset_select($table, $return, $select, array $params=null) { 1771 if ($select) { 1772 $select = "WHERE $select"; 1773 } 1774 return $this->get_fieldset_sql("SELECT $return FROM {" . $table . "} $select", $params); 1775 } 1776 1777 /** 1778 * Selects records and return values (first field) as an array using a SQL statement. 1779 * 1780 * @param string $sql The SQL query 1781 * @param array $params array of sql parameters 1782 * @return array of values 1783 * @throws dml_exception A DML specific exception is thrown for any errors. 1784 */ 1785 public abstract function get_fieldset_sql($sql, array $params=null); 1786 1787 /** 1788 * Insert new record into database, as fast as possible, no safety checks, lobs not supported. 1789 * @param string $table name 1790 * @param stdClass|array $params data record as object or array 1791 * @param bool $returnid Returns id of inserted record. 1792 * @param bool $bulk true means repeated inserts expected 1793 * @param bool $customsequence true if 'id' included in $params, disables $returnid 1794 * @return bool|int true or new id 1795 * @throws dml_exception A DML specific exception is thrown for any errors. 1796 */ 1797 public abstract function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false); 1798 1799 /** 1800 * Insert a record into a table and return the "id" field if required. 1801 * 1802 * Some conversions and safety checks are carried out. Lobs are supported. 1803 * If the return ID isn't required, then this just reports success as true/false. 1804 * $data is an object containing needed data 1805 * @param string $table The database table to be inserted into 1806 * @param object|array $dataobject A data object with values for one or more fields in the record 1807 * @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. 1808 * @param bool $bulk Set to true is multiple inserts are expected 1809 * @return bool|int true or new id 1810 * @throws dml_exception A DML specific exception is thrown for any errors. 1811 */ 1812 public abstract function insert_record($table, $dataobject, $returnid=true, $bulk=false); 1813 1814 /** 1815 * Insert multiple records into database as fast as possible. 1816 * 1817 * Order of inserts is maintained, but the operation is not atomic, 1818 * use transactions if necessary. 1819 * 1820 * This method is intended for inserting of large number of small objects, 1821 * do not use for huge objects with text or binary fields. 1822 * 1823 * @since Moodle 2.7 1824 * 1825 * @param string $table The database table to be inserted into 1826 * @param array|Traversable $dataobjects list of objects to be inserted, must be compatible with foreach 1827 * @return void does not return new record ids 1828 * 1829 * @throws coding_exception if data objects have different structure 1830 * @throws dml_exception A DML specific exception is thrown for any errors. 1831 */ 1832 public function insert_records($table, $dataobjects) { 1833 if (!is_array($dataobjects) and !($dataobjects instanceof Traversable)) { 1834 throw new coding_exception('insert_records() passed non-traversable object'); 1835 } 1836 1837 $fields = null; 1838 // Note: override in driver if there is a faster way. 1839 foreach ($dataobjects as $dataobject) { 1840 if (!is_array($dataobject) and !is_object($dataobject)) { 1841 throw new coding_exception('insert_records() passed invalid record object'); 1842 } 1843 $dataobject = (array)$dataobject; 1844 if ($fields === null) { 1845 $fields = array_keys($dataobject); 1846 } else if ($fields !== array_keys($dataobject)) { 1847 throw new coding_exception('All dataobjects in insert_records() must have the same structure!'); 1848 } 1849 $this->insert_record($table, $dataobject, false); 1850 } 1851 } 1852 1853 /** 1854 * Import a record into a table, id field is required. 1855 * Safety checks are NOT carried out. Lobs are supported. 1856 * 1857 * @param string $table name of database table to be inserted into 1858 * @param object $dataobject A data object with values for one or more fields in the record 1859 * @return bool true 1860 * @throws dml_exception A DML specific exception is thrown for any errors. 1861 */ 1862 public abstract function import_record($table, $dataobject); 1863 1864 /** 1865 * Update record in database, as fast as possible, no safety checks, lobs not supported. 1866 * @param string $table name 1867 * @param stdClass|array $params data record as object or array 1868 * @param bool $bulk True means repeated updates expected. 1869 * @return bool true 1870 * @throws dml_exception A DML specific exception is thrown for any errors. 1871 */ 1872 public abstract function update_record_raw($table, $params, $bulk=false); 1873 1874 /** 1875 * Update a record in a table 1876 * 1877 * $dataobject is an object containing needed data 1878 * Relies on $dataobject having a variable "id" to 1879 * specify the record to update 1880 * 1881 * @param string $table The database table to be checked against. 1882 * @param stdClass|array $dataobject An object with contents equal to fieldname=>fieldvalue. 1883 * Must have an entry for 'id' to map to the table specified. 1884 * @param bool $bulk True means repeated updates expected. 1885 * @return bool true 1886 * @throws dml_exception A DML specific exception is thrown for any errors. 1887 */ 1888 public abstract function update_record($table, $dataobject, $bulk=false); 1889 1890 /** 1891 * Set a single field in every table record where all the given conditions met. 1892 * 1893 * @param string $table The database table to be checked against. 1894 * @param string $newfield the field to set. 1895 * @param mixed $newvalue the value to set the field to. 1896 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1897 * @return bool true 1898 * @throws dml_exception A DML specific exception is thrown for any errors. 1899 */ 1900 public function set_field($table, $newfield, $newvalue, array $conditions=null) { 1901 list($select, $params) = $this->where_clause($table, $conditions); 1902 return $this->set_field_select($table, $newfield, $newvalue, $select, $params); 1903 } 1904 1905 /** 1906 * Set a single field in every table record which match a particular WHERE clause. 1907 * 1908 * @param string $table The database table to be checked against. 1909 * @param string $newfield the field to set. 1910 * @param mixed $newvalue the value to set the field to. 1911 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1912 * @param array $params array of sql parameters 1913 * @return bool true 1914 * @throws dml_exception A DML specific exception is thrown for any errors. 1915 */ 1916 public abstract function set_field_select($table, $newfield, $newvalue, $select, array $params=null); 1917 1918 1919 /** 1920 * Count the records in a table where all the given conditions met. 1921 * 1922 * @param string $table The table to query. 1923 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1924 * @return int The count of records returned from the specified criteria. 1925 * @throws dml_exception A DML specific exception is thrown for any errors. 1926 */ 1927 public function count_records($table, array $conditions=null) { 1928 list($select, $params) = $this->where_clause($table, $conditions); 1929 return $this->count_records_select($table, $select, $params); 1930 } 1931 1932 /** 1933 * Count the records in a table which match a particular WHERE clause. 1934 * 1935 * @param string $table The database table to be checked against. 1936 * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call. 1937 * @param array $params array of sql parameters 1938 * @param string $countitem The count string to be used in the SQL call. Default is COUNT('x'). 1939 * @return int The count of records returned from the specified criteria. 1940 * @throws dml_exception A DML specific exception is thrown for any errors. 1941 */ 1942 public function count_records_select($table, $select, array $params=null, $countitem="COUNT('x')") { 1943 if ($select) { 1944 $select = "WHERE $select"; 1945 } 1946 return $this->count_records_sql("SELECT $countitem FROM {" . $table . "} $select", $params); 1947 } 1948 1949 /** 1950 * Get the result of a SQL SELECT COUNT(...) query. 1951 * 1952 * Given a query that counts rows, return that count. (In fact, 1953 * given any query, return the first field of the first record 1954 * returned. However, this method should only be used for the 1955 * intended purpose.) If an error occurs, 0 is returned. 1956 * 1957 * @param string $sql The SQL string you wish to be executed. 1958 * @param array $params array of sql parameters 1959 * @return int the count 1960 * @throws dml_exception A DML specific exception is thrown for any errors. 1961 */ 1962 public function count_records_sql($sql, array $params=null) { 1963 $count = $this->get_field_sql($sql, $params); 1964 if ($count === false or !is_number($count) or $count < 0) { 1965 throw new coding_exception("count_records_sql() expects the first field to contain non-negative number from COUNT(), '$count' found instead."); 1966 } 1967 return (int)$count; 1968 } 1969 1970 /** 1971 * Test whether a record exists in a table where all the given conditions met. 1972 * 1973 * @param string $table The table to check. 1974 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 1975 * @return bool true if a matching record exists, else false. 1976 * @throws dml_exception A DML specific exception is thrown for any errors. 1977 */ 1978 public function record_exists($table, array $conditions) { 1979 list($select, $params) = $this->where_clause($table, $conditions); 1980 return $this->record_exists_select($table, $select, $params); 1981 } 1982 1983 /** 1984 * Test whether any records exists in a table which match a particular WHERE clause. 1985 * 1986 * @param string $table The database table to be checked against. 1987 * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call. 1988 * @param array $params array of sql parameters 1989 * @return bool true if a matching record exists, else false. 1990 * @throws dml_exception A DML specific exception is thrown for any errors. 1991 */ 1992 public function record_exists_select($table, $select, array $params=null) { 1993 if ($select) { 1994 $select = "WHERE $select"; 1995 } 1996 return $this->record_exists_sql("SELECT 'x' FROM {" . $table . "} $select", $params); 1997 } 1998 1999 /** 2000 * Test whether a SQL SELECT statement returns any records. 2001 * 2002 * This function returns true if the SQL statement executes 2003 * without any errors and returns at least one record. 2004 * 2005 * @param string $sql The SQL statement to execute. 2006 * @param array $params array of sql parameters 2007 * @return bool true if the SQL executes without errors and returns at least one record. 2008 * @throws dml_exception A DML specific exception is thrown for any errors. 2009 */ 2010 public function record_exists_sql($sql, array $params=null) { 2011 $mrs = $this->get_recordset_sql($sql, $params, 0, 1); 2012 $return = $mrs->valid(); 2013 $mrs->close(); 2014 return $return; 2015 } 2016 2017 /** 2018 * Delete the records from a table where all the given conditions met. 2019 * If conditions not specified, table is truncated. 2020 * 2021 * @param string $table the table to delete from. 2022 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between 2023 * @return bool true. 2024 * @throws dml_exception A DML specific exception is thrown for any errors. 2025 */ 2026 public function delete_records($table, array $conditions=null) { 2027 // truncate is drop/create (DDL), not transactional safe, 2028 // so we don't use the shortcut within them. MDL-29198 2029 if (is_null($conditions) && empty($this->transactions)) { 2030 return $this->execute("TRUNCATE TABLE {".$table."}"); 2031 } 2032 list($select, $params) = $this->where_clause($table, $conditions); 2033 return $this->delete_records_select($table, $select, $params); 2034 } 2035 2036 /** 2037 * Delete the records from a table where one field match one list of values. 2038 * 2039 * @param string $table the table to delete from. 2040 * @param string $field The field to search 2041 * @param array $values array of values 2042 * @return bool true. 2043 * @throws dml_exception A DML specific exception is thrown for any errors. 2044 */ 2045 public function delete_records_list($table, $field, array $values) { 2046 list($select, $params) = $this->where_clause_list($field, $values); 2047 return $this->delete_records_select($table, $select, $params); 2048 } 2049 2050 /** 2051 * Deletes records from a table using a subquery. The subquery should return a list of values 2052 * in a single column, which match one field from the table being deleted. 2053 * 2054 * The $alias parameter must be set to the name of the single column in your subquery result 2055 * (e.g. if the subquery is 'SELECT id FROM whatever', then it should be 'id'). This is not 2056 * needed on most databases, but MySQL requires it. 2057 * 2058 * (On database where the subquery is inefficient, it is implemented differently.) 2059 * 2060 * @param string $table Table to delete from 2061 * @param string $field Field in table to match 2062 * @param string $alias Name of single column in subquery e.g. 'id' 2063 * @param string $subquery Subquery that will return values of the field to delete 2064 * @param array $params Parameters for subquery 2065 * @throws dml_exception If there is any error 2066 * @since Moodle 3.10 2067 */ 2068 public function delete_records_subquery(string $table, string $field, string $alias, 2069 string $subquery, array $params = []): void { 2070 $this->delete_records_select($table, $field . ' IN (' . $subquery . ')', $params); 2071 } 2072 2073 /** 2074 * Delete one or more records from a table which match a particular WHERE clause. 2075 * 2076 * @param string $table The database table to be checked against. 2077 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria). 2078 * @param array $params array of sql parameters 2079 * @return bool true. 2080 * @throws dml_exception A DML specific exception is thrown for any errors. 2081 */ 2082 public abstract function delete_records_select($table, $select, array $params=null); 2083 2084 /** 2085 * Returns the FROM clause required by some DBs in all SELECT statements. 2086 * 2087 * To be used in queries not having FROM clause to provide cross_db 2088 * Most DBs don't need it, hence the default is '' 2089 * @return string 2090 */ 2091 public function sql_null_from_clause() { 2092 return ''; 2093 } 2094 2095 /** 2096 * Returns the SQL text to be used in order to perform one bitwise AND operation 2097 * between 2 integers. 2098 * 2099 * NOTE: The SQL result is a number and can not be used directly in 2100 * SQL condition, please compare it to some number to get a bool!! 2101 * 2102 * @param string $int1 SQL for the first integer in the operation. 2103 * @param string $int2 SQL for the second integer in the operation. 2104 * @return string The piece of SQL code to be used in your statement. 2105 */ 2106 public function sql_bitand($int1, $int2) { 2107 return '((' . $int1 . ') & (' . $int2 . '))'; 2108 } 2109 2110 /** 2111 * Returns the SQL text to be used in order to perform one bitwise NOT operation 2112 * with 1 integer. 2113 * 2114 * @param int $int1 The operand integer in the operation. 2115 * @return string The piece of SQL code to be used in your statement. 2116 */ 2117 public function sql_bitnot($int1) { 2118 return '(~(' . $int1 . '))'; 2119 } 2120 2121 /** 2122 * Returns the SQL text to be used in order to perform one bitwise OR operation 2123 * between 2 integers. 2124 * 2125 * NOTE: The SQL result is a number and can not be used directly in 2126 * SQL condition, please compare it to some number to get a bool!! 2127 * 2128 * @param int $int1 The first operand integer in the operation. 2129 * @param int $int2 The second operand integer in the operation. 2130 * @return string The piece of SQL code to be used in your statement. 2131 */ 2132 public function sql_bitor($int1, $int2) { 2133 return '((' . $int1 . ') | (' . $int2 . '))'; 2134 } 2135 2136 /** 2137 * Returns the SQL text to be used in order to perform one bitwise XOR operation 2138 * between 2 integers. 2139 * 2140 * NOTE: The SQL result is a number and can not be used directly in 2141 * SQL condition, please compare it to some number to get a bool!! 2142 * 2143 * @param int $int1 The first operand integer in the operation. 2144 * @param int $int2 The second operand integer in the operation. 2145 * @return string The piece of SQL code to be used in your statement. 2146 */ 2147 public function sql_bitxor($int1, $int2) { 2148 return '((' . $int1 . ') ^ (' . $int2 . '))'; 2149 } 2150 2151 /** 2152 * Returns the SQL text to be used in order to perform module '%' 2153 * operation - remainder after division 2154 * 2155 * @param int $int1 The first operand integer in the operation. 2156 * @param int $int2 The second operand integer in the operation. 2157 * @return string The piece of SQL code to be used in your statement. 2158 */ 2159 public function sql_modulo($int1, $int2) { 2160 return '((' . $int1 . ') % (' . $int2 . '))'; 2161 } 2162 2163 /** 2164 * Returns the cross db correct CEIL (ceiling) expression applied to fieldname. 2165 * note: Most DBs use CEIL(), hence it's the default here. 2166 * 2167 * @param string $fieldname The field (or expression) we are going to ceil. 2168 * @return string The piece of SQL code to be used in your ceiling statement. 2169 */ 2170 public function sql_ceil($fieldname) { 2171 return ' CEIL(' . $fieldname . ')'; 2172 } 2173 2174 /** 2175 * Return SQL for casting to char of given field/expression. Default implementation performs implicit cast using 2176 * concatenation with an empty string 2177 * 2178 * @param string $field Table field or SQL expression to be cast 2179 * @return string 2180 */ 2181 public function sql_cast_to_char(string $field): string { 2182 return $this->sql_concat("''", $field); 2183 } 2184 2185 /** 2186 * Returns the SQL to be used in order to CAST one CHAR column to INTEGER. 2187 * 2188 * Be aware that the CHAR column you're trying to cast contains really 2189 * int values or the RDBMS will throw an error! 2190 * 2191 * @param string $fieldname The name of the field to be casted. 2192 * @param bool $text Specifies if the original column is one TEXT (CLOB) column (true). Defaults to false. 2193 * @return string The piece of SQL code to be used in your statement. 2194 */ 2195 public function sql_cast_char2int($fieldname, $text=false) { 2196 return ' ' . $fieldname . ' '; 2197 } 2198 2199 /** 2200 * Returns the SQL to be used in order to CAST one CHAR column to REAL number. 2201 * 2202 * Be aware that the CHAR column you're trying to cast contains really 2203 * numbers or the RDBMS will throw an error! 2204 * 2205 * @param string $fieldname The name of the field to be casted. 2206 * @param bool $text Specifies if the original column is one TEXT (CLOB) column (true). Defaults to false. 2207 * @return string The piece of SQL code to be used in your statement. 2208 */ 2209 public function sql_cast_char2real($fieldname, $text=false) { 2210 return ' ' . $fieldname . ' '; 2211 } 2212 2213 /** 2214 * Returns the SQL to be used in order to an UNSIGNED INTEGER column to SIGNED. 2215 * 2216 * (Only MySQL needs this. MySQL things that 1 * -1 = 18446744073709551615 2217 * if the 1 comes from an unsigned column). 2218 * 2219 * @deprecated since 2.3 2220 * @param string $fieldname The name of the field to be cast 2221 * @return string The piece of SQL code to be used in your statement. 2222 */ 2223 public function sql_cast_2signed($fieldname) { 2224 return ' ' . $fieldname . ' '; 2225 } 2226 2227 /** 2228 * Returns the SQL text to be used to compare one TEXT (clob) column with 2229 * one varchar column, because some RDBMS doesn't support such direct 2230 * comparisons. 2231 * 2232 * @param string $fieldname The name of the TEXT field we need to order by 2233 * @param int $numchars Number of chars to use for the ordering (defaults to 32). 2234 * @return string The piece of SQL code to be used in your statement. 2235 */ 2236 public function sql_compare_text($fieldname, $numchars=32) { 2237 return $this->sql_order_by_text($fieldname, $numchars); 2238 } 2239 2240 /** 2241 * Returns an equal (=) or not equal (<>) part of a query. 2242 * 2243 * Note the use of this method may lead to slower queries (full scans) so 2244 * use it only when needed and against already reduced data sets. 2245 * 2246 * @since Moodle 3.2 2247 * 2248 * @param string $fieldname Usually the name of the table column. 2249 * @param string $param Usually the bound query parameter (?, :named). 2250 * @param bool $casesensitive Use case sensitive search when set to true (default). 2251 * @param bool $accentsensitive Use accent sensitive search when set to true (default). (not all databases support accent insensitive) 2252 * @param bool $notequal True means not equal (<>) 2253 * @return string The SQL code fragment. 2254 */ 2255 public function sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false) { 2256 // Note that, by default, it's assumed that the correct sql equal operations are 2257 // case sensitive. Only databases not observing this behavior must override the method. 2258 // Also, accent sensitiveness only will be handled by databases supporting it. 2259 $equalop = $notequal ? '<>' : '='; 2260 if ($casesensitive) { 2261 return "$fieldname $equalop $param"; 2262 } else { 2263 return "LOWER($fieldname) $equalop LOWER($param)"; 2264 } 2265 } 2266 2267 /** 2268 * Returns 'LIKE' part of a query. 2269 * 2270 * @param string $fieldname Usually the name of the table column. 2271 * @param string $param Usually the bound query parameter (?, :named). 2272 * @param bool $casesensitive Use case sensitive search when set to true (default). 2273 * @param bool $accentsensitive Use accent sensitive search when set to true (default). (not all databases support accent insensitive) 2274 * @param bool $notlike True means "NOT LIKE". 2275 * @param string $escapechar The escape char for '%' and '_'. 2276 * @return string The SQL code fragment. 2277 */ 2278 public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') { 2279 if (strpos($param, '%') !== false) { 2280 debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)'); 2281 } 2282 $LIKE = $notlike ? 'NOT LIKE' : 'LIKE'; 2283 // by default ignore any sensitiveness - each database does it in a different way 2284 return "$fieldname $LIKE $param ESCAPE '$escapechar'"; 2285 } 2286 2287 /** 2288 * Escape sql LIKE special characters like '_' or '%'. 2289 * @param string $text The string containing characters needing escaping. 2290 * @param string $escapechar The desired escape character, defaults to '\\'. 2291 * @return string The escaped sql LIKE string. 2292 */ 2293 public function sql_like_escape($text, $escapechar = '\\') { 2294 $text = str_replace('_', $escapechar.'_', $text); 2295 $text = str_replace('%', $escapechar.'%', $text); 2296 return $text; 2297 } 2298 2299 /** 2300 * Returns the proper SQL to do CONCAT between the elements(fieldnames) passed. 2301 * 2302 * This function accepts variable number of string parameters. 2303 * All strings/fieldnames will used in the SQL concatenate statement generated. 2304 * 2305 * @return string The SQL to concatenate strings passed in. 2306 * @uses func_get_args() and thus parameters are unlimited OPTIONAL number of additional field names. 2307 */ 2308 public abstract function sql_concat(); 2309 2310 /** 2311 * Returns the proper SQL to do CONCAT between the elements passed 2312 * with a given separator 2313 * 2314 * @param string $separator The separator desired for the SQL concatenating $elements. 2315 * @param array $elements The array of strings to be concatenated. 2316 * @return string The SQL to concatenate the strings. 2317 */ 2318 public abstract function sql_concat_join($separator="' '", $elements=array()); 2319 2320 /** 2321 * Return SQL for performing group concatenation on given field/expression 2322 * 2323 * @param string $field Table field or SQL expression to be concatenated 2324 * @param string $separator The separator desired between each concatetated field 2325 * @param string $sort Ordering of the concatenated field 2326 * @return string 2327 */ 2328 public abstract function sql_group_concat(string $field, string $separator = ', ', string $sort = ''): string; 2329 2330 /** 2331 * Returns the proper SQL (for the dbms in use) to concatenate $firstname and $lastname 2332 * 2333 * @todo MDL-31233 This may not be needed here. 2334 * 2335 * @param string $first User's first name (default:'firstname'). 2336 * @param string $last User's last name (default:'lastname'). 2337 * @return string The SQL to concatenate strings. 2338 */ 2339 function sql_fullname($first='firstname', $last='lastname') { 2340 return $this->sql_concat($first, "' '", $last); 2341 } 2342 2343 /** 2344 * Returns the SQL text to be used to order by one TEXT (clob) column, because 2345 * some RDBMS doesn't support direct ordering of such fields. 2346 * 2347 * Note that the use or queries being ordered by TEXT columns must be minimised, 2348 * because it's really slooooooow. 2349 * 2350 * @param string $fieldname The name of the TEXT field we need to order by. 2351 * @param int $numchars The number of chars to use for the ordering (defaults to 32). 2352 * @return string The piece of SQL code to be used in your statement. 2353 */ 2354 public function sql_order_by_text($fieldname, $numchars=32) { 2355 return $fieldname; 2356 } 2357 2358 /** 2359 * Returns the SQL text to be used to order by columns, standardising the return 2360 * pattern of null values across database types to sort nulls first when ascending 2361 * and last when descending. 2362 * 2363 * @param string $fieldname The name of the field we need to sort by. 2364 * @param int $sort An order to sort the results in. 2365 * @return string The piece of SQL code to be used in your statement. 2366 */ 2367 public function sql_order_by_null(string $fieldname, int $sort = SORT_ASC): string { 2368 return $fieldname . ' ' . ($sort == SORT_ASC ? 'ASC' : 'DESC'); 2369 } 2370 2371 /** 2372 * Returns the SQL text to be used to calculate the length in characters of one expression. 2373 * @param string $fieldname The fieldname/expression to calculate its length in characters. 2374 * @return string the piece of SQL code to be used in the statement. 2375 */ 2376 public function sql_length($fieldname) { 2377 return ' LENGTH(' . $fieldname . ')'; 2378 } 2379 2380 /** 2381 * Returns the proper substr() SQL text used to extract substrings from DB 2382 * NOTE: this was originally returning only function name 2383 * 2384 * @param string $expr Some string field, no aggregates. 2385 * @param mixed $start Integer or expression evaluating to integer (1 based value; first char has index 1) 2386 * @param mixed $length Optional integer or expression evaluating to integer. 2387 * @return string The sql substring extraction fragment. 2388 */ 2389 public function sql_substr($expr, $start, $length=false) { 2390 if (count(func_get_args()) < 2) { 2391 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.'); 2392 } 2393 if ($length === false) { 2394 return "SUBSTR($expr, $start)"; 2395 } else { 2396 return "SUBSTR($expr, $start, $length)"; 2397 } 2398 } 2399 2400 /** 2401 * Returns the SQL for returning searching one string for the location of another. 2402 * 2403 * Note, there is no guarantee which order $needle, $haystack will be in 2404 * the resulting SQL so when using this method, and both arguments contain 2405 * placeholders, you should use named placeholders. 2406 * 2407 * @param string $needle the SQL expression that will be searched for. 2408 * @param string $haystack the SQL expression that will be searched in. 2409 * @return string The required searching SQL part. 2410 */ 2411 public function sql_position($needle, $haystack) { 2412 // Implementation using standard SQL. 2413 return "POSITION(($needle) IN ($haystack))"; 2414 } 2415 2416 /** 2417 * This used to return empty string replacement character. 2418 * 2419 * @deprecated use bound parameter with empty string instead 2420 * 2421 * @return string An empty string. 2422 */ 2423 function sql_empty() { 2424 debugging("sql_empty() is deprecated, please use empty string '' as sql parameter value instead", DEBUG_DEVELOPER); 2425 return ''; 2426 } 2427 2428 /** 2429 * Returns the proper SQL to know if one field is empty. 2430 * 2431 * Note that the function behavior strongly relies on the 2432 * parameters passed describing the field so, please, be accurate 2433 * when specifying them. 2434 * 2435 * Also, note that this function is not suitable to look for 2436 * fields having NULL contents at all. It's all for empty values! 2437 * 2438 * This function should be applied in all the places where conditions of 2439 * the type: 2440 * 2441 * ... AND fieldname = ''; 2442 * 2443 * are being used. Final result for text fields should be: 2444 * 2445 * ... AND ' . sql_isempty('tablename', 'fieldname', true/false, true); 2446 * 2447 * and for varchar fields result should be: 2448 * 2449 * ... AND fieldname = :empty; "; $params['empty'] = ''; 2450 * 2451 * (see parameters description below) 2452 * 2453 * @param string $tablename Name of the table (without prefix). Not used for now but can be 2454 * necessary in the future if we want to use some introspection using 2455 * meta information against the DB. /// TODO /// 2456 * @param string $fieldname Name of the field we are going to check 2457 * @param bool $nullablefield For specifying if the field is nullable (true) or no (false) in the DB. 2458 * @param bool $textfield For specifying if it is a text (also called clob) field (true) or a varchar one (false) 2459 * @return string the sql code to be added to check for empty values 2460 */ 2461 public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) { 2462 return " ($fieldname = '') "; 2463 } 2464 2465 /** 2466 * Returns the proper SQL to know if one field is not empty. 2467 * 2468 * Note that the function behavior strongly relies on the 2469 * parameters passed describing the field so, please, be accurate 2470 * when specifying them. 2471 * 2472 * This function should be applied in all the places where conditions of 2473 * the type: 2474 * 2475 * ... AND fieldname != ''; 2476 * 2477 * are being used. Final result for text fields should be: 2478 * 2479 * ... AND ' . sql_isnotempty('tablename', 'fieldname', true/false, true/false); 2480 * 2481 * and for varchar fields result should be: 2482 * 2483 * ... AND fieldname != :empty; "; $params['empty'] = ''; 2484 * 2485 * (see parameters description below) 2486 * 2487 * @param string $tablename Name of the table (without prefix). This is not used for now but can be 2488 * necessary in the future if we want to use some introspection using 2489 * meta information against the DB. 2490 * @param string $fieldname The name of the field we are going to check. 2491 * @param bool $nullablefield Specifies if the field is nullable (true) or not (false) in the DB. 2492 * @param bool $textfield Specifies if it is a text (also called clob) field (true) or a varchar one (false). 2493 * @return string The sql code to be added to check for non empty values. 2494 */ 2495 public function sql_isnotempty($tablename, $fieldname, $nullablefield, $textfield) { 2496 return ' ( NOT ' . $this->sql_isempty($tablename, $fieldname, $nullablefield, $textfield) . ') '; 2497 } 2498 2499 /** 2500 * Returns true if this database driver supports regex syntax when searching. 2501 * @return bool True if supported. 2502 */ 2503 public function sql_regex_supported() { 2504 return false; 2505 } 2506 2507 /** 2508 * Returns the driver specific syntax (SQL part) for matching regex positively or negatively (inverted matching). 2509 * Eg: 'REGEXP':'NOT REGEXP' or '~*' : '!~*' 2510 * 2511 * @param bool $positivematch 2512 * @param bool $casesensitive 2513 * @return string or empty if not supported 2514 */ 2515 public function sql_regex($positivematch = true, $casesensitive = false) { 2516 return ''; 2517 } 2518 2519 /** 2520 * Returns the word-beginning boundary marker if this database driver supports regex syntax when searching. 2521 * @return string The word-beginning boundary marker. Otherwise, an empty string. 2522 */ 2523 public function sql_regex_get_word_beginning_boundary_marker() { 2524 if ($this->sql_regex_supported()) { 2525 return '[[:<:]]'; 2526 } 2527 2528 return ''; 2529 } 2530 2531 /** 2532 * Returns the word-end boundary marker if this database driver supports regex syntax when searching. 2533 * @return string The word-end boundary marker. Otherwise, an empty string. 2534 */ 2535 public function sql_regex_get_word_end_boundary_marker() { 2536 if ($this->sql_regex_supported()) { 2537 return '[[:>:]]'; 2538 } 2539 2540 return ''; 2541 } 2542 2543 /** 2544 * Returns the SQL that allows to find intersection of two or more queries 2545 * 2546 * @since Moodle 2.8 2547 * 2548 * @param array $selects array of SQL select queries, each of them only returns fields with the names from $fields 2549 * @param string $fields comma-separated list of fields (used only by some DB engines) 2550 * @return string SQL query that will return only values that are present in each of selects 2551 */ 2552 public function sql_intersect($selects, $fields) { 2553 if (!count($selects)) { 2554 throw new coding_exception('sql_intersect() requires at least one element in $selects'); 2555 } else if (count($selects) == 1) { 2556 return $selects[0]; 2557 } 2558 static $aliascnt = 0; 2559 $rv = '('.$selects[0].')'; 2560 for ($i = 1; $i < count($selects); $i++) { 2561 $rv .= " INTERSECT (".$selects[$i].')'; 2562 } 2563 return $rv; 2564 } 2565 2566 /** 2567 * Does this driver support tool_replace? 2568 * 2569 * @since Moodle 2.6.1 2570 * @return bool 2571 */ 2572 public function replace_all_text_supported() { 2573 return false; 2574 } 2575 2576 /** 2577 * Replace given text in all rows of column. 2578 * 2579 * @since Moodle 2.6.1 2580 * @param string $table name of the table 2581 * @param database_column_info $column 2582 * @param string $search 2583 * @param string $replace 2584 */ 2585 public function replace_all_text($table, database_column_info $column, $search, $replace) { 2586 if (!$this->replace_all_text_supported()) { 2587 return; 2588 } 2589 2590 // NOTE: override this methods if following standard compliant SQL 2591 // does not work for your driver. 2592 2593 // Enclose the column name by the proper quotes if it's a reserved word. 2594 $columnname = $this->get_manager()->generator->getEncQuoted($column->name); 2595 2596 $searchsql = $this->sql_like($columnname, '?'); 2597 $searchparam = '%'.$this->sql_like_escape($search).'%'; 2598 2599 $sql = "UPDATE {".$table."} 2600 SET $columnname = REPLACE($columnname, ?, ?) 2601 WHERE $searchsql"; 2602 2603 if ($column->meta_type === 'X') { 2604 $this->execute($sql, array($search, $replace, $searchparam)); 2605 2606 } else if ($column->meta_type === 'C') { 2607 if (core_text::strlen($search) < core_text::strlen($replace)) { 2608 $colsize = $column->max_length; 2609 $sql = "UPDATE {".$table."} 2610 SET $columnname = " . $this->sql_substr("REPLACE(" . $columnname . ", ?, ?)", 1, $colsize) . " 2611 WHERE $searchsql"; 2612 } 2613 $this->execute($sql, array($search, $replace, $searchparam)); 2614 } 2615 } 2616 2617 /** 2618 * Analyze the data in temporary tables to force statistics collection after bulk data loads. 2619 * 2620 * @return void 2621 */ 2622 public function update_temp_table_stats() { 2623 $this->temptables->update_stats(); 2624 } 2625 2626 /** 2627 * Checks and returns true if transactions are supported. 2628 * 2629 * It is not responsible to run productions servers 2630 * on databases without transaction support ;-) 2631 * 2632 * Override in driver if needed. 2633 * 2634 * @return bool 2635 */ 2636 protected function transactions_supported() { 2637 // protected for now, this might be changed to public if really necessary 2638 return true; 2639 } 2640 2641 /** 2642 * Returns true if a transaction is in progress. 2643 * @return bool 2644 */ 2645 public function is_transaction_started() { 2646 return !empty($this->transactions); 2647 } 2648 2649 /** 2650 * This is a test that throws an exception if transaction in progress. 2651 * This test does not force rollback of active transactions. 2652 * @return void 2653 * @throws dml_transaction_exception if stansaction active 2654 */ 2655 public function transactions_forbidden() { 2656 if ($this->is_transaction_started()) { 2657 throw new dml_transaction_exception('This code can not be excecuted in transaction'); 2658 } 2659 } 2660 2661 /** 2662 * On DBs that support it, switch to transaction mode and begin a transaction 2663 * you'll need to ensure you call allow_commit() on the returned object 2664 * or your changes *will* be lost. 2665 * 2666 * this is _very_ useful for massive updates 2667 * 2668 * Delegated database transactions can be nested, but only one actual database 2669 * transaction is used for the outer-most delegated transaction. This method 2670 * returns a transaction object which you should keep until the end of the 2671 * delegated transaction. The actual database transaction will 2672 * only be committed if all the nested delegated transactions commit 2673 * successfully. If any part of the transaction rolls back then the whole 2674 * thing is rolled back. 2675 * 2676 * @return moodle_transaction 2677 */ 2678 public function start_delegated_transaction() { 2679 $transaction = new moodle_transaction($this); 2680 $this->transactions[] = $transaction; 2681 if (count($this->transactions) == 1) { 2682 $this->begin_transaction(); 2683 } 2684 return $transaction; 2685 } 2686 2687 /** 2688 * Driver specific start of real database transaction, 2689 * this can not be used directly in code. 2690 * @return void 2691 */ 2692 protected abstract function begin_transaction(); 2693 2694 /** 2695 * Indicates delegated transaction finished successfully. 2696 * The real database transaction is committed only if 2697 * all delegated transactions committed. 2698 * @param moodle_transaction $transaction The transaction to commit 2699 * @return void 2700 * @throws dml_transaction_exception Creates and throws transaction related exceptions. 2701 */ 2702 public function commit_delegated_transaction(moodle_transaction $transaction) { 2703 if ($transaction->is_disposed()) { 2704 throw new dml_transaction_exception('Transactions already disposed', $transaction); 2705 } 2706 // mark as disposed so that it can not be used again 2707 $transaction->dispose(); 2708 2709 if (empty($this->transactions)) { 2710 throw new dml_transaction_exception('Transaction not started', $transaction); 2711 } 2712 2713 if ($this->force_rollback) { 2714 throw new dml_transaction_exception('Tried to commit transaction after lower level rollback', $transaction); 2715 } 2716 2717 if ($transaction !== $this->transactions[count($this->transactions) - 1]) { 2718 // one incorrect commit at any level rollbacks everything 2719 $this->force_rollback = true; 2720 throw new dml_transaction_exception('Invalid transaction commit attempt', $transaction); 2721 } 2722 2723 if (count($this->transactions) == 1) { 2724 // only commit the top most level 2725 $this->commit_transaction(); 2726 } 2727 array_pop($this->transactions); 2728 2729 if (empty($this->transactions)) { 2730 \core\event\manager::database_transaction_commited(); 2731 \core\message\manager::database_transaction_commited(); 2732 } 2733 } 2734 2735 /** 2736 * Driver specific commit of real database transaction, 2737 * this can not be used directly in code. 2738 * @return void 2739 */ 2740 protected abstract function commit_transaction(); 2741 2742 /** 2743 * Call when delegated transaction failed, this rolls back 2744 * all delegated transactions up to the top most level. 2745 * 2746 * In many cases you do not need to call this method manually, 2747 * because all open delegated transactions are rolled back 2748 * automatically if exceptions not caught. 2749 * 2750 * @param moodle_transaction $transaction An instance of a moodle_transaction. 2751 * @param Exception|Throwable $e The related exception/throwable to this transaction rollback. 2752 * @return void This does not return, instead the exception passed in will be rethrown. 2753 */ 2754 public function rollback_delegated_transaction(moodle_transaction $transaction, $e) { 2755 if (!($e instanceof Exception) && !($e instanceof Throwable)) { 2756 // PHP7 - we catch Throwables in phpunit but can't use that as the type hint in PHP5. 2757 $e = new \coding_exception("Must be given an Exception or Throwable object!"); 2758 } 2759 if ($transaction->is_disposed()) { 2760 throw new dml_transaction_exception('Transactions already disposed', $transaction); 2761 } 2762 // mark as disposed so that it can not be used again 2763 $transaction->dispose(); 2764 2765 // one rollback at any level rollbacks everything 2766 $this->force_rollback = true; 2767 2768 if (empty($this->transactions) or $transaction !== $this->transactions[count($this->transactions) - 1]) { 2769 // this may or may not be a coding problem, better just rethrow the exception, 2770 // because we do not want to loose the original $e 2771 throw $e; 2772 } 2773 2774 if (count($this->transactions) == 1) { 2775 // only rollback the top most level 2776 $this->rollback_transaction(); 2777 } 2778 array_pop($this->transactions); 2779 if (empty($this->transactions)) { 2780 // finally top most level rolled back 2781 $this->force_rollback = false; 2782 \core\event\manager::database_transaction_rolledback(); 2783 \core\message\manager::database_transaction_rolledback(); 2784 } 2785 throw $e; 2786 } 2787 2788 /** 2789 * Driver specific abort of real database transaction, 2790 * this can not be used directly in code. 2791 * @return void 2792 */ 2793 protected abstract function rollback_transaction(); 2794 2795 /** 2796 * Force rollback of all delegated transaction. 2797 * Does not throw any exceptions and does not log anything. 2798 * 2799 * This method should be used only from default exception handlers and other 2800 * core code. 2801 * 2802 * @return void 2803 */ 2804 public function force_transaction_rollback() { 2805 if ($this->transactions) { 2806 try { 2807 $this->rollback_transaction(); 2808 } catch (dml_exception $e) { 2809 // ignore any sql errors here, the connection might be broken 2810 } 2811 } 2812 2813 // now enable transactions again 2814 $this->transactions = array(); 2815 $this->force_rollback = false; 2816 2817 \core\event\manager::database_transaction_rolledback(); 2818 \core\message\manager::database_transaction_rolledback(); 2819 } 2820 2821 /** 2822 * Is session lock supported in this driver? 2823 * @return bool 2824 */ 2825 public function session_lock_supported() { 2826 return false; 2827 } 2828 2829 /** 2830 * Obtains the session lock. 2831 * @param int $rowid The id of the row with session record. 2832 * @param int $timeout The maximum allowed time to wait for the lock in seconds. 2833 * @return void 2834 * @throws dml_exception A DML specific exception is thrown for any errors. 2835 */ 2836 public function get_session_lock($rowid, $timeout) { 2837 $this->used_for_db_sessions = true; 2838 } 2839 2840 /** 2841 * Releases the session lock. 2842 * @param int $rowid The id of the row with session record. 2843 * @return void 2844 * @throws dml_exception A DML specific exception is thrown for any errors. 2845 */ 2846 public function release_session_lock($rowid) { 2847 } 2848 2849 /** 2850 * Returns the number of reads done by this database. 2851 * @return int Number of reads. 2852 */ 2853 public function perf_get_reads() { 2854 return $this->reads; 2855 } 2856 2857 /** 2858 * Returns whether we want to connect to slave database for read queries. 2859 * @return bool Want read only connection 2860 */ 2861 public function want_read_slave(): bool { 2862 return false; 2863 } 2864 2865 /** 2866 * Returns the number of reads before first write done by this database. 2867 * @return int Number of reads. 2868 */ 2869 public function perf_get_reads_slave(): int { 2870 return 0; 2871 } 2872 2873 /** 2874 * Returns the number of writes done by this database. 2875 * @return int Number of writes. 2876 */ 2877 public function perf_get_writes() { 2878 return $this->writes; 2879 } 2880 2881 /** 2882 * Returns the number of queries done by this database. 2883 * @return int Number of queries. 2884 */ 2885 public function perf_get_queries() { 2886 return $this->writes + $this->reads; 2887 } 2888 2889 /** 2890 * Time waiting for the database engine to finish running all queries. 2891 * @return float Number of seconds with microseconds 2892 */ 2893 public function perf_get_queries_time() { 2894 return $this->queriestime; 2895 } 2896 2897 /** 2898 * Whether the database is able to support full-text search or not. 2899 * 2900 * @return bool 2901 */ 2902 public function is_fulltext_search_supported() { 2903 // No support unless specified. 2904 return false; 2905 } 2906 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
