3 commits - docs/SQL lib/kolab_sync_backend_common.php lib/kolab_sync_backend_content.php

[Aleksander Machniak]

 docs/SQL/mysql.initial.sql         |    8 ++++----
 docs/SQL/mysql/2013040900.sql      |    7 +++++++
 lib/kolab_sync_backend_common.php  |    7 +++++++
 lib/kolab_sync_backend_content.php |   18 ++++++++++++++++--
 4 files changed, 34 insertions(+), 6 deletions(-)

New commits:
commit 898c4b77e00cd94eb3cf75c5ff4c05b24acb7fa8
Author: Aleksander Machniak <alec at alec.pl>
Date:   Tue Apr 9 13:10:38 2013 +0200

    Improve performance by caching some data (skip at least one SELECT)

diff --git a/lib/kolab_sync_backend_common.php b/lib/kolab_sync_backend_common.php
index d72c908..ead546e 100644
--- a/lib/kolab_sync_backend_common.php
+++ b/lib/kolab_sync_backend_common.php
@@ -56,6 +56,13 @@ class kolab_sync_backend_common implements Syncroton_Backend_IBackend
      */
     protected $db;
 
+    /**
+     * Internal cache (in-memory)
+     *
+     * @var array
+     */
+    protected $cache = array();
+
 
     /**
      * Constructor
diff --git a/lib/kolab_sync_backend_content.php b/lib/kolab_sync_backend_content.php
index 72a673e..246dcba 100644
--- a/lib/kolab_sync_backend_content.php
+++ b/lib/kolab_sync_backend_content.php
@@ -44,7 +44,11 @@ class kolab_sync_backend_content extends kolab_sync_backend_common implements Sy
 
         $result = $this->db->query('UPDATE ' . $this->table_name . ' SET is_deleted = 1 WHERE id = ?', array($id));
 
-        return (bool) $this->db->affected_rows($result);
+        if ($result = (bool) $this->db->affected_rows($result)) {
+            unset($this->cache['content_folderstate']);
+        }
+
+        return $result;
     }
 
     /**
@@ -84,6 +88,13 @@ class kolab_sync_backend_content extends kolab_sync_backend_common implements Sy
     {
         $deviceId = $_deviceId instanceof Syncroton_Model_IDevice ? $_deviceId->id : $_deviceId;
         $folderId = $_folderId instanceof Syncroton_Model_IFolder ? $_folderId->id : $_folderId;
+        $cachekey = $deviceId . ':' . $folderId;
+
+        // in Sync request we call this function twice in case when
+        // folder state changed - use cache to skip at least one SELECT query
+        if (isset($this->cache['content_folderstate'][$cache_key])) {
+            return $this->cache['content_folderstate'][$cache_key];
+        }
 
         $where[] = $this->db->quote_identifier('device_id') . ' = ' . $this->db->quote($deviceId);
         $where[] = $this->db->quote_identifier('folder_id') . ' = ' . $this->db->quote($folderId);
@@ -96,7 +107,7 @@ class kolab_sync_backend_content extends kolab_sync_backend_common implements Sy
             $result[] = $state['contentid'];
         }
 
-        return $result;
+        return $this->cache['content_folderstate'][$cache_key] = $result;
     }
 
     /**
@@ -109,6 +120,9 @@ class kolab_sync_backend_content extends kolab_sync_backend_common implements Sy
     {
         $deviceId = $_deviceId instanceof Syncroton_Model_IDevice ? $_deviceId->id : $_deviceId;
         $folderId = $_folderId instanceof Syncroton_Model_IFolder ? $_folderId->id : $_folderId;
+        $cachekey = $deviceId . ':' . $folderId;
+
+        unset($this->cache['content_folderstate'][$cache_key]);
 
         $where[] = $this->db->quote_identifier('device_id') . ' = ' . $this->db->quote($deviceId);
         $where[] = $this->db->quote_identifier('folder_id') . ' = ' . $this->db->quote($folderId);


commit a6bce09f7daa4de75455116390f06cce6c40da14
Author: Aleksander Machniak <alec at alec.pl>
Date:   Tue Apr 9 11:22:58 2013 +0200

    Improve database consistency, some fields cannot be null

diff --git a/docs/SQL/mysql.initial.sql b/docs/SQL/mysql.initial.sql
index 28ecd5d..bbaa2c7 100644
--- a/docs/SQL/mysql.initial.sql
+++ b/docs/SQL/mysql.initial.sql
@@ -65,9 +65,9 @@ CREATE TABLE IF NOT EXISTS `syncroton_synckey` (
 
 CREATE TABLE IF NOT EXISTS `syncroton_content` (
     `id` varchar(40) NOT NULL,
-    `device_id` varchar(40) DEFAULT NULL,
-    `folder_id` varchar(40) DEFAULT NULL,
-    `contentid` varchar(128) DEFAULT NULL,
+    `device_id` varchar(40) NOT NULL,
+    `folder_id` varchar(40) NOT NULL,
+    `contentid` varchar(128) NOT NULL,
     `creation_time` datetime DEFAULT NULL,
     `creation_synckey` int(11) NOT NULL,
     `is_deleted` tinyint(1) DEFAULT '0',
@@ -112,4 +112,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', '2013040700');
+INSERT INTO `system` (`name`, `value`) VALUES ('syncroton-version', '2013040900');
diff --git a/docs/SQL/mysql/2013040900.sql b/docs/SQL/mysql/2013040900.sql
new file mode 100644
index 0000000..36f8e28
--- /dev/null
+++ b/docs/SQL/mysql/2013040900.sql
@@ -0,0 +1,7 @@
+DELETE FROM `syncroton_content` WHERE `device_id` IS NULL;
+DELETE FROM `syncroton_content` WHERE `folder_id` IS NULL;
+DELETE FROM `syncroton_content` WHERE `contentid` IS NULL;
+ALTER TABLE `syncroton_content`
+    MODIFY `device_id` varchar(40) NOT NULL,
+    MODIFY `folder_id` varchar(40) NOT NULL,
+    MODIFY `contentid` varchar(128) NOT NULL;


commit f0917d66b2480e0b27146a1da5122d166d481318
Author: Aleksander Machniak <alec at alec.pl>
Date:   Tue Apr 9 10:46:27 2013 +0200

    Update DB version number

diff --git a/docs/SQL/mysql.initial.sql b/docs/SQL/mysql.initial.sql
index 4f6ebcf..28ecd5d 100644
--- a/docs/SQL/mysql.initial.sql
+++ b/docs/SQL/mysql.initial.sql
@@ -112,4 +112,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', '2013011600');
+INSERT INTO `system` (`name`, `value`) VALUES ('syncroton-version', '2013040700');