FAS DB -- domain change

Toshio Kuratomi a.badger at gmail.com
Tue Apr 10 01:14:18 UTC 2007


Currently, the FAS DB has a field for domains::

CREATE TABLE role (
  person_id INTEGER NOT NULL REFERENCES person (id),
  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 "".

If no one objects, I'll try to get this done by Friday (although it's
turning into a busy week at work so I may have to push this off until
Monday.  I'll be sure to inform the list when the changes go live.)

-Toshio
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
URL: <http://listman.redhat.com/archives/fedora-infrastructure-list/attachments/20070409/6adf3dfb/attachment.sig>


More information about the Fedora-infrastructure-list mailing list