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