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