lib/phpmailer MDL-20701 - Convert to moodle_phpmailer class
[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
7466a42f 458 /**
459 * Is db in unicode mode?
460 * @return bool
461 */
462 public function setup_is_unicodedb() {
a6283e91 463 $sql = "SHOW LOCAL VARIABLES LIKE 'character_set_database'";
464 $this->query_start($sql, null, SQL_QUERY_AUX);
465 $result = $this->mysqli->query($sql);
466 $this->query_end($result);
467
468 if ($result) {
7466a42f 469 $result->close();
470 return true;
471 }
472 return false;
473 }
474
7466a42f 475 /**
476 * Do NOT use in code, to be used by database_manager only!
477 * @param string $sql query
22d77567 478 * @return bool true
479 * @throws dml_exception if error
7466a42f 480 */
481 public function change_database_structure($sql) {
117679db 482 $this->reset_caches();
a6283e91 483
484 $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
485 $result = $this->mysqli->query($sql);
486 $this->query_end($result);
487
12e09c6b 488 return true;
7466a42f 489 }
490
491 /**
492 * Very ugly hack which emulates bound parameters in queries
493 * because prepared statements do not use query cache.
494 */
495 protected function emulate_bound_params($sql, array $params=null) {
496 if (empty($params)) {
497 return $sql;
498 }
499 /// ok, we have verified sql statement with ? and correct number of params
500 $return = strtok($sql, '?');
501 foreach ($params as $param) {
502 if (is_bool($param)) {
503 $return .= (int)$param;
504 } else if (is_null($param)) {
505 $return .= 'NULL';
c1a6529b 506 } else if (is_number($param)) { // we can not use is_numeric() because it eats leading zeros from strings like 0045646
507 $return .= $param;
508 } else if (is_float($param)) {
7466a42f 509 $return .= $param;
510 } else {
511 $param = $this->mysqli->real_escape_string($param);
512 $return .= "'$param'";
513 }
514 $return .= strtok('?');
515 }
516 return $return;
517 }
518
519 /**
520 * Execute general sql query. Should be used only when no other method suitable.
521 * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead!
522 * @param string $sql query
523 * @param array $params query parameters
22d77567 524 * @return bool true
525 * @throws dml_exception if error
7466a42f 526 */
527 public function execute($sql, array $params=null) {
528 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
529
530 if (strpos($sql, ';') !== false) {
22d77567 531 throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
7466a42f 532 }
533
534 $rawsql = $this->emulate_bound_params($sql, $params);
535
a6283e91 536 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
7466a42f 537 $result = $this->mysqli->query($rawsql);
a6283e91 538 $this->query_end($result);
7466a42f 539
22d77567 540 if ($result === true) {
7466a42f 541 return true;
542
543 } else {
544 $result->close();
545 return true;
546 }
547 }
548
549 /**
550 * Get a number of records as a moodle_recordset using a SQL statement.
551 *
552 * Since this method is a little less readable, use of it should be restricted to
553 * code where it's possible there might be large datasets being returned. For known
554 * small datasets use get_records_sql - it leads to simpler code.
555 *
556 * The return type is as for @see function get_recordset.
557 *
558 * @param string $sql the SQL select query to execute.
559 * @param array $params array of sql parameters
560 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
561 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
22d77567 562 * @return mixed an moodle_recordset object
563 * @throws dml_exception if error
7466a42f 564 */
565 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
088a4df8 566 $limitfrom = (int)$limitfrom;
567 $limitnum = (int)$limitnum;
3ff8bf26 568 $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom;
569 $limitnum = ($limitnum < 0) ? 0 : $limitnum;
570
7466a42f 571 if ($limitfrom or $limitnum) {
7466a42f 572 if ($limitnum < 1) {
573 $limitnum = "18446744073709551615";
574 }
0487f9e2 575 $sql .= " LIMIT $limitfrom, $limitnum";
7466a42f 576 }
577
0487f9e2 578 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
579 $rawsql = $this->emulate_bound_params($sql, $params);
580
a6283e91 581 $this->query_start($sql, $params, SQL_QUERY_SELECT);
7466a42f 582 // no MYSQLI_USE_RESULT here, it would block write ops on affected tables
e0eda209 583 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT);
584 $this->query_end($result);
7466a42f 585
e0eda209 586 return $this->create_recordset($result);
7466a42f 587 }
588
589 protected function create_recordset($result) {
590 return new mysqli_native_moodle_recordset($result);
591 }
592
593 /**
594 * Get a number of records as an array of objects using a SQL statement.
595 *
596 * Return value as for @see function get_records.
597 *
598 * @param string $sql the SQL select query to execute. The first column of this SELECT statement
599 * must be a unique value (usually the 'id' field), as it will be used as the key of the
600 * returned array.
601 * @param array $params array of sql parameters
602 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
603 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
22d77567 604 * @return mixed an array of objects, or empty array if no records were found
605 * @throws dml_exception if error
7466a42f 606 */
607 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
088a4df8 608 $limitfrom = (int)$limitfrom;
609 $limitnum = (int)$limitnum;
3ff8bf26 610 $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom;
611 $limitnum = ($limitnum < 0) ? 0 : $limitnum;
612
7466a42f 613 if ($limitfrom or $limitnum) {
7466a42f 614 if ($limitnum < 1) {
615 $limitnum = "18446744073709551615";
616 }
0487f9e2 617 $sql .= " LIMIT $limitfrom, $limitnum";
7466a42f 618 }
619
0487f9e2 620 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
621 $rawsql = $this->emulate_bound_params($sql, $params);
622
a6283e91 623 $this->query_start($sql, $params, SQL_QUERY_SELECT);
7466a42f 624 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT);
a6283e91 625 $this->query_end($result);
7466a42f 626
7466a42f 627 $return = array();
a6283e91 628
7466a42f 629 while($row = $result->fetch_assoc()) {
630 $row = array_change_key_case($row, CASE_LOWER);
631 $id = reset($row);
758ba89a 632 if (isset($return[$id])) {
633 $colname = key($row);
634 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);
635 }
7466a42f 636 $return[$id] = (object)$row;
637 }
638 $result->close();
a6283e91 639
7466a42f 640 return $return;
641 }
642
643 /**
644 * Selects records and return values (first field) as an array using a SQL statement.
645 *
646 * @param string $sql The SQL query
647 * @param array $params array of sql parameters
22d77567 648 * @return mixed array of values
649 * @throws dml_exception if error
7466a42f 650 */
651 public function get_fieldset_sql($sql, array $params=null) {
652 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
653 $rawsql = $this->emulate_bound_params($sql, $params);
654
a6283e91 655 $this->query_start($sql, $params, SQL_QUERY_SELECT);
7466a42f 656 $result = $this->mysqli->query($rawsql, MYSQLI_STORE_RESULT);
a6283e91 657 $this->query_end($result);
7466a42f 658
7466a42f 659 $return = array();
a6283e91 660
7466a42f 661 while($row = $result->fetch_assoc()) {
662 $return[] = reset($row);
663 }
664 $result->close();
a6283e91 665
7466a42f 666 return $return;
667 }
668
669 /**
670 * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
671 * @param string $table name
672 * @param mixed $params data record as object or array
673 * @param bool $returnit return it of inserted record
674 * @param bool $bulk true means repeated inserts expected
675 * @param bool $customsequence true if 'id' included in $params, disables $returnid
22d77567 676 * @return true or new id
677 * @throws dml_exception if error
7466a42f 678 */
679 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
680 if (!is_array($params)) {
681 $params = (array)$params;
682 }
683
684 if ($customsequence) {
685 if (!isset($params['id'])) {
22d77567 686 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
7466a42f 687 }
688 $returnid = false;
689 } else {
690 unset($params['id']);
691 }
692
693 if (empty($params)) {
22d77567 694 throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
7466a42f 695 }
696
7466a42f 697 $fields = implode(',', array_keys($params));
698 $qms = array_fill(0, count($params), '?');
699 $qms = implode(',', $qms);
700
701 $sql = "INSERT INTO {$this->prefix}$table ($fields) VALUES($qms)";
76b6daf2 702
703 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
7466a42f 704 $rawsql = $this->emulate_bound_params($sql, $params);
705
a6283e91 706 $this->query_start($sql, $params, SQL_QUERY_INSERT);
7466a42f 707 $result = $this->mysqli->query($rawsql);
b4154c2d 708 $id = @$this->mysqli->insert_id; // must be called before query_end() which may insert log into db
a6283e91 709 $this->query_end($result);
7466a42f 710
b4154c2d 711 if (!$id) {
22d77567 712 throw new dml_write_exception('unknown error fetching inserted id');
7466a42f 713 }
714
715 if (!$returnid) {
716 return true;
717 } else {
718 return (int)$id;
719 }
720 }
721
722 /**
723 * Insert a record into a table and return the "id" field if required.
724 *
725 * Some conversions and safety checks are carried out. Lobs are supported.
726 * If the return ID isn't required, then this just reports success as true/false.
727 * $data is an object containing needed data
728 * @param string $table The database table to be inserted into
729 * @param object $data A data object with values for one or more fields in the record
730 * @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 731 * @return true or new id
732 * @throws dml_exception if error
7466a42f 733 */
734 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
735 if (!is_object($dataobject)) {
736 $dataobject = (object)$dataobject;
737 }
738
739 $columns = $this->get_columns($table);
740
741 unset($dataobject->id);
742 $cleaned = array();
743
744 foreach ($dataobject as $field=>$value) {
745 if (!isset($columns[$field])) {
746 continue;
747 }
748 $column = $columns[$field];
749 if (is_bool($value)) {
750 $value = (int)$value; // prevent "false" problems
751 }
752 if (!empty($column->enums)) {
753 // workaround for problem with wrong enums in mysql
754 if (is_null($value) and !$column->not_null) {
755 // ok - nulls allowed
756 } else {
757 if (!in_array((string)$value, $column->enums)) {
22d77567 758 throw new dml_write_exception('Enum value '.s($value).' not allowed in field '.$field.' table '.$table.'.');
7466a42f 759 }
760 }
761 }
762 $cleaned[$field] = $value;
763 }
764
7466a42f 765 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
766 }
767
768 /**
769 * Import a record into a table, id field is required.
770 * Safety checks are NOT carried out. Lobs are supported.
771 *
772 * @param string $table name of database table to be inserted into
773 * @param object $dataobject A data object with values for one or more fields in the record
22d77567 774 * @return bool true
775 * @throws dml_exception if error
7466a42f 776 */
777 public function import_record($table, $dataobject) {
778 $dataobject = (object)$dataobject;
779
7466a42f 780 $columns = $this->get_columns($table);
781 $cleaned = array();
782
783 foreach ($dataobject as $field=>$value) {
784 if (!isset($columns[$field])) {
785 continue;
786 }
787 $cleaned[$field] = $value;
788 }
789
790 return $this->insert_record_raw($table, $cleaned, false, true, true);
791 }
792
793 /**
794 * Update record in database, as fast as possible, no safety checks, lobs not supported.
795 * @param string $table name
796 * @param mixed $params data record as object or array
797 * @param bool true means repeated updates expected
22d77567 798 * @return bool true
799 * @throws dml_exception if error
7466a42f 800 */
801 public function update_record_raw($table, $params, $bulk=false) {
802 if (!is_array($params)) {
803 $params = (array)$params;
804 }
805 if (!isset($params['id'])) {
22d77567 806 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
7466a42f 807 }
808 $id = $params['id'];
809 unset($params['id']);
810
811 if (empty($params)) {
22d77567 812 throw new coding_exception('moodle_database::update_record_raw() no fields found.');
7466a42f 813 }
814
7466a42f 815 $sets = array();
816 foreach ($params as $field=>$value) {
817 $sets[] = "$field = ?";
818 }
819
820 $params[] = $id; // last ? in WHERE condition
821
822 $sets = implode(',', $sets);
823 $sql = "UPDATE {$this->prefix}$table SET $sets WHERE id=?";
76b6daf2 824
825 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
7466a42f 826 $rawsql = $this->emulate_bound_params($sql, $params);
827
a6283e91 828 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
7466a42f 829 $result = $this->mysqli->query($rawsql);
a6283e91 830 $this->query_end($result);
7466a42f 831
7466a42f 832 return true;
833 }
834
835 /**
836 * Update a record in a table
837 *
838 * $dataobject is an object containing needed data
839 * Relies on $dataobject having a variable "id" to
840 * specify the record to update
841 *
842 * @param string $table The database table to be checked against.
843 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
844 * @param bool true means repeated updates expected
22d77567 845 * @return bool true
846 * @throws dml_exception if error
7466a42f 847 */
848 public function update_record($table, $dataobject, $bulk=false) {
849 if (!is_object($dataobject)) {
850 $dataobject = (object)$dataobject;
851 }
852
7466a42f 853 $columns = $this->get_columns($table);
854 $cleaned = array();
855
856 foreach ($dataobject as $field=>$value) {
857 if (!isset($columns[$field])) {
858 continue;
859 }
860 if (is_bool($value)) {
861 $value = (int)$value; // prevent "false" problems
862 }
863 $cleaned[$field] = $value;
864 }
865
866 return $this->update_record_raw($table, $cleaned, $bulk);
867 }
868
869 /**
870 * Set a single field in every table record which match a particular WHERE clause.
871 *
872 * @param string $table The database table to be checked against.
873 * @param string $newfield the field to set.
874 * @param string $newvalue the value to set the field to.
875 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
876 * @param array $params array of sql parameters
22d77567 877 * @return bool true
878 * @throws dml_exception if error
7466a42f 879 */
880 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) {
881 if ($select) {
882 $select = "WHERE $select";
883 }
884 if (is_null($params)) {
885 $params = array();
886 }
887 list($select, $params, $type) = $this->fix_sql_params($select, $params);
888
889 if (is_bool($newvalue)) {
890 $newvalue = (int)$newvalue; // prevent "false" problems
891 }
892 if (is_null($newvalue)) {
893 $newfield = "$newfield = NULL";
894 } else {
895 $newfield = "$newfield = ?";
896 array_unshift($params, $newvalue);
897 }
898 $sql = "UPDATE {$this->prefix}$table SET $newfield $select";
899 $rawsql = $this->emulate_bound_params($sql, $params);
900
a6283e91 901 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
7466a42f 902 $result = $this->mysqli->query($rawsql);
a6283e91 903 $this->query_end($result);
7466a42f 904
7466a42f 905 return true;
906 }
907
908 /**
909 * Delete one or more records from a table which match a particular WHERE clause.
910 *
911 * @param string $table The database table to be checked against.
912 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
913 * @param array $params array of sql parameters
22d77567 914 * @return bool true
915 * @throws dml_exception if error
7466a42f 916 */
917 public function delete_records_select($table, $select, array $params=null) {
918 if ($select) {
919 $select = "WHERE $select";
920 }
921 $sql = "DELETE FROM {$this->prefix}$table $select";
922
923 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
924 $rawsql = $this->emulate_bound_params($sql, $params);
925
a6283e91 926 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
7466a42f 927 $result = $this->mysqli->query($rawsql);
a6283e91 928 $this->query_end($result);
7466a42f 929
7466a42f 930 return true;
931 }
932
933 public function sql_cast_char2int($fieldname, $text=false) {
934 return ' CAST(' . $fieldname . ' AS SIGNED) ';
935 }
936
937 public function sql_concat() {
938 $arr = func_get_args();
082ae821 939 $s = implode(', ', $arr);
7466a42f 940 if ($s === '') {
d5d0890c 941 return "''";
7466a42f 942 }
943 return "CONCAT($s)";
944 }
945
946 public function sql_concat_join($separator="' '", $elements=array()) {
082ae821 947 $s = implode(', ', $elements);
948
7466a42f 949 if ($s === '') {
d5d0890c 950 return "''";
7466a42f 951 }
082ae821 952 return "CONCAT_WS($separator, $s)";
7466a42f 953 }
954
7e0db2e2 955 /**
956 * Returns the SQL text to be used to calculate the length in characters of one expression.
957 * @param string fieldname or expression to calculate its length in characters.
958 * @return string the piece of SQL code to be used in the statement.
959 */
960 public function sql_length($fieldname) {
961 return ' CHAR_LENGTH(' . $fieldname . ')';
962 }
963
7466a42f 964 /**
965 * Does this driver suppoer regex syntax when searching
966 */
967 public function sql_regex_supported() {
968 return true;
969 }
970
971 /**
972 * Return regex positive or negative match sql
973 * @param bool $positivematch
974 * @return string or empty if not supported
975 */
976 public function sql_regex($positivematch=true) {
977 return $positivematch ? 'REGEXP' : 'NOT REGEXP';
978 }
fa76662b 979
adff97c5 980 public function sql_cast_2signed($fieldname) {
981 return ' CAST(' . $fieldname . ' AS SIGNED) ';
982 }
983
7f79aaea 984/// session locking
5e9dd017 985 public function session_lock_supported() {
986 return true;
987 }
988
3b1a9849 989 public function get_session_lock($rowid) {
5e9dd017 990 parent::get_session_lock($rowid);
3b1a9849 991 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
992 $sql = "SELECT GET_LOCK('$fullname',120)";
993 $this->query_start($sql, null, SQL_QUERY_AUX);
994 $result = $this->mysqli->query($sql);
7f79aaea 995 $this->query_end($result);
996
997 if ($result) {
998 $arr = $result->fetch_assoc();
999 $result->close();
1000
1001 if (reset($arr) == 1) {
5e9dd017 1002 return;
3b1a9849 1003 } else {
1004 // try again!
5e9dd017 1005 $this->get_session_lock($rowid);
7f79aaea 1006 }
1007 }
7f79aaea 1008 }
1009
3b1a9849 1010 public function release_session_lock($rowid) {
5e9dd017 1011 parent::release_session_lock($rowid);
3b1a9849 1012 $fullname = $this->dbname.'-'.$this->prefix.'-session-'.$rowid;
1013 $sql = "SELECT RELEASE_LOCK('$fullname')";
1014 $this->query_start($sql, null, SQL_QUERY_AUX);
1015 $result = $this->mysqli->query($sql);
7f79aaea 1016 $this->query_end($result);
1017
1018 if ($result) {
7f79aaea 1019 $result->close();
7f79aaea 1020 }
7f79aaea 1021 }
1022
fa76662b 1023/// transactions
1024 /**
1025 * on DBs that support it, switch to transaction mode and begin a transaction
1026 * you'll need to ensure you call commit_sql() or your changes *will* be lost.
1027 *
1028 * this is _very_ useful for massive updates
1029 */
1030 public function begin_sql() {
dd67e10c 1031 // Only will accept transactions if using InnoDB storage engine (more engines can be added easily BDB, Falcon...)
1032 $sql = "SELECT @@storage_engine";
1033 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1034 $result = $this->mysqli->query($sql);
1035 $this->query_end($result);
1036 if ($rec = $result->fetch_assoc()) {
1037 if (!in_array($rec['@@storage_engine'], array('InnoDB'))) {
1038 return false;
1039 }
1040 } else {
1041 return false;
1042 }
1043 $result->close();
1044
a1dda107 1045 if (!parent::begin_sql()) {
1046 return false;
1047 }
dd67e10c 1048
a6283e91 1049 $sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED";
1050 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1051 $result = $this->mysqli->query($sql);
1052 $this->query_end($result);
1053
dd67e10c 1054 $sql = "START TRANSACTION";
a6283e91 1055 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1056 $result = $this->mysqli->query($sql);
1057 $this->query_end($result);
1058
fa76662b 1059 return true;
1060 }
1061
1062 /**
1063 * on DBs that support it, commit the transaction
1064 */
1065 public function commit_sql() {
a1dda107 1066 if (!parent::commit_sql()) {
1067 return false;
1068 }
a6283e91 1069 $sql = "COMMIT";
1070 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1071 $result = $this->mysqli->query($sql);
1072 $this->query_end($result);
1073
fa76662b 1074 return true;
1075 }
1076
1077 /**
1078 * on DBs that support it, rollback the transaction
1079 */
1080 public function rollback_sql() {
a1dda107 1081 if (!parent::rollback_sql()) {
1082 return false;
1083 }
a6283e91 1084 $sql = "ROLLBACK";
1085 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1086 $result = $this->mysqli->query($sql);
1087 $this->query_end($result);
1088
fa76662b 1089 return true;
1090 }
7466a42f 1091}