yet more comment typos
[moodle.git] / lib / dml / pdo_moodle_database.php
CommitLineData
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 * Experimental pdo database class
21 *
22 * @package moodlecore
23 * @subpackage DML
24 * @copyright 2008 Andrei Bautu
25 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
26 */
f33e1ed4 27
28require_once($CFG->libdir.'/dml/moodle_database.php');
29require_once($CFG->libdir.'/dml/pdo_moodle_recordset.php');
30
31/**
32 * Experimental pdo database class
f33e1ed4 33 */
34abstract class pdo_moodle_database extends moodle_database {
35
36 protected $pdb;
55946ffd 37 protected $lastError = null;
f33e1ed4 38
55946ffd 39 /**
2434f624
PS
40 * Constructor - instantiates the database, specifying if it's external (connect to other systems) or no (Moodle DB)
41 * note this has effect to decide if prefix checks must be performed or no
55946ffd 42 * @param bool true means external database used
43 */
44 public function __construct($external=false) {
45 parent::__construct($external);
f33e1ed4 46 }
47
55946ffd 48 /**
49 * Connect to db
50 * Must be called before other methods.
51 * @param string $dbhost
52 * @param string $dbuser
53 * @param string $dbpass
54 * @param string $dbname
55946ffd 55 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
56 * @param array $dboptions driver specific options
57 * @return bool success
58 */
beaa43db 59 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {
fa648098 60 $driverstatus = $this->driver_installed();
61
62 if ($driverstatus !== true) {
63 throw new dml_exception('dbdriverproblem', $driverstatus);
64 }
65
beaa43db 66 $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
55946ffd 67
fa648098 68 try{
55946ffd 69 $this->pdb = new PDO($this->get_dsn(), $this->dbuser, $this->dbpass, $this->get_pdooptions());
70 // generic PDO settings to match adodb's default; subclasses can change this in configure_dbconnection
71 $this->pdb->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
72 $this->pdb->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
f33e1ed4 73 $this->configure_dbconnection();
74 return true;
75 } catch (PDOException $ex) {
fa648098 76 throw new dml_connection_exception($ex->getMessage());
f33e1ed4 77 return false;
78 }
79 }
80
55946ffd 81 /**
82 * Returns the driver-dependent DSN for PDO based on members stored by connect.
83 * Must be called after connect (or after $dbname, $dbhost, etc. members have been set).
84 * @return string driver-dependent DSN
85 */
e9c734ba 86 abstract protected function get_dsn();
94898738 87
55946ffd 88 /**
89 * Returns the driver-dependent connection attributes for PDO based on members stored by connect.
90 * Must be called after $dbname, $dbhost, etc. members have been set.
91 * @return array A key=>value array of PDO driver-specific connection options
92 */
93 protected function get_pdooptions() {
beaa43db 94 return array(PDO::ATTR_PERSISTENT => !empty($this->dboptions['dbpersist']));
55946ffd 95 }
94898738 96
f33e1ed4 97 protected function configure_dbconnection() {
55946ffd 98 ///TODO: not needed preconfigure_dbconnection() stuff for PDO drivers?
f33e1ed4 99 }
100
16a5642c 101 /**
102 * Returns general database library name
103 * Note: can be used before connect()
eb123deb 104 * @return string db type pdo, native
16a5642c 105 */
106 protected function get_dblibrary() {
107 return 'pdo';
108 }
109
55946ffd 110 /**
111 * Returns localised database type name
112 * Note: can be used before connect()
113 * @return string
114 */
115 public function get_name() {
3b093310 116 return get_string('pdo'.$this->get_dbtype(), 'install');
117 }
118
119 /**
120 * Returns localised database configuration help.
121 * Note: can be used before connect()
122 * @return string
123 */
124 public function get_configuration_help() {
125 return get_string('pdo'.$this->get_dbtype().'help', 'install');
55946ffd 126 }
f33e1ed4 127
55946ffd 128 /**
129 * Returns localised database description
130 * Note: can be used before connect()
131 * @return string
132 */
133 public function get_configuration_hints() {
134 return get_string('databasesettingssub_' . $this->get_dbtype() . '_pdo', 'install');
135 }
f33e1ed4 136
55946ffd 137 /**
138 * Returns database server info array
139 * @return array
140 */
141 public function get_server_info() {
142 $result = array();
143 try {
144 $result['description'] = $this->pdb->getAttribute(PDO::ATTR_SERVER_INFO);
145 } catch(PDOException $ex) {}
146 try {
147 $result['version'] = $this->pdb->getAttribute(PDO::ATTR_SERVER_VERSION);
148 } catch(PDOException $ex) {}
149 return $result;
150 }
94898738 151
55946ffd 152 /**
153 * Returns supported query parameter types
154 * @return bitmask
155 */
156 protected function allowed_param_types() {
157 return SQL_PARAMS_QM | SQL_PARAMS_NAMED;
158 }
94898738 159
55946ffd 160 /**
161 * Returns last error reported by database engine.
162 */
163 public function get_last_error() {
164 return $this->lastError;
165 }
166
55946ffd 167 /**
2434f624 168 * Function to print/save/ignore debugging messages related to SQL queries.
55946ffd 169 */
170 protected function debug_query($sql, $params = null) {
171 echo '<hr /> (', $this->get_dbtype(), '): ', htmlentities($sql);
172 if($params) {
173 echo ' (parameters ';
174 print_r($params);
175 echo ')';
f33e1ed4 176 }
55946ffd 177 echo '<hr />';
178 }
94898738 179
55946ffd 180 /**
181 * Do NOT use in code, to be used by database_manager only!
182 * @param string $sql query
183 * @return bool success
184 */
185 public function change_database_structure($sql) {
e9c734ba 186 $result = true;
187 $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
188
8e378ec0 189 try {
8e378ec0 190 $this->pdb->exec($sql);
117679db 191 $this->reset_caches();
8e378ec0 192 } catch (PDOException $ex) {
193 $this->lastError = $ex->getMessage();
e9c734ba 194 $result = false;
8e378ec0 195 }
e9c734ba 196 $this->query_end($result);
197 return $result;
f33e1ed4 198 }
199
200 public function delete_records_select($table, $select, array $params=null) {
55946ffd 201 $sql = "DELETE FROM {{$table}}";
202 if ($select) {
203 $sql .= " WHERE $select";
204 }
55946ffd 205 return $this->execute($sql, $params);
206 }
207
208 /**
94898738 209 * Factory method that creates a recordset for return by a query. The generic pdo_moodle_recordset
2434f624 210 * class should fit most cases, but pdo_moodle_database subclasses can override this method to return
55946ffd 211 * a subclass of pdo_moodle_recordset.
94898738 212 * @param object $sth instance of PDOStatement
55946ffd 213 * @return object instance of pdo_moodle_recordset
214 */
215 protected function create_recordset($sth) {
216 return new pdo_moodle_recordset($sth);
217 }
218
219 /**
220 * Execute general sql query. Should be used only when no other method suitable.
221 * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead!
222 * @param string $sql query
223 * @param array $params query parameters
224 * @return bool success
225 */
226 public function execute($sql, array $params=null) {
e9c734ba 227 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
228
229 $result = true;
230 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
231
f33e1ed4 232 try {
55946ffd 233 $sth = $this->pdb->prepare($sql);
234 $sth->execute($params);
f33e1ed4 235 } catch (PDOException $ex) {
55946ffd 236 $this->lastError = $ex->getMessage();
e9c734ba 237 $result = false;
f33e1ed4 238 }
e9c734ba 239
240 $this->query_end($result);
241 return $result;
f33e1ed4 242 }
243
55946ffd 244 /**
245 * Get a number of records as an moodle_recordset. $sql must be a complete SQL query.
246 * Since this method is a little less readable, use of it should be restricted to
247 * code where it's possible there might be large datasets being returned. For known
248 * small datasets use get_records_sql - it leads to simpler code.
249 *
250 * The return type is as for @see function get_recordset.
251 *
252 * @param string $sql the SQL select query to execute.
253 * @param array $params array of sql parameters
254 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
255 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
22d77567 256 * @return mixed an moodle_recordset object, or false if an error occured.
55946ffd 257 */
f33e1ed4 258 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
e9c734ba 259
260 $result = true;
261
262 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
263 $sql = $this->get_limit_clauses($sql, $limitfrom, $limitnum);
264 $this->query_start($sql, $params, SQL_QUERY_SELECT);
265
f33e1ed4 266 try {
55946ffd 267 $sth = $this->pdb->prepare($sql);
268 $sth->execute($params);
e9c734ba 269 $result = $this->create_recordset($sth);
f33e1ed4 270 } catch (PDOException $ex) {
55946ffd 271 $this->lastError = $ex->getMessage();
e9c734ba 272 $result = false;
f33e1ed4 273 }
94898738 274
e9c734ba 275 $this->query_end($result);
276 return $result;
55946ffd 277 }
f33e1ed4 278
55946ffd 279 /**
280 * Selects rows and return values of first column as array.
281 *
282 * @param string $sql The SQL query
283 * @param array $params array of sql parameters
2434f624 284 * @return mixed array of values or false if an error occurred
55946ffd 285 */
286 public function get_fieldset_sql($sql, array $params=null) {
287 if(!$rs = $this->get_recordset_sql($sql, $params)) {
288 return false;
289 }
290 $result = array();
291 foreach($rs as $value) {
292 $result[] = reset($value);
293 }
294 $rs->close();
295 return $result;
f33e1ed4 296 }
297
55946ffd 298 /**
299 * Get a number of records as an array of objects.
300 *
301 * Return value as for @see function get_records.
302 *
303 * @param string $sql the SQL select query to execute. The first column of this SELECT statement
304 * must be a unique value (usually the 'id' field), as it will be used as the key of the
305 * returned array.
306 * @param array $params array of sql parameters
307 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
308 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
2434f624 309 * @return mixed an array of objects, or empty array if no records were found, or false if an error occurred.
55946ffd 310 */
f33e1ed4 311 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
55946ffd 312 if(!$rs = $this->get_recordset_sql($sql, $params, $limitfrom, $limitnum)) {
313 return false;
314 }
315 $objects = array();
316 $debugging = debugging('', DEBUG_DEVELOPER);
317 foreach($rs as $value) {
318 $key = reset($value);
319 if ($debugging && array_key_exists($key, $objects)) {
320 debugging("Did you remember to make the first column something unique in your call to get_records? Duplicate value '$key' found in column first column of '$sql'.", DEBUG_DEVELOPER);
321 }
322 $objects[$key] = (object)$value;
323 }
324 $rs->close();
325 return $objects;
326 }
f33e1ed4 327
55946ffd 328 /**
329 * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
330 * @param string $table name
331 * @param mixed $params data record as object or array
332 * @param bool $returnit return it of inserted record
333 * @param bool $bulk true means repeated inserts expected
94898738 334 * @param bool $customsequence true if 'id' included in $params, disables $returnid
22d77567 335 * @return true or new id
55946ffd 336 */
94898738 337 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
55946ffd 338 if (!is_array($params)) {
339 $params = (array)$params;
340 }
94898738 341
342 if ($customsequence) {
343 if (!isset($params['id'])) {
e9c734ba 344 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
94898738 345 }
346 $returnid = false;
347 } else {
348 unset($params['id']);
349 }
55946ffd 350
351 if (empty($params)) {
e9c734ba 352 throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
55946ffd 353 }
354
55946ffd 355 $fields = implode(',', array_keys($params));
356 $qms = array_fill(0, count($params), '?');
357 $qms = implode(',', $qms);
358
359 $sql = "INSERT INTO {{$table}} ($fields) VALUES($qms)";
360 if (!$this->execute($sql, $params)) {
361 return false;
362 }
363 if (!$returnid) {
364 return true;
365 }
366 if ($id = $this->pdb->lastInsertId()) {
367 return (int)$id;
368 }
369 return false;
370 }
94898738 371
55946ffd 372 /**
373 * Insert a record into a table and return the "id" field if required,
374 * Some conversions and safety checks are carried out. Lobs are supported.
375 * If the return ID isn't required, then this just reports success as true/false.
376 * $data is an object containing needed data
377 * @param string $table The database table to be inserted into
378 * @param object $data A data object with values for one or more fields in the record
379 * @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.
380 * @param bool $bulk true means repeated inserts expected
22d77567 381 * @return true or new id
55946ffd 382 */
383 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
384 if (!is_object($dataobject)) {
385 $dataobject = (object)$dataobject;
386 }
387
388 $columns = $this->get_columns($table);
389
390 unset($dataobject->id);
391 $cleaned = array();
392
393 foreach ($dataobject as $field=>$value) {
394 if (!isset($columns[$field])) {
395 continue;
396 }
397 $column = $columns[$field];
398 if (is_bool($value)) {
399 $value = (int)$value; // prevent "false" problems
400 }
401 if (!empty($column->enums)) {
a47c84c4 402 // workaround for problem with wrong enums
403 if (is_null($value) and !$column->not_null) {
404 // ok - nulls allowed
405 } else {
406 if (!in_array((string)$value, $column->enums)) {
407 debugging('Enum value '.s($value).' not allowed in field '.$field.' table '.$table.'.');
408 return false;
409 }
55946ffd 410 }
411 }
412 $cleaned[$field] = $value;
413 }
414
415 if (empty($cleaned)) {
f33e1ed4 416 return false;
417 }
55946ffd 418
419 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
f33e1ed4 420 }
94898738 421
55946ffd 422 /**
423 * Update record in database, as fast as possible, no safety checks, lobs not supported.
424 * @param string $table name
425 * @param mixed $params data record as object or array
426 * @param bool true means repeated updates expected
427 * @return bool success
428 */
429 public function update_record_raw($table, $params, $bulk=false) {
430 if (!is_array($params)) {
431 $params = (array)$params;
432 }
433 if (!isset($params['id'])) {
e9c734ba 434 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
55946ffd 435 }
436 $id = $params['id'];
437 unset($params['id']);
f33e1ed4 438
55946ffd 439 if (empty($params)) {
e9c734ba 440 throw new coding_exception('moodle_database::update_record_raw() no fields found.');
55946ffd 441 }
f33e1ed4 442
55946ffd 443 $sets = array();
444 foreach ($params as $field=>$value) {
445 $sets[] = "$field = ?";
446 }
447
448 $params[] = $id; // last ? in WHERE condition
449
450 $sets = implode(',', $sets);
451 $sql = "UPDATE {{$table}} SET $sets WHERE id=?";
55946ffd 452 return $this->execute($sql, $params);
453 }
454
455 /**
456 * Update a record in a table
457 *
458 * $dataobject is an object containing needed data
459 * Relies on $dataobject having a variable "id" to
460 * specify the record to update
461 *
462 * @param string $table The database table to be checked against.
463 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
464 * @param bool true means repeated updates expected
465 * @return bool success
466 */
467 public function update_record($table, $dataobject, $bulk=false) {
468 if (!is_object($dataobject)) {
469 $dataobject = (object)$dataobject;
470 }
471
472 if (!isset($dataobject->id) ) {
f33e1ed4 473 return false;
474 }
55946ffd 475
476 $columns = $this->get_columns($table);
477 $cleaned = array();
478
479 foreach ($dataobject as $field=>$value) {
480 if (!isset($columns[$field])) {
481 continue;
482 }
483 if (is_bool($value)) {
484 $value = (int)$value; // prevent "false" problems
485 }
486 $cleaned[$field] = $value;
487 }
488
489 return $this->update_record_raw($table, $cleaned, $bulk);
490 }
491
492 /**
493 * Set a single field in every table row where the select statement evaluates to true.
494 *
495 * @param string $table The database table to be checked against.
496 * @param string $newfield the field to set.
497 * @param string $newvalue the value to set the field to.
498 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
499 * @param array $params array of sql parameters
500 * @return bool success
501 */
502 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) {
503 if ($select) {
504 $select = "WHERE $select";
505 }
506 if (is_null($params)) {
507 $params = array();
508 }
509 list($select, $params, $type) = $this->fix_sql_params($select, $params);
510
511 if (is_bool($newvalue)) {
512 $newvalue = (int)$newvalue; // prevent "false" problems
513 }
514 if (is_null($newvalue)) {
515 $newfield = "$newfield = NULL";
516 } else {
517 // make sure SET and WHERE clauses use the same type of parameters,
518 // because we don't support different types in the same query
519 switch($type) {
520 case SQL_PARAMS_NAMED:
521 $newfield = "$newfield = :newvalueforupdate";
522 $params['newvalueforupdate'] = $newvalue;
523 break;
524 case SQL_PARAMS_QM:
525 $newfield = "$newfield = ?";
526 array_unshift($params, $newvalue);
527 break;
528 default:
60e40dda 529 $this->lastError = __FILE__ . ' LINE: ' . __LINE__ . '.';
530 print_error(unknowparamtype, 'error', '', $this->lastError);
55946ffd 531 }
532 }
533 $sql = "UPDATE {{$table}} SET $newfield $select";
55946ffd 534 return $this->execute($sql, $params);
f33e1ed4 535 }
536
f33e1ed4 537 public function sql_concat() {
60e40dda 538 print_error('TODO');
f33e1ed4 539 }
540
541 public function sql_concat_join($separator="' '", $elements=array()) {
60e40dda 542 print_error('TODO');
f33e1ed4 543 }
544
d5a8d9aa 545 protected function begin_transaction() {
e9c734ba 546 $this->query_start('', NULL, SQL_QUERY_AUX);
55946ffd 547 try {
548 $this->pdb->beginTransaction();
55946ffd 549 } catch(PDOException $ex) {
e9c734ba 550 $this->lastError = $ex->getMessage();
55946ffd 551 }
e9c734ba 552 $this->query_end($result);
f33e1ed4 553 }
e9c734ba 554
d5a8d9aa 555 protected function commit_transaction() {
e9c734ba 556 $this->query_start('', NULL, SQL_QUERY_AUX);
e9c734ba 557
55946ffd 558 try {
559 $this->pdb->commit();
55946ffd 560 } catch(PDOException $ex) {
e9c734ba 561 $this->lastError = $ex->getMessage();
55946ffd 562 }
e9c734ba 563 $this->query_end($result);
f33e1ed4 564 }
55946ffd 565
d5a8d9aa 566 protected function rollback_transaction() {
e9c734ba 567 $this->query_start('', NULL, SQL_QUERY_AUX);
e9c734ba 568
55946ffd 569 try {
570 $this->pdb->rollBack();
55946ffd 571 } catch(PDOException $ex) {
e9c734ba 572 $this->lastError = $ex->getMessage();
55946ffd 573 }
e9c734ba 574 $this->query_end($result);
f33e1ed4 575 }
94898738 576
577 /**
578 * Import a record into a table, id field is required.
579 * Basic safety checks only. Lobs are supported.
580 * @param string $table name of database table to be inserted into
581 * @param mixed $dataobject object or array with fields in the record
582 * @return bool success
583 */
584 public function import_record($table, $dataobject) {
585 $dataobject = (object)$dataobject;
586
587 $columns = $this->get_columns($table);
588 $cleaned = array();
589 foreach ($dataobject as $field=>$value) {
590 if (!isset($columns[$field])) {
591 continue;
592 }
593 $cleaned[$field] = $value;
594 }
595
596 return $this->insert_record_raw($table, $cleaned, false, true, true);
597 }
e9c734ba 598
599 /**
600 * Called before each db query.
601 *
2434f624 602 * Overridden to ensure $this->lastErorr is reset each query
e9c734ba 603 *
604 * @param string $sql
605 * @param array array of parameters
606 * @param int $type type of query
607 * @param mixed $extrainfo driver specific extra information
608 * @return void
609 */
610 protected function query_start($sql, array $params=null, $type, $extrainfo=null) {
611 $this->lastError = null;
612 parent::query_start($sql, $params, $type, $extrainfo);
613 }
f33e1ed4 614}