Grouper Developers Forum

[Chris Hyzer <>]

Let me quickly discuss something else, then I will answer your question.  :)

One of the reasons the ddl switch is more difficult than I had hoped is that 
ddlutils is very idealistic in that it needs to be operating on a normalized 
database.  The last thing you want to hear is more change requests from me, 
so, I am sorry to spring this on you.  I think our design would be fine 
otherwise, and I would have signed off on it originally, but with ddlutils, I 
suggest we make another tweak.  The goal is to have no unique constraints 
(but unique indexes are fine).  Ddlutils doesn't support unique constraints 
(and bad things happen).  The reason we need unique constraints is that some 
databases (e.g. postgres and oracle) will not allow a foreign key on a col 
which is not a primary key or unique constraint.  So if we normalize (where 
all foreign keys are based on primary key) we wont have to worry about it.  
The suggestion I have to finish up the uuid/id conversion [the point is to 
remove unneeded keys] is:

1. Drop the field type from memberships, and in grouper_fields, drop the id 
and have the name as the primary key.  Leave grouper_attributes as is.  
<should be lowest impact solution>  This works since field name is unique, so 
from the name you can derive type.
-or-
2. in grouper memberships, drop the cols for list name and type, and add the 
field uuid.  In grouper_attributes, drop the field name and add field uuid.

