Adodb Dokumentation  V5.14 8 Sept 2011
datadict/datadict-oci8.inc.php
00001 <?php
00002 
00013 // security - hide paths
00014 if (!defined('ADODB_DIR')) die();
00015 
00016 class ADODB2_oci8 extends ADODB_DataDict {
00017         
00018         var $databaseType = 'oci8';
00019         var $seqField = false;
00020         var $seqPrefix = 'SEQ_';
00021         var $dropTable = "DROP TABLE %s CASCADE CONSTRAINTS";
00022         var $trigPrefix = 'TRIG_';
00023         var $alterCol = ' MODIFY ';
00024         var $typeX = 'VARCHAR(4000)';
00025         var $typeXL = 'CLOB';
00026         
00027         function MetaType($t,$len=-1)
00028         {
00029                 if (is_object($t)) {
00030                         $fieldobj = $t;
00031                         $t = $fieldobj->type;
00032                         $len = $fieldobj->max_length;
00033                 }
00034                 switch (strtoupper($t)) {
00035                 case 'VARCHAR':
00036                 case 'VARCHAR2':
00037                 case 'CHAR':
00038                 case 'VARBINARY':
00039                 case 'BINARY':
00040                         if (isset($this) && $len <= $this->blobSize) return 'C';
00041                         return 'X';
00042                 
00043                 case 'NCHAR':
00044                 case 'NVARCHAR2':
00045                 case 'NVARCHAR':
00046                         if (isset($this) && $len <= $this->blobSize) return 'C2';
00047                         return 'X2';
00048                         
00049                 case 'NCLOB':
00050                 case 'CLOB':
00051                         return 'XL';
00052                 
00053                 case 'LONG RAW':
00054                 case 'LONG VARBINARY':
00055                 case 'BLOB':
00056                         return 'B';
00057                 
00058                 case 'TIMESTAMP':
00059                         return 'TS';
00060                         
00061                 case 'DATE': 
00062                         return 'T';
00063                 
00064                 case 'INT': 
00065                 case 'SMALLINT':
00066                 case 'INTEGER': 
00067                         return 'I';
00068                         
00069                 default:
00070                         return 'N';
00071                 }
00072         }
00073         
00074         function ActualType($meta)
00075         {
00076                 switch($meta) {
00077                 case 'C': return 'VARCHAR';
00078                 case 'X': return $this->typeX;
00079                 case 'XL': return $this->typeXL;
00080                 
00081                 case 'C2': return 'NVARCHAR2';
00082                 case 'X2': return 'NVARCHAR2(4000)';
00083                 
00084                 case 'B': return 'BLOB';
00085                 
00086                 case 'TS':
00087                                 return 'TIMESTAMP';
00088                                 
00089                 case 'D': 
00090                 case 'T': return 'DATE';
00091                 case 'L': return 'NUMBER(1)';
00092                 case 'I1': return 'NUMBER(3)';
00093                 case 'I2': return 'NUMBER(5)';
00094                 case 'I':
00095                 case 'I4': return 'NUMBER(10)';
00096                 
00097                 case 'I8': return 'NUMBER(20)';
00098                 case 'F': return 'NUMBER';
00099                 case 'N': return 'NUMBER';
00100                 case 'R': return 'NUMBER(20)';
00101                 default:
00102                         return $meta;
00103                 }       
00104         }
00105         
00106         function CreateDatabase($dbname, $options=false)
00107         {
00108                 $options = $this->_Options($options);
00109                 $password = isset($options['PASSWORD']) ? $options['PASSWORD'] : 'tiger';
00110                 $tablespace = isset($options["TABLESPACE"]) ? " DEFAULT TABLESPACE ".$options["TABLESPACE"] : '';
00111                 $sql[] = "CREATE USER ".$dbname." IDENTIFIED BY ".$password.$tablespace;
00112                 $sql[] = "GRANT CREATE SESSION, CREATE TABLE,UNLIMITED TABLESPACE,CREATE SEQUENCE TO $dbname";
00113                 
00114                 return $sql;
00115         }
00116         
00117         function AddColumnSQL($tabname, $flds)
00118         {
00119                 $f = array();
00120                 list($lines,$pkey) = $this->_GenFields($flds);
00121                 $s = "ALTER TABLE $tabname ADD (";
00122                 foreach($lines as $v) {
00123                         $f[] = "\n $v";
00124                 }
00125                 
00126                 $s .= implode(', ',$f).')';
00127                 $sql[] = $s;
00128                 return $sql;
00129         }
00130         
00131         function AlterColumnSQL($tabname, $flds)
00132         {
00133                 $f = array();
00134                 list($lines,$pkey) = $this->_GenFields($flds);
00135                 $s = "ALTER TABLE $tabname MODIFY(";
00136                 foreach($lines as $v) {
00137                         $f[] = "\n $v";
00138                 }
00139                 $s .= implode(', ',$f).')';
00140                 $sql[] = $s;
00141                 return $sql;
00142         }
00143         
00144         function DropColumnSQL($tabname, $flds)
00145         {
00146                 if (!is_array($flds)) $flds = explode(',',$flds);
00147                 foreach ($flds as $k => $v) $flds[$k] = $this->NameQuote($v);
00148                 
00149                 $sql = array();
00150                 $s = "ALTER TABLE $tabname DROP(";
00151                 $s .= implode(', ',$flds).') CASCADE CONSTRAINTS';
00152                 $sql[] = $s;
00153                 return $sql;
00154         }
00155         
00156         function _DropAutoIncrement($t)
00157         {
00158                 if (strpos($t,'.') !== false) {
00159                         $tarr = explode('.',$t);
00160                         return "drop sequence ".$tarr[0].".seq_".$tarr[1];
00161                 }
00162                 return "drop sequence seq_".$t;
00163         }
00164         
00165         // return string must begin with space
00166         function _CreateSuffix($fname,&$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
00167         {
00168                 $suffix = '';
00169                 
00170                 if ($fdefault == "''" && $fnotnull) {// this is null in oracle
00171                         $fnotnull = false;
00172                         if ($this->debug) ADOConnection::outp("NOT NULL and DEFAULT='' illegal in Oracle");
00173                 }
00174                 
00175                 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
00176                 if ($fnotnull) $suffix .= ' NOT NULL';
00177                 
00178                 if ($fautoinc) $this->seqField = $fname;
00179                 if ($fconstraint) $suffix .= ' '.$fconstraint;
00180                 
00181                 return $suffix;
00182         }
00183         
00184 /*
00185 CREATE or replace TRIGGER jaddress_insert
00186 before insert on jaddress
00187 for each row
00188 begin
00189 select seqaddress.nextval into :new.A_ID from dual;
00190 end;
00191 */
00192         function _Triggers($tabname,$tableoptions)
00193         {
00194                 if (!$this->seqField) return array();
00195                 
00196                 if ($this->schema) {
00197                         $t = strpos($tabname,'.');
00198                         if ($t !== false) $tab = substr($tabname,$t+1);
00199                         else $tab = $tabname;
00200                         $seqname = $this->schema.'.'.$this->seqPrefix.$tab;
00201                         $trigname = $this->schema.'.'.$this->trigPrefix.$this->seqPrefix.$tab;
00202                 } else {
00203                         $seqname = $this->seqPrefix.$tabname;
00204                         $trigname = $this->trigPrefix.$seqname;
00205                 }
00206                 
00207                 if (strlen($seqname) > 30) {
00208                         $seqname = $this->seqPrefix.uniqid('');
00209                 } // end if
00210                 if (strlen($trigname) > 30) {
00211                         $trigname = $this->trigPrefix.uniqid('');
00212                 } // end if
00213 
00214                 if (isset($tableoptions['REPLACE'])) $sql[] = "DROP SEQUENCE $seqname";
00215                 $seqCache = '';
00216                 if (isset($tableoptions['SEQUENCE_CACHE'])){$seqCache = $tableoptions['SEQUENCE_CACHE'];}
00217                 $seqIncr = '';
00218                 if (isset($tableoptions['SEQUENCE_INCREMENT'])){$seqIncr = ' INCREMENT BY '.$tableoptions['SEQUENCE_INCREMENT'];}
00219                 $seqStart = '';
00220                 if (isset($tableoptions['SEQUENCE_START'])){$seqIncr = ' START WITH '.$tableoptions['SEQUENCE_START'];}
00221                 $sql[] = "CREATE SEQUENCE $seqname $seqStart $seqIncr $seqCache";
00222                 $sql[] = "CREATE OR REPLACE TRIGGER $trigname BEFORE insert ON $tabname FOR EACH ROW WHEN (NEW.$this->seqField IS NULL OR NEW.$this->seqField = 0) BEGIN select $seqname.nextval into :new.$this->seqField from dual; END;";
00223                 
00224                 $this->seqField = false;
00225                 return $sql;
00226         }
00227         
00228         /*
00229         CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
00230                 [table_options] [select_statement]
00231                 create_definition:
00232                 col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
00233                 [PRIMARY KEY] [reference_definition]
00234                 or PRIMARY KEY (index_col_name,...)
00235                 or KEY [index_name] (index_col_name,...)
00236                 or INDEX [index_name] (index_col_name,...)
00237                 or UNIQUE [INDEX] [index_name] (index_col_name,...)
00238                 or FULLTEXT [INDEX] [index_name] (index_col_name,...)
00239                 or [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
00240                 [reference_definition]
00241                 or CHECK (expr)
00242         */
00243         
00244 
00245         
00246         function _IndexSQL($idxname, $tabname, $flds,$idxoptions)
00247         {
00248                 $sql = array();
00249                 
00250                 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
00251                         $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
00252                         if ( isset($idxoptions['DROP']) )
00253                                 return $sql;
00254                 }
00255                 
00256                 if ( empty ($flds) ) {
00257                         return $sql;
00258                 }
00259                 
00260                 if (isset($idxoptions['BITMAP'])) {
00261                         $unique = ' BITMAP'; 
00262                 } elseif (isset($idxoptions['UNIQUE'])) {
00263                         $unique = ' UNIQUE';
00264                 } else {
00265                         $unique = '';
00266                 }
00267                 
00268                 if ( is_array($flds) )
00269                         $flds = implode(', ',$flds);
00270                 $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' (' . $flds . ')';
00271                 
00272                 if ( isset($idxoptions[$this->upperName]) )
00273                         $s .= $idxoptions[$this->upperName];
00274                 
00275                 if (isset($idxoptions['oci8']))
00276                         $s .= $idxoptions['oci8'];
00277                 
00278 
00279                 $sql[] = $s;
00280                 
00281                 return $sql;
00282         }
00283         
00284         function GetCommentSQL($table,$col)
00285         {
00286                 $table = $this->connection->qstr($table);
00287                 $col = $this->connection->qstr($col);   
00288                 return "select comments from USER_COL_COMMENTS where TABLE_NAME=$table and COLUMN_NAME=$col";
00289         }
00290         
00291         function SetCommentSQL($table,$col,$cmt)
00292         {
00293                 $cmt = $this->connection->qstr($cmt);
00294                 return  "COMMENT ON COLUMN $table.$col IS $cmt";
00295         }
00296 }
00297 ?>