Grouper Developers Forum

[Chris Hyzer <>]

Hey,

Regarding this jira issue

https://bugs.internet2.edu/jira/browse/GRP-101

https://wiki.internet2.edu/confluence/display/GrouperWG/Database+comments+and+views

Grouper now comes packaged with db table/view/column comments and 13 views.  For comments to work, you must be running Oracle or Postgres (MySQL is does not have good enough support for comments).  For views, you must be using Oracle, MySQL, or Postgres.

The views are intended for troubleshooting purposes.  They are experimental, and are subject to change.  If you need a similar view for a system, copy the source, pick a different name (preferably not starting with grouper_), and use the built in views as examples.  Half the views show existing tables with friendly names, half are reports which show some stats about each grouper object.

You can disable the comments or views from being generated in the DDL with grouper.properties settings: ddlutils.disableViews, ddlutils.disableComments

The views and comments are generated each time a DDL is generated since it isnt known if there is a change or not... so the default DDL script is fairly large (though innocuous)

For people who arent running the right DB, here is an oracle dump of ddl for the comments and views.

CREATE OR REPLACE VIEW GROUPER_ATTRIBUTES_V (GROUP_NAME, ATTRIBUTE_NAME, ATTRIBUTE_VALUE, GROUP_TYPE_NAME, FIELD_ID, ATTRIBUTE_ID, GROUP_ID, GROUPTYPE_UUID) AS select  (select ga2.value from grouper_attributes ga2 , grouper_fields gf where ga2.group_id = gg.id and gf.name = 'name' and gf.id = ga2.field_id) as group_name, gf.NAME as attribute_name, ga.VALUE as attribute_value, gt.NAME as group_type_name, ga.FIELD_ID, ga.ID as attribute_id, gg.ID as group_id, gf.grouptype_uuid from grouper_attributes ga, grouper_groups gg, grouper_fields gf, grouper_types gt where ga.FIELD_ID = gf.ID and ga.GROUP_ID = gg.ID and gf.GROUPTYPE_UUID = gt.ID ;

 

COMMENT ON TABLE GROUPER_ATTRIBUTES_V IS 'Join of groups and attributes with friendly names.  Attributes are name/value pairs for groups.  Each group type is related to a set of 0 to many attributes, each attribute is related to one group type.grouper_fields holds each attribute name under the field type of ^attribute^';

 

COMMENT ON COLUMN GROUPER_ATTRIBUTES_V.GROUP_NAME IS 'Group name is full ip path, e.g. school:stem1:groupId';

 

COMMENT ON COLUMN GROUPER_ATTRIBUTES_V.ATTRIBUTE_NAME IS 'Attribute name is the name of the name/value pair';

 

COMMENT ON COLUMN GROUPER_ATTRIBUTES_V.ATTRIBUTE_VALUE IS 'Attribute value is the value of the name/value pair';

 

COMMENT ON COLUMN GROUPER_ATTRIBUTES_V.GROUP_TYPE_NAME IS 'Group_type_name is the name of the group type this attribute is related to';

 

COMMENT ON COLUMN GROUPER_ATTRIBUTES_V.FIELD_ID IS 'Field_id is the uuid that uniquely identifies a the field';

 

COMMENT ON COLUMN GROUPER_ATTRIBUTES_V.ATTRIBUTE_ID IS 'Attribute_id is the uuid that uniquely identifies the pairing of group and attribute';

 

COMMENT ON COLUMN GROUPER_ATTRIBUTES_V.GROUP_ID IS 'Group_id is the uuid that uniquely identifies a group';

 

COMMENT ON COLUMN GROUPER_ATTRIBUTES_V.GROUPTYPE_UUID IS 'GroupType_uuid is the uuid that uniquely identifies a group type';

 

CREATE OR REPLACE VIEW GROUPER_COMPOSITES_V (OWNER_GROUP_NAME, COMPOSITE_TYPE, LEFT_FACTOR_GROUP_NAME, RIGHT_FACTOR_GROUP_NAME, OWNER_GROUP_DISPLAYNAME, LEFT_FACTOR_GROUP_DISPLAYNAME, RIGHT_FACTOR_GROUP_DISPLAYNAME, OWNER_GROUP_ID, LEFT_FACTOR_GROUP_ID, RIGHT_FACTOR_GROUP_ID, COMPOSITE_ID, CREATE_TIME, CREATOR_ID, HIBERNATE_VERSION_NUMBER) AS select (select ga.value from grouper_attributes ga , grouper_fields gf where ga.group_id = gc.owner and gf.name = 'name' and gf.id = ga.field_id) as owner_group_name, gc.TYPE as composite_type, (select ga.value from grouper_attributes ga , grouper_fields gf where ga.group_id = gc.left_factor and gf.name = 'name' and gf.id = ga.field_id) as left_factor_group_name, (select ga.value from grouper_attributes ga , grouper_fields gf where ga.group_id = gc.right_factor and gf.name = 'name' and gf.id = ga.field_id) as right_factor_group_name, (select ga.value from grouper_attributes ga , grouper_fields gf where ga.group_id = gc.owner and gf.name = 'displayName' and gf.id = ga.field_id) as owner_group_displayname, (select ga.value from grouper_attributes ga , grouper_fields gf where ga.group_id = gc.left_factor and gf.name = 'displayName' and gf.id = ga.field_id) as left_factor_group_displayname, (select ga.value from grouper_attributes ga , grouper_fields gf where ga.group_id = gc.right_factor and gf.name = 'displayName' and gf.id = ga.field_id) as right_factor_group_displayname, gc.OWNER as owner_group_id, gc.LEFT_FACTOR as left_factor_group_id, gc.RIGHT_FACTOR as right_factor_group_id, gc.ID as composite_id, gc.CREATE_TIME, gc.CREATOR_ID, gc.HIBERNATE_VERSION_NUMBER from grouper_composites gc ;

 

COMMENT ON TABLE GROUPER_COMPOSITES_V IS 'Grouper_composites_v is a view of composite relationships with friendly names.  A composite is a joining of two groups with a group math operator of: union, intersection, or complement.';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.OWNER_GROUP_NAME IS 'OWNER_GROUP_NAME: Name of the group which is the result of the composite operation, e.g. school:stem1:allPeople';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.COMPOSITE_TYPE IS 'COMPOSITE_TYPE: union (all members), intersection (only members in both), or complement (in first, not in second)';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.LEFT_FACTOR_GROUP_NAME IS 'LEFT_FACTOR_GROUP_NAME: Name of group which is the first of two groups in the composite operation, e.g. school:stem1:part1';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.RIGHT_FACTOR_GROUP_NAME IS 'RIGHT_FACTOR_GROUP_NAME: Name of group which is the second of two groups in the composite operation, e.g. school:stem1:part2';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.OWNER_GROUP_DISPLAYNAME IS 'OWNER_GROUP_DISPLAYNAME: Display name of result group of composite operation, e.g. My school:The stem1:All people';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.LEFT_FACTOR_GROUP_DISPLAYNAME IS 'LEFT_FACTOR_GROUP_DISPLAYNAME: Display name of group which is the first of two groups in the composite operation, e.g. My school:The stem1:Part 1';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.RIGHT_FACTOR_GROUP_DISPLAYNAME IS 'RIGHT_FACTOR_GROUP_DISPLAYNAME: Display name of group which is the second of two groups in the composite operation, e.g. My school:The stem1:Part 1';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.OWNER_GROUP_ID IS 'OWNER_GROUP_ID: UUID of the result group';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.LEFT_FACTOR_GROUP_ID IS 'LEFT_FACTOR_GROUP_ID: UUID of the first group of the composite operation';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.RIGHT_FACTOR_GROUP_ID IS 'RIGHT_FACTOR_GROUP_ID: UUID of the second group of the composite operation';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.COMPOSITE_ID IS 'COMPOSITE_ID: UUID of the composite relationship among the three groups';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.CREATE_TIME IS 'CREATE_TIME: number of millis since 1970 that the composite was created';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.CREATOR_ID IS 'CREATOR_ID: member id of the subject that created the composite relationship';

 

