[Sommaire] [Imprimer]

   1  <?php
   2  
   3  /*
   4  V4.90 8 June 2006  (c) 2000-2006 John Lim (jlim#natsoft.com.my). All rights reserved.
   5    Released under both BSD license and Lesser GPL library license. 
   6    Whenever there is any discrepancy between the two licenses, 
   7    the BSD license will take precedence.
   8    Set tabs to 8.
   9   
  10  */ 
  11  
  12  class ADODB_pdo_pgsql extends ADODB_pdo {
  13      var $metaDatabasesSQL = "select datname from pg_database where datname not in ('template0','template1') order by 1";
  14      var $metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
  15      and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages',
  16       'sql_packages', 'sql_sizing', 'sql_sizing_profiles') 
  17      union 
  18          select viewname,'V' from pg_views where viewname not like 'pg\_%'";
  19      //"select tablename from pg_tables where tablename not like 'pg_%' order by 1";
  20      var $isoDates = true; // accepts dates in ISO format
  21      var $sysDate = "CURRENT_DATE";
  22      var $sysTimeStamp = "CURRENT_TIMESTAMP";
  23      var $blobEncodeType = 'C';
  24      var $metaColumnsSQL = "SELECT a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull,a.atthasdef,a.attnum 
  25          FROM pg_class c, pg_attribute a,pg_type t 
  26          WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s')) and a.attname not like '....%%'
  27  AND a.attnum > 0 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
  28  
  29      // used when schema defined
  30      var $metaColumnsSQL1 = "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum 
  31  FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n 
  32  WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s'))
  33   and c.relnamespace=n.oid and n.nspname='%s' 
  34      and a.attname not like '....%%' AND a.attnum > 0 
  35      AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
  36      
  37      // get primary key etc -- from Freek Dijkstra
  38      var $metaKeySQL = "SELECT ic.relname AS index_name, a.attname AS column_name,i.indisunique AS unique_key, i.indisprimary AS primary_key 
  39      FROM pg_class bc, pg_class ic, pg_index i, pg_attribute a WHERE bc.oid = i.indrelid AND ic.oid = i.indexrelid AND (i.indkey[0] = a.attnum OR i.indkey[1] = a.attnum OR i.indkey[2] = a.attnum OR i.indkey[3] = a.attnum OR i.indkey[4] = a.attnum OR i.indkey[5] = a.attnum OR i.indkey[6] = a.attnum OR i.indkey[7] = a.attnum) AND a.attrelid = bc.oid AND bc.relname = '%s'";
  40      
  41      var $hasAffectedRows = true;
  42      var $hasLimit = false;    // set to true for pgsql 7 only. support pgsql/mysql SELECT * FROM TABLE LIMIT 10
  43      // below suggested by Freek Dijkstra 
  44      var $true = 't';        // string that represents TRUE for a database
  45      var $false = 'f';        // string that represents FALSE for a database
  46      var $fmtDate = "'Y-m-d'";    // used by DBDate() as the default date format used by the database
  47      var $fmtTimeStamp = "'Y-m-d G:i:s'"; // used by DBTimeStamp as the default timestamp fmt.
  48      var $hasMoveFirst = true;
  49      var $hasGenID = true;
  50      var $_genIDSQL = "SELECT NEXTVAL('%s')";
  51      var $_genSeqSQL = "CREATE SEQUENCE %s START %s";
  52      var $_dropSeqSQL = "DROP SEQUENCE %s";
  53      var $metaDefaultsSQL = "SELECT d.adnum as num, d.adsrc as def from pg_attrdef d, pg_class c where d.adrelid=c.oid and c.relname='%s' order by d.adnum";
  54      var $random = 'random()';        /// random function
  55      var $concat_operator='||';
  56      
  57  	function _init($parentDriver)
  58      {
  59      
  60          $parentDriver->hasTransactions = false; ## <<< BUG IN PDO pgsql driver
  61          $parentDriver->hasInsertID = true;
  62      }
  63      
  64  	function ServerInfo()
  65      {
  66          $arr['description'] = ADOConnection::GetOne("select version()");
  67          $arr['version'] = ADOConnection::_findvers($arr['description']);
  68          return $arr;
  69      }
  70      
  71      function &SelectLimit($sql,$nrows=-1,$offset=-1,$inputarr=false,$secs2cache=0) 
  72      {
  73           $offsetStr = ($offset >= 0) ? " OFFSET $offset" : '';
  74           $limitStr  = ($nrows >= 0)  ? " LIMIT $nrows" : '';
  75           if ($secs2cache)
  76                $rs =& $this->CacheExecute($secs2cache,$sql."$limitStr$offsetStr",$inputarr);
  77           else
  78                $rs =& $this->Execute($sql."$limitStr$offsetStr",$inputarr);
  79          
  80          return $rs;
  81      }
  82      
  83      function &MetaTables($ttype=false,$showSchema=false,$mask=false) 
  84      {
  85          $info = $this->ServerInfo();
  86          if ($info['version'] >= 7.3) {
  87              $this->metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
  88                and schemaname  not in ( 'pg_catalog','information_schema')
  89      union 
  90          select viewname,'V' from pg_views where viewname not like 'pg\_%'  and schemaname  not in ( 'pg_catalog','information_schema') ";
  91          }
  92          if ($mask) {
  93              $save = $this->metaTablesSQL;
  94              $mask = $this->qstr(strtolower($mask));
  95              if ($info['version']>=7.3)
  96                  $this->metaTablesSQL = "
  97  select tablename,'T' from pg_tables where tablename like $mask and schemaname not in ( 'pg_catalog','information_schema')  
  98   union 
  99  select viewname,'V' from pg_views where viewname like $mask and schemaname  not in ( 'pg_catalog','information_schema')  ";
 100              else
 101                  $this->metaTablesSQL = "
 102  select tablename,'T' from pg_tables where tablename like $mask 
 103   union 
 104  select viewname,'V' from pg_views where viewname like $mask";
 105          }
 106          $ret =& ADOConnection::MetaTables($ttype,$showSchema);
 107          
 108          if ($mask) {
 109              $this->metaTablesSQL = $save;
 110          }
 111          return $ret;
 112      }
 113      
 114      function &MetaColumns($table,$normalize=true) 
 115      {
 116      global $ADODB_FETCH_MODE;
 117      
 118          $schema = false;
 119          $this->_findschema($table,$schema);
 120          
 121          if ($normalize) $table = strtolower($table);
 122  
 123          $save = $ADODB_FETCH_MODE;
 124          $ADODB_FETCH_MODE = ADODB_FETCH_NUM;
 125          if ($this->fetchMode !== false) $savem = $this->SetFetchMode(false);
 126          
 127          if ($schema) $rs =& $this->Execute(sprintf($this->metaColumnsSQL1,$table,$table,$schema));
 128          else $rs =& $this->Execute(sprintf($this->metaColumnsSQL,$table,$table));
 129          if (isset($savem)) $this->SetFetchMode($savem);
 130          $ADODB_FETCH_MODE = $save;
 131          
 132          if ($rs === false) {
 133              $false = false;
 134              return $false;
 135          }
 136          if (!empty($this->metaKeySQL)) {
 137              // If we want the primary keys, we have to issue a separate query
 138              // Of course, a modified version of the metaColumnsSQL query using a 
 139              // LEFT JOIN would have been much more elegant, but postgres does 
 140              // not support OUTER JOINS. So here is the clumsy way.
 141              
 142              $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
 143              
 144              $rskey = $this->Execute(sprintf($this->metaKeySQL,($table)));
 145              // fetch all result in once for performance.
 146              $keys =& $rskey->GetArray();
 147              if (isset($savem)) $this->SetFetchMode($savem);
 148              $ADODB_FETCH_MODE = $save;
 149              
 150              $rskey->Close();
 151              unset($rskey);
 152          }
 153  
 154          $rsdefa = array();
 155          if (!empty($this->metaDefaultsSQL)) {
 156              $ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
 157              $sql = sprintf($this->metaDefaultsSQL, ($table));
 158              $rsdef = $this->Execute($sql);
 159              if (isset($savem)) $this->SetFetchMode($savem);
 160              $ADODB_FETCH_MODE = $save;
 161              
 162              if ($rsdef) {
 163                  while (!$rsdef->EOF) {
 164                      $num = $rsdef->fields['num'];
 165                      $s = $rsdef->fields['def'];
 166                      if (strpos($s,'::')===false && substr($s, 0, 1) == "'") { /* quoted strings hack... for now... fixme */
 167                          $s = substr($s, 1);
 168                          $s = substr($s, 0, strlen($s) - 1);
 169                      }
 170  
 171                      $rsdefa[$num] = $s;
 172                      $rsdef->MoveNext();
 173                  }
 174              } else {
 175                  ADOConnection::outp( "==> SQL => " . $sql);
 176              }
 177              unset($rsdef);
 178          }
 179      
 180          $retarr = array();
 181          while (!$rs->EOF) {     
 182              $fld = new ADOFieldObject();
 183              $fld->name = $rs->fields[0];
 184              $fld->type = $rs->fields[1];
 185              $fld->max_length = $rs->fields[2];
 186              if ($fld->max_length <= 0) $fld->max_length = $rs->fields[3]-4;
 187              if ($fld->max_length <= 0) $fld->max_length = -1;
 188              if ($fld->type == 'numeric') {
 189                  $fld->scale = $fld->max_length & 0xFFFF;
 190                  $fld->max_length >>= 16;
 191              }
 192              // dannym
 193              // 5 hasdefault; 6 num-of-column
 194              $fld->has_default = ($rs->fields[5] == 't');
 195              if ($fld->has_default) {
 196                  $fld->default_value = $rsdefa[$rs->fields[6]];
 197              }
 198  
 199              //Freek
 200              if ($rs->fields[4] == $this->true) {
 201                  $fld->not_null = true;
 202              }
 203              
 204              // Freek
 205              if (is_array($keys)) {
 206                  foreach($keys as $key) {
 207                      if ($fld->name == $key['column_name'] AND $key['primary_key'] == $this->true) 
 208                          $fld->primary_key = true;
 209                      if ($fld->name == $key['column_name'] AND $key['unique_key'] == $this->true) 
 210                          $fld->unique = true; // What name is more compatible?
 211                  }
 212              }
 213              
 214              if ($ADODB_FETCH_MODE == ADODB_FETCH_NUM) $retarr[] = $fld;    
 215              else $retarr[($normalize) ? strtoupper($fld->name) : $fld->name] = $fld;
 216              
 217              $rs->MoveNext();
 218          }
 219          $rs->Close();
 220          if (empty($retarr)) {
 221              $false = false;
 222              return $false;
 223          } else return $retarr;    
 224          
 225      }
 226  
 227  }
 228  
 229  ?>