Merge ~cjwatson/launchpad:db-built-using into launchpad:db-devel

[Colin Watson]

Add BinarySourceReference table

This will be used to implement the Built-Using field in archives.

The grants for ArchiveDependency are needed because anything that creates BinarySourceReference rows from package relationship fields ("source-package-name (= version)") needs to work out which SPR was actually used by the build. It's not clear how to do this completely unambiguously, but we can derive a good approximation by trying the build's archive dependencies in order. To support this, anything that creates BinarySourceReferences needs to be able to read ArchiveDependency.

[William Grant (wgrant)]

The permission changes (and corresponding commit message paragraph) feel like they would have fit better in the model branch, but looks good anyway.

review: Approve (db)

[Colin Watson (cjwatson)]

OK, I've moved the permission changes over to the model branch, adjusted its commit message, and rebased that stack.

diff --git a/database/schema/patch-2210-13-0.sql b/database/schema/patch-2210-13-0.sql
new file mode 100644
index 0000000..0889468
--- /dev/null
+++ b/database/schema/patch-2210-13-0.sql
@@ -0,0 +1,25 @@
+-- Copyright 2020 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 BinarySourceReference (
+    id serial PRIMARY KEY,
+    binary_package_release integer NOT NULL REFERENCES binarypackagerelease,
+    source_package_release integer NOT NULL REFERENCES sourcepackagerelease,
+    reference_type integer NOT NULL
+);
+
+COMMENT ON TABLE BinarySourceReference IS 'A reference from a binary package release to a source package release.';
+COMMENT ON COLUMN BinarySourceReference.binary_package_release IS 'The referencing binary package release.';
+COMMENT ON COLUMN BinarySourceReference.source_package_release IS 'The referenced source package release.';
+COMMENT ON COLUMN BinarySourceReference.reference_type IS 'The type of the reference.';
+
+CREATE INDEX binarysourcereference__bpr__type__idx
+    ON BinarySourceReference (binary_package_release, reference_type);
+CREATE INDEX binarysourcereference__spr__type__idx
+    ON BinarySourceReference (source_package_release, reference_type);
+CREATE UNIQUE INDEX binarysourcereference__bpr__spr__type__key
+    ON BinarySourceReference (binary_package_release, source_package_release, reference_type);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 13, 0);