CREATE TABLE grouper_ddl
(
    id VARCHAR(40) NOT NULL,
    object_name VARCHAR(128) NULL,
    db_version INTEGER,
    last_updated VARCHAR(50) NULL,
    history text NULL,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX grouper_ddl_object_name_idx ON grouper_ddl (object_name);


insert into grouper_ddl (id, object_name, db_version, last_updated, history) values ('8e2e916ed9ce4ffd88931c99deb607d5', 'Grouper', 1, '2010/04/06 13:53:27', 
'2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ');
commit;

CREATE TABLE grouper_attributes
(
    id VARCHAR(40) NOT NULL,
    group_id VARCHAR(40) NOT NULL,
    field_id VARCHAR(40) NOT NULL,
    value VARCHAR(1024) NOT NULL,
    hibernate_version_number BIGINT,
    context_id VARCHAR(40) NULL,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX attribute_uniq_idx ON grouper_attributes (group_id, field_id);

CREATE INDEX attribute_field_value_idx ON grouper_attributes (field_id, value);

CREATE INDEX attribute_group_idx ON grouper_attributes (group_id);

CREATE INDEX attribute_context_idx ON grouper_attributes (context_id);

CREATE TABLE grouper_composites
(
    id VARCHAR(40) NOT NULL,
    owner VARCHAR(40) NOT NULL,
    left_factor VARCHAR(40) NOT NULL,
    right_factor VARCHAR(40) NOT NULL,
    type VARCHAR(32) NOT NULL,
    creator_id VARCHAR(40) NOT NULL,
    create_time BIGINT NOT NULL,
    hibernate_version_number BIGINT,
    context_id VARCHAR(40) NULL,
    PRIMARY KEY (id)
);

CREATE INDEX composite_composite_idx ON grouper_composites (owner);

CREATE INDEX composite_createtime_idx ON grouper_composites (create_time);

CREATE INDEX composite_creator_idx ON grouper_composites (creator_id);

CREATE INDEX composite_factor_idx ON grouper_composites (left_factor, right_factor);

CREATE INDEX composite_left_factor_idx ON grouper_composites (left_factor);

CREATE INDEX composite_right_factor_idx ON grouper_composites (right_factor);

CREATE INDEX composite_context_idx ON grouper_composites (context_id);

CREATE TABLE grouper_fields
(
    id VARCHAR(40) NOT NULL,
    grouptype_uuid VARCHAR(40) NOT NULL,
    is_nullable TINYINT(1),
    name VARCHAR(32) NOT NULL,
    read_privilege VARCHAR(32) NOT NULL,
    type VARCHAR(32) NOT NULL,
    write_privilege VARCHAR(32) NOT NULL,
    hibernate_version_number BIGINT,
    context_id VARCHAR(40) NULL,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX name_and_type ON grouper_fields (name, type);

CREATE INDEX fields_context_idx ON grouper_fields (context_id);

CREATE TABLE grouper_groups
(
    id VARCHAR(40) NOT NULL,
    parent_stem VARCHAR(40) NOT NULL,
    creator_id VARCHAR(40) NOT NULL,
    create_time BIGINT NOT NULL,
    modifier_id VARCHAR(40) NULL,
    modify_time BIGINT,
    last_membership_change BIGINT,
    alternate_name VARCHAR(1024) NULL,
    hibernate_version_number BIGINT,
    name VARCHAR(1024) NULL,
    display_name VARCHAR(1024) NULL,
    extension VARCHAR(255) NULL,
    display_extension VARCHAR(255) NULL,
    description VARCHAR(1024) NULL,
    context_id VARCHAR(40) NULL,
    type_of_group VARCHAR(10) DEFAULT 'group' NOT NULL,
    PRIMARY KEY (id)
);

CREATE INDEX group_last_membership_idx ON grouper_groups (last_membership_change);

CREATE INDEX group_creator_idx ON grouper_groups (creator_id);

CREATE INDEX group_createtime_idx ON grouper_groups (create_time);

CREATE INDEX group_modifier_idx ON grouper_groups (modifier_id);

CREATE INDEX group_modifytime_idx ON grouper_groups (modify_time);

CREATE INDEX group_context_idx ON grouper_groups (context_id);

CREATE TABLE grouper_groups_types
(
    id VARCHAR(40) NOT NULL,
    group_uuid VARCHAR(40) NOT NULL,
    type_uuid VARCHAR(40) NOT NULL,
    hibernate_version_number BIGINT,
    context_id VARCHAR(40) NULL,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX grouptypetyple_grouptype_idx ON grouper_groups_types (group_uuid, type_uuid);

CREATE INDEX grouptypetuple_type_idx ON grouper_groups_types (type_uuid);

CREATE INDEX grouptypetuple_group_idx ON grouper_groups_types (group_uuid);

CREATE INDEX grouptypetuple_context_idx ON grouper_groups_types (context_id);

CREATE TABLE grouper_members
(
    id VARCHAR(40) NOT NULL,
    subject_id VARCHAR(255) NOT NULL,
    subject_source VARCHAR(255) NOT NULL,
    subject_type VARCHAR(255) NOT NULL,
    hibernate_version_number BIGINT,
    context_id VARCHAR(40) NULL,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX member_subjectsourcetype_idx ON grouper_members (subject_id, subject_source, subject_type);

CREATE INDEX member_subjectsource_idx ON grouper_members (subject_source);

CREATE INDEX member_subjectid_idx ON grouper_members (subject_id);

CREATE INDEX member_subjecttype_idx ON grouper_members (subject_type);

CREATE INDEX member_context_idx ON grouper_members (context_id);

CREATE TABLE grouper_memberships
(
    id VARCHAR(40) NOT NULL,
    member_id VARCHAR(40) NOT NULL,
    owner_id VARCHAR(40) NOT NULL,
    field_id VARCHAR(40) NOT NULL,
    owner_group_id VARCHAR(40) NULL,
    owner_stem_id VARCHAR(40) NULL,
    owner_attr_def_id VARCHAR(40) NULL,
    via_composite_id VARCHAR(40) NULL,
    enabled VARCHAR(1) DEFAULT 'T' NOT NULL,
    enabled_timestamp BIGINT,
    disabled_timestamp BIGINT,
    mship_type VARCHAR(32) NOT NULL,
    creator_id VARCHAR(40) NULL,
    create_time BIGINT NOT NULL,
    hibernate_version_number BIGINT,
    context_id VARCHAR(40) NULL,
    PRIMARY KEY (id)
);

CREATE INDEX membership_owner_group_idx ON grouper_memberships (owner_group_id);

CREATE INDEX membership_owner_stem_idx ON grouper_memberships (owner_stem_id);

CREATE INDEX membership_owner_attr_idx ON grouper_memberships (owner_attr_def_id);

CREATE INDEX membership_via_composite_idx ON grouper_memberships (via_composite_id);

CREATE INDEX membership_member_cvia_idx ON grouper_memberships (member_id, via_composite_id);

CREATE INDEX membership_gowner_member_idx ON grouper_memberships (owner_group_id, member_id, field_id);

CREATE INDEX membership_sowner_member_idx ON grouper_memberships (owner_stem_id, member_id, field_id);

CREATE INDEX membership_aowner_member_idx ON grouper_memberships (owner_attr_def_id, member_id, field_id);

CREATE INDEX membership_enabled_idx ON grouper_memberships (enabled);

CREATE INDEX membership_enabled_time_idx ON grouper_memberships (enabled_timestamp);

CREATE INDEX membership_disabled_time_idx ON grouper_memberships (disabled_timestamp);

CREATE UNIQUE INDEX membership_uniq_idx ON grouper_memberships (owner_id, member_id, field_id);

CREATE INDEX membership_createtime_idx ON grouper_memberships (create_time);

CREATE INDEX membership_creator_idx ON grouper_memberships (creator_id);

CREATE INDEX membership_member_idx ON grouper_memberships (member_id);

CREATE INDEX membership_member_list_idx ON grouper_memberships (member_id, field_id);

CREATE INDEX membership_gowner_field_type_idx ON grouper_memberships (owner_group_id, field_id, mship_type);

CREATE INDEX membership_sowner_field_type_idx ON grouper_memberships (owner_stem_id, field_id, mship_type);

CREATE INDEX membership_type_idx ON grouper_memberships (mship_type);

CREATE INDEX membership_context_idx ON grouper_memberships (context_id);

CREATE TABLE grouper_group_set
(
    id VARCHAR(40) NOT NULL,
    owner_attr_def_id VARCHAR(40) NULL,
    owner_attr_def_id_null VARCHAR(40) DEFAULT '<NULL>' NOT NULL,
    owner_group_id VARCHAR(40) NULL,
    owner_group_id_null VARCHAR(40) DEFAULT '<NULL>' NOT NULL,
    owner_stem_id VARCHAR(40) NULL,
    owner_stem_id_null VARCHAR(40) DEFAULT '<NULL>' NOT NULL,
    member_attr_def_id VARCHAR(40) NULL,
    member_group_id VARCHAR(40) NULL,
    member_stem_id VARCHAR(40) NULL,
    member_id VARCHAR(40) NOT NULL,
    field_id VARCHAR(40) NOT NULL,
    member_field_id VARCHAR(40) NOT NULL,
    mship_type VARCHAR(16) NOT NULL,
    depth INTEGER NOT NULL,
    via_group_id VARCHAR(40) NULL,
    parent_id VARCHAR(40) NULL,
    creator_id VARCHAR(40) NOT NULL,
    create_time BIGINT NOT NULL,
    context_id VARCHAR(40) NULL,
    hibernate_version_number BIGINT,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX group_set_uniq_idx ON grouper_group_set (member_id, field_id, owner_attr_def_id_null, owner_group_id_null, owner_stem_id_null, mship_type, parent_id);

CREATE INDEX group_set_creator_idx ON grouper_group_set (creator_id);

CREATE INDEX group_set_parent_idx ON grouper_group_set (parent_id);

CREATE INDEX group_set_via_group_idx ON grouper_group_set (via_group_id);

CREATE INDEX group_set_context_idx ON grouper_group_set (context_id);

CREATE INDEX group_set_gmember_idx ON grouper_group_set (member_group_id);

CREATE INDEX group_set_smember_idx ON grouper_group_set (member_stem_id);

CREATE INDEX group_set_amember_idx ON grouper_group_set (member_attr_def_id);

CREATE INDEX group_set_gowner_field_idx ON grouper_group_set (owner_group_id, field_id);

CREATE INDEX group_set_sowner_field_idx ON grouper_group_set (owner_stem_id, field_id);

CREATE INDEX group_set_aowner_field_idx ON grouper_group_set (owner_attr_def_id, field_id);

CREATE INDEX group_set_gowner_member_idx ON grouper_group_set (owner_group_id, member_group_id, field_id, depth);

CREATE INDEX group_set_sowner_member_idx ON grouper_group_set (owner_stem_id, member_stem_id, field_id, depth);

CREATE INDEX group_set_aowner_member_idx ON grouper_group_set (owner_attr_def_id, member_attr_def_id, field_id, depth);

CREATE TABLE grouper_stems
(
    id VARCHAR(40) NOT NULL,
    parent_stem VARCHAR(40) NULL,
    name VARCHAR(255) NOT NULL,
    display_name VARCHAR(255) NOT NULL,
    creator_id VARCHAR(40) NOT NULL,
    create_time BIGINT NOT NULL,
    modifier_id VARCHAR(40) NULL,
    modify_time BIGINT,
    display_extension VARCHAR(255) NOT NULL,
    extension VARCHAR(255) NOT NULL,
    description VARCHAR(1024) NULL,
    last_membership_change BIGINT,
    hibernate_version_number BIGINT,
    context_id VARCHAR(40) NULL,
    PRIMARY KEY (id)
);

CREATE INDEX stem_last_membership_idx ON grouper_stems (last_membership_change);

CREATE INDEX stem_createtime_idx ON grouper_stems (create_time);

CREATE INDEX stem_creator_idx ON grouper_stems (creator_id);

CREATE INDEX stem_dislpayextn_idx ON grouper_stems (display_extension);

CREATE INDEX stem_displayname_idx ON grouper_stems (display_name);

CREATE INDEX stem_extn_idx ON grouper_stems (extension);

CREATE INDEX stem_modifier_idx ON grouper_stems (modifier_id);

CREATE INDEX stem_modifytime_idx ON grouper_stems (modify_time);

CREATE UNIQUE INDEX stem_name_idx ON grouper_stems (name);

CREATE INDEX stem_parent_idx ON grouper_stems (parent_stem);

CREATE INDEX stem_context_idx ON grouper_stems (context_id);

CREATE TABLE grouper_types
(
    id VARCHAR(40) NOT NULL,
    name VARCHAR(255) NOT NULL,
    creator_uuid VARCHAR(40) NOT NULL,
    create_time BIGINT NOT NULL,
    is_assignable TINYINT(1),
    is_internal TINYINT(1),
    hibernate_version_number BIGINT,
    context_id VARCHAR(40) NULL,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX type_name_idx ON grouper_types (name);

CREATE INDEX type_context_idx ON grouper_types (context_id);

CREATE TABLE grouper_audit_type
(
    action_name VARCHAR(50) NULL,
    audit_category VARCHAR(50) NULL,
    context_id VARCHAR(40) NULL,
    created_on BIGINT,
    hibernate_version_number BIGINT,
    id VARCHAR(40) NOT NULL,
    label_int01 VARCHAR(50) NULL,
    label_int02 VARCHAR(50) NULL,
    label_int03 VARCHAR(50) NULL,
    label_int04 VARCHAR(50) NULL,
    label_int05 VARCHAR(50) NULL,
    label_string01 VARCHAR(50) NULL,
    label_string02 VARCHAR(50) NULL,
    label_string03 VARCHAR(50) NULL,
    label_string04 VARCHAR(50) NULL,
    label_string05 VARCHAR(50) NULL,
    label_string06 VARCHAR(50) NULL,
    label_string07 VARCHAR(50) NULL,
    label_string08 VARCHAR(50) NULL,
    last_updated BIGINT,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX audit_type_category_type_idx ON grouper_audit_type (audit_category, action_name);

CREATE TABLE grouper_audit_entry
(
    act_as_member_id VARCHAR(40) NULL,
    audit_type_id VARCHAR(40) NOT NULL,
    context_id VARCHAR(40) NULL,
    created_on BIGINT,
    description text NULL,
    env_name VARCHAR(50) NULL,
    grouper_engine VARCHAR(50) NULL,
    grouper_version VARCHAR(20) NULL,
    hibernate_version_number BIGINT,
    id VARCHAR(40) NOT NULL,
    int01 BIGINT,
    int02 BIGINT,
    int03 BIGINT,
    int04 BIGINT,
    int05 BIGINT,
    last_updated BIGINT,
    logged_in_member_id VARCHAR(40) NULL,
    server_host VARCHAR(50) NULL,
    string01 text NULL,
    string02 text NULL,
    string03 text NULL,
    string04 text NULL,
    string05 text NULL,
    string06 text NULL,
    string07 text NULL,
    string08 text NULL,
    user_ip_address VARCHAR(50) NULL,
    duration_microseconds BIGINT,
    query_count INTEGER,
    server_user_name VARCHAR(50) NULL,
    PRIMARY KEY (id)
);

CREATE INDEX audit_entry_act_as_idx ON grouper_audit_entry (act_as_member_id);

CREATE INDEX audit_entry_type_idx ON grouper_audit_entry (audit_type_id);

CREATE INDEX audit_entry_context_idx ON grouper_audit_entry (context_id);

CREATE INDEX audit_entry_logged_in_idx ON grouper_audit_entry (logged_in_member_id);

CREATE TABLE grouper_change_log_type
(
    action_name VARCHAR(50) NULL,
    change_log_category VARCHAR(50) NULL,
    context_id VARCHAR(40) NULL,
    created_on BIGINT,
    hibernate_version_number BIGINT,
    id VARCHAR(40) NOT NULL,
    label_string01 VARCHAR(50) NULL,
    label_string02 VARCHAR(50) NULL,
    label_string03 VARCHAR(50) NULL,
    label_string04 VARCHAR(50) NULL,
    label_string05 VARCHAR(50) NULL,
    label_string06 VARCHAR(50) NULL,
    label_string07 VARCHAR(50) NULL,
    label_string08 VARCHAR(50) NULL,
    label_string09 VARCHAR(50) NULL,
    label_string10 VARCHAR(50) NULL,
    label_string11 VARCHAR(50) NULL,
    label_string12 VARCHAR(50) NULL,
    last_updated BIGINT,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX change_log_type_cat_type_idx ON grouper_change_log_type (change_log_category, action_name);

CREATE TABLE grouper_change_log_consumer
(
    name VARCHAR(100) NOT NULL,
    last_sequence_processed BIGINT,
    last_updated BIGINT,
    created_on BIGINT,
    id VARCHAR(40) NOT NULL,
    hibernate_version_number BIGINT,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX change_log_consumer_name_idx ON grouper_change_log_consumer (name);

CREATE TABLE grouper_change_log_entry_temp
(
    id VARCHAR(40) NOT NULL,
    change_log_type_id VARCHAR(40) NOT NULL,
    context_id VARCHAR(40) NULL,
    created_on BIGINT NOT NULL,
    string01 text NULL,
    string02 text NULL,
    string03 text NULL,
    string04 text NULL,
    string05 text NULL,
    string06 text NULL,
    string07 text NULL,
    string08 text NULL,
    string09 text NULL,
    string10 text NULL,
    string11 text NULL,
    string12 text NULL,
    PRIMARY KEY (id)
);

CREATE TABLE grouper_change_log_entry
(
    change_log_type_id VARCHAR(40) NOT NULL,
    context_id VARCHAR(40) NULL,
    created_on BIGINT,
    sequence_number BIGINT,
    string01 text NULL,
    string02 text NULL,
    string03 text NULL,
    string04 text NULL,
    string05 text NULL,
    string06 text NULL,
    string07 text NULL,
    string08 text NULL,
    string09 text NULL,
    string10 text NULL,
    string11 text NULL,
    string12 text NULL,
    PRIMARY KEY (sequence_number)
);

CREATE INDEX change_log_sequence_number_idx ON grouper_change_log_entry (sequence_number, created_on);

CREATE INDEX change_log_context_id_idx ON grouper_change_log_entry (context_id);

CREATE TABLE grouper_attribute_def
(
    attribute_def_public VARCHAR(1) DEFAULT 'F' NOT NULL,
    attribute_def_type VARCHAR(32) DEFAULT 'attr' NOT NULL,
    context_id VARCHAR(40) NULL,
    created_on BIGINT,
    creator_id VARCHAR(40) NULL,
    hibernate_version_number BIGINT,
    last_updated BIGINT,
    id VARCHAR(40) NOT NULL,
    description VARCHAR(1024) NULL,
    extension VARCHAR(255) NOT NULL,
    name VARCHAR(1024) NOT NULL,
    multi_assignable VARCHAR(1) DEFAULT 'F' NOT NULL,
    multi_valued VARCHAR(1) DEFAULT 'F' NOT NULL,
    stem_id VARCHAR(40) NOT NULL,
    value_type VARCHAR(32) DEFAULT 'marker' NOT NULL,
    assign_to_attribute_def VARCHAR(1) DEFAULT 'F' NOT NULL,
    assign_to_attribute_def_assn VARCHAR(1) DEFAULT 'F' NOT NULL,
    assign_to_eff_membership VARCHAR(1) DEFAULT 'F' NOT NULL,
    assign_to_eff_membership_assn VARCHAR(1) DEFAULT 'F' NOT NULL,
    assign_to_group VARCHAR(1) DEFAULT 'F' NOT NULL,
    assign_to_group_assn VARCHAR(1) DEFAULT 'F' NOT NULL,
    assign_to_imm_membership VARCHAR(1) DEFAULT 'F' NOT NULL,
    assign_to_imm_membership_assn VARCHAR(1) DEFAULT 'F' NOT NULL,
    assign_to_member VARCHAR(1) DEFAULT 'F' NOT NULL,
    assign_to_member_assn VARCHAR(1) DEFAULT 'F' NOT NULL,
    assign_to_stem VARCHAR(1) DEFAULT 'F' NOT NULL,
    assign_to_stem_assn VARCHAR(1) DEFAULT 'F' NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE grouper_attribute_def_name
(
    context_id VARCHAR(40) NULL,
    created_on BIGINT,
    hibernate_version_number BIGINT,
    last_updated BIGINT,
    id VARCHAR(40) NOT NULL,
    description VARCHAR(1024) NULL,
    extension VARCHAR(255) NOT NULL,
    name VARCHAR(1024) NOT NULL,
    stem_id VARCHAR(40) NOT NULL,
    attribute_def_id VARCHAR(40) NOT NULL,
    display_extension VARCHAR(128) NOT NULL,
    display_name VARCHAR(1024) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE grouper_attribute_assign
(
    attribute_assign_action_id VARCHAR(40) NOT NULL,
    attribute_def_name_id VARCHAR(40) NOT NULL,
    context_id VARCHAR(40) NULL,
    created_on BIGINT,
    disabled_time BIGINT,
    enabled VARCHAR(1) DEFAULT 'T' NOT NULL,
    enabled_time VARCHAR(1) NULL,
    hibernate_version_number BIGINT,
    id VARCHAR(40) NOT NULL,
    last_updated BIGINT,
    notes VARCHAR(1024) NULL,
    attribute_assign_delegatable VARCHAR(15) NOT NULL,
    attribute_assign_type VARCHAR(15) NOT NULL,
    owner_attribute_assign_id VARCHAR(40) NULL,
    owner_attribute_def_id VARCHAR(40) NULL,
    owner_group_id VARCHAR(40) NULL,
    owner_member_id VARCHAR(40) NULL,
    owner_membership_id VARCHAR(40) NULL,
    owner_stem_id VARCHAR(40) NULL,
    PRIMARY KEY (id)
);

CREATE INDEX attribute_asgn_attr_name_idx ON grouper_attribute_assign (attribute_def_name_id, attribute_assign_action_id);

CREATE INDEX attr_asgn_own_asgn_idx ON grouper_attribute_assign (owner_attribute_assign_id, attribute_assign_action_id);

CREATE INDEX attr_asgn_own_def_idx ON grouper_attribute_assign (owner_attribute_def_id, attribute_assign_action_id);

CREATE INDEX attr_asgn_own_group_idx ON grouper_attribute_assign (owner_group_id, attribute_assign_action_id);

CREATE INDEX attr_asgn_own_mem_idx ON grouper_attribute_assign (owner_member_id, attribute_assign_action_id);

CREATE INDEX attr_asgn_own_mship_idx ON grouper_attribute_assign (owner_membership_id, attribute_assign_action_id);

CREATE INDEX attr_asgn_own_stem_idx ON grouper_attribute_assign (owner_stem_id, attribute_assign_action_id);

CREATE TABLE grouper_attribute_assign_value
(
    attribute_assign_id VARCHAR(40) NOT NULL,
    context_id VARCHAR(40) NULL,
    created_on BIGINT,
    hibernate_version_number BIGINT,
    id VARCHAR(40) NOT NULL,
    last_updated BIGINT,
    value_integer BIGINT,
    value_string text NULL,
    value_member_id VARCHAR(40) NULL,
    PRIMARY KEY (id)
);

CREATE INDEX attribute_val_assign_idx ON grouper_attribute_assign_value (attribute_assign_id);

CREATE INDEX attribute_val_integer_idx ON grouper_attribute_assign_value (value_integer);

CREATE INDEX attribute_val_member_id_idx ON grouper_attribute_assign_value (value_member_id);

CREATE TABLE grouper_attribute_def_scope
(
    attribute_def_id VARCHAR(40) NOT NULL,
    context_id VARCHAR(40) NULL,
    created_on BIGINT,
    hibernate_version_number BIGINT,
    id VARCHAR(40) NOT NULL,
    last_updated BIGINT,
    attribute_def_scope_type VARCHAR(32) NULL,
    scope_string VARCHAR(1024) NULL,
    scope_string2 VARCHAR(1024) NULL,
    PRIMARY KEY (id)
);

CREATE INDEX attribute_def_scope_atdef_idx ON grouper_attribute_def_scope (attribute_def_id);

CREATE TABLE grouper_attribute_def_name_set
(
    context_id VARCHAR(40) NULL,
    created_on BIGINT,
    hibernate_version_number BIGINT,
    id VARCHAR(40) NOT NULL,
    last_updated BIGINT,
    depth BIGINT NOT NULL,
    if_has_attribute_def_name_id VARCHAR(40) NOT NULL,
    then_has_attribute_def_name_id VARCHAR(40) NOT NULL,
    parent_attr_def_name_set_id VARCHAR(40) NULL,
    type VARCHAR(32) NOT NULL,
    PRIMARY KEY (id)
);

CREATE INDEX attr_def_name_set_ifhas_idx ON grouper_attribute_def_name_set (if_has_attribute_def_name_id);

CREATE INDEX attr_def_name_set_then_idx ON grouper_attribute_def_name_set (then_has_attribute_def_name_id);

CREATE UNIQUE INDEX attr_def_name_set_unq_idx ON grouper_attribute_def_name_set (parent_attr_def_name_set_id, if_has_attribute_def_name_id, then_has_attribute_def_name_id);

CREATE TABLE grouper_attr_assign_action
(
    attribute_def_id VARCHAR(40) NOT NULL,
    context_id VARCHAR(40) NULL,
    created_on BIGINT,
    hibernate_version_number BIGINT,
    id VARCHAR(40) NOT NULL,
    last_updated BIGINT,
    name VARCHAR(40) NULL,
    PRIMARY KEY (id)
);

CREATE INDEX attr_assn_act_def_id_idx ON grouper_attr_assign_action (attribute_def_id);

CREATE TABLE grouper_attr_assign_action_set
(
    context_id VARCHAR(40) NULL,
    created_on BIGINT,
    hibernate_version_number BIGINT,
    id VARCHAR(40) NOT NULL,
    last_updated BIGINT,
    depth BIGINT NOT NULL,
    if_has_attr_assn_action_id VARCHAR(40) NOT NULL,
    then_has_attr_assn_action_id VARCHAR(40) NOT NULL,
    parent_attr_assn_action_id VARCHAR(40) NULL,
    type VARCHAR(32) NOT NULL,
    PRIMARY KEY (id)
);

CREATE INDEX action_set_ifhas_idx ON grouper_attr_assign_action_set (if_has_attr_assn_action_id);

CREATE INDEX action_set_then_idx ON grouper_attr_assign_action_set (then_has_attr_assn_action_id);

CREATE UNIQUE INDEX action_set_unq_idx ON grouper_attr_assign_action_set (parent_attr_assn_action_id, if_has_attr_assn_action_id, then_has_attr_assn_action_id);

CREATE TABLE grouper_role_set
(
    context_id VARCHAR(40) NULL,
    created_on BIGINT,
    hibernate_version_number BIGINT,
    id VARCHAR(40) NOT NULL,
    last_updated BIGINT,
    depth BIGINT NOT NULL,
    if_has_role_id VARCHAR(40) NOT NULL,
    then_has_role_id VARCHAR(40) NOT NULL,
    parent_role_set_id VARCHAR(40) NULL,
    type VARCHAR(32) NOT NULL,
    PRIMARY KEY (id)
);

CREATE INDEX role_set_ifhas_idx ON grouper_role_set (if_has_role_id);

CREATE INDEX role_set_then_idx ON grouper_role_set (then_has_role_id);

CREATE UNIQUE INDEX role_set_unq_idx ON grouper_role_set (parent_role_set_id, if_has_role_id, then_has_role_id);

CREATE INDEX attribute_value_idx ON grouper_attributes (value(255));

CREATE INDEX group_alternate_name_idx ON grouper_groups (alternate_name(255));

CREATE INDEX audit_entry_string01_idx ON grouper_audit_entry (string01(255));

CREATE INDEX audit_entry_string02_idx ON grouper_audit_entry (string02(255));

CREATE INDEX audit_entry_string03_idx ON grouper_audit_entry (string03(255));

CREATE INDEX audit_entry_string04_idx ON grouper_audit_entry (string04(255));

CREATE INDEX audit_entry_string05_idx ON grouper_audit_entry (string05(255));

CREATE INDEX change_log_entry_string01_idx ON grouper_change_log_entry (string01(255));

CREATE INDEX change_log_entry_string02_idx ON grouper_change_log_entry (string02(255));

CREATE INDEX change_log_entry_string03_idx ON grouper_change_log_entry (string03(255));

CREATE INDEX change_log_entry_string04_idx ON grouper_change_log_entry (string04(255));

CREATE INDEX change_log_entry_string05_idx ON grouper_change_log_entry (string05(255));

CREATE INDEX change_log_entry_string06_idx ON grouper_change_log_entry (string06(255));

CREATE INDEX change_log_entry_string07_idx ON grouper_change_log_entry (string07(255));

CREATE INDEX change_log_entry_string08_idx ON grouper_change_log_entry (string08(255));

CREATE INDEX change_log_entry_string09_idx ON grouper_change_log_entry (string09(255));

CREATE INDEX change_log_entry_string10_idx ON grouper_change_log_entry (string10(255));

CREATE INDEX change_log_entry_string11_idx ON grouper_change_log_entry (string11(255));

CREATE INDEX change_log_entry_string12_idx ON grouper_change_log_entry (string12(255));

CREATE unique INDEX attribute_def_name_idx ON grouper_attribute_def (name(255));

CREATE unique INDEX attribute_def_name_name_idx ON grouper_attribute_def_name (name(255));

CREATE INDEX attribute_val_string_idx ON grouper_attribute_assign_value (value_string(255));



update grouper_ddl set db_version = 2, last_updated = '2010/04/06 13:53:27', 
history = '2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;

CREATE TABLE grouper_loader_log
(
    id VARCHAR(40) NOT NULL,
    job_name VARCHAR(512) NULL,
    status VARCHAR(20) NULL,
    started_time DATETIME,
    ended_time DATETIME,
    millis INTEGER,
    millis_get_data INTEGER,
    millis_load_data INTEGER,
    job_type VARCHAR(128) NULL,
    job_schedule_type VARCHAR(128) NULL,
    job_description text NULL,
    job_message text NULL,
    host VARCHAR(128) NULL,
    group_uuid VARCHAR(40) NULL,
    job_schedule_quartz_cron VARCHAR(128) NULL,
    job_schedule_interval_seconds INTEGER,
    last_updated DATETIME,
    unresolvable_subject_count INTEGER,
    insert_count INTEGER,
    update_count INTEGER,
    delete_count INTEGER,
    total_count INTEGER,
    parent_job_name VARCHAR(512) NULL,
    parent_job_id VARCHAR(40) NULL,
    and_group_names VARCHAR(512) NULL,
    job_schedule_priority INTEGER,
    context_id VARCHAR(40) NULL,
    PRIMARY KEY (id)
);

CREATE INDEX loader_context_idx ON grouper_loader_log (context_id);

CREATE INDEX grouper_loader_job_name_idx ON grouper_loader_log (job_name(255));



update grouper_ddl set db_version = 3, last_updated = '2010/04/06 13:53:27', 
history = '2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 4, last_updated = '2010/04/06 13:53:27', 
history = '2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 5, last_updated = '2010/04/06 13:53:27', 
history = '2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 6, last_updated = '2010/04/06 13:53:27', 
history = '2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 7, last_updated = '2010/04/06 13:53:27', 
history = '2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 8, last_updated = '2010/04/06 13:53:27', 
history = '2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 9, last_updated = '2010/04/06 13:53:27', 
history = '2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 10, last_updated = '2010/04/06 13:53:27', 
history = '2010/04/06 13:53:27: upgrade Grouper from V9 to V10, 2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 11, last_updated = '2010/04/06 13:53:28', 
history = '2010/04/06 13:53:28: upgrade Grouper from V10 to V11, 2010/04/06 13:53:27: upgrade Grouper from V9 to V10, 2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 12, last_updated = '2010/04/06 13:53:28', 
history = '2010/04/06 13:53:28: upgrade Grouper from V11 to V12, 2010/04/06 13:53:28: upgrade Grouper from V10 to V11, 2010/04/06 13:53:27: upgrade Grouper from V9 to V10, 2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 13, last_updated = '2010/04/06 13:53:28', 
history = '2010/04/06 13:53:28: upgrade Grouper from V12 to V13, 2010/04/06 13:53:28: upgrade Grouper from V11 to V12, 2010/04/06 13:53:28: upgrade Grouper from V10 to V11, 2010/04/06 13:53:27: upgrade Grouper from V9 to V10, 2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 14, last_updated = '2010/04/06 13:53:28', 
history = '2010/04/06 13:53:28: upgrade Grouper from V13 to V14, 2010/04/06 13:53:28: upgrade Grouper from V12 to V13, 2010/04/06 13:53:28: upgrade Grouper from V11 to V12, 2010/04/06 13:53:28: upgrade Grouper from V10 to V11, 2010/04/06 13:53:27: upgrade Grouper from V9 to V10, 2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;

CREATE UNIQUE INDEX group_parent_idx ON grouper_groups (parent_stem, extension);

CREATE INDEX group_parent_display_idx ON grouper_groups (parent_stem, display_extension);

CREATE unique INDEX group_name_idx ON grouper_groups (name(255));

CREATE INDEX group_display_name_idx ON grouper_groups (display_name(255));



update grouper_ddl set db_version = 15, last_updated = '2010/04/06 13:53:28', 
history = '2010/04/06 13:53:28: upgrade Grouper from V14 to V15, 2010/04/06 13:53:28: upgrade Grouper from V13 to V14, 2010/04/06 13:53:28: upgrade Grouper from V12 to V13, 2010/04/06 13:53:28: upgrade Grouper from V11 to V12, 2010/04/06 13:53:28: upgrade Grouper from V10 to V11, 2010/04/06 13:53:27: upgrade Grouper from V9 to V10, 2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;

DROP INDEX membership_uniq_idx ON grouper_memberships;


update grouper_ddl set db_version = 16, last_updated = '2010/04/06 13:53:28', 
history = '2010/04/06 13:53:28: upgrade Grouper from V15 to V16, 2010/04/06 13:53:28: upgrade Grouper from V14 to V15, 2010/04/06 13:53:28: upgrade Grouper from V13 to V14, 2010/04/06 13:53:28: upgrade Grouper from V12 to V13, 2010/04/06 13:53:28: upgrade Grouper from V11 to V12, 2010/04/06 13:53:28: upgrade Grouper from V10 to V11, 2010/04/06 13:53:27: upgrade Grouper from V9 to V10, 2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;

CREATE UNIQUE INDEX membership_uniq_idx ON grouper_memberships (owner_id, member_id, field_id);


update grouper_ddl set db_version = 17, last_updated = '2010/04/06 13:53:28', 
history = '2010/04/06 13:53:28: upgrade Grouper from V16 to V17, 2010/04/06 13:53:28: upgrade Grouper from V15 to V16, 2010/04/06 13:53:28: upgrade Grouper from V14 to V15, 2010/04/06 13:53:28: upgrade Grouper from V13 to V14, 2010/04/06 13:53:28: upgrade Grouper from V12 to V13, 2010/04/06 13:53:28: upgrade Grouper from V11 to V12, 2010/04/06 13:53:28: upgrade Grouper from V10 to V11, 2010/04/06 13:53:27: upgrade Grouper from V9 to V10, 2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 18, last_updated = '2010/04/06 13:53:28', 
history = '2010/04/06 13:53:28: upgrade Grouper from V17 to V18, 2010/04/06 13:53:28: upgrade Grouper from V16 to V17, 2010/04/06 13:53:28: upgrade Grouper from V15 to V16, 2010/04/06 13:53:28: upgrade Grouper from V14 to V15, 2010/04/06 13:53:28: upgrade Grouper from V13 to V14, 2010/04/06 13:53:28: upgrade Grouper from V12 to V13, 2010/04/06 13:53:28: upgrade Grouper from V11 to V12, 2010/04/06 13:53:28: upgrade Grouper from V10 to V11, 2010/04/06 13:53:27: upgrade Grouper from V9 to V10, 2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 19, last_updated = '2010/04/06 13:53:28', 
history = '2010/04/06 13:53:28: upgrade Grouper from V18 to V19, 2010/04/06 13:53:28: upgrade Grouper from V17 to V18, 2010/04/06 13:53:28: upgrade Grouper from V16 to V17, 2010/04/06 13:53:28: upgrade Grouper from V15 to V16, 2010/04/06 13:53:28: upgrade Grouper from V14 to V15, 2010/04/06 13:53:28: upgrade Grouper from V13 to V14, 2010/04/06 13:53:28: upgrade Grouper from V12 to V13, 2010/04/06 13:53:28: upgrade Grouper from V11 to V12, 2010/04/06 13:53:28: upgrade Grouper from V10 to V11, 2010/04/06 13:53:27: upgrade Grouper from V9 to V10, 2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 20, last_updated = '2010/04/06 13:53:28', 
history = '2010/04/06 13:53:28: upgrade Grouper from V19 to V20, 2010/04/06 13:53:28: upgrade Grouper from V18 to V19, 2010/04/06 13:53:28: upgrade Grouper from V17 to V18, 2010/04/06 13:53:28: upgrade Grouper from V16 to V17, 2010/04/06 13:53:28: upgrade Grouper from V15 to V16, 2010/04/06 13:53:28: upgrade Grouper from V14 to V15, 2010/04/06 13:53:28: upgrade Grouper from V13 to V14, 2010/04/06 13:53:28: upgrade Grouper from V12 to V13, 2010/04/06 13:53:28: upgrade Grouper from V11 to V12, 2010/04/06 13:53:28: upgrade Grouper from V10 to V11, 2010/04/06 13:53:27: upgrade Grouper from V9 to V10, 2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 21, last_updated = '2010/04/06 13:53:28', 
history = '2010/04/06 13:53:28: upgrade Grouper from V20 to V21, 2010/04/06 13:53:28: upgrade Grouper from V19 to V20, 2010/04/06 13:53:28: upgrade Grouper from V18 to V19, 2010/04/06 13:53:28: upgrade Grouper from V17 to V18, 2010/04/06 13:53:28: upgrade Grouper from V16 to V17, 2010/04/06 13:53:28: upgrade Grouper from V15 to V16, 2010/04/06 13:53:28: upgrade Grouper from V14 to V15, 2010/04/06 13:53:28: upgrade Grouper from V13 to V14, 2010/04/06 13:53:28: upgrade Grouper from V12 to V13, 2010/04/06 13:53:28: upgrade Grouper from V11 to V12, 2010/04/06 13:53:28: upgrade Grouper from V10 to V11, 2010/04/06 13:53:27: upgrade Grouper from V9 to V10, 2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;


update grouper_ddl set db_version = 22, last_updated = '2010/04/06 13:53:28', 
history = '2010/04/06 13:53:28: upgrade Grouper from V21 to V22, 2010/04/06 13:53:28: upgrade Grouper from V20 to V21, 2010/04/06 13:53:28: upgrade Grouper from V19 to V20, 2010/04/06 13:53:28: upgrade Grouper from V18 to V19, 2010/04/06 13:53:28: upgrade Grouper from V17 to V18, 2010/04/06 13:53:28: upgrade Grouper from V16 to V17, 2010/04/06 13:53:28: upgrade Grouper from V15 to V16, 2010/04/06 13:53:28: upgrade Grouper from V14 to V15, 2010/04/06 13:53:28: upgrade Grouper from V13 to V14, 2010/04/06 13:53:28: upgrade Grouper from V12 to V13, 2010/04/06 13:53:28: upgrade Grouper from V11 to V12, 2010/04/06 13:53:28: upgrade Grouper from V10 to V11, 2010/04/06 13:53:27: upgrade Grouper from V9 to V10, 2010/04/06 13:53:27: upgrade Grouper from V8 to V9, 2010/04/06 13:53:27: upgrade Grouper from V7 to V8, 2010/04/06 13:53:27: upgrade Grouper from V6 to V7, 2010/04/06 13:53:27: upgrade Grouper from V5 to V6, 2010/04/06 13:53:27: upgrade Grouper from V4 to V5, 2010/04/06 13:53:27: upgrade Grouper from V3 to V4, 2010/04/06 13:53:27: upgrade Grouper from V2 to V3, 2010/04/06 13:53:27: upgrade Grouper from V1 to V2, 2010/04/06 13:53:27: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;

ALTER TABLE grouper_attributes
    ADD CONSTRAINT fk_attributes_field_id FOREIGN KEY (field_id) REFERENCES grouper_fields (id);

ALTER TABLE grouper_composites
    ADD CONSTRAINT fk_composites_owner FOREIGN KEY (owner) REFERENCES grouper_groups (id);

ALTER TABLE grouper_composites
    ADD CONSTRAINT fk_composites_left_factor FOREIGN KEY (left_factor) REFERENCES grouper_groups (id);

ALTER TABLE grouper_composites
    ADD CONSTRAINT fk_composites_right_factor FOREIGN KEY (right_factor) REFERENCES grouper_groups (id);

ALTER TABLE grouper_composites
    ADD CONSTRAINT fk_composites_creator_id FOREIGN KEY (creator_id) REFERENCES grouper_members (id);

ALTER TABLE grouper_fields
    ADD CONSTRAINT fk_fields_grouptype_uuid FOREIGN KEY (grouptype_uuid) REFERENCES grouper_types (id);

ALTER TABLE grouper_groups
    ADD CONSTRAINT fk_groups_parent_stem FOREIGN KEY (parent_stem) REFERENCES grouper_stems (id);

ALTER TABLE grouper_groups
    ADD CONSTRAINT fk_groups_creator_id FOREIGN KEY (creator_id) REFERENCES grouper_members (id);

ALTER TABLE grouper_groups
    ADD CONSTRAINT fk_groups_modifier_id FOREIGN KEY (modifier_id) REFERENCES grouper_members (id);

ALTER TABLE grouper_groups_types
    ADD CONSTRAINT fk_groups_types_group_uuid FOREIGN KEY (group_uuid) REFERENCES grouper_groups (id);

ALTER TABLE grouper_groups_types
    ADD CONSTRAINT fk_groups_types_type_uuid FOREIGN KEY (type_uuid) REFERENCES grouper_types (id);

ALTER TABLE grouper_memberships
    ADD CONSTRAINT fk_memberships_member_id FOREIGN KEY (member_id) REFERENCES grouper_members (id);

ALTER TABLE grouper_memberships
    ADD CONSTRAINT fk_membership_field_id FOREIGN KEY (field_id) REFERENCES grouper_fields (id);

ALTER TABLE grouper_memberships
    ADD CONSTRAINT fk_memberships_creator_id FOREIGN KEY (creator_id) REFERENCES grouper_members (id);

ALTER TABLE grouper_memberships
    ADD CONSTRAINT fk_memberships_group_owner_id FOREIGN KEY (owner_group_id) REFERENCES grouper_groups (id);

ALTER TABLE grouper_memberships
    ADD CONSTRAINT fk_memberships_stem_owner_id FOREIGN KEY (owner_stem_id) REFERENCES grouper_stems (id);

ALTER TABLE grouper_memberships
    ADD CONSTRAINT fk_memberships_comp_via_id FOREIGN KEY (via_composite_id) REFERENCES grouper_composites (id);

ALTER TABLE grouper_group_set
    ADD CONSTRAINT fk_group_set_creator_id FOREIGN KEY (creator_id) REFERENCES grouper_members (id);

ALTER TABLE grouper_group_set
    ADD CONSTRAINT fk_group_set_field_id FOREIGN KEY (field_id) REFERENCES grouper_fields (id);

ALTER TABLE grouper_group_set
    ADD CONSTRAINT fk_group_set_via_group_id FOREIGN KEY (via_group_id) REFERENCES grouper_groups (id);

ALTER TABLE grouper_group_set
    ADD CONSTRAINT fk_group_set_parent_id FOREIGN KEY (parent_id) REFERENCES grouper_group_set (id);

ALTER TABLE grouper_group_set
    ADD CONSTRAINT fk_group_set_owner_attr_def_id FOREIGN KEY (owner_attr_def_id) REFERENCES grouper_attribute_def (id);

ALTER TABLE grouper_group_set
    ADD CONSTRAINT fk_group_set_owner_group_id FOREIGN KEY (owner_group_id) REFERENCES grouper_groups (id);

ALTER TABLE grouper_group_set
    ADD CONSTRAINT fk_group_set_member_group_id FOREIGN KEY (member_group_id) REFERENCES grouper_groups (id);

ALTER TABLE grouper_group_set
    ADD CONSTRAINT fk_group_set_owner_stem_id FOREIGN KEY (owner_stem_id) REFERENCES grouper_stems (id);

ALTER TABLE grouper_group_set
    ADD CONSTRAINT fk_group_set_member_stem_id FOREIGN KEY (member_stem_id) REFERENCES grouper_stems (id);

ALTER TABLE grouper_group_set
    ADD CONSTRAINT fk_group_set_member_field_id FOREIGN KEY (member_field_id) REFERENCES grouper_fields (id);

ALTER TABLE grouper_stems
    ADD CONSTRAINT fk_stems_parent_stem FOREIGN KEY (parent_stem) REFERENCES grouper_stems (id);

ALTER TABLE grouper_stems
    ADD CONSTRAINT fk_stems_creator_id FOREIGN KEY (creator_id) REFERENCES grouper_members (id);

ALTER TABLE grouper_stems
    ADD CONSTRAINT fk_stems_modifier_id FOREIGN KEY (modifier_id) REFERENCES grouper_members (id);

ALTER TABLE grouper_types
    ADD CONSTRAINT fk_types_creator_uuid FOREIGN KEY (creator_uuid) REFERENCES grouper_members (id);

ALTER TABLE grouper_audit_entry
    ADD CONSTRAINT fk_audit_entry_type_id FOREIGN KEY (audit_type_id) REFERENCES grouper_audit_type (id);

ALTER TABLE grouper_change_log_entry
    ADD CONSTRAINT fk_change_log_entry_type_id FOREIGN KEY (change_log_type_id) REFERENCES grouper_change_log_type (id);

ALTER TABLE grouper_attribute_def
    ADD CONSTRAINT fk_attr_def_stem FOREIGN KEY (stem_id) REFERENCES grouper_stems (id);

ALTER TABLE grouper_attribute_def_name
    ADD CONSTRAINT fk_attr_def_name_stem FOREIGN KEY (stem_id) REFERENCES grouper_stems (id);

ALTER TABLE grouper_attribute_def_name_set
    ADD CONSTRAINT fk_attr_def_name_set_parent FOREIGN KEY (parent_attr_def_name_set_id) REFERENCES grouper_attribute_def_name_set (id);

ALTER TABLE grouper_attribute_def_name_set
    ADD CONSTRAINT fk_attr_def_name_if FOREIGN KEY (if_has_attribute_def_name_id) REFERENCES grouper_attribute_def_name (id);

ALTER TABLE grouper_attribute_def_name_set
    ADD CONSTRAINT fk_attr_def_name_then FOREIGN KEY (then_has_attribute_def_name_id) REFERENCES grouper_attribute_def_name (id);

ALTER TABLE grouper_attr_assign_action
    ADD CONSTRAINT fk_attr_assn_attr_def_id FOREIGN KEY (attribute_def_id) REFERENCES grouper_attribute_def (id);

ALTER TABLE grouper_role_set
    ADD CONSTRAINT fk_role_set_parent FOREIGN KEY (parent_role_set_id) REFERENCES grouper_role_set (id);

ALTER TABLE grouper_role_set
    ADD CONSTRAINT fk_role_if FOREIGN KEY (if_has_role_id) REFERENCES grouper_groups (id);

ALTER TABLE grouper_role_set
    ADD CONSTRAINT fk_role_then FOREIGN KEY (then_has_role_id) REFERENCES grouper_groups (id);

CREATE VIEW grouper_audit_entry_v (created_on, audit_category, action_name, logged_in_subject_id, act_as_subject_id, label_string01, string01, label_string02, string02, label_string03, string03, label_string04, string04, label_string05, string05, label_string06, string06, label_string07, string07, label_string08, string08, label_int01, int01, label_int02, int02, label_int03, int03, label_int04, int04, label_int05, int05, context_id, grouper_engine, description, logged_in_source_id, act_as_source_id, logged_in_member_id, act_as_member_id, audit_type_id, user_ip_address, server_host, audit_entry_last_updated, audit_entry_id, grouper_version, env_name) AS select gae.created_on, gat.audit_category, gat.action_name, (select gm.subject_id from grouper_members gm where gm.id = gae.logged_in_member_id) as logged_in_subject_id, (select gm.subject_id from grouper_members gm where gm.id = gae.act_as_member_id) as act_as_subject_id, gat.label_string01, gae.string01, gat.label_string02, gae.string02, gat.label_string03, gae.string03, gat.label_string04, gae.string04, gat.label_string05, gae.string05, gat.label_string06, gae.string06, gat.label_string07, gae.string07, gat.label_string08, gae.string08, gat.label_int01, gae.int01, gat.label_int02, gae.int02, gat.label_int03, gae.int03, gat.label_int04, gae.int04, gat.label_int05, gae.int05, gae.context_id, gae.grouper_engine, gae.description, (select gm.subject_source from grouper_members gm where gm.id = gae.logged_in_member_id) as logged_in_source_id, (select gm.subject_source from grouper_members gm where gm.id = gae.act_as_member_id) as act_as_source_id, gae.logged_in_member_id, gae.act_as_member_id, gat.id as audit_type_id, gae.user_ip_address, gae.server_host, gae.last_updated, gae.id as audit_entry_id, gae.grouper_version, gae.env_name from grouper_audit_type gat, grouper_audit_entry gae where gat.id = gae.audit_type_id ;

CREATE VIEW grouper_attributes_v (GROUP_NAME, GROUP_DISPLAY_NAME, ATTRIBUTE_NAME, ATTRIBUTE_VALUE, GROUP_TYPE_NAME, FIELD_ID, ATTRIBUTE_ID, GROUP_ID, GROUPTYPE_UUID, CONTEXT_ID) AS select  gg.name as group_name, gg.display_name as group_display_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, ga.context_id 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 ;

CREATE 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, CONTEXT_ID) AS select  (select gg.name from grouper_groups gg  where gg.id = gc.owner) as owner_group_name,  gc.TYPE as composite_type,  (select gg.name from grouper_groups gg  where gg.id =  gc.left_factor) as left_factor_group_name,  (select gg.name from grouper_groups gg  where gg.id = gc.right_factor) as right_factor_group_name,  (select gg.display_name from grouper_groups gg  where gg.id = gc.owner) as owner_group_displayname,  (select gg.display_name from grouper_groups gg  where gg.id = gc.left_factor) as left_factor_group_displayname,  (select gg.display_name from grouper_groups gg  where gg.id = gc.right_factor) 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, gc.context_id from grouper_composites gc  ;

CREATE VIEW grouper_groups_types_v (GROUP_NAME, GROUP_DISPLAYNAME, GROUP_TYPE_NAME, GROUP_ID, GROUP_TYPE_UUID, GROUPER_GROUPS_TYPES_ID, HIBERNATE_VERSION_NUMBER, CONTEXT_ID) AS select   (select gg.name from grouper_groups gg  where gg.id = ggt.GROUP_UUID) as group_name,  (select gg.display_name from grouper_groups gg  where gg.id = ggt.GROUP_UUID) 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, ggt.context_id  from grouper_groups_types ggt, grouper_types gt  where ggt.TYPE_UUID = gt.ID  ;

CREATE VIEW grouper_groups_v (EXTENSION, NAME, DISPLAY_EXTENSION, DISPLAY_NAME, DESCRIPTION, PARENT_STEM_NAME, TYPE_OF_GROUP, 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, CONTEXT_ID) AS select  gg.extension as extension, gg.name as name, gg.display_extension as display_extension, gg.display_name as display_name, gg.description as description, gs.NAME as parent_stem_name, gg.type_of_group, 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, gg.context_id   from grouper_groups gg, grouper_stems gs where gg.PARENT_STEM = gs.ID ;

CREATE VIEW grouper_roles_v (EXTENSION, NAME, DISPLAY_EXTENSION, DISPLAY_NAME, DESCRIPTION, PARENT_STEM_NAME, ROLE_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, CONTEXT_ID) AS select  gg.extension as extension, gg.name as name, gg.display_extension as display_extension, gg.display_name as display_name, gg.description as description, gs.NAME as parent_stem_name, gg.id as role_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, gg.context_id   from grouper_groups gg, grouper_stems gs where gg.PARENT_STEM = gs.ID and type_of_group = 'role' ;

CREATE VIEW grouper_memberships_all_v (MEMBERSHIP_ID, IMMEDIATE_MEMBERSHIP_ID, GROUP_SET_ID, MEMBER_ID, FIELD_ID, IMMEDIATE_FIELD_ID, OWNER_ATTR_DEF_ID, OWNER_GROUP_ID, OWNER_STEM_ID, VIA_GROUP_ID, VIA_COMPOSITE_ID, DEPTH, MSHIP_TYPE, IMMEDIATE_MSHIP_ENABLED, IMMEDIATE_MSHIP_ENABLED_TIME, IMMEDIATE_MSHIP_DISABLED_TIME, GROUP_SET_PARENT_ID, MEMBERSHIP_CREATOR_ID, MEMBERSHIP_CREATE_TIME, GROUP_SET_CREATOR_ID, GROUP_SET_CREATE_TIME, HIBERNATE_VERSION_NUMBER, CONTEXT_ID) AS select concat(ms.id, ':', gs.id) as membership_id, ms.id as immediate_membership_id, gs.id as group_set_id, ms.member_id, gs.field_id, ms.field_id, gs.owner_attr_def_id, gs.owner_group_id, gs.owner_stem_id, gs.via_group_id, ms.via_composite_id, gs.depth, gs.mship_type, ms.enabled, ms.enabled_timestamp, ms.disabled_timestamp, gs.parent_id as group_set_parent_id, ms.creator_id as membership_creator_id, ms.create_time as membership_create_time, gs.creator_id as group_set_creator_id, gs.create_time as group_set_create_time, ms.hibernate_version_number, ms.context_id from grouper_memberships ms, grouper_group_set gs where ms.owner_id = gs.member_id and ms.field_id = gs.member_field_id;

CREATE VIEW grouper_memberships_lw_v (SUBJECT_ID, SUBJECT_SOURCE, GROUP_NAME, LIST_NAME, LIST_TYPE, GROUP_ID) AS select distinct gm.SUBJECT_ID, gm.SUBJECT_SOURCE, gg.name as group_name, gfl.NAME as list_name, gfl.TYPE as list_type, gg.ID as group_id from grouper_memberships_all_v gms, grouper_members gm, grouper_groups gg, grouper_fields gfl where gms.OWNER_GROUP_ID = gg.id and gms.FIELD_ID = gfl.ID and gms.MEMBER_ID = gm.ID;

CREATE VIEW grouper_memberships_v (GROUP_NAME, GROUP_DISPLAYNAME, STEM_NAME, STEM_DISPLAYNAME, SUBJECT_ID, SUBJECT_SOURCE, MEMBER_ID, LIST_TYPE, LIST_NAME, MEMBERSHIP_TYPE, COMPOSITE_PARENT_GROUP_NAME, DEPTH, CREATOR_SOURCE, CREATOR_SUBJECT_ID, MEMBERSHIP_ID, IMMEDIATE_MEMBERSHIP_ID, GROUP_SET_ID, STEM_ID, GROUP_ID, CREATE_TIME, CREATOR_ID, FIELD_ID, CONTEXT_ID) AS select  (select gg.name from grouper_groups gg  where gg.id = gms.owner_group_id) as group_name,  (select gg.display_name from grouper_groups gg  where gg.id = gms.owner_group_id) as group_displayname,  (select gs.NAME from grouper_stems gs  where gs.ID = gms.owner_stem_id) as stem_name,  (select gs.display_NAME from grouper_stems gs  where gs.ID = gms.owner_stem_id) as stem_displayname,  gm.subject_id, gm.subject_source, gms.member_id, gf.TYPE as list_type,  gf.NAME as list_name,  gms.MSHIP_TYPE as membership_type,  (select gg.name from grouper_groups gg, grouper_composites gc  where gg.id = gms.VIA_composite_ID and gg.id = gc.OWNER) as composite_parent_group_name,  depth,   (select gm.SUBJECT_SOURCE from grouper_members gm where gm.ID = gms.membership_creator_ID) as creator_source,  (select gm.SUBJECT_ID from grouper_members gm where gm.ID = gms.membership_creator_ID) as creator_subject_id,  gms.membership_id as membership_id,   gms.immediate_membership_id as immediate_membership_id,   gms.GROUP_SET_ID as group_set_id,  (select gs.id from grouper_stems gs where gs.ID = gms.owner_stem_id) as stem_id,  (select gg.id from grouper_groups gg where gg.id = gms.owner_group_id) as group_id,  gms.membership_create_time,  gms.membership_creator_id,  gms.field_id, gms.context_id   from grouper_memberships_all_v gms, grouper_members gm, grouper_fields gf   where gms.MEMBER_ID = gm.ID and gms.field_id = gf.id  ;

CREATE 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, CONTEXT_ID) 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, gs.context_id from grouper_stems gs ;

CREATE 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 ;

CREATE 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 ;

CREATE VIEW grouper_rpt_group_field_v (GROUP_NAME, GROUP_DISPLAYNAME, FIELD_TYPE, FIELD_NAME, MEMBER_COUNT) AS select gg.name as group_name, gg.display_name 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_groups gg, grouper_fields gf where gms.FIELD_ID = gf.ID and gg.id = gms.OWNER_group_ID group by gg.name, gg.display_name, gf.type, gf.name ;

CREATE VIEW grouper_rpt_groups_v (GROUP_NAME, GROUP_DISPLAYNAME, TYPE_OF_GROUP, IMMEDIATE_MEMBERSHIP_COUNT, MEMBERSHIP_COUNT, ATTRIBUTE_COUNT, GROUPS_TYPES_COUNT, ISA_COMPOSITE_FACTOR_COUNT, ISA_MEMBER_COUNT, GROUP_ID) AS select  gg.name as group_name, gg.display_name as group_displayname, gg.type_of_group, (select count(distinct gms.MEMBER_ID) from grouper_memberships gms where gms.OWNER_group_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_group_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_group_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 ;

CREATE VIEW grouper_rpt_roles_v (ROLE_NAME, ROLE_DISPLAYNAME, IMMEDIATE_MEMBERSHIP_COUNT, MEMBERSHIP_COUNT, ATTRIBUTE_COUNT, ROLES_TYPES_COUNT, ISA_COMPOSITE_FACTOR_COUNT, ISA_MEMBER_COUNT, ROLE_ID) AS select  gg.name as role_name, gg.display_name as role_displayname, (select count(distinct gms.MEMBER_ID) from grouper_memberships gms where gms.OWNER_group_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_group_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 roles_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_group_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 role_id from grouper_groups gg  where gg.type_of_group = 'role' ;

CREATE 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_group_id) from grouper_memberships gms where gms.MEMBER_ID = gm.ID) as membership_count, gm.ID as member_id from grouper_members gm ;

CREATE 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 concat(gs.NAME,'%')) as group_count, (select count(*) from grouper_stems gs2 where gs2.name like concat(gs.NAME,'%')) as stem_count, (select count(distinct gm.member_id) from grouper_memberships gm where gm.OWNER_stem_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_stem_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_group_id = ga.group_id and ga.FIELD_ID = gf.ID and gf.NAME = 'name' and ga.value like concat(gs.NAME,'%')) as group_membership_count, gs.ID as stem_id from grouper_stems gs ;

CREATE 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 ;

CREATE VIEW grouper_role_set_v (if_has_role_name, then_has_role_name, depth, type, parent_if_has_name, parent_then_has_name, id, if_has_role_id, then_has_role_id, parent_role_set_id) AS select ifHas.name as if_has_role_name, thenHas.name as then_has_role_name,  grs.depth,   grs.type, grParentIfHas.name as parent_if_has_name, grParentThenHas.name as parent_then_has_name,   grs.id, ifHas.id as if_has_role_id, thenHas.id as then_has_role_id,   grs.parent_role_set_id  from grouper_role_set grs,   grouper_role_set grsParent,   grouper_groups grParentIfHas,   grouper_groups grParentThenHas,   grouper_groups ifHas, grouper_groups thenHas   where  thenHas.id = grs.then_has_role_id   and ifHas.id = grs.if_has_role_id   and grs.parent_role_set_id = grsParent.id   and grParentIfHas.id = grsParent.if_has_role_id   and grParentThenHas.id = grsParent.then_has_role_id   ;

CREATE VIEW grouper_attr_def_name_set_v (if_has_attr_def_name_name, then_has_attr_def_name_name, depth, type, parent_if_has_name, parent_then_has_name, id, if_has_attr_def_name_id, then_has_attr_def_name_id, parent_attr_def_name_set_id) AS select ifHas.name as if_has_attr_def_name_name, thenHas.name as then_has_attr_def_name_name,  gadns.depth,  gadns.type, gadnParentIfHas.name as parent_if_has_name, gadnParentThenHas.name as parent_then_has_name,  gadns.id,  ifHas.id as if_has_attr_def_name_id, thenHas.id as then_has_attr_def_name_id,  gadns.parent_attr_def_name_set_id from grouper_attribute_def_name_set gadns,  grouper_attribute_def_name_set gadnsParent,  grouper_attribute_def_name gadnParentIfHas,  grouper_attribute_def_name gadnParentThenHas,  grouper_attribute_def_name ifHas, grouper_attribute_def_name thenHas  where  thenHas.id = gadns.then_has_attribute_def_name_id  and ifHas.id = gadns.if_has_attribute_def_name_id  and gadns.parent_attr_def_name_set_id = gadnsParent.id  and gadnParentIfHas.id = gadnsParent.if_has_attribute_def_name_id  and gadnParentThenHas.id = gadnsParent.then_has_attribute_def_name_id  ;

CREATE VIEW grouper_attr_assn_action_set_v (if_has_attr_assn_action_name, then_has_attr_assn_action_name, depth, type, parent_if_has_name, parent_then_has_name, id, if_has_attr_assn_action_id, then_has_attr_assn_action_id, parent_attr_assn_action_id) AS select ifHas.name as if_has_attr_assn_action_name , thenHas.name as then_has_attr_assn_action_name,   gaaas.depth,   gaaas.type, gaaaParentIfHas.name as parent_if_has_name, gaaaParentThenHas.name as parent_then_has_name,   gaaas.id,   ifHas.id as if_has_attr_assn_action_id, thenHas.id as then_has_attr_assn_action_id,   gaaas.parent_attr_assn_action_id  from grouper_attr_assign_action_set gaaas,   grouper_attr_assign_action_set gaaasParent,   grouper_attr_assign_action gaaaParentIfHas,   grouper_attr_assign_action gaaaParentThenHas,   grouper_attr_assign_action ifHas, grouper_attr_assign_action thenHas   where  thenHas.id = gaaas.then_has_attr_assn_action_id   and ifHas.id = gaaas.if_has_attr_assn_action_id   and gaaas.parent_attr_assn_action_id = gaaasParent.id   and gaaaParentIfHas.id = gaaasParent.if_has_attr_assn_action_id   and gaaaParentThenHas.id = gaaasParent.then_has_attr_assn_action_id   ;

CREATE VIEW grouper_attr_asn_group_v (group_name, action, attribute_def_name_name, group_display_name, attribute_def_name_disp_name, name_of_attribute_def, attribute_assign_notes, attribute_assign_delegatable, enabled, enabled_time, disabled_time, group_id, attribute_assign_id, attribute_def_name_id, attribute_def_id, action_id) AS select gg.name as group_name, gaaa.name as action, gadn.name as attribute_def_name_name, gg.display_name as group_display_name, gadn.display_name as attribute_def_name_disp_name, gad.name as name_of_attribute_def, gaa.notes as attribute_assign_notes, gaa.attribute_assign_delegatable, gaa.enabled, gaa.enabled_time, gaa.disabled_time, gg.id as group_id, gaa.id as attribute_assign_id, gadn.id as attribute_def_name_id, gad.id as attribute_def_id, gaaa.id as action_id from grouper_attribute_assign gaa, grouper_groups gg, grouper_attribute_def_name gadn, grouper_attribute_def gad, grouper_attr_assign_action gaaa  where gaa.owner_group_id = gg.id and gaa.attribute_def_name_id = gadn.id and gadn.attribute_def_id = gad.id and gaa.owner_member_id is null and gaa.attribute_assign_action_id = gaaa.id ;

CREATE VIEW grouper_attr_asn_efmship_v (group_name, subject_source_id, subject_id, action, attribute_def_name_name, group_display_name, attribute_def_name_disp_name, name_of_attribute_def, attribute_assign_notes, list_name, attribute_assign_delegatable, enabled, enabled_time, disabled_time, group_id, attribute_assign_id, attribute_def_name_id, attribute_def_id, member_id, action_id) AS select distinct gg.name as group_name, gm.subject_source as subject_source_id, gm.subject_id, gaaa.name as action, gadn.name as attribute_def_name_name, gg.display_name as group_display_name, gadn.display_name as attribute_def_name_disp_name, gad.name as name_of_attribute_def, gaa.notes as attribute_assign_notes, gf.name as list_name, gaa.attribute_assign_delegatable, gaa.enabled, gaa.enabled_time, gaa.disabled_time, gg.id as group_id, gaa.id as attribute_assign_id, gadn.id as attribute_def_name_id, gad.id as attribute_def_id, gm.id as member_id, gaaa.id as action_id from grouper_attribute_assign gaa, grouper_memberships_all_v gmav, grouper_attribute_def_name gadn, grouper_attribute_def gad, grouper_groups gg, grouper_fields gf, grouper_members gm, grouper_attr_assign_action gaaa  where gaa.owner_group_id = gmav.owner_group_id and gaa.owner_member_id = gmav.member_id and gaa.attribute_def_name_id = gadn.id and gadn.attribute_def_id = gad.id and gmav.immediate_mship_enabled = 'T' and gmav.owner_group_id = gg.id and gmav.field_id = gf.id and gf.type = 'list' and gmav.member_id = gm.id and gaa.owner_member_id is not null and gaa.owner_group_id is null and gaa.attribute_assign_action_id = gaaa.id ;

CREATE VIEW grouper_attr_asn_stem_v (stem_name, action, attribute_def_name_name, stem_display_name, attribute_def_name_disp_name, name_of_attribute_def, attribute_assign_notes, enabled, enabled_time, disabled_time, stem_id, attribute_assign_id, attribute_def_name_id, attribute_def_id, action_id) AS select gs.name as stem_name, gaaa.name as action, gadn.name as attribute_def_name_name, gs.display_name as stem_display_name, gadn.display_name as attribute_def_name_disp_name, gad.name as name_of_attribute_def, gaa.notes as attribute_assign_notes, gaa.enabled, gaa.enabled_time, gaa.disabled_time, gs.id as stem_id, gaa.id as attribute_assign_id, gadn.id as attribute_def_name_id, gad.id as attribute_def_id, gaaa.id as action_id from grouper_attribute_assign gaa, grouper_stems gs, grouper_attribute_def_name gadn, grouper_attribute_def gad, grouper_attr_assign_action gaaa  where gaa.owner_stem_id = gs.id and gaa.attribute_def_name_id = gadn.id and gadn.attribute_def_id = gad.id and gaa.attribute_assign_action_id = gaaa.id ;

CREATE VIEW grouper_attr_asn_member_v (source_id, subject_id, action, attribute_def_name_name, attribute_def_name_disp_name, name_of_attribute_def, attribute_assign_notes, attribute_assign_delegatable, enabled, enabled_time, disabled_time, member_id, attribute_assign_id, attribute_def_name_id, attribute_def_id, action_id) AS select gm.subject_source as source_id, gm.subject_id, gaaa.name as action, gadn.name as attribute_def_name_name, gadn.display_name as attribute_def_name_disp_name, gad.name as name_of_attribute_def, gaa.notes as attribute_assign_notes, gaa.attribute_assign_delegatable, gaa.enabled, gaa.enabled_time, gaa.disabled_time, gm.id as member_id, gaa.id as attribute_assign_id, gadn.id as attribute_def_name_id, gad.id as attribute_def_id, gaaa.id as action_id from grouper_attribute_assign gaa, grouper_members gm, grouper_attribute_def_name gadn, grouper_attribute_def gad, grouper_attr_assign_action gaaa where gaa.owner_member_id = gm.id and gaa.attribute_def_name_id = gadn.id and gadn.attribute_def_id = gad.id and gaa.owner_group_id is null  and gaa.attribute_assign_action_id = gaaa.id;

CREATE VIEW grouper_attr_asn_mship_v (group_name, source_id, subject_id, action, attribute_def_name_name, attribute_def_name_disp_name, list_name, name_of_attribute_def, attribute_assign_notes, attribute_assign_delegatable, enabled, enabled_time, disabled_time, group_id, membership_id, member_id, attribute_assign_id, attribute_def_name_id, attribute_def_id, action_id) AS select gg.name as group_name, gm.subject_source as source_id, gm.subject_id, gaaa.name as action, gadn.name as attribute_def_name_name, gadn.display_name as attribute_def_name_disp_name, gf.name as list_name, gad.name as name_of_attribute_def, gaa.notes as attribute_assign_notes, gaa.attribute_assign_delegatable, gaa.enabled, gaa.enabled_time, gaa.disabled_time, gg.id as group_id, gms.id as membership_id, gm.id as member_id, gaa.id as attribute_assign_id, gadn.id as attribute_def_name_id, gad.id as attribute_def_id, gaaa.id as action_id from grouper_attribute_assign gaa, grouper_groups gg, grouper_memberships gms, grouper_attribute_def_name gadn, grouper_attribute_def gad, grouper_members gm, grouper_fields gf, grouper_attr_assign_action gaaa  where gaa.owner_membership_id = gms.id and gaa.attribute_def_name_id = gadn.id and gadn.attribute_def_id = gad.id  and gms.field_id = gf.id and gms.member_id = gm.id and gms.owner_group_id = gg.id  and gf.type = 'list' and gaa.attribute_assign_action_id = gaaa.id ;

CREATE VIEW grouper_attr_asn_attrdef_v (name_of_attr_def_assigned_to, action, attribute_def_name_name, attribute_def_name_disp_name, name_of_attribute_def_assigned, attribute_assign_notes, enabled, enabled_time, disabled_time, id_of_attr_def_assigned_to, attribute_assign_id, attribute_def_name_id, attribute_def_id, action_id) AS select gad_assigned_to.name as name_of_attr_def_assigned_to, gaaa.name as action, gadn.name as attribute_def_name_name, gadn.display_name as attribute_def_name_disp_name, gad.name as name_of_attribute_def, gaa.notes as attribute_assign_notes, gaa.enabled, gaa.enabled_time, gaa.disabled_time, gad_assigned_to.id as id_of_attr_def_assigned_to, gaa.id as attribute_assign_id, gadn.id as attribute_def_name_id, gad.id as attribute_def_id, gaaa.id as action_id from grouper_attribute_assign gaa, grouper_attribute_def gad_assigned_to, grouper_attribute_def_name gadn, grouper_attribute_def gad, grouper_attr_assign_action gaaa  where gaa.owner_attribute_def_id = gad_assigned_to.id and gaa.attribute_def_name_id = gadn.id and gadn.attribute_def_id = gad.id and gaa.attribute_assign_action_id = gaaa.id ;

CREATE VIEW grouper_attr_asn_asn_group_v (group_name, action1, action2, attribute_def_name_name1, attribute_def_name_name2, group_display_name, attribute_def_name_disp_name1, attribute_def_name_disp_name2, name_of_attribute_def1, name_of_attribute_def2, attribute_assign_notes1, attribute_assign_notes2, enabled2, enabled_time2, disabled_time2, group_id, attribute_assign_id1, attribute_assign_id2, attribute_def_name_id1, attribute_def_name_id2, attribute_def_id1, attribute_def_id2, action_id1, action_id2) AS select gg.name as group_name, gaaa1.name as action1, gaaa2.name as action2,  gadn1.name as attribute_def_name_name1, gadn2.name as attribute_def_name_name2, gg.display_name as group_display_name, gadn1.display_name as attribute_def_name_disp_name1, gadn2.display_name as attribute_def_name_disp_name2, gad1.name as name_of_attribute_def1, gad2.name as name_of_attribute_def2, gaa1.notes as attribute_assign_notes1, gaa2.notes as attribute_assign_notes2, gaa2.enabled as enabled2, gaa2.enabled_time as enabled_time2, gaa2.disabled_time as disabled_time2, gg.id as group_id, gaa1.id as attribute_assign_id1, gaa2.id as attribute_assign_id2, gadn1.id as attribute_def_name_id1, gadn2.id as attribute_def_name_id2, gad1.id as attribute_def_id1, gad2.id as attribute_def_id2, gaaa1.id as action_id1, gaaa2.id as action_id2 from grouper_attribute_assign gaa1, grouper_attribute_assign gaa2, grouper_groups gg, grouper_attribute_def_name gadn1, grouper_attribute_def_name gadn2, grouper_attribute_def gad1, grouper_attribute_def gad2, grouper_attr_assign_action gaaa1, grouper_attr_assign_action gaaa2   where gaa1.id = gaa2.owner_attribute_assign_id and gaa1.attribute_def_name_id = gadn1.id and gaa2.attribute_def_name_id = gadn2.id and gadn1.attribute_def_id = gad1.id and gadn2.attribute_def_id = gad2.id and gaa1.enabled = 'T' and gg.id = gaa1.owner_group_id and gaa1.owner_member_id is null and gaa1.attribute_assign_action_id = gaaa1.id and gaa2.attribute_assign_action_id = gaaa2.id;

CREATE VIEW grouper_attr_asn_asn_efmship_v (group_name, source_id, subject_id, action1, action2, attribute_def_name_name1, attribute_def_name_name2, attribute_def_name_disp_name1, attribute_def_name_disp_name2, list_name, name_of_attribute_def1, name_of_attribute_def2, attribute_assign_notes1, attribute_assign_notes2, enabled2, enabled_time2, disabled_time2, group_id, member_id, attribute_assign_id1, attribute_assign_id2, attribute_def_name_id1, attribute_def_name_id2, attribute_def_id1, attribute_def_id2, action_id1, action_id2) AS select distinct gg.name as group_name, gm.subject_source as source_id, gm.subject_id, gaaa1.name as action1, gaaa2.name as action2,  gadn1.name as attribute_def_name_name1, gadn2.name as attribute_def_name_name2, gadn1.display_name as attribute_def_name_disp_name1, gadn2.display_name as attribute_def_name_disp_name2, gf.name as list_name, gad1.name as name_of_attribute_def1, gad2.name as name_of_attribute_def2, gaa1.notes as attribute_assign_notes1, gaa2.notes as attribute_assign_notes2, gaa2.enabled as enabled2, gaa2.enabled_time as enabled_time2, gaa2.disabled_time as disabled_time2, gg.id as group_id, gm.id as member_id, gaa1.id as attribute_assign_id1, gaa2.id as attribute_assign_id2, gadn1.id as attribute_def_name_id1, gadn2.id as attribute_def_name_id2, gad1.id as attribute_def_id1, gad2.id as attribute_def_id2, gaaa1.id as action_id1, gaaa2.id as action_id2 from grouper_attribute_assign gaa1, grouper_attribute_assign gaa2, grouper_groups gg, grouper_memberships_all_v gmav, grouper_attribute_def_name gadn1, grouper_attribute_def_name gadn2, grouper_attribute_def gad1, grouper_attribute_def gad2, grouper_members gm, grouper_fields gf, grouper_attr_assign_action gaaa1, grouper_attr_assign_action gaaa2 where gaa1.owner_member_id = gmav.member_id and gaa1.owner_group_id = gmav.owner_group_id and gaa2.owner_attribute_assign_id = gaa1.id  and gaa1.attribute_def_name_id = gadn1.id and gaa2.attribute_def_name_id = gadn2.id and gadn1.attribute_def_id = gad1.id and gadn2.attribute_def_id = gad2.id and gaa1.enabled = 'T' and gmav.immediate_mship_enabled = 'T' and gmav.field_id = gf.id and gmav.member_id = gm.id and gmav.owner_group_id = gg.id and gf.type = 'list' and gaa1.owner_member_id is not null  and gaa1.owner_group_id is not null and gaa1.attribute_assign_action_id = gaaa1.id and gaa2.attribute_assign_action_id = gaaa2.id ;

CREATE VIEW grouper_attr_asn_asn_stem_v (stem_name, action1, action2, attribute_def_name_name1, attribute_def_name_name2, stem_display_name, attribute_def_name_disp_name1, attribute_def_name_disp_name2, name_of_attribute_def1, name_of_attribute_def2, attribute_assign_notes1, attribute_assign_notes2, enabled2, enabled_time2, disabled_time2, stem_id, attribute_assign_id1, attribute_assign_id2, attribute_def_name_id1, attribute_def_name_id2, attribute_def_id1, attribute_def_id2, action_id1, action_id2) AS select gs.name as stem_name, gaaa1.name as action1, gaaa2.name as action2,  gadn1.name as attribute_def_name_name1, gadn2.name as attribute_def_name_name2, gs.display_name as stem_display_name, gadn1.display_name as attribute_def_name_disp_name1, gadn2.display_name as attribute_def_name_disp_name2, gad1.name as name_of_attribute_def1, gad2.name as name_of_attribute_def2, gaa1.notes as attribute_assign_notes1, gaa2.notes as attribute_assign_notes2, gaa2.enabled as enabled2, gaa2.enabled_time as enabled_time2, gaa2.disabled_time as disabled_time2, gs.id as stem_id, gaa1.id as attribute_assign_id1, gaa2.id as attribute_assign_id2, gadn1.id as attribute_def_name_id1, gadn2.id as attribute_def_name_id2, gad1.id as attribute_def_id1, gad2.id as attribute_def_id2, gaaa1.id as action_id1, gaaa2.id as action_id2 from grouper_attribute_assign gaa1, grouper_attribute_assign gaa2, grouper_stems gs, grouper_attribute_def_name gadn1, grouper_attribute_def_name gadn2, grouper_attribute_def gad1, grouper_attribute_def gad2, grouper_attr_assign_action gaaa1, grouper_attr_assign_action gaaa2 where gaa1.id = gaa2.owner_attribute_assign_id and gaa1.attribute_def_name_id = gadn1.id and gaa2.attribute_def_name_id = gadn2.id and gadn1.attribute_def_id = gad1.id and gadn2.attribute_def_id = gad2.id and gaa1.enabled = 'T' and gs.id = gaa1.owner_stem_id and gaa1.attribute_assign_action_id = gaaa1.id and gaa2.attribute_assign_action_id = gaaa2.id ;

CREATE VIEW grouper_attr_asn_asn_member_v (source_id, subject_id, action1, action2, attribute_def_name_name1, attribute_def_name_name2, attribute_def_name_disp_name1, attribute_def_name_disp_name2, name_of_attribute_def1, name_of_attribute_def2, attribute_assign_notes1, attribute_assign_notes2, enabled2, enabled_time2, disabled_time2, member_id, attribute_assign_id1, attribute_assign_id2, attribute_def_name_id1, attribute_def_name_id2, attribute_def_id1, attribute_def_id2, action_id1, action_id2) AS select gm.subject_source as source_id, gm.subject_id, gaaa1.name as action1, gaaa2.name as action2,  gadn1.name as attribute_def_name_name1, gadn2.name as attribute_def_name_name2, gadn1.display_name as attribute_def_name_disp_name1, gadn2.display_name as attribute_def_name_disp_name2, gad1.name as name_of_attribute_def1, gad2.name as name_of_attribute_def2, gaa1.notes as attribute_assign_notes1, gaa2.notes as attribute_assign_notes2, gaa2.enabled as enabled2, gaa2.enabled_time as enabled_time2, gaa2.disabled_time as disabled_time2, gm.id as member_id, gaa1.id as attribute_assign_id1, gaa2.id as attribute_assign_id2, gadn1.id as attribute_def_name_id1, gadn2.id as attribute_def_name_id2, gad1.id as attribute_def_id1, gad2.id as attribute_def_id2, gaaa1.id as action_id1, gaaa2.id as action_id2 from grouper_attribute_assign gaa1, grouper_attribute_assign gaa2, grouper_members gm, grouper_attribute_def_name gadn1, grouper_attribute_def_name gadn2, grouper_attribute_def gad1, grouper_attribute_def gad2, grouper_attr_assign_action gaaa1, grouper_attr_assign_action gaaa2 where gaa1.id = gaa2.owner_attribute_assign_id and gaa1.attribute_def_name_id = gadn1.id and gaa2.attribute_def_name_id = gadn2.id and gadn1.attribute_def_id = gad1.id and gadn2.attribute_def_id = gad2.id and gaa1.enabled = 'T' and gm.id = gaa1.owner_member_id and gaa1.owner_group_id is null and gaa1.attribute_assign_action_id = gaaa1.id and gaa2.attribute_assign_action_id = gaaa2.id ;

CREATE VIEW grouper_attr_asn_asn_mship_v (group_name, source_id, subject_id, action1, action2, attribute_def_name_name1, attribute_def_name_name2, attribute_def_name_disp_name1, attribute_def_name_disp_name2, list_name, name_of_attribute_def1, name_of_attribute_def2, attribute_assign_notes1, attribute_assign_notes2, enabled2, enabled_time2, disabled_time2, group_id, membership_id, member_id, attribute_assign_id1, attribute_assign_id2, attribute_def_name_id1, attribute_def_name_id2, attribute_def_id1, attribute_def_id2, action_id1, action_id2) AS select gg.name as group_name, gm.subject_source as source_id, gm.subject_id, gaaa1.name as action1, gaaa2.name as action2,  gadn1.name as attribute_def_name_name1, gadn2.name as attribute_def_name_name2, gadn1.display_name as attribute_def_name_disp_name1, gadn2.display_name as attribute_def_name_disp_name2, gf.name as list_name, gad1.name as name_of_attribute_def1, gad2.name as name_of_attribute_def2, gaa1.notes as attribute_assign_notes1, gaa2.notes as attribute_assign_notes2, gaa2.enabled as enabled2, gaa2.enabled_time as enabled_time2, gaa2.disabled_time as disabled_time2, gg.id as group_id, gms.id as membership_id, gm.id as member_id, gaa1.id as attribute_assign_id1, gaa2.id as attribute_assign_id2, gadn1.id as attribute_def_name_id1, gadn2.id as attribute_def_name_id2, gad1.id as attribute_def_id1, gad2.id as attribute_def_id2, gaaa1.id as action_id1, gaaa2.id as action_id2 from grouper_attribute_assign gaa1, grouper_attribute_assign gaa2, grouper_groups gg, grouper_memberships gms, grouper_attribute_def_name gadn1, grouper_attribute_def_name gadn2, grouper_attribute_def gad1, grouper_attribute_def gad2, grouper_members gm, grouper_fields gf, grouper_attr_assign_action gaaa1, grouper_attr_assign_action gaaa2 where gaa1.owner_membership_id = gms.id and gaa2.owner_attribute_assign_id = gaa1.id  and gaa1.attribute_def_name_id = gadn1.id and gaa2.attribute_def_name_id = gadn2.id and gadn1.attribute_def_id = gad1.id and gadn2.attribute_def_id = gad2.id and gaa1.enabled = 'T'  and gms.field_id = gf.id and gms.member_id = gm.id and gms.owner_group_id = gg.id and gf.type = 'list' and gaa1.attribute_assign_action_id = gaaa1.id and gaa2.attribute_assign_action_id = gaaa2.id ;

CREATE VIEW grouper_attr_asn_asn_attrdef_v (name_of_attr_def_assigned_to, action1, action2, attribute_def_name_name1, attribute_def_name_name2, attribute_def_name_disp_name1, attribute_def_name_disp_name2, name_of_attribute_def1, name_of_attribute_def2, attribute_assign_notes1, attribute_assign_notes2, enabled2, enabled_time2, disabled_time2, id_of_attr_def_assigned_to, attribute_assign_id1, attribute_assign_id2, attribute_def_name_id1, attribute_def_name_id2, attribute_def_id1, attribute_def_id2, action_id1, action_id2) AS select gad.name as name_of_attr_def_assigned_to, gaaa1.name as action1, gaaa2.name as action2,  gadn1.name as attribute_def_name_name1, gadn2.name as attribute_def_name_name2, gadn1.display_name as attribute_def_name_disp_name1, gadn2.display_name as attribute_def_name_disp_name2, gad1.name as name_of_attribute_def1, gad2.name as name_of_attribute_def2, gaa1.notes as attribute_assign_notes1, gaa2.notes as attribute_assign_notes2, gaa2.enabled as enabled2, gaa2.enabled_time as enabled_time2, gaa2.disabled_time as disabled_time2, gad.id as id_of_attr_def_assigned_to, gaa1.id as attribute_assign_id1, gaa2.id as attribute_assign_id2, gadn1.id as attribute_def_name_id1, gadn2.id as attribute_def_name_id2, gad1.id as attribute_def_id1, gad2.id as attribute_def_id2, gaaa1.id as action_id1, gaaa2.id as action_id2 from grouper_attribute_assign gaa1, grouper_attribute_assign gaa2, grouper_attribute_def gad, grouper_attribute_def_name gadn1, grouper_attribute_def_name gadn2, grouper_attribute_def gad1, grouper_attribute_def gad2 , grouper_attr_assign_action gaaa1, grouper_attr_assign_action gaaa2 where gaa1.id = gaa2.owner_attribute_assign_id and gaa1.attribute_def_name_id = gadn1.id and gaa2.attribute_def_name_id = gadn2.id and gadn1.attribute_def_id = gad1.id and gadn2.attribute_def_id = gad2.id and gaa1.enabled = 'T' and gad.id = gaa1.owner_attribute_def_id and gaa1.attribute_assign_action_id = gaaa1.id and gaa2.attribute_assign_action_id = gaaa2.id ;

CREATE VIEW grouper_attr_def_priv_v (subject_id, subject_source_id, field_name, attribute_def_name, attribute_def_description, attribute_def_type, attribute_def_stem_id, attribute_def_id, member_id, field_id, immediate_membership_id, membership_id) AS select distinct gm.subject_id, gm.subject_source as subject_source_id,  gf.name as field_name, gad.name as attribute_def_name, gad.description as attribute_def_description,  gad.attribute_def_type, gad.stem_id as attribute_def_stem_id, gad.id as attribute_def_id,  gm.id as member_id, gmav.field_id, gmav.immediate_membership_id, gmav.membership_id  from grouper_memberships_all_v gmav, grouper_attribute_def gad, grouper_fields gf, grouper_members gm where gmav.owner_attr_def_id = gad.id and gmav.field_id = gf.id and gmav.immediate_mship_enabled = 'T' and gmav.member_id = gm.id ;

CREATE VIEW grouper_perms_role_v (role_name, subject_source_id, subject_id, action, attribute_def_name_name, attribute_def_name_disp_name, role_display_name, attribute_assign_delegatable, enabled, enabled_time, disabled_time, role_id, attribute_def_id, member_id, attribute_def_name_id, action_id) AS select distinct gr.name as role_name,  gm.subject_source as subject_source_id,  gm.subject_id,  gaaa.name as action, gadn.name as attribute_def_name_name,  gadn.display_name as attribute_def_name_disp_name,  gr.display_name as role_display_name,  gaa.attribute_assign_delegatable, gaa.enabled, gaa.enabled_time, gaa.disabled_time, gr.id as role_id,  gadn.attribute_def_id,  gm.id as member_id,  gadn.id as attribute_def_name_id,  gaaa.id as action_id from grouper_groups gr,  grouper_memberships_all_v gmav,  grouper_members gm,  grouper_fields gf,  grouper_role_set grs,  grouper_attribute_assign gaa,  grouper_attribute_def_name gadn,  grouper_attribute_def_name_set gadns, grouper_attr_assign_action gaaa, grouper_attr_assign_action_set gaaas where gr.type_of_group = 'role'  and gmav.owner_group_id = gr.id  and gmav.field_id = gf.id  and gf.type = 'list'  and gf.name = 'members'  and gmav.immediate_mship_enabled = 'T'  and gmav.member_id = gm.id  and grs.if_has_role_id = gr.id  and gaa.owner_group_id = grs.then_has_role_id  and gaa.attribute_def_name_id = gadns.if_has_attribute_def_name_id  and gadn.id = gadns.then_has_attribute_def_name_id  and gaa.attribute_assign_type = 'group' and gaa.attribute_assign_action_id = gaaas.if_has_attr_assn_action_id and gaaa.id = gaaas.then_has_attr_assn_action_id ;

CREATE VIEW grouper_perms_role_subject_v (role_name, subject_source_id, subject_id, action, attribute_def_name_name, attribute_def_name_disp_name, role_display_name, attribute_assign_delegatable, enabled, enabled_time, disabled_time, role_id, attribute_def_id, member_id, attribute_def_name_id, action_id) AS select distinct gr.name as role_name,  gm.subject_source as subject_source_id,  gm.subject_id,  gaaa.name as action, gadn.name as attribute_def_name_name,  gadn.display_name as attribute_def_name_disp_name,  gr.display_name as role_display_name,  gaa.attribute_assign_delegatable, gaa.enabled, gaa.enabled_time, gaa.disabled_time, gr.id as role_id,  gadn.attribute_def_id,  gm.id as member_id,  gadn.id as attribute_def_name_id,  gaaa.id as action_id from grouper_groups gr,  grouper_memberships_all_v gmav,  grouper_members gm,  grouper_fields gf,  grouper_attribute_assign gaa,  grouper_attribute_def_name gadn,  grouper_attribute_def_name_set gadns,  grouper_attr_assign_action gaaa, grouper_attr_assign_action_set gaaas where gr.type_of_group = 'role'  and gmav.owner_group_id = gr.id  and gmav.field_id = gf.id  and gmav.owner_group_id = gaa.owner_group_id  and gmav.member_id = gaa.owner_member_id  and gf.type = 'list'  and gf.name = 'members'  and gmav.immediate_mship_enabled = 'T'  and gmav.member_id = gm.id  and gaa.attribute_assign_type = 'any_mem' and gaa.attribute_def_name_id = gadns.if_has_attribute_def_name_id  and gadn.id = gadns.then_has_attribute_def_name_id and gaa.attribute_assign_action_id = gaaas.if_has_attr_assn_action_id and gaaa.id = gaaas.then_has_attr_assn_action_id ;

CREATE VIEW grouper_perms_all_v (role_name, subject_source_id, subject_id, action, attribute_def_name_name, attribute_def_name_disp_name, role_display_name, attribute_assign_delegatable, enabled, enabled_time, disabled_time, role_id, attribute_def_id, member_id, attribute_def_name_id, action_id) AS select role_name,  subject_source_id,  subject_id,  action,  attribute_def_name_name,  attribute_def_name_disp_name,  role_display_name,  attribute_assign_delegatable, enabled, enabled_time, disabled_time, role_id,  attribute_def_id,  member_id,  attribute_def_name_id,  action_id from grouper_perms_role_v  union  select role_name,  subject_source_id,  subject_id,  action,  attribute_def_name_name,  attribute_def_name_disp_name,  role_display_name,  attribute_assign_delegatable, enabled, enabled_time, disabled_time, role_id,  attribute_def_id,  member_id,  attribute_def_name_id,  action_id from grouper_perms_role_subject_v  ;

CREATE TABLE subject
(
    subjectId VARCHAR(255) NOT NULL,
    subjectTypeId VARCHAR(32) NOT NULL,
    name VARCHAR(255) NULL,
    PRIMARY KEY (subjectId)
);

CREATE TABLE subjectattribute
(
    subjectId VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    value VARCHAR(255) NOT NULL,
    searchValue VARCHAR(255) NULL,
    PRIMARY KEY (subjectId, name, value)
);

CREATE INDEX searchattribute_value_idx ON subjectattribute (value);

CREATE UNIQUE INDEX searchattribute_id_name_idx ON subjectattribute (subjectId, name);

CREATE INDEX searchattribute_name_idx ON subjectattribute (name);


insert into grouper_ddl (id, object_name, db_version, last_updated, history) values ('1f84de867ab549498486cd7485acf249', 'Subject', 1, '2010/04/06 13:53:28', 
'2010/04/06 13:53:28: upgrade Subject from V0 to V1, ');
commit;

ALTER TABLE subjectattribute
    ADD CONSTRAINT fk_subjectattr_subjectid FOREIGN KEY (subjectId) REFERENCES subject (subjectId);
