[#6109] optimized sql Share driver
| Summary | optimized sql Share driver |
| Queue | Horde Framework Packages |
| Queue Version | HEAD |
| Type | Enhancement |
| State | Resolved |
| Priority | 2. Medium |
| Owners | Michael Rubinsky <mrubinsk (at) horde (dot) org>, Chuck Hagenbuch <chuck (at) horde (dot) org> |
| Requester | Duck <duck (at) obala (dot) net> |
| Created | 01/14/2008 (116 days ago) |
| Due | |
| Updated | 04/29/2008 (10 days ago) |
| Assigned | 04/12/2008 (27 days ago) |
| Resolved | 04/29/2008 (10 days ago) |
| Attachments | sql[7].php  kronoiths.shares.sql horde.shares.genie.sql sql.diff |
| Milestone | 3.2 |
| Patch | 1 |
State ⇒ Resolved
This is done and a pretty darn solid beta if I say so myself. Huge thanks to Duck for getting this started and to Michael R. for all the help testing and for writing the migration scripts.Also added upgrade files to Genie as well...Taken from
Taken from Duck
Assigned to Michael Rubinsky
No great shakes, but yup, it's done. Now we test :)Looks like all we have left now is the conf.xml entry. Chuck, you said you have it done, right?
> As I remember, even when shares are disabled, the default user's
> share is still created. Like the default user calendar. Performing
> the select joins the users/group table. But those two tables will be
> always empty. So I add a check to avoid the JOIN.
Shouldn't the check be the other way then? Right now it only joins in those tables if no_sharing is enabled? Actually I think it should be removed, since even if users aren't allowed to control who sees their shares, someone might create system shares that everyone sees.
> Duck-
> can you explain what the check in Horde_Share_sql::_getShareCriteria
> against $conf['share']['no_sharing'] is for? It doesn't appear in the
> other backends, and I'm inclined to remove it here also unless
> there's a reason I'm missing.
As I remember, even when shares are disabled, the default user's share is still created. Like the default user calendar. Performing the select joins the users/group table. But those two tables will be always empty. So I add a check to avoid the JOIN.
Assigned to Duck
Duck-can you explain what the check in Horde_Share_sql::_getShareCriteria against $conf['share']['no_sharing'] is for? It doesn't appear in the other backends, and I'm inclined to remove it here also unless there's a reason I'm missing.
Ingo migration and UPGRADING done that leaves:
>> - datatree-to-sql upgrade scripts for Mnemo, Nag
>> - conf.xml entry (done, just waiting for full functionality)
>> - docs/UPGRADING entries Mnemo, Nag
Kronolith migration and UPGRADING doneTurba migrations and UPGRADING is done (that should be it for Turba)
So..to update the summary:
> Ingo table defs are done. That leaves, to summarize:
>
> - datatree-to-sql upgrade scripts for Ingo, Kronolith, Mnemo, Nag
> - conf.xml entry (done, just waiting for full functionality)
> - docs/UPGRADING entries Ingo, Kronolith, Mnemo, Nag
I'll start moving the migration script to the other apps and post as I complete them.
Ingo table defs are done. That leaves, to summarize:
- datatree-to-sql upgrade scripts for Ingo, Kronolith, Mnemo, Nag, Turba
- conf.xml entry (done, just waiting for full functionality)
- docs/UPGRADING entries
Table defs and table def upgrades are done for Nag and Mnemo.Table defs are done for Turba - still need to work on the conversion script
I'll try to work on what's required for Turba...table defs have been added for Kronolith; still need the upgrade scripts (both for creating the tables and migration).State ⇒ Assigned
Assigned to
Assigned to Chuck Hagenbuch
Code is working for me on SQLite now as well as MySQL. Still to do:
- sql scripts for Ingo, Kronolith (done), Mnemo, Nag, Turba
- datatree-to-sql upgrade scripts for Ingo, Kronolith, Mnemo, Nag, Turba
- conf.xml entry (done, just waiting for full functionality)
- docs/UPGRADING entries
I'm working at this, but help is certainly welcome. If anyone else wants to/can jump in here, please just add a note here so we don't overlap.
Committed along with making the code parse under PHP 4. Still need to remove the REPLACE INTO statement so it works in more than MySQL.New Attachment: sql.diff
State ⇒ Accepted
After framework update I noticed that I didn't update the code here.- Fix unsetting the permissions for guest/default/creator.
- Don't avoid permission check with attributes.
> Okay, so we just need a 'sql' option with no extra params in the
> $conf['shares'] array?
yes
Okay, so we just need a 'sql' option with no extra params in the $conf['shares'] array?> I've committed the base driver code. Can you post what the config
> settings look like for this driver?
No configuration options is needed. It uses the global horde sql configuration (with split write support). All you need is to create additional tables per very share group. Like is already attached here in horde.shares.genie.sql. You must just change the prefix by scope name and add attributes columns if needed. I am attaching another example for Kronolith. Structures for each application can be bundled with application sql structure.
State ⇒ Feedback
I've committed the base driver code. Can you post what the config settings look like for this driver?Patch ⇒ 1
Milestone ⇒ 3.2
Priority ⇒ 2. Medium
Should go into 3.2 as an experimental driver.New Attachment: sql[7].php
I am uploading a new version that fixes a little bug in _getShareCriteria I discovered while trying to port it to Ansel.
Horde_Share_Object_sqlmini is us just a hack, and can be completely omitted. Is a fact that all applications fetches the list only to get the share id, name and owner. All other actions are done on a single share object. Only Kronolith uses the results from listShares for permission checking. So it would be better to remove Horde_Share_Object_sqlmini and add a method to return an indexed array for share names/owners as we already discussed some time ago in the list.
> Just to be clear, shareimport-genie.sql is replaced by
> horde.shares.genie.sql, right?
yes
Just to be clear, shareimport-genie.sql is replaced by horde.shares.genie.sql, right?
New Attachment: sql[6].php
fix harcoded DT permissons and param_extra queryNew Attachment: sql[5].php
This is maybe a hack. But introducing an dedicated object with minimal data (id,name,owner) to work with share listing in panels etc lower the ram usage with 5.200 entries from 18M to 7M.
Tried with indexed arrays and noticed that uses even 1M less.
New Attachment: sql[4].php
Do not sort shares by name inside PHP as SQL can do it so. Now I finaly can list all 5.200 wish-list without timeout. With this I think is the last thing I can do without share api changes. Now I am just wainting mysql to use indexes for bitwise operators.
Duck
New Attachment: sql[3].php
- Added __sleep and __wakeup methods- Add perm_extra columns to mark if the share has any additional user or group definition. So we avoid queries for shares that don't have them set. In a lot of occasions we need only one query to get the entire share list.
New Attachment: sql[2].php
Make a local copy of listShares() to do a query less - do not query the db for ids and then select the data, but pick the data directlyNew Attachment: horde.shares.genie.sql
Update transition script works for kronolith, nag, mnemo and genie. Create tables, transfers data, and delete old datatree entries. Just replace the string genie with appropriate share name. Unfortunately uses subselect so it will probably work with mysql only.New Attachment: sql[1].php
Fix _getShareCriteria().
Seems to work with kronolith, nag, mnemo and genie.
New Attachment: shareimport-genie.sql
Genie DT to SQL conversion sql subselect script.With little modification it should work for all shares groups.
New Attachment: sql.php
Queue ⇒ Horde Framework Packages
Summary ⇒ optimized sql Share driver
Type ⇒ Enhancement
Priority ⇒ 1. Low
State ⇒ New
- flat attribute table- data splited per tables
- split read support