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