Build Workboard Metrics with

Merged 20after4 requested to merge workboard-metrics into main

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/ --project=PHID-PROJ-fmcvjrkfvvzz3gxavs3a --mock=test/train.transactions.json --dump=json > metrics.json

The schema for the sqlite database is as follows:

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)

Merge request reports