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