COMMENT ON COLUMN GROUPER_COMPOSITES_V.HIBERNATE_VERSION_NUMBER IS 'HIBERNATE_VERSION_NUMBER: increments with each update, starts at 0';

 

CREATE OR REPLACE VIEW GROUPER_GROUPS_TYPES_V (GROUP_NAME, GROUP_DISPLAYNAME, GROUP_TYPE_NAME, GROUP_ID, GROUP_TYPE_UUID, GROUPER_GROUPS_TYPES_ID, HIBERNATE_VERSION_NUMBER) AS select  (select ga.value from grouper_attributes ga, grouper_fields gf  where ga.group_id = ggt.GROUP_UUID and gf.name = 'name' and ga.field_id = gf.id) as group_name, (select ga.value from grouper_attributes ga, grouper_fields gf  where ga.group_id = ggt.GROUP_UUID and gf.name = 'displayName' and ga.field_id = gf.id) as group_displayname, gt.NAME as group_type_name, ggt.GROUP_UUID as group_id, ggt.TYPE_UUID as group_type_uuid, ggt.ID as grouper_groups_types_id, ggt.HIBERNATE_VERSION_NUMBER from grouper_groups_types ggt, grouper_types gt where ggt.TYPE_UUID = gt.ID ;

 

COMMENT ON TABLE GROUPER_GROUPS_TYPES_V IS 'A group can have one or many types associated.  This is a view of those relationships with friendly names';

 

COMMENT ON COLUMN GROUPER_GROUPS_TYPES_V.GROUP_NAME IS 'GROUP_NAME: name of group which has the type, e.g. school:stem1:theGroup';

 

COMMENT ON COLUMN GROUPER_GROUPS_TYPES_V.GROUP_DISPLAYNAME IS 'GROUP_DISPLAYNAME: display name of the group which has the type, e.g. My school, the stem 1, The group';

 

COMMENT ON COLUMN GROUPER_GROUPS_TYPES_V.GROUP_TYPE_NAME IS 'GROUP_TYPE_NAME: friendly name of the type, e.g. grouperLoader';

 

COMMENT ON COLUMN GROUPER_GROUPS_TYPES_V.GROUP_ID IS 'GROUP_ID: uuid unique id of the group which has the type';

 

COMMENT ON COLUMN GROUPER_GROUPS_TYPES_V.GROUP_TYPE_UUID IS 'GROUP_TYPE_UUID: uuid unique id of the type related to the group';

 

COMMENT ON COLUMN GROUPER_GROUPS_TYPES_V.GROUPER_GROUPS_TYPES_ID IS 'GROUPER_GROUPS_TYPES_ID: uuid unique id of the relationship between the group and type';

 

COMMENT ON COLUMN GROUPER_GROUPS_TYPES_V.HIBERNATE_VERSION_NUMBER IS 'HIBERNATE_VERSION_NUMBER: increments by one with each update, starts at 0';

 

CREATE OR REPLACE VIEW GROUPER_GROUPS_V (EXTENSION, NAME, DISPLAY_EXTENSION, DISPLAY_NAME, DESCRIPTION, PARENT_STEM_NAME, GROUP_ID, PARENT_STEM_ID, MODIFIER_SOURCE, MODIFIER_SUBJECT_ID, CREATOR_SOURCE, CREATOR_SUBJECT_ID, IS_COMPOSITE_OWNER, IS_COMPOSITE_FACTOR, CREATOR_ID, CREATE_TIME, MODIFIER_ID, MODIFY_TIME, HIBERNATE_VERSION_NUMBER) AS select  (select ga.value from grouper_attributes ga, grouper_fields gf where ga.group_id = gg.id and gf.name = 'extension' and gf.id = ga.field_id) as extension, (select ga.value from grouper_attributes ga , grouper_fields gf where ga.group_id = gg.id and gf.name = 'name' and gf.id = ga.field_id) as name, (select ga.value from grouper_attributes ga , grouper_fields gf where ga.group_id = gg.id and gf.name = 'displayExtension' and gf.id = ga.field_id) as display_extension,     (select ga.value from grouper_attributes ga , grouper_fields gf where ga.group_id = gg.id and gf.name = 'displayName' and gf.id = ga.field_id) as display_name, (select ga.value from grouper_attributes ga, grouper_fields gf where ga.group_id = gg.id and gf.name = 'description' and gf.id = ga.field_id) as description, gs.NAME as parent_stem_name, gg.id as group_id, gs.ID as parent_stem_id, (select gm.SUBJECT_SOURCE from grouper_members gm where gm.ID = gg.MODIFIER_ID) as modifier_source, (select gm.SUBJECT_ID from grouper_members gm where gm.ID = gg.MODIFIER_ID) as modifier_subject_id, (select gm.SUBJECT_SOURCE from grouper_members gm where gm.ID = gg.CREATOR_ID) as creator_source, (select gm.SUBJECT_ID from grouper_members gm where gm.ID = gg.CREATOR_ID) as creator_subject_id, (select distinct 'T' from grouper_composites gc where gc.OWNER = gg.ID) as is_composite_owner, (select distinct 'T' from grouper_composites gc where gc.LEFT_FACTOR = gg.ID or gc.right_factor = gg.id) as is_composite_factor, gg.CREATOR_ID, gg.CREATE_TIME, gg.MODIFIER_ID, gg.MODIFY_TIME, gg.HIBERNATE_VERSION_NUMBER   from grouper_groups gg, grouper_stems gs where gg.PARENT_STEM = gs.ID ;

 

