lib/dml/pdo_moodle_database.php

   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(...$arr) {
 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 }