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