adodb/html/datadict-postgres_8inc_8php_source.html

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 'TS':
00104                 case 'T': return 'TIMESTAMP';
00105
00106                 case 'L': return 'BOOLEAN';
00107                 case 'I': return 'INTEGER';
00108                 case 'I1': return 'SMALLINT';
00109                 case 'I2': return 'INT2';
00110                 case 'I4': return 'INT4';
00111                 case 'I8': return 'INT8';
00112
00113                 case 'F': return 'FLOAT8';
00114                 case 'N': return 'NUMERIC';
00115                 default:
00116                         return $meta;
00117                 }
00118         }
00119
00129         function AddColumnSQL($tabname, $flds)
00130         {
00131                 $tabname = $this->TableName ($tabname);
00132                 $sql = array();
00133                 list($lines,$pkey) = $this->_GenFields($flds);
00134                 $alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
00135                 foreach($lines as $v) {
00136                         if (($not_null = preg_match('/NOT NULL/i',$v))) {
00137                                 $v = preg_replace('/NOT NULL/i','',$v);
00138                         }
00139                         if (preg_match('/^([^ ]+) .*DEFAULT ([^ ]+)/',$v,$matches)) {
00140                                 list(,$colname,$default) = $matches;
00141                                 $sql[] = $alter . str_replace('DEFAULT '.$default,'',$v);
00142                                 $sql[] = 'UPDATE '.$tabname.' SET '.$colname.'='.$default;
00143                                 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
00144                         } else {
00145                                 $sql[] = $alter . $v;
00146                         }
00147                         if ($not_null) {
00148                                 list($colname) = explode(' ',$v);
00149                                 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
00150                         }
00151                 }
00152                 return $sql;
00153         }
00154
00155
00156         function DropIndexSQL ($idxname, $tabname = NULL)
00157         {
00158            return array(sprintf($this->dropIndex, $this->TableName($idxname), $this->TableName($tabname)));
00159         }
00160
00172          /*
00173         function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
00174         {
00175                 if (!$tableflds) {
00176                         if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
00177                         return array();
00178                 }
00179                 return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
00180         }*/
00181
00182         function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
00183         {
00184            // Check if alter single column datatype available - works with 8.0+
00185            $has_alter_column = 8.0 <= (float) @$this->serverInfo['version'];
00186
00187            if ($has_alter_column) {
00188               $tabname = $this->TableName($tabname);
00189               $sql = array();
00190               list($lines,$pkey) = $this->_GenFields($flds);
00191               $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
00192               foreach($lines as $v) {
00193                  if ($not_null = preg_match('/NOT NULL/i',$v)) {
00194                     $v = preg_replace('/NOT NULL/i','',$v);
00195                  }
00196                  // this next block doesn't work - there is no way that I can see to
00197                  // explicitly ask a column to be null using $flds
00198                  else if ($set_null = preg_match('/NULL/i',$v)) {
00199                     // if they didn't specify not null, see if they explicitely asked for null
00200                     $v = preg_replace('/\sNULL/i','',$v);
00201                  }
00202
00203                  if (preg_match('/^([^ ]+) .*DEFAULT ([^ ]+)/',$v,$matches)) {
00204                     list(,$colname,$default) = $matches;
00205                     $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
00206                     $sql[] = $alter . $colname . ' TYPE ' . str_replace('DEFAULT '.$default,'',$v);
00207                     $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
00208                  }
00209                  else {
00210                     // drop default?
00211                     preg_match ('/^\s*(\S+)\s+(.*)$/',$v,$matches);
00212                     list (,$colname,$rest) = $matches;
00213                     $sql[] = $alter . $colname . ' TYPE ' . $rest;
00214                  }
00215
00216                  list($colname) = explode(' ',$v);
00217                  if ($not_null) {
00218                     // this does not error out if the column is already not null
00219                     $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
00220                  }
00221                  if ($set_null) {
00222                     // this does not error out if the column is already null
00223                     $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' DROP NOT NULL';
00224                  }
00225               }
00226               return $sql;
00227            }
00228
00229            // does not have alter column
00230            if (!$tableflds) {
00231               if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
00232               return array();
00233            }
00234            return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
00235         }
00236
00248         function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
00249         {
00250                 $has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
00251                 if (!$has_drop_column && !$tableflds) {
00252                         if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
00253                 return array();
00254         }
00255                 if ($has_drop_column) {
00256                         return ADODB_DataDict::DropColumnSQL($tabname, $flds);
00257                 }
00258                 return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
00259         }
00260
00273         function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
00274         {
00275                 if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
00276                 $copyflds = array();
00277                 foreach($this->MetaColumns($tabname) as $fld) {
00278                         if (!$dropflds || !in_array($fld->name,$dropflds)) {
00279                                 // we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
00280                                 if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) &&
00281                                         in_array($fld->type,array('varchar','char','text','bytea'))) {
00282                                         $copyflds[] = "to_number($fld->name,'S9999999999999D99')";
00283                                 } else {
00284                                         $copyflds[] = $fld->name;
00285                                 }
00286                                 // identify the sequence name and the fld its on
00287                                 if ($fld->primary_key && $fld->has_default &&
00288                                         preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) {
00289                                         $seq_name = $matches[1];
00290                                         $seq_fld = $fld->name;
00291                                 }
00292                         }
00293                 }
00294                 $copyflds = implode(', ',$copyflds);
00295
00296                 $tempname = $tabname.'_tmp';
00297                 $aSql[] = 'BEGIN';              // we use a transaction, to make sure not to loose the content of the table
00298                 $aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
00299                 $aSql = array_merge($aSql,$this->DropTableSQL($tabname));
00300                 $aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
00301                 $aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname";
00302                 if ($seq_name && $seq_fld) {    // if we have a sequence we need to set it again
00303                         $seq_name = $tabname.'_'.$seq_fld.'_seq';       // has to be the name of the new implicit sequence
00304                         $aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
00305                 }
00306                 $aSql[] = "DROP TABLE $tempname";
00307                 // recreate the indexes, if they not contain one of the droped columns
00308                 foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
00309                 {
00310                         if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) {
00311                                 $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
00312                                         $idx_data['unique'] ? array('UNIQUE') : False));
00313                         }
00314                 }
00315                 $aSql[] = 'COMMIT';
00316                 return $aSql;
00317         }
00318
00319         function DropTableSQL($tabname)
00320         {
00321                 $sql = ADODB_DataDict::DropTableSQL($tabname);
00322
00323                 $drop_seq = $this->_DropAutoIncrement($tabname);
00324                 if ($drop_seq) $sql[] = $drop_seq;
00325
00326                 return $sql;
00327         }
00328
00329         // return string must begin with space
00330         function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
00331         {
00332                 if ($fautoinc) {
00333                         $ftype = 'SERIAL';
00334                         return '';
00335                 }
00336                 $suffix = '';
00337                 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
00338                 if ($fnotnull) $suffix .= ' NOT NULL';
00339                 if ($fconstraint) $suffix .= ' '.$fconstraint;
00340                 return $suffix;
00341         }
00342
00343         // search for a sequece for the given table (asumes the seqence-name contains the table-name!)
00344         // if yes return sql to drop it
00345         // this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!!
00346         function _DropAutoIncrement($tabname)
00347         {
00348                 $tabname = $this->connection->quote('%'.$tabname.'%');
00349
00350                 $seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'");
00351
00352                 // check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly
00353                 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'")) {
00354                         return False;
00355                 }
00356                 return "DROP SEQUENCE ".$seq;
00357         }
00358
00359         function RenameTableSQL($tabname,$newname)
00360         {
00361                 if (!empty($this->schema)) {
00362                         $rename_from = $this->TableName($tabname);
00363                         $schema_save = $this->schema;
00364                         $this->schema = false;
00365                         $rename_to = $this->TableName($newname);
00366                         $this->schema = $schema_save;
00367                         return array (sprintf($this->renameTable, $rename_from, $rename_to));
00368                 }
00369
00370                 return array (sprintf($this->renameTable, $this->TableName($tabname),$this->TableName($newname)));
00371         }
00372
00373         /*
00374         CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
00375         { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
00376         | table_constraint } [, ... ]
00377         )
00378         [ INHERITS ( parent_table [, ... ] ) ]
00379         [ WITH OIDS | WITHOUT OIDS ]
00380         where column_constraint is:
00381         [ CONSTRAINT constraint_name ]
00382         { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
00383         CHECK (expression) |
00384         REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
00385         [ ON DELETE action ] [ ON UPDATE action ] }
00386         [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
00387         and table_constraint is:
00388         [ CONSTRAINT constraint_name ]
00389         { UNIQUE ( column_name [, ... ] ) |
00390         PRIMARY KEY ( column_name [, ... ] ) |
00391         CHECK ( expression ) |
00392         FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
00393         [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
00394         [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
00395         */
00396
00397
00398         /*
00399         CREATE [ UNIQUE ] INDEX index_name ON table
00400 [ USING acc_method ] ( column [ ops_name ] [, ...] )
00401 [ WHERE predicate ]
00402 CREATE [ UNIQUE ] INDEX index_name ON table
00403 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
00404 [ WHERE predicate ]
00405         */
00406         function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
00407         {
00408                 $sql = array();
00409
00410                 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
00411                         $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
00412                         if ( isset($idxoptions['DROP']) )
00413                                 return $sql;
00414                 }
00415
00416                 if ( empty ($flds) ) {
00417                         return $sql;
00418                 }
00419
00420                 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
00421
00422                 $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
00423
00424                 if (isset($idxoptions['HASH']))
00425                         $s .= 'USING HASH ';
00426
00427                 if ( isset($idxoptions[$this->upperName]) )
00428                         $s .= $idxoptions[$this->upperName];
00429
00430                 if ( is_array($flds) )
00431                         $flds = implode(', ',$flds);
00432                 $s .= '(' . $flds . ')';
00433                 $sql[] = $s;
00434
00435                 return $sql;
00436         }
00437
00438         function _GetSize($ftype, $ty, $fsize, $fprec)
00439         {
00440                 if (strlen($fsize) && $ty != 'X' && $ty != 'B' && $ty  != 'I' && strpos($ftype,'(') === false) {
00441                         $ftype .= "(".$fsize;
00442                         if (strlen($fprec)) $ftype .= ",".$fprec;
00443                         $ftype .= ')';
00444                 }
00445                 return $ftype;
00446         }
00447 }
00448 ?>