[Building Sakai] Proposed API change to reduce cost of resources list view

[Mark Norton]

My understanding is that SQL statements involving "like" are not very 
efficient, either.  Are you trading one problem for another?

- Mark

Jim Eng wrote:
> The list view in the Resources tool in Sakai 2.x has a column to show  
> who can see each resource or folder. Potential values include the  
> entire site, members of particular groups, and the public.
>
> Items at any level may be made public, so the question ("is this item  
> public?") must be asked for virtually every item in a site.  
> Determining whether resources and folders are open to the public is  
> relatively expensive because it must be repeated so often.  If a site  
> contains lots of resources, the expanded view can results in dozens,  
> hundreds or thousands of queries.  The only short cut at this time is  
> that the query can be avoided for items that are inside a folder that  
> is public since they will also be public.
>
> The SQL query is against the various realms tables.  It asks whether  
> the anonymous role has the "content.read" permission for the realm  
> associated with a particular resource or folder (or for the realm of  
> any folder that contains the item).
>
> We could improve on that if the SecurityService (or  
> AuthzGroupService?) interface had a method to ask a different  
> question: Are there any realms within this site for which the  
> anonymous role has "content.read" permission (and if so, what are they)?
>
> This query would likely be implemented with a SQL "like" operator and  
> a one-sided wildcard. This is actually a join of information from  
> three or four tables, but the query would be asking for a list of  
> sakai_realm.realm_id values like '/content/group/<side-id>/%' (or like  
> '/content/group-user/<user-id>/%' for workspaces, and it's possible  
> there are other variations) for which there are entries in the  
> sakai_realm_rl_fn table where the role_key identifies the anonymous  
> role and the function_key corresponds to "content.read". The result  
> would be a list of realm identifiers.
>
> That would allow Content Hosting to get a list of realms (possibly  
> empty) for items that are public.  Content Hosting could then avoid  
> making dozens or hundreds or thousands of individual queries by  
> checking that list of public realms.  An alternative to adding a  
> specific API method for this would be to execute that query in the  
> SecurityService and do some form of caching so the Security Service  
> could answer this question hundreds of times without doing hundreds of  
> individual queries.
>
> Thoughts?
>
> Jim
>
> _______________________________________________
> sakai-dev mailing list
> sakai-dev at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>
> TO UNSUBSCRIBE: send email to sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of "unsubscribe"
>
>