Thiago F. Pappacena has proposed merging
~pappacena/launchpad:oci-bug-indexes-backport into launchpad:master.
Commit message:
Backporting OCI bug support indexes creation from db-devel (already applied in
prod)
Requested reviews:
Launchpad code reviewers (launchpad-reviewers)
For more details, see:
https://code.launchpad.net/~pappacena/launchpad/+git/launchpad/+merge/401488
--
Your team Launchpad code reviewers is requested to review the proposed merge of
~pappacena/launchpad:oci-bug-indexes-backport into launchpad:master.
diff --git a/database/schema/patch-2210-22-1.sql b/database/schema/patch-2210-22-1.sql
new file mode 100644
index 000..7f4cedd
--- /dev/null
+++ b/database/schema/patch-2210-22-1.sql
@@ -0,0 +1,135 @@
+-- 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;
+
+-- Validate check constraint.
+ALTER TABLE BugTask VALIDATE CONSTRAINT bugtask_assignment_checks;
+
+ALTER TABLE BugSummary VALIDATE CONSTRAINT bugtask_assignment_checks;
+
+-- BugTask indexes.
+CREATE UNIQUE INDEX bugtask__ociproject__bug__key
+ON BugTask (ociproject, bug)
+WHERE ociproject IS NOT NULL;
+CREATE UNIQUE INDEX bugtask__ociprojectseries__bug__key
+ON BugTask (ociprojectseries, bug)
+WHERE ociprojectseries IS NOT NULL;
+
+-- BugTaskFlat indexes.
+CREATE INDEX bugtaskflat__ociproject__bug__idx
+ON BugTaskFlat (ociproject, bug)
+WHERE ociproject IS NOT NULL;
+CREATE INDEX bugtaskflat__ociproject__date_closed__bug__idx
+ON BugTaskFlat (ociproject, date_closed, bug DESC)
+WHERE ociproject IS NOT NULL;
+CREATE INDEX bugtaskflat__ociproject__date_last_updated__idx
+ON BugTaskFlat (ociproject, date_last_updated)
+WHERE ociproject IS NOT NULL;
+CREATE INDEX bugtaskflat__ociproject__datecreated__idx
+ON BugTaskFlat (ociproject, datecreated)
+WHERE ociproject IS NOT NULL;
+CREATE INDEX bugtaskflat__ociproject__heat__bug__idx
+ON BugTaskFlat (ociproject, heat, bug DESC)
+WHERE ociproject IS NOT NULL;
+CREATE INDEX bugtaskflat__ociproject__importance__bug__idx
+ON BugTaskFlat (ociproject, importance, bug DESC)
+WHERE ociproject IS NOT NULL;
+CREATE INDEX bugtaskflat__ociproject__latest_patch_uploaded__bug__idx
+ON BugTaskFlat (ociproject, latest_patch_uploaded, bug DESC)
+WHERE ociproject IS NOT NULL;
+CREATE INDEX bugtaskflat__ociproject__status__bug__idx
+ON BugTaskFlat (ociproject, status, bug DESC)
+WHERE ociproject IS NOT NULL;
+
+CREATE INDEX bugtaskflat__ociprojectseries__bug__idx
+ON BugTaskFlat (ociprojectseries, bug)
+WHERE ociprojectseries IS NOT NULL;
+CREATE INDEX bugtaskflat__ociprojectseries__date_closed__bug__idx
+ON BugTaskFlat (ociprojectseries, date_closed, bug DESC)
+WHERE ociprojectseries IS NOT NULL;
+CREATE INDEX bugtaskflat__ociprojectseries__date_last_updated__idx
+ON BugTaskFlat (ociprojectseries, date_last_updated)
+WHERE ociprojectseries IS NOT NULL;
+CREATE INDEX bugtaskflat__ociprojectseries__datecreated__idx
+ON BugTaskFlat (ociprojectseries, datecreated)
+WHERE ociprojectseries IS NOT NULL;
+CREATE INDEX bugtaskflat__ociprojectseries__heat__bug__idx
+ON BugTaskFlat (ociprojectseries, heat, bug DESC)
+WHERE ociprojectseries IS NOT NULL;
+CREATE INDEX bugtaskflat__ociprojectseries__importance__bug__idx
+ON BugTaskFlat (ociprojectseries, importance, bug DESC)
+WHERE ociprojectseries IS NOT NULL;
+CREATE INDEX bugtaskflat__ociprojectseries__latest_patch_uploaded__bug__idx
+ON BugTaskFlat (ociprojectseries, latest_patch_uploaded, bug DESC)
+WHERE ociprojectseries IS NOT NULL;
+CREATE INDEX bugtaskflat__ociprojectseries__status__bug__idx
+ON BugTaskFlat (ociprojectseries, status, bug DESC)
+WHERE ociprojectseries IS NOT NULL;
+
+
+-- BugSummary indexes.
+CREATE INDEX bugsummary__ociproject__idx
+ON BugSummary (ociproject)
+WHERE ociproject IS NOT NULL;
+CREATE INDEX bugsummary__ociprojectseries__idx
+ON BugSummary (ociprojectseries)
+WHERE ociprojectseries IS NOT NULL;
+
+
+-- Replacing previously renamed indexes.
+CREATE UNIQUE INDEX bugtask_distinct_sourcepackage_assignment
+ON BugTask (
+bug,
+COALESCE(sourcepackagename, -1),
+COALESCE(distroseries, -1),
+COALESCE(distribution, -1)
+)
+WHERE
+product IS NULL
+AND productseries IS NULL
+AND ociproject IS NULL
+AND ociprojectseries IS NULL;
+DROP INDEX old__bugtask_distinct_sourcepackage_assignment;
+
+
+CREATE UNIQUE INDEX bugtask__product__bug__key
+ON BugTask (product, bug)
+WHERE
+product IS NOT NULL
+AND ociproject IS NULL
+AND ociprojectseries IS NULL;
+DROP INDEX old__bugtask__product__bug__key;
+
+
+CREATE UNIQUE INDEX bugsummary__unique
+ON BugSummary (
+COALESCE(product, -1),
+COALESCE(productseries, -1),
+
