Merge ~ilasc/launchpad:add-revision-status-report into launchpad:db-devel

Proposed by Ioana Lasc
Status: Merged
Approved by: Ioana Lasc
Approved revision: 88502dc0ba4a4e362c371e163489784a80379ea3
Merge reported by: Otto Co-Pilot
Merged at revision: not available
Proposed branch: ~ilasc/launchpad:add-revision-status-report
Merge into: launchpad:db-devel
Diff against target: 61 lines (+55/-0)
1 file modified
database/schema/patch-2210-37-0.sql (+55/-0)
Reviewer Review Type Date Requested Status
Colin Watson db Approve
William Grant db Approve
Review via email: mp+409900@code.launchpad.net

Commit message

Add RevisionStatusReport and RevisionStatusArtifact

To post a comment you must log in.
Revision history for this message
William Grant (wgrant) wrote :

Is there some way we can/should track who created the result?

Revision history for this message
Ioana Lasc (ilasc) :
Revision history for this message
Ioana Lasc (ilasc) wrote :

Requested changes made, MP ready for review.

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

Some of these comments are just about naming and documentation, but the "Needs Fixing" is for the missing `log` column, the properties of `date_started`, and maybe the `status`/`result` business.

I'd recommend getting a code branch ready and basically working before attempting to land this, since that will be a good demonstration that this DB structure essentially works; this is novel enough for Launchpad that I'd like to have something more than the thought experiment of the spec.

review: Needs Fixing
Revision history for this message
Colin Watson (cjwatson) :
Revision history for this message
Ioana Lasc (ilasc) wrote :

Thanks Colin, all comments addressed with the next commit, also noted that this is not to be landed until we have an approved corresponding code branch.

Revision history for this message
Colin Watson (cjwatson) :
Revision history for this message
Colin Watson (cjwatson) :
Revision history for this message
Colin Watson (cjwatson) :
Revision history for this message
Colin Watson (cjwatson) :
review: Approve (db)
Revision history for this message
William Grant (wgrant) wrote :

Thanks, just a few remaining tweaks.

review: Approve (db)
Revision history for this message
Colin Watson (cjwatson) :
Revision history for this message
Ioana Lasc (ilasc) wrote :

William & Colin thank you both for the helpful comments as usual!
Agreed on eliminating the bidirectional FK.
DB patch adjusted and the code branch for it is: https://code.launchpad.net/~ilasc/launchpad/+git/launchpad/+merge/410373

Revision history for this message
Colin Watson (cjwatson) :
review: Approve (db)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1diff --git a/database/schema/patch-2210-37-0.sql b/database/schema/patch-2210-37-0.sql
2new file mode 100644
3index 0000000..6a242be
4--- /dev/null
5+++ b/database/schema/patch-2210-37-0.sql
6@@ -0,0 +1,55 @@
7+-- Copyright 2021 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 RevisionStatusReport (
13+ id serial PRIMARY KEY,
14+ git_repository integer REFERENCES gitrepository NOT NULL,
15+ commit_sha1 character(40) NOT NULL,
16+ name text NOT NULL,
17+ url text,
18+ description text,
19+ result integer,
20+ date_created timestamp without time zone DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC') NOT NULL,
21+ creator integer REFERENCES Person NOT NULL,
22+ date_started timestamp without time zone,
23+ date_finished timestamp without time zone
24+);
25+
26+COMMENT ON TABLE RevisionStatusReport IS 'A status check for a code revision.';
27+COMMENT ON COLUMN RevisionStatusReport.git_repository IS 'The Git repository for this report..';
28+COMMENT ON COLUMN RevisionStatusReport.commit_sha1 IS 'The commit sha1 for the report.';
29+COMMENT ON COLUMN RevisionStatusReport.name IS 'Name of the report.';
30+COMMENT ON COLUMN RevisionStatusReport.url IS 'External URL to view result of report.';
31+COMMENT ON COLUMN RevisionStatusReport.description IS 'Text description of the result.';
32+COMMENT ON COLUMN RevisionStatusReport.result IS 'The result of the check job for this revision.';
33+COMMENT ON COLUMN RevisionStatusReport.date_created IS 'DateTime that report was created.';
34+COMMENT ON COLUMN RevisionStatusReport.creator IS 'The person that created the report.';
35+COMMENT ON COLUMN RevisionStatusReport.date_started IS 'DateTime that report was started.';
36+COMMENT ON COLUMN RevisionStatusReport.date_finished IS 'DateTime that report was completed.';
37+
38+CREATE INDEX revisionstatusreport__git_repository__commit_sha1__idx
39+ ON RevisionStatusReport (git_repository, commit_sha1);
40+
41+CREATE INDEX revisionstatusreport__creator__idx
42+ ON RevisionStatusReport (creator);
43+
44+CREATE TABLE RevisionStatusArtifact (
45+ id serial PRIMARY KEY,
46+ report integer REFERENCES RevisionStatusReport NOT NULL,
47+ type integer NOT NULL,
48+ library_file integer REFERENCES libraryfilealias NOT NULL
49+);
50+
51+COMMENT ON TABLE RevisionStatusArtifact IS 'An artifact produced by a status check for a code revision.';
52+COMMENT ON COLUMN RevisionStatusArtifact.report IS 'A link back to the report that the artifact was produced by.';
53+COMMENT ON COLUMN RevisionStatusArtifact.type IS 'The artifact type produced by the check job.';
54+COMMENT ON COLUMN RevisionStatusArtifact.library_file IS 'LibraryFileAlias storing the contents of the artifact.';
55+
56+CREATE INDEX revisionstatusartifact__library_file__idx
57+ ON RevisionStatusArtifact (library_file);
58+CREATE INDEX revisionstatusartifact__report__type__idx
59+ ON RevisionStatusArtifact (report, type);
60+
61+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 37, 0);

Subscribers

People subscribed via source and target branches

to status/vote changes: