If you have an application where users can only be in one group at a time (either the first group, or the most recent group), there is a model for that. This example is postgres but you could adapt this to any database.
Here is a folder with some groups and some users in the groups, and some in multiple groups.
Based on those times, if we are doing a flattened structure where each subject stays in their first group, then it should look like this
| Subject | Group |
|---|---|
| test.subject.0 | testGroup3 |
| test.subject.1 | testGroup1 |
| test.subject.5 | testGroup3 |
| test.subject.6 | testGroup3 |
| test.subject.8 | testGroup2 |
| test.subject.9 | testGroup1 |
We need another folder, "testFlattened", and each of those groups in there. In each of those groups needs to be each subject based on the first group they are in in the "test" folder.
In order to calculate that, we need to use point-in-time data on the memberships. To make this performant, we need to have an intermediate step, to put these memberships into a table with the subject, group, and start time.
Lets make that table
CREATE TABLE grouper_v2_5.test_flattened_groups ( group_extension varchar(255) NOT NULL, subject_id varchar(255) NOT NULL, the_start_time_millis int8 NOT NULL, PRIMARY KEY (group_extension, subject_id, the_start_time_millis) ); CREATE INDEX test_flattened_groups_idx ON grouper_v2_5.test_flattened_groups (subject_id, the_start_time_millis);
Now we need a view that we can sync to it
create view grouper_v2_5.test_flattened_groups_v as
select
gg.extension as group_extension,
gpmgl.subject_id,
gpmgl.the_start_time / 1000 as the_start_time_millis
from
grouper_pit_mship_group_lw_v gpmgl,
grouper_groups gg,
grouper_fields gf
where
gpmgl.group_id = gg.id
and gpmgl.subject_source = 'jdbc'
and gf.name = 'members'
and gf.id = gpmgl.field_id
and gpmgl.the_active = 'T'
and gg.name like 'test:%'
select * from grouper_v2_5.test_flattened_groups_v tfgv order by subject_id , the_start_time_millis
Now we need to sync that to a table, every 15 minutes. If you want real time ask the team for an example
grouper.client.properties:
grouperClient.syncTable.testFlattened.databaseFrom=grouper grouperClient.syncTable.testFlattened.databaseTo=grouper grouperClient.syncTable.testFlattened.tableFrom=test_flattened_groups_v grouperClient.syncTable.testFlattened.tableTo=test_flattened_groups grouperClient.syncTable.testFlattened.columns=group_extension, subject_id, the_start_time_millis grouperClient.syncTable.testFlattened.primaryKeyColumns=group_extension, subject_id, the_start_time_millis
grouper-loader.properties
otherJob.testFlattened.quartzCron=0 0/15 * 1/1 * ? * otherJob.testFlattened.class=edu.internet2.middleware.grouper.app.tableSync.TableSyncOtherJob otherJob.testFlattened.syncType=fullSyncFull otherJob.testFlattened.grouperClientTableSyncConfigKey=testFlattened
Go to grouper daemon ui page and schedule jobs (or bounce daemon).
Run job
Write a view to get the flattened groups out of the table, and into another grouper folder
create or replace
view grouper_v2_5.test_flattened_calculated_v as
select
gm.subject_id,
(select tfg.group_extension from test_flattened_groups tfg
where tfg.subject_id = gm.subject_id
and tfg.the_start_time_millis = (
select
min(tfg2.the_start_time_millis) as min
from
test_flattened_groups tfg2
where
gm.subject_id = tfg2.subject_id)
limit 1)
as group_extension
from
grouper_members gm
where
gm.subject_id in (select tfg.subject_id from test_flattened_groups tfg)
select * from grouper_v2_5.test_flattened_calculated_v
Make a loader job
SQL: select 'testFlattened:' || tfcv.group_extension as group_name, subject_id, 'jdbc' as subject_source_id from grouper_v2_5.test_flattened_calculated_v tfcv Group SQL (all groups whether have members or not): select 'testFlattened:' || gg.extension as group_name from grouper_groups gg where gg.enabled ='T' and gg.name like 'test:%' Groups like: testFlattened:%
