Merge lp:~michael.nelson/launchpad/db-changes-build-generalisation-new into lp:launchpad/db-devel

[Michael Nelson]

This branch is the schema change required for:

https://blueprints.edge.launchpad.net/soyuz/+spec/build-generalisation
https://dev.launchpad.net/LEP/GeneralBuildHistories

A slightly out-of-date visual for the schema change can be seen here:
http://people.ubuntu.com/~wgrant/launchpad/buildfarm/new-build-model-again.png

Overview
========
This schema change splits the current Build table into three: BuildFarmJob, PackageBuild and BinaryPackageBuild, and then migrates the data across. It also updates foreign key references, and some old views that are still used by the codebase to use the new tables.

I did chat with stub about this and he said with only a few hundred thousand records, performance isn't an issue, but it would be good to get him to confirm that after reading through the patch (we'll also test it on dogfood).

There is a pipeline of over 7k lines of code changes that need to land with this that ensures all the tests pass. It's all approved, currently ending with:

https://code.edge.launchpad.net/~michael.nelson/launchpad/567922-binarypackagebuild-new-table-8/+merge/25515

I just need to merge those changes with a fresh db-devel and resolve conflicts, before getting this on dogfood for some serious testing.

[Michael Nelson (michael.nelson)]

Hi stub or BjornT:

When I ran this schema change (together with the code changes) through ec2 test, I see the following error in test_sampledata - it seems related to moving the old build table to the todrop namespace rather than deleting it leaves the constraints in place - I assume I just remove the constraints on todrop.build?

http://pastebin.ubuntu.com/436693/

Also, there are a bunch of other failures:

http://pastebin.ubuntu.com/436699/

which are (I assume) because listReferences(cursor(), 'libraryfilealias', 'id') still returns 'build' as a from table?
http://pastebin.ubuntu.com/436710/

[Michael Nelson (michael.nelson)]

Never mind... dropping the constraints on todrop.build worked.

[Stuart Bishop (stub)]

Yes, you need to manually drop the constraints in the DB patch I'm afraid.

[Stuart Bishop (stub)]

This looks great. Comments, naming standards, indexes - all pretty good.

