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

Subscribers

People subscribed via source and target branches

to status/vote changes: