Search moodle.org's
Developer Documentation

See Release Notes
Long Term Support Release

  • Bug fixes for general core bugs in 3.9.x will end* 10 May 2021 (12 months).
  • Bug fixes for security issues in 3.9.x will end* 8 May 2023 (36 months).
  • PHP version: minimum PHP 7.2.0 Note: minimum PHP version has increased since Moodle 3.8. PHP 7.3.x and 7.4.x are supported too.

Differences Between: [Versions 39 and 310] [Versions 39 and 311] [Versions 39 and 400] [Versions 39 and 401] [Versions 39 and 402] [Versions 39 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   * Native pgsql class representing moodle database interface.
  19   *
  20   * @package    core_dml
  21   * @copyright  2008 Petr Skoda (http://skodak.org)
  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__.'/moodle_read_slave_trait.php');
  29  require_once (__DIR__.'/pgsql_native_moodle_recordset.php');
  30  require_once (__DIR__.'/pgsql_native_moodle_temptables.php');
  31  
  32  /**
  33   * Native pgsql class representing moodle database interface.
  34   *
  35   * @package    core_dml
  36   * @copyright  2008 Petr Skoda (http://skodak.org)
  37   * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
  38   */
  39  class pgsql_native_moodle_database extends moodle_database {
  40      use moodle_read_slave_trait {
  41          select_db_handle as read_slave_select_db_handle;
  42          can_use_readonly as read_slave_can_use_readonly;
  43          query_start as read_slave_query_start;
  44      }
  45  
  46      /** @var array $dbhcursor keep track of open cursors */
  47      private $dbhcursor = [];
  48  
  49      /** @var resource $pgsql database resource */
  50      protected $pgsql     = null;
  51  
  52      protected $last_error_reporting; // To handle pgsql driver default verbosity
  53  
  54      /** @var bool savepoint hack for MDL-35506 - workaround for automatic transaction rollback on error */
  55      protected $savepointpresent = false;
  56  
  57      /** @var int Number of cursors used (for constructing a unique ID) */
  58      protected $cursorcount = 0;
  59  
  60      /** @var int Default number of rows to fetch at a time when using recordsets with cursors */
  61      const DEFAULT_FETCH_BUFFER_SIZE = 100000;
  62  
  63      /**
  64       * Detects if all needed PHP stuff installed.
  65       * Note: can be used before connect()
  66       * @return mixed true if ok, string if something
  67       */
  68      public function driver_installed() {
  69          if (!extension_loaded('pgsql')) {
  70              return get_string('pgsqlextensionisnotpresentinphp', 'install');
  71          }
  72          return true;
  73      }
  74  
  75      /**
  76       * Returns database family type - describes SQL dialect
  77       * Note: can be used before connect()
  78       * @return string db family name (mysql, postgres, mssql, oracle, etc.)
  79       */
  80      public function get_dbfamily() {
  81          return 'postgres';
  82      }
  83  
  84      /**
  85       * Returns more specific database driver type
  86       * Note: can be used before connect()
  87       * @return string db type mysqli, pgsql, oci, mssql, sqlsrv
  88       */
  89      protected function get_dbtype() {
  90          return 'pgsql';
  91      }
  92  
  93      /**
  94       * Returns general database library name
  95       * Note: can be used before connect()
  96       * @return string db type pdo, native
  97       */
  98      protected function get_dblibrary() {
  99          return 'native';
 100      }
 101  
 102      /**
 103       * Returns localised database type name
 104       * Note: can be used before connect()
 105       * @return string
 106       */
 107      public function get_name() {
 108          return get_string('nativepgsql', 'install');
 109      }
 110  
 111      /**
 112       * Returns localised database configuration help.
 113       * Note: can be used before connect()
 114       * @return string
 115       */
 116      public function get_configuration_help() {
 117          return get_string('nativepgsqlhelp', 'install');
 118      }
 119  
 120      /**
 121       * Connect to db
 122       * @param string $dbhost The database host.
 123       * @param string $dbuser The database username.
 124       * @param string $dbpass The database username's password.
 125       * @param string $dbname The name of the database being connected to.
 126       * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
 127       * @param array $dboptions driver specific options
 128       * @return bool true
 129       * @throws dml_connection_exception if error
 130       */
 131      public function raw_connect(string $dbhost, string $dbuser, string $dbpass, string $dbname, $prefix, array $dboptions=null): bool {
 132          if ($prefix == '' and !$this->external) {
 133              //Enforce prefixes for everybody but mysql
 134              throw new dml_exception('prefixcannotbeempty', $this->get_dbfamily());
 135          }
 136  
 137          $driverstatus = $this->driver_installed();
 138  
 139          if ($driverstatus !== true) {
 140              throw new dml_exception('dbdriverproblem', $driverstatus);
 141          }
 142  
 143          $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
 144  
 145          $pass = addcslashes($this->dbpass, "'\\");
 146  
 147          // Unix socket connections should have lower overhead
 148          if (!empty($this->dboptions['dbsocket']) and ($this->dbhost === 'localhost' or $this->dbhost === '127.0.0.1')) {
 149              $connection = "user='$this->dbuser' password='$pass' dbname='$this->dbname'";
 150              if (strpos($this->dboptions['dbsocket'], '/') !== false) {
 151                  // A directory was specified as the socket location.
 152                  $connection .= " host='".$this->dboptions['dbsocket']."'";
 153              }
 154              if (!empty($this->dboptions['dbport'])) {
 155                  // A port as specified, add it to the connection as it's used as part of the socket path.
 156                  $connection .= " port ='".$this->dboptions['dbport']."'";
 157              }
 158          } else {
 159              $this->dboptions['dbsocket'] = '';
 160              if (empty($this->dbname)) {
 161                  // probably old style socket connection - do not add port
 162                  $port = "";
 163              } else if (empty($this->dboptions['dbport'])) {
 164                  $port = "port ='5432'";
 165              } else {
 166                  $port = "port ='".$this->dboptions['dbport']."'";
 167              }
 168              $connection = "host='$this->dbhost' $port user='$this->dbuser' password='$pass' dbname='$this->dbname'";
 169          }
 170  
 171          if (!empty($this->dboptions['connecttimeout'])) {
 172              $connection .= " connect_timeout=".$this->dboptions['connecttimeout'];
 173          }
 174  
 175          if (empty($this->dboptions['dbhandlesoptions'])) {
 176              // ALTER USER and ALTER DATABASE are overridden by these settings.
 177              $options = array('--client_encoding=utf8', '--standard_conforming_strings=on');
 178              // Select schema if specified, otherwise the first one wins.
 179              if (!empty($this->dboptions['dbschema'])) {
 180                  $options[] = "-c search_path=" . addcslashes($this->dboptions['dbschema'], "'\\");
 181              }
 182  
 183              $connection .= " options='" . implode(' ', $options) . "'";
 184          }
 185  
 186          ob_start();
 187          if (empty($this->dboptions['dbpersist'])) {
 188              $this->pgsql = pg_connect($connection, PGSQL_CONNECT_FORCE_NEW);
 189          } else {
 190              $this->pgsql = pg_pconnect($connection, PGSQL_CONNECT_FORCE_NEW);
 191          }
 192          $dberr = ob_get_contents();
 193          ob_end_clean();
 194  
 195          $status = pg_connection_status($this->pgsql);
 196  
 197          if ($status === false or $status === PGSQL_CONNECTION_BAD) {
 198              $this->pgsql = null;
 199              throw new dml_connection_exception($dberr);
 200          }
 201  
 202          if (!empty($this->dboptions['dbpersist'])) {
 203              // There are rare situations (such as PHP out of memory errors) when open cursors may
 204              // not be closed at the end of a connection. When using persistent connections, the
 205              // cursors remain open and 'get in the way' of future connections. To avoid this
 206              // problem, close all cursors here.
 207              $result = pg_query($this->pgsql, 'CLOSE ALL');
 208              if ($result) {
 209                  pg_free_result($result);
 210              }
 211          }
 212  
 213          if (!empty($this->dboptions['dbhandlesoptions'])) {
 214              /* We don't trust people who just set the dbhandlesoptions, this code checks up on them.
 215               * These functions do not talk to the server, they use the client library knowledge to determine state.
 216               */
 217              if (!empty($this->dboptions['dbschema'])) {
 218                  throw new dml_connection_exception('You cannot specify a schema with dbhandlesoptions, use the database to set it.');
 219              }
 220              if (pg_client_encoding($this->pgsql) != 'UTF8') {
 221                  throw new dml_connection_exception('client_encoding = UTF8 not set, it is: ' . pg_client_encoding($this->pgsql));
 222              }
 223              if (pg_escape_string($this->pgsql, '\\') != '\\') {
 224                  throw new dml_connection_exception('standard_conforming_strings = on, must be set at the database.');
 225              }
 226          }
 227  
 228          // Connection stabilised and configured, going to instantiate the temptables controller
 229          $this->temptables = new pgsql_native_moodle_temptables($this);
 230  
 231          return true;
 232      }
 233  
 234      /**
 235       * Close database connection and release all resources
 236       * and memory (especially circular memory references).
 237       * Do NOT use connect() again, create a new instance if needed.
 238       */
 239      public function dispose() {
 240          parent::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection
 241          if ($this->pgsql) {
 242              pg_close($this->pgsql);
 243              $this->pgsql = null;
 244          }
 245      }
 246  
 247      /**
 248       * Gets db handle currently used with queries
 249       * @return resource
 250       */
 251      protected function get_db_handle() {
 252          return $this->pgsql;
 253      }
 254  
 255      /**
 256       * Sets db handle to be used with subsequent queries
 257       * @param resource $dbh
 258       * @return void
 259       */
 260      protected function set_db_handle($dbh): void {
 261          $this->pgsql = $dbh;
 262      }
 263  
 264      /**
 265       * Select appropriate db handle - readwrite or readonly
 266       * @param int $type type of query
 267       * @param string $sql
 268       * @return void
 269       */
 270      protected function select_db_handle(int $type, string $sql): void {
 271          $this->read_slave_select_db_handle($type, $sql);
 272  
 273          if (preg_match('/^DECLARE (crs\w*) NO SCROLL CURSOR/', $sql, $match)) {
 274              $cursor = $match[1];
 275              $this->dbhcursor[$cursor] = $this->pgsql;
 276          }
 277          if (preg_match('/^(?:FETCH \d+ FROM|CLOSE) (crs\w*)\b/', $sql, $match)) {
 278              $cursor = $match[1];
 279              $this->pgsql = $this->dbhcursor[$cursor];
 280          }
 281      }
 282  
 283      /**
 284       * Check if The query qualifies for readonly connection execution
 285       * Logging queries are exempt, those are write operations that circumvent
 286       * standard query_start/query_end paths.
 287       * @param int $type type of query
 288       * @param string $sql
 289       * @return bool
 290       */
 291      protected function can_use_readonly(int $type, string $sql): bool {
 292          // ... pg_*lock queries always go to master.
 293          if (preg_match('/\bpg_\w*lock/', $sql)) {
 294              return false;
 295          }
 296  
 297          // ... a nuisance - temptables use this.
 298          if (preg_match('/\bpg_constraint/', $sql) && $this->temptables->get_temptables()) {
 299              return false;
 300          }
 301  
 302          return $this->read_slave_can_use_readonly($type, $sql);
 303  
 304      }
 305  
 306      /**
 307       * Called before each db query.
 308       * @param string $sql
 309       * @param array array of parameters
 310       * @param int $type type of query
 311       * @param mixed $extrainfo driver specific extra information
 312       * @return void
 313       */
 314      protected function query_start($sql, array $params=null, $type, $extrainfo=null) {
 315          $this->read_slave_query_start($sql, $params, $type, $extrainfo);
 316          // pgsql driver tends to send debug to output, we do not need that.
 317          $this->last_error_reporting = error_reporting(0);
 318      }
 319  
 320      /**
 321       * Called immediately after each db query.
 322       * @param mixed db specific result
 323       * @return void
 324       */
 325      protected function query_end($result) {
 326          // reset original debug level
 327          error_reporting($this->last_error_reporting);
 328          try {
 329              parent::query_end($result);
 330              if ($this->savepointpresent and $this->last_type != SQL_QUERY_AUX and $this->last_type != SQL_QUERY_SELECT) {
 331                  $res = @pg_query($this->pgsql, "RELEASE SAVEPOINT moodle_pg_savepoint; SAVEPOINT moodle_pg_savepoint");
 332                  if ($res) {
 333                      pg_free_result($res);
 334                  }
 335              }
 336          } catch (Exception $e) {
 337              if ($this->savepointpresent) {
 338                  $res = @pg_query($this->pgsql, "ROLLBACK TO SAVEPOINT moodle_pg_savepoint; SAVEPOINT moodle_pg_savepoint");
 339                  if ($res) {
 340                      pg_free_result($res);
 341                  }
 342              }
 343              throw $e;
 344          }
 345      }
 346  
 347      /**
 348       * Returns database server info array
 349       * @return array Array containing 'description' and 'version' info
 350       */
 351      public function get_server_info() {
 352          static $info;
 353          if (!$info) {
 354              $this->query_start("--pg_version()", null, SQL_QUERY_AUX);
 355              $info = pg_version($this->pgsql);
 356              $this->query_end(true);
 357          }
 358          return array('description'=>$info['server'], 'version'=>$info['server']);
 359      }
 360  
 361      /**
 362       * Returns supported query parameter types
 363       * @return int bitmask of accepted SQL_PARAMS_*
 364       */
 365      protected function allowed_param_types() {
 366          return SQL_PARAMS_DOLLAR;
 367      }
 368  
 369      /**
 370       * Returns last error reported by database engine.
 371       * @return string error message
 372       */
 373      public function get_last_error() {
 374          return pg_last_error($this->pgsql);
 375      }
 376  
 377      /**
 378       * Return tables in database WITHOUT current prefix.
 379       * @param bool $usecache if true, returns list of cached tables.
 380       * @return array of table names in lowercase and without prefix
 381       */
 382      public function get_tables($usecache=true) {
 383          if ($usecache and $this->tables !== null) {
 384              return $this->tables;
 385          }
 386          $this->tables = array();
 387          $prefix = str_replace('_', '|_', $this->prefix);
 388          $sql = "SELECT c.relname
 389                    FROM pg_catalog.pg_class c
 390                    JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace
 391                   WHERE c.relname LIKE '$prefix%' ESCAPE '|'
 392                         AND c.relkind = 'r'
 393                         AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())";
 394          $this->query_start($sql, null, SQL_QUERY_AUX);
 395          $result = pg_query($this->pgsql, $sql);
 396          $this->query_end($result);
 397  
 398          if ($result) {
 399              while ($row = pg_fetch_row($result)) {
 400                  $tablename = reset($row);
 401                  if ($this->prefix !== false && $this->prefix !== '') {
 402                      if (strpos($tablename, $this->prefix) !== 0) {
 403                          continue;
 404                      }
 405                      $tablename = substr($tablename, strlen($this->prefix));
 406                  }
 407                  $this->tables[$tablename] = $tablename;
 408              }
 409              pg_free_result($result);
 410          }
 411          return $this->tables;
 412      }
 413  
 414      /**
 415       * Return table indexes - everything lowercased.
 416       * @param string $table The table we want to get indexes from.
 417       * @return array of arrays
 418       */
 419      public function get_indexes($table) {
 420          $indexes = array();
 421          $tablename = $this->prefix.$table;
 422  
 423          $sql = "SELECT i.*
 424                    FROM pg_catalog.pg_indexes i
 425                    JOIN pg_catalog.pg_namespace as ns ON ns.nspname = i.schemaname
 426                   WHERE i.tablename = '$tablename'
 427                         AND (i.schemaname = current_schema() OR ns.oid = pg_my_temp_schema())";
 428  
 429          $this->query_start($sql, null, SQL_QUERY_AUX);
 430          $result = pg_query($this->pgsql, $sql);
 431          $this->query_end($result);
 432  
 433          if ($result) {
 434              while ($row = pg_fetch_assoc($result)) {
 435                  // The index definition could be generated schema-qualifying the target table name
 436                  // for safety, depending on the pgsql version (CVE-2018-1058).
 437                  if (!preg_match('/CREATE (|UNIQUE )INDEX ([^\s]+) ON (|'.$row['schemaname'].'\.)'.$tablename.' USING ([^\s]+) \(([^\)]+)\)/i', $row['indexdef'], $matches)) {
 438                      continue;
 439                  }
 440                  if ($matches[5] === 'id') {
 441                      continue;
 442                  }
 443                  $columns = explode(',', $matches[5]);
 444                  foreach ($columns as $k=>$column) {
 445                      $column = trim($column);
 446                      if ($pos = strpos($column, ' ')) {
 447                          // index type is separated by space
 448                          $column = substr($column, 0, $pos);
 449                      }
 450                      $columns[$k] = $this->trim_quotes($column);
 451                  }
 452                  $indexes[$row['indexname']] = array('unique'=>!empty($matches[1]),
 453                                                'columns'=>$columns);
 454              }
 455              pg_free_result($result);
 456          }
 457          return $indexes;
 458      }
 459  
 460      /**
 461       * Returns detailed information about columns in table.
 462       *
 463       * @param string $table name
 464       * @return database_column_info[] array of database_column_info objects indexed with column names
 465       */
 466      protected function fetch_columns(string $table): array {
 467          $structure = array();
 468  
 469          $tablename = $this->prefix.$table;
 470  
 471          $sql = "SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef,
 472                         CASE WHEN a.atthasdef THEN pg_catalog.pg_get_expr(d.adbin, d.adrelid) END AS adsrc
 473                    FROM pg_catalog.pg_class c
 474                    JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace
 475                    JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
 476                    JOIN pg_catalog.pg_type t ON t.oid = a.atttypid
 477               LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = c.oid AND d.adnum = a.attnum)
 478                   WHERE relkind = 'r' AND c.relname = '$tablename' AND c.reltype > 0 AND a.attnum > 0
 479                         AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())
 480                ORDER BY a.attnum";
 481  
 482          $this->query_start($sql, null, SQL_QUERY_AUX);
 483          $result = pg_query($this->pgsql, $sql);
 484          $this->query_end($result);
 485  
 486          if (!$result) {
 487              return array();
 488          }
 489          while ($rawcolumn = pg_fetch_object($result)) {
 490  
 491              $info = new stdClass();
 492              $info->name = $rawcolumn->field;
 493              $matches = null;
 494  
 495              if ($rawcolumn->type === 'varchar') {
 496                  $info->type          = 'varchar';
 497                  $info->meta_type     = 'C';
 498                  $info->max_length    = $rawcolumn->atttypmod - 4;
 499                  $info->scale         = null;
 500                  $info->not_null      = ($rawcolumn->attnotnull === 't');
 501                  $info->has_default   = ($rawcolumn->atthasdef === 't');
 502                  if ($info->has_default) {
 503                      $parts = explode('::', $rawcolumn->adsrc);
 504                      if (count($parts) > 1) {
 505                          $info->default_value = reset($parts);
 506                          $info->default_value = trim($info->default_value, "'");
 507                      } else {
 508                          $info->default_value = $rawcolumn->adsrc;
 509                      }
 510                  } else {
 511                      $info->default_value = null;
 512                  }
 513                  $info->primary_key   = false;
 514                  $info->binary        = false;
 515                  $info->unsigned      = null;
 516                  $info->auto_increment= false;
 517                  $info->unique        = null;
 518  
 519              } else if (preg_match('/int(\d)/i', $rawcolumn->type, $matches)) {
 520                  $info->type = 'int';
 521                  if (strpos($rawcolumn->adsrc, 'nextval') === 0) {
 522                      $info->primary_key   = true;
 523                      $info->meta_type     = 'R';
 524                      $info->unique        = true;
 525                      $info->auto_increment= true;
 526                      $info->has_default   = false;
 527                  } else {
 528                      $info->primary_key   = false;
 529                      $info->meta_type     = 'I';
 530                      $info->unique        = null;
 531                      $info->auto_increment= false;
 532                      $info->has_default   = ($rawcolumn->atthasdef === 't');
 533                  }
 534                  // Return number of decimals, not bytes here.
 535                  if ($matches[1] >= 8) {
 536                      $info->max_length = 18;
 537                  } else if ($matches[1] >= 4) {
 538                      $info->max_length = 9;
 539                  } else if ($matches[1] >= 2) {
 540                      $info->max_length = 4;
 541                  } else if ($matches[1] >= 1) {
 542                      $info->max_length = 2;
 543                  } else {
 544                      $info->max_length = 0;
 545                  }
 546                  $info->scale         = null;
 547                  $info->not_null      = ($rawcolumn->attnotnull === 't');
 548                  if ($info->has_default) {
 549                      // PG 9.5+ uses ::<TYPE> syntax for some defaults.
 550                      $parts = explode('::', $rawcolumn->adsrc);
 551                      if (count($parts) > 1) {
 552                          $info->default_value = reset($parts);
 553                      } else {
 554                          $info->default_value = $rawcolumn->adsrc;
 555                      }
 556                      $info->default_value = trim($info->default_value, "()'");
 557                  } else {
 558                      $info->default_value = null;
 559                  }
 560                  $info->binary        = false;
 561                  $info->unsigned      = false;
 562  
 563              } else if ($rawcolumn->type === 'numeric') {
 564                  $info->type = $rawcolumn->type;
 565                  $info->meta_type     = 'N';
 566                  $info->primary_key   = false;
 567                  $info->binary        = false;
 568                  $info->unsigned      = null;
 569                  $info->auto_increment= false;
 570                  $info->unique        = null;
 571                  $info->not_null      = ($rawcolumn->attnotnull === 't');
 572                  $info->has_default   = ($rawcolumn->atthasdef === 't');
 573                  if ($info->has_default) {
 574                      // PG 9.5+ uses ::<TYPE> syntax for some defaults.
 575                      $parts = explode('::', $rawcolumn->adsrc);
 576                      if (count($parts) > 1) {
 577                          $info->default_value = reset($parts);
 578                      } else {
 579                          $info->default_value = $rawcolumn->adsrc;
 580                      }
 581                      $info->default_value = trim($info->default_value, "()'");
 582                  } else {
 583                      $info->default_value = null;
 584                  }
 585                  $info->max_length    = $rawcolumn->atttypmod >> 16;
 586                  $info->scale         = ($rawcolumn->atttypmod & 0xFFFF) - 4;
 587  
 588              } else if (preg_match('/float(\d)/i', $rawcolumn->type, $matches)) {
 589                  $info->type = 'float';
 590                  $info->meta_type     = 'N';
 591                  $info->primary_key   = false;
 592                  $info->binary        = false;
 593                  $info->unsigned      = null;
 594                  $info->auto_increment= false;
 595                  $info->unique        = null;
 596                  $info->not_null      = ($rawcolumn->attnotnull === 't');
 597                  $info->has_default   = ($rawcolumn->atthasdef === 't');
 598                  if ($info->has_default) {
 599                      // PG 9.5+ uses ::<TYPE> syntax for some defaults.
 600                      $parts = explode('::', $rawcolumn->adsrc);
 601                      if (count($parts) > 1) {
 602                          $info->default_value = reset($parts);
 603                      } else {
 604                          $info->default_value = $rawcolumn->adsrc;
 605                      }
 606                      $info->default_value = trim($info->default_value, "()'");
 607                  } else {
 608                      $info->default_value = null;
 609                  }
 610                  // just guess expected number of deciaml places :-(
 611                  if ($matches[1] == 8) {
 612                      // total 15 digits
 613                      $info->max_length = 8;
 614                      $info->scale      = 7;
 615                  } else {
 616                      // total 6 digits
 617                      $info->max_length = 4;
 618                      $info->scale      = 2;
 619                  }
 620  
 621              } else if ($rawcolumn->type === 'text') {
 622                  $info->type          = $rawcolumn->type;
 623                  $info->meta_type     = 'X';
 624                  $info->max_length    = -1;
 625                  $info->scale         = null;
 626                  $info->not_null      = ($rawcolumn->attnotnull === 't');
 627                  $info->has_default   = ($rawcolumn->atthasdef === 't');
 628                  if ($info->has_default) {
 629                      $parts = explode('::', $rawcolumn->adsrc);
 630                      if (count($parts) > 1) {
 631                          $info->default_value = reset($parts);
 632                          $info->default_value = trim($info->default_value, "'");
 633                      } else {
 634                          $info->default_value = $rawcolumn->adsrc;
 635                      }
 636                  } else {
 637                      $info->default_value = null;
 638                  }
 639                  $info->primary_key   = false;
 640                  $info->binary        = false;
 641                  $info->unsigned      = null;
 642                  $info->auto_increment= false;
 643                  $info->unique        = null;
 644  
 645              } else if ($rawcolumn->type === 'bytea') {
 646                  $info->type          = $rawcolumn->type;
 647                  $info->meta_type     = 'B';
 648                  $info->max_length    = -1;
 649                  $info->scale         = null;
 650                  $info->not_null      = ($rawcolumn->attnotnull === 't');
 651                  $info->has_default   = false;
 652                  $info->default_value = null;
 653                  $info->primary_key   = false;
 654                  $info->binary        = true;
 655                  $info->unsigned      = null;
 656                  $info->auto_increment= false;
 657                  $info->unique        = null;
 658  
 659              }
 660  
 661              $structure[$info->name] = new database_column_info($info);
 662          }
 663  
 664          pg_free_result($result);
 665  
 666          return $structure;
 667      }
 668  
 669      /**
 670       * Normalise values based in RDBMS dependencies (booleans, LOBs...)
 671       *
 672       * @param database_column_info $column column metadata corresponding with the value we are going to normalise
 673       * @param mixed $value value we are going to normalise
 674       * @return mixed the normalised value
 675       */
 676      protected function normalise_value($column, $value) {
 677          $this->detect_objects($value);
 678  
 679          if (is_bool($value)) { // Always, convert boolean to int
 680              $value = (int)$value;
 681  
 682          } else if ($column->meta_type === 'B') {
 683              if (!is_null($value)) {
 684                  // standard_conforming_strings must be enabled, otherwise pg_escape_bytea() will double escape
 685                  // \ and produce data errors.  This is set on the connection.
 686                  $value = pg_escape_bytea($this->pgsql, $value);
 687              }
 688  
 689          } else if ($value === '') {
 690              if ($column->meta_type === 'I' or $column->meta_type === 'F' or $column->meta_type === 'N') {
 691                  $value = 0; // prevent '' problems in numeric fields
 692              }
 693          }
 694          return $value;
 695      }
 696  
 697      /**
 698       * Is db in unicode mode?
 699       * @return bool
 700       */
 701      public function setup_is_unicodedb() {
 702          // Get PostgreSQL server_encoding value
 703          $sql = "SHOW server_encoding";
 704          $this->query_start($sql, null, SQL_QUERY_AUX);
 705          $result = pg_query($this->pgsql, $sql);
 706          $this->query_end($result);
 707  
 708          if (!$result) {
 709              return false;
 710          }
 711          $rawcolumn = pg_fetch_object($result);
 712          $encoding = $rawcolumn->server_encoding;
 713          pg_free_result($result);
 714  
 715          return (strtoupper($encoding) == 'UNICODE' || strtoupper($encoding) == 'UTF8');
 716      }
 717  
 718      /**
 719       * Do NOT use in code, to be used by database_manager only!
 720       * @param string|array $sql query
 721       * @param array|null $tablenames an array of xmldb table names affected by this request.
 722       * @return bool true
 723       * @throws ddl_change_structure_exception A DDL specific exception is thrown for any errors.
 724       */
 725      public function change_database_structure($sql, $tablenames = null) {
 726          $this->get_manager(); // Includes DDL exceptions classes ;-)
 727          if (is_array($sql)) {
 728              $sql = implode("\n;\n", $sql);
 729          }
 730          if (!$this->is_transaction_started()) {
 731              // It is better to do all or nothing, this helps with recovery...
 732              $sql = "BEGIN ISOLATION LEVEL SERIALIZABLE;\n$sql\n; COMMIT";
 733          }
 734  
 735          try {
 736              $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
 737              $result = pg_query($this->pgsql, $sql);
 738              $this->query_end($result);
 739              pg_free_result($result);
 740          } catch (ddl_change_structure_exception $e) {
 741              if (!$this->is_transaction_started()) {
 742                  $result = @pg_query($this->pgsql, "ROLLBACK");
 743                  @pg_free_result($result);
 744              }
 745              $this->reset_caches($tablenames);
 746              throw $e;
 747          }
 748  
 749          $this->reset_caches($tablenames);
 750          return true;
 751      }
 752  
 753      /**
 754       * Execute general sql query. Should be used only when no other method suitable.
 755       * Do NOT use this to make changes in db structure, use database_manager methods instead!
 756       * @param string $sql query
 757       * @param array $params query parameters
 758       * @return bool true
 759       * @throws dml_exception A DML specific exception is thrown for any errors.
 760       */
 761      public function execute($sql, array $params=null) {
 762          list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
 763  
 764          if (strpos($sql, ';') !== false) {
 765              throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
 766          }
 767  
 768          $this->query_start($sql, $params, SQL_QUERY_UPDATE);
 769          $result = pg_query_params($this->pgsql, $sql, $params);
 770          $this->query_end($result);
 771  
 772          pg_free_result($result);
 773          return true;
 774      }
 775  
 776      /**
 777       * Get a number of records as a moodle_recordset using a SQL statement.
 778       *
 779       * Since this method is a little less readable, use of it should be restricted to
 780       * code where it's possible there might be large datasets being returned.  For known
 781       * small datasets use get_records_sql - it leads to simpler code.
 782       *
 783       * The return type is like:
 784       * @see function get_recordset.
 785       *
 786       * @param string $sql the SQL select query to execute.
 787       * @param array $params array of sql parameters
 788       * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 789       * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 790       * @return moodle_recordset instance
 791       * @throws dml_exception A DML specific exception is thrown for any errors.
 792       */
 793      public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
 794  
 795          list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum);
 796  
 797          if ($limitnum) {
 798              $sql .= " LIMIT $limitnum";
 799          }
 800          if ($limitfrom) {
 801              $sql .= " OFFSET $limitfrom";
 802          }
 803  
 804          list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
 805  
 806          // For any query that doesn't explicitly specify a limit, we must use cursors to stop it
 807          // loading the entire thing (unless the config setting is turned off).
 808          $usecursors = !$limitnum && ($this->get_fetch_buffer_size() > 0);
 809          if ($usecursors) {
 810              // Work out the cursor unique identifer. This is based on a simple count used which
 811              // should be OK because the identifiers only need to be unique within the current
 812              // transaction.
 813              $this->cursorcount++;
 814              $cursorname = 'crs' . $this->cursorcount;
 815  
 816              // Do the query to a cursor.
 817              $sql = 'DECLARE ' . $cursorname . ' NO SCROLL CURSOR WITH HOLD FOR ' . $sql;
 818          } else {
 819              $cursorname = '';
 820          }
 821  
 822          $this->query_start($sql, $params, SQL_QUERY_SELECT);
 823  
 824          $result = pg_query_params($this->pgsql, $sql, $params);
 825  
 826          $this->query_end($result);
 827          if ($usecursors) {
 828              pg_free_result($result);
 829              $result = null;
 830          }
 831  
 832          return new pgsql_native_moodle_recordset($result, $this, $cursorname);
 833      }
 834  
 835      /**
 836       * Gets size of fetch buffer used for recordset queries.
 837       *
 838       * If this returns 0 then cursors will not be used, meaning recordset queries will occupy enough
 839       * memory as needed for the Postgres library to hold the entire query results in memory.
 840       *
 841       * @return int Fetch buffer size or 0 indicating not to use cursors
 842       */
 843      protected function get_fetch_buffer_size() {
 844          if (array_key_exists('fetchbuffersize', $this->dboptions)) {
 845              return (int)$this->dboptions['fetchbuffersize'];
 846          } else {
 847              return self::DEFAULT_FETCH_BUFFER_SIZE;
 848          }
 849      }
 850  
 851      /**
 852       * Retrieves data from cursor. For use by recordset only; do not call directly.
 853       *
 854       * Return value contains the next batch of Postgres data, and a boolean indicating if this is
 855       * definitely the last batch (if false, there may be more)
 856       *
 857       * @param string $cursorname Name of cursor to read from
 858       * @return array Array with 2 elements (next data batch and boolean indicating last batch)
 859       */
 860      public function fetch_from_cursor($cursorname) {
 861          $count = $this->get_fetch_buffer_size();
 862  
 863          $sql = 'FETCH ' . $count . ' FROM ' . $cursorname;
 864  
 865          $this->query_start($sql, [], SQL_QUERY_AUX);
 866          $result = pg_query($this->pgsql, $sql);
 867          $last = pg_num_rows($result) !== $count;
 868  
 869          $this->query_end($result);
 870  
 871          return [$result, $last];
 872      }
 873  
 874      /**
 875       * Closes a cursor. For use by recordset only; do not call directly.
 876       *
 877       * @param string $cursorname Name of cursor to close
 878       * @return bool True if we actually closed one, false if the transaction was cancelled
 879       */
 880      public function close_cursor($cursorname) {
 881          // If the transaction got cancelled, then ignore this request.
 882          $sql = 'CLOSE ' . $cursorname;
 883          $this->query_start($sql, [], SQL_QUERY_AUX);
 884          $result = pg_query($this->pgsql, $sql);
 885          $this->query_end($result);
 886          if ($result) {
 887              pg_free_result($result);
 888          }
 889          return true;
 890      }
 891  
 892      /**
 893       * Get a number of records as an array of objects using a SQL statement.
 894       *
 895       * Return value is like:
 896       * @see function get_records.
 897       *
 898       * @param string $sql the SQL select query to execute. The first column of this SELECT statement
 899       *   must be a unique value (usually the 'id' field), as it will be used as the key of the
 900       *   returned array.
 901       * @param array $params array of sql parameters
 902       * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
 903       * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
 904       * @return array of objects, or empty array if no records were found
 905       * @throws dml_exception A DML specific exception is thrown for any errors.
 906       */
 907      public function get_records_sql($sql, array $params = null, $limitfrom = 0, $limitnum = 0) {
 908          list($limitfrom, $limitnum) = $this->normalise_limit_from_num($limitfrom, $limitnum);
 909  
 910          if ($limitnum) {
 911              $sql .= " LIMIT $limitnum";
 912          }
 913          if ($limitfrom) {
 914              $sql .= " OFFSET $limitfrom";
 915          }
 916  
 917          list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
 918          $this->query_start($sql, $params, SQL_QUERY_SELECT);
 919          $result = pg_query_params($this->pgsql, $sql, $params);
 920          $this->query_end($result);
 921  
 922          // find out if there are any blobs
 923          $numfields = pg_num_fields($result);
 924          $blobs = array();
 925          for ($i = 0; $i < $numfields; $i++) {
 926              $type = pg_field_type($result, $i);
 927              if ($type == 'bytea') {
 928                  $blobs[] = pg_field_name($result, $i);
 929              }
 930          }
 931  
 932          $return = [];
 933          while ($row = pg_fetch_assoc($result)) {
 934              $id = reset($row);
 935              if ($blobs) {
 936                  foreach ($blobs as $blob) {
 937                      $row[$blob] = ($row[$blob] !== null ? pg_unescape_bytea($row[$blob]) : null);
 938                  }
 939              }
 940              if (isset($return[$id])) {
 941                  $colname = key($row);
 942                  debugging("Did you remember to make the first column something unique in your call to get_records? Duplicate value '$id' found in column '$colname'.", DEBUG_DEVELOPER);
 943              }
 944              $return[$id] = (object) $row;
 945          }
 946  
 947          return $return;
 948      }
 949  
 950      /**
 951       * Selects records and return values (first field) as an array using a SQL statement.
 952       *
 953       * @param string $sql The SQL query
 954       * @param array $params array of sql parameters
 955       * @return array of values
 956       * @throws dml_exception A DML specific exception is thrown for any errors.
 957       */
 958      public function get_fieldset_sql($sql, array $params=null) {
 959          list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
 960  
 961          $this->query_start($sql, $params, SQL_QUERY_SELECT);
 962          $result = pg_query_params($this->pgsql, $sql, $params);
 963          $this->query_end($result);
 964  
 965          $return = pg_fetch_all_columns($result, 0);
 966  
 967          if (pg_field_type($result, 0) == 'bytea') {
 968              foreach ($return as $key => $value) {
 969                  $return[$key] = ($value === null ? $value : pg_unescape_bytea($value));
 970              }
 971          }
 972  
 973          pg_free_result($result);
 974  
 975          return $return;
 976      }
 977  
 978      /**
 979       * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
 980       * @param string $table name
 981       * @param mixed $params data record as object or array
 982       * @param bool $returnit return it of inserted record
 983       * @param bool $bulk true means repeated inserts expected
 984       * @param bool $customsequence true if 'id' included in $params, disables $returnid
 985       * @return bool|int true or new id
 986       * @throws dml_exception A DML specific exception is thrown for any errors.
 987       */
 988      public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
 989          if (!is_array($params)) {
 990              $params = (array)$params;
 991          }
 992  
 993          $returning = "";
 994  
 995          if ($customsequence) {
 996              if (!isset($params['id'])) {
 997                  throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
 998              }
 999              $returnid = false;
1000          } else {
1001              if ($returnid) {
1002                  $returning = "RETURNING id";
1003                  unset($params['id']);
1004              } else {
1005                  unset($params['id']);
1006              }
1007          }
1008  
1009          if (empty($params)) {
1010              throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
1011          }
1012  
1013          $fields = implode(',', array_keys($params));
1014          $values = array();
1015          $i = 1;
1016          foreach ($params as $value) {
1017              $this->detect_objects($value);
1018              $values[] = "\$".$i++;
1019          }
1020          $values = implode(',', $values);
1021  
1022          $sql = "INSERT INTO {$this->prefix}$table ($fields) VALUES($values) $returning";
1023          $this->query_start($sql, $params, SQL_QUERY_INSERT);
1024          $result = pg_query_params($this->pgsql, $sql, $params);
1025          $this->query_end($result);
1026  
1027          if ($returning !== "") {
1028              $row = pg_fetch_assoc($result);
1029              $params['id'] = reset($row);
1030          }
1031          pg_free_result($result);
1032  
1033          if (!$returnid) {
1034              return true;
1035          }
1036  
1037          return (int)$params['id'];
1038      }
1039  
1040      /**
1041       * Insert a record into a table and return the "id" field if required.
1042       *
1043       * Some conversions and safety checks are carried out. Lobs are supported.
1044       * If the return ID isn't required, then this just reports success as true/false.
1045       * $data is an object containing needed data
1046       * @param string $table The database table to be inserted into
1047       * @param object|array $dataobject A data object with values for one or more fields in the record
1048       * @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.
1049       * @return bool|int true or new id
1050       * @throws dml_exception A DML specific exception is thrown for any errors.
1051       */
1052      public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
1053          $dataobject = (array)$dataobject;
1054  
1055          $columns = $this->get_columns($table);
1056          if (empty($columns)) {
1057              throw new dml_exception('ddltablenotexist', $table);
1058          }
1059  
1060          $cleaned = array();
1061  
1062          foreach ($dataobject as $field=>$value) {
1063              if ($field === 'id') {
1064                  continue;
1065              }
1066              if (!isset($columns[$field])) {
1067                  continue;
1068              }
1069              $column = $columns[$field];
1070              $cleaned[$field] = $this->normalise_value($column, $value);
1071          }
1072  
1073          return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
1074  
1075      }
1076  
1077      /**
1078       * Insert multiple records into database as fast as possible.
1079       *
1080       * Order of inserts is maintained, but the operation is not atomic,
1081       * use transactions if necessary.
1082       *
1083       * This method is intended for inserting of large number of small objects,
1084       * do not use for huge objects with text or binary fields.
1085       *
1086       * @since Moodle 2.7
1087       *
1088       * @param string $table  The database table to be inserted into
1089       * @param array|Traversable $dataobjects list of objects to be inserted, must be compatible with foreach
1090       * @return void does not return new record ids
1091       *
1092       * @throws coding_exception if data objects have different structure
1093       * @throws dml_exception A DML specific exception is thrown for any errors.
1094       */
1095      public function insert_records($table, $dataobjects) {
1096          if (!is_array($dataobjects) and !($dataobjects instanceof Traversable)) {
1097              throw new coding_exception('insert_records() passed non-traversable object');
1098          }
1099  
1100          // PostgreSQL does not seem to have problems with huge queries.
1101          $chunksize = 500;
1102          if (!empty($this->dboptions['bulkinsertsize'])) {
1103              $chunksize = (int)$this->dboptions['bulkinsertsize'];
1104          }
1105  
1106          $columns = $this->get_columns($table, true);
1107  
1108          $fields = null;
1109          $count = 0;
1110          $chunk = array();
1111          foreach ($dataobjects as $dataobject) {
1112              if (!is_array($dataobject) and !is_object($dataobject)) {
1113                  throw new coding_exception('insert_records() passed invalid record object');
1114              }
1115              $dataobject = (array)$dataobject;
1116              if ($fields === null) {
1117                  $fields = array_keys($dataobject);
1118                  $columns = array_intersect_key($columns, $dataobject);
1119                  unset($columns['id']);
1120              } else if ($fields !== array_keys($dataobject)) {
1121                  throw new coding_exception('All dataobjects in insert_records() must have the same structure!');
1122              }
1123  
1124              $count++;
1125              $chunk[] = $dataobject;
1126  
1127              if ($count === $chunksize) {
1128                  $this->insert_chunk($table, $chunk, $columns);
1129                  $chunk = array();
1130                  $count = 0;
1131              }
1132          }
1133  
1134          if ($count) {
1135              $this->insert_chunk($table, $chunk, $columns);
1136          }
1137      }
1138  
1139      /**
1140       * Insert records in chunks, strict param types...
1141       *
1142       * Note: can be used only from insert_records().
1143       *
1144       * @param string $table
1145       * @param array $chunk
1146       * @param database_column_info[] $columns
1147       */
1148      protected function insert_chunk($table, array $chunk, array $columns) {
1149          $i = 1;
1150          $params = array();
1151          $values = array();
1152          foreach ($chunk as $dataobject) {
1153              $vals = array();
1154              foreach ($columns as $field => $column) {
1155                  $params[] = $this->normalise_value($column, $dataobject[$field]);
1156                  $vals[] = "\$".$i++;
1157              }
1158              $values[] = '('.implode(',', $vals).')';
1159          }
1160  
1161          $fieldssql = '('.implode(',', array_keys($columns)).')';
1162          $valuessql = implode(',', $values);
1163  
1164          $sql = "INSERT INTO {$this->prefix}$table $fieldssql VALUES $valuessql";
1165          $this->query_start($sql, $params, SQL_QUERY_INSERT);
1166          $result = pg_query_params($this->pgsql, $sql, $params);
1167          $this->query_end($result);
1168          pg_free_result($result);
1169      }
1170  
1171      /**
1172       * Import a record into a table, id field is required.
1173       * Safety checks are NOT carried out. Lobs are supported.
1174       *
1175       * @param string $table name of database table to be inserted into
1176       * @param object $dataobject A data object with values for one or more fields in the record
1177       * @return bool true
1178       * @throws dml_exception A DML specific exception is thrown for any errors.
1179       */
1180      public function import_record($table, $dataobject) {
1181          $dataobject = (array)$dataobject;
1182  
1183          $columns = $this->get_columns($table);
1184          $cleaned = array();
1185  
1186          foreach ($dataobject as $field=>$value) {
1187              $this->detect_objects($value);
1188              if (!isset($columns[$field])) {
1189                  continue;
1190              }
1191              $column = $columns[$field];
1192              $cleaned[$field] = $this->normalise_value($column, $value);
1193          }
1194  
1195          return $this->insert_record_raw($table, $cleaned, false, true, true);
1196      }
1197  
1198      /**
1199       * Update record in database, as fast as possible, no safety checks, lobs not supported.
1200       * @param string $table name
1201       * @param mixed $params data record as object or array
1202       * @param bool true means repeated updates expected
1203       * @return bool true
1204       * @throws dml_exception A DML specific exception is thrown for any errors.
1205       */
1206      public function update_record_raw($table, $params, $bulk=false) {
1207          $params = (array)$params;
1208  
1209          if (!isset($params['id'])) {
1210              throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
1211          }
1212          $id = $params['id'];
1213          unset($params['id']);
1214  
1215          if (empty($params)) {
1216              throw new coding_exception('moodle_database::update_record_raw() no fields found.');
1217          }
1218  
1219          $i = 1;
1220  
1221          $sets = array();
1222          foreach ($params as $field=>$value) {
1223              $this->detect_objects($value);
1224              $sets[] = "$field = \$".$i++;
1225          }
1226  
1227          $params[] = $id; // last ? in WHERE condition
1228  
1229          $sets = implode(',', $sets);
1230          $sql = "UPDATE {$this->prefix}$table SET $sets WHERE id=\$".$i;
1231  
1232          $this->query_start($sql, $params, SQL_QUERY_UPDATE);
1233          $result = pg_query_params($this->pgsql, $sql, $params);
1234          $this->query_end($result);
1235  
1236          pg_free_result($result);
1237          return true;
1238      }
1239  
1240      /**
1241       * Update a record in a table
1242       *
1243       * $dataobject is an object containing needed data
1244       * Relies on $dataobject having a variable "id" to
1245       * specify the record to update
1246       *
1247       * @param string $table The database table to be checked against.
1248       * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
1249       * @param bool true means repeated updates expected
1250       * @return bool true
1251       * @throws dml_exception A DML specific exception is thrown for any errors.
1252       */
1253      public function update_record($table, $dataobject, $bulk=false) {
1254          $dataobject = (array)$dataobject;
1255  
1256          $columns = $this->get_columns($table);
1257          $cleaned = array();
1258  
1259          foreach ($dataobject as $field=>$value) {
1260              if (!isset($columns[$field])) {
1261                  continue;
1262              }
1263              $column = $columns[$field];
1264              $cleaned[$field] = $this->normalise_value($column, $value);
1265          }
1266  
1267          $this->update_record_raw($table, $cleaned, $bulk);
1268  
1269          return true;
1270      }
1271  
1272      /**
1273       * Set a single field in every table record which match a particular WHERE clause.
1274       *
1275       * @param string $table The database table to be checked against.
1276       * @param string $newfield the field to set.
1277       * @param string $newvalue the value to set the field to.
1278       * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1279       * @param array $params array of sql parameters
1280       * @return bool true
1281       * @throws dml_exception A DML specific exception is thrown for any errors.
1282       */
1283      public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) {
1284  
1285          if ($select) {
1286              $select = "WHERE $select";
1287          }
1288          if (is_null($params)) {
1289              $params = array();
1290          }
1291          list($select, $params, $type) = $this->fix_sql_params($select, $params);
1292          $i = count($params)+1;
1293  
1294          // Get column metadata
1295          $columns = $this->get_columns($table);
1296          $column = $columns[$newfield];
1297  
1298          $normalisedvalue = $this->normalise_value($column, $newvalue);
1299  
1300          $newfield = "$newfield = \$" . $i;
1301          $params[] = $normalisedvalue;
1302          $sql = "UPDATE {$this->prefix}$table SET $newfield $select";
1303  
1304          $this->query_start($sql, $params, SQL_QUERY_UPDATE);
1305          $result = pg_query_params($this->pgsql, $sql, $params);
1306          $this->query_end($result);
1307  
1308          pg_free_result($result);
1309  
1310          return true;
1311      }
1312  
1313      /**
1314       * Delete one or more records from a table which match a particular WHERE clause, lobs not supported.
1315       *
1316       * @param string $table The database table to be checked against.
1317       * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1318       * @param array $params array of sql parameters
1319       * @return bool true
1320       * @throws dml_exception A DML specific exception is thrown for any errors.
1321       */
1322      public function delete_records_select($table, $select, array $params=null) {
1323          if ($select) {
1324              $select = "WHERE $select";
1325          }
1326          $sql = "DELETE FROM {$this->prefix}$table $select";
1327  
1328          list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1329  
1330          $this->query_start($sql, $params, SQL_QUERY_UPDATE);
1331          $result = pg_query_params($this->pgsql, $sql, $params);
1332          $this->query_end($result);
1333  
1334          pg_free_result($result);
1335  
1336          return true;
1337      }
1338  
1339      /**
1340       * Returns 'LIKE' part of a query.
1341       *
1342       * @param string $fieldname usually name of the table column
1343       * @param string $param usually bound query parameter (?, :named)
1344       * @param bool $casesensitive use case sensitive search
1345       * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
1346       * @param bool $notlike true means "NOT LIKE"
1347       * @param string $escapechar escape char for '%' and '_'
1348       * @return string SQL code fragment
1349       */
1350      public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
1351          if (strpos($param, '%') !== false) {
1352              debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)');
1353          }
1354  
1355          // postgresql does not support accent insensitive text comparisons, sorry
1356          if ($casesensitive) {
1357              $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
1358          } else {
1359              $LIKE = $notlike ? 'NOT ILIKE' : 'ILIKE';
1360          }
1361          return "$fieldname $LIKE $param ESCAPE '$escapechar'";
1362      }
1363  
1364      public function sql_bitxor($int1, $int2) {
1365          return '((' . $int1 . ') # (' . $int2 . '))';
1366      }
1367  
1368      public function sql_cast_char2int($fieldname, $text=false) {
1369          return ' CAST(' . $fieldname . ' AS INT) ';
1370      }
1371  
1372      public function sql_cast_char2real($fieldname, $text=false) {
1373          return " $fieldname::real ";
1374      }
1375  
1376      public function sql_concat() {
1377          $arr = func_get_args();
1378          $s = implode(' || ', $arr);
1379          if ($s === '') {
1380              return " '' ";
1381          }
1382          // Add always empty string element so integer-exclusive concats
1383          // will work without needing to cast each element explicitly
1384          return " '' || $s ";
1385      }
1386  
1387      public function sql_concat_join($separator="' '", $elements=array()) {
1388          for ($n=count($elements)-1; $n > 0 ; $n--) {
1389              array_splice($elements, $n, 0, $separator);
1390          }
1391          $s = implode(' || ', $elements);
1392          if ($s === '') {
1393              return " '' ";
1394          }
1395          return " $s ";
1396      }
1397  
1398      public function sql_regex_supported() {
1399          return true;
1400      }
1401  
1402      public function sql_regex($positivematch = true, $casesensitive = false) {
1403          if ($casesensitive) {
1404              return $positivematch ? '~' : '!~';
1405          } else {
1406              return $positivematch ? '~*' : '!~*';
1407          }
1408      }
1409  
1410      /**
1411       * Does this driver support tool_replace?
1412       *
1413       * @since Moodle 2.6.1
1414       * @return bool
1415       */
1416      public function replace_all_text_supported() {
1417          return true;
1418      }
1419  
1420      public function session_lock_supported() {
1421          return true;
1422      }
1423  
1424      /**
1425       * Obtain session lock
1426       * @param int $rowid id of the row with session record
1427       * @param int $timeout max allowed time to wait for the lock in seconds
1428       * @return bool success
1429       */
1430      public function get_session_lock($rowid, $timeout) {
1431          // NOTE: there is a potential locking problem for database running
1432          //       multiple instances of moodle, we could try to use pg_advisory_lock(int, int),
1433          //       luckily there is not a big chance that they would collide
1434          if (!$this->session_lock_supported()) {
1435              return;
1436          }
1437  
1438          parent::get_session_lock($rowid, $timeout);
1439  
1440          $timeoutmilli = $timeout * 1000;
1441  
1442          $sql = "SET statement_timeout TO $timeoutmilli";
1443          $this->query_start($sql, null, SQL_QUERY_AUX);
1444          $result = pg_query($this->pgsql, $sql);
1445          $this->query_end($result);
1446  
1447          if ($result) {
1448              pg_free_result($result);
1449          }
1450  
1451          $sql = "SELECT pg_advisory_lock($rowid)";
1452          $this->query_start($sql, null, SQL_QUERY_AUX);
1453          $start = time();
1454          $result = pg_query($this->pgsql, $sql);
1455          $end = time();
1456          try {
1457              $this->query_end($result);
1458          } catch (dml_exception $ex) {
1459              if ($end - $start >= $timeout) {
1460                  throw new dml_sessionwait_exception();
1461              } else {
1462                  throw $ex;
1463              }
1464          }
1465  
1466          if ($result) {
1467              pg_free_result($result);
1468          }
1469  
1470          $sql = "SET statement_timeout TO DEFAULT";
1471          $this->query_start($sql, null, SQL_QUERY_AUX);
1472          $result = pg_query($this->pgsql, $sql);
1473          $this->query_end($result);
1474  
1475          if ($result) {
1476              pg_free_result($result);
1477          }
1478      }
1479  
1480      public function release_session_lock($rowid) {
1481          if (!$this->session_lock_supported()) {
1482              return;
1483          }
1484          if (!$this->used_for_db_sessions) {
1485              return;
1486          }
1487  
1488          parent::release_session_lock($rowid);
1489  
1490          $sql = "SELECT pg_advisory_unlock($rowid)";
1491          $this->query_start($sql, null, SQL_QUERY_AUX);
1492          $result = pg_query($this->pgsql, $sql);
1493          $this->query_end($result);
1494  
1495          if ($result) {
1496              pg_free_result($result);
1497          }
1498      }
1499  
1500      /**
1501       * Driver specific start of real database transaction,
1502       * this can not be used directly in code.
1503       * @return void
1504       */
1505      protected function begin_transaction() {
1506          $this->savepointpresent = true;
1507          $sql = "BEGIN ISOLATION LEVEL READ COMMITTED; SAVEPOINT moodle_pg_savepoint";
1508          $this->query_start($sql, null, SQL_QUERY_AUX);
1509          $result = pg_query($this->pgsql, $sql);
1510          $this->query_end($result);
1511  
1512          pg_free_result($result);
1513      }
1514  
1515      /**
1516       * Driver specific commit of real database transaction,
1517       * this can not be used directly in code.
1518       * @return void
1519       */
1520      protected function commit_transaction() {
1521          $this->savepointpresent = false;
1522          $sql = "RELEASE SAVEPOINT moodle_pg_savepoint; COMMIT";
1523          $this->query_start($sql, null, SQL_QUERY_AUX);
1524          $result = pg_query($this->pgsql, $sql);
1525          $this->query_end($result);
1526  
1527          pg_free_result($result);
1528      }
1529  
1530      /**
1531       * Driver specific abort of real database transaction,
1532       * this can not be used directly in code.
1533       * @return void
1534       */
1535      protected function rollback_transaction() {
1536          $this->savepointpresent = false;
1537          $sql = "RELEASE SAVEPOINT moodle_pg_savepoint; ROLLBACK";
1538          $this->query_start($sql, null, SQL_QUERY_AUX);
1539          $result = pg_query($this->pgsql, $sql);
1540          $this->query_end($result);
1541  
1542          pg_free_result($result);
1543      }
1544  
1545      /**
1546       * Helper function trimming (whitespace + quotes) any string
1547       * needed because PG uses to enclose with double quotes some
1548       * fields in indexes definition and others
1549       *
1550       * @param string $str string to apply whitespace + quotes trim
1551       * @return string trimmed string
1552       */
1553      private function trim_quotes($str) {
1554          return trim(trim($str), "'\"");
1555      }
1556  
1557      /**
1558       * Postgresql supports full-text search indexes.
1559       *
1560       * @return bool
1561       */
1562      public function is_fulltext_search_supported() {
1563          return true;
1564      }
1565  }