Merge ~ilasc/launchpad:add-vulnerability-model into launchpad:master

[Ioana Lasc]

Add vulnerability, vulnerabilityactivity and bugvulnerability

[Ioana Lasc (ilasc)]

Diagram: https://dbdiagram.io/d/612378e36dc2bb6073b6ecce

The next MP (ORM) will contain the proposed the enum for "what_changed" on vulnerabilityactivity, but pasting it here for detail:

class VulnerabilityChanges(DBEnumeratedType):
    """Type of change in vulnerability

    We use this enum to track changes occuring in
    data stored in the vulnerability table.
    """

    STATUS = DBItem(0, """
        Status

        The status of the vulnerability changed.
        """)

    DESCRIPTION = DBItem(1, """
        Description

        The description of the vulnerability changed.
        """)

    NOTES = DBItem(2, """
        Notes

        The notes on the vulnerability changed.
        """)

    MITIGATION = DBItem(3, """
        Mitigation

        Mitigation for this vulnerability changed.
        """)

    IMPORTANCE = DBItem(4, """
        Importance

        The importance assigned for this vulnerability changed.
        """)

    IMPORTANCE_EXPLANATION = DBItem(5, """
        Importance explanation

        The importance explanation changed for this vulnerability.
        """)

    PRIVACY = DBItem(6, """
        Privacy

        The privacy for this vulnerability changed.
        """)

Unmerged commits

8b78acb... by Ioana Lasc on 2022-02-18

Add vulnerability, vulnerabilityactivity and bugvulnerability

d543821... by Guruprasad on 2022-02-16

Disallow NULL values for Bug.lock_status

Merged from https://code.launchpad.net/~lgp171188/launchpad/+git/launchpad/+merge/415259

b50f902... by Patch Queue Manager <email address hidden> on 2022-02-16

Automatic merge from stable up to 131c1c72b603

a8c7550... by Patch Queue Manager <email address hidden> on 2022-02-15

Automatic merge from stable up to a794baa81a31

bc051c7... by Patch Queue Manager <email address hidden> on 2022-02-11

Automatic merge from stable up to 88281672bd33

60cd026... by Patch Queue Manager <email address hidden> on 2022-02-10

Automatic merge from stable up to 3910f9b2ecef

0578e4e... by Patch Queue Manager <email address hidden> on 2022-02-09

Automatic merge from stable up to fb383037dc57

1e26cc5... by Patch Queue Manager <email address hidden> on 2022-02-09

Automatic merge from stable up to 27c3ab425c24

d3185b6... by Guruprasad on 2022-02-08

Disallow NULL values for Bug.lock_status

diff --git a/database/schema/patch-2210-38-1.sql b/database/schema/patch-2210-38-1.sql
new file mode 100644
index 0000000..6519b25
--- /dev/null
+++ b/database/schema/patch-2210-38-1.sql
@@ -0,0 +1,9 @@
+-- Copyright 2022 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+ALTER TABLE Bug
+    ALTER COLUMN lock_status SET NOT NULL;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 38, 1);
diff --git a/database/schema/patch-2210-42-0.sql b/database/schema/patch-2210-42-0.sql
new file mode 100644
index 0000000..2ad467e
--- /dev/null
+++ b/database/schema/patch-2210-42-0.sql
@@ -0,0 +1,64 @@
+-- Copyright 2022 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+CREATE TABLE vulnerability (
+	id serial PRIMARY KEY,
+	distribution integer REFERENCES Distribution NOT NULL,
+	cve integer REFERENCES CVE,
+	status integer,
+	description text,
+	notes text,
+	mitigation text,
+	importance integer NOT NULL,
+	importance_explanation text,
+	private boolean DEFAULT false NOT NULL
+);
+
+COMMENT ON TABLE vulnerability IS 'Expresses the notion of whether a CVE affects a distribution.';
+COMMENT ON COLUMN vulnerability.distribution IS 'Indicates control by the pillar''s owner.';
+COMMENT ON COLUMN vulnerability.cve IS 'Intentionally nullable, since we need to track vulnerabilities not associated with CVEs.';
+COMMENT ON COLUMN vulnerability.status IS 'Indicates current status of the vulnerability.';
+COMMENT ON COLUMN vulnerability.cve IS 'Overrides the cve description.';
+COMMENT ON COLUMN vulnerability.notes IS 'Free-form notes; may need some formatting machinery.';
+COMMENT ON COLUMN vulnerability.mitigation IS 'Explain why we''re ignoring something.';
+COMMENT ON COLUMN vulnerability.importance IS 'Indicates work priority, not severity.';
+COMMENT ON COLUMN vulnerability.importance_explanation IS 'Used to explain why our importance differs from somebody else''s CVSS score.';
+COMMENT ON COLUMN vulnerability.private IS 'Indicates privacy of the vulnerability.';
+
+CREATE INDEX vulnerability__distribution__cve__idx
+    ON vulnerability (distribution, cve);
+	
+CREATE TABLE vulnerabilityactivity (
+	id serial PRIMARY KEY,
+	vulnerability integer REFERENCES Vulnerability NOT NULL,
+	changer integer REFERENCES Person NOT NULL,
+	date_changed timestamp without time zone NOT NULL,
+	what_changed integer NOT NULL,
+	old_value text,
+	new_value text
+);
+
+COMMENT ON TABLE vulnerabilityactivity IS 'Tracks changes to vulnerability rows.';
+COMMENT ON COLUMN vulnerabilityactivity.vulnerability IS 'Indicates the vulnerability that the changes refer to.';
+COMMENT ON COLUMN vulnerabilityactivity.changer IS 'Indicates the person that made the changes.';
+COMMENT ON COLUMN vulnerabilityactivity.date_changed IS 'Indicates the date when the vulnerability details last changed.';
+COMMENT ON COLUMN vulnerabilityactivity.what_changed IS 'Indicates what field changed for the vulnerability by means of an enum.';
+COMMENT ON COLUMN vulnerabilityactivity.old_value IS 'Indicates the value prior to the change.';
+COMMENT ON COLUMN vulnerabilityactivity.new_value IS 'Indicates the current value.';
+
+CREATE INDEX vulnerabilityactivity__vulnerability__changer__idx
+    ON vulnerabilityactivity (vulnerability, changer);
+	
+CREATE TABLE bugvulnerability (
+	bug integer REFERENCES Bug NOT NULL,
+	vulnerability integer REFERENCES Vulnerability NOT NULL
+);
+
+COMMENT ON TABLE bugvulnerability IS 'Links a vulnerability to the bug.';
+
+CREATE INDEX bugvulnerability__bug__vulnerability__idx
+    ON bugvulnerability (bug, vulnerability);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 42, 0);