Page Menu
Home
Phorge
Search
Configure Global Search
Log In
Files
F174708
No One
Temporary
Actions
View File
Edit File
Delete File
View Transforms
Subscribe
Mute Notifications
Award Token
Flag For Later
Size
143 KB
Referenced Files
None
Subscribers
None
View Options
diff --git a/plugins/enigma/lib/enigma_driver_gnupg.php b/plugins/enigma/lib/enigma_driver_gnupg.php
index 5af339836..69d751c9f 100644
--- a/plugins/enigma/lib/enigma_driver_gnupg.php
+++ b/plugins/enigma/lib/enigma_driver_gnupg.php
@@ -1,754 +1,745 @@
<?php
/**
+-------------------------------------------------------------------------+
| GnuPG (PGP) driver for the Enigma Plugin |
| |
| Copyright (C) The Roundcube Dev Team |
| |
| 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. |
+-------------------------------------------------------------------------+
| Author: Aleksander Machniak <alec@alec.pl> |
+-------------------------------------------------------------------------+
*/
require_once 'Crypt/GPG.php';
class enigma_driver_gnupg extends enigma_driver
{
protected $rc;
protected $gpg;
protected $homedir;
protected $user;
protected $last_sig_algorithm;
protected $debug = false;
protected $db_files = array('pubring.gpg', 'secring.gpg', 'pubring.kbx');
function __construct($user)
{
$this->rc = rcmail::get_instance();
$this->user = $user;
}
/**
* Driver initialization and environment checking.
* Should only return critical errors.
*
* @return mixed NULL on success, enigma_error on failure
*/
function init()
{
$homedir = $this->rc->config->get('enigma_pgp_homedir');
$debug = $this->rc->config->get('enigma_debug');
$binary = $this->rc->config->get('enigma_pgp_binary');
$agent = $this->rc->config->get('enigma_pgp_agent');
$gpgconf = $this->rc->config->get('enigma_pgp_gpgconf');
if (!$homedir) {
return new enigma_error(enigma_error::INTERNAL,
"Option 'enigma_pgp_homedir' not specified");
}
// check if homedir exists (create it if not) and is readable
if (!file_exists($homedir)) {
return new enigma_error(enigma_error::INTERNAL,
"Keys directory doesn't exists: $homedir");
}
if (!is_writable($homedir)) {
return new enigma_error(enigma_error::INTERNAL,
"Keys directory isn't writeable: $homedir");
}
$homedir = $homedir . '/' . $this->user;
// check if user's homedir exists (create it if not) and is readable
if (!file_exists($homedir)) {
mkdir($homedir, 0700);
}
if (!file_exists($homedir)) {
return new enigma_error(enigma_error::INTERNAL,
"Unable to create keys directory: $homedir");
}
if (!is_writable($homedir)) {
return new enigma_error(enigma_error::INTERNAL,
"Unable to write to keys directory: $homedir");
}
$this->debug = $debug;
$this->homedir = $homedir;
$options = array('homedir' => $this->homedir);
if ($debug) {
$options['debug'] = array($this, 'debug');
}
if ($binary) {
$options['binary'] = $binary;
}
if ($agent) {
$options['agent'] = $agent;
}
if ($gpgconf) {
$options['gpgconf'] = $gpgconf;
}
$options['cipher-algo'] = $this->rc->config->get('enigma_pgp_cipher_algo');
$options['digest-algo'] = $this->rc->config->get('enigma_pgp_digest_algo');
// Create Crypt_GPG object
try {
$this->gpg = new Crypt_GPG($options);
}
catch (Exception $e) {
return $this->get_error_from_exception($e);
}
$this->db_sync();
}
/**
* Encryption (and optional signing).
*
* @param string Message body
* @param array List of keys (enigma_key objects)
* @param enigma_key Optional signing Key ID
*
* @return mixed Encrypted message or enigma_error on failure
*/
function encrypt($text, $keys, $sign_key = null)
{
try {
foreach ($keys as $key) {
$this->gpg->addEncryptKey($key->reference);
}
if ($sign_key) {
$this->gpg->addSignKey($sign_key->reference, $sign_key->password);
$res = $this->gpg->encryptAndSign($text, true);
$sigInfo = $this->gpg->getLastSignatureInfo();
$this->last_sig_algorithm = $sigInfo->getHashAlgorithmName();
return $res;
}
return $this->gpg->encrypt($text, true);
}
catch (Exception $e) {
return $this->get_error_from_exception($e);
}
}
/**
* Decrypt a message (and verify if signature found)
*
* @param string Encrypted message
* @param array List of key-password mapping
* @param enigma_signature Signature information (if available)
*
* @return mixed Decrypted message or enigma_error on failure
*/
function decrypt($text, $keys = array(), &$signature = null)
{
try {
foreach ($keys as $key => $password) {
$this->gpg->addDecryptKey($key, $password);
}
$result = $this->gpg->decryptAndVerify($text, true);
if (!empty($result['signatures'])) {
$signature = $this->parse_signature($result['signatures'][0]);
}
// EFAIL vulnerability mitigation (#6289)
// Handle MDC warning as an exception, this is the default for gpg 2.3.
if (method_exists($this->gpg, 'getWarnings')) {
foreach ($this->gpg->getWarnings() as $warning_msg) {
if (strpos($warning_msg, 'not integrity protected') !== false) {
return new enigma_error(enigma_error::NOMDC, ucfirst($warning_msg));
}
}
}
return $result['data'];
}
catch (Exception $e) {
return $this->get_error_from_exception($e);
}
}
/**
* Signing.
*
* @param string Message body
* @param enigma_key The key
* @param int Signing mode (enigma_engine::SIGN_*)
*
* @return mixed True on success or enigma_error on failure
*/
function sign($text, $key, $mode = null)
{
try {
$this->gpg->addSignKey($key->reference, $key->password);
$res = $this->gpg->sign($text, $mode, CRYPT_GPG::ARMOR_ASCII, true);
$sigInfo = $this->gpg->getLastSignatureInfo();
$this->last_sig_algorithm = $sigInfo->getHashAlgorithmName();
return $res;
}
catch (Exception $e) {
return $this->get_error_from_exception($e);
}
}
/**
* Signature verification.
*
* @param string Message body
* @param string Signature, if message is of type PGP/MIME and body doesn't contain it
*
* @return mixed Signature information (enigma_signature) or enigma_error
*/
function verify($text, $signature)
{
try {
$verified = $this->gpg->verify($text, $signature);
return $this->parse_signature($verified[0]);
}
catch (Exception $e) {
return $this->get_error_from_exception($e);
}
}
/**
* Key file import.
*
* @param string File name or file content
* @param bolean True if first argument is a filename
* @param array Optional key => password map
*
* @return mixed Import status array or enigma_error
*/
public function import($content, $isfile = false, $passwords = array())
{
try {
// GnuPG 2.1 requires secret key passphrases on import
foreach ($passwords as $keyid => $pass) {
$this->gpg->addPassphrase($keyid, $pass);
}
if ($isfile) {
$result = $this->gpg->importKeyFile($content);
}
else {
$result = $this->gpg->importKey($content);
}
$this->db_save();
return $result;
}
catch (Exception $e) {
return $this->get_error_from_exception($e);
}
}
/**
* Key export.
*
* @param string Key ID
* @param bool Include private key
* @param array Optional key => password map
*
* @return mixed Key content or enigma_error
*/
public function export($keyid, $with_private = false, $passwords = array())
{
try {
$key = $this->gpg->exportPublicKey($keyid, true);
if ($with_private) {
// GnuPG 2.1 requires secret key passphrases on export
foreach ($passwords as $_keyid => $pass) {
$this->gpg->addPassphrase($_keyid, $pass);
}
$priv = $this->gpg->exportPrivateKey($keyid, true);
$key .= $priv;
}
return $key;
}
catch (Exception $e) {
return $this->get_error_from_exception($e);
}
}
/**
* Keys listing.
*
* @param string Optional pattern for key ID, user ID or fingerprint
*
* @return mixed Array of enigma_key objects or enigma_error
*/
public function list_keys($pattern = '')
{
try {
$keys = $this->gpg->getKeys($pattern);
$result = array();
foreach ($keys as $idx => $key) {
$result[] = $this->parse_key($key);
unset($keys[$idx]);
}
return $result;
}
catch (Exception $e) {
return $this->get_error_from_exception($e);
}
}
/**
* Single key information.
*
* @param string Key ID, user ID or fingerprint
*
* @return mixed Key (enigma_key) object or enigma_error
*/
public function get_key($keyid)
{
$list = $this->list_keys($keyid);
if (is_array($list)) {
return $list[key($list)];
}
// error
return $list;
}
/**
* Key pair generation.
*
* @param array Key/User data (user, email, password, size)
*
* @return mixed Key (enigma_key) object or enigma_error
*/
public function gen_key($data)
{
try {
$debug = $this->rc->config->get('enigma_debug');
$keygen = new Crypt_GPG_KeyGenerator(array(
'homedir' => $this->homedir,
// 'binary' => '/usr/bin/gpg2',
'debug' => $debug ? array($this, 'debug') : false,
));
$key = $keygen
->setExpirationDate(0)
->setPassphrase($data['password'])
->generateKey($data['user'], $data['email']);
return $this->parse_key($key);
}
catch (Exception $e) {
return $this->get_error_from_exception($e);
}
}
/**
* Key deletion.
*
* @param string Key ID
*
* @return mixed True on success or enigma_error
*/
public function delete_key($keyid)
{
// delete public key
$result = $this->delete_pubkey($keyid);
// error handling
if ($result !== true) {
$code = $result->getCode();
// if not found, delete private key
if ($code == enigma_error::KEYNOTFOUND) {
$result = $this->delete_privkey($keyid);
}
// need to delete private key first
else if ($code == enigma_error::DELKEY) {
$result = $this->delete_privkey($keyid);
if ($result === true) {
$result = $this->delete_pubkey($keyid);
}
}
}
$this->db_save();
return $result;
}
/**
* Returns a name of the hash algorithm used for the last
* signing operation.
*
* @return string Hash algorithm name e.g. sha1
*/
public function signature_algorithm()
{
return $this->last_sig_algorithm;
}
/**
* Private key deletion.
*/
protected function delete_privkey($keyid)
{
try {
$this->gpg->deletePrivateKey($keyid);
return true;
}
catch (Exception $e) {
return $this->get_error_from_exception($e);
}
}
/**
* Public key deletion.
*/
protected function delete_pubkey($keyid)
{
try {
$this->gpg->deletePublicKey($keyid);
return true;
}
catch (Exception $e) {
return $this->get_error_from_exception($e);
}
}
/**
* Converts Crypt_GPG exception into Enigma's error object
*
* @param mixed Exception object
*
* @return enigma_error Error object
*/
protected function get_error_from_exception($e)
{
$data = array();
if ($e instanceof Crypt_GPG_KeyNotFoundException) {
$error = enigma_error::KEYNOTFOUND;
$data['id'] = $e->getKeyId();
}
else if ($e instanceof Crypt_GPG_BadPassphraseException) {
$error = enigma_error::BADPASS;
$data['bad'] = $e->getBadPassphrases();
$data['missing'] = $e->getMissingPassphrases();
}
else if ($e instanceof Crypt_GPG_NoDataException) {
$error = enigma_error::NODATA;
}
else if ($e instanceof Crypt_GPG_DeletePrivateKeyException) {
$error = enigma_error::DELKEY;
}
else {
$error = enigma_error::INTERNAL;
}
$msg = $e->getMessage();
return new enigma_error($error, $msg, $data);
}
/**
* Converts Crypt_GPG_Signature object into Enigma's signature object
*
* @param Crypt_GPG_Signature Signature object
*
* @return enigma_signature Signature object
*/
protected function parse_signature($sig)
{
$data = new enigma_signature();
$data->id = $sig->getId() ?: $sig->getKeyId();
$data->valid = $sig->isValid();
$data->fingerprint = $sig->getKeyFingerprint();
$data->created = $sig->getCreationDate();
$data->expires = $sig->getExpirationDate();
// In case of ERRSIG user may not be set
if ($user = $sig->getUserId()) {
$data->name = $user->getName();
$data->comment = $user->getComment();
$data->email = $user->getEmail();
}
return $data;
}
/**
* Converts Crypt_GPG_Key object into Enigma's key object
*
* @param Crypt_GPG_Key Key object
*
* @return enigma_key Key object
*/
protected function parse_key($key)
{
$ekey = new enigma_key();
foreach ($key->getUserIds() as $idx => $user) {
$id = new enigma_userid();
$id->name = $user->getName();
$id->comment = $user->getComment();
$id->email = $user->getEmail();
$id->valid = $user->isValid();
$id->revoked = $user->isRevoked();
$ekey->users[$idx] = $id;
}
$ekey->name = trim($ekey->users[0]->name . ' <' . $ekey->users[0]->email . '>');
// keep reference to Crypt_GPG's key for performance reasons
$ekey->reference = $key;
foreach ($key->getSubKeys() as $idx => $subkey) {
$skey = new enigma_subkey();
$skey->id = $subkey->getId();
$skey->revoked = $subkey->isRevoked();
$skey->created = $subkey->getCreationDate();
$skey->expires = $subkey->getExpirationDate();
$skey->fingerprint = $subkey->getFingerprint();
$skey->has_private = $subkey->hasPrivate();
$skey->algorithm = $subkey->getAlgorithm();
$skey->length = $subkey->getLength();
$skey->usage = $subkey->usage();
$ekey->subkeys[$idx] = $skey;
};
$ekey->id = $ekey->subkeys[0]->id;
return $ekey;
}
/**
* Syncronize keys database on multi-host setups
*/
protected function db_sync()
{
if (!$this->rc->config->get('enigma_multihost')) {
return;
}
$db = $this->rc->get_dbh();
$table = $db->table_name('filestore', true);
$files = array();
$result = $db->query(
"SELECT `file_id`, `filename`, `mtime` FROM $table WHERE `user_id` = ? AND `context` = ?",
$this->rc->user->ID, 'enigma');
while ($record = $db->fetch_assoc($result)) {
$file = $this->homedir . '/' . $record['filename'];
$mtime = @filemtime($file);
$files[] = $record['filename'];
if ($mtime < $record['mtime']) {
$data_result = $db->query("SELECT `data`, `mtime` FROM $table"
. " WHERE `file_id` = ?", $record['file_id']);
$record = $db->fetch_assoc($data_result);
$data = $record ? base64_decode($record['data']) : null;
if ($data === null || $data === false) {
rcube::raise_error(array(
'code' => 605, 'line' => __LINE__, 'file' => __FILE__,
'message' => "Enigma: Failed to sync $file ({$record['file_id']}). Decode error."
), true, false);
continue;
}
$tmpfile = $file . '.tmp';
if (file_put_contents($tmpfile, $data, LOCK_EX) === strlen($data)) {
rename($tmpfile, $file);
touch($file, $record['mtime']);
if ($this->debug) {
$this->debug("SYNC: Fetched file: $file");
}
}
else {
// error
@unlink($tmpfile);
rcube::raise_error(array(
'code' => 605, 'line' => __LINE__, 'file' => __FILE__,
'message' => "Enigma: Failed to sync $file."
), true, false);
}
}
}
// Remove files not in database
if (!$db->is_error($result)) {
foreach (array_diff($this->db_files_list(), $files) as $file) {
$file = $this->homedir . '/' . $file;
if (unlink($file)) {
if ($this->debug) {
$this->debug("SYNC: Removed file: $file");
}
}
}
}
// No records found, do initial sync if already have the keyring
if (!$db->is_error($result) && empty($file)) {
$this->db_save(true);
}
}
/**
* Save keys database for multi-host setups
*/
protected function db_save($is_empty = false)
{
if (!$this->rc->config->get('enigma_multihost')) {
return true;
}
$db = $this->rc->get_dbh();
$table = $db->table_name('filestore', true);
$records = array();
if (!$is_empty) {
$result = $db->query(
"SELECT `file_id`, `filename`, `mtime` FROM $table WHERE `user_id` = ? AND `context` = ?",
$this->rc->user->ID, 'enigma'
);
while ($record = $db->fetch_assoc($result)) {
$records[$record['filename']] = $record;
}
}
foreach ($this->db_files_list() as $filename) {
$file = $this->homedir . '/' . $filename;
$mtime = @filemtime($file);
$existing = $records[$filename];
unset($records[$filename]);
if ($mtime && (empty($existing) || $mtime > $existing['mtime'])) {
$data = file_get_contents($file);
$data = base64_encode($data);
$datasize = strlen($data);
if (empty($maxsize)) {
$maxsize = min($db->get_variable('max_allowed_packet', 1048500), 4*1024*1024) - 2000;
}
if ($datasize > $maxsize) {
rcube::raise_error(array(
'code' => 605, 'line' => __LINE__, 'file' => __FILE__,
'message' => "Enigma: Failed to save $file. Size exceeds max_allowed_packet."
), true, false);
continue;
}
- if (empty($existing)) {
- $result = $db->query(
- "INSERT INTO $table (`user_id`, `context`, `filename`, `mtime`, `data`)"
- . " VALUES(?, 'enigma', ?, ?, ?)",
- $this->rc->user->ID, $filename, $mtime, $data);
- }
- else {
- $result = $db->query(
- "UPDATE $table SET `mtime` = ?, `data` = ? WHERE `file_id` = ?",
- $mtime, $data, $existing['file_id']);
- }
+ $unique = array('user_id' => $this->rc->user->ID, 'context' => 'enigma', 'filename' => $filename);
+ $result = $db->insert_or_update($table, $unique, array('mtime', 'data'), array($mtime, $data));
if ($db->is_error($result)) {
rcube::raise_error(array(
'code' => 605, 'line' => __LINE__, 'file' => __FILE__,
'message' => "Enigma: Failed to save $file into database."
), true, false);
break;
}
if ($this->debug) {
$this->debug("SYNC: Pushed file: $file");
}
}
}
// Delete removed files from database
foreach (array_keys($records) as $filename) {
$file = $this->homedir . '/' . $filename;
$result = $db->query("DELETE FROM $table WHERE `user_id` = ? AND `context` = ? AND `filename` = ?",
$this->rc->user->ID, 'enigma', $filename);
if ($db->is_error($result)) {
rcube::raise_error(array(
'code' => 605, 'line' => __LINE__, 'file' => __FILE__,
'message' => "Enigma: Failed to delete $file from database."
), true, false);
break;
}
if ($this->debug) {
$this->debug("SYNC: Removed file: $file");
}
}
}
/**
* Returns list of homedir files to backup
*/
protected function db_files_list()
{
$files = array();
foreach ($this->db_files as $file) {
if (file_exists($this->homedir . '/' . $file)) {
$files[] = $file;
}
}
foreach (glob($this->homedir . '/private-keys-v1.d/*.key') as $file) {
$files[] = ltrim(substr($file, strlen($this->homedir)), '/');
}
return $files;
}
/**
* Write debug info from Crypt_GPG to logs/enigma
*/
public function debug($line)
{
rcube::write_log('enigma', 'GPG: ' . $line);
}
}
diff --git a/program/lib/Roundcube/cache/db.php b/program/lib/Roundcube/cache/db.php
index 8b16c3a89..719d55841 100644
--- a/program/lib/Roundcube/cache/db.php
+++ b/program/lib/Roundcube/cache/db.php
@@ -1,270 +1,249 @@
<?php
/**
+-----------------------------------------------------------------------+
| This file is part of the Roundcube Webmail client |
| |
| Copyright (C) The Roundcube Dev Team |
| Copyright (C) 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: |
| Caching engine - SQL DB |
+-----------------------------------------------------------------------+
| Author: Thomas Bruederli <roundcube@gmail.com> |
| Author: Aleksander Machniak <alec@alec.pl> |
+-----------------------------------------------------------------------+
*/
/**
* Interface class for accessing SQL Database cache
*
* @package Framework
* @subpackage Cache
*/
class rcube_cache_db extends rcube_cache
{
/**
* Instance of database handler
*
* @var rcube_db
*/
protected $db;
/**
* (Escaped) Cache table name (cache or cache_shared)
*
* @var string
*/
protected $table;
/**
* Object constructor.
*
* @param int $userid User identifier
* @param string $prefix Key name prefix
* @param string $ttl Expiration time of memcache/apc items
* @param bool $packed Enables/disabled data serialization.
* It's possible to disable data serialization if you're sure
* stored data will be always a safe string
*/
public function __construct($userid, $prefix = '', $ttl = 0, $packed = true)
{
parent::__construct($userid, $prefix, $ttl, $packed);
$rcube = rcube::get_instance();
$this->type = 'db';
$this->db = $rcube->get_dbh();
$this->table = $this->db->table_name($userid ? 'cache' : 'cache_shared', true);
}
/**
* Remove cache records older than ttl
*/
public function expunge()
{
if ($this->db && $this->ttl) {
$this->db->query(
"DELETE FROM {$this->table} WHERE "
. ($this->userid ? "`user_id` = {$this->userid} AND " : "")
. "`cache_key` LIKE ?"
. " AND `expires` < " . $this->db->now(),
$this->prefix . '.%');
}
}
/**
* Remove expired records of all caches
*/
public static function gc()
{
$rcube = rcube::get_instance();
$db = $rcube->get_dbh();
$db->query("DELETE FROM " . $db->table_name('cache', true) . " WHERE `expires` < " . $db->now());
$db->query("DELETE FROM " . $db->table_name('cache_shared', true) . " WHERE `expires` < " . $db->now());
}
/**
* Reads cache entry.
*
* @param string $key Cache key name
* @param boolean $nostore Enable to skip in-memory store
*
* @return mixed Cached value
*/
protected function read_record($key, $nostore=false)
{
if (!$this->db) {
return;
}
$sql_result = $this->db->query(
"SELECT `data`, `cache_key` FROM {$this->table} WHERE "
. ($this->userid ? "`user_id` = {$this->userid} AND " : "")
."`cache_key` = ?",
$this->prefix . '.' . $key);
if ($sql_arr = $this->db->fetch_assoc($sql_result)) {
if (strlen($sql_arr['data']) > 0) {
$md5sum = md5($sql_arr['data']);
$data = $this->unserialize($sql_arr['data']);
}
$this->db->reset();
if ($nostore) {
return $data;
}
$this->cache[$key] = $data;
$this->cache_sums[$key] = $md5sum;
}
else {
$this->cache[$key] = null;
}
return $this->cache[$key];
}
/**
* Writes single cache record into DB.
*
* @param string $key Cache key name
* @param mixed $data Serialized cache data
*
* @param boolean True on success, False on failure
*/
protected function write_record($key, $data)
{
if (!$this->db) {
return false;
}
// don't attempt to write too big data sets
if (strlen($data) > $this->max_packet_size()) {
trigger_error("rcube_cache: max_packet_size ($this->max_packet) exceeded for key $key. Tried to write " . strlen($data) . " bytes", E_USER_WARNING);
return false;
}
$db_key = $this->prefix . '.' . $key;
// Remove NULL rows (here we don't need to check if the record exist)
if ($data == 'N;') {
$result = $this->db->query(
"DELETE FROM {$this->table} WHERE "
. ($this->userid ? "`user_id` = {$this->userid} AND " : "")
."`cache_key` = ?",
$db_key);
return !$this->db->is_error($result);
}
- $key_exists = array_key_exists($key, $this->cache_sums);
- $expires = $this->ttl ? $this->db->now($this->ttl) : 'NULL';
-
- if (!$key_exists) {
- // Try INSERT temporarily ignoring "duplicate key" errors
- $this->db->set_option('ignore_key_errors', true);
-
- if ($this->userid) {
- $result = $this->db->query(
- "INSERT INTO {$this->table} (`expires`, `user_id`, `cache_key`, `data`)"
- . " VALUES ($expires, ?, ?, ?)",
- $this->userid, $db_key, $data);
- }
- else {
- $result = $this->db->query(
- "INSERT INTO {$this->table} (`expires`, `cache_key`, `data`)"
- . " VALUES ($expires, ?, ?)",
- $db_key, $data);
- }
+ $expires = $this->db->param($this->ttl ? $this->db->now($this->ttl) : 'NULL', rcube_db::TYPE_SQL);
+ $pkey = array('cache_key' => $db_key);
- $this->db->set_option('ignore_key_errors', false);
+ if ($this->userid) {
+ $pkey['user_id'] = $this->userid;
}
- // otherwise try UPDATE
- if (!isset($result) || !($count = $this->db->affected_rows($result))) {
- $result = $this->db->query(
- "UPDATE {$this->table} SET `expires` = $expires, `data` = ? WHERE "
- . ($this->userid ? "`user_id` = {$this->userid} AND " : "")
- . "`cache_key` = ?",
- $data, $db_key);
+ $result = $this->db->insert_or_update(
+ $this->table, $pkey, array('expires', 'data'), array($expires, $data)
+ );
- $count = $this->db->affected_rows($result);
- }
+ $count = $this->db->affected_rows($result);
return $count > 0;
}
/**
* Deletes the cache record(s).
*
* @param string $key Cache key name or pattern
* @param boolean $prefix_mode Enable it to clear all keys starting
* with prefix specified in $key
*/
protected function remove_record($key = null, $prefix_mode = false)
{
if (!$this->db) {
return;
}
// Remove all keys (in specified cache)
if ($key === null) {
$where = "`cache_key` LIKE " . $this->db->quote($this->prefix . '.%');
}
// Remove keys by name prefix
else if ($prefix_mode) {
$where = "`cache_key` LIKE " . $this->db->quote($this->prefix . '.' . $key . '%');
}
// Remove one key by name
else {
$where = "`cache_key` = " . $this->db->quote($this->prefix . '.' . $key);
}
$this->db->query(
"DELETE FROM {$this->table} WHERE "
. ($this->userid ? "`user_id` = {$this->userid} AND " : "") . $where
);
}
/**
* Serializes data for storing
*/
protected function serialize($data)
{
return $this->db ? $this->db->encode($data, $this->packed) : false;
}
/**
* Unserializes serialized data
*/
protected function unserialize($data)
{
return $this->db ? $this->db->decode($data, $this->packed) : false;
}
/**
* Determine the maximum size for cache data to be written
*/
protected function max_packet_size()
{
if ($this->max_packet < 0) {
$this->max_packet = 2097152; // default/max is 2 MB
if ($value = $this->db->get_variable('max_allowed_packet', $this->max_packet)) {
$this->max_packet = $value;
}
$this->max_packet -= 2000;
}
return $this->max_packet;
}
}
diff --git a/program/lib/Roundcube/db/mysql.php b/program/lib/Roundcube/db/mysql.php
index 6e890b509..7e4505a8e 100644
--- a/program/lib/Roundcube/db/mysql.php
+++ b/program/lib/Roundcube/db/mysql.php
@@ -1,259 +1,286 @@
<?php
/**
+-----------------------------------------------------------------------+
| This file is part of the Roundcube Webmail client |
| |
| Copyright (C) The Roundcube Dev Team |
| |
| 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 MySQL database |
+-----------------------------------------------------------------------+
| Author: Aleksander Machniak <alec@alec.pl> |
+-----------------------------------------------------------------------+
*/
/**
* Database independent query interface
*
* This is a wrapper for the PHP PDO
*
* @package Framework
* @subpackage Database
*/
class rcube_db_mysql extends rcube_db
{
public $db_provider = 'mysql';
/**
* Object constructor
*
* @param string $db_dsnw DSN for read/write operations
* @param string $db_dsnr Optional DSN for read only operations
* @param bool $pconn Enables persistent connections
*/
public function __construct($db_dsnw, $db_dsnr = '', $pconn = false)
{
parent::__construct($db_dsnw, $db_dsnr, $pconn);
// SQL identifiers quoting
$this->options['identifier_start'] = '`';
$this->options['identifier_end'] = '`';
}
/**
* 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("SET NAMES 'utf8'");
}
/**
* Abstract SQL statement for value concatenation
*
* @return string SQL statement to be used in query
*/
public function concat(/* col1, col2, ... */)
{
$args = func_get_args();
if (is_array($args[0])) {
$args = $args[0];
}
return 'CONCAT(' . implode(', ', $args) . ')';
}
/**
* Returns PDO DSN string from DSN array
*
* @param array $dsn DSN parameters
*
* @return string Connection string
*/
protected function dsn_string($dsn)
{
$params = array();
$result = 'mysql:';
if ($dsn['database']) {
$params[] = 'dbname=' . $dsn['database'];
}
if ($dsn['hostspec']) {
$params[] = 'host=' . $dsn['hostspec'];
}
if ($dsn['port']) {
$params[] = 'port=' . $dsn['port'];
}
if ($dsn['socket']) {
$params[] = 'unix_socket=' . $dsn['socket'];
}
$params[] = 'charset=utf8';
if (!empty($params)) {
$result .= implode(';', $params);
}
return $result;
}
/**
* Returns driver-specific connection options
*
* @param array $dsn DSN parameters
*
* @return array Connection options
*/
protected function dsn_options($dsn)
{
$result = parent::dsn_options($dsn);
if (!empty($dsn['key'])) {
$result[PDO::MYSQL_ATTR_SSL_KEY] = $dsn['key'];
}
if (!empty($dsn['cipher'])) {
$result[PDO::MYSQL_ATTR_SSL_CIPHER] = $dsn['cipher'];
}
if (!empty($dsn['cert'])) {
$result[PDO::MYSQL_ATTR_SSL_CERT] = $dsn['cert'];
}
if (!empty($dsn['capath'])) {
$result[PDO::MYSQL_ATTR_SSL_CAPATH] = $dsn['capath'];
}
if (!empty($dsn['ca'])) {
$result[PDO::MYSQL_ATTR_SSL_CA] = $dsn['ca'];
}
if (isset($dsn['verify_server_cert'])) {
$result[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT] = rcube_utils::get_boolean($dsn['verify_server_cert']);
}
// Always return matching (not affected only) rows count
$result[PDO::MYSQL_ATTR_FOUND_ROWS] = true;
// Enable AUTOCOMMIT mode (#1488902)
$result[PDO::ATTR_AUTOCOMMIT] = true;
return $result;
}
/**
* Returns list of tables in a database
*
* @return array List of all tables of the current database
*/
public function list_tables()
{
// get tables if not cached
if ($this->tables === null) {
$q = $this->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
. " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'"
. " ORDER BY TABLE_NAME", $this->db_dsnw_array['database']);
$this->tables = $q ? $q->fetchAll(PDO::FETCH_COLUMN, 0) : array();
}
return $this->tables;
}
/**
* Returns list of columns in database table
*
* @param string $table Table name
*
* @return array List of table cols
*/
public function list_cols($table)
{
$q = $this->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS"
. " WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?",
$this->db_dsnw_array['database'], $table);
if ($q) {
return $q->fetchAll(PDO::FETCH_COLUMN, 0);
}
return array();
}
/**
* Get database runtime variables
*
* @param string $varname Variable name
* @param mixed $default Default value if variable is not set
*
* @return mixed Variable value or default
*/
public function get_variable($varname, $default = null)
{
if (!isset($this->variables)) {
$this->variables = array();
}
if (array_key_exists($varname, $this->variables)) {
return $this->variables[$varname];
}
// configured value has higher prio
$conf_value = rcube::get_instance()->config->get('db_' . $varname);
if ($conf_value !== null) {
return $this->variables[$varname] = $conf_value;
}
$result = $this->query('SHOW VARIABLES LIKE ?', $varname);
while ($row = $this->fetch_array($result)) {
$this->variables[$row[0]] = $row[1];
}
// not found, use default
if (!isset($this->variables[$varname])) {
$this->variables[$varname] = $default;
}
return $this->variables[$varname];
}
+ /**
+ * INSERT ... ON DUPLICATE KEY UPDATE (or equivalent).
+ * When not supported by the engine we do UPDATE and INSERT.
+ *
+ * @param string $table Table name
+ * @param array $keys Hash array (column => value) of the unique constraint
+ * @param array $columns List of columns to update
+ * @param array $values List of values to update (number of elements
+ * should be the same as in $columns)
+ *
+ * @return PDOStatement|bool Query handle or False on error
+ * @todo Multi-insert support
+ */
+ public function insert_or_update($table, $keys, $columns, $values)
+ {
+ $table = $this->table_name($table, true);
+ $columns = array_map(function($i) { return "`$i`"; }, $columns);
+ $cols = implode(', ', array_map(function($i) { return "`$i`"; }, array_keys($keys)));
+ $cols .= ', ' . implode(', ', $columns);
+ $vals = implode(', ', array_map(function($i) { return $this->quote($i); }, $keys));
+ $vals .= ', ' . rtrim(str_repeat('?, ', count($columns)), ', ');
+ $update = implode(', ', array_map(function($i) { return "$i = VALUES($i)"; }, $columns));
+
+ return $this->query("INSERT INTO $table ($cols) VALUES ($vals)"
+ . " ON DUPLICATE KEY UPDATE $update", $values);
+ }
+
/**
* Handle DB errors, re-issue the query on deadlock errors from InnoDB row-level locking
*
* @param string Query that triggered the error
* @return mixed Result to be stored and returned
*/
protected function handle_error($query)
{
$error = $this->dbh->errorInfo();
// retry after "Deadlock found when trying to get lock" errors
$retries = 2;
while ($error[1] == 1213 && $retries >= 0) {
usleep(50000); // wait 50 ms
$result = $this->dbh->query($query);
if ($result !== false) {
return $result;
}
$error = $this->dbh->errorInfo();
$retries--;
}
return parent::handle_error($query);
}
}
diff --git a/program/lib/Roundcube/db/param.php b/program/lib/Roundcube/db/param.php
new file mode 100644
index 000000000..fe0a40273
--- /dev/null
+++ b/program/lib/Roundcube/db/param.php
@@ -0,0 +1,58 @@
+<?php
+
+/**
+ +-----------------------------------------------------------------------+
+ | This file is part of the Roundcube Webmail client |
+ | |
+ | Copyright (C) The Roundcube Dev Team |
+ | |
+ | 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 for query parameters |
+ +-----------------------------------------------------------------------+
+ | Author: Aleksander Machniak <alec@alec.pl> |
+ +-----------------------------------------------------------------------+
+*/
+
+/**
+ * Database query parameter
+ *
+ * @package Framework
+ * @subpackage Database
+ */
+class rcube_db_param
+{
+ protected $db;
+ protected $type;
+ protected $value;
+
+
+ /**
+ * Object constructor
+ *
+ * @param rcube_db $db Database driver
+ * @param mixed $value Parameter value
+ * @param string $type Parameter type (One of rcube_db::TYPE_* constants)
+ */
+ public function __construct($db, $value, $type = null)
+ {
+ $this->db = $db;
+ $this->value = $value;
+ $this->type = $type;
+ }
+
+ /**
+ * Returns the value as string for inlining into SQL query
+ */
+ public function __toString()
+ {
+ if ($this->type === rcube_db::TYPE_SQL) {
+ return (string) $this->value;
+ }
+
+ return (string) $this->db->quote($this->value, $this->type);
+ }
+}
diff --git a/program/lib/Roundcube/db/pgsql.php b/program/lib/Roundcube/db/pgsql.php
index da26f12f6..1e50902fa 100644
--- a/program/lib/Roundcube/db/pgsql.php
+++ b/program/lib/Roundcube/db/pgsql.php
@@ -1,300 +1,332 @@
<?php
/**
+-----------------------------------------------------------------------+
| This file is part of the Roundcube Webmail client |
| |
| Copyright (C) The Roundcube Dev Team |
| |
| 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 PostgreSQL database |
+-----------------------------------------------------------------------+
| Author: Aleksander Machniak <alec@alec.pl> |
+-----------------------------------------------------------------------+
*/
/**
* Database independent query interface
* This is a wrapper for the PHP PDO
*
* @package Framework
* @subpackage Database
*/
class rcube_db_pgsql extends rcube_db
{
public $db_provider = 'postgres';
// See https://www.postgresql.org/docs/current/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS
private static $libpq_connect_params = array("application_name", "sslmode", "sslcert", "sslkey", "sslrootcert", "sslcrl", "sslcompression", "service");
/**
* Object constructor
*
* @param string $db_dsnw DSN for read/write operations
* @param string $db_dsnr Optional DSN for read only operations
* @param bool $pconn Enables persistent connections
*/
public function __construct($db_dsnw, $db_dsnr = '', $pconn = false)
{
parent::__construct($db_dsnw, $db_dsnr, $pconn);
// use date/time input format with timezone spec.
$this->options['datetime_format'] = 'c';
}
/**
* 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("SET NAMES 'utf8'");
$dbh->query("SET DATESTYLE TO ISO");
// if ?schema= is set in dsn, set the search_path
if ($dsn['schema']) {
$dbh->query("SET search_path TO " . $this->quote($dsn['schema']));
}
}
/**
* 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') {
return false;
}
if ($table) {
$table = $this->sequence_name($table);
}
$id = $this->dbh->lastInsertId($table);
return $id;
}
/**
* 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 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 "EXTRACT (EPOCH FROM $field)";
}
/**
* 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) {
$add = ' ' . ($interval > 0 ? '+' : '-') . " interval '";
$add .= $interval > 0 ? intval($interval) : intval($interval) * -1;
$add .= " seconds'";
}
return "now()" . $add;
}
/**
* 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 $this->quote_identifier($column) . ' ILIKE ' . $this->quote($value);
}
/**
* Get database runtime variables
*
* @param string $varname Variable name
* @param mixed $default Default value if variable is not set
*
* @return mixed Variable value or default
*/
public function get_variable($varname, $default = null)
{
// There's a known case when max_allowed_packet is queried
// PostgreSQL doesn't have such limit, return immediately
if ($varname == 'max_allowed_packet') {
return rcube::get_instance()->config->get('db_' . $varname, $default);
}
$this->variables[$varname] = rcube::get_instance()->config->get('db_' . $varname);
if (!isset($this->variables)) {
$this->variables = array();
$result = $this->query('SHOW ALL');
while ($row = $this->fetch_array($result)) {
$this->variables[$row[0]] = $row[1];
}
}
return isset($this->variables[$varname]) ? $this->variables[$varname] : $default;
}
+ /**
+ * INSERT ... ON CONFLICT DO UPDATE.
+ * When not supported by the engine we do UPDATE and INSERT.
+ *
+ * @param string $table Table name
+ * @param array $keys Hash array (column => value) of the unique constraint
+ * @param array $columns List of columns to update
+ * @param array $values List of values to update (number of elements
+ * should be the same as in $columns)
+ *
+ * @return PDOStatement|bool Query handle or False on error
+ * @todo Multi-insert support
+ */
+ public function insert_or_update($table, $keys, $columns, $values)
+ {
+ // Check if version >= 9.5, otherwise use fallback
+ if ($this->get_variable('server_version_num') < 90500) {
+ return parent::insert_or_update($table, $keys, $columns, $values);
+ }
+
+ $table = $this->table_name($table, true);
+ $columns = array_map(array($this, 'quote_identifier'), $columns);
+ $target = implode(', ', array_map(array($this, 'quote_identifier'), array_keys($keys)));
+ $cols = $target . ', ' . implode(', ', $columns);
+ $vals = implode(', ', array_map(function($i) { return $this->quote($i); }, $keys));
+ $vals .= ', ' . rtrim(str_repeat('?, ', count($columns)), ', ');
+ $update = implode(', ', array_map(function($i) { return "$i = EXCLUDED.$i"; }, $columns));
+
+ return $this->query("INSERT INTO $table ($cols) VALUES ($vals)"
+ . " ON CONFLICT ($target) DO UPDATE SET $update", $values);
+ }
+
/**
* Returns list of tables in a database
*
* @return array List of all tables of the current database
*/
public function list_tables()
{
// get tables if not cached
if ($this->tables === null) {
if (($schema = $this->options['table_prefix']) && $schema[strlen($schema)-1] === '.') {
$add = " AND TABLE_SCHEMA = " . $this->quote(substr($schema, 0, -1));
}
else {
$add = " AND TABLE_SCHEMA NOT IN ('pg_catalog', 'information_schema')";
}
$q = $this->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
. " WHERE TABLE_TYPE = 'BASE TABLE'" . $add
. " ORDER BY TABLE_NAME");
$this->tables = $q ? $q->fetchAll(PDO::FETCH_COLUMN, 0) : array();
}
return $this->tables;
}
/**
* Returns list of columns in database table
*
* @param string $table Table name
*
* @return array List of table cols
*/
public function list_cols($table)
{
$args = array($table);
if (($schema = $this->options['table_prefix']) && $schema[strlen($schema)-1] === '.') {
$add = " AND TABLE_SCHEMA = ?";
$args[] = substr($schema, 0, -1);
}
else {
$add = " AND TABLE_SCHEMA NOT IN ('pg_catalog', 'information_schema')";
}
$q = $this->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS"
. " WHERE TABLE_NAME = ?" . $add, $args);
if ($q) {
return $q->fetchAll(PDO::FETCH_COLUMN, 0);
}
return array();
}
/**
* Returns PDO DSN string from DSN array
*
* @param array $dsn DSN parameters
*
* @return string DSN string
*/
protected function dsn_string($dsn)
{
$params = array();
$result = 'pgsql:';
if ($dsn['hostspec']) {
$params[] = 'host=' . $dsn['hostspec'];
}
else if ($dsn['socket']) {
$params[] = 'host=' . $dsn['socket'];
}
if ($dsn['port']) {
$params[] = 'port=' . $dsn['port'];
}
if ($dsn['database']) {
$params[] = 'dbname=' . $dsn['database'];
}
foreach (self::$libpq_connect_params as $param) {
if ($dsn[$param]) {
$params[] = $param . '=' . $dsn[$param];
}
}
if (!empty($params)) {
$result .= implode(';', $params);
}
return $result;
}
/**
* 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 postgres-specific commands
$sql = preg_replace_callback(
'/((SEQUENCE |RENAME TO |nextval\()["\']*)([^"\' \r\n]+)/',
array($this, 'fix_table_names_callback'),
$sql
);
return $sql;
}
}
diff --git a/program/lib/Roundcube/rcube_db.php b/program/lib/Roundcube/rcube_db.php
index 4bd7fe70f..ad8ea85a4 100644
--- a/program/lib/Roundcube/rcube_db.php
+++ b/program/lib/Roundcube/rcube_db.php
@@ -1,1401 +1,1462 @@
<?php
/**
+-----------------------------------------------------------------------+
| This file is part of the Roundcube Webmail client |
| |
| Copyright (C) The Roundcube Dev Team |
| |
| 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 |
+-----------------------------------------------------------------------+
| Author: Aleksander Machniak <alec@alec.pl> |
+-----------------------------------------------------------------------+
*/
/**
* Database independent query interface.
* This is a wrapper for the PHP PDO.
*
* @package Framework
* @subpackage Database
*/
class rcube_db
{
public $db_provider;
protected $db_dsnw; // DSN for write operations
protected $db_dsnr; // DSN for read operations
protected $db_connected = false; // Already connected ?
protected $db_mode; // Connection mode
protected $dbh; // Connection handle
protected $dbhs = array();
protected $table_connections = array();
protected $db_error = false;
protected $db_error_msg = '';
protected $conn_failure = false;
protected $db_index = 0;
protected $last_result;
protected $tables;
protected $variables;
protected $options = array(
// column/table quotes
'identifier_start' => '"',
'identifier_end' => '"',
// date/time input format
'datetime_format' => 'Y-m-d H:i:s',
);
const DEBUG_LINE_LENGTH = 4096;
const DEFAULT_QUOTE = '`';
+ const TYPE_SQL = 'sql';
+ const TYPE_INT = 'integer';
+ const TYPE_BOOL = 'bool';
+ const TYPE_STRING = 'string';
+
+
/**
* Factory, returns driver-specific instance of the class
*
* @param string $db_dsnw DSN for read/write operations
* @param string $db_dsnr Optional DSN for read only operations
* @param bool $pconn Enables persistent connections
*
* @return rcube_db Object instance
*/
public static function factory($db_dsnw, $db_dsnr = '', $pconn = false)
{
$driver = strtolower(substr($db_dsnw, 0, strpos($db_dsnw, ':')));
$driver_map = array(
'sqlite2' => 'sqlite',
'sybase' => 'mssql',
'dblib' => 'mssql',
'mysqli' => 'mysql',
'oci' => 'oracle',
'oci8' => 'oracle',
);
$driver = isset($driver_map[$driver]) ? $driver_map[$driver] : $driver;
$class = "rcube_db_$driver";
if (!$driver || !class_exists($class)) {
rcube::raise_error(array('code' => 600, 'type' => 'db',
'line' => __LINE__, 'file' => __FILE__,
'message' => "Configuration error. Unsupported database driver: $driver"),
true, true);
}
return new $class($db_dsnw, $db_dsnr, $pconn);
}
/**
* Object constructor
*
* @param string $db_dsnw DSN for read/write operations
* @param string $db_dsnr Optional DSN for read only operations
* @param bool $pconn Enables persistent connections
*/
public function __construct($db_dsnw, $db_dsnr = '', $pconn = false)
{
if (empty($db_dsnr)) {
$db_dsnr = $db_dsnw;
}
$this->db_dsnw = $db_dsnw;
$this->db_dsnr = $db_dsnr;
$this->db_pconn = $pconn;
$this->db_dsnw_array = self::parse_dsn($db_dsnw);
$this->db_dsnr_array = self::parse_dsn($db_dsnr);
$config = rcube::get_instance()->config;
$this->options['table_prefix'] = $config->get('db_prefix');
$this->options['dsnw_noread'] = $config->get('db_dsnw_noread', false);
$this->options['table_dsn_map'] = array_map(array($this, 'table_name'), $config->get('db_table_dsn', array()));
}
/**
* Connect to specific database
*
* @param array $dsn DSN for DB connections
* @param string $mode Connection mode (r|w)
*/
protected function dsn_connect($dsn, $mode)
{
$this->db_error = false;
$this->db_error_msg = null;
// return existing handle
if ($this->dbhs[$mode]) {
$this->dbh = $this->dbhs[$mode];
$this->db_mode = $mode;
return $this->dbh;
}
// connect to database
if ($dbh = $this->conn_create($dsn)) {
$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);
// Connect
try {
// with this check we skip fatal error on PDO object creation
if (!class_exists('PDO', false)) {
throw new Exception('PDO extension not loaded. See http://php.net/manual/en/intro.pdo.php');
}
$this->conn_prepare($dsn);
$this->dbh = new PDO($dsn_string, $dsn['username'], $dsn['password'], $dsn_options);
// don't throw exceptions or warnings
$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$this->conn_configure($dsn, $this->dbh);
}
catch (Exception $e) {
$this->db_error = true;
$this->db_error_msg = $e->getMessage();
rcube::raise_error(array('code' => 500, 'type' => 'db',
'line' => __LINE__, 'file' => __FILE__,
'message' => $this->db_error_msg), true, false);
return null;
}
return $this->dbh;
}
/**
* Driver-specific preparation of database connection
*
* @param array $dsn DSN for DB connections
*/
protected function conn_prepare($dsn)
{
}
/**
* Driver-specific configuration of database connection
*
* @param array $dsn DSN for DB connections
* @param PDO $dbh Connection handler
*/
protected function conn_configure($dsn, $dbh)
{
}
/**
* Connect to appropriate database depending on the operation
*
* @param string $mode Connection mode (r|w)
* @param boolean $force Enforce using the given mode
*/
public function db_connect($mode, $force = false)
{
// previous connection failed, don't attempt to connect again
if ($this->conn_failure) {
return;
}
// no replication
if ($this->db_dsnw == $this->db_dsnr) {
$mode = 'w';
}
// Already connected
if ($this->db_connected) {
// connected to db with the same or "higher" mode (if allowed)
if ($this->db_mode == $mode || $this->db_mode == 'w' && !$force && !$this->options['dsnw_noread']) {
return;
}
}
$dsn = ($mode == 'r') ? $this->db_dsnr_array : $this->db_dsnw_array;
$this->dsn_connect($dsn, $mode);
// use write-master when read-only fails
if (!$this->db_connected && $mode == 'r' && $this->is_replicated()) {
$this->dsn_connect($this->db_dsnw_array, 'w');
}
$this->conn_failure = !$this->db_connected;
}
/**
* Analyze the given SQL statement and select the appropriate connection to use
*/
protected function dsn_select($query)
{
// no replication
if ($this->db_dsnw == $this->db_dsnr) {
return 'w';
}
// 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($regex, $query, $matches, PREG_SET_ORDER)) {
foreach ($matches as $m) {
$table = $m[2];
// always use direct mapping
if ($this->options['table_dsn_map'][$table]) {
$mode = $this->options['table_dsn_map'][$table];
break; // primary table rules
}
else if ($mode == 'r') {
// connected to db with the same or "higher" mode for this table
$db_mode = $this->table_connections[$table];
if ($db_mode == 'w' && !$this->options['dsnw_noread']) {
$mode = $db_mode;
}
}
}
// remember mode chosen (for primary table)
$table = $matches[0][2];
$this->table_connections[$table] = $mode;
}
return $mode;
}
/**
* Activate/deactivate debug mode
*
* @param boolean $dbg True if SQL queries should be logged
*/
public function set_debug($dbg = true)
{
$this->options['debug_mode'] = $dbg;
}
/**
* Writes debug information/query to 'sql' log file
*
* @param string $query SQL query
*/
protected function debug($query)
{
if ($this->options['debug_mode']) {
if (($len = strlen($query)) > self::DEBUG_LINE_LENGTH) {
$diff = $len - self::DEBUG_LINE_LENGTH;
$query = substr($query, 0, self::DEBUG_LINE_LENGTH)
. "... [truncated $diff bytes]";
}
rcube::write_log('sql', '[' . (++$this->db_index) . '] ' . $query . ';');
}
}
/**
* Getter for error state
*
* @param mixed $result Optional query result
*
* @return string Error message
*/
public function is_error($result = null)
{
if ($result !== null) {
return $result === false ? $this->db_error_msg : null;
}
return $this->db_error ? $this->db_error_msg : null;
}
/**
* Connection state checker
*
* @return boolean True if in connected state
*/
public function is_connected()
{
return !is_object($this->dbh) ? false : $this->db_connected;
}
/**
* Is database replication configured?
*
* @return bool Returns true if dsnw != dsnr
*/
public function is_replicated()
{
return !empty($this->db_dsnr) && $this->db_dsnw != $this->db_dsnr;
}
/**
* Get database runtime variables
*
* @param string $varname Variable name
* @param mixed $default Default value if variable is not set
*
* @return mixed Variable value or default
*/
public function get_variable($varname, $default = null)
{
// to be implemented by driver class
return rcube::get_instance()->config->get('db_' . $varname, $default);
}
/**
* Execute a SQL query
*
* @param string SQL query to execute
* @param mixed Values to be inserted in query
*
* @return number Query handle identifier
*/
public function query()
{
$params = func_get_args();
$query = array_shift($params);
// Support one argument of type array, instead of n arguments
if (count($params) == 1 && is_array($params[0])) {
$params = $params[0];
}
return $this->_query($query, 0, 0, $params);
}
/**
* Execute a SQL query with limits
*
* @param string SQL query to execute
* @param int Offset for LIMIT statement
* @param int Number of rows for LIMIT statement
* @param mixed Values to be inserted in query
*
* @return PDOStatement|bool Query handle or False on error
*/
public function limitquery()
{
$params = func_get_args();
$query = array_shift($params);
$offset = array_shift($params);
$numrows = array_shift($params);
return $this->_query($query, $offset, $numrows, $params);
}
/**
* 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;
if (count($params)) {
while ($pos = strpos($query, '?', $pos)) {
if ($query[$pos+1] == '?') { // skip escaped '?'
$pos += 2;
}
else {
$val = $this->quote($params[$idx++]);
unset($params[$idx-1]);
$query = substr_replace($query, $val, $pos, 1);
$pos += strlen($val);
}
}
}
$query = rtrim($query, " \t\n\r\0\x0B;");
// replace escaped '?' and quotes back to normal, see self::quote()
$query = str_replace(
array('??', self::DEFAULT_QUOTE.self::DEFAULT_QUOTE),
array('?', self::DEFAULT_QUOTE),
$query
);
// 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->db_error_msg = null;
// send query
$result = $this->dbh->query($query);
if ($result === false) {
$result = $this->handle_error($query);
}
return $this->last_result = $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++;
}
}
return $query;
}
/**
* Helper method to handle DB errors.
* This by default logs the error but could be overridden by a driver implementation
*
* @param string $query Query that triggered the error
*
* @return mixed Result to be stored and returned
*/
protected function handle_error($query)
{
$error = $this->dbh->errorInfo();
if (empty($this->options['ignore_key_errors']) || !in_array($error[0], array('23000', '23505'))) {
$this->db_error = true;
$this->db_error_msg = sprintf('[%s] %s', $error[1], $error[2]);
if (empty($this->options['ignore_errors'])) {
rcube::raise_error(array(
'code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
'message' => $this->db_error_msg . " (SQL Query: $query)"
), true, false);
}
}
return false;
}
+ /**
+ * INSERT ... ON DUPLICATE KEY UPDATE (or equivalent).
+ * When not supported by the engine we do UPDATE and INSERT.
+ *
+ * @param string $table Table name
+ * @param array $keys Hash array (column => value) of the unique constraint
+ * @param array $columns List of columns to update
+ * @param array $values List of values to update (number of elements
+ * should be the same as in $columns)
+ *
+ * @return PDOStatement|bool Query handle or False on error
+ * @todo Multi-insert support
+ */
+ public function insert_or_update($table, $keys, $columns, $values)
+ {
+ $table = $this->table_name($table, true);
+ $columns = array_map(function($i) { return "`$i`"; }, $columns);
+ $sets = array_map(function($i) { return "$i = ?"; }, $columns);
+
+ array_walk($where, function(&$val, $key) {
+ $val = $this->quote_identifier($key) . " = " . $this->quote($val);
+ });
+
+ // First try UPDATE
+ $result = $this->query("UPDATE $table SET " . implode(", ", $sets)
+ . " WHERE " . implode(" AND ", $where), $values);
+
+ // if UPDATE fails use INSERT
+ if ($result && !$this->affected_rows($result)) {
+ $cols = implode(', ', array_map(function($i) { return "`$i`"; }, array_keys($keys)));
+ $cols .= ', ' . implode(', ', $columns);
+ $vals = implode(', ', array_map(function($i) { return $this->quote($i); }, $keys));
+ $vals .= ', ' . rtrim(str_repeat('?, ', count($columns)), ', ');
+
+ $result = $this->query("INSERT INTO $table ($cols) VALUES ($vals)", $values);
+ }
+
+ return $result;
+ }
+
/**
* 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))) {
if ($result !== true) {
return $result->rowCount();
}
}
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)
{
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);
return $query ? intval($query->fetchColumn(0)) : false;
}
else {
$num = count($result->fetchAll());
$result->execute(); // re-execute query because there's no seek(0)
return $num;
}
}
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 = '')
{
if (!$this->db_connected || $this->db_mode == 'r') {
return false;
}
if ($table) {
// resolve table name
$table = $this->table_name($table);
}
$id = $this->dbh->lastInsertId($table);
return $id;
}
/**
* 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, PDO::FETCH_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, PDO::FETCH_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))) {
if ($result !== true) {
return $result->fetch($mode);
}
}
return false;
}
/**
* Adds LIMIT,OFFSET clauses 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)
{
if ($limit) {
$query .= ' LIMIT ' . intval($limit);
}
if ($offset) {
$query .= ' OFFSET ' . intval($offset);
}
return $query;
}
/**
* Returns list of tables in a database
*
* @return array List of all tables of the current database
*/
public function list_tables()
{
// get tables if not cached
if ($this->tables === null) {
$q = $this->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES"
. " WHERE TABLE_TYPE = 'BASE TABLE'"
. " ORDER BY TABLE_NAME");
$this->tables = $q ? $q->fetchAll(PDO::FETCH_COLUMN, 0) : array();
}
return $this->tables;
}
/**
* Returns list of columns in database table
*
* @param string $table Table name
*
* @return array List of table cols
*/
public function list_cols($table)
{
$q = $this->query('SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ?',
array($table));
if ($q) {
return $q->fetchAll(PDO::FETCH_COLUMN, 0);
}
return array();
}
/**
* 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();
}
/**
* Release resources related to the last query result.
* When we know we don't need to access the last query result we can destroy it
* and release memory. Useful especially if the query returned big chunk of data.
*/
public function reset()
{
$this->last_result = null;
}
/**
* Terminate database connection.
*/
public function closeConnection()
{
$this->db_connected = false;
$this->db_index = 0;
// release statement and connection resources
$this->last_result = null;
$this->dbh = null;
$this->dbhs = array();
}
/**
* Formats input so it can be safely used in a query
*
* @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)
{
+ if ($input instanceof rcube_db_param) {
+ return (string) $input;
+ }
+
// handle int directly for better performance
if ($type == 'integer' || $type == 'int') {
return intval($input);
}
if (is_null($input)) {
return 'NULL';
}
if ($input instanceof DateTime) {
return $this->quote($input->format($this->options['datetime_format']));
}
if ($type == 'ident') {
return $this->quote_identifier($input);
}
// create DB handle if not available
if (!$this->dbh) {
$this->db_connect('r');
}
if ($this->dbh) {
$map = array(
'bool' => PDO::PARAM_BOOL,
'integer' => PDO::PARAM_INT,
);
$type = isset($map[$type]) ? $map[$type] : PDO::PARAM_STR;
return strtr($this->dbh->quote($input, $type),
// escape ? and `
array('?' => '??', self::DEFAULT_QUOTE => self::DEFAULT_QUOTE.self::DEFAULT_QUOTE)
);
}
return 'NULL';
}
/**
* Escapes a string so it can be safely used in a query
*
* @param string $str A string to escape
*
* @return string Escaped string for use in a query
*/
public function escape($str)
{
if (is_null($str)) {
return 'NULL';
}
return substr($this->quote($str), 1, -1);
}
/**
* Quotes a string so it can be safely used as a table or column name
*
* @param string $str Value to quote
*
* @return string Quoted string for use in query
* @deprecated Replaced by rcube_db::quote_identifier
* @see rcube_db::quote_identifier
*/
public function quoteIdentifier($str)
{
return $this->quote_identifier($str);
}
/**
* Escapes a string so it can be safely used in a query
*
* @param string $str A string to escape
*
* @return string Escaped string for use in a query
* @deprecated Replaced by rcube_db::escape
* @see rcube_db::escape
*/
public function escapeSimple($str)
{
return $this->escape($str);
}
/**
* Quotes a string so it can be safely used as a table or column name
*
* @param string $str Value to quote
*
* @return string Quoted string for use in query
*/
public function quote_identifier($str)
{
$start = $this->options['identifier_start'];
$end = $this->options['identifier_end'];
$name = array();
foreach (explode('.', $str) as $elem) {
$elem = str_replace(array($start, $end), '', $elem);
$name[] = $start . $elem . $end;
}
return implode('.', $name);
}
+ /**
+ * Create query parameter object
+ *
+ * @param mixed $value Parameter value
+ * @param string $type Parameter type (one of rcube_db::TYPE_* constants)
+ */
+ public function param($value, $type = null)
+ {
+ return new rcube_db_param($this, $value, $type);
+ }
+
/**
* 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) {
$add = ' ' . ($interval > 0 ? '+' : '-') . ' INTERVAL ';
$add .= $interval > 0 ? intval($interval) : intval($interval) * -1;
$add .= ' SECOND';
}
return "now()" . $add;
}
/**
* Return list of elements for use with SQL's IN clause
*
* @param array $arr Input array
* @param string $type Type of data (integer, bool, ident)
*
* @return string Comma-separated list of quoted values for use in query
*/
public function array2list($arr, $type = null)
{
if (!is_array($arr)) {
return $this->quote($arr, $type);
}
foreach ($arr as $idx => $item) {
$arr[$idx] = $this->quote($item, $type);
}
return implode(',', $arr);
}
/**
* Return SQL statement to convert a field value into a unix timestamp
*
* This method is deprecated and should not be used anymore due to limitations
* of timestamp functions in Mysql (year 2038 problem)
*
* @param string $field Field name
*
* @return string SQL statement to use in query
* @deprecated
*/
public function unixtimestamp($field)
{
return "UNIX_TIMESTAMP($field)";
}
/**
* Return SQL statement to convert from a unix timestamp
*
* @param int $timestamp Unix timestamp
*
* @return string Date string in db-specific format
* @deprecated
*/
public function fromunixtime($timestamp)
{
return $this->quote(date($this->options['datetime_format'], $timestamp));
}
/**
* 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 $this->quote_identifier($column).' LIKE '.$this->quote($value);
}
/**
* Abstract SQL statement for value concatenation
*
* @return string SQL statement to be used in query
*/
public function concat(/* col1, col2, ... */)
{
$args = func_get_args();
if (is_array($args[0])) {
$args = $args[0];
}
return '(' . implode(' || ', $args) . ')';
}
/**
* Encodes non-UTF-8 characters in string/array/object (recursive)
*
* @param mixed $input Data to fix
* @param bool $serialized Enable serialization
*
* @return mixed Properly UTF-8 encoded data
*/
public static function encode($input, $serialized = false)
{
// use Base64 encoding to workaround issues with invalid
// or null characters in serialized string (#1489142)
if ($serialized) {
return base64_encode(serialize($input));
}
if (is_object($input)) {
foreach (get_object_vars($input) as $idx => $value) {
$input->$idx = self::encode($value);
}
return $input;
}
else if (is_array($input)) {
foreach ($input as $idx => $value) {
$input[$idx] = self::encode($value);
}
return $input;
}
return utf8_encode($input);
}
/**
* Decodes encoded UTF-8 string/object/array (recursive)
*
* @param mixed $input Input data
* @param bool $serialized Enable serialization
*
* @return mixed Decoded data
*/
public static function decode($input, $serialized = false)
{
// use Base64 encoding to workaround issues with invalid
// or null characters in serialized string (#1489142)
if ($serialized) {
// Keep backward compatybility where base64 wasn't used
if (strpos(substr($input, 0, 16), ':') !== false) {
return self::decode(@unserialize($input));
}
return @unserialize(base64_decode($input));
}
if (is_object($input)) {
foreach (get_object_vars($input) as $idx => $value) {
$input->$idx = self::decode($value);
}
return $input;
}
else if (is_array($input)) {
foreach ($input as $idx => $value) {
$input[$idx] = self::decode($value);
}
return $input;
}
return utf8_decode($input);
}
/**
* Return correct name for a specific database table
*
* @param string $table Table name
* @param bool $quoted Quote table identifier
*
* @return string Translated table name
*/
public function table_name($table, $quoted = false)
{
// let plugins alter the table name (#1489837)
$plugin = rcube::get_instance()->plugins->exec_hook('db_table_name', array('table' => $table));
$table = $plugin['table'];
// add prefix to the table name if configured
if (($prefix = $this->options['table_prefix']) && strpos($table, $prefix) !== 0) {
$table = $prefix . $table;
}
if ($quoted) {
$table = $this->quote_identifier($table);
}
return $table;
}
/**
* Set class option value
*
* @param string $name Option name
* @param mixed $value Option value
*/
public function set_option($name, $value)
{
$this->options[$name] = $value;
}
/**
* Set DSN connection to be used for the given table
*
* @param string $table Table name
* @param string $mode DSN connection ('r' or 'w') to be used
*/
public function set_table_dsn($table, $mode)
{
$this->options['table_dsn_map'][$this->table_name($table)] = $mode;
}
/**
* MDB2 DSN string parser
*
* @param string $sequence Secuence name
*
* @return array DSN parameters
*/
public static function parse_dsn($dsn)
{
if (empty($dsn)) {
return null;
}
// Find phptype and dbsyntax
if (($pos = strpos($dsn, '://')) !== false) {
$str = substr($dsn, 0, $pos);
$dsn = substr($dsn, $pos + 3);
}
else {
$str = $dsn;
$dsn = null;
}
// Get phptype and dbsyntax
// $str => phptype(dbsyntax)
if (preg_match('|^(.+?)\((.*?)\)$|', $str, $arr)) {
$parsed['phptype'] = $arr[1];
$parsed['dbsyntax'] = !$arr[2] ? $arr[1] : $arr[2];
}
else {
$parsed['phptype'] = $str;
$parsed['dbsyntax'] = $str;
}
if (empty($dsn)) {
return $parsed;
}
// Get (if found): username and password
// $dsn => username:password@protocol+hostspec/database
if (($at = strrpos($dsn,'@')) !== false) {
$str = substr($dsn, 0, $at);
$dsn = substr($dsn, $at + 1);
if (($pos = strpos($str, ':')) !== false) {
$parsed['username'] = rawurldecode(substr($str, 0, $pos));
$parsed['password'] = rawurldecode(substr($str, $pos + 1));
}
else {
$parsed['username'] = rawurldecode($str);
}
}
// Find protocol and hostspec
// $dsn => proto(proto_opts)/database
if (preg_match('|^([^(]+)\((.*?)\)/?(.*?)$|', $dsn, $match)) {
$proto = $match[1];
$proto_opts = $match[2] ? $match[2] : false;
$dsn = $match[3];
}
// $dsn => protocol+hostspec/database (old format)
else {
if (strpos($dsn, '+') !== false) {
list($proto, $dsn) = explode('+', $dsn, 2);
}
if ( strpos($dsn, '//') === 0
&& strpos($dsn, '/', 2) !== false
&& $parsed['phptype'] == 'oci8'
) {
// Oracle's "Easy Connect" syntax:
// "username/password@[//]host[:port][/service_name]"
// e.g. "scott/tiger@//mymachine:1521/oracle"
$proto_opts = $dsn;
$pos = strrpos($proto_opts, '/');
$dsn = substr($proto_opts, $pos + 1);
$proto_opts = substr($proto_opts, 0, $pos);
}
else if (strpos($dsn, '/') !== false) {
list($proto_opts, $dsn) = explode('/', $dsn, 2);
}
else {
$proto_opts = $dsn;
$dsn = null;
}
}
// process the different protocol options
$parsed['protocol'] = $proto ?: 'tcp';
$proto_opts = rawurldecode($proto_opts);
if (strpos($proto_opts, ':') !== false) {
list($proto_opts, $parsed['port']) = explode(':', $proto_opts);
}
if ($parsed['protocol'] == 'tcp') {
$parsed['hostspec'] = $proto_opts;
}
else if ($parsed['protocol'] == 'unix') {
$parsed['socket'] = $proto_opts;
}
// Get database if any
// $dsn => database
if ($dsn) {
// /database
if (($pos = strpos($dsn, '?')) === false) {
$parsed['database'] = rawurldecode($dsn);
}
else {
// /database?param1=value1¶m2=value2
$parsed['database'] = rawurldecode(substr($dsn, 0, $pos));
$dsn = substr($dsn, $pos + 1);
if (strpos($dsn, '&') !== false) {
$opts = explode('&', $dsn);
}
else { // database?param1=value1
$opts = array($dsn);
}
foreach ($opts as $opt) {
list($key, $value) = explode('=', $opt);
if (!array_key_exists($key, $parsed) || false === $parsed[$key]) {
// don't allow params overwrite
$parsed[$key] = rawurldecode($value);
}
}
}
// remove problematic suffix (#7034)
$parsed['database'] = preg_replace('/;.*$/', '', $parsed['database']);
// Resolve relative path to the sqlite database file
// so for example it works with Roundcube Installer
if (!empty($parsed['phptype']) && !empty($parsed['database'])
&& stripos($parsed['phptype'], 'sqlite') === 0
&& $parsed['database'][0] != '/'
) {
$parsed['database'] = INSTALL_PATH . $parsed['database'];
}
}
return $parsed;
}
/**
* Returns PDO DSN string from DSN array
*
* @param array $dsn DSN parameters
*
* @return string DSN string
*/
protected function dsn_string($dsn)
{
$params = array();
$result = $dsn['phptype'] . ':';
if ($dsn['hostspec']) {
$params[] = 'host=' . $dsn['hostspec'];
}
if ($dsn['port']) {
$params[] = 'port=' . $dsn['port'];
}
if ($dsn['database']) {
$params[] = 'dbname=' . $dsn['database'];
}
if (!empty($params)) {
$result .= implode(';', $params);
}
return $result;
}
/**
* Returns driver-specific connection options
*
* @param array $dsn DSN parameters
*
* @return array Connection options
*/
protected function dsn_options($dsn)
{
$result = array();
if ($this->db_pconn) {
$result[PDO::ATTR_PERSISTENT] = true;
}
if (!empty($dsn['prefetch'])) {
$result[PDO::ATTR_PREFETCH] = (int) $dsn['prefetch'];
}
if (!empty($dsn['timeout'])) {
$result[PDO::ATTR_TIMEOUT] = (int) $dsn['timeout'];
}
return $result;
}
/**
* Execute the given SQL script
*
* @param string $sql SQL queries to execute
*
* @return boolen True on success, False on error
*/
public function exec_script($sql)
{
$sql = $this->fix_table_names($sql);
$buff = '';
$exec = '';
foreach (explode("\n", $sql) as $line) {
$trimmed = trim($line);
if ($trimmed == '' || preg_match('/^--/', $trimmed)) {
continue;
}
if ($trimmed == 'GO') {
$exec = $buff;
}
else if ($trimmed[strlen($trimmed)-1] == ';') {
$exec = $buff . substr(rtrim($line), 0, -1);
}
if ($exec) {
$this->query($exec);
$buff = '';
$exec = '';
if ($this->db_error) {
break;
}
}
else {
$buff .= $line . "\n";
}
}
return !$this->db_error;
}
/**
* 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 = preg_replace_callback(
'/((TABLE|TRUNCATE( TABLE)?|(?<!ON )UPDATE|INSERT INTO|FROM'
. '| ON(?! (DELETE|UPDATE))|REFERENCES|CONSTRAINT|FOREIGN KEY|INDEX|UNIQUE( INDEX)?)'
. '\s+(IF (NOT )?EXISTS )?[`"]*)([^`"\( \r\n]+)/',
array($this, 'fix_table_names_callback'),
$sql
);
return $sql;
}
/**
* Preg_replace callback for fix_table_names()
*/
protected function fix_table_names_callback($matches)
{
return $matches[1] . $this->options['table_prefix'] . $matches[count($matches)-1];
}
}
diff --git a/program/lib/Roundcube/rcube_imap_cache.php b/program/lib/Roundcube/rcube_imap_cache.php
index 66e114e03..689bcc7f6 100644
--- a/program/lib/Roundcube/rcube_imap_cache.php
+++ b/program/lib/Roundcube/rcube_imap_cache.php
@@ -1,1318 +1,1233 @@
<?php
/**
+-----------------------------------------------------------------------+
| This file is part of the Roundcube Webmail client |
| |
| Copyright (C) The Roundcube Dev Team |
| |
| 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: |
| Caching of IMAP folder contents (messages and index) |
+-----------------------------------------------------------------------+
| Author: Thomas Bruederli <roundcube@gmail.com> |
| Author: Aleksander Machniak <alec@alec.pl> |
+-----------------------------------------------------------------------+
*/
/**
* Interface class for accessing Roundcube messages cache
*
* @package Framework
* @subpackage Storage
*/
class rcube_imap_cache
{
const MODE_INDEX = 1;
const MODE_MESSAGE = 2;
/**
* Instance of rcube_imap
*
* @var rcube_imap
*/
private $imap;
/**
* Instance of rcube_db
*
* @var rcube_db
*/
private $db;
/**
* User ID
*
* @var int
*/
private $userid;
/**
* Expiration time in seconds
*
* @var int
*/
private $ttl;
/**
* Maximum cached message size
*
* @var int
*/
private $threshold;
/**
* Internal (in-memory) cache
*
* @var array
*/
private $icache = array();
private $skip_deleted = false;
private $mode;
/**
* List of known flags. Thanks to this we can handle flag changes
* with good performance. Bad thing is we need to know used flags.
*/
public $flags = array(
1 => 'SEEN', // RFC3501
2 => 'DELETED', // RFC3501
4 => 'ANSWERED', // RFC3501
8 => 'FLAGGED', // RFC3501
16 => 'DRAFT', // RFC3501
32 => 'MDNSENT', // RFC3503
64 => 'FORWARDED', // RFC5550
128 => 'SUBMITPENDING', // RFC5550
256 => 'SUBMITTED', // RFC5550
512 => 'JUNK',
1024 => 'NONJUNK',
2048 => 'LABEL1',
4096 => 'LABEL2',
8192 => 'LABEL3',
16384 => 'LABEL4',
32768 => 'LABEL5',
65536 => 'HASATTACHMENT',
131072 => 'HASNOATTACHMENT',
);
/**
* Object constructor.
*
* @param rcube_db $db DB handler
* @param rcube_imap $imap IMAP handler
* @param int $userid User identifier
* @param bool $skip_deleted skip_deleted flag
* @param string $ttl Expiration time of memcache/apc items
* @param int $threshold Maximum cached message size
*/
function __construct($db, $imap, $userid, $skip_deleted, $ttl=0, $threshold=0)
{
// convert ttl string to seconds
$ttl = get_offset_sec($ttl);
if ($ttl > 2592000) $ttl = 2592000;
$this->db = $db;
$this->imap = $imap;
$this->userid = $userid;
$this->skip_deleted = $skip_deleted;
$this->ttl = $ttl;
$this->threshold = $threshold;
// cache all possible information by default
$this->mode = self::MODE_INDEX | self::MODE_MESSAGE;
// database tables
$this->index_table = $db->table_name('cache_index', true);
$this->thread_table = $db->table_name('cache_thread', true);
$this->messages_table = $db->table_name('cache_messages', true);
}
/**
* Cleanup actions (on shutdown).
*/
public function close()
{
$this->save_icache();
$this->icache = null;
}
/**
* Set cache mode
*
* @param int $mode Cache mode
*/
public function set_mode($mode)
{
$this->mode = $mode;
}
/**
* Return (sorted) messages index (UIDs).
* If index doesn't exist or is invalid, will be updated.
*
* @param string $mailbox Folder name
* @param string $sort_field Sorting column
* @param string $sort_order Sorting order (ASC|DESC)
* @param bool $exiting Skip index initialization if it doesn't exist in DB
*
* @return array Messages index
*/
function get_index($mailbox, $sort_field = null, $sort_order = null, $existing = false)
{
if (empty($this->icache[$mailbox])) {
$this->icache[$mailbox] = array();
}
$sort_order = strtoupper($sort_order) == 'ASC' ? 'ASC' : 'DESC';
// Seek in internal cache
if (array_key_exists('index', $this->icache[$mailbox])) {
// The index was fetched from database already, but not validated yet
if (empty($this->icache[$mailbox]['index']['validated'])) {
$index = $this->icache[$mailbox]['index'];
}
// We've got a valid index
else if ($sort_field == 'ANY' || $this->icache[$mailbox]['index']['sort_field'] == $sort_field) {
$result = $this->icache[$mailbox]['index']['object'];
if ($result->get_parameters('ORDER') != $sort_order) {
$result->revert();
}
return $result;
}
}
// Get index from DB (if DB wasn't already queried)
if (empty($index) && empty($this->icache[$mailbox]['index_queried'])) {
$index = $this->get_index_row($mailbox);
// set the flag that DB was already queried for index
// this way we'll be able to skip one SELECT, when
// get_index() is called more than once
$this->icache[$mailbox]['index_queried'] = true;
}
$data = null;
// @TODO: Think about skipping validation checks.
// If we could check only every 10 minutes, we would be able to skip
// expensive checks, mailbox selection or even IMAP connection, this would require
// additional logic to force cache invalidation in some cases
// and many rcube_imap changes to connect when needed
// Entry exists, check cache status
if (!empty($index)) {
$exists = true;
if ($sort_field == 'ANY') {
$sort_field = $index['sort_field'];
}
if ($sort_field != $index['sort_field']) {
$is_valid = false;
}
else {
$is_valid = $this->validate($mailbox, $index, $exists);
}
if ($is_valid) {
$data = $index['object'];
// revert the order if needed
if ($data->get_parameters('ORDER') != $sort_order) {
$data->revert();
}
}
}
else {
if ($existing) {
return null;
}
else if ($sort_field == 'ANY') {
$sort_field = '';
}
// Got it in internal cache, so the row already exist
$exists = array_key_exists('index', $this->icache[$mailbox]);
}
// Index not found, not valid or sort field changed, get index from IMAP server
if ($data === null) {
// Get mailbox data (UIDVALIDITY, counters, etc.) for status check
$mbox_data = $this->imap->folder_data($mailbox);
$data = $this->get_index_data($mailbox, $sort_field, $sort_order, $mbox_data);
// insert/update
$this->add_index_row($mailbox, $sort_field, $data, $mbox_data, $exists, $index['modseq']);
}
$this->icache[$mailbox]['index'] = array(
'validated' => true,
'object' => $data,
'sort_field' => $sort_field,
'modseq' => !empty($index['modseq']) ? $index['modseq'] : $mbox_data['HIGHESTMODSEQ']
);
return $data;
}
/**
* Return messages thread.
* If threaded index doesn't exist or is invalid, will be updated.
*
* @param string $mailbox Folder name
*
* @return array Messages threaded index
*/
function get_thread($mailbox)
{
if (empty($this->icache[$mailbox])) {
$this->icache[$mailbox] = array();
}
// Seek in internal cache
if (array_key_exists('thread', $this->icache[$mailbox])) {
return $this->icache[$mailbox]['thread']['object'];
}
// Get thread from DB (if DB wasn't already queried)
if (empty($this->icache[$mailbox]['thread_queried'])) {
$index = $this->get_thread_row($mailbox);
// set the flag that DB was already queried for thread
// this way we'll be able to skip one SELECT, when
// get_thread() is called more than once or after clear()
$this->icache[$mailbox]['thread_queried'] = true;
}
// Entry exist, check cache status
if (!empty($index)) {
$exists = true;
$is_valid = $this->validate($mailbox, $index, $exists);
if (!$is_valid) {
$index = null;
}
}
// Index not found or not valid, get index from IMAP server
if ($index === null) {
// Get mailbox data (UIDVALIDITY, counters, etc.) for status check
$mbox_data = $this->imap->folder_data($mailbox);
// Get THREADS result
$index['object'] = $this->get_thread_data($mailbox, $mbox_data);
// insert/update
$this->add_thread_row($mailbox, $index['object'], $mbox_data, $exists);
}
$this->icache[$mailbox]['thread'] = $index;
return $index['object'];
}
/**
* Returns list of messages (headers). See rcube_imap::fetch_headers().
*
* @param string $mailbox Folder name
* @param array $msgs Message UIDs
*
* @return array The list of messages (rcube_message_header) indexed by UID
*/
function get_messages($mailbox, $msgs = array())
{
if (empty($msgs)) {
return array();
}
$result = array();
if ($this->mode & self::MODE_MESSAGE) {
// Fetch messages from cache
$sql_result = $this->db->query(
"SELECT `uid`, `data`, `flags`"
." FROM {$this->messages_table}"
." WHERE `user_id` = ?"
." AND `mailbox` = ?"
." AND `uid` IN (".$this->db->array2list($msgs, 'integer').")",
$this->userid, $mailbox);
$msgs = array_flip($msgs);
while ($sql_arr = $this->db->fetch_assoc($sql_result)) {
$uid = intval($sql_arr['uid']);
$result[$uid] = $this->build_message($sql_arr);
if (!empty($result[$uid])) {
// save memory, we don't need message body here (?)
$result[$uid]->body = null;
unset($msgs[$uid]);
}
}
$this->db->reset();
$msgs = array_flip($msgs);
}
// Fetch not found messages from IMAP server
if (!empty($msgs)) {
$messages = $this->imap->fetch_headers($mailbox, $msgs, false, true);
// Insert to DB and add to result list
if (!empty($messages)) {
foreach ($messages as $msg) {
if ($this->mode & self::MODE_MESSAGE) {
$this->add_message($mailbox, $msg, !array_key_exists($msg->uid, $result));
}
$result[$msg->uid] = $msg;
}
}
}
return $result;
}
/**
* Returns message data.
*
* @param string $mailbox Folder name
* @param int $uid Message UID
* @param bool $update If message doesn't exists in cache it will be fetched
* from IMAP server
* @param bool $no_cache Enables internal cache usage
*
* @return rcube_message_header Message data
*/
function get_message($mailbox, $uid, $update = true, $cache = true)
{
// Check internal cache
if ($this->icache['__message']
&& $this->icache['__message']['mailbox'] == $mailbox
&& $this->icache['__message']['object']->uid == $uid
) {
return $this->icache['__message']['object'];
}
if ($this->mode & self::MODE_MESSAGE) {
$sql_result = $this->db->query(
"SELECT `flags`, `data`"
." FROM {$this->messages_table}"
." WHERE `user_id` = ?"
." AND `mailbox` = ?"
." AND `uid` = ?",
$this->userid, $mailbox, (int)$uid);
if ($sql_arr = $this->db->fetch_assoc($sql_result)) {
$message = $this->build_message($sql_arr);
$found = true;
}
}
// Get the message from IMAP server
if (empty($message) && $update) {
$message = $this->imap->get_message_headers($uid, $mailbox, true);
// cache will be updated in close(), see below
}
if (!($this->mode & self::MODE_MESSAGE)) {
return $message;
}
// Save the message in internal cache, will be written to DB in close()
// Common scenario: user opens unseen message
// - get message (SELECT)
// - set message headers/structure (INSERT or UPDATE)
// - set \Seen flag (UPDATE)
// This way we can skip one UPDATE
if (!empty($message) && $cache) {
// Save current message from internal cache
$this->save_icache();
$this->icache['__message'] = array(
'object' => $message,
'mailbox' => $mailbox,
'exists' => $found,
'md5sum' => md5(serialize($message)),
);
}
return $message;
}
/**
* Saves the message in cache.
*
* @param string $mailbox Folder name
* @param rcube_message_header $message Message data
* @param bool $force Skips message in-cache existence check
*/
function add_message($mailbox, $message, $force = false)
{
if (!is_object($message) || empty($message->uid)) {
return;
}
if (!($this->mode & self::MODE_MESSAGE)) {
return;
}
$flags = 0;
$msg = clone $message;
if (!empty($message->flags)) {
foreach ($this->flags as $idx => $flag) {
if (!empty($message->flags[$flag])) {
$flags += $idx;
}
}
}
unset($msg->flags);
- $msg = $this->db->encode($msg, true);
-
- // update cache record (even if it exists, the update
- // here will work as select, assume row exist if affected_rows=0)
- if (!$force) {
- $res = $this->db->query(
- "UPDATE {$this->messages_table}"
- ." SET `flags` = ?, `data` = ?, `expires` = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL')
- ." WHERE `user_id` = ?"
- ." AND `mailbox` = ?"
- ." AND `uid` = ?",
- $flags, $msg, $this->userid, $mailbox, (int) $message->uid);
-
- if ($this->db->affected_rows($res)) {
- return;
- }
- }
-
- $this->db->set_option('ignore_key_errors', true);
- // insert new record
- $res = $this->db->query(
- "INSERT INTO {$this->messages_table}"
- ." (`user_id`, `mailbox`, `uid`, `flags`, `expires`, `data`)"
- ." VALUES (?, ?, ?, ?, ". ($this->ttl ? $this->db->now($this->ttl) : 'NULL') . ", ?)",
- $this->userid, $mailbox, (int) $message->uid, $flags, $msg);
+ $msg = $this->db->encode($msg, true);
+ $expires = $this->db->param($this->ttl ? $this->db->now($this->ttl) : 'NULL', rcube_db::TYPE_SQL);
- // race-condition, insert failed so try update (#1489146)
- // thanks to ignore_key_errors "duplicate row" errors will be ignored
- if ($force && !$res && !$this->db->is_error($res)) {
- $this->db->query(
- "UPDATE {$this->messages_table}"
- ." SET `expires` = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL')
- .", `flags` = ?, `data` = ?"
- ." WHERE `user_id` = ?"
- ." AND `mailbox` = ?"
- ." AND `uid` = ?",
- $flags, $msg, $this->userid, $mailbox, (int) $message->uid);
- }
-
- $this->db->set_option('ignore_key_errors', false);
+ $this->db->insert_or_update(
+ $this->messages_table,
+ array('user_id' => $this->userid, 'mailbox' => $mailbox, 'uid' => (int) $message->uid),
+ array('flags', 'expires', 'data'),
+ array($flags, $expires, $msg)
+ );
}
/**
* Sets the flag for specified message.
*
* @param string $mailbox Folder name
* @param array $uids Message UIDs or null to change flag
* of all messages in a folder
* @param string $flag The name of the flag
* @param bool $enabled Flag state
*/
function change_flag($mailbox, $uids, $flag, $enabled = false)
{
if (empty($uids)) {
return;
}
if (!($this->mode & self::MODE_MESSAGE)) {
return;
}
$flag = strtoupper($flag);
$idx = (int) array_search($flag, $this->flags);
$uids = (array) $uids;
if (!$idx) {
return;
}
// Internal cache update
if (($message = $this->icache['__message'])
&& $message['mailbox'] === $mailbox
&& in_array($message['object']->uid, $uids)
) {
$message['object']->flags[$flag] = $enabled;
if (count($uids) == 1) {
return;
}
}
$binary_check = $this->db->db_provider == 'oracle' ? "BITAND(`flags`, %d)" : "(`flags` & %d)";
$this->db->query(
"UPDATE {$this->messages_table}"
." SET `expires` = ". ($this->ttl ? $this->db->now($this->ttl) : 'NULL')
.", `flags` = `flags` ".($enabled ? "+ $idx" : "- $idx")
." WHERE `user_id` = ?"
." AND `mailbox` = ?"
.(!empty($uids) ? " AND `uid` IN (".$this->db->array2list($uids, 'integer').")" : "")
." AND " . sprintf($binary_check, $idx) . ($enabled ? " = 0" : " = $idx"),
$this->userid, $mailbox);
}
/**
* Removes message(s) from cache.
*
* @param string $mailbox Folder name
* @param array $uids Message UIDs, NULL removes all messages
*/
function remove_message($mailbox = null, $uids = null)
{
if (!($this->mode & self::MODE_MESSAGE)) {
return;
}
if (!strlen($mailbox)) {
$this->db->query(
"DELETE FROM {$this->messages_table}"
." WHERE `user_id` = ?",
$this->userid);
}
else {
// Remove the message from internal cache
if (!empty($uids) && ($message = $this->icache['__message'])
&& $message['mailbox'] === $mailbox
&& in_array($message['object']->uid, (array)$uids)
) {
$this->icache['__message'] = null;
}
$this->db->query(
"DELETE FROM {$this->messages_table}"
." WHERE `user_id` = ?"
." AND `mailbox` = ?"
.($uids !== null ? " AND `uid` IN (".$this->db->array2list((array)$uids, 'integer').")" : ""),
$this->userid, $mailbox);
}
}
/**
* Clears index cache.
*
* @param string $mailbox Folder name
* @param bool $remove Enable to remove the DB row
*/
function remove_index($mailbox = null, $remove = false)
{
if (!($this->mode & self::MODE_INDEX)) {
return;
}
// The index should be only removed from database when
// UIDVALIDITY was detected or the mailbox is empty
// otherwise use 'valid' flag to not loose HIGHESTMODSEQ value
if ($remove) {
$this->db->query(
"DELETE FROM {$this->index_table}"
." WHERE `user_id` = ?"
.(strlen($mailbox) ? " AND `mailbox` = ".$this->db->quote($mailbox) : ""),
$this->userid
);
}
else {
$this->db->query(
"UPDATE {$this->index_table}"
." SET `valid` = 0"
." WHERE `user_id` = ?"
.(strlen($mailbox) ? " AND `mailbox` = ".$this->db->quote($mailbox) : ""),
$this->userid
);
}
if (strlen($mailbox)) {
unset($this->icache[$mailbox]['index']);
// Index removed, set flag to skip SELECT query in get_index()
$this->icache[$mailbox]['index_queried'] = true;
}
else {
$this->icache = array();
}
}
/**
* Clears thread cache.
*
* @param string $mailbox Folder name
*/
function remove_thread($mailbox = null)
{
if (!($this->mode & self::MODE_INDEX)) {
return;
}
$this->db->query(
"DELETE FROM {$this->thread_table}"
." WHERE `user_id` = ?"
.(strlen($mailbox) ? " AND `mailbox` = ".$this->db->quote($mailbox) : ""),
$this->userid
);
if (strlen($mailbox)) {
unset($this->icache[$mailbox]['thread']);
// Thread data removed, set flag to skip SELECT query in get_thread()
$this->icache[$mailbox]['thread_queried'] = true;
}
else {
$this->icache = array();
}
}
/**
* Clears the cache.
*
* @param string $mailbox Folder name
* @param array $uids Message UIDs, NULL removes all messages in a folder
*/
function clear($mailbox = null, $uids = null)
{
$this->remove_index($mailbox, true);
$this->remove_thread($mailbox);
$this->remove_message($mailbox, $uids);
}
/**
* Delete expired cache entries
*/
static function gc()
{
$rcube = rcube::get_instance();
$db = $rcube->get_dbh();
$now = $db->now();
$db->query("DELETE FROM " . $db->table_name('cache_messages', true)
." WHERE `expires` < $now");
$db->query("DELETE FROM " . $db->table_name('cache_index', true)
." WHERE `expires` < $now");
$db->query("DELETE FROM ".$db->table_name('cache_thread', true)
." WHERE `expires` < $now");
}
/**
* Fetches index data from database
*/
private function get_index_row($mailbox)
{
if (!($this->mode & self::MODE_INDEX)) {
return;
}
// Get index from DB
$sql_result = $this->db->query(
"SELECT `data`, `valid`"
." FROM {$this->index_table}"
." WHERE `user_id` = ?"
." AND `mailbox` = ?",
$this->userid, $mailbox);
if ($sql_arr = $this->db->fetch_assoc($sql_result)) {
$data = explode('@', $sql_arr['data']);
$index = $this->db->decode($data[0], true);
unset($data[0]);
if (empty($index)) {
$index = new rcube_result_index($mailbox);
}
return array(
'valid' => $sql_arr['valid'],
'object' => $index,
'sort_field' => $data[1],
'deleted' => $data[2],
'validity' => $data[3],
'uidnext' => $data[4],
'modseq' => $data[5],
);
}
}
/**
* Fetches thread data from database
*/
private function get_thread_row($mailbox)
{
if (!($this->mode & self::MODE_INDEX)) {
return;
}
// Get thread from DB
$sql_result = $this->db->query(
"SELECT `data`"
." FROM {$this->thread_table}"
." WHERE `user_id` = ?"
." AND `mailbox` = ?",
$this->userid, $mailbox);
if ($sql_arr = $this->db->fetch_assoc($sql_result)) {
$data = explode('@', $sql_arr['data']);
$thread = $this->db->decode($data[0], true);
unset($data[0]);
if (empty($thread)) {
$thread = new rcube_result_thread($mailbox);
}
return array(
'object' => $thread,
'deleted' => $data[1],
'validity' => $data[2],
'uidnext' => $data[3],
);
}
}
/**
* Saves index data into database
*/
private function add_index_row($mailbox, $sort_field,
$data, $mbox_data = array(), $exists = false, $modseq = null)
{
if (!($this->mode & self::MODE_INDEX)) {
return;
}
$data = array(
$this->db->encode($data, true),
$sort_field,
(int) $this->skip_deleted,
(int) $mbox_data['UIDVALIDITY'],
(int) $mbox_data['UIDNEXT'],
$modseq ? $modseq : $mbox_data['HIGHESTMODSEQ'],
);
$data = implode('@', $data);
- $expires = $this->ttl ? $this->db->now($this->ttl) : 'NULL';
-
- if ($exists) {
- $res = $this->db->query(
- "UPDATE {$this->index_table}"
- ." SET `data` = ?, `valid` = 1, `expires` = $expires"
- ." WHERE `user_id` = ?"
- ." AND `mailbox` = ?",
- $data, $this->userid, $mailbox);
-
- if ($this->db->affected_rows($res)) {
- return;
- }
- }
+ $expires = $this->db->param($this->ttl ? $this->db->now($this->ttl) : 'NULL', rcube_db::TYPE_SQL);
- $this->db->set_option('ignore_key_errors', true);
-
- $res = $this->db->query(
- "INSERT INTO {$this->index_table}"
- ." (`user_id`, `mailbox`, `valid`, `expires`, `data`)"
- ." VALUES (?, ?, 1, $expires, ?)",
- $this->userid, $mailbox, $data);
-
- // race-condition, insert failed so try update (#1489146)
- // thanks to ignore_key_errors "duplicate row" errors will be ignored
- if (!$exists && !$res && !$this->db->is_error($res)) {
- $res = $this->db->query(
- "UPDATE {$this->index_table}"
- ." SET `data` = ?, `valid` = 1, `expires` = $expires"
- ." WHERE `user_id` = ?"
- ." AND `mailbox` = ?",
- $data, $this->userid, $mailbox);
- }
-
- $this->db->set_option('ignore_key_errors', false);
+ $this->db->insert_or_update(
+ $this->index_table,
+ array('user_id' => $this->userid, 'mailbox' => $mailbox),
+ array('valid', 'expires', 'data'),
+ array(1, $expires, $data)
+ );
}
/**
* Saves thread data into database
*/
private function add_thread_row($mailbox, $data, $mbox_data = array(), $exists = false)
{
if (!($this->mode & self::MODE_INDEX)) {
return;
}
$data = array(
$this->db->encode($data, true),
(int) $this->skip_deleted,
(int) $mbox_data['UIDVALIDITY'],
(int) $mbox_data['UIDNEXT'],
);
$data = implode('@', $data);
- $expires = $this->ttl ? $this->db->now($this->ttl) : 'NULL';
-
- if ($exists) {
- $res = $this->db->query(
- "UPDATE {$this->thread_table}"
- ." SET `data` = ?, `expires` = $expires"
- ." WHERE `user_id` = ?"
- ." AND `mailbox` = ?",
- $data, $this->userid, $mailbox);
+ $expires = $this->db->param($this->ttl ? $this->db->now($this->ttl) : 'NULL', rcube_db::TYPE_SQL);
- if ($this->db->affected_rows($res)) {
- return;
- }
- }
-
- $this->db->set_option('ignore_key_errors', true);
-
- $res = $this->db->query(
- "INSERT INTO {$this->thread_table}"
- ." (`user_id`, `mailbox`, `expires`, `data`)"
- ." VALUES (?, ?, $expires, ?)",
- $this->userid, $mailbox, $data);
-
- // race-condition, insert failed so try update (#1489146)
- // thanks to ignore_key_errors "duplicate row" errors will be ignored
- if (!$exists && !$res && !$this->db->is_error($res)) {
- $this->db->query(
- "UPDATE {$this->thread_table}"
- ." SET `expires` = $expires, `data` = ?"
- ." WHERE `user_id` = ?"
- ." AND `mailbox` = ?",
- $data, $this->userid, $mailbox);
- }
-
- $this->db->set_option('ignore_key_errors', false);
+ $this->db->insert_or_update(
+ $this->thread_table,
+ array('user_id' => $this->userid, 'mailbox' => $mailbox),
+ array('expires', 'data'),
+ array($expires, $data)
+ );
}
/**
* Checks index/thread validity
*/
private function validate($mailbox, $index, &$exists = true)
{
$object = $index['object'];
$is_thread = is_a($object, 'rcube_result_thread');
// sanity check
if (empty($object)) {
return false;
}
$index['validated'] = true;
// Get mailbox data (UIDVALIDITY, counters, etc.) for status check
$mbox_data = $this->imap->folder_data($mailbox);
// @TODO: Think about skipping validation checks.
// If we could check only every 10 minutes, we would be able to skip
// expensive checks, mailbox selection or even IMAP connection, this would require
// additional logic to force cache invalidation in some cases
// and many rcube_imap changes to connect when needed
// Check UIDVALIDITY
if ($index['validity'] != $mbox_data['UIDVALIDITY']) {
$this->clear($mailbox);
$exists = false;
return false;
}
// Folder is empty but cache isn't
if (empty($mbox_data['EXISTS'])) {
if (!$object->is_empty()) {
$this->clear($mailbox);
$exists = false;
return false;
}
}
// Folder is not empty but cache is
else if ($object->is_empty()) {
unset($this->icache[$mailbox][$is_thread ? 'thread' : 'index']);
return false;
}
// Validation flag
if (!$is_thread && empty($index['valid'])) {
unset($this->icache[$mailbox]['index']);
return false;
}
// Index was created with different skip_deleted setting
if ($this->skip_deleted != $index['deleted']) {
return false;
}
// Check HIGHESTMODSEQ
if (!empty($index['modseq']) && !empty($mbox_data['HIGHESTMODSEQ'])
&& $index['modseq'] == $mbox_data['HIGHESTMODSEQ']
) {
return true;
}
// Check UIDNEXT
if ($index['uidnext'] != $mbox_data['UIDNEXT']) {
unset($this->icache[$mailbox][$is_thread ? 'thread' : 'index']);
return false;
}
// @TODO: find better validity check for threaded index
if ($is_thread) {
// check messages number...
if (!$this->skip_deleted && $mbox_data['EXISTS'] != $object->count_messages()) {
return false;
}
return true;
}
// The rest of checks, more expensive
if (!empty($this->skip_deleted)) {
// compare counts if available
if (!empty($mbox_data['UNDELETED'])
&& $mbox_data['UNDELETED']->count() != $object->count()
) {
return false;
}
// compare UID sets
if (!empty($mbox_data['UNDELETED'])) {
$uids_new = $mbox_data['UNDELETED']->get();
$uids_old = $object->get();
if (count($uids_new) != count($uids_old)) {
return false;
}
sort($uids_new, SORT_NUMERIC);
sort($uids_old, SORT_NUMERIC);
if ($uids_old != $uids_new)
return false;
}
else {
// get all undeleted messages excluding cached UIDs
$ids = $this->imap->search_once($mailbox, 'ALL UNDELETED NOT UID '.
rcube_imap_generic::compressMessageSet($object->get()));
if (!$ids->is_empty()) {
return false;
}
}
}
else {
// check messages number...
if ($mbox_data['EXISTS'] != $object->count()) {
return false;
}
// ... and max UID
if ($object->max() != $this->imap->id2uid($mbox_data['EXISTS'], $mailbox)) {
return false;
}
}
return true;
}
/**
* Synchronizes the mailbox.
*
* @param string $mailbox Folder name
*/
function synchronize($mailbox)
{
// RFC4549: Synchronization Operations for Disconnected IMAP4 Clients
// RFC4551: IMAP Extension for Conditional STORE Operation
// or Quick Flag Changes Resynchronization
// RFC5162: IMAP Extensions for Quick Mailbox Resynchronization
// @TODO: synchronize with other methods?
$qresync = $this->imap->get_capability('QRESYNC');
$condstore = $qresync ? true : $this->imap->get_capability('CONDSTORE');
if (!$qresync && !$condstore) {
return;
}
// Get stored index
$index = $this->get_index_row($mailbox);
// database is empty
if (empty($index)) {
// set the flag that DB was already queried for index
// this way we'll be able to skip one SELECT in get_index()
$this->icache[$mailbox]['index_queried'] = true;
return;
}
$this->icache[$mailbox]['index'] = $index;
// no last HIGHESTMODSEQ value
if (empty($index['modseq'])) {
return;
}
if (!$this->imap->check_connection()) {
return;
}
// Enable QRESYNC
$res = $this->imap->conn->enable($qresync ? 'QRESYNC' : 'CONDSTORE');
if ($res === false) {
return;
}
// Close mailbox if already selected to get most recent data
if ($this->imap->conn->selected == $mailbox) {
$this->imap->conn->close();
}
// Get mailbox data (UIDVALIDITY, HIGHESTMODSEQ, counters, etc.)
$mbox_data = $this->imap->folder_data($mailbox);
if (empty($mbox_data)) {
return;
}
// Check UIDVALIDITY
if ($index['validity'] != $mbox_data['UIDVALIDITY']) {
$this->clear($mailbox);
return;
}
// QRESYNC not supported on specified mailbox
if (!empty($mbox_data['NOMODSEQ']) || empty($mbox_data['HIGHESTMODSEQ'])) {
return;
}
// Nothing new
if ($mbox_data['HIGHESTMODSEQ'] == $index['modseq']) {
return;
}
$uids = array();
$removed = array();
// Get known UIDs
if ($this->mode & self::MODE_MESSAGE) {
$sql_result = $this->db->query(
"SELECT `uid`"
." FROM {$this->messages_table}"
." WHERE `user_id` = ?"
." AND `mailbox` = ?",
$this->userid, $mailbox);
while ($sql_arr = $this->db->fetch_assoc($sql_result)) {
$uids[] = $sql_arr['uid'];
}
}
// Synchronize messages data
if (!empty($uids)) {
// Get modified flags and vanished messages
// UID FETCH 1:* (FLAGS) (CHANGEDSINCE 0123456789 VANISHED)
$result = $this->imap->conn->fetch($mailbox,
$uids, true, array('FLAGS'), $index['modseq'], $qresync);
if (!empty($result)) {
foreach ($result as $msg) {
$uid = $msg->uid;
// Remove deleted message
if ($this->skip_deleted && !empty($msg->flags['DELETED'])) {
$removed[] = $uid;
// Invalidate index
$index['valid'] = false;
continue;
}
$flags = 0;
if (!empty($msg->flags)) {
foreach ($this->flags as $idx => $flag) {
if (!empty($msg->flags[$flag])) {
$flags += $idx;
}
}
}
$this->db->query(
"UPDATE {$this->messages_table}"
." SET `flags` = ?, `expires` = " . ($this->ttl ? $this->db->now($this->ttl) : 'NULL')
." WHERE `user_id` = ?"
." AND `mailbox` = ?"
." AND `uid` = ?"
." AND `flags` <> ?",
$flags, $this->userid, $mailbox, $uid, $flags);
}
}
// VANISHED found?
if ($qresync) {
$mbox_data = $this->imap->folder_data($mailbox);
// Removed messages found
$uids = rcube_imap_generic::uncompressMessageSet($mbox_data['VANISHED']);
if (!empty($uids)) {
$removed = array_merge($removed, $uids);
// Invalidate index
$index['valid'] = false;
}
}
// remove messages from database
if (!empty($removed)) {
$this->remove_message($mailbox, $removed);
}
}
$sort_field = $index['sort_field'];
$sort_order = $index['object']->get_parameters('ORDER');
$exists = true;
// Validate index
if (!$this->validate($mailbox, $index, $exists)) {
// Invalidate (remove) thread index
// if $exists=false it was already removed in validate()
if ($exists) {
$this->remove_thread($mailbox);
}
// Update index
$data = $this->get_index_data($mailbox, $sort_field, $sort_order, $mbox_data);
}
else {
$data = $index['object'];
}
// update index and/or HIGHESTMODSEQ value
$this->add_index_row($mailbox, $sort_field, $data, $mbox_data, $exists);
// update internal cache for get_index()
$this->icache[$mailbox]['index']['object'] = $data;
}
/**
* Converts cache row into message object.
*
* @param array $sql_arr Message row data
*
* @return rcube_message_header Message object
*/
private function build_message($sql_arr)
{
$message = $this->db->decode($sql_arr['data'], true);
if ($message) {
$message->flags = array();
foreach ($this->flags as $idx => $flag) {
if (($sql_arr['flags'] & $idx) == $idx) {
$message->flags[$flag] = true;
}
}
}
return $message;
}
/**
* Saves message stored in internal cache
*/
private function save_icache()
{
// Save current message from internal cache
if ($message = $this->icache['__message']) {
// clean up some object's data
$this->message_object_prepare($message['object']);
// calculate current md5 sum
$md5sum = md5(serialize($message['object']));
if ($message['md5sum'] != $md5sum) {
$this->add_message($message['mailbox'], $message['object'], !$message['exists']);
}
$this->icache['__message']['md5sum'] = $md5sum;
}
}
/**
* Prepares message object to be stored in database.
*
* @param rcube_message_header|rcube_message_part
*/
private function message_object_prepare(&$msg, &$size = 0)
{
// Remove body too big
if (isset($msg->body)) {
$length = strlen($msg->body);
if ($msg->body_modified || $size + $length > $this->threshold * 1024) {
unset($msg->body);
}
else {
$size += $length;
}
}
// Fix mimetype which might be broken by some code when message is displayed
// Another solution would be to use object's copy in rcube_message class
// to prevent related issues, however I'm not sure which is better
if ($msg->mimetype) {
list($msg->ctype_primary, $msg->ctype_secondary) = explode('/', $msg->mimetype);
}
unset($msg->replaces);
if (is_object($msg->structure)) {
$this->message_object_prepare($msg->structure, $size);
}
if (is_array($msg->parts)) {
foreach ($msg->parts as $part) {
$this->message_object_prepare($part, $size);
}
}
}
/**
* Fetches index data from IMAP server
*/
private function get_index_data($mailbox, $sort_field, $sort_order, $mbox_data = array())
{
if (empty($mbox_data)) {
$mbox_data = $this->imap->folder_data($mailbox);
}
if ($mbox_data['EXISTS']) {
// fetch sorted sequence numbers
$index = $this->imap->index_direct($mailbox, $sort_field, $sort_order);
}
else {
$index = new rcube_result_index($mailbox, '* SORT');
}
return $index;
}
/**
* Fetches thread data from IMAP server
*/
private function get_thread_data($mailbox, $mbox_data = array())
{
if (empty($mbox_data)) {
$mbox_data = $this->imap->folder_data($mailbox);
}
if ($mbox_data['EXISTS']) {
// get all threads (default sort order)
return $this->imap->threads_direct($mailbox);
}
return new rcube_result_thread($mailbox, '* THREAD');
}
}
// for backward compat.
class rcube_mail_header extends rcube_message_header { }
File Metadata
Details
Attached
Mime Type
text/x-diff
Expires
Sun, Jan 19, 3:32 AM (19 h, 32 m)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
120061
Default Alt Text
(143 KB)
Attached To
Mode
R3 roundcubemail
Attached
Detach File
Event Timeline
Log In to Comment