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@xxxxxxxxxx>