COMMENT ON TABLE GROUPER_GROUPS_V IS 'Contains one record for each group, with friendly names for some attributes and some more information';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.EXTENSION IS 'EXTENSION: part of group name not including path information, e.g. theGroup';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.NAME IS 'NAME: name of the group, e.g. school:stem1:theGroup';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.DISPLAY_EXTENSION IS 'DISPLAY_EXTENSION: name for display of the group, e.g. My school:The stem 1:The group';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.DISPLAY_NAME IS 'DISPLAY_NAME: name for display of the group without any path information, e.g. The group';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.DESCRIPTION IS 'DESCRIPTION: contains user entered information about the group e.g. why it exists';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.PARENT_STEM_NAME IS 'PARENT_STEM_NAME: name of the stem this group is in, e.g. school:stem1';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.GROUP_ID IS 'GROUP_ID: uuid unique id of the group';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.PARENT_STEM_ID IS 'PARENT_STEM_ID: uuid unique id of the stem this group is in';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.MODIFIER_SOURCE IS 'MODIFIER_SOURCE: source name of the subject who last modified this group, e.g. schoolPersonSource';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.MODIFIER_SUBJECT_ID IS 'MODIFIER_SUBJECT_ID: subject id of the subject who last modified this group, e.g. 12345';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.CREATOR_SOURCE IS 'CREATOR_SOURCE: source name of the subject who created this group, e.g. schoolPersonSource';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.CREATOR_SUBJECT_ID IS 'CREATOR_SUBJECT_ID: subject id of the subject who created this group, e.g. 12345';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.IS_COMPOSITE_OWNER IS 'IS_COMPOSITE_OWNER: T if this is a result of a composite operation (union, intersection, complement), or blank if not';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.IS_COMPOSITE_FACTOR IS 'IS_COMPOSITE_FACTOR: T if this is a member of a composite operation, e.g. one of the grouper being unioned, intersected, or complemeneted';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.CREATOR_ID IS 'CREATOR_ID: member id of the subject who created this group, foreign key to grouper_members';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.CREATE_TIME IS 'CREATE_TIME: number of millis since 1970 since this group was created';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.MODIFIER_ID IS 'MODIFIER_ID: member id of the subject who last modified this group, foreign key to grouper_members';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.MODIFY_TIME IS 'MODIFY_TIME: number of millis since 1970 since this group was last changed';

 

COMMENT ON COLUMN GROUPER_GROUPS_V.HIBERNATE_VERSION_NUMBER IS 'HIBERNATE_VERSION_NUMBER: increments by 1 for each update';

 

CREATE OR REPLACE VIEW GROUPER_MEMBERSHIPS_V (GROUP_NAME, GROUP_DISPLAYNAME, STEM_NAME, STEM_DISPLAYNAME, SUBJECT_ID, SUBJECT_SOURCE, LIST_TYPE, LIST_NAME, MEMBERSHIP_TYPE, COMPOSITE_PARENT_GROUP_NAME, DEPTH, CREATOR_SOURCE, CREATOR_SUBJECT_ID, MEMBERSHIP_ID, PARENT_MEMBERSHIP_ID, STEM_ID, GROUP_ID, GROUP_OR_STEM_ID, CREATE_TIME, CREATOR_ID, FIELD_ID) AS select (select ga.value from grouper_attributes ga, grouper_fields gf  where ga.group_id = gms.owner_id and gf.name = 'name' and ga.field_id = gf.id) as group_name, (select ga.value from grouper_attributes ga, grouper_fields gf  where ga.group_id = gms.owner_id and gf.name = 'displayName' and ga.field_id = gf.id) as group_displayname, (select gs.NAME from grouper_stems gs where gs.ID = gms.owner_id) as stem_name, (select gs.display_NAME from grouper_stems gs where gs.ID = gms.owner_id) as stem_displayname, gm.SUBJECT_ID, gm.subject_source, gf.TYPE as list_type, gf.NAME as list_name, gms.MSHIP_TYPE as membership_type, (select ga.value from grouper_attributes ga, grouper_composites gc, grouper_fields gf where gc.id = gms.VIA_ID and ga.group_id = gc.OWNER and gf.name = 'name' and ga.field_id = gf.id) as composite_parent_group_name, depth,  (select gm.SUBJECT_SOURCE from grouper_members gm where gm.ID = gms.creator_ID) as creator_source, (select gm.SUBJECT_ID from grouper_members gm where gm.ID = gms.creator_ID) as creator_subject_id, gms.id as membership_id,  gms.PARENT_MEMBERSHIP as parent_membership_id, (select gs.id from grouper_stems gs where gs.ID = gms.owner_id) as stem_id, (select gg.id from grouper_groups gg where gg.id = gms.owner_id) as group_id, gms.OWNER_ID as group_or_stem_id, gms.CREATE_TIME, gms.CREATOR_ID, gms.FIELD_ID  from grouper_memberships gms, grouper_members gm, grouper_fields gf  where gms.MEMBER_ID = gm.ID and gms.field_id = gf.id ;

 

COMMENT ON TABLE GROUPER_MEMBERSHIPS_V IS 'Grouper_memberships_v holds one record for each membership or privilege in the system for members to groups or stems (for privileges).  This is denormalized so there are records for the actual immediate relationships, and the cascaded effective relationships.  This has friendly names.';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.GROUP_NAME IS 'GROUP_NAME: name of the group if this is a group membership, e.g. school:stem1:theGroup';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.GROUP_DISPLAYNAME IS 'GROUP_DISPLAYNAME: display name of the group if this is a group membership, e.g. My school:The stem1:The group';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.STEM_NAME IS 'STEM_NAME: name of the stem if this is a stem privilege, e.g. school:stem1';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.STEM_DISPLAYNAME IS 'STEM_DISPLAYNAME: display name of the stems if this is a stem privilege, e.g. My school:The stem1';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.SUBJECT_ID IS 'SUBJECT_ID: e.g. a school id of a person in the membership e.g. 12345';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.SUBJECT_SOURCE IS 'SUBJECT_SOURCE: source where the subject in the membership is from e.g. mySchoolPeople';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.LIST_TYPE IS 'LIST_TYPE: list: members of a group, access: privilege of a group, naming: privilege of a stem';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.LIST_NAME IS 'LIST_NAME: subset of list type.  which list if a list membership.  which privilege if a privilege.  e.g. members';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.MEMBERSHIP_TYPE IS 'MEMBERSHIP_TYPE: either immediate (direct membership or privilege), of effective (membership due to a composite or a group being a member of another group)';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.COMPOSITE_PARENT_GROUP_NAME IS 'COMPOSITE_PARENT_GROUP_NAME: name of group if this membership relates to a composite relationship, e.g. school:stem:allStudents';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.DEPTH IS 'DEPTH: 0 for composite, if not then it is the 0 indexed count of number of group hops between member and group';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.CREATOR_SOURCE IS 'CREATOR_SOURCE: subject source where the creator of the group is from';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.CREATOR_SUBJECT_ID IS 'CREATOR_SUBJECT_ID: subject id of the creator of the group, e.g. 12345';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.MEMBERSHIP_ID IS 'MEMBERSHIP_ID: uuid unique id of this membership';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.PARENT_MEMBERSHIP_ID IS 'PARENT_MEMBERSHIP_ID: if this is an effective membership, then this is the membership_uuid of the cause of this membership';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.STEM_ID IS 'STEM_ID: if this is a stem privilege, this is the stem uuid unique id';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.GROUP_ID IS 'GROUP_ID: if this is a group list or privilege, this is the group uuid unique id';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.GROUP_OR_STEM_ID IS 'GROUP_OR_STEM_ID: stem_id if stem, group_id if group';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.CREATE_TIME IS 'CREATE_TIME: number of millis since 1970 since this membership was created';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.CREATOR_ID IS 'CREATOR_ID: member_id of the creator, foreign key into grouper_members';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS_V.FIELD_ID IS 'FIELD_ID: uuid unique id of the field.  foreign key to grouper_fields.  This represents the list_type and list_name';

 

