158622bd |
1 | <?php //$Id$ |
2 | |
3 | require_once($CFG->libdir.'/dml/moodle_database.php'); |
4 | require_once($CFG->libdir.'/dml/pgsql_native_moodle_recordset.php'); |
5 | |
6 | /** |
7 | * Native pgsql class representing moodle database interface. |
8 | * @package dml |
9 | */ |
10 | class 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 | } |