include/DbSimple/Postgresql.php

   1 <?php
   2 /**
   3  * DbSimple_Postgreql: PostgreSQL database.
   4  * (C) Dk Lab, http://en.dklab.ru
   5  *
   6  * This library is free software; you can redistribute it and/or
   7  * modify it under the terms of the GNU Lesser General Public
   8  * License as published by the Free Software Foundation; either
   9  * version 2.1 of the License, or (at your option) any later version.
  10  * See http://www.gnu.org/copyleft/lesser.html
  11  *
  12  * Placeholders are emulated because of logging purposes.
  13  *
  14  * @author Dmitry Koterov, http://forum.dklab.ru/users/DmitryKoterov/
  15  * @author Konstantin Zhinko, http://forum.dklab.ru/users/KonstantinGinkoTit/
  16  *
  17  * @version 2.x $Id: Postgresql.php 167 2007-01-22 10:12:09Z tit $
  18  */
  19 require_once dirname(__FILE__) . '/Generic.php';
  20
  21
  22 /**
  23  * Database class for PostgreSQL.
  24  */
  25 class DbSimple_Postgresql extends DbSimple_Generic_Database
  26 {
  27
  28     var $DbSimple_Postgresql_USE_NATIVE_PHOLDERS = null;
  29     var $prepareCache = array();
  30     var $link;
  31
  32     /**
  33      * constructor(string $dsn)
  34      * Connect to PostgresSQL.
  35      */
  36     function DbSimple_Postgresql($dsn)
  37     {
  38         $p = DbSimple_Generic::parseDSN($dsn);
  39         if (!is_callable('pg_connect')) {
  40             return $this->_setLastError("-1", "PostgreSQL extension is not loaded", "pg_connect");
  41         }
  42
  43         // Prepare+execute works only in PHP 5.1+.
  44         $this->DbSimple_Postgresql_USE_NATIVE_PHOLDERS = function_exists('pg_prepare');
  45
  46         $ok = $this->link = @pg_connect(
  47             $t = (!empty($p['host']) ? 'host='.$p['host'].' ' : '').
  48             (!empty($p['port']) ? 'port='.$p['port'].' ' : '').
  49             'dbname='.preg_replace('{^/}s', '', $p['path']).' '.
  50             (!empty($p['user']) ? 'user='.$p['user'].' ' : '').
  51             (!empty($p['pass']) ? 'password='.$p['pass'].' ' : '')
  52         );
  53         $this->_resetLastError();
  54         if (!$ok) return $this->_setDbError('pg_connect()');
  55     }
  56
  57
  58     function _performEscape($s, $isIdent=false)
  59     {
  60         if (!$isIdent)
  61             return "'" . str_replace("'", "''", $s) . "'";
  62         else
  63             return '"' . str_replace('"', '_', $s) . '"';
  64     }
  65
  66
  67     function _performTransaction($parameters=null)
  68     {
  69         return $this->query('BEGIN');
  70     }
  71
  72
  73     function& _performNewBlob($blobid=null)
  74     {
  75         $obj =& new DbSimple_Postgresql_Blob($this, $blobid);
  76         return $obj;
  77     }
  78
  79
  80     function _performGetBlobFieldNames($result)
  81     {
  82         $blobFields = array();
  83         for ($i=pg_num_fields($result)-1; $i>=0; $i--) {
  84             $type = pg_field_type($result, $i);
  85             if (strpos($type, "BLOB") !== false) $blobFields[] = pg_field_name($result, $i);
  86         }
  87         return $blobFields;
  88     }
  89
  90     // TODO: Real PostgreSQL escape
  91     function _performGetPlaceholderIgnoreRe()
  92     {
  93         return '
  94             "   (?> [^"\\\\]+|\\\\"|\\\\)*    "   |
  95             \'  (?> [^\'\\\\]+|\\\\\'|\\\\)* \'   |
  96             /\* .*?                          \*/      # comments
  97         ';
  98     }
  99
 100     function _performGetNativePlaceholderMarker($n)
 101     {
 102         // PostgreSQL uses specific placeholders such as $1, $2, etc.
 103         return '$' . ($n + 1);
 104     }
 105
 106     function _performCommit()
 107     {
 108         return $this->query('COMMIT');
 109     }
 110
 111
 112     function _performRollback()
 113     {
 114         return $this->query('ROLLBACK');
 115     }
 116
 117
 118     function _performTransformQuery(&$queryMain, $how)
 119     {
 120
 121         // If we also need to calculate total number of found rows...
 122         switch ($how) {
 123             // Prepare total calculation (if possible)
 124             case 'CALC_TOTAL':
 125                 // Not possible
 126                 return true;
 127
 128             // Perform total calculation.
 129             case 'GET_TOTAL':
 130                 // TODO: GROUP BY ... -> COUNT(DISTINCT ...)
 131                 $re = '/^
 132                     (?> -- [^\r\n]* | \s+)*
 133                     (\s* SELECT \s+)                                             #1
 134                     (.*?)                                                        #2
 135                     (\s+ FROM \s+ .*?)                                           #3
 136                         ((?:\s+ ORDER \s+ BY \s+ .*?)?)                          #4
 137                         ((?:\s+ LIMIT \s+ \S+ \s* (?: OFFSET \s* \S+ \s*)? )?)  #5
 138                 $/six';
 139                 $m = null;
 140                 if (preg_match($re, $queryMain[0], $m)) {
 141                     $queryMain[0] = $m[1] . $this->_fieldList2Count($m[2]) . " AS C" . $m[3];
 142                     $skipTail = substr_count($m[4] . $m[5], '?');
 143                     if ($skipTail) array_splice($queryMain, -$skipTail);
 144                 }
 145                 return true;
 146         }
 147
 148         return false;
 149     }
 150
 151
 152     function _performQuery($queryMain)
 153     {
 154         $this->_lastQuery = $queryMain;
 155         $isInsert = preg_match('/^\s* INSERT \s+/six', $queryMain[0]);
 156
 157         //
 158         // Note that in case of INSERT query we CANNOT work with prepare...execute
 159         // cache, because RULEs do not work after pg_execute(). This is a very strange
 160         // bug... To reproduce:
 161         //   $DB->query("CREATE TABLE test(id SERIAL, str VARCHAR(10)) WITH OIDS");
 162         //   $DB->query("CREATE RULE test_r AS ON INSERT TO test DO (SELECT 111 AS id)");
 163         //   print_r($DB->query("INSERT INTO test(str) VALUES ('test')"));
 164         // In case INSERT + pg_execute() it returns new row OID (numeric) instead
 165         // of result of RULE query. Strange, very strange...
 166         //
 167
 168         if ($this->DbSimple_Postgresql_USE_NATIVE_PHOLDERS && !$isInsert) {
 169             // Use native placeholders only if PG supports them.
 170             $this->_expandPlaceholders($queryMain, true);
 171             $hash = md5($queryMain[0]);
 172             if (!isset($this->prepareCache[$hash])) {
 173                 $this->prepareCache[$hash] = true;
 174                 $prepared = @pg_prepare($this->link, $hash, $queryMain[0]);
 175                 if ($prepared === false) return $this->_setDbError($queryMain[0]);
 176             } else {
 177                 // Prepare cache hit!
 178             }
 179             $result = pg_execute($this->link, $hash, array_slice($queryMain, 1));
 180         } else {
 181             // No support for native placeholders or INSERT query.
 182             $this->_expandPlaceholders($queryMain, false);
 183             $result = @pg_query($this->link, $queryMain[0]);
 184         }
 185
 186         if ($result === false) return $this->_setDbError($queryMain);
 187         if (!pg_num_fields($result)) {
 188             if ($isInsert) {
 189                 // INSERT queries return generated OID (if table is WITH OIDs).
 190                 //
 191                 // Please note that unfortunately we cannot use lastval() PostgreSQL
 192                 // stored function because it generates fatal error if INSERT query
 193                 // does not contain sequence-based field at all. This error terminates
 194                 // the current transaction, and we cannot continue to work nor know
 195                 // if table contains sequence-updateable field or not.
 196                 //
 197                 // To use auto-increment functionality you must invoke
 198                 //   $insertedId = $DB->query("SELECT lastval()")
 199                 // manually where it is really needed.
 200                 //
 201                 return @pg_last_oid($result);
 202             }
 203             // Non-SELECT queries return number of affected rows, SELECT - resource.
 204             return @pg_affected_rows($result);
 205         }
 206         return $result;
 207     }
 208
 209
 210     function _performFetch($result)
 211     {
 212         $row = @pg_fetch_assoc($result);
 213         if (pg_last_error($this->link)) return $this->_setDbError($this->_lastQuery);
 214         if ($row === false) return null;
 215         return $row;
 216     }
 217
 218
 219     function _setDbError($query)
 220     {
 221         return $this->_setLastError(null, pg_last_error($this->link), $query);
 222     }
 223
 224     function _getVersion()
 225     {
 226     }
 227 }
 228
 229
 230 class DbSimple_Postgresql_Blob extends DbSimple_Generic_Blob
 231 {
 232     var $blob; // resourse link
 233     var $id;
 234     var $database;
 235
 236     function DbSimple_Postgresql_Blob(&$database, $id=null)
 237     {
 238         $this->database =& $database;
 239         $this->database->transaction();
 240         $this->id = $id;
 241         $this->blob = null;
 242     }
 243
 244     function read($len)
 245     {
 246         if ($this->id === false) return ''; // wr-only blob
 247         if (!($e=$this->_firstUse())) return $e;
 248         $data = @pg_lo_read($this->blob, $len);
 249         if ($data === false) return $this->_setDbError('read');
 250         return $data;
 251     }
 252
 253     function write($data)
 254     {
 255         if (!($e=$this->_firstUse())) return $e;
 256         $ok = @pg_lo_write($this->blob, $data);
 257         if ($ok === false) return $this->_setDbError('add data to');
 258         return true;
 259     }
 260
 261     function close()
 262     {
 263         if (!($e=$this->_firstUse())) return $e;
 264         if ($this->blob) {
 265             $id = @pg_lo_close($this->blob);
 266             if ($id === false) return $this->_setDbError('close');
 267             $this->blob = null;
 268         } else {
 269             $id = null;
 270         }
 271         $this->database->commit();
 272         return $this->id? $this->id : $id;
 273     }
 274
 275     function length()
 276     {
 277         if (!($e=$this->_firstUse())) return $e;
 278
 279         @pg_lo_seek($this->blob, 0, PGSQL_SEEK_END);
 280         $len = @pg_lo_tell($this->blob);
 281         @pg_lo_seek($this->blob, 0, PGSQL_SEEK_SET);
 282
 283         if (!$len) return $this->_setDbError('get length of');
 284         return $len;
 285     }
 286
 287     function _setDbError($query)
 288     {
 289         $hId = $this->id === null? "null" : ($this->id === false? "false" : $this->id);
 290         $query = "-- $query BLOB $hId";
 291         $this->database->_setDbError($query);
 292     }
 293
 294     // Called on each blob use (reading or writing).
 295     function _firstUse()
 296     {
 297         // BLOB opened - do nothing.
 298         if (is_resource($this->blob)) return true;
 299
 300         // Open or create blob.
 301         if ($this->id !== null) {
 302             $this->blob = @pg_lo_open($this->database->link, $this->id, 'rw');
 303             if ($this->blob === false) return $this->_setDbError('open');
 304         } else {
 305             $this->id = @pg_lo_create($this->database->link);
 306             $this->blob = @pg_lo_open($this->database->link, $this->id, 'w');
 307             if ($this->blob === false) return $this->_setDbError('create');
 308         }
 309         return true;
 310     }
 311 }
 312 ?>