Subversion Repositories Applications.papyrus

Rev

Rev 443 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
320 jpm 1
<?php
2
 
3
/* vim: set expandtab tabstop=4 shiftwidth=4 softtabstop=4: */
4
 
5
/**
6
 * The PEAR DB driver for PHP's pgsql extension
7
 * for interacting with PostgreSQL databases
8
 *
9
 * PHP versions 4 and 5
10
 *
11
 * LICENSE: This source file is subject to version 3.0 of the PHP license
12
 * that is available through the world-wide-web at the following URI:
13
 * http://www.php.net/license/3_0.txt.  If you did not receive a copy of
14
 * the PHP License and are unable to obtain it through the web, please
15
 * send a note to license@php.net so we can mail you a copy immediately.
16
 *
17
 * @category   Database
18
 * @package    DB
19
 * @author     Rui Hirokawa <hirokawa@php.net>
20
 * @author     Stig Bakken <ssb@php.net>
21
 * @author     Daniel Convissor <danielc@php.net>
22
 * @copyright  1997-2005 The PHP Group
23
 * @license    http://www.php.net/license/3_0.txt  PHP License 3.0
1173 jp_milcent 24
 * @version    CVS: $Id: pgsql.php,v 1.3 2006-12-14 15:04:28 jp_milcent Exp $
320 jpm 25
 * @link       http://pear.php.net/package/DB
26
 */
27
 
28
/**
29
 * Obtain the DB_common class so it can be extended from
30
 */
31
require_once 'DB/common.php';
32
 
33
/**
34
 * The methods PEAR DB uses to interact with PHP's pgsql extension
35
 * for interacting with PostgreSQL databases
36
 *
37
 * These methods overload the ones declared in DB_common.
38
 *
39
 * @category   Database
40
 * @package    DB
41
 * @author     Rui Hirokawa <hirokawa@php.net>
42
 * @author     Stig Bakken <ssb@php.net>
43
 * @author     Daniel Convissor <danielc@php.net>
44
 * @copyright  1997-2005 The PHP Group
45
 * @license    http://www.php.net/license/3_0.txt  PHP License 3.0
1173 jp_milcent 46
 * @version    Release: 1.7.6
320 jpm 47
 * @link       http://pear.php.net/package/DB
48
 */
