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