Grouper Developers Forum

[Tom Barton <>]

Chris Hyzer wrote:
> 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.

I'm not getting it. Could you more precisely describe what would become 
the primary key and any indices you'd maintain on these tables under #1 
and #2?

> 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:
> ... snip ...
>
> > 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

begin:vcard
fn:Tom Barton
n:Barton;Tom
org:University of Chicago;Networking Services & Information Technologies
email;internet:
title:Sr. Director for Integration
tel;work:+1 773 834 1700
version:2.1
end:vcard