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