[Sommaire] [Imprimer]

   1  <?php
   2  /**
   3    V4.65 22 July 2005  (c) 2000-2005 John Lim (jlim@natsoft.com.my). 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      
   8    Set tabs to 4 for best viewing.
   9    
  10    Modified 28 August, 2005 for use with ADOdb Lite by Mark Dickenson
  11    
  12  */
  13  
  14  // security - hide paths
  15  if (!defined('ADODB_DIR')) die();
  16  
  17  class ADODB2_postgres7 extends ADODB_DataDict {
  18  
  19      var $dbtype = 'postgres7';
  20      var $seqField = false;
  21      var $seqPrefix = 'SEQ_';
  22      var $addCol = ' ADD COLUMN';
  23      var $quote = '"';
  24      var $renameTable = 'ALTER TABLE %s RENAME TO %s'; // at least since 7.1
  25  
  26      var $metaTablesSQL = "select tablename,'T' from pg_tables where tablename not like 'pg\_%'
  27      and tablename not in ('sql_features', 'sql_implementation_info', 'sql_languages',
  28       'sql_packages', 'sql_sizing', 'sql_sizing_profiles') 
  29      union 
  30          select viewname,'V' from pg_views where viewname not like 'pg\_%'";
  31      //"select tablename from pg_tables where tablename not like 'pg_%' order by 1";
  32      var $metaColumnsSQL = "SELECT a.attname,t.typname,a.attlen,a.atttypmod,a.attnotnull,a.atthasdef,a.attnum 
  33          FROM pg_class c, pg_attribute a,pg_type t 
  34          WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s')) and a.attname not like '....%%'
  35  AND a.attnum > 0 AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
  36  
  37      // used when schema defined
  38      var $metaColumnsSQL1 = "SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef, a.attnum 
  39  FROM pg_class c, pg_attribute a, pg_type t, pg_namespace n 
  40  WHERE relkind in ('r','v') AND (c.relname='%s' or c.relname = lower('%s'))
  41   and c.relnamespace=n.oid and n.nspname='%s' 
  42      and a.attname not like '....%%' AND a.attnum > 0 
  43      AND a.atttypid = t.oid AND a.attrelid = c.oid ORDER BY a.attnum";
  44      
  45      // get primary key etc -- from Freek Dijkstra
  46      var $metaKeySQL = "SELECT ic.relname AS index_name, a.attname AS column_name,i.indisunique AS unique_key, i.indisprimary AS primary_key 
  47      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'";
  48  
  49   	function ActualType($meta)
  50      {
  51          switch($meta) {
  52          case 'C': return 'VARCHAR';
  53          case 'XL':
  54          case 'X': return 'TEXT';
  55          
  56          case 'C2': return 'VARCHAR';
  57          case 'X2': return 'TEXT';
  58          
  59          case 'B': return 'BYTEA';
  60              
  61          case 'D': return 'DATE';
  62          case 'T': return 'TIMESTAMP';
  63          case 'DT': return 'TIMESTAMP';
  64          
  65          case 'L': return 'BOOLEAN';
  66          case 'I': return 'INTEGER';
  67          case 'I1': return 'SMALLINT';
  68          case 'I2': return 'INT2';
  69          case 'I4': return 'INT4';
  70          case 'I8': return 'INT8';
  71          
  72          case 'F': return 'FLOAT8';
  73          case 'N': return 'NUMERIC';
  74          default:
  75              return $meta;
  76          }
  77      }
  78  
  79  	function AddColumnSQL($tabname, $flds)
  80      {
  81          $tabname = $this->TableName ($tabname);
  82          $sql = array();
  83          list($lines,$pkey) = $this->_GenFields($flds);
  84          $alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
  85          foreach($lines as $v) {
  86              if (($not_null = preg_match('/NOT NULL/i',$v))) {
  87                  $v = preg_replace('/NOT NULL/i','',$v);
  88              }
  89              if (preg_match('/^([^ ]+) .*(DEFAULT [^ ]+)/',$v,$matches)) {
  90                  list(,$colname,$default) = $matches;
  91                  $sql[] = $alter . str_replace($default,'',$v);
  92                  $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET ' . $default;
  93              } else {                
  94                  $sql[] = $alter . $v;
  95              }
  96              if ($not_null) {
  97                  list($colname) = explode(' ',$v);
  98                  $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
  99              }
 100          }
 101          return $sql;
 102      }
 103  
 104  	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 105      {
 106          if (!$tableflds) {
 107              if ($this->debug) $this->outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
 108              return array();
 109          }
 110          return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
 111      }
 112  
 113  	function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 114      {
 115          $has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
 116          if (!$has_drop_column && !$tableflds) {
 117              if ($this->debug) $this->outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
 118          return array();
 119      }
 120          if ($has_drop_column) {
 121              return ADODB_DataDict::DropColumnSQL($tabname, $flds);
 122          }
 123          return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
 124      }
 125  
 126  	function DropTableSQL($tabname)
 127      {
 128          $sql = ADODB_DataDict::DropTableSQL($tabname);
 129          $drop_seq = $this->_DropAutoIncrement($tabname);
 130          if ($drop_seq) $sql[] = $drop_seq;
 131          return $sql;
 132      }
 133  
 134  	function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint)
 135      {
 136          if ($fautoinc) {
 137              $ftype = 'SERIAL';
 138              return '';
 139          }
 140          $suffix = '';
 141          if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 142          if ($fnotnull) $suffix .= ' NOT NULL';
 143          if ($fconstraint) $suffix .= ' '.$fconstraint;
 144          return $suffix;
 145      }
 146  
 147  	function _DropAutoIncrement($tabname)
 148      {
 149          $tabname = $this->connection->quote('%'.$tabname.'%');
 150  
 151          $seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'");
 152  
 153          // check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly
 154          if (!$seq || $this->connection->GetOne("SELECT relname FROM pg_class JOIN pg_depend ON pg_class.relfilenode=pg_depend.objid WHERE relname='$seq' AND relkind='S' AND deptype='i'")) {
 155              return False;
 156          }
 157          return "DROP SEQUENCE ".$seq;
 158      }
 159  
 160  	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 161      {
 162          $sql = array();
 163          if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 164              $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
 165              if ( isset($idxoptions['DROP']) )
 166                  return $sql;
 167          }
 168          if ( empty ($flds) ) {
 169              return $sql;
 170          }
 171  
 172          $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 173          $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
 174          if (isset($idxoptions['HASH']))
 175              $s .= 'USING HASH ';
 176  
 177          if ( isset($idxoptions[$this->upperName]) )
 178              $s .= $idxoptions[$this->upperName];
 179  
 180          if ( is_array($flds) )
 181              $flds = implode(', ',$flds);
 182          $s .= '(' . $flds . ')';
 183          $sql[] = $s;
 184          return $sql;
 185      }
 186  
 187  	function MetaType($t,$len=-1,$fieldobj=false)
 188      {
 189          if (is_object($t)) {
 190              $fieldobj = $t;
 191              $t = $fieldobj->type;
 192              $len = $fieldobj->max_length;
 193          }
 194          $is_serial = is_object($fieldobj) && $fieldobj->primary_key && $fieldobj->unique && $fieldobj->has_default && substr($fieldobj->default_value,0,8) == 'nextval(';
 195          switch (strtoupper($t)) {
 196              case 'INTERVAL':
 197              case 'CHAR':
 198              case 'CHARACTER':
 199              case 'VARCHAR':
 200              case 'NAME':
 201                 case 'BPCHAR':
 202                  if ($len <= $this->blobSize) return 'C';
 203              case 'TEXT':
 204                  return 'X';
 205              case 'IMAGE': // user defined type
 206              case 'BLOB': // user defined type
 207              case 'BIT':    // This is a bit string, not a single bit, so don't return 'L'
 208              case 'VARBIT':
 209              case 'BYTEA':
 210                  return 'B';
 211              case 'BOOL':
 212              case 'BOOLEAN':
 213                  return 'L';
 214              case 'DATE':
 215                  return 'D';
 216              case 'TIME':
 217              case 'DATETIME':
 218              case 'TIMESTAMP':
 219              case 'TIMESTAMPTZ':
 220                  return 'T';
 221              case 'INTEGER': return !$is_serial ? 'I' : 'R';
 222              case 'SMALLINT': 
 223              case 'INT2': return !$is_serial ? 'I2' : 'R';
 224              case 'INT4': return !$is_serial ? 'I4' : 'R';
 225              case 'BIGINT': 
 226              case 'INT8': return !$is_serial ? 'I8' : 'R';
 227              case 'OID':
 228              case 'SERIAL':
 229                  return 'R';
 230              case 'FLOAT4':
 231              case 'FLOAT8':
 232              case 'DOUBLE PRECISION':
 233              case 'REAL':
 234                  return 'F';
 235              default:
 236                   return 'N';
 237          }
 238      }
 239  
 240  //    function &MetaTables($ttype=false,$showSchema=false,$mask=false) 
 241  //    {
 242  //        global $ADODB_FETCH_MODE;
 243  //    }
 244  
 245  //    function &MetaColumns($table,$upper=true) 
 246  //    {
 247  //        global $ADODB_FETCH_MODE;
 248  //    }
 249  
 250  //    function MetaPrimaryKeys($table, $owner=false)
 251  //    {
 252  //    }
 253  
 254  //     function &MetaIndexes($table, $primary = false, $owner = false)
 255  //     {
 256  //     }
 257  
 258  	function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
 259      {
 260          if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
 261          $copyflds = array();
 262          foreach($this->MetaColumns($tabname) as $fld) {
 263              if (!$dropflds || !in_array($fld->name,$dropflds)) {
 264                  // we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
 265                  if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) && 
 266                      in_array($fld->type,array('varchar','char','text','bytea'))) {
 267                      $copyflds[] = "to_number($fld->name,'S99D99')";
 268                  } else {
 269                      $copyflds[] = $fld->name;
 270                  }
 271                  // identify the sequence name and the fld its on
 272                  if ($fld->primary_key && $fld->has_default && 
 273                      preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) {
 274                      $seq_name = $matches[1];
 275                      $seq_fld = $fld->name;
 276                  }
 277              }
 278          }
 279          $copyflds = implode(', ',$copyflds);
 280          
 281          $tempname = $tabname.'_tmp';
 282          $aSql[] = 'BEGIN';        // we use a transaction, to make sure not to loose the content of the table
 283          $aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
 284          $aSql = array_merge($aSql,$this->DropTableSQL($tabname));
 285          $aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
 286          $aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname";
 287          if ($seq_name && $seq_fld) {    // if we have a sequence we need to set it again
 288              $seq_name = $tabname.'_'.$seq_fld.'_seq';    // has to be the name of the new implicit sequence
 289              $aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
 290          }
 291          $aSql[] = "DROP TABLE $tempname";
 292          // recreate the indexes, if they not contain one of the droped columns
 293          foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
 294          {
 295              if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) {
 296                  $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
 297                      $idx_data['unique'] ? array('UNIQUE') : False));
 298              }
 299          }
 300          $aSql[] = 'COMMIT';
 301          return $aSql;
 302      }
 303  }
 304  
 305  ?>