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

Subscribers

People subscribed via source and target branches

to status/vote changes: