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