1
diff --git a/database/schema/patch-2210-22-1.sql b/database/schema/patch-2210-22-1.sql
2
0
new file mode 100644
0
new file mode 100644
3
index 0000000..7f4cedd
4
--- /dev/null
5
+++ b/database/schema/patch-2210-22-1.sql
6
@@ -0,0 +1,135 @@
7
1
-- Copyright 2020 Canonical Ltd.  This software is licensed under the
8
2
-- GNU Affero General Public License version 3 (see the file LICENSE).
9
3
10
4
SET client_min_messages=ERROR;
11
5
12
6
-- Validate check constraint.
13
7
ALTER TABLE BugTask VALIDATE CONSTRAINT bugtask_assignment_checks;
14
8
15
9
ALTER TABLE BugSummary VALIDATE CONSTRAINT bugtask_assignment_checks;
16
10
17
11
-- BugTask indexes.
18
12
CREATE UNIQUE INDEX bugtask__ociproject__bug__key
19
13
    ON BugTask (ociproject, bug)
20
14
    WHERE ociproject IS NOT NULL;
21
15
CREATE UNIQUE INDEX bugtask__ociprojectseries__bug__key
22
16
    ON BugTask (ociprojectseries, bug)
23
17
    WHERE ociprojectseries IS NOT NULL;
24
18
25
19
-- BugTaskFlat indexes.
26
20
CREATE INDEX bugtaskflat__ociproject__bug__idx
27
21
    ON BugTaskFlat (ociproject, bug)
28
22
    WHERE ociproject IS NOT NULL;
29
23
CREATE INDEX bugtaskflat__ociproject__date_closed__bug__idx
30
24
    ON BugTaskFlat (ociproject, date_closed, bug DESC)
31
25
    WHERE ociproject IS NOT NULL;
32
26
CREATE INDEX bugtaskflat__ociproject__date_last_updated__idx
33
27
    ON BugTaskFlat (ociproject, date_last_updated)
34
28
    WHERE ociproject IS NOT NULL;
35
29
CREATE INDEX bugtaskflat__ociproject__datecreated__idx
36
30
    ON BugTaskFlat (ociproject, datecreated)
37
31
    WHERE ociproject IS NOT NULL;
38
32
CREATE INDEX bugtaskflat__ociproject__heat__bug__idx
39
33
    ON BugTaskFlat (ociproject, heat, bug DESC)
40
34
    WHERE ociproject IS NOT NULL;
41
35
CREATE INDEX bugtaskflat__ociproject__importance__bug__idx
42
36
    ON BugTaskFlat (ociproject, importance, bug DESC)
43
37
    WHERE ociproject IS NOT NULL;
44
38
CREATE INDEX bugtaskflat__ociproject__latest_patch_uploaded__bug__idx
45
39
    ON BugTaskFlat (ociproject, latest_patch_uploaded, bug DESC)
46
40
    WHERE ociproject IS NOT NULL;
47
41
CREATE INDEX bugtaskflat__ociproject__status__bug__idx
48
42
    ON BugTaskFlat (ociproject, status, bug DESC)
49
43
    WHERE ociproject IS NOT NULL;
50
44
51
45
CREATE INDEX bugtaskflat__ociprojectseries__bug__idx
52
46
    ON BugTaskFlat (ociprojectseries, bug)
53
47
    WHERE ociprojectseries IS NOT NULL;
54
48
CREATE INDEX bugtaskflat__ociprojectseries__date_closed__bug__idx
55
49
    ON BugTaskFlat (ociprojectseries, date_closed, bug DESC)
56
50
    WHERE ociprojectseries IS NOT NULL;
57
51
CREATE INDEX bugtaskflat__ociprojectseries__date_last_updated__idx
58
52
    ON BugTaskFlat (ociprojectseries, date_last_updated)
59
53
    WHERE ociprojectseries IS NOT NULL;
60
54
CREATE INDEX bugtaskflat__ociprojectseries__datecreated__idx
61
55
    ON BugTaskFlat (ociprojectseries, datecreated)
62
56
    WHERE ociprojectseries IS NOT NULL;
63
57
CREATE INDEX bugtaskflat__ociprojectseries__heat__bug__idx
64
58
    ON BugTaskFlat (ociprojectseries, heat, bug DESC)
65
59
    WHERE ociprojectseries IS NOT NULL;
66
60
CREATE INDEX bugtaskflat__ociprojectseries__importance__bug__idx
67
61
    ON BugTaskFlat (ociprojectseries, importance, bug DESC)
68
62
    WHERE ociprojectseries IS NOT NULL;
69
63
CREATE INDEX bugtaskflat__ociprojectseries__latest_patch_uploaded__bug__idx
70
64
    ON BugTaskFlat (ociprojectseries, latest_patch_uploaded, bug DESC)
71
65
    WHERE ociprojectseries IS NOT NULL;
72
66
CREATE INDEX bugtaskflat__ociprojectseries__status__bug__idx
73
67
    ON BugTaskFlat (ociprojectseries, status, bug DESC)
74
68
    WHERE ociprojectseries IS NOT NULL;
75
69
76
70
77
71
-- BugSummary indexes.
78
72
CREATE INDEX bugsummary__ociproject__idx
79
73
    ON BugSummary (ociproject)
80
74
    WHERE ociproject IS NOT NULL;
81
75
CREATE INDEX bugsummary__ociprojectseries__idx
82
76
    ON BugSummary (ociprojectseries)
83
77
    WHERE ociprojectseries IS NOT NULL;
84
78
85
79
86
80
-- Replacing previously renamed indexes.
87
81
CREATE UNIQUE INDEX bugtask_distinct_sourcepackage_assignment
88
82
    ON BugTask (
89
83
        bug,
90
84
        COALESCE(sourcepackagename, -1),
91
85
        COALESCE(distroseries, -1),
92
86
        COALESCE(distribution, -1)
93
87
    )
94
88
    WHERE
95
89
        product IS NULL
96
90
        AND productseries IS NULL
97
91
        AND ociproject IS NULL
98
92
        AND ociprojectseries IS NULL;
99
93
DROP INDEX old__bugtask_distinct_sourcepackage_assignment;
100
94
101
95
102
96
CREATE UNIQUE INDEX bugtask__product__bug__key
103
97
    ON BugTask (product, bug)
104
98
    WHERE
105
99
        product IS NOT NULL
106
100
        AND ociproject IS NULL
107
101
        AND ociprojectseries IS NULL;
108
102
DROP INDEX old__bugtask__product__bug__key;
109
103
110
104
111
105
CREATE UNIQUE INDEX bugsummary__unique
112
106
    ON BugSummary (
113
107
        COALESCE(product, -1),
114
108
        COALESCE(productseries, -1),
115
109
        COALESCE(distribution, -1),
116
110
        COALESCE(distroseries, -1),
117
111
        COALESCE(sourcepackagename, -1),
118
112
        COALESCE(ociproject, -1),
119
113
        COALESCE(ociprojectseries, -1),
120
114
        status,
121
115
        importance,
122
116
        has_patch,
123
117
        COALESCE(tag, ''::text),
124
118
        COALESCE(milestone, -1),
125
119
        COALESCE(viewed_by, -1),
126
120
        COALESCE(access_policy, -1)
127
121
    );
128
122
DROP INDEX old__bugsummary__unique;
129
123
130
124
131
125
CREATE INDEX bugsummaryjournal__full__idx
132
126
    ON BugSummaryJournal (
133
127
        status, product, productseries, distribution, distroseries,
134
128
        sourcepackagename, ociproject, ociprojectseries, viewed_by, milestone,
135
129
        tag
136
130
    );
137
131
138
132
DROP INDEX old__bugsummaryjournal__full__idx;
139
133
140
134
141
135
INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 22, 1);
Status:	Merged
Approved by:	Colin Watson on 2021-04-20
Approved revision:	411d991a229f27aa0268faa53ca386c04a45ed6e
Merge reported by:	Otto Co-Pilot
Merged at revision:	not available
Proposed branch:	~pappacena/launchpad:oci-bug-indexes
Merge into:	launchpad:db-devel
Prerequisite:	~pappacena/launchpad:oci-bug-add-oci-columns
Diff against target:	141 lines (+135/-0) 1 file modified database/schema/patch-2210-22-1.sql (+135/-0)
Related bugs:	Link a bug report
Reviewer	Review Type	Date Requested	Status
William Grant	db		Approve on 2021-04-13
Colin Watson (community)	db	2020-11-10	Approve on 2021-04-09
Review via email: mp+393579@code.launchpad.net