On 23/01/12 09:46, Stuart Bishop wrote: > On Sat, Jan 21, 2012 at 3:18 AM, Guilherme Salgado > <guilherme.salg...@linaro.org> 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> _______________________________________________ Mailing list: https://launchpad.net/~launchpad-dev Post to : launchpad-dev@lists.launchpad.net Unsubscribe : https://launchpad.net/~launchpad-dev More help : https://help.launchpad.net/ListHelp
