FAS DB -- domain change

[Karel Zak]

On Mon, Apr 09, 2007 at 06:14:18PM -0700, Toshio Kuratomi wrote:
> Currently, the FAS DB has a field for domains::
> 
> CREATE TABLE role (
>   person_id INTEGER NOT NULL REFERENCES person (id),

  ON DELETE ??? ON UPDATE ???

 :-)

>   project_group_id INTEGER NOT NULL REFERENCES project_group (id),
>   --- role_domain would be relevant for limiting the person to a
> specific portion of the project,
>   --- e.g. '/rpms/util-linux'
>   [...]
>   role_domain VARCHAR(80),
> 
>   UNIQUE (person_id, project_group_id, role_domain)
> );
> 
> The intention of this definition is for a person to belong to a group
> within a domain no more than once.  However, it doesn't work out that
> way.  The value of role_domain is allowed to be null in the above
> definition.  Among SQL databases, Null is the absence of a value and
> postgres (among other databases) takes this to mean that a Null value
> has no effect on the unique clause.  So it's legal to have several
> entries for the same person_id and project_group_id  as long as the
> role_domain is null.  Additionally, a role_domain of "" (empty string)
> and Null are different values as well.
> 
> These issues are causing us to see some users having duplicate groups
> defined in the FAS.  In order to fix this, I would like to change the
> database schema to make role_domain non-nullable.  Concurrently, I'll be
> converting all roles where a group is defined as null into a group where
> the group is defined as "" and merging any that have been duplicated.
> This fix also requires auditing the FAS1 code for places where
> role_domain is defined as Null and changing them to "".

 Why there is not a role_domain table and reference to this table
 rather than the VARCHAR(80).

    Karel


-- 
 Karel Zak  <kzak at redhat.com>