49
class DB_pgsql extends DB_common
50
{
51
    // {{{ properties
52
 
53
    /**
54
     * The DB driver type (mysql, oci8, odbc, etc.)
55
     * @var string
56
     */
57
    var $phptype = 'pgsql';
58
 
59
    /**
60
     * The database syntax variant to be used (db2, access, etc.), if any
61
     * @var string
62
     */
63
    var $dbsyntax = 'pgsql';
64
 
65
    /**
66
     * The capabilities of this DB implementation
67
     *
68
     * The 'new_link' element contains the PHP version that first provided
69
     * new_link support for this DBMS.  Contains false if it's unsupported.
70
     *
71
     * Meaning of the 'limit' element:
72
     *   + 'emulate' = emulate with fetch row by number
73
     *   + 'alter'   = alter the query
74
     *   + false     = skip rows
75
     *
76
     * @var array
77
     */
78
    var $features = array(
79
        'limit'         => 'alter',
80
        'new_link'      => '4.3.0',
81
        'numrows'       => true,
82
        'pconnect'      => true,
83
        'prepare'       => false,
84
        'ssl'           => true,
85
        'transactions'  => true,
86
    );
87
 
88
    /**
89
     * A mapping of native error codes to DB error codes
90
     * @var array
91
     */
92
    var $errorcode_map = array(
93
    );
94
 
95
    /**
96
     * The raw database connection created by PHP
97
     * @var resource
98
     */
99
    var $connection;
100
 
101
    /**
102
     * The DSN information for connecting to a database
103
     * @var array
104
     */
105
    var $dsn = array();
106
 
107
 
108
    /**
109
     * Should data manipulation queries be committed automatically?
110
     * @var bool
111
     * @access private
112
     */
113
    var $autocommit = true;
114
 
115
    /**
116
     * The quantity of transactions begun
117
     *
118
     * {@internal  While this is private, it can't actually be designated
119
     * private in PHP 5 because it is directly accessed in the test suite.}}
120
     *
121
     * @var integer
122
     * @access private
123
     */
124
    var $transaction_opcount = 0;
125
 
126
    /**
127
     * The number of rows affected by a data manipulation query
128
     * @var integer
129
     */
130
    var $affected = 0;
131
 
132
    /**
133
     * The current row being looked at in fetchInto()
134
     * @var array
135
     * @access private
136
     */
137
    var $row = array();
138
 
139
    /**
140
     * The number of rows in a given result set
141
     * @var array
142
     * @access private
143
     */
144
    var $_num_rows = array();
145
 
146
 
147
    // }}}
148
    // {{{ constructor
149
 
150
    /**
151
     * This constructor calls <kbd>$this->DB_common()</kbd>
152
     *
153
     * @return void
154
     */
155
    function DB_pgsql()
156
    {
157
        $this->DB_common();
158
    }
159
 
160
    // }}}
161
    // {{{ connect()
162
 
163
    /**
164
     * Connect to the database server, log in and open the database
165
     *
166
     * Don't call this method directly.  Use DB::connect() instead.
167
     *
168
     * PEAR DB's pgsql driver supports the following extra DSN options:
169
     *   + connect_timeout  How many seconds to wait for a connection to
170
     *                       be established.  Available since PEAR DB 1.7.0.
171
     *   + new_link         If set to true, causes subsequent calls to
172
     *                       connect() to return a new connection link
173
     *                       instead of the existing one.  WARNING: this is
174
     *                       not portable to other DBMS's.  Available only
175
     *                       if PHP is >= 4.3.0 and PEAR DB is >= 1.7.0.
176
     *   + options          Command line options to be sent to the server.
177
     *                       Available since PEAR DB 1.6.4.
178
     *   + service          Specifies a service name in pg_service.conf that
179
     *                       holds additional connection parameters.
180
     *                       Available since PEAR DB 1.7.0.
181
     *   + sslmode          How should SSL be used when connecting?  Values:
182
     *                       disable, allow, prefer or require.
183
     *                       Available since PEAR DB 1.7.0.
184
     *   + tty              This was used to specify where to send server
185
     *                       debug output.  Available since PEAR DB 1.6.4.
186
     *
187
     * Example of connecting to a new link via a socket:
188
     * <code>
189
     * require_once 'DB.php';
190
     *
191
     * $dsn = 'pgsql://user:pass@unix(/tmp)/dbname?new_link=true';
192
     * $options = array(
193
     *     'portability' => DB_PORTABILITY_ALL,
194
     * );
195
     *
196
     * $db =& DB::connect($dsn, $options);
197
     * if (PEAR::isError($db)) {
198
     *     die($db->getMessage());
199
     * }
200
     * </code>
201
     *
202
     * @param array $dsn         the data source name
203
     * @param bool  $persistent  should the connection be persistent?
204
     *
205
     * @return int  DB_OK on success. A DB_Error object on failure.
206
     *
207
     * @link http://www.postgresql.org/docs/current/static/libpq.html#LIBPQ-CONNECT
208
     */
209
    function connect($dsn, $persistent = false)
210
    {
211
        if (!PEAR::loadExtension('pgsql')) {
212
            return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
213
        }
214
 
215
        $this->dsn = $dsn;
216
        if ($dsn['dbsyntax']) {
217
            $this->dbsyntax = $dsn['dbsyntax'];
218
        }
219
 
220
        $protocol = $dsn['protocol'] ? $dsn['protocol'] : 'tcp';
221
 
222
        $params = array('');
223
        if ($protocol == 'tcp') {
224
            if ($dsn['hostspec']) {
225
                $params[0] .= 'host=' . $dsn['hostspec'];
226
            }
227
            if ($dsn['port']) {
228
                $params[0] .= ' port=' . $dsn['port'];
229
            }
230
        } elseif ($protocol == 'unix') {
231
            // Allow for pg socket in non-standard locations.
232
            if ($dsn['socket']) {
233
                $params[0] .= 'host=' . $dsn['socket'];
234
            }
235
            if ($dsn['port']) {
236
                $params[0] .= ' port=' . $dsn['port'];
237
            }
238
        }
239
        if ($dsn['database']) {
240
            $params[0] .= ' dbname=\'' . addslashes($dsn['database']) . '\'';
241
        }
242
        if ($dsn['username']) {
243
            $params[0] .= ' user=\'' . addslashes($dsn['username']) . '\'';
244
        }
245
        if ($dsn['password']) {
246
            $params[0] .= ' password=\'' . addslashes($dsn['password']) . '\'';
247
        }
248
        if (!empty($dsn['options'])) {
249
            $params[0] .= ' options=' . $dsn['options'];
250
        }
251
        if (!empty($dsn['tty'])) {
252
            $params[0] .= ' tty=' . $dsn['tty'];
253
        }
254
        if (!empty($dsn['connect_timeout'])) {
255
            $params[0] .= ' connect_timeout=' . $dsn['connect_timeout'];
256
        }
257
        if (!empty($dsn['sslmode'])) {
258
            $params[0] .= ' sslmode=' . $dsn['sslmode'];
259
        }
260
        if (!empty($dsn['service'])) {
261
            $params[0] .= ' service=' . $dsn['service'];
262
        }
263
 
264
        if (isset($dsn['new_link'])
265
            && ($dsn['new_link'] == 'true' || $dsn['new_link'] === true))
266
        {
267
            if (version_compare(phpversion(), '4.3.0', '>=')) {
268
                $params[] = PGSQL_CONNECT_FORCE_NEW;
269
            }
270
        }
271
 
272
        $connect_function = $persistent ? 'pg_pconnect' : 'pg_connect';
273
 
274
        $ini = ini_get('track_errors');
275
        $php_errormsg = '';
276
        if ($ini) {
277
            $this->connection = @call_user_func_array($connect_function,
278
                                                      $params);
279
        } else {
280
            ini_set('track_errors', 1);
281
            $this->connection = @call_user_func_array($connect_function,
282
                                                      $params);
283
            ini_set('track_errors', $ini);
284
        }
285
 
286
        if (!$this->connection) {
287
            return $this->raiseError(DB_ERROR_CONNECT_FAILED,
288
                                     null, null, null,
289
                                     $php_errormsg);
290
        }
291
        return DB_OK;
292
    }
293
 
294
    // }}}
295
    // {{{ disconnect()
296
 
297
    /**
298
     * Disconnects from the database server
299
     *
300
     * @return bool  TRUE on success, FALSE on failure
301
     */
302
    function disconnect()
303
    {
304
        $ret = @pg_close($this->connection);
305
        $this->connection = null;
306
        return $ret;
307
    }
308
 
309
    // }}}
310
    // {{{ simpleQuery()
311
 
312
    /**
313
     * Sends a query to the database server
314
     *
315
     * @param string  the SQL query string
316
     *
317
     * @return mixed  + a PHP result resrouce for successful SELECT queries
318
     *                + the DB_OK constant for other successful queries
319
     *                + a DB_Error object on failure
320
     */
321
    function simpleQuery($query)
322
    {
323
        $ismanip = DB::isManip($query);
324
        $this->last_query = $query;
325
        $query = $this->modifyQuery($query);
326
        if (!$this->autocommit && $ismanip) {
327
            if ($this->transaction_opcount == 0) {
328
                $result = @pg_exec($this->connection, 'begin;');
329
                if (!$result) {
330
                    return $this->pgsqlRaiseError();
331
                }
332
            }
333
            $this->transaction_opcount++;
334
        }
335
        $result = @pg_exec($this->connection, $query);
336
        if (!$result) {
337
            return $this->pgsqlRaiseError();
338
        }
339
        // Determine which queries that should return data, and which
340
        // should return an error code only.
341
        if ($ismanip) {
342
            $this->affected = @pg_affected_rows($result);
343
            return DB_OK;
344
        } elseif (preg_match('/^\s*\(*\s*(SELECT|EXPLAIN|SHOW)\s/si', $query)) {
345
            /* PostgreSQL commands:
346
               ABORT, ALTER, BEGIN, CLOSE, CLUSTER, COMMIT, COPY,
347
               CREATE, DECLARE, DELETE, DROP TABLE, EXPLAIN, FETCH,
348
               GRANT, INSERT, LISTEN, LOAD, LOCK, MOVE, NOTIFY, RESET,
349
               REVOKE, ROLLBACK, SELECT, SELECT INTO, SET, SHOW,
350
               UNLISTEN, UPDATE, VACUUM
351
            */
352
            $this->row[(int)$result] = 0; // reset the row counter.
353
            $numrows = $this->numRows($result);
354
            if (is_object($numrows)) {
355
                return $numrows;
356
            }
357
            $this->_num_rows[(int)$result] = $numrows;
358
            $this->affected = 0;
359
            return $result;
360
        } else {
361
            $this->affected = 0;
362
            return DB_OK;
363
        }
364
    }
365
 
366
    // }}}
367
    // {{{ nextResult()
368
 
369
    /**
370
     * Move the internal pgsql result pointer to the next available result
371
     *
372
     * @param a valid fbsql result resource
373
     *
374
     * @access public
375
     *
376
     * @return true if a result is available otherwise return false
377
     */
378
    function nextResult($result)
379
    {
380
        return false;
381
    }
382
 
383
    // }}}
384
    // {{{ fetchInto()
385
 
386
    /**
387
     * Places a row from the result set into the given array
388
     *
389
     * Formating of the array and the data therein are configurable.
390
     * See DB_result::fetchInto() for more information.
391
     *
392
     * This method is not meant to be called directly.  Use
393
     * DB_result::fetchInto() instead.  It can't be declared "protected"
394
     * because DB_result is a separate object.
395
     *
396
     * @param resource $result    the query result resource
397
     * @param array    $arr       the referenced array to put the data in
398
     * @param int      $fetchmode how the resulting array should be indexed
399
     * @param int      $rownum    the row number to fetch (0 = first row)
400
     *
401
     * @return mixed  DB_OK on success, NULL when the end of a result set is
402
     *                 reached or on failure
403
     *
404
     * @see DB_result::fetchInto()
405
     */
406
    function fetchInto($result, &$arr, $fetchmode, $rownum = null)
407
    {
408
        $result_int = (int)$result;
409
        $rownum = ($rownum !== null) ? $rownum : $this->row[$result_int];
410
        if ($rownum >= $this->_num_rows[$result_int]) {
411
            return null;
412
        }
413
        if ($fetchmode & DB_FETCHMODE_ASSOC) {
414
            $arr = @pg_fetch_array($result, $rownum, PGSQL_ASSOC);
415
            if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE && $arr) {
416
                $arr = array_change_key_case($arr, CASE_LOWER);
417
            }
418
        } else {
419
            $arr = @pg_fetch_row($result, $rownum);
420
        }
421
        if (!$arr) {
422
            return null;
423
        }
424
        if ($this->options['portability'] & DB_PORTABILITY_RTRIM) {
425
            $this->_rtrimArrayValues($arr);
426
        }
427
        if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
428
            $this->_convertNullArrayValuesToEmpty($arr);
429
        }
