[Summary view] [Print] [Text view]

   1  <?php
   2  /*
   3   V5.19  23-Apr-2014  (c) 2000-2014 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         // Richard 3/18/2012 - Modified SQL to return SERIAL type correctly AS old driver no longer return SERIAL as data type.
  25      var $metaColumnsSQL =
  26                           "SELECT a.attname,
  27                                      CASE
  28                                                 WHEN x.sequence_name != '' THEN 'SERIAL'
  29                                                 ELSE t.typname
  30                                      END AS typname,
  31                                      a.attlen,a.atttypmod,a.attnotnull,a.atthasdef,a.attnum
  32                           FROM pg_class c, pg_attribute a
  33                           JOIN pg_type t ON a.atttypid = t.oid
  34                           LEFT JOIN
  35                                      (SELECT c.relname as sequence_name,
  36                                                    c1.relname as related_table,
  37                                                    a.attname as related_column
  38                                      FROM pg_class c
  39                                         JOIN pg_depend d ON d.objid = c.oid
  40                                         LEFT JOIN pg_class c1 ON d.refobjid = c1.oid
  41                                         LEFT JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
  42                                      WHERE c.relkind = 'S' AND c1.relname = '%s') x
  43                                      ON x.related_column= a.attname
  44                           WHERE c.relkind in ('r','v') AND
  45                                      (c.relname='%s' or c.relname = lower('%s')) AND
  46                                      a.attname not like '....%%' AND
  47                                      a.attnum > 0 AND
  48                                      a.attrelid = c.oid
  49                           ORDER BY a.attnum";
  50  
  51     // used when schema defined
  52      var $metaColumnsSQL1 = "
  53                           SELECT a.attname,
  54                                      CASE
  55                                                 WHEN x.sequence_name != '' THEN 'SERIAL'
  56                                                 ELSE t.typname
  57                                      END AS typname,
  58                                      a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum
  59                           FROM pg_class c, pg_namespace n, pg_attribute a
  60                           JOIN pg_type t ON a.atttypid = t.oid
  61                           LEFT JOIN
  62                                      (SELECT c.relname as sequence_name,
  63                                                    c1.relname as related_table,
  64                                                    a.attname as related_column
  65                                      FROM pg_class c
  66                                         JOIN pg_depend d ON d.objid = c.oid
  67                                         LEFT JOIN pg_class c1 ON d.refobjid = c1.oid
  68                                         LEFT JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
  69                                      WHERE c.relkind = 'S' AND c1.relname = '%s') x
  70                                      ON x.related_column= a.attname
  71                           WHERE c.relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s'))
  72                                      AND c.relnamespace=n.oid and n.nspname='%s'
  73                                      AND a.attname not like '....%%' AND a.attnum > 0
  74                                      AND a.atttypid = t.oid AND a.attrelid = c.oid
  75                           ORDER BY a.attnum";
  76  
  77  
  78  	function __construct()
  79      {
  80          parent::__construct();
  81          if (ADODB_ASSOC_CASE !== 2) {
  82              $this->rsPrefix .= 'assoc_';
  83          }
  84          $this->_bindInputArray = PHP_VERSION >= 5.1;
  85      }
  86  
  87  
  88      // the following should be compat with postgresql 7.2,
  89      // which makes obsolete the LIMIT limit,offset syntax
  90  	 function SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0)
  91       {
  92           $offsetStr = ($offset >= 0) ? " OFFSET ".((integer)$offset) : '';
  93           $limitStr  = ($nrows >= 0)  ? " LIMIT ".((integer)$nrows) : '';
  94           if ($secs2cache)
  95                $rs = $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
  96           else
  97                $rs = $this->Execute($sql."$limitStr$offsetStr",$inputarr);
  98  
  99          return $rs;
 100       }
 101       /*
 102       function Prepare($sql)
 103      {
 104          $info = $this->ServerInfo();
 105          if ($info['version']>=7.3) {
 106              return array($sql,false);
 107          }
 108          return $sql;
 109      }
 110       */
 111  
 112      /*
 113          I discovered that the MetaForeignKeys method no longer worked for Postgres 8.3.
 114          I went ahead and modified it to work for both 8.2 and 8.3.
 115          Please feel free to include this change in your next release of adodb.
 116           William Kolodny [William.Kolodny#gt-t.net]
 117      */
 118  	function MetaForeignKeys($table, $owner=false, $upper=false)
 119      {
 120        $sql="
 121        SELECT fum.ftblname AS lookup_table, split_part(fum.rf, ')'::text, 1) AS lookup_field,
 122           fum.ltable AS dep_table, split_part(fum.lf, ')'::text, 1) AS dep_field
 123        FROM (
 124        SELECT fee.ltable, fee.ftblname, fee.consrc, split_part(fee.consrc,'('::text, 2) AS lf,
 125          split_part(fee.consrc, '('::text, 3) AS rf
 126        FROM (
 127            SELECT foo.relname AS ltable, foo.ftblname,
 128                pg_get_constraintdef(foo.oid) AS consrc
 129            FROM (
 130                SELECT c.oid, c.conname AS name, t.relname, ft.relname AS ftblname
 131                FROM pg_constraint c
 132                JOIN pg_class t ON (t.oid = c.conrelid)
 133                JOIN pg_class ft ON (ft.oid = c.confrelid)
 134                JOIN pg_namespace nft ON (nft.oid = ft.relnamespace)
 135                LEFT JOIN pg_description ds ON (ds.objoid = c.oid)
 136                JOIN pg_namespace n ON (n.oid = t.relnamespace)
 137                WHERE c.contype = 'f'::\"char\"
 138                ORDER BY t.relname, n.nspname, c.conname, c.oid
 139                ) foo
 140            ) fee) fum
 141        WHERE fum.ltable='".strtolower($table)."'
 142        ORDER BY fum.ftblname, fum.ltable, split_part(fum.lf, ')'::text, 1)
 143        ";
 144        $rs = $this->Execute($sql);
 145  
 146        if (!$rs || $rs->EOF) return false;
 147  
 148        $a = array();
 149        while (!$rs->EOF) {
 150          if ($upper) {
 151            $a[strtoupper($rs->Fields('lookup_table'))][] = strtoupper(str_replace('"','',$rs->Fields('dep_field').'='.$rs->Fields('lookup_field')));
 152          } else {
 153            $a[$rs->Fields('lookup_table')][] = str_replace('"','',$rs->Fields('dep_field').'='.$rs->Fields('lookup_field'));
 154          }
 155          $rs->MoveNext();
 156        }
 157  
 158        return $a;
 159  
 160      }
 161  
 162      // from  Edward Jaramilla, improved version - works on pg 7.4
 163  	function _old_MetaForeignKeys($table, $owner=false, $upper=false)
 164      {
 165          $sql = 'SELECT t.tgargs as args
 166          FROM
 167          pg_trigger t,pg_class c,pg_proc p
 168          WHERE
 169          t.tgenabled AND
 170          t.tgrelid = c.oid AND
 171          t.tgfoid = p.oid AND
 172          p.proname = \'RI_FKey_check_ins\' AND
 173          c.relname = \''.strtolower($table).'\'
 174          ORDER BY
 175              t.tgrelid';
 176  
 177          $rs = $this->Execute($sql);
 178  
 179          if (!$rs || $rs->EOF) return false;
 180  
 181          $arr = $rs->GetArray();
 182          $a = array();
 183          foreach($arr as $v) {
 184              $data = explode(chr(0), $v['args']);
 185              $size = count($data)-1; //-1 because the last node is empty
 186              for($i = 4; $i < $size; $i++) {
 187                  if ($upper)
 188                      $a[strtoupper($data[2])][] = strtoupper($data[$i].'='.$data[++$i]);
 189                  else
 190                      $a[$data[2]][] = $data[$i].'='.$data[++$i];
 191              }
 192          }
 193          return $a;
 194      }
 195  
 196  	function _query($sql,$inputarr=false)
 197      {
 198          if (! $this->_bindInputArray) {
 199              // We don't have native support for parameterized queries, so let's emulate it at the parent
 200              return ADODB_postgres64::_query($sql, $inputarr);
 201          }
 202  
 203          $this->_pnum = 0;
 204          $this->_errorMsg = false;
 205          // -- added Cristiano da Cunha Duarte
 206          if ($inputarr) {
 207              $sqlarr = explode('?',trim($sql));
 208              $sql = '';
 209              $i = 1;
 210              $last = sizeof($sqlarr)-1;
 211              foreach($sqlarr as $v) {
 212                  if ($last < $i) $sql .= $v;
 213                  else $sql .= $v.' $'.$i;
 214                  $i++;
 215              }
 216  
 217              $rez = pg_query_params($this->_connectionID,$sql, $inputarr);
 218          } else {
 219              $rez = pg_query($this->_connectionID,$sql);
 220          }
 221          // check if no data returned, then no need to create real recordset
 222          if ($rez && pg_numfields($rez) <= 0) {
 223              if (is_resource($this->_resultid) && get_resource_type($this->_resultid) === 'pgsql result') {
 224                  pg_freeresult($this->_resultid);
 225              }
 226              $this->_resultid = $rez;
 227              return true;
 228          }
 229          return $rez;
 230      }
 231  
 232        // this is a set of functions for managing client encoding - very important if the encodings
 233      // of your database and your output target (i.e. HTML) don't match
 234      //for instance, you may have UNICODE database and server it on-site as WIN1251 etc.
 235      // GetCharSet - get the name of the character set the client is using now
 236      // the functions should work with Postgres 7.0 and above, the set of charsets supported
 237      // depends on compile flags of postgres distribution - if no charsets were compiled into the server
 238      // it will return 'SQL_ANSI' always
 239  	function GetCharSet()
 240      {
 241          //we will use ADO's builtin property charSet
 242          $this->charSet = @pg_client_encoding($this->_connectionID);
 243          if (!$this->charSet) {
 244              return false;
 245          } else {
 246              return $this->charSet;
 247          }
 248      }
 249  
 250      // SetCharSet - switch the client encoding
 251  	function SetCharSet($charset_name)
 252      {
 253          $this->GetCharSet();
 254          if ($this->charSet !== $charset_name) {
 255              $if = pg_set_client_encoding($this->_connectionID, $charset_name);
 256              if ($if == "0" & $this->GetCharSet() == $charset_name) {
 257                  return true;
 258              } else return false;
 259          } else return true;
 260      }
 261  
 262  }
 263  
 264  /*--------------------------------------------------------------------------------------
 265       Class Name: Recordset
 266  --------------------------------------------------------------------------------------*/
 267  
 268  class ADORecordSet_postgres7 extends ADORecordSet_postgres64{
 269  
 270      var $databaseType = "postgres7";
 271  
 272  
 273  	function __construct($queryID, $mode=false)
 274      {
 275          parent::__construct($queryID, $mode);
 276      }
 277  
 278      // 10% speedup to move MoveNext to child class
 279  	function MoveNext()
 280      {
 281          if (!$this->EOF) {
 282              $this->_currentRow++;
 283              if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
 284                  $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
 285  
 286                  if (is_array($this->fields)) {
 287                      if ($this->fields && isset($this->_blobArr)) $this->_fixblobs();
 288                      return true;
 289                  }
 290              }
 291              $this->fields = false;
 292              $this->EOF = true;
 293          }
 294          return false;
 295      }
 296  
 297  }
 298  
 299  class ADORecordSet_assoc_postgres7 extends ADORecordSet_postgres64{
 300  
 301      var $databaseType = "postgres7";
 302  
 303  
 304  	function __construct($queryID, $mode=false)
 305      {
 306          parent::__construct($queryID, $mode);
 307      }
 308  
 309  	function _fetch()
 310      {
 311          if ($this->_currentRow >= $this->_numOfRows && $this->_numOfRows >= 0)
 312              return false;
 313  
 314          $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
 315  
 316          if ($this->fields) {
 317              if (isset($this->_blobArr)) $this->_fixblobs();
 318              $this->_updatefields();
 319          }
 320  
 321          return (is_array($this->fields));
 322      }
 323  
 324          // Create associative array
 325  	function _updatefields()
 326      {
 327          if (ADODB_ASSOC_CASE == 2) return; // native
 328  
 329          $arr = array();
 330          $lowercase = (ADODB_ASSOC_CASE == 0);
 331  
 332          foreach($this->fields as $k => $v) {
 333              if (is_integer($k)) $arr[$k] = $v;
 334              else {
 335                  if ($lowercase)
 336                      $arr[strtolower($k)] = $v;
 337                  else
 338                      $arr[strtoupper($k)] = $v;
 339              }
 340          }
 341          $this->fields = $arr;
 342      }
 343  
 344  	function MoveNext()
 345      {
 346          if (!$this->EOF) {
 347              $this->_currentRow++;
 348              if ($this->_numOfRows < 0 || $this->_numOfRows > $this->_currentRow) {
 349                  $this->fields = @pg_fetch_array($this->_queryID,$this->_currentRow,$this->fetchMode);
 350  
 351                  if (is_array($this->fields)) {
 352                      if ($this->fields) {
 353                          if (isset($this->_blobArr)) $this->_fixblobs();
 354  
 355                          $this->_updatefields();
 356                      }
 357                      return true;
 358                  }
 359              }
 360  
 361  
 362              $this->fields = false;
 363              $this->EOF = true;
 364          }
 365          return false;
 366      }
 367  }