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