Grouper Loader classlist example from Penn

Summary

University of Pennsylvania implemented class-lists with the Grouper Loader which includes lists of students, instructors, assistants (to instructors), guests, and all members. The students, instructors, and assistants are fed from our student system, so they should be include/exclude. The guests are an ad hoc group. The "all" members should include the groups: students, instructors, assistants, guests. There can be cross listings (multiple names for a course), and the cross listing groups should just contain the primary course groups (5 groups: all, students, instructors, guests, assistants). Security should be setup on all courses such that there is a global readers group, a global updaters group, and in each course the instructors and assistants should be able to read and update the appropriate groups. Note you need Grouper 1.4 built after 10/18/2009, or 1.5+ for all of this to work properly.

Naming

Our naming convention is similar to name groups like this (this is just one course of many):

penn:community:student:course   -   root of all courses
penn:community:student:loader   - holds groups with loader rules
penn:community:student:security   - holds security groups (e.g. readers or updaters of all courses)

penn:community:student:course:2009C:EG:CIS:120:203:all

- holds all members for term 2009C, engineering, computer science, course 120, section 203
- includes the students, instructors, assistants, and guests

penn:community:student:course:2009C:EG:CIS:120:203:assistants
      - overall assistants to instructors (system of record, add includes, remove excludes)
penn:community:student:course:2009C:EG:CIS:120:203:assistants excludes
      -assistants to instructors excludes list (remove from system of record)
penn:community:student:course:2009C:EG:CIS:120:203:assistants includes
      - assistants to instructors includes list
penn:community:student:course:2009C:EG:CIS:120:203:assistants_systemOfRecord
      - assistants to instructors system or record from student system
penn:community:student:course:2009C:EG:CIS:120:203:guests
      - ad hoc guests group to course
penn:community:student:course:2009C:EG:CIS:120:203:instructors
      - overall instructors group (system of record, add includes, subtract excludes)
penn:community:student:course:2009C:EG:CIS:120:203:instructors excludes
      - instructors excludes, will block members from system of record
penn:community:student:course:2009C:EG:CIS:120:203:instructors includes
      - instructors includes, to add members to the system of record
penn:community:student:course:2009C:EG:CIS:120:203:instructors_systemOfRecord
      - instructors system of record, form student system
penn:community:student:course:2009C:EG:CIS:120:203:students
      - overall students list, made up of system of record, add includes, subtract excludes
penn:community:student:course:2009C:EG:CIS:120:203:students excludes
      - students excludes removes students from the system of record
penn:community:student:course:2009C:EG:CIS:120:203:students includes
      - students includes add members to the system of record
penn:community:student:course:2009C:EG:CIS:120:203:students_systemOfRecord
      - students system of record is fed from student system

Security design

There are two high level global security groups for system or admins:

penn:community:student:security:courseReaders (can read all course membership lists)
penn:community:student:security:courseUpdaters (can update all course membership lists)

The instructors and assistants can read all groups in the course (listed above), and all cross-listed courses to that course.

They can update:

guests
instructors includes
instructors excludes
students includes
students excludes
assistants includes
assistants excludes

Note, no cross listed courses are editable since they mirror the primary course

Primary course students list

For primary courses (not cross listings), lets setup a job which manages the memberships and security:

This shows:

The data is coming from our warehouse (configured in grouper-loader.properties)
There is a grouper group type applied to the group, which is addIncludeExclude. This automatically creates the includes, excludes, and overall groups
The job runs once per day at 8am
It is a SQL_GROUP_LIST which manages many groups at once
The membership query: (penn_id and group_name one), returns data like this:
12345678    penn:community:student:course:2009C:EG:BE:099:001:students_systemOfRecord
12345679    penn:community:student:course:2009C:EG:BE:100:001:students_systemOfRecord
12345677    penn:community:student:course:2009C:EG:BE:100:001:students_systemOfRecord
12345676    penn:community:student:course:2009C:EG:BE:100:001:students_systemOfRecord
12345675    penn:community:student:course:2009C:EG:BE:101:001:students_systemOfRecord
12345678    penn:community:student:course:2009C:EG:BE:101:001:students_systemOfRecord

The groups query, controls empty groups, names groups, and sets the security. It returns data that looks like this:

GROUP_NAME	READERS	UPDATERS

GROUP_NAME	READERS	UPDATERS
penn:community:student:course:2009C:EG:BE:099:001:students_systemOfRecord	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:BE:099:001:assistants,penn:community:student:course:2009C:EG:BE:099:001:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:EG:BE:099:001:assistants,penn:community:student:course:2009C:EG:BE:099:001:instructors
penn:community:student:course:2009C:EG:BE:100:001:students_systemOfRecord	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:BE:100:001:assistants,penn:community:student:course:2009C:EG:BE:100:001:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:EG:BE:100:001:assistants,penn:community:student:course:2009C:EG:BE:100:001:instructors
penn:community:student:course:2009C:EG:BE:100:201:students_systemOfRecord	penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:BE:100:201:assistants,penn:community:student:course:2009C:EG:BE:100:201:instructors	penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:EG:BE:100:201:assistants,penn:community:student:course:2009C:EG:BE:100:201:instructors

Note that the queries in the loader job are built on views, it is important that you do this so you can see when upgrades make things not compile, and so the SQL can be changed without editing the job, and since there is a character limit for attribute values
First I create a stem name view, which includes if a course is primary or not (cross listed). Note this also restricts which terms to select from


CREATE OR REPLACE VIEW COURSE_GROUP_STEM_NAME_V
(GROUP_NAME_STEM, TERM, SECTION_ID, XLIST_PRIMARY, PRIMARY_COURSE)
AS select 'penn:community:student:course:' || trim(cs.term) || ':' || trim(cs.section_school)
            || ':' || trim(cs.subject_area)
           || ':' || trim(cs.course_number) || ':' || trim(cs.section_number)
            as group_name_stem,
           cs.term, cs.section_id, cs.XLIST_PRIMARY, decode(cs.SECTION_ID, cs.XLIST_PRIMARY, 'T', 'F') as primary_course
           from course_section cs, course_section cs_xlist_primary, course_term_v ctv ...