[dev] new optimized sql Share driver

[Duck]

I started to work on a new share driver today as I having problems with memory 
and execution time inside Horde_Share. Fist I must mention that my my driver, 
for now, do not support session cache, share names and hierarchies (no sub 
shares for ansel yet) as I was concentrating on the performance of the data 
retrieval.

So what I did. Mainly I moved all data out of DT into a separate dedicated and 
smaller table where I could flatten data. This allows me to save space and 
time as I was able to set the column types and indexes according to the real 
data representation For example DT has varchars but perms are integers and 
values must be check in pairs with their names/keys instead of a single 
integer keys.

My approach forces administration intervention as 3 tables for every share 
group (data, users perm, groups perm) must be created. And you must know even 
the structure of the share attributes and create columns in the main table, 
but we can avoid this problem by adding separate sql creation scripts in 
applications for people using this driver. Or create an more general driver 
version.

real data:
53,906 entries in horde_datatree.
299,980 horde_datatree_attributes
5.190 entries for horde.share.genie

DataTree driver
time: 14.450 sec
memory peak usage: 28,496,772

My SQL driver
time: 0.031 sec
memory peak usage: 5,236,708

I think is not need to comment how fast and memory friendly it is.

Attached is a driver and a small mysql script to create and genie shares 
tables and copy the initial testing data.

Comments before I continue?

Duck
-------------- next part --------------

CREATE TABLE `genie_shares` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  `owner` varchar(32) NOT NULL,
  `perm_creator` tinyint(3) unsigned NOT NULL,
  `perm_default` tinyint(3) unsigned NOT NULL,
  `perm_guest` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `owner` (`owner`,`perm_creator`,`perm_default`)
);

CREATE TABLE `genie_shares_groups` (
  `id` int(10) unsigned NOT NULL,
  `group_uid` int(10) unsigned NOT NULL,
  `perm` tinyint(3) unsigned NOT NULL,
  KEY `id` (`id`),
  KEY `group_uid` (`group_uid`),
  KEY `perm` (`perm`)
);

CREATE TABLE `genie_shares_users` (
  `id` int(10) unsigned NOT NULL,
  `user_uid` varchar(32) NOT NULL,
  `perm` smallint(5) unsigned NOT NULL,
  KEY `id` (`id`),
  KEY `user_uid` (`user_uid`),
  KEY `perm` (`perm`)
);

INSERT INTO
    genie_shares
    (id, name, owner)
(
    SELECT
        datatree_id, datatree_name, user_uid
    FROM
        `horde_datatree`
    WHERE
        `group_uid` = "horde.shares.genie"
);

UPDATE
    genie_shares, horde_datatree_attributes
SET
    genie_shares.perm_guest = horde_datatree_attributes.attribute_value
WHERE
    genie_shares.id = horde_datatree_attributes.datatree_id
    AND horde_datatree_attributes.attribute_name = "perm_guest";


UPDATE
    genie_shares, horde_datatree_attributes
SET
    genie_shares.perm_default = horde_datatree_attributes.attribute_value
WHERE
    genie_shares.id = horde_datatree_attributes.datatree_id
    AND horde_datatree_attributes.attribute_name = "perm_default";

UPDATE
    genie_shares, horde_datatree_attributes
SET
    genie_shares.perm_creator = horde_datatree_attributes.attribute_value
WHERE
    genie_shares.id = horde_datatree_attributes.datatree_id
    AND horde_datatree_attributes.attribute_name = "perm_creator";

-------------- next part --------------
A non-text attachment was scrubbed...
Name: sql.php
Type: application/x-php
Size: 22597 bytes
Desc: not available
Url : http://lists.horde.org/archives/dev/attachments/20080113/6cdf835c/attachment-0001.bin