Merge lp:~cjwatson/launchpad/db-pocket-permissions into lp:launchpad/db-devel

[Colin Watson]

Add ArchivePermission.pocket column.

== Summary ==

Bug 914779 requests per-pocket upload permissions. This is tied in with getting rid of archive admin shell access, as one of the steps there involves sorting out the ubuntu-security team's processes and they currently rely on all sorts of weird hacks, including a celebrity, to make sure they can publish security updates in a timely fashion; it would also make life easier for the Ubuntu backporters team.

== Proposed fix ==

This is the DB part of the fix, adding a pocket column to ArchivePermission in more or less the obvious way, and adjusting the constraint to match (we're not interested in any of the cases where you might want intersected permissions such as component+pocket, as far as I can make out). There's some fiddling to sort out the constraint since PostgreSQL lacks MODIFY CONSTRAINT, but wgrant reckons this approach should be OK on a relatively small table such as ArchivePermission.

[Stuart Bishop (stub)]

This looks fine.There are no problems with dropping and recreating the table as there are only 800 rows to check.

review: Approve (db)

=== added file 'database/schema/patch-2209-18-1.sql'
--- database/schema/patch-2209-18-1.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-18-1.sql	2012-05-17 02:59:27 +0000
@@ -0,0 +1,14 @@
+-- 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;
+
+ALTER TABLE ArchivePermission DROP CONSTRAINT one_target;
+
+ALTER TABLE ArchivePermission ADD COLUMN pocket INTEGER;
+
+ALTER TABLE ArchivePermission ADD CONSTRAINT one_target CHECK ((null_count(ARRAY[packageset, component, sourcepackagename, pocket]) = 3));
+
+COMMENT ON COLUMN ArchivePermission.pocket IS 'The pocket to which this permission applies.';
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 18, 1);