ILIAS  release_5-1 Revision 5.0.0-5477-g43f3e3fab5f
class.ilDBOracle.php
Go to the documentation of this file.
1<?php
2/* Copyright (c) 1998-2009 ILIAS open source, Extended GPL, see docs/LICENSE */
3
4
5include_once ("./Services/Database/classes/class.ilDB.php");
6
18class ilDBOracle extends ilDB
19{
20 const CLOB_BUFFER_SIZE = 2000;
22
23
27 function getDSN()
28 {
29 // TODO: check if there is another solution.
30 // This works with 11g
31 if(!isset($GLOBALS['_MDB2_dsninfo_default']['charset']) or
32 $GLOBALS['_MDB2_dsninfo_default']['charset'] != 'utf8')
33 {
34 $GLOBALS['_MDB2_dsninfo_default']['charset'] = 'utf8';
35 }
36
37 return array('phptype' => 'oci8',
38 'hostspec' => $this->getDBHost(),
39 'username' => $this->getDBUser(),
40 'password' => $this->getDBPassword(),
41 'port' => $this->getDBPort(),
42 'service' => $this->getDBName()
43 );
44
45 //return "oci8://".$this->getDBUser().":".$this->getDBPassword()."@".
46 // $this->getDBHost()."/?service=".$this->getDBName();
47 }
48
52 function getDBType()
53 {
54 return "oracle";
55 }
56
57 public function getDBVersion()
58 {
59 $query = 'SELECT * FROM v$version';
60 $res = $this->db->query($query);
61
63 {
64 return parent::getDBVersion();
65 }
66 $row = $res->fetchRow(DB_FETCHMODE_ASSOC);
67 return isset($row['banner']) ? $row['banner'] : parent::getDBVersion();
68 }
69
73 static function getReservedWords()
74 {
75 // version: 10g
76 // url: http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/ap_keywd.htm#g691972
77 return array(
78 "ACCESS", "ADD", "ALL", "ALTER", "AND", "ANY", "AS", "ASC",
79 "AUDIT", "BETWEEN", "BY", "CHAR", "CHECK", "CLUSTER", "COLUMN",
80 "COMMENT", "COMPRESS", "CONNECT", "CREATE", "CURRENT", "DATE",
81 "DECIMAL", "DEFAULT", "DELETE", "DESC", "DISTINCT", "DROP", "ELSE",
82 "EXCLUSIVE", "EXISTS", "FILE", "FLOAT", "FOR", "FROM", "GRANT", "GROUP",
83 "HAVING", "IDENTIFIED", "IMMEDIATE", "IN", "INCREMENT", "INDEX", "INITIAL",
84 "INSERT", "INTEGER", "INTERSECT", "INTO", "IS", "LEVEL", "LIKE", "LOCK", "LONG",
85 "MAXEXTENTS", "MINUS", "MLSLABEL", "MODE", "MODIFY", "NOAUDIT", "NOCOMPRESS", "NOT",
86 "NOWAIT", "NULL", "NUMBER", "OF", "OFFLINE", "ON", "ONLINE","OPTION",
87 "OR", "ORDER", "PCTFREE", "PRIOR", "PRIVILEGES", "PUBLIC", "RAW", "RENAME",
88 "RESOURCE", "REVOKE", "ROW", "ROWID", "ROWNUM", "ROWS", "SELECT", "SESSION", "SET",
89 "SHARE", "SIZE", "SMALLINT", "START", "SUCCESSFUL", "SYNONYM", "SYSDATE","TABLE",
90 "THEN", "TO", "TRIGGER", "UID", "UNION", "UNIQUE", "UPDATE", "USER","VALIDATE",
91 "VALUES", "VARCHAR", "VARCHAR2", "VIEW", "WHENEVER", "WHERE", "WITH"
92 );
93 }
94
98 function doConnect()
99 {
100 $this->db = MDB2::connect($this->getDSN(),
101 array("use_transactions" => true));
102 }
103
107 function initConnection()
108 {
109 $GLOBALS['_MDB2_dsninfo_default']['charset'] = 'utf8';
110 $this->query("ALTER SESSION SET nls_length_semantics='CHAR'");
111 $this->query("ALTER SESSION SET NLS_SORT = binary_ci");
112 }
113
114/* function manipulate($sql)
115 {
116//echo "1";
117//if (!is_int(strpos($sql, "frm_thread_access")))
118//{
119//echo "2";
120 return parent::manipulate($sql);
121//}
122//echo "3";
123 }*/
124
129 function now()
130 {
131 return "LOCALTIMESTAMP";
132 }
133
137 function fromUnixtime($a_expr, $a_to_text = true)
138 {
139 if (!$a_to_text)
140 {
141 return "TO_DATE('19700101000000','YYYYMMDDHH24MISS') ".
142 "+ NUMTODSINTERVAL(".$a_expr.", 'SECOND')";
143 }
144 else
145 {
146 return "to_char(TO_DATE('19700101000000','YYYYMMDDHH24MISS') ".
147 "+ NUMTODSINTERVAL(".$a_expr.", 'SECOND'), 'yyyy-mm-dd hh24:mi:ss')";
148 }
149 }
150
154 function unixTimestamp()
155 {
156 // sysdate should respect database time zone
157 // current_date would respect session time zone
158 return "ROUND((sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400))";
159 }
160
164 function constraintName($a_table, $a_constraint)
165 {
166 return $a_table."_".$a_constraint;
167 }
168
173 {
174 return "pk";
175 }
176
181 {
182 return false;
183 }
184
192 function replace($a_table, $a_pk_columns, $a_other_columns)
193 {
194 $a_columns = array_merge($a_pk_columns, $a_other_columns);
195 $fields = array();
196 $field_values = array();
197 $placeholders = array();
198 $types = array();
199 $values = array();
200 $lobs = false;
201 $lob = array();
202 $val_field = array();
203 $a = array();
204 $b = array();
205 foreach ($a_columns as $k => $col)
206 {
207 if($col[0] == 'clob' or $col[0] == 'blob')
208 {
209 $val_field[] = $this->quote($col[1], 'text')." ".$k;
210 }
211 else
212 {
213 $val_field[] = $this->quote($col[1], $col[0])." ".$k;
214 }
215 $fields[] = $k;
216 $placeholders[] = "%s";
217 $placeholders2[] = ":$k";
218 $types[] = $col[0];
219
220 // integer auto-typecast (this casts bool values to integer)
221 if ($col[0] == 'integer' && !is_null($col[1]))
222 {
223 $col[1] = (int) $col[1];
224 }
225
226 $values[] = $col[1];
227 $field_values[$k] = $col[1];
228 if ($col[0] == "blob" || $col[0] == "clob")
229 {
230 $lobs = true;
231 $lob[$k] = $k;
232 }
233 $a[] = "a.".$k;
234 $b[] = "b.".$k;
235 }
236 $abpk = array();
237 $aboc = array();
238 $delwhere = array();
239 foreach ($a_pk_columns as $k => $col)
240 {
241 $abpk[] = "a.".$k." = b.".$k;
242 $delwhere[] = $k." = ".$this->quote($col[1], $col[0]);
243 }
244 foreach ($a_other_columns as $k => $col)
245 {
246 $aboc[] = "a.".$k." = b.".$k;
247 }
248 if ($lobs) // delete/insert
249 {
250 $this->manipulate("DELETE FROM ".$a_table." WHERE ".
251 implode ($delwhere, " AND ")
252 );
253 $this->insert($a_table, $a_columns);
254
255 //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
256 $this->handleError($r, "replace, delete/insert(".$a_table.")");
257 }
258 else // if no lobs are used, use manipulate
259 {
260 $q = "MERGE INTO ".$a_table." a ".
261 "USING (SELECT ".implode($val_field, ", ")." ".
262 "FROM DUAL) b ON (".implode($abpk, " AND ").") ".
263 "WHEN MATCHED THEN UPDATE SET ".implode($aboc, ", ")." ".
264 "WHEN NOT MATCHED THEN INSERT (".implode($a, ",").") VALUES (".implode($b, ",").")";
265 $r = $this->manipulate($q);
266 }
267 return $r;
268 }
269
277 public function locate($a_needle,$a_string,$a_start_pos = 1)
278 {
279 $locate = ' INSTR( ';
280 $locate .= ('SUBSTR('.$a_string.',0,'.self::CLOB_BUFFER_SIZE.')');
281 $locate .= ',';
282 $locate .= $a_needle;
283 $locate .= ',';
284 $locate .= $a_start_pos;
285 $locate .= ') ';
286 return $locate;
287 }
288
300 public function like($a_col, $a_type, $a_value = "?", $case_insensitive = true)
301 {
302 if($a_type == 'text')
303 {
304 return parent::like($a_col,$a_type,$a_value,$case_insensitive);
305 }
306
307 if (!in_array($a_type, array("text", "clob", "blob")))
308 {
309 $this->raisePearError("Like: Invalid column type '".$a_type."'.", $this->error_class->FATAL);
310 }
311 if ($a_value == "?")
312 {
313 if ($case_insensitive)
314 {
315 return "UPPER(SUBSTR(".$a_col.",0,".self::CLOB_BUFFER_SIZE.")) LIKE(UPPER(?))";
316 }
317 else
318 {
319 return "SUBSTR(".$a_col .",0,".self::CLOB_BUFFER_SIZE.") LIKE(?)";
320 }
321 }
322 else
323 {
324 if ($case_insensitive)
325 {
326 return " UPPER(SUBSTR(".$a_col.",0,".self::CLOB_BUFFER_SIZE.")) LIKE(UPPER(".$this->quote($a_value, 'text')."))";
327 }
328 else
329 {
330 return " SUBSTR(".$a_col.",0,".self::CLOB_BUFFER_SIZE.") LIKE(".$this->quote($a_value, 'text').")";
331 }
332 }
333 }
334
341 public function concat($a_values, $a_allow_null = true)
342 {
343 if(count($a_values) <= 2)
344 {
345 return parent::concat($a_values,false);
346 }
347
348 $first = true;
349 foreach($a_values as $field_info)
350 {
351 if(!$first)
352 {
353 $concat_value = parent::concat(
354 array(
355 array($concat_value,$concat_type),
356 array($field_info[0],$field_info[1])),
357 false
358 );
359 }
360 else
361 {
362 $first = false;
363 $concat_value = $field_info[0];
364 $concat_type = $field_info[1];
365 }
366 }
367 return $concat_value;
368 }
369
384 function in($a_field, $a_values, $negate = false, $a_type = "")
385 {
386 if(count($a_values) <= self::LIMIT_EXPRESSIONS_IN_LIST)
387 {
388 return parent::in($a_field,$a_values,$negate,$a_type);
389 }
390
391 $first = true;
392 $concat = $negate ? ' AND ' : ' OR ';
393 $in = '(';
394 do
395 {
396 if(!$first)
397 {
398 $in .= $concat;
399
400 }
401 $first = false;
402
403 $spliced = array_splice($a_values, 0, self::LIMIT_EXPRESSIONS_IN_LIST);
404 $in .= parent::in($a_field, $spliced, $negate, $a_type);
405
406 }
407 while($a_values);
408
409 return $in." ) ";
410 }
411
412
422 public function lockTables($a_tables)
423 {
424 global $ilLog;
425
426 $locks = array();
427
428 $counter = 0;
429 foreach($a_tables as $table)
430 {
431 $lock = 'LOCK TABLE ';
432
433 $lock .= ($table['name'].' ');
434
435 switch($table['type'])
436 {
437 case ilDB::LOCK_READ:
438 $lock .= ' IN SHARE MODE ';
439 break;
440
441 case ilDB::LOCK_WRITE:
442 $lock .= ' IN EXCLUSIVE MODE ';
443 break;
444 }
445
446 $locks[] = $lock;
447 }
448
449 // @TODO use and store a unique identifier to allow nested lock/unlocks
450 $this->db->beginTransaction();
451 foreach($locks as $lock)
452 {
453 $this->db->query($lock);
454 $ilLog->write(__METHOD__.': '.$lock);
455 }
456 return true;
457 }
458
463 public function unlockTables()
464 {
465 $this->db->commit();
466 }
467
473 function dropPrimaryKey($a_table)
474 {
475 // oracle 10: dropping constraint may not drop attached index
476 // http://www.orafaq.com/forum/t/59807/0/
477
478 $query = "ALTER TABLE ".$a_table." DROP PRIMARY KEY DROP INDEX";
479 $res = $this->db->query($query);
480
481 return $this->handleError($res, "dropPrimaryKey(".$a_table.")");
482 }
483}
484?>
isError($data, $code=null)
Tell whether a value is a MDB2 error.
Definition: MDB2.php:594
& connect($dsn, $options=false)
Create a new MDB2 connection object and connect to the specified database.
Definition: MDB2.php:431
const DB_FETCHMODE_ASSOC
Definition: class.ilDB.php:10
Oracle Database Wrapper.
concat($a_values, $a_allow_null=true)
CONCAT for oracle allows only the concatenation of two values.
initConnection()
Initialize the database connection.
supportsFulltext()
Is fulltext index supported?
getDSN()
Get DSN.
dropPrimaryKey($a_table)
Drop a primary key from a table.
constraintName($a_table, $a_constraint)
Constraint names must be "globally" unique in oracle.
locate($a_needle, $a_string, $a_start_pos=1)
Create locate string.
like($a_col, $a_type, $a_value="?", $case_insensitive=true)
Provisional LIKE support for oracle CLOB's Uses SUBSTR to reduce the length.
fromUnixtime($a_expr, $a_to_text=true)
fromUnixtime (makes timestamp out of unix timestamp)
lockTables($a_tables)
Lock table.
const CLOB_BUFFER_SIZE
getDBType()
Get DB Type.
static getReservedWords()
Get reserved words.
doConnect()
Standard way to connect to db.
unixTimestamp()
Unix timestamp.
replace($a_table, $a_pk_columns, $a_other_columns)
Replace into method.
getDBVersion()
Get DB version.
unlockTables()
Unlock tables.
getPrimaryKeyIdentifier()
Primary key identifier.
in($a_field, $a_values, $negate=false, $a_type="")
Overwritten implementation of $ilDB->in to avoid ORA-01795 (maximum number of expressions in a list i...
const LIMIT_EXPRESSIONS_IN_LIST
Database Wrapper.
Definition: class.ilDB.php:29
const LOCK_WRITE
Definition: class.ilDB.php:30
manipulate($sql)
Data manipulation.
getDBPort()
Get database port.
Definition: class.ilDB.php:113
handleError($a_res, $a_info="", $a_level="")
Handle MDB2 Errors.
Definition: class.ilDB.php:405
getDBUser()
Get database user.
Definition: class.ilDB.php:93
getDBName()
Get database name.
Definition: class.ilDB.php:173
query($sql, $a_handle_error=true)
Query.
insert($a_table, $a_columns)
Convenient method for standard insert statements, example field array:
const LOCK_READ
Definition: class.ilDB.php:31
raisePearError($a_message, $a_level="")
Raise an error.
Definition: class.ilDB.php:485
getDBPassword()
Get database password.
Definition: class.ilDB.php:153
getDBHost()
Get database host.
Definition: class.ilDB.php:133
quote($a_query, $a_type=null)
Wrapper for quote method.
$r
Definition: example_031.php:79
$GLOBALS['PHPCAS_CLIENT']
This global variable is used by the interface class phpCAS.
Definition: CAS.php:276