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