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