MDL-17020 dml: native pgsql driver - improved get_record and get_field - this should...
[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 }
80ffbad3 190 $columns = explode(',', $matches[4]);
191 $columns = array_map('trim', $columns);
158622bd 192 $indexes[$matches[2]] = array('unique'=>!empty($matches[1]),
80ffbad3 193 'columns'=>$columns);
158622bd 194 }
195 pg_free_result($result);
196 }
197 return $indexes;
198 }
199
200 /**
201 * Returns datailed information about columns in table. This information is cached internally.
202 * @param string $table name
203 * @param bool $usecache
204 * @return array array of database_column_info objects indexed with column names
205 */
206 public function get_columns($table, $usecache=true) {
207 if ($usecache and isset($this->columns[$table])) {
208 return $this->columns[$table];
209 }
210
211 $this->columns[$table] = array();
212
213 $tablename = $this->prefix.$table;
214
215 $sql = "SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, d.adsrc
216 FROM pg_catalog.pg_class c
217 JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
218 JOIN pg_catalog.pg_type t ON t.oid = a.atttypid
219 LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = c.oid AND d.adnum = a.attnum)
220 WHERE relkind = 'r' AND c.relname = '$tablename' AND c.reltype > 0 AND a.attnum > 0
221 ORDER BY a.attnum";
222
223 if (!$result = pg_query($this->pgsql, $sql)) {
224 return array();
225 }
226 while ($rawcolumn = pg_fetch_object($result)) {
227
228 $info = new object();
229 $info->name = $rawcolumn->field;
230 $matches = null;
231
232 if ($rawcolumn->type === 'varchar') {
233 //TODO add some basic enum support here
234 $info->type = 'varchar';
235 $info->meta_type = 'C';
236 $info->max_length = $rawcolumn->atttypmod - 4;
237 $info->scale = null;
238 $info->not_null = (bool)$rawcolumn->attnotnull;
239 $info->has_default = (bool)$rawcolumn->atthasdef;
240 if ($info->has_default) {
241 $parts = explode('::', $rawcolumn->adsrc);
242 if (count($parts) > 1) {
243 $info->default_value = reset($parts);
244 $info->default_value = trim($info->default_value, "'");
245 } else {
246 $info->default_value = $rawcolumn->adsrc;
247 }
248 } else {
249 $info->default_value = null;
250 }
251 $info->primary_key = false;
252 $info->binary = false;
253 $info->unsigned = null;
254 $info->auto_increment= false;
255 $info->unique = null;
256
257 } else if (preg_match('/int(\d)/i', $rawcolumn->type, $matches)) {
258 $info->type = 'int';
259 if (strpos($rawcolumn->adsrc, 'nextval') === 0) {
260 $info->primary_key = true;
261 $info->meta_type = 'R';
262 $info->unique = true;
263 $info->auto_increment= true;
264 $info->has_default = false;
265 } else {
266 $info->primary_key = false;
267 $info->meta_type = 'I';
268 $info->unique = null;
269 $info->auto_increment= false;
270 $info->has_default = (bool)$rawcolumn->atthasdef;
271 }
272 $info->max_length = $matches[1];
273 $info->scale = null;
274 $info->not_null = (bool)$rawcolumn->attnotnull;
275 if ($info->has_default) {
276 $info->default_value = $rawcolumn->adsrc;
277 } else {
278 $info->default_value = null;
279 }
280 $info->binary = false;
281 $info->unsigned = false;
282
283 } else if ($rawcolumn->type === 'numeric') {
284 $info->type = $rawcolumn->type;
285 $info->meta_type = 'N';
286 $info->primary_key = false;
287 $info->binary = false;
288 $info->unsigned = null;
289 $info->auto_increment= false;
290 $info->unique = null;
291 $info->not_null = (bool)$rawcolumn->attnotnull;
292 $info->has_default = (bool)$rawcolumn->atthasdef;
293 if ($info->has_default) {
294 $info->default_value = $rawcolumn->adsrc;
295 } else {
296 $info->default_value = null;
297 }
298 $info->max_length = $rawcolumn->atttypmod >> 16;
299 $info->scale = ($rawcolumn->atttypmod & 0xFFFF) - 4;
300
301 } else if (preg_match('/float(\d)/i', $rawcolumn->type, $matches)) {
302 $info->type = 'float';
303 $info->meta_type = 'N';
304 $info->primary_key = false;
305 $info->binary = false;
306 $info->unsigned = null;
307 $info->auto_increment= false;
308 $info->unique = null;
309 $info->not_null = (bool)$rawcolumn->attnotnull;
310 $info->has_default = (bool)$rawcolumn->atthasdef;
311 if ($info->has_default) {
312 $info->default_value = $rawcolumn->adsrc;
313 } else {
314 $info->default_value = null;
315 }
316 // just guess expected number of deciaml places :-(
317 if ($matches[1] == 8) {
318 // total 15 digits
319 $info->max_length = 8;
320 $info->scale = 7;
321 } else {
322 // total 6 digits
323 $info->max_length = 4;
324 $info->scale = 2;
325 }
326
327 } else if ($rawcolumn->type === 'text') {
328 $info->type = $rawcolumn->type;
329 $info->meta_type = 'X';
330 $info->max_length = -1;
331 $info->scale = null;
332 $info->not_null = (bool)$rawcolumn->attnotnull;
333 $info->has_default = (bool)$rawcolumn->atthasdef;
334 if ($info->has_default) {
335 $parts = explode('::', $rawcolumn->adsrc);
336 if (count($parts) > 1) {
337 $info->default_value = reset($parts);
338 $info->default_value = trim($info->default_value, "'");
339 } else {
340 $info->default_value = $rawcolumn->adsrc;
341 }
342 } else {
343 $info->default_value = null;
344 }
345 $info->primary_key = false;
346 $info->binary = false;
347 $info->unsigned = null;
348 $info->auto_increment= false;
349 $info->unique = null;
350
351 } else if ($rawcolumn->type === 'bytea') {
352 $info->type = $rawcolumn->type;
353 $info->meta_type = 'B';
354 $info->max_length = -1;
355 $info->scale = null;
356 $info->not_null = (bool)$rawcolumn->attnotnull;
357 $info->has_default = false;
358 $info->default_value = null;
359 $info->primary_key = false;
360 $info->binary = true;
361 $info->unsigned = null;
362 $info->auto_increment= false;
363 $info->unique = null;
364
365 }
366
367 $this->columns[$table][$info->name] = new database_column_info($info);
368 }
369
e4f9c142 370 pg_free_result($result);
371
158622bd 372 return $this->columns[$table];
373 }
374
375 /**
376 * Reset a sequence to the id field of a table.
377 * @param string $table name of table
378 * @return success
379 */
380 public function reset_sequence($table) {
381 if (!$this->get_manager()->table_exists($table)) {
382 return false;
383 }
384 $value = (int)$this->get_field_sql('SELECT MAX(id) FROM {'.$table.'}');
385 $value++;
386 return $this->change_database_structure("ALTER SEQUENCE $this->prefix{$table}_id_seq RESTART WITH $value");
387 }
388
389 /**
390 * Is db in unicode mode?
391 * @return bool
392 */
393 public function setup_is_unicodedb() {
394 /// Get PostgreSQL server_encoding value
395 $this->reads++;
396 if (!$result = pg_query($this->pgsql, "SHOW server_encoding")) {
397 return false;
398 }
399 $rawcolumn = pg_fetch_object($result);
400 $encoding = $rawcolumn->server_encoding;
401 pg_free_result($result);
402
403 return (strtoupper($encoding) == 'UNICODE' || strtoupper($encoding) == 'UTF8');
404 }
405
406 /**
407 * Enable/disable very detailed debugging
408 * @param bool $state
409 */
410 public function set_debug($state) {
411 $this->debug = $state;
412 }
413
414 /**
415 * Returns debug status
416 * @return bool $state
417 */
418 public function get_debug() {
419 return $this->debug;
420 }
421
422 /**
423 * Enable/disable detailed sql logging
424 * @param bool $state
425 */
426 public function set_logging($state) {
427 //TODO
428 }
429
430 /**
431 * Do NOT use in code, to be used by database_manager only!
432 * @param string $sql query
433 * @return bool success
434 */
435 public function change_database_structure($sql) {
436 $this->writes++;
437 $this->print_debug($sql);
438 $result = pg_query($this->pgsql, $sql);
439 $this->reset_columns();
440 if ($result === false) {
441 $this->report_error($sql);
442 return false;
443 }
e4f9c142 444 pg_free_result($result);
158622bd 445 return true;
446 }
447
448 /**
449 * Execute general sql query. Should be used only when no other method suitable.
450 * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead!
451 * @param string $sql query
452 * @param array $params query parameters
453 * @return bool success
454 */
455 public function execute($sql, array $params=null) {
456 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
457
458 if (strpos($sql, ';') !== false) {
459 debugging('Error: Multiple sql statements found or bound parameters not used properly in query!');
460 return false;
461 }
462
463 $this->writes++;
464 $this->print_debug($sql, $params);
465 $result = pg_query_params($this->pgsql, $sql, $params);
466
467 if ($result === false) {
468 $this->report_error($sql, $params);
469 return false;
470
471 }
e4f9c142 472 pg_free_result($result);
158622bd 473 return true;
474 }
475
476 /**
477 * Get a number of records as a moodle_recordset using a SQL statement.
478 *
479 * Since this method is a little less readable, use of it should be restricted to
480 * code where it's possible there might be large datasets being returned. For known
481 * small datasets use get_records_sql - it leads to simpler code.
482 *
483 * The return type is as for @see function get_recordset.
484 *
485 * @param string $sql the SQL select query to execute.
486 * @param array $params array of sql parameters
487 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
488 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
489 * @return mixed an moodle_recorset object, or false if an error occured.
490 */
491 public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
492 if ($limitfrom or $limitnum) {
493 $limitfrom = (int)$limitfrom;
494 $limitnum = (int)$limitnum;
495 if ($limitnum < 1) {
496 $limitnum = "18446744073709551615";
497 }
498 $sql .= " LIMIT $limitnum OFFSET $limitfrom";
499 }
500
501 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
502
503 $this->reads++;
504 $this->print_debug($sql, $params);
505 $result = pg_query_params($this->pgsql, $sql, $params);
506
507 if ($result === false) {
508 $this->report_error($sql, $params);
509 return false;
510 }
511
512 return $this->create_recordset($result);
513 }
514
515 protected function create_recordset($result) {
516 return new pgsql_native_moodle_recordset($result);
517 }
518
519 /**
520 * Get a number of records as an array of objects using a SQL statement.
521 *
522 * Return value as for @see function get_records.
523 *
524 * @param string $sql the SQL select query to execute. The first column of this SELECT statement
525 * must be a unique value (usually the 'id' field), as it will be used as the key of the
526 * returned array.
527 * @param array $params array of sql parameters
528 * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
529 * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
530 * @return mixed an array of objects, or empty array if no records were found, or false if an error occured.
531 */
532 public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
533 if ($limitfrom or $limitnum) {
534 $limitfrom = (int)$limitfrom;
535 $limitnum = (int)$limitnum;
536 if ($limitnum < 1) {
537 $limitnum = "18446744073709551615";
538 }
539 $sql .= " LIMIT $limitnum OFFSET $limitfrom";
540 }
541
542 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
543 $this->reads++;
544 $this->print_debug($sql, $params);
545 $result = pg_query_params($this->pgsql, $sql, $params);
546
547 if ($result === false) {
548 $this->report_error($sql, $params);
549 return false;
550 }
551
552 $rows = pg_fetch_all($result);
553 pg_free_result($result);
554
555 $return = array();
556 if ($rows) {
557 foreach ($rows as $row) {
558 $id = reset($row);
559 $return[$id] = (object)$row;
560 }
561 }
562
563 return $return;
564 }
565
566 /**
567 * Selects records and return values (first field) as an array using a SQL statement.
568 *
569 * @param string $sql The SQL query
570 * @param array $params array of sql parameters
571 * @return mixed array of values or false if an error occured
572 */
573 public function get_fieldset_sql($sql, array $params=null) {
574 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
575
576 $this->reads++;
577 $this->print_debug($sql, $params);
578 $result = pg_query_params($this->pgsql, $sql, $params);
579
580 if ($result === false) {
581 $this->report_error($sql, $params);
582 return false;
583 }
584
585 $return = pg_fetch_all_columns($result, 0);
586 pg_free_result($result);
587
588 return $return;
589 }
590
591 /**
592 * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
593 * @param string $table name
594 * @param mixed $params data record as object or array
595 * @param bool $returnit return it of inserted record
596 * @param bool $bulk true means repeated inserts expected
597 * @param bool $customsequence true if 'id' included in $params, disables $returnid
598 * @return mixed success or new id
599 */
600 public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
601 if (!is_array($params)) {
602 $params = (array)$params;
603 }
604
605 $returning = "";
606
607 if ($customsequence) {
608 if (!isset($params['id'])) {
609 return false;
610 }
611 $returnid = false;
612 } else {
613 if ($returnid) {
614 if ($this->is_min_version('8.2.0')) {
615 $returning = "RETURNING id";
616 unset($params['id']);
617 } else {
618 //ugly workaround for pg < 8.2
619 $this->reads++;
620 $seqsql = "SELECT NEXTVAL({$this->prefix}{$table}_id_seq) AS id";
621 $result = pg_query($this->pgsql, $seqsql);
622 if ($result === false) {
623 throw new dml_exception('missingidsequence', "{$this->prefix}{$table}"); // TODO: add localised string
624 }
625 $row = pg_fetch_assoc($result);
626 $params['id'] = reset($row);
627 pg_free_result($result);
628 }
629 } else {
630 unset($params['id']);
631 }
632 }
633
634 if (empty($params)) {
635 return false;
636 }
637
638 $fields = implode(',', array_keys($params));
639 $values = array();
640 $count = count($params);
641 for ($i=1; $i<=$count; $i++) {
642 $values[] = "\$".$i;
643 }
644 $values = implode(',', $values);
645
646 $sql = "INSERT INTO {$this->prefix}$table ($fields) VALUES($values) $returning";
647 $this->writes++;
648 $this->print_debug($sql, $params);
649 $result = pg_query_params($this->pgsql, $sql, $params);
650
651 if ($result === false) {
652 $this->report_error($sql, $params);
653 return false;
654 }
655
656 if ($returning !== "") {
657 $row = pg_fetch_assoc($result);
658 $params['id'] = reset($row);
158622bd 659 }
e4f9c142 660 pg_free_result($result);
158622bd 661
662 if (!$returnid) {
663 return true;
664 }
665
666 return (int)$params['id'];
667 }
668
669 /**
670 * Insert a record into a table and return the "id" field if required.
671 *
672 * Some conversions and safety checks are carried out. Lobs are supported.
673 * If the return ID isn't required, then this just reports success as true/false.
674 * $data is an object containing needed data
675 * @param string $table The database table to be inserted into
676 * @param object $data A data object with values for one or more fields in the record
677 * @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.
678 * @return mixed success or new ID
679 */
680 public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
681 if (!is_object($dataobject)) {
682 $dataobject = (object)$dataobject;
683 }
684
685 $columns = $this->get_columns($table);
686
687 unset($dataobject->id);
688 $cleaned = array();
689 $blobs = array();
690
691 foreach ($dataobject as $field=>$value) {
692 if (!isset($columns[$field])) {
693 continue;
694 }
695 $column = $columns[$field];
696 if ($column->meta_type == 'B') {
697 if (is_null($value)) {
698 $cleaned[$field] = null;
699 } else {
700 $blobs[$field] = $value;
701 $cleaned[$field] = '@#BLOB#@';
702 }
703 continue;
704
705 } else if (is_bool($value)) {
706 $value = (int)$value; // prevent false '' problems
707
708 } else if ($value === '') {
709 if ($column->meta_type == 'I' or $column->meta_type == 'F' or $column->meta_type == 'N') {
710 $value = 0; // prevent '' problems in numeric fields
711 }
712 }
713
714 $cleaned[$field] = $value;
715 }
716
717 if (empty($cleaned)) {
718 return false;
719 }
720
721 if (empty($blobs)) {
722 return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
723 }
724
725 if (!$id = $this->insert_record_raw($table, $cleaned, true, $bulk)) {
726 return false;
727 }
728
729 foreach ($blobs as $key=>$value) {
730 $this->writes++;
731 $value = pg_escape_bytea($this->pgsql, $value);
732 $sql = "UPDATE {$this->prefix}$table SET $key = '$value'::bytea WHERE id = $id";
e4f9c142 733 $result = pg_query($this->pgsql, $sql);
734 if ($result !== false) {
735 pg_free_result($result);
736 }
158622bd 737 }
738
739 return ($returnid ? $id : true);
740
741 }
742
743 /**
744 * Import a record into a table, id field is required.
745 * Safety checks are NOT carried out. Lobs are supported.
746 *
747 * @param string $table name of database table to be inserted into
748 * @param object $dataobject A data object with values for one or more fields in the record
749 * @return bool success
750 */
751 public function import_record($table, $dataobject) {
752 $dataobject = (object)$dataobject;
753
754 if (empty($dataobject->id)) {
755 return false;
756 }
757
758 $columns = $this->get_columns($table);
759 $cleaned = array();
760
761 foreach ($dataobject as $field=>$value) {
762 if (!isset($columns[$field])) {
763 continue;
764 }
765 $cleaned[$field] = $value;
766 }
767
768 return $this->insert_record_raw($table, $cleaned, false, true, true);
769 }
770
771 /**
772 * Update record in database, as fast as possible, no safety checks, lobs not supported.
773 * @param string $table name
774 * @param mixed $params data record as object or array
775 * @param bool true means repeated updates expected
776 * @return bool success
777 */
778 public function update_record_raw($table, $params, $bulk=false) {
779 if (!is_array($params)) {
780 $params = (array)$params;
781 }
782 if (!isset($params['id'])) {
783 return false;
784 }
785 $id = $params['id'];
786 unset($params['id']);
787
788 if (empty($params)) {
789 return false;
790 }
791
792 $i = 1;
793
794 $sets = array();
795 foreach ($params as $field=>$value) {
796 $sets[] = "$field = \$".$i++;
797 }
798
799 $params[] = $id; // last ? in WHERE condition
800
801 $sets = implode(',', $sets);
802 $sql = "UPDATE {$this->prefix}$table SET $sets WHERE id=\$".$i;
803
804 $this->writes++;
805 $this->print_debug($sql, $params);
806 $result = pg_query_params($this->pgsql, $sql, $params);
807
808 if ($result === false) {
809 $this->report_error($sql, $params);
810 return false;
811 }
812
e4f9c142 813 pg_free_result($result);
158622bd 814 return true;
815 }
816
817 /**
818 * Update a record in a table
819 *
820 * $dataobject is an object containing needed data
821 * Relies on $dataobject having a variable "id" to
822 * specify the record to update
823 *
824 * @param string $table The database table to be checked against.
825 * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
826 * @param bool true means repeated updates expected
827 * @return bool success
828 */
829 public function update_record($table, $dataobject, $bulk=false) {
830 if (!is_object($dataobject)) {
831 $dataobject = (object)$dataobject;
832 }
833
834 if (!isset($dataobject->id) ) {
835 return false;
836 }
837
838 $columns = $this->get_columns($table);
839 $cleaned = array();
840
841 foreach ($dataobject as $field=>$value) {
842 if (!isset($columns[$field])) {
843 continue;
844 }
845 if (is_bool($value)) {
846 $value = (int)$value; // prevent "false" problems
847 }
848 $cleaned[$field] = $value;
849 }
850
851 return $this->update_record_raw($table, $cleaned, $bulk);
852 }
853
854 /**
855 * Set a single field in every table record which match a particular WHERE clause.
856 *
857 * @param string $table The database table to be checked against.
858 * @param string $newfield the field to set.
859 * @param string $newvalue the value to set the field to.
860 * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
861 * @param array $params array of sql parameters
862 * @return bool success
863 */
864 public function set_field_select($table, $newfield, $newvalue, $select, array $params=null) {
865 if ($select) {
866 $select = "WHERE $select";
867 }
868 if (is_null($params)) {
869 $params = array();
870 }
871 list($select, $params, $type) = $this->fix_sql_params($select, $params);
872 $i = count($params)+1;
873
874 if (is_bool($newvalue)) {
875 $newvalue = (int)$newvalue; // prevent "false" problems
876 }
877 if (is_null($newvalue)) {
878 $newfield = "$newfield = NULL";
879 } else {
880 $newfield = "$newfield = \$".$i;
881 $params[] = $newvalue;
882 }
883 $sql = "UPDATE {$this->prefix}$table SET $newfield $select";
884
885 $this->writes++;
886 $this->print_debug($sql, $params);
887 $result = pg_query_params($this->pgsql, $sql, $params);
888
889 if ($result === false) {
890 $this->report_error($sql, $params);
891 return false;
892 }
e4f9c142 893 pg_free_result($result);
158622bd 894
895 return true;
896 }
897
898 /**
899 * Delete one or more records from a table which match a particular WHERE clause.
900 *
901 * @param string $table The database table to be checked against.
902 * @param string $select A fragment of SQL to be used in a where clause in the SQL call (used to define the selection criteria).
903 * @param array $params array of sql parameters
904 * @return returns success.
905 */
906 public function delete_records_select($table, $select, array $params=null) {
907 if ($select) {
908 $select = "WHERE $select";
909 }
910 $sql = "DELETE FROM {$this->prefix}$table $select";
911
912 list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
913
914 $this->writes++;
915 $this->print_debug($sql, $params);
916 $result = pg_query_params($this->pgsql, $sql, $params);
917
918 if ($result === false) {
919 $this->report_error($sql, $params);
920 return false;
921 }
e4f9c142 922 pg_free_result($result);
158622bd 923
924 return true;
925 }
926
927 public function sql_ilike() {
928 return 'ILIKE';
929 }
930
931 public function sql_bitxor($int1, $int2) {
932 return '(' . $this->sql_bitor($int1, $int2) . ' - ' . $this->sql_bitand($int1, $int2) . ')';
933 }
934
935 public function sql_cast_char2int($fieldname, $text=false) {
936 return ' CAST(' . $fieldname . ' AS INT) ';
937 }
938
939 public function sql_cast_char2real($fieldname, $text=false) {
940 return " $fieldname::real ";
941 }
942
943 public function sql_concat() {
944 $arr = func_get_args();
945 $s = implode(' || ', $arr);
946 if ($s === '') {
947 return " '' ";
948 }
949 return " $s ";
950 }
951
952 public function sql_concat_join($separator="' '", $elements=array()) {
953 for ($n=count($elements)-1; $n > 0 ; $n--) {
954 array_splice($elements, $n, 0, $separator);
955 }
956 $s = implode(' || ', $elements);
957 if ($s === '') {
958 return " '' ";
959 }
960 return " $s ";
961 }
962
963 public function sql_substr() {
964 return "SUBSTRING";
965 }
966
967 public function sql_regex_supported() {
968 return true;
969 }
970
971 public function sql_regex($positivematch=true) {
972 return $positivematch ? '~*' : '!~*';
973 }
974
975}