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