Merge ~cjwatson/launchpad:db-artifactory-publish-2 into launchpad:master

[Colin Watson]

Artifactory publishing: indexes

This will need to be applied live to production.

[William Grant (wgrant)]

review: Approve (db)

8cf09d6... by Colin Watson on 2022-04-06

Drop unnecessary binarypackagerelease__ci_build__idx

The non-NULL cases are covered by
binarypackagerelease__ci_build__bpn__key, and querying for the NULL
cases isn't interesting.

diff --git a/database/schema/patch-2210-44-0.sql b/database/schema/patch-2210-44-0.sql
index 47c163d..144ab1c 100644
--- a/database/schema/patch-2210-44-0.sql
+++ b/database/schema/patch-2210-44-0.sql
@@ -111,8 +111,6 @@ CREATE UNIQUE INDEX binarypackagerelease__build__bpn__key
 CREATE UNIQUE INDEX binarypackagerelease__ci_build__bpn__key
     ON BinaryPackageRelease (ci_build, binarypackagename)
     WHERE ci_build IS NOT NULL;
-CREATE INDEX binarypackagerelease__ci_build__idx
-    ON BinaryPackageRelease (ci_build);
 
 ALTER TABLE BinaryPackageRelease
     VALIDATE CONSTRAINT one_build,
diff --git a/database/schema/patch-2210-44-1.sql b/database/schema/patch-2210-44-1.sql
 new file mode 100644
index 0000000..a8d719c
--- /dev/null
+++ b/database/schema/patch-2210-44-1.sql
@@ -0,0 +1,34 @@
+-- Copyright 2022 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+-- STEP 2, HOT
+
+CREATE INDEX sourcepackagerelease__ci_build__idx
+    ON SourcePackageRelease (ci_build);
+
+ALTER TABLE SourcePackageRelease
+    VALIDATE CONSTRAINT at_most_one_build,
+    VALIDATE CONSTRAINT debian_columns;
+
+ALTER TABLE SourcePackagePublishingHistory
+    VALIDATE CONSTRAINT debian_columns,
+    VALIDATE CONSTRAINT no_debian_channel;
+
+CREATE UNIQUE INDEX binarypackagerelease__build__bpn__key
+    ON BinaryPackageRelease (build, binarypackagename)
+    WHERE build IS NOT NULL;
+CREATE UNIQUE INDEX binarypackagerelease__ci_build__bpn__key
+    ON BinaryPackageRelease (ci_build, binarypackagename)
+    WHERE ci_build IS NOT NULL;
+
+ALTER TABLE BinaryPackageRelease
+    VALIDATE CONSTRAINT one_build,
+    VALIDATE CONSTRAINT debian_columns;
+
+ALTER TABLE BinaryPackagePublishingHistory
+    VALIDATE CONSTRAINT debian_columns,
+    VALIDATE CONSTRAINT no_debian_channel;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 44, 1);