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