Main Grouper Loader page

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

      
    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 ...
  • Then I built on that to create a course group name which has all the names of the groups (for easy reuse)
CREATE OR REPLACE VIEW COURSE_GROUP_NAME_V
(GROUP_NAME_STEM, STUDENTS, STUDENTS_SYSTEMOFRECORD, STUDENTS_INCLUDES, STUDENTS_EXCLUDES,
 INSTRUCTORS, INSTRUCTORS_SYSTEMOFRECORD, INSTRUCTORS_INCLUDES, INSTRUCTORS_EXCLUDES, ASSISTANTS,
 ASSISTANTS_SYSTEMOFRECORD, ASSISTANTS_INCLUDES, ASSISTANTS_EXCLUDES, ALLGROUP, GUESTS,
 SECTION_ID, TERM, XLIST_PRIMARY, PRIMARY_COURSE)
AS
select
cgsnv.GROUP_NAME_STEM,
cgsnv.GROUP_NAME_STEM || ':students' as students,
cgsnv.GROUP_NAME_STEM || ':students_systemOfRecord' as students_systemOfRecord,
cgsnv.GROUP_NAME_STEM || ':students_includes' as students_includes,
cgsnv.GROUP_NAME_STEM || ':students_excludes' as students_excludes,
cgsnv.GROUP_NAME_STEM || ':instructors' as instructors,
cgsnv.GROUP_NAME_STEM || ':instructors_systemOfRecord' as instructors_systemOfRecord,
cgsnv.GROUP_NAME_STEM || ':instructors_includes' as instructors_includes,
cgsnv.GROUP_NAME_STEM || ':instructors_excludes' as instructors_excludes,
cgsnv.GROUP_NAME_STEM || ':assistants' as assistants,
cgsnv.GROUP_NAME_STEM || ':assistants_systemOfRecord' as assistants_systemOfRecord,
cgsnv.GROUP_NAME_STEM || ':assistants_includes' as assistants_includes,
cgsnv.GROUP_NAME_STEM || ':assistants_excludes' as assistants_excludes,
cgsnv.GROUP_NAME_STEM || ':all' as allGroup,
cgsnv.GROUP_NAME_STEM || ':guests' as guests,
cgsnv.SECTION_ID,
cgsnv.TERM,
cgsnv.XLIST_PRIMARY,
cgsnv.PRIMARY_COURSE
from COURSE_GROUP_STEM_NAME_V cgsnv
  • Then we can build course list view (note the members of cross listed courses are includes in the primary course)
CREATE OR REPLACE VIEW COURSE_PRIMARY_STUDENT_LIST_V
(GROUP_NAME, PENN_ID, TERM, SECTION_ID)
AS
SELECT
  distinct cgnv.STUDENTS_SYSTEMOFRECORD as group_name,
  eav.PENN_ID,
  eav.TERM,
  cs.XLIST_PRIMARY section_id
FROM
  DWADMIN.ENROLLMENT_ALL_V  eav,
  DWADMIN.COURSE_SECTION  cs,
  course_group_name_v cgnv
WHERE
  eav.SECTION_ID=cs.section_id and eav.TERM=cs.TERM
  and cgnv.SECTION_ID = cs.XLIST_PRIMARY
  and cgnv.TERM = cs.term
  and cgnv.PRIMARY_COURSE = 'T'
  • Finally we make the primary students group view for the students list which includes the security aspects
CREATE OR REPLACE VIEW COURSE_PRIM_STUD_GROUP_QUERY_V
(GROUP_NAME, READERS, UPDATERS, SECTION_ID, TERM)
AS
select cgnv.STUDENTS_SYSTEMOFRECORD as group_name,
'penn:community:student:security:courseReaders, ' || cgnv.ASSISTANTS
|| ',' || cgnv.INSTRUCTORS as readers,
'penn:community:student:security:courseUpdaters, ' || cgnv.ASSISTANTS
|| ',' || cgnv.INSTRUCTORS as updaters, cgnv.SECTION_ID, cgnv.TERM
from course_group_name_v cgnv where cgnv.PRIMARY_COURSE = 'T'

