[Summary view]

Differences Between: [Versions 310 and 403] [Versions 311 and 403] [Versions 39 and 403] [Versions 400 and 403] [Versions 401 and 403]

   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       * @param array|null $tablenames an array of xmldb table names affected by this request.
 179       * @return bool true
 180       * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors.
 181       */
 182      public function change_database_structure($sql, $tablenames = null) {
 183          $this->get_manager(); // Includes DDL exceptions classes ;-)
 184          $sqls = (array)$sql;
 185  
 186          try {
 187              foreach ($sqls as $sql) {
 188                  $result = true;
 189                  $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
 190  
 191                  try {
 192                      $this->pdb->exec($sql);
 193                  } catch (PDOException $ex) {
 194                      $this->lastError = $ex->getMessage();
 195                      $result = false;
 196                  }
 197                  $this->query_end($result);
 198              }
 199          } catch (ddl_change_structure_exception $e) {
 200              $this->reset_caches($tablenames);
 201              throw $e;
 202          }
 203  
 204          $this->reset_caches($tablenames);
 205          return true;
 206      }
 207  
 208      public function delete_records_select($table, $select, array $params=null) {
 209          $sql = "DELETE FROM {{$table}}";
 210          if ($select) {
 211              $sql .= " WHERE $select";
 212          }
 213          return $this->execute($sql, $params);
 214      }
 215  
 216      /**
 217       * Factory method that creates a recordset for return by a query. The generic pdo_moodle_recordset
 218       * class should fit most cases, but pdo_moodle_database subclasses can override this method to return
 219       * a subclass of pdo_moodle_recordset.
 220       * @param object $sth instance of PDOStatement
 221       * @return object instance of pdo_moodle_recordset
 222       */
 223      protected function create_recordset($sth) {
 224          return new pdo_moodle_recordset($sth);
 225      }
 226  
 227      /**
 228       * Execute general sql query. Should be used only when no other method suitable.
 229       * Do NOT use this to make changes in db structure, use database_manager methods instead!
 230       * @param string $sql query
 231       * @param array $params query parameters
 232       * @return bool success
 233       */
 234      public function execute($sql, array $params=null) {
 235          list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
 236  
 237          $result = true;
 238          $this->query_start($sql, $params, SQL_QUERY_UPDATE);
 239  
 240          try {
 241              $sth = $this->pdb->prepare($sql);
 242              $sth->execute($params);
 243          } catch (PDOException $ex) {
 244              $this->lastError = $ex->getMessage();
 245              $result = false;
 246          }
 247  
 248          $this->query_end($result);
 249          return $result;
 250      }
 251  
 252      /**
 253       * Get a number of records as an moodle_recordset.  $sql must be a complete SQL query.
 254       * Since this method is a little less readable, use of it should be restricted to
 255       * code where it's possible there might be large datasets being returned.  For known
 256       * small datasets use get_records_sql - it leads to simpler code.
 257       *
 258       * The return type is like:
 259       * @see function get_recordset.
 260       *
 261       * @param string $sql the SQL select query to execute.
 262       * @param array $params array of sql parameters
 263       * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 264       * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 265       * @return moodle_recordset instance
 266       */
 267      public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
 268  
 269          $result = true;
 270  
 271          list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
 272          $sql = $this->get_limit_clauses($sql, $limitfrom, $limitnum);
 273          $this->query_start($sql, $params, SQL_QUERY_SELECT);
 274  
 275          try {
 276              $sth = $this->pdb->prepare($sql);
 277              $sth->execute($params);
 278              $result = $this->create_recordset($sth);
 279          } catch (PDOException $ex) {
 280              $this->lastError = $ex->getMessage();
 281              $result = false;
 282          }
 283  
 284          $this->query_end($result);
 285          return $result;
 286      }
 287  
 288      /**
 289       * Selects rows and return values of first column as array.
 290       *
 291       * @param string $sql The SQL query
 292       * @param array $params array of sql parameters
 293       * @return array of values
 294       */
 295      public function get_fieldset_sql($sql, array $params=null) {
 296          $rs = $this->get_recordset_sql($sql, $params);
 297          if (!$rs->valid()) {
 298              $rs->close(); // Not going to iterate (but exit), close rs
 299              return false;
 300          }
 301          $result = array();
 302          foreach($rs as $value) {
 303              $result[] = reset($value);
 304          }
 305          $rs->close();
 306          return $result;
 307      }
 308  
 309      /**
 310       * Get a number of records as an array of objects.
 311       *
 312       * Return value is like:
 313       * @see function get_records.
 314       *
 315       * @param string $sql the SQL select query to execute. The first column of this SELECT statement
 316       *   must be a unique value (usually the 'id' field), as it will be used as the key of the
 317       *   returned array.
 318       * @param array $params array of sql parameters
 319       * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 320       * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 321       * @return array of objects, or empty array if no records were found, or false if an error occurred.
 322       */
 323      public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
 324          global $CFG;
 325  
 326          $rs = $this->get_recordset_sql($sql, $params, $limitfrom, $limitnum);
 327          if (!$rs->valid()) {
 328              $rs->close(); // Not going to iterate (but exit), close rs
 329              return false;
 330          }
 331          $objects = array();
 332          foreach($rs as $value) {
 333              $key = reset($value);
 334              if ($CFG->debugdeveloper && array_key_exists($key, $objects)) {
 335                  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);
 336              }
 337              $objects[$key] = (object)$value;
 338          }
 339          $rs->close();
 340          return $objects;
 341      }
 342  
 343      /**
 344       * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
 345       * @param string $table name
 346       * @param mixed $params data record as object or array
 347       * @param bool $returnit return it of inserted record
 348       * @param bool $bulk true means repeated inserts expected
 349       * @param bool $customsequence true if 'id' included in $params, disables $returnid
 350       * @return bool|int true or new id
 351       */
 352      public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
 353          if (!is_array($params)) {
 354              $params = (array)$params;
 355          }
 356  
 357          if ($customsequence) {
 358              if (!isset($params['id'])) {
 359                  throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
 360              }
 361              $returnid = false;
 362          } else {
 363              unset($params['id']);
 364          }
 365  
 366          if (empty($params)) {
 367              throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
 368          }
 369  
 370          $fields = implode(',', array_keys($params));
 371          $qms    = array_fill(0, count($params), '?');
 372          $qms    = implode(',', $qms);
 373  
 374          $sql = "INSERT INTO {{$table}} ($fields) VALUES($qms)";
 375          if (!$this->execute($sql, $params)) {
 376              return false;
 377          }
 378          if (!$returnid) {
 379              return true;
 380          }
 381          if ($id = $this->pdb->lastInsertId()) {
 382              return (int)$id;
 383          }
 384          return false;
 385      }
 386  
 387      /**
 388       * Insert a record into a table and return the "id" field if required,
 389       * Some conversions and safety checks are carried out. Lobs are supported.
 390       * If the return ID isn't required, then this just reports success as true/false.
 391       * $data is an object containing needed data
 392       * @param string $table The database table to be inserted into
 393       * @param object|array $dataobject A data object with values for one or more fields in the record
 394       * @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.
 395       * @param bool $bulk true means repeated inserts expected
 396       * @return bool|int true or new id
 397       */
 398      public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
 399          $dataobject = (array)$dataobject;
 400  
 401          $columns = $this->get_columns($table);
 402          if (empty($columns)) {
 403              throw new dml_exception('ddltablenotexist', $table);
 404          }
 405  
 406          $cleaned = array();
 407  
 408          foreach ($dataobject as $field=>$value) {
 409              if ($field === 'id') {
 410                  continue;
 411              }
 412              if (!isset($columns[$field])) {
 413                  continue;
 414              }
 415              $column = $columns[$field];
 416              if (is_bool($value)) {
 417                  $value = (int)$value; // prevent "false" problems
 418              }
 419              $cleaned[$field] = $value;
 420          }
 421  
 422          if (empty($cleaned)) {
 423              return false;
 424          }
 425  
 426          return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
 427      }
 428  
 429      /**
 430       * Update record in database, as fast as possible, no safety checks, lobs not supported.
 431       * @param string $table name
 432       * @param stdClass|array $params data record as object or array
 433       * @param bool true means repeated updates expected
 434       * @return bool success
 435       */
 436      public function update_record_raw($table, $params, $bulk=false) {
 437          $params = (array)$params;
 438  
 439          if (!isset($params['id'])) {
 440              throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
 441          }
 442          $id = $params['id'];
 443          unset($params['id']);
 444  
 445          if (empty($params)) {
 446              throw new coding_exception('moodle_database::update_record_raw() no fields found.');
 447          }
 448  
 449          $sets = array();
 450          foreach ($params as $field=>$value) {
 451              $sets[] = "$field = ?";
 452          }
 453  
 454          $params[] = $id; // last ? in WHERE condition
 455  
 456          $sets = implode(',', $sets);
 457          $sql = "UPDATE {{$table}} SET $sets WHERE id=?";
 458          return $this->execute($sql, $params);
 459      }
 460  
 461      /**
 462       * Update a record in a table
 463       *
 464       * $dataobject is an object containing needed data
 465       * Relies on $dataobject having a variable "id" to
 466       * specify the record to update
 467       *
 468       * @param string $table The database table to be checked against.
 469       * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
 470       * @param bool true means repeated updates expected
 471       * @return bool success
 472       */
 473      public function update_record($table, $dataobject, $bulk=false) {
 474          $dataobject = (array)$dataobject;
 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:
 529                  $this->lastError = __FILE__ . ' LINE: ' . __LINE__ . '.';
 530                  throw new \moodle_exception(unknowparamtype, 'error', '', $this->lastError);
 531              }
 532          }
 533          $sql = "UPDATE {{$table}} SET $newfield $select";
 534          return $this->execute($sql, $params);
 535      }
 536  
 537      public function sql_concat() {
 538          throw new \moodle_exception('TODO');
 539      }
 540  
 541      public function sql_concat_join($separator="' '", $elements=array()) {
 542          throw new \moodle_exception('TODO');
 543      }
 544  
 545      /**
 546       * Return SQL for performing group concatenation on given field/expression
 547       *
 548       * @param string $field
 549       * @param string $separator
 550       * @param string $sort
 551       * @return string
 552       */
 553      public function sql_group_concat(string $field, string $separator = ', ', string $sort = ''): string {
 554          return ''; // TODO.
 555      }
 556  
 557      protected function begin_transaction() {
 558          $this->query_start('', NULL, SQL_QUERY_AUX);
 559          try {
 560              $this->pdb->beginTransaction();
 561          } catch(PDOException $ex) {
 562              $this->lastError = $ex->getMessage();
 563          }
 564          $this->query_end($result);
 565      }
 566  
 567      protected function commit_transaction() {
 568          $this->query_start('', NULL, SQL_QUERY_AUX);
 569  
 570          try {
 571              $this->pdb->commit();
 572          } catch(PDOException $ex) {
 573              $this->lastError = $ex->getMessage();
 574          }
 575          $this->query_end($result);
 576      }
 577  
 578      protected function rollback_transaction() {
 579          $this->query_start('', NULL, SQL_QUERY_AUX);
 580  
 581          try {
 582              $this->pdb->rollBack();
 583          } catch(PDOException $ex) {
 584              $this->lastError = $ex->getMessage();
 585          }
 586          $this->query_end($result);
 587      }
 588  
 589      /**
 590       * Import a record into a table, id field is required.
 591       * Basic safety checks only. Lobs are supported.
 592       * @param string $table name of database table to be inserted into
 593       * @param mixed $dataobject object or array with fields in the record
 594       * @return bool success
 595       */
 596      public function import_record($table, $dataobject) {
 597          $dataobject = (object)$dataobject;
 598  
 599          $columns = $this->get_columns($table);
 600          $cleaned = array();
 601          foreach ($dataobject as $field=>$value) {
 602              if (!isset($columns[$field])) {
 603                  continue;
 604              }
 605              $cleaned[$field] = $value;
 606          }
 607  
 608          return $this->insert_record_raw($table, $cleaned, false, true, true);
 609      }
 610  
 611      /**
 612       * Called before each db query.
 613       *
 614       * Overridden to ensure $this->lastErorr is reset each query
 615       *
 616       * @param string $sql
 617       * @param array|null $params An array of parameters.
 618       * @param int $type type of query
 619       * @param mixed $extrainfo driver specific extra information
 620       * @return void
 621       */
 622      protected function query_start($sql, ?array $params, $type, $extrainfo=null) {
 623          $this->lastError = null;
 624          parent::query_start($sql, $params, $type, $extrainfo);
 625      }
 626  }