[Sommaire] [Imprimer]

   1  <?php
   2  
   3  /**
   4    V4.94 23 Jan 2007  (c) 2000-2007 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      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 'T': return 'TIMESTAMP';
 104          
 105          case 'L': return 'BOOLEAN';
 106          case 'I': return 'INTEGER';
 107          case 'I1': return 'SMALLINT';
 108          case 'I2': return 'INT2';
 109          case 'I4': return 'INT4';
 110          case 'I8': return 'INT8';
 111          
 112          case 'F': return 'FLOAT8';
 113          case 'N': return 'NUMERIC';
 114          default:
 115              return $meta;
 116          }
 117      }
 118      
 119      /**
 120       * Adding a new Column 
 121       *
 122       * reimplementation of the default function as postgres does NOT allow to set the default in the same statement
 123       *
 124       * @param string $tabname table-name
 125       * @param string $flds column-names and types for the changed columns
 126       * @return array with SQL strings
 127       */
 128  	function AddColumnSQL($tabname, $flds)
 129      {
 130          $tabname = $this->TableName ($tabname);
 131          $sql = array();
 132          list($lines,$pkey) = $this->_GenFields($flds);
 133          $alter = 'ALTER TABLE ' . $tabname . $this->addCol . ' ';
 134          foreach($lines as $v) {
 135              if (($not_null = preg_match('/NOT NULL/i',$v))) {
 136                  $v = preg_replace('/NOT NULL/i','',$v);
 137              }
 138              if (preg_match('/^([^ ]+) .*DEFAULT ([^ ]+)/',$v,$matches)) {
 139                  list(,$colname,$default) = $matches;
 140                  $sql[] = $alter . str_replace('DEFAULT '.$default,'',$v);
 141                  $sql[] = 'UPDATE '.$tabname.' SET '.$colname.'='.$default;
 142                  $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
 143              } else {                
 144                  $sql[] = $alter . $v;
 145              }
 146              if ($not_null) {
 147                  list($colname) = explode(' ',$v);
 148                  $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
 149              }
 150          }
 151          return $sql;
 152      }
 153      
 154      /**
 155       * Change the definition of one column
 156       *
 157       * Postgres can't do that on it's own, you need to supply the complete defintion of the new table,
 158       * to allow, recreating the table and copying the content over to the new table
 159       * @param string $tabname table-name
 160       * @param string $flds column-name and type for the changed column
 161       * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
 162       * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
 163       * @return array with SQL strings
 164       */
 165      /*function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 166      {
 167          if (!$tableflds) {
 168              if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
 169              return array();
 170          }
 171          return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
 172      }*/
 173      
 174  	function AlterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 175      {
 176         // Check if alter single column datatype available - works with 8.0+
 177         $has_alter_column = 8.0 <= (float) @$this->serverInfo['version'];
 178      
 179         if ($has_alter_column) {
 180            $tabname = $this->TableName($tabname);
 181            $sql = array();
 182            list($lines,$pkey) = $this->_GenFields($flds);
 183            $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
 184            foreach($lines as $v) {
 185               if ($not_null = preg_match('/NOT NULL/i',$v)) {
 186                  $v = preg_replace('/NOT NULL/i','',$v);
 187               }
 188               // this next block doesn't work - there is no way that I can see to 
 189               // explicitly ask a column to be null using $flds
 190               else if ($set_null = preg_match('/NULL/i',$v)) {
 191                  // if they didn't specify not null, see if they explicitely asked for null
 192                  $v = preg_replace('/\sNULL/i','',$v);
 193               }
 194               
 195               if (preg_match('/^([^ ]+) .*DEFAULT ([^ ]+)/',$v,$matches)) {
 196                  list(,$colname,$default) = $matches;
 197                  $v = preg_replace('/^' . preg_quote($colname) . '\s/', '', $v);
 198                  $sql[] = $alter . $colname . ' TYPE ' . str_replace('DEFAULT '.$default,'',$v);
 199                  $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET DEFAULT ' . $default;
 200               } 
 201               else {
 202                  // drop default?
 203                  preg_match ('/^\s*(\S+)\s+(.*)$/',$v,$matches);
 204                  list (,$colname,$rest) = $matches;
 205                  $sql[] = $alter . $colname . ' TYPE ' . $rest;
 206               }
 207      
 208               list($colname) = explode(' ',$v);
 209               if ($not_null) {
 210                  // this does not error out if the column is already not null
 211                  $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' SET NOT NULL';
 212               }
 213               if ($set_null) {
 214                  // this does not error out if the column is already null
 215                  $sql[] = 'ALTER TABLE '.$tabname.' ALTER COLUMN '.$colname.' DROP NOT NULL';
 216               }
 217            }
 218            return $sql;
 219         }
 220      
 221         // does not have alter column
 222         if (!$tableflds) {
 223            if ($this->debug) ADOConnection::outp("AlterColumnSQL needs a complete table-definiton for PostgreSQL");
 224            return array();
 225         }
 226         return $this->_recreate_copy_table($tabname,False,$tableflds,$tableoptions);
 227      }
 228      
 229      /**
 230       * Drop one column
 231       *
 232       * Postgres < 7.3 can't do that on it's own, you need to supply the complete defintion of the new table,
 233       * to allow, recreating the table and copying the content over to the new table
 234       * @param string $tabname table-name
 235       * @param string $flds column-name and type for the changed column
 236       * @param string $tableflds complete defintion of the new table, eg. for postgres, default ''
 237       * @param array/ $tableoptions options for the new table see CreateTableSQL, default ''
 238       * @return array with SQL strings
 239       */
 240  	function DropColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
 241      {
 242          $has_drop_column = 7.3 <= (float) @$this->serverInfo['version'];
 243          if (!$has_drop_column && !$tableflds) {
 244              if ($this->debug) ADOConnection::outp("DropColumnSQL needs complete table-definiton for PostgreSQL < 7.3");
 245          return array();
 246      }
 247          if ($has_drop_column) {
 248              return ADODB_DataDict::DropColumnSQL($tabname, $flds);
 249          }
 250          return $this->_recreate_copy_table($tabname,$flds,$tableflds,$tableoptions);
 251      }
 252      
 253      /**
 254       * Save the content into a temp. table, drop and recreate the original table and copy the content back in
 255       *
 256       * We also take care to set the values of the sequenz and recreate the indexes.
 257       * All this is done in a transaction, to not loose the content of the table, if something went wrong!
 258       * @internal
 259       * @param string $tabname table-name
 260       * @param string $dropflds column-names to drop
 261       * @param string $tableflds complete defintion of the new table, eg. for postgres
 262       * @param array/string $tableoptions options for the new table see CreateTableSQL, default ''
 263       * @return array with SQL strings
 264       */
 265  	function _recreate_copy_table($tabname,$dropflds,$tableflds,$tableoptions='')
 266      {
 267          if ($dropflds && !is_array($dropflds)) $dropflds = explode(',',$dropflds);
 268          $copyflds = array();
 269          foreach($this->MetaColumns($tabname) as $fld) {
 270              if (!$dropflds || !in_array($fld->name,$dropflds)) {
 271                  // we need to explicit convert varchar to a number to be able to do an AlterColumn of a char column to a nummeric one
 272                  if (preg_match('/'.$fld->name.' (I|I2|I4|I8|N|F)/i',$tableflds,$matches) && 
 273                      in_array($fld->type,array('varchar','char','text','bytea'))) {
 274                      $copyflds[] = "to_number($fld->name,'S9999999999999D99')";
 275                  } else {
 276                      $copyflds[] = $fld->name;
 277                  }
 278                  // identify the sequence name and the fld its on
 279                  if ($fld->primary_key && $fld->has_default && 
 280                      preg_match("/nextval\('([^']+)'::text\)/",$fld->default_value,$matches)) {
 281                      $seq_name = $matches[1];
 282                      $seq_fld = $fld->name;
 283                  }
 284              }
 285          }
 286          $copyflds = implode(', ',$copyflds);
 287          
 288          $tempname = $tabname.'_tmp';
 289          $aSql[] = 'BEGIN';        // we use a transaction, to make sure not to loose the content of the table
 290          $aSql[] = "SELECT * INTO TEMPORARY TABLE $tempname FROM $tabname";
 291          $aSql = array_merge($aSql,$this->DropTableSQL($tabname));
 292          $aSql = array_merge($aSql,$this->CreateTableSQL($tabname,$tableflds,$tableoptions));
 293          $aSql[] = "INSERT INTO $tabname SELECT $copyflds FROM $tempname";
 294          if ($seq_name && $seq_fld) {    // if we have a sequence we need to set it again
 295              $seq_name = $tabname.'_'.$seq_fld.'_seq';    // has to be the name of the new implicit sequence
 296              $aSql[] = "SELECT setval('$seq_name',MAX($seq_fld)) FROM $tabname";
 297          }
 298          $aSql[] = "DROP TABLE $tempname";
 299          // recreate the indexes, if they not contain one of the droped columns
 300          foreach($this->MetaIndexes($tabname) as $idx_name => $idx_data)
 301          {
 302              if (substr($idx_name,-5) != '_pkey' && (!$dropflds || !count(array_intersect($dropflds,$idx_data['columns'])))) {
 303                  $aSql = array_merge($aSql,$this->CreateIndexSQL($idx_name,$tabname,$idx_data['columns'],
 304                      $idx_data['unique'] ? array('UNIQUE') : False));
 305              }
 306          }
 307          $aSql[] = 'COMMIT';
 308          return $aSql;
 309      }
 310      
 311  	function DropTableSQL($tabname)
 312      {
 313          $sql = ADODB_DataDict::DropTableSQL($tabname);
 314          
 315          $drop_seq = $this->_DropAutoIncrement($tabname);
 316          if ($drop_seq) $sql[] = $drop_seq;
 317          
 318          return $sql;
 319      }
 320  
 321      // return string must begin with space
 322  	function _CreateSuffix($fname, &$ftype, $fnotnull,$fdefault,$fautoinc,$fconstraint)
 323      {
 324          if ($fautoinc) {
 325              $ftype = 'SERIAL';
 326              return '';
 327          }
 328          $suffix = '';
 329          if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
 330          if ($fnotnull) $suffix .= ' NOT NULL';
 331          if ($fconstraint) $suffix .= ' '.$fconstraint;
 332          return $suffix;
 333      }
 334      
 335      // search for a sequece for the given table (asumes the seqence-name contains the table-name!)
 336      // if yes return sql to drop it
 337      // this is still necessary if postgres < 7.3 or the SERIAL was created on an earlier version!!!
 338  	function _DropAutoIncrement($tabname)
 339      {
 340          $tabname = $this->connection->quote('%'.$tabname.'%');
 341  
 342          $seq = $this->connection->GetOne("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relname LIKE $tabname AND relkind='S'");
 343  
 344          // check if a tables depends on the sequenz and it therefor cant and dont need to be droped separatly
 345          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'")) {
 346              return False;
 347          }
 348          return "DROP SEQUENCE ".$seq;
 349      }
 350      
 351  	function RenameTableSQL($tabname,$newname)
 352      {
 353          if (!empty($this->schema)) {
 354              $rename_from = $this->TableName($tabname);
 355              $schema_save = $this->schema;
 356              $this->schema = false;
 357              $rename_to = $this->TableName($newname);
 358              $this->schema = $schema_save;
 359              return array (sprintf($this->renameTable, $rename_from, $rename_to));
 360          }
 361  
 362          return array (sprintf($this->renameTable, $this->TableName($tabname),$this->TableName($newname)));
 363      }
 364      
 365      /*
 366      CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name (
 367      { column_name data_type [ DEFAULT default_expr ] [ column_constraint [, ... ] ]
 368      | table_constraint } [, ... ]
 369      )
 370      [ INHERITS ( parent_table [, ... ] ) ]
 371      [ WITH OIDS | WITHOUT OIDS ]
 372      where column_constraint is:
 373      [ CONSTRAINT constraint_name ]
 374      { NOT NULL | NULL | UNIQUE | PRIMARY KEY |
 375      CHECK (expression) |
 376      REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ]
 377      [ ON DELETE action ] [ ON UPDATE action ] }
 378      [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
 379      and table_constraint is:
 380      [ CONSTRAINT constraint_name ]
 381      { UNIQUE ( column_name [, ... ] ) |
 382      PRIMARY KEY ( column_name [, ... ] ) |
 383      CHECK ( expression ) |
 384      FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
 385      [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] }
 386      [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
 387      */
 388      
 389      
 390      /*
 391      CREATE [ UNIQUE ] INDEX index_name ON table
 392  [ USING acc_method ] ( column [ ops_name ] [, ...] )
 393  [ WHERE predicate ]
 394  CREATE [ UNIQUE ] INDEX index_name ON table
 395  [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] )
 396  [ WHERE predicate ]
 397      */
 398  	function _IndexSQL($idxname, $tabname, $flds, $idxoptions)
 399      {
 400          $sql = array();
 401          
 402          if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
 403              $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
 404              if ( isset($idxoptions['DROP']) )
 405                  return $sql;
 406          }
 407          
 408          if ( empty ($flds) ) {
 409              return $sql;
 410          }
 411          
 412          $unique = isset($idxoptions['UNIQUE']) ? ' UNIQUE' : '';
 413          
 414          $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' ';
 415          
 416          if (isset($idxoptions['HASH']))
 417              $s .= 'USING HASH ';
 418          
 419          if ( isset($idxoptions[$this->upperName]) )
 420              $s .= $idxoptions[$this->upperName];
 421          
 422          if ( is_array($flds) )
 423              $flds = implode(', ',$flds);
 424          $s .= '(' . $flds . ')';
 425          $sql[] = $s;
 426          
 427          return $sql;
 428      }
 429      
 430  	function _GetSize($ftype, $ty, $fsize, $fprec)
 431      {
 432          if (strlen($fsize) && $ty != 'X' && $ty != 'B' && $ty  != 'I' && strpos($ftype,'(') === false) {
 433              $ftype .= "(".$fsize;
 434              if (strlen($fprec)) $ftype .= ",".$fprec;
 435              $ftype .= ')';
 436          }
 437          return $ftype;
 438      }
 439  }
 440  ?>