[Tickets #7363] Re: Avoid bitwise operations in the SQL Share driver

[bugs at horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: http://bugs.horde.org/ticket/7363
------------------------------------------------------------------------------
  Ticket             | 7363
  Updated By         | ibon.igartua at ehu.es
  Summary            | Avoid bitwise operations in the SQL Share driver
  Queue              | Horde Framework Packages
  Version            | FRAMEWORK_3
  Type               | Enhancement
  State              | Assigned
  Priority           | 2. Medium
  Milestone          |
  Patch              |
  Owners             | Horde Developers
+New Attachment     | sql.php.patch
------------------------------------------------------------------------------


ibon.igartua at ehu.es (2010-05-31 08:56) wrote:


The attached patch optimizes the way we do the problematic slow query.  
We have patched our production environment and our problem seems to be  
definitively solved. Thanks god!

We can do it because in our horde installation we don't allow sharing  
anything with all the users (authenticated or not) - perm_creator,  
perm_default and perm_guest will always be zero in our database

We are changing the original LEFT JOIN with a UNION

At the moment we ONLY change the query if the user is not part of any  
group and if we don't have $attributes. It could be improved, but this  
is enough for us.

Original query:
	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 = 'username'
	OR (s.perm_creator & 2)
	OR (s.perm_default & 2)
	OR ( u.user_uid = 'username' AND (u.perm & 2))
	ORDER BY s.attribute_name ASC;

Improved query (written by David Fernandez -sysadmin at the UPV/EHU-  
based on the patch sent by John Madden to the kronolith mailing list:
	SELECT DISTINCT s.*
	FROM nag_shares s
	WHERE s.share_owner = 'username'
	UNION
	SELECT s2.*
	FROM nag_shares s2 RIGHT JOIN nag_shares_users u ON s2.share_id = u.share_id
	WHERE ( u.user_uid = 'username' AND (u.perm & 2))

Comments are welcomed !