Skip to Content.
Sympa Menu

grouper-dev - Re: [grouper-dev] reducing the uuid/id pair to identify a row in grouper tables

Subject: Grouper Developers Forum

List archive

Re: [grouper-dev] reducing the uuid/id pair to identify a row in grouper tables


Chronological Thread 
  • From: Tom Barton <>
  • To: Chris Hyzer <>
  • Cc: "" <>
  • Subject: Re: [grouper-dev] reducing the uuid/id pair to identify a row in grouper tables
  • Date: Thu, 31 Jul 2008 11:49:41 -0500

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




Archive powered by MHonArc 2.6.16.

Top of Page