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