430
        $this->row[$result_int] = ++$rownum;
431
        return DB_OK;
432
    }
433
 
434
    // }}}
435
    // {{{ freeResult()
436
 
437
    /**
438
     * Deletes the result set and frees the memory occupied by the result set
439
     *
440
     * This method is not meant to be called directly.  Use
441
     * DB_result::free() instead.  It can't be declared "protected"
442
     * because DB_result is a separate object.
443
     *
444
     * @param resource $result  PHP's query result resource
445
     *
446
     * @return bool  TRUE on success, FALSE if $result is invalid
447
     *
448
     * @see DB_result::free()
449
     */
450
    function freeResult($result)
451
    {
452
        if (is_resource($result)) {
453
            unset($this->row[(int)$result]);
454
            unset($this->_num_rows[(int)$result]);
455
            $this->affected = 0;
456
            return @pg_freeresult($result);
457
        }
458
        return false;
459
    }
460
 
461
    // }}}
462
    // {{{ quote()
463
 
464
    /**
465
     * @deprecated  Deprecated in release 1.6.0
466
     * @internal
467
     */
468
    function quote($str)
469
    {
470
        return $this->quoteSmart($str);
471
    }
472
 
473
    // }}}
474
    // {{{ quoteSmart()
475
 
476
    /**
477
     * Formats input so it can be safely used in a query
478
     *
479
     * @param mixed $in  the data to be formatted
480
     *
481
     * @return mixed  the formatted data.  The format depends on the input's
482
     *                 PHP type:
483
     *                 + null = the string <samp>NULL</samp>
484
     *                 + boolean = string <samp>TRUE</samp> or <samp>FALSE</samp>
485
     *                 + integer or double = the unquoted number
486
     *                 + other (including strings and numeric strings) =
487
     *                   the data escaped according to MySQL's settings
488
     *                   then encapsulated between single quotes
489
     *
490
     * @see DB_common::quoteSmart()
491
     * @since Method available since Release 1.6.0
492
     */
