Merge lp:~linaro-infrastructure/launchpad/workitems-schema-changes into lp:launchpad/db-devel

[Mattias Backman]

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

[Guilherme Salgado (salgado)]

We've also implemented the code to migrate work items from the whiteboard to the new table (lp:~linaro-infrastructure/launchpad/workitems-schema-changes), to make sure this is all we need.

[Guilherme Salgado (salgado)]

I obviously meant lp:~linaro-infrastructure/launchpad/workitems-migration-script above

[Stuart Bishop (stub)]

Mostly fine.

Do we need a 'name' column on SpecificationWorkItem? If we need to address them individually in the URL space for the web UI or restful API, we might prefer a name rather than expose the internal id.

Please rename the 'date' column on SpecificationWorkItemChange to 'date_created'. This is consistent, and will avoid headaches from using a keyword as a column name.

I don't really like the 'day' column name on SpecificationWorkItemStats, but I can't think of a better alternative at the moment.

We need some indexes:

-- Foreign key, selecting by specification and sorting by date_created.
CREATE INDEX specificationworkitem__specification__date_created__idx
    ON SpecificationWorkItem(specification, date_created);

-- Foreign key.
CREATE INDEX specificationworkitem__milestone__idx
    ON SpecificationWorkItem(milestone);

-- Foreign key, required for person merge.
CREATE INDEX specificationworkitem__assignee__idx
    ON SpecificationWorkItem(assignee) WHERE assignee IS NOT NULL;

-- Foreign key, selecting by work_item and ordering by date_created
CREATE INDEX specificationworkitemchange__work_item__date_created__idx
    ON SpecificationWorkItemChange(work_item, date_created);

-- Foreign key.
CREATE INDEX specificationworkitemchange__new_milestone__idx
    ON SpecificationWorkItemChange(new_milestone)
        WHERE new_milestone IS NOT NULL;

-- Foreign key, required for person merge.
CREATE INDEX specificationworkitemchange__new_assignee__idx
    ON SpecificationWorkItemChange(new_assignee) WHERE new_assignee IS NOT NULL;

-- Foreign key, and selection by date.
CREATE INDEX specificationworkitemstats_specification__day__idx
    ON SpecificationWorkItemStats(specification, day);

-- Foreign key, required for person merge.
CREATE INDEX specificationworkitemstats__assignee__idx
    ON SpecificationWorkItemStats(assignee) WHERE assignee IS NOT NULL;

-- Foreign key.
CREATE INDEX specificationworkitemstats__milestone__idx
    ON SpecificationWorkItemStats(milestone) WHERE milestone IS NOT NULL;

review: Approve (db)

[Stuart Bishop (stub)]

wgrant mentioned that you might want a 'sequence' column to allow you to maintain display or implementation order of work items. If you do, please add it and resubmit the proposal.

[Mattias Backman (mabac)]

On Sun, Feb 5, 2012 at 9:50 PM, Stuart Bishop
<email address hidden> wrote:
> Review: Approve db
>
> Mostly fine.

Good to hear. Thanks.

>
> Do we need a 'name' column on SpecificationWorkItem? If we need to address them individually in the URL space for the web UI or restful API, we might prefer a name rather than expose the internal id.

I don't think we really need it, at least not for the user end of
things. We wouldn't pass around url:s to work items as we do with bugs
for instance, we'd link to the specification instead to reach the
complete set of work items for that specification.

We could add a name column, but it should be automatically generated
so the user would not have to enter a name for each work item. That
would just add overhead to something that's supposed to be really
light weight. Could we start out with just using the internal id for
API calls and maybe add a namn column later if we need it?

>
> Please rename the 'date' column on SpecificationWorkItemChange to 'date_created'. This is consistent, and will avoid headaches from using a keyword as a column name.

Absolutely, will do.

>
> I don't really like the 'day' column name on SpecificationWorkItemStats, but I can't think of a better alternative at the moment.

How about day_harvested or something like that?

>
> We need some indexes:
>
> -- Foreign key, selecting by specification and sorting by date_created.
> CREATE INDEX specificationworkitem__specification__date_created__idx
>    ON SpecificationWorkItem(specification, date_created);
>
> -- Foreign key.
> CREATE INDEX specificationworkitem__milestone__idx
>    ON SpecificationWorkItem(milestone);
>
> -- Foreign key, required for person merge.
> CREATE INDEX specificationworkitem__assignee__idx
>    ON SpecificationWorkItem(assignee) WHERE assignee IS NOT NULL;
>
>
> -- Foreign key, selecting by work_item and ordering by date_created
> CREATE INDEX specificationworkitemchange__work_item__date_created__idx
>    ON SpecificationWorkItemChange(work_item, date_created);
>
> -- Foreign key.
> CREATE INDEX specificationworkitemchange__new_milestone__idx
>    ON SpecificationWorkItemChange(new_milestone)
>        WHERE new_milestone IS NOT NULL;
>
> -- Foreign key, required for person merge.
> CREATE INDEX specificationworkitemchange__new_assignee__idx
>    ON SpecificationWorkItemChange(new_assignee) WHERE new_assignee IS NOT NULL;
>
> -- Foreign key, and selection by date.
> CREATE INDEX specificationworkitemstats_specification__day__idx
>    ON SpecificationWorkItemStats(specification, day);
>
> -- Foreign key, required for person merge.
> CREATE INDEX specificationworkitemstats__assignee__idx
>    ON SpecificationWorkItemStats(assignee) WHERE assignee IS NOT NULL;
>
> -- Foreign key.
> CREATE INDEX specificationworkitemstats__milestone__idx
>    ON SpecificationWorkItemStats(milestone) WHERE milestone IS NOT NULL;

