eGroupWare PHP Cross Reference Groupware Applications

Source: /phpgwapi/inc/adodb/datadict/datadict-postgres.inc.php - 369 lines - 12182 bytes - Summary - Text - Print

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

title

Description

title

Description

title

Description

title

title

Body