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