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