+CREATE TABLE BinaryPackageBuild (
+ id serial PRIMARY KEY,
+ package_build integer NOT NULL CONSTRAINT binarypackagebuild__package_build__fk REFERENCES packagebuild,
+ distro_arch_series integer NOT NULL CONSTRAINT binarypackagebuild__distro_arch_series__fk REFERENCES distroarchseries,
+ source_package_release integer NOT NULL CONSTRAINT binarypackagebuild__source_package_release__fk REFERENCES sourcepackagerelease

I believe Sourcepackage is one word in Launchpad's vocabulary, so that last column should be sourcepackage_release. Confirm with Bjorn.

+CREATE TABLE BinaryPackageBuild (
+ id serial PRIMARY KEY,
+ package_build integer NOT NULL CONSTRAINT binarypackagebuild__package_build__fk REFERENCES packagebuild,
+ distro_arch_series integer NOT NULL CONSTRAINT binarypackagebuild__distro_arch_series__fk REFERENCES distroarchseries,
+ source_package_release integer NOT NULL CONSTRAINT binarypackagebuild__source_package_release__fk REFERENCES sourcepackagerelease
+);
+
+CREATE UNIQUE INDEX binarypackagebuild__package_build__idx ON binarypackagebuild(package_build);
+-- Indexes that we can no longer create:
+-- CREATE UNIQUE INDEX binarypackagebuild__distro_arch_series_uniq__idx ON binarypackagebuild(distro_arch_series, source_package_release, archive)
+-- CREATE INDEX binarypackagebuild__distro_arch_series__status__idx ON binarypackagebuild(distro_arch_series, status?)
+-- CREATE INDEX binarypackagebuild__distro_arch_series__date_finished ON binarypackagebuild(distro_arch_series, date_finished)
+CREATE INDEX binarypackagebuild__source_package_release_idx ON binarypackagebuild(source_package_release);

We might need an index on distro_arch_series too

CREATE INDEX binarypackagebuild__distro_arch_series__idx ON BinaryPackageBuild(distro_arch_series);

+-- Step 2
+-- Migrate the current data from the build table to the newly created
+-- relationships.
+CREATE OR REPLACE FUNCTION migrate_build_rows() RETURNS integer
+LANGUAGE plpgsql AS
+$$
+DECLARE
+ build_info RECORD;
+ rows_migrated integer;
+ buildfarmjob_id integer;
+ packagebuild_id integer;
+BEGIN
+ rows_migrated := 0;
+ FOR build_info IN
+ SELECT
+ build.id,
+ build.processor,
+ archive.require_virtualized AS virtualized,
+ -- Currently we do not know if a build was virtual or not? (it's
+ -- only on the archive and the builder, both of which can
+ -- change). IBuild.is_virtualized just queries the archive.
+ build.datecreated AS date_created,
+ (build.datebuilt - build.buildduration) AS date_started,
+ build.datebuilt AS date_finished,
+ build.date_first_dispatched,
+ build.builder,
+ build.buildstate AS status,
+ build.buildlog AS log,
+ build.archive,
+ build.pocket,
+ build.upload_log,
+ build.dependencies,
+ build.distroarchseries AS distro_arch_series,
+ build.sourcepackagerelease AS source_package_release,
+ build.build_warnings -- We don't seem to use this in LP code at all?
+ FROM
+ build JOIN archive ON build.archive = archive.id

We have to have 'ORDER BY Build.id' at the end here. Any data migration in a DB patch is run individually on each database replica. We have to guarantee that it will run iden...

review: Approve (db)

[Michael Nelson (michael.nelson)]

On Thu, May 20, 2010 at 4:29 PM, Stuart Bishop
<email address hidden> wrote:
> Review: Approve db
> This looks great. Comments, naming standards, indexes - all pretty good.

Thanks Stuart.

>
>
> +CREATE TABLE BinaryPackageBuild (
> + id serial PRIMARY KEY,
> + package_build integer NOT NULL CONSTRAINT binarypackagebuild__package_build__fk REFERENCES packagebuild,
> + distro_arch_series integer NOT NULL CONSTRAINT binarypackagebuild__distro_arch_series__fk REFERENCES distroarchseries,
> + source_package_release integer NOT NULL CONSTRAINT binarypackagebuild__source_package_release__fk REFERENCES sourcepackagerelease
>
> I believe Sourcepackage is one word in Launchpad's vocabulary, so that last column should be sourcepackage_release. Confirm with Bjorn.

The current devel code has sourcepackagerelease (as well as lots of
other non-underscored vars seen in the schema change - datecreated,
buildlog, etc.), and part of this change is to start using underscores
consistently.

Grepping for "source_package" in the tip of db-devel has 1113 hits
(skip over the wadl ;) ) for variable names "source_package" in
registry, as well as things like "source_package_name",
"source_package_format" etc. (admittedly, there are many more for
"sourcepackage", due to the old non-underscored variable and column
names (sourcepackagename, sourcepackagerelease etc.)

Since no one commented on the suggestion in the diagram at:

http://people.ubuntu.com/~wgrant/launchpad/buildfarm/new-build-model-again.png

I've gone ahead and code source_package_release through the pipeline
of changes. Bjorn, if you feel strongly about it, I can change the
schema and the code (or just change the schema and field definition
for the moment?), but I'd prefer to leave it for the moment, and if
you'd like the change, do a separate branch later to standardise
(we'll need to do this anyway as we've lots of other classes that
still use names without any underscores).

>
>
> +CREATE TABLE BinaryPackageBuild (
> + id serial PRIMARY KEY,
> + package_build integer NOT NULL CONSTRAINT binarypackagebuild__package_build__fk REFERENCES packagebuild,
> + distro_arch_series integer NOT NULL CONSTRAINT binarypackagebuild__distro_arch_series__fk REFERENCES distroarchseries,
> + source_package_release integer NOT NULL CONSTRAINT binarypackagebuild__source_package_release__fk REFERENCES sourcepackagerelease
> +);
> +
> +CREATE UNIQUE INDEX binarypackagebuild__package_build__idx ON binarypackagebuild(package_build);
> +-- Indexes that we can no longer create:
> +-- CREATE UNIQUE INDEX binarypackagebuild__distro_arch_series_uniq__idx ON binarypackagebuild(distro_arch_series, source_package_release, archive)
> +-- CREATE INDEX binarypackagebuild__distro_arch_series__status__idx ON binarypackagebuild(distro_arch_series, status?)
> +-- CREATE INDEX binarypackagebuild__distro_arch_series__date_finished ON binarypackagebuild(distro_arch_series, date_finished)
> +CREATE INDEX binarypackagebuild__source_package_release_idx ON binarypackagebuild(source_package_release);
>
> We might need an index on distro_arch_series too
>
> CREATE INDEX binarypackagebuild__distro_arch_series__idx ON BinaryPackageBuild(distro_a...

[Björn Tillenius (bjornt)]

The schema changes look good. I'm having a hard time following the migration part, but I trust that Stuart reviewed it, and that you test it on dogfood properly.

review: Approve (db)

[Michael Nelson (michael.nelson)]

Thanks Bjorn. I've another question for you both: the patch moves the old build table to todrop.build and deletes its constraints.

Should I have a contingency patch that (1) restores the old table, (2) restores its constraints (3) moves the new tables to the todrop namespace?

I'm kindof nervous about landing this as I don't see a way to back it out if need be (given the size etc.)

I'll get this tested today on dogfood.

[Stuart Bishop (stub)]

You could write a patch to do that now and probably waste your time, or you could write it if you actually need it. The trick will not be renaming the tables, but migrating updated data back.

[Michael Nelson (michael.nelson)]

{{{
launchpad@mawson:/srv/launchpad.net/codelines/current$ time PGUSER=postgres LPCONFIG=dogfood bin/py
./database/schema/upgrade.py -vv -p
2010-05-26 11:58:50 INFO Applying patches to unreplicated environment.
2010-05-26 11:58:50 INFO Applying trusted.sql
2010-05-26 11:58:50 DEBUG Committing changes
2010-05-26 11:58:50 DEBUG Found patch 2207.57.0 -- ./database/schema/patch-2207-57-0.sql
2010-05-26 11:58:50 INFO Applying ./database/schema/patch-2207-57-0.sql
^[2010-05-26 13:41:57 DEBUG Committing changes
2010-05-26 13:41:57 INFO Applying comments.sql
2010-05-26 13:42:03 DEBUG Committing changes
2010-05-26 13:42:03 DEBUG Committing changes

real 103m14.459s
user 0m1.340s
sys 0m0.430s
launchpad@mawson
}}}

[Stuart Bishop (stub)]

Dogfood isn't great for timings. Staging timings are better.

If we think it is too slow, or we can't wait to land on staging to be sure, I'll need to refactor the migration.

[Michael Nelson (michael.nelson)]

{{{
11:01 < stub> noodles775: It took 17 minutes on staging, so maybe 35-40 mins to apply to production.
11:01 < noodles775> stub: thanks. I assume that's way too long? What is acceptable?
11:02 < stub> Thats up to the release manager and losas
11:02 < noodles775> OK.
11:10 < stub> noodles775: This version should be identical and takes 8 minutes, so 16-20 mins on production: http://paste.ubuntu.com/440285/
11:11 < noodles775> Thanks stub... I'll update and send it off to ec2... but at this time I don't think I'll be landing it.. there seem to be other issues with teh current db-devel on dogfood that need looking in to.
}}}

=== modified file 'database/sampledata/current-dev.sql'
--- database/sampledata/current-dev.sql	2010-04-28 13:58:04 +0000
+++ database/sampledata/current-dev.sql	2010-05-27 13:25:49 +0000
@@ -785,6 +785,12 @@
 
 
 
+
+
+
+
+
+
 SET SESSION AUTHORIZATION DEFAULT;
 
 ALTER TABLE account DISABLE TRIGGER ALL;
@@ -2512,21 +2518,6 @@
 ALTER TABLE authtoken ENABLE TRIGGER ALL;
 
 
-ALTER TABLE binarypackagename DISABLE TRIGGER ALL;
-
-INSERT INTO binarypackagename (id, name) VALUES (6, 'foobar');
-INSERT INTO binarypackagename (id, name) VALUES (8, 'mozilla-firefox');
-INSERT INTO binarypackagename (id, name) VALUES (13, 'pmount');
-INSERT INTO binarypackagename (id, name) VALUES (14, 'linux-2.6.12');
-INSERT INTO binarypackagename (id, name) VALUES (15, 'at');
-INSERT INTO binarypackagename (id, name) VALUES (16, 'cdrkit');
-INSERT INTO binarypackagename (id, name) VALUES (17, 'commercialpackage');
-INSERT INTO binarypackagename (id, name) VALUES (18, 'mozilla-firefox-data');
-
-
-ALTER TABLE binarypackagename ENABLE TRIGGER ALL;
-
-
 ALTER TABLE processor DISABLE TRIGGER ALL;
 
 INSERT INTO processor (id, family, name, title, description) VALUES (1, 1, '386', 'Intel 386', 'Intel 386 and its many derivatives and clones, the basic 32-bit chip in the x86 family');
@@ -2546,6 +2537,68 @@
 ALTER TABLE builder ENABLE TRIGGER ALL;
 
 
+ALTER TABLE buildfarmjob DISABLE TRIGGER ALL;
+
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (2, 1, false, '2004-09-27 11:57:13', '2004-09-27 11:55:13', '2004-09-27 11:57:14', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (6, 1, false, '2006-12-01 00:00:00', '2006-12-01 00:00:00', '2006-12-01 00:00:01', NULL, 1, 2, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (7, 1, false, '2005-03-24 00:00:00', '2005-03-24 23:58:43', '2005-03-25 00:00:03', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (8, 1, false, '2005-09-30 00:00:00', NULL, NULL, NULL, NULL, 6, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (9, 1, false, '2005-10-01 00:00:00', '2005-10-01 23:56:41', '2005-10-02 00:00:01', NULL, 1, 2, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (10, 1, false, '2006-01-27 00:00:00', NULL, NULL, NULL, NULL, 1, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (11, 1, false, '2006-02-14 00:00:00', NULL, NULL, NULL, NULL, 0, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (12, 1, false, '2006-02-28 00:00:00', '2006-02-27 23:53:59', '2006-02-28 00:00:01', NULL, 1, 3, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (13, 1, false, '2006-03-21 00:00:00', '2006-03-21 00:58:33', '2006-03-21 01:00:03', NULL, 1, 5, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (14, 1, false, '2006-03-22 00:00:00', '2006-03-21 00:58:32', '2006-03-21 01:00:02', NULL, 1, 5, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (15, 1, false, '2006-03-22 00:00:01', '2006-03-21 00:58:30', '2006-03-21 01:00:00', NULL, 1, 5, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (16, 1, false, '2005-03-24 00:00:01', '2005-03-24 23:58:42', '2005-03-25 00:00:02', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (18, 1, false, '2004-09-27 11:57:14', '2004-09-27 11:55:12', '2004-09-27 11:57:13', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (19, 1, false, '2005-03-24 00:00:02', '2005-03-24 23:58:41', '2005-03-25 00:00:01', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (21, 1, false, '2006-12-01 00:00:01', NULL, NULL, NULL, NULL, 2, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (22, 1, false, '2007-04-20 00:00:00', '2007-04-19 23:58:41', '2007-04-20 00:00:01', NULL, 1, 7, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (23, 1, false, '2006-04-11 12:00:00', NULL, NULL, NULL, NULL, 1, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (24, 1, true, '2007-05-30 00:00:00', '2007-05-29 23:58:41', '2007-05-30 00:00:01', NULL, 1, 2, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (25, 1, true, '2007-07-08 12:00:00', NULL, NULL, NULL, NULL, 1, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (26, 1, true, '2007-07-08 00:00:00', '2007-07-07 23:58:41', '2007-07-08 00:00:01', NULL, 1, 2, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (27, 1, true, '2007-07-24 00:00:00', '2007-07-23 23:58:41', '2007-07-24 00:00:01', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (28, 3, true, '2007-08-10 00:00:00', '2007-08-10 00:00:00', '2007-08-10 00:00:13', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (29, 1, false, '2007-08-09 21:54:18.553132', NULL, '2007-08-09 23:59:59', NULL, NULL, 1, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (30, 3, false, '2007-08-10 00:00:01', '2007-08-10 00:00:01', '2007-08-10 00:00:14', NULL, 1, 1, 1, 1);
+
+
+ALTER TABLE buildfarmjob ENABLE TRIGGER ALL;
+
+
+ALTER TABLE packagebuild DISABLE TRIGGER ALL;
+
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (1, 2, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (2, 6, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (3, 7, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (4, 8, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (5, 9, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (6, 10, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (7, 11, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (8, 12, 1, 0, NULL, 'cpp (>= 4:4.0.1-3), gcc-4.0 (>= 4.0.1-2)');
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (9, 13, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (10, 14, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (11, 15, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (12, 16, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (13, 18, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (14, 19, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (15, 21, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (16, 22, 1, 0, 91, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (17, 23, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (18, 24, 11, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (19, 25, 9, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (20, 26, 9, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (21, 27, 9, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (22, 28, 9, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (23, 29, 12, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (24, 30, 1, 0, NULL, NULL);
+
+
+ALTER TABLE packagebuild ENABLE TRIGGER ALL;
+
+
 ALTER TABLE section DISABLE TRIGGER ALL;
 
 INSERT INTO section (id, name) VALUES (1, 'base');
@@ -2681,35 +2734,50 @@
 ALTER TABLE sourcepackagerelease ENABLE TRIGGER ALL;
 
 
-ALTER TABLE build DISABLE TRIGGER ALL;
-
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (2, '2004-09-27 11:57:13', 1, 1, 1, '2004-09-27 11:57:14', '00:02:01', 1, 1, 14, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (6, '2006-12-01 00:00:00', 1, 1, 2, '2006-12-01 00:00:01', '00:00:01', 1, 1, 32, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (7, '2005-03-24 00:00:00', 1, 6, 1, '2005-03-25 00:00:03', '00:01:20', 1, 1, 20, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (8, '2005-09-30 00:00:00', 1, 6, 6, NULL, NULL, NULL, NULL, 14, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (9, '2005-10-01 00:00:00', 1, 1, 2, '2005-10-02 00:00:01', '00:03:20', 1, 1, 20, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (10, '2006-01-27 00:00:00', 1, 1, 1, NULL, NULL, NULL, NULL, 26, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (11, '2006-02-14 00:00:00', 1, 6, 0, NULL, NULL, NULL, NULL, 25, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (12, '2006-02-28 00:00:00', 1, 6, 3, '2006-02-28 00:00:01', '00:06:02', 1, 1, 27, 0, 'cpp (>= 4:4.0.1-3), gcc-4.0 (>= 4.0.1-2)', 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (13, '2006-03-21 00:00:00', 1, 1, 5, '2006-03-21 01:00:03', '00:01:30', 1, 1, 17, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (14, '2006-03-22 00:00:00', 1, 1, 5, '2006-03-21 01:00:02', '00:01:30', 1, 1, 28, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (15, '2006-03-22 00:00:01', 1, 1, 5, '2006-03-21 01:00:00', '00:01:30', 1, 1, 29, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (16, '2005-03-24 00:00:01', 1, 11, 1, '2005-03-25 00:00:02', '00:01:20', 1, 1, 20, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (18, '2004-09-27 11:57:14', 1, 8, 1, '2004-09-27 11:57:13', '00:02:01', 1, 1, 14, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (19, '2005-03-24 00:00:02', 1, 8, 1, '2005-03-25 00:00:01', '00:01:20', 1, 1, 20, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (21, '2006-12-01 00:00:01', 1, 1, 2, NULL, NULL, NULL, NULL, 33, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (22, '2007-04-20 00:00:00', 1, 8, 7, '2007-04-20 00:00:01', '00:01:20', 1, 1, 33, 0, NULL, 1, NULL, NULL, 91);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (23, '2006-04-11 12:00:00', 1, 1, 1, NULL, NULL, NULL, NULL, 35, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (24, '2007-05-30 00:00:00', 1, 1, 2, '2007-05-30 00:00:01', '00:01:20', 1, 1, 33, 0, NULL, 11, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (25, '2007-07-08 12:00:00', 1, 1, 1, NULL, NULL, NULL, NULL, 35, 0, NULL, 9, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (26, '2007-07-08 00:00:00', 1, 8, 2, '2007-07-08 00:00:01', '00:01:20', 1, 1, 33, 0, NULL, 9, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (27, '2007-07-24 00:00:00', 1, 1, 1, '2007-07-24 00:00:01', '00:01:20', 1, 1, 20, 0, NULL, 9, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (28, '2007-08-10 00:00:00', 3, 12, 1, '2007-08-10 00:00:13', '00:00:13', 1, 1, 14, 0, NULL, 9, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (29, '2007-08-09 21:54:18.553132', 1, 8, 1, '2007-08-09 23:59:59', NULL, NULL, NULL, 36, 0, NULL, 12, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (30, '2007-08-10 00:00:01', 3, 12, 1, '2007-08-10 00:00:14', '00:00:13', 1, 1, 14, 0, NULL, 1, NULL, NULL, NULL);
-
-
-ALTER TABLE build ENABLE TRIGGER ALL;
+ALTER TABLE binarypackagebuild DISABLE TRIGGER ALL;
+
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (2, 1, 1, 14);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (6, 2, 1, 32);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (7, 3, 6, 20);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (8, 4, 6, 14);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (9, 5, 1, 20);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (10, 6, 1, 26);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (11, 7, 6, 25);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (12, 8, 6, 27);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (13, 9, 1, 17);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (14, 10, 1, 28);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (15, 11, 1, 29);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (16, 12, 11, 20);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (18, 13, 8, 14);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (19, 14, 8, 20);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (21, 15, 1, 33);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (22, 16, 8, 33);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (23, 17, 1, 35);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (24, 18, 1, 33);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (25, 19, 1, 35);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (26, 20, 8, 33);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (27, 21, 1, 20);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (28, 22, 12, 14);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (29, 23, 8, 36);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (30, 24, 12, 14);
+
+
+ALTER TABLE binarypackagebuild ENABLE TRIGGER ALL;
+
+
+ALTER TABLE binarypackagename DISABLE TRIGGER ALL;
+
+INSERT INTO binarypackagename (id, name) VALUES (6, 'foobar');
+INSERT INTO binarypackagename (id, name) VALUES (8, 'mozilla-firefox');
+INSERT INTO binarypackagename (id, name) VALUES (13, 'pmount');
+INSERT INTO binarypackagename (id, name) VALUES (14, 'linux-2.6.12');
+INSERT INTO binarypackagename (id, name) VALUES (15, 'at');
+INSERT INTO binarypackagename (id, name) VALUES (16, 'cdrkit');
+INSERT INTO binarypackagename (id, name) VALUES (17, 'commercialpackage');
+INSERT INTO binarypackagename (id, name) VALUES (18, 'mozilla-firefox-data');
+
+
+ALTER TABLE binarypackagename ENABLE TRIGGER ALL;
 
 
 ALTER TABLE binarypackagerelease DISABLE TRIGGER ALL;

=== modified file 'database/sampledata/current.sql'
--- database/sampledata/current.sql	2010-04-28 13:58:04 +0000
+++ database/sampledata/current.sql	2010-05-27 13:25:49 +0000
@@ -785,6 +785,12 @@
 
 
 
+
+
+
+
+
+
 SET SESSION AUTHORIZATION DEFAULT;
 
 ALTER TABLE account DISABLE TRIGGER ALL;
@@ -2470,21 +2476,6 @@
 ALTER TABLE authtoken ENABLE TRIGGER ALL;
 
 
-ALTER TABLE binarypackagename DISABLE TRIGGER ALL;
-
-INSERT INTO binarypackagename (id, name) VALUES (6, 'foobar');
-INSERT INTO binarypackagename (id, name) VALUES (8, 'mozilla-firefox');
-INSERT INTO binarypackagename (id, name) VALUES (13, 'pmount');
-INSERT INTO binarypackagename (id, name) VALUES (14, 'linux-2.6.12');
-INSERT INTO binarypackagename (id, name) VALUES (15, 'at');
-INSERT INTO binarypackagename (id, name) VALUES (16, 'cdrkit');
-INSERT INTO binarypackagename (id, name) VALUES (17, 'commercialpackage');
-INSERT INTO binarypackagename (id, name) VALUES (18, 'mozilla-firefox-data');
-
-
-ALTER TABLE binarypackagename ENABLE TRIGGER ALL;
-
-
 ALTER TABLE processor DISABLE TRIGGER ALL;
 
 INSERT INTO processor (id, family, name, title, description) VALUES (1, 1, '386', 'Intel 386', 'Intel 386 and its many derivatives and clones, the basic 32-bit chip in the x86 family');
@@ -2504,6 +2495,68 @@
 ALTER TABLE builder ENABLE TRIGGER ALL;
 
 
+ALTER TABLE buildfarmjob DISABLE TRIGGER ALL;
+
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (2, 1, false, '2004-09-27 11:57:13', '2004-09-27 11:55:13', '2004-09-27 11:57:14', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (6, 1, false, '2006-12-01 00:00:00', '2006-12-01 00:00:00', '2006-12-01 00:00:01', NULL, 1, 2, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (7, 1, false, '2005-03-24 00:00:00', '2005-03-24 23:58:43', '2005-03-25 00:00:03', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (8, 1, false, '2005-09-30 00:00:00', NULL, NULL, NULL, NULL, 6, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (9, 1, false, '2005-10-01 00:00:00', '2005-10-01 23:56:41', '2005-10-02 00:00:01', NULL, 1, 2, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (10, 1, false, '2006-01-27 00:00:00', NULL, NULL, NULL, NULL, 1, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (11, 1, false, '2006-02-14 00:00:00', NULL, NULL, NULL, NULL, 0, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (12, 1, false, '2006-02-28 00:00:00', '2006-02-27 23:53:59', '2006-02-28 00:00:01', NULL, 1, 3, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (13, 1, false, '2006-03-21 00:00:00', '2006-03-21 00:58:33', '2006-03-21 01:00:03', NULL, 1, 5, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (14, 1, false, '2006-03-22 00:00:00', '2006-03-21 00:58:32', '2006-03-21 01:00:02', NULL, 1, 5, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (15, 1, false, '2006-03-22 00:00:01', '2006-03-21 00:58:30', '2006-03-21 01:00:00', NULL, 1, 5, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (16, 1, false, '2005-03-24 00:00:01', '2005-03-24 23:58:42', '2005-03-25 00:00:02', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (18, 1, false, '2004-09-27 11:57:14', '2004-09-27 11:55:12', '2004-09-27 11:57:13', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (19, 1, false, '2005-03-24 00:00:02', '2005-03-24 23:58:41', '2005-03-25 00:00:01', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (21, 1, false, '2006-12-01 00:00:01', NULL, NULL, NULL, NULL, 2, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (22, 1, false, '2007-04-20 00:00:00', '2007-04-19 23:58:41', '2007-04-20 00:00:01', NULL, 1, 7, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (23, 1, false, '2006-04-11 12:00:00', NULL, NULL, NULL, NULL, 1, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (24, 1, true, '2007-05-30 00:00:00', '2007-05-29 23:58:41', '2007-05-30 00:00:01', NULL, 1, 2, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (25, 1, true, '2007-07-08 12:00:00', NULL, NULL, NULL, NULL, 1, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (26, 1, true, '2007-07-08 00:00:00', '2007-07-07 23:58:41', '2007-07-08 00:00:01', NULL, 1, 2, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (27, 1, true, '2007-07-24 00:00:00', '2007-07-23 23:58:41', '2007-07-24 00:00:01', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (28, 3, true, '2007-08-10 00:00:00', '2007-08-10 00:00:00', '2007-08-10 00:00:13', NULL, 1, 1, 1, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (29, 1, false, '2007-08-09 21:54:18.553132', NULL, '2007-08-09 23:59:59', NULL, NULL, 1, NULL, 1);
+INSERT INTO buildfarmjob (id, processor, virtualized, date_created, date_started, date_finished, date_first_dispatched, builder, status, log, job_type) VALUES (30, 3, false, '2007-08-10 00:00:01', '2007-08-10 00:00:01', '2007-08-10 00:00:14', NULL, 1, 1, 1, 1);
+
+
+ALTER TABLE buildfarmjob ENABLE TRIGGER ALL;
+
+
+ALTER TABLE packagebuild DISABLE TRIGGER ALL;
+
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (1, 2, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (2, 6, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (3, 7, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (4, 8, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (5, 9, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (6, 10, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (7, 11, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (8, 12, 1, 0, NULL, 'cpp (>= 4:4.0.1-3), gcc-4.0 (>= 4.0.1-2)');
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (9, 13, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (10, 14, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (11, 15, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (12, 16, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (13, 18, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (14, 19, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (15, 21, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (16, 22, 1, 0, 91, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (17, 23, 1, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (18, 24, 11, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (19, 25, 9, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (20, 26, 9, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (21, 27, 9, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (22, 28, 9, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (23, 29, 12, 0, NULL, NULL);
+INSERT INTO packagebuild (id, build_farm_job, archive, pocket, upload_log, dependencies) VALUES (24, 30, 1, 0, NULL, NULL);
+
+
+ALTER TABLE packagebuild ENABLE TRIGGER ALL;
+
+
 ALTER TABLE section DISABLE TRIGGER ALL;
 
 INSERT INTO section (id, name) VALUES (1, 'base');
@@ -2639,35 +2692,50 @@
 ALTER TABLE sourcepackagerelease ENABLE TRIGGER ALL;
 
 
-ALTER TABLE build DISABLE TRIGGER ALL;
-
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (2, '2004-09-27 11:57:13', 1, 1, 1, '2004-09-27 11:57:14', '00:02:01', 1, 1, 14, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (6, '2006-12-01 00:00:00', 1, 1, 2, '2006-12-01 00:00:01', '00:00:01', 1, 1, 32, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (7, '2005-03-24 00:00:00', 1, 6, 1, '2005-03-25 00:00:03', '00:01:20', 1, 1, 20, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (8, '2005-09-30 00:00:00', 1, 6, 6, NULL, NULL, NULL, NULL, 14, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (9, '2005-10-01 00:00:00', 1, 1, 2, '2005-10-02 00:00:01', '00:03:20', 1, 1, 20, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (10, '2006-01-27 00:00:00', 1, 1, 1, NULL, NULL, NULL, NULL, 26, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (11, '2006-02-14 00:00:00', 1, 6, 0, NULL, NULL, NULL, NULL, 25, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (12, '2006-02-28 00:00:00', 1, 6, 3, '2006-02-28 00:00:01', '00:06:02', 1, 1, 27, 0, 'cpp (>= 4:4.0.1-3), gcc-4.0 (>= 4.0.1-2)', 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (13, '2006-03-21 00:00:00', 1, 1, 5, '2006-03-21 01:00:03', '00:01:30', 1, 1, 17, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (14, '2006-03-22 00:00:00', 1, 1, 5, '2006-03-21 01:00:02', '00:01:30', 1, 1, 28, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (15, '2006-03-22 00:00:01', 1, 1, 5, '2006-03-21 01:00:00', '00:01:30', 1, 1, 29, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (16, '2005-03-24 00:00:01', 1, 11, 1, '2005-03-25 00:00:02', '00:01:20', 1, 1, 20, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (18, '2004-09-27 11:57:14', 1, 8, 1, '2004-09-27 11:57:13', '00:02:01', 1, 1, 14, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (19, '2005-03-24 00:00:02', 1, 8, 1, '2005-03-25 00:00:01', '00:01:20', 1, 1, 20, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (21, '2006-12-01 00:00:01', 1, 1, 2, NULL, NULL, NULL, NULL, 33, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (22, '2007-04-20 00:00:00', 1, 8, 7, '2007-04-20 00:00:01', '00:01:20', 1, 1, 33, 0, NULL, 1, NULL, NULL, 91);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (23, '2006-04-11 12:00:00', 1, 1, 1, NULL, NULL, NULL, NULL, 35, 0, NULL, 1, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (24, '2007-05-30 00:00:00', 1, 1, 2, '2007-05-30 00:00:01', '00:01:20', 1, 1, 33, 0, NULL, 11, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (25, '2007-07-08 12:00:00', 1, 1, 1, NULL, NULL, NULL, NULL, 35, 0, NULL, 9, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (26, '2007-07-08 00:00:00', 1, 8, 2, '2007-07-08 00:00:01', '00:01:20', 1, 1, 33, 0, NULL, 9, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (27, '2007-07-24 00:00:00', 1, 1, 1, '2007-07-24 00:00:01', '00:01:20', 1, 1, 20, 0, NULL, 9, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (28, '2007-08-10 00:00:00', 3, 12, 1, '2007-08-10 00:00:13', '00:00:13', 1, 1, 14, 0, NULL, 9, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (29, '2007-08-09 21:54:18.553132', 1, 8, 1, '2007-08-09 23:59:59', NULL, NULL, NULL, 36, 0, NULL, 12, NULL, NULL, NULL);
-INSERT INTO build (id, datecreated, processor, distroarchseries, buildstate, datebuilt, buildduration, buildlog, builder, sourcepackagerelease, pocket, dependencies, archive, build_warnings, date_first_dispatched, upload_log) VALUES (30, '2007-08-10 00:00:01', 3, 12, 1, '2007-08-10 00:00:14', '00:00:13', 1, 1, 14, 0, NULL, 1, NULL, NULL, NULL);
-
-
-ALTER TABLE build ENABLE TRIGGER ALL;
+ALTER TABLE binarypackagebuild DISABLE TRIGGER ALL;
+
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (2, 1, 1, 14);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (6, 2, 1, 32);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (7, 3, 6, 20);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (8, 4, 6, 14);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (9, 5, 1, 20);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (10, 6, 1, 26);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (11, 7, 6, 25);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (12, 8, 6, 27);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (13, 9, 1, 17);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (14, 10, 1, 28);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (15, 11, 1, 29);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (16, 12, 11, 20);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (18, 13, 8, 14);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (19, 14, 8, 20);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (21, 15, 1, 33);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (22, 16, 8, 33);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (23, 17, 1, 35);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (24, 18, 1, 33);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (25, 19, 1, 35);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (26, 20, 8, 33);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (27, 21, 1, 20);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (28, 22, 12, 14);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (29, 23, 8, 36);
+INSERT INTO binarypackagebuild (id, package_build, distro_arch_series, source_package_release) VALUES (30, 24, 12, 14);
+
+
+ALTER TABLE binarypackagebuild ENABLE TRIGGER ALL;
+
+
+ALTER TABLE binarypackagename DISABLE TRIGGER ALL;
+
+INSERT INTO binarypackagename (id, name) VALUES (6, 'foobar');
+INSERT INTO binarypackagename (id, name) VALUES (8, 'mozilla-firefox');
+INSERT INTO binarypackagename (id, name) VALUES (13, 'pmount');
+INSERT INTO binarypackagename (id, name) VALUES (14, 'linux-2.6.12');
+INSERT INTO binarypackagename (id, name) VALUES (15, 'at');
+INSERT INTO binarypackagename (id, name) VALUES (16, 'cdrkit');
+INSERT INTO binarypackagename (id, name) VALUES (17, 'commercialpackage');
+INSERT INTO binarypackagename (id, name) VALUES (18, 'mozilla-firefox-data');
+
+
+ALTER TABLE binarypackagename ENABLE TRIGGER ALL;
 
 
 ALTER TABLE binarypackagerelease DISABLE TRIGGER ALL;

=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2010-05-20 13:59:11 +0000
+++ database/schema/comments.sql	2010-05-27 13:25:49 +0000
@@ -1596,23 +1596,32 @@
 COMMENT ON COLUMN PushMirrorAccess.name IS 'Name of an arch archive on the push mirror, e.g. [e-mail]--2003-example';
 COMMENT ON COLUMN PushMirrorAccess.person IS 'A person that has access to update the named archive';
 
--- Build
-COMMENT ON TABLE Build IS 'Build: This table stores the build procedure information of a sourcepackagerelease and its results (binarypackagereleases) for a given distroarchseries.';
-COMMENT ON COLUMN Build.datecreated IS 'When the build record was created.';
-COMMENT ON COLUMN Build.datebuilt IS 'When the build record was processed.';
-COMMENT ON COLUMN Build.buildduration IS 'How long this build took to be processed.';
-COMMENT ON COLUMN Build.distroarchseries IS 'Points the target Distroarchrelease for this build.';
-COMMENT ON COLUMN Build.processor IS 'Points to the Distroarchrelease available processor target for this build.';
-COMMENT ON COLUMN Build.sourcepackagerelease IS 'Sourcepackagerelease which originated this build.';
-COMMENT ON COLUMN Build.buildstate IS 'Stores the current build procedure state.';
-COMMENT ON COLUMN Build.buildlog IS 'Points to the buildlog file stored in librarian.';
-COMMENT ON COLUMN Build.builder IS 'Points to the builder which has once processed it.';
-COMMENT ON COLUMN Build.pocket IS 'Stores the target pocket identifier for this build.';
-COMMENT ON COLUMN Build.dependencies IS 'Contains a debian-like dependency line specifying the current missing-dependencies for this package.';
-COMMENT ON COLUMN Build.archive IS 'Targeted archive for this build.';
-COMMENT ON COLUMN Build.build_warnings IS 'Warnings and diagnosis messages provided by the builder while building this job.';
-COMMENT ON COLUMN Build.date_first_dispatched IS 'The instant the build was dispatched the first time. This value will not get overridden if the build is retried.';
-COMMENT ON COLUMN Build.upload_log IS 'Reference to a LibraryFileAlias containing the upload log messages generated while processing the binaries resulted from this build.';
+-- BuildFarmJob, and its related tables, PackageBuild, BinaryPackageBuild
+COMMENT ON TABLE BuildFarmJob IS 'BuildFarmJob: This table stores the information common to all jobs on the Launchpad build farm.';
+COMMENT ON COLUMN BuildFarmJob.processor IS 'Points to the required processor target for this job, or null.';
+COMMENT ON COLUMN BuildFarmJob.virtualized IS 'The virtualization setting required by this build farm job, or null.';
+COMMENT ON COLUMN BuildFarmJob.date_created IS 'When the build farm job record was created.';
+COMMENT ON COLUMN BuildFarmJob.date_started IS 'When the build farm job started being processed.';
+COMMENT ON COLUMN BuildFarmJob.date_finished IS 'When the build farm job finished being processed.';
+COMMENT ON COLUMN BuildFarmJob.date_first_dispatched IS 'The instant the build was dispatched the first time. This value will not get overridden if the build is retried.';
+COMMENT ON COLUMN BuildFarmJob.builder IS 'Points to the builder which processed this build farm job.';
+COMMENT ON COLUMN BuildFarmJob.status IS 'Stores the current build status.';
+COMMENT ON COLUMN BuildFarmJob.log IS 'Points to the log for this build farm job file stored in librarian.';
+COMMENT ON COLUMN BuildFarmJob.job_type IS 'The type of build farm job to which this record corresponds.';
+
+-- PackageBuild
+COMMENT ON TABLE PackageBuild IS 'PackageBuild: This table stores the information common to build farm jobs that build source or binary packages.';
+COMMENT ON COLUMN PackageBuild.build_farm_job IS 'Points to the build farm job with the base information.';
+COMMENT ON COLUMN PackageBuild.archive IS 'Targeted archive for this package build.';
+COMMENT ON COLUMN PackageBuild.pocket IS 'Stores the target pocket identifier for this package build.';
+COMMENT ON COLUMN PackageBuild.upload_log IS 'Reference to a LibraryFileAlias containing the upload log messages generated while processing the packages resulting from this package build.';
+COMMENT ON COLUMN PackageBuild.dependencies IS 'Contains a debian-like dependency line specifying the current missing-dependencies for this package.';
+
+-- BinaryPackageBuild
+COMMENT ON TABLE BinaryPackageBuild IS 'BinaryPackageBuild: This table links a package build with a distroarchseries and sourcepackagerelease.';
+COMMENT ON COLUMN BinaryPackageBuild.package_build IS 'Points to the related package build with the base information.';
+COMMENT ON COLUMN BinaryPackageBuild.distro_arch_series IS 'Points the target DistroArchSeries for this build.';
+COMMENT ON COLUMN BinaryPackageBuild.source_package_release IS 'SourcePackageRelease which originated this build.';
 
 -- Builder
 COMMENT ON TABLE Builder IS 'Builder: This table stores the build-slave registry and status information as: name, url, trusted, builderok, builderaction, failnotes.';

=== added file 'database/schema/patch-2207-57-0.sql'
--- database/schema/patch-2207-57-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2207-57-0.sql	2010-05-27 13:25:49 +0000
@@ -0,0 +1,210 @@
+-- Copyright 2010 Canonical Ltd. This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+CREATE TEMPORARY TABLE BuildInfo AS
+SELECT
+    build.id,
+    build.processor,
+    archive.require_virtualized AS virtualized,
+    -- Currently we do not know if a build was virtual or not? (it's
+    -- only on the archive and the builder, both of which can
+    -- change). IBuild.is_virtualized just queries the archive.
+    build.datecreated AS date_created,
+    (build.datebuilt - build.buildduration) AS date_started,
+    build.datebuilt AS date_finished,
+    build.date_first_dispatched,
+    build.builder,
+    build.buildstate AS status,
+    build.buildlog AS log,
+    build.archive,
+    build.pocket,
+    build.upload_log,
+    build.dependencies,
+    build.distroarchseries AS distro_arch_series,
+    build.sourcepackagerelease AS source_package_release,
+    build.build_warnings -- We don't seem to use this in LP code at all?
+FROM build JOIN archive ON build.archive = archive.id;
+
+-- The schema patch for general build histories. See
+-- https://dev.launchpad.net/LEP/GeneralBuildHistories and the linked
+-- blueprint/bug for more information.
+
+-- Step 1
+-- Create the new BuildFarmJob, PackageBuild and BinaryPackageBuild tables,
+-- with indexes based on the current Build table.
+CREATE TABLE BuildFarmJob (
+    id serial PRIMARY KEY,
+    processor integer
+        CONSTRAINT buildfarmjob__processor__fk REFERENCES processor,
+    virtualized boolean,
+    date_created timestamp without time zone
+        DEFAULT timezone('UTC'::text, now()) NOT NULL,
+    date_started timestamp without time zone,
+    date_finished timestamp without time zone,
+    date_first_dispatched timestamp without time zone,
+    builder integer CONSTRAINT buildfarmjob__builder__fk REFERENCES builder,
+    status integer NOT NULL,
+    log integer CONSTRAINT buildfarmjob__log__fk REFERENCES libraryfilealias,
+    job_type integer NOT NULL
+);
+
+INSERT INTO BuildFarmJob (
+    id, processor, virtualized, date_created, date_started,
+    date_finished, builder, status, log, job_type)
+SELECT
+    id, processor, virtualized, date_created, date_started,
+    date_finished, builder, status, log, 1
+FROM BuildInfo;
+
+SELECT setval('buildfarmjob_id_seq', MAX(id)) FROM BuildFarmJob;
+
+CREATE INDEX buildfarmjob__date_created__idx ON buildfarmjob(date_created);
+CREATE INDEX buildfarmjob__date_started__idx ON buildfarmjob(date_started);
+CREATE INDEX buildfarmjob__date_finished__idx ON buildfarmjob(date_finished);
+CREATE INDEX buildfarmjob__builder_and_status__idx
+    ON buildfarmjob(builder, status);
+CREATE INDEX buildfarmjob__log__idx ON buildfarmjob(log) WHERE log IS NOT NULL;
+
+
+CREATE TABLE PackageBuild (
+    id serial PRIMARY KEY,
+    build_farm_job integer NOT NULL
+        CONSTRAINT packagebuild__build_farm_job__fk REFERENCES buildfarmjob,
+    archive integer NOT NULL
+        CONSTRAINT packagebuild__archive__fk REFERENCES archive,
+    pocket integer NOT NULL DEFAULT 0,
+    upload_log integer
+        CONSTRAINT packagebuild__log__fk REFERENCES libraryfilealias,
+    dependencies text
+);
+
+INSERT INTO PackageBuild (
+    id, build_farm_job, archive, pocket, upload_log, dependencies)
+SELECT id, id AS build_farm_job, archive, pocket, upload_log, dependencies
+FROM BuildInfo;
+SELECT setval('packagebuild_id_seq', MAX(id)) FROM PackageBuild;
+
+CREATE UNIQUE INDEX packagebuild__build_farm_job__idx ON packagebuild(
+    build_farm_job);
+CREATE INDEX packagebuild__archive__idx ON packagebuild(archive);
+CREATE INDEX packagebuild__upload_log__idx ON packagebuild(upload_log)
+    WHERE upload_log IS NOT NULL;
+
+CREATE TABLE BinaryPackageBuild (
+    id serial PRIMARY KEY,
+    package_build integer NOT NULL
+        CONSTRAINT binarypackagebuild__package_build__fk
+        REFERENCES packagebuild,
+    distro_arch_series integer NOT NULL
+        CONSTRAINT binarypackagebuild__distro_arch_series__fk
+        REFERENCES distroarchseries,
+    source_package_release integer NOT NULL
+        CONSTRAINT binarypackagebuild__source_package_release__fk
+        REFERENCES sourcepackagerelease
+);
+
+INSERT INTO binarypackagebuild(
+    id, package_build, distro_arch_series, source_package_release)
+SELECT id, id, distro_arch_series, source_package_release
+FROM BuildInfo;
+
+SELECT setval('binarypackagebuild_id_seq', MAX(id)) FROM BinaryPackageBuild;
+
+CREATE UNIQUE INDEX binarypackagebuild__package_build__idx
+    ON binarypackagebuild(package_build);
+-- Indexes that we can no longer create:
+-- CREATE UNIQUE INDEX binarypackagebuild__distro_arch_series_uniq__idx ON binarypackagebuild(distro_arch_series, source_package_release, archive)
+-- CREATE INDEX binarypackagebuild__distro_arch_series__status__idx ON binarypackagebuild(distro_arch_series, status?)
+-- CREATE INDEX binarypackagebuild__distro_arch_series__date_finished ON binarypackagebuild(distro_arch_series, date_finished)
+CREATE INDEX binarypackagebuild__source_package_release_idx
+    ON binarypackagebuild(source_package_release);
+CREATE INDEX binarypackagebuild__distro_arch_series__idx
+    ON BinaryPackageBuild(distro_arch_series);
+
+-- Step 3
+-- Need to update all the references to the current build table to point to
+-- the new table, shown by:
+-- launchpad_dev=# select t.constraint_name, t.table_name, t.constraint_type,
+-- launchpad_dev-# c.table_name, c.column_name
+-- launchpad_dev-# from information_schema.table_constraints t,
+-- launchpad_dev-# information_schema.constraint_column_usage c
+-- launchpad_dev-# where t.constraint_name = c.constraint_name
+-- launchpad_dev-# and t.constraint_type = 'FOREIGN KEY'
+-- launchpad_dev-# and c.table_name = 'build'
+-- launchpad_dev-# ;
+
+-- "binarypackagerelease__build__fk" FOREIGN KEY (build) REFERENCES build(id) ON DELETE CASCADE
+ALTER TABLE BinaryPackageRelease DROP CONSTRAINT binarypackagerelease__build__fk;
+ALTER TABLE BinaryPackageRelease
+ ADD CONSTRAINT binarypackagerelease_build_fk
+ FOREIGN KEY (build) REFERENCES binarypackagebuild(id) ON DELETE CASCADE;
+
+-- "buildpackagejob__build__fk" FOREIGN KEY (build) REFERENCES build(id)
+ALTER TABLE BuildPackageJob DROP CONSTRAINT buildpackagejob__build__fk;
+ALTER TABLE BuildPackageJob
+ ADD CONSTRAINT buildpackagejob_build_fk
+ FOREIGN KEY (build) REFERENCES binarypackagebuild(id);
+
+-- "packageuploadbuild_build_fk" FOREIGN KEY (build) REFERENCES build(id)
+ALTER TABLE packageuploadbuild DROP CONSTRAINT packageuploadbuild_build_fk;
+ALTER TABLE packageuploadbuild
+ ADD CONSTRAINT packageuploadbuild_build_fk
+ FOREIGN KEY (build) REFERENCES binarypackagebuild(id);
+
+
+-- "securebinarypackagepublishinghistory_supersededby_fk" FOREIGN KEY (supersededby) REFERENCES build(id)
+ALTER TABLE binarypackagepublishinghistory
+ DROP CONSTRAINT securebinarypackagepublishinghistory_supersededby_fk;
+ALTER TABLE binarypackagepublishinghistory
+ ADD CONSTRAINT binarypackagepublishinghistory_supersededby_fk
+ FOREIGN KEY (supersededby) REFERENCES binarypackagebuild(id);
+
+
+-- Step 4
+-- Drop the old Build table and its constraints.
+ALTER TABLE Build SET SCHEMA todrop;
+ALTER TABLE todrop.Build DROP CONSTRAINT build__archive__fk;
+ALTER TABLE todrop.Build DROP CONSTRAINT build__builder__fk;
+ALTER TABLE todrop.Build DROP CONSTRAINT build__buildlog__fk;
+ALTER TABLE todrop.Build DROP CONSTRAINT build__distroarchseries__fk;
+ALTER TABLE todrop.Build DROP CONSTRAINT build__processor__fk;
+ALTER TABLE todrop.Build DROP CONSTRAINT build__sourcepackagerelease__fk;
+ALTER TABLE todrop.Build DROP CONSTRAINT build__upload_log__fk;
+
+
+-- Step 5
+-- Update views that reference the build table:
+CREATE OR REPLACE VIEW PublishedPackage AS
+SELECT securebinarypackagepublishinghistory.id, distroarchseries.id AS distroarchseries, distroseries.distribution, distroseries.id AS distroseries, distroseries.name AS distroseriesname, processorfamily.id AS processorfamily, processorfamily.name AS processorfamilyname, securebinarypackagepublishinghistory.status AS packagepublishingstatus, component.name AS component, section.name AS section, binarypackagerelease.id AS binarypackagerelease, binarypackagename.name AS binarypackagename, binarypackagerelease.summary AS binarypackagesummary, binarypackagerelease.description AS binarypackagedescription, binarypackagerelease.version AS binarypackageversion, binarypackagebuild.id AS build, buildfarmjob.date_finished AS datebuilt, sourcepackagerelease.id AS sourcepackagerelease, sourcepackagerelease.version AS sourcepackagereleaseversion, sourcepackagename.name AS sourcepackagename, securebinarypackagepublishinghistory.pocket, securebinarypackagepublishinghistory.archive, binarypackagerelease.fti AS binarypackagefti
+ FROM binarypackagepublishinghistory securebinarypackagepublishinghistory
+ JOIN distroarchseries ON distroarchseries.id = securebinarypackagepublishinghistory.distroarchseries
+ JOIN distroseries ON distroarchseries.distroseries = distroseries.id
+ JOIN processorfamily ON distroarchseries.processorfamily = processorfamily.id
+ JOIN component ON securebinarypackagepublishinghistory.component = component.id
+ JOIN binarypackagerelease ON securebinarypackagepublishinghistory.binarypackagerelease = binarypackagerelease.id
+ JOIN section ON securebinarypackagepublishinghistory.section = section.id
+ JOIN binarypackagename ON binarypackagerelease.binarypackagename = binarypackagename.id
+ JOIN binarypackagebuild ON binarypackagerelease.build = binarypackagebuild.id
+ JOIN packagebuild ON binarypackagebuild.package_build = packagebuild.id
+ JOIN buildfarmjob ON packagebuild.build_farm_job = buildfarmjob.id
+ JOIN sourcepackagerelease ON binarypackagebuild.source_package_release = sourcepackagerelease.id
+ JOIN sourcepackagename ON sourcepackagerelease.sourcepackagename = sourcepackagename.id
+ WHERE securebinarypackagepublishinghistory.dateremoved IS NULL;
+
+CREATE OR REPLACE VIEW BinaryPackageFilePublishing As
+SELECT (libraryfilealias.id::text || '.'::text) || securebinarypackagepublishinghistory.id::text AS id, distroseries.distribution, securebinarypackagepublishinghistory.id AS binarypackagepublishing, component.name AS componentname, libraryfilealias.filename AS libraryfilealiasfilename, sourcepackagename.name AS sourcepackagename, binarypackagefile.libraryfile AS libraryfilealias, distroseries.name AS distroseriesname, distroarchseries.architecturetag, securebinarypackagepublishinghistory.status AS publishingstatus, securebinarypackagepublishinghistory.pocket, securebinarypackagepublishinghistory.archive
+ FROM binarypackagepublishinghistory securebinarypackagepublishinghistory
+ JOIN binarypackagerelease ON securebinarypackagepublishinghistory.binarypackagerelease = binarypackagerelease.id
+ JOIN binarypackagebuild ON binarypackagerelease.build = binarypackagebuild.id
+ JOIN sourcepackagerelease ON binarypackagebuild.source_package_release = sourcepackagerelease.id
+ JOIN sourcepackagename ON sourcepackagerelease.sourcepackagename = sourcepackagename.id
+ JOIN binarypackagefile ON binarypackagefile.binarypackagerelease = binarypackagerelease.id
+ JOIN libraryfilealias ON binarypackagefile.libraryfile = libraryfilealias.id
+ JOIN distroarchseries ON securebinarypackagepublishinghistory.distroarchseries = distroarchseries.id
+ JOIN distroseries ON distroarchseries.distroseries = distroseries.id
+ JOIN component ON securebinarypackagepublishinghistory.component = component.id
+ WHERE securebinarypackagepublishinghistory.dateremoved IS NULL;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2207, 57, 0);

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2010-05-20 13:59:11 +0000
+++ database/schema/security.cfg	2010-05-27 13:25:49 +0000
@@ -293,7 +293,9 @@
 public.bug                              = SELECT
 public.bugaffectsperson                 = SELECT, INSERT, UPDATE, DELETE
 public.bugtask                          = SELECT
-public.build                            = SELECT
+public.buildfarmjob                     = SELECT
+public.packagebuild                     = SELECT
+public.binarypackagebuild               = SELECT
 public.distribution                     = SELECT
 public.distributionsourcepackagecache   = SELECT, INSERT, UPDATE, DELETE
 public.distroarchseries                 = SELECT, UPDATE
@@ -344,7 +346,9 @@
 public.binarypackagefile                = SELECT
 public.branchmergeproposal              = SELECT
 public.bugattachment                    = SELECT
-public.build                            = SELECT
+public.buildfarmjob                     = SELECT
+public.packagebuild                     = SELECT
+public.binarypackagebuild               = SELECT
 public.codeimportresult                 = SELECT
 public.diff                             = SELECT
 public.distribution                     = SELECT
@@ -633,7 +637,9 @@
 public.binarypackagefile                        = SELECT
 public.binarypackagename                        = SELECT
 public.binarypackagerelease                     = SELECT
-public.build                                    = SELECT
+public.buildfarmjob                             = SELECT
+public.packagebuild                             = SELECT
+public.binarypackagebuild                       = SELECT
 public.component                                = SELECT
 public.componentselection                       = SELECT
 public.distribution                             = SELECT
@@ -787,7 +793,9 @@
 public.job                                      = SELECT, INSERT, UPDATE, DELETE
 public.buildpackagejob                          = SELECT, INSERT, UPDATE, DELETE
 public.builder                                  = SELECT, INSERT, UPDATE
-public.build                                    = SELECT, INSERT, UPDATE
+public.buildfarmjob                             = SELECT, INSERT, UPDATE
+public.packagebuild                             = SELECT, INSERT, UPDATE
+public.binarypackagebuild                       = SELECT, INSERT, UPDATE
 public.distribution                             = SELECT, UPDATE
 public.distroseries                             = SELECT, UPDATE
 public.distroarchseries                         = SELECT, UPDATE
@@ -871,7 +879,9 @@
 public.bugtracker                       = SELECT, INSERT, UPDATE, DELETE
 public.bugtrackeralias                  = SELECT, INSERT, UPDATE, DELETE
 public.bugwatch                         = SELECT, INSERT, UPDATE, DELETE
-public.build                            = SELECT, INSERT, UPDATE
+public.buildfarmjob                     = SELECT, INSERT, UPDATE
+public.packagebuild                     = SELECT, INSERT, UPDATE
+public.binarypackagebuild               = SELECT, INSERT, UPDATE
 public.builder                          = SELECT, INSERT, UPDATE
 public.buildqueue                       = SELECT, INSERT, UPDATE, DELETE
 public.job                              = SELECT, INSERT, UPDATE, DELETE
@@ -1061,7 +1071,9 @@
 public.sourcepackagereleasefile         = SELECT, INSERT
 public.binarypackagefile                = SELECT, INSERT
 public.pocketchroot                     = SELECT
-public.build                            = SELECT, INSERT, UPDATE
+public.buildfarmjob                     = SELECT, INSERT, UPDATE
+public.packagebuild                     = SELECT, INSERT, UPDATE
+public.binarypackagebuild               = SELECT, INSERT, UPDATE
 public.sourcepackagerecipebuild         = SELECT, UPDATE
 public.buildqueue                       = SELECT, INSERT, UPDATE
 public.job                              = SELECT, INSERT, UPDATE
@@ -1146,7 +1158,9 @@
 public.processor                        = SELECT
 public.processorfamily                  = SELECT
 public.distrocomponentuploader          = SELECT
-public.build                            = SELECT, INSERT, UPDATE
+public.buildfarmjob                     = SELECT, INSERT, UPDATE
+public.packagebuild                     = SELECT, INSERT, UPDATE
+public.binarypackagebuild               = SELECT, INSERT, UPDATE
 public.buildqueue                       = SELECT, INSERT, UPDATE
 public.job                              = SELECT, INSERT, UPDATE
 public.buildpackagejob                  = SELECT, INSERT, UPDATE
@@ -1809,7 +1823,9 @@
 [nagios]
 type=user
 public.archive                          = SELECT
-public.build                            = SELECT
+public.buildfarmjob                     = SELECT
+public.packagebuild                     = SELECT
+public.binarypackagebuild               = SELECT
 public.buildqueue                       = SELECT
 public.buildpackagejob                  = SELECT
 public.job                              = SELECT