493
    function quoteSmart($in)
494
    {
495
        if (is_int($in) || is_double($in)) {
496
            return $in;
497
        } elseif (is_bool($in)) {
498
            return $in ? 'TRUE' : 'FALSE';
499
        } elseif (is_null($in)) {
500
            return 'NULL';
501
        } else {
502
            return "'" . $this->escapeSimple($in) . "'";
503
        }
504
    }
505
 
506
    // }}}
507
    // {{{ escapeSimple()
508
 
509
    /**
510
     * Escapes a string according to the current DBMS's standards
511
     *
512
     * {@internal PostgreSQL treats a backslash as an escape character,
513
     * so they are escaped as well.
514
     *
515
     * Not using pg_escape_string() yet because it requires PostgreSQL
516
     * to be at version 7.2 or greater.}}
517
     *
518
     * @param string $str  the string to be escaped
519
     *
520
     * @return string  the escaped string
521
     *
522
     * @see DB_common::quoteSmart()
523
     * @since Method available since Release 1.6.0
524
     */
525
    function escapeSimple($str)
526
    {
527
        return str_replace("'", "''", str_replace('\\', '\\\\', $str));
528
    }
529
 
530
    // }}}
531
    // {{{ numCols()
532
 
533
    /**
534
     * Gets the number of columns in a result set
535
     *
536
     * This method is not meant to be called directly.  Use
537
     * DB_result::numCols() instead.  It can't be declared "protected"
538
     * because DB_result is a separate object.
539
     *
540
     * @param resource $result  PHP's query result resource
541
     *
542
     * @return int  the number of columns.  A DB_Error object on failure.
543
     *
544
     * @see DB_result::numCols()
545
     */
546
    function numCols($result)
547
    {
548
        $cols = @pg_numfields($result);
549
        if (!$cols) {
550
            return $this->pgsqlRaiseError();
551
        }
552
        return $cols;
553
    }
554
 
555
    // }}}
556
    // {{{ numRows()
557
 
558
    /**
559
     * Gets the number of rows in a result set
560
     *
561
     * This method is not meant to be called directly.  Use
562
     * DB_result::numRows() instead.  It can't be declared "protected"
563
     * because DB_result is a separate object.
564
     *
565
     * @param resource $result  PHP's query result resource
566
     *
567
     * @return int  the number of rows.  A DB_Error object on failure.
568
     *
569
     * @see DB_result::numRows()
570
     */
