Search moodle.org's
Developer Documentation
Developer Documentation
- Bug fixes for general core bugs in 4.3.x will end 7 October 2024 (12 months).
- Bug fixes for security issues in 4.3.x will end 21 April 2025 (18 months).
- PHP version: minimum PHP 8.0.0 Note: minimum PHP version has increased since Moodle 4.1. PHP 8.2.x is supported too.
/lib/dml/ -> mysqli_native_moodle_database.php (source)
Differences Between: [Versions 310 and 403] [Versions 311 and 403] [Versions 39 and 403] [Versions 400 and 403] [Versions 401 and 403] [Versions 402 and 403]
1 <?php 2 // This file is part of Moodle - http://moodle.org/ 3 // 4 // Moodle is free software: you can redistribute it and/or modify 5 // it under the terms of the GNU General Public License as published by 6 // the Free Software Foundation, either version 3 of the License, or 7 // (at your option) any later version. 8 // 9 // Moodle is distributed in the hope that it will be useful, 10 // but WITHOUT ANY WARRANTY; without even the implied warranty of 11 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 12 // GNU General Public License for more details. 13 // 14 // You should have received a copy of the GNU General Public License 15 // along with Moodle. If not, see <http://www.gnu.org/licenses/>. 16 17 /** 18 * Native mysqli class representing moodle database interface. 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__.'/moodle_database.php'); 28 require_once (__DIR__.'/moodle_read_slave_trait.php'); 29 require_once (__DIR__.'/mysqli_native_moodle_recordset.php'); 30 require_once (__DIR__.'/mysqli_native_moodle_temptables.php'); 31 32 /** 33 * Native mysqli class representing moodle database interface. 34 * 35 * @package core_dml 36 * @copyright 2008 Petr Skoda (http://skodak.org) 37 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later 38 */ 39 class mysqli_native_moodle_database extends moodle_database { 40 use moodle_read_slave_trait { 41 can_use_readonly as read_slave_can_use_readonly; 42 } 43 44 /** @var array $sslmodes */ 45 private static $sslmodes = [ 46 'require', 47 'verify-full' 48 ]; 49 50 /** @var mysqli $mysqli */ 51 protected $mysqli = null; 52 /** @var bool is compressed row format supported cache */ 53 protected $compressedrowformatsupported = null; 54 /** @var string DB server actual version */ 55 protected $serverversion = null; 56 57 private $transactions_supported = null; 58 59 /** 60 * Attempt to create the database 61 * @param string $dbhost 62 * @param string $dbuser 63 * @param string $dbpass 64 * @param string $dbname 65 * @return bool success 66 * @throws dml_exception A DML specific exception is thrown for any errors. 67 */ 68 public function create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null) { 69 $driverstatus = $this->driver_installed(); 70 71 if ($driverstatus !== true) { 72 throw new dml_exception('dbdriverproblem', $driverstatus); 73 } 74 75 if (!empty($dboptions['dbsocket']) 76 and (strpos($dboptions['dbsocket'], '/') !== false or strpos($dboptions['dbsocket'], '\\') !== false)) { 77 $dbsocket = $dboptions['dbsocket']; 78 } else { 79 $dbsocket = ini_get('mysqli.default_socket'); 80 } 81 if (empty($dboptions['dbport'])) { 82 $dbport = (int)ini_get('mysqli.default_port'); 83 } else { 84 $dbport = (int)$dboptions['dbport']; 85 } 86 // verify ini.get does not return nonsense 87 if (empty($dbport)) { 88 $dbport = 3306; 89 } 90 ob_start(); 91 $conn = new mysqli($dbhost, $dbuser, $dbpass, '', $dbport, $dbsocket); // Connect without db 92 $dberr = ob_get_contents(); 93 ob_end_clean(); 94 $errorno = @$conn->connect_errno; 95 96 if ($errorno !== 0) { 97 throw new dml_connection_exception($dberr); 98 } 99 100 // Normally a check would be done before setting utf8mb4, but the database can be created 101 // before the enviroment checks are done. We'll proceed with creating the database and then do checks next. 102 $charset = 'utf8mb4'; 103 if (isset($dboptions['dbcollation']) and (strpos($dboptions['dbcollation'], 'utf8_') === 0 104 || strpos($dboptions['dbcollation'], 'utf8mb4_') === 0)) { 105 $collation = $dboptions['dbcollation']; 106 $collationinfo = explode('_', $dboptions['dbcollation']); 107 $charset = reset($collationinfo); 108 } else { 109 $collation = 'utf8mb4_unicode_ci'; 110 } 111 112 $result = $conn->query("CREATE DATABASE $dbname DEFAULT CHARACTER SET $charset DEFAULT COLLATE ".$collation); 113 114 $conn->close(); 115 116 if (!$result) { 117 throw new dml_exception('cannotcreatedb'); 118 } 119 120 return true; 121 } 122 123 /** 124 * Detects if all needed PHP stuff installed. 125 * Note: can be used before connect() 126 * @return mixed true if ok, string if something 127 */ 128 public function driver_installed() { 129 if (!extension_loaded('mysqli')) { 130 return get_string('mysqliextensionisnotpresentinphp', 'install'); 131 } 132 return true; 133 } 134 135 /** 136 * Returns database family type - describes SQL dialect 137 * Note: can be used before connect() 138 * @return string db family name (mysql, postgres, mssql, oracle, etc.) 139 */ 140 public function get_dbfamily() { 141 return 'mysql'; 142 } 143 144 /** 145 * Returns more specific database driver type 146 * Note: can be used before connect() 147 * @return string db type mysqli, pgsql, oci, mssql, sqlsrv 148 */ 149 protected function get_dbtype() { 150 return 'mysqli'; 151 } 152 153 /** 154 * Returns general database library name 155 * Note: can be used before connect() 156 * @return string db type pdo, native 157 */ 158 protected function get_dblibrary() { 159 return 'native'; 160 } 161 162 /** 163 * Returns the current MySQL db engine. 164 * 165 * This is an ugly workaround for MySQL default engine problems, 166 * Moodle is designed to work best on ACID compliant databases 167 * with full transaction support. Do not use MyISAM. 168 * 169 * @return string or null MySQL engine name 170 */ 171 public function get_dbengine() { 172 if (isset($this->dboptions['dbengine'])) { 173 return $this->dboptions['dbengine']; 174 } 175 176 if ($this->external) { 177 return null; 178 } 179 180 $engine = null; 181 182 // Look for current engine of our config table (the first table that gets created), 183 // so that we create all tables with the same engine. 184 $sql = "SELECT engine 185 FROM INFORMATION_SCHEMA.TABLES 186 WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}config'"; 187 $this->query_start($sql, null, SQL_QUERY_AUX); 188 $result = $this->mysqli->query($sql); 189 $this->query_end($result); 190 if ($rec = $result->fetch_assoc()) { 191 // MySQL 8 BC: information_schema.* returns the fields in upper case. 192 $rec = array_change_key_case($rec, CASE_LOWER); 193 $engine = $rec['engine']; 194 } 195 $result->close(); 196 197 if ($engine) { 198 // Cache the result to improve performance. 199 $this->dboptions['dbengine'] = $engine; 200 return $engine; 201 } 202 203 // Get the default database engine. 204 $sql = "SELECT @@default_storage_engine engine"; 205 $this->query_start($sql, null, SQL_QUERY_AUX); 206 $result = $this->mysqli->query($sql); 207 $this->query_end($result); 208 if ($rec = $result->fetch_assoc()) { 209 $engine = $rec['engine']; 210 } 211 $result->close(); 212 213 if ($engine === 'MyISAM') { 214 // we really do not want MyISAM for Moodle, InnoDB or XtraDB is a reasonable defaults if supported 215 $sql = "SHOW STORAGE ENGINES"; 216 $this->query_start($sql, null, SQL_QUERY_AUX); 217 $result = $this->mysqli->query($sql); 218 $this->query_end($result); 219 $engines = array(); 220 while ($res = $result->fetch_assoc()) { 221 if ($res['Support'] === 'YES' or $res['Support'] === 'DEFAULT') { 222 $engines[$res['Engine']] = true; 223 } 224 } 225 $result->close(); 226 if (isset($engines['InnoDB'])) { 227 $engine = 'InnoDB'; 228 } 229 if (isset($engines['XtraDB'])) { 230 $engine = 'XtraDB'; 231 } 232 } 233 234 // Cache the result to improve performance. 235 $this->dboptions['dbengine'] = $engine; 236 return $engine; 237 } 238 239 /** 240 * Returns the current MySQL db collation. 241 * 242 * This is an ugly workaround for MySQL default collation problems. 243 * 244 * @return string or null MySQL collation name 245 */ 246 public function get_dbcollation() { 247 if (isset($this->dboptions['dbcollation'])) { 248 return $this->dboptions['dbcollation']; 249 } 250 } 251 252 /** 253 * Set 'dbcollation' option 254 * 255 * @return string|null $dbcollation 256 */ 257 private function detect_collation(): ?string { 258 if ($this->external) { 259 return null; 260 } 261 262 $collation = null; 263 264 // Look for current collation of our config table (the first table that gets created), 265 // so that we create all tables with the same collation. 266 $sql = "SELECT collation_name 267 FROM INFORMATION_SCHEMA.COLUMNS 268 WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}config' AND column_name = 'value'"; 269 $result = $this->mysqli->query($sql); 270 if ($rec = $result->fetch_assoc()) { 271 // MySQL 8 BC: information_schema.* returns the fields in upper case. 272 $rec = array_change_key_case($rec, CASE_LOWER); 273 $collation = $rec['collation_name']; 274 } 275 $result->close(); 276 277 278 if (!$collation) { 279 // Get the default database collation, but only if using UTF-8. 280 $sql = "SELECT @@collation_database"; 281 $result = $this->mysqli->query($sql); 282 if ($rec = $result->fetch_assoc()) { 283 if (strpos($rec['@@collation_database'], 'utf8_') === 0 || strpos($rec['@@collation_database'], 'utf8mb4_') === 0) { 284 $collation = $rec['@@collation_database']; 285 } 286 } 287 $result->close(); 288 } 289 290 if (!$collation) { 291 // We want only utf8 compatible collations. 292 $collation = null; 293 $sql = "SHOW COLLATION WHERE Collation LIKE 'utf8mb4\_%' AND Charset = 'utf8mb4'"; 294 $result = $this->mysqli->query($sql); 295 while ($res = $result->fetch_assoc()) { 296 $collation = $res['Collation']; 297 if (strtoupper($res['Default']) === 'YES') { 298 $collation = $res['Collation']; 299 break; 300 } 301 } 302 $result->close(); 303 } 304 305 // Cache the result to improve performance. 306 $this->dboptions['dbcollation'] = $collation; 307 return $collation; 308 } 309 310 /** 311 * Tests if the Antelope file format is still supported or it has been removed. 312 * When removed, only Barracuda file format is supported, given the XtraDB/InnoDB engine. 313 * 314 * @return bool True if the Antelope file format has been removed; otherwise, false. 315 */ 316 protected function is_antelope_file_format_no_more_supported() { 317 // Breaking change: Antelope file format support has been removed from both MySQL and MariaDB. 318 // The following InnoDB file format configuration parameters were deprecated and then removed: 319 // - innodb_file_format 320 // - innodb_file_format_check 321 // - innodb_file_format_max 322 // - innodb_large_prefix 323 // 1. MySQL: deprecated in 5.7.7 and removed 8.0.0+. 324 $ismysqlge8d0d0 = ($this->get_dbtype() == 'mysqli' || $this->get_dbtype() == 'auroramysql') && 325 version_compare($this->get_server_info()['version'], '8.0.0', '>='); 326 // 2. MariaDB: deprecated in 10.2.0 and removed 10.3.1+. 327 $ismariadbge10d3d1 = ($this->get_dbtype() == 'mariadb') && 328 version_compare($this->get_server_info()['version'], '10.3.1', '>='); 329 330 return $ismysqlge8d0d0 || $ismariadbge10d3d1; 331 } 332 333 /** 334 * Get the row format from the database schema. 335 * 336 * @param string $table 337 * @return string row_format name or null if not known or table does not exist. 338 */ 339 public function get_row_format($table = null) { 340 $rowformat = null; 341 if (isset($table)) { 342 $table = $this->mysqli->real_escape_string($table); 343 $sql = "SELECT row_format 344 FROM INFORMATION_SCHEMA.TABLES 345 WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}$table'"; 346 } else { 347 if ($this->is_antelope_file_format_no_more_supported()) { 348 // Breaking change: Antelope file format support has been removed, only Barracuda. 349 $dbengine = $this->get_dbengine(); 350 $supporteddbengines = array('InnoDB', 'XtraDB'); 351 if (in_array($dbengine, $supporteddbengines)) { 352 $rowformat = 'Barracuda'; 353 } 354 355 return $rowformat; 356 } 357 358 $sql = "SHOW VARIABLES LIKE 'innodb_file_format'"; 359 } 360 $this->query_start($sql, null, SQL_QUERY_AUX); 361 $result = $this->mysqli->query($sql); 362 $this->query_end($result); 363 if ($rec = $result->fetch_assoc()) { 364 // MySQL 8 BC: information_schema.* returns the fields in upper case. 365 $rec = array_change_key_case($rec, CASE_LOWER); 366 if (isset($table)) { 367 $rowformat = $rec['row_format']; 368 } else { 369 $rowformat = $rec['value']; 370 } 371 } 372 $result->close(); 373 374 return $rowformat; 375 } 376 377 /** 378 * Is this database compatible with compressed row format? 379 * This feature is necessary for support of large number of text 380 * columns in InnoDB/XtraDB database. 381 * 382 * @param bool $cached use cached result 383 * @return bool true if table can be created or changed to compressed row format. 384 */ 385 public function is_compressed_row_format_supported($cached = true) { 386 if ($cached and isset($this->compressedrowformatsupported)) { 387 return($this->compressedrowformatsupported); 388 } 389 390 $engine = strtolower($this->get_dbengine()); 391 $info = $this->get_server_info(); 392 393 if (version_compare($info['version'], '5.5.0') < 0) { 394 // MySQL 5.1 is not supported here because we cannot read the file format. 395 $this->compressedrowformatsupported = false; 396 397 } else if ($engine !== 'innodb' and $engine !== 'xtradb') { 398 // Other engines are not supported, most probably not compatible. 399 $this->compressedrowformatsupported = false; 400 401 } else if (!$this->is_file_per_table_enabled()) { 402 $this->compressedrowformatsupported = false; 403 404 } else if ($this->get_row_format() !== 'Barracuda') { 405 $this->compressedrowformatsupported = false; 406 407 } else if ($this->get_dbtype() === 'auroramysql') { 408 // Aurora MySQL doesn't support COMPRESSED and falls back to COMPACT if you try to use it. 409 $this->compressedrowformatsupported = false; 410 411 } else { 412 // All the tests passed, we can safely use ROW_FORMAT=Compressed in sql statements. 413 $this->compressedrowformatsupported = true; 414 } 415 416 return $this->compressedrowformatsupported; 417 } 418 419 /** 420 * Check the database to see if innodb_file_per_table is on. 421 * 422 * @return bool True if on otherwise false. 423 */ 424 public function is_file_per_table_enabled() { 425 if ($filepertable = $this->get_record_sql("SHOW VARIABLES LIKE 'innodb_file_per_table'")) { 426 if ($filepertable->value == 'ON') { 427 return true; 428 } 429 } 430 return false; 431 } 432 433 /** 434 * Check the database to see if innodb_large_prefix is on. 435 * 436 * @return bool True if on otherwise false. 437 */ 438 public function is_large_prefix_enabled() { 439 if ($this->is_antelope_file_format_no_more_supported()) { 440 // Breaking change: Antelope file format support has been removed, only Barracuda. 441 return true; 442 } 443 444 if ($largeprefix = $this->get_record_sql("SHOW VARIABLES LIKE 'innodb_large_prefix'")) { 445 if ($largeprefix->value == 'ON') { 446 return true; 447 } 448 } 449 return false; 450 } 451 452 /** 453 * Determine if the row format should be set to compressed, dynamic, or default. 454 * 455 * Terrible kludge. If we're using utf8mb4 AND we're using InnoDB, we need to specify row format to 456 * be either dynamic or compressed (default is compact) in order to allow for bigger indexes (MySQL 457 * errors #1709 and #1071). 458 * 459 * @param string $engine The database engine being used. Will be looked up if not supplied. 460 * @param string $collation The database collation to use. Will look up the current collation if not supplied. 461 * @return string An sql fragment to add to sql statements. 462 */ 463 public function get_row_format_sql($engine = null, $collation = null) { 464 465 if (!isset($engine)) { 466 $engine = $this->get_dbengine(); 467 } 468 $engine = strtolower($engine); 469 470 if (!isset($collation)) { 471 $collation = $this->get_dbcollation(); 472 } 473 474 $rowformat = ''; 475 if (($engine === 'innodb' || $engine === 'xtradb') && strpos($collation, 'utf8mb4_') === 0) { 476 if ($this->is_compressed_row_format_supported()) { 477 $rowformat = "ROW_FORMAT=Compressed"; 478 } else { 479 $rowformat = "ROW_FORMAT=Dynamic"; 480 } 481 } 482 return $rowformat; 483 } 484 485 /** 486 * Returns localised database type name 487 * Note: can be used before connect() 488 * @return string 489 */ 490 public function get_name() { 491 return get_string('nativemysqli', 'install'); 492 } 493 494 /** 495 * Returns localised database configuration help. 496 * Note: can be used before connect() 497 * @return string 498 */ 499 public function get_configuration_help() { 500 return get_string('nativemysqlihelp', 'install'); 501 } 502 503 /** 504 * Diagnose database and tables, this function is used 505 * to verify database and driver settings, db engine types, etc. 506 * 507 * @return string null means everything ok, string means problem found. 508 */ 509 public function diagnose() { 510 $sloppymyisamfound = false; 511 $prefix = str_replace('_', '\\_', $this->prefix); 512 $sql = "SELECT COUNT('x') 513 FROM INFORMATION_SCHEMA.TABLES 514 WHERE table_schema = DATABASE() 515 AND table_name LIKE BINARY '$prefix%' 516 AND Engine = 'MyISAM'"; 517 $this->query_start($sql, null, SQL_QUERY_AUX); 518 $result = $this->mysqli->query($sql); 519 $this->query_end($result); 520 if ($result) { 521 if ($arr = $result->fetch_assoc()) { 522 $count = reset($arr); 523 if ($count) { 524 $sloppymyisamfound = true; 525 } 526 } 527 $result->close(); 528 } 529 530 if ($sloppymyisamfound) { 531 return get_string('myisamproblem', 'error'); 532 } else { 533 return null; 534 } 535 } 536 537 /** 538 * Connect to db 539 * @param string $dbhost The database host. 540 * @param string $dbuser The database username. 541 * @param string $dbpass The database username's password. 542 * @param string $dbname The name of the database being connected to.e 543 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used 544 * @param array $dboptions driver specific options 545 * @return bool success 546 * @throws moodle_exception 547 * @throws dml_connection_exception if error 548 */ 549 public function raw_connect(string $dbhost, string $dbuser, string $dbpass, string $dbname, $prefix, array $dboptions=null): bool { 550 $driverstatus = $this->driver_installed(); 551 552 if ($driverstatus !== true) { 553 throw new dml_exception('dbdriverproblem', $driverstatus); 554 } 555 556 $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions); 557 558 // The dbsocket option is used ONLY if host is null or 'localhost'. 559 // You can not disable it because it is always tried if dbhost is 'localhost'. 560 if (!empty($this->dboptions['dbsocket']) 561 and (strpos($this->dboptions['dbsocket'], '/') !== false or strpos($this->dboptions['dbsocket'], '\\') !== false)) { 562 $dbsocket = $this->dboptions['dbsocket']; 563 } else { 564 $dbsocket = ini_get('mysqli.default_socket'); 565 } 566 if (empty($this->dboptions['dbport'])) { 567 $dbport = (int)ini_get('mysqli.default_port'); 568 } else { 569 $dbport = (int)$this->dboptions['dbport']; 570 } 571 // verify ini.get does not return nonsense 572 if (empty($dbport)) { 573 $dbport = 3306; 574 } 575 if ($dbhost and !empty($this->dboptions['dbpersist'])) { 576 $dbhost = "p:$dbhost"; 577 } 578 579 // We want to keep exceptions out from the native driver. 580 // TODO: See MDL-75761 for future improvements. 581 mysqli_report(MYSQLI_REPORT_OFF); // Disable reporting (default before PHP 8.1). 582 583 $this->mysqli = mysqli_init(); 584 if (!empty($this->dboptions['connecttimeout'])) { 585 $this->mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, $this->dboptions['connecttimeout']); 586 } 587 588 $flags = 0; 589 if ($this->dboptions['clientcompress'] ?? false) { 590 $flags |= MYSQLI_CLIENT_COMPRESS; 591 } 592 if (isset($this->dboptions['ssl'])) { 593 $sslmode = $this->dboptions['ssl']; 594 if (!in_array($sslmode, self::$sslmodes, true)) { 595 throw new moodle_exception("Invalid 'dboptions''ssl' value '$sslmode'"); 596 } 597 $flags |= MYSQLI_CLIENT_SSL; 598 if ($sslmode === 'verify-full') { 599 $flags |= MYSQLI_CLIENT_SSL_VERIFY_SERVER_CERT; 600 } 601 } 602 603 $conn = null; 604 $dberr = null; 605 try { 606 // real_connect() is doing things we don't expext. 607 $conn = @$this->mysqli->real_connect($dbhost, $dbuser, $dbpass, $dbname, $dbport, $dbsocket, $flags); 608 } catch (\Exception $e) { 609 $dberr = "$e"; 610 } 611 if (!$conn) { 612 $dberr = $dberr ?: "{$this->mysqli->connect_error} ({$this->mysqli->connect_errno})"; 613 $this->mysqli = null; 614 throw new dml_connection_exception($dberr); 615 } 616 617 // Disable logging until we are fully setup. 618 $this->query_log_prevent(); 619 620 if (isset($dboptions['dbcollation'])) { 621 $collation = $this->dboptions['dbcollation'] = $dboptions['dbcollation']; 622 } else { 623 $collation = $this->detect_collation(); 624 } 625 $collationinfo = explode('_', $collation); 626 $charset = reset($collationinfo); 627 628 $this->mysqli->set_charset($charset); 629 630 // If available, enforce strict mode for the session. That guaranties 631 // standard behaviour under some situations, avoiding some MySQL nasty 632 // habits like truncating data or performing some transparent cast losses. 633 // With strict mode enforced, Moodle DB layer will be consistently throwing 634 // the corresponding exceptions as expected. 635 $si = $this->get_server_info(); 636 if (version_compare($si['version'], '5.0.2', '>=')) { 637 $sql = "SET SESSION sql_mode = 'STRICT_ALL_TABLES'"; 638 $result = $this->mysqli->query($sql); 639 } 640 641 // We can enable logging now. 642 $this->query_log_allow(); 643 644 // Connection stabilised and configured, going to instantiate the temptables controller 645 $this->temptables = new mysqli_native_moodle_temptables($this); 646 647 return true; 648 } 649 650 /** 651 * Close database connection and release all resources 652 * and memory (especially circular memory references). 653 * Do NOT use connect() again, create a new instance if needed. 654 */ 655 public function dispose() { 656 parent::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection 657 if ($this->mysqli) { 658 $this->mysqli->close(); 659 $this->mysqli = null; 660 } 661 } 662 663 /** 664 * Gets db handle currently used with queries 665 * @return resource 666 */ 667 protected function get_db_handle() { 668 return $this->mysqli; 669 } 670 671 /** 672 * Sets db handle to be used with subsequent queries 673 * @param resource $dbh 674 * @return void 675 */ 676 protected function set_db_handle($dbh): void { 677 $this->mysqli = $dbh; 678 } 679 680 /** 681 * Check if The query qualifies for readonly connection execution 682 * Logging queries are exempt, those are write operations that circumvent 683 * standard query_start/query_end paths. 684 * @param int $type type of query 685 * @param string $sql 686 * @return bool 687 */ 688 protected function can_use_readonly(int $type, string $sql): bool { 689 // ... *_LOCK queries always go to master. 690 if (preg_match('/\b(GET|RELEASE)_LOCK/i', $sql)) { 691 return false; 692 } 693 694 return $this->read_slave_can_use_readonly($type, $sql); 695 } 696 697 /** 698 * Returns the version of the MySQL server, as reported by the PHP client connection. 699 * 700 * Wrap $this->mysqli->server_info to improve testing strategy. 701 * 702 * @return string A string representing the version of the MySQL server that the MySQLi extension is connected to. 703 */ 704 protected function get_mysqli_server_info(): string { 705 return $this->mysqli->server_info; 706 } 707 708 /** 709 * Returns the version of the MySQL server, as reported by 'SELECT VERSION()' query. 710 * 711 * @return string A string that indicates the MySQL server version. 712 * @throws dml_read_exception If the execution of 'SELECT VERSION()' query will fail. 713 */ 714 protected function get_version_from_db(): string { 715 $version = null; 716 // Query the DB server for the server version. 717 $sql = "SELECT VERSION() version;"; 718 try { 719 $result = $this->mysqli->query($sql); 720 if ($result) { 721 if ($row = $result->fetch_assoc()) { 722 $version = $row['version']; 723 } 724 $result->close(); 725 unset($row); 726 } 727 } catch (\Throwable $e) { // Exceptions in case of MYSQLI_REPORT_STRICT. 728 // It looks like we've an issue out of the expected boolean 'false' result above. 729 throw new dml_read_exception($e->getMessage(), $sql); 730 } 731 if (empty($version)) { 732 // Exception dml_read_exception usually reports raw mysqli errors i.e. not localised by Moodle. 733 throw new dml_read_exception("Unable to read the DB server version.", $sql); 734 } 735 736 return $version; 737 } 738 739 /** 740 * Returns whether $CFG->dboptions['versionfromdb'] has been set to boolean `true`. 741 * 742 * @return bool True if $CFG->dboptions['versionfromdb'] has been set to boolean `true`. Otherwise, `false`. 743 */ 744 protected function should_db_version_be_read_from_db(): bool { 745 if (!empty($this->dboptions['versionfromdb'])) { 746 return true; 747 } 748 749 return false; 750 } 751 752 /** 753 * Returns database server info array. 754 * @return array Array containing 'description' and 'version' info. 755 * @throws dml_read_exception If the execution of 'SELECT VERSION()' query will fail. 756 */ 757 public function get_server_info() { 758 $version = $this->serverversion; 759 if (empty($version)) { 760 $version = $this->get_mysqli_server_info(); 761 // The version returned by the PHP client could not be the actual DB server version. 762 // For example in MariaDB, it was prefixed by the RPL_VERSION_HACK, "5.5.5-" (MDEV-4088), starting from 10.x, 763 // when not using an authentication plug-in. 764 // Strip the RPL_VERSION_HACK prefix off - it will be "always" there in MariaDB until MDEV-28910 will be implemented. 765 $version = str_replace('5.5.5-', '', $version); 766 767 // Should we use the VERSION function to get the actual DB version instead of the PHP client version above? 768 if ($this->should_db_version_be_read_from_db()) { 769 // Try to query the actual version of the target database server: indeed some cloud providers, e.g. Azure, 770 // put a gateway in front of the actual instance which reports its own version to the PHP client 771 // and it doesn't represent the actual version of the DB server the PHP client is connected to. 772 // Refs: 773 // - https://learn.microsoft.com/en-us/azure/mariadb/concepts-supported-versions 774 // - https://learn.microsoft.com/en-us/azure/mysql/single-server/concepts-connect-to-a-gateway-node . 775 // Reset the version returned by the PHP client with the actual DB version reported by 'VERSION' function. 776 $version = $this->get_version_from_db(); 777 } 778 779 // The version here starts with the following naming scheme: 'X.Y.Z[-<suffix>]'. 780 // Example: in MariaDB at least one suffix is "always" there, hardcoded in 'mysql_version.h.in': 781 // #define MYSQL_SERVER_VERSION "@VERSION@-MariaDB" 782 // MariaDB and MySQL server version could have extra suffixes too, set by the compilation environment, 783 // e.g. '-debug', '-embedded', '-log' or any other vendor specific suffix (e.g. build information). 784 // Strip out any suffix. 785 $parts = explode('-', $version, 2); 786 // Finally, keep just major, minor and patch versions (X.Y.Z) from the reported DB server version. 787 $this->serverversion = $parts[0]; 788 } 789 790 return [ 791 'description' => $this->get_mysqli_server_info(), 792 'version' => $this->serverversion 793 ]; 794 } 795 796 /** 797 * Returns supported query parameter types 798 * @return int bitmask of accepted SQL_PARAMS_* 799 */ 800 protected function allowed_param_types() { 801 return SQL_PARAMS_QM; 802 } 803 804 /** 805 * Returns last error reported by database engine. 806 * @return string error message 807 */ 808 public function get_last_error() { 809 return $this->mysqli->error; 810 } 811 812 /** 813 * Return tables in database WITHOUT current prefix 814 * @param bool $usecache if true, returns list of cached tables. 815 * @return array of table names in lowercase and without prefix 816 */ 817 public function get_tables($usecache=true) { 818 if ($usecache and $this->tables !== null) { 819 return $this->tables; 820 } 821 $this->tables = array(); 822 $prefix = str_replace('_', '\\_', $this->prefix); 823 $sql = "SHOW TABLES LIKE '$prefix%'"; 824 $this->query_start($sql, null, $usecache ? SQL_QUERY_AUX_READONLY : SQL_QUERY_AUX); 825 $result = $this->mysqli->query($sql); 826 $this->query_end($result); 827 $len = strlen($this->prefix); 828 if ($result) { 829 while ($arr = $result->fetch_assoc()) { 830 $tablename = reset($arr); 831 $tablename = substr($tablename, $len); 832 $this->tables[$tablename] = $tablename; 833 } 834 $result->close(); 835 } 836 837 // Add the currently available temptables 838 $this->tables = array_merge($this->tables, $this->temptables->get_temptables()); 839 return $this->tables; 840 } 841 842 /** 843 * Return table indexes - everything lowercased. 844 * @param string $table The table we want to get indexes from. 845 * @return array An associative array of indexes containing 'unique' flag and 'columns' being indexed 846 */ 847 public function get_indexes($table) { 848 $indexes = array(); 849 $fixedtable = $this->fix_table_name($table); 850 $sql = "SHOW INDEXES FROM $fixedtable"; 851 $this->query_start($sql, null, SQL_QUERY_AUX_READONLY); 852 $result = $this->mysqli->query($sql); 853 try { 854 $this->query_end($result); 855 } catch (dml_read_exception $e) { 856 return $indexes; // table does not exist - no indexes... 857 } 858 if ($result) { 859 while ($res = $result->fetch_object()) { 860 if ($res->Key_name === 'PRIMARY') { 861 continue; 862 } 863 if (!isset($indexes[$res->Key_name])) { 864 $indexes[$res->Key_name] = array('unique'=>empty($res->Non_unique), 'columns'=>array()); 865 } 866 $indexes[$res->Key_name]['columns'][$res->Seq_in_index-1] = $res->Column_name; 867 } 868 $result->close(); 869 } 870 return $indexes; 871 } 872 873 /** 874 * Fetches detailed information about columns in table. 875 * 876 * @param string $table name 877 * @return database_column_info[] array of database_column_info objects indexed with column names 878 */ 879 protected function fetch_columns(string $table): array { 880 $structure = array(); 881 882 $sql = "SELECT column_name, data_type, character_maximum_length, numeric_precision, 883 numeric_scale, is_nullable, column_type, column_default, column_key, extra 884 FROM information_schema.columns 885 WHERE table_name = '" . $this->prefix.$table . "' 886 AND table_schema = '" . $this->dbname . "' 887 ORDER BY ordinal_position"; 888 $this->query_start($sql, null, SQL_QUERY_AUX_READONLY); 889 $result = $this->mysqli->query($sql); 890 $this->query_end(true); // Don't want to throw anything here ever. MDL-30147 891 892 if ($result === false) { 893 return array(); 894 } 895 896 if ($result->num_rows > 0) { 897 // standard table exists 898 while ($rawcolumn = $result->fetch_assoc()) { 899 // MySQL 8 BC: information_schema.* returns the fields in upper case. 900 $rawcolumn = array_change_key_case($rawcolumn, CASE_LOWER); 901 $info = (object)$this->get_column_info((object)$rawcolumn); 902 $structure[$info->name] = new database_column_info($info); 903 } 904 $result->close(); 905 906 } else { 907 // temporary tables are not in information schema, let's try it the old way 908 $result->close(); 909 $fixedtable = $this->fix_table_name($table); 910 $sql = "SHOW COLUMNS FROM $fixedtable"; 911 $this->query_start($sql, null, SQL_QUERY_AUX_READONLY); 912 $result = $this->mysqli->query($sql); 913 $this->query_end(true); 914 if ($result === false) { 915 return array(); 916 } 917 while ($rawcolumn = $result->fetch_assoc()) { 918 $rawcolumn = (object)array_change_key_case($rawcolumn, CASE_LOWER); 919 $rawcolumn->column_name = $rawcolumn->field; unset($rawcolumn->field); 920 $rawcolumn->column_type = $rawcolumn->type; unset($rawcolumn->type); 921 $rawcolumn->character_maximum_length = null; 922 $rawcolumn->numeric_precision = null; 923 $rawcolumn->numeric_scale = null; 924 $rawcolumn->is_nullable = $rawcolumn->null; unset($rawcolumn->null); 925 $rawcolumn->column_default = $rawcolumn->default; unset($rawcolumn->default); 926 $rawcolumn->column_key = $rawcolumn->key; unset($rawcolumn->key); 927 928 if (preg_match('/(enum|varchar)\((\d+)\)/i', $rawcolumn->column_type, $matches)) { 929 $rawcolumn->data_type = $matches[1]; 930 $rawcolumn->character_maximum_length = $matches[2]; 931 932 } else if (preg_match('/([a-z]*int[a-z]*)\((\d+)\)/i', $rawcolumn->column_type, $matches)) { 933 $rawcolumn->data_type = $matches[1]; 934 $rawcolumn->numeric_precision = $matches[2]; 935 $rawcolumn->max_length = $rawcolumn->numeric_precision; 936 937 $type = strtoupper($matches[1]); 938 if ($type === 'BIGINT') { 939 $maxlength = 18; 940 } else if ($type === 'INT' or $type === 'INTEGER') { 941 $maxlength = 9; 942 } else if ($type === 'MEDIUMINT') { 943 $maxlength = 6; 944 } else if ($type === 'SMALLINT') { 945 $maxlength = 4; 946 } else if ($type === 'TINYINT') { 947 $maxlength = 2; 948 } else { 949 // This should not happen. 950 $maxlength = 0; 951 } 952 if ($maxlength < $rawcolumn->max_length) { 953 $rawcolumn->max_length = $maxlength; 954 } 955 956 } else if (preg_match('/(decimal)\((\d+),(\d+)\)/i', $rawcolumn->column_type, $matches)) { 957 $rawcolumn->data_type = $matches[1]; 958 $rawcolumn->numeric_precision = $matches[2]; 959 $rawcolumn->numeric_scale = $matches[3]; 960 961 } else if (preg_match('/(double|float)(\((\d+),(\d+)\))?/i', $rawcolumn->column_type, $matches)) { 962 $rawcolumn->data_type = $matches[1]; 963 $rawcolumn->numeric_precision = isset($matches[3]) ? $matches[3] : null; 964 $rawcolumn->numeric_scale = isset($matches[4]) ? $matches[4] : null; 965 966 } else if (preg_match('/([a-z]*text)/i', $rawcolumn->column_type, $matches)) { 967 $rawcolumn->data_type = $matches[1]; 968 $rawcolumn->character_maximum_length = -1; // unknown 969 970 } else if (preg_match('/([a-z]*blob)/i', $rawcolumn->column_type, $matches)) { 971 $rawcolumn->data_type = $matches[1]; 972 973 } else { 974 $rawcolumn->data_type = $rawcolumn->column_type; 975 } 976 977 $info = $this->get_column_info($rawcolumn); 978 $structure[$info->name] = new database_column_info($info); 979 } 980 $result->close(); 981 } 982 983 return $structure; 984 } 985 986 /** 987 * Indicates whether column information retrieved from `information_schema.columns` has default values quoted or not. 988 * @return boolean True when default values are quoted (breaking change); otherwise, false. 989 */ 990 protected function has_breaking_change_quoted_defaults() { 991 return false; 992 } 993 994 /** 995 * Indicates whether SQL_MODE default value has changed in a not backward compatible way. 996 * @return boolean True when SQL_MODE breaks BC; otherwise, false. 997 */ 998 public function has_breaking_change_sqlmode() { 999 return false; 1000 } 1001 1002 /** 1003 * Returns moodle column info for raw column from information schema. 1004 * @param stdClass $rawcolumn 1005 * @return stdClass standardised colum info 1006 */ 1007 private function get_column_info(stdClass $rawcolumn) { 1008 $rawcolumn = (object)$rawcolumn; 1009 $info = new stdClass(); 1010 $info->name = $rawcolumn->column_name; 1011 $info->type = $rawcolumn->data_type; 1012 $info->meta_type = $this->mysqltype2moodletype($rawcolumn->data_type); 1013 if ($this->has_breaking_change_quoted_defaults()) { 1014 $info->default_value = is_null($rawcolumn->column_default) ? null : trim($rawcolumn->column_default, "'"); 1015 if ($info->default_value === 'NULL') { 1016 $info->default_value = null; 1017 } 1018 } else { 1019 $info->default_value = $rawcolumn->column_default; 1020 } 1021 $info->has_default = !is_null($info->default_value); 1022 $info->not_null = ($rawcolumn->is_nullable === 'NO'); 1023 $info->primary_key = ($rawcolumn->column_key === 'PRI'); 1024 $info->binary = false; 1025 $info->unsigned = null; 1026 $info->auto_increment = false; 1027 $info->unique = null; 1028 $info->scale = null; 1029 1030 if ($info->meta_type === 'C') { 1031 $info->max_length = $rawcolumn->character_maximum_length; 1032 1033 } else if ($info->meta_type === 'I') { 1034 if ($info->primary_key) { 1035 $info->meta_type = 'R'; 1036 $info->unique = true; 1037 } 1038 // Return number of decimals, not bytes here. 1039 $info->max_length = $rawcolumn->numeric_precision; 1040 if (preg_match('/([a-z]*int[a-z]*)\((\d+)\)/i', $rawcolumn->column_type, $matches)) { 1041 $type = strtoupper($matches[1]); 1042 if ($type === 'BIGINT') { 1043 $maxlength = 18; 1044 } else if ($type === 'INT' or $type === 'INTEGER') { 1045 $maxlength = 9; 1046 } else if ($type === 'MEDIUMINT') { 1047 $maxlength = 6; 1048 } else if ($type === 'SMALLINT') { 1049 $maxlength = 4; 1050 } else if ($type === 'TINYINT') { 1051 $maxlength = 2; 1052 } else { 1053 // This should not happen. 1054 $maxlength = 0; 1055 } 1056 // It is possible that display precision is different from storage type length, 1057 // always use the smaller value to make sure our data fits. 1058 if ($maxlength < $info->max_length) { 1059 $info->max_length = $maxlength; 1060 } 1061 } 1062 $info->unsigned = (stripos($rawcolumn->column_type, 'unsigned') !== false); 1063 $info->auto_increment= (strpos($rawcolumn->extra, 'auto_increment') !== false); 1064 1065 } else if ($info->meta_type === 'N') { 1066 $info->max_length = $rawcolumn->numeric_precision; 1067 $info->scale = $rawcolumn->numeric_scale; 1068 $info->unsigned = (stripos($rawcolumn->column_type, 'unsigned') !== false); 1069 1070 } else if ($info->meta_type === 'X') { 1071 if ("$rawcolumn->character_maximum_length" === '4294967295') { // watch out for PHP max int limits! 1072 // means maximum moodle size for text column, in other drivers it may also mean unknown size 1073 $info->max_length = -1; 1074 } else { 1075 $info->max_length = $rawcolumn->character_maximum_length; 1076 } 1077 $info->primary_key = false; 1078 1079 } else if ($info->meta_type === 'B') { 1080 $info->max_length = -1; 1081 $info->primary_key = false; 1082 $info->binary = true; 1083 } 1084 1085 return $info; 1086 } 1087 1088 /** 1089 * Normalise column type. 1090 * @param string $mysql_type 1091 * @return string one character 1092 * @throws dml_exception 1093 */ 1094 private function mysqltype2moodletype($mysql_type) { 1095 $type = null; 1096 1097 switch(strtoupper($mysql_type)) { 1098 case 'BIT': 1099 $type = 'L'; 1100 break; 1101 1102 case 'TINYINT': 1103 case 'SMALLINT': 1104 case 'MEDIUMINT': 1105 case 'INT': 1106 case 'INTEGER': 1107 case 'BIGINT': 1108 $type = 'I'; 1109 break; 1110 1111 case 'FLOAT': 1112 case 'DOUBLE': 1113 case 'DECIMAL': 1114 $type = 'N'; 1115 break; 1116 1117 case 'CHAR': 1118 case 'ENUM': 1119 case 'SET': 1120 case 'VARCHAR': 1121 $type = 'C'; 1122 break; 1123 1124 case 'TINYTEXT': 1125 case 'TEXT': 1126 case 'MEDIUMTEXT': 1127 case 'LONGTEXT': 1128 $type = 'X'; 1129 break; 1130 1131 case 'BINARY': 1132 case 'VARBINARY': 1133 case 'BLOB': 1134 case 'TINYBLOB': 1135 case 'MEDIUMBLOB': 1136 case 'LONGBLOB': 1137 $type = 'B'; 1138 break; 1139 1140 case 'DATE': 1141 case 'TIME': 1142 case 'DATETIME': 1143 case 'TIMESTAMP': 1144 case 'YEAR': 1145 $type = 'D'; 1146 break; 1147 } 1148 1149 if (!$type) { 1150 throw new dml_exception('invalidmysqlnativetype', $mysql_type); 1151 } 1152 return $type; 1153 } 1154 1155 /** 1156 * Normalise values based in RDBMS dependencies (booleans, LOBs...) 1157 * 1158 * @param database_column_info $column column metadata corresponding with the value we are going to normalise 1159 * @param mixed $value value we are going to normalise 1160 * @return mixed the normalised value 1161 */ 1162 protected function normalise_value($column, $value) { 1163 $this->detect_objects($value); 1164 1165 if (is_bool($value)) { // Always, convert boolean to int 1166 $value = (int)$value; 1167 1168 } else if ($value === '') { 1169 if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') { 1170 $value = 0; // prevent '' problems in numeric fields 1171 } 1172 // Any float value being stored in varchar or text field is converted to string to avoid 1173 // any implicit conversion by MySQL 1174 } else if (is_float($value) and ($column->meta_type == 'C' or $column->meta_type == 'X')) { 1175 $value = "$value"; 1176 } 1177 return $value; 1178 } 1179 1180 /** 1181 * Is this database compatible with utf8? 1182 * @return bool 1183 */ 1184 public function setup_is_unicodedb() { 1185 // All new tables are created with this collation, we just have to make sure it is utf8 compatible, 1186 // if config table already exists it has this collation too. 1187 $collation = $this->get_dbcollation(); 1188 1189 $collationinfo = explode('_', $collation); 1190 $charset = reset($collationinfo); 1191 1192 $sql = "SHOW COLLATION WHERE Collation ='$collation' AND Charset = '$charset'"; 1193 $this->query_start($sql, null, SQL_QUERY_AUX_READONLY); 1194 $result = $this->mysqli->query($sql); 1195 $this->query_end($result); 1196 if ($result->fetch_assoc()) { 1197 $return = true; 1198 } else { 1199 $return = false; 1200 } 1201 $result->close(); 1202 1203 return $return; 1204 } 1205 1206 /** 1207 * Do NOT use in code, to be used by database_manager only! 1208 * @param string|array $sql query 1209 * @param array|null $tablenames an array of xmldb table names affected by this request. 1210 * @return bool true 1211 * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors. 1212 */ 1213 public function change_database_structure($sql, $tablenames = null) { 1214 $this->get_manager(); // Includes DDL exceptions classes ;-) 1215 if (is_array($sql)) { 1216 $sql = implode("\n;\n", $sql); 1217 } 1218 1219 try { 1220 $this->query_start($sql, null, SQL_QUERY_STRUCTURE); 1221 $result = $this->mysqli->multi_query($sql); 1222 if ($result === false) { 1223 $this->query_end(false); 1224 } 1225 while ($this->mysqli->more_results()) { 1226 $result = $this->mysqli->next_result(); 1227 if ($result === false) { 1228 $this->query_end(false); 1229 } 1230 } 1231 $this->query_end(true); 1232 } catch (ddl_change_structure_exception $e) { 1233 while (@$this->mysqli->more_results()) { 1234 @$this->mysqli->next_result(); 1235 } 1236 $this->reset_caches($tablenames); 1237 throw $e; 1238 } 1239 1240 $this->reset_caches($tablenames); 1241 return true; 1242 } 1243 1244 /** 1245 * Very ugly hack which emulates bound parameters in queries 1246 * because prepared statements do not use query cache. 1247 */ 1248 protected function emulate_bound_params($sql, array $params=null) { 1249 if (empty($params)) { 1250 return $sql; 1251 } 1252 // ok, we have verified sql statement with ? and correct number of params 1253 $parts = array_reverse(explode('?', $sql)); 1254 $return = array_pop($parts); 1255 foreach ($params as $param) { 1256 if (is_bool($param)) { 1257 $return .= (int)$param; 1258 } else if (is_null($param)) { 1259 $return .= 'NULL'; 1260 } else if (is_number($param)) { 1261 $return .= "'".$param."'"; // we have to always use strings because mysql is using weird automatic int casting 1262 } else if (is_float($param)) { 1263 $return .= $param; 1264 } else { 1265 $param = $this->mysqli->real_escape_string($param); 1266 $return .= "'$param'"; 1267 } 1268 $return .= array_pop($parts); 1269 } 1270 return $return; 1271 } 1272 1273 /** 1274 * Execute general sql query. Should be used only when no other method suitable. 1275 * Do NOT use this to make changes in db structure, use database_manager methods instead! 1276 * @param string $sql query 1277 * @param array $params query parameters 1278 * @return bool true 1279 * @throws dml_exception A DML specific exception is thrown for any errors. 1280 */ 1281 public function execute($sql, array $params=null) { 1282 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1283 1284 if (strpos($sql, ';') !== false) { 1285 throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!'); 1286 } 1287 1288 $rawsql = $this->emulate_bound_params($sql, $params); 1289 1290 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1291 $result = $this->mysqli->query($rawsql); 1292 $this->query_end($result); 1293 1294 if ($result === true) { 1295 return true; 1296 1297 } else { 1298 $result->close(); 1299 return true; 1300 } 1301 } 1302 1303 /** 1304 * Get a number of records as a moodle_recordset using a SQL statement. 1305 * 1306 * Since this method is a little less readable, use of it should be restricted to 1307 * code where it's possible there might be large datasets being returned. For known 1308 * small datasets use get_records_sql - it leads to simpler code. 1309 * 1310 * The return type is like: 1311 * @see function get_recordset. 1312 * 1313 * @param string $sql the SQL select query to execute. 1314 * @param array $params array of sql parameters 1315 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). 1316 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1317 * @return moodle_recordset instance 1318 * @throws dml_exception A DML specific exception is thrown for any errors. 1319 */ 1320 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { 1321 1322 list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum); 1323 1324 if ($limitfrom or $limitnum) { 1325 if ($limitnum < 1) { 1326 $limitnum = "18446744073709551615"; 1327 } 1328 $sql .= " LIMIT $limitfrom, $limitnum"; 1329 } 1330 1331 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1332 $rawsql = $this->emulate_bound_params($sql, $params); 1333 1334 $this->query_start($sql, $params, SQL_QUERY_SELECT); 1335 // no MYSQLI_USE_RESULT here, it would block write ops on affected tables 1336 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT); 1337 $this->query_end($result); 1338 1339 return $this->create_recordset($result); 1340 } 1341 1342 /** 1343 * Get all records from a table. 1344 * 1345 * This method works around potential memory problems and may improve performance, 1346 * this method may block access to table until the recordset is closed. 1347 * 1348 * @param string $table Name of database table. 1349 * @return moodle_recordset A moodle_recordset instance {@link function get_recordset}. 1350 * @throws dml_exception A DML specific exception is thrown for any errors. 1351 */ 1352 public function export_table_recordset($table) { 1353 $sql = $this->fix_table_names("SELECT * FROM {{$table}}"); 1354 1355 $this->query_start($sql, array(), SQL_QUERY_SELECT); 1356 // MYSQLI_STORE_RESULT may eat all memory for large tables, unfortunately MYSQLI_USE_RESULT blocks other queries. 1357 $result = $this->mysqli->query($sql, MYSQLI_USE_RESULT); 1358 $this->query_end($result); 1359 1360 return $this->create_recordset($result); 1361 } 1362 1363 protected function create_recordset($result) { 1364 return new mysqli_native_moodle_recordset($result); 1365 } 1366 1367 /** 1368 * Get a number of records as an array of objects using a SQL statement. 1369 * 1370 * Return value is like: 1371 * @see function get_records. 1372 * 1373 * @param string $sql the SQL select query to execute. The first column of this SELECT statement 1374 * must be a unique value (usually the 'id' field), as it will be used as the key of the 1375 * returned array. 1376 * @param array $params array of sql parameters 1377 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set). 1378 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set). 1379 * @return array of objects, or empty array if no records were found 1380 * @throws dml_exception A DML specific exception is thrown for any errors. 1381 */ 1382 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) { 1383 1384 list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum); 1385 1386 if ($limitfrom or $limitnum) { 1387 if ($limitnum < 1) { 1388 $limitnum = "18446744073709551615"; 1389 } 1390 $sql .= " LIMIT $limitfrom, $limitnum"; 1391 } 1392 1393 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1394 $rawsql = $this->emulate_bound_params($sql, $params); 1395 1396 $this->query_start($sql, $params, SQL_QUERY_SELECT); 1397 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT); 1398 $this->query_end($result); 1399 1400 $return = array(); 1401 1402 while($row = $result->fetch_assoc()) { 1403 $row = array_change_key_case($row, CASE_LOWER); 1404 $id = reset($row); 1405 if (isset($return[$id])) { 1406 $colname = key($row); 1407 debugging("Did you remember to make the first column something unique in your call to get_records? Duplicate value '$id' found in column '$colname'.", DEBUG_DEVELOPER); 1408 } 1409 $return[$id] = (object)$row; 1410 } 1411 $result->close(); 1412 1413 return $return; 1414 } 1415 1416 /** 1417 * Selects records and return values (first field) as an array using a SQL statement. 1418 * 1419 * @param string $sql The SQL query 1420 * @param array $params array of sql parameters 1421 * @return array of values 1422 * @throws dml_exception A DML specific exception is thrown for any errors. 1423 */ 1424 public function get_fieldset_sql($sql, array $params=null) { 1425 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1426 $rawsql = $this->emulate_bound_params($sql, $params); 1427 1428 $this->query_start($sql, $params, SQL_QUERY_SELECT); 1429 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT); 1430 $this->query_end($result); 1431 1432 $return = array(); 1433 1434 while($row = $result->fetch_assoc()) { 1435 $return[] = reset($row); 1436 } 1437 $result->close(); 1438 1439 return $return; 1440 } 1441 1442 /** 1443 * Insert new record into database, as fast as possible, no safety checks, lobs not supported. 1444 * @param string $table name 1445 * @param mixed $params data record as object or array 1446 * @param bool $returnit return it of inserted record 1447 * @param bool $bulk true means repeated inserts expected 1448 * @param bool $customsequence true if 'id' included in $params, disables $returnid 1449 * @return bool|int true or new id 1450 * @throws dml_exception A DML specific exception is thrown for any errors. 1451 */ 1452 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) { 1453 if (!is_array($params)) { 1454 $params = (array)$params; 1455 } 1456 1457 if ($customsequence) { 1458 if (!isset($params['id'])) { 1459 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.'); 1460 } 1461 $returnid = false; 1462 } else { 1463 unset($params['id']); 1464 } 1465 1466 if (empty($params)) { 1467 throw new coding_exception('moodle_database::insert_record_raw() no fields found.'); 1468 } 1469 1470 $fields = implode(',', array_keys($params)); 1471 $qms = array_fill(0, count($params), '?'); 1472 $qms = implode(',', $qms); 1473 $fixedtable = $this->fix_table_name($table); 1474 $sql = "INSERT INTO $fixedtable ($fields) VALUES($qms)"; 1475 1476 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1477 $rawsql = $this->emulate_bound_params($sql, $params); 1478 1479 $this->query_start($sql, $params, SQL_QUERY_INSERT); 1480 $result = $this->mysqli->query($rawsql); 1481 $id = @$this->mysqli->insert_id; // must be called before query_end() which may insert log into db 1482 $this->query_end($result); 1483 1484 if (!$customsequence and !$id) { 1485 throw new dml_write_exception('unknown error fetching inserted id'); 1486 } 1487 1488 if (!$returnid) { 1489 return true; 1490 } else { 1491 return (int)$id; 1492 } 1493 } 1494 1495 /** 1496 * Insert a record into a table and return the "id" field if required. 1497 * 1498 * Some conversions and safety checks are carried out. Lobs are supported. 1499 * If the return ID isn't required, then this just reports success as true/false. 1500 * $data is an object containing needed data 1501 * @param string $table The database table to be inserted into 1502 * @param object|array $dataobject A data object with values for one or more fields in the record 1503 * @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. 1504 * @return bool|int true or new id 1505 * @throws dml_exception A DML specific exception is thrown for any errors. 1506 */ 1507 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) { 1508 $dataobject = (array)$dataobject; 1509 1510 $columns = $this->get_columns($table); 1511 if (empty($columns)) { 1512 throw new dml_exception('ddltablenotexist', $table); 1513 } 1514 1515 $cleaned = array(); 1516 1517 foreach ($dataobject as $field=>$value) { 1518 if ($field === 'id') { 1519 continue; 1520 } 1521 if (!isset($columns[$field])) { 1522 continue; 1523 } 1524 $column = $columns[$field]; 1525 $cleaned[$field] = $this->normalise_value($column, $value); 1526 } 1527 1528 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk); 1529 } 1530 1531 /** 1532 * Get chunk size for multiple records insert 1533 * @return int 1534 */ 1535 private function insert_chunk_size(): int { 1536 // MySQL has a relatively small query length limit by default, 1537 // make sure 'max_allowed_packet' in my.cnf is high enough 1538 // if you change the following default... 1539 static $chunksize = null; 1540 if ($chunksize === null) { 1541 if (!empty($this->dboptions['bulkinsertsize'])) { 1542 $chunksize = (int)$this->dboptions['bulkinsertsize']; 1543 1544 } else { 1545 if (PHP_INT_SIZE === 4) { 1546 // Bad luck for Windows, we cannot do any maths with large numbers. 1547 $chunksize = 5; 1548 } else { 1549 $sql = "SHOW VARIABLES LIKE 'max_allowed_packet'"; 1550 $this->query_start($sql, null, SQL_QUERY_AUX); 1551 $result = $this->mysqli->query($sql); 1552 $this->query_end($result); 1553 $size = 0; 1554 if ($rec = $result->fetch_assoc()) { 1555 $size = $rec['Value']; 1556 } 1557 $result->close(); 1558 // Hopefully 200kb per object are enough. 1559 $chunksize = (int)($size / 200000); 1560 if ($chunksize > 50) { 1561 $chunksize = 50; 1562 } 1563 } 1564 } 1565 } 1566 return $chunksize; 1567 } 1568 1569 /** 1570 * Insert multiple records into database as fast as possible. 1571 * 1572 * Order of inserts is maintained, but the operation is not atomic, 1573 * use transactions if necessary. 1574 * 1575 * This method is intended for inserting of large number of small objects, 1576 * do not use for huge objects with text or binary fields. 1577 * 1578 * @since Moodle 2.7 1579 * 1580 * @param string $table The database table to be inserted into 1581 * @param array|Traversable $dataobjects list of objects to be inserted, must be compatible with foreach 1582 * @return void does not return new record ids 1583 * 1584 * @throws coding_exception if data objects have different structure 1585 * @throws dml_exception A DML specific exception is thrown for any errors. 1586 */ 1587 public function insert_records($table, $dataobjects) { 1588 if (!is_array($dataobjects) && !$dataobjects instanceof Traversable) { 1589 throw new coding_exception('insert_records() passed non-traversable object'); 1590 } 1591 1592 $chunksize = $this->insert_chunk_size(); 1593 $columns = $this->get_columns($table, true); 1594 $fields = null; 1595 $count = 0; 1596 $chunk = array(); 1597 foreach ($dataobjects as $dataobject) { 1598 if (!is_array($dataobject) and !is_object($dataobject)) { 1599 throw new coding_exception('insert_records() passed invalid record object'); 1600 } 1601 $dataobject = (array)$dataobject; 1602 if ($fields === null) { 1603 $fields = array_keys($dataobject); 1604 $columns = array_intersect_key($columns, $dataobject); 1605 unset($columns['id']); 1606 } else if ($fields !== array_keys($dataobject)) { 1607 throw new coding_exception('All dataobjects in insert_records() must have the same structure!'); 1608 } 1609 1610 $count++; 1611 $chunk[] = $dataobject; 1612 1613 if ($count === $chunksize) { 1614 $this->insert_chunk($table, $chunk, $columns); 1615 $chunk = array(); 1616 $count = 0; 1617 } 1618 } 1619 1620 if ($count) { 1621 $this->insert_chunk($table, $chunk, $columns); 1622 } 1623 } 1624 1625 /** 1626 * Insert records in chunks. 1627 * 1628 * Note: can be used only from insert_records(). 1629 * 1630 * @param string $table 1631 * @param array $chunk 1632 * @param database_column_info[] $columns 1633 */ 1634 protected function insert_chunk($table, array $chunk, array $columns) { 1635 $fieldssql = '('.implode(',', array_keys($columns)).')'; 1636 1637 $valuessql = '('.implode(',', array_fill(0, count($columns), '?')).')'; 1638 $valuessql = implode(',', array_fill(0, count($chunk), $valuessql)); 1639 1640 $params = array(); 1641 foreach ($chunk as $dataobject) { 1642 foreach ($columns as $field => $column) { 1643 $params[] = $this->normalise_value($column, $dataobject[$field]); 1644 } 1645 } 1646 1647 $fixedtable = $this->fix_table_name($table); 1648 $sql = "INSERT INTO $fixedtable $fieldssql VALUES $valuessql"; 1649 1650 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1651 $rawsql = $this->emulate_bound_params($sql, $params); 1652 1653 $this->query_start($sql, $params, SQL_QUERY_INSERT); 1654 $result = $this->mysqli->query($rawsql); 1655 $this->query_end($result); 1656 } 1657 1658 /** 1659 * Import a record into a table, id field is required. 1660 * Safety checks are NOT carried out. Lobs are supported. 1661 * 1662 * @param string $table name of database table to be inserted into 1663 * @param object $dataobject A data object with values for one or more fields in the record 1664 * @return bool true 1665 * @throws dml_exception A DML specific exception is thrown for any errors. 1666 */ 1667 public function import_record($table, $dataobject) { 1668 $dataobject = (array)$dataobject; 1669 1670 $columns = $this->get_columns($table); 1671 $cleaned = array(); 1672 1673 foreach ($dataobject as $field=>$value) { 1674 if (!isset($columns[$field])) { 1675 continue; 1676 } 1677 $cleaned[$field] = $value; 1678 } 1679 1680 return $this->insert_record_raw($table, $cleaned, false, true, true); 1681 } 1682 1683 /** 1684 * Update record in database, as fast as possible, no safety checks, lobs not supported. 1685 * @param string $table name 1686 * @param stdClass|array $params data record as object or array 1687 * @param bool true means repeated updates expected 1688 * @return bool true 1689 * @throws dml_exception A DML specific exception is thrown for any errors. 1690 */ 1691 public function update_record_raw($table, $params, $bulk=false) { 1692 $params = (array)$params; 1693 1694 if (!isset($params['id'])) { 1695 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.'); 1696 } 1697 $id = $params['id']; 1698 unset($params['id']); 1699 1700 if (empty($params)) { 1701 throw new coding_exception('moodle_database::update_record_raw() no fields found.'); 1702 } 1703 1704 $sets = array(); 1705 foreach ($params as $field=>$value) { 1706 $sets[] = "$field = ?"; 1707 } 1708 1709 $params[] = $id; // last ? in WHERE condition 1710 1711 $sets = implode(',', $sets); 1712 $fixedtable = $this->fix_table_name($table); 1713 $sql = "UPDATE $fixedtable SET $sets WHERE id=?"; 1714 1715 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1716 $rawsql = $this->emulate_bound_params($sql, $params); 1717 1718 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1719 $result = $this->mysqli->query($rawsql); 1720 $this->query_end($result); 1721 1722 return true; 1723 } 1724 1725 /** 1726 * Update a record in a table 1727 * 1728 * $dataobject is an object containing needed data 1729 * Relies on $dataobject having a variable "id" to 1730 * specify the record to update 1731 * 1732 * @param string $table The database table to be checked against. 1733 * @param stdClass|array $dataobject An object with contents equal to fieldname=>fieldvalue. 1734 * Must have an entry for 'id' to map to the table specified. 1735 * @param bool true means repeated updates expected 1736 * @return bool true 1737 * @throws dml_exception A DML specific exception is thrown for any errors. 1738 */ 1739 public function update_record($table, $dataobject, $bulk=false) { 1740 $dataobject = (array)$dataobject; 1741 1742 $columns = $this->get_columns($table); 1743 $cleaned = array(); 1744 1745 foreach ($dataobject as $field=>$value) { 1746 if (!isset($columns[$field])) { 1747 continue; 1748 } 1749 $column = $columns[$field]; 1750 $cleaned[$field] = $this->normalise_value($column, $value); 1751 } 1752 1753 return $this->update_record_raw($table, $cleaned, $bulk); 1754 } 1755 1756 /** 1757 * Set a single field in every table record which match a particular WHERE clause. 1758 * 1759 * @param string $table The database table to be checked against. 1760 * @param string $newfield the field to set. 1761 * @param string $newvalue the value to set the field to. 1762 * @param string $select A fragment of SQL to be used in a where clause in the SQL call. 1763 * @param array $params array of sql parameters 1764 * @return bool true 1765 * @throws dml_exception A DML specific exception is thrown for any errors. 1766 */ 1767 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) { 1768 if ($select) { 1769 $select = "WHERE $select"; 1770 } 1771 if (is_null($params)) { 1772 $params = array(); 1773 } 1774 list($select, $params, $type) = $this->fix_sql_params($select, $params); 1775 1776 // Get column metadata 1777 $columns = $this->get_columns($table); 1778 $column = $columns[$newfield]; 1779 1780 $normalised_value = $this->normalise_value($column, $newvalue); 1781 1782 if (is_null($normalised_value)) { 1783 $newfield = "$newfield = NULL"; 1784 } else { 1785 $newfield = "$newfield = ?"; 1786 array_unshift($params, $normalised_value); 1787 } 1788 $fixedtable = $this->fix_table_name($table); 1789 $sql = "UPDATE $fixedtable SET $newfield $select"; 1790 $rawsql = $this->emulate_bound_params($sql, $params); 1791 1792 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1793 $result = $this->mysqli->query($rawsql); 1794 $this->query_end($result); 1795 1796 return true; 1797 } 1798 1799 /** 1800 * Delete one or more records from a table which match a particular WHERE clause. 1801 * 1802 * @param string $table The database table to be checked against. 1803 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria). 1804 * @param array $params array of sql parameters 1805 * @return bool true 1806 * @throws dml_exception A DML specific exception is thrown for any errors. 1807 */ 1808 public function delete_records_select($table, $select, array $params=null) { 1809 if ($select) { 1810 $select = "WHERE $select"; 1811 } 1812 $fixedtable = $this->fix_table_name($table); 1813 $sql = "DELETE FROM $fixedtable $select"; 1814 1815 list($sql, $params, $type) = $this->fix_sql_params($sql, $params); 1816 $rawsql = $this->emulate_bound_params($sql, $params); 1817 1818 $this->query_start($sql, $params, SQL_QUERY_UPDATE); 1819 $result = $this->mysqli->query($rawsql); 1820 $this->query_end($result); 1821 1822 return true; 1823 } 1824 1825 /** 1826 * Deletes records using a subquery, which is done with a strange DELETE...JOIN syntax in MySQL 1827 * because it performs very badly with normal subqueries. 1828 * 1829 * @param string $table Table to delete from 1830 * @param string $field Field in table to match 1831 * @param string $alias Name of single column in subquery e.g. 'id' 1832 * @param string $subquery Query that will return values of the field to delete 1833 * @param array $params Parameters for query 1834 * @throws dml_exception If there is any error 1835 */ 1836 public function delete_records_subquery(string $table, string $field, string $alias, string $subquery, array $params = []): void { 1837 // Aliases mysql_deltable and mysql_subquery are chosen to be unlikely to conflict. 1838 $this->execute("DELETE mysql_deltable FROM {" . $table . "} mysql_deltable JOIN " . 1839 "($subquery) mysql_subquery ON mysql_subquery.$alias = mysql_deltable.$field", $params); 1840 } 1841 1842 public function sql_cast_char2int($fieldname, $text=false) { 1843 return ' CAST(' . $fieldname . ' AS SIGNED) '; 1844 } 1845 1846 public function sql_cast_char2real($fieldname, $text=false) { 1847 // Set to 65 (max mysql 5.5 precision) with 7 as scale 1848 // because we must ensure at least 6 decimal positions 1849 // per casting given that postgres is casting to that scale (::real::). 1850 // Can be raised easily but that must be done in all DBs and tests. 1851 return ' CAST(' . $fieldname . ' AS DECIMAL(65,7)) '; 1852 } 1853 1854 public function sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false) { 1855 $equalop = $notequal ? '<>' : '='; 1856 1857 $collationinfo = explode('_', $this->get_dbcollation()); 1858 $bincollate = reset($collationinfo) . '_bin'; 1859 1860 if ($casesensitive) { 1861 // Current MySQL versions do not support case sensitive and accent insensitive. 1862 return "$fieldname COLLATE $bincollate $equalop $param"; 1863 } else if ($accentsensitive) { 1864 // Case insensitive and accent sensitive, we can force a binary comparison once all texts are using the same case. 1865 return "LOWER($fieldname) COLLATE $bincollate $equalop LOWER($param)"; 1866 } else { 1867 // Case insensitive and accent insensitive. All collations are that way, but utf8_bin. 1868 $collation = ''; 1869 if ($this->get_dbcollation() == 'utf8_bin') { 1870 $collation = 'COLLATE utf8_unicode_ci'; 1871 } else if ($this->get_dbcollation() == 'utf8mb4_bin') { 1872 $collation = 'COLLATE utf8mb4_unicode_ci'; 1873 } 1874 return "$fieldname $collation $equalop $param"; 1875 } 1876 } 1877 1878 /** 1879 * Returns 'LIKE' part of a query. 1880 * 1881 * Note that mysql does not support $casesensitive = true and $accentsensitive = false. 1882 * More information in http://bugs.mysql.com/bug.php?id=19567. 1883 * 1884 * @param string $fieldname usually name of the table column 1885 * @param string $param usually bound query parameter (?, :named) 1886 * @param bool $casesensitive use case sensitive search 1887 * @param bool $accensensitive use accent sensitive search (ignored if $casesensitive is true) 1888 * @param bool $notlike true means "NOT LIKE" 1889 * @param string $escapechar escape char for '%' and '_' 1890 * @return string SQL code fragment 1891 */ 1892 public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') { 1893 if (strpos($param, '%') !== false) { 1894 debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)'); 1895 } 1896 $escapechar = $this->mysqli->real_escape_string($escapechar); // prevents problems with C-style escapes of enclosing '\' 1897 1898 $collationinfo = explode('_', $this->get_dbcollation()); 1899 $bincollate = reset($collationinfo) . '_bin'; 1900 1901 $LIKE = $notlike ? 'NOT LIKE' : 'LIKE'; 1902 1903 if ($casesensitive) { 1904 // Current MySQL versions do not support case sensitive and accent insensitive. 1905 return "$fieldname $LIKE $param COLLATE $bincollate ESCAPE '$escapechar'"; 1906 1907 } else if ($accentsensitive) { 1908 // Case insensitive and accent sensitive, we can force a binary comparison once all texts are using the same case. 1909 return "LOWER($fieldname) $LIKE LOWER($param) COLLATE $bincollate ESCAPE '$escapechar'"; 1910 1911 } else { 1912 // Case insensitive and accent insensitive. 1913 $collation = ''; 1914 if ($this->get_dbcollation() == 'utf8_bin') { 1915 // Force a case insensitive comparison if using utf8_bin. 1916 $collation = 'COLLATE utf8_unicode_ci'; 1917 } else if ($this->get_dbcollation() == 'utf8mb4_bin') { 1918 // Force a case insensitive comparison if using utf8mb4_bin. 1919 $collation = 'COLLATE utf8mb4_unicode_ci'; 1920 } 1921 1922 return "$fieldname $LIKE $param $collation ESCAPE '$escapechar'"; 1923 } 1924 } 1925 1926 /** 1927 * Returns the proper SQL to do CONCAT between the elements passed 1928 * Can take many parameters 1929 * 1930 * @param string $str,... 1 or more fields/strings to concat 1931 * 1932 * @return string The concat sql 1933 */ 1934 public function sql_concat() { 1935 $arr = func_get_args(); 1936 $s = implode(', ', $arr); 1937 if ($s === '') { 1938 return "''"; 1939 } 1940 return "CONCAT($s)"; 1941 } 1942 1943 /** 1944 * Returns the proper SQL to do CONCAT between the elements passed 1945 * with a given separator 1946 * 1947 * @param string $separator The string to use as the separator 1948 * @param array $elements An array of items to concatenate 1949 * @return string The concat SQL 1950 */ 1951 public function sql_concat_join($separator="' '", $elements=array()) { 1952 $s = implode(', ', $elements); 1953 1954 if ($s === '') { 1955 return "''"; 1956 } 1957 return "CONCAT_WS($separator, $s)"; 1958 } 1959 1960 /** 1961 * Return SQL for performing group concatenation on given field/expression 1962 * 1963 * @param string $field 1964 * @param string $separator 1965 * @param string $sort 1966 * @return string 1967 */ 1968 public function sql_group_concat(string $field, string $separator = ', ', string $sort = ''): string { 1969 $fieldsort = $sort ? "ORDER BY {$sort}" : ''; 1970 return "GROUP_CONCAT({$field} {$fieldsort} SEPARATOR '{$separator}')"; 1971 } 1972 1973 /** 1974 * Returns the SQL text to be used to calculate the length in characters of one expression. 1975 * @param string fieldname or expression to calculate its length in characters. 1976 * @return string the piece of SQL code to be used in the statement. 1977 */ 1978 public function sql_length($fieldname) { 1979 return ' CHAR_LENGTH(' . $fieldname . ')'; 1980 } 1981 1982 /** 1983 * Does this driver support regex syntax when searching 1984 */ 1985 public function sql_regex_supported() { 1986 return true; 1987 } 1988 1989 /** 1990 * Return regex positive or negative match sql 1991 * @param bool $positivematch 1992 * @param bool $casesensitive 1993 * @return string or empty if not supported 1994 */ 1995 public function sql_regex($positivematch = true, $casesensitive = false) { 1996 $collation = ''; 1997 if ($casesensitive) { 1998 if (substr($this->get_dbcollation(), -4) !== '_bin') { 1999 $collationinfo = explode('_', $this->get_dbcollation()); 2000 $collation = 'COLLATE ' . $collationinfo[0] . '_bin '; 2001 } 2002 } else { 2003 if ($this->get_dbcollation() == 'utf8_bin') { 2004 $collation = 'COLLATE utf8_unicode_ci '; 2005 } else if ($this->get_dbcollation() == 'utf8mb4_bin') { 2006 $collation = 'COLLATE utf8mb4_unicode_ci '; 2007 } 2008 } 2009 2010 return $collation . ($positivematch ? 'REGEXP' : 'NOT REGEXP'); 2011 } 2012 2013 /** 2014 * Returns the word-beginning boundary marker based on MySQL version. 2015 * @return string The word-beginning boundary marker. 2016 */ 2017 public function sql_regex_get_word_beginning_boundary_marker() { 2018 $ismysql = ($this->get_dbtype() == 'mysqli' || $this->get_dbtype() == 'auroramysql'); 2019 $ismysqlge8d0d4 = ($ismysql && version_compare($this->get_server_info()['version'], '8.0.4', '>=')); 2020 if ($ismysqlge8d0d4) { 2021 return '\\b'; 2022 } 2023 // Prior to MySQL 8.0.4, MySQL used the Henry Spencer regular expression library to support regular expression operations, 2024 // rather than International Components for Unicode (ICU). 2025 // MariaDB still supports the "old marker" (MDEV-5357). 2026 return '[[:<:]]'; 2027 } 2028 2029 /** 2030 * Returns the word-end boundary marker based on MySQL version. 2031 * @return string The word-end boundary marker. 2032 */ 2033 public function sql_regex_get_word_end_boundary_marker() { 2034 $ismysql = ($this->get_dbtype() == 'mysqli' || $this->get_dbtype() == 'auroramysql'); 2035 $ismysqlge8d0d4 = ($ismysql && version_compare($this->get_server_info()['version'], '8.0.4', '>=')); 2036 if ($ismysqlge8d0d4) { 2037 return '\\b'; 2038 } 2039 // Prior to MySQL 8.0.4, MySQL used the Henry Spencer regular expression library to support regular expression operations, 2040 // rather than International Components for Unicode (ICU). 2041 // MariaDB still supports the "old marker" (MDEV-5357). 2042 return '[[:>:]]'; 2043 } 2044 2045 /** 2046 * Returns the SQL to be used in order to an UNSIGNED INTEGER column to SIGNED. 2047 * 2048 * @deprecated since 2.3 2049 * @param string $fieldname The name of the field to be cast 2050 * @return string The piece of SQL code to be used in your statement. 2051 */ 2052 public function sql_cast_2signed($fieldname) { 2053 return ' CAST(' . $fieldname . ' AS SIGNED) '; 2054 } 2055 2056 /** 2057 * Returns the SQL that allows to find intersection of two or more queries 2058 * 2059 * @since Moodle 2.8 2060 * 2061 * @param array $selects array of SQL select queries, each of them only returns fields with the names from $fields 2062 * @param string $fields comma-separated list of fields 2063 * @return string SQL query that will return only values that are present in each of selects 2064 */ 2065 public function sql_intersect($selects, $fields) { 2066 if (count($selects) <= 1) { 2067 return parent::sql_intersect($selects, $fields); 2068 } 2069 $fields = preg_replace('/\s/', '', $fields); 2070 static $aliascnt = 0; 2071 $falias = 'intsctal'.($aliascnt++); 2072 $rv = "SELECT $falias.". 2073 preg_replace('/,/', ','.$falias.'.', $fields). 2074 " FROM ($selects[0]) $falias"; 2075 for ($i = 1; $i < count($selects); $i++) { 2076 $alias = 'intsctal'.($aliascnt++); 2077 $rv .= " JOIN (".$selects[$i].") $alias ON ". 2078 join(' AND ', 2079 array_map( 2080 function($a) use ($alias, $falias) { 2081 return $falias . '.' . $a .' = ' . $alias . '.' . $a; 2082 }, 2083 preg_split('/,/', $fields)) 2084 ); 2085 } 2086 return $rv; 2087 } 2088 2089 /** 2090 * Does this driver support tool_replace? 2091 * 2092 * @since Moodle 2.6.1 2093 * @return bool 2094 */ 2095 public function replace_all_text_supported() { 2096 return true; 2097 } 2098 2099 public function session_lock_supported() { 2100 return true; 2101 } 2102 2103 /** 2104 * Obtain session lock 2105 * @param int $rowid id of the row with session record 2106 * @param int $timeout max allowed time to wait for the lock in seconds 2107 * @return void 2108 */ 2109 public function get_session_lock($rowid, $timeout) { 2110 parent::get_session_lock($rowid, $timeout); 2111 2112 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; 2113 $sql = "SELECT GET_LOCK('$fullname', $timeout)"; 2114 $this->query_start($sql, null, SQL_QUERY_AUX); 2115 $result = $this->mysqli->query($sql); 2116 $this->query_end($result); 2117 2118 if ($result) { 2119 $arr = $result->fetch_assoc(); 2120 $result->close(); 2121 2122 if (reset($arr) == 1) { 2123 return; 2124 } else { 2125 throw new dml_sessionwait_exception(); 2126 } 2127 } 2128 } 2129 2130 public function release_session_lock($rowid) { 2131 if (!$this->used_for_db_sessions) { 2132 return; 2133 } 2134 2135 parent::release_session_lock($rowid); 2136 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid; 2137 $sql = "SELECT RELEASE_LOCK('$fullname')"; 2138 $this->query_start($sql, null, SQL_QUERY_AUX); 2139 $result = $this->mysqli->query($sql); 2140 $this->query_end($result); 2141 2142 if ($result) { 2143 $result->close(); 2144 } 2145 } 2146 2147 /** 2148 * Are transactions supported? 2149 * It is not responsible to run productions servers 2150 * on databases without transaction support ;-) 2151 * 2152 * MyISAM does not support support transactions. 2153 * 2154 * You can override this via the dbtransactions option. 2155 * 2156 * @return bool 2157 */ 2158 protected function transactions_supported() { 2159 if (!is_null($this->transactions_supported)) { 2160 return $this->transactions_supported; 2161 } 2162 2163 // this is all just guessing, might be better to just specify it in config.php 2164 if (isset($this->dboptions['dbtransactions'])) { 2165 $this->transactions_supported = $this->dboptions['dbtransactions']; 2166 return $this->transactions_supported; 2167 } 2168 2169 $this->transactions_supported = false; 2170 2171 $engine = $this->get_dbengine(); 2172 2173 // Only will accept transactions if using compatible storage engine (more engines can be added easily BDB, Falcon...) 2174 if (in_array($engine, array('InnoDB', 'INNOBASE', 'BDB', 'XtraDB', 'Aria', 'Falcon'))) { 2175 $this->transactions_supported = true; 2176 } 2177 2178 return $this->transactions_supported; 2179 } 2180 2181 /** 2182 * Driver specific start of real database transaction, 2183 * this can not be used directly in code. 2184 * @return void 2185 */ 2186 protected function begin_transaction() { 2187 if (!$this->transactions_supported()) { 2188 return; 2189 } 2190 2191 $sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED"; 2192 $this->query_start($sql, null, SQL_QUERY_AUX); 2193 $result = $this->mysqli->query($sql); 2194 $this->query_end($result); 2195 2196 $sql = "START TRANSACTION"; 2197 $this->query_start($sql, null, SQL_QUERY_AUX); 2198 $result = $this->mysqli->query($sql); 2199 $this->query_end($result); 2200 } 2201 2202 /** 2203 * Driver specific commit of real database transaction, 2204 * this can not be used directly in code. 2205 * @return void 2206 */ 2207 protected function commit_transaction() { 2208 if (!$this->transactions_supported()) { 2209 return; 2210 } 2211 2212 $sql = "COMMIT"; 2213 $this->query_start($sql, null, SQL_QUERY_AUX); 2214 $result = $this->mysqli->query($sql); 2215 $this->query_end($result); 2216 } 2217 2218 /** 2219 * Driver specific abort of real database transaction, 2220 * this can not be used directly in code. 2221 * @return void 2222 */ 2223 protected function rollback_transaction() { 2224 if (!$this->transactions_supported()) { 2225 return; 2226 } 2227 2228 $sql = "ROLLBACK"; 2229 $this->query_start($sql, null, SQL_QUERY_AUX); 2230 $result = $this->mysqli->query($sql); 2231 $this->query_end($result); 2232 2233 return true; 2234 } 2235 2236 /** 2237 * Converts a table to either 'Compressed' or 'Dynamic' row format. 2238 * 2239 * @param string $tablename Name of the table to convert to the new row format. 2240 */ 2241 public function convert_table_row_format($tablename) { 2242 $currentrowformat = $this->get_row_format($tablename); 2243 if ($currentrowformat == 'Compact' || $currentrowformat == 'Redundant') { 2244 $rowformat = ($this->is_compressed_row_format_supported(false)) ? "ROW_FORMAT=Compressed" : "ROW_FORMAT=Dynamic"; 2245 $prefix = $this->get_prefix(); 2246 $this->change_database_structure("ALTER TABLE {$prefix}$tablename $rowformat"); 2247 } 2248 } 2249 2250 /** 2251 * Does this mysql instance support fulltext indexes? 2252 * 2253 * @return bool 2254 */ 2255 public function is_fulltext_search_supported() { 2256 $info = $this->get_server_info(); 2257 2258 if (version_compare($info['version'], '5.6.4', '>=')) { 2259 return true; 2260 } 2261 return false; 2262 } 2263 2264 /** 2265 * Fixes any table names that clash with reserved words. 2266 * 2267 * @param string $tablename The table name 2268 * @return string The fixed table name 2269 */ 2270 protected function fix_table_name($tablename) { 2271 $prefixedtablename = parent::fix_table_name($tablename); 2272 // This function quotes the table name if it matches one of the MySQL reserved 2273 // words, e.g. groups. 2274 return $this->get_manager()->generator->getEncQuoted($prefixedtablename); 2275 } 2276 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