Primary course instructors list

Note: this is called instructors since it is more of a role than a title, so it is not "Professors".  Here is the loader screenshot:

  • This is a query which runs from our warehouse connection (configured in grouper-loader.properties), add include/exclude type to the groups for include exclude lists, runs at 8:05 in the morning
  • The membership query builds on a view which gives instructors or admins for a course (or any of its cross listings)
select cpiav.INSTRUCTOR_PENN_ID penn_id,
cpiav.instructors_GROUP_NAME as group_name
from COURSE_PRIMARY_INSTR_ASST_V cpiav
where cpiav.INSTRUCTOR_LOAD <> 0
  • This returns data like this:

    PENN_ID

    GROUP_NAME

    12345678

    penn:community:student:course:2009C:EG:BE:200:204:instructors_systemOfRecord

    12345678

    penn:community:student:course:2009C:EG:MGMT:586:501:instructors_systemOfRecord

    12345677

    penn:community:student:course:2009C:EG:MSE:990:001:instructors_systemOfRecord

    12345676

    penn:community:student:course:2009C:EG:BE:497:001:instructors_systemOfRecord

  • The groups view gives the name of the group and the security rules
CREATE OR REPLACE VIEW COURSE_PRI_INSTR_GROUP_QUERY_V
(GROUP_NAME, READERS, UPDATERS, SECTION_ID, TERM)
AS
select cgnv.instructors_SYSTEMOFRECORD as group_name,
'penn:community:student:security:courseReaders, ' || cgnv.ASSISTANTS
|| ',' || cgnv.INSTRUCTORS as readers,
'penn:community:student:security:courseUpdaters, ' || cgnv.ASSISTANTS
|| ',' || cgnv.INSTRUCTORS as updaters, cgnv.SECTION_ID, cgnv.TERM
from course_group_name_v cgnv
  • This returns data that looks like this:

    GROUP_NAME

    READERS

    UPDATERS

    SECTION_ID

    TERM

    penn:community:student:course:2009C:AS:ANTH:258:401:instructors_systemOfRecord           

    penn:community:student:security:courseReaders, penn:community:student:course:2009C:AS:ANTH:258:401:assistants,penn:community:student:course:2009C:AS:ANTH:258:401:instructors

    penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:AS:ANTH:258:401:assistants,penn:community:student:course:2009C:AS:ANTH:258:401:instructors

    ANTH258401

    2009C

    penn:community:student:course:2009C:FA:ARCH:727:401:instructors_systemOfRecord

    penn:community:student:security:courseReaders, penn:community:student:course:2009C:FA:ARCH:727:401:assistants,penn:community:student:course:2009C:FA:ARCH:727:401:instructors

    penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:FA:ARCH:727:401:assistants,penn:community:student:course:2009C:FA:ARCH:727:401:instructors

    ARCH727401   

    2009C

Primary course assistants list

  • Note, this is not called "teaching assistants" since this group is more of a role than a title
  • Here is the loader screenshot
  • This runs from the data warehouse (configured in grouper-loader.properties), at 8:10 in the morning, and includeExclude is added for the include and exclude lists
  • Here is a view of the members:
select cpiav.INSTRUCTOR_PENN_ID penn_id,
cpiav.ASSISTANTS_GROUP_NAME as group_name
from COURSE_PRIMARY_INSTR_ASST_V cpiav
where cpiav.INSTRUCTOR_LOAD = 0
  • Here is a sample of the data returned:

    PENN_ID

    GROUP_NAME

    38430684

    penn:community:student:course:2009C:EG:MEAM:210:203:assistants_systemOfRecord

    45535464

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

    24636453

    penn:community:student:course:2009C:EG:ESE:112:001:assistants_systemOfRecord

  • Here is the view of the group query:
