pgsql_native_moodle_database.php 44 KB
Newer Older
skodak's avatar
skodak committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<?php

// This file is part of Moodle - http://moodle.org/
//
// Moodle is free software: you can redistribute it and/or modify
// it under the terms of the GNU General Public License as published by
// the Free Software Foundation, either version 3 of the License, or
// (at your option) any later version.
//
// Moodle is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU General Public License for more details.
//
// You should have received a copy of the GNU General Public License
// along with Moodle.  If not, see <http://www.gnu.org/licenses/>.


/**
 * Native pgsql class representing moodle database interface.
 *
22
 * @package    core
23
 * @subpackage dml
skodak's avatar
skodak committed
24
25
26
 * @copyright  2008 Petr Skoda (http://skodak.org)
 * @license    http://www.gnu.org/copyleft/gpl.html GNU GPL v3 or later
 */
27

28
29
defined('MOODLE_INTERNAL') || die();

30
31
require_once($CFG->libdir.'/dml/moodle_database.php');
require_once($CFG->libdir.'/dml/pgsql_native_moodle_recordset.php');
32
require_once($CFG->libdir.'/dml/pgsql_native_moodle_temptables.php');
33
34
35
36
37
38

/**
 * Native pgsql class representing moodle database interface.
 */
class pgsql_native_moodle_database extends moodle_database {

39
40
    protected $pgsql     = null;
    protected $bytea_oid = null;
41

42
    protected $last_error_reporting; // To handle pgsql driver default verbosity
43

44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
    /**
     * Detects if all needed PHP stuff installed.
     * Note: can be used before connect()
     * @return mixed true if ok, string if something
     */
    public function driver_installed() {
        if (!extension_loaded('pgsql')) {
            return get_string('pgsqlextensionisnotpresentinphp', 'install');
        }
        return true;
    }

    /**
     * Returns database family type - describes SQL dialect
     * Note: can be used before connect()
     * @return string db family name (mysql, postgres, mssql, oracle, etc.)
     */
    public function get_dbfamily() {
        return 'postgres';
    }

    /**
     * Returns more specific database driver type
     * Note: can be used before connect()
68
     * @return string db type mysqli, pgsql, oci, mssql, sqlsrv
69
70
71
72
73
74
75
76
     */
    protected function get_dbtype() {
        return 'pgsql';
    }

    /**
     * Returns general database library name
     * Note: can be used before connect()
77
     * @return string db type pdo, native
78
79
80
81
82
83
84
85
86
87
88
     */
    protected function get_dblibrary() {
        return 'native';
    }

    /**
     * Returns localised database type name
     * Note: can be used before connect()
     * @return string
     */
    public function get_name() {
skodak's avatar
skodak committed
89
        return get_string('nativepgsql', 'install');
90
91
    }

skodak's avatar
skodak committed
92
93
94
95
96
97
98
99
100
    /**
     * Returns localised database configuration help.
     * Note: can be used before connect()
     * @return string
     */
    public function get_configuration_help() {
        return get_string('nativepgsqlhelp', 'install');
    }

101
102
103
104
105
106
    /**
     * Returns localised database description
     * Note: can be used before connect()
     * @return string
     */
    public function get_configuration_hints() {
skodak's avatar
skodak committed
107
        return get_string('databasesettingssub_postgres7', 'install');
108
109
110
111
112
113
114
115
116
117
118
    }

