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