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