Grouper provisioning / daemon tables

Create tables in 2.4

Run in GSH (pass true if not prod and you want to run the script):

edu.internet2.middleware.grouper.app.tableSync.TableSyncCreateTables.main(new String[]{"false"});

Run the script that is outputted.

grouper_loader_log

Grouper has a daemon log table: grouper_loader_log.  This table will remain, holding logs for recent runs of daemon jobs

grouper_sync

Overall table for a grouper scheduled daemon (generally for syncing things, but could be for daemon)

grouper_sync_job

Each daemon has an overall record, and one for the job that runs.  This differentiates the full sync or the incremental run, or a one off that syncs an individual group or user or membership.  This also assures that multiple jobs do not run at once.  This record should be updated in a thread so that progress can be tracked and a "heartbeat" is established so we know the job is running.  Every daemon has a record in grouper_sync, and grouper_sync_job.

grouper_sync_group

If this daemon affects groups and wants to use this table, there would be a record here for each group affected for each daemon (not each job).  For instance, for pspng, there is full and incremental, but only one record per group in this table.  If a group is deleted, this record might still exist for some more time (a week?) .  If the group is deprovisioned, this record could remain or be removed.  Note this could be tracking a group going to a target, or coming from a target.  There are no plans to integrate this with the loader.

grouper_sync_member

If this daemon affects subjects and wants to use this table, there would be a record here for each entity affected for each daemon (not each job).  For instance, for pspng, there is full and incremental, but only one record for each subject in this table.  If a member is deleted, this record might still exist for some more time (a week?) .  If the member is deprovisioned, this record could remain or be removed.  Note this could be tracking a member going to a target, or coming from a target.  There are no plans to integrate this with the loader.

grouper_sync_membership

If this daemon affects memberships and wants to use this table, there would be a record here for each effective membership affected for each daemon (not each job).  For instance, for pspng, there is full and incremental, but only one record for each membership in this table.  If a membership is deleted, this record might still exist for some more time (a week?) .  If the member is deprovisioned, this record could remain or be removed.  Note this could be tracking a membership going to a target, or coming from a target.  There are no plans to integrate this with the loader.  Generally for large jobs, memberships are not tracked.  Perhaps it is configured to be on for troubleshooting only.

grouper_sync_log

This table has the last log that work was done for the job (not daemon), or for each group, member, or membership.  These logs can be used on the UI so users can know the last work done.  If no work is done (inserts/updates), then there is no log entry change.

Sample queries

Get all memberships of provisioned groups based on sync metadata (new method)

select distinct gg.id as group_id, gg.name, gm.subject_id, gm.id as member_id
  FROM grouper_memberships ms, grouper_group_set gs, grouper_groups gg, grouper_fields gfl, grouper_members gm,
       grouper_sync gsync, grouper_sync_group gsg
  WHERE ms.owner_id = gs.member_id AND ms.field_id = gs.member_field_id and ms.enabled = 'T'
       and gs.OWNER_GROUP_ID = gg.id AND gs.FIELD_ID = gfl.ID and GM.SUBJECT_SOURCE = 'jdbc'
       and gg.name = 'test:testGroup' and gfl.name = 'members'
       and gsync.sync_engine = 'theSyncEngine' and gsync.provisioner_name = 'someLdap'
       and gsync.id = gsg.grouper_sync_id and gsg.group_id = gg.id 
       and (gsg.provisionable = 'T' or gsg.in_target = 'T')

Get all memberships of provisioned groups based on provisioning attributes (legacy method)

   SELECT distinct gg.id as group_id, gg.name,
          gm.SUBJECT_ID, GM.ID as member_id
     FROM grouper_memberships ms, grouper_group_set gs, grouper_groups gg, grouper_fields gfl, grouper_members gm,
          grouper_attribute_assign gaa_marker, grouper_attribute_def_name gadn_marker,
          grouper_attribute_assign gaa_do_provision, grouper_attribute_def_name gadn_do_provision, grouper_attribute_assign_value gaav_do_provision,
          grouper_attribute_assign gaa_target, grouper_attribute_def_name gadn_target, grouper_attribute_assign_value gaav_target          
    WHERE ms.owner_id = gs.member_id AND ms.field_id = gs.member_field_id and ms.enabled = 'T'
          and gs.OWNER_GROUP_ID = gg.id AND gs.FIELD_ID = gfl.ID and GM.SUBJECT_SOURCE = 'jdbc'
          and gfl.name = 'members'
          and gg.id = gaa_marker.owner_group_id and gaa_marker.id = gaa_do_provision.owner_attribute_assign_id and gaa_marker.id = gaa_target.owner_attribute_assign_id
          AND gaa_marker.attribute_def_name_id = gadn_marker.id and gadn_marker.name = 'etc:provisioning:provisioningMarker'
             AND gaa_marker.enabled = 'T'
          AND gaa_do_provision.attribute_def_name_id = gadn_do_provision.id and gadn_do_provision.name = 'etc:provisioning:provisioningDoProvision' 
              AND gaa_do_provision.enabled = 'T' and gaav_do_provision.attribute_assign_id = gaa_do_provision.id and gaav_do_provision.value_string = 'true'
          AND gaa_target.attribute_def_name_id = gadn_target.id and gadn_target.name = 'etc:provisioning:provisioningTarget'
              AND gaa_target.enabled = 'T' and gaav_target.attribute_assign_id = gaa_target.id and GAAV_TARGET.VALUE_STRING = 'personSourceTest'