This example uses 4.7.2. If you are provisioning to SQL and you can provision to three tables, that is ideal. If you want the app to read memberhsips, just make a view of the tables for the app to use.
Has an entry per provisionable group. Stored the ID (ID index), and name (ID path). The ID is the cascade delete foreign key to other tables
CREATE TABLE from_grouper_group ( id varchar(50) NOT NULL, "name" varchar(1024) NOT NULL, CONSTRAINT from_grouper_group_pk PRIMARY KEY (id) ); CREATE UNIQUE INDEX from_grouper_group_name_idx ON from_grouper_group ("name"); |
Holds arbitrary attributes about groups
CREATE TABLE from_grouper_group_attr ( group_id varchar(50) NOT NULL, attribute_name varchar(50) NOT NULL, attribute_value varchar(1000) NOT NULL, CONSTRAINT from_grouper_group_attr_pk PRIMARY KEY (group_id,attribute_name,attribute_value) ); ALTER TABLE from_grouper_group_attr ADD CONSTRAINT from_grouper_group_attr_fk FOREIGN KEY (group_id) REFERENCES from_grouper_group(id) ON DELETE CASCADE; CREATE INDEX from_grouper_group_attr_attribute_name_idx ON public.from_grouper_group_attr (attribute_name,attribute_value); |
CREATE TABLE from_grouper_entity ( id varchar(50) NOT NULL, subject_id varchar(100) NOT NULL, subject_source_id varchar(20) NOT NULL, CONSTRAINT from_grouper_entity_pk PRIMARY KEY (id) ); CREATE UNIQUE INDEX from_grouper_entity_subject_id_idx ON from_grouper_entity (subject_id,subject_source_id); |
CREATE TABLE from_grouper_membership ( group_id varchar(50) NOT NULL, entity_id varchar(50) NOT NULL, CONSTRAINT from_grouper_membership_pk PRIMARY KEY (group_id,entity_id), CONSTRAINT from_grouper_membership_fk FOREIGN KEY (group_id) REFERENCES from_grouper_group(id), CONSTRAINT from_grouper_membership_fk_1 FOREIGN KEY (entity_id) REFERENCES from_grouper_entity(id) ); |
create view from_grouper_attributes_v as select gg.name as group_name, (select gaagv.value_integer from grouper_aval_asn_group_v gaagv where gaagv.attribute_def_name_name = 'test:gidNumber' and gaagv.group_id = gg.id ) as gid_number from grouper_groups gg; |
create view from_grouper_membership_v as select fgg."name" , fge.subject_id, fge.subject_source_id, (select fgga.attribute_value from from_grouper_group_attr fgga where fgga.group_id = fgg.id and fgga.attribute_name = 'gidNumber') as gid_number from from_grouper_entity fge, from_grouper_group fgg, from_grouper_membership fgm where fgg.id = fgm.group_id and fge.id = fgm.entity_id |
|