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