Merge lp://staging/~gary/launchpad/bug753000 into lp://staging/launchpad/db-devel

The data migration looks fine, and should be fast enough with production data to run as part of the patch.

I've reworked the indexes below to minimize index size, and dropped redundant indexes. patch-2208-65-0.sql

-- CREATE CONSTRAINTS ----------------------------------------------------

CREATE UNIQUE INDEX structuralsubscription__product__subscriber__key
ON StructuralSubscription(product, subscriber) WHERE product IS NOT NULL;

CREATE UNIQUE INDEX structuralsubscription__project__subscriber__key
ON StructuralSubscription(project, subscriber) WHERE project IS NOT NULL;

-- We want to do this.
-- ALTER TABLE ONLY StructuralSubscription
-- ADD CONSTRAINT structuralsubscription__distribution__sourcepackagename__subscriber__unique
-- UNIQUE (distribution, sourcepackagename, subscriber);
-- However, we also want to do this, *if* the sourcepackagename is NULL.
-- ALTER TABLE ONLY StructuralSubscription
-- ADD CONSTRAINT structuralsubscription__distribution__subscriber__unique
-- UNIQUE (distribution, subscriber);
-- The second constraint will disallow sourcepackagename flexibility in the
-- first. Therefore, we use a unique index instead, as seen below.

CREATE UNIQUE INDEX
    structuralsubscription__distribution__sourcepackagename__subscriber__key
ON StructuralSubscription(distribution, sourcepackagename, subscriber)
WHERE distribution IS NOT NULL AND sourcepackagename IS NOT NULL;

CREATE UNIQUE INDEX structuralsubscription__distribution__subscriber__key
ON StructuralSubscription(distribution, subscriber)
WHERE distribution IS NOT NULL AND sourcepackagename IS NULL;

CREATE UNIQUE INDEX structuralsubscription__distroseries__subscriber__key
ON StructuralSubscription(distroseries, subscriber)
WHERE distroseries IS NOT NULL;

-- NB. Currently we can't subscribe to a (distroseries, sourcepackagename)
-- so no need for the second partial distroseries index like the two
-- distribution indexes.

CREATE UNIQUE INDEX structuralsubscription__milestone__subscriber__key
ON StructuralSubscription(milestone, subscriber)
WHERE milestone IS NOT NULL;

CREATE UNIQUE INDEX structuralsubscription__productseries__subscriber__key
ON StructuralSubscription(productseries, subscriber)
WHERE productseries IS NOT NULL;

-- Drop obsolete indexes - the above constraints make them redundant.
DROP INDEX structuralsubscription__distribution__sourcepackagename__idx;
DROP INDEX structuralsubscription__distroseries__idx;
DROP INDEX structuralsubscription__milestone__idx;
DROP INDEX structuralsubscription__product__idx;
DROP INDEX structuralsubscription__productseries__idx;
DROP INDEX structuralsubscription__project__idx;

INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 65, 0);

Hi Gary,

The tests - or at least the assert*()s you've added need some explanatory comments, but other than that this is good to go.