CREATE OR REPLACE VIEW GROUPER_STEMS_V (EXTENSION, NAME, DISPLAY_EXTENSION, DISPLAY_NAME, DESCRIPTION, PARENT_STEM_NAME, PARENT_STEM_DISPLAYNAME, CREATOR_SOURCE, CREATOR_SUBJECT_ID, MODIFIER_SOURCE, MODIFIER_SUBJECT_ID, CREATE_TIME, CREATOR_ID, STEM_ID, MODIFIER_ID, MODIFY_TIME, PARENT_STEM, HIBERNATE_VERSION_NUMBER) AS select gs.extension, gs.NAME, gs.DISPLAY_EXTENSION, gs.DISPLAY_NAME, gs.DESCRIPTION, (select gs_parent.NAME from grouper_stems gs_parent where gs_parent.id = gs.PARENT_STEM) as parent_stem_name, (select gs_parent.DISPLAY_NAME from grouper_stems gs_parent where gs_parent.id = gs.PARENT_STEM) as parent_stem_displayname, (select gm.SUBJECT_SOURCE from grouper_members gm where gm.ID = gs.creator_ID) as creator_source, (select gm.SUBJECT_ID from grouper_members gm where gm.ID = gs.creator_ID) as creator_subject_id, (select gm.SUBJECT_SOURCE from grouper_members gm where gm.ID = gs.MODIFIER_ID) as modifier_source, (select gm.SUBJECT_ID from grouper_members gm where gm.ID = gs.MODIFIER_ID) as modifier_subject_id, gs.CREATE_TIME, gs.CREATOR_ID,  gs.ID as stem_id, gs.MODIFIER_ID, gs.MODIFY_TIME, gs.PARENT_STEM, gs.HIBERNATE_VERSION_NUMBER from grouper_stems gs ;

 

COMMENT ON TABLE GROUPER_STEMS_V IS 'GROUPER_STEMS_V: holds one record for each stem (folder) in grouper, with friendly names';

 

COMMENT ON COLUMN GROUPER_STEMS_V.EXTENSION IS 'EXTENSION: name of the stem without the parent stem names, e.g. stem1';

 

COMMENT ON COLUMN GROUPER_STEMS_V.NAME IS 'NAME: name of the stem including parent stem names, e.g. school:stem1';

 

COMMENT ON COLUMN GROUPER_STEMS_V.DISPLAY_EXTENSION IS 'DISPLAY_EXTENSION: display name of the stem without parent stem names, e.g. The stem 1';

 

COMMENT ON COLUMN GROUPER_STEMS_V.DISPLAY_NAME IS 'DISPLAY_NAME: display name of the stem including parent stem names, e.g. My school: The stem 1';

 

COMMENT ON COLUMN GROUPER_STEMS_V.DESCRIPTION IS 'DESCRIPTION: description entered in about the stem, for example including why the stem exists and who has access';

 

COMMENT ON COLUMN GROUPER_STEMS_V.PARENT_STEM_NAME IS 'PARENT_STEM_NAME: name of the stem (folder) that this stem is in.  e.g. school';

 

COMMENT ON COLUMN GROUPER_STEMS_V.PARENT_STEM_DISPLAYNAME IS 'PARENT_STEM_DISPLAYNAME: display name of the stem (folder) that this stem is in.  e.g. My school';

 

COMMENT ON COLUMN GROUPER_STEMS_V.CREATOR_SOURCE IS 'CREATOR_SOURCE: subject source where the subject that created this stem is from, e.g. mySchoolPeople';

 

COMMENT ON COLUMN GROUPER_STEMS_V.CREATOR_SUBJECT_ID IS 'CREATOR_SUBJECT_ID: e.g. the school id of the subject that created this stem, e.g. 12345';

 

COMMENT ON COLUMN GROUPER_STEMS_V.MODIFIER_SOURCE IS 'MODIFIER_SOURCE: subject source where the subject that last modified this stem is from, e.g. mySchoolPeople';

 

COMMENT ON COLUMN GROUPER_STEMS_V.MODIFIER_SUBJECT_ID IS 'MODIFIER_SUBJECT_ID: e.g. the school id of the subject who last modified this stem, e.g. 12345';

 

COMMENT ON COLUMN GROUPER_STEMS_V.CREATE_TIME IS 'CREATE_TIME: number of millis since 1970 that this stem was created';

 

COMMENT ON COLUMN GROUPER_STEMS_V.CREATOR_ID IS 'CREATOR_ID: member id of the subject who created this stem, foreign key to grouper_members';

 

COMMENT ON COLUMN GROUPER_STEMS_V.STEM_ID IS 'STEM_ID: uuid unique id of this stem';

 

COMMENT ON COLUMN GROUPER_STEMS_V.MODIFIER_ID IS 'MODIFIER_ID: member id of the subject who last modified this stem, foreign key to grouper_members';

 

COMMENT ON COLUMN GROUPER_STEMS_V.MODIFY_TIME IS 'MODIFY_TIME: number of millis since 1970 since this stem was last modified';

 

COMMENT ON COLUMN GROUPER_STEMS_V.PARENT_STEM IS 'PARENT_STEM: stem_id uuid unique id of the stem (folder) that this stem is in';

 

COMMENT ON COLUMN GROUPER_STEMS_V.HIBERNATE_VERSION_NUMBER IS 'HIBERNATE_VERSION_NUMBER: increments by one for each update from hibernate';

 

CREATE OR REPLACE VIEW GROUPER_RPT_ATTRIBUTES_V (ATTRIBUTE_NAME, GROUP_COUNT, GROUP_TYPE_NAME, FIELD_ID, GROUP_TYPE_ID) AS select gf.NAME as attribute_name,  (select count(*) from grouper_attributes ga where ga.FIELD_ID = gf.id) as group_count,   gt.NAME as group_type_name, gf.ID as field_id, gt.ID as group_type_id from grouper_fields gf, grouper_types gt where gf.TYPE = 'attribute' and gf.GROUPTYPE_UUID = gt.ID ;

 

COMMENT ON TABLE GROUPER_RPT_ATTRIBUTES_V IS 'GROUPER_RPT_ATTRIBUTES_V: report on attributes, how many groups use each attribute';

 

COMMENT ON COLUMN GROUPER_RPT_ATTRIBUTES_V.ATTRIBUTE_NAME IS 'ATTRIBUTE_NAME: friendly name of the attribute which is actually from grouper_fields';

 

