MDL-14679 ok, here is the big patch with new dmllib and ddlib API, some code is alrea...
[moodle.git] / lib / dml / moodle_database.php
CommitLineData
f33e1ed4 1<?php //$Id$
2
3/**
4 * Abstract class representing moodle database interface.
5 * @package dmlib
6 */
7abstract class moodle_database {
8
9 /**
10 * supports :name, "?" or both types of parameters?
11 * must be set in implementing class constructor
12 */
13 protected $param_types;
14
15 protected $database_manager;
16
17 // db connection options
18 protected $dbhost;
19 protected $dbuser;
20 protected $dbpass;
21 protected $dbname;
22 protected $dbpersist;
23 protected $prefix;
24
25 // TODO: perf stuff goes here
26 // TODO: do we really need record caching??
27
28 /**
29 * Contructor - sets up database connection, prints error in case of problem.
30 * @param string $dbhost
31 * @param string $dbuser
32 * @param string $dbpass
33 * @param string $dbname
34 * @param string $dbpersist
35 * @param string $prefix table prefix
36 */
37 public function __construct($dbhost, $dbuser, $dbpass, $dbname, $dbpersist, $prefix) {
38 $this->dbhost = $dbhost;
39 $this->dbuser = $dbuser;
40 $this->dbpass = $dbpass;
41 $this->dbname = $dbname;
42 $this->dbpersist = $dbpersist;
43 $this->prefix = $prefix;
44 }
45
46 /**
47 * Returns database family type - describes SQL dialect
48 * @return string db family name (mysql, postgres, mssql, oracle, etc.)
49 */
50 public abstract function get_dbfamily();
51
52 /**
53 * Returns database server info array
54 * @return array
55 */
56 public abstract function get_server_info();
57
58 /**
59 * Returns database table prefix
60 * @return string database table prefix
61 */
62 public function get_prefix() {
63 return $this->prefix;
64 }
65
66 /**
67 * Returns database type
68 * @return string db type mysql, mysqli, postgres7
69 */
70 protected abstract function get_dbtype();
71
72 /**
73 * Returns supported query parameter types
74 * @return bitmask
75 */
76 protected abstract function allowed_param_types();
77
78 /**
79 * Connect to db specified in constructor
80 * Must be called before any other methods.
81 * @return bool success
82 */
83 public abstract function connect();
84
85 /**
86 * Returns last error reported by database engine.
87 */
88 public abstract function get_last_error();
89
90 /**
91 * Report database error somewhere
92 * TODO: do we need some message with hints?
93 * @param string $sql query which caused problems
94 * @param array $params optional query parameters
95 * @param mixed $obj optional library specific object
96 */
97 protected function report_error($sql, array $params=null, $obj=null) {
98 debugging($this->get_last_error() .'<br /><br />'. s($sql).'<br /> ['.var_export($params, true).']');
99 }
100
101 /**
102 * Constructs IN() or = sql fragment
103 * @param mixed $items single or array of values
104 * @param int $type bound param type
105 * @param string named param placeholder start
106 * @return array - $sql and $params
107 */
108 public function get_in_or_equal($items, $type=SQL_PARAMS_QM, $start='param0000') {
109 if ($type == SQL_PARAMS_QM) {
110 if (!is_array($items) or count($items) == 1) {
111 $sql = '= ?';
112 $params = array($items);
113 } else {
114 $sql = 'IN ('.implode(',', array_fill(0, count($items), '?')).')';
115 $params = array_values($items);
116 }
117
118 } else if ($type == SQL_PARAMS_NAMED) {
119 if (!is_array($items) or count($items) == 1) {
120 $sql = '= :'.$start;
121 $params = array($start=>$items);
122 } else {
123 $params = array();
124 $sql = array();
125 foreach ($items as $item) {
126 $params[$start] = $item;
127 $sql .= ':'.$start++;
128 }
129 $sql = 'IN ('.implode(',', $sql).')';
130 }
131
132 } else {
133 error('todo: type not implemented');
134 }
135 return array($sql, $params);
136 }
137
138 /**
139 * Normalizes sql query parameters and verifies parameters.
140 * @param string $sql query or part of it
141 * @param array $params query parameters
142 */
143 public function fix_sql_params($sql, array $params=null) {
144 $params = (array)$params; // mke null array if needed
145 $allowed_types = $this->allowed_param_types();
146
147 // convert table names
148 $sql = preg_replace('/\{([a-z][a-z0-9_]*)\}/', $this->prefix.'$1', $sql);
149
150 $named_count = preg_match_all('/(?!:):[a-z][a-z0-9_]*/', $sql, $named_matches); // :: used in pgsql casts
151 $dolar_count = preg_match_all('/\$[1-9][0-9]*/', $sql, $dolar_matches);
152 $q_count = substr_count($sql, '?');
153
154 $count = 0;
155
156 if ($named_count) {
157 $type = SQL_PARAMS_NAMED;
158 $count = $named_count;
159
160 }
161 if ($dolar_count) {
162 if ($count) {
163 error('ERROR: Mixed types of sql query parameters!!');
164 }
165 $type = SQL_PARAMS_DOLAR;
166 $count = $dolar_count;
167
168 }
169 if ($q_count) {
170 if ($count) {
171 error('ERROR: Mixed types of sql query parameters!!');
172 }
173 $type = SQL_PARAMS_QM;
174 $count = $q_count;
175
176 }
177
178 if (!$count) {
179 // ignore params
180 if ($allowed_types & SQL_PARAMS_NAMED) {
181 return array($sql, array(), SQL_PARAMS_NAMED);
182 } else if ($allowed_types & SQL_PARAMS_QM) {
183 return array($sql, array(), SQL_PARAMS_QM);
184 } else {
185 return array($sql, array(), SQL_PARAMS_DOLAR);
186 }
187 }
188
189 if ($count > count($params)) {
190 error('ERROR: Incorrect number of query parameters!! '.s($sql));
191 }
192
193 if ($type & $allowed_types) { // bitwise AND
194 if ($count == count($params)) {
195 if ($type == SQL_PARAMS_QM) {
196 return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based array required
197 } else {
198 //better do the validation of names bellow
199 }
200 }
201 // needs some fixing or validation - there might be more params than needed
202 $target_type = $type;
203
204 } else {
205 $target_type = $allowed_types;
206 }
207
208 if ($type == SQL_PARAMS_NAMED) {
209 $finalparams = array();
210 foreach ($named_matches[0] as $key) {
211 $key = trim($key, ':');
212 if (!array_key_exists($key, $params)) {
213 error('ERROR: missing param "'.$key.'" in query');
214 }
215 $finalparams[$key] = $params[$key];
216 }
217 if ($count != count($finalparams)) {
218 error('ERROR: duplicate parameter name in query');
219 }
220
221 if ($target_type & SQL_PARAMS_QM) {
222 $sql = preg_replace('/(?!:):[a-z][a-z0-9_]*/', '?', $sql);
223 return array($sql, array_values($finalparams), SQL_PARAMS_QM); // 0-based required
224 } else if ($target_type & SQL_PARAMS_NAMED) {
225 return array($sql, $finalparams, SQL_PARAMS_NAMED);
226 } else { // $type & SQL_PARAMS_DOLAR
227 error('Pg $1, $2 bound syntax not supported yet :-(');
228 }
229
230 } else if ($type == SQL_PARAMS_DOLAR) {
231 error('Pg $1, $2 bound syntax not supported yet :-(');
232
233 } else { // $type == SQL_PARAMS_QM
234 if (count($params) != $count) {
235 $params = array_slice($params, 0, $count);
236 }
237
238 if ($target_type & SQL_PARAMS_QM) {
239 return array($sql, array_values($params), SQL_PARAMS_QM); // 0-based required
240 } else if ($target_type & SQL_PARAMS_NAMED) {
241 $finalparams = array();
242 $pname = 'param00000';
243 $parts = explode('?', $sql);
244 $sql = array_shift($parts);
245 foreach ($parts as $part) {
246 $param = array_shift($params);
247 $pname++;
248 $sql .= ':'.$pname.$part;
249 $finalparams[$pname] = $param;
250 }
251 return array($sql, $finalparams, SQL_PARAMS_NAMED);
252 } else { // $type & SQL_PARAMS_DOLAR
253 error('Pg $1, $2 bound syntax not supported yet :-(');
254 }
255 }
256 }
257
258 /**
259 * Return tables in database with current prefix
260 * @return array of table names
261 */
262 public abstract function get_tables();
263
264 /**
265 * Return table indexes
266 * @return array of arrays
267 */
268 public abstract function get_indexes($table);
269
270 /**
271 * Returns datailed information about columns in table. This information is cached internally.
272 * @param string $table name
273 * @return array array of database_column_info objects indexed with column names
274 */
275 public abstract function get_columns($table);
276
277 /**
278 * Reset internal column details cache
279 * @param string $table - empty means all, or one if name of table given
280 * @return void
281 */
282 public abstract function reset_columns($table=null);
283
284 /**
285 * Returns sql generator used for db manipulation.
286 * Used mostly in upgrade.php scripts.
287 * @return object database_manager instance
288 */
289 public function get_manager() {
290 global $CFG;
291
292 if (!$this->database_manager) {
293 require_once($CFG->libdir.'/ddllib.php');
294 require_once($CFG->libdir.'/ddl/database_manager.php');
295
296 $classname = $this->get_dbfamily().'_sql_generator';
297 require_once("$CFG->libdir/ddl/$classname.php");
298 $generator = new $classname($this);
299
300 $this->database_manager = new database_manager($this, $generator);
301 }
302 return $this->database_manager;
303 }
304
305 /**
306 * Attempt to change db encoding toUTF-8 if poossible
307 * @return bool success
308 */
309 public function change_db_encoding() {
310 return false;
311 }
312
313 /**
314 * Is db in unicode mode?
315 * @return bool
316 */
317 public function setup_is_unicodedb() {
318 return true;
319 }
320
321 /**
322 * Enable/disable very detailed debugging
323 * TODO: do we need levels?
324 * @param bool $state
325 */
326 public abstract function set_debug($state);
327
328 /**
329 * Returns debug status
330 * @return bool $state
331 */
332 public abstract function get_debug();
333
334 /**
335 * Enable/disable detailed sql logging
336 * TODO: do we need levels?
337 * @param bool $state
338 */
339 public abstract function set_logging($state);
340
341 /**
342 * Do NOT use in code, to be used by database_manager only!
343 * @param string $sql query
344 * @return bool success
345 */
346 public abstract function change_database_structure($sql);
347
348 /**
349 * Execute general sql query. Should be used only when no other method suitable.
350 * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead!
351 * @param string $sql query
352 * @param array $params query parameters
353 * @return bool success
354 */
355 public abstract function execute($sql, array $params=null);
356
357 /**
358 * Get a number of records as a moodle_recordset.
359 *
360 * Selects records from the table $table.
361 *
362 * If specified, only records meeting $conditions.
363 *
364 * If specified, the results will be sorted as specified by $sort. This
365 * is added to the SQL as "ORDER BY $sort". Example values of $sort
366 * mightbe "time ASC" or "time DESC".
367 *
368 * If $fields is specified, only those fields are returned.
369 *
370 * Since this method is a little less readable, use of it should be restricted to
371 * code where it's possible there might be large datasets being returned. For known
372 * small datasets use get_records - it leads to simpler code.
373 *
374 * If you only want some of the records, specify $limitfrom and $limitnum.
375 * The query will skip the first $limitfrom records (according to the sort
376 * order) and then return the next $limitnum records. If either of $limitfrom
377 * or $limitnum is specified, both must be present.
378 *
379 * The return value is a moodle_recordset
380 * if the query succeeds. If an error occurrs, false is returned.
381 *
382 * @param string $table the table to query.
383 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
384 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
385 * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
386 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
387 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
388 * @return mixed an moodle_recorset object, or false if an error occured.
389 */
390 public function get_recordset($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
391 list($select, $params) = $this->where_clause($conditions);
392 return $this->get_recordset_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum);
393 }
394
395 /**
396 * Get a number of records a moodle_recordset.
397 *
398 * Only records where $field takes one of the values $values are returned.
399 * $values should be a comma-separated list of values, for example "4,5,6,10"
400 * or "'foo','bar','baz'".
401 *
402 * Other arguments and the return type as for @see function get_recordset.
403 *
404 * @param string $table the table to query.
405 * @param string $field a field to check (optional).
406 * @param array $values array of values the field must have
407 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
408 * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
409 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
410 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
411 * @return mixed an moodle_recorset object, or false if an error occured.
412 */
413 public function get_recordset_list($table, $field, array $values, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
414 $params = array();
415 $select = array();
416 foreach ($values as $value) {
417 if (is_bool($value)) {
418 $value = (int)$value;
419 }
420 if (is_null($value)) {
421 $select[] = "$field IS NULL";
422 } else {
423 $select[] = "$field = ?";
424 $params[] = $value;
425 }
426 }
427 $select = implode(" AND ", $select);
428 return get_recordset_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum);
429 }
430
431 /**
432 * Get a number of records a moodle_recordset.
433 *
434 * If given, $select is used as the SELECT parameter in the SQL query,
435 * otherwise all records from the table are returned.
436 *
437 * Other arguments and the return type as for @see function get_recordset.
438 *
439 * @param string $table the table to query.
440 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
441 * @param array $params array of sql parameters
442 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
443 * @param string $fields a comma separated list of fields to return (optional, by default all fields are returned).
444 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
445 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
446 * @return mixed an moodle_recorset object, or false if an error occured.
447 */
448 public function get_recordset_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
449 if ($select) {
450 $select = "WHERE $select";
451 }
452 if ($sort) {
453 $sort = " ORDER BY $sort";
454 }
455 return $this->get_recordset_sql("SELECT * FROM {$this->prefix}$table $select $sort", $params, $limitfrom, $limitnum);
456 }
457
458 /**
459 * Get a number of records as an moodle_recordset. $sql must be a complete SQL query.
460 * Since this method is a little less readable, use of it should be restricted to
461 * code where it's possible there might be large datasets being returned. For known
462 * small datasets use get_records_sql - it leads to simpler code.
463 *
464 * The return type is as for @see function get_recordset.
465 *
466 * @param string $sql the SQL select query to execute.
467 * @param array $params array of sql parameters
468 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
469 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
470 * @return mixed an moodle_recorset object, or false if an error occured.
471 */
472 public abstract function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0);
473
474 /**
475 * Get a number of records as an array of objects.
476 *
477 * If the query succeeds and returns at least one record, the
478 * return value is an array of objects, one object for each
479 * record found. The array key is the value from the first
480 * column of the result set. The object associated with that key
481 * has a member variable for each column of the results.
482 *
483 * @param string $table the table to query.
484 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
485 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
486 * @param string $fields a comma separated list of fields to return (optional, by default
487 * all fields are returned). The first field will be used as key for the
488 * array so must be a unique field such as 'id'.
489 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
490 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
491 * @return mixed an array of objects, or empty array if no records were found, or false if an error occured.
492 */
493 public function get_records($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
494 list($select, $params) = $this->where_clause($conditions);
495 return $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum);
496 }
497
498 /**
499 * Get a number of records as an array of objects.
500 *
501 * Return value as for @see function get_records.
502 *
503 * @param string $table The database table to be checked against.
504 * @param string $field The field to search
505 * @param string $values array of values
506 * @param string $sort Sort order (as valid SQL sort parameter)
507 * @param string $fields A comma separated list of fields to be returned from the chosen table. If specified,
508 * the first field should be a unique one such as 'id' since it will be used as a key in the associative
509 * array.
510 * @return mixed an array of objects, or empty array if no records were found, or false if an error occured.
511 */
512 public function get_records_list($table, $field, array $values=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
513 $params = array();
514 $select = array();
515 $values = (array)$values;
516 foreach ($values as $value) {
517 if (is_bool($value)) {
518 $value = (int)$value;
519 }
520 if (is_null($value)) {
521 $select[] = "$field IS NULL";
522 } else {
523 $select[] = "$field = ?";
524 $params[] = $value;
525 }
526 }
527 $select = implode(" AND ", $select);
528 return $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum);
529 }
530
531 /**
532 * Get a number of records as an array of objects.
533 *
534 * Return value as for @see function get_records.
535 *
536 * @param string $table the table to query.
537 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
538 * @param array $params array of sql parameters
539 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
540 * @param string $fields a comma separated list of fields to return
541 * (optional, by default all fields are returned). The first field will be used as key for the
542 * array so must be a unique field such as 'id'.
543 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
544 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
545 * @return mixed an array of objects, or empty array if no records were found, or false if an error occured.
546 */
547 public function get_records_select($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
548 if ($select) {
549 $select = "WHERE $select";
550 }
551 if ($sort) {
552 $sort = " ORDER BY $sort";
553 }
554 return $this->get_records_sql("SELECT $fields FROM {$this->prefix}$table $select $sort", $params, $limitfrom, $limitnum);
555 }
556
557 /**
558 * Get a number of records as an array of objects.
559 *
560 * Return value as for @see function get_records.
561 *
562 * @param string $sql the SQL select query to execute. The first column of this SELECT statement
563 * must be a unique value (usually the 'id' field), as it will be used as the key of the
564 * returned array.
565 * @param array $params array of sql parameters
566 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
567 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
568 * @return mixed an array of objects, or empty array if no records were found, or false if an error occured.
569 */
570 public abstract function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0);
571
572 /**
573 * Get the first two columns from a number of records as an associative array.
574 *
575 * Arguments as for @see function get_recordset.
576 *
577 * If no errors occur the return value
578 * is an associative whose keys come from the first field of each record,
579 * and whose values are the corresponding second fields.
580 * False is returned if an error occurs.
581 *
582 * @param string $table the table to query.
583 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
584 * @param string $sort an order to sort the results in (optional, a valid SQL ORDER BY parameter).
585 * @param string $fields a comma separated list of fields to return - the number of fields should be 2!
586 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
587 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
588 * @return mixed an associative array, or false if an error occured.
589 */
590 public function get_records_menu($table, array $conditions=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
591 $menu = array();
592 if ($records = $this->get_records($table, $conditions, $sort, $fields, $limitfrom, $limitnum)) {
593 foreach ($records as $record) {
594 $record = (array)$record;
595 $key = array_shift($record);
596 $value = array_shift($record);
597 $menu[$key] = $value;
598 }
599 }
600 return $menu;
601 }
602
603 /**
604 * Get the first two columns from a number of records as an associative array.
605 *
606 * Arguments as for @see function get_recordset_select.
607 * Return value as for @see function get_records_menu.
608 *
609 * @param string $table The database table to be checked against.
610 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
611 * @param array $params array of sql parameters
612 * @param string $sort Sort order (optional) - a valid SQL order parameter
613 * @param string $fields A comma separated list of fields to be returned from the chosen table - the number of fields should be 2!
614 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
615 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
616 * @return mixed an associative array, or false if an error occured.
617 */
618 public function get_records_select_menu($table, $select, array $params=null, $sort='', $fields='*', $limitfrom=0, $limitnum=0) {
619 $menu = array();
620 if ($records = $this->get_records_select($table, $select, $params, $sort, $fields, $limitfrom, $limitnum)) {
621 foreach ($records as $record) {
622 $key = array_unshift($record);
623 $value = array_unshift($record);
624 $menu[$key] = $value;
625 }
626 }
627 return $menu;
628 }
629
630 /**
631 * Get the first two columns from a number of records as an associative array.
632 *
633 * Arguments as for @see function get_recordset_sql.
634 * Return value as for @see function get_records_menu.
635 *
636 * @param string $sql The SQL string you wish to be executed.
637 * @param array $params array of sql parameters
638 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
639 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
640 * @return mixed an associative array, or false if an error occured.
641 */
642 public function get_records_sql_menu($sql, array $params=null, $limitfrom=0, $limitnum=0) {
643 $menu = array();
644 if ($records = $this->get_records_sql($sql, $params, $limitfrom, $limitnum)) {
645 foreach ($records as $record) {
646 $key = array_unshift($record);
647 $value = array_unshift($record);
648 $menu[$key] = $value;
649 }
650 }
651 return $menu;
652 }
653
654 /**
655 * Get a single database record as an object
656 *
657 * @param string $table The table to select from.
658 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
659 * @param string $fields A comma separated list of fields to be returned from the chosen table.
660 * @param bool $ignoremultiple ignore multiple records if found
661 * @return mixed a fieldset object containing the first mathcing record, or false if none found.
662 */
663 public function get_record($table, array $conditions, $fields='*', $ignoremultiple=false) {
664 list($select, $params) = $this->where_clause($conditions);
665 return $this->get_record_select($table, $select, $params, $fields, $ignoremultiple);
666 }
667
668 /**
669 * Get a single database record as an object
670 *
671 * @param string $table The database table to be checked against.
672 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
673 * @param array $params array of sql parameters
674 * @param string $fields A comma separated list of fields to be returned from the chosen table.
675 * @param bool $ignoremultiple ignore multiple records if found
676 * @return mixed a fieldset object containing the first mathcing record, or false if none found.
677 */
678 public function get_record_select($table, $select, array $params=null, $fields='*', $ignoremultiple=false) {
679 if ($select) {
680 $select = "WHERE $select";
681 }
682 return $this->get_record_sql("SELECT $fields FROM {$this->prefix}$table $select", $params, $ignoremultiple);
683 }
684
685 /**
686 * Get a single record as an object using an SQL statement
687 *
688 * The SQL statement should normally only return one record. In debug mode
689 * you will get a warning if more records are found. In non-debug mode,
690 * it just returns the first record.
691 *
692 * Use get_records_sql() if more matches possible!
693 *
694 * @param string $sql The SQL string you wish to be executed, should normally only return one record.
695 * @param array $params array of sql parameters
696 * @param bool $ignoremultiple ignore multiple records if found
697 * @return mixed a fieldset object containing the first mathcing record, or false if none found.
698 */
699 public function get_record_sql($sql, array $params=null, $ignoremultiple=false) {
700 $count = $ignoremultiple ? 1 : 2;
701 if (!$mrs = $this->get_recordset_sql($sql, $params, 0, $count)) {
702 return false;
703 }
704 if (!$mrs->valid()) {
705 $mrs->close();
706 return false;
707 }
708
709 $return = (object)$mrs->current();
710
711 $mrs->next();
712 if (!$ignoremultiple and $mrs->valid()) {
713 debugging('Error: mdb->get_record() found more than one record!');
714 }
715
716 $mrs->close();
717 return $return;
718 }
719
720 /**
721 * Get a single value from a table row where all the given fields match the given values.
722 *
723 * @param string $table the table to query.
724 * @param string $return the field to return the value of.
725 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
726 * @return mixed the specified value, or false if an error occured.
727 */
728 public function get_field($table, $return, array $conditions) {
729 list($select, $params) = $this->where_clause($conditions);
730 return $this->get_field_select($table, $return, $select, $params);
731 }
732
733 /**
734 * Get a single value from a table row.
735 *
736 * @param string $table the table to query.
737 * @param string $return the field to return the value of.
738 * @param string $select A fragment of SQL to be used in a where clause returning one row with one column
739 * @param array $params array of sql parameters
740 * @return mixed the specified value, or false if an error occured.
741 */
742 public function get_field_select($table, $return, $select, array $params=null) {
743 if ($select) {
744 $select = "WHERE $select";
745 }
746 return $this->get_field_sql("SELECT $return FROM {$this->prefix}$table $select", $params);
747 }
748
749 /**
750 * Get a single value from a table.
751 *
752 * @param string $table the table to query.
753 * @param string $return the field to return the value of.
754 * @param string $sql The SQL query returning one row with one column
755 * @param array $params array of sql parameters
756 * @return mixed the specified value, or false if an error occured.
757 */
758 public function get_field_sql($sql, array $params=null) {
759 if ($mrs = $this->get_recordset_sql($sql, $params, 0, 1)) {
760 if ($mrs->valid()) {
761 $record = $mrs->current();
762 return reset($record); // first column
763 }
764 $mrs->close();
765 }
766 return false;
767 }
768
769 /**
770 * Selects rows and return values of chosen field as array.
771 *
772 * @param string $table the table to query.
773 * @param string $return the field we are intered in
774 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
775 * @param array $params array of sql parameters
776 * @return mixed array of values or false if an error occured
777 */
778 public function get_fieldset_select($table, $return, $select, array $params=null) {
779 if ($select) {
780 $select = "WHERE $select";
781 }
782 return $this->get_fieldset_sql("SELECT $return FROM {$this->prefix}$table $select", $params);
783 }
784
785 /**
786 * Selects rows and return values of first column as array.
787 *
788 * @param string $sql The SQL query
789 * @param array $params array of sql parameters
790 * @return mixed array of values or false if an error occured
791 */
792 public abstract function get_fieldset_sql($sql, array $params=null);
793
794 /**
795 * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
796 * @param string $table name
797 * @param mixed $params data record as object or array
798 * @param bool $returnit return it of inserted record
799 * @param bool $bulk true means repeated inserts expected
800 * @return mixed success or new id
801 */
802 public abstract function insert_record_raw($table, $params, $returnid=true, $bulk=false);
803
804 /**
805 * Insert a record into a table and return the "id" field if required,
806 * Some conversions and safety checks are carried out. Lobs are supported.
807 * If the return ID isn't required, then this just reports success as true/false.
808 * $data is an object containing needed data
809 * @param string $table The database table to be inserted into
810 * @param object $data A data object with values for one or more fields in the record
811 * @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.
812 * @return mixed success or new ID
813 */
814 public abstract function insert_record($table, $dataobject, $returnid=true, $bulk=false);
815
816 /**
817 * Update record in database, as fast as possible, no safety checks, lobs not supported.
818 * @param string $table name
819 * @param mixed $params data record as object or array
820 * @param bool true means repeated updates expected
821 * @return bool success
822 */
823 public abstract function update_record_raw($table, $params, $bulk=false);
824
825 /**
826 * Update a record in a table
827 *
828 * $dataobject is an object containing needed data
829 * Relies on $dataobject having a variable "id" to
830 * specify the record to update
831 *
832 * @param string $table The database table to be checked against.
833 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
834 * @param bool true means repeated updates expected
835 * @return bool success
836 */
837 public abstract function update_record($table, $dataobject, $bulk=false);
838
839
840 /**
841 * Set a single field in every table row where all the given conditions met.
842 *
843 * @param string $table The database table to be checked against.
844 * @param string $newfield the field to set.
845 * @param string $newvalue the value to set the field to.
846 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
847 * @return bool success
848 */
849 public function set_field($table, $newfield, $newvalue, array $conditions=null) {
850 list($select, $params) = $this->where_clause($conditions);
851 return $this->set_field_select($table, $newfield, $newvalue, $select, $params);
852 }
853
854 /**
855 * Set a single field in every table row where the select statement evaluates to true.
856 *
857 * @param string $table The database table to be checked against.
858 * @param string $newfield the field to set.
859 * @param string $newvalue the value to set the field to.
860 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
861 * @param array $params array of sql parameters
862 * @return bool success
863 */
864 public abstract function set_field_select($table, $newfield, $newvalue, $select, array $params=null);
865
866
867 /**
868 * Count the records in a table where all the given conditions met.
869 *
870 * @param string $table The table to query.
871 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
872 * @return int The count of records returned from the specified criteria.
873 */
874 public function count_records($table, array $conditions=null, array $params=null) {
875 list($select, $params) = $this->where_clause($conditions);
876 return $this->count_records_select($table, $select, $params);
877 }
878
879 /**
880 * Count the records in a table which match a particular WHERE clause.
881 *
882 * @param string $table The database table to be checked against.
883 * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call.
884 * @param array $params array of sql parameters
885 * @param string $countitem The count string to be used in the SQL call. Default is COUNT('x').
886 * @return int The count of records returned from the specified criteria.
887 */
888 public function count_records_select($table, $select, array $params=null, $countitem="COUNT('x')") {
889 if ($select) {
890 $select = "WHERE $select";
891 }
892 return $this->count_records_sql("SELECT $countitem FROM {$this->prefix}$table $select", $params);
893 }
894
895 /**
896 * Get the result of a SQL SELECT COUNT(...) query.
897 *
898 * Given a query that counts rows, return that count. (In fact,
899 * given any query, return the first field of the first record
900 * returned. However, this method should only be used for the
901 * intended purpose.) If an error occurrs, 0 is returned.
902 *
903 * @param string $sql The SQL string you wish to be executed.
904 * @param array $params array of sql parameters
905 * @return int the count. If an error occurrs, 0 is returned.
906 */
907 public function count_records_sql($sql, array $params=null) {
908 if ($count = $this->get_field_sql($sql, $params)) {
909 return $count;
910 } else {
911 return 0;
912 }
913 }
914
915 /**
916 * Test whether a record exists in a table where all the given conditions met.
917 *
918 * The record to test is specified by giving up to three fields that must
919 * equal the corresponding values.
920 *
921 * @param string $table The table to check.
922 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
923 * @return bool true if a matching record exists, else false.
924 */
925 public function record_exists($table, array $conditions) {
926 list($select, $params) = $this->where_clause($conditions);
927 return $this->record_exists_select($table, $select, $params);
928 }
929
930 /**
931 * Test whether any records exists in a table which match a particular WHERE clause.
932 *
933 * @param string $table The database table to be checked against.
934 * @param string $select A fragment of SQL to be used in a WHERE clause in the SQL call.
935 * @param array $params array of sql parameters
936 * @return bool true if a matching record exists, else false.
937 */
938 public function record_exists_select($table, $select, array $params=null) {
939 if ($select) {
940 $select = "WHERE $select";
941 }
942 return $this->record_exists_sql("SELECT 'x' FROM {$this->prefix}$table $select", $params);
943 }
944
945 /**
946 * Test whether a SQL SELECT statement returns any records.
947 *
948 * This function returns true if the SQL statement executes
949 * without any errors and returns at least one record.
950 *
951 * @param string $sql The SQL statement to execute.
952 * @param array $params array of sql parameters
953 * @return bool true if the SQL executes without errors and returns at least one record.
954 */
955 public function record_exists_sql($sql, array $params=null) {
956 if ($mrs = $this->get_recordset_sql($sql, $params, 0, 1)) {
957 $return = $mrs->valid();
958 $mrs->close();
959 return $return;
960 }
961 return false;
962 }
963
964 /**
965 * Delete the records from a table where all the given conditions met.
966 *
967 * @param string $table the table to delete from.
968 * @param array $conditions optional array $fieldname=>requestedvalue with AND in between
969 * @return returns success.
970 */
971 public function delete_records($table, array $conditions) {
972 list($select, $params) = $this->where_clause($conditions);
973 return $this->delete_records_select($table, $select, $params);
974 }
975
976 /**
977 * Delete one or more records from a table
978 *
979 * @param string $table The database table to be checked against.
980 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
981 * @param array $params array of sql parameters
982 * @return returns success.
983 */
984 public abstract function delete_records_select($table, $select, array $params=null);
985
986
987
988/// sql contructs
989 /**
990 * Returns the SQL text to be used in order to perform one bitwise AND operation
991 * between 2 integers.
992 * @param integer int1 first integer in the operation
993 * @param integer int2 second integer in the operation
994 * @return string the piece of SQL code to be used in your statement.
995 */
996 public function sql_bitand($int1, $int2) {
997 return '((' . $int1 . ') & (' . $int2 . '))';
998 }
999
1000 /**
1001 * Returns the SQL text to be used in order to perform one bitwise NOT operation
1002 * with 1 integer.
1003 * @param integer int1 integer in the operation
1004 * @return string the piece of SQL code to be used in your statement.
1005 */
1006 public function sql_bitnot($int1) {
1007 return '(~(' . $int1 . '))';
1008 }
1009
1010 /**
1011 * Returns the SQL text to be used in order to perform one bitwise OR operation
1012 * between 2 integers.
1013 * @param integer int1 first integer in the operation
1014 * @param integer int2 second integer in the operation
1015 * @return string the piece of SQL code to be used in your statement.
1016 */
1017 public function sql_bitor($int1, $int2) {
1018 return '((' . $int1 . ') | (' . $int2 . '))';
1019 }
1020
1021 /**
1022 * Returns the SQL text to be used in order to perform one bitwise XOR operation
1023 * between 2 integers.
1024 * @param integer int1 first integer in the operation
1025 * @param integer int2 second integer in the operation
1026 * @return string the piece of SQL code to be used in your statement.
1027 */
1028 public function sql_bitxor($int1, $int2) {
1029 return '((' . $int1 . ') ^ (' . $int2 . '))';
1030 }
1031
1032 /**
1033 * Returns the SQL to be used in order to CAST one CHAR column to INTEGER.
1034 *
1035 * Be aware that the CHAR column you're trying to cast contains really
1036 * int values or the RDBMS will throw an error!
1037 *
1038 * @param string fieldname the name of the field to be casted
1039 * @param boolean text to specify if the original column is one TEXT (CLOB) column (true). Defaults to false.
1040 * @return string the piece of SQL code to be used in your statement.
1041 */
1042 public function sql_cast_char2int($fieldname, $text=false) {
1043 return ' ' . $fieldname . ' ';
1044 }
1045
1046 /**
1047 * Returns the SQL text to be used to compare one TEXT (clob) column with
1048 * one varchar column, because some RDBMS doesn't support such direct
1049 * comparisons.
1050 * @param string fieldname the name of the TEXT field we need to order by
1051 * @param string number of chars to use for the ordering (defaults to 32)
1052 * @return string the piece of SQL code to be used in your statement.
1053 */
1054 public function sql_compare_text($fieldname, $numchars=32) {
1055 return $this->sql_order_by_text($fieldname, $numchars);
1056 }
1057
1058 /**
1059 * Returns the proper SQL to do CONCAT between the elements passed
1060 * Can take many parameters - just a passthrough to $db->Concat()
1061 *
1062 * @param string $element
1063 * @return string
1064 */
1065 public abstract function sql_concat();
1066
1067 /**
1068 * Returns the proper SQL to do CONCAT between the elements passed
1069 * with a given separator
1070 *
1071 * @uses $db
1072 * @param string $separator
1073 * @param array $elements
1074 * @return string
1075 */
1076 public abstract function sql_concat_join($separator="' '", $elements=array());
1077
1078 /**
1079 * Returns the proper SQL (for the dbms in use) to concatenate $firstname and $lastname
1080 *
1081 * @param string $firstname User's first name
1082 * @param string $lastname User's last name
1083 * @return string
1084 */
1085 function sql_fullname($first='firstname', $last='lastname') {
1086 return $this->sql_concat($first, "' '", $last);
1087 }
1088
1089 /**
1090 * Returns the proper SQL to do LIKE in a case-insensitive way
1091 *
1092 * Note the LIKE are case sensitive for Oracle. Oracle 10g is required to use
1093 * the caseinsensitive search using regexp_like() or NLS_COMP=LINGUISTIC :-(
1094 * See http://docs.moodle.org/en/XMLDB_Problems#Case-insensitive_searches
1095 *
1096 * @return string
1097 */
1098 public function sql_ilike() {
1099 return 'LIKE';
1100 }
1101
1102 /**
1103 * Returns the SQL text to be used to order by one TEXT (clob) column, because
1104 * some RDBMS doesn't support direct ordering of such fields.
1105 * Note that the use or queries being ordered by TEXT columns must be minimised,
1106 * because it's really slooooooow.
1107 * @param string fieldname the name of the TEXT field we need to order by
1108 * @param string number of chars to use for the ordering (defaults to 32)
1109 * @return string the piece of SQL code to be used in your statement.
1110 */
1111 public function sql_order_by_text($fieldname, $numchars=32) {
1112 return $fieldname;
1113 }
1114
1115 /**
1116 * Returns the proper substr() function for each DB
1117 * Relies on ADOdb $db->substr property
1118 */
1119 public abstract function sql_substr();
1120
1121 public function where_clause(array $conditions=null) {
1122 $allowed_types = $this->allowed_param_types();
1123 if (empty($conditions)) {
1124 return array('', array());
1125 }
1126 $where = array();
1127 $params = array();
1128 foreach ($conditions as $key=>$value) {
1129 if (is_null($value)) {
1130 $where[] = "$key IS NULL";
1131 } else {
1132 if ($allowed_types & SQL_PARAMS_NAMED) {
1133 $where[] = "$key = :$key";
1134 $params[$key] = $value;
1135 } else {
1136 $where[] = "$key = ?";
1137 $params[] = $value;
1138 }
1139 }
1140 }
1141 $where = implode(" AND ", $where);
1142 return array($where, $params);
1143 }
1144
1145 /**
1146 * Returns the empty string char used by every supported DB. To be used when
1147 * we are searching for that values in our queries. Only Oracle uses this
1148 * for now (will be out, once we migrate to proper NULLs if that days arrives)
1149 */
1150 function sql_empty() {
1151 return '';
1152 }
1153
1154 /**
1155 * Returns the proper SQL to know if one field is empty.
1156 *
1157 * Note that the function behavior strongly relies on the
1158 * parameters passed describing the field so, please, be accurate
1159 * when speciffying them.
1160 *
1161 * Also, note that this function is not suitable to look for
1162 * fields having NULL contents at all. It's all for empty values!
1163 *
1164 * This function should be applied in all the places where conditins of
1165 * the type:
1166 *
1167 * ... AND fieldname = '';
1168 *
1169 * are being used. Final result should be:
1170 *
1171 * ... AND ' . sql_isempty('tablename', 'fieldname', true/false, true/false);
1172 *
1173 * (see parameters description below)
1174 *
1175 * @param string $tablename name of the table (without prefix). Not used for now but can be
1176 * necessary in the future if we want to use some introspection using
1177 * meta information against the DB. /// TODO ///
1178 * @param string $fieldname name of the field we are going to check
1179 * @param boolean $nullablefield to specify if the field us nullable (true) or no (false) in the DB
1180 * @param boolean $textfield to specify if it is a text (also called clob) field (true) or a varchar one (false)
1181 * @return string the sql code to be added to check for empty values
1182 */
1183 public function sql_isempty($tablename, $fieldname, $nullablefield, $textfield) {
1184 return " $fieldname = '' ";
1185 }
1186
1187 /**
1188 * Returns the proper SQL to know if one field is not empty.
1189 *
1190 * Note that the function behavior strongly relies on the
1191 * parameters passed describing the field so, please, be accurate
1192 * when speciffying them.
1193 *
1194 * This function should be applied in all the places where conditions of
1195 * the type:
1196 *
1197 * ... AND fieldname != '';
1198 *
1199 * are being used. Final result should be:
1200 *
1201 * ... AND ' . sql_isnotempty('tablename', 'fieldname', true/false, true/false);
1202 *
1203 * (see parameters description below)
1204 *
1205 * @param string $tablename name of the table (without prefix). Not used for now but can be
1206 * necessary in the future if we want to use some introspection using
1207 * meta information against the DB. /// TODO ///
1208 * @param string $fieldname name of the field we are going to check
1209 * @param boolean $nullablefield to specify if the field us nullable (true) or no (false) in the DB
1210 * @param boolean $textfield to specify if it is a text (also called clob) field (true) or a varchar one (false)
1211 * @return string the sql code to be added to check for non empty values
1212 */
1213 public function sql_isnotempty($tablename, $fieldname, $nullablefield, $textfield) {
1214 return ' ( NOT ' . $this->sql_isempty($tablename, $fieldname, $nullablefield, $textfield) . ') ';
1215 }
1216
1217/// transactions
1218 /**
1219 * on DBs that support it, switch to transaction mode and begin a transaction
1220 * you'll need to ensure you call commit_sql() or your changes *will* be lost.
1221 *
1222 * this is _very_ useful for massive updates
1223 */
1224 public function begin_sql() {
1225 return true;
1226 }
1227
1228 /**
1229 * on DBs that support it, commit the transaction
1230 */
1231 public function commit_sql() {
1232 return true;
1233 }
1234
1235 /**
1236 * on DBs that support it, rollback the transaction
1237 */
1238 public function rollback_sql() {
1239 return true;
1240 }
1241}