Build Workboard Metrics with boardmetrics.py
The output is suitable for generating a timeline with data points for each time a task enters or leaves a column.
If you run the following command, a metrics.db will be created
./ddd/boardmetrics.py --project=PHID-PROJ-fmcvjrkfvvzz3gxavs3a --mock=test/train.transactions.json --dump=json > metrics.json
The schema for the sqlite database is as follows:
schema |
---|
events - individual datapoint for each task event (e.g. workboard move, status change |
CREATE TABLE events(ts, task, project phid, user phid, event, old, new) |
CREATE UNIQUE INDEX events_pk on events(ts, task, event) |
column_metrics - metrics summarized by workboard columns |
CREATE TABLE column_metrics (trnsid, ts, project phid, column phid, task, type, value) |
CREATE UNIQUE INDEX trnsid on column_metrics(ts, column, task, value) |
CREATE INDEX ts_column_value on column_metrics(column, task, ts, value) |
task_metrics - metrics summarized by task |
CREATE TABLE task_metrics(task, metric phid, next_metric phid, ts, ts2, duration) |
CREATE UNIQUE INDEX task_metric ON task_metrics(task, metric) |
Columns - duplicates information in phobjects table, will probably remove this in the future |
CREATE TABLE columns(project,phid,name,status,proxyPHID,dateCreated,dateModified) |
phobjects - cache of every phid to resolve object names and status of each referenced phid |
CREATE TABLE phobjects (phid PRIMARY KEY,name TEXT,dateCreated timestamp,dateModified timestamp,status TEXT,type TEXT,data TEXT) |
CREATE INDEX phid_name ON phobjects (phid, name) |
CREATE UNIQUE INDEX pk_phobjects ON phobjects (phid) |
CREATE INDEX type_status ON phobjects (type, status) |