lib/WikiDB/adodb/drivers/adodb-postgres7.inc.php

   1 <?php
   2 /*
   3  V5.18 3 Sep 2012  (c) 2000-2012 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 ADODB_postgres7()
  79         {
  80                 $this->ADODB_postgres64();
  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 ADORecordSet_postgres7($queryID,$mode=false)
 274         {
 275                 $this->ADORecordSet_postgres64($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 ADORecordSet_assoc_postgres7($queryID,$mode=false)
 305         {
 306                 $this->ADORecordSet_postgres64($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 }
 368 ?>