The downside to #2 is the db is less easily human readable (though I don't 
think it is human readable now, and the views will address this).  #1 is more 
consistent with our use of uuids.  Though for enum type rows like these I 
think it is less important (especially since we don't rename them).  If we do 
either option, fixing the postgres problems (and oracle problems below) with 
ddl will be easier (and other dbs).  Can we discuss this on the next call?  I 
will try to make it since this is critical path for me...

Anyways, hope you made it this far, to answer your question, I ran the 
upgrade script on a slow oracle database with 1 million memberships, 100k 
groups, 100k stems, etc.  It took 1.5 hours (the main perf bottleneck is the 
update on the memberships table setting id's).  I think on a prod system, or 
with fewer rows, it will be speedier, but plan for an hour or two or 
whatever.  I did have to babysit it as some of the old constraints aren't 
named appropriately, here is my troubleshooting:


################# Troubleshooting begin #####################
ORA-01408: such column list already indexed
Details: CREATE UNIQUE INDEX type_name_idx ON GROUPER_TYPES (NAME)
>>> resolution, drop unique constraint on name, drop existing index, then run 
>>> again

Details: ALTER TABLE GROUPER_MEMBERSHIPS DROP COLUMN MEMBERSHIP_UUID
ORA-12991: column is referenced in a multi-column constraint

>>> resolution, drop multi-column constraint, then run again

Details: ALTER TABLE GROUPER_MEMBERSHIPS ADD CONSTRAINT 
fk_memberships_list_name_type
  FOREIGN KEY (LIST_NAME, LIST_TYPE) REFERENCES GROUPER_FIELDS (NAME, TYPE)
ORA-02270: no matching unique or primary key for this column-list

ORA-02429: cannot drop index used for enforcement of unique/primary key

>>> resolution, add a unique constraint on name and type in grouper fields
>>> ALTER TABLE AUTHZADM.GROUPER_FIELDS ADD  CONSTRAINT fields_name_type_unq
>>> UNIQUE (NAME, TYPE) ENABLE VALIDATE


Details: DROP INDEX SUBJECTATTRIBUTE_ID_NAME_IDX
ORA-02429: cannot drop index used for enforcement of unique/primary key
>>> this worked when i tried again


Details: ALTER TABLE SUBJECTATTRIBUTE ADD CONSTRAINT fk_subjectattr_subjectid 
FOREIGN KEY
   (SUBJECTID) REFERENCES SUBJECT (SUBJECTID)
ORA-02298: cannot validate (AUTHZADM.FK_SUBJECTATTR_SUBJECTID) - parent keys 
not found

>>> this worked when I tried again

################# Troubleshooting end #####################



Here is the script that I ran which is generated by starting grouper, or 
doing an "ant schemaexport":




##################### Script begin ####################

CREATE TABLE grouper_ddl
(
    id VARCHAR2(128) NOT NULL,
    object_name VARCHAR2(128),
    db_version INTEGER,
    last_updated VARCHAR2(50),
    history VARCHAR2(4000),
    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 ('a3200905-52c9-44ee-84bf-36693af9e3c1', 'Grouper', 1, '2008/07/30 
16:40:48',
'2008/07/30 16:40:48: upgrade Grouper from V0 to V1, ');
commit;

DROP INDEX COMPOSITE_UUID_IDX;

DROP INDEX MEMBER_UUID_IDX;

DROP INDEX MEMBERSHIP_UUID_IDX;

DROP INDEX STEM_DISPLAYEXTN_IDX;

DROP INDEX STEM_UUID_IDX;

ALTER TABLE GROUPER_ATTRIBUTES
    ADD hibernate_version_number NUMBER(38);

ALTER TABLE GROUPER_COMPOSITES
    ADD hibernate_version_number NUMBER(38);

ALTER TABLE GROUPER_FIELDS
    ADD hibernate_version_number NUMBER(38);

ALTER TABLE GROUPER_GROUPS
    ADD hibernate_version_number NUMBER(38);

ALTER TABLE GROUPER_GROUPS_TYPES
    ADD hibernate_version_number NUMBER(38);

ALTER TABLE GROUPER_MEMBERS
    ADD hibernate_version_number NUMBER(38);

ALTER TABLE GROUPER_MEMBERSHIPS
    ADD hibernate_version_number NUMBER(38);

ALTER TABLE GROUPER_SESSIONS
    ADD hibernate_version_number NUMBER(38);

ALTER TABLE GROUPER_STEMS
    ADD hibernate_version_number NUMBER(38);

ALTER TABLE GROUPER_TYPES
    ADD hibernate_version_number NUMBER(38);

CREATE UNIQUE INDEX attribute_uniq_idx ON GROUPER_ATTRIBUTES (GROUP_ID, 
FIELD_NAME);

CREATE INDEX attribute_field_value_idx ON GROUPER_ATTRIBUTES (FIELD_NAME, 
VALUE);

CREATE UNIQUE INDEX composite_uuid_idx ON GROUPER_COMPOSITES (UUID);

CREATE INDEX composite_left_factor_idx ON GROUPER_COMPOSITES (LEFT_FACTOR);

CREATE INDEX composite_right_factor_idx ON GROUPER_COMPOSITES (RIGHT_FACTOR);

CREATE UNIQUE INDEX field_uuid_idx ON GROUPER_FIELDS (FIELD_UUID);

CREATE UNIQUE INDEX name_and_type ON GROUPER_FIELDS (NAME, TYPE);

CREATE UNIQUE INDEX grouptypetyple_grouptype_idx ON GROUPER_GROUPS_TYPES 
(GROUP_UUID, TYPE_UUID);

CREATE UNIQUE INDEX member_uuid_idx ON GROUPER_MEMBERS (MEMBER_UUID);

CREATE UNIQUE INDEX member_subjectsourcetype_idx ON GROUPER_MEMBERS 
(SUBJECT_ID, SUBJECT_SOURCE, SUBJECT_TYPE);

CREATE UNIQUE INDEX membership_uuid_idx ON GROUPER_MEMBERSHIPS 
(MEMBERSHIP_UUID);

CREATE INDEX membership_member_list_idx ON GROUPER_MEMBERSHIPS (MEMBER_ID, 
LIST_NAME, LIST_TYPE);

CREATE INDEX membership_member_via_idx ON GROUPER_MEMBERSHIPS (MEMBER_ID, 
VIA_ID);

CREATE INDEX membership_owner_list_type_idx ON GROUPER_MEMBERSHIPS (OWNER_ID, 
LIST_NAME, LIST_TYPE, MSHIP_TYPE);

CREATE INDEX membership_owner_member_idx ON GROUPER_MEMBERSHIPS (OWNER_ID, 
MEMBER_ID, LIST_NAME, LIST_TYPE, DEPTH);

CREATE UNIQUE INDEX session_uuid_idx ON GROUPER_SESSIONS (SESSION_UUID);

CREATE INDEX session_member_idx ON GROUPER_SESSIONS (MEMBER_ID);

CREATE UNIQUE INDEX stem_uuid_idx ON GROUPER_STEMS (UUID);

CREATE INDEX stem_dislpayextn_idx ON GROUPER_STEMS (DISPLAY_EXTENSION);

CREATE UNIQUE INDEX type_uuid_idx ON GROUPER_TYPES (TYPE_UUID);

CREATE UNIQUE INDEX type_name_idx ON GROUPER_TYPES (NAME);

update grouper_ddl set db_version = 2, last_updated = '2008/07/30 16:40:50',
history = '2008/07/30 16:40:50: upgrade Grouper from V1 to V2, 2008/07/30 
16:40:48: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;

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

CREATE INDEX grouper_loader_job_name_idx ON grouper_loader_log (job_name);

update grouper_ddl set db_version = 3, last_updated = '2008/07/30 16:40:54',
history = '2008/07/30 16:40:54: upgrade Grouper from V2 to V3, 2008/07/30 
16:40:50: upgrade Grouper from V1 to V2, 2008/07/30 16:40:48: upgrade Grouper 
from V0 to V1, ' where object_name = 'Grouper';
commit;

update grouper_ddl set db_version = 4, last_updated = '2008/07/30 16:40:56',
history = '2008/07/30 16:40:56: upgrade Grouper from V3 to V4, 2008/07/30 
16:40:54: upgrade Grouper from V2 to V3, 2008/07/30 16:40:50: upgrade Grouper 
from V1 to V2, 2008/07/30 16:40:48: upgrade Grouper from V0 to V1, ' where 
object_name = 'Grouper';
commit;

ALTER TABLE GROUPER_COMPOSITES
    ADD old_id VARCHAR2(128);

ALTER TABLE GROUPER_COMPOSITES
    ADD old_uuid VARCHAR2(128);

ALTER TABLE GROUPER_FIELDS
    ADD old_id VARCHAR2(128);

ALTER TABLE GROUPER_FIELDS
    ADD old_field_uuid VARCHAR2(128);

ALTER TABLE GROUPER_GROUPS
    ADD old_id VARCHAR2(128);

ALTER TABLE GROUPER_GROUPS
    ADD old_uuid VARCHAR2(128);

ALTER TABLE GROUPER_MEMBERS
    ADD old_id VARCHAR2(128);

ALTER TABLE GROUPER_MEMBERS
    ADD old_member_uuid VARCHAR2(128);

ALTER TABLE GROUPER_MEMBERSHIPS
    ADD old_id VARCHAR2(128);

ALTER TABLE GROUPER_MEMBERSHIPS
    ADD old_membership_uuid VARCHAR2(128);

ALTER TABLE GROUPER_SESSIONS
    ADD old_id VARCHAR2(128);

ALTER TABLE GROUPER_SESSIONS
    ADD old_session_uuid VARCHAR2(128);

ALTER TABLE GROUPER_STEMS
    ADD old_id VARCHAR2(128);

ALTER TABLE GROUPER_STEMS
    ADD old_uuid VARCHAR2(128);

ALTER TABLE GROUPER_TYPES
    ADD old_id VARCHAR2(128);

ALTER TABLE GROUPER_TYPES
    ADD old_type_uuid VARCHAR2(128);
update grouper_composites set old_id = id, id = uuid, old_uuid = uuid where 
uuid is not null;
commit;
update grouper_memberships set old_id = id, id = membership_uuid, 
old_membership_uuid = membership_uuid where membership_uuid is not null;
commit;
update grouper_fields set old_id = id, id = field_uuid, old_field_uuid = 
field_uuid where field_uuid is not null;
commit;
update grouper_groups set old_id = id, id = uuid, old_uuid = uuid where uuid 
is not null;
commit;
update grouper_members set old_id = id, id = member_uuid, old_member_uuid = 
member_uuid where member_uuid is not null;
commit;
update grouper_sessions set old_id = id, id = session_uuid, old_session_uuid 
= session_uuid where session_uuid is not null;
commit;
update grouper_stems set old_id = id, id = uuid, old_uuid = uuid where uuid 
is not null;
commit;
update grouper_types set old_id = id, id = type_uuid, old_type_uuid = 
type_uuid where type_uuid is not null;
commit;


update grouper_ddl set db_version = 5, last_updated = '2008/07/30 16:40:58',
history = '2008/07/30 16:40:58: upgrade Grouper from V4 to V5, 2008/07/30 
16:40:56: upgrade Grouper from V3 to V4, 2008/07/30 16:40:54: upgrade Grouper 
from V2 to V3, 2008/07/30 16:40:50: upgrade Grouper from V1 to V2, 2008/07/30 
16:40:48: upgrade Grouper from V0 to V1, ' where object_name = 'Grouper';
commit;

DROP INDEX composite_uuid_idx;

DROP INDEX field_uuid_idx;

DROP INDEX GROUP_UUID_IDX;

DROP INDEX member_uuid_idx;

DROP INDEX membership_uuid_idx;

DROP INDEX session_uuid_idx;

DROP INDEX stem_uuid_idx;

DROP INDEX type_uuid_idx;

ALTER TABLE GROUPER_COMPOSITES
    DROP COLUMN UUID;

ALTER TABLE GROUPER_FIELDS
    DROP COLUMN FIELD_UUID;

ALTER TABLE GROUPER_GROUPS
    DROP COLUMN UUID;

ALTER TABLE GROUPER_MEMBERS
    DROP COLUMN MEMBER_UUID;

ALTER TABLE GROUPER_MEMBERSHIPS
    DROP COLUMN MEMBERSHIP_UUID;

ALTER TABLE GROUPER_SESSIONS
    DROP COLUMN SESSION_UUID;

ALTER TABLE GROUPER_STEMS
    DROP COLUMN UUID;

ALTER TABLE GROUPER_TYPES
    DROP COLUMN TYPE_UUID;
commit;


update grouper_ddl set db_version = 6, last_updated = '2008/07/30 16:41:02',
history = '2008/07/30 16:41:02: upgrade Grouper from V5 to V6, 2008/07/30 
16:40:58: upgrade Grouper from V4 to V5, 2008/07/30 16:40:56: upgrade Grouper 
from V3 to V4, 2008/07/30 16:40:54: upgrade Grouper from V2 to V3, 2008/07/30 
16:40:50: upgrade Grouper from V1 to V2, 2008/07/30 16:40:48: upgrade Grouper 
from V0 to V1, ' where object_name = 'Grouper';
commit;


commit;


update grouper_ddl set db_version = 7, last_updated = '2008/07/30 16:41:06',
history = '2008/07/30 16:41:06: upgrade Grouper from V6 to V7, 2008/07/30 
16:41:02: upgrade Grouper from V5 to V6, 2008/07/30 16:40:58: upgrade Grouper 
from V4 to V5, 2008/07/30 16:40:56: upgrade Grouper from V3 to V4, 2008/07/30 
16:40:54: upgrade Grouper from V2 to V3, 2008/07/30 16:40:50: upgrade Grouper 
from V1 to V2, 2008/07/30 16:40:48: upgrade Grouper from V0 to V1, ' where 
object_name = 'Grouper';
commit;

ALTER TABLE GROUPER_ATTRIBUTES
    ADD CONSTRAINT fk_attributes_group_id FOREIGN KEY (GROUP_ID) REFERENCES 
GROUPER_GROUPS (ID);

ALTER TABLE GROUPER_ATTRIBUTES
    ADD CONSTRAINT fk_attributes_field_name FOREIGN KEY (FIELD_NAME) 
REFERENCES GROUPER_FIELDS (NAME);

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_memberships_list_name_type FOREIGN KEY (LIST_NAME, 
LIST_TYPE) REFERENCES GROUPER_FIELDS (NAME, TYPE);

ALTER TABLE GROUPER_MEMBERSHIPS
    ADD CONSTRAINT fk_memberships_parent FOREIGN KEY (PARENT_MEMBERSHIP) 
REFERENCES GROUPER_MEMBERSHIPS (ID);

ALTER TABLE GROUPER_MEMBERSHIPS
    ADD CONSTRAINT fk_memberships_creator_id FOREIGN KEY (CREATOR_ID) 
REFERENCES GROUPER_MEMBERS (ID);

ALTER TABLE GROUPER_SESSIONS
    ADD CONSTRAINT fk_sessions_member_id FOREIGN KEY (MEMBER_ID) REFERENCES 
GROUPER_MEMBERS (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);
DROP INDEX SUBJECTATTRIBUTE_ID_NAME_IDX;

DROP INDEX SUBJECTATTRIBUTEA_KEY_IDX;

ALTER TABLE SUBJECTATTRIBUTE
    DROP PRIMARY KEY;

ALTER TABLE SUBJECTATTRIBUTE
    ADD CONSTRAINT SUBJECTATTRIBUTE_PK 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 ('c676ed61-cf40-4c94-8d61-8ffb5955f9ac', 'Subject', 1, '2008/07/30 
16:41:09',
'2008/07/30 16:41:09: upgrade Subject from V0 to V1, ');
commit;

ALTER TABLE SUBJECTATTRIBUTE
    ADD CONSTRAINT fk_subjectattr_subjectid FOREIGN KEY (SUBJECTID) 
REFERENCES SUBJECT (SUBJECTID);

##################### Script end ####################


> -----Original Message-----
> From: Tom Barton 
> [mailto:]
> Sent: Wednesday, July 30, 2008 3:10 PM
> To: Chris Hyzer
> Cc: 
> 
> Subject: Re: [grouper-dev] reducing the uuid/id pair to identify a row
> in grouper tables
>
> Cool. Can you recommend a way that each site can calculate or
> anticipate the length of time it will take this script to run on their
> databases?
>
> Chris Hyzer wrote:
> > All ddlutils scripts will start with dropping all foreign keys, and
> > end with adding back