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
infrastructure@lists.fedoraproject.org