571
    function numRows($result)
572
    {
573
        $rows = @pg_numrows($result);
574
        if ($rows === null) {
575
            return $this->pgsqlRaiseError();
576
        }
577
        return $rows;
578
    }
579
 
580
    // }}}
581
    // {{{ autoCommit()
582
 
583
    /**
584
     * Enables or disables automatic commits
585
     *
586
     * @param bool $onoff  true turns it on, false turns it off
587
     *
588
     * @return int  DB_OK on success.  A DB_Error object if the driver
589
     *               doesn't support auto-committing transactions.
590
     */
591
    function autoCommit($onoff = false)
592
    {
593
        // XXX if $this->transaction_opcount > 0, we should probably
594
        // issue a warning here.
595
        $this->autocommit = $onoff ? true : false;
596
        return DB_OK;
597
    }
598
 
599
    // }}}
600
    // {{{ commit()
601
 
602
    /**
603
     * Commits the current transaction
604
     *
605
     * @return int  DB_OK on success.  A DB_Error object on failure.
606
     */
607
    function commit()
608
    {
609
        if ($this->transaction_opcount > 0) {
610
            // (disabled) hack to shut up error messages from libpq.a
611
            //@fclose(@fopen("php://stderr", "w"));
612
            $result = @pg_exec($this->connection, 'end;');
613
            $this->transaction_opcount = 0;
614
            if (!$result) {
615
                return $this->pgsqlRaiseError();
616
            }
617
        }
618
        return DB_OK;
619
    }
620
 
621
    // }}}
622
    // {{{ rollback()
623
 
624
    /**
625
     * Reverts the current transaction
626
     *
627
     * @return int  DB_OK on success.  A DB_Error object on failure.
628
     */
629
    function rollback()
630
    {
631
        if ($this->transaction_opcount > 0) {
632
            $result = @pg_exec($this->connection, 'abort;');
633
            $this->transaction_opcount = 0;
634
            if (!$result) {
635
                return $this->pgsqlRaiseError();
636
            }
637
        }
638
        return DB_OK;
639
    }
640
 
641
    // }}}
642
    // {{{ affectedRows()
643
 
644
    /**
645
     * Determines the number of rows affected by a data maniuplation query
646
     *
647
     * 0 is returned for queries that don't manipulate data.
648
     *
649
     * @return int  the number of rows.  A DB_Error object on failure.
650
     */
651
    function affectedRows()
652
    {
653
        return $this->affected;
654
    }
655
 
656
    // }}}
657
    // {{{ nextId()
658
 
659
    /**
660
     * Returns the next free id in a sequence
661
     *
662
     * @param string  $seq_name  name of the sequence
663
     * @param boolean $ondemand  when true, the seqence is automatically
664
     *                            created if it does not exist
665
     *
666
     * @return int  the next id number in the sequence.
667
     *               A DB_Error object on failure.
668
     *
669
     * @see DB_common::nextID(), DB_common::getSequenceName(),
670
     *      DB_pgsql::createSequence(), DB_pgsql::dropSequence()
671
     */
672
    function nextId($seq_name, $ondemand = true)
673
    {
674
        $seqname = $this->getSequenceName($seq_name);
675
        $repeat = false;
676
        do {
677
            $this->pushErrorHandling(PEAR_ERROR_RETURN);
678
            $result =& $this->query("SELECT NEXTVAL('${seqname}')");
679
            $this->popErrorHandling();
680
            if ($ondemand && DB::isError($result) &&
681
                $result->getCode() == DB_ERROR_NOSUCHTABLE) {
682
                $repeat = true;
683
                $this->pushErrorHandling(PEAR_ERROR_RETURN);
684
                $result = $this->createSequence($seq_name);
685
                $this->popErrorHandling();
686
                if (DB::isError($result)) {
687
                    return $this->raiseError($result);
688
                }
689
            } else {
690
                $repeat = false;
691
            }
692
        } while ($repeat);
693
        if (DB::isError($result)) {
694
            return $this->raiseError($result);
695
        }
696
        $arr = $result->fetchRow(DB_FETCHMODE_ORDERED);
697
        $result->free();
698
        return $arr[0];
699
    }
700
 
701
    // }}}
702
    // {{{ createSequence()
703
 
704
    /**
705
     * Creates a new sequence
706
     *
707
     * @param string $seq_name  name of the new sequence
708
     *
709
     * @return int  DB_OK on success.  A DB_Error object on failure.
710
     *
711
     * @see DB_common::createSequence(), DB_common::getSequenceName(),
712
     *      DB_pgsql::nextID(), DB_pgsql::dropSequence()
713
     */
714
    function createSequence($seq_name)
715
    {
716
        $seqname = $this->getSequenceName($seq_name);
717
        $result = $this->query("CREATE SEQUENCE ${seqname}");
718
        return $result;
719
    }
