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