Page MenuHomePhorge
Files F223149

No OneTemporary
Actions

View Options

diff --git a/SQL/oracle.initial.sql b/SQL/oracle.initial.sql
index 84f7a1f16..d32cc926f 100644
--- a/SQL/oracle.initial.sql
+++ b/SQL/oracle.initial.sql
@@ -1,221 +1,221 @@
-- Roundcube Webmail initial database structure
-- This was tested with Oracle 11g
CREATE TABLE "users" (
"user_id" integer PRIMARY KEY,
"username" varchar(128) NOT NULL,
"mail_host" varchar(128) NOT NULL,
"created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
"last_login" timestamp with time zone DEFAULT NULL,
"language" varchar(5),
"preferences" long DEFAULT NULL,
CONSTRAINT "users_username_key" UNIQUE ("username", "mail_host")
);
CREATE SEQUENCE "users_seq"
START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE TRIGGER "users_seq_trig"
BEFORE INSERT ON "users" FOR EACH ROW
BEGIN
:NEW."user_id" := "users_seq".nextval;
END;
-
+/
CREATE TABLE "session" (
"sess_id" varchar(128) NOT NULL PRIMARY KEY,
"created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
"changed" timestamp with time zone DEFAULT current_timestamp NOT NULL,
"ip" varchar(41) NOT NULL,
"vars" long NOT NULL
);
CREATE INDEX "session_changed_idx" ON "session" ("changed");
CREATE TABLE "identities" (
"identity_id" integer PRIMARY KEY,
"user_id" integer NOT NULL
REFERENCES "users" ("user_id") ON DELETE CASCADE,
"changed" timestamp with time zone DEFAULT current_timestamp NOT NULL,
"del" smallint DEFAULT 0 NOT NULL,
"standard" smallint DEFAULT 0 NOT NULL,
"name" varchar(128) NOT NULL,
"organization" varchar(128),
"email" varchar(128) NOT NULL,
"reply-to" varchar(128),
"bcc" varchar(128),
"signature" long,
"html_signature" integer DEFAULT 0 NOT NULL
);
CREATE INDEX "identities_user_id_idx" ON "identities" ("user_id", "del");
CREATE INDEX "identities_email_idx" ON "identities" ("email", "del");
CREATE SEQUENCE "identities_seq"
START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE TRIGGER "identities_seq_trig"
BEFORE INSERT ON "identities" FOR EACH ROW
BEGIN
:NEW."identity_id" := "identities_seq".nextval;
END;
-
+/
CREATE TABLE "contacts" (
"contact_id" integer PRIMARY KEY,
"user_id" integer NOT NULL
REFERENCES "users" ("user_id") ON DELETE CASCADE,
"changed" timestamp with time zone DEFAULT current_timestamp NOT NULL,
"del" smallint DEFAULT 0 NOT NULL,
"name" varchar(128) DEFAULT NULL,
"email" varchar(4000) DEFAULT NULL,
"firstname" varchar(128) DEFAULT NULL,
"surname" varchar(128) DEFAULT NULL,
"vcard" long,
"words" varchar(4000)
);
CREATE INDEX "contacts_user_id_idx" ON "contacts" ("user_id", "del");
CREATE SEQUENCE "contacts_seq"
START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE TRIGGER "contacts_seq_trig"
BEFORE INSERT ON "contacts" FOR EACH ROW
BEGIN
:NEW."contact_id" := "contacts_seq".nextval;
END;
-
+/
CREATE TABLE "contactgroups" (
"contactgroup_id" integer PRIMARY KEY,
"user_id" integer NOT NULL
REFERENCES "users" ("user_id") ON DELETE CASCADE,
"changed" timestamp with time zone DEFAULT current_timestamp NOT NULL,
"del" smallint DEFAULT 0 NOT NULL,
"name" varchar(128) NOT NULL
);
CREATE INDEX "contactgroups_user_id_idx" ON "contactgroups" ("user_id", "del");
CREATE SEQUENCE "contactgroups_seq"
START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE TRIGGER "contactgroups_seq_trig"
BEFORE INSERT ON "contactgroups" FOR EACH ROW
BEGIN
:NEW."contactgroup_id" := "contactgroups_seq".nextval;
END;
-
+/
CREATE TABLE "contactgroupmembers" (
"contactgroup_id" integer NOT NULL
REFERENCES "contactgroups" ("contactgroup_id") ON DELETE CASCADE,
"contact_id" integer NOT NULL
REFERENCES "contacts" ("contact_id") ON DELETE CASCADE,
"created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
PRIMARY KEY ("contactgroup_id", "contact_id")
);
CREATE INDEX "contactgroupmembers_idx" ON "contactgroupmembers" ("contact_id");
CREATE TABLE "cache" (
"user_id" integer NOT NULL
REFERENCES "users" ("user_id") ON DELETE CASCADE,
"cache_key" varchar(128) NOT NULL,
"created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
"expires" timestamp with time zone DEFAULT NULL,
"data" long NOT NULL
);
CREATE INDEX "cache_user_id_idx" ON "cache" ("user_id", "cache_key");
CREATE INDEX "cache_expires_idx" ON "cache" ("expires");
CREATE TABLE "cache_shared" (
"cache_key" varchar(255) NOT NULL,
"created" timestamp with time zone DEFAULT current_timestamp NOT NULL,
"expires" timestamp with time zone DEFAULT NULL,
"data" long NOT NULL
);
CREATE INDEX "cache_shared_cache_key_idx" ON "cache_shared" ("cache_key");
CREATE INDEX "cache_shared_expires_idx" ON "cache_shared" ("expires");
CREATE TABLE "cache_index" (
"user_id" integer NOT NULL
REFERENCES "users" ("user_id") ON DELETE CASCADE,
"mailbox" varchar(255) NOT NULL,
"expires" timestamp with time zone DEFAULT NULL,
"valid" smallint DEFAULT 0 NOT NULL,
"data" long NOT NULL,
PRIMARY KEY ("user_id", "mailbox")
);
CREATE INDEX "cache_index_expires_idx" ON "cache_index" ("expires");
CREATE TABLE "cache_thread" (
"user_id" integer NOT NULL
REFERENCES "users" ("user_id") ON DELETE CASCADE,
"mailbox" varchar(255) NOT NULL,
"expires" timestamp with time zone DEFAULT NULL,
"data" long NOT NULL,
PRIMARY KEY ("user_id", "mailbox")
);
CREATE INDEX "cache_thread_expires_idx" ON "cache_thread" ("expires");
CREATE TABLE "cache_messages" (
"user_id" integer NOT NULL
REFERENCES "users" ("user_id") ON DELETE CASCADE,
"mailbox" varchar(255) NOT NULL,
"uid" integer NOT NULL,
"expires" timestamp with time zone DEFAULT NULL,
"data" long NOT NULL,
"flags" integer DEFAULT 0 NOT NULL,
PRIMARY KEY ("user_id", "mailbox", "uid")
);
CREATE INDEX "cache_messages_expires_idx" ON "cache_messages" ("expires");
CREATE TABLE "dictionary" (
"user_id" integer DEFAULT NULL
REFERENCES "users" ("user_id") ON DELETE CASCADE,
"language" varchar(5) NOT NULL,
"data" long DEFAULT NULL,
CONSTRAINT "dictionary_user_id_lang_key" UNIQUE ("user_id", "language")
);
CREATE TABLE "searches" (
"search_id" integer PRIMARY KEY,
"user_id" integer NOT NULL
REFERENCES "users" ("user_id") ON DELETE CASCADE,
"type" smallint DEFAULT 0 NOT NULL,
"name" varchar(128) NOT NULL,
"data" long NOT NULL,
CONSTRAINT "searches_user_id_key" UNIQUE ("user_id", "type", "name")
);
CREATE SEQUENCE "searches_seq"
START WITH 1 INCREMENT BY 1 NOMAXVALUE;
CREATE TRIGGER "searches_seq_trig"
BEFORE INSERT ON "searches" FOR EACH ROW
BEGIN
:NEW."search_id" := "searches_seq".nextval;
END;
-
+/
CREATE TABLE "system" (
"name" varchar(64) NOT NULL PRIMARY KEY,
"value" long
);
INSERT INTO "system" ("name", "value") VALUES ('roundcube-version', '2014042900');
diff --git a/program/lib/Roundcube/rcube_db_oracle.php b/program/lib/Roundcube/rcube_db_oracle.php
index 338eb2e2a..362beb075 100644
--- a/program/lib/Roundcube/rcube_db_oracle.php
+++ b/program/lib/Roundcube/rcube_db_oracle.php
@@ -1,599 +1,599 @@
<?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@kolabsys.com> |
+-----------------------------------------------------------------------+
*/
/**
* Database independent query interface
*
* @package Framework
* @subpackage Database
*/
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
*
* @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 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;
}
/**
* 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 == '') {
+ if (preg_match('/^--/', $line) || $tok == '' || $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();
}
}

File Metadata

Mime Type
text/x-diff
Expires
Sat, Mar 1, 1:59 AM (10 h, 26 m)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
165422
Default Alt Text
(24 KB)

Event Timeline