Merge lp:~wgrant/launchpad/flatten-bfj-0-db into lp:launchpad/db-devel

[William Grant]

Add performance denorm columns to BinaryPackageBuild/SourcePackageRecipeBuild/TranslationTemplatesBuild.

The build farm job schema is being reworked to improve performance. Columns from PackageBuild and BuildFarmJob are being merged into tables that previously delegated to them. The PackageBuild table will end up dying entirely, but BuildFarmJob will remain, a shadow of its former self, to answer questions about Archive:+builds and Builder:+history. Additionally, BinaryPackageBuild is growing new distribution, distroseries, sourcepackagename and is_distro_archive columns to make searches even faster.

This branch contains the main DB changes. BPB and SPRB gain all columns that previously lived on BFJ and PB, and TTB gets everything from BFJ. BPB and SPRB get direct FKs to BFJ, and their FKs to PB become nullable. BFJ gains an archive column to replace PB for use in Archive:+builds, failure_count becomes nullable as it's to be dropped soon, and the started_if_finished constraint dies because date_started is about to as well.

Additionally, BPB gains distribution, distroseries, sourcepackagename and is_distro_archive columns for more efficient searches.

[Stuart Bishop (stub)]

You need indexes on all of the log and upload_log columns or the Librarian Garbage Collector will hate you. This will need to be landed separately and applied live straight after, as there are a several million existing rows.

You can't default date_created and failure_count columns when creating the column without rewriting the entire tables and their indexes. Add the DEFAULTs using separate ALTER TABLE statements and you will be fine.

review: Approve (db)

=== added file 'database/schema/patch-2209-41-0.sql'
--- database/schema/patch-2209-41-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-41-0.sql	2013-02-03 03:11:26 +0000
@@ -0,0 +1,78 @@
+-- Copyright 2013 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+ALTER TABLE binarypackagebuild
+    ADD COLUMN archive integer REFERENCES archive,
+    ADD COLUMN pocket integer,
+    ADD COLUMN processor integer REFERENCES processor,
+    ADD COLUMN virtualized boolean,
+    ADD COLUMN date_created timestamp without time zone,
+    ADD COLUMN date_started timestamp without time zone,
+    ADD COLUMN date_finished timestamp without time zone,
+    ADD COLUMN date_first_dispatched timestamp without time zone,
+    ADD COLUMN builder integer REFERENCES builder,
+    ADD COLUMN status integer,
+    ADD COLUMN log integer REFERENCES libraryfilealias,
+    ADD COLUMN upload_log integer REFERENCES libraryfilealias,
+    ADD COLUMN dependencies text,
+    ADD COLUMN failure_count integer,
+    ADD COLUMN build_farm_job integer REFERENCES buildfarmjob,
+    ADD COLUMN distribution integer REFERENCES distribution,
+    ADD COLUMN distro_series integer REFERENCES distroseries,
+    ADD COLUMN is_distro_archive boolean,
+    ADD COLUMN source_package_name integer REFERENCES sourcepackagename,
+    ALTER COLUMN package_build DROP NOT NULL;
+ALTER TABLE binarypackagebuild
+    ALTER COLUMN date_created
+        SET DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
+    ALTER COLUMN failure_count SET DEFAULT 0;
+
+ALTER TABLE sourcepackagerecipebuild
+    ADD COLUMN archive integer REFERENCES archive,
+    ADD COLUMN pocket integer,
+    ADD COLUMN processor integer REFERENCES processor,
+    ADD COLUMN virtualized boolean,
+    ADD COLUMN date_created timestamp without time zone,
+    ADD COLUMN date_started timestamp without time zone,
+    ADD COLUMN date_finished timestamp without time zone,
+    ADD COLUMN date_first_dispatched timestamp without time zone,
+    ADD COLUMN builder integer REFERENCES builder,
+    ADD COLUMN status integer,
+    ADD COLUMN log integer REFERENCES libraryfilealias,
+    ADD COLUMN upload_log integer REFERENCES libraryfilealias,
+    ADD COLUMN dependencies text,
+    ADD COLUMN failure_count integer,
+    ADD COLUMN build_farm_job integer REFERENCES buildfarmjob,
+    ALTER COLUMN package_build DROP NOT NULL;
+ALTER TABLE sourcepackagerecipebuild
+    ALTER COLUMN date_created
+        SET DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
+    ALTER COLUMN failure_count SET DEFAULT 0;
+
+ALTER TABLE translationtemplatesbuild
+    ADD COLUMN processor integer REFERENCES processor,
+    ADD COLUMN virtualized boolean,
+    ADD COLUMN date_created timestamp without time zone,
+    ADD COLUMN date_started timestamp without time zone,
+    ADD COLUMN date_finished timestamp without time zone,
+    ADD COLUMN date_first_dispatched timestamp without time zone,
+    ADD COLUMN builder integer REFERENCES builder,
+    ADD COLUMN status integer,
+    ADD COLUMN log integer REFERENCES libraryfilealias,
+    ADD COLUMN failure_count integer;
+ALTER TABLE sourcepackagerecipebuild
+    ALTER COLUMN date_created
+        SET DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
+    ALTER COLUMN failure_count SET DEFAULT 0;
+
+-- BuildFarmJob is becoming a shadow of its former self, so more columns
+-- have to be nullable, but it also grows an archive column to hasten
+-- access checks.
+ALTER TABLE buildfarmjob
+    ADD COLUMN archive integer REFERENCES archive,
+    ALTER COLUMN failure_count DROP NOT NULL,
+    DROP CONSTRAINT started_if_finished;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 41, 0);
 
=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2013-01-15 05:24:27 +0000
+++ database/schema/security.cfg	2013-02-03 03:11:26 +0000
@@ -444,6 +444,7 @@
 public.subunitstream                    = SELECT
 public.temporaryblobstorage             = SELECT, DELETE
 public.translationimportqueueentry      = SELECT
+public.translationtemplatesbuild        = SELECT
 type=user
 
 [productreleasefinder]