lib/adodb/datadict/datadict-db2.inc.php

   1 <?php
   2 /**
   3   @version   v5.21.0  2021-02-27
   4   @copyright (c) 2000-2013 John Lim (jlim#natsoft.com). All rights reserved.
   5   @copyright (c) 2014      Damien Regad, Mark Newnham and the ADOdb community
   6   Released under both BSD license and Lesser GPL library license.
   7   Whenever there is any discrepancy between the two licenses,
   8   the BSD license will take precedence.
   9
  10   Set tabs to 4 for best viewing.
  11
  12 */
  13 // security - hide paths
  14 if (!defined('ADODB_DIR')) die();
  15
  16 class ADODB2_db2 extends ADODB_DataDict {
  17
  18         var $databaseType = 'db2';
  19         var $seqField = false;
  20         var $dropCol = 'ALTER TABLE %s DROP COLUMN %s';
  21
  22         public $blobAllowsDefaultValue = true;
  23         public $blobAllowsNotNull      = true;
  24
  25
  26         function ActualType($meta)
  27         {
  28                 switch($meta) {
  29                 case 'C': return 'VARCHAR';
  30                 case 'XL': return 'CLOB';
  31                 case 'X': return 'VARCHAR(3600)';
  32
  33                 case 'C2': return 'VARCHAR'; // up to 32K
  34                 case 'X2': return 'VARCHAR(3600)'; // up to 32000, but default page size too small
  35
  36                 case 'B': return 'BLOB';
  37
  38                 case 'D': return 'DATE';
  39                 case 'TS':
  40                 case 'T': return 'TIMESTAMP';
  41
  42                 case 'L': return 'SMALLINT';
  43                 case 'I': return 'INTEGER';
  44                 case 'I1': return 'SMALLINT';
  45                 case 'I2': return 'SMALLINT';
  46                 case 'I4': return 'INTEGER';
  47                 case 'I8': return 'BIGINT';
  48
  49                 case 'F': return 'DOUBLE';
  50                 case 'N': return 'DECIMAL';
  51                 default:
  52                         return $meta;
  53                 }
  54         }
  55
  56         // return string must begin with space
  57         function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
  58         {
  59                 $suffix = '';
  60                 if ($fautoinc) return ' GENERATED ALWAYS AS IDENTITY'; # as identity start with
  61                 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
  62                 if ($fnotnull) $suffix .= ' NOT NULL';
  63                 if ($fconstraint) $suffix .= ' '.$fconstraint;
  64                 return $suffix;
  65         }
  66
  67         function alterColumnSQL($tabname, $flds, $tableflds='',$tableoptions='')
  68         {
  69                 $tabname = $this->TableName ($tabname);
  70                 $sql = array();
  71                 list($lines,$pkey,$idxs) = $this->_GenFields($flds);
  72                 // genfields can return FALSE at times
  73                 if ($lines == null) $lines = array();
  74                 $alter = 'ALTER TABLE ' . $tabname . $this->alterCol . ' ';
  75
  76                 $dataTypeWords = array('SET','DATA','TYPE');
  77
  78                 foreach($lines as $v)
  79                 {
  80                         /*
  81                          * We must now post-process the line to insert the 'SET DATA TYPE'
  82                          * text into the alter statement
  83                          */
  84                         $e = explode(' ',$v);
  85
  86                         array_splice($e,1,0,$dataTypeWords);
  87
  88                         $v = implode(' ',$e);
  89
  90                         $sql[] = $alter . $v;
  91                 }
  92                 if (is_array($idxs))
  93                 {
  94                         foreach($idxs as $idx => $idxdef) {
  95                                 $sql_idxs = $this->CreateIndexSql($idx, $tabname, $idxdef['cols'], $idxdef['opts']);
  96                                 $sql = array_merge($sql, $sql_idxs);
  97                         }
  98
  99                 }
 100                 return $sql;
 101         }
 102
 103
 104
 105         function dropColumnSql($tabname, $flds, $tableflds='',$tableoptions='')
 106         {
 107
 108
 109                 $tabname = $this->connection->getMetaCasedValue($tabname);
 110                 $flds    = $this->connection->getMetaCasedValue($flds);
 111
 112                 if (ADODB_ASSOC_CASE  == ADODB_ASSOC_CASE_NATIVE )
 113                 {
 114                         /*
 115                          * METACASE_NATIVE
 116                          */
 117                         $tabname = $this->connection->nameQuote . $tabname . $this->connection->nameQuote;
 118                         $flds    = $this->connection->nameQuote . $flds . $this->connection->nameQuote;
 119                 }
 120                 $sql = sprintf($this->dropCol,$tabname,$flds);
 121                 return (array)$sql;
 122
 123         }
 124
 125
 126         function changeTableSQL($tablename, $flds, $tableoptions = false, $dropOldFields=false)
 127         {
 128
 129                 /**
 130                   Allow basic table changes to DB2 databases
 131                   DB2 will fatally reject changes to non character columns
 132
 133                 */
 134
 135                 $validTypes = array("CHAR","VARC");
 136                 $invalidTypes = array("BIGI","BLOB","CLOB","DATE", "DECI","DOUB", "INTE", "REAL","SMAL", "TIME");
 137                 // check table exists
 138
 139
 140                 $cols = $this->metaColumns($tablename);
 141                 if ( empty($cols)) {
 142                         return $this->createTableSQL($tablename, $flds, $tableoptions);
 143                 }
 144
 145                 // already exists, alter table instead
 146                 list($lines,$pkey) = $this->_GenFields($flds);
 147                 $alter = 'ALTER TABLE ' . $this->tableName($tablename);
 148                 $sql = array();
 149
 150                 foreach ( $lines as $id => $v ) {
 151                         /*
 152                          * If the metaCasing was NATIVE the col returned with nameQuotes
 153                          * around the field. We need to remove this for the metaColumn
 154                          * match
 155                          */
 156                         $id = str_replace($this->connection->nameQuote,'',$id);
 157                         if ( isset($cols[$id]) && is_object($cols[$id]) ) {
 158                                 /**
 159                                   If the first field of $v is the fieldname, and
 160                                   the second is the field type/size, we assume its an
 161                                   attempt to modify the column size, so check that it is allowed
 162                                   $v can have an indeterminate number of blanks between the
 163                                   fields, so account for that too
 164                                  */
 165                                 $vargs = explode(' ' , $v);
 166                                 // assume that $vargs[0] is the field name.
 167                                 $i=0;
 168                                 // Find the next non-blank value;
 169                                 for ($i=1;$i<sizeof($vargs);$i++)
 170                                         if ($vargs[$i] != '')
 171                                                 break;
 172
 173                                 // if $vargs[$i] is one of the following, we are trying to change the
 174                                 // size of the field, if not allowed, simply ignore the request.
 175                                 if (in_array(substr($vargs[$i],0,4),$invalidTypes))
 176                                         continue;
 177                                 // insert the appropriate DB2 syntax
 178                                 if (in_array(substr($vargs[$i],0,4),$validTypes)) {
 179                                         array_splice($vargs,$i,0,array('SET','DATA','TYPE'));
 180                                 }
 181
 182                                 // Now Look for the NOT NULL statement as this is not allowed in
 183                                 // the ALTER table statement. If it is in there, remove it
 184                                 if (in_array('NOT',$vargs) && in_array('NULL',$vargs)) {
 185                                         for ($i=1;$i<sizeof($vargs);$i++)
 186                                         if ($vargs[$i] == 'NOT')
 187                                                 break;
 188                                         array_splice($vargs,$i,2,'');
 189                                 }
 190                                 $v = implode(' ',$vargs);
 191                                 $sql[] = $alter . $this->alterCol . ' ' . $v;
 192                         } else {
 193                                 $sql[] = $alter . $this->addCol . ' ' . $v;
 194                         }
 195                 }
 196
 197                 return $sql;
 198         }
 199
 200 }