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