[horde] share sql driver and bitwise operations

[Andrew Morgan]

On Wed, 17 Sep 2008, Leena Heino wrote:

> Hello,
>
> It seems that these bitwise operations in share sql-driver is a major 
> performance killer at least in MySQL. This seems to be because SQL's bitwise 
> operations do not use index in MySQL and therefore each query made in share 
> sql-driver has to make a full table scan.
>
> Would it be possible to change the database schema and the code so that each 
> permission to different user type (creator/default/guest) would be stored in 
> its own column and therefore we would have columns like perm_creator_show, 
> perm_creator_read, perm_creator_edit and perm_creator_delete. This would 
> allow us to ditch the bitwise operations in the query.
>
> Instead of current query like this:
> SELECT DISTINCT s.*  FROM nag_shares s
> LEFT JOIN nag_shares_users AS u
> ON u.share_id = s.share_id WHERE s.share_owner = 'foo'
> OR (s.perm_creator & 4) != 0
> OR (s.perm_default & 4) != 0
> OR ( u.user_uid = 'foo' AND (u.perm & 4) != 0)
> ORDER BY s.attribute_name ASC;
>
> we could do the same query as:
> SELECT DISTINCT s.*  FROM nag_shares s
> LEFT JOIN nag_shares_users AS u
> ON u.share_id = s.share_id WHERE s.share_owner = 'foo'
> OR (s.perm_creator_read = 1)
> OR (s.perm_default_read = 1)
> OR ( u.user_uid = 'foo' AND (u.perm_read = 1))
> ORDER BY s.attribute_name ASC;

I'll start working on this problem, following the discussion on 
http://bugs.horde.org/ticket/7363 for guidance.  I need to fix this by the 
end of the week and the student horde returns!

 	Andy