MDL-25910 add support for custom mysql socket location
[moodle.git] / lib / dml / mysqli_native_moodle_database.php
CommitLineData
49926145 1<?php
2
3// This file is part of Moodle - http://moodle.org/
4//
5// Moodle is free software: you can redistribute it and/or modify
6// it under the terms of the GNU General Public License as published by
7// the Free Software Foundation, either version 3 of the License, or
8// (at your option) any later version.
9//
10// Moodle is distributed in the hope that it will be useful,
11// but WITHOUT ANY WARRANTY; without even the implied warranty of
12// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13// GNU General Public License for more details.
14//
15// You should have received a copy of the GNU General Public License
16// along with Moodle. If not, see <http://www.gnu.org/licenses/>.
17
18
19/**
20 * Native mysqli class representing moodle database interface.
21 *
66c0ee78 22 * @package core
f2ed3f05 23 * @subpackage dml
49926145 24 * @copyright 2008 Petr Skoda (http://skodak.org)
25 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
26 */
7466a42f 27
f2ed3f05
PS
28defined('MOODLE_INTERNAL') || die();
29
7466a42f 30require_once($CFG->libdir.'/dml/moodle_database.php');
31require_once($CFG->libdir.'/dml/mysqli_native_moodle_recordset.php');
3f33c9e2 32require_once($CFG->libdir.'/dml/mysqli_native_moodle_temptables.php');
7466a42f 33
34/**
35 * Native mysqli class representing moodle database interface.
7466a42f 36 */
37class mysqli_native_moodle_database extends moodle_database {
38
0487f9e2 39 protected $mysqli = null;
7466a42f 40
d5a8d9aa
PS
41 private $transactions_supported = null;
42
30d2832d 43 /**
44 * Attempt to create the database
45 * @param string $dbhost
46 * @param string $dbuser
47 * @param string $dbpass
48 * @param string $dbname
49 * @return bool success
50 * @throws dml_exception if error
51 */
3b093310 52 public function create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null) {
797ffad0 53 $driverstatus = $this->driver_installed();
54
55 if ($driverstatus !== true) {
56 throw new dml_exception('dbdriverproblem', $driverstatus);
57 }
58
bac14015
AB
59 if (empty($this->dboptions['dbport'])) {
60 $dbport = ini_get('mysqli.default_port');
61 } else {
62 $dbport = (int)$this->dboptions['dbport'];
63 }
30d2832d 64 ob_start();
bac14015 65 $conn = new mysqli($dbhost, $dbuser, $dbpass, '', $dbport); /// Connect without db
30d2832d 66 $dberr = ob_get_contents();
67 ob_end_clean();
68 $errorno = @$conn->connect_errno;
69
70 if ($errorno !== 0) {
71 throw new dml_connection_exception($dberr);
72 }
73
74 $result = $conn->query("CREATE DATABASE $dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci");
75
76 $conn->close();
77
78 if (!$result) {
79 throw new dml_exception('cannotcreatedb');
80 }
81
82 return true;
83 }
84
7466a42f 85 /**
86 * Detects if all needed PHP stuff installed.
87 * Note: can be used before connect()
88 * @return mixed true if ok, string if something
89 */
90 public function driver_installed() {
91 if (!extension_loaded('mysqli')) {
92 return get_string('mysqliextensionisnotpresentinphp', 'install');
93 }
94 return true;
95 }
96
97 /**
98 * Returns database family type - describes SQL dialect
99 * Note: can be used before connect()
100 * @return string db family name (mysql, postgres, mssql, oracle, etc.)
101 */
102 public function get_dbfamily() {
103 return 'mysql';
104 }
105
106 /**
107 * Returns more specific database driver type
108 * Note: can be used before connect()
4a6c2ab8 109 * @return string db type mysqli, pgsql, oci, mssql, sqlsrv
7466a42f 110 */
111 protected function get_dbtype() {
112 return 'mysqli';
113 }
114
115 /**
116 * Returns general database library name
117 * Note: can be used before connect()
eb123deb 118 * @return string db type pdo, native
7466a42f 119 */
120 protected function get_dblibrary() {
121 return 'native';
122 }
123
d35ece6c
PS
124 /**
125 * Returns the current MySQL db engine.
126 *
127 * This is an ugly workaround for MySQL default engine problems,
128 * Moodle is designed to work best on ACID compliant databases
129 * with full transaction support. Do not use MyISAM.
130 *
131 * @return string or null MySQL engine name
132 */
133 public function get_dbengine() {
134 if (isset($this->dboptions['dbengine'])) {
135 return $this->dboptions['dbengine'];
136 }
137
138 $engine = null;
139
140 if (!$this->external) {
141 // look for current engine of our config table (the first table that gets created),
142 // so that we create all tables with the same engine
143 $sql = "SELECT engine FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = '{$this->prefix}config'";
144 $this->query_start($sql, NULL, SQL_QUERY_AUX);
145 $result = $this->mysqli->query($sql);
146 $this->query_end($result);
147 if ($rec = $result->fetch_assoc()) {
148 $engine = $rec['engine'];
149 }
150 $result->close();
151 }
152
153 if ($engine) {
154 return $engine;
155 }
156
157 // get the default database engine
158 $sql = "SELECT @@storage_engine";
159 $this->query_start($sql, NULL, SQL_QUERY_AUX);
160 $result = $this->mysqli->query($sql);
161 $this->query_end($result);
162 if ($rec = $result->fetch_assoc()) {
163 $engine = $rec['@@storage_engine'];
164 }
165 $result->close();
166
167 if (!$this->external and $engine === 'MyISAM') {
168 // we really do not want MyISAM for Moodle, InnoDB or XtraDB is a reasonable defaults if supported
169 $sql = "SHOW STORAGE ENGINES";
170 $this->query_start($sql, NULL, SQL_QUERY_AUX);
171 $result = $this->mysqli->query($sql);
172 $this->query_end($result);
ed22700d 173 $engines = array();
d35ece6c 174 while ($res = $result->fetch_assoc()) {
ed22700d
PS
175 if ($res['Support'] === 'YES' or $res['Support'] === 'DEFAULT') {
176 $engines[$res['Engine']] = true;
d35ece6c
PS
177 }
178 }
179 $result->close();
ed22700d
PS
180 if (isset($engines['InnoDB'])) {
181 $engine = 'InnoDB';
182 }
183 if (isset($engines['XtraDB'])) {
184 $engine = 'XtraDB';
185 }
d35ece6c
PS
186 }
187
188 return $engine;
189 }
190
7466a42f 191 /**
192 * Returns localised database type name
193 * Note: can be used before connect()
194 * @return string
195 */
196 public function get_name() {
2aeb3bcb 197 return get_string('nativemysqli', 'install');
7466a42f 198 }
199
3b093310 200 /**
201 * Returns localised database configuration help.
202 * Note: can be used before connect()
203 * @return string
204 */
205 public function get_configuration_help() {
206 return get_string('nativemysqlihelp', 'install');
207 }
208
7466a42f 209 /**
210 * Returns localised database description
211 * Note: can be used before connect()
212 * @return string
213 */
214 public function get_configuration_hints() {
2aeb3bcb 215 return get_string('databasesettingssub_mysqli', 'install');
7466a42f 216 }
217
d35ece6c
PS
218 /**
219 * Diagnose database and tables, this function is used
220 * to verify database and driver settings, db engine types, etc.
221 *
222 * @return string null means everything ok, string means problem found.
223 */
224 public function diagnose() {
225 $sloppymyisamfound = false;
226 $prefix = str_replace('_', '\\_', $this->prefix);
227 $sql = "SHOW TABLE STATUS WHERE Name LIKE BINARY '$prefix%'";
228 $this->query_start($sql, null, SQL_QUERY_AUX);
229 $result = $this->mysqli->query($sql);
230 $this->query_end($result);
231 if ($result) {
232 while ($arr = $result->fetch_assoc()) {
233 if ($arr['Engine'] === 'MyISAM') {
234 $sloppymyisamfound = true;
235 break;
236 }
237 }
238 $result->close();
239 }
240
241 if ($sloppymyisamfound) {
242 return get_string('myisamproblem', 'error');
243 } else {
244 return null;
245 }
246 }
247
7466a42f 248 /**
249 * Connect to db
250 * Must be called before other methods.
251 * @param string $dbhost
252 * @param string $dbuser
253 * @param string $dbpass
254 * @param string $dbname
7466a42f 255 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
256 * @param array $dboptions driver specific options
257 * @return bool success
258 */
beaa43db 259 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {
ce152606 260 $driverstatus = $this->driver_installed();
261
262 if ($driverstatus !== true) {
263 throw new dml_exception('dbdriverproblem', $driverstatus);
264 }
265
beaa43db 266 $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
7466a42f 267
79ac5a8c
PS
268 // dbsocket is used ONLY if host is NULL or 'localhost',
269 // you can not disable it because it is always tried if dbhost is 'localhost'
270 if (!empty($this->dboptions['dbsocket']) and strpos($this->dboptions['dbsocket'], '/') !== false) {
271 $dbsocket = $this->dboptions['dbsocket'];
272 } else {
273 $dbsocket = ini_get('mysqli.default_socket');
274 }
feaddca9
AB
275 if (empty($this->dboptions['dbport'])) {
276 $dbport = ini_get('mysqli.default_port');
277 } else {
278 $dbport = (int)$this->dboptions['dbport'];
279 }
ce152606 280 ob_start();
79ac5a8c 281 $this->mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname, $dbport, $dbsocket);
ce152606 282 $dberr = ob_get_contents();
283 ob_end_clean();
284 $errorno = @$this->mysqli->connect_errno;
285
286 if ($errorno !== 0) {
287 throw new dml_connection_exception($dberr);
7466a42f 288 }
ce152606 289
a6283e91 290 $this->query_start("--set_charset()", null, SQL_QUERY_AUX);
7466a42f 291 $this->mysqli->set_charset('utf8');
a6283e91 292 $this->query_end(true);
293
77abbc47 294 // If available, enforce strict mode for the session. That guaranties
295 // standard behaviour under some situations, avoiding some MySQL nasty
296 // habits like truncating data or performing some transparent cast losses.
02df9cfd 297 // With strict mode enforced, Moodle DB layer will be consistently throwing
77abbc47 298 // the corresponding exceptions as expected.
299 $si = $this->get_server_info();
300 if (version_compare($si['version'], '5.0.2', '>=')) {
301 $sql = "SET SESSION sql_mode = 'STRICT_ALL_TABLES'";
302 $this->query_start($sql, null, SQL_QUERY_AUX);
303 $result = $this->mysqli->query($sql);
304 $this->query_end($result);
305 }
306
3f33c9e2 307 // Connection stabilished and configured, going to instantiate the temptables controller
308 $this->temptables = new mysqli_native_moodle_temptables($this);
309
7466a42f 310 return true;
311 }
312
313 /**
314 * Close database connection and release all resources
315 * and memory (especially circular memory references).
316 * Do NOT use connect() again, create a new instance if needed.
317 */
318 public function dispose() {
02df9cfd 319 parent::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection
7466a42f 320 if ($this->mysqli) {
321 $this->mysqli->close();
322 $this->mysqli = null;
323 }
7466a42f 324 }
325
326 /**
327 * Returns database server info array
328 * @return array
329 */
330 public function get_server_info() {
331 return array('description'=>$this->mysqli->server_info, 'version'=>$this->mysqli->server_info);
332 }
333
334 /**
335 * Returns supported query parameter types
9331d879 336 * @return int bitmask
7466a42f 337 */
338 protected function allowed_param_types() {
339 return SQL_PARAMS_QM;
340 }
341
342 /**
343 * Returns last error reported by database engine.
9331d879 344 * @return string error message
7466a42f 345 */
346 public function get_last_error() {
347 return $this->mysqli->error;
348 }
349
350 /**
351 * Return tables in database WITHOUT current prefix
352 * @return array of table names in lowercase and without prefix
353 */
117679db 354 public function get_tables($usecache=true) {
355 if ($usecache and $this->tables !== null) {
356 return $this->tables;
357 }
358 $this->tables = array();
a6283e91 359 $sql = "SHOW TABLES";
360 $this->query_start($sql, null, SQL_QUERY_AUX);
361 $result = $this->mysqli->query($sql);
362 $this->query_end($result);
363 if ($result) {
7466a42f 364 while ($arr = $result->fetch_assoc()) {
365 $tablename = reset($arr);
7fdcb779 366 if ($this->prefix !== '') {
367 if (strpos($tablename, $this->prefix) !== 0) {
368 continue;
369 }
370 $tablename = substr($tablename, strlen($this->prefix));
7466a42f 371 }
117679db 372 $this->tables[$tablename] = $tablename;
7466a42f 373 }
374 $result->close();
375 }
3f33c9e2 376
377 // Add the currently available temptables
378 $this->tables = array_merge($this->tables, $this->temptables->get_temptables());
117679db 379 return $this->tables;
7466a42f 380 }
381
382 /**
383 * Return table indexes - everything lowercased
384 * @return array of arrays
385 */
386 public function get_indexes($table) {
7466a42f 387 $indexes = array();
a6283e91 388 $sql = "SHOW INDEXES FROM {$this->prefix}$table";
389 $this->query_start($sql, null, SQL_QUERY_AUX);
390 $result = $this->mysqli->query($sql);
391 $this->query_end($result);
392 if ($result) {
7466a42f 393 while ($res = $result->fetch_object()) {
394 if ($res->Key_name === 'PRIMARY') {
395 continue;
396 }
397 if (!isset($indexes[$res->Key_name])) {
398 $indexes[$res->Key_name] = array('unique'=>empty($res->Non_unique), 'columns'=>array());
399 }
400 $indexes[$res->Key_name]['columns'][$res->Seq_in_index-1] = $res->Column_name;
401 }
402 $result->close();
403 }
404 return $indexes;
405 }
406
407 /**
02df9cfd 408 * Returns detailed information about columns in table. This information is cached internally.
7466a42f 409 * @param string $table name
410 * @param bool $usecache
411 * @return array array of database_column_info objects indexed with column names
412 */
413 public function get_columns($table, $usecache=true) {
414 if ($usecache and isset($this->columns[$table])) {
415 return $this->columns[$table];
416 }
417
418 $this->columns[$table] = array();
419
a6283e91 420 $sql = "SHOW COLUMNS FROM {$this->prefix}$table";
421 $this->query_start($sql, null, SQL_QUERY_AUX);
422 $result = $this->mysqli->query($sql);
423 $this->query_end($result);
424
425 if ($result === false) {
7466a42f 426 return array();
427 }
428
a6283e91 429 while ($rawcolumn = $result->fetch_assoc()) {
430 $rawcolumn = (object)array_change_key_case($rawcolumn, CASE_LOWER);
431
ac6f1a82 432 $info = new stdClass();
7466a42f 433 $info->name = $rawcolumn->field;
434 $matches = null;
435
436 if (preg_match('/varchar\((\d+)\)/i', $rawcolumn->type, $matches)) {
437 $info->type = 'varchar';
438 $info->meta_type = 'C';
439 $info->max_length = $matches[1];
440 $info->scale = null;
441 $info->not_null = ($rawcolumn->null === 'NO');
442 $info->default_value = $rawcolumn->default;
443 $info->has_default = is_null($info->default_value) ? false : true;
444 $info->primary_key = ($rawcolumn->key === 'PRI');
445 $info->binary = false;
446 $info->unsigned = null;
447 $info->auto_increment= false;
448 $info->unique = null;
449
450 } else if (preg_match('/([a-z]*int[a-z]*)\((\d+)\)/i', $rawcolumn->type, $matches)) {
451 $info->type = $matches[1];
452 $info->primary_key = ($rawcolumn->key === 'PRI');
453 if ($info->primary_key) {
454 $info->meta_type = 'R';
455 $info->max_length = $matches[2];
456 $info->scale = null;
457 $info->not_null = ($rawcolumn->null === 'NO');
458 $info->default_value = $rawcolumn->default;
459 $info->has_default = is_null($info->default_value) ? false : true;
460 $info->binary = false;
461 $info->unsigned = (stripos($rawcolumn->type, 'unsigned') !== false);
462 $info->auto_increment= true;
463 $info->unique = true;
464 } else {
465 $info->meta_type = 'I';
466 $info->max_length = $matches[2];
467 $info->scale = null;
468 $info->not_null = ($rawcolumn->null === 'NO');
469 $info->default_value = $rawcolumn->default;
470 $info->has_default = is_null($info->default_value) ? false : true;
471 $info->binary = false;
472 $info->unsigned = (stripos($rawcolumn->type, 'unsigned') !== false);
473 $info->auto_increment= false;
474 $info->unique = null;
475 }
476
c65845cc 477 } else if (preg_match('/(decimal|double|float)\((\d+),(\d+)\)/i', $rawcolumn->type, $matches)) {
478 $info->type = $matches[1];
7466a42f 479 $info->meta_type = 'N';
c65845cc 480 $info->max_length = $matches[2];
481 $info->scale = $matches[3];
7466a42f 482 $info->not_null = ($rawcolumn->null === 'NO');
483 $info->default_value = $rawcolumn->default;
484 $info->has_default = is_null($info->default_value) ? false : true;
485 $info->primary_key = ($rawcolumn->key === 'PRI');
486 $info->binary = false;
487 $info->unsigned = null;
488 $info->auto_increment= false;
489 $info->unique = null;
490
491 } else if (preg_match('/([a-z]*text)/i', $rawcolumn->type, $matches)) {
492 $info->type = $matches[1];
493 $info->meta_type = 'X';
494 $info->max_length = -1;
495 $info->scale = null;
496 $info->not_null = ($rawcolumn->null === 'NO');
497 $info->default_value = $rawcolumn->default;
498 $info->has_default = is_null($info->default_value) ? false : true;
499 $info->primary_key = ($rawcolumn->key === 'PRI');
500 $info->binary = false;
501 $info->unsigned = null;
502 $info->auto_increment= false;
503 $info->unique = null;
504
75dfa4a3 505 } else if (preg_match('/([a-z]*blob)/i', $rawcolumn->type, $matches)) {
506 $info->type = $matches[1];
507 $info->meta_type = 'B';
508 $info->max_length = -1;
509 $info->scale = null;
510 $info->not_null = ($rawcolumn->null === 'NO');
511 $info->default_value = $rawcolumn->default;
512 $info->has_default = is_null($info->default_value) ? false : true;
513 $info->primary_key = false;
514 $info->binary = true;
515 $info->unsigned = null;
516 $info->auto_increment= false;
517 $info->unique = null;
518
7466a42f 519 } else if (preg_match('/enum\((.*)\)/i', $rawcolumn->type, $matches)) {
520 $info->type = 'enum';
521 $info->meta_type = 'C';
522 $info->enums = array();
523 $info->max_length = 0;
524 $values = $matches[1];
525 $values = explode(',', $values);
526 $textlib = textlib_get_instance();
527 foreach ($values as $val) {
528 $val = trim($val, "'");
529 $length = $textlib->strlen($val);
530 $info->enums[] = $val;
531 $info->max_length = ($info->max_length < $length) ? $length : $info->max_length;
532 }
533 $info->scale = null;
534 $info->not_null = ($rawcolumn->null === 'NO');
535 $info->default_value = $rawcolumn->default;
536 $info->has_default = is_null($info->default_value) ? false : true;
537 $info->primary_key = ($rawcolumn->key === 'PRI');
538 $info->binary = false;
539 $info->unsigned = null;
540 $info->auto_increment= false;
541 $info->unique = null;
542 }
543
544 $this->columns[$table][$info->name] = new database_column_info($info);
545 }
546
a6283e91 547 $result->close();
548
7466a42f 549 return $this->columns[$table];
550 }
551
8fb501e9
EL
552 /**
553 * Normalise values based in RDBMS dependencies (booleans, LOBs...)
554 *
555 * @param database_column_info $column column metadata corresponding with the value we are going to normalise
556 * @param mixed $value value we are going to normalise
557 * @return mixed the normalised value
558 */
e3acc8af 559 protected function normalise_value($column, $value) {
8fb501e9
EL
560 if (is_bool($value)) { // Always, convert boolean to int
561 $value = (int)$value;
562
563 } else if ($value === '') {
564 if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') {
565 $value = 0; // prevent '' problems in numeric fields
566 }
567 }
568 // workaround for problem with wrong enums in mysql - TODO: Out in Moodle 2.1
569 if (!empty($column->enums)) {
570 if (is_null($value) and !$column->not_null) {
571 // ok - nulls allowed
572 } else {
573 if (!in_array((string)$value, $column->enums)) {
574 throw new dml_write_exception('Enum value '.s($value).' not allowed in field '.$field.' table '.$table.'.');
575 }
576 }
577 }
578 return $value;
579 }
580
7466a42f 581 /**
582 * Is db in unicode mode?
583 * @return bool
584 */
585 public function setup_is_unicodedb() {
a6283e91 586 $sql = "SHOW LOCAL VARIABLES LIKE 'character_set_database'";
587 $this->query_start($sql, null, SQL_QUERY_AUX);
588 $result = $this->mysqli->query($sql);
589 $this->query_end($result);
590
cd4f1859 591 $return = false;
a6283e91 592 if ($result) {
cd4f1859
PS
593 while($row = $result->fetch_assoc()) {
594 if (isset($row['Value'])) {
595 $return = (strtoupper($row['Value']) === 'UTF8' or strtoupper($row['Value']) === 'UTF-8');
596 }
597 break;
598 }
7466a42f 599 $result->close();
7466a42f 600 }
cd4f1859
PS
601
602 if (!$return) {
603 return false;
604 }
605
606 $sql = "SHOW LOCAL VARIABLES LIKE 'collation_database'";
607 $this->query_start($sql, null, SQL_QUERY_AUX);
608 $result = $this->mysqli->query($sql);
609 $this->query_end($result);
610
611 $return = false;
612 if ($result) {
613 while($row = $result->fetch_assoc()) {
614 if (isset($row['Value'])) {
615 $return = (strpos($row['Value'], 'latin1') !== 0);
616 }
617 break;
618 }
619 $result->close();
620 }
621
622 return $return;
7466a42f 623 }
624
7466a42f 625 /**
626 * Do NOT use in code, to be used by database_manager only!
627 * @param string $sql query
22d77567 628 * @return bool true
629 * @throws dml_exception if error
7466a42f 630 */
631 public function change_database_structure($sql) {
117679db 632 $this->reset_caches();
a6283e91 633
634 $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
635 $result = $this->mysqli->query($sql);
636 $this->query_end($result);
637
12e09c6b 638 return true;
7466a42f 639 }
640
641 /**
642 * Very ugly hack which emulates bound parameters in queries
643 * because prepared statements do not use query cache.
644 */
645 protected function emulate_bound_params($sql, array $params=null) {
646 if (empty($params)) {
647 return $sql;
648 }
649 /// ok, we have verified sql statement with ? and correct number of params
650 $return = strtok($sql, '?');
651 foreach ($params as $param) {
652 if (is_bool($param)) {
653 $return .= (int)$param;
654 } else if (is_null($param)) {
655 $return .= 'NULL';
70ff13be
PS
656 } else if (is_number($param)) {
657 $return .= "'".$param."'"; // we have to always use strings because mysql is using weird automatic int casting
c1a6529b 658 } else if (is_float($param)) {
7466a42f 659 $return .= $param;
660 } else {
661 $param = $this->mysqli->real_escape_string($param);
662 $return .= "'$param'";
663 }
664 $return .= strtok('?');
665 }
666 return $return;
667 }
668
669 /**
670 * Execute general sql query. Should be used only when no other method suitable.
671 * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead!
672 * @param string $sql query
673 * @param array $params query parameters
22d77567 674 * @return bool true
675 * @throws dml_exception if error
7466a42f 676 */
677 public function execute($sql, array $params=null) {
678 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
679
680 if (strpos($sql, ';') !== false) {
22d77567 681 throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
7466a42f 682 }
683
684 $rawsql = $this->emulate_bound_params($sql, $params);
685
a6283e91 686 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
7466a42f 687 $result = $this->mysqli->query($rawsql);
a6283e91 688 $this->query_end($result);
7466a42f 689
22d77567 690 if ($result === true) {
7466a42f 691 return true;
692
693 } else {
694 $result->close();
695 return true;
696 }
697 }
698
699 /**
700 * Get a number of records as a moodle_recordset using a SQL statement.
701 *
702 * Since this method is a little less readable, use of it should be restricted to
703 * code where it's possible there might be large datasets being returned. For known
704 * small datasets use get_records_sql - it leads to simpler code.
705 *
706 * The return type is as for @see function get_recordset.
707 *
708 * @param string $sql the SQL select query to execute.
709 * @param array $params array of sql parameters
710 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
711 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
5212b07f 712 * @return moodle_recordset instance
22d77567 713 * @throws dml_exception if error
7466a42f 714 */
715 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
088a4df8 716 $limitfrom = (int)$limitfrom;
717 $limitnum = (int)$limitnum;
3ff8bf26 718 $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom;
719 $limitnum = ($limitnum < 0) ? 0 : $limitnum;
720
7466a42f 721 if ($limitfrom or $limitnum) {
7466a42f 722 if ($limitnum < 1) {
723 $limitnum = "18446744073709551615";
724 }
0487f9e2 725 $sql .= " LIMIT $limitfrom, $limitnum";
7466a42f 726 }
727
0487f9e2 728 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
729 $rawsql = $this->emulate_bound_params($sql, $params);
730
a6283e91 731 $this->query_start($sql, $params, SQL_QUERY_SELECT);
7466a42f 732 // no MYSQLI_USE_RESULT here, it would block write ops on affected tables
e0eda209 733 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT);
734 $this->query_end($result);
7466a42f 735
e0eda209 736 return $this->create_recordset($result);
7466a42f 737 }
738
739 protected function create_recordset($result) {
740 return new mysqli_native_moodle_recordset($result);
741 }
742
743 /**
744 * Get a number of records as an array of objects using a SQL statement.
745 *
746 * Return value as for @see function get_records.
747 *
748 * @param string $sql the SQL select query to execute. The first column of this SELECT statement
749 * must be a unique value (usually the 'id' field), as it will be used as the key of the
750 * returned array.
751 * @param array $params array of sql parameters
752 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
753 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
3503dcad 754 * @return array of objects, or empty array if no records were found
22d77567 755 * @throws dml_exception if error
7466a42f 756 */
757 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
088a4df8 758 $limitfrom = (int)$limitfrom;
759 $limitnum = (int)$limitnum;
3ff8bf26 760 $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom;
761 $limitnum = ($limitnum < 0) ? 0 : $limitnum;
762
7466a42f 763 if ($limitfrom or $limitnum) {
7466a42f 764 if ($limitnum < 1) {
765 $limitnum = "18446744073709551615";
766 }
0487f9e2 767 $sql .= " LIMIT $limitfrom, $limitnum";
7466a42f 768 }
769
0487f9e2 770 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
771 $rawsql = $this->emulate_bound_params($sql, $params);
772
a6283e91 773 $this->query_start($sql, $params, SQL_QUERY_SELECT);
7466a42f 774 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT);
a6283e91 775 $this->query_end($result);
7466a42f 776
7466a42f 777 $return = array();
a6283e91 778
7466a42f 779 while($row = $result->fetch_assoc()) {
780 $row = array_change_key_case($row, CASE_LOWER);
781 $id = reset($row);
758ba89a 782 if (isset($return[$id])) {
783 $colname = key($row);
784 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);
785 }
7466a42f 786 $return[$id] = (object)$row;
787 }
788 $result->close();
a6283e91 789
7466a42f 790 return $return;
791 }
792
793 /**
794 * Selects records and return values (first field) as an array using a SQL statement.
795 *
796 * @param string $sql The SQL query
797 * @param array $params array of sql parameters
3503dcad 798 * @return array of values
22d77567 799 * @throws dml_exception if error
7466a42f 800 */
801 public function get_fieldset_sql($sql, array $params=null) {
802 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
803 $rawsql = $this->emulate_bound_params($sql, $params);
804
a6283e91 805 $this->query_start($sql, $params, SQL_QUERY_SELECT);
7466a42f 806 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT);
a6283e91 807 $this->query_end($result);
7466a42f 808
7466a42f 809 $return = array();
a6283e91 810
7466a42f 811 while($row = $result->fetch_assoc()) {
812 $return[] = reset($row);
813 }
814 $result->close();
a6283e91 815
7466a42f 816 return $return;
817 }
818
819 /**
820 * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
821 * @param string $table name
822 * @param mixed $params data record as object or array
823 * @param bool $returnit return it of inserted record
824 * @param bool $bulk true means repeated inserts expected
825 * @param bool $customsequence true if 'id' included in $params, disables $returnid
3503dcad 826 * @return bool|int true or new id
22d77567 827 * @throws dml_exception if error
7466a42f 828 */
829 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
830 if (!is_array($params)) {
831 $params = (array)$params;
832 }
833
834 if ($customsequence) {
835 if (!isset($params['id'])) {
22d77567 836 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
7466a42f 837 }
838 $returnid = false;
839 } else {
840 unset($params['id']);
841 }
842
843 if (empty($params)) {
22d77567 844 throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
7466a42f 845 }
846
7466a42f 847 $fields = implode(',', array_keys($params));
848 $qms = array_fill(0, count($params), '?');
849 $qms = implode(',', $qms);
850
851 $sql = "INSERT INTO {$this->prefix}$table ($fields) VALUES($qms)";
76b6daf2 852
853 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
7466a42f 854 $rawsql = $this->emulate_bound_params($sql, $params);
855
a6283e91 856 $this->query_start($sql, $params, SQL_QUERY_INSERT);
7466a42f 857 $result = $this->mysqli->query($rawsql);
b4154c2d 858 $id = @$this->mysqli->insert_id; // must be called before query_end() which may insert log into db
a6283e91 859 $this->query_end($result);
7466a42f 860
b4154c2d 861 if (!$id) {
22d77567 862 throw new dml_write_exception('unknown error fetching inserted id');
7466a42f 863 }
864
865 if (!$returnid) {
866 return true;
867 } else {
868 return (int)$id;
869 }
870 }
871
872 /**
873 * Insert a record into a table and return the "id" field if required.
874 *
875 * Some conversions and safety checks are carried out. Lobs are supported.
876 * If the return ID isn't required, then this just reports success as true/false.
877 * $data is an object containing needed data
878 * @param string $table The database table to be inserted into
879 * @param object $data A data object with values for one or more fields in the record
880 * @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 881 * @return bool|int true or new id
22d77567 882 * @throws dml_exception if error
7466a42f 883 */
884 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
d8fa8e40 885 $dataobject = (array)$dataobject;
7466a42f 886
887 $columns = $this->get_columns($table);
7466a42f 888 $cleaned = array();
889
890 foreach ($dataobject as $field=>$value) {
d8fa8e40
PS
891 if ($field === 'id') {
892 continue;
893 }
7466a42f 894 if (!isset($columns[$field])) {
895 continue;
896 }
897 $column = $columns[$field];
8fb501e9 898 $cleaned[$field] = $this->normalise_value($column, $value);
7466a42f 899 }
900
7466a42f 901 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
902 }
903
904 /**
905 * Import a record into a table, id field is required.
906 * Safety checks are NOT carried out. Lobs are supported.
907 *
908 * @param string $table name of database table to be inserted into
909 * @param object $dataobject A data object with values for one or more fields in the record
22d77567 910 * @return bool true
911 * @throws dml_exception if error
7466a42f 912 */
913 public function import_record($table, $dataobject) {
d8fa8e40 914 $dataobject = (array)$dataobject;
7466a42f 915
7466a42f 916 $columns = $this->get_columns($table);
917 $cleaned = array();
918
919 foreach ($dataobject as $field=>$value) {
920 if (!isset($columns[$field])) {
921 continue;
922 }
923 $cleaned[$field] = $value;
924 }
925
926 return $this->insert_record_raw($table, $cleaned, false, true, true);
927 }
928
929 /**
930 * Update record in database, as fast as possible, no safety checks, lobs not supported.
931 * @param string $table name
932 * @param mixed $params data record as object or array
933 * @param bool true means repeated updates expected
22d77567 934 * @return bool true
935 * @throws dml_exception if error
7466a42f 936 */
937 public function update_record_raw($table, $params, $bulk=false) {
d8fa8e40
PS
938 $params = (array)$params;
939
7466a42f 940 if (!isset($params['id'])) {
22d77567 941 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
7466a42f 942 }
943 $id = $params['id'];
944 unset($params['id']);
945
946 if (empty($params)) {
22d77567 947 throw new coding_exception('moodle_database::update_record_raw() no fields found.');
7466a42f 948 }
949
7466a42f 950 $sets = array();
951 foreach ($params as $field=>$value) {
952 $sets[] = "$field = ?";
953 }
954
955 $params[] = $id; // last ? in WHERE condition
956
957 $sets = implode(',', $sets);
958 $sql = "UPDATE {$this->prefix}$table SET $sets WHERE id=?";
76b6daf2 959
960 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
7466a42f 961 $rawsql = $this->emulate_bound_params($sql, $params);
962
a6283e91 963 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
7466a42f 964 $result = $this->mysqli->query($rawsql);
a6283e91 965 $this->query_end($result);
7466a42f 966
7466a42f 967 return true;
968 }
969
970 /**
971 * Update a record in a table
972 *
973 * $dataobject is an object containing needed data
974 * Relies on $dataobject having a variable "id" to
975 * specify the record to update
976 *
977 * @param string $table The database table to be checked against.
978 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
979 * @param bool true means repeated updates expected
22d77567 980 * @return bool true
981 * @throws dml_exception if error
7466a42f 982 */
983 public function update_record($table, $dataobject, $bulk=false) {
d8fa8e40 984 $dataobject = (array)$dataobject;
7466a42f 985
7466a42f 986 $columns = $this->get_columns($table);
987 $cleaned = array();
988
989 foreach ($dataobject as $field=>$value) {
990 if (!isset($columns[$field])) {
991 continue;
992 }
8fb501e9
EL
993 $column = $columns[$field];
994 $cleaned[$field] = $this->normalise_value($column, $value);
7466a42f 995 }
996
997 return $this->update_record_raw($table, $cleaned, $bulk);
998 }
999
1000 /**
1001 * Set a single field in every table record which match a particular WHERE clause.
1002 *
1003 * @param string $table The database table to be checked against.
1004 * @param string $newfield the field to set.
1005 * @param string $newvalue the value to set the field to.
1006 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1007 * @param array $params array of sql parameters
22d77567 1008 * @return bool true
1009 * @throws dml_exception if error
7466a42f 1010 */
1011 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) {
1012 if ($select) {
1013 $select = "WHERE $select";
1014 }
1015 if (is_null($params)) {
1016 $params = array();
1017 }
1018 list($select, $params, $type) = $this->fix_sql_params($select, $params);
1019
8fb501e9
EL
1020 // Get column metadata
1021 $columns = $this->get_columns($table);
1022 $column = $columns[$newfield];
1023
1024 $normalised_value = $this->normalise_value($column, $newvalue);
1025
1026 if (is_null($normalised_value)) {
7466a42f 1027 $newfield = "$newfield = NULL";
1028 } else {
1029 $newfield = "$newfield = ?";
8fb501e9 1030 array_unshift($params, $normalised_value);
7466a42f 1031 }
1032 $sql = "UPDATE {$this->prefix}$table SET $newfield $select";
1033 $rawsql = $this->emulate_bound_params($sql, $params);
1034
a6283e91 1035 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
7466a42f 1036 $result = $this->mysqli->query($rawsql);
a6283e91 1037 $this->query_end($result);
7466a42f 1038
7466a42f 1039 return true;
1040 }
1041
1042 /**
1043 * Delete one or more records from a table which match a particular WHERE clause.
1044 *
1045 * @param string $table The database table to be checked against.
1046 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1047 * @param array $params array of sql parameters
22d77567 1048 * @return bool true
1049 * @throws dml_exception if error
7466a42f 1050 */
1051 public function delete_records_select($table, $select, array $params=null) {
1052 if ($select) {
1053 $select = "WHERE $select";
1054 }
1055 $sql = "DELETE FROM {$this->prefix}$table $select";
1056
1057 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1058 $rawsql = $this->emulate_bound_params($sql, $params);
1059
a6283e91 1060 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
7466a42f 1061 $result = $this->mysqli->query($rawsql);
a6283e91 1062 $this->query_end($result);
7466a42f 1063
7466a42f 1064 return true;
1065 }
1066
1067 public function sql_cast_char2int($fieldname, $text=false) {
1068 return ' CAST(' . $fieldname . ' AS SIGNED) ';
9882b2c8
EL
1069 }
1070
1071 public function sql_cast_char2real($fieldname, $text=false) {
1072 return ' CAST(' . $fieldname . ' AS DECIMAL) ';
7466a42f 1073 }
1074
6055f89d
PS
1075 /**
1076 * Returns 'LIKE' part of a query.
1077 *
1078 * @param string $fieldname usually name of the table column
1079 * @param string $param usually bound query parameter (?, :named)
1080 * @param bool $casesensitive use case sensitive search
1081 * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
16114b9d 1082 * @param bool $notlike true means "NOT LIKE"
6055f89d
PS
1083 * @param string $escapechar escape char for '%' and '_'
1084 * @return string SQL code fragment
1085 */
16114b9d 1086 public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
6055f89d
PS
1087 if (strpos($param, '%') !== false) {
1088 debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
1089 }
1090 $escapechar = $this->mysqli->real_escape_string($escapechar); // prevents problems with C-style escapes of enclosing '\'
1091
16114b9d 1092 $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
6055f89d 1093 if ($casesensitive) {
16114b9d 1094 return "$fieldname $LIKE $param COLLATE utf8_bin ESCAPE '$escapechar'";
6055f89d
PS
1095 } else {
1096 if ($accentsensitive) {
16114b9d 1097 return "LOWER($fieldname) $LIKE LOWER($param) COLLATE utf8_bin ESCAPE '$escapechar'";
6055f89d 1098 } else {
16114b9d 1099 return "$fieldname $LIKE $param ESCAPE '$escapechar'";
6055f89d
PS
1100 }
1101 }
1102 }
1103
215d7cb8
SH
1104 /**
1105 * Returns the proper SQL to do CONCAT between the elements passed
1106 * Can take many parameters
1107 *
1108 * @param string $str,... 1 or more fields/strings to concat
1109 *
1110 * @return string The concat sql
1111 */
7466a42f 1112 public function sql_concat() {
1113 $arr = func_get_args();
082ae821 1114 $s = implode(', ', $arr);
7466a42f 1115 if ($s === '') {
d5d0890c 1116 return "''";
7466a42f 1117 }
1118 return "CONCAT($s)";
1119 }
1120
215d7cb8
SH
1121 /**
1122 * Returns the proper SQL to do CONCAT between the elements passed
1123 * with a given separator
1124 *
1125 * @param string $separator The string to use as the separator
1126 * @param array $elements An array of items to concatenate
1127 * @return string The concat SQL
1128 */
7466a42f 1129 public function sql_concat_join($separator="' '", $elements=array()) {
082ae821 1130 $s = implode(', ', $elements);
1131
7466a42f 1132 if ($s === '') {
d5d0890c 1133 return "''";
7466a42f 1134 }
082ae821 1135 return "CONCAT_WS($separator, $s)";
7466a42f 1136 }
1137
7e0db2e2 1138 /**
1139 * Returns the SQL text to be used to calculate the length in characters of one expression.
1140 * @param string fieldname or expression to calculate its length in characters.
1141 * @return string the piece of SQL code to be used in the statement.
1142 */
1143 public function sql_length($fieldname) {
1144 return ' CHAR_LENGTH(' . $fieldname . ')';
1145 }
1146
7466a42f 1147 /**
02df9cfd 1148 * Does this driver support regex syntax when searching
7466a42f 1149 */
1150 public function sql_regex_supported() {
1151 return true;
1152 }
1153
1154 /**
1155 * Return regex positive or negative match sql
1156 * @param bool $positivematch
1157 * @return string or empty if not supported
1158 */
1159 public function sql_regex($positivematch=true) {
1160 return $positivematch ? 'REGEXP' : 'NOT REGEXP';
1161 }
fa76662b 1162
adff97c5 1163 public function sql_cast_2signed($fieldname) {
1164 return ' CAST(' . $fieldname . ' AS SIGNED) ';
1165 }
1166
7f79aaea 1167/// session locking
5e9dd017 1168 public function session_lock_supported() {
1169 return true;
1170 }
1171
3b1a9849 1172 public function get_session_lock($rowid) {
5e9dd017 1173 parent::get_session_lock($rowid);
3b1a9849 1174 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1175 $sql = "SELECT GET_LOCK('$fullname',120)";
1176 $this->query_start($sql, null, SQL_QUERY_AUX);
1177 $result = $this->mysqli->query($sql);
7f79aaea 1178 $this->query_end($result);
1179
1180 if ($result) {
1181 $arr = $result->fetch_assoc();
1182 $result->close();
1183
1184 if (reset($arr) == 1) {
5e9dd017 1185 return;
3b1a9849 1186 } else {
1187 // try again!
5e9dd017 1188 $this->get_session_lock($rowid);
7f79aaea 1189 }
1190 }
7f79aaea 1191 }
1192
3b1a9849 1193 public function release_session_lock($rowid) {
5e9dd017 1194 parent::release_session_lock($rowid);
3b1a9849 1195 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1196 $sql = "SELECT RELEASE_LOCK('$fullname')";
1197 $this->query_start($sql, null, SQL_QUERY_AUX);
1198 $result = $this->mysqli->query($sql);
7f79aaea 1199 $this->query_end($result);
1200
1201 if ($result) {
7f79aaea 1202 $result->close();
7f79aaea 1203 }
7f79aaea 1204 }
1205
fa76662b 1206/// transactions
1207 /**
d5a8d9aa
PS
1208 * Are transactions supported?
1209 * It is not responsible to run productions servers
1210 * on databases without transaction support ;-)
1211 *
1212 * MyISAM does not support support transactions.
fa76662b 1213 *
d35ece6c
PS
1214 * You can override this via the dbtransactions option.
1215 *
d5a8d9aa 1216 * @return bool
fa76662b 1217 */
d5a8d9aa
PS
1218 protected function transactions_supported() {
1219 if (!is_null($this->transactions_supported)) {
1220 return $this->transactions_supported;
1221 }
1222
d35ece6c
PS
1223 // this is all just guessing, might be better to just specify it in config.php
1224 if (isset($this->dboptions['dbtransactions'])) {
1225 $this->transactions_supported = $this->dboptions['dbtransactions'];
1226 return $this->transactions_supported;
1227 }
1228
d5a8d9aa
PS
1229 $this->transactions_supported = false;
1230
d35ece6c 1231 $engine = $this->get_dbengine();
d5a8d9aa 1232
d35ece6c
PS
1233 // Only will accept transactions if using compatible storage engine (more engines can be added easily BDB, Falcon...)
1234 if (in_array($engine, array('InnoDB', 'INNOBASE', 'BDB', 'XtraDB', 'Aria', 'Falcon'))) {
1235 $this->transactions_supported = true;
dd67e10c 1236 }
dd67e10c 1237
d5a8d9aa
PS
1238 return $this->transactions_supported;
1239 }
1240
1241 /**
1242 * Driver specific start of real database transaction,
1243 * this can not be used directly in code.
1244 * @return void
1245 */
1246 protected function begin_transaction() {
1247 if (!$this->transactions_supported()) {
1248 return;
a1dda107 1249 }
dd67e10c 1250
a6283e91 1251 $sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED";
1252 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1253 $result = $this->mysqli->query($sql);
1254 $this->query_end($result);
1255
dd67e10c 1256 $sql = "START TRANSACTION";
a6283e91 1257 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1258 $result = $this->mysqli->query($sql);
1259 $this->query_end($result);
fa76662b 1260 }
1261
1262 /**
d5a8d9aa
PS
1263 * Driver specific commit of real database transaction,
1264 * this can not be used directly in code.
1265 * @return void
fa76662b 1266 */
d5a8d9aa
PS
1267 protected function commit_transaction() {
1268 if (!$this->transactions_supported()) {
1269 return;
a1dda107 1270 }
d5a8d9aa 1271
a6283e91 1272 $sql = "COMMIT";
1273 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1274 $result = $this->mysqli->query($sql);
1275 $this->query_end($result);
fa76662b 1276 }
1277
1278 /**
d5a8d9aa
PS
1279 * Driver specific abort of real database transaction,
1280 * this can not be used directly in code.
1281 * @return void
fa76662b 1282 */
d5a8d9aa
PS
1283 protected function rollback_transaction() {
1284 if (!$this->transactions_supported()) {
1285 return;
a1dda107 1286 }
d5a8d9aa 1287
a6283e91 1288 $sql = "ROLLBACK";
1289 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1290 $result = $this->mysqli->query($sql);
1291 $this->query_end($result);
1292
fa76662b 1293 return true;
1294 }
7466a42f 1295}