Grouper Developers Forum

[Chris Hyzer <>]

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

Attachment: pto.gif
Description: pto.gif