00001 <?php
00002 
00013 // security - hide paths
00014 if (!defined('ADODB_DIR')) die();
00015 
00016 class ADODB2_postgres extends ADODB_DataDict {
00017         
00018         var $databaseType = 'postgres';
00019         var $seqField = false;
00020         var $seqPrefix = 'SEQ_';
00021         var $addCol = ' ADD COLUMN';
00022         var $quote = '"';
00023         var $renameTable = 'ALTER TABLE %s RENAME TO %s'; // at least since 7.1
00024         var $dropTable = 'DROP TABLE %s CASCADE';
00025         
00026         function MetaType($t,$len=-1,$fieldobj=false)
00027         {
00028                 if (is_object($t)) {
00029                         $fieldobj = $t;
00030                         $t = $fieldobj->type;
00031                         $len = $fieldobj->max_length;
00032                 }
00033                 $is_serial = is_object($fieldobj) && $fieldobj->primary_key && $fieldobj->unique && 
00034                         $fieldobj->has_default && substr($fieldobj->default_value,0,8) == 'nextval(';
00035                 
00036                 switch (strtoupper($t)) {
00037                         case 'INTERVAL':
00038                         case 'CHAR':
00039                         case 'CHARACTER':
00040                         case 'VARCHAR':
00041                         case 'NAME':
00042                         case 'BPCHAR':
00043                                 if ($len <= $this->blobSize) return 'C';
00044                         
00045                         case 'TEXT':
00046                                 return 'X';
00047         
00048                         case 'IMAGE': // user defined type
00049                         case 'BLOB': // user defined type
00050                         case 'BIT':     // This is a bit string, not a single bit, so don't return 'L'
00051                         case 'VARBIT':
00052                         case 'BYTEA':
00053                                 return 'B';
00054                         
00055                         case 'BOOL':
00056                         case 'BOOLEAN':
00057                                 return 'L';
00058                         
00059                         case 'DATE':
00060                                 return 'D';
00061                         
00062                         case 'TIME':
00063                         case 'DATETIME':
00064                         case 'TIMESTAMP':
00065                         case 'TIMESTAMPTZ':
00066                                 return 'T';
00067                         
00068                         case 'INTEGER': return !$is_serial ? 'I' : 'R';
00069                         case 'SMALLINT': 
00070                         case 'INT2': return !$is_serial ? 'I2' : 'R';
00071                         case 'INT4': return !$is_serial ? 'I4' : 'R';
00072                         case 'BIGINT': 
00073                         case 'INT8': return !$is_serial ? 'I8' : 'R';
00074                                 
00075                         case 'OID':
00076                         case 'SERIAL':
00077                                 return 'R';
00078                         
00079                         case 'FLOAT4':
00080                         case 'FLOAT8':
00081                         case 'DOUBLE PRECISION':
00082                         case 'REAL':
00083                                 return 'F';
00084                                 
00085                          default:
00086                                 return 'N';
00087                 }
00088         }
00089         
00090         function ActualType($meta)
00091         {
00092                 switch($meta) {
00093                 case 'C': return 'VARCHAR';
00094                 case 'XL':
00095                 case 'X': return 'TEXT';
00096                 
00097                 case 'C2': return 'VARCHAR';
00098                 case 'X2': return 'TEXT';
00099                 
00100                 case 'B': return 'BYTEA';
00101                         
00102                 case 'D': return 'DATE';
00103                 case 'T': return 'TIMESTAMP';
00104                 
00105                 case 'L': return 'BOOLEAN';
00106                 case 'I': return 'INTEGER';
00107                 case 'I1': return 'SMALLINT';
00108                 case 'I2': return 'INT2';
00109                 case 'I4': return 'INT4';
00110                 case 'I8': return 'INT8';
00111                 
00112                 case 'F': return 'FLOAT8';
00113                 case 'N': return 'NUMERIC';
00114                 default:
00115                         return $meta;
00116                 }
00117         }
00118         
00128         function AddColumnSQL($tabname, $flds)
00129         {
00130                 $tabname = $this->TableName ($tabname);
00131                 $sql = array();
00132                 list($lines,$pkey) = $this->_GenFields($flds);
00133                 $alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
00134                 foreach($lines as $v) {
00135                         if (($not_null = preg_match('/NOT NULL/i',$v))) {
00136                                 $v = preg_replace('/NOT NULL/i','',$v);
00137                         }
00138                         if (preg_match('/^([^ ]+) .*DEFAULT ([^ ]+)/',$v,$matches)) {
00139                                 list(,$colname,$default) = $matches;
00140                                 $sql[] = $alter . str_replace('DEFAULT '.$default,'',$v);
00141                                 $sql[] = 'UPDATE '.$tabname.' SET '.$colname.'='.$default;
00142                                 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
00143                         } else {                                
00144                                 $sql[] = $alter . $v;
00145                         }
00146                         if ($not_null) {
00147                                 list($colname) = explode(' ',$v);
00148                                 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
00149                         }
00150                 }
00151                 return $sql;
00152         }
00153         
00165         function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
00166         {
00167                 if (!$tableflds) {
00168                         if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
00169                         return array();
00170                 }
00171                 return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
00172         }
00173         
00185         function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
00186         {
00187                 $has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
00188                 if (!$has_drop_column && !$tableflds) {
00189                         if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
00190                 return array();
00191         }
00192                 if ($has_drop_column) {
00193                         return ADODB_DataDict::DropColumnSQL($tabname, $flds);
00194                 }
00195                 return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
00196         }
00197         
00210         function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
00211         {
00212                 if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
00213                 $copyflds = array();
00214                 foreach($this->MetaColumns($tabname) as $fld) {
00215                         if (!$dropflds || !in_array($fld->name,$dropflds)) {
00216                                 // we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
00217                                 if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) && 
00218                                         in_array($fld->type,array('varchar','char','text','bytea'))) {
00219                                         $copyflds[] = "to_number($fld->name,'S9999999999999D99')";
00220                                 } else {
00221                                         $copyflds[] = $fld->name;
00222                                 }
00223                                 // identify the sequence name and the fld its on
00224                                 if ($fld->primary_key && $fld->has_default && 
00225                                         preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) {
00226                                         $seq_name = $matches[1];
00227                                         $seq_fld = $fld->name;
00228                                 }
00229                         }
00230                 }
00231                 $copyflds = implode(', ',$copyflds);
00232                 
00233                 $tempname = $tabname.'_tmp';
00234                 $aSql[] = 'BEGIN';              // we use a transaction, to make sure not to loose the content of the table
00235                 $aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
00236                 $aSql = array_merge($aSql,$this->DropTableSQL($tabname));
00237                 $aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
00238                 $aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname";
00239                 if ($seq_name && $seq_fld) {    // if we have a sequence we need to set it again
00240                         $seq_name = $tabname.'_'.$seq_fld.'_seq';       // has to be the name of the new implicit sequence
00241                         $aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
00242                 }
00243                 $aSql[] = "DROP TABLE $tempname";
00244                 // recreate the indexes, if they not contain one of the droped columns
00245                 foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
00246                 {
00247                         if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) {
00248                                 $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
00249                                         $idx_data['unique'] ? array('UNIQUE') : False));
00250                         }
00251                 }
00252                 $aSql[] = 'COMMIT';
00253                 return $aSql;
00254         }
00255         
00256         function DropTableSQL($tabname)
00257         {
00258                 $sql = ADODB_DataDict::DropTableSQL($tabname);
00259                 
00260                 $drop_seq = $this->_DropAutoIncrement($tabname);
00261                 if ($drop_seq) $sql[] = $drop_seq;
00262                 
00263                 return $sql;
00264         }
00265 
00266         // return string must begin with space
00267         function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint)
00268         {
00269                 if ($fautoinc) {
00270                         $ftype = 'SERIAL';
00271                         return '';
00272                 }
00273                 $suffix = '';
00274                 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
00275                 if ($fnotnull) $suffix .= ' NOT NULL';
00276                 if ($fconstraint) $suffix .= ' '.$fconstraint;
00277                 return $suffix;
00278         }
00279         
00280         // search for a sequece for the given table (asumes the seqence-name contains the table-name!)
00281         // if yes return sql to drop it
00282         // this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!!
00283         function _DropAutoIncrement($tabname)
00284         {
00285                 $tabname = $this->connection->quote('%'.$tabname.'%');
00286 
00287                 $seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'");
00288 
00289                 // check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly
00290                 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'")) {
00291                         return False;
00292                 }
00293                 return "DROP SEQUENCE ".$seq;
00294         }
00295         
00296         /*
00297         CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
00298         { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
00299         | table_constraint } [, ... ]
00300         )
00301         [ INHERITS ( parent_table [, ... ] ) ]
00302         [ WITH OIDS | WITHOUT OIDS ]
00303         where column_constraint is:
00304         [ CONSTRAINT constraint_name ]
00305         { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
00306         CHECK (expression) |
00307         REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
00308         [ ON DELETE action ] [ ON UPDATE action ] }
00309         [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
00310         and table_constraint is:
00311         [ CONSTRAINT constraint_name ]
00312         { UNIQUE ( column_name [, ... ] ) |
00313         PRIMARY KEY ( column_name [, ... ] ) |
00314         CHECK ( expression ) |
00315         FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
00316         [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
00317         [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
00318         */
00319         
00320         
00321         /*
00322         CREATE [ UNIQUE ] INDEX index_name ON table
00323 [ USING acc_method ] ( column [ ops_name ] [, ...] )
00324 [ WHERE predicate ]
00325 CREATE [ UNIQUE ] INDEX index_name ON table
00326 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
00327 [ WHERE predicate ]
00328         */
00329         function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
00330         {
00331                 $sql = array();
00332                 
00333                 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
00334                         $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
00335                         if ( isset($idxoptions['DROP']) )
00336                                 return $sql;
00337                 }
00338                 
00339                 if ( empty ($flds) ) {
00340                         return $sql;
00341                 }
00342                 
00343                 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
00344                 
00345                 $s = 'CREATE' . $unique . ' INDEX "' . $idxname . '" ON "' . $tabname . '" ';
00346                 
00347                 if (isset($idxoptions['HASH']))
00348                         $s .= 'USING HASH ';
00349                 
00350                 if ( isset($idxoptions[$this->upperName]) )
00351                         $s .= $idxoptions[$this->upperName];
00352                 
00353                 if ( is_array($flds) )
00354                         $flds = implode('", "',$flds);
00355                 $s .= '("' . $flds . '")';
00356                 $sql[] = $s;
00357                 
00358                 return $sql;
00359         }
00360         
00361         function _GetSize($ftype, $ty, $fsize, $fprec)
00362         {
00363                 if (strlen($fsize) && $ty != 'X' && $ty != 'B' && $ty  != 'I' && strpos($ftype,'(') === false) {
00364                         $ftype .= "(".$fsize;
00365                         if (strlen($fprec)) $ftype .= ",".$fprec;
00366                         $ftype .= ')';
00367                 }
00368                 return $ftype;
00369         }
00370 }
00371 ?>
datadict-postgres.inc.php
