library/adodb/datadict/datadict-postgres.inc.php

   1 <?php
   2
   3 /**
   4   V5.14 8 Sept 2011  (c) 2000-2011 John Lim (jlim#natsoft.com). 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
   9   Set tabs to 4 for best viewing.
  10
  11 */
  12
  13 // security - hide paths
  14 if (!defined('ADODB_DIR')) die();
  15
  16 class ADODB2_postgres extends ADODB_DataDict {
  17
  18         var $databaseType = 'postgres';
  19         var $seqField = false;
  20         var $seqPrefix = 'SEQ_';
  21         var $addCol = ' ADD COLUMN';
  22         var $quote = '"';
  23         var $renameTable = 'ALTER TABLE %s RENAME TO %s'; // at least since 7.1
  24         var $dropTable = 'DROP TABLE %s CASCADE';
  25
  26         function MetaType($t,$len=-1,$fieldobj=false)
  27         {
  28                 if (is_object($t)) {
  29                         $fieldobj = $t;
  30                         $t = $fieldobj->type;
  31                         $len = $fieldobj->max_length;
  32                 }
  33                 $is_serial = is_object($fieldobj) && $fieldobj->primary_key && $fieldobj->unique &&
  34                         $fieldobj->has_default && substr($fieldobj->default_value,0,8) == 'nextval(';
  35
  36                 switch (strtoupper($t)) {
  37                         case 'INTERVAL':
  38                         case 'CHAR':
  39                         case 'CHARACTER':
  40                         case 'VARCHAR':
  41                         case 'NAME':
  42                         case 'BPCHAR':
  43                                 if ($len <= $this->blobSize) return 'C';
  44
  45                         case 'TEXT':
  46                                 return 'X';
  47
  48                         case 'IMAGE': // user defined type
  49                         case 'BLOB': // user defined type
  50                         case 'BIT':     // This is a bit string, not a single bit, so don't return 'L'
  51                         case 'VARBIT':
  52                         case 'BYTEA':
  53                                 return 'B';
  54
  55                         case 'BOOL':
  56                         case 'BOOLEAN':
  57                                 return 'L';
  58
  59                         case 'DATE':
  60                                 return 'D';
  61
  62                         case 'TIME':
  63                         case 'DATETIME':
  64                         case 'TIMESTAMP':
  65                         case 'TIMESTAMPTZ':
  66                                 return 'T';
  67
  68                         case 'INTEGER': return !$is_serial ? 'I' : 'R';
  69                         case 'SMALLINT':
  70                         case 'INT2': return !$is_serial ? 'I2' : 'R';
  71                         case 'INT4': return !$is_serial ? 'I4' : 'R';
  72                         case 'BIGINT':
  73                         case 'INT8': return !$is_serial ? 'I8' : 'R';
  74
  75                         case 'OID':
  76                         case 'SERIAL':
  77                                 return 'R';
  78
  79                         case 'FLOAT4':
  80                         case 'FLOAT8':
  81                         case 'DOUBLE PRECISION':
  82                         case 'REAL':
  83                                 return 'F';
  84
  85                          default:
  86                                 return 'N';
  87                 }
  88         }
  89
  90         function ActualType($meta)
  91         {
  92                 switch($meta) {
  93                 case 'C': return 'VARCHAR';
  94                 case 'XL':
  95                 case 'X': return 'TEXT';
  96
  97                 case 'C2': return 'VARCHAR';
  98                 case 'X2': return 'TEXT';
  99
 100                 case 'B': return 'BYTEA';
 101
 102                 case 'D': return 'DATE';
 103                 case 'TS':
 104                 case 'T': return 'TIMESTAMP';
 105
 106                 case 'L': return 'BOOLEAN';
 107                 case 'I': return 'INTEGER';
 108                 case 'I1': return 'SMALLINT';
 109                 case 'I2': return 'INT2';
 110                 case 'I4': return 'INT4';
 111                 case 'I8': return 'INT8';
 112
 113                 case 'F': return 'FLOAT8';
 114                 case 'N': return 'NUMERIC';
 115                 default:
 116                         return $meta;
 117                 }
 118         }
 119
 120         /**
 121          * Adding a new Column
 122          *
 123          * reimplementation of the default function as postgres does NOT allow to set the default in the same statement
 124          *
 125          * @param string $tabname table-name
 126          * @param string $flds column-names and types for the changed columns
 127          * @return array with SQL strings
 128          */
 129         function AddColumnSQL($tabname, $flds)
 130         {
 131                 $tabname = $this->TableName ($tabname);
 132                 $sql = array();
 133                 list($lines,$pkey) = $this->_GenFields($flds);
 134                 $alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
 135                 foreach($lines as $v) {
 136                         if (($not_null = preg_match('/NOT NULL/i',$v))) {
 137                                 $v = preg_replace('/NOT NULL/i','',$v);
 138                         }
 139                         if (preg_match('/^([^ ]+) .*DEFAULT ([^ ]+)/',$v,$matches)) {
 140                                 list(,$colname,$default) = $matches;
 141                                 $sql[] = $alter . str_replace('DEFAULT '.$default,'',$v);
 142                                 $sql[] = 'UPDATE '.$tabname.' SET '.$colname.'='.$default;
 143                                 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
 144                         } else {
 145                                 $sql[] = $alter . $v;
 146                         }
 147                         if ($not_null) {
 148                                 list($colname) = explode(' ',$v);
 149                                 $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
 150                         }
 151                 }
 152                 return $sql;
 153         }
 154
 155
 156         function DropIndexSQL ($idxname, $tabname = NULL)
 157         {
 158            return array(sprintf($this->dropIndex, $this->TableName($idxname), $this->TableName($tabname)));
 159         }
 160
 161         /**
 162          * Change the definition of one column
 163          *
 164          * Postgres can't do that on it's own, you need to supply the complete defintion of the new table,
 165          * to allow, recreating the table and copying the content over to the new table
 166          * @param string $tabname table-name
 167          * @param string $flds column-name and type for the changed column
 168          * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
 169          * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
 170          * @return array with SQL strings
 171          */
 172          /*
 173         function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 174         {
 175                 if (!$tableflds) {
 176                         if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
 177                         return array();
 178                 }
 179                 return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
 180         }*/
 181
 182         function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 183         {
 184            // Check if alter single column datatype available - works with 8.0+
 185            $has_alter_column = 8.0 <= (float) @$this->serverInfo['version'];
 186
 187            if ($has_alter_column) {
 188               $tabname = $this->TableName($tabname);
 189               $sql = array();
 190               list($lines,$pkey) = $this->_GenFields($flds);
 191               $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
 192               foreach($lines as $v) {
 193                  if ($not_null = preg_match('/NOT NULL/i',$v)) {
 194                     $v = preg_replace('/NOT NULL/i','',$v);
 195                  }
 196                  // this next block doesn't work - there is no way that I can see to
 197                  // explicitly ask a column to be null using $flds
 198                  else if ($set_null = preg_match('/NULL/i',$v)) {
 199                     // if they didn't specify not null, see if they explicitely asked for null
 200                     $v = preg_replace('/\sNULL/i','',$v);
 201                  }
 202
 203                  if (preg_match('/^([^ ]+) .*DEFAULT ([^ ]+)/',$v,$matches)) {
 204                     list(,$colname,$default) = $matches;
 205                     $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
 206                     $sql[] = $alter . $colname . ' TYPE ' . str_replace('DEFAULT '.$default,'',$v);
 207                     $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
 208                  }
 209                  else {
 210                     // drop default?
 211                     preg_match ('/^\s*(\S+)\s+(.*)$/',$v,$matches);
 212                     list (,$colname,$rest) = $matches;
 213                     $sql[] = $alter . $colname . ' TYPE ' . $rest;
 214                  }
 215
 216                  list($colname) = explode(' ',$v);
 217                  if ($not_null) {
 218                     // this does not error out if the column is already not null
 219                     $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
 220                  }
 221                  if ($set_null) {
 222                     // this does not error out if the column is already null
 223                     $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' DROP NOT NULL';
 224                  }
 225               }
 226               return $sql;
 227            }
 228
 229            // does not have alter column
 230            if (!$tableflds) {
 231               if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
 232               return array();
 233            }
 234            return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
 235         }
 236
 237         /**
 238          * Drop one column
 239          *
 240          * Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table,
 241          * to allow, recreating the table and copying the content over to the new table
 242          * @param string $tabname table-name
 243          * @param string $flds column-name and type for the changed column
 244          * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
 245          * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
 246          * @return array with SQL strings
 247          */
 248         function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 249         {
 250                 $has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
 251                 if (!$has_drop_column && !$tableflds) {
 252                         if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
 253                 return array();
 254         }
 255                 if ($has_drop_column) {
 256                         return ADODB_DataDict::DropColumnSQL($tabname, $flds);
 257                 }
 258                 return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
 259         }
 260
 261         /**
 262          * Save the content into a temp. table, drop and recreate the original table and copy the content back in
 263          *
 264          * We also take care to set the values of the sequenz and recreate the indexes.
 265          * All this is done in a transaction, to not loose the content of the table, if something went wrong!
 266          * @internal
 267          * @param string $tabname table-name
 268          * @param string $dropflds column-names to drop
 269          * @param string $tableflds complete defintion of the new table, eg. for postgres
 270          * @param array/string $tableoptions options for the new table see CreateTableSQL, default ''
 271          * @return array with SQL strings
 272          */
 273         function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
 274         {
 275                 if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
 276                 $copyflds = array();
 277                 foreach($this->MetaColumns($tabname) as $fld) {
 278                         if (!$dropflds || !in_array($fld->name,$dropflds)) {
 279                                 // we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
 280                                 if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) &&
 281                                         in_array($fld->type,array('varchar','char','text','bytea'))) {
 282                                         $copyflds[] = "to_number($fld->name,'S9999999999999D99')";
 283                                 } else {
 284                                         $copyflds[] = $fld->name;
 285                                 }
 286                                 // identify the sequence name and the fld its on
 287                                 if ($fld->primary_key && $fld->has_default &&
 288                                         preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) {
 289                                         $seq_name = $matches[1];
 290                                         $seq_fld = $fld->name;
 291                                 }
 292                         }
 293                 }
 294                 $copyflds = implode(', ',$copyflds);
 295
 296                 $tempname = $tabname.'_tmp';
 297                 $aSql[] = 'BEGIN';              // we use a transaction, to make sure not to loose the content of the table
 298                 $aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
 299                 $aSql = array_merge($aSql,$this->DropTableSQL($tabname));
 300                 $aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
 301                 $aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname";
 302                 if ($seq_name && $seq_fld) {    // if we have a sequence we need to set it again
 303                         $seq_name = $tabname.'_'.$seq_fld.'_seq';       // has to be the name of the new implicit sequence
 304                         $aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
 305                 }
 306                 $aSql[] = "DROP TABLE $tempname";
 307                 // recreate the indexes, if they not contain one of the droped columns
 308                 foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
 309                 {
 310                         if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) {
 311                                 $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
 312                                         $idx_data['unique'] ? array('UNIQUE') : False));
 313                         }
 314                 }
 315                 $aSql[] = 'COMMIT';
 316                 return $aSql;
 317         }
 318
 319         function DropTableSQL($tabname)
 320         {
 321                 $sql = ADODB_DataDict::DropTableSQL($tabname);
 322
 323                 $drop_seq = $this->_DropAutoIncrement($tabname);
 324                 if ($drop_seq) $sql[] = $drop_seq;
 325
 326                 return $sql;
 327         }
 328
 329         // return string must begin with space
 330         function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
 331         {
 332                 if ($fautoinc) {
 333                         $ftype = 'SERIAL';
 334                         return '';
 335                 }
 336                 $suffix = '';
 337                 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 338                 if ($fnotnull) $suffix .= ' NOT NULL';
 339                 if ($fconstraint) $suffix .= ' '.$fconstraint;
 340                 return $suffix;
 341         }
 342
 343         // search for a sequece for the given table (asumes the seqence-name contains the table-name!)
 344         // if yes return sql to drop it
 345         // this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!!
 346         function _DropAutoIncrement($tabname)
 347         {
 348                 $tabname = $this->connection->quote('%'.$tabname.'%');
 349
 350                 $seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'");
 351
 352                 // check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly
 353                 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'")) {
 354                         return False;
 355                 }
 356                 return "DROP SEQUENCE ".$seq;
 357         }
 358
 359         function RenameTableSQL($tabname,$newname)
 360         {
 361                 if (!empty($this->schema)) {
 362                         $rename_from = $this->TableName($tabname);
 363                         $schema_save = $this->schema;
 364                         $this->schema = false;
 365                         $rename_to = $this->TableName($newname);
 366                         $this->schema = $schema_save;
 367                         return array (sprintf($this->renameTable, $rename_from, $rename_to));
 368                 }
 369
 370                 return array (sprintf($this->renameTable, $this->TableName($tabname),$this->TableName($newname)));
 371         }
 372
 373         /*
 374         CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
 375         { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
 376         | table_constraint } [, ... ]
 377         )
 378         [ INHERITS ( parent_table [, ... ] ) ]
 379         [ WITH OIDS | WITHOUT OIDS ]
 380         where column_constraint is:
 381         [ CONSTRAINT constraint_name ]
 382         { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
 383         CHECK (expression) |
 384         REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
 385         [ ON DELETE action ] [ ON UPDATE action ] }
 386         [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
 387         and table_constraint is:
 388         [ CONSTRAINT constraint_name ]
 389         { UNIQUE ( column_name [, ... ] ) |
 390         PRIMARY KEY ( column_name [, ... ] ) |
 391         CHECK ( expression ) |
 392         FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
 393         [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
 394         [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
 395         */
 396
 397
 398         /*
 399         CREATE [ UNIQUE ] INDEX index_name ON table
 400 [ USING acc_method ] ( column [ ops_name ] [, ...] )
 401 [ WHERE predicate ]
 402 CREATE [ UNIQUE ] INDEX index_name ON table
 403 [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
 404 [ WHERE predicate ]
 405         */
 406         function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 407         {
 408                 $sql = array();
 409
 410                 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 411                         $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
 412                         if ( isset($idxoptions['DROP']) )
 413                                 return $sql;
 414                 }
 415
 416                 if ( empty ($flds) ) {
 417                         return $sql;
 418                 }
 419
 420                 $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 421
 422                 $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
 423
 424                 if (isset($idxoptions['HASH']))
 425                         $s .= 'USING HASH ';
 426
 427                 if ( isset($idxoptions[$this->upperName]) )
 428                         $s .= $idxoptions[$this->upperName];
 429
 430                 if ( is_array($flds) )
 431                         $flds = implode(', ',$flds);
 432                 $s .= '(' . $flds . ')';
 433                 $sql[] = $s;
 434
 435                 return $sql;
 436         }
 437
 438         function _GetSize($ftype, $ty, $fsize, $fprec)
 439         {
 440                 if (strlen($fsize) && $ty != 'X' && $ty != 'B' && $ty  != 'I' && strpos($ftype,'(') === false) {
 441                         $ftype .= "(".$fsize;
 442                         if (strlen($fprec)) $ftype .= ",".$fprec;
 443                         $ftype .= ')';
 444                 }
 445                 return $ftype;
 446         }
 447 }
 448 ?>