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