[Summary view] [Print] [Text view]

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