    /**
     * Connect to db
     * Must be called before other methods.
     * @param string $dbhost
     * @param string $dbuser
     * @param string $dbpass
     * @param string $dbname
     * @param mixed $prefix string means moodle db prefix, false used for external databases where prefix not used
     * @param array $dboptions driver specific options
119
120
     * @return bool true
     * @throws dml_connection_exception if error
121
     */
122
    public function connect($dbhost, $dbuser, $dbpass, $dbname, $prefix, array $dboptions=null) {
123
124
125
126
127
        if ($prefix == '' and !$this->external) {
            //Enforce prefixes for everybody but mysql
            throw new dml_exception('prefixcannotbeempty', $this->get_dbfamily());
        }

128
129
130
131
132
133
        $driverstatus = $this->driver_installed();

        if ($driverstatus !== true) {
            throw new dml_exception('dbdriverproblem', $driverstatus);
        }

134
        $this->store_settings($dbhost, $dbuser, $dbpass, $dbname, $prefix, $dboptions);
135

136
137
138
        $pass = addcslashes($this->dbpass, "'\\");

        // Unix socket connections should have lower overhead
139
        if (!empty($this->dboptions['dbsocket']) and ($this->dbhost === 'localhost' or $this->dbhost === '127.0.0.1')) {
140
            $connection = "user='$this->dbuser' password='$pass' dbname='$this->dbname'";
141
142
143
            if (strpos($this->dboptions['dbsocket'], '/') !== false) {
                $connection = $connection." host='".$this->dboptions['dbsocket']."'";
            }
144
        } else {
145
            $this->dboptions['dbsocket'] = '';
146
147
148
149
150
            if (empty($this->dbname)) {
                // probably old style socket connection - do not add port
                $port = "";
            } else if (empty($this->dboptions['dbport'])) {
                $port = "port ='5432'";
skodak's avatar
skodak committed
151
            } else {
152
                $port = "port ='".$this->dboptions['dbport']."'";
skodak's avatar
skodak committed
153
            }
154
            $connection = "host='$this->dbhost' $port user='$this->dbuser' password='$pass' dbname='$this->dbname'";
155
156
        }

157
        ob_start();
158
        if (empty($this->dboptions['dbpersist'])) {
159
160
161
162
            $this->pgsql = pg_connect($connection, PGSQL_CONNECT_FORCE_NEW);
        } else {
            $this->pgsql = pg_pconnect($connection, PGSQL_CONNECT_FORCE_NEW);
        }
163
164
        $dberr = ob_get_contents();
        ob_end_clean();
165

166
        $status = pg_connection_status($this->pgsql);
167
168

        if ($status === false or $status === PGSQL_CONNECTION_BAD) {
169
            $this->pgsql = null;
170
            throw new dml_connection_exception($dberr);
171
        }
172

173
        $this->query_start("--pg_set_client_encoding()", null, SQL_QUERY_AUX);
174
        pg_set_client_encoding($this->pgsql, 'utf8');
175
176
        $this->query_end(true);

177
        // find out the bytea oid
178
        $sql = "SELECT oid FROM pg_type WHERE typname = 'bytea'";
179
        $this->query_start($sql, null, SQL_QUERY_AUX);
180
        $result = pg_query($this->pgsql, $sql);
181
        $this->query_end($result);
182

183
        $this->bytea_oid = pg_fetch_result($result, 0, 0);
184
185
        pg_free_result($result);
        if ($this->bytea_oid === false) {
186
187
            $this->pgsql = null;
            throw new dml_connection_exception('Can not read bytea type.');
188
        }
189

190
        // Connection stabilised and configured, going to instantiate the temptables controller
191
192
        $this->temptables = new pgsql_native_moodle_temptables($this);

193
194
195
196
197
198
199
200
201
        return true;
    }

    /**
     * Close database connection and release all resources
     * and memory (especially circular memory references).
     * Do NOT use connect() again, create a new instance if needed.
     */
    public function dispose() {
Petr Skoda's avatar
Petr Skoda committed
202
        parent::dispose(); // Call parent dispose to write/close session and other common stuff before closing connection
203
204
205
206
207
208
        if ($this->pgsql) {
            pg_close($this->pgsql);
            $this->pgsql = null;
        }
    }

209
210
211
212
213
214
215
216
217
218
219
220

    /**
     * Called before each db query.
     * @param string $sql
     * @param array array of parameters
     * @param int $type type of query
     * @param mixed $extrainfo driver specific extra information
     * @return void
     */
    protected function query_start($sql, array $params=null, $type, $extrainfo=null) {
        parent::query_start($sql, $params, $type, $extrainfo);
        // pgsql driver tents to send debug to output, we do not need that ;-)
221
        $this->last_error_reporting = error_reporting(0);
222
223
224
225
226
227
228
229
    }

    /**
     * Called immediately after each db query.
     * @param mixed db specific result
     * @return void
     */
    protected function query_end($result) {
230
231
        // reset original debug level
        error_reporting($this->last_error_reporting);
232
233
234
        parent::query_end($result);
    }

235
236
237
238
239
240
241
    /**
     * Returns database server info array
     * @return array
     */
    public function get_server_info() {
        static $info;
        if (!$info) {
242
            $this->query_start("--pg_version()", null, SQL_QUERY_AUX);
243
            $info = pg_version($this->pgsql);
244
            $this->query_end(true);
245
246
247
248
249
250
251
252
253
254
255
256
        }
        return array('description'=>$info['server'], 'version'=>$info['server']);
    }

    protected function is_min_version($version) {
        $server = $this->get_server_info();
        $server = $server['version'];
        return version_compare($server, $version, '>=');
    }

