Grouper Developers Forum

[Tom Barton <>]

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.

Tom

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…
>
>  
>
> Thoughts?
>
>  
>
> Thanks,
>
> Chris
>
>
> ------------------------------------------------------------------------