[Summary view] [Print] [Text view]

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