    /**
     * Returns supported query parameter types
Petr Skoda's avatar
Petr Skoda committed
257
     * @return int bitmask
258
259
260
261
262
263
264
     */
    protected function allowed_param_types() {
        return SQL_PARAMS_DOLLAR;
    }

    /**
     * Returns last error reported by database engine.
Petr Skoda's avatar
Petr Skoda committed
265
     * @return string error message
266
267
268
269
270
271
272
273
274
     */
    public function get_last_error() {
        return pg_last_error($this->pgsql);
    }

    /**
     * Return tables in database WITHOUT current prefix
     * @return array of table names in lowercase and without prefix
     */
275
276
277
278
279
    public function get_tables($usecache=true) {
        if ($usecache and $this->tables !== null) {
            return $this->tables;
        }
        $this->tables = array();
280
        $prefix = str_replace('_', '\\\\_', $this->prefix);
281
282
283
284
285
286
        // Get them from information_schema instead of catalog as far as
        // we want to get only own session temp objects (catalog returns all)
        $sql = "SELECT table_name
                  FROM information_schema.tables
                 WHERE table_name LIKE '$prefix%'
                   AND table_type IN ('BASE TABLE', 'LOCAL TEMPORARY')";
287
288
289
        $this->query_start($sql, null, SQL_QUERY_AUX);
        $result = pg_query($this->pgsql, $sql);
        $this->query_end($result);
skodak's avatar
skodak committed
290

291
        if ($result) {
292
293
294
295
296
297
            while ($row = pg_fetch_row($result)) {
                $tablename = reset($row);
                if (strpos($tablename, $this->prefix) !== 0) {
                    continue;
                }
                $tablename = substr($tablename, strlen($this->prefix));
298
                $this->tables[$tablename] = $tablename;
299
300
301
            }
            pg_free_result($result);
        }
302
        return $this->tables;
303
304
305
306
307
308
309
310
311
312
313
314
315
    }

    /**
     * Return table indexes - everything lowercased
     * @return array of arrays
     */
    public function get_indexes($table) {
        $indexes = array();
        $tablename = $this->prefix.$table;

        $sql = "SELECT *
                  FROM pg_catalog.pg_indexes
                 WHERE tablename = '$tablename'";
316
317
318
319
320
321

        $this->query_start($sql, null, SQL_QUERY_AUX);
        $result = pg_query($this->pgsql, $sql);
        $this->query_end($result);

        if ($result) {
322
323
324
325
326
327
328
            while ($row = pg_fetch_assoc($result)) {
                if (!preg_match('/CREATE (|UNIQUE )INDEX ([^\s]+) ON '.$tablename.' USING ([^\s]+) \(([^\)]+)\)/i', $row['indexdef'], $matches)) {
                    continue;
                }
                if ($matches[4] === 'id') {
                    continue;
                }
329
                $columns = explode(',', $matches[4]);
330
                $columns = array_map(array($this, 'trim_quotes'), $columns);
331
                $indexes[$row['indexname']] = array('unique'=>!empty($matches[1]),
332
                                              'columns'=>$columns);
333
334
335
336
337
338
339
            }
            pg_free_result($result);
        }
        return $indexes;
    }

