MDL-37420 add support for more PostgreSQL schemas
[moodle.git] / lib / dml / pgsql_native_moodle_database.php
CommitLineData
49926145 1<?php
49926145 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
49926145 17/**
18 * Native pgsql class representing moodle database interface.
19 *
00902cd9 20 * @package core_dml
49926145 21 * @copyright 2008 Petr Skoda (http://skodak.org)
22 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
23 */
158622bd 24
f2ed3f05
PS
25defined('MOODLE_INTERNAL') || die();
26
00902cd9
PS
27require_once(__DIR__.'/moodle_database.php');
28require_once(__DIR__.'/pgsql_native_moodle_recordset.php');
29require_once(__DIR__.'/pgsql_native_moodle_temptables.php');
158622bd 30
31/**
32 * Native pgsql class representing moodle database interface.
a0eb2e97 33 *
00902cd9 34 * @package core_dml
a0eb2e97
AB
35 * @copyright 2008 Petr Skoda (http://skodak.org)
36 * @license http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
158622bd 37 */
38class pgsql_native_moodle_database extends moodle_database {
39
db7aea38 40 protected $pgsql = null;
db7aea38 41 protected $bytea_oid = null;
158622bd 42
190ff43b 43 protected $last_error_reporting; // To handle pgsql driver default verbosity
ba98912f 44
158622bd 45 /**
46 * Detects if all needed PHP stuff installed.
47 * Note: can be used before connect()
48 * @return mixed true if ok, string if something
49 */
50 public function driver_installed() {
51 if (!extension_loaded('pgsql')) {
52 return get_string('pgsqlextensionisnotpresentinphp', 'install');
53 }
54 return true;
55 }
56
57 /**
58 * Returns database family type - describes SQL dialect
59 * Note: can be used before connect()
60 * @return string db family name (mysql, postgres, mssql, oracle, etc.)
61 */
62 public function get_dbfamily() {
63 return 'postgres';
64 }
65
66 /**
67 * Returns more specific database driver type
68 * Note: can be used before connect()
4a6c2ab8 69 * @return string db type mysqli, pgsql, oci, mssql, sqlsrv
158622bd 70 */
71 protected function get_dbtype() {
72 return 'pgsql';
73 }
74
75 /**
76 * Returns general database library name
77 * Note: can be used before connect()
eb123deb 78 * @return string db type pdo, native
158622bd 79 */
80 protected function get_dblibrary() {
81 return 'native';
82 }
83
84 /**
85 * Returns localised database type name
86 * Note: can be used before connect()
87 * @return string
88 */
89 public function get_name() {
bba50616 90 return get_string('nativepgsql', 'install');
158622bd 91 }
92
3b093310 93 /**
94 * Returns localised database configuration help.
95 * Note: can be used before connect()
96 * @return string
97 */
98 public function get_configuration_help() {
99 return get_string('nativepgsqlhelp', 'install');
100 }
101
158622bd 102 /**
103 * Returns localised database description
104 * Note: can be used before connect()
105 * @return string
106 */
107 public function get_configuration_hints() {
bba50616 108 return get_string('databasesettingssub_postgres7', 'install');
158622bd 109 }
110
111 /**
112 * Connect to db
113 * Must be called before other methods.
6df26010
AB
114 * @param string $dbhost The database host.
115 * @param string $dbuser The database username.
116 * @param string $dbpass The database username's password.
117 * @param string $dbname The name of the database being connected to.
158622bd 118 * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
119 * @param array $dboptions driver specific options
ce152606 120 * @return bool true
121 * @throws dml_connection_exception if error
158622bd 122 */
beaa43db 123 public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {
189f3ee9 124 if ($prefix == '' and !$this->external) {
125 //Enforce prefixes for everybody but mysql
126 throw new dml_exception('prefixcannotbeempty', $this->get_dbfamily());
127 }
128
ce152606 129 $driverstatus = $this->driver_installed();
130
131 if ($driverstatus !== true) {
132 throw new dml_exception('dbdriverproblem', $driverstatus);
133 }
134
beaa43db 135 $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
158622bd 136
dd2ce459 137 $pass = addcslashes($this->dbpass, "'\\");
138
139 // Unix socket connections should have lower overhead
e6b854a0 140 if (!empty($this->dboptions['dbsocket']) and ($this->dbhost === 'localhost' or $this->dbhost === '127.0.0.1')) {
dd2ce459 141 $connection = "user='$this->dbuser' password='$pass' dbname='$this->dbname'";
3c1293fe
PS
142 if (strpos($this->dboptions['dbsocket'], '/') !== false) {
143 $connection = $connection." host='".$this->dboptions['dbsocket']."'";
144 }
dd2ce459 145 } else {
3c1293fe 146 $this->dboptions['dbsocket'] = '';
e6b854a0 147 if (empty($this->dbname)) {
148 // probably old style socket connection - do not add port
149 $port = "";
150 } else if (empty($this->dboptions['dbport'])) {
151 $port = "port ='5432'";
d139b067 152 } else {
e6b854a0 153 $port = "port ='".$this->dboptions['dbport']."'";
d139b067 154 }
e6b854a0 155 $connection = "host='$this->dbhost' $port user='$this->dbuser' password='$pass' dbname='$this->dbname'";
dd2ce459 156 }
157
ce152606 158 ob_start();
a0b7200d 159 if (empty($this->dboptions['dbpersist'])) {
dd2ce459 160 $this->pgsql = pg_connect($connection, PGSQL_CONNECT_FORCE_NEW);
161 } else {
162 $this->pgsql = pg_pconnect($connection, PGSQL_CONNECT_FORCE_NEW);
163 }
ce152606 164 $dberr = ob_get_contents();
165 ob_end_clean();
117bd748 166
158622bd 167 $status = pg_connection_status($this->pgsql);
9a4f9e33 168
169 if ($status === false or $status === PGSQL_CONNECTION_BAD) {
158622bd 170 $this->pgsql = null;
ce152606 171 throw new dml_connection_exception($dberr);
158622bd 172 }
9a4f9e33 173
1500142b 174 $this->query_start("--pg_set_client_encoding()", null, SQL_QUERY_AUX);
158622bd 175 pg_set_client_encoding($this->pgsql, 'utf8');
1500142b 176 $this->query_end(true);
177
97d00c9f
EL
178 $sql = '';
179 // Only for 9.0 and upwards, set bytea encoding to old format.
180 if ($this->is_min_version('9.0')) {
181 $sql = "SET bytea_output = 'escape'; ";
182 }
183
090c6848
PS
184 // Select schema if specified, otherwise the first one wins.
185 if (isset($this->dboptions['dbschema'])) {
186 $sql .= "SET search_path = '".$this->dboptions['dbschema']."'; ";
187 }
188
97d00c9f
EL
189 // Find out the bytea oid.
190 $sql .= "SELECT oid FROM pg_type WHERE typname = 'bytea'";
1500142b 191 $this->query_start($sql, null, SQL_QUERY_AUX);
db7aea38 192 $result = pg_query($this->pgsql, $sql);
1500142b 193 $this->query_end($result);
ce152606 194
b6265b7e 195 $this->bytea_oid = pg_fetch_result($result, 0, 0);
db7aea38 196 pg_free_result($result);
197 if ($this->bytea_oid === false) {
ce152606 198 $this->pgsql = null;
199 throw new dml_connection_exception('Can not read bytea type.');
db7aea38 200 }
1500142b 201
b6265b7e 202 // Connection stabilised and configured, going to instantiate the temptables controller
10f375aa
EL
203 $this->temptables = new pgsql_native_moodle_temptables($this);
204
158622bd 205 return true;
206 }
207
208 /**
209 * Close database connection and release all resources
210 * and memory (especially circular memory references).
211 * Do NOT use connect() again, create a new instance if needed.
212 */
213 public function dispose() {
2434f624 214 parent::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection
158622bd 215 if ($this->pgsql) {
216 pg_close($this->pgsql);
217 $this->pgsql = null;
218 }
158622bd 219 }
220
ba98912f 221
222 /**
223 * Called before each db query.
224 * @param string $sql
225 * @param array array of parameters
226 * @param int $type type of query
227 * @param mixed $extrainfo driver specific extra information
228 * @return void
229 */
230 protected function query_start($sql, array $params=null, $type, $extrainfo=null) {
231 parent::query_start($sql, $params, $type, $extrainfo);
232 // pgsql driver tents to send debug to output, we do not need that ;-)
190ff43b 233 $this->last_error_reporting = error_reporting(0);
ba98912f 234 }
235
236 /**
237 * Called immediately after each db query.
238 * @param mixed db specific result
239 * @return void
240 */
241 protected function query_end($result) {
190ff43b 242 // reset original debug level
243 error_reporting($this->last_error_reporting);
ba98912f 244 parent::query_end($result);
245 }
246
158622bd 247 /**
248 * Returns database server info array
6df26010 249 * @return array Array containing 'description' and 'version' info
158622bd 250 */
251 public function get_server_info() {
252 static $info;
253 if (!$info) {
1500142b 254 $this->query_start("--pg_version()", null, SQL_QUERY_AUX);
158622bd 255 $info = pg_version($this->pgsql);
1500142b 256 $this->query_end(true);
158622bd 257 }
258 return array('description'=>$info['server'], 'version'=>$info['server']);
259 }
260
7b74fb70
EL
261 /**
262 * Returns if the RDBMS server fulfills the required version
263 *
264 * @param string $version version to check against
265 * @return bool returns if the version is fulfilled (true) or no (false)
7b74fb70 266 */
54c5b1ad 267 private function is_min_version($version) {
158622bd 268 $server = $this->get_server_info();
269 $server = $server['version'];
270 return version_compare($server, $version, '>=');
271 }
272
273 /**
274 * Returns supported query parameter types
6df26010 275 * @return int bitmask of accepted SQL_PARAMS_*
158622bd 276 */
277 protected function allowed_param_types() {
278 return SQL_PARAMS_DOLLAR;
279 }
280
281 /**
282 * Returns last error reported by database engine.
9331d879 283 * @return string error message
158622bd 284 */
285 public function get_last_error() {
286 return pg_last_error($this->pgsql);
287 }
288
289 /**
6df26010
AB
290 * Return tables in database WITHOUT current prefix.
291 * @param bool $usecache if true, returns list of cached tables.
158622bd 292 * @return array of table names in lowercase and without prefix
293 */
117679db 294 public function get_tables($usecache=true) {
295 if ($usecache and $this->tables !== null) {
296 return $this->tables;
297 }
298 $this->tables = array();
d5b3deec 299 $prefix = str_replace('_', '|_', $this->prefix);
090c6848
PS
300 $sql = "SELECT c.relname
301 FROM pg_catalog.pg_class c
302 JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace
303 WHERE c.relname LIKE '$prefix%' ESCAPE '|'
b20b1c32 304 AND c.relkind = 'r'
090c6848 305 AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())";
1500142b 306 $this->query_start($sql, null, SQL_QUERY_AUX);
307 $result = pg_query($this->pgsql, $sql);
308 $this->query_end($result);
d139b067 309
1500142b 310 if ($result) {
158622bd 311 while ($row = pg_fetch_row($result)) {
312 $tablename = reset($row);
62cd8cd9
PS
313 if ($this->prefix !== '') {
314 if (strpos($tablename, $this->prefix) !== 0) {
315 continue;
316 }
317 $tablename = substr($tablename, strlen($this->prefix));
158622bd 318 }
117679db 319 $this->tables[$tablename] = $tablename;
158622bd 320 }
321 pg_free_result($result);
322 }
117679db 323 return $this->tables;
158622bd 324 }
325
326 /**
6df26010
AB
327 * Return table indexes - everything lowercased.
328 * @param string $table The table we want to get indexes from.
158622bd 329 * @return array of arrays
330 */
331 public function get_indexes($table) {
332 $indexes = array();
333 $tablename = $this->prefix.$table;
334
090c6848
PS
335 $sql = "SELECT i.*
336 FROM pg_catalog.pg_indexes i
337 JOIN pg_catalog.pg_namespace as ns ON ns.nspname = i.schemaname
338 WHERE i.tablename = '$tablename'
339 AND (i.schemaname = current_schema() OR ns.oid = pg_my_temp_schema())";
1500142b 340
341 $this->query_start($sql, null, SQL_QUERY_AUX);
342 $result = pg_query($this->pgsql, $sql);
343 $this->query_end($result);
344
345 if ($result) {
158622bd 346 while ($row = pg_fetch_assoc($result)) {
347 if (!preg_match('/CREATE (|UNIQUE )INDEX ([^\s]+) ON '.$tablename.' USING ([^\s]+) \(([^\)]+)\)/i', $row['indexdef'], $matches)) {
348 continue;
349 }
350 if ($matches[4] === 'id') {
351 continue;
352 }
80ffbad3 353 $columns = explode(',', $matches[4]);
d64514aa 354 $columns = array_map(array($this, 'trim_quotes'), $columns);
471dc4d3 355 $indexes[$row['indexname']] = array('unique'=>!empty($matches[1]),
80ffbad3 356 'columns'=>$columns);
158622bd 357 }
358 pg_free_result($result);
359 }
360 return $indexes;
361 }
362
363 /**
2434f624 364 * Returns detailed information about columns in table. This information is cached internally.
158622bd 365 * @param string $table name
366 * @param bool $usecache
367 * @return array array of database_column_info objects indexed with column names
368 */
369 public function get_columns($table, $usecache=true) {
370 if ($usecache and isset($this->columns[$table])) {
371 return $this->columns[$table];
372 }
373
374 $this->columns[$table] = array();
375
376 $tablename = $this->prefix.$table;
377
378 $sql = "SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, d.adsrc
379 FROM pg_catalog.pg_class c
090c6848 380 JOIN pg_catalog.pg_namespace as ns ON ns.oid = c.relnamespace
9a4f9e33 381 JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
158622bd 382 JOIN pg_catalog.pg_type t ON t.oid = a.atttypid
383 LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = c.oid AND d.adnum = a.attnum)
384 WHERE relkind = 'r' AND c.relname = '$tablename' AND c.reltype > 0 AND a.attnum > 0
090c6848 385 AND (ns.nspname = current_schema() OR ns.oid = pg_my_temp_schema())
158622bd 386 ORDER BY a.attnum";
387
1500142b 388 $this->query_start($sql, null, SQL_QUERY_AUX);
389 $result = pg_query($this->pgsql, $sql);
390 $this->query_end($result);
391
392 if (!$result) {
158622bd 393 return array();
394 }
395 while ($rawcolumn = pg_fetch_object($result)) {
396
ac6f1a82 397 $info = new stdClass();
158622bd 398 $info->name = $rawcolumn->field;
399 $matches = null;
400
401 if ($rawcolumn->type === 'varchar') {
158622bd 402 $info->type = 'varchar';
403 $info->meta_type = 'C';
404 $info->max_length = $rawcolumn->atttypmod - 4;
405 $info->scale = null;
298d9250 406 $info->not_null = ($rawcolumn->attnotnull === 't');
407 $info->has_default = ($rawcolumn->atthasdef === 't');
158622bd 408 if ($info->has_default) {
409 $parts = explode('::', $rawcolumn->adsrc);
410 if (count($parts) > 1) {
411 $info->default_value = reset($parts);
412 $info->default_value = trim($info->default_value, "'");
413 } else {
414 $info->default_value = $rawcolumn->adsrc;
415 }
416 } else {
417 $info->default_value = null;
418 }
419 $info->primary_key = false;
420 $info->binary = false;
421 $info->unsigned = null;
422 $info->auto_increment= false;
423 $info->unique = null;
424
425 } else if (preg_match('/int(\d)/i', $rawcolumn->type, $matches)) {
426 $info->type = 'int';
427 if (strpos($rawcolumn->adsrc, 'nextval') === 0) {
428 $info->primary_key = true;
429 $info->meta_type = 'R';
430 $info->unique = true;
431 $info->auto_increment= true;
432 $info->has_default = false;
433 } else {
434 $info->primary_key = false;
435 $info->meta_type = 'I';
436 $info->unique = null;
437 $info->auto_increment= false;
298d9250 438 $info->has_default = ($rawcolumn->atthasdef === 't');
158622bd 439 }
440 $info->max_length = $matches[1];
441 $info->scale = null;
298d9250 442 $info->not_null = ($rawcolumn->attnotnull === 't');
158622bd 443 if ($info->has_default) {
69857fa0 444 $info->default_value = trim($rawcolumn->adsrc, '()');
158622bd 445 } else {
446 $info->default_value = null;
447 }
448 $info->binary = false;
449 $info->unsigned = false;
450
451 } else if ($rawcolumn->type === 'numeric') {
452 $info->type = $rawcolumn->type;
453 $info->meta_type = 'N';
454 $info->primary_key = false;
455 $info->binary = false;
456 $info->unsigned = null;
457 $info->auto_increment= false;
458 $info->unique = null;
298d9250 459 $info->not_null = ($rawcolumn->attnotnull === 't');
460 $info->has_default = ($rawcolumn->atthasdef === 't');
158622bd 461 if ($info->has_default) {
69857fa0 462 $info->default_value = trim($rawcolumn->adsrc, '()');
158622bd 463 } else {
464 $info->default_value = null;
465 }
466 $info->max_length = $rawcolumn->atttypmod >> 16;
467 $info->scale = ($rawcolumn->atttypmod & 0xFFFF) - 4;
468
469 } else if (preg_match('/float(\d)/i', $rawcolumn->type, $matches)) {
470 $info->type = 'float';
471 $info->meta_type = 'N';
472 $info->primary_key = false;
473 $info->binary = false;
474 $info->unsigned = null;
475 $info->auto_increment= false;
476 $info->unique = null;
298d9250 477 $info->not_null = ($rawcolumn->attnotnull === 't');
478 $info->has_default = ($rawcolumn->atthasdef === 't');
158622bd 479 if ($info->has_default) {
69857fa0 480 $info->default_value = trim($rawcolumn->adsrc, '()');
158622bd 481 } else {
482 $info->default_value = null;
483 }
484 // just guess expected number of deciaml places :-(
485 if ($matches[1] == 8) {
486 // total 15 digits
487 $info->max_length = 8;
488 $info->scale = 7;
489 } else {
490 // total 6 digits
491 $info->max_length = 4;
492 $info->scale = 2;
493 }
494
495 } else if ($rawcolumn->type === 'text') {
496 $info->type = $rawcolumn->type;
497 $info->meta_type = 'X';
498 $info->max_length = -1;
499 $info->scale = null;
298d9250 500 $info->not_null = ($rawcolumn->attnotnull === 't');
501 $info->has_default = ($rawcolumn->atthasdef === 't');
158622bd 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 ($rawcolumn->type === 'bytea') {
520 $info->type = $rawcolumn->type;
521 $info->meta_type = 'B';
522 $info->max_length = -1;
523 $info->scale = null;
298d9250 524 $info->not_null = ($rawcolumn->attnotnull === 't');
158622bd 525 $info->has_default = false;
526 $info->default_value = null;
527 $info->primary_key = false;
528 $info->binary = true;
529 $info->unsigned = null;
530 $info->auto_increment= false;
531 $info->unique = null;
532
533 }
534
535 $this->columns[$table][$info->name] = new database_column_info($info);
536 }
537
e4f9c142 538 pg_free_result($result);
539
158622bd 540 return $this->columns[$table];
541 }
542
8fb501e9
EL
543 /**
544 * Normalise values based in RDBMS dependencies (booleans, LOBs...)
545 *
546 * @param database_column_info $column column metadata corresponding with the value we are going to normalise
547 * @param mixed $value value we are going to normalise
548 * @return mixed the normalised value
549 */
e3acc8af 550 protected function normalise_value($column, $value) {
e618cdf3
PS
551 $this->detect_objects($value);
552
8fb501e9
EL
553 if (is_bool($value)) { // Always, convert boolean to int
554 $value = (int)$value;
555
f3e317dc 556 } else if ($column->meta_type === 'B') { // BLOB detected, we return 'blob' array instead of raw value to allow
8fb501e9
EL
557 if (!is_null($value)) { // binding/executing code later to know about its nature
558 $value = array('blob' => $value);
559 }
560
561 } else if ($value === '') {
f3e317dc 562 if ($column->meta_type === 'I' or $column->meta_type === 'F' or $column->meta_type === 'N') {
8fb501e9
EL
563 $value = 0; // prevent '' problems in numeric fields
564 }
565 }
566 return $value;
567 }
568
158622bd 569 /**
570 * Is db in unicode mode?
571 * @return bool
572 */
573 public function setup_is_unicodedb() {
00902cd9 574 // Get PostgreSQL server_encoding value
1500142b 575 $sql = "SHOW server_encoding";
576 $this->query_start($sql, null, SQL_QUERY_AUX);
577 $result = pg_query($this->pgsql, $sql);
578 $this->query_end($result);
579
580 if (!$result) {
158622bd 581 return false;
582 }
583 $rawcolumn = pg_fetch_object($result);
584 $encoding = $rawcolumn->server_encoding;
585 pg_free_result($result);
586
587 return (strtoupper($encoding) == 'UNICODE' || strtoupper($encoding) == 'UTF8');
588 }
589
158622bd 590 /**
591 * Do NOT use in code, to be used by database_manager only!
592 * @param string $sql query
22d77567 593 * @return bool true
6df26010 594 * @throws dml_exception A DML specific exception is thrown for any errors.
158622bd 595 */
596 public function change_database_structure($sql) {
117679db 597 $this->reset_caches();
1500142b 598
599 $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
600 $result = pg_query($this->pgsql, $sql);
601 $this->query_end($result);
602
e4f9c142 603 pg_free_result($result);
158622bd 604 return true;
605 }
606
607 /**
608 * Execute general sql query. Should be used only when no other method suitable.
a681b6c0 609 * Do NOT use this to make changes in db structure, use database_manager methods instead!
158622bd 610 * @param string $sql query
611 * @param array $params query parameters
22d77567 612 * @return bool true
6df26010 613 * @throws dml_exception A DML specific exception is thrown for any errors.
158622bd 614 */
615 public function execute($sql, array $params=null) {
616 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
617
618 if (strpos($sql, ';') !== false) {
22d77567 619 throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
158622bd 620 }
621
1500142b 622 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
158622bd 623 $result = pg_query_params($this->pgsql, $sql, $params);
1500142b 624 $this->query_end($result);
158622bd 625
e4f9c142 626 pg_free_result($result);
158622bd 627 return true;
628 }
629
630 /**
631 * Get a number of records as a moodle_recordset using a SQL statement.
632 *
633 * Since this method is a little less readable, use of it should be restricted to
634 * code where it's possible there might be large datasets being returned. For known
635 * small datasets use get_records_sql - it leads to simpler code.
636 *
dafa20e8
AB
637 * The return type is like:
638 * @see function get_recordset.
158622bd 639 *
640 * @param string $sql the SQL select query to execute.
641 * @param array $params array of sql parameters
642 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
643 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
5212b07f 644 * @return moodle_recordset instance
6df26010 645 * @throws dml_exception A DML specific exception is thrown for any errors.
158622bd 646 */
647 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
088a4df8 648 $limitfrom = (int)$limitfrom;
649 $limitnum = (int)$limitnum;
3ff8bf26 650 $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom;
651 $limitnum = ($limitnum < 0) ? 0 : $limitnum;
158622bd 652 if ($limitfrom or $limitnum) {
158622bd 653 if ($limitnum < 1) {
72a3902f 654 $limitnum = "ALL";
4335aa29
PS
655 } else if (PHP_INT_MAX - $limitnum < $limitfrom) {
656 // this is a workaround for weird max int problem
657 $limitnum = "ALL";
158622bd 658 }
659 $sql .= " LIMIT $limitnum OFFSET $limitfrom";
660 }
661
662 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
663
1500142b 664 $this->query_start($sql, $params, SQL_QUERY_SELECT);
158622bd 665 $result = pg_query_params($this->pgsql, $sql, $params);
1500142b 666 $this->query_end($result);
158622bd 667
158622bd 668 return $this->create_recordset($result);
669 }
670
671 protected function create_recordset($result) {
db7aea38 672 return new pgsql_native_moodle_recordset($result, $this->bytea_oid);
158622bd 673 }
674
675 /**
676 * Get a number of records as an array of objects using a SQL statement.
677 *
dafa20e8
AB
678 * Return value is like:
679 * @see function get_records.
158622bd 680 *
681 * @param string $sql the SQL select query to execute. The first column of this SELECT statement
682 * must be a unique value (usually the 'id' field), as it will be used as the key of the
683 * returned array.
684 * @param array $params array of sql parameters
685 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
686 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
3503dcad 687 * @return array of objects, or empty array if no records were found
6df26010 688 * @throws dml_exception A DML specific exception is thrown for any errors.
158622bd 689 */
690 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
088a4df8 691 $limitfrom = (int)$limitfrom;
692 $limitnum = (int)$limitnum;
3ff8bf26 693 $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom;
694 $limitnum = ($limitnum < 0) ? 0 : $limitnum;
158622bd 695 if ($limitfrom or $limitnum) {
158622bd 696 if ($limitnum < 1) {
72a3902f 697 $limitnum = "ALL";
4335aa29
PS
698 } else if (PHP_INT_MAX - $limitnum < $limitfrom) {
699 // this is a workaround for weird max int problem
700 $limitnum = "ALL";
158622bd 701 }
702 $sql .= " LIMIT $limitnum OFFSET $limitfrom";
703 }
704
705 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1500142b 706 $this->query_start($sql, $params, SQL_QUERY_SELECT);
158622bd 707 $result = pg_query_params($this->pgsql, $sql, $params);
1500142b 708 $this->query_end($result);
158622bd 709
db7aea38 710 // find out if there are any blobs
711 $numrows = pg_num_fields($result);
712 $blobs = array();
713 for($i=0; $i<$numrows; $i++) {
714 $type_oid = pg_field_type_oid($result, $i);
715 if ($type_oid == $this->bytea_oid) {
716 $blobs[] = pg_field_name($result, $i);
717 }
718 }
158622bd 719
720 $rows = pg_fetch_all($result);
721 pg_free_result($result);
722
723 $return = array();
724 if ($rows) {
725 foreach ($rows as $row) {
726 $id = reset($row);
db7aea38 727 if ($blobs) {
728 foreach ($blobs as $blob) {
cda6d44b 729 // note: in PostgreSQL 9.0 the returned blobs are hexencoded by default - see http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-BYTEA-OUTPUT
2b4cf032 730 $row[$blob] = $row[$blob] !== null ? pg_unescape_bytea($row[$blob]) : null;
db7aea38 731 }
732 }
758ba89a 733 if (isset($return[$id])) {
734 $colname = key($row);
735 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);
736 }
158622bd 737 $return[$id] = (object)$row;
738 }
739 }
db7aea38 740
158622bd 741 return $return;
742 }
743
744 /**
745 * Selects records and return values (first field) as an array using a SQL statement.
746 *
747 * @param string $sql The SQL query
748 * @param array $params array of sql parameters
3503dcad 749 * @return array of values
6df26010 750 * @throws dml_exception A DML specific exception is thrown for any errors.
158622bd 751 */
752 public function get_fieldset_sql($sql, array $params=null) {
753 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
754
1500142b 755 $this->query_start($sql, $params, SQL_QUERY_SELECT);
158622bd 756 $result = pg_query_params($this->pgsql, $sql, $params);
1500142b 757 $this->query_end($result);
158622bd 758
158622bd 759 $return = pg_fetch_all_columns($result, 0);
760 pg_free_result($result);
9a4f9e33 761
158622bd 762 return $return;
763 }
764
765 /**
766 * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
767 * @param string $table name
768 * @param mixed $params data record as object or array
769 * @param bool $returnit return it of inserted record
770 * @param bool $bulk true means repeated inserts expected
771 * @param bool $customsequence true if 'id' included in $params, disables $returnid
3503dcad 772 * @return bool|int true or new id
6df26010 773 * @throws dml_exception A DML specific exception is thrown for any errors.
158622bd 774 */
775 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
776 if (!is_array($params)) {
777 $params = (array)$params;
778 }
779
780 $returning = "";
781
782 if ($customsequence) {
783 if (!isset($params['id'])) {
22d77567 784 throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
158622bd 785 }
786 $returnid = false;
787 } else {
788 if ($returnid) {
4b5732f9 789 $returning = "RETURNING id";
790 unset($params['id']);
158622bd 791 } else {
792 unset($params['id']);
793 }
794 }
795
796 if (empty($params)) {
22d77567 797 throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
158622bd 798 }
799
800 $fields = implode(',', array_keys($params));
801 $values = array();
e618cdf3
PS
802 $i = 1;
803 foreach ($params as $value) {
804 $this->detect_objects($value);
805 $values[] = "\$".$i++;
9a4f9e33 806 }
158622bd 807 $values = implode(',', $values);
808
809 $sql = "INSERT INTO {$this->prefix}$table ($fields) VALUES($values) $returning";
1500142b 810 $this->query_start($sql, $params, SQL_QUERY_INSERT);
158622bd 811 $result = pg_query_params($this->pgsql, $sql, $params);
1500142b 812 $this->query_end($result);
158622bd 813
158622bd 814 if ($returning !== "") {
815 $row = pg_fetch_assoc($result);
816 $params['id'] = reset($row);
158622bd 817 }
e4f9c142 818 pg_free_result($result);
158622bd 819
820 if (!$returnid) {
821 return true;
822 }
823
824 return (int)$params['id'];
825 }
826
827 /**
828 * Insert a record into a table and return the "id" field if required.
829 *
830 * Some conversions and safety checks are carried out. Lobs are supported.
831 * If the return ID isn't required, then this just reports success as true/false.
832 * $data is an object containing needed data
833 * @param string $table The database table to be inserted into
834 * @param object $data A data object with values for one or more fields in the record
835 * @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.
3503dcad 836 * @return bool|int true or new id
6df26010 837 * @throws dml_exception A DML specific exception is thrown for any errors.
158622bd 838 */
839 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
d8fa8e40 840 $dataobject = (array)$dataobject;
158622bd 841
842 $columns = $this->get_columns($table);
158622bd 843 $cleaned = array();
844 $blobs = array();
845
846 foreach ($dataobject as $field=>$value) {
d8fa8e40
PS
847 if ($field === 'id') {
848 continue;
849 }
158622bd 850 if (!isset($columns[$field])) {
851 continue;
852 }
853 $column = $columns[$field];
8fb501e9
EL
854 $normalised_value = $this->normalise_value($column, $value);
855 if (is_array($normalised_value) && array_key_exists('blob', $normalised_value)) {
856 $cleaned[$field] = '@#BLOB#@';
857 $blobs[$field] = $normalised_value['blob'];
858 } else {
859 $cleaned[$field] = $normalised_value;
158622bd 860 }
158622bd 861 }
862
158622bd 863 if (empty($blobs)) {
864 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
865 }
866
22d77567 867 $id = $this->insert_record_raw($table, $cleaned, true, $bulk);
158622bd 868
869 foreach ($blobs as $key=>$value) {
158622bd 870 $value = pg_escape_bytea($this->pgsql, $value);
871 $sql = "UPDATE {$this->prefix}$table SET $key = '$value'::bytea WHERE id = $id";
1500142b 872 $this->query_start($sql, NULL, SQL_QUERY_UPDATE);
e4f9c142 873 $result = pg_query($this->pgsql, $sql);
1500142b 874 $this->query_end($result);
e4f9c142 875 if ($result !== false) {
876 pg_free_result($result);
877 }
158622bd 878 }
879
880 return ($returnid ? $id : true);
881
882 }
883
884 /**
885 * Import a record into a table, id field is required.
886 * Safety checks are NOT carried out. Lobs are supported.
887 *
888 * @param string $table name of database table to be inserted into
889 * @param object $dataobject A data object with values for one or more fields in the record
22d77567 890 * @return bool true
6df26010 891 * @throws dml_exception A DML specific exception is thrown for any errors.
158622bd 892 */
893 public function import_record($table, $dataobject) {
d8fa8e40 894 $dataobject = (array)$dataobject;
158622bd 895
158622bd 896 $columns = $this->get_columns($table);
897 $cleaned = array();
f3e317dc 898 $blobs = array();
158622bd 899
900 foreach ($dataobject as $field=>$value) {
e618cdf3 901 $this->detect_objects($value);
158622bd 902 if (!isset($columns[$field])) {
903 continue;
904 }
f3e317dc
PS
905 if ($columns[$field]->meta_type === 'B') {
906 if (!is_null($value)) {
907 $cleaned[$field] = '@#BLOB#@';
908 $blobs[$field] = $value;
909 continue;
910 }
911 }
912
158622bd 913 $cleaned[$field] = $value;
914 }
915
f3e317dc 916 $this->insert_record_raw($table, $cleaned, false, true, true);
992c19a7 917 $id = $dataobject['id'];
f3e317dc
PS
918
919 foreach ($blobs as $key=>$value) {
920 $value = pg_escape_bytea($this->pgsql, $value);
921 $sql = "UPDATE {$this->prefix}$table SET $key = '$value'::bytea WHERE id = $id";
922 $this->query_start($sql, NULL, SQL_QUERY_UPDATE);
923 $result = pg_query($this->pgsql, $sql);
924 $this->query_end($result);
925 if ($result !== false) {
926 pg_free_result($result);
927 }
928 }
929
930 return true;
158622bd 931 }
932
933 /**
934 * Update record in database, as fast as possible, no safety checks, lobs not supported.
935 * @param string $table name
936 * @param mixed $params data record as object or array
937 * @param bool true means repeated updates expected
22d77567 938 * @return bool true
6df26010 939 * @throws dml_exception A DML specific exception is thrown for any errors.
158622bd 940 */
941 public function update_record_raw($table, $params, $bulk=false) {
d8fa8e40
PS
942 $params = (array)$params;
943
158622bd 944 if (!isset($params['id'])) {
22d77567 945 throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
158622bd 946 }
947 $id = $params['id'];
948 unset($params['id']);
949
950 if (empty($params)) {
22d77567 951 throw new coding_exception('moodle_database::update_record_raw() no fields found.');
158622bd 952 }
953
954 $i = 1;
955
956 $sets = array();
957 foreach ($params as $field=>$value) {
e618cdf3 958 $this->detect_objects($value);
158622bd 959 $sets[] = "$field = \$".$i++;
960 }
961
962 $params[] = $id; // last ? in WHERE condition
963
964 $sets = implode(',', $sets);
965 $sql = "UPDATE {$this->prefix}$table SET $sets WHERE id=\$".$i;
966
1500142b 967 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
158622bd 968 $result = pg_query_params($this->pgsql, $sql, $params);
1500142b 969 $this->query_end($result);
158622bd 970
e4f9c142 971 pg_free_result($result);
158622bd 972 return true;
973 }
974
975 /**
976 * Update a record in a table
977 *
978 * $dataobject is an object containing needed data
979 * Relies on $dataobject having a variable "id" to
980 * specify the record to update
981 *
982 * @param string $table The database table to be checked against.
983 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
984 * @param bool true means repeated updates expected
22d77567 985 * @return bool true
6df26010 986 * @throws dml_exception A DML specific exception is thrown for any errors.
158622bd 987 */
988 public function update_record($table, $dataobject, $bulk=false) {
d8fa8e40 989 $dataobject = (array)$dataobject;
158622bd 990
158622bd 991 $columns = $this->get_columns($table);
992 $cleaned = array();
d246cdd2 993 $blobs = array();
158622bd 994
995 foreach ($dataobject as $field=>$value) {
996 if (!isset($columns[$field])) {
997 continue;
998 }
d246cdd2 999 $column = $columns[$field];
8fb501e9
EL
1000 $normalised_value = $this->normalise_value($column, $value);
1001 if (is_array($normalised_value) && array_key_exists('blob', $normalised_value)) {
1002 $cleaned[$field] = '@#BLOB#@';
1003 $blobs[$field] = $normalised_value['blob'];
1004 } else {
1005 $cleaned[$field] = $normalised_value;
158622bd 1006 }
158622bd 1007 }
1008
22d77567 1009 $this->update_record_raw($table, $cleaned, $bulk);
d246cdd2 1010
1011 if (empty($blobs)) {
1012 return true;
1013 }
1014
1045bec0 1015 $id = (int)$dataobject['id'];
22d77567 1016
d246cdd2 1017 foreach ($blobs as $key=>$value) {
d246cdd2 1018 $value = pg_escape_bytea($this->pgsql, $value);
1019 $sql = "UPDATE {$this->prefix}$table SET $key = '$value'::bytea WHERE id = $id";
1500142b 1020 $this->query_start($sql, NULL, SQL_QUERY_UPDATE);
d246cdd2 1021 $result = pg_query($this->pgsql, $sql);
1500142b 1022 $this->query_end($result);
22d77567 1023
d246cdd2 1024 pg_free_result($result);
1025 }
1026
1027 return true;
158622bd 1028 }
1029
1030 /**
1031 * Set a single field in every table record which match a particular WHERE clause.
1032 *
1033 * @param string $table The database table to be checked against.
1034 * @param string $newfield the field to set.
1035 * @param string $newvalue the value to set the field to.
1036 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
1037 * @param array $params array of sql parameters
22d77567 1038 * @return bool true
6df26010 1039 * @throws dml_exception A DML specific exception is thrown for any errors.
158622bd 1040 */
1041 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) {
762bd0df 1042
158622bd 1043 if ($select) {
1044 $select = "WHERE $select";
1045 }
1046 if (is_null($params)) {
1047 $params = array();
1048 }
1049 list($select, $params, $type) = $this->fix_sql_params($select, $params);
1050 $i = count($params)+1;
1051
00902cd9 1052 // Get column metadata
762bd0df 1053 $columns = $this->get_columns($table);
1054 $column = $columns[$newfield];
1055
8fb501e9
EL
1056 $normalised_value = $this->normalise_value($column, $newvalue);
1057 if (is_array($normalised_value) && array_key_exists('blob', $normalised_value)) {
00902cd9 1058 // Update BYTEA and return
8fb501e9
EL
1059 $normalised_value = pg_escape_bytea($this->pgsql, $normalised_value['blob']);
1060 $sql = "UPDATE {$this->prefix}$table SET $newfield = '$normalised_value'::bytea $select";
762bd0df 1061 $this->query_start($sql, NULL, SQL_QUERY_UPDATE);
1062 $result = pg_query_params($this->pgsql, $sql, $params);
1063 $this->query_end($result);
1064 pg_free_result($result);
1065 return true;
1066 }
1067
8fb501e9 1068 if (is_null($normalised_value)) {
158622bd 1069 $newfield = "$newfield = NULL";
1070 } else {
1071 $newfield = "$newfield = \$".$i;
8fb501e9 1072 $params[] = $normalised_value;
158622bd 1073 }
1074 $sql = "UPDATE {$this->prefix}$table SET $newfield $select";
1075
1500142b 1076 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
158622bd 1077 $result = pg_query_params($this->pgsql, $sql, $params);
1500142b 1078 $this->query_end($result);
158622bd 1079
e4f9c142 1080 pg_free_result($result);
158622bd 1081
1082 return true;
1083 }
1084
1085 /**
1086 * Delete one or more records from a table which match a particular WHERE clause.
1087 *
1088 * @param string $table The database table to be checked against.
1089 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
1090 * @param array $params array of sql parameters
22d77567 1091 * @return bool true
6df26010 1092 * @throws dml_exception A DML specific exception is thrown for any errors.
158622bd 1093 */
1094 public function delete_records_select($table, $select, array $params=null) {
1095 if ($select) {
1096 $select = "WHERE $select";
1097 }
1098 $sql = "DELETE FROM {$this->prefix}$table $select";
1099
1100 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
1101
1500142b 1102 $this->query_start($sql, $params, SQL_QUERY_UPDATE);
158622bd 1103 $result = pg_query_params($this->pgsql, $sql, $params);
1500142b 1104 $this->query_end($result);
158622bd 1105
e4f9c142 1106 pg_free_result($result);
158622bd 1107
1108 return true;
1109 }
1110
6055f89d
PS
1111 /**
1112 * Returns 'LIKE' part of a query.
1113 *
1114 * @param string $fieldname usually name of the table column
1115 * @param string $param usually bound query parameter (?, :named)
1116 * @param bool $casesensitive use case sensitive search
1117 * @param bool $accensensitive use accent sensitive search (not all databases support accent insensitive)
16114b9d 1118 * @param bool $notlike true means "NOT LIKE"
6055f89d
PS
1119 * @param string $escapechar escape char for '%' and '_'
1120 * @return string SQL code fragment
1121 */
16114b9d 1122 public function sql_like($fieldname, $param, $casesensitive = true, $accentsensitive = true, $notlike = false, $escapechar = '\\') {
6055f89d 1123 if (strpos($param, '%') !== false) {
de640a2d 1124 debugging('Potential SQL injection detected, sql_like() expects bound parameters (? or :named)');
6055f89d 1125 }
ed63f7f8
PS
1126 if ($escapechar === '\\') {
1127 // Prevents problems with C-style escapes of enclosing '\',
1128 // E'... bellow prevents compatibility warnings.
1129 $escapechar = '\\\\';
1130 }
6055f89d
PS
1131
1132 // postgresql does not support accent insensitive text comparisons, sorry
1133 if ($casesensitive) {
16114b9d 1134 $LIKE = $notlike ? 'NOT LIKE' : 'LIKE';
6055f89d 1135 } else {
16114b9d 1136 $LIKE = $notlike ? 'NOT ILIKE' : 'ILIKE';
6055f89d 1137 }
ed63f7f8 1138 return "$fieldname $LIKE $param ESCAPE E'$escapechar'";
6055f89d
PS
1139 }
1140
158622bd 1141 public function sql_bitxor($int1, $int2) {
30899510 1142 return '((' . $int1 . ') # (' . $int2 . '))';
158622bd 1143 }
1144
1145 public function sql_cast_char2int($fieldname, $text=false) {
1146 return ' CAST(' . $fieldname . ' AS INT) ';
1147 }
1148
1149 public function sql_cast_char2real($fieldname, $text=false) {
1150 return " $fieldname::real ";
1151 }
1152
1153 public function sql_concat() {
1154 $arr = func_get_args();
1155 $s = implode(' || ', $arr);
1156 if ($s === '') {
1157 return " '' ";
1158 }
4cf2e5d5 1159 // Add always empty string element so integer-exclusive concats
00902cd9 1160 // will work without needing to cast each element explicitly
4cf2e5d5 1161 return " '' || $s ";
158622bd 1162 }
1163
1164 public function sql_concat_join($separator="' '", $elements=array()) {
1165 for ($n=count($elements)-1; $n > 0 ; $n--) {
1166 array_splice($elements, $n, 0, $separator);
1167 }
1168 $s = implode(' || ', $elements);
1169 if ($s === '') {
1170 return " '' ";
1171 }
1172 return " $s ";
1173 }
1174
158622bd 1175 public function sql_regex_supported() {
1176 return true;
1177 }
1178
1179 public function sql_regex($positivematch=true) {
1180 return $positivematch ? '~*' : '!~*';
1181 }
1182
5e9dd017 1183 public function session_lock_supported() {
4b5732f9 1184 return true;
5e9dd017 1185 }
1186
2b0e3941
PS
1187 /**
1188 * Obtain session lock
1189 * @param int $rowid id of the row with session record
1190 * @param int $timeout max allowed time to wait for the lock in seconds
1191 * @return bool success
1192 */
1193 public function get_session_lock($rowid, $timeout) {
bba50616 1194 // NOTE: there is a potential locking problem for database running
5e9dd017 1195 // multiple instances of moodle, we could try to use pg_advisory_lock(int, int),
1196 // luckily there is not a big chance that they would collide
1197 if (!$this->session_lock_supported()) {
1198 return;
1199 }
1200
2b0e3941
PS
1201 parent::get_session_lock($rowid, $timeout);
1202
1203 $timeoutmilli = $timeout * 1000;
1204
1205 $sql = "SET statement_timeout TO $timeoutmilli";
1206 $this->query_start($sql, null, SQL_QUERY_AUX);
1207 $result = pg_query($this->pgsql, $sql);
1208 $this->query_end($result);
1209
1210 if ($result) {
1211 pg_free_result($result);
1212 }
1213
5e9dd017 1214 $sql = "SELECT pg_advisory_lock($rowid)";
1215 $this->query_start($sql, null, SQL_QUERY_AUX);
2b0e3941
PS
1216 $start = time();
1217 $result = pg_query($this->pgsql, $sql);
1218 $end = time();
1219 try {
1220 $this->query_end($result);
1221 } catch (dml_exception $ex) {
1222 if ($end - $start >= $timeout) {
1223 throw new dml_sessionwait_exception();
1224 } else {
1225 throw $ex;
1226 }
1227 }
1228
1229 if ($result) {
1230 pg_free_result($result);
1231 }
1232
1233 $sql = "SET statement_timeout TO DEFAULT";
1234 $this->query_start($sql, null, SQL_QUERY_AUX);
5e9dd017 1235 $result = pg_query($this->pgsql, $sql);
1236 $this->query_end($result);
1237
1238 if ($result) {
1239 pg_free_result($result);
1240 }
1241 }
1242
1243 public function release_session_lock($rowid) {
1244 if (!$this->session_lock_supported()) {
1245 return;
1246 }
1247 parent::release_session_lock($rowid);
1248
1249 $sql = "SELECT pg_advisory_unlock($rowid)";
1250 $this->query_start($sql, null, SQL_QUERY_AUX);
1251 $result = pg_query($this->pgsql, $sql);
1252 $this->query_end($result);
1253
1254 if ($result) {
1255 pg_free_result($result);
1256 }
1257 }
1258
fb76304b 1259 /**
d5a8d9aa
PS
1260 * Driver specific start of real database transaction,
1261 * this can not be used directly in code.
1262 * @return void
fb76304b 1263 */
d5a8d9aa 1264 protected function begin_transaction() {
1500142b 1265 $sql = "BEGIN ISOLATION LEVEL READ COMMITTED";
1266 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1267 $result = pg_query($this->pgsql, $sql);
1268 $this->query_end($result);
1269
fb76304b 1270 pg_free_result($result);
fb76304b 1271 }
1272
1273 /**
d5a8d9aa
PS
1274 * Driver specific commit of real database transaction,
1275 * this can not be used directly in code.
1276 * @return void
fb76304b 1277 */
d5a8d9aa 1278 protected function commit_transaction() {
1500142b 1279 $sql = "COMMIT";
1280 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1281 $result = pg_query($this->pgsql, $sql);
1282 $this->query_end($result);
1283
fb76304b 1284 pg_free_result($result);
fb76304b 1285 }
1286
1287 /**
d5a8d9aa
PS
1288 * Driver specific abort of real database transaction,
1289 * this can not be used directly in code.
1290 * @return void
fb76304b 1291 */
d5a8d9aa 1292 protected function rollback_transaction() {
1500142b 1293 $sql = "ROLLBACK";
1294 $this->query_start($sql, NULL, SQL_QUERY_AUX);
1295 $result = pg_query($this->pgsql, $sql);
1296 $this->query_end($result);
1297
fb76304b 1298 pg_free_result($result);
fb76304b 1299 }
d64514aa 1300
1301 /**
1302 * Helper function trimming (whitespace + quotes) any string
1303 * needed because PG uses to enclose with double quotes some
1304 * fields in indexes definition and others
1305 *
1306 * @param string $str string to apply whitespace + quotes trim
1307 * @return string trimmed string
1308 */
1309 private function trim_quotes($str) {
1310 return trim(trim($str), "'\"");
1311 }
158622bd 1312}