COMMENT ON COLUMN GROUPER_RPT_ATTRIBUTES_V.GROUP_COUNT IS 'GROUP_COUNT: number of groups which define this attribute';

 

COMMENT ON COLUMN GROUPER_RPT_ATTRIBUTES_V.GROUP_TYPE_NAME IS 'GROUP_TYPE_NAME: group type which owns this attribute';

 

COMMENT ON COLUMN GROUPER_RPT_ATTRIBUTES_V.FIELD_ID IS 'FIELD_ID: uuid unique id of this field (attribute), foreign key from grouper_attributes to grouper_fields';

 

COMMENT ON COLUMN GROUPER_RPT_ATTRIBUTES_V.GROUP_TYPE_ID IS 'GROUP_TYPE_ID: uuid unique id of the group type.  foreign key from grouper_fields to grouper_types';

 

CREATE OR REPLACE VIEW GROUPER_RPT_COMPOSITES_V (COMPOSITE_TYPE, THE_COUNT) AS select gc.TYPE as composite_type, count(*) as the_count from grouper_composites gc group by gc.type ;

 

COMMENT ON TABLE GROUPER_RPT_COMPOSITES_V IS 'GROUPER_RPT_COMPOSITES_V: report on the three composite types: union, intersection, complement and how many of each exist';

 

COMMENT ON COLUMN GROUPER_RPT_COMPOSITES_V.COMPOSITE_TYPE IS 'COMPOSITE_TYPE: either union: all members from both factors, intersection: only members in both factors, complement: members in first but not second factor';

 

COMMENT ON COLUMN GROUPER_RPT_COMPOSITES_V.THE_COUNT IS 'THE_COUNT: nubmer of composites of this type in the system';

 

CREATE OR REPLACE VIEW GROUPER_RPT_GROUP_FIELD_V (GROUP_NAME, GROUP_DISPLAYNAME, FIELD_TYPE, FIELD_NAME, MEMBER_COUNT) AS select ga.value as group_name, ga2.value as group_displayName, gf.type as field_type, gf.name as field_name, count(distinct gms.member_id) as member_count from grouper_memberships gms, grouper_fields gf, grouper_attributes ga, grouper_fields gaf,  grouper_attributes ga2, grouper_fields gaf2 where gms.FIELD_ID = gf.ID and ga.FIELD_ID = gaf.id and gaf.name = 'name' and ga.group_id = gms.OWNER_ID and ga2.group_id = gms.owner_id and ga2.field_id = gaf2.id and gaf2.name = 'displayName' group by ga.value, ga2.value, gf.type, gf.name ;

 

COMMENT ON TABLE GROUPER_RPT_GROUP_FIELD_V IS 'GROUPER_RPT_GROUP_FIELD_V: report on how many unique members are in each group based on field (or list) name and type';

 

COMMENT ON COLUMN GROUPER_RPT_GROUP_FIELD_V.GROUP_NAME IS 'GROUP_NAME: name of the group where the list and members are, e.g. school:stem1:myGroup';

 

COMMENT ON COLUMN GROUPER_RPT_GROUP_FIELD_V.GROUP_DISPLAYNAME IS 'GROUP_DISPLAYNAME: display name of the group where the list and members are, e.g. My school:The stem1:My group';

 

COMMENT ON COLUMN GROUPER_RPT_GROUP_FIELD_V.FIELD_TYPE IS 'FIELD_TYPE: membership field type, e.g. list or access';

 

COMMENT ON COLUMN GROUPER_RPT_GROUP_FIELD_V.FIELD_NAME IS 'FIELD_NAME: membership field name, e.g. members, admins, readers';

 

COMMENT ON COLUMN GROUPER_RPT_GROUP_FIELD_V.MEMBER_COUNT IS 'MEMBER_COUNT: number of unique members in the group/field';

 

CREATE OR REPLACE VIEW GROUPER_RPT_GROUPS_V (GROUP_NAME, GROUP_DISPLAYNAME, IMMEDIATE_MEMBERSHIP_COUNT, MEMBERSHIP_COUNT, ATTRIBUTE_COUNT, GROUPS_TYPES_COUNT, ISA_COMPOSITE_FACTOR_COUNT, ISA_MEMBER_COUNT, GROUP_ID) AS select  (select ga.value from grouper_attributes ga , grouper_fields gf where ga.group_id = gg.id and gf.name = 'name' and gf.id = ga.field_id) as group_name,     (select ga.value from grouper_attributes ga , grouper_fields gf where ga.group_id = gg.id and gf.name = 'displayName' and gf.id = ga.field_id) as group_displayname, (select count(distinct gms.MEMBER_ID) from grouper_memberships gms where gms.OWNER_ID = gg.id and gms.MSHIP_TYPE = 'immediate') as immediate_membership_count, (select count(distinct gms.MEMBER_ID) from grouper_memberships gms where gms.OWNER_ID = gg.id) as membership_count, (select count(*) from grouper_attributes ga where ga.GROUP_ID = gg.id) as attribute_count, (select count(*) from grouper_groups_types ggt where ggt.GROUP_UUID = gg.id) as groups_types_count, (select count(*) from grouper_composites gc where gc.LEFT_FACTOR = gg.id or gc.RIGHT_FACTOR = gg.id) as isa_composite_factor_count, (select count(distinct gms.OWNER_ID) from grouper_memberships gms, grouper_members gm where gm.SUBJECT_ID = gg.ID and gms.MEMBER_ID = gm.ID ) as isa_member_count, gg.ID as group_id from grouper_groups gg ;

 

COMMENT ON TABLE GROUPER_RPT_GROUPS_V IS 'GROUPER_RPT_GROUPS_V: report with a line for each group and some counts of immediate and effective members etc';

 

COMMENT ON COLUMN GROUPER_RPT_GROUPS_V.GROUP_NAME IS 'GROUP_NAME: name of group which has the stats, e.g. school:stem1:theGroup';

 

COMMENT ON COLUMN GROUPER_RPT_GROUPS_V.GROUP_DISPLAYNAME IS 'GROUP_DISPLAYNAME: display name of the group which has the stats, e.g. My school:The stem1:The group';

 

COMMENT ON COLUMN GROUPER_RPT_GROUPS_V.IMMEDIATE_MEMBERSHIP_COUNT IS 'IMMEDIATE_MEMBERSHIP_COUNT: number of unique immediate members, directly assigned to this group';

 

COMMENT ON COLUMN GROUPER_RPT_GROUPS_V.MEMBERSHIP_COUNT IS 'MEMBERSHIP_COUNT: total number of unique members, immediate or effective';

 

COMMENT ON COLUMN GROUPER_RPT_GROUPS_V.ATTRIBUTE_COUNT IS 'ATTRIBUTE_COUNT: number of attributes defined for this group';

 

COMMENT ON COLUMN GROUPER_RPT_GROUPS_V.GROUPS_TYPES_COUNT IS 'GROUPS_TYPES_COUNT: number of group types associated with this group';

 

