launchpad-reviewers team mailing list archive

[Andrey Fedoseev <mp+434688@xxxxxxxxxxxxxxxxxx>]

Andrey Fedoseev has proposed merging ~andrey-fedoseev/launchpad:db-bug-task-channel-indexes into launchpad:db-devel with ~andrey-fedoseev/launchpad:db-bug-task-channel as a prerequisite.

Commit message:
Include the new `channel` to the indexes

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~andrey-fedoseev/launchpad/+git/launchpad/+merge/434688
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of ~andrey-fedoseev/launchpad:db-bug-task-channel-indexes into launchpad:db-devel.

diff --git a/database/schema/patch-2211-15-1.sql b/database/schema/patch-2211-15-1.sql
new file mode 100644
index 0000000..cddc62d
--- /dev/null
+++ b/database/schema/patch-2211-15-1.sql
@@ -0,0 +1,61 @@
+-- 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;
+
+-- Replacing previously renamed indexes.
+CREATE UNIQUE INDEX bugtask_distinct_sourcepackage_assignment
+    ON bugtask (
+        bug,
+        COALESCE(sourcepackagename, '-1'::integer),
+        COALESCE(distroseries, '-1'::integer),
+        COALESCE(distribution, '-1'::integer),
+        COALESCE(channel, '{}'::jsonb)
+    )
+    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 bugsummary__unique
+    ON bugsummary (
+        COALESCE(product, '-1'::integer),
+        COALESCE(productseries, '-1'::integer),
+        COALESCE(distribution, '-1'::integer),
+        COALESCE(distroseries, '-1'::integer),
+        COALESCE(sourcepackagename, '-1'::integer),
+        COALESCE(ociproject, '-1'::integer),
+        COALESCE(ociprojectseries, '-1'::integer),
+        COALESCE(channel, '{}'::jsonb),
+        status,
+        importance,
+        has_patch,
+        COALESCE(tag, ''::text),
+        COALESCE(milestone, '-1'::integer),
+        COALESCE(viewed_by, '-1'::integer),
+        COALESCE(access_policy, '-1'::integer)
+);
+DROP INDEX old__bugsummary__unique;
+
+CREATE INDEX bugsummaryjournal__full__idx
+    ON bugsummaryjournal (
+        status,
+        product,
+        productseries,
+        distribution,
+        distroseries,
+        sourcepackagename,
+        ociproject,
+        ociprojectseries,
+        channel,
+        viewed_by,
+        milestone,
+        tag
+    );
+DROP INDEX old__bugsummaryjournal__full__idx;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 15, 1);