ILIAS  release_5-3 Revision v5.3.23-19-g915713cf615
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/MDB2/class.ilDB.php");
6
18class ilDBOracle extends ilDB
19{
20 const CLOB_BUFFER_SIZE = 2000;
22
23
24 public function loadModule($module)
25 {
26 // TODO: Implement loadModule() method.
27 }
28
29
33 public function getDSN()
34 {
35 // TODO: check if there is another solution.
36 // This works with 11g
37 if (!isset($GLOBALS['_MDB2_dsninfo_default']['charset']) or
38 $GLOBALS['_MDB2_dsninfo_default']['charset'] != 'utf8') {
39 $GLOBALS['_MDB2_dsninfo_default']['charset'] = 'utf8';
40 }
41
42 return array('phptype' => 'oci8',
43 'hostspec' => $this->getDBHost(),
44 'username' => $this->getDBUser(),
45 'password' => $this->getDBPassword(),
46 'port' => $this->getDBPort(),
47 'service' => $this->getDBName()
48 );
49
50 //return "oci8://".$this->getDBUser().":".$this->getDBPassword()."@".
51 // $this->getDBHost()."/?service=".$this->getDBName();
52 }
53
57 public function getDBType()
58 {
59 return "oracle";
60 }
61
62 public function getDBVersion()
63 {
64 $query = 'SELECT * FROM v$version';
65 $res = $this->db->query($query);
66
67 if (MDB2::isError($res)) {
68 return parent::getDBVersion();
69 }
71 return isset($row['banner']) ? $row['banner'] : parent::getDBVersion();
72 }
73
77 public static function getReservedWords()
78 {
79 // version: 10g
80 // url: http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/ap_keywd.htm#g691972
81 return array(
82 "ACCESS", "ADD", "ALL", "ALTER", "AND", "ANY", "AS", "ASC",
83 "AUDIT", "BETWEEN", "BY", "CHAR", "CHECK", "CLUSTER", "COLUMN",
84 "COMMENT", "COMPRESS", "CONNECT", "CREATE", "CURRENT", "DATE",
85 "DECIMAL", "DEFAULT", "DELETE", "DESC", "DISTINCT", "DROP", "ELSE",
86 "EXCLUSIVE", "EXISTS", "FILE", "FLOAT", "FOR", "FROM", "GRANT", "GROUP",
87 "HAVING", "IDENTIFIED", "IMMEDIATE", "IN", "INCREMENT", "INDEX", "INITIAL",
88 "INSERT", "INTEGER", "INTERSECT", "INTO", "IS", "LEVEL", "LIKE", "LOCK", "LONG",
89 "MAXEXTENTS", "MINUS", "MLSLABEL", "MODE", "MODIFY", "NOAUDIT", "NOCOMPRESS", "NOT",
90 "NOWAIT", "NULL", "NUMBER", "OF", "OFFLINE", "ON", "ONLINE","OPTION",
91 "OR", "ORDER", "PCTFREE", "PRIOR", "PRIVILEGES", "PUBLIC", "RAW", "RENAME",
92 "RESOURCE", "REVOKE", "ROW", "ROWID", "ROWNUM", "ROWS", "SELECT", "SESSION", "SET",
93 "SHARE", "SIZE", "SMALLINT", "START", "SUCCESSFUL", "SYNONYM", "SYSDATE","TABLE",
94 "THEN", "TO", "TRIGGER", "UID", "UNION", "UNIQUE", "UPDATE", "USER","VALIDATE",
95 "VALUES", "VARCHAR", "VARCHAR2", "VIEW", "WHENEVER", "WHERE", "WITH"
96 );
97 }
98
102 public function doConnect()
103 {
104 $this->db = MDB2::connect(
105 $this->getDSN(),
106 array("use_transactions" => true)
107 );
108 }
109
113 public function initConnection()
114 {
115 $GLOBALS['_MDB2_dsninfo_default']['charset'] = 'utf8';
116 $this->query("ALTER SESSION SET nls_length_semantics='CHAR'");
117 $this->query("ALTER SESSION SET NLS_SORT = binary_ci");
118 }
119
120 /* function manipulate($sql)
121 {
122 //echo "1";
123 //if (!is_int(strpos($sql, "frm_thread_access")))
124 //{
125 //echo "2";
126 return parent::manipulate($sql);
127 //}
128 //echo "3";
129 }*/
130
135 public function now()
136 {
137 return "LOCALTIMESTAMP";
138 }
139
143 public function fromUnixtime($a_expr, $a_to_text = true)
144 {
145 if (!$a_to_text) {
146 return "TO_DATE('19700101000000','YYYYMMDDHH24MISS') " .
147 "+ NUMTODSINTERVAL(" . $a_expr . ", 'SECOND')";
148 } else {
149 return "to_char(TO_DATE('19700101000000','YYYYMMDDHH24MISS') " .
150 "+ NUMTODSINTERVAL(" . $a_expr . ", 'SECOND'), 'yyyy-mm-dd hh24:mi:ss')";
151 }
152 }
153
157 public function unixTimestamp()
158 {
159 // sysdate should respect database time zone
160 // current_date would respect session time zone
161 return "ROUND((sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400))";
162 }
163
167 public function constraintName($a_table, $a_constraint)
168 {
169 return $a_table . "_" . $a_constraint;
170 }
171
175 public function getPrimaryKeyIdentifier()
176 {
177 return "pk";
178 }
179
183 public function supportsFulltext()
184 {
185 return false;
186 }
187
195 public function replace($a_table, $a_pk_columns, $a_other_columns)
196 {
197 $a_columns = array_merge($a_pk_columns, $a_other_columns);
198 $fields = array();
199 $field_values = array();
200 $placeholders = array();
201 $types = array();
202 $values = array();
203 $lobs = false;
204 $lob = array();
205 $val_field = array();
206 $a = array();
207 $b = array();
208 foreach ($a_columns as $k => $col) {
209 if ($col[0] == 'clob' or $col[0] == 'blob') {
210 $val_field[] = $this->quote($col[1], 'text') . " " . $k;
211 } else {
212 $val_field[] = $this->quote($col[1], $col[0]) . " " . $k;
213 }
214 $fields[] = $k;
215 $placeholders[] = "%s";
216 $placeholders2[] = ":$k";
217 $types[] = $col[0];
218
219 // integer auto-typecast (this casts bool values to integer)
220 if ($col[0] == 'integer' && !is_null($col[1])) {
221 $col[1] = (int) $col[1];
222 }
223
224 $values[] = $col[1];
225 $field_values[$k] = $col[1];
226 if ($col[0] == "blob" || $col[0] == "clob") {
227 $lobs = true;
228 $lob[$k] = $k;
229 }
230 $a[] = "a." . $k;
231 $b[] = "b." . $k;
232 }
233 $abpk = array();
234 $aboc = array();
235 $delwhere = array();
236 foreach ($a_pk_columns as $k => $col) {
237 $abpk[] = "a." . $k . " = b." . $k;
238 $delwhere[] = $k . " = " . $this->quote($col[1], $col[0]);
239 }
240 foreach ($a_other_columns as $k => $col) {
241 $aboc[] = "a." . $k . " = b." . $k;
242 }
243 if ($lobs) { // delete/insert
244 $this->manipulate(
245 "DELETE FROM " . $a_table . " WHERE " .
246 implode($delwhere, " AND ")
247 );
248 $this->insert($a_table, $a_columns);
249
250 //$r = $this->db->extended->autoExecute($a_table, $field_values, MDB2_AUTOQUERY_INSERT, null, $types);
251 $this->handleError($r, "replace, delete/insert(" . $a_table . ")");
252 } else { // if no lobs are used, use manipulate
253 $q = "MERGE INTO " . $a_table . " a " .
254 "USING (SELECT " . implode($val_field, ", ") . " " .
255 "FROM DUAL) b ON (" . implode($abpk, " AND ") . ") " .
256 "WHEN MATCHED THEN UPDATE SET " . implode($aboc, ", ") . " " .
257 "WHEN NOT MATCHED THEN INSERT (" . implode($a, ",") . ") VALUES (" . implode($b, ",") . ")";
258 $r = $this->manipulate($q);
259 }
260 return $r;
261 }
262
270 public function locate($a_needle, $a_string, $a_start_pos = 1)
271 {
272 $locate = ' INSTR( ';
273 $locate .= ('SUBSTR(' . $a_string . ',0,' . self::CLOB_BUFFER_SIZE . ')');
274 $locate .= ',';
275 $locate .= $a_needle;
276 $locate .= ',';
277 $locate .= $a_start_pos;
278 $locate .= ') ';
279 return $locate;
280 }
281
293 public function like($a_col, $a_type, $a_value = "?", $case_insensitive = true)
294 {
295 if ($a_type == 'text') {
296 return parent::like($a_col, $a_type, $a_value, $case_insensitive);
297 }
298
299 if (!in_array($a_type, array("text", "clob", "blob"))) {
300 $this->raisePearError("Like: Invalid column type '" . $a_type . "'.", $this->error_class->FATAL);
301 }
302 if ($a_value == "?") {
303 if ($case_insensitive) {
304 return "UPPER(SUBSTR(" . $a_col . ",0," . self::CLOB_BUFFER_SIZE . ")) LIKE(UPPER(?))";
305 } else {
306 return "SUBSTR(" . $a_col . ",0," . self::CLOB_BUFFER_SIZE . ") LIKE(?)";
307 }
308 } else {
309 if ($case_insensitive) {
310 return " UPPER(SUBSTR(" . $a_col . ",0," . self::CLOB_BUFFER_SIZE . ")) LIKE(UPPER(" . $this->quote($a_value, 'text') . "))";
311 } else {
312 return " SUBSTR(" . $a_col . ",0," . self::CLOB_BUFFER_SIZE . ") LIKE(" . $this->quote($a_value, 'text') . ")";
313 }
314 }
315 }
316
323 public function concat(array $a_values, $a_allow_null = true)
324 {
325 if (count($a_values) <= 2) {
326 return parent::concat($a_values, false);
327 }
328
329 $first = true;
330 foreach ($a_values as $field_info) {
331 if (!$first) {
332 $concat_value = parent::concat(
333 array(
334 array($concat_value,$concat_type),
335 array($field_info[0],$field_info[1])),
336 false
337 );
338 } else {
339 $first = false;
340 $concat_value = $field_info[0];
341 $concat_type = $field_info[1];
342 }
343 }
344 return $concat_value;
345 }
346
361 public function in($a_field, $a_values, $negate = false, $a_type = "")
362 {
363 if (count($a_values) <= self::LIMIT_EXPRESSIONS_IN_LIST) {
364 return parent::in($a_field, $a_values, $negate, $a_type);
365 }
366
367 $first = true;
368 $concat = $negate ? ' AND ' : ' OR ';
369 $in = '(';
370 do {
371 if (!$first) {
372 $in .= $concat;
373 }
374 $first = false;
375
376 $spliced = array_splice($a_values, 0, self::LIMIT_EXPRESSIONS_IN_LIST);
377 $in .= parent::in($a_field, $spliced, $negate, $a_type);
378 } while ($a_values);
379
380 return $in . " ) ";
381 }
382
383
394 public function lockTables($a_tables)
395 {
396 global $ilLog;
397
398 $locks = array();
399
400 $counter = 0;
401 foreach ($a_tables as $table) {
402 $lock = 'LOCK TABLE ';
403
404 $lock .= ($table['name'] . ' ');
405
406 switch ($table['type']) {
408 $lock .= ' IN SHARE MODE ';
409 break;
410
412 $lock .= ' IN EXCLUSIVE MODE ';
413 break;
414 }
415
416 $locks[] = $lock;
417 }
418
419 // @TODO use and store a unique identifier to allow nested lock/unlocks
420 $this->db->beginTransaction();
421 foreach ($locks as $lock) {
422 $this->db->query($lock);
423 $ilLog->write(__METHOD__ . ': ' . $lock);
424 }
425 return true;
426 }
427
433 public function unlockTables()
434 {
435 $this->db->commit();
436 }
437
443 public function dropPrimaryKey($a_table)
444 {
445 // oracle 10: dropping constraint may not drop attached index
446 // http://www.orafaq.com/forum/t/59807/0/
447
448 $query = "ALTER TABLE " . $a_table . " DROP PRIMARY KEY DROP INDEX";
449 $res = $this->db->query($query);
450
451 return $this->handleError($res, "dropPrimaryKey(" . $a_table . ")");
452 }
453
454
455 public function setStorageEngine($storage_engine)
456 {
457 unset($storage_engine);
458 }
459
460
461 public function getStorageEngine()
462 {
463 return null;
464 }
465
466
467 public function dropFulltextIndex($a_table, $a_name)
468 {
469 return false;
470 }
471}
if(php_sapi_name() !='cli') $in
Definition: Utf8Test.php:37
An exception for terminatinating execution or to throw for unit testing.
isError($data, $code=null)
Tell whether a value is a MDB2 error.
Definition: MDB2.php:599
& connect($dsn, $options=false)
Create a new MDB2 connection object and connect to the specified database.
Definition: MDB2.php:436
Oracle Database Wrapper.
concat(array $a_values, $a_allow_null=true)
CONCAT for oracle allows only the concatenation of two values.
setStorageEngine($storage_engine)
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.
loadModule($module)
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.
dropFulltextIndex($a_table, $a_name)
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:30
manipulate($sql)
Data manipulation.
getDBPort()
Get database port.
Definition: class.ilDB.php:103
handleError($a_res, $a_info="", $a_level="")
Handle MDB2 Errors.
Definition: class.ilDB.php:423
getDBUser()
Get database user.
Definition: class.ilDB.php:83
getDBName()
Get database name.
Definition: class.ilDB.php:163
query($sql, $a_handle_error=true)
Query.
insert($a_table, $a_columns)
Convenient method for standard insert statements, example field array:
raisePearError($a_message, $a_level="")
Raise an error.
Definition: class.ilDB.php:460
getDBPassword()
Get database password.
Definition: class.ilDB.php:143
getDBHost()
Get database host.
Definition: class.ilDB.php:123
quote($a_query, $a_type=null)
Wrapper for quote method.
$counter
$r
Definition: example_031.php:79
$GLOBALS['loaded']
Global hash that tracks already loaded includes.
if($modEnd===false) $module
Definition: module.php:59
$query
if(empty($password)) $table
Definition: pwgen.php:24
foreach($_POST as $key=> $value) $res
$a_type
Definition: workflow.php:92