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