docs/SQL lib/kolab_sync_backend.php lib/kolab_sync_data_email.php lib/kolab_sync_data.php
Aleksander Machniak
machniak at kolabsys.com
Mon Oct 13 13:36:08 CEST 2014
docs/SQL/mysql.initial.sql | 12 ++
docs/SQL/mysql/2014101300.sql | 10 ++
docs/SQL/oracle.initial.sql | 11 ++
docs/SQL/oracle/2014101300.sql | 8 +
lib/kolab_sync_backend.php | 58 +++++++++++-
lib/kolab_sync_data.php | 198 ++++++++++++++++++++++++++++-------------
lib/kolab_sync_data_email.php | 133 ++++++++++++++++++++++++---
7 files changed, 353 insertions(+), 77 deletions(-)
New commits:
commit 92b081cf384a7c5158a2852f52a3c11a717f936a
Author: Aleksander Machniak <machniak at kolabsys.com>
Date: Fri Sep 5 06:12:40 2014 -0400
Complete email categories support, fix category changes detection (#3489)
diff --git a/docs/SQL/mysql.initial.sql b/docs/SQL/mysql.initial.sql
index 7c68bf2..aa8aeb4 100644
--- a/docs/SQL/mysql.initial.sql
+++ b/docs/SQL/mysql.initial.sql
@@ -105,6 +105,16 @@ CREATE TABLE IF NOT EXISTS `syncroton_modseq` (
CONSTRAINT `syncroton_modseq::device_id--syncroton_device::id` FOREIGN KEY (`device_id`) REFERENCES `syncroton_device` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+CREATE TABLE IF NOT EXISTS `syncroton_relations_state` (
+ `device_id` varchar(40) NOT NULL,
+ `folder_id` varchar(40) NOT NULL,
+ `synctime` datetime NOT NULL,
+ `data` longblob,
+ PRIMARY KEY (`device_id`,`folder_id`,`synctime`),
+ KEY `syncroton_relations_state::device_id` (`device_id`),
+ CONSTRAINT `syncroton_relations_state::device_id--syncroton_device::id` FOREIGN KEY (`device_id`) REFERENCES `syncroton_device` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
-- Roundcube core table should exist if we're using the same database
CREATE TABLE IF NOT EXISTS `system` (
@@ -113,4 +123,4 @@ CREATE TABLE IF NOT EXISTS `system` (
PRIMARY KEY(`name`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
-INSERT INTO `system` (`name`, `value`) VALUES ('syncroton-version', '2014093000');
+INSERT INTO `system` (`name`, `value`) VALUES ('syncroton-version', '2014101300');
diff --git a/docs/SQL/mysql/2014101300.sql b/docs/SQL/mysql/2014101300.sql
new file mode 100644
index 0000000..cf3ea13
--- /dev/null
+++ b/docs/SQL/mysql/2014101300.sql
@@ -0,0 +1,10 @@
+CREATE TABLE IF NOT EXISTS `syncroton_relations_state` (
+ `device_id` varchar(40) NOT NULL,
+ `folder_id` varchar(40) NOT NULL,
+ `synctime` datetime NOT NULL,
+ `data` longblob,
+ PRIMARY KEY (`device_id`,`folder_id`,`synctime`),
+ KEY `syncroton_relations_state::device_id` (`device_id`),
+ CONSTRAINT `syncroton_relations_state::device_id--syncroton_device::id`
+ FOREIGN KEY (`device_id`) REFERENCES `syncroton_device` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
diff --git a/docs/SQL/oracle.initial.sql b/docs/SQL/oracle.initial.sql
index f3d1894..1ac4227 100644
--- a/docs/SQL/oracle.initial.sql
+++ b/docs/SQL/oracle.initial.sql
@@ -101,4 +101,13 @@ CREATE TABLE "syncroton_modseq" (
PRIMARY KEY ("device_id", "folder_id", "synctime")
);
-INSERT INTO "system" ("name", "value") VALUES ('syncroton-version', '2014093000');
+CREATE TABLE "syncroton_relations_state" (
+ "device_id" varchar(40) NOT NULL
+ REFERENCES "syncroton_device" ("id") ON DELETE CASCADE,
+ "folder_id" varchar(40) NOT NULL,
+ "synctime" timestamp NOT NULL,
+ "data" clob,
+ PRIMARY KEY ("device_id", "folder_id", "synctime")
+);
+
+INSERT INTO "system" ("name", "value") VALUES ('syncroton-version', '2014101300');
diff --git a/docs/SQL/oracle/2014101300.sql b/docs/SQL/oracle/2014101300.sql
new file mode 100644
index 0000000..cff3c65
--- /dev/null
+++ b/docs/SQL/oracle/2014101300.sql
@@ -0,0 +1,8 @@
+CREATE TABLE "syncroton_relations_state" (
+ "device_id" varchar(40) NOT NULL
+ REFERENCES "syncroton_device" ("id") ON DELETE CASCADE,
+ "folder_id" varchar(40) NOT NULL,
+ "synctime" timestamp NOT NULL,
+ "data" clob,
+ PRIMARY KEY ("device_id", "folder_id", "synctime")
+);
diff --git a/lib/kolab_sync_backend.php b/lib/kolab_sync_backend.php
index b88c984..8af2633 100644
--- a/lib/kolab_sync_backend.php
+++ b/lib/kolab_sync_backend.php
@@ -853,7 +853,7 @@ class kolab_sync_backend
if ($row = $db->fetch_assoc()) {
$synctime = $row['synctime'];
// @TODO: make sure synctime from sql is in "Y-m-d H:i:s" format
- $this->modseq[$folderid][$synctime] = json_decode($row['data']);
+ $this->modseq[$folderid][$synctime] = json_decode($row['data'], true);
}
// Cleanup: remove all records except the current one
@@ -866,6 +866,62 @@ class kolab_sync_backend
}
/**
+ * Set state of relation objects at specified point in time
+ */
+ public function relations_state_set($deviceid, $folderid, $synctime, $relations)
+ {
+ $synctime = $synctime->format('Y-m-d H:i:s');
+ $rcube = rcube::get_instance();
+ $db = $rcube->get_dbh();
+ $old_data = $this->relations[$folderid][$synctime];
+
+ if (empty($old_data)) {
+ $this->relations[$folderid][$synctime] = $relations;
+ $data = rcube_charset::clean(json_encode($relations));
+
+ $db->set_option('ignore_key_errors', true);
+ $db->query("INSERT INTO `syncroton_relations_state`"
+ ." (`device_id`, `folder_id`, `synctime`, `data`)"
+ ." VALUES (?, ?, ?, ?)",
+ $deviceid, $folderid, $synctime, $data);
+ $db->set_option('ignore_key_errors', false);
+ }
+ }
+
+ /**
+ * Get state of relation objects at specified point in time
+ */
+ public function relations_state_get($deviceid, $folderid, $synctime)
+ {
+ $synctime = $synctime->format('Y-m-d H:i:s');
+
+ if (empty($this->relations[$folderid][$synctime])) {
+ $this->relations[$folderid] = array();
+
+ $rcube = rcube::get_instance();
+ $db = $rcube->get_dbh();
+
+ $db->limitquery("SELECT `data`, `synctime` FROM `syncroton_relations_state`"
+ ." WHERE `device_id` = ? AND `folder_id` = ? AND `synctime` <= ?"
+ ." ORDER BY `synctime` DESC",
+ 0, 1, $deviceid, $folderid, $synctime);
+
+ if ($row = $db->fetch_assoc()) {
+ $synctime = $row['synctime'];
+ // @TODO: make sure synctime from sql is in "Y-m-d H:i:s" format
+ $this->relations[$folderid][$synctime] = json_decode($row['data'], true);
+ }
+
+ // Cleanup: remove all records except the current one
+ $db->query("DELETE FROM `syncroton_relations_state`"
+ ." WHERE `device_id` = ? AND `folder_id` = ? AND `synctime` <> ?",
+ $deviceid, $folderid, $synctime);
+ }
+
+ return @$this->relations[$folderid][$synctime];
+ }
+
+ /**
* Compares two arrays
*
* @param array $array1
diff --git a/lib/kolab_sync_data.php b/lib/kolab_sync_data.php
index 09956c2..9589b73 100644
--- a/lib/kolab_sync_data.php
+++ b/lib/kolab_sync_data.php
@@ -536,12 +536,15 @@ abstract class kolab_sync_data implements Syncroton_Data_IData
if (empty($filter)) {
$filter = array();
}
+ else {
+ $changed_objects = $this->getChangesByRelations($folderid, $filter);
+ }
$result = $result_type == self::RESULT_COUNT ? 0 : array();
$found = 0;
- foreach ($folders as $folderid) {
- $foldername = $this->backend->folder_id2name($folderid, $this->device->deviceid);
+ foreach ($folders as $folder_id) {
+ $foldername = $this->backend->folder_id2name($folder_id, $this->device->deviceid);
if ($foldername === null || !($folder = $this->getFolderObject($foldername))) {
continue;
@@ -577,108 +580,183 @@ abstract class kolab_sync_data implements Syncroton_Data_IData
if ($error) {
throw new Syncroton_Exception_Status(Syncroton_Exception_Status::SERVER_ERROR);
}
+
+ // handle tag modifications
+ if (!empty($changed_objects)) {
+ // build new filter
+ // search objects mathing current filter,
+ // relations may contain members of many types, we need to
+ // search them by UID in all requested folders to get
+ // only these with requested type (and that really exist
+ // in specified folders)
+ $tag_filter = array(array('uid', '=', $changed_objects));
+ foreach ($filter as $f) {
+ if ($f[0] != 'changed') {
+ $tag_filter[] = $f;
+ }
+ }
+
+ switch ($result_type) {
+ case self::RESULT_COUNT:
+ // Note: this way we're potentally counting the same objects twice
+ // I'm not sure if this is a problem, we most likely do not
+ // need a precise result here
+ $count = $folder->count($tag_filter);
+ if ($count !== null && $count !== false) {
+ $result += (int) $count;
+ }
+
+ break;
+
+ case self::RESULT_UID:
+ $uids = $folder->get_uids($tag_filter);
+ if (is_array($uids)) {
+ $result = array_unique(array_merge($result, $uids));
+ }
+
+ break;
+ }
+ }
}
if (!$found) {
throw new Syncroton_Exception_Status(Syncroton_Exception_Status::SERVER_ERROR);
}
- // consider Tag object modifications if needed
- if ($this->tag_categories) {
- $this->searchEntriesByTagChanges($filter, $folders, $result_type, $result);
- }
-
return $result;
}
/**
- * Checks if any Tags have been modified in specified time
- * and returns UIDs (or count) of objects assigned to them
+ * Detect changes of relation (tag) objects data and assigned objects
+ * Returns relation member identifiers
*/
- protected function searchEntriesByTagChanges($filter, $folders, $result_type, &$result)
+ protected function getChangesByRelations($folderid, $filter)
{
- $tag_filter = array();
- $obj_filter = array();
+ if (!$this->tag_categories) {
+ return;
+ }
// get period filter, create new objects filter
foreach ($filter as $f) {
- if ($f[0] == 'changed') {
- $tag_filter[] = $f;
- }
- else {
- $obj_filter[] = $f;
+ if ($f[0] == 'changed' && $f[1] == '>') {
+ $since = $f[2];
}
}
// this is not search for changes, do nothing
- if (empty($tag_filter)) {
+ if (empty($since)) {
return;
}
- // we're detecting changes here, let's check if any tags have been modified
- // we're covering here cases when tag name or tag assignment has been changed
+ // get relations state from the last sync
+ $last_state = (array) $this->backend->relations_state_get($this->device->id, $folderid, $since);
+ // get current relations state
$config = kolab_storage_config::get_instance();
- $members = array();
$default = true;
$filter = array(
array('type', '=', 'relation'),
array('category', '=', 'tag')
);
- $filter = array_merge($filter, $tag_filter);
- $tags = $config->get_objects($filter, $default, 100);
+ $relations = $config->get_objects($filter, $default, 100);
- // get UIDs of tag members
- foreach ($tags as $tag) {
- foreach ((array)$tag['members'] as $url) {
- if (strpos($url, 'urn:uuid:') === 0) {
- $members[] = substr($url, 9);
- }
+ $result = array();
+ $changed = false;
+
+ // compare states, get members of changed relations
+ foreach ($relations as $idx => $relation) {
+ $rel_id = $relation['uid'];
+
+ if ($relation['changed']) {
+ $relation['changed']->setTimezone(new DateTimeZone('UTC'));
}
- }
- $members = array_unique($members);
+ // last state unknown...
+ if (empty($last_state[$rel_id])) {
+ // ...get all members
+ if (!empty($relation['members'])) {
+ $changed = true;
+ $result = array_merge($result, $relation['members']);
+ }
+ }
+ // last state known, changed tag name...
+ else if ($last_state[$rel_id]['name'] != $relation['name']) {
+ // ...get all (old and new) members
+ $members_old = explode("\n", $last_state[$rel_id]['members']);
+ $changed = true;
+ $members = array_unique(array_merge($relation['members'], $members_old));
+ $result = array_merge($result, $members);
+ }
+ // last state known, any other change change...
+ else if ($last_state[$rel_id]['changed'] < $relation['changed']->format('U')) {
+ // ...find new and removed members
+ $members_old = explode("\n", $last_state[$rel_id]['members']);
+ $new = array_diff($relation['members'], $members_old);
+ $removed = array_diff($members_old, $relation['members']);
+
+ if (!empty($new) || !empty($removed)) {
+ $changed = true;
+ $result = array_merge($result, $new, $removed);
+ }
+ }
- // if we have UIDs in result already we can remove them here
- // FIXME: if the result is an int we can end up
- // with wrong result (some objects might be counted twice)
- if ($result_type == self::RESULT_UID) {
- $members = array_diff($members, $result);
+ unset($last_state[$rel_id]);
}
- if (empty($members)) {
- return;
+ // get members of deleted relations
+ if (!empty($last_state)) {
+ $changed = true;
+ foreach ($last_state as $relation) {
+ $members = explode("\n", $relation['members']);
+ $result = array_merge($result, $members);
+ }
}
- // search objects mathing current filter,
- // tags/relations may contain members of many types, we need to
- // search them by UID in all requested folders to get
- // only these with requested type (and that really exist)
- $obj_filter[] = array('uid', '=', $members);
-
- foreach ($folders as $folderid) {
- $foldername = $this->backend->folder_id2name($folderid, $this->device->deviceid);
-
- if ($foldername === null || !($folder = $this->getFolderObject($foldername))) {
- continue;
+ // save current state
+ if ($changed) {
+ $data = array();
+ foreach ($relations as $relation) {
+ $data[$relation['uid']] = array(
+ 'name' => $relation['name'],
+ 'changed' => $relation['changed']->format('U'),
+ 'members' => implode("\n", $relation['members']),
+ );
}
- switch ($result_type) {
- case self::RESULT_COUNT:
- $count = $folder->count($obj_filter);
- $result += (int) $count;
- break;
+ $now = new DateTime('now', new DateTimeZone('UTC'));
- case self::RESULT_UID:
- $uids = $folder->get_uids($obj_filter);
+ $this->backend->relations_state_set($this->device->id, $folderid, $now, $data);
+ }
- if (is_array($uids)) {
- $result = array_merge($result, $uids);
+ // in mail mode return only message URIs
+ if ($this->modelName == 'mail') {
+ // lambda function to skip email members
+ $filter_func = function($value) {
+ return strpos($value, 'imap://') === 0;
+ };
+
+ $result = array_filter(array_unique($result), $filter_func);
+ }
+ // otherwise return only object UIDs
+ else {
+ // lambda function to skip email members
+ $filter_func = function($value) {
+ return strpos($value, 'urn:uuid:') === 0;
+ };
+
+ // lambda function to parse member URI
+ $member_func = function($value) {
+ if (strpos($value, 'urn:uuid:') === 0) {
+ $value = substr($value, 9);
}
- break;
- }
+ return $value;
+ };
+
+ $result = array_map($member_func, array_filter(array_unique($result), $filter_func));
}
+
+ return $result;
}
/**
diff --git a/lib/kolab_sync_data_email.php b/lib/kolab_sync_data_email.php
index 99d1af5..381c3cf 100644
--- a/lib/kolab_sync_data_email.php
+++ b/lib/kolab_sync_data_email.php
@@ -805,6 +805,9 @@ class kolab_sync_data_email extends kolab_sync_data implements Syncroton_Data_ID
}
}
+ // get members of modified relations
+ $changed_msgs = $this->getChangesByRelations($folderid, $filter);
+
$result = $result_type == self::RESULT_COUNT ? 0 : array();
// no sorting for best performance
$sort_by = null;
@@ -830,6 +833,7 @@ class kolab_sync_data_email extends kolab_sync_data implements Syncroton_Data_ID
}
// We're in "get changes" mode
+ $modified = false;
if (isset($modseq_data)) {
$folder_data = $this->storage->folder_data($foldername);
$got_changes = true;
@@ -848,7 +852,7 @@ class kolab_sync_data_email extends kolab_sync_data implements Syncroton_Data_ID
// We can only get folder's HIGHESTMODSEQ value and store it for the next try
// Skip search if HIGHESTMODSEQ didn't change
if (!$got_changes || empty($modseq) || empty($modseq[$foldername])) {
- continue;
+ $modified = true;
}
$filter_str .= " MODSEQ " . ($modseq[$foldername] + 1);
@@ -865,25 +869,45 @@ class kolab_sync_data_email extends kolab_sync_data implements Syncroton_Data_ID
// $search = $this->storage->index($foldername, null, null, true, true);
// else
- $search = $this->storage->search_once($foldername, $filter_str);
+ if ($modified) {
+ $search = $this->storage->search_once($foldername, $filter_str);
+
+ if (!($search instanceof rcube_result_index) || $search->is_error()) {
+ throw new Syncroton_Exception_Status(Syncroton_Exception_Status::SERVER_ERROR);
+ }
- if (!($search instanceof rcube_result_index) || $search->is_error()) {
- throw new Syncroton_Exception_Status(Syncroton_Exception_Status::SERVER_ERROR);
+ switch ($result_type) {
+ case self::RESULT_COUNT:
+ $result += (int) $search->count();
+ break;
+
+ case self::RESULT_UID:
+ if ($uids = $search->get()) {
+ foreach ($uids as $idx => $uid) {
+ $uids[$idx] = $this->createMessageId($folder_id, $uid);
+ }
+ $result = array_merge($result, $uids);
+ }
+ break;
+ }
}
- switch ($result_type) {
- case self::RESULT_COUNT:
- $result += (int) $search->count();
- break;
+ // handle relation changes
+ if (!empty($changed_msgs)) {
+ $uids = $this->findRelationMembersInFolder($foldername, $changed_msgs, $filter);
- case self::RESULT_UID:
- if ($uids = $search->get()) {
+ switch ($result_type) {
+ case self::RESULT_COUNT:
+ $result += (int) count($uids);
+ break;
+
+ case self::RESULT_UID:
foreach ($uids as $idx => $uid) {
$uids[$idx] = $this->createMessageId($folder_id, $uid);
}
- $result = array_merge($result, $uids);
+ $result = array_unique(array_merge($result, $uids));
+ break;
}
- break;
}
}
@@ -906,12 +930,93 @@ class kolab_sync_data_email extends kolab_sync_data implements Syncroton_Data_ID
}
}
- // @TODO: tag objects modification detection
-
return $result;
}
/**
+ * Find members (messages) in specified folder
+ */
+ protected function findRelationMembersInFolder($foldername, $members, $filter)
+ {
+ foreach ($members as $member) {
+ // IMAP URI members
+ if ($url = kolab_storage_config::parse_member_url($member)) {
+ $result[$url['folder']][$url['uid']] = $url['params'];
+ }
+ }
+
+ // convert filter into one IMAP search string
+ $filter_str = 'ALL UNDELETED';
+ foreach ($filter as $idx => $filter_item) {
+ if (is_string($filter_item)) {
+ $filter_str .= ' ' . $filter_item;
+ }
+ }
+
+ $rcube = rcube::get_instance();
+ $storage = $rcube->get_storage();
+ $found = array();
+
+ // first find messages by UID
+ if (!empty($result[$foldername])) {
+ $index = $storage->search_once($foldername, 'UID '
+ . rcube_imap_generic::compressMessageSet(array_keys($result[$foldername])));
+ $found = $index->get();
+
+ // remove found messages from the $result
+ if (!empty($found)) {
+ $result[$foldername] = array_diff_key($result[$foldername], array_flip($found));
+
+ if (empty($result[$foldername])) {
+ unset($result[$foldername]);
+ }
+
+ // now apply the current filter to the found messages
+ $index = $storage->search_once($foldername, $filter_str . ' UID '
+ . rcube_imap_generic::compressMessageSet($found));
+ $found = $index->get();
+ }
+ }
+
+ // search by message parameters
+ if (!empty($result)) {
+ // @TODO: do this search in chunks (for e.g. 25 messages)?
+ $search = '';
+ $search_count = 0;
+
+ foreach ($result as $folder => $data) {
+ foreach ($data as $p) {
+ $search_params = array();
+ $search_count++;
+
+ foreach ($p as $key => $val) {
+ $key = strtoupper($key);
+ // don't search by subject, we don't want false-positives
+ if ($key != 'SUBJECT') {
+ $search_params[] = 'HEADER ' . $key . ' ' . rcube_imap_generic::escape($val);
+ }
+ }
+
+ $search .= ' (' . implode(' ', $search_params) . ')';
+ }
+ }
+
+ $search_str .= ' ' . str_repeat(' OR', $search_count-1) . $search;
+
+ // search messages in current folder
+ $search = $storage->search_once($foldername, $search_str);
+ $uids = $search->get();
+
+ if (!empty($uids)) {
+ // add UIDs into the result
+ $found = array_unique(array_merge($found, $uids));
+ }
+ }
+
+ return $found;
+ }
+
+ /**
* ActiveSync Search handler
*
* @param Syncroton_Model_StoreRequest $store Search query
More information about the commits
mailing list