launchpad-reviewers team mailing list archive

[Ian Booth <ian.booth@xxxxxxxxxxxxx>]

Ian Booth has proposed merging lp:~wallyworld/launchpad/latestpublishedreleases-1071581 into lp:launchpad.

Commit message:
Add new table for reporting on publications, and a table to allow garbo jobs to persist state between runs.

Requested reviews:
  William Grant (wgrant)
  Stuart Bishop (stub)
Related bugs:
  Bug #1071581 in Launchpad itself: "+ppa-packages timeout"
  https://bugs.launchpad.net/launchpad/+bug/1071581

For more details, see:
https://code.launchpad.net/~wallyworld/launchpad/latestpublishedreleases-1071581/+merge/132466

This db patch adds 2 new tables.

1. LatestPublishedReleases

Stores information about the latest published source packages. Effectively denormalised SPPH and SPR data. Required for efficient reporting.

I added indices which are required immediately for the task at hand.

I also added a reference to SPR, even though it's not used right now; it likely will be in the future.

Since it's for reporting only, I left off the FK references. This allows the referenced objects to be deleted and the reporting data can stay behind.

2. GarboJobState

A more robust alternative to using memcache to allow garbo jobs to store their state between runs.



-- 
https://code.launchpad.net/~wallyworld/launchpad/latestpublishedreleases-1071581/+merge/132466
Your team Launchpad code reviewers is subscribed to branch lp:launchpad.

=== added file 'database/schema/patch-2209-38-0.sql'
--- database/schema/patch-2209-38-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-38-0.sql	2012-11-01 08:56:22 +0000
@@ -0,0 +1,50 @@
+-- Copyright 2012 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 TABLE LatestPublishedReleases (
+    publication integer PRIMARY KEY,
+    date_uploaded timestamp without time zone,
+    creator integer NOT NULL,
+    maintainer integer NOT NULL,
+    archive_purpose integer NOT NULL,
+    upload_archive integer NOT NULL,
+    upload_distroseries integer NOT NULL,
+    sourcepackagename integer NOT NULL,
+    sourcepackagerelease integer NOT NULL
+);
+
+
+CREATE INDEX latestpublishedreleases__creator__idx
+    ON LatestPublishedReleases USING btree (creator);
+
+CREATE INDEX latestpublishedreleases__maintainer__idx
+    ON LatestPublishedReleases USING btree (maintainer);
+
+CREATE INDEX latestpublishedreleases__archive_purpose__idx
+    ON LatestPublishedReleases USING btree (archive_purpose);
+
+ALTER TABLE LatestPublishedReleases ADD CONSTRAINT upload_archive__upload_distroseries__sourcepackagename__key
+     UNIQUE (upload_archive, upload_distroseries, sourcepackagename);
+
+COMMENT ON TABLE LatestPublishedReleases IS 'LatestPublishedReleases: The most recent published source package releases for a given (distroseries, archive, sourcepackage).';
+COMMENT ON COLUMN LatestPublishedReleases.upload_archive IS 'The target archive for the release.';
+COMMENT ON COLUMN LatestPublishedReleases.sourcepackagename IS 'The SourcePackageName of the release.';
+COMMENT ON COLUMN LatestPublishedReleases.upload_distroseries IS 'The distroseries into which the sourcepackagerelease was published.';
+COMMENT ON COLUMN LatestPublishedReleases.sourcepackagerelease IS 'The sourcepackagerelease which was published.';
+COMMENT ON COLUMN LatestPublishedReleases.archive_purpose IS 'The purpose of the archive, e.g. COMMERCIAL.  See the ArchivePurpose DBSchema item.';
+COMMENT ON COLUMN LatestPublishedReleases.date_uploaded IS 'The date/time on which the source was actually published into the archive.';
+
+
+CREATE TABLE GarboJobState (
+    name text PRIMARY KEY,
+    json_data text
+);
+
+COMMENT ON TABLE GarboJobState IS 'Contains persistent state for named garbo jobs.';
+COMMENT ON COLUMN GarboJobState.name IS 'The name of the job.';
+COMMENT ON COLUMN GarboJobState.json_data IS 'A JSON struct containing data for the job.';
+
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 38, 0);