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