Re: Proposed SQL schema rev1

On Thu, 2002-01-31 at 18:53, Todd Nemanich wrote:
> 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.

You need a string to point to both the rpm on the filesystem and a
string to point to the header on the filesystem.

Don't put anything in the database (yet) that we don't need. email, etc.
I want the db code as small/simple as possible first go round.

> -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. 

Obsoletes should occur in exactly the same way here 
(I know obsoletes aren't in current yet, breaking one of my own rules,
but hey, thats what maintainers do. Since I think I know what to do for 
obsoletes, please add them to the db design)

> -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.

I can't see where this would be used at all. "Group"ing of packages in
RHN is strictly by channel and sub-channel. What feature(s) would this
support?


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

You need arch family (i386 is the arch family for i386, i486, i686,
etc), and in the future, a "parent channel" field for sub-channels to
indicate who the parent channel is.

> 
> -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.

OH, I see. You seperated the concept of a package from a package file.
You meant to reduce the number of packages by letting multiple "package
files" share the same deps/provides, didn't you? 

Thats a good idea, but tragically won't work. Glibc i586 provides stuff
that glibc i386 doesn't. 

Please roll this into the "package" above. Having the src rpm here is
good, but leave out the spec file. Current won't use it.

> 
> -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).

What would this provide? RHN itself never cares what the "interior"
checksums for a package are. In fact, it lets the network layer worry
about transmission problems. What feature would this provide?

> -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.

Future. I could see this being useful, but not for the first design
please.

> -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.

In Current, packagedb.py and channel.py deal with rpms. In my play tree,
there is also a client.py (for computers) and admin.py (for users
[really admins] of those computers. 

For the shelve implementation, it was necessary to keep the full
packagedb at least conceptually seperate from individual channels. I'm 
perfectly willing to beleive that a sql backend doesn't need that
seperation. 

But, I want to continue to seperate "packages" from "clients". And in
the first sql implementation, I'd like to leave out the client parts
altogether. (Why? Makes it easier to test such a big change in the
backend, if we just change types of data-stores, and not whats in those
data stores. Then on the next release, we can add client/admin support.)
That might be incompatible with why everyone wants a sql backend - if
so, fine, BUT, make the client/admin document different. Please.

> 
> 
> 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.

Cut both, in the first run.

> 
> 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.

Perfect. We want to mirror how RHN does it, which is 
                       P
                  +----+----+
                  |    |    |
                  C    C    C 

Where a parent channel (P) may have any number of children (C), but no
child is the parent of anything, and there is no relationship between
children. (IE, a tree may never have a height of more than 1)
              
> 
> 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).

See above.

> 
> 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.

Oh. Ok, here's where you do conflicts and obsoletes .Cool.

> 
> 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.

See above for why this might not work.

> 
> 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.

Again, I'm not sure what a "layer" is in RHN.

> 
> 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.

Make sure this strictness can be turned on or off - it is often the case
that you want package trees which are incomplete: IE not every package
has every dependency satisfied in the tree. This occurs in the real
world for odd technical or political reasons. 

> 	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.
> 
> _______________________________________________
> Current-server mailing list
> [email protected]
> http://lists.dulug.duke.edu/mailman/listinfo/current-server
> 
> 
-- 
Hunter Matthews                          Unix / Network Administrator
Office: BioScience 145/244               Duke Univ. Biology Department
Key: F0F88438 / FFB5 34C0 B350 99A4 BB02  9779 A5DB 8B09 F0F8 8438
Never take candy from strangers. Especially on the internet.