CREATE OR REPLACE VIEW COURSE_PRIM_ASST_GROUP_QUERY_V
(GROUP_NAME, READERS, UPDATERS, SECTION_ID, TERM)
AS
select cgnv.assistants_SYSTEMOFRECORD as group_name,
'penn:community:student:security:courseReaders, ' || cgnv.ASSISTANTS
|| ',' || cgnv.INSTRUCTORS as readers,
'penn:community:student:security:courseUpdaters, ' || cgnv.ASSISTANTS
|| ',' || cgnv.INSTRUCTORS as updaters, cgnv.SECTION_ID, cgnv.TERM
from course_group_name_v cgnv
  • Here is an example of data returned:

    GROUP_NAME

    READERS

    UPDATERS

    SECTION_ID

    TERM

    penn:community:student:course:2009C:AS:ANTH:258:401:assistants_systemOfRecord

    penn:community:student:security:courseReaders, penn:community:student:course:2009C:AS:ANTH:258:401:assistants,penn:community:student:course:2009C:AS:ANTH:258:401:instructors

    penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:AS:ANTH:258:401:assistants,penn:community:student:course:2009C:AS:ANTH:258:401:instructors

    ANTH258401

    2009C

    penn:community:student:course:2009C:FA:ARCH:727:401:assistants_systemOfRecord

    penn:community:student:security:courseReaders, penn:community:student:course:2009C:FA:ARCH:727:401:assistants,penn:community:student:course:2009C:FA:ARCH:727:401:instructors

    penn:community:student:security:courseUpdaters, penn:community:student:course:2009C:FA:ARCH:727:401:assistants,penn:community:student:course:2009C:FA:ARCH:727:401:instructors

    ARCH727401

    2009C

    penn:community:student:course:2009C:EG:BE:099:001:assistants_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

    BE  099001

    2009C

  •  

Primary guests group

The guests group is an ad hoc group, but we want it auto-created, and managed by the loader for security.  The grouper loader doesnt really support this at the moment (we should add support), but one workaround is just to sync the members from the grouper membership table (immediate memberships).  Here is the loader screenshot

  • Note, this is driven from the grouper db connection, since it needs to hit the grouper registry for membership info.  The members view (needs to join with a db link to the warehouse to get the group names), note: driving site means that the names should be brought from the warehouse to the grouper registry, then calculated there.  It runs at 8:15 (even though screen says 8:20, this was changed).  Note this query will need to change in 1.5
CREATE OR REPLACE VIEW COURSE_GUESTS_MEMBERS_V
(GROUP_NAME, SUBJECT_ID, SUBJECT_SOURCE_ID)
AS
select /*+DRIVING_SITE(gga)*/
cgnv.guests as group_name, gm.subject_id, gm.SUBJECT_SOURCE as subject_source_id
 from course_group_name_v@authzadm_warehouse cgnv,
grouper_attributes gga,
grouper_fields ggf, grouper_memberships gms, grouper_fields gf,
grouper_members gm
where gga.field_id = ggf.ID and ggf.NAME = 'name'
and gga.VALUE = cgnv.guests
and cgnv.primary_course = 'T'
and gms.OWNER_ID = gga.GROUP_ID
and gms.FIELD_ID = gf.ID
and gms.MSHIP_TYPE = 'immediate'
and gf.NAME = 'members'
and gms.MEMBER_ID = gm.id
  • This gives data like this:

    GROUP_NAME

    SUBJECT_ID

    SUBJECT_SOURCE_ID

    penn:community:student:course:2009C:AS:ANTH:258:401:guests

    12345678

    pennperson

    penn:community:student:course:2009C:AS:ANTH:258:402:guests

    12345677

    pennperson

  • The groups query is run via db link as well (since the members query must be run from grouper registry, and both queries must run from same db).  Here is the view (on warehouse)
