launchpad-reviewers team mailing list archive

[Mattias Backman <mattias.backman@xxxxxxxxxx>]

Mattias Backman has proposed merging lp:~linaro-infrastructure/launchpad/workitems-schema-changes into lp:launchpad/db-devel.

Requested reviews:
  Stuart Bishop (stub): db
  Robert Collins (lifeless): db
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~linaro-infrastructure/launchpad/workitems-schema-changes/+merge/91295

Hi,

This branch adds the schema required for making Work Items first class database objects.

The topic has been discussed here https://lists.launchpad.net/launchpad-dev/msg08782.html . The gist of it is that we would like Work Items to be proper Launchpad objects to make status.ubuntu.org and status.linaro.org development easier and enable new status pages for teams and individuals.

Thanks,

Mattias
-- 
https://code.launchpad.net/~linaro-infrastructure/launchpad/workitems-schema-changes/+merge/91295
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~linaro-infrastructure/launchpad/workitems-schema-changes into lp:launchpad/db-devel.

=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2012-01-25 06:02:40 +0000
+++ database/schema/comments.sql	2012-02-02 15:46:10 +0000
@@ -899,6 +899,35 @@
 COMMENT ON COLUMN RevisionCache.sourcepackagename IS 'The sourcepackagename for which a source package branch contains the revision.';
 COMMENT ON COLUMN RevisionCache.private IS 'True if the revision is only found in private branches, False if it can be found in a non-private branch.';
 
+-- specificationworkitem
+COMMENT ON TABLE specificationworkitem IS 'A work item which is a piece of work relating to a blueprint.';
+COMMENT ON COLUMN specificationworkitem.id IS 'The id of the work item.';
+COMMENT ON COLUMN specificationworkitem.title IS 'The title of the work item.';
+COMMENT ON COLUMN specificationworkitem.specification IS 'The blueprint that this work item is a part of.';
+COMMENT ON COLUMN specificationworkitem.assignee IS 'The person who is assigned to complete the work item.';
+COMMENT ON COLUMN specificationworkitem.milestone IS 'The milestone this work item is targetted to.';
+COMMENT ON COLUMN specificationworkitem.date_created IS 'The date on which the work item was created.';
+COMMENT ON COLUMN specificationworkitem.deleted IS 'Marks if the work item has been deleted. To be able to keep history we do not want to actually delete them from the database.';
+
+-- specificationworkitemchange
+COMMENT ON TABLE specificationworkitemchange IS 'A property change on a work item.';
+COMMENT ON COLUMN specificationworkitemchange.id IS 'Id of the change.';
+COMMENT ON COLUMN specificationworkitemchange.work_item IS 'The work item for which a propery has changed.';
+COMMENT ON COLUMN specificationworkitemchange.new_status IS 'The new status for the work item.';
+COMMENT ON COLUMN specificationworkitemchange.new_milestone IS 'The new milestone the work item has been targetted to.';
+COMMENT ON COLUMN specificationworkitemchange.new_assignee IS 'The person which the work item has be assigned to.';
+COMMENT ON COLUMN specificationworkitemchange.date IS 'The time of the change.';
+
+-- specificationworkitemstats
+COMMENT ON TABLE specificationworkitemstats IS 'Stats for work items that are collected by a scheduled script.';
+COMMENT ON COLUMN specificationworkitemstats.id IS 'The id for this stats collection.';
+COMMENT ON COLUMN specificationworkitemstats.specification IS 'The related blueprint.';
+COMMENT ON COLUMN specificationworkitemstats.day IS 'Day when the stats where collected.';
+COMMENT ON COLUMN specificationworkitemstats.status IS 'The work item status that work items are counted for.';
+COMMENT ON COLUMN specificationworkitemstats.assignee IS 'The assignee that work items are counted for.';
+COMMENT ON COLUMN specificationworkitemstats.milestone IS 'The milestone that work items are counted for.';
+COMMENT ON COLUMN specificationworkitemstats.count IS 'The number of work items for the blueprint with the particular status, assignee and milestone.';
+
 -- Sprint
 COMMENT ON TABLE Sprint IS 'A meeting, sprint or conference. This is a convenient way to keep track of a collection of specs that will be discussed, and the people that will be attending.';
 COMMENT ON COLUMN Sprint.driver IS 'The driver (together with the registrant or owner) is responsible for deciding which topics will be accepted onto the agenda of the sprint.';

=== added file 'database/schema/patch-2209-06-1.sql'
--- database/schema/patch-2209-06-1.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-06-1.sql	2012-02-02 15:46:10 +0000
@@ -0,0 +1,35 @@
+-- Copyright 2012 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 specificationworkitem (
+    id SERIAL PRIMARY KEY,
+    title text NOT NULL,
+    specification integer NOT NULL REFERENCES specification,
+    assignee integer REFERENCES person,
+    milestone integer REFERENCES milestone,
+    date_created timestamp without time zone DEFAULT 
+        timezone('UTC'::text, now()) NOT NULL,
+    status integer NOT NULL,
+    deleted boolean NOT NULL DEFAULT FALSE);
+
+CREATE TABLE specificationworkitemchange (
+    id SERIAL PRIMARY KEY,
+    work_item integer NOT NULL REFERENCES specificationworkitem,
+    new_status integer NOT NULL,
+    new_milestone integer REFERENCES milestone,
+    new_assignee integer REFERENCES person,
+    date timestamp without time zone DEFAULT 
+        timezone('UTC'::text, now()) NOT NULL);
+
+CREATE TABLE specificationworkitemstats (
+    id SERIAL PRIMARY KEY,
+    specification integer REFERENCES specification,
+    day date NOT NULL,
+    status integer NOT NULL,
+    assignee integer REFERENCES person,
+    milestone integer REFERENCES milestone,
+    count integer NOT NULL);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 06, 1);

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2012-02-01 14:30:51 +0000
+++ database/schema/security.cfg	2012-02-02 15:46:10 +0000
@@ -288,6 +288,9 @@
 public.specificationfeedback            = SELECT, INSERT, UPDATE, DELETE
 public.specificationmessage             = SELECT, INSERT
 public.specificationsubscription        = SELECT, INSERT, UPDATE, DELETE
+public.specificationworkitem            = SELECT, INSERT, UPDATE
+public.specificationworkitemchange      = SELECT, INSERT, UPDATE
+public.specificationworkitemstats       = SELECT
 public.spokenin                         = SELECT, INSERT, DELETE
 public.sprint                           = SELECT, INSERT, UPDATE
 public.sprintattendance                 = SELECT, INSERT, UPDATE, DELETE
@@ -2298,3 +2301,10 @@
 groups=read
 public.oauthnonce = SELECT
 public.openidconsumernonce = SELECT
+
+[work_items_stats]
+type=user
+groups=script
+public.specificationworkitemstats       = SELECT, INSERT
+public.specificationworkitem            = SELECT
+public.specificationworkitemchange      = SELECT