diff options
Diffstat (limited to 'program/lib/Roundcube/rcube_db_oracle.php')
| -rw-r--r-- | program/lib/Roundcube/rcube_db_oracle.php | 396 |
1 files changed, 366 insertions, 30 deletions
diff --git a/program/lib/Roundcube/rcube_db_oracle.php b/program/lib/Roundcube/rcube_db_oracle.php index ddd351ec3..338eb2e2a 100644 --- a/program/lib/Roundcube/rcube_db_oracle.php +++ b/program/lib/Roundcube/rcube_db_oracle.php @@ -10,8 +10,8 @@ | See the README file for a full license statement. | | | | PURPOSE: | - | Database wrapper class that implements PHP PDO functions | - | for Oracle database | + | Database wrapper class that implements database functions | + | for Oracle database using OCI8 extension | +-----------------------------------------------------------------------+ | Author: Aleksander Machniak <machniak@kolabsys.com> | +-----------------------------------------------------------------------+ @@ -19,7 +19,6 @@ /** * Database independent query interface - * This is a wrapper for the PHP PDO * * @package Framework * @subpackage Database @@ -28,6 +27,49 @@ class rcube_db_oracle extends rcube_db { public $db_provider = 'oracle'; + + /** + * Create connection instance + */ + protected function conn_create($dsn) + { + // Get database specific connection options + $dsn_options = $this->dsn_options($dsn); + + $function = $this->db_pconn ? 'oci_pconnect' : 'oci_connect'; + + if (!function_exists($function)) { + $this->db_error = true; + $this->db_error_msg = 'OCI8 extension not loaded. See http://php.net/manual/en/book.oci8.php'; + + rcube::raise_error(array('code' => 500, 'type' => 'db', + 'line' => __LINE__, 'file' => __FILE__, + 'message' => $this->db_error_msg), true, false); + + return; + } + + // connect + $dbh = @$function($dsn['username'], $dsn['password'], $dsn_options['database'], $dsn_options['charset']); + + if (!$dbh) { + $error = oci_error(); + $this->db_error = true; + $this->db_error_msg = $error['message']; + + rcube::raise_error(array('code' => 500, 'type' => 'db', + 'line' => __LINE__, 'file' => __FILE__, + 'message' => $this->db_error_msg), true, false); + + return; + } + + // configure session + $this->conn_configure($dsn, $dbh); + + return $dbh; + } + /** * Driver-specific configuration of database connection * @@ -36,8 +78,135 @@ class rcube_db_oracle extends rcube_db */ protected function conn_configure($dsn, $dbh) { - $dbh->query("ALTER SESSION SET nls_date_format = 'YYYY-MM-DD'"); - $dbh->query("ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS'"); + $init_queries = array( + "ALTER SESSION SET nls_date_format = 'YYYY-MM-DD'", + "ALTER SESSION SET nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS'", + ); + + foreach ($init_queries as $query) { + $stmt = oci_parse($dbh, $query); + oci_execute($stmt); + } + } + + /** + * Connection state checker + * + * @return boolean True if in connected state + */ + public function is_connected() + { + return empty($this->dbh) ? false : $this->db_connected; + } + + /** + * Execute a SQL query with limits + * + * @param string $query SQL query to execute + * @param int $offset Offset for LIMIT statement + * @param int $numrows Number of rows for LIMIT statement + * @param array $params Values to be inserted in query + * + * @return PDOStatement|bool Query handle or False on error + */ + protected function _query($query, $offset, $numrows, $params) + { + $query = ltrim($query); + + $this->db_connect($this->dsn_select($query), true); + + // check connection before proceeding + if (!$this->is_connected()) { + return $this->last_result = false; + } + + if ($numrows || $offset) { + $query = $this->set_limit($query, $numrows, $offset); + } + + // replace self::DEFAULT_QUOTE with driver-specific quoting + $query = $this->query_parse($query); + + // Because in Roundcube we mostly use queries that are + // executed only once, we will not use prepared queries + $pos = 0; + $idx = 0; + $args = array(); + + if (count($params)) { + while ($pos = strpos($query, '?', $pos)) { + if ($query[$pos+1] == '?') { // skip escaped '?' + $pos += 2; + } + else { + $val = $this->quote($params[$idx++]); + + // long strings are not allowed inline, need to be parametrized + if (strlen($val) > 4000) { + $key = ':param' . (count($args) + 1); + $args[$key] = $params[$idx-1]; + $val = $key; + } + + unset($params[$idx-1]); + $query = substr_replace($query, $val, $pos, 1); + $pos += strlen($val); + } + } + } + + // replace escaped '?' back to normal, see self::quote() + $query = str_replace('??', '?', $query); + $query = rtrim($query, " \t\n\r\0\x0B;"); + + // log query + $this->debug($query); + + // destroy reference to previous result + $this->last_result = null; + $this->db_error_msg = null; + + // prepare query + $result = @oci_parse($this->dbh, $query); + $mode = $this->in_transaction ? OCI_NO_AUTO_COMMIT : OCI_COMMIT_ON_SUCCESS; + + if ($result) { + foreach ($args as $param => $arg) { + oci_bind_by_name($result, $param, $args[$param], -1, SQLT_LNG); + } + } + + // execute query + if (!$result || !@oci_execute($result, $mode)) { + $result = $this->handle_error($query, $result); + } + + return $this->last_result = $result; + } + + /** + * Helper method to handle DB errors. + * This by default logs the error but could be overriden by a driver implementation + * + * @param string Query that triggered the error + * @return mixed Result to be stored and returned + */ + protected function handle_error($query, $result = null) + { + $error = oci_error(is_resource($result) ? $result : $this->dbh); + + // @TODO: Find error codes for key errors + if (empty($this->options['ignore_key_errors']) || !in_array($error['code'], array('23000', '23505'))) { + $this->db_error = true; + $this->db_error_msg = sprintf('[%s] %s', $error['code'], $error['message']); + + rcube::raise_error(array('code' => 500, 'type' => 'db', + 'line' => __LINE__, 'file' => __FILE__, + 'message' => $this->db_error_msg . " (SQL Query: $query)" + ), true, false); + } + + return false; } /** @@ -54,9 +223,83 @@ class rcube_db_oracle extends rcube_db } $sequence = $this->quote_identifier($this->sequence_name($table)); - $result = $dbh->query("SELECT $sequence.currval FROM dual"); + $result = $this->query("SELECT $sequence.currval FROM dual"); + $result = $this->fetch_array($result); + + return $result[0] ?: false; + } + + /** + * Get number of affected rows for the last query + * + * @param mixed $result Optional query handle + * + * @return int Number of (matching) rows + */ + public function affected_rows($result = null) + { + if ($result || ($result === null && ($result = $this->last_result))) { + return oci_num_rows($result); + } + + return 0; + } + + /** + * Get number of rows for a SQL query + * If no query handle is specified, the last query will be taken as reference + * + * @param mixed $result Optional query handle + * @return mixed Number of rows or false on failure + * @deprecated This method shows very poor performance and should be avoided. + */ + public function num_rows($result = null) + { + // not implemented + return false; + } + + /** + * Get an associative array for one row + * If no query handle is specified, the last query will be taken as reference + * + * @param mixed $result Optional query handle + * + * @return mixed Array with col values or false on failure + */ + public function fetch_assoc($result = null) + { + return $this->_fetch_row($result, OCI_ASSOC); + } - return $result ? $result->fetchColumn() : false; + /** + * Get an index array for one row + * If no query handle is specified, the last query will be taken as reference + * + * @param mixed $result Optional query handle + * + * @return mixed Array with col values or false on failure + */ + public function fetch_array($result = null) + { + return $this->_fetch_row($result, OCI_NUM); + } + + /** + * Get col values for a result row + * + * @param mixed $result Optional query handle + * @param int $mode Fetch mode identifier + * + * @return mixed Array with col values or false on failure + */ + protected function _fetch_row($result, $mode) + { + if ($result || ($result === null && ($result = $this->last_result))) { + return oci_fetch_array($result, $mode + OCI_RETURN_NULLS + OCI_RETURN_LOBS); + } + + return false; } /** @@ -177,23 +420,13 @@ class rcube_db_oracle extends rcube_db // @TODO: Oracle 12g has better OFFSET support - $orderby = stristr($query, 'ORDER BY'); - $select = substr($query, 0, stripos($query, 'FROM')); - $offset += 1; - - if ($orderby !== false) { - $query = trim(substr($query, 0, -1 * strlen($orderby))); + if (!$offset) { + $query = "SELECT * FROM ($query) a WHERE rownum <= $end"; } else { - // it shouldn't happen, paging without sorting has not much sense - // @FIXME: I don't know how to build paging query without ORDER BY - $orderby = "ORDER BY 1"; + $query = "SELECT * FROM (SELECT a.*, rownum as rn FROM ($query) a WHERE rownum <= $end) b WHERE rn > $offset"; } - $query = preg_replace('/^SELECT\s/i', '', $query); - $query = "$select FROM (SELECT ROW_NUMBER() OVER ($orderby) AS row_number, $query)" - . " WHERE row_number BETWEEN $offset AND $end"; - return $query; } @@ -232,12 +465,11 @@ class rcube_db_oracle extends rcube_db } /** - * Returns PDO DSN string from DSN array + * Returns connection options from DSN array */ - protected function dsn_string($dsn) + protected function dsn_options($dsn) { $params = array(); - $result = 'oci:'; if ($dsn['hostspec']) { $host = $dsn['hostspec']; @@ -245,19 +477,123 @@ class rcube_db_oracle extends rcube_db $host .= ':' . $dsn['port']; } - $dsn['database'] = $host . '/' . $dsn['database']; + $params['database'] = $host . '/' . $dsn['database']; } - if ($dsn['database']) { - $params[] = 'dbname=' . $dsn['database']; + $params['charset'] = 'UTF8'; + + return $params; + } + + /** + * Execute the given SQL script + * + * @param string SQL queries to execute + * + * @return boolen True on success, False on error + */ + public function exec_script($sql) + { + $sql = $this->fix_table_names($sql); + $buff = ''; + $body = false; + + foreach (explode("\n", $sql) as $line) { + $tok = strtolower(trim($line)); + if (preg_match('/^--/', $line) || $tok == '') { + continue; + } + + $buff .= $line . "\n"; + + // detect PL/SQL function bodies, don't break on semicolon + if ($body && $tok == 'end;') { + $body = false; + } + else if (!$body && $tok == 'begin') { + $body = true; + } + + if (!$body && substr($tok, -1) == ';') { + $this->query($buff); + $buff = ''; + if ($this->db_error) { + break; + } + } } - $params['charset'] = 'UTF8'; + return !$this->db_error; + } + + /** + * Start transaction + * + * @return bool True on success, False on failure + */ + public function startTransaction() + { + $this->db_connect('w', true); + + // check connection before proceeding + if (!$this->is_connected()) { + return $this->last_result = false; + } + + $this->debug('BEGIN TRANSACTION'); + + return $this->last_result = $this->in_transaction = true; + } + + /** + * Commit transaction + * + * @return bool True on success, False on failure + */ + public function endTransaction() + { + $this->db_connect('w', true); - if (!empty($params)) { - $result .= implode(';', $params); + // check connection before proceeding + if (!$this->is_connected()) { + return $this->last_result = false; + } + + $this->debug('COMMIT TRANSACTION'); + + if ($result = @oci_commit($this->dbh)) { + $this->in_transaction = true; + } + else { + $this->handle_error('COMMIT'); + } + + return $this->last_result = $result; + } + + /** + * Rollback transaction + * + * @return bool True on success, False on failure + */ + public function rollbackTransaction() + { + $this->db_connect('w', true); + + // check connection before proceeding + if (!$this->is_connected()) { + return $this->last_result = false; + } + + $this->debug('ROLLBACK TRANSACTION'); + + if ($result = @oci_rollback($this->dbh)) { + $this->in_transaction = false; + } + else { + $this->handle_error('ROLLBACK'); } - return $result; + return $this->last_result = $this->dbh->rollBack(); } } |