Merge lp:~cjwatson/launchpad/db-archive-index-by-hash into lp:launchpad/db-devel

[Colin Watson]

Add ArchiveFile table.

Add ArchiveFile table.

This lets us keep track of files that are published in an archive but that aren't part of a source or binary publication, such as index files. The immediate use of this will be to implement by-hash (hence the scheduled deletion date arrangements), but the same facility should also be useful as a step towards diskless archives.

[William Grant (wgrant)]

review: Approve (db)

=== added file 'database/schema/patch-2209-74-0.sql'
--- database/schema/patch-2209-74-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-74-0.sql	2016-03-18 15:01:46 +0000
@@ -0,0 +1,32 @@
+-- Copyright 2016 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 ArchiveFile (
+    id serial PRIMARY KEY,
+    archive integer NOT NULL REFERENCES archive ON DELETE CASCADE,
+    container text NOT NULL,
+    path text NOT NULL,
+    library_file integer NOT NULL REFERENCES libraryfilealias,
+    scheduled_deletion_date timestamp without time zone
+);
+
+COMMENT ON TABLE ArchiveFile IS 'A file in an archive.';
+COMMENT ON COLUMN ArchiveFile.archive IS 'The archive containing the file.';
+COMMENT ON COLUMN ArchiveFile.container IS 'An identifier for the component that manages this file.';
+COMMENT ON COLUMN ArchiveFile.path IS 'The path to the file within the published archive.';
+COMMENT ON COLUMN ArchiveFile.library_file IS 'The file in the librarian.';
+COMMENT ON COLUMN ArchiveFile.scheduled_deletion_date IS 'The date when this file should stop being published.';
+
+CREATE INDEX archivefile__archive__container__idx
+    ON ArchiveFile (archive, container);
+CREATE INDEX archivefile__archive__scheduled_deletion_date__container__idx
+    ON ArchiveFile (archive, scheduled_deletion_date, container)
+    WHERE scheduled_deletion_date IS NOT NULL;
+CREATE INDEX archivefile__archive__path__idx
+    ON ArchiveFile (archive, path);
+CREATE INDEX archivefile__library_file__idx
+    ON ArchiveFile (library_file);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 74, 0);

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2016-03-14 19:40:14 +0000
+++ database/schema/security.cfg	2016-03-18 15:01:46 +0000
@@ -129,6 +129,7 @@
 public.archivearch                      = SELECT, INSERT, UPDATE, DELETE
 public.archiveauthtoken                 = SELECT, INSERT, UPDATE
 public.archivedependency                = SELECT, INSERT, DELETE
+public.archivefile                      = SELECT, INSERT, UPDATE, DELETE
 public.archivejob                       = SELECT, INSERT, UPDATE, DELETE
 public.archivepermission                = SELECT, INSERT, UPDATE, DELETE
 public.archivesubscriber                = SELECT, INSERT, UPDATE
@@ -880,6 +881,7 @@
 public.archive                          = SELECT, UPDATE
 public.archivearch                      = SELECT
 public.archiveauthtoken                 = SELECT, UPDATE
+public.archivefile                      = SELECT, INSERT, UPDATE, DELETE
 public.archivepermission                = SELECT, INSERT
 public.archivesubscriber                = SELECT, UPDATE
 public.binarypackagepublishinghistory   = SELECT, INSERT, UPDATE, DELETE