Merge branch 'MDL-65448-master' of git://github.com/lucaboesch/moodle
[moodle.git] / lib / dml / mysqli_native_moodle_database.php
CommitLineData
49926145 1<?php
49926145 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
49926145 17/**
18 * Native mysqli class representing moodle database interface.
19 *
00902cd9 20 * @package core_dml
49926145 21 * @copyright 2008 Petr Skoda (http://skodak.org)
22 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
23 */
7466a42f 24
f2ed3f05
PS
25defined('MOODLE_INTERNAL') || die();
26
00902cd9 27require_once(__DIR__.'/moodle_database.php');
46cfde3d 28require_once(__DIR__.'/moodle_read_slave_trait.php');
00902cd9
PS
29require_once(__DIR__.'/mysqli_native_moodle_recordset.php');
30require_once(__DIR__.'/mysqli_native_moodle_temptables.php');
7466a42f 31
32/**
33 * Native mysqli class representing moodle database interface.
a0eb2e97 34 *
00902cd9 35 * @package core_dml
a0eb2e97
AB
36 * @copyright 2008 Petr Skoda (http://skodak.org)
37 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
7466a42f 38 */
39class mysqli_native_moodle_database extends moodle_database {
46cfde3d
S
40 use moodle_read_slave_trait {
41 can_use_readonly as read_slave_can_use_readonly;
42 }
7466a42f 43
b4b03d38 44 /** @var mysqli $mysqli */
0487f9e2 45 protected $mysqli = null;
a45d54b8
PS
46 /** @var bool is compressed row format supported cache */
47 protected $compressedrowformatsupported = null;
7466a42f 48
d5a8d9aa
PS
49 private $transactions_supported = null;
50
30d2832d 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
6df26010 58 * @throws dml_exception A DML specific exception is thrown for any errors.
30d2832d 59 */
3b093310 60 public function create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null) {
797ffad0 61 $driverstatus = $this->driver_installed();
62
63 if ($driverstatus !== true) {
64 throw new dml_exception('dbdriverproblem', $driverstatus);
65 }
66
9e9543e5
PS
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'])) {
77f9f23b 74 $dbport = (int)ini_get('mysqli.default_port');
bac14015 75 } else {
9e9543e5 76 $dbport = (int)$dboptions['dbport'];
bac14015 77 }
77f9f23b
PS
78 // verify ini.get does not return nonsense
79 if (empty($dbport)) {
80 $dbport = 3306;
81 }
30d2832d 82 ob_start();
00902cd9 83 $conn = new mysqli($dbhost, $dbuser, $dbpass, '', $dbport, $dbsocket); // Connect without db
30d2832d 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
0bbefd81
AG
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)) {
1eece176 97 $collation = $dboptions['dbcollation'];
0bbefd81
AG
98 $collationinfo = explode('_', $dboptions['dbcollation']);
99 $charset = reset($collationinfo);
1eece176 100 } else {
0bbefd81 101 $collation = 'utf8mb4_unicode_ci';
1eece176
PS
102 }
103
0bbefd81 104 $result = $conn->query("CREATE DATABASE $dbname DEFAULT CHARACTER SET $charset DEFAULT COLLATE ".$collation);
30d2832d 105
106 $conn->close();
107
108 if (!$result) {
109 throw new dml_exception('cannotcreatedb');
110 }
111
112 return true;
113 }
114
7466a42f 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()
4a6c2ab8 139 * @return string db type mysqli, pgsql, oci, mssql, sqlsrv
7466a42f 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()
eb123deb 148 * @return string db type pdo, native
7466a42f 149 */
150 protected function get_dblibrary() {
151 return 'native';
152 }
153
d35ece6c
PS
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
ed047dab
PS
168 if ($this->external) {
169 return null;
170 }
171
d35ece6c
PS
172 $engine = null;
173
ed047dab
PS
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()) {
500d8650
MS
183 // MySQL 8 BC: information_schema.* returns the fields in upper case.
184 $rec = array_change_key_case($rec, CASE_LOWER);
ed047dab 185 $engine = $rec['engine'];
d35ece6c 186 }
ed047dab 187 $result->close();
d35ece6c
PS
188
189 if ($engine) {
ed047dab
PS
190 // Cache the result to improve performance.
191 $this->dboptions['dbengine'] = $engine;
d35ece6c
PS
192 return $engine;
193 }
194
57ee8956
MG
195 // Get the default database engine.
196 $sql = "SELECT @@default_storage_engine engine";
d35ece6c
PS
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()) {
57ee8956 201 $engine = $rec['engine'];
d35ece6c
PS
202 }
203 $result->close();
204
ed047dab 205 if ($engine === 'MyISAM') {
d35ece6c
PS
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);
ed22700d 211 $engines = array();
d35ece6c 212 while ($res = $result->fetch_assoc()) {
ed22700d
PS
213 if ($res['Support'] === 'YES' or $res['Support'] === 'DEFAULT') {
214 $engines[$res['Engine']] = true;
d35ece6c
PS
215 }
216 }
217 $result->close();
ed22700d
PS
218 if (isset($engines['InnoDB'])) {
219 $engine = 'InnoDB';
220 }
221 if (isset($engines['XtraDB'])) {
222 $engine = 'XtraDB';
223 }
d35ece6c
PS
224 }
225
ed047dab
PS
226 // Cache the result to improve performance.
227 $this->dboptions['dbengine'] = $engine;
d35ece6c
PS
228 return $engine;
229 }
230
7466a42f 231 /**
1eece176
PS
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 }
46cfde3d
S
242 }
243
244 /**
245 * Set 'dbcollation' option
246 *
247 * @return string $dbcollation
248 */
249 private function detect_collation(): string {
1eece176
PS
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'";
1eece176 261 $result = $this->mysqli->query($sql);
1eece176 262 if ($rec = $result->fetch_assoc()) {
500d8650
MS
263 // MySQL 8 BC: information_schema.* returns the fields in upper case.
264 $rec = array_change_key_case($rec, CASE_LOWER);
1eece176
PS
265 $collation = $rec['collation_name'];
266 }
267 $result->close();
268
0bbefd81 269
1eece176
PS
270 if (!$collation) {
271 // Get the default database collation, but only if using UTF-8.
272 $sql = "SELECT @@collation_database";
1eece176 273 $result = $this->mysqli->query($sql);
1eece176 274 if ($rec = $result->fetch_assoc()) {
0bbefd81 275 if (strpos($rec['@@collation_database'], 'utf8_') === 0 || strpos($rec['@@collation_database'], 'utf8mb4_') === 0) {
1eece176
PS
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;
0bbefd81 285 $sql = "SHOW COLLATION WHERE Collation LIKE 'utf8mb4\_%' AND Charset = 'utf8mb4'";
1eece176 286 $result = $this->mysqli->query($sql);
1eece176
PS
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
82c2d98f
MS
302 /**
303 * Tests if the Antelope file format is still supported or it has been removed.
304 * When removed, only Barracuda file format is supported, given the XtraDB/InnoDB engine.
305 *
306 * @return bool True if the Antelope file format has been removed; otherwise, false.
307 */
308 protected function is_antelope_file_format_no_more_supported() {
309 // Breaking change: Antelope file format support has been removed from both MySQL and MariaDB.
310 // The following InnoDB file format configuration parameters were deprecated and then removed:
311 // - innodb_file_format
312 // - innodb_file_format_check
313 // - innodb_file_format_max
314 // - innodb_large_prefix
315 // 1. MySQL: deprecated in 5.7.7 and removed 8.0.0+.
316 $ismysqlge8d0d0 = ($this->get_dbtype() == 'mysqli') &&
317 version_compare($this->get_server_info()['version'], '8.0.0', '>=');
318 // 2. MariaDB: deprecated in 10.2.0 and removed 10.3.1+.
319 $ismariadbge10d3d1 = ($this->get_dbtype() == 'mariadb') &&
320 version_compare($this->get_server_info()['version'], '10.3.1', '>=');
321
322 return $ismysqlge8d0d0 || $ismariadbge10d3d1;
323 }
324
a45d54b8
PS
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 */
0bbefd81 331 public function get_row_format($table = null) {
a45d54b8 332 $rowformat = null;
0bbefd81
AG
333 if (isset($table)) {
334 $table = $this->mysqli->real_escape_string($table);
335 $sql = "SELECT row_format
336 FROM INFORMATION_SCHEMA.TABLES
669de783 337 WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}$table'";
0bbefd81 338 } else {
82c2d98f
MS
339 if ($this->is_antelope_file_format_no_more_supported()) {
340 // Breaking change: Antelope file format support has been removed, only Barracuda.
740a9315
MS
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
0bbefd81
AG
350 $sql = "SHOW VARIABLES LIKE 'innodb_file_format'";
351 }
a45d54b8
PS
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()) {
500d8650
MS
356 // MySQL 8 BC: information_schema.* returns the fields in upper case.
357 $rec = array_change_key_case($rec, CASE_LOWER);
0bbefd81
AG
358 if (isset($table)) {
359 $rowformat = $rec['row_format'];
360 } else {
500d8650 361 $rowformat = $rec['value'];
0bbefd81 362 }
a45d54b8
PS
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
0bbefd81 393 } else if (!$this->is_file_per_table_enabled()) {
a45d54b8
PS
394 $this->compressedrowformatsupported = false;
395
0bbefd81 396 } else if ($this->get_row_format() !== 'Barracuda') {
a45d54b8
PS
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
0bbefd81
AG
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() {
82c2d98f
MS
427 if ($this->is_antelope_file_format_no_more_supported()) {
428 // Breaking change: Antelope file format support has been removed, only Barracuda.
740a9315
MS
429 return true;
430 }
431
0bbefd81
AG
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
1eece176 473 /**
7466a42f 474 * Returns localised database type name
475 * Note: can be used before connect()
476 * @return string
477 */
478 public function get_name() {
2aeb3bcb 479 return get_string('nativemysqli', 'install');
7466a42f 480 }
481
3b093310 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
d35ece6c
PS
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);
3c2fbdbb
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'";
d35ece6c
PS
505 $this->query_start($sql, null, SQL_QUERY_AUX);
506 $result = $this->mysqli->query($sql);
507 $this->query_end($result);
508 if ($result) {
3c2fbdbb
509 if ($arr = $result->fetch_assoc()) {
510 $count = reset($arr);
511 if ($count) {
d35ece6c 512 $sloppymyisamfound = true;
d35ece6c
PS
513 }
514 }
515 $result->close();
516 }
517
518 if ($sloppymyisamfound) {
519 return get_string('myisamproblem', 'error');
520 } else {
521 return null;
522 }
523 }
524
7466a42f 525 /**
526 * Connect to db
6df26010
AB
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
7466a42f 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 */
46cfde3d 535 public function raw_connect(string $dbhost, string $dbuser, string $dbpass, string $dbname, $prefix, array $dboptions=null): bool {
ce152606 536 $driverstatus = $this->driver_installed();
537
538 if ($driverstatus !== true) {
539 throw new dml_exception('dbdriverproblem', $driverstatus);
540 }
541
beaa43db 542 $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
7466a42f 543
79ac5a8c
PS
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'
8c4b142c
PS
546 if (!empty($this->dboptions['dbsocket'])
547 and (strpos($this->dboptions['dbsocket'], '/') !== false or strpos($this->dboptions['dbsocket'], '\\') !== false)) {
79ac5a8c
PS
548 $dbsocket = $this->dboptions['dbsocket'];
549 } else {
550 $dbsocket = ini_get('mysqli.default_socket');
551 }
feaddca9 552 if (empty($this->dboptions['dbport'])) {
77f9f23b 553 $dbport = (int)ini_get('mysqli.default_port');
feaddca9
AB
554 } else {
555 $dbport = (int)$this->dboptions['dbport'];
556 }
77f9f23b
PS
557 // verify ini.get does not return nonsense
558 if (empty($dbport)) {
559 $dbport = 3306;
560 }
33b5b9f9
PS
561 if ($dbhost and !empty($this->dboptions['dbpersist'])) {
562 $dbhost = "p:$dbhost";
563 }
46cfde3d
S
564 $this->mysqli = mysqli_init();
565 if (!empty($this->dboptions['connecttimeout'])) {
566 $this->mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, $this->dboptions['connecttimeout']);
567 }
ce152606 568
46cfde3d 569 $conn = null;
3612d0b8 570 $dberr = null;
46cfde3d 571 try {
317432ca
S
572 // real_connect() is doing things we don't expext.
573 $conn = @$this->mysqli->real_connect($dbhost, $dbuser, $dbpass, $dbname, $dbport, $dbsocket);
46cfde3d
S
574 } catch (\Exception $e) {
575 $dberr = "$e";
576 }
577 if (!$conn) {
578 $dberr = $dberr ?: $this->mysqli->connect_error;
a197c413 579 $this->mysqli = null;
ce152606 580 throw new dml_connection_exception($dberr);
7466a42f 581 }
ce152606 582
f2b5ed40
MG
583 // Disable logging until we are fully setup.
584 $this->query_log_prevent();
585
0bbefd81 586 if (isset($dboptions['dbcollation'])) {
46cfde3d 587 $collation = $this->dboptions['dbcollation'] = $dboptions['dbcollation'];
0bbefd81 588 } else {
46cfde3d 589 $collation = $this->detect_collation();
0bbefd81 590 }
46cfde3d 591 $collationinfo = explode('_', $collation);
0bbefd81
AG
592 $charset = reset($collationinfo);
593
0bbefd81 594 $this->mysqli->set_charset($charset);
a6283e91 595
77abbc47 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.
02df9cfd 599 // With strict mode enforced, Moodle DB layer will be consistently throwing
77abbc47 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'";
77abbc47 604 $result = $this->mysqli->query($sql);
77abbc47 605 }
606
c728b459
AA
607 // We can enable logging now.
608 $this->query_log_allow();
609
00902cd9 610 // Connection stabilised and configured, going to instantiate the temptables controller
3f33c9e2 611 $this->temptables = new mysqli_native_moodle_temptables($this);
612
7466a42f 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() {
02df9cfd 622 parent::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection
7466a42f 623 if ($this->mysqli) {
624 $this->mysqli->close();
625 $this->mysqli = null;
626 }
7466a42f 627 }
628
46cfde3d
S
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
7466a42f 663 /**
664 * Returns database server info array
6df26010 665 * @return array Array containing 'description' and 'version' info
7466a42f 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
6df26010 673 * @return int bitmask of accepted SQL_PARAMS_*
7466a42f 674 */
675 protected function allowed_param_types() {
676 return SQL_PARAMS_QM;
677 }
678
679 /**
680 * Returns last error reported by database engine.
9331d879 681 * @return string error message
7466a42f 682 */
683 public function get_last_error() {
684 return $this->mysqli->error;
685 }
686
687 /**
688 * Return tables in database WITHOUT current prefix
6df26010 689 * @param bool $usecache if true, returns list of cached tables.
7466a42f 690 * @return array of table names in lowercase and without prefix
691 */
117679db 692 public function get_tables($usecache=true) {
693 if ($usecache and $this->tables !== null) {
694 return $this->tables;
695 }
696 $this->tables = array();
04bfa67c
TM
697 $prefix = str_replace('_', '\\_', $this->prefix);
698 $sql = "SHOW TABLES LIKE '$prefix%'";
a6283e91 699 $this->query_start($sql, null, SQL_QUERY_AUX);
700 $result = $this->mysqli->query($sql);
701 $this->query_end($result);
c92e462b 702 $len = strlen($this->prefix);
a6283e91 703 if ($result) {
7466a42f 704 while ($arr = $result->fetch_assoc()) {
705 $tablename = reset($arr);
c92e462b 706 $tablename = substr($tablename, $len);
117679db 707 $this->tables[$tablename] = $tablename;
7466a42f 708 }
709 $result->close();
710 }
3f33c9e2 711
712 // Add the currently available temptables
713 $this->tables = array_merge($this->tables, $this->temptables->get_temptables());
117679db 714 return $this->tables;
7466a42f 715 }
716
717 /**
6df26010
AB
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
7466a42f 721 */
722 public function get_indexes($table) {
7466a42f 723 $indexes = array();
f12c5918
RW
724 $fixedtable = $this->fix_table_name($table);
725 $sql = "SHOW INDEXES FROM $fixedtable";
a6283e91 726 $this->query_start($sql, null, SQL_QUERY_AUX);
727 $result = $this->mysqli->query($sql);
a3d5830a
PS
728 try {
729 $this->query_end($result);
730 } catch (dml_read_exception $e) {
731 return $indexes; // table does not exist - no indexes...
732 }
a6283e91 733 if ($result) {
7466a42f 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 /**
b4d062c6
SR
749 * Fetches detailed information about columns in table.
750 *
7466a42f 751 * @param string $table name
cc5dba8e 752 * @return database_column_info[] array of database_column_info objects indexed with column names
7466a42f 753 */
b4d062c6 754 protected function fetch_columns(string $table): array {
9381983e 755 $structure = array();
7466a42f 756
bc09aa15
PS
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
669de783 760 WHERE table_name = '" . $this->prefix.$table . "'
bc09aa15
PS
761 AND table_schema = '" . $this->dbname . "'
762 ORDER BY ordinal_position";
a6283e91 763 $this->query_start($sql, null, SQL_QUERY_AUX);
764 $result = $this->mysqli->query($sql);
870896ec 765 $this->query_end(true); // Don't want to throw anything here ever. MDL-30147
a6283e91 766
767 if ($result === false) {
7466a42f 768 return array();
769 }
770
bc09aa15
PS
771 if ($result->num_rows > 0) {
772 // standard table exists
773 while ($rawcolumn = $result->fetch_assoc()) {
500d8650
MS
774 // MySQL 8 BC: information_schema.* returns the fields in upper case.
775 $rawcolumn = array_change_key_case($rawcolumn, CASE_LOWER);
bc09aa15 776 $info = (object)$this->get_column_info((object)$rawcolumn);
9381983e 777 $structure[$info->name] = new database_column_info($info);
bc09aa15
PS
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();
669de783 784 $fixedtable = $this->fix_table_name($table);
f12c5918 785 $sql = "SHOW COLUMNS FROM $fixedtable";
bc09aa15
PS
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);
dae6c5dc 801 $rawcolumn->column_key = $rawcolumn->key; unset($rawcolumn->key);
bc09aa15
PS
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];
633f3248
PS
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;
3c17c85a 823 } else {
633f3248
PS
824 // This should not happen.
825 $maxlength = 0;
826 }
827 if ($maxlength < $rawcolumn->max_length) {
828 $rawcolumn->max_length = $maxlength;
3c17c85a 829 }
bc09aa15
PS
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
7466a42f 848 } else {
bc09aa15 849 $rawcolumn->data_type = $rawcolumn->column_type;
7466a42f 850 }
851
bc09aa15 852 $info = $this->get_column_info($rawcolumn);
9381983e 853 $structure[$info->name] = new database_column_info($info);
bc09aa15
PS
854 }
855 $result->close();
856 }
857
9381983e 858 return $structure;
bc09aa15 859 }
75dfa4a3 860
0510d5c7
MS
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
f6b16e95
MS
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
bc09aa15
PS
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);
0510d5c7
MS
888 if ($this->has_breaking_change_quoted_defaults()) {
889 $info->default_value = trim($rawcolumn->column_default, "'");
2ec50fe3
MS
890 if ($info->default_value === 'NULL') {
891 $info->default_value = null;
892 }
0510d5c7
MS
893 } else {
894 $info->default_value = $rawcolumn->column_default;
895 }
2ec50fe3 896 $info->has_default = !is_null($info->default_value);
bc09aa15
PS
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;
7466a42f 912 }
3c17c85a 913 // Return number of decimals, not bytes here.
bc09aa15 914 $info->max_length = $rawcolumn->numeric_precision;
3c17c85a 915 if (preg_match('/([a-z]*int[a-z]*)\((\d+)\)/i', $rawcolumn->column_type, $matches)) {
633f3248
PS
916 $type = strtoupper($matches[1]);
917 if ($type === 'BIGINT') {
3c17c85a 918 $maxlength = 18;
633f3248 919 } else if ($type === 'INT' or $type === 'INTEGER') {
3c17c85a 920 $maxlength = 9;
633f3248
PS
921 } else if ($type === 'MEDIUMINT') {
922 $maxlength = 6;
923 } else if ($type === 'SMALLINT') {
3c17c85a 924 $maxlength = 4;
633f3248 925 } else if ($type === 'TINYINT') {
3c17c85a
PS
926 $maxlength = 2;
927 } else {
633f3248 928 // This should not happen.
3c17c85a
PS
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 }
bc09aa15
PS
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;
7466a42f 953
bc09aa15
PS
954 } else if ($info->meta_type === 'B') {
955 $info->max_length = -1;
956 $info->primary_key = false;
957 $info->binary = true;
7466a42f 958 }
959
bc09aa15
PS
960 return $info;
961 }
a6283e91 962
bc09aa15
PS
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':
633f3248 981 case 'INTEGER':
bc09aa15
PS
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;
7466a42f 1028 }
1029
8fb501e9
EL
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 */
e3acc8af 1037 protected function normalise_value($column, $value) {
e618cdf3
PS
1038 $this->detect_objects($value);
1039
8fb501e9
EL
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 }
7aa9e45f
EL
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";
8fb501e9 1051 }
8fb501e9
EL
1052 return $value;
1053 }
1054
7466a42f 1055 /**
9d0ded66 1056 * Is this database compatible with utf8?
7466a42f 1057 * @return bool
1058 */
1059 public function setup_is_unicodedb() {
9d0ded66
PS
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();
cd4f1859 1063
0bbefd81
AG
1064 $collationinfo = explode('_', $collation);
1065 $charset = reset($collationinfo);
1066
1067 $sql = "SHOW COLLATION WHERE Collation ='$collation' AND Charset = '$charset'";
9d0ded66 1068 $this->query_start($sql, NULL, SQL_QUERY_AUX);
cd4f1859
PS
1069 $result = $this->mysqli->query($sql);
1070 $this->query_end($result);
9d0ded66
PS
1071 if ($result->fetch_assoc()) {
1072 $return = true;
1073 } else {
1074 $return = false;
cd4f1859 1075 }
9d0ded66 1076 $result->close();
cd4f1859
PS
1077
1078 return $return;
7466a42f 1079 }
1080
7466a42f 1081 /**
1082 * Do NOT use in code, to be used by database_manager only!
3f17d709 1083 * @param string|array $sql query
87a3e501 1084 * @param array|null $tablenames an array of xmldb table names affected by this request.
22d77567 1085 * @return bool true
3f17d709 1086 * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors.
7466a42f 1087 */
87a3e501 1088 public function change_database_structure($sql, $tablenames = null) {
3f17d709 1089 $this->get_manager(); // Includes DDL exceptions classes ;-)
b4b03d38
1090 if (is_array($sql)) {
1091 $sql = implode("\n;\n", $sql);
1092 }
a6283e91 1093
3f17d709 1094 try {
b4b03d38
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 }
3f17d709 1105 }
b4b03d38 1106 $this->query_end(true);
3f17d709 1107 } catch (ddl_change_structure_exception $e) {
b4b03d38
1108 while (@$this->mysqli->more_results()) {
1109 @$this->mysqli->next_result();
1110 }
87a3e501 1111 $this->reset_caches($tablenames);
3f17d709
PS
1112 throw $e;
1113 }
a6283e91 1114
87a3e501 1115 $this->reset_caches($tablenames);
12e09c6b 1116 return true;
7466a42f 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 }
00902cd9 1127 // ok, we have verified sql statement with ? and correct number of params
4f3e38e7
ML
1128 $parts = array_reverse(explode('?', $sql));
1129 $return = array_pop($parts);
7466a42f 1130 foreach ($params as $param) {
1131 if (is_bool($param)) {
1132 $return .= (int)$param;
1133 } else if (is_null($param)) {
1134 $return .= 'NULL';
70ff13be
PS
1135 } else if (is_number($param)) {
1136 $return .= "'".$param."'"; // we have to always use strings because mysql is using weird automatic int casting
c1a6529b 1137 } else if (is_float($param)) {
7466a42f 1138 $return .= $param;
1139 } else {
1140 $param = $this->mysqli->real_escape_string($param);
1141 $return .= "'$param'";
1142 }
4f3e38e7 1143 $return .= array_pop($parts);
7466a42f 1144 }
1145 return $return;
1146 }
1147
1148 /**
1149 * Execute general sql query. Should be used only when no other method suitable.
a681b6c0 1150 * Do NOT use this to make changes in db structure, use database_manager methods instead!
7466a42f 1151 * @param string $sql query
1152 * @param array $params query parameters
22d77567 1153 * @return bool true
6df26010 1154 * @throws dml_exception A DML specific exception is thrown for any errors.
7466a42f 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) {
22d77567 1160 throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
7466a42f 1161 }
1162
1163 $rawsql = $this->emulate_bound_params($sql, $params);
1164
a6283e91 1165 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
7466a42f 1166 $result = $this->mysqli->query($rawsql);
a6283e91 1167 $this->query_end($result);
7466a42f 1168
22d77567 1169 if ($result === true) {
7466a42f 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 *
dafa20e8
AB
1185 * The return type is like:
1186 * @see function get_recordset.
7466a42f 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).
5212b07f 1192 * @return moodle_recordset instance
6df26010 1193 * @throws dml_exception A DML specific exception is thrown for any errors.
7466a42f 1194 */
1195 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
c582565a
DP
1196
1197 list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum);
3ff8bf26 1198
7466a42f 1199 if ($limitfrom or $limitnum) {
7466a42f 1200 if ($limitnum < 1) {
1201 $limitnum = "18446744073709551615";
1202 }
0487f9e2 1203 $sql .= " LIMIT $limitfrom, $limitnum";
7466a42f 1204 }
1205
0487f9e2 1206 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1207 $rawsql = $this->emulate_bound_params($sql, $params);
1208
a6283e91 1209 $this->query_start($sql, $params, SQL_QUERY_SELECT);
7466a42f 1210 // no MYSQLI_USE_RESULT here, it would block write ops on affected tables
e0eda209 1211 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT);
1212 $this->query_end($result);
7466a42f 1213
e0eda209 1214 return $this->create_recordset($result);
7466a42f 1215 }
1216
cabc4112
PS
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
7466a42f 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 *
dafa20e8
AB
1245 * Return value is like:
1246 * @see function get_records.
7466a42f 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).
3503dcad 1254 * @return array of objects, or empty array if no records were found
6df26010 1255 * @throws dml_exception A DML specific exception is thrown for any errors.
7466a42f 1256 */
1257 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
c582565a
DP
1258
1259 list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum);
3ff8bf26 1260
7466a42f 1261 if ($limitfrom or $limitnum) {
7466a42f 1262 if ($limitnum < 1) {
1263 $limitnum = "18446744073709551615";
1264 }
0487f9e2 1265 $sql .= " LIMIT $limitfrom, $limitnum";
7466a42f 1266 }
1267
0487f9e2 1268 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1269 $rawsql = $this->emulate_bound_params($sql, $params);
1270
a6283e91 1271 $this->query_start($sql, $params, SQL_QUERY_SELECT);
7466a42f 1272 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT);
a6283e91 1273 $this->query_end($result);
7466a42f 1274
7466a42f 1275 $return = array();
a6283e91 1276
7466a42f 1277 while($row = $result->fetch_assoc()) {
1278 $row = array_change_key_case($row, CASE_LOWER);
1279 $id = reset($row);
758ba89a 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 }
7466a42f 1284 $return[$id] = (object)$row;
1285 }
1286 $result->close();
a6283e91 1287
7466a42f 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
3503dcad 1296 * @return array of values
6df26010 1297 * @throws dml_exception A DML specific exception is thrown for any errors.
7466a42f 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
a6283e91 1303 $this->query_start($sql, $params, SQL_QUERY_SELECT);
7466a42f 1304 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT);
a6283e91 1305 $this->query_end($result);
7466a42f 1306
7466a42f 1307 $return = array();
a6283e91 1308
7466a42f 1309 while($row = $result->fetch_assoc()) {
1310 $return[] = reset($row);
1311 }
1312 $result->close();
a6283e91 1313
7466a42f 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
3503dcad 1324 * @return bool|int true or new id
6df26010 1325 * @throws dml_exception A DML specific exception is thrown for any errors.
7466a42f 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'])) {
22d77567 1334 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
7466a42f 1335 }
1336 $returnid = false;
1337 } else {
1338 unset($params['id']);
1339 }
1340
1341 if (empty($params)) {
22d77567 1342 throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
7466a42f 1343 }
1344
7466a42f 1345 $fields = implode(',', array_keys($params));
1346 $qms = array_fill(0, count($params), '?');
1347 $qms = implode(',', $qms);
f12c5918
RW
1348 $fixedtable = $this->fix_table_name($table);
1349 $sql = "INSERT INTO $fixedtable ($fields) VALUES($qms)";
76b6daf2 1350
1351 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
7466a42f 1352 $rawsql = $this->emulate_bound_params($sql, $params);
1353
a6283e91 1354 $this->query_start($sql, $params, SQL_QUERY_INSERT);
7466a42f 1355 $result = $this->mysqli->query($rawsql);
b4154c2d 1356 $id = @$this->mysqli->insert_id; // must be called before query_end() which may insert log into db
a6283e91 1357 $this->query_end($result);
7466a42f 1358
3b4f7338 1359 if (!$customsequence and !$id) {
22d77567 1360 throw new dml_write_exception('unknown error fetching inserted id');
7466a42f 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 $data 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.
3503dcad 1379 * @return bool|int true or new id
6df26010 1380 * @throws dml_exception A DML specific exception is thrown for any errors.
7466a42f 1381 */
1382 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
d8fa8e40 1383 $dataobject = (array)$dataobject;
7466a42f 1384
1385 $columns = $this->get_columns($table);
9c7dfbe6
RS
1386 if (empty($columns)) {
1387 throw new dml_exception('ddltablenotexist', $table);
1388 }
1389
7466a42f 1390 $cleaned = array();
1391
1392 foreach ($dataobject as $field=>$value) {
d8fa8e40
PS
1393 if ($field === 'id') {
1394 continue;
1395 }
7466a42f 1396 if (!isset($columns[$field])) {
1397 continue;
1398 }
1399 $column = $columns[$field];
8fb501e9 1400 $cleaned[$field] = $this->normalise_value($column, $value);
7466a42f 1401 }
1402
7466a42f 1403 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
1404 }
1405
cc5dba8e
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 *
5bcfd504 1415 * @since Moodle 2.7
cc5dba8e
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
f12c5918
RW
1514 $fixedtable = $this->fix_table_name($table);
1515 $sql = "INSERT INTO $fixedtable $fieldssql VALUES $valuessql";
cc5dba8e
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
7466a42f 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
22d77567 1531 * @return bool true
6df26010 1532 * @throws dml_exception A DML specific exception is thrown for any errors.
7466a42f 1533 */
1534 public function import_record($table, $dataobject) {
d8fa8e40 1535 $dataobject = (array)$dataobject;
7466a42f 1536
7466a42f 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
22d77567 1555 * @return bool true
6df26010 1556 * @throws dml_exception A DML specific exception is thrown for any errors.
7466a42f 1557 */
1558 public function update_record_raw($table, $params, $bulk=false) {
d8fa8e40
PS
1559 $params = (array)$params;
1560
7466a42f 1561 if (!isset($params['id'])) {
22d77567 1562 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
7466a42f 1563 }
1564 $id = $params['id'];
1565 unset($params['id']);
1566
1567 if (empty($params)) {
22d77567 1568 throw new coding_exception('moodle_database::update_record_raw() no fields found.');
7466a42f 1569 }
1570
7466a42f 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);
f12c5918
RW
1579 $fixedtable = $this->fix_table_name($table);
1580 $sql = "UPDATE $fixedtable SET $sets WHERE id=?";
76b6daf2 1581
1582 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
7466a42f 1583 $rawsql = $this->emulate_bound_params($sql, $params);
1584
a6283e91 1585 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
7466a42f 1586 $result = $this->mysqli->query($rawsql);
a6283e91 1587 $this->query_end($result);
7466a42f 1588
7466a42f 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
22d77567 1602 * @return bool true
6df26010 1603 * @throws dml_exception A DML specific exception is thrown for any errors.
7466a42f 1604 */
1605 public function update_record($table, $dataobject, $bulk=false) {
d8fa8e40 1606 $dataobject = (array)$dataobject;
7466a42f 1607
7466a42f 1608 $columns = $this->get_columns($table);
1609 $cleaned = array();
1610
1611 foreach ($dataobject as $field=>$value) {
1612 if (!isset($columns[$field])) {
1613 continue;
1614 }
8fb501e9
EL
1615 $column = $columns[$field];
1616 $cleaned[$field] = $this->normalise_value($column, $value);
7466a42f 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
22d77567 1630 * @return bool true
6df26010 1631 * @throws dml_exception A DML specific exception is thrown for any errors.
7466a42f 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
8fb501e9
EL
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)) {
7466a42f 1649 $newfield = "$newfield = NULL";
1650 } else {
1651 $newfield = "$newfield = ?";
8fb501e9 1652 array_unshift($params, $normalised_value);
7466a42f 1653 }
f12c5918
RW
1654 $fixedtable = $this->fix_table_name($table);
1655 $sql = "UPDATE $fixedtable SET $newfield $select";
7466a42f 1656 $rawsql = $this->emulate_bound_params($sql, $params);
1657
a6283e91 1658 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
7466a42f 1659 $result = $this->mysqli->query($rawsql);
a6283e91 1660 $this->query_end($result);
7466a42f 1661
7466a42f 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
22d77567 1671 * @return bool true
6df26010 1672 * @throws dml_exception A DML specific exception is thrown for any errors.
7466a42f 1673 */
1674 public function delete_records_select($table, $select, array $params=null) {
1675 if ($select) {
1676 $select = "WHERE $select";
1677 }
f12c5918
RW
1678 $fixedtable = $this->fix_table_name($table);
1679 $sql = "DELETE FROM $fixedtable $select";
7466a42f 1680
1681 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1682 $rawsql = $this->emulate_bound_params($sql, $params);
1683
a6283e91 1684 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
7466a42f 1685 $result = $this->mysqli->query($rawsql);
a6283e91 1686 $this->query_end($result);
7466a42f 1687
7466a42f 1688 return true;
1689 }
1690
edb5cd0d 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
7466a42f 1708 public function sql_cast_char2int($fieldname, $text=false) {
1709 return ' CAST(' . $fieldname . ' AS SIGNED) ';
9882b2c8
EL
1710 }
1711
1712 public function sql_cast_char2real($fieldname, $text=false) {
4ae65302
EL
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)) ';
7466a42f 1718 }
1719
9d5c12be
EL
1720 public function sql_equal($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notequal = false) {
1721 $equalop = $notequal ? '<>' : '=';
0bbefd81
AG
1722
1723 $collationinfo = explode('_', $this->get_dbcollation());
1724 $bincollate = reset($collationinfo) . '_bin';
1725
9d5c12be
EL
1726 if ($casesensitive) {
1727 // Current MySQL versions do not support case sensitive and accent insensitive.
0bbefd81 1728 return "$fieldname COLLATE $bincollate $equalop $param";
9d5c12be
EL
1729 } else if ($accentsensitive) {
1730 // Case insensitive and accent sensitive, we can force a binary comparison once all texts are using the same case.
0bbefd81 1731 return "LOWER($fieldname) COLLATE $bincollate $equalop LOWER($param)";
9d5c12be
EL
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';
0bbefd81
AG
1737 } else if ($this->get_dbcollation() == 'utf8mb4_bin') {
1738 $collation = 'COLLATE utf8mb4_unicode_ci';
9d5c12be
EL
1739 }
1740 return "$fieldname $collation $equalop $param";
1741 }
1742 }
1743
6055f89d
PS
1744 /**
1745 * Returns 'LIKE' part of a query.
1746 *
2bdeb8ef
DM
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 *
6055f89d
PS
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
2bdeb8ef 1753 * @param bool $accensensitive use accent sensitive search (ignored if $casesensitive is true)
16114b9d 1754 * @param bool $notlike true means "NOT LIKE"
6055f89d
PS
1755 * @param string $escapechar escape char for '%' and '_'
1756 * @return string SQL code fragment
1757 */
16114b9d 1758 public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
6055f89d 1759 if (strpos($param, '%') !== false) {
de640a2d 1760 debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)');
6055f89d
PS
1761 }
1762 $escapechar = $this->mysqli->real_escape_string($escapechar); // prevents problems with C-style escapes of enclosing '\'
1763
0bbefd81
AG
1764 $collationinfo = explode('_', $this->get_dbcollation());
1765 $bincollate = reset($collationinfo) . '_bin';
1766
16114b9d 1767 $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
2bdeb8ef 1768
6055f89d 1769 if ($casesensitive) {
2bdeb8ef 1770 // Current MySQL versions do not support case sensitive and accent insensitive.
0bbefd81 1771 return "$fieldname $LIKE $param COLLATE $bincollate ESCAPE '$escapechar'";
2bdeb8ef
DM
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.
0bbefd81 1775 return "LOWER($fieldname) $LIKE LOWER($param) COLLATE $bincollate ESCAPE '$escapechar'";
2bdeb8ef 1776
6055f89d 1777 } else {
2bdeb8ef
DM
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';
0bbefd81
AG
1783 } else if ($this->get_dbcollation() == 'utf8mb4_bin') {
1784 // Force a case insensitive comparison if using utf8mb4_bin.
1785 $collation = 'COLLATE utf8mb4_unicode_ci';
6055f89d 1786 }
2bdeb8ef
DM
1787
1788 return "$fieldname $LIKE $param $collation ESCAPE '$escapechar'";
6055f89d
PS
1789 }
1790 }
1791
215d7cb8
SH
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 */
7466a42f 1800 public function sql_concat() {
1801 $arr = func_get_args();
082ae821 1802 $s = implode(', ', $arr);
7466a42f 1803 if ($s === '') {
d5d0890c 1804 return "''";
7466a42f 1805 }
1806 return "CONCAT($s)";
1807 }
1808
215d7cb8
SH
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 */
7466a42f 1817 public function sql_concat_join($separator="' '", $elements=array()) {
082ae821 1818 $s = implode(', ', $elements);
1819
7466a42f 1820 if ($s === '') {
d5d0890c 1821 return "''";
7466a42f 1822 }
082ae821 1823 return "CONCAT_WS($separator, $s)";
7466a42f 1824 }
1825
7e0db2e2 1826 /**
1827 * Returns the SQL text to be used to calculate the length in characters of one expression.
1828 * @param string fieldname or expression to calculate its length in characters.
1829 * @return string the piece of SQL code to be used in the statement.
1830 */
1831 public function sql_length($fieldname) {
1832 return ' CHAR_LENGTH(' . $fieldname . ')';
1833 }
1834
7466a42f 1835 /**
02df9cfd 1836 * Does this driver support regex syntax when searching
7466a42f 1837 */
1838 public function sql_regex_supported() {
1839 return true;
1840 }
1841
1842 /**
1843 * Return regex positive or negative match sql
1844 * @param bool $positivematch
109fbd5a 1845 * @param bool $casesensitive
7466a42f 1846 * @return string or empty if not supported
1847 */
109fbd5a
MG
1848 public function sql_regex($positivematch = true, $casesensitive = false) {
1849 $collation = '';
1850 if ($casesensitive) {
1851 if (substr($this->get_dbcollation(), -4) !== '_bin') {
1852 $collationinfo = explode('_', $this->get_dbcollation());
1853 $collation = 'COLLATE ' . $collationinfo[0] . '_bin ';
1854 }
1855 } else {
1856 if ($this->get_dbcollation() == 'utf8_bin') {
1857 $collation = 'COLLATE utf8_unicode_ci ';
1858 } else if ($this->get_dbcollation() == 'utf8mb4_bin') {
1859 $collation = 'COLLATE utf8mb4_unicode_ci ';
1860 }
1861 }
1862
1863 return $collation . ($positivematch ? 'REGEXP' : 'NOT REGEXP');
7466a42f 1864 }
fa76662b 1865
7e522ccb
PS
1866 /**
1867 * Returns the SQL to be used in order to an UNSIGNED INTEGER column to SIGNED.
1868 *
1869 * @deprecated since 2.3
1870 * @param string $fieldname The name of the field to be cast
1871 * @return string The piece of SQL code to be used in your statement.
1872 */
adff97c5 1873 public function sql_cast_2signed($fieldname) {
1874 return ' CAST(' . $fieldname . ' AS SIGNED) ';
1875 }
1876
3b4db5bd
MG
1877 /**
1878 * Returns the SQL that allows to find intersection of two or more queries
1879 *
1880 * @since Moodle 2.8
1881 *
1882 * @param array $selects array of SQL select queries, each of them only returns fields with the names from $fields
1883 * @param string $fields comma-separated list of fields
1884 * @return string SQL query that will return only values that are present in each of selects
1885 */
1886 public function sql_intersect($selects, $fields) {
1887 if (count($selects) <= 1) {
1888 return parent::sql_intersect($selects, $fields);
1889 }
1890 $fields = preg_replace('/\s/', '', $fields);
1891 static $aliascnt = 0;
1892 $falias = 'intsctal'.($aliascnt++);
1893 $rv = "SELECT $falias.".
1894 preg_replace('/,/', ','.$falias.'.', $fields).
1895 " FROM ($selects[0]) $falias";
1896 for ($i = 1; $i < count($selects); $i++) {
1897 $alias = 'intsctal'.($aliascnt++);
1898 $rv .= " JOIN (".$selects[$i].") $alias ON ".
1899 join(' AND ',
1900 array_map(
484b43f4
MG
1901 function($a) use ($alias, $falias) {
1902 return $falias . '.' . $a .' = ' . $alias . '.' . $a;
1903 },
3b4db5bd
MG
1904 preg_split('/,/', $fields))
1905 );
1906 }
1907 return $rv;
1908 }
1909
6c3ae510
PS
1910 /**
1911 * Does this driver support tool_replace?
1912 *
5bcfd504 1913 * @since Moodle 2.6.1
6c3ae510
PS
1914 * @return bool
1915 */
1916 public function replace_all_text_supported() {
1917 return true;
1918 }
1919
5e9dd017 1920 public function session_lock_supported() {
1921 return true;
1922 }
1923
2b0e3941
PS
1924 /**
1925 * Obtain session lock
1926 * @param int $rowid id of the row with session record
1927 * @param int $timeout max allowed time to wait for the lock in seconds
00902cd9 1928 * @return void
2b0e3941
PS
1929 */
1930 public function get_session_lock($rowid, $timeout) {
1931 parent::get_session_lock($rowid, $timeout);
1932
3b1a9849 1933 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
2b0e3941 1934 $sql = "SELECT GET_LOCK('$fullname', $timeout)";
3b1a9849 1935 $this->query_start($sql, null, SQL_QUERY_AUX);
1936 $result = $this->mysqli->query($sql);
7f79aaea 1937 $this->query_end($result);
1938
1939 if ($result) {
1940 $arr = $result->fetch_assoc();
1941 $result->close();
1942
1943 if (reset($arr) == 1) {
5e9dd017 1944 return;
3b1a9849 1945 } else {
2b0e3941 1946 throw new dml_sessionwait_exception();
7f79aaea 1947 }
1948 }
7f79aaea 1949 }
1950
3b1a9849 1951 public function release_session_lock($rowid) {
46a86dbb
1952 if (!$this->used_for_db_sessions) {
1953 return;
1954 }
1955
5e9dd017 1956 parent::release_session_lock($rowid);
3b1a9849 1957 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1958 $sql = "SELECT RELEASE_LOCK('$fullname')";
1959 $this->query_start($sql, null, SQL_QUERY_AUX);
1960 $result = $this->mysqli->query($sql);
7f79aaea 1961 $this->query_end($result);
1962
1963 if ($result) {
7f79aaea 1964 $result->close();
7f79aaea 1965 }
7f79aaea 1966 }
1967
fa76662b 1968 /**
d5a8d9aa
PS
1969 * Are transactions supported?
1970 * It is not responsible to run productions servers
1971 * on databases without transaction support ;-)
1972 *
1973 * MyISAM does not support support transactions.
fa76662b 1974 *
d35ece6c
PS
1975 * You can override this via the dbtransactions option.
1976 *
d5a8d9aa 1977 * @return bool
fa76662b 1978 */
d5a8d9aa
PS
1979 protected function transactions_supported() {
1980 if (!is_null($this->transactions_supported)) {
1981 return $this->transactions_supported;
1982 }
1983
d35ece6c
PS
1984 // this is all just guessing, might be better to just specify it in config.php
1985 if (isset($this->dboptions['dbtransactions'])) {
1986 $this->transactions_supported = $this->dboptions['dbtransactions'];
1987 return $this->transactions_supported;
1988 }
1989
d5a8d9aa
PS
1990 $this->transactions_supported = false;
1991
d35ece6c 1992 $engine = $this->get_dbengine();
d5a8d9aa 1993
d35ece6c
PS
1994 // Only will accept transactions if using compatible storage engine (more engines can be added easily BDB, Falcon...)
1995 if (in_array($engine, array('InnoDB', 'INNOBASE', 'BDB', 'XtraDB', 'Aria', 'Falcon'))) {
1996 $this->transactions_supported = true;
dd67e10c 1997 }
dd67e10c 1998
d5a8d9aa
PS
1999 return $this->transactions_supported;
2000 }
2001
2002 /**
2003 * Driver specific start of real database transaction,
2004 * this can not be used directly in code.
2005 * @return void
2006 */
2007 protected function begin_transaction() {
2008 if (!$this->transactions_supported()) {
2009 return;
a1dda107 2010 }
dd67e10c 2011
a6283e91 2012 $sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED";
2013 $this->query_start($sql, NULL, SQL_QUERY_AUX);
2014 $result = $this->mysqli->query($sql);
2015 $this->query_end($result);
2016
dd67e10c 2017 $sql = "START TRANSACTION";
a6283e91 2018 $this->query_start($sql, NULL, SQL_QUERY_AUX);
2019 $result = $this->mysqli->query($sql);
2020 $this->query_end($result);
fa76662b 2021 }
2022
2023 /**
d5a8d9aa
PS
2024 * Driver specific commit of real database transaction,
2025 * this can not be used directly in code.
2026 * @return void
fa76662b 2027 */
d5a8d9aa
PS
2028 protected function commit_transaction() {
2029 if (!$this->transactions_supported()) {
2030 return;
a1dda107 2031 }
d5a8d9aa 2032
a6283e91 2033 $sql = "COMMIT";
2034 $this->query_start($sql, NULL, SQL_QUERY_AUX);
2035 $result = $this->mysqli->query($sql);
2036 $this->query_end($result);
fa76662b 2037 }
2038
2039 /**
d5a8d9aa
PS
2040 * Driver specific abort of real database transaction,
2041 * this can not be used directly in code.
2042 * @return void
fa76662b 2043 */
d5a8d9aa
PS
2044 protected function rollback_transaction() {
2045 if (!$this->transactions_supported()) {
2046 return;
a1dda107 2047 }
d5a8d9aa 2048
a6283e91 2049 $sql = "ROLLBACK";
2050 $this->query_start($sql, NULL, SQL_QUERY_AUX);
2051 $result = $this->mysqli->query($sql);
2052 $this->query_end($result);
2053
fa76662b 2054 return true;
2055 }
3a8c9891
AG
2056
2057 /**
2058 * Converts a table to either 'Compressed' or 'Dynamic' row format.
2059 *
2060 * @param string $tablename Name of the table to convert to the new row format.
2061 */
2062 public function convert_table_row_format($tablename) {
2063 $currentrowformat = $this->get_row_format($tablename);
2064 if ($currentrowformat == 'Compact' || $currentrowformat == 'Redundant') {
2065 $rowformat = ($this->is_compressed_row_format_supported(false)) ? "ROW_FORMAT=Compressed" : "ROW_FORMAT=Dynamic";
2066 $prefix = $this->get_prefix();
2067 $this->change_database_structure("ALTER TABLE {$prefix}$tablename $rowformat");
2068 }
2069 }
c2e97077
DM
2070
2071 /**
2072 * Does this mysql instance support fulltext indexes?
2073 *
2074 * @return bool
2075 */
2076 public function is_fulltext_search_supported() {
2077 $info = $this->get_server_info();
2078
2079 if (version_compare($info['version'], '5.6.4', '>=')) {
2080 return true;
2081 }
2082 return false;
2083 }
30d2b9cd
RW
2084
2085 /**
2086 * Fixes any table names that clash with reserved words.
2087 *
2088 * @param string $tablename The table name
2089 * @return string The fixed table name
2090 */
2091 protected function fix_table_name($tablename) {
2092 $prefixedtablename = parent::fix_table_name($tablename);
2093 // This function quotes the table name if it matches one of the MySQL reserved
2094 // words, e.g. groups.
2095 return $this->get_manager()->generator->getEncQuoted($prefixedtablename);
2096 }
7466a42f 2097}