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

   1 <?php
   2 /*
   3  @version   v5.20.9  21-Dec-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 }