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

[Stephen Marquard]

In looking at the case previously where a Resources list view of 1000+ or so items in a site generates a large volume of queries, it seemed that one problem is that the security service caches on the basis of the item reference, whereas in fact the lookup is resolved to finding whether a role has a permission in a set of authzgroups.

If we moved the caching in SecurityService one layer down (or added a layer of caching) it would eliminate duplicate queries for items that resolve to the same set of authzgroups.

I think the introduction of a 'LIKE %' query for authz lookups may well be problematic, and a cure worse than the problem.

Regards
Stephen
 
 
>>> Jim Eng <jimeng at umich.edu> 9/22/2009 6:58 PM >>> 
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"