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