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