launchpad-dev team mailing list archive

[Guilherme Salgado <guilherme.salgado@xxxxxxxxxx>]

On 23/01/12 09:46, Stuart Bishop wrote:
> On Sat, Jan 21, 2012 at 3:18 AM, Guilherme Salgado
> <guilherme.salgado@xxxxxxxxxx> wrote:
> 
>> Oh, I'm also attaching the SQL for the new tables we think would be
>> necessary for this.
> 
> We can worry about the indexes and such later when this becomes more
> solid. I'm curious why you have chosen date instead of timestamp for
> some of the columns - I see no reason to throw away the extra
> accuracy, and a 'date' doesn't really make sense across timezones
> where your Monday is my Tuesday.

It's mostly because this is the granularity we have in status.l.o
(lp-work-items-tracker), and that's because for the reports we have
there we're only interested in knowing the status of work items on any
given day.

Although I don't think we'll ever need more accurate data for the
reports, it might be useful for other things (e.g. showing all status
changes of WIs on a given blueprint), and when generating the per-day
stats we can just take only the last entry of the day from
specificationworkitemchange, which should be easy.

> Our replication setup requires every table to have an explicitly
> declared primary key. I think you want specificationworkitemstats to
> have its 'specification' column declared as a primary key (in addition
> to referencing the specification table), which will also ensure the
> column is unique. If this column is not unique, we will need a SERIAL
> PRIMARY KEY added to keep replication happy (and ensure that *we* are
> able to uniquely address rows too :) )

Oh, right, that was an oversight on my part. We need a separate primary
key as the counts are per (spec,date,status,assignee,milestone).

> CREATE TABLE specificationworkitemstats (
>     specification integer PRIMARY KEY REFERENCES specification,
>     date date NOT NULL,
>     status integer NOT NULL,
>     assignee integer REFERENCES person,
>     milestone integer REFERENCES milestone,
>     count integer NOT NULL);

This table would be maintained by a script and its sole purpose is to
make it easy to generate the reports we need so here it'd be better to
use just the date.

-- 
Guilherme Salgado <https://launchpad.net/~salgado>