Launchpad itself

Merge ~andrey-fedoseev/launchpad:db-bug-task-channel into launchpad:db-devel

  1. Git
  2. lp:~andrey-fedoseev/launchpad
  3. db-bug-task-channel
  4. Merge into db-devel
Proposed by Andrey Fedoseev
Status: Needs review
Proposed branch: ~andrey-fedoseev/launchpad:db-bug-task-channel
Merge into: launchpad:db-devel
Diff against target: 548 lines (+542/-0)
1 file modified
database/schema/patch-2211-14-0.sql (+542/-0)
Reviewer Review Type Date Requested Status
Launchpad code reviewers Pending
Review via email: mp+434686@code.launchpad.net

Commit message

Add `channel` field to `BugTask` and its derived tables/views

To post a comment you must log in.

Unmerged commits

52d9000... by Andrey Fedoseev

Add `channel` field to `BugTask` and its derived tables/views

Succeeded
[SUCCEEDED] docs:0 (build)
[SUCCEEDED] lint:0 (build)
[SUCCEEDED] mypy:0 (build)
1 → 3 of 3 results First • Previous • Next • Last

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
diff --git a/database/schema/patch-2211-14-0.sql b/database/schema/patch-2211-14-0.sql
0new file mode 1006440new file mode 100644
index 0000000..719d4dd
--- /dev/null
+++ b/database/schema/patch-2211-14-0.sql
@@ -0,0 +1,542 @@
1-- Copyright 2022 Canonical Ltd. This software is licensed under the
2-- GNU Affero General Public License version 3 (see the file LICENSE).
3
4SET client_min_messages = ERROR;
5
6ALTER TABLE BugTask
7 ADD COLUMN channel jsonb;
8
9ALTER INDEX bugtask_distinct_sourcepackage_assignment
10 RENAME TO old__bugtask_distinct_sourcepackage_assignment;
11
12ALTER TABLE BugTaskFlat
13 ADD COLUMN channel jsonb;
14
15ALTER TABLE BugSummary
16 ADD COLUMN channel jsonb,
17 DROP CONSTRAINT bugtask_assignment_checks,
18 ADD CONSTRAINT bugtask_assignment_checks CHECK (
19 CASE
20 WHEN product IS NOT NULL THEN
21 productseries IS NULL
22 AND distribution IS NULL
23 AND distroseries IS NULL
24 AND sourcepackagename IS NULL
25 WHEN productseries IS NOT NULL THEN
26 distribution IS NULL
27 AND distroseries IS NULL
28 AND sourcepackagename IS NULL
29 AND ociproject IS NULL
30 AND ociprojectseries IS NULL
31 WHEN distribution IS NOT NULL THEN
32 distroseries IS NULL
33 WHEN distroseries IS NOT NULL THEN
34 ociproject IS NULL
35 AND ociprojectseries IS NULL
36 WHEN ociproject IS NOT NULL THEN
37 ociprojectseries IS NULL
38 AND (distribution IS NOT NULL OR product IS NOT NULL)
39 AND sourcepackagename IS NULL
40 WHEN ociprojectseries IS NOT NULL THEN
41 ociproject IS NULL
42 AND (distribution IS NOT NULL OR product IS NOT NULL)
43 AND sourcepackagename IS NULL
44 WHEN channel IS NOT NULL THEN
45 distroseries IS NOT NULL
46 AND sourcepackagename IS NOT NULL
47 ELSE false
48 END) NOT VALID;
49
50ALTER INDEX bugsummary__unique
51 RENAME TO old__bugsummary__unique;
52
53
54ALTER TABLE BugSummaryJournal ADD COLUMN channel jsonb;
55
56ALTER INDEX bugsummaryjournal__full__idx
57 RENAME TO old__bugsummaryjournal__full__idx;
58
59
60-- Functions
61
62CREATE OR REPLACE FUNCTION bugtask_flatten(task_id integer, check_only boolean)
63 RETURNS boolean
64 SECURITY DEFINER
65 SET search_path = public
66 LANGUAGE plpgsql
67 AS $$
68DECLARE
69 bug_row Bug%ROWTYPE;
70 task_row BugTask%ROWTYPE;
71 old_flat_row BugTaskFlat%ROWTYPE;
72 new_flat_row BugTaskFlat%ROWTYPE;
73 _product_active boolean;
74 _access_policies integer[];
75 _access_grants integer[];
76BEGIN
77 -- This is the master function to update BugTaskFlat, but there are
78 -- maintenance triggers and jobs on the involved tables that update
79 -- it directly. Any changes here probably require a corresponding
80 -- change in other trigger functions.
81
82 SELECT * INTO task_row FROM BugTask WHERE id = task_id;
83 SELECT * INTO old_flat_row FROM BugTaskFlat WHERE bugtask = task_id;
84
85 -- If the task doesn't exist, ensure that there's no flat row.
86 IF task_row.id IS NULL THEN
87 IF old_flat_row.bugtask IS NOT NULL THEN
88 IF NOT check_only THEN
89 DELETE FROM BugTaskFlat WHERE bugtask = task_id;
90 END IF;
91 RETURN FALSE;
92 ELSE
93 RETURN TRUE;
94 END IF;
95 END IF;
96
97 SELECT * INTO bug_row FROM bug WHERE id = task_row.bug;
98
99 -- If it's a product(series) task, we must consult the active flag.
100 IF task_row.product IS NOT NULL THEN
101 SELECT product.active INTO _product_active
102 FROM product WHERE product.id = task_row.product LIMIT 1;
103 ELSIF task_row.productseries IS NOT NULL THEN
104 SELECT product.active INTO _product_active
105 FROM
106 product
107 JOIN productseries ON productseries.product = product.id
108 WHERE productseries.id = task_row.productseries LIMIT 1;
109 END IF;
110
111 SELECT policies, grants
112 INTO _access_policies, _access_grants
113 FROM bug_build_access_cache(bug_row.id, bug_row.information_type)
114 AS (policies integer[], grants integer[]);
115
116 -- Compile the new flat row.
117 SELECT task_row.id, bug_row.id, task_row.datecreated,
118 bug_row.duplicateof, bug_row.owner, bug_row.fti,
119 bug_row.information_type, bug_row.date_last_updated,
120 bug_row.heat, task_row.product, task_row.productseries,
121 task_row.distribution, task_row.distroseries,
122 task_row.sourcepackagename, task_row.status,
123 task_row.importance, task_row.assignee,
124 task_row.milestone, task_row.owner,
125 COALESCE(_product_active, TRUE),
126 _access_policies,
127 _access_grants,
128 bug_row.latest_patch_uploaded, task_row.date_closed,
129 task_row.ociproject, task_row.ociprojectseries,
130 task_row.channel
131 INTO new_flat_row;
132
133 -- Calculate the necessary updates.
134 IF old_flat_row.bugtask IS NULL THEN
135 IF NOT check_only THEN
136 INSERT INTO BugTaskFlat VALUES (new_flat_row.*);
137 END IF;
138 RETURN FALSE;
139 ELSIF new_flat_row != old_flat_row THEN
140 IF NOT check_only THEN
141 UPDATE BugTaskFlat SET
142 bug = new_flat_row.bug,
143 datecreated = new_flat_row.datecreated,
144 duplicateof = new_flat_row.duplicateof,
145 bug_owner = new_flat_row.bug_owner,
146 fti = new_flat_row.fti,
147 information_type = new_flat_row.information_type,
148 date_last_updated = new_flat_row.date_last_updated,
149 heat = new_flat_row.heat,
150 product = new_flat_row.product,
151 productseries = new_flat_row.productseries,
152 distribution = new_flat_row.distribution,
153 distroseries = new_flat_row.distroseries,
154 sourcepackagename = new_flat_row.sourcepackagename,
155 status = new_flat_row.status,
156 importance = new_flat_row.importance,
157 assignee = new_flat_row.assignee,
158 milestone = new_flat_row.milestone,
159 owner = new_flat_row.owner,
160 active = new_flat_row.active,
161 access_policies = new_flat_row.access_policies,
162 access_grants = new_flat_row.access_grants,
163 date_closed = new_flat_row.date_closed,
164 latest_patch_uploaded = new_flat_row.latest_patch_uploaded,
165 ociproject = new_flat_row.ociproject,
166 ociprojectseries = new_flat_row.ociprojectseries,
167 channel = new_flat_row.channel
168 WHERE bugtask = new_flat_row.bugtask;
169 END IF;
170 RETURN FALSE;
171 ELSE
172 RETURN TRUE;
173 END IF;
174END;
175$$;
176
177CREATE OR REPLACE FUNCTION bug_summary_inc(d bugsummary)
178 RETURNS VOID
179 LANGUAGE plpgsql
180 AS $$
181BEGIN
182 -- Shameless adaption from postgresql manual
183 LOOP
184 -- first try to update the row
185 UPDATE BugSummary SET count = count + d.count
186 WHERE
187 product IS NOT DISTINCT FROM $1.product
188 AND productseries IS NOT DISTINCT FROM $1.productseries
189 AND distribution IS NOT DISTINCT FROM $1.distribution
190 AND distroseries IS NOT DISTINCT FROM $1.distroseries
191 AND sourcepackagename IS NOT DISTINCT FROM $1.sourcepackagename
192 AND ociproject IS NOT DISTINCT FROM $1.ociproject
193 AND ociprojectseries IS NOT DISTINCT FROM $1.ociprojectseries
194 AND channel IS NOT DISTINCT FROM $1.channel
195 AND viewed_by IS NOT DISTINCT FROM $1.viewed_by
196 AND tag IS NOT DISTINCT FROM $1.tag
197 AND status = $1.status
198 AND ((milestone IS NULL AND $1.milestone IS NULL)
199 OR milestone = $1.milestone)
200 AND importance = $1.importance
201 AND has_patch = $1.has_patch
202 AND access_policy IS NOT DISTINCT FROM $1.access_policy;
203 IF found THEN
204 RETURN;
205 END IF;
206 -- not there, so try to insert the key
207 -- if someone else inserts the same key concurrently,
208 -- we could get a unique-key failure
209 BEGIN
210 INSERT INTO BugSummary(
211 count, product, productseries, distribution,
212 distroseries, sourcepackagename,
213 ociproject, ociprojectseries, channel,
214 viewed_by, tag,
215 status, milestone, importance, has_patch, access_policy)
216 VALUES (
217 d.count, d.product, d.productseries, d.distribution,
218 d.distroseries, d.sourcepackagename,
219 d.ociproject, d.ociprojectseries, d.channel,
220 d.viewed_by, d.tag,
221 d.status, d.milestone, d.importance, d.has_patch,
222 d.access_policy);
223 RETURN;
224 EXCEPTION WHEN unique_violation THEN
225 -- do nothing, and loop to try the UPDATE again
226 END;
227 END LOOP;
228END;
229$$;
230
231CREATE OR REPLACE FUNCTION bug_summary_dec(bugsummary)
232 RETURNS VOID
233 LANGUAGE sql
234 AS $$
235 -- We own the row reference, so in the absence of bugs this cannot
236 -- fail - just decrement the row.
237 UPDATE BugSummary SET count = count + $1.count
238 WHERE
239 ((product IS NULL AND $1.product IS NULL)
240 OR product = $1.product)
241 AND ((productseries IS NULL AND $1.productseries IS NULL)
242 OR productseries = $1.productseries)
243 AND ((distribution IS NULL AND $1.distribution IS NULL)
244 OR distribution = $1.distribution)
245 AND ((distroseries IS NULL AND $1.distroseries IS NULL)
246 OR distroseries = $1.distroseries)
247 AND ((sourcepackagename IS NULL AND $1.sourcepackagename IS NULL)
248 OR sourcepackagename = $1.sourcepackagename)
249 AND ((ociproject IS NULL AND $1.ociproject IS NULL)
250 OR ociproject = $1.ociproject)
251 AND ((ociprojectseries IS NULL AND $1.ociprojectseries IS NULL)
252 OR ociprojectseries = $1.ociprojectseries)
253 AND ((channel IS NULL AND $1.channel IS NULL)
254 OR channel = $1.channel)
255 AND ((viewed_by IS NULL AND $1.viewed_by IS NULL)
256 OR viewed_by = $1.viewed_by)
257 AND ((tag IS NULL AND $1.tag IS NULL)
258 OR tag = $1.tag)
259 AND status = $1.status
260 AND ((milestone IS NULL AND $1.milestone IS NULL)
261 OR milestone = $1.milestone)
262 AND importance = $1.importance
263 AND has_patch = $1.has_patch
264 AND access_policy IS NOT DISTINCT FROM $1.access_policy;
265$$;
266
267CREATE OR REPLACE FUNCTION bugsummary_rollup_journal(batchsize integer DEFAULT NULL::integer)
268 RETURNS VOID
269 SECURITY DEFINER
270 SET search_path = public
271 LANGUAGE plpgsql
272 AS $$
273DECLARE
274 d bugsummary%ROWTYPE;
275 max_id integer;
276BEGIN
277 -- Lock so we don't content with other invocations of this
278 -- function. We can happily lock the BugSummary table for writes
279 -- as this function is the only thing that updates that table.
280 -- BugSummaryJournal remains unlocked so nothing should be blocked.
281 LOCK TABLE BugSummary IN ROW EXCLUSIVE MODE;
282
283 IF batchsize IS NULL THEN
284 SELECT MAX(id) INTO max_id FROM BugSummaryJournal;
285 ELSE
286 SELECT MAX(id) INTO max_id FROM (
287 SELECT id FROM BugSummaryJournal ORDER BY id LIMIT batchsize
288 ) AS Whatever;
289 END IF;
290
291 FOR d IN
292 SELECT
293 NULL as id,
294 SUM(count),
295 product,
296 productseries,
297 distribution,
298 distroseries,
299 sourcepackagename,
300 viewed_by,
301 tag,
302 status,
303 milestone,
304 importance,
305 has_patch,
306 access_policy,
307 ociproject,
308 ociprojectseries,
309 channel
310 FROM BugSummaryJournal
311 WHERE id <= max_id
312 GROUP BY
313 product, productseries, distribution, distroseries,
314 sourcepackagename, ociproject, ociprojectseries, channel,
315 viewed_by, tag, status, milestone,
316 importance, has_patch, access_policy
317 HAVING sum(count) <> 0
318 LOOP
319 IF d.count < 0 THEN
320 PERFORM bug_summary_dec(d);
321 ELSIF d.count > 0 THEN
322 PERFORM bug_summary_inc(d);
323 END IF;
324 END LOOP;
325
326 -- Clean out any counts we reduced to 0.
327 DELETE FROM BugSummary WHERE count=0;
328 -- Clean out the journal entries we have handled.
329 DELETE FROM BugSummaryJournal WHERE id <= max_id;
330END;
331$$;
332
333CREATE OR REPLACE FUNCTION bugtask_maintain_bugtaskflat_trig()
334 RETURNS TRIGGER
335 SECURITY DEFINER
336 SET search_path = public
337 LANGUAGE plpgsql
338 AS $$
339BEGIN
340 IF TG_OP = 'INSERT' THEN
341 PERFORM bugtask_flatten(NEW.id, FALSE);
342 ELSIF TG_OP = 'UPDATE' THEN
343 IF NEW.bug != OLD.bug THEN
344 RAISE EXCEPTION 'cannot move bugtask to a different bug';
345 ELSIF (NEW.product IS DISTINCT FROM OLD.product
346 OR NEW.productseries IS DISTINCT FROM OLD.productseries) THEN
347 -- product.active may differ. Do a full update.
348 PERFORM bugtask_flatten(NEW.id, FALSE);
349 ELSIF (
350 NEW.datecreated IS DISTINCT FROM OLD.datecreated
351 OR NEW.product IS DISTINCT FROM OLD.product
352 OR NEW.productseries IS DISTINCT FROM OLD.productseries
353 OR NEW.distribution IS DISTINCT FROM OLD.distribution
354 OR NEW.distroseries IS DISTINCT FROM OLD.distroseries
355 OR NEW.sourcepackagename IS DISTINCT FROM OLD.sourcepackagename
356 OR NEW.ociproject IS DISTINCT FROM OLD.ociproject
357 OR NEW.ociprojectseries IS DISTINCT FROM OLD.ociprojectseries
358 OR NEW.channel IS DISTINCT FROM OLD.channel
359 OR NEW.status IS DISTINCT FROM OLD.status
360 OR NEW.importance IS DISTINCT FROM OLD.importance
361 OR NEW.assignee IS DISTINCT FROM OLD.assignee
362 OR NEW.milestone IS DISTINCT FROM OLD.milestone
363 OR NEW.owner IS DISTINCT FROM OLD.owner
364 OR NEW.date_closed IS DISTINCT FROM OLD.date_closed) THEN
365 -- Otherwise just update the columns from bugtask.
366 -- Access policies and grants may have changed due to target
367 -- transitions, but an earlier trigger will already have
368 -- mirrored them to all relevant flat tasks.
369 UPDATE BugTaskFlat SET
370 datecreated = NEW.datecreated,
371 product = NEW.product,
372 productseries = NEW.productseries,
373 distribution = NEW.distribution,
374 distroseries = NEW.distroseries,
375 sourcepackagename = NEW.sourcepackagename,
376 ociproject = NEW.ociproject,
377 ociprojectseries = NEW.ociprojectseries,
378 channel = NEW.channel,
379 status = NEW.status,
380 importance = NEW.importance,
381 assignee = NEW.assignee,
382 milestone = NEW.milestone,
383 owner = NEW.owner,
384 date_closed = NEW.date_closed
385 WHERE bugtask = NEW.id;
386 END IF;
387 ELSIF TG_OP = 'DELETE' THEN
388 PERFORM bugtask_flatten(OLD.id, FALSE);
389 END IF;
390 RETURN NULL;
391END;
392$$;
393
394DROP FUNCTION bugsummary_targets;
395CREATE FUNCTION bugsummary_targets(btf_row bugtaskflat)
396 RETURNS TABLE(
397 product integer,
398 productseries integer,
399 distribution integer,
400 distroseries integer,
401 sourcepackagename integer,
402 ociproject integer,
403 ociprojectseries integer,
404 channel jsonb
405 )
406 IMMUTABLE
407 LANGUAGE sql
408 AS $$
409 -- Include a sourcepackagename-free/ociproject(series)-free task if this
410 -- one has a sourcepackagename/ociproject(series), so package tasks are
411 -- also counted in their distro/series.
412 SELECT
413 $1.product, $1.productseries, $1.distribution,
414 $1.distroseries, $1.sourcepackagename,
415 $1.ociproject, $1.ociprojectseries, $1.channel
416 UNION -- Implicit DISTINCT
417 SELECT
418 $1.product, $1.productseries, $1.distribution,
419 $1.distroseries, NULL, NULL, NULL, NULL;
420$$;
421
422CREATE OR REPLACE FUNCTION bugsummary_locations(btf_row bugtaskflat, tags text[])
423 RETURNS SETOF bugsummaryjournal
424 LANGUAGE plpgsql
425 AS $$
426BEGIN
427 IF btf_row.duplicateof IS NOT NULL THEN
428 RETURN;
429 END IF;
430 RETURN QUERY
431 SELECT
432 CAST(NULL AS integer) AS id,
433 CAST(1 AS integer) AS count,
434 bug_targets.product, bug_targets.productseries,
435 bug_targets.distribution, bug_targets.distroseries,
436 bug_targets.sourcepackagename,
437 bug_viewers.viewed_by, bug_tags.tag, btf_row.status,
438 btf_row.milestone, btf_row.importance,
439 btf_row.latest_patch_uploaded IS NOT NULL AS has_patch,
440 bug_viewers.access_policy,
441 bug_targets.ociproject, bug_targets.ociprojectseries,
442 bug_targets.channel
443 FROM
444 bugsummary_targets(btf_row) AS bug_targets,
445 unnest(tags) AS bug_tags (tag),
446 bugsummary_viewers(btf_row) AS bug_viewers;
447END;
448$$;
449
450CREATE OR REPLACE FUNCTION bugsummary_insert_journals(journals bugsummaryjournal[])
451 RETURNS VOID
452 LANGUAGE sql
453 AS $$
454 -- We sum the rows here to minimise the number of inserts into the
455 -- journal, as in the case of UPDATE statement we may have -1s and +1s
456 -- cancelling each other out.
457 INSERT INTO BugSummaryJournal(
458 count, product, productseries, distribution, distroseries,
459 sourcepackagename, ociproject, ociprojectseries, channel,
460 viewed_by, tag, status, milestone, importance, has_patch,
461 access_policy)
462 SELECT
463 SUM(count), product, productseries, distribution, distroseries,
464 sourcepackagename, ociproject, ociprojectseries, channel,
465 viewed_by, tag, status, milestone, importance, has_patch,
466 access_policy
467 FROM unnest(journals)
468 GROUP BY
469 product, productseries, distribution, distroseries,
470 sourcepackagename, ociproject, ociprojectseries, channel,
471 viewed_by, tag, status, milestone, importance, has_patch,
472 access_policy
473 HAVING SUM(count) != 0;
474$$;
475
476-- Views
477
478-- Combined view so we don't have to manually collate rows from both tables.
479-- Note that we flip the sign of the id column of BugSummaryJournal to avoid
480-- clashes. This is enough to keep Storm happy as it never needs to update
481-- this table, and there are no other suitable primary keys.
482-- We don't SUM() rows here to ensure PostgreSQL has the most hope of
483-- generating good query plans when we query this view.
484CREATE OR REPLACE VIEW CombinedBugSummary (
485 id,
486 count,
487 product,
488 productseries,
489 distribution,
490 distroseries,
491 sourcepackagename,
492 viewed_by,
493 tag,
494 status,
495 milestone,
496 importance,
497 has_patch,
498 access_policy,
499 ociproject,
500 ociprojectseries,
501 channel
502)
503AS
504SELECT id,
505 count,
506 product,
507 productseries,
508 distribution,
509 distroseries,
510 sourcepackagename,
511 viewed_by,
512 tag,
513 status,
514 milestone,
515 importance,
516 has_patch,
517 access_policy,
518 ociproject,
519 ociprojectseries,
520 channel
521FROM bugsummary
522UNION ALL
523SELECT -id AS id,
524 count,
525 product,
526 productseries,
527 distribution,
528 distroseries,
529 sourcepackagename,
530 viewed_by,
531 tag,
532 status,
533 milestone,
534 importance,
535 has_patch,
536 access_policy,
537 ociproject,
538 ociprojectseries,
539 channel
540FROM bugsummaryjournal;
541
542INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 14, 0);

Subscribers

People subscribed via source and target branches

to status/vote changes: