2 // This file is part of Moodle - http://moodle.org/
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.
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.
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/>.
18 * Native mysqli class representing moodle database interface.
21 * @copyright 2008 Petr Skoda (http://skodak.org)
22 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
25 defined('MOODLE_INTERNAL') ||
die();
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');
33 * Native mysqli class representing moodle database interface.
36 * @copyright 2008 Petr Skoda (http://skodak.org)
37 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
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
;
44 /** @var array $sslmodes */
45 private static $sslmodes = [
50 /** @var mysqli $mysqli */
51 protected $mysqli = null;
52 /** @var bool is compressed row format supported cache */
53 protected $compressedrowformatsupported = null;
54 /** @var string DB server actual version */
55 protected $serverversion = null;
57 private $transactions_supported = null;
60 * Attempt to create the database
61 * @param string $dbhost
62 * @param string $dbuser
63 * @param string $dbpass
64 * @param string $dbname
65 * @return bool success
66 * @throws dml_exception A DML specific exception is thrown for any errors.
68 public function create_database($dbhost, $dbuser, $dbpass, $dbname, ?
array $dboptions=null) {
69 $driverstatus = $this->driver_installed();
71 if ($driverstatus !== true) {
72 throw new dml_exception('dbdriverproblem', $driverstatus);
75 if (!empty($dboptions['dbsocket'])
76 and (strpos($dboptions['dbsocket'], '/') !== false or strpos($dboptions['dbsocket'], '\\') !== false)) {
77 $dbsocket = $dboptions['dbsocket'];
79 $dbsocket = ini_get('mysqli.default_socket');
81 if (empty($dboptions['dbport'])) {
82 $dbport = (int)ini_get('mysqli.default_port');
84 $dbport = (int)$dboptions['dbport'];
86 // verify ini.get does not return nonsense
91 $conn = new mysqli($dbhost, $dbuser, $dbpass, '', $dbport, $dbsocket); // Connect without db
92 $dberr = ob_get_contents();
94 $errorno = @$conn->connect_errno
;
97 throw new dml_connection_exception($dberr);
100 // Normally a check would be done before setting utf8mb4, but the database can be created
101 // before the enviroment checks are done. We'll proceed with creating the database and then do checks next.
102 $charset = 'utf8mb4';
103 if (isset($dboptions['dbcollation']) and (strpos($dboptions['dbcollation'], 'utf8_') === 0
104 ||
strpos($dboptions['dbcollation'], 'utf8mb4_') === 0)) {
105 $collation = $dboptions['dbcollation'];
106 $collationinfo = explode('_', $dboptions['dbcollation']);
107 $charset = reset($collationinfo);
109 $collation = 'utf8mb4_unicode_ci';
112 $result = $conn->query("CREATE DATABASE $dbname DEFAULT CHARACTER SET $charset DEFAULT COLLATE ".$collation);
117 throw new dml_exception('cannotcreatedb');
124 * Detects if all needed PHP stuff installed.
125 * Note: can be used before connect()
126 * @return mixed true if ok, string if something
128 public function driver_installed() {
129 if (!extension_loaded('mysqli')) {
130 return get_string('mysqliextensionisnotpresentinphp', 'install');
136 * Returns database family type - describes SQL dialect
137 * Note: can be used before connect()
138 * @return string db family name (mysql, postgres, mssql, oracle, etc.)
140 public function get_dbfamily() {
145 * Returns more specific database driver type
146 * Note: can be used before connect()
147 * @return string db type mysqli, pgsql, oci, mssql, sqlsrv
149 protected function get_dbtype() {
154 * Returns general database library name
155 * Note: can be used before connect()
156 * @return string db type pdo, native
158 protected function get_dblibrary() {
163 * Returns the current MySQL db engine.
165 * This is an ugly workaround for MySQL default engine problems,
166 * Moodle is designed to work best on ACID compliant databases
167 * with full transaction support. Do not use MyISAM.
169 * @return string or null MySQL engine name
171 public function get_dbengine() {
172 if (isset($this->dboptions
['dbengine'])) {
173 return $this->dboptions
['dbengine'];
176 if ($this->external
) {
182 // Look for current engine of our config table (the first table that gets created),
183 // so that we create all tables with the same engine.
184 $sql = "SELECT engine
185 FROM INFORMATION_SCHEMA.TABLES
186 WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}config'";
187 $this->query_start($sql, null, SQL_QUERY_AUX
);
188 $result = $this->mysqli
->query($sql);
189 $this->query_end($result);
190 if ($rec = $result->fetch_assoc()) {
191 // MySQL 8 BC: information_schema.* returns the fields in upper case.
192 $rec = array_change_key_case($rec, CASE_LOWER
);
193 $engine = $rec['engine'];
198 // Cache the result to improve performance.
199 $this->dboptions
['dbengine'] = $engine;
203 // Get the default database engine.
204 $sql = "SELECT @@default_storage_engine engine";
205 $this->query_start($sql, null, SQL_QUERY_AUX
);
206 $result = $this->mysqli
->query($sql);
207 $this->query_end($result);
208 if ($rec = $result->fetch_assoc()) {
209 $engine = $rec['engine'];
213 if ($engine === 'MyISAM') {
214 // we really do not want MyISAM for Moodle, InnoDB or XtraDB is a reasonable defaults if supported
215 $sql = "SHOW STORAGE ENGINES";
216 $this->query_start($sql, null, SQL_QUERY_AUX
);
217 $result = $this->mysqli
->query($sql);
218 $this->query_end($result);
220 while ($res = $result->fetch_assoc()) {
221 if ($res['Support'] === 'YES' or $res['Support'] === 'DEFAULT') {
222 $engines[$res['Engine']] = true;
226 if (isset($engines['InnoDB'])) {
229 if (isset($engines['XtraDB'])) {
234 // Cache the result to improve performance.
235 $this->dboptions
['dbengine'] = $engine;
240 * Returns the current MySQL db collation.
242 * This is an ugly workaround for MySQL default collation problems.
244 * @return string or null MySQL collation name
246 public function get_dbcollation() {
247 if (isset($this->dboptions
['dbcollation'])) {
248 return $this->dboptions
['dbcollation'];
253 * Set 'dbcollation' option
255 * @return string|null $dbcollation
257 private function detect_collation(): ?
string {
258 if ($this->external
) {
264 // Look for current collation of our config table (the first table that gets created),
265 // so that we create all tables with the same collation.
266 $sql = "SELECT collation_name
267 FROM INFORMATION_SCHEMA.COLUMNS
268 WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}config' AND column_name = 'value'";
269 $result = $this->mysqli
->query($sql);
270 if ($rec = $result->fetch_assoc()) {
271 // MySQL 8 BC: information_schema.* returns the fields in upper case.
272 $rec = array_change_key_case($rec, CASE_LOWER
);
273 $collation = $rec['collation_name'];
279 // Get the default database collation, but only if using UTF-8.
280 $sql = "SELECT @@collation_database";
281 $result = $this->mysqli
->query($sql);
282 if ($rec = $result->fetch_assoc()) {
283 if (strpos($rec['@@collation_database'], 'utf8_') === 0 ||
strpos($rec['@@collation_database'], 'utf8mb4_') === 0) {
284 $collation = $rec['@@collation_database'];
291 // We want only utf8 compatible collations.
293 $sql = "SHOW COLLATION WHERE Collation LIKE 'utf8mb4\_%' AND Charset = 'utf8mb4'";
294 $result = $this->mysqli
->query($sql);
295 while ($res = $result->fetch_assoc()) {
296 $collation = $res['Collation'];
297 if (strtoupper($res['Default']) === 'YES') {
298 $collation = $res['Collation'];
305 // Cache the result to improve performance.
306 $this->dboptions
['dbcollation'] = $collation;
311 * Tests if the Antelope file format is still supported or it has been removed.
312 * When removed, only Barracuda file format is supported, given the XtraDB/InnoDB engine.
314 * @return bool True if the Antelope file format has been removed; otherwise, false.
316 protected function is_antelope_file_format_no_more_supported() {
317 // Breaking change: Antelope file format support has been removed from both MySQL and MariaDB.
318 // The following InnoDB file format configuration parameters were deprecated and then removed:
319 // - innodb_file_format
320 // - innodb_file_format_check
321 // - innodb_file_format_max
322 // - innodb_large_prefix
323 // 1. MySQL: deprecated in 5.7.7 and removed 8.0.0+.
324 $ismysqlge8d0d0 = ($this->get_dbtype() == 'mysqli' ||
$this->get_dbtype() == 'auroramysql') &&
325 version_compare($this->get_server_info()['version'], '8.0.0', '>=');
326 // 2. MariaDB: deprecated in 10.2.0 and removed 10.3.1+.
327 $ismariadbge10d3d1 = ($this->get_dbtype() == 'mariadb') &&
328 version_compare($this->get_server_info()['version'], '10.3.1', '>=');
330 return $ismysqlge8d0d0 ||
$ismariadbge10d3d1;
334 * Get the row format from the database schema.
336 * @param string $table
337 * @return string row_format name or null if not known or table does not exist.
339 public function get_row_format($table = null) {
342 $table = $this->mysqli
->real_escape_string($table);
343 $sql = "SELECT row_format
344 FROM INFORMATION_SCHEMA.TABLES
345 WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}$table'";
347 if ($this->is_antelope_file_format_no_more_supported()) {
348 // Breaking change: Antelope file format support has been removed, only Barracuda.
349 $dbengine = $this->get_dbengine();
350 $supporteddbengines = array('InnoDB', 'XtraDB');
351 if (in_array($dbengine, $supporteddbengines)) {
352 $rowformat = 'Barracuda';
358 $sql = "SHOW VARIABLES LIKE 'innodb_file_format'";
360 $this->query_start($sql, null, SQL_QUERY_AUX
);
361 $result = $this->mysqli
->query($sql);
362 $this->query_end($result);
363 if ($rec = $result->fetch_assoc()) {
364 // MySQL 8 BC: information_schema.* returns the fields in upper case.
365 $rec = array_change_key_case($rec, CASE_LOWER
);
367 $rowformat = $rec['row_format'];
369 $rowformat = $rec['value'];
378 * Is this database compatible with compressed row format?
379 * This feature is necessary for support of large number of text
380 * columns in InnoDB/XtraDB database.
382 * @param bool $cached use cached result
383 * @return bool true if table can be created or changed to compressed row format.
385 public function is_compressed_row_format_supported($cached = true) {
386 if ($cached and isset($this->compressedrowformatsupported
)) {
387 return($this->compressedrowformatsupported
);
390 $engine = strtolower($this->get_dbengine());
391 $info = $this->get_server_info();
393 if (version_compare($info['version'], '5.5.0') < 0) {
394 // MySQL 5.1 is not supported here because we cannot read the file format.
395 $this->compressedrowformatsupported
= false;
397 } else if ($engine !== 'innodb' and $engine !== 'xtradb') {
398 // Other engines are not supported, most probably not compatible.
399 $this->compressedrowformatsupported
= false;
401 } else if (!$this->is_file_per_table_enabled()) {
402 $this->compressedrowformatsupported
= false;
404 } else if ($this->get_row_format() !== 'Barracuda') {
405 $this->compressedrowformatsupported
= false;
407 } else if ($this->get_dbtype() === 'auroramysql') {
408 // Aurora MySQL doesn't support COMPRESSED and falls back to COMPACT if you try to use it.
409 $this->compressedrowformatsupported
= false;
412 // All the tests passed, we can safely use ROW_FORMAT=Compressed in sql statements.
413 $this->compressedrowformatsupported
= true;
416 return $this->compressedrowformatsupported
;
420 * Check the database to see if innodb_file_per_table is on.
422 * @return bool True if on otherwise false.
424 public function is_file_per_table_enabled() {
425 if ($filepertable = $this->get_record_sql("SHOW VARIABLES LIKE 'innodb_file_per_table'")) {
426 if ($filepertable->value
== 'ON') {
434 * Check the database to see if innodb_large_prefix is on.
436 * @return bool True if on otherwise false.
438 public function is_large_prefix_enabled() {
439 if ($this->is_antelope_file_format_no_more_supported()) {
440 // Breaking change: Antelope file format support has been removed, only Barracuda.
444 if ($largeprefix = $this->get_record_sql("SHOW VARIABLES LIKE 'innodb_large_prefix'")) {
445 if ($largeprefix->value
== 'ON') {
453 * Determine if the row format should be set to compressed, dynamic, or default.
455 * Terrible kludge. If we're using utf8mb4 AND we're using InnoDB, we need to specify row format to
456 * be either dynamic or compressed (default is compact) in order to allow for bigger indexes (MySQL
457 * errors #1709 and #1071).
459 * @param string $engine The database engine being used. Will be looked up if not supplied.
460 * @param string $collation The database collation to use. Will look up the current collation if not supplied.
461 * @return string An sql fragment to add to sql statements.
463 public function get_row_format_sql($engine = null, $collation = null) {
465 if (!isset($engine)) {
466 $engine = $this->get_dbengine();
468 $engine = strtolower($engine);
470 if (!isset($collation)) {
471 $collation = $this->get_dbcollation();
475 if (($engine === 'innodb' ||
$engine === 'xtradb') && strpos($collation, 'utf8mb4_') === 0) {
476 if ($this->is_compressed_row_format_supported()) {
477 $rowformat = "ROW_FORMAT=Compressed";
479 $rowformat = "ROW_FORMAT=Dynamic";
486 * Returns localised database type name
487 * Note: can be used before connect()
490 public function get_name() {
491 return get_string('nativemysqli', 'install');
495 * Returns localised database configuration help.
496 * Note: can be used before connect()
499 public function get_configuration_help() {
500 return get_string('nativemysqlihelp', 'install');
505 * @param string $dbhost The database host.
506 * @param string $dbuser The database username.
507 * @param string $dbpass The database username's password.
508 * @param string $dbname The name of the database being connected to.e
509 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
510 * @param array $dboptions driver specific options
511 * @return bool success
512 * @throws moodle_exception
513 * @throws dml_connection_exception if error
515 public function raw_connect(string $dbhost, string $dbuser, string $dbpass, string $dbname, $prefix, ?
array $dboptions=null): bool {
516 $driverstatus = $this->driver_installed();
518 if ($driverstatus !== true) {
519 throw new dml_exception('dbdriverproblem', $driverstatus);
522 $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
524 // The dbsocket option is used ONLY if host is null or 'localhost'.
525 // You can not disable it because it is always tried if dbhost is 'localhost'.
526 if (!empty($this->dboptions
['dbsocket'])
527 and (strpos($this->dboptions
['dbsocket'], '/') !== false or strpos($this->dboptions
['dbsocket'], '\\') !== false)) {
528 $dbsocket = $this->dboptions
['dbsocket'];
530 $dbsocket = ini_get('mysqli.default_socket');
532 if (empty($this->dboptions
['dbport'])) {
533 $dbport = (int)ini_get('mysqli.default_port');
535 $dbport = (int)$this->dboptions
['dbport'];
537 // verify ini.get does not return nonsense
538 if (empty($dbport)) {
541 if ($dbhost and !empty($this->dboptions
['dbpersist'])) {
542 $dbhost = "p:$dbhost";
545 // We want to keep exceptions out from the native driver.
546 // TODO: See MDL-75761 for future improvements.
547 mysqli_report(MYSQLI_REPORT_OFF
); // Disable reporting (default before PHP 8.1).
549 $this->mysqli
= mysqli_init();
550 if (!empty($this->dboptions
['connecttimeout'])) {
551 $this->mysqli
->options(MYSQLI_OPT_CONNECT_TIMEOUT
, $this->dboptions
['connecttimeout']);
555 if ($this->dboptions
['clientcompress'] ??
false) {
556 $flags |
= MYSQLI_CLIENT_COMPRESS
;
558 if (isset($this->dboptions
['ssl'])) {
559 $sslmode = $this->dboptions
['ssl'];
560 if (!in_array($sslmode, self
::$sslmodes, true)) {
561 throw new moodle_exception("Invalid 'dboptions''ssl' value '$sslmode'");
563 $flags |
= MYSQLI_CLIENT_SSL
;
564 if ($sslmode === 'verify-full') {
565 $flags |
= MYSQLI_CLIENT_SSL_VERIFY_SERVER_CERT
;
572 // real_connect() is doing things we don't expext.
573 $conn = @$this->mysqli
->real_connect($dbhost, $dbuser, $dbpass, $dbname, $dbport, $dbsocket, $flags);
574 } catch (\Exception
$e) {
578 $dberr = $dberr ?
: "{$this->mysqli->connect_error} ({$this->mysqli->connect_errno})";
579 $this->mysqli
= null;
580 throw new dml_connection_exception($dberr);
583 // Disable logging until we are fully setup.
584 $this->query_log_prevent();
586 if (isset($dboptions['dbcollation'])) {
587 $collation = $this->dboptions
['dbcollation'] = $dboptions['dbcollation'];
589 $collation = $this->detect_collation();
591 $collationinfo = explode('_', $collation);
592 $charset = reset($collationinfo);
594 $this->mysqli
->set_charset($charset);
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);
607 // We can enable logging now.
608 $this->query_log_allow();
610 // Connection stabilised and configured, going to instantiate the temptables controller
611 $this->temptables
= new mysqli_native_moodle_temptables($this);
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.
621 public function dispose() {
622 parent
::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection
624 $this->mysqli
->close();
625 $this->mysqli
= null;
630 * Gets db handle currently used with queries
633 protected function get_db_handle() {
634 return $this->mysqli
;
638 * Sets db handle to be used with subsequent queries
639 * @param resource $dbh
642 protected function set_db_handle($dbh): void
{
643 $this->mysqli
= $dbh;
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
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)) {
660 return $this->read_slave_can_use_readonly($type, $sql);
664 * Returns the version of the MySQL server, as reported by the PHP client connection.
666 * Wrap $this->mysqli->server_info to improve testing strategy.
668 * @return string A string representing the version of the MySQL server that the MySQLi extension is connected to.
670 protected function get_mysqli_server_info(): string {
671 return $this->mysqli
->server_info
;
675 * Returns the version of the MySQL server, as reported by 'SELECT VERSION()' query.
677 * @return string A string that indicates the MySQL server version.
678 * @throws dml_read_exception If the execution of 'SELECT VERSION()' query will fail.
680 protected function get_version_from_db(): string {
682 // Query the DB server for the server version.
683 $sql = "SELECT VERSION() version;";
685 $result = $this->mysqli
->query($sql);
687 if ($row = $result->fetch_assoc()) {
688 $version = $row['version'];
693 } catch (\Throwable
$e) { // Exceptions in case of MYSQLI_REPORT_STRICT.
694 // It looks like we've an issue out of the expected boolean 'false' result above.
695 throw new dml_read_exception($e->getMessage(), $sql);
697 if (empty($version)) {
698 // Exception dml_read_exception usually reports raw mysqli errors i.e. not localised by Moodle.
699 throw new dml_read_exception("Unable to read the DB server version.", $sql);
706 * Returns whether $CFG->dboptions['versionfromdb'] has been set to boolean `true`.
708 * @return bool True if $CFG->dboptions['versionfromdb'] has been set to boolean `true`. Otherwise, `false`.
710 protected function should_db_version_be_read_from_db(): bool {
711 if (!empty($this->dboptions
['versionfromdb'])) {
719 * Returns database server info array.
720 * @return array Array containing 'description' and 'version' info.
721 * @throws dml_read_exception If the execution of 'SELECT VERSION()' query will fail.
723 public function get_server_info() {
724 $version = $this->serverversion
;
725 if (empty($version)) {
726 $version = $this->get_mysqli_server_info();
727 // The version returned by the PHP client could not be the actual DB server version.
728 // For example in MariaDB, it was prefixed by the RPL_VERSION_HACK, "5.5.5-" (MDEV-4088), starting from 10.x,
729 // when not using an authentication plug-in.
730 // Strip the RPL_VERSION_HACK prefix off - it will be "always" there in MariaDB until MDEV-28910 will be implemented.
731 $version = str_replace('5.5.5-', '', $version);
733 // Should we use the VERSION function to get the actual DB version instead of the PHP client version above?
734 if ($this->should_db_version_be_read_from_db()) {
735 // Try to query the actual version of the target database server: indeed some cloud providers, e.g. Azure,
736 // put a gateway in front of the actual instance which reports its own version to the PHP client
737 // and it doesn't represent the actual version of the DB server the PHP client is connected to.
739 // - https://learn.microsoft.com/en-us/azure/mariadb/concepts-supported-versions
740 // - https://learn.microsoft.com/en-us/azure/mysql/single-server/concepts-connect-to-a-gateway-node .
741 // Reset the version returned by the PHP client with the actual DB version reported by 'VERSION' function.
742 $version = $this->get_version_from_db();
745 // The version here starts with the following naming scheme: 'X.Y.Z[-<suffix>]'.
746 // Example: in MariaDB at least one suffix is "always" there, hardcoded in 'mysql_version.h.in':
747 // #define MYSQL_SERVER_VERSION "@VERSION@-MariaDB"
748 // MariaDB and MySQL server version could have extra suffixes too, set by the compilation environment,
749 // e.g. '-debug', '-embedded', '-log' or any other vendor specific suffix (e.g. build information).
750 // Strip out any suffix.
751 $parts = explode('-', $version, 2);
752 // Finally, keep just major, minor and patch versions (X.Y.Z) from the reported DB server version.
753 $this->serverversion
= $parts[0];
757 'description' => $this->get_mysqli_server_info(),
758 'version' => $this->serverversion
763 * Returns supported query parameter types
764 * @return int bitmask of accepted SQL_PARAMS_*
766 protected function allowed_param_types() {
767 return SQL_PARAMS_QM
;
771 * Returns last error reported by database engine.
772 * @return string error message
774 public function get_last_error() {
775 return $this->mysqli
->error
;
779 * Return tables in database WITHOUT current prefix
780 * @param bool $usecache if true, returns list of cached tables.
781 * @return array of table names in lowercase and without prefix
783 public function get_tables($usecache=true) {
784 if ($usecache and $this->tables
!== null) {
785 return $this->tables
;
787 $this->tables
= array();
788 $prefix = str_replace('_', '\\_', $this->prefix
);
789 $sql = "SHOW TABLES LIKE '$prefix%'";
790 $this->query_start($sql, null, $usecache ? SQL_QUERY_AUX_READONLY
: SQL_QUERY_AUX
);
791 $result = $this->mysqli
->query($sql);
792 $this->query_end($result);
793 $len = strlen($this->prefix
);
795 while ($arr = $result->fetch_assoc()) {
796 $tablename = reset($arr);
797 $tablename = substr($tablename, $len);
798 $this->tables
[$tablename] = $tablename;
803 // Add the currently available temptables
804 $this->tables
= array_merge($this->tables
, $this->temptables
->get_temptables());
805 return $this->tables
;
809 * Return table indexes - everything lowercased.
810 * @param string $table The table we want to get indexes from.
811 * @return array An associative array of indexes containing 'unique' flag and 'columns' being indexed
813 public function get_indexes($table) {
815 $fixedtable = $this->fix_table_name($table);
816 $sql = "SHOW INDEXES FROM $fixedtable";
817 $this->query_start($sql, null, SQL_QUERY_AUX_READONLY
);
818 $result = $this->mysqli
->query($sql);
820 $this->query_end($result);
821 } catch (dml_read_exception
$e) {
822 return $indexes; // table does not exist - no indexes...
825 while ($res = $result->fetch_object()) {
826 if ($res->Key_name
=== 'PRIMARY') {
829 if (!isset($indexes[$res->Key_name
])) {
830 $indexes[$res->Key_name
] = array('unique'=>empty($res->Non_unique
), 'columns'=>array());
832 $indexes[$res->Key_name
]['columns'][$res->Seq_in_index
-1] = $res->Column_name
;
840 * Fetches detailed information about columns in table.
842 * @param string $table name
843 * @return database_column_info[] array of database_column_info objects indexed with column names
845 protected function fetch_columns(string $table): array {
846 $structure = array();
848 $sql = "SELECT column_name, data_type, character_maximum_length, numeric_precision,
849 numeric_scale, is_nullable, column_type, column_default, column_key, extra
850 FROM information_schema.columns
851 WHERE table_name = '" . $this->prefix
.$table . "'
852 AND table_schema = '" . $this->dbname
. "'
853 ORDER BY ordinal_position";
854 $this->query_start($sql, null, SQL_QUERY_AUX_READONLY
);
855 $result = $this->mysqli
->query($sql);
856 $this->query_end(true); // Don't want to throw anything here ever. MDL-30147
858 if ($result === false) {
862 if ($result->num_rows
> 0) {
863 // standard table exists
864 while ($rawcolumn = $result->fetch_assoc()) {
865 // MySQL 8 BC: information_schema.* returns the fields in upper case.
866 $rawcolumn = array_change_key_case($rawcolumn, CASE_LOWER
);
867 $info = (object)$this->get_column_info((object)$rawcolumn);
868 $structure[$info->name
] = new database_column_info($info);
873 // temporary tables are not in information schema, let's try it the old way
875 $fixedtable = $this->fix_table_name($table);
876 $sql = "SHOW COLUMNS FROM $fixedtable";
877 $this->query_start($sql, null, SQL_QUERY_AUX_READONLY
);
878 $result = $this->mysqli
->query($sql);
879 $this->query_end(true);
880 if ($result === false) {
883 while ($rawcolumn = $result->fetch_assoc()) {
884 $rawcolumn = (object)array_change_key_case($rawcolumn, CASE_LOWER
);
885 $rawcolumn->column_name
= $rawcolumn->field
; unset($rawcolumn->field
);
886 $rawcolumn->column_type
= $rawcolumn->type
; unset($rawcolumn->type
);
887 $rawcolumn->character_maximum_length
= null;
888 $rawcolumn->numeric_precision
= null;
889 $rawcolumn->numeric_scale
= null;
890 $rawcolumn->is_nullable
= $rawcolumn->null; unset($rawcolumn->null);
891 $rawcolumn->column_default
= $rawcolumn->default; unset($rawcolumn->default);
892 $rawcolumn->column_key
= $rawcolumn->key
; unset($rawcolumn->key
);
894 if (preg_match('/(enum|varchar)\((\d+)\)/i', $rawcolumn->column_type
, $matches)) {
895 $rawcolumn->data_type
= $matches[1];
896 $rawcolumn->character_maximum_length
= $matches[2];
898 } else if (preg_match('/([a-z]*int[a-z]*)\((\d+)\)/i', $rawcolumn->column_type
, $matches)) {
899 $rawcolumn->data_type
= $matches[1];
900 $rawcolumn->numeric_precision
= $matches[2];
901 $rawcolumn->max_length
= $rawcolumn->numeric_precision
;
903 $type = strtoupper($matches[1]);
904 if ($type === 'BIGINT') {
906 } else if ($type === 'INT' or $type === 'INTEGER') {
908 } else if ($type === 'MEDIUMINT') {
910 } else if ($type === 'SMALLINT') {
912 } else if ($type === 'TINYINT') {
915 // This should not happen.
918 if ($maxlength < $rawcolumn->max_length
) {
919 $rawcolumn->max_length
= $maxlength;
922 } else if (preg_match('/(decimal)\((\d+),(\d+)\)/i', $rawcolumn->column_type
, $matches)) {
923 $rawcolumn->data_type
= $matches[1];
924 $rawcolumn->numeric_precision
= $matches[2];
925 $rawcolumn->numeric_scale
= $matches[3];
927 } else if (preg_match('/(double|float)(\((\d+),(\d+)\))?/i', $rawcolumn->column_type
, $matches)) {
928 $rawcolumn->data_type
= $matches[1];
929 $rawcolumn->numeric_precision
= isset($matches[3]) ?
$matches[3] : null;
930 $rawcolumn->numeric_scale
= isset($matches[4]) ?
$matches[4] : null;
932 } else if (preg_match('/([a-z]*text)/i', $rawcolumn->column_type
, $matches)) {
933 $rawcolumn->data_type
= $matches[1];
934 $rawcolumn->character_maximum_length
= -1; // unknown
936 } else if (preg_match('/([a-z]*blob)/i', $rawcolumn->column_type
, $matches)) {
937 $rawcolumn->data_type
= $matches[1];
940 $rawcolumn->data_type
= $rawcolumn->column_type
;
943 $info = $this->get_column_info($rawcolumn);
944 $structure[$info->name
] = new database_column_info($info);
953 * Indicates whether column information retrieved from `information_schema.columns` has default values quoted or not.
954 * @return boolean True when default values are quoted (breaking change); otherwise, false.
956 protected function has_breaking_change_quoted_defaults() {
961 * Indicates whether SQL_MODE default value has changed in a not backward compatible way.
962 * @return boolean True when SQL_MODE breaks BC; otherwise, false.
964 public function has_breaking_change_sqlmode() {
969 * Returns moodle column info for raw column from information schema.
970 * @param stdClass $rawcolumn
971 * @return stdClass standardised colum info
973 private function get_column_info(stdClass
$rawcolumn) {
974 $rawcolumn = (object)$rawcolumn;
975 $info = new stdClass();
976 $info->name
= $rawcolumn->column_name
;
977 $info->type
= $rawcolumn->data_type
;
978 $info->meta_type
= $this->mysqltype2moodletype($rawcolumn->data_type
);
979 if ($this->has_breaking_change_quoted_defaults()) {
980 $info->default_value
= is_null($rawcolumn->column_default
) ?
null : trim($rawcolumn->column_default
, "'");
981 if ($info->default_value
=== 'NULL') {
982 $info->default_value
= null;
985 $info->default_value
= $rawcolumn->column_default
;
987 $info->has_default
= !is_null($info->default_value
);
988 $info->not_null
= ($rawcolumn->is_nullable
=== 'NO');
989 $info->primary_key
= ($rawcolumn->column_key
=== 'PRI');
990 $info->binary
= false;
991 $info->unsigned
= null;
992 $info->auto_increment
= false;
993 $info->unique
= null;
996 if ($info->meta_type
=== 'C') {
997 $info->max_length
= $rawcolumn->character_maximum_length
;
999 } else if ($info->meta_type
=== 'I') {
1000 if ($info->primary_key
) {
1001 $info->meta_type
= 'R';
1002 $info->unique
= true;
1004 // Return number of decimals, not bytes here.
1005 $info->max_length
= $rawcolumn->numeric_precision
;
1006 if (preg_match('/([a-z]*int[a-z]*)\((\d+)\)/i', $rawcolumn->column_type
, $matches)) {
1007 $type = strtoupper($matches[1]);
1008 if ($type === 'BIGINT') {
1010 } else if ($type === 'INT' or $type === 'INTEGER') {
1012 } else if ($type === 'MEDIUMINT') {
1014 } else if ($type === 'SMALLINT') {
1016 } else if ($type === 'TINYINT') {
1019 // This should not happen.
1022 // It is possible that display precision is different from storage type length,
1023 // always use the smaller value to make sure our data fits.
1024 if ($maxlength < $info->max_length
) {
1025 $info->max_length
= $maxlength;
1028 $info->unsigned
= (stripos($rawcolumn->column_type
, 'unsigned') !== false);
1029 $info->auto_increment
= (strpos($rawcolumn->extra
, 'auto_increment') !== false);
1031 } else if ($info->meta_type
=== 'N') {
1032 $info->max_length
= $rawcolumn->numeric_precision
;
1033 $info->scale
= $rawcolumn->numeric_scale
;
1034 $info->unsigned
= (stripos($rawcolumn->column_type
, 'unsigned') !== false);
1036 } else if ($info->meta_type
=== 'X') {
1037 if ("$rawcolumn->character_maximum_length" === '4294967295') { // watch out for PHP max int limits!
1038 // means maximum moodle size for text column, in other drivers it may also mean unknown size
1039 $info->max_length
= -1;
1041 $info->max_length
= $rawcolumn->character_maximum_length
;
1043 $info->primary_key
= false;
1045 } else if ($info->meta_type
=== 'B') {
1046 $info->max_length
= -1;
1047 $info->primary_key
= false;
1048 $info->binary
= true;
1055 * Normalise column type.
1056 * @param string $mysql_type
1057 * @return string one character
1058 * @throws dml_exception
1060 private function mysqltype2moodletype($mysql_type) {
1063 switch(strtoupper($mysql_type)) {
1116 throw new dml_exception('invalidmysqlnativetype', $mysql_type);
1122 * Normalise values based in RDBMS dependencies (booleans, LOBs...)
1124 * @param database_column_info $column column metadata corresponding with the value we are going to normalise
1125 * @param mixed $value value we are going to normalise
1126 * @return mixed the normalised value
1128 protected function normalise_value($column, $value) {
1129 $this->detect_objects($value);
1131 if (is_bool($value)) { // Always, convert boolean to int
1132 $value = (int)$value;
1134 } else if ($value === '') {
1135 if ($column->meta_type
== 'I' or $column->meta_type
== 'F' or $column->meta_type
== 'N') {
1136 $value = 0; // prevent '' problems in numeric fields
1138 // Any float value being stored in varchar or text field is converted to string to avoid
1139 // any implicit conversion by MySQL
1140 } else if (is_float($value) and ($column->meta_type
== 'C' or $column->meta_type
== 'X')) {
1147 * Is this database compatible with utf8?
1150 public function setup_is_unicodedb() {
1151 // All new tables are created with this collation, we just have to make sure it is utf8 compatible,
1152 // if config table already exists it has this collation too.
1153 $collation = $this->get_dbcollation();
1155 $collationinfo = explode('_', $collation);
1156 $charset = reset($collationinfo);
1158 $sql = "SHOW COLLATION WHERE Collation ='$collation' AND Charset = '$charset'";
1159 $this->query_start($sql, null, SQL_QUERY_AUX_READONLY
);
1160 $result = $this->mysqli
->query($sql);
1161 $this->query_end($result);
1162 if ($result->fetch_assoc()) {
1173 * Do NOT use in code, to be used by database_manager only!
1174 * @param string|array $sql query
1175 * @param array|null $tablenames an array of xmldb table names affected by this request.
1177 * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors.
1179 public function change_database_structure($sql, $tablenames = null) {
1180 $this->get_manager(); // Includes DDL exceptions classes ;-)
1181 if (is_array($sql)) {
1182 $sql = implode("\n;\n", $sql);
1186 $this->query_start($sql, null, SQL_QUERY_STRUCTURE
);
1187 $result = $this->mysqli
->multi_query($sql);
1188 if ($result === false) {
1189 $this->query_end(false);
1191 while ($this->mysqli
->more_results()) {
1192 $result = $this->mysqli
->next_result();
1193 if ($result === false) {
1194 $this->query_end(false);
1197 $this->query_end(true);
1198 } catch (ddl_change_structure_exception
$e) {
1199 while (@$this->mysqli
->more_results()) {
1200 @$this->mysqli
->next_result();
1202 $this->reset_caches($tablenames);
1206 $this->reset_caches($tablenames);
1211 * Very ugly hack which emulates bound parameters in queries
1212 * because prepared statements do not use query cache.
1214 protected function emulate_bound_params($sql, ?
array $params=null) {
1215 if (empty($params)) {
1218 // ok, we have verified sql statement with ? and correct number of params
1219 $parts = array_reverse(explode('?', $sql));
1220 $return = array_pop($parts);
1221 foreach ($params as $param) {
1222 if (is_bool($param)) {
1223 $return .= (int)$param;
1224 } else if (is_null($param)) {
1226 } else if (is_number($param)) {
1227 $return .= "'".$param."'"; // we have to always use strings because mysql is using weird automatic int casting
1228 } else if (is_float($param)) {
1231 $param = $this->mysqli
->real_escape_string($param);
1232 $return .= "'$param'";
1234 $return .= array_pop($parts);
1240 * Execute general sql query. Should be used only when no other method suitable.
1241 * Do NOT use this to make changes in db structure, use database_manager methods instead!
1242 * @param string $sql query
1243 * @param array $params query parameters
1245 * @throws dml_exception A DML specific exception is thrown for any errors.
1247 public function execute($sql, ?
array $params=null) {
1248 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1250 if (strpos($sql, ';') !== false) {
1251 throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
1254 $rawsql = $this->emulate_bound_params($sql, $params);
1256 $this->query_start($sql, $params, SQL_QUERY_UPDATE
);
1257 $result = $this->mysqli
->query($rawsql);
1258 $this->query_end($result);
1260 if ($result === true) {
1270 * Get a number of records as a moodle_recordset using a SQL statement.
1272 * Since this method is a little less readable, use of it should be restricted to
1273 * code where it's possible there might be large datasets being returned. For known
1274 * small datasets use get_records_sql - it leads to simpler code.
1276 * The return type is like:
1277 * @see function get_recordset.
1279 * @param string $sql the SQL select query to execute.
1280 * @param array $params array of sql parameters
1281 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1282 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1283 * @return moodle_recordset instance
1284 * @throws dml_exception A DML specific exception is thrown for any errors.
1286 public function get_recordset_sql($sql, ?
array $params=null, $limitfrom=0, $limitnum=0) {
1288 list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum);
1290 if ($limitfrom or $limitnum) {
1291 if ($limitnum < 1) {
1292 $limitnum = "18446744073709551615";
1294 $sql .= " LIMIT $limitfrom, $limitnum";
1297 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1298 $rawsql = $this->emulate_bound_params($sql, $params);
1300 $this->query_start($sql, $params, SQL_QUERY_SELECT
);
1301 // no MYSQLI_USE_RESULT here, it would block write ops on affected tables
1302 $result = $this->mysqli
->query($rawsql, MYSQLI_STORE_RESULT
);
1303 $this->query_end($result);
1305 return $this->create_recordset($result);
1309 * Get all records from a table.
1311 * This method works around potential memory problems and may improve performance,
1312 * this method may block access to table until the recordset is closed.
1314 * @param string $table Name of database table.
1315 * @return moodle_recordset A moodle_recordset instance {@link function get_recordset}.
1316 * @throws dml_exception A DML specific exception is thrown for any errors.
1318 public function export_table_recordset($table) {
1319 $sql = $this->fix_table_names("SELECT * FROM {{$table}}");
1321 $this->query_start($sql, array(), SQL_QUERY_SELECT
);
1322 // MYSQLI_STORE_RESULT may eat all memory for large tables, unfortunately MYSQLI_USE_RESULT blocks other queries.
1323 $result = $this->mysqli
->query($sql, MYSQLI_USE_RESULT
);
1324 $this->query_end($result);
1326 return $this->create_recordset($result);
1329 protected function create_recordset($result) {
1330 return new mysqli_native_moodle_recordset($result);
1334 * Get a number of records as an array of objects using a SQL statement.
1336 * Return value is like:
1337 * @see function get_records.
1339 * @param string $sql the SQL select query to execute. The first column of this SELECT statement
1340 * must be a unique value (usually the 'id' field), as it will be used as the key of the
1342 * @param array $params array of sql parameters
1343 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1344 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
1345 * @return array of objects, or empty array if no records were found
1346 * @throws dml_exception A DML specific exception is thrown for any errors.
1348 public function get_records_sql($sql, ?
array $params=null, $limitfrom=0, $limitnum=0) {
1350 list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum);
1352 if ($limitfrom or $limitnum) {
1353 if ($limitnum < 1) {
1354 $limitnum = "18446744073709551615";
1356 $sql .= " LIMIT $limitfrom, $limitnum";
1359 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1360 $rawsql = $this->emulate_bound_params($sql, $params);
1362 $this->query_start($sql, $params, SQL_QUERY_SELECT
);
1363 $result = $this->mysqli
->query($rawsql, MYSQLI_STORE_RESULT
);
1364 $this->query_end($result);
1368 while($row = $result->fetch_assoc()) {
1369 $row = array_change_key_case($row, CASE_LOWER
);
1371 if (isset($return[$id])) {
1372 $colname = key($row);
1373 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
);
1375 $return[$id] = (object)$row;
1383 * Selects records and return values (first field) as an array using a SQL statement.
1385 * @param string $sql The SQL query
1386 * @param array $params array of sql parameters
1387 * @return array of values
1388 * @throws dml_exception A DML specific exception is thrown for any errors.
1390 public function get_fieldset_sql($sql, ?
array $params=null) {
1391 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1392 $rawsql = $this->emulate_bound_params($sql, $params);
1394 $this->query_start($sql, $params, SQL_QUERY_SELECT
);
1395 $result = $this->mysqli
->query($rawsql, MYSQLI_STORE_RESULT
);
1396 $this->query_end($result);
1400 while($row = $result->fetch_assoc()) {
1401 $return[] = reset($row);
1409 * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
1410 * @param string $table name
1411 * @param mixed $params data record as object or array
1412 * @param bool $returnit return it of inserted record
1413 * @param bool $bulk true means repeated inserts expected
1414 * @param bool $customsequence true if 'id' included in $params, disables $returnid
1415 * @return bool|int true or new id
1416 * @throws dml_exception A DML specific exception is thrown for any errors.
1418 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
1419 if (!is_array($params)) {
1420 $params = (array)$params;
1423 if ($customsequence) {
1424 if (!isset($params['id'])) {
1425 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
1429 unset($params['id']);
1432 if (empty($params)) {
1433 throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
1436 $fields = implode(',', array_keys($params));
1437 $qms = array_fill(0, count($params), '?');
1438 $qms = implode(',', $qms);
1439 $fixedtable = $this->fix_table_name($table);
1440 $sql = "INSERT INTO $fixedtable ($fields) VALUES($qms)";
1442 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1443 $rawsql = $this->emulate_bound_params($sql, $params);
1445 $this->query_start($sql, $params, SQL_QUERY_INSERT
);
1446 $result = $this->mysqli
->query($rawsql);
1447 $id = @$this->mysqli
->insert_id
; // must be called before query_end() which may insert log into db
1448 $this->query_end($result);
1450 if (!$customsequence and !$id) {
1451 throw new dml_write_exception('unknown error fetching inserted id');
1462 * Insert a record into a table and return the "id" field if required.
1464 * Some conversions and safety checks are carried out. Lobs are supported.
1465 * If the return ID isn't required, then this just reports success as true/false.
1466 * $data is an object containing needed data
1467 * @param string $table The database table to be inserted into
1468 * @param object|array $dataobject A data object with values for one or more fields in the record
1469 * @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.
1470 * @return bool|int true or new id
1471 * @throws dml_exception A DML specific exception is thrown for any errors.
1473 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
1474 $dataobject = (array)$dataobject;
1476 $columns = $this->get_columns($table);
1477 if (empty($columns)) {
1478 throw new dml_exception('ddltablenotexist', $table);
1483 foreach ($dataobject as $field=>$value) {
1484 if ($field === 'id') {
1487 if (!isset($columns[$field])) {
1490 $column = $columns[$field];
1491 $cleaned[$field] = $this->normalise_value($column, $value);
1494 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
1498 * Get chunk size for multiple records insert
1501 private function insert_chunk_size(): int {
1502 // MySQL has a relatively small query length limit by default,
1503 // make sure 'max_allowed_packet' in my.cnf is high enough
1504 // if you change the following default...
1505 static $chunksize = null;
1506 if ($chunksize === null) {
1507 if (!empty($this->dboptions
['bulkinsertsize'])) {
1508 $chunksize = (int)$this->dboptions
['bulkinsertsize'];
1511 if (PHP_INT_SIZE
=== 4) {
1512 // Bad luck for Windows, we cannot do any maths with large numbers.
1515 $sql = "SHOW VARIABLES LIKE 'max_allowed_packet'";
1516 $this->query_start($sql, null, SQL_QUERY_AUX
);
1517 $result = $this->mysqli
->query($sql);
1518 $this->query_end($result);
1520 if ($rec = $result->fetch_assoc()) {
1521 $size = $rec['Value'];
1524 // Hopefully 200kb per object are enough.
1525 $chunksize = (int)($size / 200000);
1526 if ($chunksize > 50) {
1536 * Insert multiple records into database as fast as possible.
1538 * Order of inserts is maintained, but the operation is not atomic,
1539 * use transactions if necessary.
1541 * This method is intended for inserting of large number of small objects,
1542 * do not use for huge objects with text or binary fields.
1546 * @param string $table The database table to be inserted into
1547 * @param array|Traversable $dataobjects list of objects to be inserted, must be compatible with foreach
1548 * @return void does not return new record ids
1550 * @throws coding_exception if data objects have different structure
1551 * @throws dml_exception A DML specific exception is thrown for any errors.
1553 public function insert_records($table, $dataobjects) {
1554 if (!is_array($dataobjects) && !$dataobjects instanceof Traversable
) {
1555 throw new coding_exception('insert_records() passed non-traversable object');
1558 $chunksize = $this->insert_chunk_size();
1559 $columns = $this->get_columns($table, true);
1563 foreach ($dataobjects as $dataobject) {
1564 if (!is_array($dataobject) and !is_object($dataobject)) {
1565 throw new coding_exception('insert_records() passed invalid record object');
1567 $dataobject = (array)$dataobject;
1568 if ($fields === null) {
1569 $fields = array_keys($dataobject);
1570 $columns = array_intersect_key($columns, $dataobject);
1571 unset($columns['id']);
1572 } else if ($fields !== array_keys($dataobject)) {
1573 throw new coding_exception('All dataobjects in insert_records() must have the same structure!');
1577 $chunk[] = $dataobject;
1579 if ($count === $chunksize) {
1580 $this->insert_chunk($table, $chunk, $columns);
1587 $this->insert_chunk($table, $chunk, $columns);
1592 * Insert records in chunks.
1594 * Note: can be used only from insert_records().
1596 * @param string $table
1597 * @param array $chunk
1598 * @param database_column_info[] $columns
1600 protected function insert_chunk($table, array $chunk, array $columns) {
1601 $fieldssql = '('.implode(',', array_keys($columns)).')';
1603 $valuessql = '('.implode(',', array_fill(0, count($columns), '?')).')';
1604 $valuessql = implode(',', array_fill(0, count($chunk), $valuessql));
1607 foreach ($chunk as $dataobject) {
1608 foreach ($columns as $field => $column) {
1609 $params[] = $this->normalise_value($column, $dataobject[$field]);
1613 $fixedtable = $this->fix_table_name($table);
1614 $sql = "INSERT INTO $fixedtable $fieldssql VALUES $valuessql";
1616 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1617 $rawsql = $this->emulate_bound_params($sql, $params);
1619 $this->query_start($sql, $params, SQL_QUERY_INSERT
);
1620 $result = $this->mysqli
->query($rawsql);
1621 $this->query_end($result);
1625 * Import a record into a table, id field is required.
1626 * Safety checks are NOT carried out. Lobs are supported.
1628 * @param string $table name of database table to be inserted into
1629 * @param object $dataobject A data object with values for one or more fields in the record
1631 * @throws dml_exception A DML specific exception is thrown for any errors.
1633 public function import_record($table, $dataobject) {
1634 $dataobject = (array)$dataobject;
1636 $columns = $this->get_columns($table);
1639 foreach ($dataobject as $field=>$value) {
1640 if (!isset($columns[$field])) {
1643 $cleaned[$field] = $value;
1646 return $this->insert_record_raw($table, $cleaned, false, true, true);
1650 * Update record in database, as fast as possible, no safety checks, lobs not supported.
1651 * @param string $table name
1652 * @param stdClass|array $params data record as object or array
1653 * @param bool true means repeated updates expected
1655 * @throws dml_exception A DML specific exception is thrown for any errors.
1657 public function update_record_raw($table, $params, $bulk=false) {
1658 $params = (array)$params;
1660 if (!isset($params['id'])) {
1661 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
1663 $id = $params['id'];
1664 unset($params['id']);
1666 if (empty($params)) {
1667 throw new coding_exception('moodle_database::update_record_raw() no fields found.');
1671 foreach ($params as $field=>$value) {
1672 $sets[] = "$field = ?";
1675 $params[] = $id; // last ? in WHERE condition
1677 $sets = implode(',', $sets);
1678 $fixedtable = $this->fix_table_name($table);
1679 $sql = "UPDATE $fixedtable SET $sets WHERE id=?";
1681 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1682 $rawsql = $this->emulate_bound_params($sql, $params);
1684 $this->query_start($sql, $params, SQL_QUERY_UPDATE
);
1685 $result = $this->mysqli
->query($rawsql);
1686 $this->query_end($result);
1692 * Update a record in a table
1694 * $dataobject is an object containing needed data
1695 * Relies on $dataobject having a variable "id" to
1696 * specify the record to update
1698 * @param string $table The database table to be checked against.
1699 * @param stdClass|array $dataobject An object with contents equal to fieldname=>fieldvalue.
1700 * Must have an entry for 'id' to map to the table specified.
1701 * @param bool true means repeated updates expected
1703 * @throws dml_exception A DML specific exception is thrown for any errors.
1705 public function update_record($table, $dataobject, $bulk=false) {
1706 $dataobject = (array)$dataobject;
1708 $columns = $this->get_columns($table);
1711 foreach ($dataobject as $field=>$value) {
1712 if (!isset($columns[$field])) {
1715 $column = $columns[$field];
1716 $cleaned[$field] = $this->normalise_value($column, $value);
1719 return $this->update_record_raw($table, $cleaned, $bulk);
1723 * Set a single field in every table record which match a particular WHERE clause.
1725 * @param string $table The database table to be checked against.
1726 * @param string $newfield the field to set.
1727 * @param string $newvalue the value to set the field to.
1728 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1729 * @param array $params array of sql parameters
1731 * @throws dml_exception A DML specific exception is thrown for any errors.
1733 public function set_field_select($table, $newfield, $newvalue, $select, ?
array $params=null) {
1735 $select = "WHERE $select";
1737 if (is_null($params)) {
1740 list($select, $params, $type) = $this->fix_sql_params($select, $params);
1742 // Get column metadata
1743 $columns = $this->get_columns($table);
1744 $column = $columns[$newfield];
1746 $normalised_value = $this->normalise_value($column, $newvalue);
1748 if (is_null($normalised_value)) {
1749 $newfield = "$newfield = NULL";
1751 $newfield = "$newfield = ?";
1752 array_unshift($params, $normalised_value);
1754 $fixedtable = $this->fix_table_name($table);
1755 $sql = "UPDATE $fixedtable SET $newfield $select";
1756 $rawsql = $this->emulate_bound_params($sql, $params);
1758 $this->query_start($sql, $params, SQL_QUERY_UPDATE
);
1759 $result = $this->mysqli
->query($rawsql);
1760 $this->query_end($result);
1766 * Delete one or more records from a table which match a particular WHERE clause.
1768 * @param string $table The database table to be checked against.
1769 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1770 * @param array $params array of sql parameters
1772 * @throws dml_exception A DML specific exception is thrown for any errors.
1774 public function delete_records_select($table, $select, ?
array $params=null) {
1776 $select = "WHERE $select";
1778 $fixedtable = $this->fix_table_name($table);
1779 $sql = "DELETE FROM $fixedtable $select";
1781 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1782 $rawsql = $this->emulate_bound_params($sql, $params);
1784 $this->query_start($sql, $params, SQL_QUERY_UPDATE
);
1785 $result = $this->mysqli
->query($rawsql);
1786 $this->query_end($result);
1792 * Deletes records using a subquery, which is done with a strange DELETE...JOIN syntax in MySQL
1793 * because it performs very badly with normal subqueries.
1795 * @param string $table Table to delete from
1796 * @param string $field Field in table to match
1797 * @param string $alias Name of single column in subquery e.g. 'id'
1798 * @param string $subquery Query that will return values of the field to delete
1799 * @param array $params Parameters for query
1800 * @throws dml_exception If there is any error
1802 public function delete_records_subquery(string $table, string $field, string $alias, string $subquery, array $params = []): void
{
1803 // Aliases mysql_deltable and mysql_subquery are chosen to be unlikely to conflict.
1804 $this->execute("DELETE mysql_deltable FROM {" . $table . "} mysql_deltable JOIN " .
1805 "($subquery) mysql_subquery ON mysql_subquery.$alias = mysql_deltable.$field", $params);
1808 public function sql_cast_char2int($fieldname, $text=false) {
1809 return ' CAST(' . $fieldname . ' AS SIGNED) ';
1812 public function sql_cast_char2real($fieldname, $text=false) {
1813 // Set to 65 (max mysql 5.5 precision) with 7 as scale
1814 // because we must ensure at least 6 decimal positions
1815 // per casting given that postgres is casting to that scale (::real::).
1816 // Can be raised easily but that must be done in all DBs and tests.
1817 return ' CAST(' . $fieldname . ' AS DECIMAL(65,7)) ';
1820 public function sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false) {
1821 $equalop = $notequal ?
'<>' : '=';
1823 $collationinfo = explode('_', $this->get_dbcollation());
1824 $bincollate = reset($collationinfo) . '_bin';
1826 if ($casesensitive) {
1827 // Current MySQL versions do not support case sensitive and accent insensitive.
1828 return "$fieldname COLLATE $bincollate $equalop $param";
1829 } else if ($accentsensitive) {
1830 // Case insensitive and accent sensitive, we can force a binary comparison once all texts are using the same case.
1831 return "LOWER($fieldname) COLLATE $bincollate $equalop LOWER($param)";
1833 // Case insensitive and accent insensitive. All collations are that way, but utf8_bin.
1835 if ($this->get_dbcollation() == 'utf8_bin') {
1836 $collation = 'COLLATE utf8_unicode_ci';
1837 } else if ($this->get_dbcollation() == 'utf8mb4_bin') {
1838 $collation = 'COLLATE utf8mb4_unicode_ci';
1840 return "$fieldname $collation $equalop $param";
1845 * Returns 'LIKE' part of a query.
1847 * Note that mysql does not support $casesensitive = true and $accentsensitive = false.
1848 * More information in http://bugs.mysql.com/bug.php?id=19567.
1850 * @param string $fieldname usually name of the table column
1851 * @param string $param usually bound query parameter (?, :named)
1852 * @param bool $casesensitive use case sensitive search
1853 * @param bool $accensensitive use accent sensitive search (ignored if $casesensitive is true)
1854 * @param bool $notlike true means "NOT LIKE"
1855 * @param string $escapechar escape char for '%' and '_'
1856 * @return string SQL code fragment
1858 public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
1859 if (strpos($param, '%') !== false) {
1860 debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)');
1862 $escapechar = $this->mysqli
->real_escape_string($escapechar); // prevents problems with C-style escapes of enclosing '\'
1864 $collationinfo = explode('_', $this->get_dbcollation());
1865 $bincollate = reset($collationinfo) . '_bin';
1867 $LIKE = $notlike ?
'NOT LIKE' : 'LIKE';
1869 if ($casesensitive) {
1870 // Current MySQL versions do not support case sensitive and accent insensitive.
1871 return "$fieldname $LIKE $param COLLATE $bincollate ESCAPE '$escapechar'";
1873 } else if ($accentsensitive) {
1874 // Case insensitive and accent sensitive, we can force a binary comparison once all texts are using the same case.
1875 return "LOWER($fieldname) $LIKE LOWER($param) COLLATE $bincollate ESCAPE '$escapechar'";
1878 // Case insensitive and accent insensitive.
1880 if ($this->get_dbcollation() == 'utf8_bin') {
1881 // Force a case insensitive comparison if using utf8_bin.
1882 $collation = 'COLLATE utf8_unicode_ci';
1883 } else if ($this->get_dbcollation() == 'utf8mb4_bin') {
1884 // Force a case insensitive comparison if using utf8mb4_bin.
1885 $collation = 'COLLATE utf8mb4_unicode_ci';
1888 return "$fieldname $LIKE $param $collation ESCAPE '$escapechar'";
1893 * Returns the proper SQL to do CONCAT between the elements passed
1894 * Can take many parameters
1896 * @param string $arr,... 1 or more fields/strings to concat
1898 * @return string The concat sql
1900 public function sql_concat(...$arr) {
1901 $s = implode(', ', $arr);
1905 return "CONCAT($s)";
1909 * Returns the proper SQL to do CONCAT between the elements passed
1910 * with a given separator
1912 * @param string $separator The string to use as the separator
1913 * @param array $elements An array of items to concatenate
1914 * @return string The concat SQL
1916 public function sql_concat_join($separator="' '", $elements=array()) {
1917 $s = implode(', ', $elements);
1922 return "CONCAT_WS($separator, $s)";
1926 * Return SQL for performing group concatenation on given field/expression
1928 * @param string $field
1929 * @param string $separator
1930 * @param string $sort
1933 public function sql_group_concat(string $field, string $separator = ', ', string $sort = ''): string {
1934 $fieldsort = $sort ?
"ORDER BY {$sort}" : '';
1935 return "GROUP_CONCAT({$field} {$fieldsort} SEPARATOR '{$separator}')";
1939 * Returns the SQL text to be used to calculate the length in characters of one expression.
1940 * @param string fieldname or expression to calculate its length in characters.
1941 * @return string the piece of SQL code to be used in the statement.
1943 public function sql_length($fieldname) {
1944 return ' CHAR_LENGTH(' . $fieldname . ')';
1948 * Does this driver support regex syntax when searching
1950 public function sql_regex_supported() {
1955 * Return regex positive or negative match sql
1956 * @param bool $positivematch
1957 * @param bool $casesensitive
1958 * @return string or empty if not supported
1960 public function sql_regex($positivematch = true, $casesensitive = false) {
1962 if ($casesensitive) {
1963 if (substr($this->get_dbcollation(), -4) !== '_bin') {
1964 $collationinfo = explode('_', $this->get_dbcollation());
1965 $collation = 'COLLATE ' . $collationinfo[0] . '_bin ';
1968 if ($this->get_dbcollation() == 'utf8_bin') {
1969 $collation = 'COLLATE utf8_unicode_ci ';
1970 } else if ($this->get_dbcollation() == 'utf8mb4_bin') {
1971 $collation = 'COLLATE utf8mb4_unicode_ci ';
1975 return $collation . ($positivematch ?
'REGEXP' : 'NOT REGEXP');
1979 * Returns the word-beginning boundary marker based on MySQL version.
1980 * @return string The word-beginning boundary marker.
1982 public function sql_regex_get_word_beginning_boundary_marker() {
1983 $ismysql = ($this->get_dbtype() == 'mysqli' ||
$this->get_dbtype() == 'auroramysql');
1984 $ismysqlge8d0d4 = ($ismysql && version_compare($this->get_server_info()['version'], '8.0.4', '>='));
1985 if ($ismysqlge8d0d4) {
1988 // Prior to MySQL 8.0.4, MySQL used the Henry Spencer regular expression library to support regular expression operations,
1989 // rather than International Components for Unicode (ICU).
1990 // MariaDB still supports the "old marker" (MDEV-5357).
1995 * Returns the word-end boundary marker based on MySQL version.
1996 * @return string The word-end boundary marker.
1998 public function sql_regex_get_word_end_boundary_marker() {
1999 $ismysql = ($this->get_dbtype() == 'mysqli' ||
$this->get_dbtype() == 'auroramysql');
2000 $ismysqlge8d0d4 = ($ismysql && version_compare($this->get_server_info()['version'], '8.0.4', '>='));
2001 if ($ismysqlge8d0d4) {
2004 // Prior to MySQL 8.0.4, MySQL used the Henry Spencer regular expression library to support regular expression operations,
2005 // rather than International Components for Unicode (ICU).
2006 // MariaDB still supports the "old marker" (MDEV-5357).
2011 * Returns the SQL to be used in order to an UNSIGNED INTEGER column to SIGNED.
2013 * @deprecated since 2.3
2014 * @param string $fieldname The name of the field to be cast
2015 * @return string The piece of SQL code to be used in your statement.
2017 public function sql_cast_2signed($fieldname) {
2018 return ' CAST(' . $fieldname . ' AS SIGNED) ';
2022 * Returns the SQL that allows to find intersection of two or more queries
2026 * @param array $selects array of SQL select queries, each of them only returns fields with the names from $fields
2027 * @param string $fields comma-separated list of fields
2028 * @return string SQL query that will return only values that are present in each of selects
2030 public function sql_intersect($selects, $fields) {
2031 if (count($selects) <= 1) {
2032 return parent
::sql_intersect($selects, $fields);
2034 $fields = preg_replace('/\s/', '', $fields);
2035 static $aliascnt = 0;
2036 $falias = 'intsctal'.($aliascnt++
);
2037 $rv = "SELECT $falias.".
2038 preg_replace('/,/', ','.$falias.'.', $fields).
2039 " FROM ($selects[0]) $falias";
2040 for ($i = 1; $i < count($selects); $i++
) {
2041 $alias = 'intsctal'.($aliascnt++
);
2042 $rv .= " JOIN (".$selects[$i].") $alias ON ".
2045 function($a) use ($alias, $falias) {
2046 return $falias . '.' . $a .' = ' . $alias . '.' . $a;
2048 preg_split('/,/', $fields))
2055 * Does this driver support tool_replace?
2057 * @since Moodle 2.6.1
2060 public function replace_all_text_supported() {
2064 public function session_lock_supported() {
2069 * Obtain session lock
2070 * @param int $rowid id of the row with session record
2071 * @param int $timeout max allowed time to wait for the lock in seconds
2074 public function get_session_lock($rowid, $timeout) {
2075 parent
::get_session_lock($rowid, $timeout);
2077 $fullname = $this->dbname
.'-'.$this->prefix
.'-session-'.$rowid;
2078 $sql = "SELECT GET_LOCK('$fullname', $timeout)";
2079 $this->query_start($sql, null, SQL_QUERY_AUX
);
2080 $result = $this->mysqli
->query($sql);
2081 $this->query_end($result);
2084 $arr = $result->fetch_assoc();
2087 if (reset($arr) == 1) {
2090 throw new dml_sessionwait_exception();
2095 public function release_session_lock($rowid) {
2096 if (!$this->used_for_db_sessions
) {
2100 parent
::release_session_lock($rowid);
2101 $fullname = $this->dbname
.'-'.$this->prefix
.'-session-'.$rowid;
2102 $sql = "SELECT RELEASE_LOCK('$fullname')";
2103 $this->query_start($sql, null, SQL_QUERY_AUX
);
2104 $result = $this->mysqli
->query($sql);
2105 $this->query_end($result);
2113 * Are transactions supported?
2114 * It is not responsible to run productions servers
2115 * on databases without transaction support ;-)
2117 * MyISAM does not support support transactions.
2119 * You can override this via the dbtransactions option.
2123 protected function transactions_supported() {
2124 if (!is_null($this->transactions_supported
)) {
2125 return $this->transactions_supported
;
2128 // this is all just guessing, might be better to just specify it in config.php
2129 if (isset($this->dboptions
['dbtransactions'])) {
2130 $this->transactions_supported
= $this->dboptions
['dbtransactions'];
2131 return $this->transactions_supported
;
2134 $this->transactions_supported
= false;
2136 $engine = $this->get_dbengine();
2138 // Only will accept transactions if using compatible storage engine (more engines can be added easily BDB, Falcon...)
2139 if (in_array($engine, array('InnoDB', 'INNOBASE', 'BDB', 'XtraDB', 'Aria', 'Falcon'))) {
2140 $this->transactions_supported
= true;
2143 return $this->transactions_supported
;
2147 * Driver specific start of real database transaction,
2148 * this can not be used directly in code.
2151 protected function begin_transaction() {
2152 if (!$this->transactions_supported()) {
2156 $sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED";
2157 $this->query_start($sql, null, SQL_QUERY_AUX
);
2158 $result = $this->mysqli
->query($sql);
2159 $this->query_end($result);
2161 $sql = "START TRANSACTION";
2162 $this->query_start($sql, null, SQL_QUERY_AUX
);
2163 $result = $this->mysqli
->query($sql);
2164 $this->query_end($result);
2168 * Driver specific commit of real database transaction,
2169 * this can not be used directly in code.
2172 protected function commit_transaction() {
2173 if (!$this->transactions_supported()) {
2178 $this->query_start($sql, null, SQL_QUERY_AUX
);
2179 $result = $this->mysqli
->query($sql);
2180 $this->query_end($result);
2184 * Driver specific abort of real database transaction,
2185 * this can not be used directly in code.
2188 protected function rollback_transaction() {
2189 if (!$this->transactions_supported()) {
2194 $this->query_start($sql, null, SQL_QUERY_AUX
);
2195 $result = $this->mysqli
->query($sql);
2196 $this->query_end($result);
2202 * Converts a table to either 'Compressed' or 'Dynamic' row format.
2204 * @param string $tablename Name of the table to convert to the new row format.
2206 public function convert_table_row_format($tablename) {
2207 $currentrowformat = $this->get_row_format($tablename);
2208 if ($currentrowformat == 'Compact' ||
$currentrowformat == 'Redundant') {
2209 $rowformat = ($this->is_compressed_row_format_supported(false)) ?
"ROW_FORMAT=Compressed" : "ROW_FORMAT=Dynamic";
2210 $prefix = $this->get_prefix();
2211 $this->change_database_structure("ALTER TABLE {$prefix}$tablename $rowformat");
2216 * Does this mysql instance support fulltext indexes?
2220 public function is_fulltext_search_supported() {
2221 $info = $this->get_server_info();
2223 if (version_compare($info['version'], '5.6.4', '>=')) {
2230 * Fixes any table names that clash with reserved words.
2232 * @param string $tablename The table name
2233 * @return string The fixed table name
2235 protected function fix_table_name($tablename) {
2236 $prefixedtablename = parent
::fix_table_name($tablename);
2237 // This function quotes the table name if it matches one of the MySQL reserved
2238 // words, e.g. groups.
2239 return $this->get_manager()->generator
->getEncQuoted($prefixedtablename);