CREATE OR REPLACE VIEW COURSE_GUESTS_GROUP_QUERY_V
(GROUP_NAME, READERS, UPDATERS)
AS
select primary_cgnv.guests as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers,
'penn:community:student:security:courseUpdaters, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as updaters
from course_group_name_v primary_cgnv
where primary_cgnv.primary_course = 'T'
  • Here is an example of the data:

    GROUP_NAME

    READERS

    UPDATERS

    penn:community:student:course:2009C:EG:BE:099:001:guests

    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:guests      

    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:guests     

    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

  •  

The 'All' groups for primary courses

Now that we have the students, instructors, assistants and guests, we can roll up all of those into an 'all' group.  Here is the loader screenshot:

  • This runs at 8:20 (change from before, the screen says 8:15).  Since this builds on existing groups and we need to know the group id for the subject id.
  • Here is the membership view (in warehouse db):
CREATE OR REPLACE VIEW COURSE_ALL_GROUP_MEMBERS_V
(GROUP_NAME, MEMBER_GROUP_NAME)
AS
select primary_cgnv.allgroup as group_name,
primary_cgnv.ASSISTANTS as member_group_name
from course_group_name_v primary_cgnv
where primary_cgnv.primary_course = 'T'
union all
select primary_cgnv.allgroup as group_name,
primary_cgnv.guests as member_group_name
from course_group_name_v primary_cgnv
where primary_cgnv.primary_course = 'T'
union all
select primary_cgnv.allgroup as group_name,
primary_cgnv.students as member_group_name
from course_group_name_v primary_cgnv
where primary_cgnv.primary_course = 'T'
union all
select primary_cgnv.allgroup as group_name,
primary_cgnv.instructors as member_group_name
from course_group_name_v primary_cgnv
where primary_cgnv.primary_course = 'T'
  • Here is the membership view (in grouper db): [note, this query will change in 1.5)
CREATE OR REPLACE VIEW COURSE_ALL_GROUP_V
(GROUP_NAME, SUBJECT_ID, SUBJECT_SOURCE_ID, MEMBER_GROUP_NAME)
AS
select /*+DRIVING_SITE(gga)*/
cagmv.GROUP_NAME,
gga.GROUP_ID as subject_id, 'g:gsa' as subject_source_id,
cagmv.member_group_name
from COURSE_ALL_GROUP_MEMBERS_V@authzadm_warehouse cagmv, grouper_attributes gga,
grouper_fields ggf
where gga.field_id = ggf.ID and ggf.NAME = 'name'
and gga.VALUE = cagmv.member_group_name
  • Here is an example of the data returned:

GROUP_NAME

SUBJECT_ID

SUBJECT_SOURCE_ID

MEMBER_GROUP_NAME

penn:community:student:course:2009C:EG:BE:099:001:all

f73ec967fd7f4710929a82f5d9d299cf   

g:gsa 

penn:community:student:course:2009C:EG:BE:099:001:assistants

penn:community:student:course:2009C:EG:BE:099:001:all

28374298347928347982374928347

g:gsa

penn:community:student:course:2009C:EG:BE:099:001:guests

penn:community:student:course:2009C:EG:BE:099:001:all

287349823749827349827349823749

g:gsa

penn:community:student:course:2009C:EG:BE:099:001:students

penn:community:student:course:2009C:EG:BE:099:001:all

2387234897234987293874982374

g:gsa

penn:community:student:course:2009C:EG:BE:099:001:instructors

penn:community:student:course:2009C:EG:BE:099:002:all

23874928374982374982734897329

g:gsa

penn:community:student:course:2009C:EG:BE:099:002:assistants

  • Here is the group view
