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