COMMENT ON COLUMN GROUPER_RPT_GROUPS_V.ISA_COMPOSITE_FACTOR_COUNT IS 'ISA_COMPOSITE_FACTOR_COUNT: number of composites this group is a factor of';

 

COMMENT ON COLUMN GROUPER_RPT_GROUPS_V.ISA_MEMBER_COUNT IS 'ISA_MEMBER_COUNT: number of groups this group is an immediate or effective member of';

 

COMMENT ON COLUMN GROUPER_RPT_GROUPS_V.GROUP_ID IS 'GROUP_ID: uuid unique id of this group';

 

CREATE OR REPLACE VIEW GROUPER_RPT_MEMBERS_V (SUBJECT_ID, SUBJECT_SOURCE, MEMBERSHIP_COUNT, MEMBER_ID) AS select gm.SUBJECT_ID, gm.SUBJECT_SOURCE, (select count(distinct gms.owner_id) from grouper_memberships gms where gms.MEMBER_ID = gm.ID) as membership_count, gm.ID as member_id from grouper_members gm ;

 

COMMENT ON TABLE GROUPER_RPT_MEMBERS_V IS 'GROUPER_RPT_MEMBERS_V: report for each member in grouper_members and some stats like how many groups they are in';

 

COMMENT ON COLUMN GROUPER_RPT_MEMBERS_V.SUBJECT_ID IS 'SUBJECT_ID: e.g. the school person id of the person e.g. 12345';

 

COMMENT ON COLUMN GROUPER_RPT_MEMBERS_V.SUBJECT_SOURCE IS 'SUBJECT_SOURCE: subject source where the subject is from, e.g. schoolAllPeople';

 

COMMENT ON COLUMN GROUPER_RPT_MEMBERS_V.MEMBERSHIP_COUNT IS 'MEMBERSHIP_COUNT: number of distinct groups or stems this member has a membership with';

 

COMMENT ON COLUMN GROUPER_RPT_MEMBERS_V.MEMBER_ID IS 'MEMBER_ID: uuid unique id of the member in grouper_members';

 

CREATE OR REPLACE VIEW GROUPER_RPT_STEMS_V (STEM_NAME, STEM_DISPLAYNAME, GROUP_IMMEDIATE_COUNT, STEM_IMMEDIATE_COUNT, GROUP_COUNT, STEM_COUNT, THIS_STEM_MEMBERSHIP_COUNT, CHILD_GROUP_MEMBERSHIP_COUNT, GROUP_MEMBERSHIP_COUNT, STEM_ID) AS select gs.NAME as stem_name, gs.DISPLAY_NAME as stem_displayname, (select count(*) from grouper_groups gg where gg.PARENT_STEM = gs.ID) as group_immediate_count, (select count(*) from grouper_stems gs2 where gs.id = gs2.PARENT_STEM ) as stem_immediate_count, (select count(*) from grouper_attributes ga, grouper_fields gf where ga.FIELD_ID = gf.ID and gf.NAME = 'name' and ga.value like gs.NAME || '%') as group_count, (select count(*) from grouper_stems gs2 where gs2.name like gs.NAME || '%') as stem_count, (select count(distinct gm.member_id) from grouper_memberships gm where gm.OWNER_ID = gs.id) as this_stem_membership_count,  (select count(distinct gm.member_id) from grouper_memberships gm, grouper_groups gg where gg.parent_stem = gs.id and gm.OWNER_ID = gg.id) as child_group_membership_count,  (select count(distinct gm.member_id) from grouper_memberships gm, grouper_attributes ga, grouper_fields gf where gm.owner_id = ga.group_id and ga.FIELD_ID = gf.ID and gf.NAME = 'name' and ga.value like gs.NAME || '%') as group_membership_count, gs.ID as stem_id from grouper_stems gs ;

 

COMMENT ON TABLE GROUPER_RPT_STEMS_V IS 'GROUPER_RPT_STEMS_V: report with a row for each stem and stats on many groups or members are inside';

 

COMMENT ON COLUMN GROUPER_RPT_STEMS_V.STEM_NAME IS 'STEM_NAME: name of the stem in report, e.g. school:stem1';

 

COMMENT ON COLUMN GROUPER_RPT_STEMS_V.STEM_DISPLAYNAME IS 'STEM_DISPLAYNAME: display name of the stem in report, e.g. My school:The stem 1';

 

COMMENT ON COLUMN GROUPER_RPT_STEMS_V.GROUP_IMMEDIATE_COUNT IS 'GROUP_IMMEDIATE_COUNT: number of groups directly inside this stem';

 

COMMENT ON COLUMN GROUPER_RPT_STEMS_V.STEM_IMMEDIATE_COUNT IS 'STEM_IMMEDIATE_COUNT: number of stems directly inside this stem';

 

COMMENT ON COLUMN GROUPER_RPT_STEMS_V.GROUP_COUNT IS 'GROUP_COUNT: number of groups inside this stem, or in a stem inside this stem etc';

 

COMMENT ON COLUMN GROUPER_RPT_STEMS_V.STEM_COUNT IS 'STEM_COUNT: number of stems inside this stem or in a stem inside this stem etc';

 

COMMENT ON COLUMN GROUPER_RPT_STEMS_V.THIS_STEM_MEMBERSHIP_COUNT IS 'THIS_STEM_MEMBERSHIP_COUNT: number of access memberships related to this stem (e.g. how many people can create groups/stems inside)';

 

COMMENT ON COLUMN GROUPER_RPT_STEMS_V.CHILD_GROUP_MEMBERSHIP_COUNT IS 'CHILD_GROUP_MEMBERSHIP_COUNT: number of memberships in groups immediately in this stem';

 

COMMENT ON COLUMN GROUPER_RPT_STEMS_V.GROUP_MEMBERSHIP_COUNT IS 'GROUP_MEMBERSHIP_COUNT: number of memberships in groups in this stem or in stems in this stem etc';

 

COMMENT ON COLUMN GROUPER_RPT_STEMS_V.STEM_ID IS 'STEM_ID: uuid unique id of this stem';

 

CREATE OR REPLACE VIEW GROUPER_RPT_TYPES_V (GROUP_TYPE_NAME, GROUP_COUNT, GROUP_TYPE_ID) AS select gt.NAME as group_type_name, (select count(*) from grouper_groups_types ggt where ggt.TYPE_UUID = gt.ID) as group_count, gt.id as group_type_id from grouper_types gt ;

 

COMMENT ON TABLE GROUPER_RPT_TYPES_V IS 'GROUPER_RPT_TYPES_V: report on group types and how many groups have that type';

 

COMMENT ON COLUMN GROUPER_RPT_TYPES_V.GROUP_TYPE_NAME IS 'GROUP_TYPE_NAME: friendly name of this group type';

 

COMMENT ON COLUMN GROUPER_RPT_TYPES_V.GROUP_COUNT IS 'GROUP_COUNT: number of groups that have this group type';

 

