Using Graphviz to Build Flowcharts

We create software for developing and supporting Oracle databases, and the static PL / SQL analyzer is one of the main features of our applications. Anyone familiar with Oracle knows well what PL / SQL is.

A well-known saying goes: "It is better to see once than hear a hundred times." Therefore, we decided to implant the static analyzer in such a way that it visualized the code in the form of flowcharts and call diagrams (Call Trees). Although it is not difficult to draw blocks and their connections, to optimize their location on the "sheet" seemed to be a task requiring considerable effort. In order for the arrows to intersect and flow around the blocks minimally, the blocks were combined into groups, and the diagram did not turn into a mess, it took a lot of effort and time.

And then we decided to look for a ready-made solution, so as not to reinvent the wheel. Graphviz, an open source chart visualization solution, immediately caught our attention. Its first versions were developed by AT&T, and now it is available as a set of utilities and libraries, as well as in source codes under the Eclipse Public License (EPL).

Its chart engine uses the DOT graph description language, which is a textual description of the graph structure: vertices, their relationships, groups and attributes for their visual design.

Description of the simplest graph:

digraph graphname {
  a -> b;
}

If you need to add attributes to nodes, for example, signatures, you must separately describe the nodes:

digraph graphname {
  a [label="source"]
  b [label="destination"]
  a -> b;
}

Next, I will show you an example of a simple PL / SQL procedure:

CREATE PROCEDURE WRITE_STRING(
    P_MESSAGE_TYPE IN NUMBER
  , P_MESSAGE IN VARCHAR2
)
IS
BEGIN
  IF P_MESSAGE_TYPE >= LOG_LEVEL THEN
    ADD_LOG_RECORD(P_MESSAGE_TYPE, P_MESSAGE);
  END IF;
END WRITE_STRING;

The code is understandable even if you are not familiar with the PL / SQL syntax.

Now we will describe this code in the DOT language. Explanations again redundant:

digraph G {
  N0 [label="”]
  N1_COND [label="P_MESSAGE_TYPE \>= LOG_LEVEL ?"];
  N2 [label="ADD_LOG_RECORD(P_MESSAGE_TYPE, P_MESSAGE);"];
  EXIT_LABEL [label="END"];
  N0 -> N1_COND
  N1_COND -> N2
  N1_COND -> EXIT_LABEL
  N2 -> EXIT_LABEL 
}

Now you can “feed” this Graphviz file by substituting the names of the output (png) and input (dot) files instead of% PNGFILE% and% DOTFILE%:

dot -v -Tpng –o%PNGFILE% %DOTFILE%

Using Graphviz from the description above, we get the following picture:



Visually, but very ascetic.

It is possible to make the chart attractive by adding attributes to define the shape of blocks (shape=diamond), signatures arrows (label="Yes")and colors color, fontcolor.

We can also combine into a subgraph blocks that are executable. This is implemented by the construction subgraph {}, inside which we can list the names of the blocks included in the subgraph and specify the attributes for visual design (frame color).

digraph G {
  comment="PL/SQL flowchart generated by ClearSQL";
  node [fontname="Arial", fontsize=8, height=.2, width=.25, color="#000000", fontcolor="#000000"];
  edge [fontname="Arial", fontsize=8, arrowsize=.8, color="#000000", fontcolor="#000000"];
  N0 [shape=plaintext, label="", style=invis];
  N1_COND [shape=diamond, conditional=true, label="P_MESSAGE_TYPE \>= LOG_LEVEL ?"];
  N2 [shape=box, label="ADD_LOG_RECORD(P_MESSAGE_TYPE, P_MESSAGE);"];
  EXIT_LABEL [shape=Msquare, label="END"];
  N0 -> N1_COND [color=darkgoldenrod];
  N1_COND -> N2 [label="  Yes " color="#228b22" fontcolor="#228b22"];
  N1_COND -> EXIT_LABEL [label="  No " color="#ff7e40" fontcolor="#ff7e40"];
  N2 -> EXIT_LABEL  
  subgraph "cluster_#1" {
    color="#0000ff"; 
    N1_COND; N2
  }  
}

It turns out quite a nice block diagram, which people are not ashamed to show.



So that not only programmers, but also “mere mortals” could read the code, we came up with this trick: we added recognition of special tags in comments that the developer would write as pseudo-code - a language for describing algorithms. This has helped to add value to the flowchart, which can now display business logic, and not just visualize the listing.

CREATE PROCEDURE WRITE_STRING(
      P_MESSAGE_TYPE IN NUMBER
    , P_MESSAGE IN VARCHAR2
)
IS
BEGIN
  --## Уровень данного сообщения по отношению к порогу журналирования
  --##@true Выше
  --##@false Ниже
  IF P_MESSAGE_TYPE >= LOG_LEVEL THEN
    --## Добавляем запись в журнал
    ADD_LOG_RECORD(P_MESSAGE_TYPE, P_MESSAGE);
  END IF;
END WRITE_STRING;

Voila! Graphviz gives you the ability to make stunning visualizations at minimal cost.