[Buildd-tools-devel] RFC: Creation of a PostgreSQL database schema for wanna-build data

[Roger Leigh]

Dear all,

Over the last few weeks I, with the help of HE and others on
#debian-buildd, have been developing a PostgreSQL schema[1] for
wanna build[2].  The intent is to replace the existing setup of
multiple MLDBM databases with an extensible and flexible
relational database for use both by wanna-build and other
tools such as web CGI scripts.  The schema has been based upon
the needs of the current wanna-build code, but is rather
different to the old hash db being inspired in part from the
projectb schema used by dak, but is geared to the needs of
the buildds rather than managing the archive.

Compared with MLDBM:
- There is just one database, rather than one per arch
- Tracking of users is not hacked in
- Logging of database operations is built in
- A full history is kept, rather than just current and
  last state change.
- The whole database is intended to have full relational
  integrity and should be in 3NF.

We would be grateful for any comments you might have, particularly
if you have used the existing wanna-build and can see any defects,
omissions, or any suggestions for anything we missed or could
implement in a more optimal fashion.  If you are a PostgreSQL guru,
a review of the PL/pgSQL and PL/Perl functions and triggers used
for custom constraint checking would be much appreciated.

An ER diagram for the schema is attached, with the code available
at [1].


Thanks,
Roger


[1] http://git.debian.org/?p=users/rleigh/sbuild.git;a=tree;f=db;h=26f19b8aef033bc07fdba1c33fa32c80b78ce468;hb=38dd87a21b925eca2c936adb9530530b1854cd07
[2] http://git.debian.org/?p=buildd-tools/sbuild.git;a=summary

-- 
  .''`.  Roger Leigh
 : :' :  Debian GNU/Linux             http://people.debian.org/~rleigh/
 `. `'   Printing on GNU/Linux?       http://gutenprint.sourceforge.net/
   `-    GPG Public Key: 0x25BFB848   Please GPG sign your mail.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sbuild-packages.svgz
Type: image/svg+xml
Size: 5345 bytes
Desc: not available
Url : http://lists.alioth.debian.org/pipermail/buildd-tools-devel/attachments/20090117/fd025cb1/attachment.svgz 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 197 bytes
Desc: Digital signature
Url : http://lists.alioth.debian.org/pipermail/buildd-tools-devel/attachments/20090117/fd025cb1/attachment.pgp