COMMENT ON COLUMN GROUPER_RPT_TYPES_V.GROUP_TYPE_ID IS 'GROUP_TYPE_ID: uuid unique id of this group type';

 

COMMENT ON TABLE GROUPER_ATTRIBUTES IS 'attributes for groups, including name, extension, etc';

 

COMMENT ON COLUMN GROUPER_ATTRIBUTES.ID IS 'db id of this attribute record';

 

COMMENT ON COLUMN GROUPER_ATTRIBUTES.GROUP_ID IS 'group_uuid foreign key';

 

COMMENT ON COLUMN GROUPER_ATTRIBUTES.FIELD_ID IS 'foreign key to field by id';

 

COMMENT ON COLUMN GROUPER_ATTRIBUTES.VALUE IS 'value this attribute record';

 

COMMENT ON COLUMN GROUPER_ATTRIBUTES.HIBERNATE_VERSION_NUMBER IS 'hibernate uses this to version rows';

 

COMMENT ON TABLE GROUPER_COMPOSITES IS 'records the composite group, and its factors';

 

COMMENT ON COLUMN GROUPER_COMPOSITES.ID IS 'db id of this composite record';

 

COMMENT ON COLUMN GROUPER_COMPOSITES.OWNER IS 'group uuid of the composite group';

 

COMMENT ON COLUMN GROUPER_COMPOSITES.LEFT_FACTOR IS 'left factor of the composite group';

 

COMMENT ON COLUMN GROUPER_COMPOSITES.RIGHT_FACTOR IS 'right factor of the composite group';

 

COMMENT ON COLUMN GROUPER_COMPOSITES.TYPE IS 'e.g. union, complement, intersection';

 

COMMENT ON COLUMN GROUPER_COMPOSITES.CREATOR_ID IS 'member uuid of who created this';

 

COMMENT ON COLUMN GROUPER_COMPOSITES.CREATE_TIME IS 'number of millis since 1970 until when created';

 

COMMENT ON COLUMN GROUPER_COMPOSITES.HIBERNATE_VERSION_NUMBER IS 'hibernate uses this to version rows';

 

COMMENT ON TABLE GROUPER_DDL IS 'holds a record for each database object name, and db version, and java version';

 

COMMENT ON COLUMN GROUPER_DDL.ID IS 'uuid of this ddl record';

 

COMMENT ON COLUMN GROUPER_DDL.OBJECT_NAME IS 'Corresponds to an enum in grouper.ddl package (with Ddl on end), represents one module, so grouper itself is one object';

 

COMMENT ON COLUMN GROUPER_DDL.DB_VERSION IS 'Version of this object as far as DB knows about';

 

COMMENT ON COLUMN GROUPER_DDL.LAST_UPDATED IS 'last update timestamp, string so it can easily be used from update statement';

 

COMMENT ON COLUMN GROUPER_DDL.HISTORY IS 'history of this object name, with most recent first (truncated after 4k)';

 

COMMENT ON TABLE GROUPER_FIELDS IS 'describes fields related to types';

 

COMMENT ON COLUMN GROUPER_FIELDS.ID IS 'db id of this field record';

 

COMMENT ON COLUMN GROUPER_FIELDS.GROUPTYPE_UUID IS 'foreign key to group type';

 

COMMENT ON COLUMN GROUPER_FIELDS.IS_NULLABLE IS 'if this is nullable';

 

COMMENT ON COLUMN GROUPER_FIELDS.NAME IS 'name of the field';

 

COMMENT ON COLUMN GROUPER_FIELDS.READ_PRIVILEGE IS 'which privilege is required to read this field';

 

COMMENT ON COLUMN GROUPER_FIELDS.TYPE IS 'type of field (e.g. attribute, list, access, naming)';

 

COMMENT ON COLUMN GROUPER_FIELDS.WRITE_PRIVILEGE IS 'which privilege is required to write this attribute';

 

COMMENT ON COLUMN GROUPER_FIELDS.HIBERNATE_VERSION_NUMBER IS 'hibernate uses this to version rows';

 

COMMENT ON TABLE GROUPER_GROUPS IS 'holds the groups in the grouper system';

 

COMMENT ON COLUMN GROUPER_GROUPS.ID IS 'db id of this group record';

 

COMMENT ON COLUMN GROUPER_GROUPS.PARENT_STEM IS 'uuid of the stem that this group refers to';

 

COMMENT ON COLUMN GROUPER_GROUPS.CREATOR_ID IS 'member uuid of the creator of this group';

 

COMMENT ON COLUMN GROUPER_GROUPS.CREATE_TIME IS 'number of millis since 1970 that this group was created';

 

COMMENT ON COLUMN GROUPER_GROUPS.MODIFIER_ID IS 'member uuid of the last modifier of this group';

 

COMMENT ON COLUMN GROUPER_GROUPS.MODIFY_TIME IS 'number of millis since 1970 that this group was modified';

 

COMMENT ON COLUMN GROUPER_GROUPS.CREATE_SOURCE IS 'subject source of who created this group';

 

COMMENT ON COLUMN GROUPER_GROUPS.MODIFY_SOURCE IS 'subject source of who modified this group';

 

COMMENT ON COLUMN GROUPER_GROUPS.HIBERNATE_VERSION_NUMBER IS 'hibernate uses this to version rows';

 

COMMENT ON TABLE GROUPER_GROUPS_TYPES IS 'holds the association between group and type';

 

COMMENT ON COLUMN GROUPER_GROUPS_TYPES.ID IS 'id of this group/type record';

 

COMMENT ON COLUMN GROUPER_GROUPS_TYPES.GROUP_UUID IS 'group uuid foreign key';

 

COMMENT ON COLUMN GROUPER_GROUPS_TYPES.TYPE_UUID IS 'type uuid foreign key';

 

COMMENT ON COLUMN GROUPER_GROUPS_TYPES.HIBERNATE_VERSION_NUMBER IS 'hibernate uses this to version rows';

 

COMMENT ON TABLE GROUPER_LOADER_LOG IS 'log table with a row for each grouper loader job run';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.ID IS 'uuid of this log record';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.JOB_NAME IS 'Could be group name (friendly) or just config name';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.STATUS IS 'STARTED, SUCCESS, ERROR, WARNING, CONFIG_ERROR';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.STARTED_TIME IS 'When the job was started';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.ENDED_TIME IS 'When the job ended (might be blank if daemon died)';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.MILLIS IS 'Milliseconds this process took';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.MILLIS_GET_DATA IS 'Milliseconds this process took to get the data from the source';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.MILLIS_LOAD_DATA IS 'Milliseconds this process took to load the data to grouper';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.JOB_TYPE IS 'GrouperLoaderJobType enum value';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.JOB_SCHEDULE_TYPE IS 'GrouperLoaderJobscheduleType enum value';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.JOB_DESCRIPTION IS 'More information about the job';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.JOB_MESSAGE IS 'Could be a status or error message or stack';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.HOST IS 'Host that this job ran on';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.GROUP_UUID IS 'If this job involves one group, this is uuid';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.JOB_SCHEDULE_QUARTZ_CRON IS 'Quartz cron string for this col';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.JOB_SCHEDULE_INTERVAL_SECONDS IS 'How many seconds this is supposed to wait between runs';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.LAST_UPDATED IS 'When this record was last updated';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.UNRESOLVABLE_SUBJECT_COUNT IS 'The number of records which were not subject resolvable';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.INSERT_COUNT IS 'The number of records inserted';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.UPDATE_COUNT IS 'The number of records updated';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.DELETE_COUNT IS 'The number of records deleted';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.TOTAL_COUNT IS 'The total number of records (e.g. total number of members)';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.PARENT_JOB_NAME IS 'If this job is a subjob of another job, then put the parent job name here';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.PARENT_JOB_ID IS 'If this job is a subjob of another job, then put the parent job id here';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.AND_GROUP_NAMES IS 'If this group query is anded with another group or groups, they are listed here comma separated';

 

