Child pages
  • Grouper loader from attributes example
Skip to end of metadata
Go to start of metadata

The design here is to intersect multiple groups of unions that are easy to setup and don't pollute the namespace with tons of intermediate groups.

An example of this use case is someone can mark an arbitrary group as "people in any of these 4 jobs", while also "in any of these 2 departments", while also "with any of these 5 titles".  That would normally be a bunch of helper groups composited together.  This allows you to do it in one group.  Granted, you lose visualization and maybe other things.  Always pros and cons.

loaderFromAttributes

Sample data

Add the groups, some memberships, and the attributes.

Note: the basis groups would normally be loaded, but here they are just GSH'ed for an example

import edu.internet2.middleware.grouper.*;
import edu.internet2.middleware.grouper.attr.*;
import edu.internet2.middleware.grouper.attr.assign.*;
import edu.internet2.middleware.grouper.attr.finder.*;

GrouperSession grouperSession = GrouperSession.startRootSession();
new GroupSave(grouperSession).assignName("basis:employee:orgUnitCodes:orgUnitCode_180101").assignCreateParentStemsIfNotExist(true).save();
new GroupSave(grouperSession).assignName("basis:employee:orgUnitCodes:orgUnitCode_123456").save();
new GroupSave(grouperSession).assignName("basis:employee:orgUnitCodes:orgUnitCode_234567").save();
new GroupSave(grouperSession).assignName("basis:employee:orgUnitCodes:orgUnitCode_345678").save();
addMember("basis:employee:orgUnitCodes:orgUnitCode_180101", "test.subject.0");
addMember("basis:employee:orgUnitCodes:orgUnitCode_123456", "test.subject.0");
addMember("basis:employee:orgUnitCodes:orgUnitCode_234567", "test.subject.0");
addMember("basis:employee:orgUnitCodes:orgUnitCode_180101", "test.subject.1");
addMember("basis:employee:orgUnitCodes:orgUnitCode_123456", "test.subject.1");
addMember("basis:employee:orgUnitCodes:orgUnitCode_234567", "test.subject.1");
addMember("basis:employee:orgUnitCodes:orgUnitCode_345678", "test.subject.1");
addMember("basis:employee:orgUnitCodes:orgUnitCode_180101", "test.subject.2");
new GroupSave(grouperSession).assignName("basis:employee:jobClasses:jobClass_EFX").assignCreateParentStemsIfNotExist(true).save();
new GroupSave(grouperSession).assignName("basis:employee:jobClasses:jobClass_ABC").save();
new GroupSave(grouperSession).assignName("basis:employee:jobClasses:jobClass_DEF").save();
new GroupSave(grouperSession).assignName("basis:employee:jobClasses:jobClass_EFG").save();
addMember("basis:employee:jobClasses:jobClass_EFX", "test.subject.0");
addMember("basis:employee:jobClasses:jobClass_ABC", "test.subject.0");
addMember("basis:employee:jobClasses:jobClass_DEF", "test.subject.0");
addMember("basis:employee:jobClasses:jobClass_EFX", "test.subject.1");
addMember("basis:employee:jobClasses:jobClass_ABC", "test.subject.1");
addMember("basis:employee:jobClasses:jobClass_DEF", "test.subject.1");
addMember("basis:employee:jobClasses:jobClass_EFG", "test.subject.1");
addMember("basis:employee:jobClasses:jobClass_ABC", "test.subject.3");
new GroupSave(grouperSession).assignName("basis:employee:jobCodes:jobCode_C902IT").assignCreateParentStemsIfNotExist(true).save();
new GroupSave(grouperSession).assignName("basis:employee:jobCodes:jobCode_B32931").save();
new GroupSave(grouperSession).assignName("basis:employee:jobCodes:jobCode_A46192").save();
addMember("basis:employee:jobCodes:jobCode_C902IT", "test.subject.0");
addMember("basis:employee:jobCodes:jobCode_B32931", "test.subject.0");
addMember("basis:employee:jobCodes:jobCode_C902IT", "test.subject.1");
addMember("basis:employee:jobCodes:jobCode_B32931", "test.subject.1");
addMember("basis:employee:jobCodes:jobCode_A46192", "test.subject.1");
addMember("basis:employee:jobCodes:jobCode_C902IT", "test.subject.2");
addMember("basis:employee:jobCodes:jobCode_C902IT", "test.subject.3");