    /**
Petr Skoda's avatar
Petr Skoda committed
340
     * Returns detailed information about columns in table. This information is cached internally.
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
     * @param string $table name
     * @param bool $usecache
     * @return array array of database_column_info objects indexed with column names
     */
    public function get_columns($table, $usecache=true) {
        if ($usecache and isset($this->columns[$table])) {
            return $this->columns[$table];
        }

        $this->columns[$table] = array();

        $tablename = $this->prefix.$table;

        $sql = "SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, d.adsrc
                  FROM pg_catalog.pg_class c
356
                  JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
357
358
359
360
361
                  JOIN pg_catalog.pg_type t ON t.oid = a.atttypid
             LEFT JOIN pg_catalog.pg_attrdef d ON (d.adrelid = c.oid AND d.adnum = a.attnum)
                 WHERE relkind = 'r' AND c.relname = '$tablename' AND c.reltype > 0 AND a.attnum > 0
              ORDER BY a.attnum";

362
363
364
365
366
        $this->query_start($sql, null, SQL_QUERY_AUX);
        $result = pg_query($this->pgsql, $sql);
        $this->query_end($result);

        if (!$result) {
367
368
369
370
            return array();
        }
        while ($rawcolumn = pg_fetch_object($result)) {

Petr Skoda's avatar
Petr Skoda committed
371
            $info = new stdClass();
372
373
374
375
376
377
378
379
            $info->name = $rawcolumn->field;
            $matches = null;

            if ($rawcolumn->type === 'varchar') {
                $info->type          = 'varchar';
                $info->meta_type     = 'C';
                $info->max_length    = $rawcolumn->atttypmod - 4;
                $info->scale         = null;
380
381
                $info->not_null      = ($rawcolumn->attnotnull === 't');
                $info->has_default   = ($rawcolumn->atthasdef === 't');
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
                if ($info->has_default) {
                    $parts = explode('::', $rawcolumn->adsrc);
                    if (count($parts) > 1) {
                        $info->default_value = reset($parts);
                        $info->default_value = trim($info->default_value, "'");
                    } else {
                        $info->default_value = $rawcolumn->adsrc;
                    }
                } else {
                    $info->default_value = null;
                }
                $info->primary_key   = false;
                $info->binary        = false;
                $info->unsigned      = null;
                $info->auto_increment= false;
                $info->unique        = null;

            } else if (preg_match('/int(\d)/i', $rawcolumn->type, $matches)) {
                $info->type = 'int';
                if (strpos($rawcolumn->adsrc, 'nextval') === 0) {
                    $info->primary_key   = true;
                    $info->meta_type     = 'R';
                    $info->unique        = true;
                    $info->auto_increment= true;
                    $info->has_default   = false;
                } else {
                    $info->primary_key   = false;
                    $info->meta_type     = 'I';
                    $info->unique        = null;
                    $info->auto_increment= false;
412
                    $info->has_default   = ($rawcolumn->atthasdef === 't');
413
414
415
                }
                $info->max_length    = $matches[1];
                $info->scale         = null;
416
                $info->not_null      = ($rawcolumn->attnotnull === 't');
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
                if ($info->has_default) {
                    $info->default_value = $rawcolumn->adsrc;
                } else {
                    $info->default_value = null;
                }
                $info->binary        = false;
                $info->unsigned      = false;

            } else if ($rawcolumn->type === 'numeric') {
                $info->type = $rawcolumn->type;
                $info->meta_type     = 'N';
                $info->primary_key   = false;
                $info->binary        = false;
                $info->unsigned      = null;
                $info->auto_increment= false;
                $info->unique        = null;
433
434
                $info->not_null      = ($rawcolumn->attnotnull === 't');
                $info->has_default   = ($rawcolumn->atthasdef === 't');
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
                if ($info->has_default) {
                    $info->default_value = $rawcolumn->adsrc;
                } else {
                    $info->default_value = null;
                }
                $info->max_length    = $rawcolumn->atttypmod >> 16;
                $info->scale         = ($rawcolumn->atttypmod & 0xFFFF) - 4;

            } else if (preg_match('/float(\d)/i', $rawcolumn->type, $matches)) {
                $info->type = 'float';
                $info->meta_type     = 'N';
                $info->primary_key   = false;
                $info->binary        = false;
                $info->unsigned      = null;
                $info->auto_increment= false;
                $info->unique        = null;
451
452
                $info->not_null      = ($rawcolumn->attnotnull === 't');
                $info->has_default   = ($rawcolumn->atthasdef === 't');
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
                if ($info->has_default) {
                    $info->default_value = $rawcolumn->adsrc;
                } else {
                    $info->default_value = null;
                }
                // just guess expected number of deciaml places :-(
                if ($matches[1] == 8) {
                    // total 15 digits
                    $info->max_length = 8;
                    $info->scale      = 7;
                } else {
                    // total 6 digits
                    $info->max_length = 4;
                    $info->scale      = 2;
                }

            } else if ($rawcolumn->type === 'text') {
                $info->type          = $rawcolumn->type;
                $info->meta_type     = 'X';
                $info->max_length    = -1;
                $info->scale         = null;
474
475
                $info->not_null      = ($rawcolumn->attnotnull === 't');
                $info->has_default   = ($rawcolumn->atthasdef === 't');
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
                if ($info->has_default) {
                    $parts = explode('::', $rawcolumn->adsrc);
                    if (count($parts) > 1) {
                        $info->default_value = reset($parts);
                        $info->default_value = trim($info->default_value, "'");
                    } else {
                        $info->default_value = $rawcolumn->adsrc;
                    }
                } else {
                    $info->default_value = null;
                }
                $info->primary_key   = false;
                $info->binary        = false;
                $info->unsigned      = null;
                $info->auto_increment= false;
                $info->unique        = null;

            } else if ($rawcolumn->type === 'bytea') {
                $info->type          = $rawcolumn->type;
                $info->meta_type     = 'B';
                $info->max_length    = -1;
                $info->scale         = null;
498
                $info->not_null      = ($rawcolumn->attnotnull === 't');
499
500
501
502
503
504
505
506
507
508
509
510
511
                $info->has_default   = false;
                $info->default_value = null;
                $info->primary_key   = false;
                $info->binary        = true;
                $info->unsigned      = null;
                $info->auto_increment= false;
                $info->unique        = null;

            }

            $this->columns[$table][$info->name] = new database_column_info($info);
        }

512
513
        pg_free_result($result);

514
515
516
        return $this->columns[$table];
    }

517
518
519
520
521
522
523
    /**
     * Normalise values based in RDBMS dependencies (booleans, LOBs...)
     *
     * @param database_column_info $column column metadata corresponding with the value we are going to normalise
     * @param mixed $value value we are going to normalise
     * @return mixed the normalised value
     */
524
    protected function normalise_value($column, $value) {
525
526
527
        if (is_bool($value)) { // Always, convert boolean to int
            $value = (int)$value;

528
        } else if ($column->meta_type === 'B') { // BLOB detected, we return 'blob' array instead of raw value to allow
529
530
531
532
533
            if (!is_null($value)) {             // binding/executing code later to know about its nature
                $value = array('blob' => $value);
            }

        } else if ($value === '') {
534
            if ($column->meta_type === 'I' or $column->meta_type === 'F' or $column->meta_type === 'N') {
535
536
537
538
539
540
                $value = 0; // prevent '' problems in numeric fields
            }
        }
        return $value;
    }

541
542
543
544
545
546
    /**
     * Is db in unicode mode?
     * @return bool
     */
    public function setup_is_unicodedb() {
    /// Get PostgreSQL server_encoding value
547
548
549
550
551
552
        $sql = "SHOW server_encoding";
        $this->query_start($sql, null, SQL_QUERY_AUX);
        $result = pg_query($this->pgsql, $sql);
        $this->query_end($result);

        if (!$result) {
553
554
555
556
557
558
559
560
561
562
563
564
            return false;
        }
        $rawcolumn = pg_fetch_object($result);
        $encoding = $rawcolumn->server_encoding;
        pg_free_result($result);

        return (strtoupper($encoding) == 'UNICODE' || strtoupper($encoding) == 'UTF8');
    }

