[2] | 1 | <?php |
---|
| 2 | |
---|
| 3 | /** |
---|
[34] | 4 | V4.94 23 Jan 2007 (c) 2000-2007 John Lim (jlim#natsoft.com.my). All rights reserved. |
---|
[2] | 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_oci8 extends ADODB_DataDict { |
---|
| 17 | |
---|
| 18 | var $databaseType = 'oci8'; |
---|
| 19 | var $seqField = false; |
---|
| 20 | var $seqPrefix = 'SEQ_'; |
---|
| 21 | var $dropTable = "DROP TABLE %s CASCADE CONSTRAINTS"; |
---|
| 22 | var $trigPrefix = 'TRIG_'; |
---|
[34] | 23 | var $alterCol = ' MODIFY '; |
---|
| 24 | var $typeX = 'VARCHAR(4000)'; |
---|
| 25 | var $typeXL = 'CLOB'; |
---|
[2] | 26 | |
---|
| 27 | function MetaType($t,$len=-1) |
---|
| 28 | { |
---|
| 29 | if (is_object($t)) { |
---|
| 30 | $fieldobj = $t; |
---|
| 31 | $t = $fieldobj->type; |
---|
| 32 | $len = $fieldobj->max_length; |
---|
| 33 | } |
---|
| 34 | switch (strtoupper($t)) { |
---|
| 35 | case 'VARCHAR': |
---|
| 36 | case 'VARCHAR2': |
---|
| 37 | case 'CHAR': |
---|
| 38 | case 'VARBINARY': |
---|
| 39 | case 'BINARY': |
---|
| 40 | if (isset($this) && $len <= $this->blobSize) return 'C'; |
---|
| 41 | return 'X'; |
---|
| 42 | |
---|
| 43 | case 'NCHAR': |
---|
| 44 | case 'NVARCHAR2': |
---|
| 45 | case 'NVARCHAR': |
---|
| 46 | if (isset($this) && $len <= $this->blobSize) return 'C2'; |
---|
| 47 | return 'X2'; |
---|
| 48 | |
---|
| 49 | case 'NCLOB': |
---|
| 50 | case 'CLOB': |
---|
| 51 | return 'XL'; |
---|
| 52 | |
---|
| 53 | case 'LONG RAW': |
---|
| 54 | case 'LONG VARBINARY': |
---|
| 55 | case 'BLOB': |
---|
| 56 | return 'B'; |
---|
| 57 | |
---|
| 58 | case 'DATE': |
---|
| 59 | return 'T'; |
---|
| 60 | |
---|
| 61 | case 'INT': |
---|
| 62 | case 'SMALLINT': |
---|
| 63 | case 'INTEGER': |
---|
| 64 | return 'I'; |
---|
| 65 | |
---|
| 66 | default: |
---|
| 67 | return 'N'; |
---|
| 68 | } |
---|
| 69 | } |
---|
| 70 | |
---|
| 71 | function ActualType($meta) |
---|
| 72 | { |
---|
| 73 | switch($meta) { |
---|
| 74 | case 'C': return 'VARCHAR'; |
---|
[34] | 75 | case 'X': return $this->typeX; |
---|
| 76 | case 'XL': return $this->typeXL; |
---|
[2] | 77 | |
---|
[34] | 78 | case 'C2': return 'NVARCHAR2'; |
---|
| 79 | case 'X2': return 'NVARCHAR2(4000)'; |
---|
[2] | 80 | |
---|
| 81 | case 'B': return 'BLOB'; |
---|
| 82 | |
---|
| 83 | case 'D': |
---|
| 84 | case 'T': return 'DATE'; |
---|
| 85 | case 'L': return 'DECIMAL(1)'; |
---|
| 86 | case 'I1': return 'DECIMAL(3)'; |
---|
| 87 | case 'I2': return 'DECIMAL(5)'; |
---|
| 88 | case 'I': |
---|
| 89 | case 'I4': return 'DECIMAL(10)'; |
---|
| 90 | |
---|
| 91 | case 'I8': return 'DECIMAL(20)'; |
---|
| 92 | case 'F': return 'DECIMAL'; |
---|
| 93 | case 'N': return 'DECIMAL'; |
---|
| 94 | default: |
---|
| 95 | return $meta; |
---|
| 96 | } |
---|
| 97 | } |
---|
| 98 | |
---|
| 99 | function CreateDatabase($dbname, $options=false) |
---|
| 100 | { |
---|
| 101 | $options = $this->_Options($options); |
---|
| 102 | $password = isset($options['PASSWORD']) ? $options['PASSWORD'] : 'tiger'; |
---|
| 103 | $tablespace = isset($options["TABLESPACE"]) ? " DEFAULT TABLESPACE ".$options["TABLESPACE"] : ''; |
---|
| 104 | $sql[] = "CREATE USER ".$dbname." IDENTIFIED BY ".$password.$tablespace; |
---|
| 105 | $sql[] = "GRANT CREATE SESSION, CREATE TABLE,UNLIMITED TABLESPACE,CREATE SEQUENCE TO $dbname"; |
---|
| 106 | |
---|
| 107 | return $sql; |
---|
| 108 | } |
---|
| 109 | |
---|
| 110 | function AddColumnSQL($tabname, $flds) |
---|
| 111 | { |
---|
| 112 | $f = array(); |
---|
| 113 | list($lines,$pkey) = $this->_GenFields($flds); |
---|
| 114 | $s = "ALTER TABLE $tabname ADD ("; |
---|
| 115 | foreach($lines as $v) { |
---|
| 116 | $f[] = "\n $v"; |
---|
| 117 | } |
---|
| 118 | |
---|
[34] | 119 | $s .= implode(', ',$f).')'; |
---|
[2] | 120 | $sql[] = $s; |
---|
| 121 | return $sql; |
---|
| 122 | } |
---|
| 123 | |
---|
| 124 | function AlterColumnSQL($tabname, $flds) |
---|
| 125 | { |
---|
| 126 | $f = array(); |
---|
| 127 | list($lines,$pkey) = $this->_GenFields($flds); |
---|
| 128 | $s = "ALTER TABLE $tabname MODIFY("; |
---|
| 129 | foreach($lines as $v) { |
---|
| 130 | $f[] = "\n $v"; |
---|
| 131 | } |
---|
[34] | 132 | $s .= implode(', ',$f).')'; |
---|
[2] | 133 | $sql[] = $s; |
---|
| 134 | return $sql; |
---|
| 135 | } |
---|
| 136 | |
---|
| 137 | function DropColumnSQL($tabname, $flds) |
---|
| 138 | { |
---|
| 139 | if (!is_array($flds)) $flds = explode(',',$flds); |
---|
[34] | 140 | foreach ($flds as $k => $v) $flds[$k] = $this->NameQuote($v); |
---|
| 141 | |
---|
[2] | 142 | $sql = array(); |
---|
| 143 | $s = "ALTER TABLE $tabname DROP("; |
---|
[34] | 144 | $s .= implode(', ',$flds).') CASCADE CONSTRAINTS'; |
---|
[2] | 145 | $sql[] = $s; |
---|
| 146 | return $sql; |
---|
| 147 | } |
---|
| 148 | |
---|
| 149 | function _DropAutoIncrement($t) |
---|
| 150 | { |
---|
| 151 | if (strpos($t,'.') !== false) { |
---|
| 152 | $tarr = explode('.',$t); |
---|
| 153 | return "drop sequence ".$tarr[0].".seq_".$tarr[1]; |
---|
| 154 | } |
---|
| 155 | return "drop sequence seq_".$t; |
---|
| 156 | } |
---|
| 157 | |
---|
| 158 | // return string must begin with space |
---|
| 159 | function _CreateSuffix($fname,$ftype,$fnotnull,$fdefault,$fautoinc,$fconstraint,$funsigned) |
---|
| 160 | { |
---|
| 161 | $suffix = ''; |
---|
| 162 | |
---|
| 163 | if ($fdefault == "''" && $fnotnull) {// this is null in oracle |
---|
| 164 | $fnotnull = false; |
---|
| 165 | if ($this->debug) ADOConnection::outp("NOT NULL and DEFAULT='' illegal in Oracle"); |
---|
| 166 | } |
---|
| 167 | |
---|
| 168 | if (strlen($fdefault)) $suffix .= " DEFAULT $fdefault"; |
---|
| 169 | if ($fnotnull) $suffix .= ' NOT NULL'; |
---|
| 170 | |
---|
| 171 | if ($fautoinc) $this->seqField = $fname; |
---|
| 172 | if ($fconstraint) $suffix .= ' '.$fconstraint; |
---|
| 173 | |
---|
| 174 | return $suffix; |
---|
| 175 | } |
---|
| 176 | |
---|
| 177 | /* |
---|
| 178 | CREATE or replace TRIGGER jaddress_insert |
---|
| 179 | before insert on jaddress |
---|
| 180 | for each row |
---|
| 181 | begin |
---|
| 182 | select seqaddress.nextval into :new.A_ID from dual; |
---|
| 183 | end; |
---|
| 184 | */ |
---|
| 185 | function _Triggers($tabname,$tableoptions) |
---|
| 186 | { |
---|
| 187 | if (!$this->seqField) return array(); |
---|
| 188 | |
---|
| 189 | if ($this->schema) { |
---|
| 190 | $t = strpos($tabname,'.'); |
---|
| 191 | if ($t !== false) $tab = substr($tabname,$t+1); |
---|
| 192 | else $tab = $tabname; |
---|
| 193 | $seqname = $this->schema.'.'.$this->seqPrefix.$tab; |
---|
| 194 | $trigname = $this->schema.'.'.$this->trigPrefix.$this->seqPrefix.$tab; |
---|
| 195 | } else { |
---|
| 196 | $seqname = $this->seqPrefix.$tabname; |
---|
| 197 | $trigname = $this->trigPrefix.$seqname; |
---|
| 198 | } |
---|
[34] | 199 | |
---|
| 200 | if (strlen($seqname) > 30) { |
---|
| 201 | $seqname = $this->seqPrefix.uniqid(''); |
---|
| 202 | } // end if |
---|
| 203 | if (strlen($trigname) > 30) { |
---|
| 204 | $trigname = $this->trigPrefix.uniqid(''); |
---|
| 205 | } // end if |
---|
| 206 | |
---|
[2] | 207 | if (isset($tableoptions['REPLACE'])) $sql[] = "DROP SEQUENCE $seqname"; |
---|
| 208 | $seqCache = ''; |
---|
| 209 | if (isset($tableoptions['SEQUENCE_CACHE'])){$seqCache = $tableoptions['SEQUENCE_CACHE'];} |
---|
| 210 | $seqIncr = ''; |
---|
| 211 | if (isset($tableoptions['SEQUENCE_INCREMENT'])){$seqIncr = ' INCREMENT BY '.$tableoptions['SEQUENCE_INCREMENT'];} |
---|
| 212 | $seqStart = ''; |
---|
| 213 | if (isset($tableoptions['SEQUENCE_START'])){$seqIncr = ' START WITH '.$tableoptions['SEQUENCE_START'];} |
---|
| 214 | $sql[] = "CREATE SEQUENCE $seqname $seqStart $seqIncr $seqCache"; |
---|
| 215 | $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;"; |
---|
| 216 | |
---|
| 217 | $this->seqField = false; |
---|
| 218 | return $sql; |
---|
| 219 | } |
---|
| 220 | |
---|
| 221 | /* |
---|
| 222 | CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] |
---|
| 223 | [table_options] [select_statement] |
---|
| 224 | create_definition: |
---|
| 225 | col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] |
---|
| 226 | [PRIMARY KEY] [reference_definition] |
---|
| 227 | or PRIMARY KEY (index_col_name,...) |
---|
| 228 | or KEY [index_name] (index_col_name,...) |
---|
| 229 | or INDEX [index_name] (index_col_name,...) |
---|
| 230 | or UNIQUE [INDEX] [index_name] (index_col_name,...) |
---|
| 231 | or FULLTEXT [INDEX] [index_name] (index_col_name,...) |
---|
| 232 | or [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...) |
---|
| 233 | [reference_definition] |
---|
| 234 | or CHECK (expr) |
---|
| 235 | */ |
---|
| 236 | |
---|
| 237 | |
---|
| 238 | |
---|
| 239 | function _IndexSQL($idxname, $tabname, $flds,$idxoptions) |
---|
| 240 | { |
---|
| 241 | $sql = array(); |
---|
| 242 | |
---|
| 243 | if ( isset($idxoptions['REPLACE']) || isset($idxoptions['DROP']) ) { |
---|
| 244 | $sql[] = sprintf ($this->dropIndex, $idxname, $tabname); |
---|
| 245 | if ( isset($idxoptions['DROP']) ) |
---|
| 246 | return $sql; |
---|
| 247 | } |
---|
| 248 | |
---|
| 249 | if ( empty ($flds) ) { |
---|
| 250 | return $sql; |
---|
| 251 | } |
---|
| 252 | |
---|
| 253 | if (isset($idxoptions['BITMAP'])) { |
---|
| 254 | $unique = ' BITMAP'; |
---|
| 255 | } elseif (isset($idxoptions['UNIQUE'])) { |
---|
| 256 | $unique = ' UNIQUE'; |
---|
| 257 | } else { |
---|
| 258 | $unique = ''; |
---|
| 259 | } |
---|
| 260 | |
---|
| 261 | if ( is_array($flds) ) |
---|
| 262 | $flds = implode(', ',$flds); |
---|
| 263 | $s = 'CREATE' . $unique . ' INDEX ' . $idxname . ' ON ' . $tabname . ' (' . $flds . ')'; |
---|
| 264 | |
---|
| 265 | if ( isset($idxoptions[$this->upperName]) ) |
---|
| 266 | $s .= $idxoptions[$this->upperName]; |
---|
| 267 | |
---|
| 268 | if (isset($idxoptions['oci8'])) |
---|
| 269 | $s .= $idxoptions['oci8']; |
---|
| 270 | |
---|
| 271 | |
---|
| 272 | $sql[] = $s; |
---|
| 273 | |
---|
| 274 | return $sql; |
---|
| 275 | } |
---|
| 276 | |
---|
| 277 | function GetCommentSQL($table,$col) |
---|
| 278 | { |
---|
| 279 | $table = $this->connection->qstr($table); |
---|
| 280 | $col = $this->connection->qstr($col); |
---|
| 281 | return "select comments from USER_COL_COMMENTS where TABLE_NAME=$table and COLUMN_NAME=$col"; |
---|
| 282 | } |
---|
| 283 | |
---|
| 284 | function SetCommentSQL($table,$col,$cmt) |
---|
| 285 | { |
---|
| 286 | $cmt = $this->connection->qstr($cmt); |
---|
| 287 | return "COMMENT ON COLUMN $table.$col IS $cmt"; |
---|
| 288 | } |
---|
| 289 | } |
---|
| 290 | ?> |
---|