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