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 'DATE':
00059                         return 'T';
00060 
00061                 case 'INT':
00062                 case 'SMALLINT':
00063                 case 'INTEGER':
00064                         return 'I';
00065 
00066                 default:
00067                         return 'N';
00068                 }
00069         }
00070 
00071         function ActualType($meta)
00072         {
00073                 switch($meta) {
00074                 case 'C': return 'VARCHAR';
00075                 case 'X': return $this->typeX;
00076                 case 'XL': return $this->typeXL;
00077 
00078                 case 'C2': return 'NVARCHAR';
00079                 case 'X2': return 'NVARCHAR(2000)';
00080 
00081                 case 'B': return 'BLOB';
00082 
00083                 case 'D':
00084                 case 'T': return 'DATE';
00085                 case 'L': return 'DECIMAL(1)';
00086                 case 'I1': return 'DECIMAL(3)';
00087                 case 'I2': return 'DECIMAL(5)';
00088                 case 'I':
00089                 case 'I4': return 'DECIMAL(10)';
00090 
00091                 case 'I8': return 'DECIMAL(20)';
00092                 case 'F': return 'DECIMAL';
00093                 case 'N': return 'DECIMAL';
00094                 default:
00095                         return $meta;
00096                 }
00097         }
00098 
00099         function CreateDatabase($dbname, $options=false)
00100         {
00101                 $options = $this->_Options($options);
00102                 $password = isset($options['PASSWORD']) ? $options['PASSWORD'] : 'tiger';
00103                 $tablespace = isset($options["TABLESPACE"]) ? " DEFAULT TABLESPACE ".$options["TABLESPACE"] : '';
00104                 $sql[] = "CREATE USER ".$dbname." IDENTIFIED BY ".$password.$tablespace;
00105                 $sql[] = "GRANT CREATE SESSION, CREATE TABLE,UNLIMITED TABLESPACE,CREATE SEQUENCE TO $dbname";
00106 
00107                 return $sql;
00108         }
00109 
00110         function AddColumnSQL($tabname, $flds)
00111         {
00112                 $tabname = $this->TableName ($tabname);
00113                 $f = array();
00114                 list($lines,$pkey) = $this->_GenFields($flds);
00115                 $s = "ALTER TABLE $tabname ADD (";
00116                 foreach($lines as $v) {
00117                         $f[] = "\n $v";
00118                 }
00119 
00120                 $s .= implode(', ',$f).')';
00121                 $sql[] = $s;
00122                 return $sql;
00123         }
00124 
00125         function AlterColumnSQL($tabname, $flds)
00126         {
00127                 $tabname = $this->TableName ($tabname);
00128                 $f = array();
00129                 list($lines,$pkey) = $this->_GenFields($flds);
00130                 $s = "ALTER TABLE $tabname MODIFY(";
00131                 foreach($lines as $v) {
00132                         $f[] = "\n $v";
00133                 }
00134                 $s .= implode(', ',$f).')';
00135                 $sql[] = $s;
00136                 return $sql;
00137         }
00138 
00139         function DropColumnSQL($tabname, $flds)
00140         {
00141                 $tabname = $this->TableName ($tabname);
00142                 if (!is_array($flds)) $flds = explode(',',$flds);
00143                 foreach ($flds as $k => $v) $flds[$k] = $this->NameQuote($v);
00144 
00145                 $sql = array();
00146                 $s = "ALTER TABLE $tabname DROP(";
00147                 $s .= implode(', ',$flds).') CASCADE CONSTRAINTS';
00148                 $sql[] = $s;
00149                 return $sql;
00150         }
00151 
00152         function _DropAutoIncrement($t)
00153         {
00154                 if (strpos($t,'.') !== false) {
00155                         $tarr = explode('.',$t);
00156                         return "drop sequence ".$tarr[0].".seq_".$tarr[1];
00157                 }
00158                 return "drop sequence seq_".$t;
00159         }
00160 
00161         // return string must begin with space
00162         function _CreateSuffix($fname,$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned)
00163         {
00164                 $suffix = '';
00165 
00166                 if ($fdefault == "''" && $fnotnull) {// this is null in oracle
00167                         $fnotnull = false;
00168                         if ($this->debug) ADOConnection::outp("NOT NULL and DEFAULT='' illegal in Oracle");
00169                 }
00170 
00171                 if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault";
00172                 if ($fnotnull) $suffix .= ' NOT NULL';
00173 
00174                 if ($fautoinc) $this->seqField = $fname;
00175                 if ($fconstraint) $suffix .= ' '.$fconstraint;
00176 
00177                 return $suffix;
00178         }
00179 
00180 /*
00181 CREATE or replace TRIGGER jaddress_insert
00182 before insert on jaddress
00183 for each row
00184 begin
00185 select seqaddress.nextval into :new.A_ID from dual;
00186 end;
00187 */
00188         function _Triggers($tabname,$tableoptions)
00189         {
00190                 if (!$this->seqField) return array();
00191 
00192                 if ($this->schema) {
00193                         $t = strpos($tabname,'.');
00194                         if ($t !== false) $tab = substr($tabname,$t+1);
00195                         else $tab = $tabname;
00196                         $seqname = $this->schema.'.'.$this->seqPrefix.$tab;
00197                         $trigname = $this->schema.'.'.$this->trigPrefix.$this->seqPrefix.$tab;
00198                 } else {
00199                         $seqname = $this->seqPrefix.$tabname;
00200                         $trigname = $this->trigPrefix.$seqname;
00201                 }
00202                 if (isset($tableoptions['REPLACE'])) $sql[] = "DROP SEQUENCE $seqname";
00203                 $seqCache = '';
00204                 if (isset($tableoptions['SEQUENCE_CACHE'])){$seqCache = $tableoptions['SEQUENCE_CACHE'];}
00205                 $seqIncr = '';
00206                 if (isset($tableoptions['SEQUENCE_INCREMENT'])){$seqIncr = ' INCREMENT BY '.$tableoptions['SEQUENCE_INCREMENT'];}
00207                 $seqStart = '';
00208                 if (isset($tableoptions['SEQUENCE_START'])){$seqIncr = ' START WITH '.$tableoptions['SEQUENCE_START'];}
00209                 $sql[] = "CREATE SEQUENCE $seqname $seqStart $seqIncr $seqCache";
00210                 $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;";
00211 
00212                 $this->seqField = false;
00213                 return $sql;
00214         }
00215 
00216         /*
00217         CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
00218                 [table_options] [select_statement]
00219                 create_definition:
00220                 col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
00221                 [PRIMARY KEY] [reference_definition]
00222                 or PRIMARY KEY (index_col_name,...)
00223                 or KEY [index_name] (index_col_name,...)
00224                 or INDEX [index_name] (index_col_name,...)
00225                 or UNIQUE [INDEX] [index_name] (index_col_name,...)
00226                 or FULLTEXT [INDEX] [index_name] (index_col_name,...)
00227                 or [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
00228                 [reference_definition]
00229                 or CHECK (expr)
00230         */
00231 
00232 
00233 
00234         function _IndexSQL($idxname, $tabname, $flds,$idxoptions)
00235         {
00236                 $sql = array();
00237 
00238                 if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) {
00239                         $sql[] = sprintf ($this->dropIndex, $idxname, $tabname);
00240                         if ( isset($idxoptions['DROP']) )
00241                                 return $sql;
00242                 }
00243 
00244                 if ( empty ($flds) ) {
00245                         return $sql;
00246                 }
00247 
00248                 if (isset($idxoptions['BITMAP'])) {
00249                         $unique = ' BITMAP';
00250                 } elseif (isset($idxoptions['UNIQUE'])) {
00251                         $unique = ' UNIQUE';
00252                 } else {
00253                         $unique = '';
00254                 }
00255 
00256                 if ( is_array($flds) )
00257                         $flds = implode('", "',$flds);
00258                 $s = 'CREATE' . $unique . ' INDEX "' . $idxname . '" ON "' .$tabname . '" ("' . $flds . '")';
00259 
00260                 if ( isset($idxoptions[$this->upperName]) )
00261                         $s .= $idxoptions[$this->upperName];
00262 
00263                 if (isset($idxoptions['oci8']))
00264                         $s .= $idxoptions['oci8'];
00265 
00266 
00267                 $sql[] = $s;
00268 
00269                 return $sql;
00270         }
00271 
00272         function GetCommentSQL($table,$col)
00273         {
00274                 $table = $this->connection->qstr($table);
00275                 $col = $this->connection->qstr($col);
00276                 return "select comments from USER_COL_COMMENTS where TABLE_NAME=$table and COLUMN_NAME=$col";
00277         }
00278 
00279         function SetCommentSQL($table,$col,$cmt)
00280         {
00281                 $cmt = $this->connection->qstr($cmt);
00282                 return  "COMMENT ON COLUMN $table.$col IS $cmt";
00283         }
00284 }
00285 ?>
datadict-oci8.inc.php