COMMENT ON COLUMN GROUPER_LOADER_LOG.JOB_SCHEDULE_PRIORITY IS 'Priority of this job (5 is unprioritized, higher the better)';

 

COMMENT ON TABLE GROUPER_MEMBERS IS 'keeps track of subjects used in grouper.  Records are never deleted from this table';

 

COMMENT ON COLUMN GROUPER_MEMBERS.ID IS 'db id of this row';

 

COMMENT ON COLUMN GROUPER_MEMBERS.SUBJECT_ID IS 'subject id is the id from the subject source';

 

COMMENT ON COLUMN GROUPER_MEMBERS.SUBJECT_SOURCE IS 'id of the source from sources.xml';

 

COMMENT ON COLUMN GROUPER_MEMBERS.SUBJECT_TYPE IS 'type of subject, e.g. person';

 

COMMENT ON COLUMN GROUPER_MEMBERS.HIBERNATE_VERSION_NUMBER IS 'hibernate uses this to version rows';

 

COMMENT ON TABLE GROUPER_MEMBERSHIPS IS 'keeps track of memberships and permissions';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS.ID IS 'db id of this row';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS.OWNER_ID IS 'group of the membership';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS.MEMBER_ID IS 'member of the memership';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS.FIELD_ID IS 'foreign key to field by id';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS.MSHIP_TYPE IS 'type of membership, one of the three: immediate, effective, composite';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS.VIA_ID IS 'if effective, this is the group above the member in the chain, for composite, this is the composite uuid';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS.DEPTH IS 'for effective membership, the number of hops (excluding composites) along the membership graph';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS.PARENT_MEMBERSHIP IS 'for effective membership, uuid of membership record from the group in question to the parent group of the member';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS.CREATOR_ID IS 'member uuid of the creator of this record';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS.CREATE_TIME IS 'number of millis since 1970 that this record was created';

 

COMMENT ON COLUMN GROUPER_MEMBERSHIPS.HIBERNATE_VERSION_NUMBER IS 'hibernate uses this to version rows';

 

COMMENT ON TABLE GROUPER_STEMS IS 'entries for stems and their attributes';

 

COMMENT ON COLUMN GROUPER_STEMS.ID IS 'db id of this row';

 

COMMENT ON COLUMN GROUPER_STEMS.PARENT_STEM IS 'stem uuid of parent stem or empty if under root';

 

COMMENT ON COLUMN GROUPER_STEMS.NAME IS 'full name (id) path of stem';

 

COMMENT ON COLUMN GROUPER_STEMS.DISPLAY_NAME IS 'full dislpay name path of stem';

 

COMMENT ON COLUMN GROUPER_STEMS.CREATOR_ID IS 'member_id of who created this stem';

 

COMMENT ON COLUMN GROUPER_STEMS.CREATE_TIME IS 'number of millis since 1970 since this was created';

 

COMMENT ON COLUMN GROUPER_STEMS.MODIFIER_ID IS 'member_id of modifier who last edited';

 

COMMENT ON COLUMN GROUPER_STEMS.MODIFY_TIME IS 'number of millis since 1970 since this was edited';

 

COMMENT ON COLUMN GROUPER_STEMS.DISPLAY_EXTENSION IS 'display extension (not full path) of stem';

 

COMMENT ON COLUMN GROUPER_STEMS.EXTENSION IS 'extension (id) (not full path) of this stem';

 

COMMENT ON COLUMN GROUPER_STEMS.DESCRIPTION IS 'description of stem';

 

COMMENT ON COLUMN GROUPER_STEMS.CREATE_SOURCE IS 'subject source of who created this stem';

 

COMMENT ON COLUMN GROUPER_STEMS.MODIFY_SOURCE IS 'subject source of who modified this stem';

 

COMMENT ON COLUMN GROUPER_STEMS.HIBERNATE_VERSION_NUMBER IS 'hibernate uses this to version rows';

 

COMMENT ON TABLE GROUPER_TYPES IS 'the various types which can be assigned to groups';

 

COMMENT ON COLUMN GROUPER_TYPES.ID IS 'db id of this row';

 

COMMENT ON COLUMN GROUPER_TYPES.NAME IS 'name of this type';

 

COMMENT ON COLUMN GROUPER_TYPES.CREATOR_UUID IS 'member_id of the creator';

 

COMMENT ON COLUMN GROUPER_TYPES.CREATE_TIME IS 'number of millis since 1970 since this was created';

 

COMMENT ON COLUMN GROUPER_TYPES.IS_ASSIGNABLE IS 'if this type is assignable (not internal)';

 

COMMENT ON COLUMN GROUPER_TYPES.IS_INTERNAL IS 'if this type if internal (not assignable)';

 

COMMENT ON COLUMN GROUPER_TYPES.HIBERNATE_VERSION_NUMBER IS 'hibernate uses this to version rows';

 

ALTER TABLE SUBJECTATTRIBUTE DROP constraint FK_SUBJECTATTR_SUBJECTID;

ALTER TABLE SUBJECTATTRIBUTE

    ADD CONSTRAINT fk_subjectattr_subjectid FOREIGN KEY (SUBJECTID) REFERENCES SUBJECT (SUBJECTID);

 

COMMENT ON TABLE SUBJECT IS 'sample subject table for grouper unit tests';

 

COMMENT ON COLUMN SUBJECT.SUBJECTID IS 'subject id of row';

 

COMMENT ON COLUMN SUBJECT.SUBJECTTYPEID IS 'subject type e.g. person';

 

COMMENT ON COLUMN SUBJECT.NAME IS 'name of this subject';

 

COMMENT ON TABLE SUBJECTATTRIBUTE IS 'attribute data for each subject';

 

COMMENT ON COLUMN SUBJECTATTRIBUTE.SUBJECTID IS 'subject id of row';

 

COMMENT ON COLUMN SUBJECTATTRIBUTE.NAME IS 'name of attribute';

 

COMMENT ON COLUMN SUBJECTATTRIBUTE.VALUE IS 'value of attribute';

 

COMMENT ON COLUMN SUBJECTATTRIBUTE.SEARCHVALUE IS 'search value (e.g. all lower)';