    /**
     * Do NOT use in code, to be used by database_manager only!
     * @param string $sql query
565
566
     * @return bool true
     * @throws dml_exception if error
567
568
     */
    public function change_database_structure($sql) {
569
        $this->reset_caches();
570
571
572
573
574

        $this->query_start($sql, null, SQL_QUERY_STRUCTURE);
        $result = pg_query($this->pgsql, $sql);
        $this->query_end($result);

575
        pg_free_result($result);
576
577
578
579
580
581
582
583
        return true;
    }

    /**
     * Execute general sql query. Should be used only when no other method suitable.
     * Do NOT use this to make changes in db structure, use database_manager::execute_sql() instead!
     * @param string $sql query
     * @param array $params query parameters
584
585
     * @return bool true
     * @throws dml_exception if error
586
587
588
589
590
     */
    public function execute($sql, array $params=null) {
        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);

        if (strpos($sql, ';') !== false) {
591
            throw new coding_exception('moodle_database::execute() Multiple sql statements found or bound parameters not used properly in query!');
592
593
        }

594
        $this->query_start($sql, $params, SQL_QUERY_UPDATE);
595
        $result = pg_query_params($this->pgsql, $sql, $params);
596
        $this->query_end($result);
597

598
        pg_free_result($result);
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
        return true;
    }

    /**
     * Get a number of records as a moodle_recordset using a SQL statement.
     *
     * Since this method is a little less readable, use of it should be restricted to
     * code where it's possible there might be large datasets being returned.  For known
     * small datasets use get_records_sql - it leads to simpler code.
     *
     * The return type is as for @see function get_recordset.
     *
     * @param string $sql the SQL select query to execute.
     * @param array $params array of sql parameters
     * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
615
     * @return moodle_recordset instance
616
     * @throws dml_exception if error
617
618
     */
    public function get_recordset_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
skodak's avatar
skodak committed
619
620
        $limitfrom = (int)$limitfrom;
        $limitnum  = (int)$limitnum;
621
622
        $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom;
        $limitnum  = ($limitnum < 0)  ? 0 : $limitnum;
