Rob’s Tech Blog

Welcome to part one of a four part series on getting some sweet visualisations of workflows, using the open source digraph tool “Dot”. This first post will be about dot’s DSL (which rocks), and how to generate it. Part two will look at some advanced styling and layout. Parts 3 and 4 will be about ways to publish this data over the Internet (Dot is a command line tool).

Dot’s input language is very simple. Here’s an example of a digraph generated by dot:

 

And the code to create this graph was:

digraph G{ //declare a digraph, and give it a name of “G”
    node[fontname=arial]; //sets the default font for all nodes
    bismuth212->thallium208[label="36%"]; //create a labelled edge
    bismuth212->polonium212[label="64%"];
    thallium208->lead208; //create an unlabelled edge
    polonium212->lead208;
    lead208[color=gray] //set the colour for the (already declared) lead 208 node
}

You can also give labels to nodes – the following code creates exactly the same graph:

digraph G{
    node[fontname=arial];

    1[label=bismuth212]
    2[label=thallium208]
    3[label=polonium212]
    4[label=lead208,color=gray]

    1->2[label="36%"];
    1->3[label="64%"];
    2->4;
    3->4;
}

You can get more information about the Dot language at the graphviz site.

I have recently started working on a project that involves a simple workflow. This workflow is modelled in my database. I’ve simplified it a bit, but here’s my schema:

 

And here’s my data:

State	Transition
ID    Name 1    Start 2    Write test 3    Run test (red stage) 4    Write function 5    Run test (green stage) 6    Refactor 7    Run test (refactor stage) 8    Finish	ID    FromStateID    ToStateID    Description 2    1    2    3    2    3    4    3    2    Passed 5    3    4    Failed 6    4    5    7    5    6    Passed 8    5    4    Failed 9    6    7    10    7    8    Passed 11    7    4    Failed

 

Not that easy to follow, huh? And this is a very simple example.

However, if we run the following (simple) SQL:

 

DECLARE @crlf varchar(2)
SET @crlf = char(13)+char(10)

DECLARE @s varchar(max)
SET @s = '  node[fontname=arial];'+@crlf

SELECT @s=@s+'  '
    +convert(varchar,ID)
    +'[label="'+Name+'"];'
    +@crlf
FROM State

SELECT @s=@s+'  '
    +convert(varchar,FromStateID)
    +'->'+convert(varchar,ToStateID)
    +'[label="'+Description+'"];'
    +@crlf
FROM Transition

SELECT 'digraph G{'+@crlf+@s+'}'

we’ll get the following output:

digraph G{

  node[fontname=arial];

  1[label="Start"];

  2[label="Write test"];

  3[label="Run test (red stage)"];

  4[label="Write function"];

  5[label="Run test (green stage)"];

  6[label="Refactor"];

  7[label="Run test (refactor stage)"];

  8[label="Finish"];

  1->2[label=""];

  2->3[label=""];

  3->2[label="Passed"];

  3->4[label="Failed"];

  4->5[label=""];

  5->6[label="Passed"];

  5->4[label="Failed"];

  6->7[label=""];

  7->8[label="Passed"];

  7->4[label="Failed"];

}

Which we can run through the dot tool:

C:\Program Files\Graphviz 2.21\bin>dot -Tgif -ooutput.gif

It’s not pretty (yet), but it’s so much easier to follow than looking at numbers. Hopefully I’ve got you thinking about how you could rewrite that SQL statement to work against your own database tables.

I’ll be posting a guide to styling and laying out this Dot diagram soon – stay tuned!