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