Branch 'dev/kolab-cache-refactoring' - 2 commits - plugins/libkolab
Thomas Brüderli
bruederli at kolabsys.com
Mon Oct 7 12:13:17 CEST 2013
plugins/libkolab/SQL/mysql.initial.sql | 51 +++---
plugins/libkolab/SQL/mysql/2013100400.sql | 147 +++++++++++++++++++
plugins/libkolab/lib/kolab_storage_cache.php | 8 -
plugins/libkolab/lib/kolab_storage_cache_contact.php | 5
plugins/libkolab/lib/kolab_storage_cache_task.php | 2
5 files changed, 185 insertions(+), 28 deletions(-)
New commits:
commit 944b42afec882cf184f981b9103e84f99aecc11f
Author: Thomas Bruederli <bruederli at kolabsys.com>
Date: Mon Oct 7 12:13:05 2013 +0200
Improve database schema and provide schema update queries
diff --git a/plugins/libkolab/SQL/mysql.initial.sql b/plugins/libkolab/SQL/mysql.initial.sql
index 2ce664b..8e9c8dc 100644
--- a/plugins/libkolab/SQL/mysql.initial.sql
+++ b/plugins/libkolab/SQL/mysql.initial.sql
@@ -21,6 +21,8 @@ CREATE TABLE `kolab_folders` (
DROP TABLE IF EXISTS `kolab_cache`;
+DROP TABLE IF EXISTS `kolab_cache_contact`;
+
CREATE TABLE `kolab_cache_contact` (
`folder_id` BIGINT UNSIGNED NOT NULL,
`msguid` BIGINT UNSIGNED NOT NULL,
@@ -35,10 +37,11 @@ CREATE TABLE `kolab_cache_contact` (
CONSTRAINT `fk_kolab_cache_contact_folder` FOREIGN KEY (`folder_id`)
REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(`folder_id`,`msguid`),
- INDEX `contact_uid` (`uid`),
INDEX `contact_type` (`folder_id`,`type`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+DROP TABLE IF EXISTS `kolab_cache_event`;
+
CREATE TABLE `kolab_cache_event` (
`folder_id` BIGINT UNSIGNED NOT NULL,
`msguid` BIGINT UNSIGNED NOT NULL,
@@ -47,16 +50,17 @@ CREATE TABLE `kolab_cache_event` (
`changed` DATETIME DEFAULT NULL,
`data` TEXT NOT NULL,
`xml` TEXT NOT NULL,
- `dtstart` DATETIME,
- `dtend` DATETIME,
`tags` VARCHAR(255) NOT NULL,
`words` TEXT NOT NULL,
+ `dtstart` DATETIME,
+ `dtend` DATETIME,
CONSTRAINT `fk_kolab_cache_event_folder` FOREIGN KEY (`folder_id`)
REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
- PRIMARY KEY(`folder_id`,`msguid`),
- INDEX `event_uid` (`uid`)
+ PRIMARY KEY(`folder_id`,`msguid`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+DROP TABLE IF EXISTS `kolab_cache_task`;
+
CREATE TABLE `kolab_cache_task` (
`folder_id` BIGINT UNSIGNED NOT NULL,
`msguid` BIGINT UNSIGNED NOT NULL,
@@ -65,16 +69,17 @@ CREATE TABLE `kolab_cache_task` (
`changed` DATETIME DEFAULT NULL,
`data` TEXT NOT NULL,
`xml` TEXT NOT NULL,
- `dtstart` DATETIME,
- `dtend` DATETIME,
`tags` VARCHAR(255) NOT NULL,
`words` TEXT NOT NULL,
+ `dtstart` DATETIME,
+ `dtend` DATETIME,
CONSTRAINT `fk_kolab_cache_task_folder` FOREIGN KEY (`folder_id`)
REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
- PRIMARY KEY(`folder_id`,`msguid`),
- INDEX `task_uid` (`uid`)
+ PRIMARY KEY(`folder_id`,`msguid`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+DROP TABLE IF EXISTS `kolab_cache_journal`;
+
CREATE TABLE `kolab_cache_journal` (
`folder_id` BIGINT UNSIGNED NOT NULL,
`msguid` BIGINT UNSIGNED NOT NULL,
@@ -83,16 +88,17 @@ CREATE TABLE `kolab_cache_journal` (
`changed` DATETIME DEFAULT NULL,
`data` TEXT NOT NULL,
`xml` TEXT NOT NULL,
- `dtstart` DATETIME,
- `dtend` DATETIME,
`tags` VARCHAR(255) NOT NULL,
`words` TEXT NOT NULL,
+ `dtstart` DATETIME,
+ `dtend` DATETIME,
CONSTRAINT `fk_kolab_cache_journal_folder` FOREIGN KEY (`folder_id`)
REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
- PRIMARY KEY(`folder_id`,`msguid`),
- INDEX `journal` (`uid`)
+ PRIMARY KEY(`folder_id`,`msguid`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+DROP TABLE IF EXISTS `kolab_cache_note`;
+
CREATE TABLE `kolab_cache_note` (
`folder_id` BIGINT UNSIGNED NOT NULL,
`msguid` BIGINT UNSIGNED NOT NULL,
@@ -105,10 +111,11 @@ CREATE TABLE `kolab_cache_note` (
`words` TEXT NOT NULL,
CONSTRAINT `fk_kolab_cache_note_folder` FOREIGN KEY (`folder_id`)
REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
- PRIMARY KEY(`folder_id`,`msguid`),
- INDEX `note_uid` (`uid`)
+ PRIMARY KEY(`folder_id`,`msguid`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+DROP TABLE IF EXISTS `kolab_cache_file`;
+
CREATE TABLE `kolab_cache_file` (
`folder_id` BIGINT UNSIGNED NOT NULL,
`msguid` BIGINT UNSIGNED NOT NULL,
@@ -127,6 +134,8 @@ CREATE TABLE `kolab_cache_file` (
INDEX `folder_filename` (`folder_id`, `filename`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+DROP TABLE IF EXISTS `kolab_cache_configuration`;
+
CREATE TABLE `kolab_cache_configuration` (
`folder_id` BIGINT UNSIGNED NOT NULL,
`msguid` BIGINT UNSIGNED NOT NULL,
@@ -141,10 +150,11 @@ CREATE TABLE `kolab_cache_configuration` (
CONSTRAINT `fk_kolab_cache_configuration_folder` FOREIGN KEY (`folder_id`)
REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(`folder_id`,`msguid`),
- INDEX `configuration_uid` (`uid`),
INDEX `configuration_type` (`folder_id`,`type`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+DROP TABLE IF EXISTS `kolab_cache_freebusy`;
+
CREATE TABLE `kolab_cache_freebusy` (
`folder_id` BIGINT UNSIGNED NOT NULL,
`msguid` BIGINT UNSIGNED NOT NULL,
@@ -153,14 +163,13 @@ CREATE TABLE `kolab_cache_freebusy` (
`changed` DATETIME DEFAULT NULL,
`data` TEXT NOT NULL,
`xml` TEXT NOT NULL,
- `dtstart` DATETIME,
- `dtend` DATETIME,
`tags` VARCHAR(255) NOT NULL,
`words` TEXT NOT NULL,
- CONSTRAINT `fk_kolab_cache_cfreebusy_folder` FOREIGN KEY (`folder_id`)
+ `dtstart` DATETIME,
+ `dtend` DATETIME,
+ CONSTRAINT `fk_kolab_cache_freebusy_folder` FOREIGN KEY (`folder_id`)
REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
- PRIMARY KEY(`folder_id`,`msguid`),
- INDEX `freebusy_uid` (`uid`)
+ PRIMARY KEY(`folder_id`,`msguid`)
) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
diff --git a/plugins/libkolab/SQL/mysql/2013100400.sql b/plugins/libkolab/SQL/mysql/2013100400.sql
new file mode 100644
index 0000000..cdc1df6
--- /dev/null
+++ b/plugins/libkolab/SQL/mysql/2013100400.sql
@@ -0,0 +1,147 @@
+DROP TABLE IF EXISTS `kolab_cache`;
+
+CREATE TABLE `kolab_folders` (
+ `ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
+ `resource` VARCHAR(255) CHARACTER SET ascii NOT NULL,
+ `type` VARCHAR(32) CHARACTER SET ascii NOT NULL,
+ `synclock` INT(10) NOT NULL DEFAULT '0',
+ `ctag` VARCHAR(32) DEFAULT NULL,
+ PRIMARY KEY(`ID`),
+ INDEX `resource_type` (`resource`, `type`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `kolab_cache_contact` (
+ `folder_id` BIGINT UNSIGNED NOT NULL,
+ `msguid` BIGINT UNSIGNED NOT NULL,
+ `uid` VARCHAR(128) CHARACTER SET ascii NOT NULL,
+ `created` DATETIME DEFAULT NULL,
+ `changed` DATETIME DEFAULT NULL,
+ `data` TEXT NOT NULL,
+ `xml` TEXT NOT NULL,
+ `tags` VARCHAR(255) NOT NULL,
+ `words` TEXT NOT NULL,
+ `type` VARCHAR(32) CHARACTER SET ascii NOT NULL,
+ CONSTRAINT `fk_kolab_cache_contact_folder` FOREIGN KEY (`folder_id`)
+ REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
+ PRIMARY KEY(`folder_id`,`msguid`),
+ INDEX `contact_type` (`folder_id`,`type`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `kolab_cache_event` (
+ `folder_id` BIGINT UNSIGNED NOT NULL,
+ `msguid` BIGINT UNSIGNED NOT NULL,
+ `uid` VARCHAR(128) CHARACTER SET ascii NOT NULL,
+ `created` DATETIME DEFAULT NULL,
+ `changed` DATETIME DEFAULT NULL,
+ `data` TEXT NOT NULL,
+ `xml` TEXT NOT NULL,
+ `tags` VARCHAR(255) NOT NULL,
+ `words` TEXT NOT NULL,
+ `dtstart` DATETIME,
+ `dtend` DATETIME,
+ CONSTRAINT `fk_kolab_cache_event_folder` FOREIGN KEY (`folder_id`)
+ REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
+ PRIMARY KEY(`folder_id`,`msguid`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `kolab_cache_task` (
+ `folder_id` BIGINT UNSIGNED NOT NULL,
+ `msguid` BIGINT UNSIGNED NOT NULL,
+ `uid` VARCHAR(128) CHARACTER SET ascii NOT NULL,
+ `created` DATETIME DEFAULT NULL,
+ `changed` DATETIME DEFAULT NULL,
+ `data` TEXT NOT NULL,
+ `xml` TEXT NOT NULL,
+ `tags` VARCHAR(255) NOT NULL,
+ `words` TEXT NOT NULL,
+ `dtstart` DATETIME,
+ `dtend` DATETIME,
+ CONSTRAINT `fk_kolab_cache_task_folder` FOREIGN KEY (`folder_id`)
+ REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
+ PRIMARY KEY(`folder_id`,`msguid`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `kolab_cache_journal` (
+ `folder_id` BIGINT UNSIGNED NOT NULL,
+ `msguid` BIGINT UNSIGNED NOT NULL,
+ `uid` VARCHAR(128) CHARACTER SET ascii NOT NULL,
+ `created` DATETIME DEFAULT NULL,
+ `changed` DATETIME DEFAULT NULL,
+ `data` TEXT NOT NULL,
+ `xml` TEXT NOT NULL,
+ `tags` VARCHAR(255) NOT NULL,
+ `words` TEXT NOT NULL,
+ `dtstart` DATETIME,
+ `dtend` DATETIME,
+ CONSTRAINT `fk_kolab_cache_journal_folder` FOREIGN KEY (`folder_id`)
+ REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
+ PRIMARY KEY(`folder_id`,`msguid`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `kolab_cache_note` (
+ `folder_id` BIGINT UNSIGNED NOT NULL,
+ `msguid` BIGINT UNSIGNED NOT NULL,
+ `uid` VARCHAR(128) CHARACTER SET ascii NOT NULL,
+ `created` DATETIME DEFAULT NULL,
+ `changed` DATETIME DEFAULT NULL,
+ `data` TEXT NOT NULL,
+ `xml` TEXT NOT NULL,
+ `tags` VARCHAR(255) NOT NULL,
+ `words` TEXT NOT NULL,
+ CONSTRAINT `fk_kolab_cache_note_folder` FOREIGN KEY (`folder_id`)
+ REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
+ PRIMARY KEY(`folder_id`,`msguid`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `kolab_cache_file` (
+ `folder_id` BIGINT UNSIGNED NOT NULL,
+ `msguid` BIGINT UNSIGNED NOT NULL,
+ `uid` VARCHAR(128) CHARACTER SET ascii NOT NULL,
+ `created` DATETIME DEFAULT NULL,
+ `changed` DATETIME DEFAULT NULL,
+ `data` TEXT NOT NULL,
+ `xml` TEXT NOT NULL,
+ `tags` VARCHAR(255) NOT NULL,
+ `words` TEXT NOT NULL,
+ `type` VARCHAR(32) CHARACTER SET ascii NOT NULL,
+ `filename` varchar(255) DEFAULT NULL,
+ CONSTRAINT `fk_kolab_cache_file_folder` FOREIGN KEY (`folder_id`)
+ REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
+ PRIMARY KEY(`folder_id`,`msguid`),
+ INDEX `folder_filename` (`folder_id`, `filename`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `kolab_cache_configuration` (
+ `folder_id` BIGINT UNSIGNED NOT NULL,
+ `msguid` BIGINT UNSIGNED NOT NULL,
+ `uid` VARCHAR(128) CHARACTER SET ascii NOT NULL,
+ `created` DATETIME DEFAULT NULL,
+ `changed` DATETIME DEFAULT NULL,
+ `data` TEXT NOT NULL,
+ `xml` TEXT NOT NULL,
+ `tags` VARCHAR(255) NOT NULL,
+ `words` TEXT NOT NULL,
+ `type` VARCHAR(32) CHARACTER SET ascii NOT NULL,
+ CONSTRAINT `fk_kolab_cache_configuration_folder` FOREIGN KEY (`folder_id`)
+ REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
+ PRIMARY KEY(`folder_id`,`msguid`),
+ INDEX `configuration_type` (`folder_id`,`type`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
+CREATE TABLE `kolab_cache_freebusy` (
+ `folder_id` BIGINT UNSIGNED NOT NULL,
+ `msguid` BIGINT UNSIGNED NOT NULL,
+ `uid` VARCHAR(128) CHARACTER SET ascii NOT NULL,
+ `created` DATETIME DEFAULT NULL,
+ `changed` DATETIME DEFAULT NULL,
+ `data` TEXT NOT NULL,
+ `xml` TEXT NOT NULL,
+ `tags` VARCHAR(255) NOT NULL,
+ `words` TEXT NOT NULL,
+ `dtstart` DATETIME,
+ `dtend` DATETIME,
+ CONSTRAINT `fk_kolab_cache_freebusy_folder` FOREIGN KEY (`folder_id`)
+ REFERENCES `kolab_folders`(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
+ PRIMARY KEY(`folder_id`,`msguid`)
+) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8 COLLATE utf8_general_ci */;
+
commit 20a731c54057c766644cda1681381b5178e9731e
Author: Thomas Bruederli <bruederli at kolabsys.com>
Date: Mon Oct 7 12:12:23 2013 +0200
Minor code improvements
diff --git a/plugins/libkolab/lib/kolab_storage_cache.php b/plugins/libkolab/lib/kolab_storage_cache.php
index 48a0285..589b809 100644
--- a/plugins/libkolab/lib/kolab_storage_cache.php
+++ b/plugins/libkolab/lib/kolab_storage_cache.php
@@ -41,11 +41,7 @@ class kolab_storage_cache
protected $folders_table;
protected $max_sql_packet;
protected $max_sync_lock_time = 600;
- protected $binary_items = array(
- 'photo' => '|<photo><uri>[^;]+;base64,([^<]+)</uri></photo>|i',
- 'pgppublickey' => '|<key><uri>date:application/pgp-keys;base64,([^<]+)</uri></photo>|i',
- 'pkcs7publickey' => '|<key><uri>date:application/pkcs7-mime;base64,([^<]+)</uri></photo>|i',
- );
+ protected $binary_items = array();
protected $extra_cols = array();
@@ -600,7 +596,7 @@ class kolab_storage_cache
*/
protected function _serialize($object)
{
- $sql_data = array('changed' => null, 'dtstart' => null, 'dtend' => null, 'xml' => '', 'tags' => '', 'words' => '');
+ $sql_data = array('changed' => null, 'xml' => '', 'tags' => '', 'words' => '');
if ($object['changed']) {
$sql_data['changed'] = date('Y-m-d H:i:s', is_object($object['changed']) ? $object['changed']->format('U') : $object['changed']);
diff --git a/plugins/libkolab/lib/kolab_storage_cache_contact.php b/plugins/libkolab/lib/kolab_storage_cache_contact.php
index 3d1ecf0..763e9f7 100644
--- a/plugins/libkolab/lib/kolab_storage_cache_contact.php
+++ b/plugins/libkolab/lib/kolab_storage_cache_contact.php
@@ -24,6 +24,11 @@
class kolab_storage_cache_contact extends kolab_storage_cache
{
protected $extra_cols = array('type');
+ protected $binary_items = array(
+ 'photo' => '|<photo><uri>[^;]+;base64,([^<]+)</uri></photo>|i',
+ 'pgppublickey' => '|<key><uri>date:application/pgp-keys;base64,([^<]+)</uri></photo>|i',
+ 'pkcs7publickey' => '|<key><uri>date:application/pkcs7-mime;base64,([^<]+)</uri></photo>|i',
+ );
/**
* Helper method to convert the given Kolab object into a dataset to be written to cache
diff --git a/plugins/libkolab/lib/kolab_storage_cache_task.php b/plugins/libkolab/lib/kolab_storage_cache_task.php
index 03facb4..a1953f6 100644
--- a/plugins/libkolab/lib/kolab_storage_cache_task.php
+++ b/plugins/libkolab/lib/kolab_storage_cache_task.php
@@ -32,7 +32,7 @@ class kolab_storage_cache_task extends kolab_storage_cache
*/
protected function _serialize($object)
{
- $sql_data = parent::_serialize($object);
+ $sql_data = parent::_serialize($object) + array('dtstart' => null, 'dtend' => null);
if ($object['start'])
$sql_data['dtstart'] = date('Y-m-d H:i:s', is_object($object['start']) ? $object['start']->format('U') : $object['start']);
More information about the commits
mailing list