PHPOpenChat PHP Cross Reference Customer Relationship Management

Source: /include/adodb/datadict/datadict-postgres.inc.php - 359 lines - 12021 bytes - Summary - Text - Print

   1  <?php
   2  
   3  /**

   4    V4.61 24 Feb 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 'SMALLINT';
 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,'',$v);
 140                  $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET ' . $default;
 141              } else {                
 142                  $sql[] = $alter . $v;
 143              }
 144              if ($not_null) {
 145                  list($colname) = explode(' ',$v);
 146                  $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
 147              }
 148          }
 149          return $sql;
 150      }
 151      
 152      /**

 153       * Change the definition of one column

 154       *

 155       * Postgres can't do that on it's own, you need to supply the complete defintion of the new table,

 156       * to allow, recreating the table and copying the content over to the new table

 157       * @param string $tabname table-name

 158       * @param string $flds column-name and type for the changed column

 159       * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''

 160       * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''

 161       * @return array with SQL strings

 162       */
 163  	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 164      {
 165          if (!$tableflds) {
 166              if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
 167              return array();
 168          }
 169          return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
 170      }
 171      
 172      /**

 173       * Drop one column

 174       *

 175       * Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table,

 176       * to allow, recreating the table and copying the content over to the new table

 177       * @param string $tabname table-name

 178       * @param string $flds column-name and type for the changed column

 179       * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''

 180       * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''

 181       * @return array with SQL strings

 182       */
 183  	function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 184      {
 185          $has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
 186          if (!$has_drop_column && !$tableflds) {
 187              if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
 188          return array();
 189      }
 190          if ($has_drop_column) {
 191              return ADODB_DataDict::DropColumnSQL($tabname, $flds);
 192          }
 193          return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
 194      }
 195      
 196      /**

 197       * Save the content into a temp. table, drop and recreate the original table and copy the content back in

 198       *

 199       * We also take care to set the values of the sequenz and recreate the indexes.

 200       * All this is done in a transaction, to not loose the content of the table, if something went wrong!

 201       * @internal

 202       * @param string $tabname table-name

 203       * @param string $dropflds column-names to drop

 204       * @param string $tableflds complete defintion of the new table, eg. for postgres

 205       * @param array/string $tableoptions options for the new table see CreateTableSQL, default ''

 206       * @return array with SQL strings

 207       */
 208  	function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
 209      {
 210          if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
 211          $copyflds = array();
 212          foreach($this->MetaColumns($tabname) as $fld) {
 213              if (!$dropflds || !in_array($fld->name,$dropflds)) {
 214                  // we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one

 215                  if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) && 
 216                      in_array($fld->type,array('varchar','char','text','bytea'))) {
 217                      $copyflds[] = "to_number($fld->name,'S99D99')";
 218                  } else {
 219                      $copyflds[] = $fld->name;
 220                  }
 221                  // identify the sequence name and the fld its on

 222                  if ($fld->primary_key && $fld->has_default && 
 223                      preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) {
 224                      $seq_name = $matches[1];
 225                      $seq_fld = $fld->name;
 226                  }
 227              }
 228          }
 229          $copyflds = implode(', ',$copyflds);
 230          
 231          $tempname = $tabname.'_tmp';
 232          $aSql[] = 'BEGIN';        // we use a transaction, to make sure not to loose the content of the table

 233          $aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
 234          $aSql = array_merge($aSql,$this->DropTableSQL($tabname));
 235          $aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
 236          $aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname";
 237          if ($seq_name && $seq_fld) {    // if we have a sequence we need to set it again
 238              $seq_name = $tabname.'_'.$seq_fld.'_seq';    // has to be the name of the new implicit sequence

 239              $aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
 240          }
 241          $aSql[] = "DROP TABLE $tempname";
 242          // recreate the indexes, if they not contain one of the droped columns

 243          foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
 244          {
 245              if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) {
 246                  $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
 247                      $idx_data['unique'] ? array('UNIQUE') : False));
 248              }
 249          }
 250          $aSql[] = 'COMMIT';
 251          return $aSql;
 252      }
 253      
 254  	function DropTableSQL($tabname)
 255      {
 256          $sql = ADODB_DataDict::DropTableSQL($tabname);
 257          
 258          $drop_seq = $this->_DropAutoIncrement($tabname);
 259          if ($drop_seq) $sql[] = $drop_seq;
 260          
 261          return $sql;
 262      }
 263  
 264      // return string must begin with space

 265  	function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint)
 266      {
 267          if ($fautoinc) {
 268              $ftype = 'SERIAL';
 269              return '';
 270          }
 271          $suffix = '';
 272          if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 273          if ($fnotnull) $suffix .= ' NOT NULL';
 274          if ($fconstraint) $suffix .= ' '.$fconstraint;
 275          return $suffix;
 276      }
 277      
 278      // search for a sequece for the given table (asumes the seqence-name contains the table-name!)

 279      // if yes return sql to drop it

 280      // this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!!

 281  	function _DropAutoIncrement($tabname)
 282      {
 283          $tabname = $this->connection->quote('%'.$tabname.'%');
 284  
 285          $seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'");
 286  
 287          // check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly

 288          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'")) {
 289              return False;
 290          }
 291          return "DROP SEQUENCE ".$seq;
 292      }
 293      
 294      /*

 295      CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (

 296      { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]

 297      | table_constraint } [, ... ]

 298      )

 299      [ INHERITS ( parent_table [, ... ] ) ]

 300      [ WITH OIDS | WITHOUT OIDS ]

 301      where column_constraint is:

 302      [ CONSTRAINT constraint_name ]

 303      { NOT NULL | NULL | UNIQUE | PRIMARY KEY |

 304      CHECK (expression) |

 305      REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]

 306      [ ON DELETE action ] [ ON UPDATE action ] }

 307      [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

 308      and table_constraint is:

 309      [ CONSTRAINT constraint_name ]

 310      { UNIQUE ( column_name [, ... ] ) |

 311      PRIMARY KEY ( column_name [, ... ] ) |

 312      CHECK ( expression ) |

 313      FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]

 314      [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }

 315      [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

 316      */
 317      
 318      
 319      /*

 320      CREATE [ UNIQUE ] INDEX index_name ON table

 321  [ USING acc_method ] ( column [ ops_name ] [, ...] )

 322  [ WHERE predicate ]

 323  CREATE [ UNIQUE ] INDEX index_name ON table

 324  [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )

 325  [ WHERE predicate ]

 326      */
 327  	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 328      {
 329          $sql = array();
 330          
 331          if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 332              $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
 333              if ( isset($idxoptions['DROP']) )
 334                  return $sql;
 335          }
 336          
 337          if ( empty ($flds) ) {
 338              return $sql;
 339          }
 340          
 341          $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 342          
 343          $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
 344          
 345          if (isset($idxoptions['HASH']))
 346              $s .= 'USING HASH ';
 347          
 348          if ( isset($idxoptions[$this->upperName]) )
 349              $s .= $idxoptions[$this->upperName];
 350          
 351          if ( is_array($flds) )
 352              $flds = implode(', ',$flds);
 353          $s .= '(' . $flds . ')';
 354          $sql[] = $s;
 355          
 356          return $sql;
 357      }
 358  }
 359  ?>

title

Description

title

Description

title

Description

title

title

Body