launchpad-reviewers team mailing list archive

[Colin Watson <cjwatson@xxxxxxxxxxxxx>]

Colin Watson has proposed merging ~cjwatson/launchpad:db-built-using into launchpad:db-devel.

Commit message:
Add BinarySourceReference table

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)
Related bugs:
  Bug #1868558 in Launchpad itself: "Honour Built-Using field"
  https://bugs.launchpad.net/launchpad/+bug/1868558

For more details, see:
https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/381036

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.
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of ~cjwatson/launchpad:db-built-using into launchpad:db-devel.

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);
diff --git a/database/schema/security.cfg b/database/schema/security.cfg
index 91b8407..a149b19 100644
--- a/database/schema/security.cfg
+++ b/database/schema/security.cfg
@@ -1202,12 +1202,14 @@ public.accesspolicyartifact             = SELECT, INSERT, DELETE
 public.account                          = SELECT, INSERT, UPDATE
 public.archive                          = SELECT, INSERT, UPDATE
 public.archivearch                      = SELECT, INSERT, UPDATE, DELETE
+public.archivedependency                = SELECT
 public.archivejob                       = SELECT, INSERT
 public.binarypackagebuild               = SELECT, INSERT, UPDATE
 public.binarypackagefile                = SELECT, INSERT, UPDATE
 public.binarypackagename                = SELECT, INSERT, UPDATE
 public.binarypackagepublishinghistory   = SELECT, INSERT, UPDATE, DELETE
 public.binarypackagerelease             = SELECT, INSERT, UPDATE
+public.binarysourcereference            = SELECT, INSERT
 public.branch                           = SELECT, INSERT, UPDATE
 public.bug                              = SELECT, INSERT, UPDATE
 public.bugactivity                      = SELECT, INSERT, UPDATE
@@ -1370,6 +1372,7 @@ public.account                          = SELECT, INSERT
 public.answercontact                    = SELECT
 public.archive                          = SELECT, INSERT, UPDATE
 public.archivearch                      = SELECT, INSERT, UPDATE
+public.archivedependency                = SELECT
 public.archivefile                      = SELECT
 public.archivejob                       = SELECT, INSERT
 public.archivepermission                = SELECT
@@ -1378,6 +1381,7 @@ public.binarypackagefile                = SELECT, INSERT
 public.binarypackagename                = SELECT, INSERT
 public.binarypackagepublishinghistory   = SELECT
 public.binarypackagerelease             = SELECT, INSERT
+public.binarysourcereference            = SELECT, INSERT
 public.bug                              = SELECT, UPDATE
 public.bugactivity                      = SELECT, INSERT
 public.bugaffectsperson                 = SELECT, INSERT, UPDATE, DELETE