Launchpad itself

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

  1. Git
  2. lp:~andrey-fedoseev/launchpad
  3. db-bug-presence
  4. Merge into db-devel
Proposed by Andrey Fedoseev
Status: Needs review
Proposed branch: ~andrey-fedoseev/launchpad:db-bug-presence
Merge into: launchpad:db-devel
Diff against target: 88 lines (+71/-0)
2 files modified
database/schema/patch-2211-12-0.sql (+70/-0)
database/schema/security.cfg (+1/-0)
Reviewer Review Type Date Requested Status
Colin Watson (community) db Approve
William Grant db Pending
Review via email: mp+431711@code.launchpad.net

Commit message

Add `BugPresence` table

Description of the change

It represents a range of versions or git commits in which the bug was present.

To post a comment you must log in.
Revision history for this message
Colin Watson (cjwatson) wrote :

I haven't fully thought through all of this, and as discussed today there are some details around this that I want to discuss with stakeholders next week, but here's a first-pass review with some issues I noticed that you should be able to clean up in the meantime.

review: Needs Fixing (db)
Reply
Revision history for this message
Andrey Fedoseev (andrey-fedoseev) wrote :

Colin,

I've made the requested changes here and updated the code branch accordingly https://code.launchpad.net/~andrey-fedoseev/launchpad/+git/launchpad/+merge/431710:

 - git commit fields are set to be exactly 40 chars long
 - remove ON DELETE CASCADE from the bug presence table; when a git repository is removed the related bug presence instances are removed via `destroySelf`
 - distribution is no longer a valid target for bug presence; a target can be either: git repo, project, source package name or distribution package; I'm not sure about "source package name" being a target, let me know it this needs to be changed.

Reply
Revision history for this message
Colin Watson (cjwatson) wrote :

I think I'm mostly OK with this now, though it still needs a few tweaks. Adding William for a second DB review.

review: Approve (db)
Reply
Revision history for this message
Andrey Fedoseev (andrey-fedoseev) :
Reply
Revision history for this message
Andrey Fedoseev (andrey-fedoseev) wrote :

I updated the constraints, this is ready for another review.

Reply

Unmerged commits

9a3276a... by Andrey Fedoseev

Add `BugPresence` table

It represents a range of versions or git commits in which the bug was present.

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-12-0.sql b/database/schema/patch-2211-12-0.sql
0new file mode 1006440new file mode 100644
index 0000000..cce46ff
--- /dev/null
+++ b/database/schema/patch-2211-12-0.sql
@@ -0,0 +1,70 @@
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
6CREATE TABLE BugPresence
7(
8 id serial PRIMARY KEY,
9 bug integer NOT NULL REFERENCES Bug,
10 project integer REFERENCES Product,
11 distribution integer REFERENCES Distribution,
12 source_package_name integer REFERENCES SourcePackageName,
13 git_repository integer REFERENCES GitRepository,
14 broken_version text,
15 fixed_version text,
16 broken_git_commit_sha1 character(40),
17 fixed_git_commit_sha1 character(40),
18 CONSTRAINT version_xor_git_range CHECK (
19 (
20 broken_version is NULL
21 and fixed_version is NULL
22 ) != (
23 broken_git_commit_sha1 is NULL
24 and fixed_git_commit_sha1 is NULL
25 )
26 ),
27 CONSTRAINT version_range_target CHECK (
28 (
29 broken_version is not NULL
30 or fixed_version is not NULL
31 ) = (
32 git_repository is NULL
33 and (
34 project is not NULL
35 or source_package_name is not NULL
36 )
37 )
38 ),
39 CONSTRAINT git_range_target CHECK (
40 (
41 broken_git_commit_sha1 is not NULL
42 or fixed_git_commit_sha1 is not NULL
43 ) = (
44 git_repository is not NULL
45 and project is NULL
46 and source_package_name is NULL
47 )
48 ),
49 CONSTRAINT project_xor_package CHECK (
50 not (project is not NULL and source_package_name is not NULL)
51 ),
52 CONSTRAINT package_has_distribution CHECK (
53 (source_package_name is NULL) = (distribution is NULL)
54 )
55);
56
57CREATE INDEX BugPresence__bug__idx ON BugPresence(bug);
58
59COMMENT ON TABLE BugPresence IS 'Range of versions or git commits in which the bug is present.';
60COMMENT ON COLUMN BugPresence.bug is 'The Bug this BugPresence related to.';
61COMMENT ON COLUMN BugPresence.project is 'The project in which the bug is present.';
62COMMENT ON COLUMN BugPresence.distribution is 'The distribution in which the bug is present.';
63COMMENT ON COLUMN BugPresence.source_package_name is 'The package in which the bug is present.';
64COMMENT ON COLUMN BugPresence.git_repository is 'The git repository in which the bug is present.';
65COMMENT ON COLUMN BugPresence.broken_version is 'The version in which the bug was introduced.';
66COMMENT ON COLUMN BugPresence.fixed_version is 'The version in which the bug was fixed.';
67COMMENT ON COLUMN BugPresence.broken_git_commit_sha1 is 'The git commit in which the bug was introduced.';
68COMMENT ON COLUMN BugPresence.fixed_git_commit_sha1 is 'The git commit in which the bug was fixed.';
69
70INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 12, 0);
diff --git a/database/schema/security.cfg b/database/schema/security.cfg
index d5578d7..5bc861e 100644
--- a/database/schema/security.cfg
+++ b/database/schema/security.cfg
@@ -149,6 +149,7 @@ public.bugnotification = SELECT, INSERT, UPDATE, DELETE
149public.bugnotificationattachment = SELECT, INSERT149public.bugnotificationattachment = SELECT, INSERT
150public.bugnotificationfilter = SELECT, INSERT, UPDATE, DELETE150public.bugnotificationfilter = SELECT, INSERT, UPDATE, DELETE
151public.bugnotificationrecipient = SELECT, INSERT, UPDATE, DELETE151public.bugnotificationrecipient = SELECT, INSERT, UPDATE, DELETE
152public.bugpresence = SELECT, INSERT, UPDATE, DELETE
152public.bugsummary = SELECT153public.bugsummary = SELECT
153public.bugsummaryjournal = SELECT154public.bugsummaryjournal = SELECT
154public.bugsummary_rollup_journal(integer) = EXECUTE155public.bugsummary_rollup_journal(integer) = EXECUTE

Subscribers

People subscribed via source and target branches

to status/vote changes: