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