2 commits - plugins/kolab_addressbook plugins/libkolab
Thomas Brüderli
bruederli at kolabsys.com
Mon Feb 10 13:07:53 CET 2014
plugins/kolab_addressbook/lib/rcube_kolab_contacts.php | 66 +++++++++++++----
plugins/libkolab/SQL/mysql/2014021000.sql | 9 ++
plugins/libkolab/bin/modcache.sh | 31 +++++++
plugins/libkolab/lib/kolab_storage_cache.php | 55 ++++++++++++--
plugins/libkolab/lib/kolab_storage_cache_contact.php | 12 ++-
plugins/libkolab/lib/kolab_storage_folder.php | 16 +++-
6 files changed, 164 insertions(+), 25 deletions(-)
New commits:
commit 7c32605d1441172b9bf0f6346d112236590a6e02
Author: Thomas Bruederli <thomas at roundcube.net>
Date: Mon Feb 10 12:03:59 2014 +0100
Fix fatal error when attempting to set user on a rcube instance
diff --git a/plugins/libkolab/bin/modcache.sh b/plugins/libkolab/bin/modcache.sh
index bef931c..1329050 100755
--- a/plugins/libkolab/bin/modcache.sh
+++ b/plugins/libkolab/bin/modcache.sh
@@ -221,7 +221,7 @@ function authenticate(&$opts)
if ($opts['verbose'])
echo "IMAP login succeeded.\n";
if (($user = rcube_user::query($opts['username'], $auth['host'])) && $user->ID)
- $rcmail->set_user($user);
+ $rcmail->user = $user;
}
else
die("Login to IMAP server failed!\n");
commit af6d366a1f8c7761aaabd53c6ab80f4b6a75face
Author: Thomas Bruederli <thomas at roundcube.net>
Date: Mon Feb 10 11:46:50 2014 +0100
Optimize access to kolab contacts using a sorted and limited query (#2828)
- Add columns for sorting in kolab_cache_contact
- Extend bin/modcache.sh script to update existing cache records
- Add setters for ORDER BY and LIMIT clauses
- Adapt the kolab_addressbook plugin to fetch contacts page-wise
ATTENTION: This changeset contains database schema changes!
Run `bin/updatedb.sh --dir plugins/libkolab/SQL --package libkolab`
Afterwards, the cached data needs to be updated. To do so, either run
`plugins/libkolab/bin/modcache.sh update --type=contact`
or execute the following query
DELETE FROM `kolab_folders` WHERE `type`='contact';
diff --git a/plugins/kolab_addressbook/lib/rcube_kolab_contacts.php b/plugins/kolab_addressbook/lib/rcube_kolab_contacts.php
index 858ad17..6838f1e 100644
--- a/plugins/kolab_addressbook/lib/rcube_kolab_contacts.php
+++ b/plugins/kolab_addressbook/lib/rcube_kolab_contacts.php
@@ -275,6 +275,7 @@ class rcube_kolab_contacts extends rcube_addressbook
public function list_records($cols = null, $subset = 0, $nocount = false)
{
$this->result = new rcube_result_set(0, ($this->list_page-1) * $this->page_size);
+ $fetch_all = false;
// list member of the selected group
if ($this->gid) {
@@ -298,12 +299,13 @@ class rcube_kolab_contacts extends rcube_addressbook
else if (!empty($member['email'])) {
$this->contacts[$member['ID']] = $member;
$local_sortindex[$member['ID']] = $this->_sort_string($member);
+ $fetch_all = true;
}
}
// get members by UID
if (!empty($uids)) {
- $this->_fetch_contacts(array(array('uid', '=', $uids)));
+ $this->_fetch_contacts($query = array(array('uid', '=', $uids)), !$fetch_all);
$this->sortindex = array_merge($this->sortindex, $local_sortindex);
}
}
@@ -311,26 +313,34 @@ class rcube_kolab_contacts extends rcube_addressbook
$ids = $this->filter['ids'];
if (count($ids)) {
$uids = array_map(array($this, 'id2uid'), $this->filter['ids']);
- $this->_fetch_contacts(array(array('uid', '=', $uids)));
+ $this->_fetch_contacts($query = array(array('uid', '=', $uids)), true);
}
}
else {
- $this->_fetch_contacts();
+ $this->_fetch_contacts($query = array(), true);
}
- // sort results (index only)
- asort($this->sortindex, SORT_LOCALE_STRING);
- $ids = array_keys($this->sortindex);
+ if ($fetch_all) {
+ // sort results (index only)
+ asort($this->sortindex, SORT_LOCALE_STRING);
+ $ids = array_keys($this->sortindex);
- // fill contact data into the current result set
- $this->result->count = count($ids);
- $start_row = $subset < 0 ? $this->result->first + $this->page_size + $subset : $this->result->first;
- $last_row = min($subset != 0 ? $start_row + abs($subset) : $this->result->first + $this->page_size, $this->result->count);
+ // fill contact data into the current result set
+ $this->result->count = count($ids);
+ $start_row = $subset < 0 ? $this->result->first + $this->page_size + $subset : $this->result->first;
+ $last_row = min($subset != 0 ? $start_row + abs($subset) : $this->result->first + $this->page_size, $this->result->count);
- for ($i = $start_row; $i < $last_row; $i++) {
- if (array_key_exists($i, $ids)) {
- $idx = $ids[$i];
- $this->result->add($this->contacts[$idx] ?: $this->_to_rcube_contact($this->dataset[$idx]));
+ for ($i = $start_row; $i < $last_row; $i++) {
+ if (array_key_exists($i, $ids)) {
+ $idx = $ids[$i];
+ $this->result->add($this->contacts[$idx] ?: $this->_to_rcube_contact($this->dataset[$idx]));
+ }
+ }
+ }
+ else {
+ $this->result->count = $this->storagefolder->count($query);
+ foreach ($this->dataset as $idx => $record) {
+ $this->result->add($this->_to_rcube_contact($record));
}
}
@@ -971,9 +981,12 @@ class rcube_kolab_contacts extends rcube_addressbook
/**
* Query storage layer and store records in private member var
*/
- private function _fetch_contacts($query = array())
+ private function _fetch_contacts($query = array(), $limit = false)
{
if (!isset($this->dataset) || !empty($query)) {
+ if ($limit) {
+ $this->storagefolder->set_order_and_limit($this->_sort_columns(), $this->page_size, ($this->list_page-1) * $this->page_size);
+ }
$this->sortindex = array();
$this->dataset = $this->storagefolder->select($query);
foreach ($this->dataset as $idx => $record) {
@@ -1011,6 +1024,29 @@ class rcube_kolab_contacts extends rcube_addressbook
}
/**
+ * Return the cache table columns to order by
+ */
+ private function _sort_columns()
+ {
+ $sortcols = array();
+
+ switch ($this->sort_col) {
+ case 'name':
+ $sortcols[] = 'name';
+ case 'firstname':
+ $sortcols[] = 'firstname';
+ break;
+
+ case 'surname':
+ $sortcols[] = 'surname';
+ break;
+ }
+
+ $sortcols[] = 'email';
+ return $sortcols;
+ }
+
+ /**
* Read distribution-lists AKA groups from server
*/
private function _fetch_groups($with_contacts = false)
diff --git a/plugins/libkolab/SQL/mysql/2014021000.sql b/plugins/libkolab/SQL/mysql/2014021000.sql
new file mode 100644
index 0000000..31ce699
--- /dev/null
+++ b/plugins/libkolab/SQL/mysql/2014021000.sql
@@ -0,0 +1,9 @@
+ALTER TABLE `kolab_cache_contact` ADD `name` VARCHAR(255) NOT NULL,
+ ADD `firstname` VARCHAR(255) NOT NULL,
+ ADD `surname` VARCHAR(255) NOT NULL,
+ ADD `email` VARCHAR(255) NOT NULL;
+
+-- updating or clearing all contacts caches is required.
+-- either run `bin/modcache.sh update --type=contact` or execute the following query:
+-- DELETE FROM `kolab_folders` WHERE `type`='contact';
+
diff --git a/plugins/libkolab/bin/modcache.sh b/plugins/libkolab/bin/modcache.sh
index 40b57ac..bef931c 100755
--- a/plugins/libkolab/bin/modcache.sh
+++ b/plugins/libkolab/bin/modcache.sh
@@ -7,7 +7,7 @@
* @version 3.1
* @author Thomas Bruederli <bruederli at kolabsys.com>
*
- * Copyright (C) 2012, Kolab Systems AG <contact at kolabsys.com>
+ * Copyright (C) 2012-2014, Kolab Systems AG <contact at kolabsys.com>
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
@@ -65,6 +65,7 @@ $db->db_connect('w');
if (!$db->is_connected() || $db->is_error())
die("No DB connection\n");
+ini_set('display_errors', 1);
/*
* Script controller
@@ -142,6 +143,32 @@ case 'prewarm':
die("Authentication failed for " . $opts['user']);
break;
+/**
+ * Update the cache meta columns from the serialized/xml data
+ * (might be run after a schema update)
+ */
+case 'update':
+ // make sure libkolab classes are loaded
+ $rcmail->plugins->load_plugin('libkolab');
+
+ $folder_types = $opts['type'] ? explode(',', $opts['type']) : array('contact','configuration','event','file','task');
+ foreach ($folder_types as $type) {
+ $class = 'kolab_storage_cache_' . $type;
+ $sql_result = $db->query("SELECT folder_id FROM kolab_folders WHERE type=? AND synclock = 0", $type);
+ while ($sql_result && ($sql_arr = $db->fetch_assoc($sql_result))) {
+ $folder = new $class;
+ $folder->select_by_id($sql_arr['folder_id']);
+ echo "Updating " . $sql_arr['folder_id'] . " ($type) ";
+ foreach ($folder->select() as $object) {
+ $object['_formatobj']->to_array(); // load data
+ $folder->save($object['_msguid'], $object, $object['_msguid']);
+ echo ".";
+ }
+ echo "done.\n";
+ }
+ }
+ break;
+
/*
* Unknown action => show usage
diff --git a/plugins/libkolab/lib/kolab_storage_cache.php b/plugins/libkolab/lib/kolab_storage_cache.php
index d4a8528..925f4fe 100644
--- a/plugins/libkolab/lib/kolab_storage_cache.php
+++ b/plugins/libkolab/lib/kolab_storage_cache.php
@@ -44,6 +44,8 @@ class kolab_storage_cache
protected $max_sync_lock_time = 600;
protected $binary_items = array();
protected $extra_cols = array();
+ protected $order_by = null;
+ protected $limit = null;
/**
@@ -88,6 +90,24 @@ class kolab_storage_cache
$this->set_folder($storage_folder);
}
+ /**
+ * Direct access to cache by folder_id
+ * (only for internal use)
+ */
+ public function select_by_id($folder_id)
+ {
+ $folders_table = $this->db->table_name('kolab_folders');
+ $sql_arr = $this->db->fetch_assoc($this->db->query("SELECT * FROM $folders_table WHERE folder_id=?", $folder_id));
+ if ($sql_arr) {
+ $this->metadata = $sql_arr;
+ $this->folder_id = $sql_arr['folder_id'];
+ $this->folder = new StdClass;
+ $this->folder->type = $sql_arr['type'];
+ $this->resource_uri = $sql_arr['resource'];
+ $this->cache_table = $this->db->table_name('kolab_cache_' . $sql_arr['type']);
+ $this->ready = true;
+ }
+ }
/**
* Connect cache with a storage folder
@@ -445,12 +465,15 @@ class kolab_storage_cache
$this->_read_folder_data();
// fetch full object data on one query if a small result set is expected
- $fetchall = !$uids && $this->count($query) < 500;
- $sql_result = $this->db->query(
- "SELECT " . ($fetchall ? '*' : 'msguid AS _msguid, uid') . " FROM $this->cache_table ".
- "WHERE folder_id=? " . $this->_sql_where($query),
- $this->folder_id
- );
+ $fetchall = !$uids && ($this->limit ? $this->limit[0] : $this->count($query)) < 500;
+ $sql_query = "SELECT " . ($fetchall ? '*' : 'msguid AS _msguid, uid') . " FROM $this->cache_table ".
+ "WHERE folder_id=? " . $this->_sql_where($query);
+ if (!empty($this->order_by)) {
+ $sql_query .= ' ORDER BY ' . $this->order_by;
+ }
+ $sql_result = $this->limit ?
+ $this->db->limitquery($sql_query, $this->limit[1], $this->limit[0], $this->folder_id) :
+ $this->db->query($sql_query, $this->folder_id);
if ($this->db->is_error($sql_result)) {
if ($uids) {
@@ -562,6 +585,26 @@ class kolab_storage_cache
return $count;
}
+ /**
+ * Define ORDER BY clause for cache queries
+ */
+ public function set_order_by($sortcols)
+ {
+ if (!empty($sortcols)) {
+ $this->order_by = join(', ', (array)$sortcols);
+ }
+ else {
+ $this->order_by = null;
+ }
+ }
+
+ /**
+ * Define LIMIT clause for cache queries
+ */
+ public function set_limit($length, $offset = 0)
+ {
+ $this->limit = array($length, $offset);
+ }
/**
* Helper method to compose a valid SQL query from pseudo filter triplets
diff --git a/plugins/libkolab/lib/kolab_storage_cache_contact.php b/plugins/libkolab/lib/kolab_storage_cache_contact.php
index e17923d..fde27bc 100644
--- a/plugins/libkolab/lib/kolab_storage_cache_contact.php
+++ b/plugins/libkolab/lib/kolab_storage_cache_contact.php
@@ -23,7 +23,7 @@
class kolab_storage_cache_contact extends kolab_storage_cache
{
- protected $extra_cols = array('type');
+ protected $extra_cols = array('type','name','firstname','surname','email');
protected $binary_items = array(
'photo' => '|<photo><uri>[^;]+;base64,([^<]+)</uri></photo>|i',
'pgppublickey' => '|<key><uri>date:application/pgp-keys;base64,([^<]+)</uri></key>|i',
@@ -40,6 +40,16 @@ class kolab_storage_cache_contact extends kolab_storage_cache
$sql_data = parent::_serialize($object);
$sql_data['type'] = $object['_type'];
+ // columns for sorting
+ $sql_data['name'] = $object['name'] . $object['prefix'];
+ $sql_data['firstname'] = $object['firstname'] . $object['middlename'] . $object['surname'];
+ $sql_data['surname'] = $object['surname'] . $object['firstname'] . $object['middlename'];
+ $sql_data['email'] = is_array($object['email']) ? $object['email'][0] : $object['email'];
+
+ if (is_array($sql_data['email'])) {
+ $sql_data['email'] = $sql_data['email']['address'];
+ }
+
return $sql_data;
}
}
\ No newline at end of file
diff --git a/plugins/libkolab/lib/kolab_storage_folder.php b/plugins/libkolab/lib/kolab_storage_folder.php
index f0aac7b..1580314 100644
--- a/plugins/libkolab/lib/kolab_storage_folder.php
+++ b/plugins/libkolab/lib/kolab_storage_folder.php
@@ -92,7 +92,6 @@ class kolab_storage_folder
$this->cache->set_folder($this);
}
-
/**
*
*/
@@ -424,6 +423,21 @@ class kolab_storage_folder
return $this->cache->select($this->_prepare_query($query), true);
}
+ /**
+ * Setter for ORDER BY and LIMIT parameters for cache queries
+ *
+ * @param array List of columns to order by
+ * @param integer Limit result set to this length
+ * @param integer Offset row
+ */
+ public function set_order_and_limit($sortcols, $length = null, $offset = 0)
+ {
+ $this->cache->set_order_by($sortcols);
+
+ if ($length !== null) {
+ $this->cache->set_limit($length, $offset);
+ }
+ }
/**
* Helper method to sanitize query arguments
More information about the commits
mailing list