720
 
721
    // }}}
722
    // {{{ dropSequence()
723
 
724
    /**
725
     * Deletes a sequence
726
     *
727
     * @param string $seq_name  name of the sequence to be deleted
728
     *
729
     * @return int  DB_OK on success.  A DB_Error object on failure.
730
     *
731
     * @see DB_common::dropSequence(), DB_common::getSequenceName(),
732
     *      DB_pgsql::nextID(), DB_pgsql::createSequence()
733
     */
734
    function dropSequence($seq_name)
735
    {
736
        return $this->query('DROP SEQUENCE '
737
                            . $this->getSequenceName($seq_name));
738
    }
739
 
740
    // }}}
741
    // {{{ modifyLimitQuery()
742
 
743
    /**
744
     * Adds LIMIT clauses to a query string according to current DBMS standards
745
     *
746
     * @param string $query   the query to modify
747
     * @param int    $from    the row to start to fetching (0 = the first row)
748
     * @param int    $count   the numbers of rows to fetch
749
     * @param mixed  $params  array, string or numeric data to be used in
750
     *                         execution of the statement.  Quantity of items
751
     *                         passed must match quantity of placeholders in
752
     *                         query:  meaning 1 placeholder for non-array
753
     *                         parameters or 1 placeholder per array element.
754
     *
755
     * @return string  the query string with LIMIT clauses added
756
     *
757
     * @access protected
758
     */
759
    function modifyLimitQuery($query, $from, $count, $params = array())
760
    {
761
        return "$query LIMIT $count OFFSET $from";
762
    }
763
 
764
    // }}}
765
    // {{{ pgsqlRaiseError()
766
 
767
    /**
768
     * Produces a DB_Error object regarding the current problem
769
     *
770
     * @param int $errno  if the error is being manually raised pass a
771
     *                     DB_ERROR* constant here.  If this isn't passed
772
     *                     the error information gathered from the DBMS.
773
     *
774
     * @return object  the DB_Error object
775
     *
776
     * @see DB_common::raiseError(),
777
     *      DB_pgsql::errorNative(), DB_pgsql::errorCode()
778
     */
779
    function pgsqlRaiseError($errno = null)
780
    {
781
        $native = $this->errorNative();
782
        if ($errno === null) {
783
            $errno = $this->errorCode($native);
784
        }
785
        return $this->raiseError($errno, null, null, null, $native);
786
    }
787
 
788
    // }}}
789
    // {{{ errorNative()
790
 
791
    /**
792
     * Gets the DBMS' native error message produced by the last query
793
     *
794
     * {@internal Error messages are used instead of error codes
795
     * in order to support older versions of PostgreSQL.}}
796
     *
797
     * @return string  the DBMS' error message
798
     */
799
    function errorNative()
800
    {
801
        return @pg_errormessage($this->connection);
802
    }
803
 
804
    // }}}
805
    // {{{ errorCode()
806
 
807
    /**
808
     * Determines PEAR::DB error code from the database's text error message.
809
     *
810
     * @param  string  $errormsg  error message returned from the database
811
     * @return integer  an error number from a DB error constant
812
     */
813
    function errorCode($errormsg)
814
    {
815
        static $error_regexps;
816
        if (!isset($error_regexps)) {
817
            $error_regexps = array(
818
                '/(relation|sequence|table).*does not exist|class .* not found/i'
819
                    => DB_ERROR_NOSUCHTABLE,
820
                '/index .* does not exist/'
821
                    => DB_ERROR_NOT_FOUND,
822
                '/column .* does not exist/i'
823
                    => DB_ERROR_NOSUCHFIELD,
824
                '/relation .* already exists/i'
825
                    => DB_ERROR_ALREADY_EXISTS,
826
                '/(divide|division) by zero$/i'
827
                    => DB_ERROR_DIVZERO,
828
                '/pg_atoi: error in .*: can\'t parse /i'
829
                    => DB_ERROR_INVALID_NUMBER,
830
                '/invalid input syntax for( type)? (integer|numeric)/i'
831
                    => DB_ERROR_INVALID_NUMBER,
832
                '/value .* is out of range for type \w*int/i'
833
                    => DB_ERROR_INVALID_NUMBER,
834
                '/integer out of range/i'
835
                    => DB_ERROR_INVALID_NUMBER,
836
                '/value too long for type character/i'
837
                    => DB_ERROR_INVALID,
838
                '/attribute .* not found|relation .* does not have attribute/i'
839
                    => DB_ERROR_NOSUCHFIELD,
840
                '/column .* specified in USING clause does not exist in (left|right) table/i'
841
                    => DB_ERROR_NOSUCHFIELD,
842
                '/parser: parse error at or near/i'
843
                    => DB_ERROR_SYNTAX,
844
                '/syntax error at/'
845
                    => DB_ERROR_SYNTAX,
846
                '/column reference .* is ambiguous/i'
847
                    => DB_ERROR_SYNTAX,
848
                '/permission denied/'
849
                    => DB_ERROR_ACCESS_VIOLATION,
850
                '/violates not-null constraint/'
851
                    => DB_ERROR_CONSTRAINT_NOT_NULL,
852
                '/violates [\w ]+ constraint/'
853
                    => DB_ERROR_CONSTRAINT,
854
                '/referential integrity violation/'
855
                    => DB_ERROR_CONSTRAINT,
856
                '/more expressions than target columns/i'
857
                    => DB_ERROR_VALUE_COUNT_ON_ROW,
858
            );
859
        }
860
        foreach ($error_regexps as $regexp => $code) {
861
            if (preg_match($regexp, $errormsg)) {
862
                return $code;
863
            }
864
        }
865
        // Fall back to DB_ERROR if there was no mapping.
866
        return DB_ERROR;
867
    }