623
624
        if ($limitfrom or $limitnum) {
            if ($limitnum < 1) {
625
                $limitnum = "ALL";
626
627
628
629
630
631
            }
            $sql .= " LIMIT $limitnum OFFSET $limitfrom";
        }

        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);

632
        $this->query_start($sql, $params, SQL_QUERY_SELECT);
633
        $result = pg_query_params($this->pgsql, $sql, $params);
634
        $this->query_end($result);
635
636
637
638
639

        return $this->create_recordset($result);
    }

    protected function create_recordset($result) {
640
        return new pgsql_native_moodle_recordset($result, $this->bytea_oid);
641
642
643
644
645
646
647
648
649
650
651
652
653
    }

    /**
     * Get a number of records as an array of objects using a SQL statement.
     *
     * Return value as for @see function get_records.
     *
     * @param string $sql the SQL select query to execute. The first column of this SELECT statement
     *   must be a unique value (usually the 'id' field), as it will be used as the key of the
     *   returned array.
     * @param array $params array of sql parameters
     * @param int $limitfrom return a subset of records, starting at this point (optional, required if $limitnum is set).
     * @param int $limitnum return a subset comprising this many records (optional, required if $limitfrom is set).
654
     * @return array of objects, or empty array if no records were found
655
     * @throws dml_exception if error
656
657
     */
    public function get_records_sql($sql, array $params=null, $limitfrom=0, $limitnum=0) {
skodak's avatar
skodak committed
658
659
        $limitfrom = (int)$limitfrom;
        $limitnum  = (int)$limitnum;
660
661
        $limitfrom = ($limitfrom < 0) ? 0 : $limitfrom;
        $limitnum  = ($limitnum < 0)  ? 0 : $limitnum;
662
663
        if ($limitfrom or $limitnum) {
            if ($limitnum < 1) {
664
                $limitnum = "ALL";
665
666
667
668
669
            }
            $sql .= " LIMIT $limitnum OFFSET $limitfrom";
        }

        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);
670
        $this->query_start($sql, $params, SQL_QUERY_SELECT);
671
        $result = pg_query_params($this->pgsql, $sql, $params);
672
        $this->query_end($result);
673

674
675
676
677
678
679
680
681
682
        // find out if there are any blobs
        $numrows = pg_num_fields($result);
        $blobs = array();
        for($i=0; $i<$numrows; $i++) {
            $type_oid = pg_field_type_oid($result, $i);
            if ($type_oid == $this->bytea_oid) {
                $blobs[] = pg_field_name($result, $i);
            }
        }
683
684
685
686
687
688
689
690

        $rows = pg_fetch_all($result);
        pg_free_result($result);

        $return = array();
        if ($rows) {
            foreach ($rows as $row) {
                $id = reset($row);
691
692
                if ($blobs) {
                    foreach ($blobs as $blob) {
Petr Skoda's avatar
Petr Skoda committed
693
                        // note: in PostgreSQL 9.0 the returned blobs are hexencoded by default - see http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-BYTEA-OUTPUT
694
                        $row[$blob] = $row[$blob] !== null ? pg_unescape_bytea($row[$blob]) : null;
695
696
                    }
                }
697
698
699
700
                if (isset($return[$id])) {
                    $colname = key($row);
                    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);
                }
701
702
703
                $return[$id] = (object)$row;
            }
        }
704

