Page MenuHomePhabricator
Paste P17189

jsonnet
ActivePublic

Authored by mmodell on Sep 2 2021, 8:08 PM.
{
databases:
{
train:
{
queries:
{
train_overview:
|||
SELECT
version,
rollbacks,
rollbacks_time,
group2_delay_days,
(group0_delay_days +
group1_delay_days +
group2_delay_days) AS total_delay,
total_time AS train_total_time,
(select count(*) from blocker b where b.train_id = t.id) AS blockers,
(select count(*) from blocker b where b.train_id = t.id and resolved = 1) AS resolved_blockers,
patches,
(select max(time_in_review) from patch p where p.train_id = t.id)/(60*60) AS max_time_in_review,
(select max(comments) from patch where patch.train_id = t.id) AS max_comments_per_patch,
(select max(start_time - created) from patch p where p.train_id = t.id)/(60*60) AS max_cycle_time
FROM train t
ORDER BY version DESC limit 10
|||,
},
},
},
plugins: {
'datasette-dashboards': {
train: {
title: 'Train Dashboard',
description: 'Train metrics demo',
layout: [
[ 'train-conductors', 'train-blockers-unblockers'],
['train-timing', 'train-rollbacks'],
[ 'train-blockers-time-to-unblock', 'train-blockers-time-to-unblock'],
['train-patches-line', 'train-blockers-trend' ],
],
filters: {
date_start: {
name: 'Date Start',
type: 'date',
default: '2021-01-01',
},
date_end: {
name: 'Date End',
type: 'date',
},
},
charts: {
local parseIntTransform = {"calculate": "parseInt(datum.count)", "as": "x"},
'train-rollbacks': {
title: 'Patches and rollbacks',
db: 'train',
query: "select version, patches, rollbacks, rollbacks_time / 3600 as rollbacks_hours, total_time / 3600 as total_hours, count(b.id) as blockers, datetime(start_time+total_time, 'unixepoch') as end_time, datetime(start_time, 'unixepoch', 'weekday 3') as mid_week, datetime(start_time, 'unixepoch') as start_time from train t, blocker b on t.id=b.train_id group by t.version order by start_time desc limit 3",
library: 'vega',
display: {
local chart = self,
local xf = { field: 'start_time', type: 'temporal' },
transform: [
],
layer: [
{
local layer = self,
mark: { type: 'rule', tooltip: true, color: { value: 'black' } },
encoding: {
y: { field: 'version', type: 'ordinal' },
x: xf,
x2: { field: 'end_time', type: 'temporal' },
tooltip: { field: 'total_hours', type: 'quantitative' },
},
},
{
mark: { type: 'rule' },
encoding: {
x: xf,
y: { field: 'version', type: 'nominal', bandPosition: '0.1' },
y2: { field: 'version', type: 'nominal', bandPosition: '0.9' },
},
},
{
mark: { type: 'rule' },
encoding: {
x: { field: 'end_time', type: 'temporal' },
y: { field: 'version', type: 'nominal', bandPosition: '0.1' },
y2: { field: 'version', type: 'nominal', bandPosition: '0.9' },
},
},
{
mark: { type: 'point', tooltip: true, filled: true },
encoding: {
x: { field: 'mid_week', type: 'temporal', title: 'train time' },
y: { field: 'version', type: 'ordinal', title: 'MediaWiki Version' },
color: { field: 'version', type: 'nominal', title: 'MediaWiki Version' },
size: { field: 'patches', type: 'quantitative', legend: { orient: 'bottom' } },
tooltip: [
{ field: 'version', title: 'MediaWiki Version' },
{ field: 'patches', title: 'Patches deployed' },
{ field: 'blockers', title: 'Train-blocking bugs' },
{ field: 'total_hours', title: 'Total time (hours)' },
{ field: 'rollbacks_hours', title: 'Rolled-back (hours)' },
],
},
},
],
},
},
'train-conductors': {
title: 'Number of trains by conductor',
db: 'train',
query: 'SELECT conductor, count(version) AS count, sum(patches) AS patches, sum(rollbacks) AS rollbacks FROM train WHERE TRUE GROUP BY conductor',
library: 'vega',
display: {
transform: [ parseIntTransform ],
mark: { type: 'bar', tooltip: true },
encoding: {
y: { field: 'conductor', type: 'nominal', sort: '-x' },
color: { field: 'count', type: 'quantitative' },
x: { field: 'x', type: 'quantitative' },
},
},
},
'train-patches-line': {
title: 'Number of patches trend',
db: 'train',
query: "select id, version, patches, total_time, datetime(start_time, 'unixepoch') as start_time, datetime(start_time+total_time, 'unixepoch') as end_time from train order by start_time asc",
library: 'vega',
display: {
local encoding = {
y: { field: 'patches', type: 'quantitative', title: 'Patches merged.' },
x: { field: 'start_time', type: 'temporal', title:'train time' },
//x2: { field: 'end_time', type: 'temporal', title:'' },
},
layer: [
{
mark: { type: 'bar', tooltip: true },
encoding: encoding,
},
{
"transform": [
{
"regression": "patches",
"on": "start_time"
}
],
mark: { type: 'line', color: 'firebrick', tooltip: true },
encoding: {
x: encoding.x,
y: encoding.y
}
},
]
},
},
'train-blockers-trend': {
title: 'Number of Blockers trend',
db: 'train',
query: "select id, train_id, datetime(blocked, 'unixepoch') as start_time, datetime(unblocked, 'unixepoch') as end_time, blocker, unblocker, removed, resolved, task, url, status, group_blocked, group_unblocked, count(*) as count from blocker group by train_id order by blocked, unblocked asc",
library: 'vega',
display: {
local encoding = {
y: { field: 'count', type: 'quantitative', title: 'Blockers reported.' },
x: { field: 'start_time', type: 'temporal', title:'Train time' },
//x2: { field: 'end_time', type: 'temporal', title:'' },
},
layer: [
{
mark: { type: 'area', tooltip: true },
encoding: encoding,
},
{
"transform": [
{
"regression": "count",
"on": "start_time"
}
],
mark: { type: 'line', color: 'firebrick', tooltip: true },
encoding: {
x: encoding.x,
y: encoding.y
}
},
]
},
},
'train-blockers-unblockers': {
title: 'Train blockers reported',
db: 'train',
query: "with b2 as ( select unblocker, count(*) as unblock_count from blocker group by unblocker having unblock_count > 5), b1 as ( select blocker, count(*) as block_count from blocker group by blocker having block_count > 5 ) select blocker as who, block_count, 0-unblock_count as unblock_count from b1 join b2 on b1.blocker=b2.unblocker ",
library: 'vega',
display:
{
"transform": [
{
fold: ['block_count', 'unblock_count']
},
],
mark: { type: 'bar', tooltip: true },
encoding: {
y: { field: 'who', type: 'nominal', title: 'Who', "sort": "x" },
x: { field: 'value', type: 'quantitative', title:'number resolved | number reported', scale: { domain: [-200, 200], stack: "center" },},
//x2: { field: 'v2', type: 'quantitative', title:'number resolved', scale: { domain: [0, 100]},"stack": "center"},
color: { field: 'key', type: "ordinal", "scale": {"range": ["#675193", "#ca8861"]} },
},
}
},
'train-blockers-time-to-unblock': {
title: 'Time to unblock',
db: 'train',
query: "select blocker, unblocker,datetime(min(blocked), 'unixepoch') as start_time, datetime(max(unblocked), 'unixepoch') as end_time, avg(unblocked-blocked)/3600 as blocked_time, count(*) as count from blocker group by unblocker having block_count > 4 ",
library: 'vega',
display:
{
"transform": [parseIntTransform],
mark: { type: 'bar', tooltip: true },
encoding: {
x: {
field: 'start_time',
type: 'temporal',
title: 'Hours blocked (mean)',
axis: {"grid": true, "tickBand": "extent"}
},
x2: { field: "end_time", type: "temporal" },
y: { field: 'unblocker', type: 'nominal', title:'Who', sort: 'x' },
color: { field: 'unblocker', type: "nominal" },
size: { field: 'blocked_time', type: 'quantitative', title: 'Blockers resolved.' }
//color: { field: '', type: 'quantitative', title:'', sort: "x" },
},
}
},
'train-timing': {
title: 'Cycle time and Lead time',
db: 'train',
query: "SELECT t.id, t.version, datetime(start_time, 'unixepoch') AS start_time, (start_time - created)/(60*60) AS cycle_time, (start_time - submitted)/(60*60) AS lead_time, datetime(created, 'unixepoch') AS created, datetime(submitted, 'unixepoch') submitted FROM patch p JOIN train t ON t.id = p.train_id WHERE ( lead_time > 0 AND cycle_time > 0 ) ORDER BY start_time DESC",
library: 'vega',
display: {
transform: [
{ fold: ['lead_time', 'cycle_time'] },
],
layer: [
{
mark: { type: 'bar' },
},
{
mark: { type: 'text', dx: 10 },
encoding: {
text: {
field: 'value',
aggregate: 'mean',
format: 'd',
fontSize: 18,
},
color: {
value: 'white',
},
},
},
],
encoding: {
x: {
aggregate: 'mean',
field: 'value',
axis: {
title: 'Lead, Cycle time (minutes)',
labelFontSize: 12,
},
type: 'quantitative',
},
y: {
field: 'version',
type: 'ordinal',
axis: {
labelAngle: 0,
labelFontSize: 17,
},
},
color: {
field: 'key',
type: 'nominal',
},
},
},
},
},
},
},
},
}