AttributeDef attributeDef = new AttributeDefSave(grouperSession).assignName("attr:employeeIntersectDef").assignCreateParentStemsIfNotExist(true).assignCreateParentStemsIfNotExist(true).assignToGroup(true).assignAttributeDefType(AttributeDefType.attr).assignMultiAssignable(false).assignMultiValued(false).assignValueType(AttributeDefValueType.marker).save();
AttributeDef attributeValueDef = new AttributeDefSave(grouperSession).assignName("attr:employeeIntersectValueDef").assignCreateParentStemsIfNotExist(true).assignToGroupAssn(true).assignAttributeDefType(AttributeDefType.attr).assignMultiAssignable(false).assignMultiValued(true).assignValueType(AttributeDefValueType.string).save();

attributeDef.getAttributeDefActionDelegate().configureActionList("assign");
attributeValueDef.getAttributeDefActionDelegate().configureActionList("assign");

employeeIntersect = new AttributeDefNameSave(grouperSession, attributeDef).assignName("attr:employeeIntersect").assignCreateParentStemsIfNotExist(true).assignDisplayName("attr:employeeIntersect").save();

attributeValueDef.getAttributeDefScopeDelegate().assignOwnerNameEquals("attr:employeeIntersect");

jobClass = new AttributeDefNameSave(grouperSession, attributeValueDef).assignName("attr:jobClass").assignCreateParentStemsIfNotExist(true).assignDisplayName("attr:jobClass").save();
jobCode = new AttributeDefNameSave(grouperSession, attributeValueDef).assignName("attr:jobCode").assignCreateParentStemsIfNotExist(true).assignDisplayName("attr:jobCode").save();
orgUnitCode = new AttributeDefNameSave(grouperSession, attributeValueDef).assignName("attr:orgUnitCode").assignCreateParentStemsIfNotExist(true).assignDisplayName("attr:orgUnitCode").save();

Group someList1 = new GroupSave(grouperSession).assignName("app:emailLists:someList1").assignCreateParentStemsIfNotExist(true).save();

attributeAssignSave = new AttributeAssignSave(grouperSession).assignAttributeAssignType(AttributeAssignType.group).assignAttributeDefName(employeeIntersect).assignOwnerGroup(someList1);

attributeAssignOnAssignSave = new AttributeAssignSave(grouperSession).assignAttributeAssignType(AttributeAssignType.group_asgn).assignAttributeDefName(jobClass).addValue("EFX").addValue("ABC").addValue("DEF");

attributeAssignSave.addAttributeAssignOnThisAssignment(attributeAssignOnAssignSave);

attributeAssignOnAssignSave = new AttributeAssignSave(grouperSession).assignAttributeAssignType(AttributeAssignType.group_asgn).assignAttributeDefName(jobCode).addValue("C902IT").addValue("B32931");

attributeAssignSave.addAttributeAssignOnThisAssignment(attributeAssignOnAssignSave);

attributeAssignOnAssignSave = new AttributeAssignSave(grouperSession).assignAttributeAssignType(AttributeAssignType.group_asgn).assignAttributeDefName(orgUnitCode).addValue("123456").addValue("180101");

attributeAssignSave.addAttributeAssignOnThisAssignment(attributeAssignOnAssignSave);

attributeAssignSave.save();

Group someList2= new GroupSave(grouperSession).assignName("app:emailLists:someList2").assignCreateParentStemsIfNotExist(true).save();

attributeAssignSave = new AttributeAssignSave(grouperSession).assignAttributeAssignType(AttributeAssignType.group).assignAttributeDefName(employeeIntersect).assignOwnerGroup(someList2);

attributeAssignOnAssignSave = new AttributeAssignSave(grouperSession).assignAttributeAssignType(AttributeAssignType.group_asgn).assignAttributeDefName(jobClass).addValue("EFX").addValue("ABC").addValue("EFG");

attributeAssignSave.addAttributeAssignOnThisAssignment(attributeAssignOnAssignSave);

attributeAssignOnAssignSave = new AttributeAssignSave(grouperSession).assignAttributeAssignType(AttributeAssignType.group_asgn).assignAttributeDefName(jobCode).addValue("C902IT").addValue("A46192");

