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

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