Package Database Schema v0.4

[Toshio Kuratomi]

Here's the latest version of the package DB schema.  Thanks to Karel,
Jeff, and Sopwith for the comments on the last version!  

Some of the things that still need to be worked on:
- I've added several triggers.  These need to be tested.

- The relationship between PackageBuild, PackageListing,
PackageBuildListing, and Package is ugly.
  * PackageListing tells that a Package is present in a Collection
  * PackageBuild is a specific build of a package (PackageId, EVR make
these records unique)
  * PackageBuildListing combines these two (as a Build may belong to
more than one Collection.)
  We want the PackageBuild to belong to one or more Collections that the
Package belongs to.  I'm currently using a trigger to try to enforce
that but I have the nagging feeling I've just designed something
wrong.  

- Package Groups (for collections) and Package Categories (for packages)
should now be possible.  Need to implement them.

- Review grant statements once we've finalized the tables we'll be
providing.

Here's a short ChangeLog:

Move the StatusCodes into their own table to make translations easier.
This involves several new tables:
 - StatusCode holds the status codes.
 - StatusCodeTranslation holds the translated strings for each status
code.  This is prefilled with the C translations.
 - *StatusCode are tables that hold a subset of the StatusCode table.
These are used in foreign key relations to limit the status codes that
can be used on those tables.  (For instance, Collection.status is a
foreign key of CollectionStatusCode.)
 - *LogStatusCode are tables that holds all the status codes plus other
statuses that belong to logs.  Since logs record status changes plus
"Added" and "Removed", these are generated from the StatusCode.

There is also a trigger to keep the *LogStatusCode tables in sync with
their *StatusCode tables.
(Thanks to jcollie for the idea to do this)

Branch: Make distTag and branchName unique values. (Thanks Karel)

Add on delete and on update clauses to all foreign keys. (Thanks Karel)

CollectionSet: Add a priority field to specify the search order when
overlaying collections. (Thanks f13)

Rename PackageVersion* to PackageBuild*

Trigger to make sure PackageBuildListing references PackageBuilds and
PackageListings with the same Package.

Restructure PackageACL, PersonPackageACL, and GroupPackageACL.  The ACL
list can now have one record for every ACL-Package combination.
*PackageACL tables add users and groups to the relevant ACL. (Thanks
Karel)

Trigger to make changing the acl field illegal.  This prevents possible
abuse.

Add a description field to Log for possible extra information.

Add some grant statements to give out permissions for pkgdbadmin to do
useful things in the db.

-Toshio
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pkgdb.sql
Type: text/x-vhdl
Size: 27580 bytes
Desc: not available
Url : http://lists.fedoraproject.org/pipermail/infrastructure/attachments/20061222/a8703710/attachment.bin 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://lists.fedoraproject.org/pipermail/infrastructure/attachments/20061222/a8703710/attachment-0001.bin