3 // This file is part of Moodle - http://moodle.org/
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.
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.
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/>.
20 * Experimental pdo database class
24 * @copyright 2008 Andrei Bautu
25 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
28 defined('MOODLE_INTERNAL') ||
die();
30 require_once($CFG->libdir
.'/dml/moodle_database.php');
31 require_once($CFG->libdir
.'/dml/pdo_moodle_recordset.php');
34 * Experimental pdo database class
36 abstract class pdo_moodle_database
extends moodle_database
{
39 protected $lastError = null;
42 * Constructor - instantiates the database, specifying if it's external (connect to other systems) or no (Moodle DB)
43 * note this has effect to decide if prefix checks must be performed or no
44 * @param bool true means external database used
46 public function __construct($external=false) {
47 parent
::__construct($external);
52 * Must be called before other methods.
53 * @param string $dbhost
54 * @param string $dbuser
55 * @param string $dbpass
56 * @param string $dbname
57 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
58 * @param array $dboptions driver specific options
59 * @return bool success
61 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {
62 $driverstatus = $this->driver_installed();
64 if ($driverstatus !== true) {
65 throw new dml_exception('dbdriverproblem', $driverstatus);
68 $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
71 $this->pdb
= new PDO($this->get_dsn(), $this->dbuser
, $this->dbpass
, $this->get_pdooptions());
72 // generic PDO settings to match adodb's default; subclasses can change this in configure_dbconnection
73 $this->pdb
->setAttribute(PDO
::ATTR_CASE
, PDO
::CASE_LOWER
);
74 $this->pdb
->setAttribute(PDO
::ATTR_ERRMODE
, PDO
::ERRMODE_EXCEPTION
);
75 $this->configure_dbconnection();
77 } catch (PDOException
$ex) {
78 throw new dml_connection_exception($ex->getMessage());
84 * Returns the driver-dependent DSN for PDO based on members stored by connect.
85 * Must be called after connect (or after $dbname, $dbhost, etc. members have been set).
86 * @return string driver-dependent DSN
88 abstract protected function get_dsn();
91 * Returns the driver-dependent connection attributes for PDO based on members stored by connect.
92 * Must be called after $dbname, $dbhost, etc. members have been set.
93 * @return array A key=>value array of PDO driver-specific connection options
95 protected function get_pdooptions() {
96 return array(PDO
::ATTR_PERSISTENT
=> !empty($this->dboptions
['dbpersist']));
99 protected function configure_dbconnection() {
100 ///TODO: not needed preconfigure_dbconnection() stuff for PDO drivers?
104 * Returns general database library name
105 * Note: can be used before connect()
106 * @return string db type pdo, native
108 protected function get_dblibrary() {
113 * Returns localised database type name
114 * Note: can be used before connect()
117 public function get_name() {
118 return get_string('pdo'.$this->get_dbtype(), 'install');
122 * Returns localised database configuration help.
123 * Note: can be used before connect()
126 public function get_configuration_help() {
127 return get_string('pdo'.$this->get_dbtype().'help', 'install');
131 * Returns localised database description
132 * Note: can be used before connect()
135 public function get_configuration_hints() {
136 return get_string('databasesettingssub_' . $this->get_dbtype() . '_pdo', 'install');
140 * Returns database server info array
143 public function get_server_info() {
146 $result['description'] = $this->pdb
->getAttribute(PDO
::ATTR_SERVER_INFO
);
147 } catch(PDOException
$ex) {}
149 $result['version'] = $this->pdb
->getAttribute(PDO
::ATTR_SERVER_VERSION
);
150 } catch(PDOException
$ex) {}
155 * Returns supported query parameter types
156 * @return int bitmask
158 protected function allowed_param_types() {
159 return SQL_PARAMS_QM | SQL_PARAMS_NAMED
;
163 * Returns last error reported by database engine.
164 * @return string error message
166 public function get_last_error() {
167 return $this->lastError
;
171 * Function to print/save/ignore debugging messages related to SQL queries.
173 protected function debug_query($sql, $params = null) {
174 echo '<hr /> (', $this->get_dbtype(), '): ', htmlentities($sql);
176 echo ' (parameters ';
184 * Do NOT use in code, to be used by database_manager only!
185 * @param string $sql query
186 * @return bool success
188 public function change_database_structure($sql) {
190 $this->query_start($sql, null, SQL_QUERY_STRUCTURE
);
193 $this->pdb
->exec($sql);
194 $this->reset_caches();
195 } catch (PDOException
$ex) {
196 $this->lastError
= $ex->getMessage();
199 $this->query_end($result);
203 public function delete_records_select($table, $select, array $params=null) {
204 $sql = "DELETE FROM {{$table}}";
206 $sql .= " WHERE $select";
208 return $this->execute($sql, $params);
212 * Factory method that creates a recordset for return by a query. The generic pdo_moodle_recordset
213 * class should fit most cases, but pdo_moodle_database subclasses can override this method to return
214 * a subclass of pdo_moodle_recordset.
215 * @param object $sth instance of PDOStatement
216 * @return object instance of pdo_moodle_recordset
218 protected function create_recordset($sth) {
219 return new pdo_moodle_recordset($sth);
223 * Execute general sql query. Should be used only when no other method suitable.
224 * Do NOT use this to make changes in db structure, use database_manager methods instead!
225 * @param string $sql query
226 * @param array $params query parameters
227 * @return bool success
229 public function execute($sql, array $params=null) {
230 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
233 $this->query_start($sql, $params, SQL_QUERY_UPDATE
);
236 $sth = $this->pdb
->prepare($sql);
237 $sth->execute($params);
238 } catch (PDOException
$ex) {
239 $this->lastError
= $ex->getMessage();
243 $this->query_end($result);
248 * Get a number of records as an moodle_recordset. $sql must be a complete SQL query.
249 * Since this method is a little less readable, use of it should be restricted to
250 * code where it's possible there might be large datasets being returned. For known
251 * small datasets use get_records_sql - it leads to simpler code.
253 * The return type is as for @see function get_recordset.
255 * @param string $sql the SQL select query to execute.
256 * @param array $params array of sql parameters
257 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
258 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
259 * @return moodle_recordset instance
261 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
265 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
266 $sql = $this->get_limit_clauses($sql, $limitfrom, $limitnum);
267 $this->query_start($sql, $params, SQL_QUERY_SELECT
);
270 $sth = $this->pdb
->prepare($sql);
271 $sth->execute($params);
272 $result = $this->create_recordset($sth);
273 } catch (PDOException
$ex) {
274 $this->lastError
= $ex->getMessage();
278 $this->query_end($result);
283 * Selects rows and return values of first column as array.
285 * @param string $sql The SQL query
286 * @param array $params array of sql parameters
287 * @return array of values
289 public function get_fieldset_sql($sql, array $params=null) {
290 $rs = $this->get_recordset_sql($sql, $params);
292 $rs->close(); // Not going to iterate (but exit), close rs
296 foreach($rs as $value) {
297 $result[] = reset($value);
304 * Get a number of records as an array of objects.
306 * Return value as for @see function get_records.
308 * @param string $sql the SQL select query to execute. The first column of this SELECT statement
309 * must be a unique value (usually the 'id' field), as it will be used as the key of the
311 * @param array $params array of sql parameters
312 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
313 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
314 * @return array of objects, or empty array if no records were found, or false if an error occurred.
316 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
317 $rs = $this->get_recordset_sql($sql, $params, $limitfrom, $limitnum);
319 $rs->close(); // Not going to iterate (but exit), close rs
323 $debugging = debugging('', DEBUG_DEVELOPER
);
324 foreach($rs as $value) {
325 $key = reset($value);
326 if ($debugging && array_key_exists($key, $objects)) {
327 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
);
329 $objects[$key] = (object)$value;
336 * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
337 * @param string $table name
338 * @param mixed $params data record as object or array
339 * @param bool $returnit return it of inserted record
340 * @param bool $bulk true means repeated inserts expected
341 * @param bool $customsequence true if 'id' included in $params, disables $returnid
342 * @return bool|int true or new id
344 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
345 if (!is_array($params)) {
346 $params = (array)$params;
349 if ($customsequence) {
350 if (!isset($params['id'])) {
351 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
355 unset($params['id']);
358 if (empty($params)) {
359 throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
362 $fields = implode(',', array_keys($params));
363 $qms = array_fill(0, count($params), '?');
364 $qms = implode(',', $qms);
366 $sql = "INSERT INTO {{$table}} ($fields) VALUES($qms)";
367 if (!$this->execute($sql, $params)) {
373 if ($id = $this->pdb
->lastInsertId()) {
380 * Insert a record into a table and return the "id" field if required,
381 * Some conversions and safety checks are carried out. Lobs are supported.
382 * If the return ID isn't required, then this just reports success as true/false.
383 * $data is an object containing needed data
384 * @param string $table The database table to be inserted into
385 * @param object $data A data object with values for one or more fields in the record
386 * @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.
387 * @param bool $bulk true means repeated inserts expected
388 * @return bool|int true or new id
390 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
391 $dataobject = (array)$dataobject;
393 $columns = $this->get_columns($table);
396 foreach ($dataobject as $field=>$value) {
397 if ($field === 'id') {
400 if (!isset($columns[$field])) {
403 $column = $columns[$field];
404 if (is_bool($value)) {
405 $value = (int)$value; // prevent "false" problems
407 if (!empty($column->enums
)) {
408 // workaround for problem with wrong enums
409 if (is_null($value) and !$column->not_null
) {
410 // ok - nulls allowed
412 if (!in_array((string)$value, $column->enums
)) {
413 debugging('Enum value '.s($value).' not allowed in field '.$field.' table '.$table.'.');
418 $cleaned[$field] = $value;
421 if (empty($cleaned)) {
425 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
429 * Update record in database, as fast as possible, no safety checks, lobs not supported.
430 * @param string $table name
431 * @param mixed $params data record as object or array
432 * @param bool true means repeated updates expected
433 * @return bool success
435 public function update_record_raw($table, $params, $bulk=false) {
436 $params = (array)$params;
438 if (!isset($params['id'])) {
439 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
442 unset($params['id']);
444 if (empty($params)) {
445 throw new coding_exception('moodle_database::update_record_raw() no fields found.');
449 foreach ($params as $field=>$value) {
450 $sets[] = "$field = ?";
453 $params[] = $id; // last ? in WHERE condition
455 $sets = implode(',', $sets);
456 $sql = "UPDATE {{$table}} SET $sets WHERE id=?";
457 return $this->execute($sql, $params);
461 * Update a record in a table
463 * $dataobject is an object containing needed data
464 * Relies on $dataobject having a variable "id" to
465 * specify the record to update
467 * @param string $table The database table to be checked against.
468 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
469 * @param bool true means repeated updates expected
470 * @return bool success
472 public function update_record($table, $dataobject, $bulk=false) {
473 $dataobject = (array)$dataobject;
475 $columns = $this->get_columns($table);
478 foreach ($dataobject as $field=>$value) {
479 if (!isset($columns[$field])) {
482 if (is_bool($value)) {
483 $value = (int)$value; // prevent "false" problems
485 $cleaned[$field] = $value;
488 return $this->update_record_raw($table, $cleaned, $bulk);
492 * Set a single field in every table row where the select statement evaluates to true.
494 * @param string $table The database table to be checked against.
495 * @param string $newfield the field to set.
496 * @param string $newvalue the value to set the field to.
497 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
498 * @param array $params array of sql parameters
499 * @return bool success
501 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) {
503 $select = "WHERE $select";
505 if (is_null($params)) {
508 list($select, $params, $type) = $this->fix_sql_params($select, $params);
510 if (is_bool($newvalue)) {
511 $newvalue = (int)$newvalue; // prevent "false" problems
513 if (is_null($newvalue)) {
514 $newfield = "$newfield = NULL";
516 // make sure SET and WHERE clauses use the same type of parameters,
517 // because we don't support different types in the same query
519 case SQL_PARAMS_NAMED
:
520 $newfield = "$newfield = :newvalueforupdate";
521 $params['newvalueforupdate'] = $newvalue;
524 $newfield = "$newfield = ?";
525 array_unshift($params, $newvalue);
528 $this->lastError
= __FILE__
. ' LINE: ' . __LINE__
. '.';
529 print_error(unknowparamtype
, 'error', '', $this->lastError
);
532 $sql = "UPDATE {{$table}} SET $newfield $select";
533 return $this->execute($sql, $params);
536 public function sql_concat() {
540 public function sql_concat_join($separator="' '", $elements=array()) {
544 protected function begin_transaction() {
545 $this->query_start('', NULL, SQL_QUERY_AUX
);
547 $this->pdb
->beginTransaction();
548 } catch(PDOException
$ex) {
549 $this->lastError
= $ex->getMessage();
551 $this->query_end($result);
554 protected function commit_transaction() {
555 $this->query_start('', NULL, SQL_QUERY_AUX
);
558 $this->pdb
->commit();
559 } catch(PDOException
$ex) {
560 $this->lastError
= $ex->getMessage();
562 $this->query_end($result);
565 protected function rollback_transaction() {
566 $this->query_start('', NULL, SQL_QUERY_AUX
);
569 $this->pdb
->rollBack();
570 } catch(PDOException
$ex) {
571 $this->lastError
= $ex->getMessage();
573 $this->query_end($result);
577 * Import a record into a table, id field is required.
578 * Basic safety checks only. Lobs are supported.
579 * @param string $table name of database table to be inserted into
580 * @param mixed $dataobject object or array with fields in the record
581 * @return bool success
583 public function import_record($table, $dataobject) {
584 $dataobject = (object)$dataobject;
586 $columns = $this->get_columns($table);
588 foreach ($dataobject as $field=>$value) {
589 if (!isset($columns[$field])) {
592 $cleaned[$field] = $value;
595 return $this->insert_record_raw($table, $cleaned, false, true, true);
599 * Called before each db query.
601 * Overridden to ensure $this->lastErorr is reset each query
604 * @param array array of parameters
605 * @param int $type type of query
606 * @param mixed $extrainfo driver specific extra information
609 protected function query_start($sql, array $params=null, $type, $extrainfo=null) {
610 $this->lastError
= null;
611 parent
::query_start($sql, $params, $type, $extrainfo);