attributeAssignSave.addAttributeAssignOnThisAssignment(attributeAssignOnAssignSave);

attributeAssignSave.save();


Attribute assignments on groups

Note, if you delegate who has access to the employee intersect attribute def names, then others can control their own groups

Two groups with attributes:


Control views

The attribute assignment queries are complicated and have performance implications.  So to simplify things, we will make two views, and sync those into tables, so when we do the loader query, it is simpler and performs better.

View for which groups are employeeIntersects

Note, these are for mysql but will either work or will work with minor edits in other databases

create view employee_intersect_owners_v as
select distinct gaagv.group_id, gaagv.group_name,
case when exists 
(select 1 from grouper_aval_asn_asn_group_v gaaagv where gaaagv.attribute_def_name_name2 = 'attr:jobClass' and gaaagv.group_name = gaagv.group_name and gaaagv.enabled2 = 'T')
then 'T'
else 'F'
end as has_job_class,
case when exists 
(select 1 from grouper_aval_asn_asn_group_v gaaagv where gaaagv.attribute_def_name_name2 = 'attr:jobCode' and gaaagv.group_name = gaagv.group_name and gaaagv.enabled2 = 'T')
then 'T'
else 'F'
end as has_job_code,
case when exists 
(select 1 from grouper_aval_asn_asn_group_v gaaagv where gaaagv.attribute_def_name_name2 = 'attr:orgUnitCode' and gaaagv.group_name = gaagv.group_name and gaaagv.enabled2 = 'T')
then 'T'
else 'F'
end as has_org_unit_code
from  grouper_attr_asn_group_v gaagv
where gaagv.attribute_def_name_name = 'attr:employeeIntersect'
and gaagv.enabled = 'T';


View for which groups are unioned before intersected.

create view employee_intersect_unions_v as
select distinct gaaagv_union.group_id as group_id_owner,
gaaagv_union.group_name as group_name_owner,
case
  when gaaagv_union.attribute_def_name_name2 = 'attr:jobClass' then 'jobClass'
  when gaaagv_union.attribute_def_name_name2 = 'attr:jobCode' then 'jobCode'
  when gaaagv_union.attribute_def_name_name2 = 'attr:orgUnitCode' then 'orgUnitCode'
end as union_attribute,
case
  when gaaagv_union.attribute_def_name_name2 = 'attr:jobClass' then concat('basis:employee:jobClasses:jobClass_', upper(trim(gaaagv_union.value_string)))
  when gaaagv_union.attribute_def_name_name2 = 'attr:jobCode' then concat('basis:employee:jobCodes:jobCode_', upper(trim(gaaagv_union.value_string)))
  when gaaagv_union.attribute_def_name_name2 = 'attr:orgUnitCode' then concat('basis:employee:orgUnitCodes:orgUnitCode_', upper(trim(gaaagv_union.value_string)))
end as group_name_union
from  grouper_aval_asn_asn_group_v gaaagv_union
where gaaagv_union.attribute_def_name_name1 = 'attr:employeeIntersect'
  and gaaagv_union.attribute_def_name_name2 in ('attr:jobClass', 'attr:jobCode', 'attr:orgUnitCode')
  and gaaagv_union.enabled2 = 'T'
order by 2, 3, 4
;



Control tables

To simplify things and improve performance, we will sync those two views to two tables

Convert to tables:

create table employee_intersect_owners as select * from employee_intersect_owners_v;
ALTER TABLE employee_intersect_owners ADD PRIMARY KEY(group_id);
CREATE INDEX employee_int_owners_name_idx ON employee_intersect_owners (group_name);

create table employee_intersect_unions as select * from employee_intersect_unions_v;
ALTER TABLE employee_intersect_unions MODIFY group_name_union varchar(1024);
ALTER TABLE employee_intersect_unions ADD PRIMARY KEY(group_id_owner, group_name_union(300));
CREATE INDEX employee_int_unions_name_idx ON employee_intersect_unions (group_name_owner);

Sync the views to tables. 

grouper.client.properties

grouperClient.syncTable.employeeIntersectOwners.databaseFrom = grouper
grouperClient.syncTable.employeeIntersectOwners.tableFrom = employee_intersect_owners_v
grouperClient.syncTable.employeeIntersectOwners.databaseTo = grouper
grouperClient.syncTable.employeeIntersectOwners.tableTo = employee_intersect_owners
grouperClient.syncTable.employeeIntersectOwners.columns = *
grouperClient.syncTable.employeeIntersectOwners.primaryKeyColumns = group_id

