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