6 commits - docs/SQL lib/ext lib/kolab_sync_backend_common.php lib/kolab_sync_backend_content.php lib/kolab_sync_backend_folder.php lib/kolab_sync_backend.php lib/kolab_sync_backend_state.php lib/kolab_sync_db.php lib/kolab_sync.php lib/kolab_sync_transaction_manager.php
Aleksander Machniak
machniak at kolabsys.com
Mon Sep 22 09:16:30 CEST 2014
docs/SQL/oracle.initial.sql | 104 +++++
lib/ext/Roundcube/rcube_db.php | 177 ++++++++-
lib/ext/Roundcube/rcube_db_mysql.php | 7
lib/ext/Roundcube/rcube_db_oracle.php | 599 +++++++++++++++++++++++++++++++++
lib/kolab_sync.php | 2
lib/kolab_sync_backend.php | 12
lib/kolab_sync_backend_common.php | 26 -
lib/kolab_sync_backend_content.php | 8
lib/kolab_sync_backend_folder.php | 6
lib/kolab_sync_backend_state.php | 16
lib/kolab_sync_db.php | 64 ---
lib/kolab_sync_transaction_manager.php | 31 -
12 files changed, 908 insertions(+), 144 deletions(-)
New commits:
commit 4f12f064ffdff954e2010fa156257be2a64ff83c
Author: Aleksander Machniak <machniak at kolabsys.com>
Date: Fri Sep 19 14:05:20 2014 +0200
Use parametrized INSERT/UPDATE queries to "workaround" Oracle length limits
diff --git a/lib/kolab_sync_backend_common.php b/lib/kolab_sync_backend_common.php
index 55e2f3b..13fdf23 100644
--- a/lib/kolab_sync_backend_common.php
+++ b/lib/kolab_sync_backend_common.php
@@ -90,17 +90,19 @@ class kolab_sync_backend_common implements Syncroton_Backend_IBackend
throw new InvalidArgumentException('$object must be instanace of ' . $this->interface_name);
}
- $data = $this->object_to_array($object);
- $insert = array();
+ $data = $this->object_to_array($object);
+ $cols = array();
$data['id'] = $object->id = sha1(mt_rand(). microtime());
- foreach ($data as $key => $value) {
- $insert[$this->db->quote_identifier($key)] = $this->db->quote($value);
+ foreach (array_keys($data) as $key) {
+ $cols[] = $this->db->quote_identifier($key);
}
- $this->db->query('INSERT INTO `' . $this->table_name . '`'
- . ' (' . implode(', ', array_keys($insert)) . ')' . ' VALUES(' . implode(', ', $insert) . ')');
+ $this->db->query('INSERT INTO `' . $this->table_name . '`' . ' (' . implode(', ', $cols) . ')'
+ . ' VALUES(' . implode(', ', array_fill(0, count($cols), '?')) . ')',
+ array_values($data)
+ );
if (!$this->db->insert_id($this->table_name)) {
// @TODO: throw exception
@@ -169,12 +171,12 @@ class kolab_sync_backend_common implements Syncroton_Backend_IBackend
$data = $this->object_to_array($object);
$set = array();
- foreach ($data as $key => $value) {
- $set[] = $this->db->quote_identifier($key) . ' = ' . $this->db->quote($value);
+ foreach (array_keys($data) as $key) {
+ $set[] = $this->db->quote_identifier($key) . ' = ?';
}
$this->db->query('UPDATE `' . $this->table_name . '` SET ' . implode(', ', $set)
- . ' WHERE `id` = ' . $this->db->quote($object->id));
+ . ' WHERE `id` = ' . $this->db->quote($object->id), array_values($data));
return $object;
}
commit 32aa1ab046776f823cfaec222f7cda07e1d3752b
Author: Aleksander Machniak <machniak at kolabsys.com>
Date: Fri Sep 19 08:33:17 2014 +0200
Update db classes with fixes for Oracle
diff --git a/lib/ext/Roundcube/rcube_db.php b/lib/ext/Roundcube/rcube_db.php
index 5a2ad03..1e6a206 100644
--- a/lib/ext/Roundcube/rcube_db.php
+++ b/lib/ext/Roundcube/rcube_db.php
@@ -70,6 +70,7 @@ class rcube_db
'dblib' => 'mssql',
'mysqli' => 'mysql',
'oci' => 'oracle',
+ 'oci8' => 'oracle',
);
$driver = isset($driver_map[$driver]) ? $driver_map[$driver] : $driver;
diff --git a/lib/ext/Roundcube/rcube_db_oci8.php b/lib/ext/Roundcube/rcube_db_oci8.php
deleted file mode 100644
index a55ba04..0000000
--- a/lib/ext/Roundcube/rcube_db_oci8.php
+++ /dev/null
@@ -1,534 +0,0 @@
-<?php
-
-/**
- +-----------------------------------------------------------------------+
- | This file is part of the Roundcube Webmail client |
- | Copyright (C) 2011-2014, Kolab Systems AG |
- | |
- | Licensed under the GNU General Public License version 3 or |
- | any later version with exceptions for skins & plugins. |
- | See the README file for a full license statement. |
- | |
- | PURPOSE: |
- | Database wrapper class that implements database functions |
- | for Oracle database using OCI8 extension |
- +-----------------------------------------------------------------------+
- | Author: Aleksander Machniak <machniak at kolabsys.com> |
- +-----------------------------------------------------------------------+
-*/
-
-/**
- * Database independent query interface
- *
- * @package Framework
- * @subpackage Database
- */
-class rcube_db_oci8 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
- *
- * @param array $dsn DSN for DB connections
- * @param PDO $dbh Connection handler
- */
- protected function conn_configure($dsn, $dbh)
- {
- $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 the query
- */
- protected function query_execute($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 (!@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;
- }
-
- /**
- * Get last inserted record ID
- *
- * @param string $table Table name (to find the incremented sequence)
- *
- * @return mixed ID or false on failure
- */
- public function insert_id($table = null)
- {
- if (!$this->db_connected || $this->db_mode == 'r' || empty($table)) {
- return false;
- }
-
- $sequence = $this->quote_identifier($this->sequence_name($table));
- $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);
- }
-
- /**
- * 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;
- }
-
- /**
- * Formats input so it can be safely used in a query
- * PDO_OCI does not implement quote() method
- *
- * @param mixed $input Value to quote
- * @param string $type Type of data (integer, bool, ident)
- *
- * @return string Quoted/converted string for use in query
- */
- public function quote($input, $type = null)
- {
- // handle int directly for better performance
- if ($type == 'integer' || $type == 'int') {
- return intval($input);
- }
-
- if (is_null($input)) {
- return 'NULL';
- }
-
- if ($type == 'ident') {
- return $this->quote_identifier($input);
- }
-
- switch ($type) {
- case 'bool':
- case 'integer':
- return intval($input);
- default:
- return "'" . strtr($input, array(
- '?' => '??',
- "'" => "''",
- rcube_db::DEFAULT_QUOTE => rcube_db::DEFAULT_QUOTE . rcube_db::DEFAULT_QUOTE
- )) . "'";
- }
- }
-
- /**
- * Return correct name for a specific database sequence
- *
- * @param string $table Table name
- *
- * @return string Translated sequence name
- */
- protected function sequence_name($table)
- {
- // Note: we support only one sequence per table
- // Note: The sequence name must be <table_name>_seq
- $sequence = $table . '_seq';
-
- // modify sequence name if prefix is configured
- if ($prefix = $this->options['table_prefix']) {
- return $prefix . $sequence;
- }
-
- return $sequence;
- }
-
- /**
- * Return SQL statement for case insensitive LIKE
- *
- * @param string $column Field name
- * @param string $value Search value
- *
- * @return string SQL statement to use in query
- */
- public function ilike($column, $value)
- {
- return 'UPPER(' . $this->quote_identifier($column) . ') LIKE UPPER(' . $this->quote($value) . ')';
- }
-
- /**
- * Return SQL function for current time and date
- *
- * @param int $interval Optional interval (in seconds) to add/subtract
- *
- * @return string SQL function to use in query
- */
- public function now($interval = 0)
- {
- if ($interval) {
- $interval = intval($interval);
- return "current_timestamp + INTERVAL '$interval' SECOND";
- }
-
- return "current_timestamp";
- }
-
- /**
- * Return SQL statement to convert a field value into a unix timestamp
- *
- * @param string $field Field name
- *
- * @return string SQL statement to use in query
- * @deprecated
- */
- public function unixtimestamp($field)
- {
- return "(($field - to_date('1970-01-01','YYYY-MM-DD')) * 60 * 60 * 24)";
- }
-
- /**
- * Adds TOP (LIMIT,OFFSET) clause to the query
- *
- * @param string $query SQL query
- * @param int $limit Number of rows
- * @param int $offset Offset
- *
- * @return string SQL query
- */
- protected function set_limit($query, $limit = 0, $offset = 0)
- {
- $limit = intval($limit);
- $offset = intval($offset);
- $end = $offset + $limit;
-
- // @TODO: Oracle 12g has better OFFSET support
-
- if (!$offset) {
- $query = "SELECT * FROM ($query) a WHERE rownum <= $end";
- }
- else {
- $query = "SELECT * FROM (SELECT a.*, rownum as rn FROM ($query) a WHERE rownum <= $end) b WHERE rn > $offset";
- }
-
- return $query;
- }
-
- /**
- * Parse SQL file and fix table names according to table prefix
- */
- protected function fix_table_names($sql)
- {
- if (!$this->options['table_prefix']) {
- return $sql;
- }
-
- $sql = parent::fix_table_names($sql);
-
- // replace sequence names, and other Oracle-specific commands
- $sql = preg_replace_callback('/((SEQUENCE ["]?)([^" \r\n]+)/',
- array($this, 'fix_table_names_callback'),
- $sql
- );
-
- $sql = preg_replace_callback(
- '/([ \r\n]+["]?)([^"\' \r\n\.]+)(["]?\.nextval)/',
- array($this, 'fix_table_names_seq_callback'),
- $sql
- );
-
- return $sql;
- }
-
- /**
- * Preg_replace callback for fix_table_names()
- */
- protected function fix_table_names_seq_callback($matches)
- {
- return $matches[1] . $this->options['table_prefix'] . $matches[2] . $matches[3];
- }
-
- /**
- * Returns connection options from DSN array
- */
- protected function dsn_options($dsn)
- {
- $params = array();
-
- if ($dsn['hostspec']) {
- $host = $dsn['hostspec'];
- if ($dsn['port']) {
- $host .= ':' . $dsn['port'];
- }
-
- $params['database'] = $host . '/' . $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;
- }
- }
- }
-
- 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);
-
- // 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 $this->last_result = $this->dbh->rollBack();
- }
-}
diff --git a/lib/ext/Roundcube/rcube_db_oracle.php b/lib/ext/Roundcube/rcube_db_oracle.php
index ddd351e..338eb2e 100644
--- a/lib/ext/Roundcube/rcube_db_oracle.php
+++ b/lib/ext/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 at 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();
}
}
commit c4bc1954b0d17155ed2bce9591f4e4d256f10452
Author: Aleksander Machniak <machniak at kolabsys.com>
Date: Thu Sep 18 11:30:12 2014 +0200
Handle database transactions with rcube_db class
diff --git a/lib/kolab_sync.php b/lib/kolab_sync.php
index ab9c27c..515076e 100644
--- a/lib/kolab_sync.php
+++ b/lib/kolab_sync.php
@@ -141,7 +141,7 @@ class kolab_sync extends rcube
// Register Syncroton backends
Syncroton_Registry::set('loggerBackend', $this->logger);
- Syncroton_Registry::set(Syncroton_Registry::DATABASE, new kolab_sync_db);
+ Syncroton_Registry::set(Syncroton_Registry::DATABASE, $this->get_dbh());
Syncroton_Registry::set(Syncroton_Registry::TRANSACTIONMANAGER, kolab_sync_transaction_manager::getInstance());
Syncroton_Registry::set(Syncroton_Registry::DEVICEBACKEND, new kolab_sync_backend_device);
Syncroton_Registry::set(Syncroton_Registry::FOLDERBACKEND, new kolab_sync_backend_folder);
diff --git a/lib/kolab_sync_db.php b/lib/kolab_sync_db.php
deleted file mode 100644
index b56ce52..0000000
--- a/lib/kolab_sync_db.php
+++ /dev/null
@@ -1,64 +0,0 @@
-<?php
-
-/**
- +--------------------------------------------------------------------------+
- | Kolab Sync (ActiveSync for Kolab) |
- | |
- | Copyright (C) 2011-2012, Kolab Systems AG <contact at kolabsys.com> |
- | |
- | This program is free software: you can redistribute it and/or modify |
- | it under the terms of the GNU Affero General Public License as published |
- | by the Free Software Foundation, either version 3 of the License, or |
- | (at your option) any later version. |
- | |
- | This program is distributed in the hope that it will be useful, |
- | but WITHOUT ANY WARRANTY; without even the implied warranty of |
- | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
- | GNU Affero General Public License for more details. |
- | |
- | You should have received a copy of the GNU Affero General Public License |
- | along with this program. If not, see <http://www.gnu.org/licenses/> |
- +--------------------------------------------------------------------------+
- | Author: Aleksander Machniak <machniak at kolabsys.com> |
- +--------------------------------------------------------------------------+
-*/
-
-/**
- * Database layer wrapper with transaction support
- */
-class kolab_sync_db
-{
- /**
- * the database adapter
- *
- * @var rcube_db
- */
- protected $db;
-
-
- public function __construct()
- {
- $this->db = rcube::get_instance()->get_dbh();
- }
-
- public function beginTransaction()
- {
- $query = 'BEGIN';
-
- $this->db->query($query);
- }
-
- public function commit()
- {
- $query = 'COMMIT';
-
- $this->db->query($query);
- }
-
- public function rollBack()
- {
- $query = 'ROLLBACK';
-
- $this->db->query($query);
- }
-}
diff --git a/lib/kolab_sync_transaction_manager.php b/lib/kolab_sync_transaction_manager.php
index e097378..2121d89 100644
--- a/lib/kolab_sync_transaction_manager.php
+++ b/lib/kolab_sync_transaction_manager.php
@@ -103,13 +103,8 @@ class kolab_sync_transaction_manager implements Syncroton_TransactionManagerInte
}
if (! in_array($_transactionable, $this->_openTransactionables)) {
- if ($this->_logger instanceof Zend_Log) {
- $this->_logger->debug(__METHOD__ . '::' . __LINE__ . " new transactionable. Starting transaction on this resource");
- }
-
- if ($_transactionable instanceof kolab_sync_db) {
- //setAutocommit($_transactionable,false);
- $_transactionable->beginTransaction();
+ if ($_transactionable instanceof rcube_db) {
+ $_transactionable->startTransaction();
}
else {
$this->rollBack();
@@ -122,10 +117,6 @@ class kolab_sync_transaction_manager implements Syncroton_TransactionManagerInte
$transactionId = sha1(mt_rand(). microtime());
array_push($this->_openTransactions, $transactionId);
- if ($this->_logger instanceof Zend_Log) {
- $this->_logger->debug(__METHOD__ . '::' . __LINE__ . " queued transaction with id $transactionId");
- }
-
return $transactionId;
}
@@ -149,19 +140,14 @@ class kolab_sync_transaction_manager implements Syncroton_TransactionManagerInte
$numOpenTransactions = count($this->_openTransactions);
if ($numOpenTransactions === 0) {
- if ($this->_logger instanceof Zend_Log) {
- $this->_logger->debug(__METHOD__ . '::' . __LINE__ . " no more open transactions in queue commiting all transactionables");
- }
-
foreach ($this->_openTransactionables as $transactionableIdx => $transactionable) {
- if ($transactionable instanceof kolab_sync_db) {
- $transactionable->commit();
- //setAutocommit($transactionable,true);
+ if ($transactionable instanceof rcube_db) {
+ $transactionable->endTransaction();
}
}
$this->_openTransactionables = array();
- $this->_openTransactions = array();
+ $this->_openTransactions = array();
}
else {
if ($this->_logger instanceof Zend_Log) {
@@ -182,13 +168,12 @@ class kolab_sync_transaction_manager implements Syncroton_TransactionManagerInte
}
foreach ($this->_openTransactionables as $transactionable) {
- if ($transactionable instanceof kolab_sync_db) {
- $transactionable->rollBack();
- //setAutocommit($transactionable,true);
+ if ($transactionable instanceof rcube_db) {
+ $transactionable->rollbackTransaction();
}
}
$this->_openTransactionables = array();
- $this->_openTransactions = array();
+ $this->_openTransactions = array();
}
}
commit 18dc3e4ae07b77dba761e0712571aa5f6bed4187
Author: Aleksander Machniak <machniak at kolabsys.com>
Date: Thu Sep 18 11:25:42 2014 +0200
Add Oracle drivers
diff --git a/lib/ext/Roundcube/rcube_db.php b/lib/ext/Roundcube/rcube_db.php
index a46df97..5a2ad03 100644
--- a/lib/ext/Roundcube/rcube_db.php
+++ b/lib/ext/Roundcube/rcube_db.php
@@ -50,6 +50,7 @@ class rcube_db
);
const DEBUG_LINE_LENGTH = 4096;
+ const DEFAULT_QUOTE = '`';
/**
* Factory, returns driver-specific instance of the class
@@ -68,6 +69,7 @@ class rcube_db
'sybase' => 'mssql',
'dblib' => 'mssql',
'mysqli' => 'mysql',
+ 'oci' => 'oracle',
);
$driver = isset($driver_map[$driver]) ? $driver_map[$driver] : $driver;
@@ -128,6 +130,20 @@ class rcube_db
return $this->dbh;
}
+ // connect to database
+ if ($dbh = $this->conn_create($dsn)) {
+ $this->dbh = $dbh;
+ $this->dbhs[$mode] = $dbh;
+ $this->db_mode = $mode;
+ $this->db_connected = true;
+ }
+ }
+
+ /**
+ * Create PDO connection
+ */
+ protected function conn_create($dsn)
+ {
// Get database specific connection options
$dsn_string = $this->dsn_string($dsn);
$dsn_options = $this->dsn_options($dsn);
@@ -149,6 +165,8 @@ class rcube_db
// don't throw exceptions or warnings
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
+
+ $this->conn_configure($dsn, $dbh);
}
catch (Exception $e) {
$this->db_error = true;
@@ -161,11 +179,7 @@ class rcube_db
return null;
}
- $this->dbh = $dbh;
- $this->dbhs[$mode] = $dbh;
- $this->db_mode = $mode;
- $this->db_connected = true;
- $this->conn_configure($dsn, $dbh);
+ return $dbh;
}
/**
@@ -237,8 +251,12 @@ class rcube_db
// Read or write ?
$mode = preg_match('/^(select|show|set)/i', $query) ? 'r' : 'w';
+ $start = '[' . $this->options['identifier_start'] . self::DEFAULT_QUOTE . ']';
+ $end = '[' . $this->options['identifier_end'] . self::DEFAULT_QUOTE . ']';
+ $regex = '/(?:^|\s)(from|update|into|join)\s+'.$start.'?([a-z0-9._]+)'.$end.'?\s+/i';
+
// find tables involved in this query
- if (preg_match_all('/(?:^|\s)(from|update|into|join)\s+'.$this->options['identifier_start'].'?([a-z0-9._]+)'.$this->options['identifier_end'].'?\s+/i', $query, $matches, PREG_SET_ORDER)) {
+ if (preg_match_all($regex, $query, $matches, PREG_SET_ORDER)) {
foreach ($matches as $m) {
$table = $m[2];
@@ -407,6 +425,9 @@ class rcube_db
$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;
@@ -430,10 +451,19 @@ class rcube_db
$query = str_replace('??', '?', $query);
$query = rtrim($query, " \t\n\r\0\x0B;");
+ // log query
$this->debug($query);
+ return $this->query_execute($query);
+ }
+
+ /**
+ * Query execution
+ */
+ protected function query_execute($query)
+ {
// destroy reference to previous result, required for SQLite driver (#1488874)
- $this->last_result = null;
+ $this->last_result = null;
$this->db_error_msg = null;
// send query
@@ -443,9 +473,52 @@ class rcube_db
$result = $this->handle_error($query);
}
- $this->last_result = $result;
+ return $this->last_result = $result;
+ }
- return $result;
+ /**
+ * Parse SQL query and replace identifier quoting
+ *
+ * @param string $query SQL query
+ *
+ * @return string SQL query
+ */
+ protected function query_parse($query)
+ {
+ $start = $this->options['identifier_start'];
+ $end = $this->options['identifier_end'];
+ $quote = self::DEFAULT_QUOTE;
+
+ if ($start == $quote) {
+ return $query;
+ }
+
+ $pos = 0;
+ $in = false;
+
+ while ($pos = strpos($query, $quote, $pos)) {
+ if ($query[$pos+1] == $quote) { // skip escaped quote
+ $pos += 2;
+ }
+ else {
+ if ($in) {
+ $q = $end;
+ $in = false;
+ }
+ else {
+ $q = $start;
+ $in = true;
+ }
+
+ $query = substr_replace($query, $q, $pos, 1);
+ $pos++;
+ }
+ }
+
+ // replace escaped quote back to normal, see self::quote()
+ $query = str_replace($quote.$quote, $quote, $query);
+
+ return $query;
}
/**
@@ -482,7 +555,9 @@ class rcube_db
public function affected_rows($result = null)
{
if ($result || ($result === null && ($result = $this->last_result))) {
- return $result->rowCount();
+ if ($result !== true) {
+ return $result->rowCount();
+ }
}
return 0;
@@ -498,7 +573,7 @@ class rcube_db
*/
public function num_rows($result = null)
{
- if ($result || ($result === null && ($result = $this->last_result))) {
+ if (($result || ($result === null && ($result = $this->last_result))) && $result !== true) {
// repeat query with SELECT COUNT(*) ...
if (preg_match('/^SELECT\s+(?:ALL\s+|DISTINCT\s+)?(?:.*?)\s+FROM\s+(.*)$/ims', $result->queryString, $m)) {
$query = $this->dbh->query('SELECT COUNT(*) FROM ' . $m[1], PDO::FETCH_NUM);
@@ -574,7 +649,9 @@ class rcube_db
protected function _fetch_row($result, $mode)
{
if ($result || ($result === null && ($result = $this->last_result))) {
- return $result->fetch($mode);
+ if ($result !== true) {
+ return $result->fetch($mode);
+ }
}
return false;
@@ -644,6 +721,63 @@ class rcube_db
}
/**
+ * 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->dbh->beginTransaction();
+ }
+
+ /**
+ * Commit transaction
+ *
+ * @return bool True on success, False on failure
+ */
+ public function endTransaction()
+ {
+ $this->db_connect('w', true);
+
+ // check connection before proceeding
+ if (!$this->is_connected()) {
+ return $this->last_result = false;
+ }
+
+ $this->debug('COMMIT TRANSACTION');
+
+ return $this->last_result = $this->dbh->commit();
+ }
+
+ /**
+ * 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');
+
+ return $this->last_result = $this->dbh->rollBack();
+ }
+
+ /**
* Formats input so it can be safely used in a query
*
* @param mixed $input Value to quote
@@ -676,8 +810,13 @@ class rcube_db
'bool' => PDO::PARAM_BOOL,
'integer' => PDO::PARAM_INT,
);
+
$type = isset($map[$type]) ? $map[$type] : PDO::PARAM_STR;
- return strtr($this->dbh->quote($input, $type), array('?' => '??')); // escape ?
+
+ return strtr($this->dbh->quote($input, $type),
+ // escape ? and `
+ array('?' => '??', self::DEFAULT_QUOTE => self::DEFAULT_QUOTE.self::DEFAULT_QUOTE)
+ );
}
return 'NULL';
@@ -916,15 +1055,20 @@ class rcube_db
/**
* Return correct name for a specific database table
*
- * @param string $table Table name
+ * @param string $table Table name
+ * @param bool $quoted Quote table identifier
*
* @return string Translated table name
*/
- public function table_name($table)
+ public function table_name($table, $quoted = false)
{
// add prefix to the table name if configured
if (($prefix = $this->options['table_prefix']) && strpos($table, $prefix) !== 0) {
- return $prefix . $table;
+ $table = $prefix . $table;
+ }
+
+ if ($quoted) {
+ $table = $this->quote_identifier($table);
}
return $table;
diff --git a/lib/ext/Roundcube/rcube_db_mysql.php b/lib/ext/Roundcube/rcube_db_mysql.php
index e6417cc..400813d 100644
--- a/lib/ext/Roundcube/rcube_db_mysql.php
+++ b/lib/ext/Roundcube/rcube_db_mysql.php
@@ -38,13 +38,6 @@ class rcube_db_mysql extends rcube_db
*/
public function __construct($db_dsnw, $db_dsnr = '', $pconn = false)
{
- if (version_compare(PHP_VERSION, '5.3.0', '<')) {
- rcube::raise_error(array('code' => 600, 'type' => 'db',
- 'line' => __LINE__, 'file' => __FILE__,
- 'message' => "MySQL driver requires PHP >= 5.3, current version is " . PHP_VERSION),
- true, true);
- }
-
parent::__construct($db_dsnw, $db_dsnr, $pconn);
// SQL identifiers quoting
diff --git a/lib/ext/Roundcube/rcube_db_oci8.php b/lib/ext/Roundcube/rcube_db_oci8.php
new file mode 100644
index 0000000..a55ba04
--- /dev/null
+++ b/lib/ext/Roundcube/rcube_db_oci8.php
@@ -0,0 +1,534 @@
+<?php
+
+/**
+ +-----------------------------------------------------------------------+
+ | This file is part of the Roundcube Webmail client |
+ | Copyright (C) 2011-2014, Kolab Systems AG |
+ | |
+ | Licensed under the GNU General Public License version 3 or |
+ | any later version with exceptions for skins & plugins. |
+ | See the README file for a full license statement. |
+ | |
+ | PURPOSE: |
+ | Database wrapper class that implements database functions |
+ | for Oracle database using OCI8 extension |
+ +-----------------------------------------------------------------------+
+ | Author: Aleksander Machniak <machniak at kolabsys.com> |
+ +-----------------------------------------------------------------------+
+*/
+
+/**
+ * Database independent query interface
+ *
+ * @package Framework
+ * @subpackage Database
+ */
+class rcube_db_oci8 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
+ *
+ * @param array $dsn DSN for DB connections
+ * @param PDO $dbh Connection handler
+ */
+ protected function conn_configure($dsn, $dbh)
+ {
+ $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 the query
+ */
+ protected function query_execute($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 (!@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;
+ }
+
+ /**
+ * Get last inserted record ID
+ *
+ * @param string $table Table name (to find the incremented sequence)
+ *
+ * @return mixed ID or false on failure
+ */
+ public function insert_id($table = null)
+ {
+ if (!$this->db_connected || $this->db_mode == 'r' || empty($table)) {
+ return false;
+ }
+
+ $sequence = $this->quote_identifier($this->sequence_name($table));
+ $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);
+ }
+
+ /**
+ * 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;
+ }
+
+ /**
+ * Formats input so it can be safely used in a query
+ * PDO_OCI does not implement quote() method
+ *
+ * @param mixed $input Value to quote
+ * @param string $type Type of data (integer, bool, ident)
+ *
+ * @return string Quoted/converted string for use in query
+ */
+ public function quote($input, $type = null)
+ {
+ // handle int directly for better performance
+ if ($type == 'integer' || $type == 'int') {
+ return intval($input);
+ }
+
+ if (is_null($input)) {
+ return 'NULL';
+ }
+
+ if ($type == 'ident') {
+ return $this->quote_identifier($input);
+ }
+
+ switch ($type) {
+ case 'bool':
+ case 'integer':
+ return intval($input);
+ default:
+ return "'" . strtr($input, array(
+ '?' => '??',
+ "'" => "''",
+ rcube_db::DEFAULT_QUOTE => rcube_db::DEFAULT_QUOTE . rcube_db::DEFAULT_QUOTE
+ )) . "'";
+ }
+ }
+
+ /**
+ * Return correct name for a specific database sequence
+ *
+ * @param string $table Table name
+ *
+ * @return string Translated sequence name
+ */
+ protected function sequence_name($table)
+ {
+ // Note: we support only one sequence per table
+ // Note: The sequence name must be <table_name>_seq
+ $sequence = $table . '_seq';
+
+ // modify sequence name if prefix is configured
+ if ($prefix = $this->options['table_prefix']) {
+ return $prefix . $sequence;
+ }
+
+ return $sequence;
+ }
+
+ /**
+ * Return SQL statement for case insensitive LIKE
+ *
+ * @param string $column Field name
+ * @param string $value Search value
+ *
+ * @return string SQL statement to use in query
+ */
+ public function ilike($column, $value)
+ {
+ return 'UPPER(' . $this->quote_identifier($column) . ') LIKE UPPER(' . $this->quote($value) . ')';
+ }
+
+ /**
+ * Return SQL function for current time and date
+ *
+ * @param int $interval Optional interval (in seconds) to add/subtract
+ *
+ * @return string SQL function to use in query
+ */
+ public function now($interval = 0)
+ {
+ if ($interval) {
+ $interval = intval($interval);
+ return "current_timestamp + INTERVAL '$interval' SECOND";
+ }
+
+ return "current_timestamp";
+ }
+
+ /**
+ * Return SQL statement to convert a field value into a unix timestamp
+ *
+ * @param string $field Field name
+ *
+ * @return string SQL statement to use in query
+ * @deprecated
+ */
+ public function unixtimestamp($field)
+ {
+ return "(($field - to_date('1970-01-01','YYYY-MM-DD')) * 60 * 60 * 24)";
+ }
+
+ /**
+ * Adds TOP (LIMIT,OFFSET) clause to the query
+ *
+ * @param string $query SQL query
+ * @param int $limit Number of rows
+ * @param int $offset Offset
+ *
+ * @return string SQL query
+ */
+ protected function set_limit($query, $limit = 0, $offset = 0)
+ {
+ $limit = intval($limit);
+ $offset = intval($offset);
+ $end = $offset + $limit;
+
+ // @TODO: Oracle 12g has better OFFSET support
+
+ if (!$offset) {
+ $query = "SELECT * FROM ($query) a WHERE rownum <= $end";
+ }
+ else {
+ $query = "SELECT * FROM (SELECT a.*, rownum as rn FROM ($query) a WHERE rownum <= $end) b WHERE rn > $offset";
+ }
+
+ return $query;
+ }
+
+ /**
+ * Parse SQL file and fix table names according to table prefix
+ */
+ protected function fix_table_names($sql)
+ {
+ if (!$this->options['table_prefix']) {
+ return $sql;
+ }
+
+ $sql = parent::fix_table_names($sql);
+
+ // replace sequence names, and other Oracle-specific commands
+ $sql = preg_replace_callback('/((SEQUENCE ["]?)([^" \r\n]+)/',
+ array($this, 'fix_table_names_callback'),
+ $sql
+ );
+
+ $sql = preg_replace_callback(
+ '/([ \r\n]+["]?)([^"\' \r\n\.]+)(["]?\.nextval)/',
+ array($this, 'fix_table_names_seq_callback'),
+ $sql
+ );
+
+ return $sql;
+ }
+
+ /**
+ * Preg_replace callback for fix_table_names()
+ */
+ protected function fix_table_names_seq_callback($matches)
+ {
+ return $matches[1] . $this->options['table_prefix'] . $matches[2] . $matches[3];
+ }
+
+ /**
+ * Returns connection options from DSN array
+ */
+ protected function dsn_options($dsn)
+ {
+ $params = array();
+
+ if ($dsn['hostspec']) {
+ $host = $dsn['hostspec'];
+ if ($dsn['port']) {
+ $host .= ':' . $dsn['port'];
+ }
+
+ $params['database'] = $host . '/' . $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;
+ }
+ }
+ }
+
+ 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);
+
+ // 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 $this->last_result = $this->dbh->rollBack();
+ }
+}
diff --git a/lib/ext/Roundcube/rcube_db_oracle.php b/lib/ext/Roundcube/rcube_db_oracle.php
new file mode 100644
index 0000000..ddd351e
--- /dev/null
+++ b/lib/ext/Roundcube/rcube_db_oracle.php
@@ -0,0 +1,263 @@
+<?php
+
+/**
+ +-----------------------------------------------------------------------+
+ | This file is part of the Roundcube Webmail client |
+ | Copyright (C) 2011-2014, Kolab Systems AG |
+ | |
+ | Licensed under the GNU General Public License version 3 or |
+ | any later version with exceptions for skins & plugins. |
+ | See the README file for a full license statement. |
+ | |
+ | PURPOSE: |
+ | Database wrapper class that implements PHP PDO functions |
+ | for Oracle database |
+ +-----------------------------------------------------------------------+
+ | Author: Aleksander Machniak <machniak at kolabsys.com> |
+ +-----------------------------------------------------------------------+
+*/
+
+/**
+ * Database independent query interface
+ * This is a wrapper for the PHP PDO
+ *
+ * @package Framework
+ * @subpackage Database
+ */
+class rcube_db_oracle extends rcube_db
+{
+ public $db_provider = 'oracle';
+
+ /**
+ * Driver-specific configuration of database connection
+ *
+ * @param array $dsn DSN for DB connections
+ * @param PDO $dbh Connection handler
+ */
+ 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'");
+ }
+
+ /**
+ * Get last inserted record ID
+ *
+ * @param string $table Table name (to find the incremented sequence)
+ *
+ * @return mixed ID or false on failure
+ */
+ public function insert_id($table = null)
+ {
+ if (!$this->db_connected || $this->db_mode == 'r' || empty($table)) {
+ return false;
+ }
+
+ $sequence = $this->quote_identifier($this->sequence_name($table));
+ $result = $dbh->query("SELECT $sequence.currval FROM dual");
+
+ return $result ? $result->fetchColumn() : false;
+ }
+
+ /**
+ * Formats input so it can be safely used in a query
+ * PDO_OCI does not implement quote() method
+ *
+ * @param mixed $input Value to quote
+ * @param string $type Type of data (integer, bool, ident)
+ *
+ * @return string Quoted/converted string for use in query
+ */
+ public function quote($input, $type = null)
+ {
+ // handle int directly for better performance
+ if ($type == 'integer' || $type == 'int') {
+ return intval($input);
+ }
+
+ if (is_null($input)) {
+ return 'NULL';
+ }
+
+ if ($type == 'ident') {
+ return $this->quote_identifier($input);
+ }
+
+ switch ($type) {
+ case 'bool':
+ case 'integer':
+ return intval($input);
+ default:
+ return "'" . strtr($input, array(
+ '?' => '??',
+ "'" => "''",
+ rcube_db::DEFAULT_QUOTE => rcube_db::DEFAULT_QUOTE . rcube_db::DEFAULT_QUOTE
+ )) . "'";
+ }
+ }
+
+ /**
+ * Return correct name for a specific database sequence
+ *
+ * @param string $table Table name
+ *
+ * @return string Translated sequence name
+ */
+ protected function sequence_name($table)
+ {
+ // Note: we support only one sequence per table
+ // Note: The sequence name must be <table_name>_seq
+ $sequence = $table . '_seq';
+
+ // modify sequence name if prefix is configured
+ if ($prefix = $this->options['table_prefix']) {
+ return $prefix . $sequence;
+ }
+
+ return $sequence;
+ }
+
+ /**
+ * Return SQL statement for case insensitive LIKE
+ *
+ * @param string $column Field name
+ * @param string $value Search value
+ *
+ * @return string SQL statement to use in query
+ */
+ public function ilike($column, $value)
+ {
+ return 'UPPER(' . $this->quote_identifier($column) . ') LIKE UPPER(' . $this->quote($value) . ')';
+ }
+
+ /**
+ * Return SQL function for current time and date
+ *
+ * @param int $interval Optional interval (in seconds) to add/subtract
+ *
+ * @return string SQL function to use in query
+ */
+ public function now($interval = 0)
+ {
+ if ($interval) {
+ $interval = intval($interval);
+ return "current_timestamp + INTERVAL '$interval' SECOND";
+ }
+
+ return "current_timestamp";
+ }
+
+ /**
+ * Return SQL statement to convert a field value into a unix timestamp
+ *
+ * @param string $field Field name
+ *
+ * @return string SQL statement to use in query
+ * @deprecated
+ */
+ public function unixtimestamp($field)
+ {
+ return "(($field - to_date('1970-01-01','YYYY-MM-DD')) * 60 * 60 * 24)";
+ }
+
+ /**
+ * Adds TOP (LIMIT,OFFSET) clause to the query
+ *
+ * @param string $query SQL query
+ * @param int $limit Number of rows
+ * @param int $offset Offset
+ *
+ * @return string SQL query
+ */
+ protected function set_limit($query, $limit = 0, $offset = 0)
+ {
+ $limit = intval($limit);
+ $offset = intval($offset);
+ $end = $offset + $limit;
+
+ // @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)));
+ }
+ 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 = 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;
+ }
+
+ /**
+ * Parse SQL file and fix table names according to table prefix
+ */
+ protected function fix_table_names($sql)
+ {
+ if (!$this->options['table_prefix']) {
+ return $sql;
+ }
+
+ $sql = parent::fix_table_names($sql);
+
+ // replace sequence names, and other Oracle-specific commands
+ $sql = preg_replace_callback('/((SEQUENCE ["]?)([^" \r\n]+)/',
+ array($this, 'fix_table_names_callback'),
+ $sql
+ );
+
+ $sql = preg_replace_callback(
+ '/([ \r\n]+["]?)([^"\' \r\n\.]+)(["]?\.nextval)/',
+ array($this, 'fix_table_names_seq_callback'),
+ $sql
+ );
+
+ return $sql;
+ }
+
+ /**
+ * Preg_replace callback for fix_table_names()
+ */
+ protected function fix_table_names_seq_callback($matches)
+ {
+ return $matches[1] . $this->options['table_prefix'] . $matches[2] . $matches[3];
+ }
+
+ /**
+ * Returns PDO DSN string from DSN array
+ */
+ protected function dsn_string($dsn)
+ {
+ $params = array();
+ $result = 'oci:';
+
+ if ($dsn['hostspec']) {
+ $host = $dsn['hostspec'];
+ if ($dsn['port']) {
+ $host .= ':' . $dsn['port'];
+ }
+
+ $dsn['database'] = $host . '/' . $dsn['database'];
+ }
+
+ if ($dsn['database']) {
+ $params[] = 'dbname=' . $dsn['database'];
+ }
+
+ $params['charset'] = 'UTF8';
+
+ if (!empty($params)) {
+ $result .= implode(';', $params);
+ }
+
+ return $result;
+ }
+}
commit 092232c129fc7c9743057d169f6178ebb3d99871
Author: Aleksander Machniak <machniak at kolabsys.com>
Date: Mon Sep 15 12:28:09 2014 +0200
Initial DDL for Oracle
diff --git a/docs/SQL/oracle.initial.sql b/docs/SQL/oracle.initial.sql
new file mode 100644
index 0000000..cb78340
--- /dev/null
+++ b/docs/SQL/oracle.initial.sql
@@ -0,0 +1,104 @@
+CREATE TABLE "syncroton_policy" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "name" varchar(255) NOT NULL,
+ "description" varchar(255) DEFAULT NULL,
+ "policy_key" varchar(64) NOT NULL,
+ "json_policy" clob DEFAULT NULL
+);
+
+CREATE TABLE "syncroton_device" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "deviceid" varchar(64) NOT NULL,
+ "devicetype" varchar(64) NOT NULL,
+ "owner_id" varchar(40) NOT NULL,
+ "acsversion" varchar(40) NOT NULL,
+ "policykey" varchar(64) DEFAULT NULL,
+ "policy_id" varchar(40) DEFAULT NULL,
+ "useragent" varchar(255) DEFAULT NULL,
+ "imei" varchar(255) DEFAULT NULL,
+ "model" varchar(255) DEFAULT NULL,
+ "friendlyname" varchar(255) DEFAULT NULL,
+ "os" varchar(255) DEFAULT NULL,
+ "oslanguage" varchar(255) DEFAULT NULL,
+ "phonenumber" varchar(255) DEFAULT NULL,
+ "pinglifetime" integer DEFAULT NULL,
+ "remotewipe" integer DEFAULT 0,
+ "pingfolder" clob DEFAULT NULL,
+ "lastsynccollection" clob DEFAULT NULL,
+ "lastping" timestamp DEFAULT NULL,
+ "contactsfilter_id" varchar(40) DEFAULT NULL,
+ "calendarfilter_id" varchar(40) DEFAULT NULL,
+ "tasksfilter_id" varchar(40) DEFAULT NULL,
+ "emailfilter_id" varchar(40) DEFAULT NULL
+);
+
+CREATE UNIQUE INDEX "syncroton_device_owner_id_idx" ON "syncroton_device" ("owner_id", "deviceid");
+
+
+CREATE TABLE "syncroton_folder" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "device_id" varchar(40) NOT NULL
+ REFERENCES "syncroton_device" ("id") ON DELETE CASCADE,
+ "class" varchar(64) NOT NULL,
+ "folderid" varchar(254) NOT NULL,
+ "parentid" varchar(254) DEFAULT NULL,
+ "displayname" varchar(254) NOT NULL,
+ "type" integer NOT NULL,
+ "creation_time" timestamp NOT NULL,
+ "lastfiltertype" integer DEFAULT NULL,
+ "supportedfields" clob DEFAULT NULL
+);
+
+CREATE UNIQUE INDEX "syncroton_folder_device_id_idx" ON "syncroton_folder" ("device_id", "class", "folderid");
+
+
+CREATE TABLE "syncroton_synckey" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "device_id" varchar(40) NOT NULL
+ REFERENCES "syncroton_device" ("id") ON DELETE CASCADE,
+ "type" varchar(64) DEFAULT NULL,
+ "counter" integer DEFAULT 0 NOT NULL,
+ "lastsync" timestamp DEFAULT NULL,
+ "pendingdata" clob
+);
+
+CREATE UNIQUE INDEX "syncroton_synckey_device_idx" ON "syncroton_synckey" ("device_id", "type", "counter");
+
+CREATE TABLE "syncroton_content" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "device_id" varchar(40) NOT NULL
+ REFERENCES "syncroton_device" ("id") ON DELETE CASCADE,
+ "folder_id" varchar(40) NOT NULL,
+ "contentid" varchar(128) NOT NULL,
+ "creation_time" timestamp DEFAULT NULL,
+ "creation_synckey" integer NOT NULL,
+ "is_deleted" smallint DEFAULT 0
+);
+
+CREATE UNIQUE INDEX "syncroton_content_device_idx" ON "syncroton_content" ("device_id", "folder_id", "contentid");
+
+CREATE TABLE "syncroton_data" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "class" varchar(40) NOT NULL,
+ "folder_id" varchar(40) NOT NULL,
+ "data" clob
+);
+
+CREATE TABLE "syncroton_data_folder" (
+ "id" varchar(40) NOT NULL PRIMARY KEY,
+ "type" integer NOT NULL,
+ "name" varchar(255) NOT NULL,
+ "owner_id" varchar(40) NOT NULL,
+ "parent_id" varchar(40) DEFAULT NULL
+);
+
+CREATE TABLE "syncroton_modseq" (
+ "device_id" varchar(40) NOT NULL
+ REFERENCES "syncroton_device" ("id") ON DELETE CASCADE,
+ "folder_id" varchar(40) NOT NULL,
+ "synctime" timestamp NOT NULL,
+ "data" clob,
+ PRIMARY KEY ("device_id", "folder_id", "synctime")
+);
+
+INSERT INTO "system" ("name", "value") VALUES ('syncroton-version', '2013100800');
commit 33a41c4a338df09dd9480fca2dbdf2b47d0f6b0f
Author: Aleksander Machniak <machniak at kolabsys.com>
Date: Mon Sep 15 12:27:50 2014 +0200
Use consistent column/table quoting in sql queries
diff --git a/lib/kolab_sync_backend.php b/lib/kolab_sync_backend.php
index e288530..b88c984 100644
--- a/lib/kolab_sync_backend.php
+++ b/lib/kolab_sync_backend.php
@@ -828,7 +828,7 @@ class kolab_sync_backend
$data = json_encode($data);
$db->set_option('ignore_key_errors', true);
- $db->query("INSERT INTO syncroton_modseq (device_id, folder_id, synctime, data)"
+ $db->query("INSERT INTO `syncroton_modseq` (`device_id`, `folder_id`, `synctime`, `data`)"
." VALUES (?, ?, ?, ?)",
$deviceid, $folderid, $synctime, $data);
$db->set_option('ignore_key_errors', false);
@@ -845,9 +845,9 @@ class kolab_sync_backend
$rcube = rcube::get_instance();
$db = $rcube->get_dbh();
- $db->limitquery("SELECT data, synctime FROM syncroton_modseq"
- ." WHERE device_id = ? AND folder_id = ? AND synctime <= ?"
- ." ORDER BY synctime DESC",
+ $db->limitquery("SELECT `data`, `synctime` FROM `syncroton_modseq`"
+ ." WHERE `device_id` = ? AND `folder_id` = ? AND `synctime` <= ?"
+ ." ORDER BY `synctime` DESC",
0, 1, $deviceid, $folderid, $synctime);
if ($row = $db->fetch_assoc()) {
@@ -857,8 +857,8 @@ class kolab_sync_backend
}
// Cleanup: remove all records except the current one
- $db->query("DELETE FROM syncroton_modseq"
- ." WHERE device_id = ? AND folder_id = ? AND synctime <> ?",
+ $db->query("DELETE FROM `syncroton_modseq`"
+ ." WHERE `device_id` = ? AND `folder_id` = ? AND `synctime` <> ?",
$deviceid, $folderid, $synctime);
}
diff --git a/lib/kolab_sync_backend_common.php b/lib/kolab_sync_backend_common.php
index 49bb62f..55e2f3b 100644
--- a/lib/kolab_sync_backend_common.php
+++ b/lib/kolab_sync_backend_common.php
@@ -99,7 +99,7 @@ class kolab_sync_backend_common implements Syncroton_Backend_IBackend
$insert[$this->db->quote_identifier($key)] = $this->db->quote($value);
}
- $this->db->query('INSERT INTO ' . $this->table_name
+ $this->db->query('INSERT INTO `' . $this->table_name . '`'
. ' (' . implode(', ', array_keys($insert)) . ')' . ' VALUES(' . implode(', ', $insert) . ')');
if (!$this->db->insert_id($this->table_name)) {
@@ -121,7 +121,7 @@ class kolab_sync_backend_common implements Syncroton_Backend_IBackend
$id = $id instanceof $this->interface_name ? $id->id : $id;
if ($id) {
- $select = $this->db->query('SELECT * FROM ' . $this->table_name . ' WHERE id = ?', array($id));
+ $select = $this->db->query('SELECT * FROM `' . $this->table_name . '` WHERE `id` = ?', array($id));
$data = $this->db->fetch_assoc($select);
}
@@ -147,7 +147,7 @@ class kolab_sync_backend_common implements Syncroton_Backend_IBackend
return false;
}
- $result = $this->db->query('DELETE FROM ' . $this->table_name .' WHERE id = ?', array($id));
+ $result = $this->db->query('DELETE FROM `' . $this->table_name .'` WHERE `id` = ?', array($id));
return (bool) $this->db->affected_rows($result);
}
@@ -173,8 +173,8 @@ class kolab_sync_backend_common implements Syncroton_Backend_IBackend
$set[] = $this->db->quote_identifier($key) . ' = ' . $this->db->quote($value);
}
- $this->db->query('UPDATE ' . $this->table_name . ' SET ' . implode(', ', $set)
- . ' WHERE ' . $this->db->quote_identifier('id') . ' = ' . $this->db->quote($object->id));
+ $this->db->query('UPDATE `' . $this->table_name . '` SET ' . implode(', ', $set)
+ . ' WHERE `id` = ' . $this->db->quote($object->id));
return $object;
}
diff --git a/lib/kolab_sync_backend_content.php b/lib/kolab_sync_backend_content.php
index 84702e6..793c136 100644
--- a/lib/kolab_sync_backend_content.php
+++ b/lib/kolab_sync_backend_content.php
@@ -42,7 +42,7 @@ class kolab_sync_backend_content extends kolab_sync_backend_common implements Sy
{
$id = $id instanceof Syncroton_Model_IContent ? $id->id : $id;
- $result = $this->db->query('UPDATE ' . $this->table_name . ' SET is_deleted = 1 WHERE id = ?', array($id));
+ $result = $this->db->query("UPDATE `{$this->table_name}` SET `is_deleted` = 1 WHERE `id` = ?", array($id));
if ($result = (bool) $this->db->affected_rows($result)) {
unset($this->cache['content_folderstate']);
@@ -67,7 +67,7 @@ class kolab_sync_backend_content extends kolab_sync_backend_common implements Sy
$where[] = $this->db->quote_identifier('contentid') . ' = ' . $this->db->quote($_contentId);
$where[] = $this->db->quote_identifier('is_deleted') . ' = 0';
- $select = $this->db->query('SELECT * FROM ' . $this->table_name .' WHERE ' . implode(' AND ', $where));
+ $select = $this->db->query("SELECT * FROM `{$this->table_name}` WHERE " . implode(' AND ', $where));
$state = $this->db->fetch_assoc($select);
if (empty($state)) {
@@ -100,7 +100,7 @@ class kolab_sync_backend_content extends kolab_sync_backend_common implements Sy
$where[] = $this->db->quote_identifier('folder_id') . ' = ' . $this->db->quote($folderId);
$where[] = $this->db->quote_identifier('is_deleted') . ' = 0';
- $select = $this->db->query('SELECT contentid FROM ' . $this->table_name .' WHERE ' . implode(' AND ', $where));
+ $select = $this->db->query("SELECT `contentid` FROM `{$this->table_name}` WHERE " . implode(' AND ', $where));
$result = array();
while ($state = $this->db->fetch_assoc($select)) {
@@ -127,6 +127,6 @@ class kolab_sync_backend_content extends kolab_sync_backend_common implements Sy
$where[] = $this->db->quote_identifier('device_id') . ' = ' . $this->db->quote($deviceId);
$where[] = $this->db->quote_identifier('folder_id') . ' = ' . $this->db->quote($folderId);
- $this->db->query('DELETE FROM ' . $this->table_name .' WHERE ' . implode(' AND ', $where));
+ $this->db->query("DELETE FROM `{$this->table_name}` WHERE " . implode(' AND ', $where));
}
}
diff --git a/lib/kolab_sync_backend_folder.php b/lib/kolab_sync_backend_folder.php
index f2a846c..982b35d 100644
--- a/lib/kolab_sync_backend_folder.php
+++ b/lib/kolab_sync_backend_folder.php
@@ -42,7 +42,7 @@ class kolab_sync_backend_folder extends kolab_sync_backend_common implements Syn
$where[] = $this->db->quote_identifier('device_id') . ' = ' . $this->db->quote($device_id);
- $this->db->query('DELETE FROM ' . $this->table_name .' WHERE ' . implode(' AND ', $where));
+ $this->db->query('DELETE FROM `' . $this->table_name . '` WHERE ' . implode(' AND ', $where));
}
/**
@@ -60,7 +60,7 @@ class kolab_sync_backend_folder extends kolab_sync_backend_common implements Syn
$where[] = $this->db->quote_identifier('device_id') . ' = ' . $this->db->quote($device_id);
$where[] = $this->db->quote_identifier('class') . ' = ' . $this->db->quote($class);
- $select = $this->db->query('SELECT * FROM ' . $this->table_name .' WHERE ' . implode(' AND ', $where));
+ $select = $this->db->query('SELECT * FROM `' . $this->table_name .'` WHERE ' . implode(' AND ', $where));
$result = array();
while ($folder = $this->db->fetch_assoc($select)) {
@@ -85,7 +85,7 @@ class kolab_sync_backend_folder extends kolab_sync_backend_common implements Syn
$where[] = $this->db->quote_identifier('device_id') . ' = ' . $this->db->quote($device_id);
$where[] = $this->db->quote_identifier('folderid') . ' = ' . $this->db->quote($folderid);
- $select = $this->db->query('SELECT * FROM ' . $this->table_name .' WHERE ' . implode(' AND ', $where));
+ $select = $this->db->query('SELECT * FROM `' . $this->table_name . '` WHERE ' . implode(' AND ', $where));
$folder = $this->db->fetch_assoc($select);
if (empty($folder)) {
diff --git a/lib/kolab_sync_backend_state.php b/lib/kolab_sync_backend_state.php
index f65ea6c..6d6b7c8 100644
--- a/lib/kolab_sync_backend_state.php
+++ b/lib/kolab_sync_backend_state.php
@@ -61,7 +61,7 @@ class kolab_sync_backend_state extends kolab_sync_backend_common implements Sync
$where[] = $this->db->quote_identifier('type') . ' = ' . $this->db->quote($state->type);
$where[] = $this->db->quote_identifier('counter') . ' <> ' . $this->db->quote($state->counter);
- $this->db->query('DELETE FROM ' . $this->table_name .' WHERE ' . implode(' AND ', $where));
+ $this->db->query("DELETE FROM `{$this->table_name}` WHERE " . implode(' AND ', $where));
}
/**
@@ -108,8 +108,8 @@ class kolab_sync_backend_state extends kolab_sync_backend_common implements Sync
$where[] = $this->db->quote_identifier('device_id') . ' = ' . $this->db->quote($device_id);
$where[] = $this->db->quote_identifier('type') . ' = ' . $this->db->quote($folder_id);
- $select = $this->db->limitquery('SELECT * FROM ' . $this->table_name . ' WHERE ' . implode(' AND ', $where)
- .' ORDER BY counter DESC', 0, 1);
+ $select = $this->db->limitquery("SELECT * FROM `{$this->table_name}` WHERE " . implode(' AND ', $where)
+ . " ORDER BY `counter` DESC", 0, 1);
$state = $this->db->fetch_assoc($select);
@@ -134,7 +134,7 @@ class kolab_sync_backend_state extends kolab_sync_backend_common implements Sync
$where[] = $this->db->quote_identifier('device_id') . ' = ' . $this->db->quote($device_id);
$where[] = $this->db->quote_identifier('type') . ' = ' . $this->db->quote($folder_id);
- $this->db->query('DELETE FROM ' . $this->table_name .' WHERE ' . implode(' AND ', $where));
+ $this->db->query("DELETE FROM `{$this->table_name}` WHERE " . implode(' AND ', $where));
}
/**
@@ -158,7 +158,7 @@ class kolab_sync_backend_state extends kolab_sync_backend_common implements Sync
$where['device_id'] = $this->db->quote_identifier('device_id') . ' = ' . $this->db->quote($device_id);
$where['type'] = $this->db->quote_identifier('type') . ' = ' . $this->db->quote($folder_id);
- $select = $this->db->query('SELECT * FROM ' . $this->table_name .' WHERE ' . implode(' AND ', $where));
+ $select = $this->db->query("SELECT * FROM `{$this->table_name}` WHERE " . implode(' AND ', $where));
while ($row = $this->db->fetch_assoc($select)) {
$states[$row['counter']] = $this->get_object($row);
@@ -181,17 +181,17 @@ class kolab_sync_backend_state extends kolab_sync_backend_common implements Sync
if ($next > $sync_key) {
$where['synckey'] = $this->db->quote_identifier('creation_synckey') . ' = ' . $this->db->quote($state->counter);
// undelete entries marked as deleted in syncroton_content table
- $this->db->query('UPDATE syncroton_content SET is_deleted = 0 WHERE ' . implode(' AND ', $where));
+ $this->db->query("UPDATE `syncroton_content` SET `is_deleted` = 0 WHERE " . implode(' AND ', $where));
// remove entries added during latest sync in syncroton_content table
unset($where['is_deleted']);
$where['synckey'] = $this->db->quote_identifier('creation_synckey') . ' > ' . $this->db->quote($state->counter);
- $this->db->query('DELETE FROM syncroton_content WHERE ' . implode(' AND ', $where));
+ $this->db->query("DELETE FROM `syncroton_content` WHERE " . implode(' AND ', $where));
}
else {
// finaly delete all entries marked for removal in syncroton_content table
- $this->db->query('DELETE FROM syncroton_content WHERE ' . implode(' AND ', $where));
+ $this->db->query("DELETE FROM `syncroton_content` WHERE " . implode(' AND ', $where));
}
// remove all other synckeys
More information about the commits
mailing list