Mercurial > hg > rc2

comparison program/lib/Roundcube/rcube_db_oracle.php @ 0:4681f974d28b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
vanilla 1.3.3 distro, I hope
author Charlie Root
date Thu, 04 Jan 2018 15:52:31 -0500
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:4681f974d28b
1 <?php
2
3 /**
4 +-----------------------------------------------------------------------+
5 | This file is part of the Roundcube Webmail client |
6 | Copyright (C) 2011-2014, Kolab Systems AG |
7 | |
8 | Licensed under the GNU General Public License version 3 or |
9 | any later version with exceptions for skins & plugins. |
10 | See the README file for a full license statement. |
11 | |
12 | PURPOSE: |
13 | Database wrapper class that implements database functions |
14 | for Oracle database using OCI8 extension |
15 +-----------------------------------------------------------------------+
16 | Author: Aleksander Machniak <machniak@kolabsys.com> |
17 +-----------------------------------------------------------------------+
18 */
19
20 /**
21 * Database independent query interface
22 *
23 * @package Framework
24 * @subpackage Database
25 */
26 class rcube_db_oracle extends rcube_db
27 {
28 public $db_provider = 'oracle';
29
30
31 /**
32 * Create connection instance
33 */
34 protected function conn_create($dsn)
35 {
36 // Get database specific connection options
37 $dsn_options = $this->dsn_options($dsn);
38
39 $function = $this->db_pconn ? 'oci_pconnect' : 'oci_connect';
40
41 if (!function_exists($function)) {
42 $this->db_error = true;
43 $this->db_error_msg = 'OCI8 extension not loaded. See http://php.net/manual/en/book.oci8.php';
44
45 rcube::raise_error(array('code' => 500, 'type' => 'db',
46 'line' => __LINE__, 'file' => __FILE__,
47 'message' => $this->db_error_msg), true, false);
48
49 return;
50 }
51
52 // connect
53 $dbh = @$function($dsn['username'], $dsn['password'], $dsn_options['database'], $dsn_options['charset']);
54
55 if (!$dbh) {
56 $error = oci_error();
57 $this->db_error = true;
58 $this->db_error_msg = $error['message'];
59
60 rcube::raise_error(array('code' => 500, 'type' => 'db',
61 'line' => __LINE__, 'file' => __FILE__,
62 'message' => $this->db_error_msg), true, false);
63
64 return;
65 }
66
67 // configure session
68 $this->conn_configure($dsn, $dbh);
69
70 return $dbh;
71 }
72
73 /**
74 * Driver-specific configuration of database connection
75 *
76 * @param array $dsn DSN for DB connections
77 * @param PDO $dbh Connection handler
78 */
79 protected function conn_configure($dsn, $dbh)
80 {
81 $init_queries = array(
82 "ALTER SESSION SET nls_date_format = 'YYYY-MM-DD'",
83 "ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS'",
84 );
85
86 foreach ($init_queries as $query) {
87 $stmt = oci_parse($dbh, $query);
88 oci_execute($stmt);
89 }
90 }
91
92 /**
93 * Connection state checker
94 *
95 * @return boolean True if in connected state
96 */
97 public function is_connected()
98 {
99 return empty($this->dbh) ? false : $this->db_connected;
100 }
101
102 /**
103 * Execute a SQL query with limits
104 *
105 * @param string $query SQL query to execute
106 * @param int $offset Offset for LIMIT statement
107 * @param int $numrows Number of rows for LIMIT statement
108 * @param array $params Values to be inserted in query
109 *
110 * @return PDOStatement|bool Query handle or False on error
111 */
112 protected function _query($query, $offset, $numrows, $params)
113 {
114 $query = ltrim($query);
115
116 $this->db_connect($this->dsn_select($query), true);
117
118 // check connection before proceeding
119 if (!$this->is_connected()) {
120 return $this->last_result = false;
121 }
122
123 if ($numrows || $offset) {
124 $query = $this->set_limit($query, $numrows, $offset);
125 }
126
127 // replace self::DEFAULT_QUOTE with driver-specific quoting
128 $query = $this->query_parse($query);
129
130 // Because in Roundcube we mostly use queries that are
131 // executed only once, we will not use prepared queries
132 $pos = 0;
133 $idx = 0;
134 $args = array();
135
136 if (!empty($params)) {
137 while ($pos = strpos($query, '?', $pos)) {
138 if ($query[$pos+1] == '?') { // skip escaped '?'
139 $pos += 2;
140 }
141 else {
142 $val = $this->quote($params[$idx++]);
143
144 // long strings are not allowed inline, need to be parametrized
145 if (strlen($val) > 4000) {
146 $key = ':param' . (count($args) + 1);
147 $args[$key] = $params[$idx-1];
148 $val = $key;
149 }
150
151 unset($params[$idx-1]);
152 $query = substr_replace($query, $val, $pos, 1);
153 $pos += strlen($val);
154 }
155 }
156 }
157
158 $query = rtrim($query, " \t\n\r\0\x0B;");
159
160 // replace escaped '?' and quotes back to normal, see self::quote()
161 $query = str_replace(
162 array('??', self::DEFAULT_QUOTE.self::DEFAULT_QUOTE),
163 array('?', self::DEFAULT_QUOTE),
164 $query
165 );
166
167 // log query
168 $this->debug($query);
169
170 // destroy reference to previous result
171 $this->last_result = null;
172 $this->db_error_msg = null;
173
174 // prepare query
175 $result = @oci_parse($this->dbh, $query);
176 $mode = $this->in_transaction ? OCI_NO_AUTO_COMMIT : OCI_COMMIT_ON_SUCCESS;
177
178 if ($result) {
179 foreach (array_keys($args) as $param) {
180 oci_bind_by_name($result, $param, $args[$param], -1, SQLT_LNG);
181 }
182 }
183
184 // execute query
185 if (!$result || !@oci_execute($result, $mode)) {
186 $result = $this->handle_error($query, $result);
187 }
188
189 return $this->last_result = $result;
190 }
191
192 /**
193 * Helper method to handle DB errors.
194 * This by default logs the error but could be overridden by a driver implementation
195 *
196 * @param string Query that triggered the error
197 * @return mixed Result to be stored and returned
198 */
199 protected function handle_error($query, $result = null)
200 {
201 $error = oci_error(is_resource($result) ? $result : $this->dbh);
202
203 // @TODO: Find error codes for key errors
204 if (empty($this->options['ignore_key_errors']) || !in_array($error['code'], array('23000', '23505'))) {
205 $this->db_error = true;
206 $this->db_error_msg = sprintf('[%s] %s', $error['code'], $error['message']);
207
208 rcube::raise_error(array('code' => 500, 'type' => 'db',
209 'line' => __LINE__, 'file' => __FILE__,
210 'message' => $this->db_error_msg . " (SQL Query: $query)"
211 ), true, false);
212 }
213
214 return false;
215 }
216
217 /**
218 * Get last inserted record ID
219 *
220 * @param string $table Table name (to find the incremented sequence)
221 *
222 * @return mixed ID or false on failure
223 */
224 public function insert_id($table = null)
225 {
226 if (!$this->db_connected || $this->db_mode == 'r' || empty($table)) {
227 return false;
228 }
229
230 $sequence = $this->quote_identifier($this->sequence_name($table));
231 $result = $this->query("SELECT $sequence.currval FROM dual");
232 $result = $this->fetch_array($result);
233
234 return $result[0] ?: false;
235 }
236
237 /**
238 * Get number of affected rows for the last query
239 *
240 * @param mixed $result Optional query handle
241 *
242 * @return int Number of (matching) rows
243 */
244 public function affected_rows($result = null)
245 {
246 if ($result || ($result === null && ($result = $this->last_result))) {
247 return oci_num_rows($result);
248 }
249
250 return 0;
251 }
252
253 /**
254 * Get number of rows for a SQL query
255 * If no query handle is specified, the last query will be taken as reference
256 *
257 * @param mixed $result Optional query handle
258 * @return mixed Number of rows or false on failure
259 * @deprecated This method shows very poor performance and should be avoided.
260 */
261 public function num_rows($result = null)
262 {
263 // not implemented
264 return false;
265 }
266
267 /**
268 * Get an associative array for one row
269 * If no query handle is specified, the last query will be taken as reference
270 *
271 * @param mixed $result Optional query handle
272 *
273 * @return mixed Array with col values or false on failure
274 */
275 public function fetch_assoc($result = null)
276 {
277 return $this->_fetch_row($result, OCI_ASSOC);
278 }
279
280 /**
281 * Get an index array for one row
282 * If no query handle is specified, the last query will be taken as reference
283 *
284 * @param mixed $result Optional query handle
285 *
286 * @return mixed Array with col values or false on failure
287 */
288 public function fetch_array($result = null)
289 {
290 return $this->_fetch_row($result, OCI_NUM);
291 }
292
293 /**
294 * Get col values for a result row
295 *
296 * @param mixed $result Optional query handle
297 * @param int $mode Fetch mode identifier
298 *
299 * @return mixed Array with col values or false on failure
300 */
301 protected function _fetch_row($result, $mode)
302 {
303 if ($result || ($result === null && ($result = $this->last_result))) {
304 return oci_fetch_array($result, $mode + OCI_RETURN_NULLS + OCI_RETURN_LOBS);
305 }
306
307 return false;
308 }
309
310 /**
311 * Formats input so it can be safely used in a query
312 * PDO_OCI does not implement quote() method
313 *
314 * @param mixed $input Value to quote
315 * @param string $type Type of data (integer, bool, ident)
316 *
317 * @return string Quoted/converted string for use in query
318 */
319 public function quote($input, $type = null)
320 {
321 // handle int directly for better performance
322 if ($type == 'integer' || $type == 'int') {
323 return intval($input);
324 }
325
326 if (is_null($input)) {
327 return 'NULL';
328 }
329
330 if ($input instanceof DateTime) {
331 return $this->quote($input->format($this->options['datetime_format']));
332 }
333
334 if ($type == 'ident') {
335 return $this->quote_identifier($input);
336 }
337
338 switch ($type) {
339 case 'bool':
340 case 'integer':
341 return intval($input);
342 default:
343 return "'" . strtr($input, array(
344 '?' => '??',
345 "'" => "''",
346 rcube_db::DEFAULT_QUOTE => rcube_db::DEFAULT_QUOTE . rcube_db::DEFAULT_QUOTE
347 )) . "'";
348 }
349 }
350
351 /**
352 * Return correct name for a specific database sequence
353 *
354 * @param string $table Table name
355 *
356 * @return string Translated sequence name
357 */
358 protected function sequence_name($table)
359 {
360 // Note: we support only one sequence per table
361 // Note: The sequence name must be <table_name>_seq
362 $sequence = $table . '_seq';
363
364 // modify sequence name if prefix is configured
365 if ($prefix = $this->options['table_prefix']) {
366 return $prefix . $sequence;
367 }
368
369 return $sequence;
370 }
371
372 /**
373 * Return SQL statement for case insensitive LIKE
374 *
375 * @param string $column Field name
376 * @param string $value Search value
377 *
378 * @return string SQL statement to use in query
379 */
380 public function ilike($column, $value)
381 {
382 return 'UPPER(' . $this->quote_identifier($column) . ') LIKE UPPER(' . $this->quote($value) . ')';
383 }
384
385 /**
386 * Return SQL function for current time and date
387 *
388 * @param int $interval Optional interval (in seconds) to add/subtract
389 *
390 * @return string SQL function to use in query
391 */
392 public function now($interval = 0)
393 {
394 if ($interval) {
395 $interval = intval($interval);
396 return "current_timestamp + INTERVAL '$interval' SECOND";
397 }
398
399 return "current_timestamp";
400 }
401
402 /**
403 * Return SQL statement to convert a field value into a unix timestamp
404 *
405 * @param string $field Field name
406 *
407 * @return string SQL statement to use in query
408 * @deprecated
409 */
410 public function unixtimestamp($field)
411 {
412 return "(($field - to_date('1970-01-01','YYYY-MM-DD')) * 60 * 60 * 24)";
413 }
414
415 /**
416 * Adds TOP (LIMIT,OFFSET) clause to the query
417 *
418 * @param string $query SQL query
419 * @param int $limit Number of rows
420 * @param int $offset Offset
421 *
422 * @return string SQL query
423 */
424 protected function set_limit($query, $limit = 0, $offset = 0)
425 {
426 $limit = intval($limit);
427 $offset = intval($offset);
428 $end = $offset + $limit;
429
430 // @TODO: Oracle 12g has better OFFSET support
431
432 if (!$offset) {
433 $query = "SELECT * FROM ($query) a WHERE rownum <= $end";
434 }
435 else {
436 $query = "SELECT * FROM (SELECT a.*, rownum as rn FROM ($query) a WHERE rownum <= $end) b WHERE rn > $offset";
437 }
438
439 return $query;
440 }
441
442 /**
443 * Parse SQL file and fix table names according to table prefix
444 */
445 protected function fix_table_names($sql)
446 {
447 if (!$this->options['table_prefix']) {
448 return $sql;
449 }
450
451 $sql = parent::fix_table_names($sql);
452
453 // replace sequence names, and other Oracle-specific commands
454 $sql = preg_replace_callback('/((SEQUENCE ["]?)([^" \r\n]+)/',
455 array($this, 'fix_table_names_callback'),
456 $sql
457 );
458
459 $sql = preg_replace_callback(
460 '/([ \r\n]+["]?)([^"\' \r\n\.]+)(["]?\.nextval)/',
461 array($this, 'fix_table_names_seq_callback'),
462 $sql
463 );
464
465 return $sql;
466 }
467
468 /**
469 * Preg_replace callback for fix_table_names()
470 */
471 protected function fix_table_names_seq_callback($matches)
472 {
473 return $matches[1] . $this->options['table_prefix'] . $matches[2] . $matches[3];
474 }
475
476 /**
477 * Returns connection options from DSN array
478 */
479 protected function dsn_options($dsn)
480 {
481 $params = array();
482
483 if ($dsn['hostspec']) {
484 $host = $dsn['hostspec'];
485 if ($dsn['port']) {
486 $host .= ':' . $dsn['port'];
487 }
488
489 $params['database'] = $host . '/' . $dsn['database'];
490 }
491
492 $params['charset'] = 'UTF8';
493
494 return $params;
495 }
496
497 /**
498 * Execute the given SQL script
499 *
500 * @param string $sql SQL queries to execute
501 *
502 * @return boolen True on success, False on error
503 */
504 public function exec_script($sql)
505 {
506 $sql = $this->fix_table_names($sql);
507 $buff = '';
508 $body = false;
509
510 foreach (explode("\n", $sql) as $line) {
511 $tok = strtolower(trim($line));
512 if (preg_match('/^--/', $line) || $tok == '' || $tok == '/') {
513 continue;
514 }
515
516 $buff .= $line . "\n";
517
518 // detect PL/SQL function bodies, don't break on semicolon
519 if ($body && $tok == 'end;') {
520 $body = false;
521 }
522 else if (!$body && $tok == 'begin') {
523 $body = true;
524 }
525
526 if (!$body && substr($tok, -1) == ';') {
527 $this->query($buff);
528 $buff = '';
529 if ($this->db_error) {
530 break;
531 }
532 }
533 }
534
535 return !$this->db_error;
536 }
537
538 /**
539 * Start transaction
540 *
541 * @return bool True on success, False on failure
542 */
543 public function startTransaction()
544 {
545 $this->db_connect('w', true);
546
547 // check connection before proceeding
548 if (!$this->is_connected()) {
549 return $this->last_result = false;
550 }
551
552 $this->debug('BEGIN TRANSACTION');
553
554 return $this->last_result = $this->in_transaction = true;
555 }
556
557 /**
558 * Commit transaction
559 *
560 * @return bool True on success, False on failure
561 */
562 public function endTransaction()
563 {
564 $this->db_connect('w', true);
565
566 // check connection before proceeding
567 if (!$this->is_connected()) {
568 return $this->last_result = false;
569 }
570
571 $this->debug('COMMIT TRANSACTION');
572
573 if ($result = @oci_commit($this->dbh)) {
574 $this->in_transaction = true;
575 }
576 else {
577 $this->handle_error('COMMIT');
578 }
579
580 return $this->last_result = $result;
581 }
582
583 /**
584 * Rollback transaction
585 *
586 * @return bool True on success, False on failure
587 */
588 public function rollbackTransaction()
589 {
590 $this->db_connect('w', true);
591
592 // check connection before proceeding
593 if (!$this->is_connected()) {
594 return $this->last_result = false;
595 }
596
597 $this->debug('ROLLBACK TRANSACTION');
598
599 if (@oci_rollback($this->dbh)) {
600 $this->in_transaction = false;
601 }
602 else {
603 $this->handle_error('ROLLBACK');
604 }
605
606 return $this->last_result = $this->dbh->rollBack();
607 }
608
609 /**
610 * Terminate database connection.
611 */
612 public function closeConnection()
613 {
614 // release statement and close connection(s)
615 $this->last_result = null;
616 foreach ($this->dbhs as $dbh) {
617 oci_close($dbh);
618 }
619
620 parent::closeConnection();
621 }
622 }