library/adodb/drivers/adodb-postgres7.inc.php

   1 <?php
   2 /*
   3  V5.14 8 Sept 2011  (c) 2000-2011 John Lim (jlim#natsoft.com). All rights reserved.
   4   Released under both BSD license and Lesser GPL library license.
   5   Whenever there is any discrepancy between the two licenses,
   6   the BSD license will take precedence.
   7   Set tabs to 4.
   8
   9   Postgres7 support.
  10   28 Feb 2001: Currently indicate that we support LIMIT
  11   01 Dec 2001: dannym added support for default values
  12 */
  13
  14 // security - hide paths
  15 if (!defined('ADODB_DIR')) die();
  16
  17 include_once(ADODB_DIR."/drivers/adodb-postgres64.inc.php");
  18
  19 class ADODB_postgres7 extends ADODB_postgres64 {
  20         var $databaseType = 'postgres7';
  21         var $hasLimit = true;   // set to true for pgsql 6.5+ only. support pgsql/mysql SELECT * FROM TABLE LIMIT 10
  22         var $ansiOuter = true;
  23         var $charSet = true; //set to true for Postgres 7 and above - PG client supports encodings
  24
  25
  26         function ADODB_postgres7()
  27         {
  28                 $this->ADODB_postgres64();
  29                 if (ADODB_ASSOC_CASE !== 2) {
  30                         $this->rsPrefix .= 'assoc_';
  31                 }
  32                 $this->_bindInputArray = PHP_VERSION >= 5.1;
  33         }
  34
  35
  36         // the following should be compat with postgresql 7.2,
  37         // which makes obsolete the LIMIT limit,offset syntax
  38          function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
  39          {
  40                  $offsetStr = ($offset >= 0) ? " OFFSET ".((integer)$offset) : '';
  41                  $limitStr  = ($nrows >= 0)  ? " LIMIT ".((integer)$nrows) : '';
  42                  if ($secs2cache)
  43                         $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
  44                  else
  45                         $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
  46
  47                 return $rs;
  48          }
  49         /*
  50         function Prepare($sql)
  51         {
  52                 $info = $this->ServerInfo();
  53                 if ($info['version']>=7.3) {
  54                         return array($sql,false);
  55                 }
  56                 return $sql;
  57         }
  58         */
  59
  60         /*
  61                 I discovered that the MetaForeignKeys method no longer worked for Postgres 8.3.
  62                 I went ahead and modified it to work for both 8.2 and 8.3.
  63                 Please feel free to include this change in your next release of adodb.
  64                  William Kolodny [William.Kolodny#gt-t.net]
  65         */
  66         function MetaForeignKeys($table, $owner=false, $upper=false)
  67         {
  68           $sql="
  69           SELECT fum.ftblname AS lookup_table, split_part(fum.rf, ')'::text, 1) AS lookup_field,
  70              fum.ltable AS dep_table, split_part(fum.lf, ')'::text, 1) AS dep_field
  71           FROM (
  72           SELECT fee.ltable, fee.ftblname, fee.consrc, split_part(fee.consrc,'('::text, 2) AS lf,
  73             split_part(fee.consrc, '('::text, 3) AS rf
  74           FROM (
  75               SELECT foo.relname AS ltable, foo.ftblname,
  76                   pg_get_constraintdef(foo.oid) AS consrc
  77               FROM (
  78                   SELECT c.oid, c.conname AS name, t.relname, ft.relname AS ftblname
  79                   FROM pg_constraint c
  80                   JOIN pg_class t ON (t.oid = c.conrelid)
  81                   JOIN pg_class ft ON (ft.oid = c.confrelid)
  82                   JOIN pg_namespace nft ON (nft.oid = ft.relnamespace)
  83                   LEFT JOIN pg_description ds ON (ds.objoid = c.oid)
  84                   JOIN pg_namespace n ON (n.oid = t.relnamespace)
  85                   WHERE c.contype = 'f'::\"char\"
  86                   ORDER BY t.relname, n.nspname, c.conname, c.oid
  87                   ) foo
  88               ) fee) fum
  89           WHERE fum.ltable='".strtolower($table)."'
  90           ORDER BY fum.ftblname, fum.ltable, split_part(fum.lf, ')'::text, 1)
  91           ";
  92           $rs = $this->Execute($sql);
  93
  94           if (!$rs || $rs->EOF) return false;
  95
  96           $a = array();
  97           while (!$rs->EOF) {
  98             if ($upper) {
  99               $a[strtoupper($rs->Fields('lookup_table'))][] = strtoupper(str_replace('"','',$rs->Fields('dep_field').'='.$rs->Fields('lookup_field')));
 100             } else {
 101               $a[$rs->Fields('lookup_table')][] = str_replace('"','',$rs->Fields('dep_field').'='.$rs->Fields('lookup_field'));
 102             }
 103                 $rs->MoveNext();
 104           }
 105
 106           return $a;
 107
 108         }
 109
 110         // from  Edward Jaramilla, improved version - works on pg 7.4
 111         function _old_MetaForeignKeys($table, $owner=false, $upper=false)
 112         {
 113                 $sql = 'SELECT t.tgargs as args
 114                 FROM
 115                 pg_trigger t,pg_class c,pg_proc p
 116                 WHERE
 117                 t.tgenabled AND
 118                 t.tgrelid = c.oid AND
 119                 t.tgfoid = p.oid AND
 120                 p.proname = \'RI_FKey_check_ins\' AND
 121                 c.relname = \''.strtolower($table).'\'
 122                 ORDER BY
 123                         t.tgrelid';
 124
 125                 $rs = $this->Execute($sql);
 126
 127                 if (!$rs || $rs->EOF) return false;
 128
 129                 $arr = $rs->GetArray();
 130                 $a = array();
 131                 foreach($arr as $v) {
 132                         $data = explode(chr(0), $v['args']);
 133                         $size = count($data)-1; //-1 because the last node is empty
 134                         for($i = 4; $i < $size; $i++) {
 135                                 if ($upper)
 136                                         $a[strtoupper($data[2])][] = strtoupper($data[$i].'='.$data[++$i]);
 137                                 else
 138                                         $a[$data[2]][] = $data[$i].'='.$data[++$i];
 139                         }
 140                 }
 141                 return $a;
 142         }
 143
 144         function _query($sql,$inputarr=false)
 145         {
 146                 if (! $this->_bindInputArray) {
 147                         // We don't have native support for parameterized queries, so let's emulate it at the parent
 148                         return ADODB_postgres64::_query($sql, $inputarr);
 149                 }
 150
 151                 $this->_pnum = 0;
 152                 $this->_errorMsg = false;
 153                 // -- added Cristiano da Cunha Duarte
 154                 if ($inputarr) {
 155                         $sqlarr = explode('?',trim($sql));
 156                         $sql = '';
 157                         $i = 1;
 158                         $last = sizeof($sqlarr)-1;
 159                         foreach($sqlarr as $v) {
 160                                 if ($last < $i) $sql .= $v;
 161                                 else $sql .= $v.' $'.$i;
 162                                 $i++;
 163                         }
 164
 165                         $rez = pg_query_params($this->_connectionID,$sql, $inputarr);
 166                 } else {
 167                         $rez = pg_query($this->_connectionID,$sql);
 168                 }
 169                 // check if no data returned, then no need to create real recordset
 170                 if ($rez && pg_numfields($rez) <= 0) {
 171                         if (is_resource($this->_resultid) && get_resource_type($this->_resultid) === 'pgsql result') {
 172                                 pg_freeresult($this->_resultid);
 173                         }
 174                         $this->_resultid = $rez;
 175                         return true;
 176                 }
 177                 return $rez;
 178         }
 179
 180          // this is a set of functions for managing client encoding - very important if the encodings
 181         // of your database and your output target (i.e. HTML) don't match
 182         //for instance, you may have UNICODE database and server it on-site as WIN1251 etc.
 183         // GetCharSet - get the name of the character set the client is using now
 184         // the functions should work with Postgres 7.0 and above, the set of charsets supported
 185         // depends on compile flags of postgres distribution - if no charsets were compiled into the server
 186         // it will return 'SQL_ANSI' always
 187         function GetCharSet()
 188         {
 189                 //we will use ADO's builtin property charSet
 190                 $this->charSet = @pg_client_encoding($this->_connectionID);
 191                 if (!$this->charSet) {
 192                         return false;
 193                 } else {
 194                         return $this->charSet;
 195                 }
 196         }
 197
 198         // SetCharSet - switch the client encoding
 199         function SetCharSet($charset_name)
 200         {
 201                 $this->GetCharSet();
 202                 if ($this->charSet !== $charset_name) {
 203                         $if = pg_set_client_encoding($this->_connectionID, $charset_name);
 204                         if ($if == "0" & $this->GetCharSet() == $charset_name) {
 205                                 return true;
 206                         } else return false;
 207                 } else return true;
 208         }
 209
 210 }
 211
 212 /*--------------------------------------------------------------------------------------
 213          Class Name: Recordset
 214 --------------------------------------------------------------------------------------*/
 215
 216 class ADORecordSet_postgres7 extends ADORecordSet_postgres64{
 217
 218         var $databaseType = "postgres7";
 219
 220
 221         function ADORecordSet_postgres7($queryID,$mode=false)
 222         {
 223                 $this->ADORecordSet_postgres64($queryID,$mode);
 224         }
 225
 226                 // 10% speedup to move MoveNext to child class
 227         function MoveNext()
 228         {
 229                 if (!$this->EOF) {
 230                         $this->_currentRow++;
 231                         if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
 232                                 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
 233
 234                                 if (is_array($this->fields)) {
 235                                         if ($this->fields && isset($this->_blobArr)) $this->_fixblobs();
 236                                         return true;
 237                                 }
 238                         }
 239                         $this->fields = false;
 240                         $this->EOF = true;
 241                 }
 242                 return false;
 243         }
 244
 245 }
 246
 247 class ADORecordSet_assoc_postgres7 extends ADORecordSet_postgres64{
 248
 249         var $databaseType = "postgres7";
 250
 251
 252         function ADORecordSet_assoc_postgres7($queryID,$mode=false)
 253         {
 254                 $this->ADORecordSet_postgres64($queryID,$mode);
 255         }
 256
 257         function _fetch()
 258         {
 259                 if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0)
 260                 return false;
 261
 262                 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
 263
 264                 if ($this->fields) {
 265                         if (isset($this->_blobArr)) $this->_fixblobs();
 266                         $this->_updatefields();
 267                 }
 268
 269                 return (is_array($this->fields));
 270         }
 271
 272                 // Create associative array
 273         function _updatefields()
 274         {
 275                 if (ADODB_ASSOC_CASE == 2) return; // native
 276
 277                 $arr = array();
 278                 $lowercase = (ADODB_ASSOC_CASE == 0);
 279
 280                 foreach($this->fields as $k => $v) {
 281                         if (is_integer($k)) $arr[$k] = $v;
 282                         else {
 283                                 if ($lowercase)
 284                                         $arr[strtolower($k)] = $v;
 285                                 else
 286                                         $arr[strtoupper($k)] = $v;
 287                         }
 288                 }
 289                 $this->fields = $arr;
 290         }
 291
 292         function MoveNext()
 293         {
 294                 if (!$this->EOF) {
 295                         $this->_currentRow++;
 296                         if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
 297                                 $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
 298
 299                                 if (is_array($this->fields)) {
 300                                         if ($this->fields) {
 301                                                 if (isset($this->_blobArr)) $this->_fixblobs();
 302
 303                                                 $this->_updatefields();
 304                                         }
 305                                         return true;
 306                                 }
 307                         }
 308
 309
 310                         $this->fields = false;
 311                         $this->EOF = true;
 312                 }
 313                 return false;
 314         }
 315 }
 316 ?>