So we'll add that to the patch then.

> --
> https://code.launchpad.net/~linaro-infrastructure/launchpad/workitems-schema-changes/+merge/91295
> Your team Linaro Infrastructure is subscribed to branch lp:~linaro-infrastructure/launchpad/workitems-schema-changes.

[Mattias Backman (mabac)]

On Sun, Feb 5, 2012 at 11:19 PM, Stuart Bishop
<email address hidden> wrote:
> wgrant mentioned that you might want a 'sequence' column to allow you to maintain display or implementation order of work items. If you do, please add it and resubmit the proposal.

I'm not sure I understand I understand the use for a sequence column.
If it's just for maintaining display order we can use the id column.

> --
> https://code.launchpad.net/~linaro-infrastructure/launchpad/workitems-schema-changes/+merge/91295
> Your team Linaro Infrastructure is subscribed to branch lp:~linaro-infrastructure/launchpad/workitems-schema-changes.

[Robert Collins (lifeless)]

On Tue, Feb 7, 2012 at 8:34 AM, Mattias Backman
<email address hidden> wrote:
> I'm not sure I understand I understand the use for a sequence column.
> If it's just for maintaining display order we can use the id column.

in the presence of deletes and adds the id column won't match your UI,
unless your UI only allows adds at the bottom of the list.

[Mattias Backman (mabac)]

On Mon, Feb 6, 2012 at 11:47 AM, Robert Collins
<email address hidden> wrote:
> On Tue, Feb 7, 2012 at 8:34 AM, Mattias Backman
> <email address hidden> wrote:
>> I'm not sure I understand I understand the use for a sequence column.
>> If it's just for maintaining display order we can use the id column.
>
> in the presence of deletes and adds the id column won't match your UI,
> unless your UI only allows adds at the bottom of the list.

Ah you're right, we will want to preserve the order of the work items.
So we need a sequence column.

>
> --
> https://code.launchpad.net/~linaro-infrastructure/launchpad/workitems-schema-changes/+merge/91295
> Your team Linaro Infrastructure is subscribed to branch lp:~linaro-infrastructure/launchpad/workitems-schema-changes.

=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2012-01-25 06:02:40 +0000
+++ database/schema/comments.sql	2012-02-06 23:47:23 +0000
@@ -899,6 +899,36 @@
 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.sequence IS 'The sequence number specifies the order of work items in the UI.';
+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_created 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-06 23:47:23 +0000
@@ -0,0 +1,73 @@
+-- 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,
+    sequence 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_created 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);
+
+-- Foreign key, selecting by specification and sorting by date_created.
+CREATE INDEX specificationworkitem__specification__date_created__idx
+    ON SpecificationWorkItem(specification, date_created);
+
+-- Foreign key.
+CREATE INDEX specificationworkitem__milestone__idx
+    ON SpecificationWorkItem(milestone);
+
+-- Foreign key, required for person merge.
+CREATE INDEX specificationworkitem__assignee__idx
+    ON SpecificationWorkItem(assignee) WHERE assignee IS NOT NULL;
+
+-- Foreign key, selecting by work_item and ordering by date_created
+CREATE INDEX specificationworkitemchange__work_item__date_created__idx
+    ON SpecificationWorkItemChange(work_item, date_created);
+
+-- Foreign key.
+CREATE INDEX specificationworkitemchange__new_milestone__idx
+    ON SpecificationWorkItemChange(new_milestone)
+        WHERE new_milestone IS NOT NULL;
+
+-- Foreign key, required for person merge.
+CREATE INDEX specificationworkitemchange__new_assignee__idx
+    ON SpecificationWorkItemChange(new_assignee) WHERE new_assignee IS NOT NULL;
+
+-- Foreign key, and selection by date.
+CREATE INDEX specificationworkitemstats_specification__day__idx
+    ON SpecificationWorkItemStats(specification, day);
+
+-- Foreign key, required for person merge.
+CREATE INDEX specificationworkitemstats__assignee__idx
+    ON SpecificationWorkItemStats(assignee) WHERE assignee IS NOT NULL;
+
+-- Foreign key.
+CREATE INDEX specificationworkitemstats__milestone__idx
+    ON SpecificationWorkItemStats(milestone) WHERE milestone IS 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-06 23:47:23 +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