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