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