Search moodle.org's
Developer Documentation

See Release Notes

  • Bug fixes for general core bugs in 3.11.x will end 14 Nov 2022 (12 months plus 6 months extension).
  • Bug fixes for security issues in 3.11.x will end 13 Nov 2023 (18 months plus 12 months extension).
  • PHP version: minimum PHP 7.3.0 Note: minimum PHP version has increased since Moodle 3.10. PHP 7.4.x is supported too.

Differences Between: [Versions 310 and 311] [Versions 311 and 401] [Versions 311 and 402] [Versions 311 and 403] [Versions 39 and 311]

   1  <?php
   2  // This file is part of Moodle - http://moodle.org/
   3  //
   4  // Moodle is free software: you can redistribute it and/or modify
   5  // it under the terms of the GNU General Public License as published by
   6  // the Free Software Foundation, either version 3 of the License, or
   7  // (at your option) any later version.
   8  //
   9  // Moodle is distributed in the hope that it will be useful,
  10  // but WITHOUT ANY WARRANTY; without even the implied warranty of
  11  // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  12  // GNU General Public License for more details.
  13  //
  14  // You should have received a copy of the GNU General Public License
  15  // along with Moodle.  If not, see <http://www.gnu.org/licenses/>.
  16  
  17  /**
  18   * Native mysqli class representing moodle database interface.
  19   *
  20   * @package    core_dml
  21   * @copyright  2008 Petr Skoda (http://skodak.org)
  22   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  23   */
  24  
  25  defined('MOODLE_INTERNAL') || die();
  26  
  27  require_once (__DIR__.'/moodle_database.php');
  28  require_once (__DIR__.'/moodle_read_slave_trait.php');
  29  require_once (__DIR__.'/mysqli_native_moodle_recordset.php');
  30  require_once (__DIR__.'/mysqli_native_moodle_temptables.php');
  31  
  32  /**
  33   * Native mysqli class representing moodle database interface.
  34   *
  35   * @package    core_dml
  36   * @copyright  2008 Petr Skoda (http://skodak.org)
  37   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  38   */
  39  class mysqli_native_moodle_database extends moodle_database {
  40      use moodle_read_slave_trait {
  41          can_use_readonly as read_slave_can_use_readonly;
  42      }
  43  
  44      /** @var mysqli $mysqli */
  45      protected $mysqli = null;
  46      /** @var bool is compressed row format supported cache */
  47      protected $compressedrowformatsupported = null;
  48  
  49      private $transactions_supported = null;
  50  
  51      /**
  52       * Attempt to create the database
  53       * @param string $dbhost
  54       * @param string $dbuser
  55       * @param string $dbpass
  56       * @param string $dbname
  57       * @return bool success
  58       * @throws dml_exception A DML specific exception is thrown for any errors.
  59       */
  60      public function create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null) {
  61          $driverstatus = $this->driver_installed();
  62  
  63          if ($driverstatus !== true) {
  64              throw new dml_exception('dbdriverproblem', $driverstatus);
  65          }
  66  
  67          if (!empty($dboptions['dbsocket'])
  68                  and (strpos($dboptions['dbsocket'], '/') !== false or strpos($dboptions['dbsocket'], '\\') !== false)) {
  69              $dbsocket = $dboptions['dbsocket'];
  70          } else {
  71              $dbsocket = ini_get('mysqli.default_socket');
  72          }
  73          if (empty($dboptions['dbport'])) {
  74              $dbport = (int)ini_get('mysqli.default_port');
  75          } else {
  76              $dbport = (int)$dboptions['dbport'];
  77          }
  78          // verify ini.get does not return nonsense
  79          if (empty($dbport)) {
  80              $dbport = 3306;
  81          }
  82          ob_start();
  83          $conn = new mysqli($dbhost, $dbuser, $dbpass, '', $dbport, $dbsocket); // Connect without db
  84          $dberr = ob_get_contents();
  85          ob_end_clean();
  86          $errorno = @$conn->connect_errno;
  87  
  88          if ($errorno !== 0) {
  89              throw new dml_connection_exception($dberr);
  90          }
  91  
  92          // Normally a check would be done before setting utf8mb4, but the database can be created
  93          // before the enviroment checks are done. We'll proceed with creating the database and then do checks next.
  94          $charset = 'utf8mb4';
  95          if (isset($dboptions['dbcollation']) and (strpos($dboptions['dbcollation'], 'utf8_') === 0
  96                  || strpos($dboptions['dbcollation'], 'utf8mb4_') === 0)) {
  97              $collation = $dboptions['dbcollation'];
  98              $collationinfo = explode('_', $dboptions['dbcollation']);
  99              $charset = reset($collationinfo);
 100          } else {
 101              $collation = 'utf8mb4_unicode_ci';
 102          }
 103  
 104          $result = $conn->query("CREATE DATABASE $dbname DEFAULT CHARACTER SET $charset DEFAULT COLLATE ".$collation);
 105  
 106          $conn->close();
 107  
 108          if (!$result) {
 109              throw new dml_exception('cannotcreatedb');
 110          }
 111  
 112          return true;
 113      }
 114  
 115      /**
 116       * Detects if all needed PHP stuff installed.
 117       * Note: can be used before connect()
 118       * @return mixed true if ok, string if something
 119       */
 120      public function driver_installed() {
 121          if (!extension_loaded('mysqli')) {
 122              return get_string('mysqliextensionisnotpresentinphp', 'install');
 123          }
 124          return true;
 125      }
 126  
 127      /**
 128       * Returns database family type - describes SQL dialect
 129       * Note: can be used before connect()
 130       * @return string db family name (mysql, postgres, mssql, oracle, etc.)
 131       */
 132      public function get_dbfamily() {
 133          return 'mysql';
 134      }
 135  
 136      /**
 137       * Returns more specific database driver type
 138       * Note: can be used before connect()
 139       * @return string db type mysqli, pgsql, oci, mssql, sqlsrv
 140       */
 141      protected function get_dbtype() {
 142          return 'mysqli';
 143      }
 144  
 145      /**
 146       * Returns general database library name
 147       * Note: can be used before connect()
 148       * @return string db type pdo, native
 149       */
 150      protected function get_dblibrary() {
 151          return 'native';
 152      }
 153  
 154      /**
 155       * Returns the current MySQL db engine.
 156       *
 157       * This is an ugly workaround for MySQL default engine problems,
 158       * Moodle is designed to work best on ACID compliant databases
 159       * with full transaction support. Do not use MyISAM.
 160       *
 161       * @return string or null MySQL engine name
 162       */
 163      public function get_dbengine() {
 164          if (isset($this->dboptions['dbengine'])) {
 165              return $this->dboptions['dbengine'];
 166          }
 167  
 168          if ($this->external) {
 169              return null;
 170          }
 171  
 172          $engine = null;
 173  
 174          // Look for current engine of our config table (the first table that gets created),
 175          // so that we create all tables with the same engine.
 176          $sql = "SELECT engine
 177                    FROM INFORMATION_SCHEMA.TABLES
 178                   WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}config'";
 179          $this->query_start($sql, NULL, SQL_QUERY_AUX);
 180          $result = $this->mysqli->query($sql);
 181          $this->query_end($result);
 182          if ($rec = $result->fetch_assoc()) {
 183              // MySQL 8 BC: information_schema.* returns the fields in upper case.
 184              $rec = array_change_key_case($rec, CASE_LOWER);
 185              $engine = $rec['engine'];
 186          }
 187          $result->close();
 188  
 189          if ($engine) {
 190              // Cache the result to improve performance.
 191              $this->dboptions['dbengine'] = $engine;
 192              return $engine;
 193          }
 194  
 195          // Get the default database engine.
 196          $sql = "SELECT @@default_storage_engine engine";
 197          $this->query_start($sql, NULL, SQL_QUERY_AUX);
 198          $result = $this->mysqli->query($sql);
 199          $this->query_end($result);
 200          if ($rec = $result->fetch_assoc()) {
 201              $engine = $rec['engine'];
 202          }
 203          $result->close();
 204  
 205          if ($engine === 'MyISAM') {
 206              // we really do not want MyISAM for Moodle, InnoDB or XtraDB is a reasonable defaults if supported
 207              $sql = "SHOW STORAGE ENGINES";
 208              $this->query_start($sql, NULL, SQL_QUERY_AUX);
 209              $result = $this->mysqli->query($sql);
 210              $this->query_end($result);
 211              $engines = array();
 212              while ($res = $result->fetch_assoc()) {
 213                  if ($res['Support'] === 'YES' or $res['Support'] === 'DEFAULT') {
 214                      $engines[$res['Engine']] = true;
 215                  }
 216              }
 217              $result->close();
 218              if (isset($engines['InnoDB'])) {
 219                  $engine = 'InnoDB';
 220              }
 221              if (isset($engines['XtraDB'])) {
 222                  $engine = 'XtraDB';
 223              }
 224          }
 225  
 226          // Cache the result to improve performance.
 227          $this->dboptions['dbengine'] = $engine;
 228          return $engine;
 229      }
 230  
 231      /**
 232       * Returns the current MySQL db collation.
 233       *
 234       * This is an ugly workaround for MySQL default collation problems.
 235       *
 236       * @return string or null MySQL collation name
 237       */
 238      public function get_dbcollation() {
 239          if (isset($this->dboptions['dbcollation'])) {
 240              return $this->dboptions['dbcollation'];
 241          }
 242      }
 243  
 244      /**
 245       * Set 'dbcollation' option
 246       *
 247       * @return string|null $dbcollation
 248       */
 249      private function detect_collation(): ?string {
 250          if ($this->external) {
 251              return null;
 252          }
 253  
 254          $collation = null;
 255  
 256          // Look for current collation of our config table (the first table that gets created),
 257          // so that we create all tables with the same collation.
 258          $sql = "SELECT collation_name
 259                    FROM INFORMATION_SCHEMA.COLUMNS
 260                   WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}config' AND column_name = 'value'";
 261          $result = $this->mysqli->query($sql);
 262          if ($rec = $result->fetch_assoc()) {
 263              // MySQL 8 BC: information_schema.* returns the fields in upper case.
 264              $rec = array_change_key_case($rec, CASE_LOWER);
 265              $collation = $rec['collation_name'];
 266          }
 267          $result->close();
 268  
 269  
 270          if (!$collation) {
 271              // Get the default database collation, but only if using UTF-8.
 272              $sql = "SELECT @@collation_database";
 273              $result = $this->mysqli->query($sql);
 274              if ($rec = $result->fetch_assoc()) {
 275                  if (strpos($rec['@@collation_database'], 'utf8_') === 0 || strpos($rec['@@collation_database'], 'utf8mb4_') === 0) {
 276                      $collation = $rec['@@collation_database'];
 277                  }
 278              }
 279              $result->close();
 280          }
 281  
 282          if (!$collation) {
 283              // We want only utf8 compatible collations.
 284              $collation = null;
 285              $sql = "SHOW COLLATION WHERE Collation LIKE 'utf8mb4\_%' AND Charset = 'utf8mb4'";
 286              $result = $this->mysqli->query($sql);
 287              while ($res = $result->fetch_assoc()) {
 288                  $collation = $res['Collation'];
 289                  if (strtoupper($res['Default']) === 'YES') {
 290                      $collation = $res['Collation'];
 291                      break;
 292                  }
 293              }
 294              $result->close();
 295          }
 296  
 297          // Cache the result to improve performance.
 298          $this->dboptions['dbcollation'] = $collation;
 299          return $collation;
 300      }
 301  
 302      /**
 303       * Tests if the Antelope file format is still supported or it has been removed.
 304       * When removed, only Barracuda file format is supported, given the XtraDB/InnoDB engine.
 305       *
 306       * @return bool True if the Antelope file format has been removed; otherwise, false.
 307       */
 308      protected function is_antelope_file_format_no_more_supported() {
 309          // Breaking change: Antelope file format support has been removed from both MySQL and MariaDB.
 310          // The following InnoDB file format configuration parameters were deprecated and then removed:
 311          // - innodb_file_format
 312          // - innodb_file_format_check
 313          // - innodb_file_format_max
 314          // - innodb_large_prefix
 315          // 1. MySQL: deprecated in 5.7.7 and removed 8.0.0+.
 316          $ismysqlge8d0d0 = ($this->get_dbtype() == 'mysqli' || $this->get_dbtype() == 'auroramysql') &&
 317                  version_compare($this->get_server_info()['version'], '8.0.0', '>=');
 318          // 2. MariaDB: deprecated in 10.2.0 and removed 10.3.1+.
 319          $ismariadbge10d3d1 = ($this->get_dbtype() == 'mariadb') &&
 320                  version_compare($this->get_server_info()['version'], '10.3.1', '>=');
 321  
 322          return $ismysqlge8d0d0 || $ismariadbge10d3d1;
 323      }
 324  
 325      /**
 326       * Get the row format from the database schema.
 327       *
 328       * @param string $table
 329       * @return string row_format name or null if not known or table does not exist.
 330       */
 331      public function get_row_format($table = null) {
 332          $rowformat = null;
 333          if (isset($table)) {
 334              $table = $this->mysqli->real_escape_string($table);
 335              $sql = "SELECT row_format
 336                        FROM INFORMATION_SCHEMA.TABLES
 337                       WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}$table'";
 338          } else {
 339              if ($this->is_antelope_file_format_no_more_supported()) {
 340                  // Breaking change: Antelope file format support has been removed, only Barracuda.
 341                  $dbengine = $this->get_dbengine();
 342                  $supporteddbengines = array('InnoDB', 'XtraDB');
 343                  if (in_array($dbengine, $supporteddbengines)) {
 344                      $rowformat = 'Barracuda';
 345                  }
 346  
 347                  return $rowformat;
 348              }
 349  
 350              $sql = "SHOW VARIABLES LIKE 'innodb_file_format'";
 351          }
 352          $this->query_start($sql, NULL, SQL_QUERY_AUX);
 353          $result = $this->mysqli->query($sql);
 354          $this->query_end($result);
 355          if ($rec = $result->fetch_assoc()) {
 356              // MySQL 8 BC: information_schema.* returns the fields in upper case.
 357              $rec = array_change_key_case($rec, CASE_LOWER);
 358              if (isset($table)) {
 359                  $rowformat = $rec['row_format'];
 360              } else {
 361                  $rowformat = $rec['value'];
 362              }
 363          }
 364          $result->close();
 365  
 366          return $rowformat;
 367      }
 368  
 369      /**
 370       * Is this database compatible with compressed row format?
 371       * This feature is necessary for support of large number of text
 372       * columns in InnoDB/XtraDB database.
 373       *
 374       * @param bool $cached use cached result
 375       * @return bool true if table can be created or changed to compressed row format.
 376       */
 377      public function is_compressed_row_format_supported($cached = true) {
 378          if ($cached and isset($this->compressedrowformatsupported)) {
 379              return($this->compressedrowformatsupported);
 380          }
 381  
 382          $engine = strtolower($this->get_dbengine());
 383          $info = $this->get_server_info();
 384  
 385          if (version_compare($info['version'], '5.5.0') < 0) {
 386              // MySQL 5.1 is not supported here because we cannot read the file format.
 387              $this->compressedrowformatsupported = false;
 388  
 389          } else if ($engine !== 'innodb' and $engine !== 'xtradb') {
 390              // Other engines are not supported, most probably not compatible.
 391              $this->compressedrowformatsupported = false;
 392  
 393          } else if (!$this->is_file_per_table_enabled()) {
 394              $this->compressedrowformatsupported = false;
 395  
 396          } else if ($this->get_row_format() !== 'Barracuda') {
 397              $this->compressedrowformatsupported = false;
 398  
 399          } else {
 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       * Return SQL for performing group concatenation on given field/expression
1828       *
1829       * @param string $field
1830       * @param string $separator
1831       * @param string $sort
1832       * @return string
1833       */
1834      public function sql_group_concat(string $field, string $separator = ', ', string $sort = ''): string {
1835          $fieldsort = $sort ? "ORDER BY {$sort}" : '';
1836          return "GROUP_CONCAT({$field} {$fieldsort} SEPARATOR '{$separator}')";
1837      }
1838  
1839      /**
1840       * Returns the SQL text to be used to calculate the length in characters of one expression.
1841       * @param string fieldname or expression to calculate its length in characters.
1842       * @return string the piece of SQL code to be used in the statement.
1843       */
1844      public function sql_length($fieldname) {
1845          return ' CHAR_LENGTH(' . $fieldname . ')';
1846      }
1847  
1848      /**
1849       * Does this driver support regex syntax when searching
1850       */
1851      public function sql_regex_supported() {
1852          return true;
1853      }
1854  
1855      /**
1856       * Return regex positive or negative match sql
1857       * @param bool $positivematch
1858       * @param bool $casesensitive
1859       * @return string or empty if not supported
1860       */
1861      public function sql_regex($positivematch = true, $casesensitive = false) {
1862          $collation = '';
1863          if ($casesensitive) {
1864              if (substr($this->get_dbcollation(), -4) !== '_bin') {
1865                  $collationinfo = explode('_', $this->get_dbcollation());
1866                  $collation = 'COLLATE ' . $collationinfo[0] . '_bin ';
1867              }
1868          } else {
1869              if ($this->get_dbcollation() == 'utf8_bin') {
1870                  $collation = 'COLLATE utf8_unicode_ci ';
1871              } else if ($this->get_dbcollation() == 'utf8mb4_bin') {
1872                  $collation = 'COLLATE utf8mb4_unicode_ci ';
1873              }
1874          }
1875  
1876          return $collation . ($positivematch ? 'REGEXP' : 'NOT REGEXP');
1877      }
1878  
1879      /**
1880       * Returns the word-beginning boundary marker based on MySQL version.
1881       * @return string The word-beginning boundary marker.
1882       */
1883      public function sql_regex_get_word_beginning_boundary_marker() {
1884          $ismysql = ($this->get_dbtype() == 'mysqli' || $this->get_dbtype() == 'auroramysql');
1885          $ismysqlge8d0d4 = ($ismysql && version_compare($this->get_server_info()['version'], '8.0.4', '>='));
1886          if ($ismysqlge8d0d4) {
1887              return '\\b';
1888          }
1889          // Prior to MySQL 8.0.4, MySQL used the Henry Spencer regular expression library to support regular expression operations,
1890          // rather than International Components for Unicode (ICU).
1891          // MariaDB still supports the "old marker" (MDEV-5357).
1892          return '[[:<:]]';
1893      }
1894  
1895      /**
1896       * Returns the word-end boundary marker based on MySQL version.
1897       * @return string The word-end boundary marker.
1898       */
1899      public function sql_regex_get_word_end_boundary_marker() {
1900          $ismysql = ($this->get_dbtype() == 'mysqli' || $this->get_dbtype() == 'auroramysql');
1901          $ismysqlge8d0d4 = ($ismysql && version_compare($this->get_server_info()['version'], '8.0.4', '>='));
1902          if ($ismysqlge8d0d4) {
1903              return '\\b';
1904          }
1905          // Prior to MySQL 8.0.4, MySQL used the Henry Spencer regular expression library to support regular expression operations,
1906          // rather than International Components for Unicode (ICU).
1907          // MariaDB still supports the "old marker" (MDEV-5357).
1908          return '[[:>:]]';
1909      }
1910  
1911      /**
1912       * Returns the SQL to be used in order to an UNSIGNED INTEGER column to SIGNED.
1913       *
1914       * @deprecated since 2.3
1915       * @param string $fieldname The name of the field to be cast
1916       * @return string The piece of SQL code to be used in your statement.
1917       */
1918      public function sql_cast_2signed($fieldname) {
1919          return ' CAST(' . $fieldname . ' AS SIGNED) ';
1920      }
1921  
1922      /**
1923       * Returns the SQL that allows to find intersection of two or more queries
1924       *
1925       * @since Moodle 2.8
1926       *
1927       * @param array $selects array of SQL select queries, each of them only returns fields with the names from $fields
1928       * @param string $fields comma-separated list of fields
1929       * @return string SQL query that will return only values that are present in each of selects
1930       */
1931      public function sql_intersect($selects, $fields) {
1932          if (count($selects) <= 1) {
1933              return parent::sql_intersect($selects, $fields);
1934          }
1935          $fields = preg_replace('/\s/', '', $fields);
1936          static $aliascnt = 0;
1937          $falias = 'intsctal'.($aliascnt++);
1938          $rv = "SELECT $falias.".
1939              preg_replace('/,/', ','.$falias.'.', $fields).
1940              " FROM ($selects[0]) $falias";
1941          for ($i = 1; $i < count($selects); $i++) {
1942              $alias = 'intsctal'.($aliascnt++);
1943              $rv .= " JOIN (".$selects[$i].") $alias ON ".
1944                  join(' AND ',
1945                      array_map(
1946                          function($a) use ($alias, $falias) {
1947                              return $falias . '.' . $a .' = ' . $alias . '.' . $a;
1948                          },
1949                          preg_split('/,/', $fields))
1950                  );
1951          }
1952          return $rv;
1953      }
1954  
1955      /**
1956       * Does this driver support tool_replace?
1957       *
1958       * @since Moodle 2.6.1
1959       * @return bool
1960       */
1961      public function replace_all_text_supported() {
1962          return true;
1963      }
1964  
1965      public function session_lock_supported() {
1966          return true;
1967      }
1968  
1969      /**
1970       * Obtain session lock
1971       * @param int $rowid id of the row with session record
1972       * @param int $timeout max allowed time to wait for the lock in seconds
1973       * @return void
1974       */
1975      public function get_session_lock($rowid, $timeout) {
1976          parent::get_session_lock($rowid, $timeout);
1977  
1978          $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1979          $sql = "SELECT GET_LOCK('$fullname', $timeout)";
1980          $this->query_start($sql, null, SQL_QUERY_AUX);
1981          $result = $this->mysqli->query($sql);
1982          $this->query_end($result);
1983  
1984          if ($result) {
1985              $arr = $result->fetch_assoc();
1986              $result->close();
1987  
1988              if (reset($arr) == 1) {
1989                  return;
1990              } else {
1991                  throw new dml_sessionwait_exception();
1992              }
1993          }
1994      }
1995  
1996      public function release_session_lock($rowid) {
1997          if (!$this->used_for_db_sessions) {
1998              return;
1999          }
2000  
2001          parent::release_session_lock($rowid);
2002          $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
2003          $sql = "SELECT RELEASE_LOCK('$fullname')";
2004          $this->query_start($sql, null, SQL_QUERY_AUX);
2005          $result = $this->mysqli->query($sql);
2006          $this->query_end($result);
2007  
2008          if ($result) {
2009              $result->close();
2010          }
2011      }
2012  
2013      /**
2014       * Are transactions supported?
2015       * It is not responsible to run productions servers
2016       * on databases without transaction support ;-)
2017       *
2018       * MyISAM does not support support transactions.
2019       *
2020       * You can override this via the dbtransactions option.
2021       *
2022       * @return bool
2023       */
2024      protected function transactions_supported() {
2025          if (!is_null($this->transactions_supported)) {
2026              return $this->transactions_supported;
2027          }
2028  
2029          // this is all just guessing, might be better to just specify it in config.php
2030          if (isset($this->dboptions['dbtransactions'])) {
2031              $this->transactions_supported = $this->dboptions['dbtransactions'];
2032              return $this->transactions_supported;
2033          }
2034  
2035          $this->transactions_supported = false;
2036  
2037          $engine = $this->get_dbengine();
2038  
2039          // Only will accept transactions if using compatible storage engine (more engines can be added easily BDB, Falcon...)
2040          if (in_array($engine, array('InnoDB', 'INNOBASE', 'BDB', 'XtraDB', 'Aria', 'Falcon'))) {
2041              $this->transactions_supported = true;
2042          }
2043  
2044          return $this->transactions_supported;
2045      }
2046  
2047      /**
2048       * Driver specific start of real database transaction,
2049       * this can not be used directly in code.
2050       * @return void
2051       */
2052      protected function begin_transaction() {
2053          if (!$this->transactions_supported()) {
2054              return;
2055          }
2056  
2057          $sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED";
2058          $this->query_start($sql, NULL, SQL_QUERY_AUX);
2059          $result = $this->mysqli->query($sql);
2060          $this->query_end($result);
2061  
2062          $sql = "START TRANSACTION";
2063          $this->query_start($sql, NULL, SQL_QUERY_AUX);
2064          $result = $this->mysqli->query($sql);
2065          $this->query_end($result);
2066      }
2067  
2068      /**
2069       * Driver specific commit of real database transaction,
2070       * this can not be used directly in code.
2071       * @return void
2072       */
2073      protected function commit_transaction() {
2074          if (!$this->transactions_supported()) {
2075              return;
2076          }
2077  
2078          $sql = "COMMIT";
2079          $this->query_start($sql, NULL, SQL_QUERY_AUX);
2080          $result = $this->mysqli->query($sql);
2081          $this->query_end($result);
2082      }
2083  
2084      /**
2085       * Driver specific abort of real database transaction,
2086       * this can not be used directly in code.
2087       * @return void
2088       */
2089      protected function rollback_transaction() {
2090          if (!$this->transactions_supported()) {
2091              return;
2092          }
2093  
2094          $sql = "ROLLBACK";
2095          $this->query_start($sql, NULL, SQL_QUERY_AUX);
2096          $result = $this->mysqli->query($sql);
2097          $this->query_end($result);
2098  
2099          return true;
2100      }
2101  
2102      /**
2103       * Converts a table to either 'Compressed' or 'Dynamic' row format.
2104       *
2105       * @param string $tablename Name of the table to convert to the new row format.
2106       */
2107      public function convert_table_row_format($tablename) {
2108          $currentrowformat = $this->get_row_format($tablename);
2109          if ($currentrowformat == 'Compact' || $currentrowformat == 'Redundant') {
2110              $rowformat = ($this->is_compressed_row_format_supported(false)) ? "ROW_FORMAT=Compressed" : "ROW_FORMAT=Dynamic";
2111              $prefix = $this->get_prefix();
2112              $this->change_database_structure("ALTER TABLE {$prefix}$tablename $rowformat");
2113          }
2114      }
2115  
2116      /**
2117       * Does this mysql instance support fulltext indexes?
2118       *
2119       * @return bool
2120       */
2121      public function is_fulltext_search_supported() {
2122          $info = $this->get_server_info();
2123  
2124          if (version_compare($info['version'], '5.6.4', '>=')) {
2125              return true;
2126          }
2127          return false;
2128      }
2129  
2130      /**
2131       * Fixes any table names that clash with reserved words.
2132       *
2133       * @param string $tablename The table name
2134       * @return string The fixed table name
2135       */
2136      protected function fix_table_name($tablename) {
2137          $prefixedtablename = parent::fix_table_name($tablename);
2138          // This function quotes the table name if it matches one of the MySQL reserved
2139          // words, e.g. groups.
2140          return $this->get_manager()->generator->getEncQuoted($prefixedtablename);
2141      }
2142  }