Proposed SQL schema rev1

Hey everyone,
	I have revised the schema I was working on to deal 
with dependencies better. I would appreciate any 
comments about this. Expansion on entities, 
additional entities, etc. In particular I would 
like to hear thoughts on the dependencies-provides 
relationship. If anyone has questions, please ask. 
I will post an SQL schema of this RSN. Here goes:


Entities Name(attribute,attribute,...) :

-Packages (name, version, release, epoch, 
description) : This is an individual rpm. Name, 
version, release, and epoch are the PK. epoch is 
an int8, the others are text fields. description 
is the package summary. Other rpm headers with a 
single value will go here as well (Vendor, Build 
date, license, etc). The email address of the 
packager can be FK'd here.

-Dependencies (dep_name, dep_ver, flags): (weak 
entity) A dependency of a package. 
name,version,release, and epoch are FK'd off 
packages. They are combined with dep_name to form 
the PK. dep_name and dep_ver are text entries from 
the RPM header. Flags will indicate what type of 
dependency. This could be an int4 like in the rpm 
headers.

-Provides (dep_name, dep_ver, flags): (weak 
entity) Something provided by the package. 
Essentially the same as dependencies.

-Package_layers (layer_name): This is analagous to 
a group of packages in the comps file from 
anaconda. layer_name is a text field. An example 
would be GNOME.

-Channels (channel_name): This is a package tree. 
channel_name is a text field.

-Package_files (arch, os, os_version, 
package_file, spec, source_package_file): This 
will point to the package file on the filesystem. 
pkg_name, pkg_version, pkg_release, and epoch are 
FK'd off packages. Arch indicates the 
architechture for the RPM. os and os_version 
indicate the OS and os_version. package_file and 
source package files are the location on the FS of 
the package. Spec could be either the spec file 
itself, or the location on the FS of the spec.

-Checksums (file, checksum, config): This contains 
the checksums for the files contained by the 
package. File is a full path for the file, 
checksum is a text string containing the md5 hash. 
Config is a boolean (to indicate if it is ok that 
the file failed a checksum at some later point).

-Packager (Name, email, gpg_key): This is the 
person who built the package. Their email address 
and name are stored here. The GPG key is the GPG 
or PGP key they use to sign packages.

-Devices (device_id,device_name) : This is a stub 
entity. I originally intended to tie this DB to 
another DB I work on, but I am prevented from 
releasing code to that system. The important piece 
is that each device (host) has a unique ID (and it 
should not be an IP address). I will use a 
sequence for it in my initial SQL implementation.


Relationships Entity(cardinality) <relationship> 
Entity(cardinality) [relation_attribute,...] :

Packages(1) <requires> Dependencies(N) : This 
indicates the package that carries the dependency. 
This is needed because dependencies is a weak entity.

Packages(1) <provides> Provides(N) : These are the 
things provided by a package. Similar to previous.

Dependencies(1) <solved by> Provides(N) 
[channel,preferred] : This relationship identifies 
which provides (read packages providing) will 
solve which dependencies. The relationship has two 
attributes, channel and preferred, and they go 
together. Preferred is an admin setable boolean 
indicating that this is the preferred way to solve 
this dependency. Channel allows you to set 
different preferred flags for different channels. 
This is a tertiary relationship, and I may cut the 
channels attribute in the first run for simplicity.

Channels(1) <works with> Channels(N) : This allows 
for subchannels to use higher channels to resolve 
dependecies within themselves. For now, this will 
be forced into a tree structure by not allowing 
two channels to each use the other for solving 
dependencies.

Devices(1) <accesses> Channels(N) [priority] : 
This is sort of a server side permissions thing. 
It says which hosts can set which channel. The 
priority flag is an integer that ranks the order 
in which a host goes through channels looking for 
updates (set by admin).

Packages(1) <conflicts> Packages(N) [version]: 
This establishes which the packages an rpm 
conflicts with. version carries optional version 
info on the conflict. This could be made a weak 
entity like dependencies and provides

Packages(1) <obsoletes> Packages(N) [version]: 
This establishes which packages an rpm obsoletes. 
Similar to previous.

Packages(1) <in> Package_files(N) : This 
relationship establishes which files contain the 
rpm. This is a 1->N because of the architecture 
and os attributes of package_files.

Packages_layer(N) <in> Channels(M) : This 
indicates what groups of packages form a channel. 
The same layer can be used by multiple channels, 
resulting in an N->M relation.

Package_layer(1) <has sublayer> package_layer(N) 
[Qualifier] : This allows groups of packages to be 
included by other groups of packages. The 
qualifier can optionally decide if the layer is 
used, dependent on some outside information (from 
current or the client).

Package_layer(1) <contains> Package(N) [Qualifier] 
: This establishes which packages form a package 
layer or group. The qualifier can optionally 
indicate if a package should be used, dependent on 
outside information (again from current or the 
client).

Checksums(N) <matches> Packages(1) : This probably 
should be to package_files. This establishes the 
package the checksums correspond to.

Packages(N) <installed on> Devices(M) 
[install_date, auto_update] : This is a stub for 
package tracking. auto_update can be set by the 
admin to prevent updates via up2date if desired 
for a particular package.


That is the schema I'm looking at in it's current 
form (no pun intended). Additionally, I will be 
providing a couple of triggers that can be used to 
provide strict dependency checking upon packages 
being inserted or added to a channel. The triggers 
will be in PgPLSQL.
	This schema will provide more controllable 
dependency resolution. This is done through 
channel priority + preferred tagging. The channel 
priority indicates which channels to search first 
for a resolution to a dependency. This is 
restricted by which channels the channel providing 
the package works with. Additionally, if a channel 
provides multiple resolutions to a dependency, the 
preferred tagging can be used to indicate which 
one to use.
	Please send questions and/or comments to the list. 
I've tried to normalize this and keep it very 
flexible. Later.