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