MDL-23925, MDL-23888 new sql_ilike() and sql_binary_equal() - this should finally...
[moodle.git] / lib / dml / moodle_database.php
CommitLineData
49926145 1<?php
2
3// This file is part of Moodle - http://moodle.org/
4//
5// Moodle is free software: you can redistribute it and/or modify
6// it under the terms of the GNU General Public License as published by
7// the Free Software Foundation, either version 3 of the License, or
8// (at your option) any later version.
9//
10// Moodle is distributed in the hope that it will be useful,
11// but WITHOUT ANY WARRANTY; without even the implied warranty of
12// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13// GNU General Public License for more details.
14//
15// You should have received a copy of the GNU General Public License
16// along with Moodle. If not, see <http://www.gnu.org/licenses/>.
17
18
19/**
20 * Abstract database driver class.
21 *
66c0ee78 22 * @package core
f2ed3f05 23 * @subpackage dml
49926145 24 * @copyright 2008 Petr Skoda (http://skodak.org)
25 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
26 */
f33e1ed4 27
f2ed3f05
PS
28defined('MOODLE_INTERNAL') || die();
29
0a0ea79d 30require_once($CFG->libdir.'/dml/database_column_info.php');
8aff8482 31require_once($CFG->libdir.'/dml/moodle_recordset.php');
d5a8d9aa 32require_once($CFG->libdir.'/dml/moodle_transaction.php');
0a0ea79d 33
3dce78e1 34/// GLOBAL CONSTANTS /////////////////////////////////////////////////////////
35
bb5f11ec 36/** Bitmask, indicates :name type parameters are supported by db backend. */
3dce78e1 37define('SQL_PARAMS_NAMED', 1);
38
bb5f11ec 39/** Bitmask, indicates ? type parameters are supported by db backend. */
3dce78e1 40define('SQL_PARAMS_QM', 2);
41
bb5f11ec 42/** Bitmask, indicates $1, $2, ... type parameters are supported by db backend. */
3dce78e1 43define('SQL_PARAMS_DOLLAR', 4);
44
bb5f11ec 45
46/** Normal select query, reading only */
47define('SQL_QUERY_SELECT', 1);
48
49/** Insert select query, writing */
50define('SQL_QUERY_INSERT', 2);
51
52/** Update select query, writing */
53define('SQL_QUERY_UPDATE', 3);
54
55/** Query changing db structure, writing */
56define('SQL_QUERY_STRUCTURE', 4);
57
58/** Auxiliary query done by driver, setting connection config, getting table info, etc. */
59define('SQL_QUERY_AUX', 5);
60
f33e1ed4 61/**
62 * Abstract class representing moodle database interface.
f33e1ed4 63 */
64abstract class moodle_database {
65
5da75074 66 /** @var database_manager db manager which allows db structure modifications */
f33e1ed4 67 protected $database_manager;
10f375aa
EL
68 /** @var moodle_temptables temptables manager to provide cross-db support for temp tables */
69 protected $temptables;
5da75074 70 /** @var array cache of column info */
a7544e37 71 protected $columns = array(); // I wish we had a shared memory cache for this :-(
5da75074 72 /** @var array cache of table info */
117679db 73 protected $tables = null;
a7544e37 74
f33e1ed4 75 // db connection options
5da75074 76 /** @var string db host name */
f33e1ed4 77 protected $dbhost;
5da75074 78 /** @var string db host user */
f33e1ed4 79 protected $dbuser;
5da75074 80 /** @var string db host password */
f33e1ed4 81 protected $dbpass;
5da75074 82 /** @var string db name */
f33e1ed4 83 protected $dbname;
5da75074 84 /** @var string table prefix */
f33e1ed4 85 protected $prefix;
86
5da75074 87 /** @var array Database or driver specific options, such as sockets or TCPIP db connections */
3b093310 88 protected $dboptions;
f33e1ed4 89
5da75074 90 /** @var bool true means non-moodle external database used.*/
3b093310 91 protected $external;
6d5a22b2 92
5da75074 93 /** @var int The database reads (performance counter).*/
ab130a0b 94 protected $reads = 0;
5da75074 95 /** @var int The database writes (performance counter).*/
ab130a0b 96 protected $writes = 0;
97
5da75074 98 /** @var int Debug level */
22d77567 99 protected $debug = 0;
100
5da75074 101 /** @var string last query sql */
c23b0ea1 102 protected $last_sql;
5da75074 103 /** @var array last query parameters */
c23b0ea1 104 protected $last_params;
5da75074 105 /** @var int last query type */
c23b0ea1 106 protected $last_type;
5da75074 107 /** @var string last extra info */
c23b0ea1 108 protected $last_extrainfo;
f688db7d 109 /** @var float last time in seconds with millisecond precision */
54d51f60 110 protected $last_time;
862d54c3 111 /** @var bool flag indicating logging of query in progress, prevents infinite loops */
54d51f60 112 private $loggingquery = false;
f33e1ed4 113
5da75074 114 /** @var bool true if db used for db sessions */
5e9dd017 115 protected $used_for_db_sessions = false;
0a2092a3 116
d5a8d9aa
PS
117 /** @var array open transactions */
118 private $transactions = array();
119 /** @var bool force rollback of all current transactions */
120 private $force_rollback = false;
a1dda107 121
5da75074 122 /** @var int internal temporary variable */
ba63f377 123 private $fix_sql_params_i;
124
f33e1ed4 125 /**
862d54c3 126 * Constructor - instantiates the database, specifying if it's external (connect to other systems) or no (Moodle DB)
6d5a22b2 127 * note this has effect to decide if prefix checks must be performed or no
128 * @param bool true means external database used
f33e1ed4 129 */
6d5a22b2 130 public function __construct($external=false) {
131 $this->external = $external;
132 }
f33e1ed4 133
6cc97718 134 /**
135 * Destructor - cleans up and flushes everything needed.
136 */
8fdb24f0 137 public function __destruct() {
6cc97718 138 $this->dispose();
139 }
140
f33e1ed4 141 /**
6d5a22b2 142 * Detects if all needed PHP stuff installed.
143 * Note: can be used before connect()
144 * @return mixed true if ok, string if something
f33e1ed4 145 */
6d5a22b2 146 public abstract function driver_installed();
f33e1ed4 147
148 /**
149 * Returns database table prefix
6d5a22b2 150 * Note: can be used before connect()
f33e1ed4 151 * @return string database table prefix
152 */
153 public function get_prefix() {
154 return $this->prefix;
155 }
156
9fed64fa 157 /**
8aff8482 158 * Loads and returns a database instance with the specified type and library.
9fed64fa 159 * @param string $type database type of the driver (mysql, postgres7, mssql, etc)
8aff8482 160 * @param string $library database library of the driver (adodb, pdo, native, etc)
161 * @return moodle_database driver object or null if error
9fed64fa 162 */
8aff8482 163 public static function get_driver_instance($type, $library) {
9fed64fa 164 global $CFG;
8aff8482 165
166 $classname = $type.'_'.$library.'_moodle_database';
167 $libfile = "$CFG->libdir/dml/$classname.php";
168
169 if (!file_exists($libfile)) {
170 return null;
171 }
172
173 require_once($libfile);
174 return new $classname();
9fed64fa 175 }
176
f33e1ed4 177 /**
6d5a22b2 178 * Returns database family type - describes SQL dialect
179 * Note: can be used before connect()
180 * @return string db family name (mysql, postgres, mssql, oracle, etc.)
181 */
182 public abstract function get_dbfamily();
183
184 /**
185 * Returns more specific database driver type
186 * Note: can be used before connect()
f33e1ed4 187 * @return string db type mysql, mysqli, postgres7
188 */
189 protected abstract function get_dbtype();
190
16a5642c 191 /**
192 * Returns general database library name
193 * Note: can be used before connect()
eb123deb 194 * @return string db type pdo, native
16a5642c 195 */
196 protected abstract function get_dblibrary();
197
f33e1ed4 198 /**
6d5a22b2 199 * Returns localised database type name
200 * Note: can be used before connect()
201 * @return string
f33e1ed4 202 */
6d5a22b2 203 public abstract function get_name();
204
205 /**
3b093310 206 * Returns localised database configuration help.
6d5a22b2 207 * Note: can be used before connect()
208 * @return string
209 */
3b093310 210 public abstract function get_configuration_help();
f33e1ed4 211
212 /**
3b093310 213 * Returns localised database description
6d5a22b2 214 * Note: can be used before connect()
215 * @return string
216 */
3b093310 217 public abstract function get_configuration_hints();
16a5642c 218
3b093310 219 /**
220 * Returns db related part of config.php
221 * @return object
222 */
223 public function export_dbconfig() {
16a5642c 224 $cfg = new stdClass();
225 $cfg->dbtype = $this->get_dbtype();
226 $cfg->dblibrary = $this->get_dblibrary();
227 $cfg->dbhost = $this->dbhost;
228 $cfg->dbname = $this->dbname;
229 $cfg->dbuser = $this->dbuser;
230 $cfg->dbpass = $this->dbpass;
231 $cfg->prefix = $this->prefix;
232 if ($this->dboptions) {
233 $cfg->dboptions = $this->dboptions;
234 }
235
236 return $cfg;
237 }
6d5a22b2 238
d35ece6c
PS
239 /**
240 * Diagnose database and tables, this function is used
241 * to verify database and driver settings, db engine types, etc.
242 *
243 * @return string null means everything ok, string means problem found.
244 */
245 public function diagnose() {
246 return null;
247 }
248
6d5a22b2 249 /**
250 * Connect to db
251 * Must be called before other methods.
252 * @param string $dbhost
253 * @param string $dbuser
254 * @param string $dbpass
255 * @param string $dbname
6d5a22b2 256 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
257 * @param array $dboptions driver specific options
ce152606 258 * @return bool true
259 * @throws dml_connection_exception if error
f33e1ed4 260 */
beaa43db 261 public abstract function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null);
6d5a22b2 262
16a5642c 263 /**
264 * Store various database settings
265 * @param string $dbhost
266 * @param string $dbuser
267 * @param string $dbpass
268 * @param string $dbname
16a5642c 269 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
270 * @param array $dboptions driver specific options
271 * @return void
272 */
beaa43db 273 protected function store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {
16a5642c 274 $this->dbhost = $dbhost;
275 $this->dbuser = $dbuser;
276 $this->dbpass = $dbpass;
277 $this->dbname = $dbname;
16a5642c 278 $this->prefix = $prefix;
279 $this->dboptions = (array)$dboptions;
280 }
281
e4c033a9 282 /**
283 * Attempt to create the database
284 * @param string $dbhost
285 * @param string $dbuser
286 * @param string $dbpass
287 * @param string $dbname
288 *
289 * @return bool success
290 */
3b093310 291 public function create_database($dbhost, $dbuser, $dbpass, $dbname, array $dboptions=null) {
e4c033a9 292 return false;
293 }
294
25d854c6 295 /**
296 * Close database connection and release all resources
297 * and memory (especially circular memory references).
298 * Do NOT use connect() again, create a new instance if needed.
9331d879 299 * @return void
25d854c6 300 */
301 public function dispose() {
d5a8d9aa 302 if ($this->transactions) {
862d54c3
PS
303 // this should not happen, it usually indicates wrong catching of exceptions,
304 // because all transactions should be finished manually or in default exception handler.
d5a8d9aa
PS
305 // unfortunately we can not access global $CFG any more and can not print debug,
306 // the diagnostic info should be printed in footer instead
07b81ebd
PS
307 $lowesttransaction = end($this->transactions);
308 $backtrace = $lowesttransaction->get_backtrace();
309
310 error_log('Potential coding error - active database transaction detected when disposing database:'."\n".format_backtrace($backtrace, true));
d5a8d9aa 311 $this->force_transaction_rollback();
a1dda107 312 }
0a2092a3 313 if ($this->used_for_db_sessions) {
314 // this is needed because we need to save session to db before closing it
56949c17 315 session_get_instance()->write_close();
5e9dd017 316 $this->used_for_db_sessions = false;
0a2092a3 317 }
10f375aa
EL
318 if ($this->temptables) {
319 $this->temptables->dispose();
320 $this->temptables = null;
321 }
25d854c6 322 if ($this->database_manager) {
323 $this->database_manager->dispose();
324 $this->database_manager = null;
325 }
6cc97718 326 $this->columns = array();
117679db 327 $this->tables = null;
25d854c6 328 }
329
bb5f11ec 330 /**
331 * Called before each db query.
332 * @param string $sql
333 * @param array array of parameters
334 * @param int $type type of query
1500142b 335 * @param mixed $extrainfo driver specific extra information
bb5f11ec 336 * @return void
337 */
1500142b 338 protected function query_start($sql, array $params=null, $type, $extrainfo=null) {
54d51f60 339 if ($this->loggingquery) {
340 return;
341 }
c23b0ea1 342 $this->last_sql = $sql;
343 $this->last_params = $params;
344 $this->last_type = $type;
345 $this->last_extrainfo = $extrainfo;
54d51f60 346 $this->last_time = microtime(true);
c23b0ea1 347
1500142b 348 switch ($type) {
349 case SQL_QUERY_SELECT:
350 case SQL_QUERY_AUX:
351 $this->reads++;
352 break;
353 case SQL_QUERY_INSERT:
354 case SQL_QUERY_UPDATE:
355 case SQL_QUERY_STRUCTURE:
356 $this->writes++;
357 }
c23b0ea1 358
359 $this->print_debug($sql, $params);
bb5f11ec 360 }
361
362 /**
363 * Called immediately after each db query.
364 * @param mixed db specific result
365 * @return void
366 */
1500142b 367 protected function query_end($result) {
54d51f60 368 if ($this->loggingquery) {
369 return;
370 }
b4154c2d 371 if ($result !== false) {
372 $this->query_log();
373 // free memory
374 $this->last_sql = null;
375 $this->last_params = null;
376 return;
377 }
378
379 // remember current info, log queries may alter it
54d51f60 380 $type = $this->last_type;
381 $sql = $this->last_sql;
382 $params = $this->last_params;
383 $time = microtime(true) - $this->last_time;
b4154c2d 384 $error = $this->get_last_error();
54d51f60 385
b4154c2d 386 $this->query_log($error);
54d51f60 387
b4154c2d 388 switch ($type) {
c23b0ea1 389 case SQL_QUERY_SELECT:
390 case SQL_QUERY_AUX:
54d51f60 391 throw new dml_read_exception($error, $sql, $params);
c23b0ea1 392 case SQL_QUERY_INSERT:
393 case SQL_QUERY_UPDATE:
54d51f60 394 throw new dml_write_exception($error, $sql, $params);
591ffe1a 395 case SQL_QUERY_STRUCTURE:
396 $this->get_manager(); // includes ddl exceptions classes ;-)
54d51f60 397 throw new ddl_change_structure_exception($error, $sql);
398 }
399 }
400
401 /**
862d54c3 402 * Log last database query if requested
54d51f60 403 * @param mixed string error or false if not error
404 * @return void
405 */
b4154c2d 406 public function query_log($error=false) {
54d51f60 407 $logall = !empty($this->dboptions['logall']);
408 $logslow = !empty($this->dboptions['logslow']) ? $this->dboptions['logslow'] : false;
409 $logerrors = !empty($this->dboptions['logerrors']);
410 $iserror = ($error !== false);
411
b4154c2d 412 $time = microtime(true) - $this->last_time;
413
54d51f60 414 if ($logall or ($logslow and ($logslow < ($time+0.00001))) or ($iserror and $logerrors)) {
415 $this->loggingquery = true;
416 try {
b4154c2d 417 $backtrace = debug_backtrace();
418 if ($backtrace) {
419 //remove query_log()
420 array_shift($backtrace);
421 }
422 if ($backtrace) {
423 //remove query_end()
424 array_shift($backtrace);
425 }
426 $log = new object();
427 $log->qtype = $this->last_type;
428 $log->sqltext = $this->last_sql;
429 $log->sqlparams = var_export((array)$this->last_params, true);
430 $log->error = (int)$iserror;
431 $log->info = $iserror ? $error : null;
34a2777c 432 $log->backtrace = format_backtrace($backtrace, true);
b4154c2d 433 $log->exectime = $time;
434 $log->timelogged = time();
435 $this->insert_record('log_queries', $log);
54d51f60 436 } catch (Exception $ignored) {
437 }
438 $this->loggingquery = false;
c23b0ea1 439 }
bb5f11ec 440 }
441
6d5a22b2 442 /**
443 * Returns database server info array
444 * @return array
445 */
446 public abstract function get_server_info();
447
448 /**
449 * Returns supported query parameter types
9331d879 450 * @return int bitmask
6d5a22b2 451 */
452 protected abstract function allowed_param_types();
f33e1ed4 453
454 /**
455 * Returns last error reported by database engine.
9331d879 456 * @return string error message
f33e1ed4 457 */
458 public abstract function get_last_error();
459
0487f9e2 460 /**
461 * Print sql debug info
462 * @param string $sql query which caused problems
463 * @param array $params optional query parameters
464 * @param mixed $obj optional library specific object
3d54726f 465 * @return void
0487f9e2 466 */
467 protected function print_debug($sql, array $params=null, $obj=null) {
468 if (!$this->get_debug()) {
469 return;
470 }
7900a54c 471 if (CLI_SCRIPT) {
472 echo "--------------------------------\n";
473 echo $sql."\n";
474 if (!is_null($params)) {
475 echo "[".var_export($params, true)."]\n";
476 }
477 echo "--------------------------------\n";
478 } else {
479 echo "<hr />\n";
480 echo s($sql)."\n";
481 if (!is_null($params)) {
482 echo "[".s(var_export($params, true))."]\n";
483 }
484 echo "<hr />\n";
0487f9e2 485 }
f33e1ed4 486 }
487
082ae821 488 /**
489 * Returns SQL WHERE conditions.
490 *
491 * @param array conditions - must not contain numeric indexes
492 * @return array sql part and params
493 */
494 protected function where_clause(array $conditions=null) {
495 $allowed_types = $this->allowed_param_types();
496 if (empty($conditions)) {
497 return array('', array());
498 }
499 $where = array();
500 $params = array();
501 foreach ($conditions as $key=>$value) {
502 if (is_int($key)) {
503 throw new dml_exception('invalidnumkey');
504 }
505 if (is_null($value)) {
506 $where[] = "$key IS NULL";
507 } else {
6055f89d 508 $isstring = (is_string($value) && !is_number($value));
082ae821 509 if ($allowed_types & SQL_PARAMS_NAMED) {
6055f89d
PS
510 // Need to verify key names because they can contain, originally,
511 // spaces and other forbidden chars when using sql_xxx() functions and friends.
512 $normkey = trim(preg_replace('/[^a-zA-Z0-9-_]/', '_', $key), '-_');
513 if ($normkey !== $key) {
514 debugging('Invalid key found in the conditions array.');
515 }
516 if ($isstring) {
517 $where[] = $this->sql_binary_equal($key, ':'.$normkey);
518 } else {
519 $where[] = "$key = :$normkey";
520 }
521 $params[$normkey] = $value;
522 } else {
523 if ($isstring) {
524 $where[] = $this->sql_binary_equal($key, '?');
525 } else {
526 $where[] = "$key = ?";
527 }
082ae821 528 $params[] = $value;
529 }
530 }
531 }
532 $where = implode(" AND ", $where);
533 return array($where, $params);
534 }
535
0e8e23cc 536 /**
537 * Returns SQL WHERE conditions for the ..._list methods.
538 *
539 * @param string $field the name of a field.
540 * @param array $values the values field might take.
541 * @return array sql part and params
542 */
543 protected function where_clause_list($field, array $values) {
544 $params = array();
545 $select = array();
546 $values = (array)$values;
547 foreach ($values as $value) {
548 if (is_bool($value)) {
549 $value = (int)$value;
550 }
551 if (is_null($value)) {
552 $select[] = "$field IS NULL";
553 } else {
554 $select[] = "$field = ?";
555 $params[] = $value;
556 }
557 }
558 $select = implode(" OR ", $select);
559 return array($select, $params);
560 }
561
f33e1ed4 562 /**
563 * Constructs IN() or = sql fragment
564 * @param mixed $items single or array of values
f38f7ff7 565 * @param int $type bound param type SQL_PARAMS_QM or SQL_PARAMS_NAMED
f33e1ed4 566 * @param string named param placeholder start
3b905063 567 * @param bool true means equal, false not equal
f33e1ed4 568 * @return array - $sql and $params
569 */
3b905063 570 public function get_in_or_equal($items, $type=SQL_PARAMS_QM, $start='param0000', $equal=true) {
44f6ca9e 571 if (is_array($items) and empty($items)) {
1336c8eb 572 throw new coding_exception('moodle_database::get_in_or_equal() does not accept empty arrays');
573 }
f33e1ed4 574 if ($type == SQL_PARAMS_QM) {
575 if (!is_array($items) or count($items) == 1) {
3b905063 576 $sql = $equal ? '= ?' : '<> ?';
1d2b7f03 577 $items = (array)$items;
578 $params = array_values($items);
f33e1ed4 579 } else {
3b905063 580 if ($equal) {
581 $sql = 'IN ('.implode(',', array_fill(0, count($items), '?')).')';
582 } else {
583 $sql = 'NOT IN ('.implode(',', array_fill(0, count($items), '?')).')';
584 }
f33e1ed4 585 $params = array_values($items);
586 }
587
588 } else if ($type == SQL_PARAMS_NAMED) {
1d2b7f03 589 if (!is_array($items)){
3b905063 590 $sql = $equal ? "= :$start" : "<> :$start";
f33e1ed4 591 $params = array($start=>$items);
1d2b7f03 592 } else if (count($items) == 1) {
3b905063 593 $sql = $equal ? "= :$start" : "<> :$start";
1d2b7f03 594 $item = reset($items);
595 $params = array($start=>$item);
f33e1ed4 596 } else {
597 $params = array();
598 $sql = array();
599 foreach ($items as $item) {
600 $params[$start] = $item;
3b905063 601 $sql[] = ':'.$start++;
602 }
603 if ($equal) {
604 $sql = 'IN ('.implode(',', $sql).')';
605 } else {
606 $sql = 'NOT IN ('.implode(',', $sql).')';
f33e1ed4 607 }
f33e1ed4 608 }
609
610 } else {
6a68d8c0 611 throw new dml_exception('typenotimplement');
f33e1ed4 612 }
613 return array($sql, $params);
614 }
615
b922e86b 616 /**
617 * Converts short table name {tablename} to real table name
618 * @param string sql
619 * @return string sql
620 */
621 protected function fix_table_names($sql) {
622 return preg_replace('/\{([a-z][a-z0-9_]*)\}/', $this->prefix.'$1', $sql);
623 }
624
ba63f377 625 /** Internal function */
626 private function _fix_sql_params_dollar_callback($match) {
627 $this->fix_sql_params_i++;
628 return "\$".$this->fix_sql_params_i;
629 }
630
f33e1ed4 631 /**
632 * Normalizes sql query parameters and verifies parameters.
633 * @param string $sql query or part of it
634 * @param array $params query parameters
3d54726f 635 * @return array (sql, params, type of params)
f33e1ed4 636 */
637 public function fix_sql_params($sql, array $params=null) {
638 $params = (array)$params; // mke null array if needed
639 $allowed_types = $this->allowed_param_types();
640
641 // convert table names
b922e86b 642 $sql = $this->fix_table_names($sql);
f33e1ed4 643
73f7ad71 644 // NICOLAS C: Fixed regexp for negative backwards lookahead of double colons. Thanks for Sam Marshall's help
645 $named_count = preg_match_all('/(?<!:):[a-z][a-z0-9_]*/', $sql, $named_matches); // :: used in pgsql casts
646 $dollar_count = preg_match_all('/\$[1-9][0-9]*/', $sql, $dollar_matches);
f33e1ed4 647 $q_count = substr_count($sql, '?');
648
649 $count = 0;
650
651 if ($named_count) {
652 $type = SQL_PARAMS_NAMED;
653 $count = $named_count;
654
655 }
73f7ad71 656 if ($dollar_count) {
f33e1ed4 657 if ($count) {
6a68d8c0 658 throw new dml_exception('mixedtypesqlparam');
f33e1ed4 659 }
73f7ad71 660 $type = SQL_PARAMS_DOLLAR;
661 $count = $dollar_count;
f33e1ed4 662
663 }
664 if ($q_count) {
665 if ($count) {
6a68d8c0 666 throw new dml_exception('mixedtypesqlparam');
f33e1ed4 667 }
668 $type = SQL_PARAMS_QM;
669 $count = $q_count;
670
671 }
672
673 if (!$count) {
674 // ignore params
675 if ($allowed_types & SQL_PARAMS_NAMED) {
676 return array($sql, array(), SQL_PARAMS_NAMED);
677 } else if ($allowed_types & SQL_PARAMS_QM) {
678 return array($sql, array(), SQL_PARAMS_QM);
679 } else {
73f7ad71 680 return array($sql, array(), SQL_PARAMS_DOLLAR);
f33e1ed4 681 }
682 }
683
684 if ($count > count($params)) {
10e99fea 685 $a = new stdClass;
686 $a->expected = $count;
687 $a->actual = count($params);
688 throw new dml_exception('invalidqueryparam', $a);
f33e1ed4 689 }
690
3e51b51d 691 $target_type = $allowed_types;
692
f33e1ed4 693 if ($type & $allowed_types) { // bitwise AND
694 if ($count == count($params)) {
695 if ($type == SQL_PARAMS_QM) {
696 return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based array required
697 } else {
73f7ad71 698 //better do the validation of names below
f33e1ed4 699 }
700 }
701 // needs some fixing or validation - there might be more params than needed
702 $target_type = $type;
f33e1ed4 703 }
704
705 if ($type == SQL_PARAMS_NAMED) {
706 $finalparams = array();
707 foreach ($named_matches[0] as $key) {
708 $key = trim($key, ':');
709 if (!array_key_exists($key, $params)) {
34a36b2e 710 throw new dml_exception('missingkeyinsql', $key, '');
f33e1ed4 711 }
712 $finalparams[$key] = $params[$key];
713 }
714 if ($count != count($finalparams)) {
6a68d8c0 715 throw new dml_exception('duplicateparaminsql');
f33e1ed4 716 }
717
718 if ($target_type & SQL_PARAMS_QM) {
73f7ad71 719 $sql = preg_replace('/(?<!:):[a-z][a-z0-9_]*/', '?', $sql);
f33e1ed4 720 return array($sql, array_values($finalparams), SQL_PARAMS_QM); // 0-based required
721 } else if ($target_type & SQL_PARAMS_NAMED) {
722 return array($sql, $finalparams, SQL_PARAMS_NAMED);
73f7ad71 723 } else { // $type & SQL_PARAMS_DOLLAR
ba63f377 724 //lambda-style functions eat memory - we use globals instead :-(
725 $this->fix_sql_params_i = 0;
726 $sql = preg_replace_callback('/(?<!:):[a-z][a-z0-9_]*/', array($this, '_fix_sql_params_dollar_callback'), $sql);
3f0c17b8 727 return array($sql, array_values($finalparams), SQL_PARAMS_DOLLAR); // 0-based required
f33e1ed4 728 }
729
73f7ad71 730 } else if ($type == SQL_PARAMS_DOLLAR) {
3f0c17b8 731 if ($target_type & SQL_PARAMS_DOLLAR) {
732 return array($sql, array_values($params), SQL_PARAMS_DOLLAR); // 0-based required
935956b8 733 } else if ($target_type & SQL_PARAMS_QM) {
734 $sql = preg_replace('/\$[0-9]+/', '?', $sql);
735 return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based required
736 } else { //$target_type & SQL_PARAMS_NAMED
737 $sql = preg_replace('/\$([0-9]+)/', ':param\\1', $sql);
738 $finalparams = array();
739 foreach ($params as $key=>$param) {
740 $key++;
741 $finalparams['param'.$key] = $param;
742 }
743 return array($sql, $finalparams, SQL_PARAMS_NAMED);
3f0c17b8 744 }
f33e1ed4 745
746 } else { // $type == SQL_PARAMS_QM
747 if (count($params) != $count) {
748 $params = array_slice($params, 0, $count);
749 }
750
751 if ($target_type & SQL_PARAMS_QM) {
752 return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based required
753 } else if ($target_type & SQL_PARAMS_NAMED) {
754 $finalparams = array();
3ff8bf26 755 $pname = 'param0';
f33e1ed4 756 $parts = explode('?', $sql);
757 $sql = array_shift($parts);
758 foreach ($parts as $part) {
759 $param = array_shift($params);
760 $pname++;
761 $sql .= ':'.$pname.$part;
762 $finalparams[$pname] = $param;
763 }
764 return array($sql, $finalparams, SQL_PARAMS_NAMED);
73f7ad71 765 } else { // $type & SQL_PARAMS_DOLLAR
ba63f377 766 //lambda-style functions eat memory - we use globals instead :-(
767 $this->fix_sql_params_i = 0;
768 $sql = preg_replace_callback('/\?/', array($this, '_fix_sql_params_dollar_callback'), $sql);
3f0c17b8 769 return array($sql, array_values($params), SQL_PARAMS_DOLLAR); // 0-based required
f33e1ed4 770 }
771 }
772 }
773
774 /**
71c920cf 775 * Return tables in database WITHOUT current prefix
776 * @return array of table names in lowercase and without prefix
f33e1ed4 777 */
117679db 778 public abstract function get_tables($usecache=true);
f33e1ed4 779
780 /**
e108cea4 781 * Return table indexes - everything lowercased
f33e1ed4 782 * @return array of arrays
783 */
784 public abstract function get_indexes($table);
785
786 /**
787 * Returns datailed information about columns in table. This information is cached internally.
788 * @param string $table name
a7544e37 789 * @param bool $usecache
f33e1ed4 790 * @return array array of database_column_info objects indexed with column names
791 */
a7544e37 792 public abstract function get_columns($table, $usecache=true);
f33e1ed4 793
e3acc8af
EL
794 /**
795 * Normalise values based in RDBMS dependencies (booleans, LOBs...)
796 *
797 * @param database_column_info $column column metadata corresponding with the value we are going to normalise
798 * @param mixed $value value we are going to normalise
799 * @return mixed the normalised value
800 */
801 protected abstract function normalise_value($column, $value);
802
f33e1ed4 803 /**
804 * Reset internal column details cache
805 * @param string $table - empty means all, or one if name of table given
806 * @return void
807 */
117679db 808 public function reset_caches() {
5fe3f5c3 809 $this->columns = array();
117679db 810 $this->tables = null;
a7544e37 811 }
f33e1ed4 812
813 /**
814 * Returns sql generator used for db manipulation.
815 * Used mostly in upgrade.php scripts.
816 * @return object database_manager instance
817 */
818 public function get_manager() {
819 global $CFG;
820
821 if (!$this->database_manager) {
ebf20af0 822 require_once($CFG->libdir.'/ddllib.php');
f33e1ed4 823
824 $classname = $this->get_dbfamily().'_sql_generator';
825 require_once("$CFG->libdir/ddl/$classname.php");
10f375aa 826 $generator = new $classname($this, $this->temptables);
f33e1ed4 827
828 $this->database_manager = new database_manager($this, $generator);
829 }
830 return $this->database_manager;
831 }
832
833 /**
862d54c3 834 * Attempt to change db encoding toUTF-8 if possible
f33e1ed4 835 * @return bool success
836 */
837 public function change_db_encoding() {
838 return false;
839 }
840
841 /**
842 * Is db in unicode mode?
843 * @return bool
844 */
845 public function setup_is_unicodedb() {
846 return true;
847 }
848
849 /**
850 * Enable/disable very detailed debugging
f33e1ed4 851 * @param bool $state
852 */
22d77567 853 public function set_debug($state) {
854 $this->debug = $state;
855 }
f33e1ed4 856
857 /**
858 * Returns debug status
859 * @return bool $state
860 */
22d77567 861 public function get_debug() {
862 return $this->debug;
863 }
f33e1ed4 864
865 /**
866 * Enable/disable detailed sql logging
f33e1ed4 867 * @param bool $state
868 */
22d77567 869 public function set_logging($state) {
7900a54c 870 // adodb sql logging shares one table without prefix per db - this is no longer acceptable :-(
22d77567 871 // we must create one table shared by all drivers
872 }
f33e1ed4 873
874 /**
875 * Do NOT use in code, to be used by database_manager only!
876 * @param string $sql query
22d77567 877 * @return bool true
878 * @throws dml_exception if error
f33e1ed4 879 */
880 public abstract function change_database_structure($sql);
881
882 /**
883 * Execute general sql query. Should be used only when no other method suitable.
884 * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead!
885 * @param string $sql query
886 * @param array $params query parameters
22d77567 887 * @return bool true
888 * @throws dml_exception if error
f33e1ed4 889 */
890 public abstract function execute($sql, array $params=null);
891
892 /**
ad434752 893 * Get a number of records as a moodle_recordset where all the given conditions met.
f33e1ed4 894 *
895 * Selects records from the table $table.
896 *
897 * If specified, only records meeting $conditions.
898 *
899 * If specified, the results will be sorted as specified by $sort. This
900 * is added to the SQL as "ORDER BY $sort". Example values of $sort
862d54c3 901 * might be "time ASC" or "time DESC".
f33e1ed4 902 *
903 * If $fields is specified, only those fields are returned.
904 *
905 * Since this method is a little less readable, use of it should be restricted to
906 * code where it's possible there might be large datasets being returned. For known
907 * small datasets use get_records - it leads to simpler code.
908 *
909 * If you only want some of the records, specify $limitfrom and $limitnum.
910 * The query will skip the first $limitfrom records (according to the sort
911 * order) and then return the next $limitnum records. If either of $limitfrom
912 * or $limitnum is specified, both must be present.
913 *
914 * The return value is a moodle_recordset
862d54c3 915 * if the query succeeds. If an error occurs, false is returned.
f33e1ed4 916 *
917 * @param string $table the table to query.
918 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
919 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
920 * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
921 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
922 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
5212b07f 923 * @return moodle_recordset instance
22d77567 924 * @throws dml_exception if error
f33e1ed4 925 */
926 public function get_recordset($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
927 list($select, $params) = $this->where_clause($conditions);
928 return $this->get_recordset_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum);
929 }
930
931 /**
ad434752 932 * Get a number of records as a moodle_recordset where one field match one list of values.
f33e1ed4 933 *
934 * Only records where $field takes one of the values $values are returned.
0088bd31 935 * $values must be an array of values.
f33e1ed4 936 *
937 * Other arguments and the return type as for @see function get_recordset.
938 *
939 * @param string $table the table to query.
940 * @param string $field a field to check (optional).
941 * @param array $values array of values the field must have
942 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
943 * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
944 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
945 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
5212b07f 946 * @return moodle_recordset instance
22d77567 947 * @throws dml_exception if error
f33e1ed4 948 */
949 public function get_recordset_list($table, $field, array $values, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
0e8e23cc 950 list($select, $params) = $this->where_clause_list($field, $values);
c362878e 951 if (empty($select)) {
952 $select = '? = ?'; /// Fake condition, won't return rows ever. MDL-17645
953 $params = array(1, 2);
954 }
a77aaef2 955 return $this->get_recordset_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum);
f33e1ed4 956 }
957
958 /**
ad434752 959 * Get a number of records as a moodle_recordset which match a particular WHERE clause.
f33e1ed4 960 *
961 * If given, $select is used as the SELECT parameter in the SQL query,
962 * otherwise all records from the table are returned.
963 *
964 * Other arguments and the return type as for @see function get_recordset.
965 *
966 * @param string $table the table to query.
967 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
968 * @param array $params array of sql parameters
969 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
970 * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
971 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
972 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
5212b07f 973 * @return moodle_recordset instance
22d77567 974 * @throws dml_exception if error
f33e1ed4 975 */
976 public function get_recordset_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
66e75f8d 977 $sql = "SELECT $fields FROM {".$table."}";
f33e1ed4 978 if ($select) {
66e75f8d 979 $sql .= " WHERE $select";
f33e1ed4 980 }
981 if ($sort) {
66e75f8d 982 $sql .= " ORDER BY $sort";
f33e1ed4 983 }
66e75f8d 984 return $this->get_recordset_sql($sql, $params, $limitfrom, $limitnum);
f33e1ed4 985 }
986
987 /**
ad434752 988 * Get a number of records as a moodle_recordset using a SQL statement.
989 *
f33e1ed4 990 * Since this method is a little less readable, use of it should be restricted to
991 * code where it's possible there might be large datasets being returned. For known
992 * small datasets use get_records_sql - it leads to simpler code.
993 *
994 * The return type is as for @see function get_recordset.
995 *
996 * @param string $sql the SQL select query to execute.
997 * @param array $params array of sql parameters
998 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
999 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
5212b07f 1000 * @return moodle_recordset instance
22d77567 1001 * @throws dml_exception if error
f33e1ed4 1002 */
1003 public abstract function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0);
1004
1005 /**
b5e1cac9 1006 * Get a number of records as an array of objects where all the given conditions met.
f33e1ed4 1007 *
1008 * If the query succeeds and returns at least one record, the
1009 * return value is an array of objects, one object for each
1010 * record found. The array key is the value from the first
1011 * column of the result set. The object associated with that key
1012 * has a member variable for each column of the results.
1013 *
1014 * @param string $table the table to query.
1015 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
1016 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
1017 * @param string $fields a comma separated list of fields to return (optional, by default
1018 * all fields are returned). The first field will be used as key for the
1019 * array so must be a unique field such as 'id'.
1020 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1021 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
22d77567 1022 * @return array of objects indexed by first column
1023 * @throws dml_exception if error
f33e1ed4 1024 */
1025 public function get_records($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
1026 list($select, $params) = $this->where_clause($conditions);
1027 return $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum);
1028 }
1029
1030 /**
b5e1cac9 1031 * Get a number of records as an array of objects where one field match one list of values.
f33e1ed4 1032 *
1033 * Return value as for @see function get_records.
1034 *
1035 * @param string $table The database table to be checked against.
1036 * @param string $field The field to search
1037 * @param string $values array of values
1038 * @param string $sort Sort order (as valid SQL sort parameter)
1039 * @param string $fields A comma separated list of fields to be returned from the chosen table. If specified,
1040 * the first field should be a unique one such as 'id' since it will be used as a key in the associative
1041 * array.
22d77567 1042 * @return array of objects indexed by first column
1043 * @throws dml_exception if error
f33e1ed4 1044 */
44e1b7d7 1045 public function get_records_list($table, $field, array $values, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
0e8e23cc 1046 list($select, $params) = $this->where_clause_list($field, $values);
44e1b7d7 1047 if (empty($select)) {
1048 // nothing to return
1049 return array();
1050 }
f33e1ed4 1051 return $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum);
1052 }
1053
1054 /**
b5e1cac9 1055 * Get a number of records as an array of objects which match a particular WHERE clause.
f33e1ed4 1056 *
1057 * Return value as for @see function get_records.
1058 *
1059 * @param string $table the table to query.
1060 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1061 * @param array $params array of sql parameters
1062 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
1063 * @param string $fields a comma separated list of fields to return
1064 * (optional, by default all fields are returned). The first field will be used as key for the
1065 * array so must be a unique field such as 'id'.
1066 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1067 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
22d77567 1068 * @return array of objects indexed by first column
1069 * @throws dml_exception if error
f33e1ed4 1070 */
1071 public function get_records_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
1072 if ($select) {
1073 $select = "WHERE $select";
1074 }
1075 if ($sort) {
1076 $sort = " ORDER BY $sort";
1077 }
e4a16bdc 1078 return $this->get_records_sql("SELECT $fields FROM {" . $table . "} $select $sort", $params, $limitfrom, $limitnum);
f33e1ed4 1079 }
1080
1081 /**
b5e1cac9 1082 * Get a number of records as an array of objects using a SQL statement.
f33e1ed4 1083 *
1084 * Return value as for @see function get_records.
1085 *
1086 * @param string $sql the SQL select query to execute. The first column of this SELECT statement
1087 * must be a unique value (usually the 'id' field), as it will be used as the key of the
1088 * returned array.
1089 * @param array $params array of sql parameters
1090 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1091 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
22d77567 1092 * @return array of objects indexed by first column
1093 * @throws dml_exception if error
f33e1ed4 1094 */
1095 public abstract function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0);
1096
1097 /**
b5e1cac9 1098 * Get the first two columns from a number of records as an associative array where all the given conditions met.
f33e1ed4 1099 *
1100 * Arguments as for @see function get_recordset.
1101 *
1102 * If no errors occur the return value
1103 * is an associative whose keys come from the first field of each record,
1104 * and whose values are the corresponding second fields.
1105 * False is returned if an error occurs.
1106 *
1107 * @param string $table the table to query.
1108 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
1109 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
1110 * @param string $fields a comma separated list of fields to return - the number of fields should be 2!
1111 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1112 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
22d77567 1113 * @return array an associative array
1114 * @throws dml_exception if error
f33e1ed4 1115 */
1116 public function get_records_menu($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
1117 $menu = array();
1118 if ($records = $this->get_records($table, $conditions, $sort, $fields, $limitfrom, $limitnum)) {
1119 foreach ($records as $record) {
1120 $record = (array)$record;
1121 $key = array_shift($record);
1122 $value = array_shift($record);
1123 $menu[$key] = $value;
1124 }
1125 }
1126 return $menu;
1127 }
1128
1129 /**
b5e1cac9 1130 * Get the first two columns from a number of records as an associative array which match a particular WHERE clause.
f33e1ed4 1131 *
1132 * Arguments as for @see function get_recordset_select.
1133 * Return value as for @see function get_records_menu.
1134 *
1135 * @param string $table The database table to be checked against.
1136 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1137 * @param array $params array of sql parameters
1138 * @param string $sort Sort order (optional) - a valid SQL order parameter
1139 * @param string $fields A comma separated list of fields to be returned from the chosen table - the number of fields should be 2!
1140 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1141 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
22d77567 1142 * @return array an associative array
1143 * @throws dml_exception if error
f33e1ed4 1144 */
1145 public function get_records_select_menu($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
1146 $menu = array();
1147 if ($records = $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum)) {
1148 foreach ($records as $record) {
ce7fbdb8 1149 $record = (array)$record;
1150 $key = array_shift($record);
1151 $value = array_shift($record);
f33e1ed4 1152 $menu[$key] = $value;
1153 }
1154 }
1155 return $menu;
1156 }
1157
1158 /**
b5e1cac9 1159 * Get the first two columns from a number of records as an associative array using a SQL statement.
f33e1ed4 1160 *
1161 * Arguments as for @see function get_recordset_sql.
1162 * Return value as for @see function get_records_menu.
1163 *
1164 * @param string $sql The SQL string you wish to be executed.
1165 * @param array $params array of sql parameters
1166 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
1167 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
22d77567 1168 * @return array an associative array
1169 * @throws dml_exception if error
f33e1ed4 1170 */
1171 public function get_records_sql_menu($sql, array $params=null, $limitfrom=0, $limitnum=0) {
1172 $menu = array();
1173 if ($records = $this->get_records_sql($sql, $params, $limitfrom, $limitnum)) {
1174 foreach ($records as $record) {
ce7fbdb8 1175 $record = (array)$record;
1176 $key = array_shift($record);
1177 $value = array_shift($record);
f33e1ed4 1178 $menu[$key] = $value;
1179 }
1180 }
1181 return $menu;
1182 }
1183
1184 /**
b5e1cac9 1185 * Get a single database record as an object where all the given conditions met.
f33e1ed4 1186 *
1187 * @param string $table The table to select from.
1188 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
1189 * @param string $fields A comma separated list of fields to be returned from the chosen table.
e6c6531c 1190 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1191 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1192 * MUST_EXIST means throw exception if no record or multiple records found
af12ea93 1193 * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode
22d77567 1194 * @throws dml_exception if error
f33e1ed4 1195 */
2bad34a3 1196 public function get_record($table, array $conditions, $fields='*', $strictness=IGNORE_MISSING) {
f33e1ed4 1197 list($select, $params) = $this->where_clause($conditions);
af12ea93 1198 return $this->get_record_select($table, $select, $params, $fields, $strictness);
f33e1ed4 1199 }
1200
1201 /**
b5e1cac9 1202 * Get a single database record as an object which match a particular WHERE clause.
f33e1ed4 1203 *
1204 * @param string $table The database table to be checked against.
1205 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1206 * @param array $params array of sql parameters
e6c6531c 1207 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1208 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1209 * MUST_EXIST means throw exception if no record or multiple records found
af12ea93 1210 * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode
22d77567 1211 * @throws dml_exception if error
f33e1ed4 1212 */
2bad34a3 1213 public function get_record_select($table, $select, array $params=null, $fields='*', $strictness=IGNORE_MISSING) {
f33e1ed4 1214 if ($select) {
1215 $select = "WHERE $select";
1216 }
af12ea93 1217 try {
e4a16bdc 1218 return $this->get_record_sql("SELECT $fields FROM {" . $table . "} $select", $params, $strictness);
af12ea93 1219 } catch (dml_missing_record_exception $e) {
1220 // create new exception which will contain correct table name
1221 throw new dml_missing_record_exception($table, $e->sql, $e->params);
1222 }
f33e1ed4 1223 }
1224
1225 /**
b5e1cac9 1226 * Get a single database record as an object using a SQL statement.
f33e1ed4 1227 *
af12ea93 1228 * The SQL statement should normally only return one record.
1229 * It is recommended to use get_records_sql() if more matches possible!
f33e1ed4 1230 *
1231 * @param string $sql The SQL string you wish to be executed, should normally only return one record.
1232 * @param array $params array of sql parameters
e6c6531c 1233 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1234 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1235 * MUST_EXIST means throw exception if no record or multiple records found
af12ea93 1236 * @return mixed a fieldset object containing the first matching record, false or exception if error not found depending on mode
22d77567 1237 * @throws dml_exception if error
f33e1ed4 1238 */
2bad34a3 1239 public function get_record_sql($sql, array $params=null, $strictness=IGNORE_MISSING) {
e6c6531c 1240 $strictness = (int)$strictness; // we support true/false for BC reasons too
1241 if ($strictness == IGNORE_MULTIPLE) {
af12ea93 1242 $count = 1;
1243 } else {
1244 $count = 0;
1245 }
30fff7b7 1246 if (!$records = $this->get_records_sql($sql, $params, 0, $count)) {
22d77567 1247 // not found
e6c6531c 1248 if ($strictness == MUST_EXIST) {
af12ea93 1249 throw new dml_missing_record_exception('', $sql, $params);
1250 }
f33e1ed4 1251 return false;
1252 }
1253
af12ea93 1254 if (count($records) > 1) {
e6c6531c 1255 if ($strictness == MUST_EXIST) {
af12ea93 1256 throw new dml_multiple_records_exception($sql, $params);
1257 }
f33e1ed4 1258 debugging('Error: mdb->get_record() found more than one record!');
1259 }
1260
30fff7b7 1261 $return = reset($records);
f33e1ed4 1262 return $return;
1263 }
1264
1265 /**
b5e1cac9 1266 * Get a single field value from a table record where all the given conditions met.
f33e1ed4 1267 *
1268 * @param string $table the table to query.
1269 * @param string $return the field to return the value of.
1270 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
e6c6531c 1271 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1272 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1273 * MUST_EXIST means throw exception if no record or multiple records found
22d77567 1274 * @return mixed the specified value false if not found
1275 * @throws dml_exception if error
f33e1ed4 1276 */
2bad34a3 1277 public function get_field($table, $return, array $conditions, $strictness=IGNORE_MISSING) {
f33e1ed4 1278 list($select, $params) = $this->where_clause($conditions);
af12ea93 1279 return $this->get_field_select($table, $return, $select, $params, $strictness);
f33e1ed4 1280 }
1281
1282 /**
b5e1cac9 1283 * Get a single field value from a table record which match a particular WHERE clause.
f33e1ed4 1284 *
1285 * @param string $table the table to query.
1286 * @param string $return the field to return the value of.
1287 * @param string $select A fragment of SQL to be used in a where clause returning one row with one column
1288 * @param array $params array of sql parameters
e6c6531c 1289 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1290 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1291 * MUST_EXIST means throw exception if no record or multiple records found
22d77567 1292 * @return mixed the specified value false if not found
1293 * @throws dml_exception if error
f33e1ed4 1294 */
2bad34a3 1295 public function get_field_select($table, $return, $select, array $params=null, $strictness=IGNORE_MISSING) {
f33e1ed4 1296 if ($select) {
1297 $select = "WHERE $select";
1298 }
af12ea93 1299 try {
1300 return $this->get_field_sql("SELECT $return FROM {" . $table . "} $select", $params, $strictness);
1301 } catch (dml_missing_record_exception $e) {
1302 // create new exception which will contain correct table name
1303 throw new dml_missing_record_exception($table, $e->sql, $e->params);
1304 }
f33e1ed4 1305 }
1306
1307 /**
ad434752 1308 * Get a single field value (first field) using a SQL statement.
f33e1ed4 1309 *
1310 * @param string $table the table to query.
1311 * @param string $return the field to return the value of.
1312 * @param string $sql The SQL query returning one row with one column
1313 * @param array $params array of sql parameters
e6c6531c 1314 * @param int $strictness IGNORE_MISSING means compatible mode, false returned if record not found, debug message if more found;
1315 * IGNORE_MULTIPLE means return first, ignore multiple records found(not recommended);
1316 * MUST_EXIST means throw exception if no record or multiple records found
22d77567 1317 * @return mixed the specified value false if not found
1318 * @throws dml_exception if error
f33e1ed4 1319 */
2bad34a3 1320 public function get_field_sql($sql, array $params=null, $strictness=IGNORE_MISSING) {
af12ea93 1321 if (!$record = $this->get_record_sql($sql, $params, $strictness)) {
1322 return false;
f33e1ed4 1323 }
af12ea93 1324
1325 $record = (array)$record;
1326 return reset($record); // first column
f33e1ed4 1327 }
1328
1329 /**
ad434752 1330 * Selects records and return values of chosen field as an array which match a particular WHERE clause.
f33e1ed4 1331 *
1332 * @param string $table the table to query.
1333 * @param string $return the field we are intered in
1334 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1335 * @param array $params array of sql parameters
3503dcad 1336 * @return array of values
22d77567 1337 * @throws dml_exception if error
f33e1ed4 1338 */
1339 public function get_fieldset_select($table, $return, $select, array $params=null) {
1340 if ($select) {
1341 $select = "WHERE $select";
1342 }
e4a16bdc 1343 return $this->get_fieldset_sql("SELECT $return FROM {" . $table . "} $select", $params);
f33e1ed4 1344 }
1345
1346 /**
ad434752 1347 * Selects records and return values (first field) as an array using a SQL statement.
f33e1ed4 1348 *
1349 * @param string $sql The SQL query
1350 * @param array $params array of sql parameters
3503dcad 1351 * @return array of values
22d77567 1352 * @throws dml_exception if error
f33e1ed4 1353 */
1354 public abstract function get_fieldset_sql($sql, array $params=null);
1355
1356 /**
1357 * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
1358 * @param string $table name
1359 * @param mixed $params data record as object or array
1360 * @param bool $returnit return it of inserted record
1361 * @param bool $bulk true means repeated inserts expected
94898738 1362 * @param bool $customsequence true if 'id' included in $params, disables $returnid
3503dcad 1363 * @return bool|int true or new id
22d77567 1364 * @throws dml_exception if error
f33e1ed4 1365 */
94898738 1366 public abstract function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false);
f33e1ed4 1367
1368 /**
b5e1cac9 1369 * Insert a record into a table and return the "id" field if required.
1370 *
f33e1ed4 1371 * Some conversions and safety checks are carried out. Lobs are supported.
1372 * If the return ID isn't required, then this just reports success as true/false.
1373 * $data is an object containing needed data
1374 * @param string $table The database table to be inserted into
1375 * @param object $data A data object with values for one or more fields in the record
1376 * @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.
3503dcad 1377 * @return bool|int true or new id
22d77567 1378 * @throws dml_exception if error
f33e1ed4 1379 */
1380 public abstract function insert_record($table, $dataobject, $returnid=true, $bulk=false);
1381
94898738 1382 /**
1383 * Import a record into a table, id field is required.
1384 * Safety checks are NOT carried out. Lobs are supported.
1385 *
1386 * @param string $table name of database table to be inserted into
1387 * @param object $dataobject A data object with values for one or more fields in the record
22d77567 1388 * @return bool true
1389 * @throws dml_exception if error
94898738 1390 */
1391 public abstract function import_record($table, $dataobject);
1392
f33e1ed4 1393 /**
1394 * Update record in database, as fast as possible, no safety checks, lobs not supported.
1395 * @param string $table name
1396 * @param mixed $params data record as object or array
1397 * @param bool true means repeated updates expected
22d77567 1398 * @return bool true
1399 * @throws dml_exception if error
f33e1ed4 1400 */
1401 public abstract function update_record_raw($table, $params, $bulk=false);
1402
1403 /**
1404 * Update a record in a table
1405 *
1406 * $dataobject is an object containing needed data
1407 * Relies on $dataobject having a variable "id" to
1408 * specify the record to update
1409 *
1410 * @param string $table The database table to be checked against.
1411 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
1412 * @param bool true means repeated updates expected
22d77567 1413 * @return bool true
1414 * @throws dml_exception if error
f33e1ed4 1415 */
1416 public abstract function update_record($table, $dataobject, $bulk=false);
1417
1418
1419 /**
b5e1cac9 1420 * Set a single field in every table record where all the given conditions met.
f33e1ed4 1421 *
1422 * @param string $table The database table to be checked against.
1423 * @param string $newfield the field to set.
1424 * @param string $newvalue the value to set the field to.
1425 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
22d77567 1426 * @return bool true
1427 * @throws dml_exception if error
f33e1ed4 1428 */
1429 public function set_field($table, $newfield, $newvalue, array $conditions=null) {
1430 list($select, $params) = $this->where_clause($conditions);
1431 return $this->set_field_select($table, $newfield, $newvalue, $select, $params);
1432 }
1433
1434 /**
b5e1cac9 1435 * Set a single field in every table record which match a particular WHERE clause.
f33e1ed4 1436 *
1437 * @param string $table The database table to be checked against.
1438 * @param string $newfield the field to set.
1439 * @param string $newvalue the value to set the field to.
1440 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1441 * @param array $params array of sql parameters
22d77567 1442 * @return bool true
1443 * @throws dml_exception if error
f33e1ed4 1444 */
1445 public abstract function set_field_select($table, $newfield, $newvalue, $select, array $params=null);
1446
1447
1448 /**
1449 * Count the records in a table where all the given conditions met.
1450 *
1451 * @param string $table The table to query.
1452 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
1453 * @return int The count of records returned from the specified criteria.
22d77567 1454 * @throws dml_exception if error
f33e1ed4 1455 */
4906c7de 1456 public function count_records($table, array $conditions=null) {
f33e1ed4 1457 list($select, $params) = $this->where_clause($conditions);
1458 return $this->count_records_select($table, $select, $params);
1459 }
1460
1461 /**
1462 * Count the records in a table which match a particular WHERE clause.
1463 *
1464 * @param string $table The database table to be checked against.
1465 * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call.
1466 * @param array $params array of sql parameters
1467 * @param string $countitem The count string to be used in the SQL call. Default is COUNT('x').
1468 * @return int The count of records returned from the specified criteria.
22d77567 1469 * @throws dml_exception if error
f33e1ed4 1470 */
1471 public function count_records_select($table, $select, array $params=null, $countitem="COUNT('x')") {
1472 if ($select) {
1473 $select = "WHERE $select";
1474 }
e4a16bdc 1475 return $this->count_records_sql("SELECT $countitem FROM {" . $table . "} $select", $params);
f33e1ed4 1476 }
1477
1478 /**
1479 * Get the result of a SQL SELECT COUNT(...) query.
1480 *
1481 * Given a query that counts rows, return that count. (In fact,
1482 * given any query, return the first field of the first record
1483 * returned. However, this method should only be used for the
862d54c3 1484 * intended purpose.) If an error occurs, 0 is returned.
f33e1ed4 1485 *
1486 * @param string $sql The SQL string you wish to be executed.
1487 * @param array $params array of sql parameters
22d77567 1488 * @return int the count
1489 * @throws dml_exception if error
f33e1ed4 1490 */
1491 public function count_records_sql($sql, array $params=null) {
1492 if ($count = $this->get_field_sql($sql, $params)) {
1493 return $count;
1494 } else {
1495 return 0;
1496 }
1497 }
1498
1499 /**
1500 * Test whether a record exists in a table where all the given conditions met.
1501 *
1502 * The record to test is specified by giving up to three fields that must
1503 * equal the corresponding values.
1504 *
1505 * @param string $table The table to check.
1506 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
1507 * @return bool true if a matching record exists, else false.
22d77567 1508 * @throws dml_exception if error
f33e1ed4 1509 */
1510 public function record_exists($table, array $conditions) {
1511 list($select, $params) = $this->where_clause($conditions);
1512 return $this->record_exists_select($table, $select, $params);
1513 }
1514
1515 /**
1516 * Test whether any records exists in a table which match a particular WHERE clause.
1517 *
1518 * @param string $table The database table to be checked against.
1519 * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call.
1520 * @param array $params array of sql parameters
1521 * @return bool true if a matching record exists, else false.
22d77567 1522 * @throws dml_exception if error
f33e1ed4 1523 */
1524 public function record_exists_select($table, $select, array $params=null) {
1525 if ($select) {
1526 $select = "WHERE $select";
1527 }
e4a16bdc 1528 return $this->record_exists_sql("SELECT 'x' FROM {" . $table . "} $select", $params);
f33e1ed4 1529 }
1530
1531 /**
1532 * Test whether a SQL SELECT statement returns any records.
1533 *
1534 * This function returns true if the SQL statement executes
1535 * without any errors and returns at least one record.
1536 *
1537 * @param string $sql The SQL statement to execute.
1538 * @param array $params array of sql parameters
1539 * @return bool true if the SQL executes without errors and returns at least one record.
22d77567 1540 * @throws dml_exception if error
f33e1ed4 1541 */
1542 public function record_exists_sql($sql, array $params=null) {
1543 if ($mrs = $this->get_recordset_sql($sql, $params, 0, 1)) {
1544 $return = $mrs->valid();
1545 $mrs->close();
1546 return $return;
1547 }
1548 return false;
1549 }
1550
1551 /**
1552 * Delete the records from a table where all the given conditions met.
b820eb8c 1553 * If conditions not specified, table is truncated.
f33e1ed4 1554 *
1555 * @param string $table the table to delete from.
1556 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
22d77567 1557 * @return bool true.
1558 * @throws dml_exception if error
f33e1ed4 1559 */
b820eb8c 1560 public function delete_records($table, array $conditions=null) {
1561 if (is_null($conditions)) {
1562 return $this->execute("TRUNCATE TABLE {".$table."}");
1563 }
f33e1ed4 1564 list($select, $params) = $this->where_clause($conditions);
1565 return $this->delete_records_select($table, $select, $params);
1566 }
1567
0e8e23cc 1568 /**
1569 * Delete the records from a table where one field match one list of values.
1570 *
1571 * @param string $table the table to delete from.
1572 * @param string $field The field to search
38ead57d 1573 * @param array $values array of values
0e8e23cc 1574 * @return bool true.
1575 * @throws dml_exception if error
1576 */
c362878e 1577 public function delete_records_list($table, $field, array $values) {
0e8e23cc 1578 list($select, $params) = $this->where_clause_list($field, $values);
1579 if (empty($select)) {
1580 // nothing to delete
c362878e 1581 return true;
0e8e23cc 1582 }
1583 return $this->delete_records_select($table, $select, $params);
1584 }
1585
f33e1ed4 1586 /**
b5e1cac9 1587 * Delete one or more records from a table which match a particular WHERE clause.
f33e1ed4 1588 *
1589 * @param string $table The database table to be checked against.
1590 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1591 * @param array $params array of sql parameters
22d77567 1592 * @return bool true.
1593 * @throws dml_exception if error
f33e1ed4 1594 */
1595 public abstract function delete_records_select($table, $select, array $params=null);
1596
1597
1598
862d54c3 1599/// sql constructs
082ae821 1600 /**
1601 * Returns the FROM clause required by some DBs in all SELECT statements.
1602 *
1603 * To be used in queries not having FROM clause to provide cross_db
1604 * Most DBs don't need it, hence the default is ''
3d54726f 1605 * @return string
082ae821 1606 */
1607 public function sql_null_from_clause() {
1608 return '';
1609 }
1610
f33e1ed4 1611 /**
1612 * Returns the SQL text to be used in order to perform one bitwise AND operation
1613 * between 2 integers.
c643c2f5 1614 *
1615 * NOTE: The SQL result is a number and can not be used directly in
1616 * SQL condition, please compare it to some number to get a bool!!
1617 *
38ead57d
PS
1618 * @param int $int1 first integer in the operation
1619 * @param int $int2 second integer in the operation
c643c2f5 1620 * @return string the piece of SQL code to be used in your statement
f33e1ed4 1621 */
1622 public function sql_bitand($int1, $int2) {
1623 return '((' . $int1 . ') & (' . $int2 . '))';
1624 }
1625
1626 /**
1627 * Returns the SQL text to be used in order to perform one bitwise NOT operation
1628 * with 1 integer.
740f769b 1629 *
38ead57d 1630 * @param int $int1 integer in the operation
f33e1ed4 1631 * @return string the piece of SQL code to be used in your statement.
1632 */
1633 public function sql_bitnot($int1) {
1634 return '(~(' . $int1 . '))';
1635 }
1636
1637 /**
1638 * Returns the SQL text to be used in order to perform one bitwise OR operation
1639 * between 2 integers.
740f769b 1640 *
c643c2f5 1641 * NOTE: The SQL result is a number and can not be used directly in
1642 * SQL condition, please compare it to some number to get a bool!!
1643 *
38ead57d
PS
1644 * @param int $int1 first integer in the operation
1645 * @param int $int2 second integer in the operation
f33e1ed4 1646 * @return string the piece of SQL code to be used in your statement.
1647 */
1648 public function sql_bitor($int1, $int2) {
1649 return '((' . $int1 . ') | (' . $int2 . '))';
1650 }
1651
1652 /**
1653 * Returns the SQL text to be used in order to perform one bitwise XOR operation
1654 * between 2 integers.
740f769b 1655 *
c643c2f5 1656 * NOTE: The SQL result is a number and can not be used directly in
1657 * SQL condition, please compare it to some number to get a bool!!
1658 *
38ead57d
PS
1659 * @param int $int1 first integer in the operation
1660 * @param int $int2 second integer in the operation
f33e1ed4 1661 * @return string the piece of SQL code to be used in your statement.
1662 */
1663 public function sql_bitxor($int1, $int2) {
1664 return '((' . $int1 . ') ^ (' . $int2 . '))';
1665 }
1666
e6df3734 1667 /**
1668 * Returns the SQL text to be used in order to perform module '%'
862d54c3 1669 * operation - remainder after division
e6df3734 1670 *
38ead57d
PS
1671 * @param int $int1 first integer in the operation
1672 * @param int $int2 second integer in the operation
e6df3734 1673 * @return string the piece of SQL code to be used in your statement.
1674 */
1675 public function sql_modulo($int1, $int2) {
1676 return '((' . $int1 . ') % (' . $int2 . '))';
1677 }
1678
888375bc 1679 /**
740f769b 1680 * Returns the correct CEIL expression applied to fieldname.
1681 *
38ead57d 1682 * @param string $fieldname the field (or expression) we are going to ceil
888375bc 1683 * @return string the piece of SQL code to be used in your ceiling statement
1684 * Most DB use CEIL(), hence it's the default.
1685 */
1686 public function sql_ceil($fieldname) {
1687 return ' CEIL(' . $fieldname . ')';
1688 }
1689
f33e1ed4 1690 /**
1691 * Returns the SQL to be used in order to CAST one CHAR column to INTEGER.
1692 *
1693 * Be aware that the CHAR column you're trying to cast contains really
1694 * int values or the RDBMS will throw an error!
1695 *
38ead57d
PS
1696 * @param string $fieldname the name of the field to be casted
1697 * @param bool $text to specify if the original column is one TEXT (CLOB) column (true). Defaults to false.
f33e1ed4 1698 * @return string the piece of SQL code to be used in your statement.
1699 */
1700 public function sql_cast_char2int($fieldname, $text=false) {
1701 return ' ' . $fieldname . ' ';
1702 }
1703
29f83769 1704 /**
1705 * Returns the SQL to be used in order to CAST one CHAR column to REAL number.
1706 *
1707 * Be aware that the CHAR column you're trying to cast contains really
1708 * numbers or the RDBMS will throw an error!
1709 *
38ead57d
PS
1710 * @param string $fieldname the name of the field to be casted
1711 * @param bool $text to specify if the original column is one TEXT (CLOB) column (true). Defaults to false.
29f83769 1712 * @return string the piece of SQL code to be used in your statement.
1713 */
1714 public function sql_cast_char2real($fieldname, $text=false) {
1715 return ' ' . $fieldname . ' ';
1716 }
1717
adff97c5 1718 /**
1719 * Returns the SQL to be used in order to an UNSIGNED INTEGER column to SIGNED.
1720 *
1721 * (Only MySQL needs this. MySQL things that 1 * -1 = 18446744073709551615
1722 * if the 1 comes from an unsigned column).
1723 *
38ead57d 1724 * @param string $fieldname the name of the field to be cast
adff97c5 1725 * @return string the piece of SQL code to be used in your statement.
1726 */
1727 public function sql_cast_2signed($fieldname) {
1728 return ' ' . $fieldname . ' ';
1729 }
1730
f33e1ed4 1731 /**
1732 * Returns the SQL text to be used to compare one TEXT (clob) column with
1733 * one varchar column, because some RDBMS doesn't support such direct
1734 * comparisons.
740f769b 1735 *
38ead57d
PS
1736 * @param string $fieldname the name of the TEXT field we need to order by
1737 * @param int $numchars of chars to use for the ordering (defaults to 32)
f33e1ed4 1738 * @return string the piece of SQL code to be used in your statement.
1739 */
1740 public function sql_compare_text($fieldname, $numchars=32) {
1741 return $this->sql_order_by_text($fieldname, $numchars);
1742 }
1743
6055f89d
PS
1744 /**
1745 * Case and collation sensitive string 'string = string'
1746 * @param string $string1
1747 * @param string $string2
1748 * @return string SQL fragment
1749 */
1750 public function sql_binary_equal($string1, $string2) {
1751 return "$string1 = $string2";
1752 }
1753
1754 /**
1755 * Returns 'LIKE' part of a query.
1756 *
1757 * @param string $fieldname usually name of the table column
1758 * @param string $param usually bound query parameter (?, :named)
1759 * @param bool $casesensitive use case sensitive search
1760 * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
1761 * @param string $escapechar escape char for '%' and '_'
1762 * @return string SQL code fragment
1763 */
1764 public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $escapechar = '\\') {
1765 if (strpos($param, '%') !== false) {
1766 debugging('Potential SQL injection detected, sql_ilike() expects bound parameters (? or :named)');
1767 }
1768 // by default ignore any sensitiveness - each database does it in a different way
1769 return "$fieldname LIKE $param ESCAPE '$escapechar'";
1770 }
1771
1772 /**
1773 * Escape sql LIKE special characters.
1774 * @param string $text
1775 * @param string $escapechar
1776 * @return string
1777 */
1778 public function sql_like_escape($text, $escapechar = '\\') {
1779 $text = str_replace('_', $escapechar.'_', $text);
1780 $text = str_replace('%', $escapechar.'%', $text);
1781 return $text;
1782 }
1783
082ae821 1784 /**
1785 * Returns the proper SQL to do LIKE in a case-insensitive way.
1786 *
1787 * Note the LIKE are case sensitive for Oracle. Oracle 10g is required to use
862d54c3 1788 * the case insensitive search using regexp_like() or NLS_COMP=LINGUISTIC :-(
082ae821 1789 * See http://docs.moodle.org/en/XMLDB_Problems#Case-insensitive_searches
1790 *
6055f89d 1791 * @deprecated
082ae821 1792 * @return string
1793 */
1794 public function sql_ilike() {
6055f89d 1795 //TODO: debugging('sql_ilike() is deprecated, please use sql_like() instead');
082ae821 1796 return 'LIKE';
1797 }
1798
f33e1ed4 1799 /**
1800 * Returns the proper SQL to do CONCAT between the elements passed
96d13878 1801 * Can take many parameters
f33e1ed4 1802 *
38ead57d
PS
1803 * This function accepts variable number of string parameters.
1804 *
f33e1ed4 1805 * @return string
1806 */
1807 public abstract function sql_concat();
1808
1809 /**
1810 * Returns the proper SQL to do CONCAT between the elements passed
1811 * with a given separator
1812 *
f33e1ed4 1813 * @param string $separator
1814 * @param array $elements
1815 * @return string
1816 */
1817 public abstract function sql_concat_join($separator="' '", $elements=array());
1818
1819 /**
1820 * Returns the proper SQL (for the dbms in use) to concatenate $firstname and $lastname
740f769b 1821 * TODO: Does this really need to be here? Eloy 20070727.
3d54726f 1822 * TODO: we definitely do not! (skodak)
f33e1ed4 1823 *
38ead57d
PS
1824 * @param string $first User's first name
1825 * @param string $last User's last name
f33e1ed4 1826 * @return string
1827 */
1828 function sql_fullname($first='firstname', $last='lastname') {
1829 return $this->sql_concat($first, "' '", $last);
1830 }
1831
f33e1ed4 1832 /**
1833 * Returns the SQL text to be used to order by one TEXT (clob) column, because
1834 * some RDBMS doesn't support direct ordering of such fields.
740f769b 1835 *
f33e1ed4 1836 * Note that the use or queries being ordered by TEXT columns must be minimised,
1837 * because it's really slooooooow.
38ead57d
PS
1838 *
1839 * @param string $fieldname the name of the TEXT field we need to order by
1840 * @param string $numchars of chars to use for the ordering (defaults to 32)
f33e1ed4 1841 * @return string the piece of SQL code to be used in your statement.
1842 */
1843 public function sql_order_by_text($fieldname, $numchars=32) {
1844 return $fieldname;
1845 }
1846
7e0db2e2 1847 /**
1848 * Returns the SQL text to be used to calculate the length in characters of one expression.
38ead57d 1849 * @param string $fieldname or expression to calculate its length in characters.
7e0db2e2 1850 * @return string the piece of SQL code to be used in the statement.
1851 */
1852 public function sql_length($fieldname) {
1853 return ' LENGTH(' . $fieldname . ')';
1854 }
1855
f33e1ed4 1856 /**
ab62bd23 1857 * Returns the proper substr() SQL text used to extract substrings from DB
655bbf51 1858 * NOTE: this was originally returning only function name
740f769b 1859 *
655bbf51 1860 * @param string $expr some string field, no aggregates
862d54c3
PS
1861 * @param mixed $start integer or expression evaluating to int (1 based value; first char has index 1)
1862 * @param mixed $length optional integer or expression evaluating to int
655bbf51 1863 * @return string sql fragment
f33e1ed4 1864 */
655bbf51 1865 public function sql_substr($expr, $start, $length=false) {
1866 if (count(func_get_args()) < 2) {
862d54c3 1867 throw new coding_exception('moodle_database::sql_substr() requires at least two parameters', 'Originally this function was only returning name of SQL substring function, it now requires all parameters.');
655bbf51 1868 }
1869 if ($length === false) {
40b5f655 1870 return "SUBSTR($expr, $start)";
655bbf51 1871 } else {
1872 return "SUBSTR($expr, $start, $length)";
1873 }
1874 }
f33e1ed4 1875
1d861fce 1876 /**
1877 * Returns the SQL for returning searching one string for the location of another.
e16e3881 1878 * Note, there is no guarantee which order $needle, $haystack will be in
1d861fce 1879 * the resulting SQL, so when using this method, and both arguments contain
1880 * placeholders, you should use named placeholders.
1881 * @param string $needle the SQL expression that will be searched for.
1882 * @param string $haystack the SQL expression that will be searched in.
1883 * @return string the required SQL
1884 */
1885 public function sql_position($needle, $haystack) {
1886 // Implementation using standard SQL.
1887 return "POSITION(($needle) IN ($haystack))";
1888 }
1889
f33e1ed4 1890 /**
1891 * Returns the empty string char used by every supported DB. To be used when
1892 * we are searching for that values in our queries. Only Oracle uses this
1893 * for now (will be out, once we migrate to proper NULLs if that days arrives)
3d54726f 1894 * @return string
f33e1ed4 1895 */
1896 function sql_empty() {
1897 return '';
1898 }
1899
1900 /**
1901 * Returns the proper SQL to know if one field is empty.
1902 *
1903 * Note that the function behavior strongly relies on the
1904 * parameters passed describing the field so, please, be accurate
862d54c3 1905 * when specifying them.
f33e1ed4 1906 *
1907 * Also, note that this function is not suitable to look for
1908 * fields having NULL contents at all. It's all for empty values!
1909 *
862d54c3 1910 * This function should be applied in all the places where conditions of
f33e1ed4 1911 * the type:
1912 *
1913 * ... AND fieldname = '';
1914 *
1915 * are being used. Final result should be:
1916 *
1917 * ... AND ' . sql_isempty('tablename', 'fieldname', true/false, true/false);
1918 *
1919 * (see parameters description below)
1920 *
1921 * @param string $tablename name of the table (without prefix). Not used for now but can be
1922 * necessary in the future if we want to use some introspection using
1923 * meta information against the DB. /// TODO ///
1924 * @param string $fieldname name of the field we are going to check
1925 * @param boolean $nullablefield to specify if the field us nullable (true) or no (false) in the DB
1926 * @param boolean $textfield to specify if it is a text (also called clob) field (true) or a varchar one (false)
1927 * @return string the sql code to be added to check for empty values
1928 */
1929 public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) {
63b3d8ab 1930 return " ($fieldname = '') ";
f33e1ed4 1931 }
1932
1933 /**
1934 * Returns the proper SQL to know if one field is not empty.
1935 *
1936 * Note that the function behavior strongly relies on the
1937 * parameters passed describing the field so, please, be accurate
862d54c3 1938 * when specifying them.
f33e1ed4 1939 *
1940 * This function should be applied in all the places where conditions of
1941 * the type:
1942 *
1943 * ... AND fieldname != '';
1944 *
1945 * are being used. Final result should be:
1946 *
1947 * ... AND ' . sql_isnotempty('tablename', 'fieldname', true/false, true/false);
1948 *
1949 * (see parameters description below)
1950 *
1951 * @param string $tablename name of the table (without prefix). Not used for now but can be
1952 * necessary in the future if we want to use some introspection using
1953 * meta information against the DB. /// TODO ///
1954 * @param string $fieldname name of the field we are going to check
1955 * @param boolean $nullablefield to specify if the field us nullable (true) or no (false) in the DB
1956 * @param boolean $textfield to specify if it is a text (also called clob) field (true) or a varchar one (false)
1957 * @return string the sql code to be added to check for non empty values
1958 */
1959 public function sql_isnotempty($tablename, $fieldname, $nullablefield, $textfield) {
1960 return ' ( NOT ' . $this->sql_isempty($tablename, $fieldname, $nullablefield, $textfield) . ') ';
1961 }
1962
06c1a1da 1963 /**
1964 * Does this driver suppoer regex syntax when searching
3d54726f 1965 * @return bool
06c1a1da 1966 */
1967 public function sql_regex_supported() {
1968 return false;
1969 }
1970
1971 /**
1972 * Return regex positive or negative match sql
1973 * @param bool $positivematch
1974 * @return string or empty if not supported
1975 */
1976 public function sql_regex($positivematch=true) {
1977 return '';
1978 }
1979
f33e1ed4 1980/// transactions
d5a8d9aa
PS
1981
1982 /**
1983 * Are transactions supported?
1984 * It is not responsible to run productions servers
1985 * on databases without transaction support ;-)
1986 *
1987 * Override in driver if needed.
1988 *
1989 * @return bool
1990 */
1991 protected function transactions_supported() {
1992 // protected for now, this might be changed to public if really necessary
1993 return true;
1994 }
1995
54d51f60 1996 /**
1997 * Returns true if transaction in progress
1998 * @return bool
1999 */
d5a8d9aa
PS
2000 public function is_transaction_started() {
2001 return !empty($this->transactions);
2002 }
2003
2004 /**
2005 * Throws exception if transaction in progress.
2006 * This test does not force rollback of active transactions.
2007 * @return void
2008 */
2009 public function transactions_forbidden() {
2010 if ($this->is_transaction_started()) {
2011 throw new dml_transaction_exception('This code can not be excecuted in transaction');
2012 }
54d51f60 2013 }
2014
f33e1ed4 2015 /**
d5a8d9aa 2016 * On DBs that support it, switch to transaction mode and begin a transaction
b3f1efb5
SM
2017 * you'll need to ensure you call allow_commit() on the returned object
2018 * or your changes *will* be lost.
f33e1ed4 2019 *
2020 * this is _very_ useful for massive updates
a1dda107 2021 *
d5a8d9aa
PS
2022 * Delegated database transactions can be nested, but only one actual database
2023 * transaction is used for the outer-most delegated transaction. This method
2024 * returns a transaction object which you should keep until the end of the
2025 * delegated transaction. The actual database transaction will
2026 * only be committed if all the nested delegated transactions commit
2027 * successfully. If any part of the transaction rolls back then the whole
2028 * thing is rolled back.
2029 *
2030 * @return moodle_transaction
2031 */
2032 public function start_delegated_transaction() {
2033 $transaction = new moodle_transaction($this);
2034 $this->transactions[] = $transaction;
2035 if (count($this->transactions) == 1) {
2036 $this->begin_transaction();
2037 }
2038 return $transaction;
2039 }
2040
2041 /**
2042 * Driver specific start of real database transaction,
2043 * this can not be used directly in code.
2044 * @return void
f33e1ed4 2045 */
d5a8d9aa
PS
2046 protected abstract function begin_transaction();
2047
2048 /**
2049 * Indicates delegated transaction finished successfully.
2050 * The real database transaction is committed only if
2051 * all delegated transactions committed.
2052 * @return void
2053 */
2054 public function commit_delegated_transaction(moodle_transaction $transaction) {
2055 if ($transaction->is_disposed()) {
2056 throw new dml_transaction_exception('Transactions already disposed', $transaction);
a1dda107 2057 }
d5a8d9aa
PS
2058 // mark as disposed so that it can not be used again
2059 $transaction->dispose();
2060
2061 if (empty($this->transactions)) {
2062 throw new dml_transaction_exception('Transaction not started', $transaction);
2063 }
2064
2065 if ($this->force_rollback) {
2066 throw new dml_transaction_exception('Tried to commit transaction after lower level rollback', $transaction);
2067 }
2068
2069 if ($transaction !== $this->transactions[count($this->transactions) - 1]) {
2070 // one incorrect commit at any level rollbacks everything
2071 $this->force_rollback = true;
2072 throw new dml_transaction_exception('Invalid transaction commit attempt', $transaction);
2073 }
2074
2075 if (count($this->transactions) == 1) {
2076 // only commit the top most level
2077 $this->commit_transaction();
2078 }
2079 array_pop($this->transactions);
f33e1ed4 2080 }
2081
2082 /**
d5a8d9aa
PS
2083 * Driver specific commit of real database transaction,
2084 * this can not be used directly in code.
2085 * @return void
f33e1ed4 2086 */
d5a8d9aa
PS
2087 protected abstract function commit_transaction();
2088
2089 /**
2090 * Call when delegated transaction failed, this rolls back
2091 * all delegated transactions up to the top most level.
2092 *
2093 * In many cases you do not need to call this method manually,
2094 * because all open delegated transactions are rolled back
862d54c3 2095 * automatically if exceptions not caught.
d5a8d9aa
PS
2096 *
2097 * @param moodle_transaction $transaction
2098 * @param Exception $e exception that caused the problem
3d54726f 2099 * @return void - does not return, exception is rethrown
d5a8d9aa
PS
2100 */
2101 public function rollback_delegated_transaction(moodle_transaction $transaction, Exception $e) {
2102 if ($transaction->is_disposed()) {
2103 throw new dml_transaction_exception('Transactions already disposed', $transaction);
a1dda107 2104 }
d5a8d9aa
PS
2105 // mark as disposed so that it can not be used again
2106 $transaction->dispose();
2107
2108 // one rollback at any level rollbacks everything
2109 $this->force_rollback = true;
2110
2111 if (empty($this->transactions) or $transaction !== $this->transactions[count($this->transactions) - 1]) {
2112 // this may or may not be a coding problem, better just rethrow the exception,
2113 // because we do not want to loose the original $e
2114 throw $e;
2115 }
2116
2117 if (count($this->transactions) == 1) {
2118 // only rollback the top most level
2119 $this->rollback_transaction();
2120 }
2121 array_pop($this->transactions);
2122 if (empty($this->transactions)) {
2123 // finally top most level rolled back
2124 $this->force_rollback = false;
2125 }
2126 throw $e;
f33e1ed4 2127 }
2128
2129 /**
862d54c3 2130 * Driver specific abort of real database transaction,
d5a8d9aa
PS
2131 * this can not be used directly in code.
2132 * @return void
f33e1ed4 2133 */
d5a8d9aa
PS
2134 protected abstract function rollback_transaction();
2135
2136 /**
862d54c3 2137 * Force rollback of all delegated transaction.
d5a8d9aa
PS
2138 * Does not trow any exceptions and does not log anything.
2139 *
2140 * This method should be used only from default exception handlers and other
2141 * core code.
2142 *
2143 * @return void
2144 */
2145 public function force_transaction_rollback() {
2146 if ($this->transactions) {
2147 try {
2148 $this->rollback_transaction();
2149 } catch (dml_exception $e) {
2150 // ignore any sql errors here, the connection might be broken
2151 }
a1dda107 2152 }
d5a8d9aa
PS
2153
2154 // now enable transactions again
2155 $this->transactions = array(); // unfortunately all unfinished exceptions are kept in memory
2156 $this->force_rollback = false;
f33e1ed4 2157 }
ab130a0b 2158
7f79aaea 2159/// session locking
5da75074 2160 /**
2161 * Is session lock supported in this driver?
2162 * @return bool
2163 */
5e9dd017 2164 public function session_lock_supported() {
2165 return false;
2166 }
2167
5da75074 2168 /**
2169 * Obtain session lock
2170 * @param int $rowid id of the row with session record
3d54726f 2171 * @return bool success
5da75074 2172 */
3b1a9849 2173 public function get_session_lock($rowid) {
5e9dd017 2174 $this->used_for_db_sessions = true;
7f79aaea 2175 }
2176
5da75074 2177 /**
2178 * Release session lock
2179 * @param int $rowid id of the row with session record
3d54726f 2180 * @return bool success
5da75074 2181 */
3b1a9849 2182 public function release_session_lock($rowid) {
7f79aaea 2183 }
2184
ec22e380 2185/// performance and logging
edef70c9 2186 /**
2187 * Returns number of reads done by this database
2188 * @return int
2189 */
ab130a0b 2190 public function perf_get_reads() {
2191 return $this->reads;
2192 }
2193
edef70c9 2194 /**
2195 * Returns number of writes done by this database
2196 * @return int
2197 */
ab130a0b 2198 public function perf_get_writes() {
2199 return $this->writes;
2200 }
edef70c9 2201
2202 /**
2203 * Returns number of queries done by this database
2204 * @return int
2205 */
2206 public function perf_get_queries() {
2207 return $this->writes + $this->reads;
2208 }
f33e1ed4 2209}