868
 
869
    // }}}
870
    // {{{ tableInfo()
871
 
872
    /**
873
     * Returns information about a table or a result set
874
     *
875
     * NOTE: only supports 'table' and 'flags' if <var>$result</var>
876
     * is a table name.
877
     *
878
     * @param object|string  $result  DB_result object from a query or a
879
     *                                 string containing the name of a table.
880
     *                                 While this also accepts a query result
881
     *                                 resource identifier, this behavior is
882
     *                                 deprecated.
883
     * @param int            $mode    a valid tableInfo mode
884
     *
885
     * @return array  an associative array with the information requested.
886
     *                 A DB_Error object on failure.
887
     *
888
     * @see DB_common::tableInfo()
889
     */
890
    function tableInfo($result, $mode = null)
891
    {
892
        if (is_string($result)) {
893
            /*
894
             * Probably received a table name.
895
             * Create a result resource identifier.
896
             */
897
            $id = @pg_exec($this->connection, "SELECT * FROM $result LIMIT 0");
898
            $got_string = true;
899
        } elseif (isset($result->result)) {
900
            /*
901
             * Probably received a result object.
902
             * Extract the result resource identifier.
903
             */
904
            $id = $result->result;
905
            $got_string = false;
906
        } else {
907
            /*
908
             * Probably received a result resource identifier.
909
             * Copy it.
910
             * Deprecated.  Here for compatibility only.
911
             */
912
            $id = $result;
913
            $got_string = false;
914
        }
915
 
916
        if (!is_resource($id)) {
917
            return $this->pgsqlRaiseError(DB_ERROR_NEED_MORE_DATA);
918
        }
919
 
920
        if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
921
            $case_func = 'strtolower';
922
        } else {
923
            $case_func = 'strval';
924
        }
925
 
926
        $count = @pg_numfields($id);
927
        $res   = array();
928
 
929
        if ($mode) {
930
            $res['num_fields'] = $count;
931
        }
932
 
933
        for ($i = 0; $i < $count; $i++) {
934
            $res[$i] = array(
935
                'table' => $got_string ? $case_func($result) : '',
936
                'name'  => $case_func(@pg_fieldname($id, $i)),
937
                'type'  => @pg_fieldtype($id, $i),
938
                'len'   => @pg_fieldsize($id, $i),
939
                'flags' => $got_string
940
                           ? $this->_pgFieldFlags($id, $i, $result)
941
                           : '',
942
            );
943
            if ($mode & DB_TABLEINFO_ORDER) {
944
                $res['order'][$res[$i]['name']] = $i;
945
            }
946
            if ($mode & DB_TABLEINFO_ORDERTABLE) {
947
                $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
948
            }
949
        }
950
 
951
        // free the result only if we were called on a table
952
        if ($got_string) {
953
            @pg_freeresult($id);
954
        }
955
        return $res;
956
    }
957
 
958
    // }}}
959
    // {{{ _pgFieldFlags()
960
 
961
    /**
962
     * Get a column's flags
963
     *
964
     * Supports "not_null", "default_value", "primary_key", "unique_key"
965
     * and "multiple_key".  The default value is passed through
966
     * rawurlencode() in case there are spaces in it.
967
     *
968
     * @param int $resource   the PostgreSQL result identifier
969
     * @param int $num_field  the field number
970
     *
971
     * @return string  the flags
972
     *
973
     * @access private
974
     */
975
    function _pgFieldFlags($resource, $num_field, $table_name)
