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