Re: RFC: Creation of a PostgreSQL database schema for wanna-build data

[Peter Palfrader <weasel@debian.org>]

On Sun, 25 Jan 2009, Roger Leigh wrote:

> >    On IRC, there was talk about periodically pruning the sources table,
> >    and hence pruning the history on build_jobs as well. I think sources
> >    should always be prunned as soon as a version is no longer present in
> >    any of the distributions

I agree here.  Throw out stuff that is no longer current immediately.


> One question I do have is exactly what we want to store in build_state_history?
> Would it be a subset or superset of what's in build_jobs?

If you decide build_state_history is important (and I'm not arguing
against it here), then what you could do is this:

The build_state table always contains the current information, and just
the current information.  All the attributes that relate the information
to stuff in other tables are protected by referencial integrity etc.

Whenever there is an insert or an update to the build_state table the
new information is duplicated with a trigger in the build_state_history
table, with any relevant information that is only available by following
references to other tables being duplicated right in the history table.

E.g. if (I know that isn't the design currently, but it gives the idea)
the build_state table had a field source_id that only referenced a
source package by a number then the history insert would lookup the
source package name and version and insert that into the history table.
-- 
                           |  .''`.  ** Debian GNU/Linux **
      Peter Palfrader      | : :' :      The  universal
 http://www.palfrader.org/ | `. `'      Operating System
                           |   `-    http://www.debian.org/