Merge ~pappacena/launchpad:cold-db-patch-ociproject-project-pillar into launchpad:master

[Thiago F. Pappacena]

Changing GitRepository indexes to support projects as pillars of OCI projects.

This hot patch should be applied manually, with CONCURRENTLY on the indexes creation.

[Colin Watson (cjwatson)]

In our terminology, this is a hot patch, not a cold patch (2210-08-8 is a cold patch). It would be a good idea to rename the branch and adjust the MP description to avoid possible confusion.

review: Approve

e50b04e... by Thiago F. Pappacena on 2020-05-27

Fixing missing condition on index creation

Unmerged commits

e50b04e... by Thiago F. Pappacena on 2020-05-27

Fixing missing condition on index creation

a927d0f... by Thiago F. Pappacena on 2020-05-21

Index and unique constraints for GitRepository.oci_project

ccaed55... by Thiago F. Pappacena on 2020-05-13

Fixing syntax error

ea3a8e3... by Thiago F. Pappacena on 2020-05-13

GitRepository index changes for project as pillar for OCIProject

diff --git a/database/schema/patch-2210-08-9.sql b/database/schema/patch-2210-08-9.sql
new file mode 100644
index 0000000..414b516
--- /dev/null
+++ b/database/schema/patch-2210-08-9.sql
@@ -0,0 +1,38 @@
+-- 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 new UNIQUE constraints to replace existing distribution + ociprojectname ones.
+CREATE UNIQUE INDEX gitrepository__owner__oci_project__name__key
+    ON GitRepository (owner, oci_project, name)
+    WHERE oci_project IS NOT NULL;
+
+CREATE UNIQUE INDEX gitrepository__owner__oci_project__owner_default__key
+    ON GitRepository (owner, oci_project)
+    WHERE oci_project IS NOT NULL AND owner_default;
+
+CREATE UNIQUE INDEX gitrepository__oci_project__target_default__key
+    ON GitRepository (oci_project)
+    WHERE oci_project IS NOT NULL AND target_default;
+
+
+-- Create new indexes to replace existing ociprojectname ones
+CREATE INDEX "gitrepository__oci_project__date_last_modified__idx"
+    ON GitRepository (oci_project, date_last_modified)
+    WHERE oci_project IS NOT NULL;
+
+CREATE INDEX "gitrepository__oci_project__id__idx"
+    ON GitRepository (oci_project, id)
+    WHERE oci_project IS NOT NULL;
+
+CREATE INDEX "gitrepository__owner__oci_project__date_last_modified__idx"
+    ON GitRepository (owner, oci_project, date_last_modified)
+    WHERE oci_project IS NOT NULL;
+
+CREATE INDEX "gitrepository__owner__oci_project__id__idx"
+    ON GitRepository (owner, oci_project, id)
+    WHERE oci_project IS NOT NULL;
+
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 8, 9);