CREATE OR REPLACE VIEW COURSE_ALL_GROUP_GROUP_QUERY_V
(GROUP_NAME, READERS, UPDATERS)
AS
select primary_cgnv.ALLGROUP as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers,
null as updaters
from course_group_name_v primary_cgnv
where primary_cgnv.primary_course = 'T'
  • Here is an example of the data

    GROUP_NAME

    READERS   

    UPDATERS

       penn:community:student:course:2009C:EG:BE:099:001:all       

    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:course:2009C:EG:BE:100:001:all

    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

     

Crosslisted course lists

This is the screenshot for the crosslist loader job

  • Again, since this is loading groups in other groups, we need to run from the grouper DB connection.  It says 6:15, but this is really run at 8:30am.
  • Here is the cross list members view in the warehouse:
CREATE OR REPLACE VIEW COURSE_XLIST_GROUP_MEMBERS_V
(GROUP_NAME, MEMBER_GROUP_NAME)
AS
select xlist_cgnv.ASSISTANTS as group_name,
primary_cgnv.ASSISTANTS as member_group_name
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.instructors as group_name,
primary_cgnv.instructors as member_group_name
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.students as group_name,
primary_cgnv.students as member_group_name
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.ALLGROUP as group_name,
primary_cgnv.allgroup as member_group_name
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.GUESTS as group_name,
primary_cgnv.guests as member_group_name
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
  • Here is the members query in the grouper database
CREATE OR REPLACE VIEW COURSE_XLIST_V
(GROUP_NAME, SUBJECT_ID, SUBJECT_SOURCE_ID, XLIST_PRIMARY_GROUP_NAME)
AS
select cgm.GROUP_NAME,
ga.GROUP_ID as subject_id, 'g:gsa' as subject_source_id,
cgm.member_group_name as xlist_primary_group_name
from course_xLIST_GROUP_MEMBERS_V@authzadm_warehouse cgm, grouper_attributes ga,
grouper_fields gf
where ga.field_id = gf.ID and gf.NAME = 'name'
and ga.VALUE = cgm.member_group_name
  • Here is a sample of the data returned

    GROUP_NAME

    SUBJECT_ID

    SUBJECT_SOURCE_ID

    XLIST_PRIMARY_GROUP_NAME

    penn:community:student:course:2009C:AS:ANTH:258:401:assistants

    dc0b03453453453454357d7d23c4   

    g:gsa   

    penn:community:student:course:2009C:EG:CIS:106:401:assistants

    penn:community:student:course:2009C:FA:ARCH:727:401:all

    234234234234234234234234234

    g:gsa   

    penn:community:student:course:2009C:EG:IPD:527:401:all

    penn:community:student:course:2009C:EG:BE:330:401:instructors

    435345345345345345345435345

    g:gsa   

    penn:community:student:course:2009C:EG:MSE:330:401:instructors

  • Here is the all group query
CREATE OR REPLACE VIEW COURSE_XLIST_GROUP_GROUP_V
(GROUP_NAME, READERS)
AS
select xlist_cgnv.ASSISTANTS as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.INSTRUCTORS as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.STUDENTS as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.allgroup as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
union all
select xlist_cgnv.guests as group_name,
'penn:community:student:security:courseReaders, ' || primary_cgnv.ASSISTANTS
|| ',' || primary_cgnv.INSTRUCTORS as readers
from course_group_name_v xlist_cgnv,
course_group_name_v primary_cgnv
where xlist_cgnv.PRIMARY_COURSE = 'F'
and primary_cgnv.primary_course = 'T'
and xlist_cgnv.XLIST_PRIMARY = primary_cgnv.SECTION_ID
  • Here is sample data:

    GROUP_NAME

    READERS

    penn:community:student:course:2009C:AS:ANTH:258:401:assistants

    penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:CIS:106:401:assistants,penn:community:student:course:2009C:EG:CIS:106:401:instructors

    penn:community:student:course:2009C:FA:ARCH:727:401:instructors

    penn:community:student:security:courseReaders, penn:community:student:course:2009C:EG:IPD:527:401:assistants,penn:community:student:course:2009C:EG:IPD:527:401:instructors

  •