976
    {
977
        $field_name = @pg_fieldname($resource, $num_field);
978
 
979
        $result = @pg_exec($this->connection, "SELECT f.attnotnull, f.atthasdef
980
                                FROM pg_attribute f, pg_class tab, pg_type typ
981
                                WHERE tab.relname = typ.typname
982
                                AND typ.typrelid = f.attrelid
983
                                AND f.attname = '$field_name'
984
                                AND tab.relname = '$table_name'");
985
        if (@pg_numrows($result) > 0) {
986
            $row = @pg_fetch_row($result, 0);
987
            $flags  = ($row[0] == 't') ? 'not_null ' : '';
988
 
989
            if ($row[1] == 't') {
990
                $result = @pg_exec($this->connection, "SELECT a.adsrc
991
                                    FROM pg_attribute f, pg_class tab, pg_type typ, pg_attrdef a
992
                                    WHERE tab.relname = typ.typname AND typ.typrelid = f.attrelid
993
                                    AND f.attrelid = a.adrelid AND f.attname = '$field_name'
994
                                    AND tab.relname = '$table_name' AND f.attnum = a.adnum");
995
                $row = @pg_fetch_row($result, 0);
996
                $num = preg_replace("/'(.*)'::\w+/", "\\1", $row[0]);
997
                $flags .= 'default_' . rawurlencode($num) . ' ';
998
            }
999
        } else {
1000
            $flags = '';
1001
        }
1002
        $result = @pg_exec($this->connection, "SELECT i.indisunique, i.indisprimary, i.indkey
1003
                                FROM pg_attribute f, pg_class tab, pg_type typ, pg_index i
1004
                                WHERE tab.relname = typ.typname
1005
                                AND typ.typrelid = f.attrelid
1006
                                AND f.attrelid = i.indrelid
1007
                                AND f.attname = '$field_name'
1008
                                AND tab.relname = '$table_name'");
1009
        $count = @pg_numrows($result);
1010
 
1011
        for ($i = 0; $i < $count ; $i++) {
1012
            $row = @pg_fetch_row($result, $i);
1013
            $keys = explode(' ', $row[2]);
1014
 
1015
            if (in_array($num_field + 1, $keys)) {
1016
                $flags .= ($row[0] == 't' && $row[1] == 'f') ? 'unique_key ' : '';
1017
                $flags .= ($row[1] == 't') ? 'primary_key ' : '';
1018
                if (count($keys) > 1)
1019
                    $flags .= 'multiple_key ';
1020
            }
1021
        }
1022
 
1023
        return trim($flags);
1024
    }
1025
 
1026
    // }}}
1027
    // {{{ getSpecialQuery()
1028
 
1029
    /**
1030
     * Obtains the query string needed for listing a given type of objects
1031
     *
1032
     * @param string $type  the kind of objects you want to retrieve
1033
     *
1034
     * @return string  the SQL query string or null if the driver doesn't
1035
     *                  support the object type requested
1036
     *
1037
     * @access protected
1038
     * @see DB_common::getListOf()
1039
     */
1040
    function getSpecialQuery($type)
1041
    {
1042
        switch ($type) {
1043
            case 'tables':
1044
                return 'SELECT c.relname AS "Name"'
1045
                        . ' FROM pg_class c, pg_user u'
1046
                        . ' WHERE c.relowner = u.usesysid'
1047
                        . " AND c.relkind = 'r'"
1048
                        . ' AND NOT EXISTS'
1049
                        . ' (SELECT 1 FROM pg_views'
1050
                        . '  WHERE viewname = c.relname)'
1051
                        . " AND c.relname !~ '^(pg_|sql_)'"
1052
                        . ' UNION'
1053
                        . ' SELECT c.relname AS "Name"'
1054
                        . ' FROM pg_class c'
1055
                        . " WHERE c.relkind = 'r'"
1056
                        . ' AND NOT EXISTS'
1057
                        . ' (SELECT 1 FROM pg_views'
1058
                        . '  WHERE viewname = c.relname)'
1059
                        . ' AND NOT EXISTS'
1060
                        . ' (SELECT 1 FROM pg_user'
1061
                        . '  WHERE usesysid = c.relowner)'
1062
                        . " AND c.relname !~ '^pg_'";
1063
            case 'schema.tables':
1064
                return "SELECT schemaname || '.' || tablename"
1065
                        . ' AS "Name"'
1066
                        . ' FROM pg_catalog.pg_tables'
1067
                        . ' WHERE schemaname NOT IN'
1068
                        . " ('pg_catalog', 'information_schema', 'pg_toast')";
1069
            case 'views':
1070
                // Table cols: viewname | viewowner | definition
1071
                return 'SELECT viewname from pg_views WHERE schemaname'
1072
                        . " NOT IN ('information_schema', 'pg_catalog')";
1073
            case 'users':
1074
                // cols: usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd  |valuntil
1075
                return 'SELECT usename FROM pg_user';
1076
            case 'databases':
1077
                return 'SELECT datname FROM pg_database';
1078
            case 'functions':
1079
            case 'procedures':
1080
                return 'SELECT proname FROM pg_proc WHERE proowner <> 1';
1081
            default:
1082
                return null;
1083
        }
1084
    }
1085
 
1086
    // }}}
1087
 
1088
}
1089
 
1090
/*
1091
 * Local variables:
1092
 * tab-width: 4
1093
 * c-basic-offset: 4
1094
 * End:
1095
 */
1096
 
1097
?>