Skip to Content.
Sympa Menu

grouper-dev - Re: [grouper-dev] use case for sql interface to grouper

Subject: Grouper Developers Forum

List archive

Re: [grouper-dev] use case for sql interface to grouper

Chronological Thread 
  • From: Tom Barton <>
  • To: Chris Hyzer <>
  • Cc: "" <>
  • Subject: Re: [grouper-dev] use case for sql interface to grouper
  • Date: Tue, 29 Jul 2008 11:28:04 -0500

I think this would be nice, but I really want to leave tightly-coupled use cases like this out of scope for the grouper project per se. In this case, the tightly-coupled line is crossed when the use case requires a substantial slice of the site's Person Registry to be presented in a relational database co-located with the grouper DB.

There's a debatable middle ground design in signet that comes closer to handling this type of use case. Signet maintains "signetSubject" objects which cache a configurable set of subject attributes within the signet DB on Subjects that have been referenced in the signet UI to-date. Of course, signet also must implement cache refresh strategies that are as yet unproven. And even this approach doesn't actually support your use case unless some utility regularly causes signet to "touch" all Subjects in the site's Person Registry.

GRP-101 would provide diagnostic views on the grouper DB, which does not depend on any details of site-specific practices, and so doesn't trigger my "no tightly-coupled use cases!" alarm.

For sites, unlike Penn, that aren't in position to implement a tight coupling like this on their own, a stock approach is to provision group info into a store that presents Subjects, and that store can be used for applications wishing to have a rich search of Subjects satisfying a membership constraint. That can be done using LDAP or SQL.


Chris Hyzer wrote:
I know one the call we said we discussed this before, and the SQL interface was not something we wanted, but…

At penn we don’t have LDAP running yet from grouper, but our first production use for grouper was a group of faculty and staff which can be assigned as supervisors in our Paid-Time-Off system. Anyone managed in this system can search for a supervisor (show max 30 results). So we have hundreds of thousands of subjects in our subject source, and 10’s of thousands of members in this group, and we want to search by first and/or last name, and/or id (and show results in ajax auto-complete [that part isn’t important to grouper, but it is nice J]). Here is a screenshot (attached). So how would this work without SQL? Find all subjects with a name pattern, then see which ones are in that group? Or find all members in the group, and then get their subject info, and see which ones match the name pattern? And then pare it down to max size 30? Either way it doesn’t scale to what we want (search for a user with first name “*a*” and you get thousands of results).

So we just joined the membership table (and member and attribute tables) to a person table, and query from it with hibernate paging. Very easy:

select distinct ga.VALUE, ps.PENN_ID, nvl(ps.PENNNAME, ps.penn_id) pennname_id, ps.NAME, ps.description_lower, ps.FIRST_NAME, ps.LAST_NAME, ps.AFFILIATION_ID

from person_source ps, grouper_attributes ga, grouper_members gm, grouper_memberships gms

where ps.PENN_ID = gm.SUBJECT_ID AND gm.SUBJECT_SOURCE = 'pennperson'

AND gm.MEMBER_UUID = gms.MEMBER_ID AND gms.list_type = 'list'

AND gms.LIST_NAME = 'members' AND gms.OWNER_ID = ga.GROUP_ID

AND ga.FIELD_NAME = 'name' and ga.VALUE = 'penn:isc:ait:apps:pto:ptoExternalUsers'

I know not all schools have a SQL table that associates subject id’s with name (or whatever is searched), but if you do, then you will have a lot more flexibility with interfacing to grouper [especially for administrative systems that live near the or in the grouper DB J ]. I think this should be officially supported. The only thing we would need to do to support it is just not change the registry in an extreme way such that this isn’t possible, right? And maybe document how to right the queries. But if we keep all the memberships expanded and ready to go, it is easy to “support”, right? One next step to make it a little easier to use would be to provide a view of the join of these three grouper tables so that it is easier to join to whatever else… see GRP-101. Btw, I did think of adding a web service to query memberships by member names, but I couldn’t think of a way to do it in sql and ldap, so I punted… J

Our Grouper LDAP repository will probably just have subject_id’s in it anyways, not names (since not the system of record), so an LDAP query wouldn’t be possible anyways, and forget about hibernate paging…





Archive powered by MHonArc 2.6.16.

Top of Page