grouperClient.syncTable.employeeIntersectUnions.databaseFrom = grouper
grouperClient.syncTable.employeeIntersectUnions.tableFrom = employee_intersect_unions_v
grouperClient.syncTable.employeeIntersectUnions.databaseTo = grouper
grouperClient.syncTable.employeeIntersectUnions.tableTo = employee_intersect_unions
grouperClient.syncTable.employeeIntersectUnions.columns = *
grouperClient.syncTable.employeeIntersectUnions.primaryKeyColumns = group_id_owner, group_name_union


Lets schedule this hourly at 50 minutes passed the hour

grouper-loader.properties

otherJob.employeeIntersectOwnersFull.class = edu.internet2.middleware.grouper.app.tableSync.TableSyncOtherJob
otherJob.employeeIntersectOwnersFull.quartzCron = 0 50 * * * ?
otherJob.employeeIntersectOwnersFull.grouperClientTableSyncConfigKey = employeeIntersectOwners
otherJob.employeeIntersectOwnersFull.syncType = fullSyncFull

otherJob.employeeIntersectUnionsFull.class = edu.internet2.middleware.grouper.app.tableSync.TableSyncOtherJob
otherJob.employeeIntersectUnionsFull.quartzCron = 0 51 * * * ?
otherJob.employeeIntersectUnionsFull.grouperClientTableSyncConfigKey = employeeIntersectUnions
otherJob.employeeIntersectUnionsFull.syncType = fullSyncFull

Run the jobs and make sure they are ok



Membership view

This view has the loader results to load the groups with attributes.  Note, since all the groups used are loaded basis groups (not in this example, but yes in practice) with immediate memberships, we can join to the grouper_memberships table.  This is done for performance reasons.  If there were effective members, then the grouper_memberships_lw_v would need to be used, and the grouper_groups/grouper_fields table wouldnt be needed since that is in the view.

create view employee_intersect_mships_v as
select eio.group_name as group_name,
       gm.subject_id as subject_id,
       gm.subject_source as subject_source_id
from employee_intersect_owners eio,
     grouper_members gm
     where
       (eio.has_job_class = 'T' or eio.has_job_code = 'T' or eio.has_org_unit_code = 'T')
       and  (eio.has_job_class = 'F' 
            or exists (select 1 from grouper_memberships gms, employee_intersect_unions eiu, grouper_groups gg, grouper_fields gf
                       where eiu.group_id_owner = eio.group_id and gf.name = 'members' and gf.id = gms.field_id
                         and gm.id = gms.member_id and gms.enabled = 'T'
                         and eiu.union_attribute = 'jobClass' and gg.name = eiu.group_name_union and gms.owner_id = gg.id ))
        and (eio.has_job_code = 'F' 
            or exists (select 1 from grouper_memberships gms, employee_intersect_unions eiu, grouper_groups gg, grouper_fields gf
                       where eiu.group_id_owner = eio.group_id  and gf.name = 'members' and gf.id = gms.field_id
                         and gm.id = gms.member_id and gms.enabled = 'T'
                         and eiu.union_attribute = 'jobCode' and gg.name = eiu.group_name_union and gms.owner_id = gg.id ))
       and (eio.has_org_unit_code = 'F' 
            or exists (select 1 from grouper_memberships gms, employee_intersect_unions eiu, grouper_groups gg, grouper_fields gf
                       where eiu.group_id_owner = eio.group_id  and gf.name = 'members' and gf.id = gms.field_id
                         and gm.id = gms.member_id and gms.enabled = 'T'
                         and eiu.union_attribute = 'orgUnitCode' and gg.name = eiu.group_name_union and gms.owner_id = gg.id ))



Loader job



See memberships on any groups with employee intersect attributes

Real time updates

This is not implemented yet but you could easily have a change log consumer that looks for membership changes to any of the union groups, and looks for attribute changes for the applicable attributes, and runs a full or incremental loader job based on events.  Ask the Grouper team for an example if this is a requirement.  Otherwise the above will run periodically (this example is hourly)




  • No labels