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