705
706
707
708
709
710
711
712
        return $return;
    }

    /**
     * Selects records and return values (first field) as an array using a SQL statement.
     *
     * @param string $sql The SQL query
     * @param array $params array of sql parameters
713
     * @return array of values
714
     * @throws dml_exception if error
715
716
717
718
     */
    public function get_fieldset_sql($sql, array $params=null) {
        list($sql, $params, $type) = $this->fix_sql_params($sql, $params);

719
        $this->query_start($sql, $params, SQL_QUERY_SELECT);
720
        $result = pg_query_params($this->pgsql, $sql, $params);
721
        $this->query_end($result);
722
723
724

        $return = pg_fetch_all_columns($result, 0);
        pg_free_result($result);
725

726
727
728
729
730
731
732
733
734
735
        return $return;
    }

    /**
     * Insert new record into database, as fast as possible, no safety checks, lobs not supported.
     * @param string $table name
     * @param mixed $params data record as object or array
     * @param bool $returnit return it of inserted record
     * @param bool $bulk true means repeated inserts expected
     * @param bool $customsequence true if 'id' included in $params, disables $returnid
736
     * @return bool|int true or new id
737
     * @throws dml_exception if error
738
739
740
741
742
743
744
745
746
747
     */
    public function insert_record_raw($table, $params, $returnid=true, $bulk=false, $customsequence=false) {
        if (!is_array($params)) {
            $params = (array)$params;
        }

        $returning = "";

        if ($customsequence) {
            if (!isset($params['id'])) {
748
                throw new coding_exception('moodle_database::insert_record_raw() id field must be specified if custom sequences used.');
749
750
751
752
            }
            $returnid = false;
        } else {
            if ($returnid) {
753
754
                $returning = "RETURNING id";
                unset($params['id']);
755
756
757
758
759
760
            } else {
                unset($params['id']);
            }
        }

        if (empty($params)) {
761
            throw new coding_exception('moodle_database::insert_record_raw() no fields found.');
762
763
764
765
766
767
768
        }

        $fields = implode(',', array_keys($params));
        $values = array();
        $count = count($params);
        for ($i=1; $i<=$count; $i++) {
            $values[] = "\$".$i;
769
        }
770
771
772
        $values = implode(',', $values);

        $sql = "INSERT INTO {$this->prefix}$table ($fields) VALUES($values) $returning";
773
        $this->query_start($sql, $params, SQL_QUERY_INSERT);
774
        $result = pg_query_params($this->pgsql, $sql, $params);
775
        $this->query_end($result);
776
777
778
779
780

        if ($returning !== "") {
            $row = pg_fetch_assoc($result);
            $params['id'] = reset($row);
        }
781
        pg_free_result($result);
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798

        if (!$returnid) {
            return true;
        }

        return (int)$params['id'];
    }

    /**
     * Insert a record into a table and return the "id" field if required.
     *
     * Some conversions and safety checks are carried out. Lobs are supported.
     * If the return ID isn't required, then this just reports success as true/false.
     * $data is an object containing needed data
     * @param string $table The database table to be inserted into
     * @param object $data A data object with values for one or more fields in the record
     * @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.
799
     * @return bool|int true or new id
800
     * @throws dml_exception if error
801
802
     */
    public function insert_record($table, $dataobject, $returnid=true, $bulk=false) {
803
        $dataobject = (array)$dataobject;
804
805
806
807
808
809

        $columns = $this->get_columns($table);
        $cleaned = array();
        $blobs   = array();

        foreach ($dataobject as $field=>$value) {
810
811
812
            if ($field === 'id') {
                continue;
            }
813
814
815
816
            if (!isset($columns[$field])) {
                continue;
            }
            $column = $columns[$field];
817
818
819
820
821
822
            $normalised_value = $this->normalise_value($column, $value);
            if (is_array($normalised_value) && array_key_exists('blob', $normalised_value)) {
                $cleaned[$field] = '@#BLOB#@';
                $blobs[$field] = $normalised_value['blob'];
            } else {
                $cleaned[$field] = $normalised_value;
823
824
825
826
827
828
829
            }
        }

        if (empty($blobs)) {
            return $this->insert_record_raw($table, $cleaned, $returnid, $bulk);
        }

830
        $id = $this->insert_record_raw($table, $cleaned, true, $bulk);
831
832
833
834

        foreach ($blobs as $key=>$value) {
            $value = pg_escape_bytea($this->pgsql, $value);
            $sql = "UPDATE {$this->prefix}$table SET $key = '$value'::bytea WHERE id = $id";
835
            $this->query_start($sql, NULL, SQL_QUERY_UPDATE);
836
            $result = pg_query($this->pgsql, $sql);
837
            $this->query_end($result);
838
839
840
            if ($result !== false) {
                pg_free_result($result);
            }
841
842
843
844
845
846
847
848
849
850
851
852
        }

        return ($returnid ? $id : true);

    }

    /**
     * Import a record into a table, id field is required.
     * Safety checks are NOT carried out. Lobs are supported.
     *
     * @param string $table name of database table to be inserted into
     * @param object $dataobject A data object with values for one or more fields in the record
853
854
     * @return bool true
     * @throws dml_exception if error
855
856
     */
    public function import_record($table, $dataobject) {
857
        $dataobject = (array)$dataobject;
858
859
860

        $columns = $this->get_columns($table);
        $cleaned = array();
861
        $blobs   = array();
862
863
864
865
866

        foreach ($dataobject as $field=>$value) {
            if (!isset($columns[$field])) {
                continue;
            }
867
868
869
870
871
872
873
874
            if ($columns[$field]->meta_type === 'B') {
                if (!is_null($value)) {
                    $cleaned[$field] = '@#BLOB#@';
                    $blobs[$field] = $value;
                    continue;
                }
            }

875
876
877
            $cleaned[$field] = $value;
        }

878
        $this->insert_record_raw($table, $cleaned, false, true, true);
879
        $id = $dataobject['id'];
880
881
882
883
884
885
886
887
888
889
890
891
892

        foreach ($blobs as $key=>$value) {
            $value = pg_escape_bytea($this->pgsql, $value);
            $sql = "UPDATE {$this->prefix}$table SET $key = '$value'::bytea WHERE id = $id";
            $this->query_start($sql, NULL, SQL_QUERY_UPDATE);
            $result = pg_query($this->pgsql, $sql);
            $this->query_end($result);
            if ($result !== false) {
                pg_free_result($result);
            }
        }

        return true;
893
894
895
896
897
898
899
    }

    /**
     * Update record in database, as fast as possible, no safety checks, lobs not supported.
     * @param string $table name
     * @param mixed $params data record as object or array
     * @param bool true means repeated updates expected
900
901
     * @return bool true
     * @throws dml_exception if error
902
903
     */
    public function update_record_raw($table, $params, $bulk=false) {
904
905
        $params = (array)$params;

906
        if (!isset($params['id'])) {
907
            throw new coding_exception('moodle_database::update_record_raw() id field must be specified.');
908
909
910
911
912
        }
        $id = $params['id'];
        unset($params['id']);

        if (empty($params)) {
913
            throw new coding_exception('moodle_database::update_record_raw() no fields found.');
914
915
916
917
918
919
920
921
922
923
924
925
926
927
        }

        $i = 1;

        $sets = array();
        foreach ($params as $field=>$value) {
            $sets[] = "$field = \$".$i++;
        }

        $params[] = $id; // last ? in WHERE condition

        $sets = implode(',', $sets);
        $sql = "UPDATE {$this->prefix}$table SET $sets WHERE id=\$".$i;

928
        $this->query_start($sql, $params, SQL_QUERY_UPDATE);
929
        $result = pg_query_params($this->pgsql, $sql, $params);
930
        $this->query_end($result);
931

932
        pg_free_result($result);
933
934
935
936
937
938
939
940
941
942
943
944
945
        return true;
    }

    /**
     * Update a record in a table
     *
     * $dataobject is an object containing needed data
     * Relies on $dataobject having a variable "id" to
     * specify the record to update
     *
     * @param string $table The database table to be checked against.
     * @param object $dataobject An object with contents equal to fieldname=>fieldvalue. Must have an entry for 'id' to map to the table specified.
     * @param bool true means repeated updates expected
946
947
     * @return bool true
     * @throws dml_exception if error
948
949
     */
    public function update_record($table, $dataobject, $bulk=false) {
950
        $dataobject = (array)$dataobject;
951
952
953

        $columns = $this->get_columns($table);
        $cleaned = array();
954
        $blobs   = array();
955
956
957
958
959

        foreach ($dataobject as $field=>$value) {
            if (!isset($columns[$field])) {
                continue;
            }
960
            $column = $columns[$field];
961
962
963
964
965
966
            $normalised_value = $this->normalise_value($column, $value);
            if (is_array($normalised_value) && array_key_exists('blob', $normalised_value)) {
                $cleaned[$field] = '@#BLOB#@';
                $blobs[$field] = $normalised_value['blob'];
            } else {
                $cleaned[$field] = $normalised_value;
967
968
969
            }
        }

970
        $this->update_record_raw($table, $cleaned, $bulk);
971
972
973
974
975

        if (empty($blobs)) {
            return true;
        }

976
        $id = (int)$dataobject['id'];
977

978
979
980
        foreach ($blobs as $key=>$value) {
            $value = pg_escape_bytea($this->pgsql, $value);
            $sql = "UPDATE {$this->prefix}$table SET $key = '$value'::bytea WHERE id = $id";
981
            $this->query_start($sql, NULL, SQL_QUERY_UPDATE);
982
            $result = pg_query($this->pgsql, $sql);
983
            $this->query_end($result);
984

985
986
987
988
            pg_free_result($result);
        }

        return true;
989
990
991
992
993
994
995
996
997
998
    }

    /**
     * Set a single field in every table record which match a particular WHERE clause.
     *
     * @param string $table The database table to be checked against.
     * @param string $newfield the field to set.
     * @param string $newvalue the value to set the field to.
     * @param string $select A fragment of SQL to be used in a where clause in the SQL call.
     * @param array $params array of sql parameters
999
1000
     * @return bool true
     * @throws dml_exception if error
For faster browsing, not all history is shown. View entire blame