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