Generating graphs as suggested by Emden R. Gansner:
python revj.py - | fdp | gvpr -fdir.g | dot -Grankdir=LR -Edir=none -T png -o x.png
The nodes have lines of text clipped, usually the top and bottom . Tried to manualy remove from the output of gvpr node attributes as rects="..", width=, height=, hoping that the directed graph will do the layout without clipping.
Layout is fine, but the clipping is same. Please advice
-Alex
Input:
Comments:
digraph G {
F [height="2.00",
label="F | (FACT_SALES) |<date_id> date_id|<product_id> product_id|<store_id> store_id|
<sum> SUM(units_sold)",
pos="248,140",
rects="192,188,305,212 192,164,305,188 192,140,305,164 192,116,305,140 192,92,305,116 1
92,68,305,92",
shape=record,
width="1.56"];
P [height="1.67",
label="P | (DIM_PRODUCT) |<brand_id> brand_id|<id> id|<product_category_id> product_cat
egory_id",
pos="266,281",
rects="199,317,333,341 199,293,333,317 199,269,333,293 199,245,333,269 199,221,333,245",
shape=record,
width="1.86"];
F:product_id:e -> P:id:w;
D [height="1.33",
label="D | (DIM_DATE) |<id> id|<where>year = 1997",
pos="363,85",
rects="315,109,411,133 315,85,411,109 315,61,411,85 315,37,411,61",
shape=record,
width="1.33"];
D:id:e -> F:date_id:w ;
S [height="1.33",
label="S | (DIM_STORE) |<geography_id> geography_id|<id> id",
pos="129,160",
rects="77,184,182,208 77,160,182,184 77,136,182,160 77,112,182,136",
shape=record,
width="1.44"];
S:id:e -> F:store_id:w;
G [height="1.33",
label="G | (DIM_GEOGRAPHY) |GROUP BY country|<id> id",
pos="74,49",
rects="1,73,147,97 1,49,147,73 1,25,147,49 1,1,147,25",
shape=record,
width="2.03"];
G:id:e -> S:geography_id:w;
C [height="1.33",
label="C | (DIM_PRODUCT_CATEGORY) |<id> id|product_category = 'tv'",
pos="478,328",
rects="373,352,583,376 373,328,583,352 373,304,583,328 373,280,583,304",
shape=record,
width="2.92"];
P:product_category_id:e -> C:id:w;
B [height="1.33",
label="<alias>B | <table> (DIM_BRAND) |<group_by>GROUP BY brand|<id> id",
pos="106,403",
rects="45,427,168,451 45,403,168,427 45,379,168,403 45,355,168,379",
shape=record,
width="1.69"];
P:brand_id:e -> B:id:w;
TABLE [label="This is the name of the table", style=filled, fillcolor=gray, width="2.0", shape=record];
ALIAS [label="This is the alias name", style=filled, fillcolor=gray, width="2.0", shape=record];
ALIAS -> B:alias [color=gray];
TABLE -> B:table [color=gray];
WHERE [label="This is a WHERE clause", style=filled, fillcolor=gray, width="2.0", shape=record];
WHERE -> D:where [color=gray];
GROUP_BY [label="This is one of the | GROUP BY columns", style=filled, fillcolor=gray, width="2.0", shape=record];
GROUP_BY -> B:group_by [color=gray];
SUM [label="And this is the SUM", style=filled, fillcolor=gray, width="2.0", shape=record];
F:sum -> SUM [color=gray];
}
[ellson] Could you run this command again, with -v added to the dot command, and send me the console messages, please?
I'm trying to understand the plugin combination that exhibits this problem.
[toth] ere is a step by step execution of the pipe (on Windows, dot 2.20)
Create a file sample.sql with contents:
SELECT
B.Brand,
G.Country,
SUM (F.Units_Sold)
FROM
Fact_Sales F
INNER JOIN Dim_Date D
ON F.Date_Id = D.Id
INNER JOIN Dim_Store S
ON F.Store_Id = S.Id
INNER JOIN Dim_Geography G
ON S.Geography_Id = G.Id
INNER JOIN Dim_Product P
ON F.Product_Id = P.Id
INNER JOIN Dim_Product_Category C
ON P.Product_Category_Id = C.Id
INNER JOIN Dim_Brand B
ON P.Brand_Id = B.Id
WHERE
D.Year = 1997
AND
C.Product_Category = 'tv'
GROUP BY
B.Brand,
G.Country
-------------------
python revj.py sample.sql
graph
{
node [shape=record];
graph [splines=true];
rankdir=LR;
F [label="F | (FACT_SALES) |<date_id> date_id|<product_id> product_id|<s
tore_id> store_id|SUM(units_sold)"];
S [label="S | (DIM_STORE) |<geography_id> geography_id|<id> id"];
G [label="G | (DIM_GEOGRAPHY) |GROUP BY country|<id> id"];
P [label="P | (DIM_PRODUCT) |<brand_id> brand_id|<id> id|<product_catego
ry_id> product_category_id"];
C [label="C | (DIM_PRODUCT_CATEGORY) |<id> id|product_category = 'tv'"];
B [label="B | (DIM_BRAND) |GROUP BY brand|<id> id"];
D [label="D | (DIM_DATE) |<id> id|year = 1997"];
B:id -- P:brand_id;
G:id -- S:geography_id;
F:store_id -- S:id;
D:id -- F:date_id;
C:id -- P:product_category_id;
F:product_id -- P:id;
}
----------------------------------
python revj.py sample.sql | fdp -v
Activated plugin library: gvplugin_pango.dll
Using textlayout: textlayout:cairo
Activated plugin library: gvplugin_neato_layout.dll
Using layout: fdp:neato_layout
Activated plugin library: gvplugin_core.dll
Using render: dot:core
Using device: dot:dot:core
The plugin configuration file:
C:Program FilesGraphviz2.20Binconfig
was successfully loaded.
render : cairo dot fig gd map ps svg tk vml vrml xdot
layout : circo dot fdp neato nop nop1 nop2 twopi
textlayout : textlayout
device : canon cmap cmapx cmapx_np dia dot eps fig gd gd2 gif hpgl ima
p imap_np ismap jpe jpeg jpg mif mp pcl pdf pic plain plain-ext png ps ps2 svg s
vgz tk vml vmlz vrml vtx wbmp xdot
loadimage : (lib) gd gd2 gif jpe jpeg jpg png ps svg
fontname: "Times-Roman" resolved to: (ps) "Times New Roman 13.9990234375"
layout _anonymous_0
xLayout tries = 9, mode = portho
Node separation: add=1 (4.000000,4.000000)
end _anonymous_0
Edge separation: add=1 (3.200000,3.200000)
Creating edges using splines
Using render: dot:core
Using device: dot:dot:core
graph {
graph [splines=true, rankdir=LR];
node [label="N", shape=record];
graph [bb="0,0,584,452"];
F [label="F | (FACT_SALES) |<date_id> date_id|<product_id> product_id|<s
tore_id> store_id|SUM(units_sold)", pos="248,140", rects="192,188,305,212 192,16
4,305,188 192,140,305,164 192,116,305,140 192,92,305,116 192,68,305,92", width="
1.56", height="2.00"];
S [label="S | (DIM_STORE) |<geography_id> geography_id|<id> id", pos="12
9,160", rects="77,184,182,208 77,160,182,184 77,136,182,160 77,112,182,136", wid
th="1.44", height="1.33"];
G [label="G | (DIM_GEOGRAPHY) |GROUP BY country|<id> id", pos="74,49", r
ects="1,73,147,97 1,49,147,73 1,25,147,49 1,1,147,25", width="2.03", height="1.3
3"];
P [label="P | (DIM_PRODUCT) |<brand_id> brand_id|<id> id|<product_catego
ry_id> product_category_id", pos="266,281", rects="199,317,333,341 199,293,333,3
17 199,269,333,293 199,245,333,269 199,221,333,245", width="1.86", height="1.67"
];
C [label="C | (DIM_PRODUCT_CATEGORY) |<id> id|product_category = 'tv'",
pos="478,328", rects="373,352,583,376 373,328,583,352 373,304,583,328 373,280,58
3,304", width="2.92", height="1.33"];
B [label="B | (DIM_BRAND) |GROUP BY brand|<id> id", pos="106,403", rects
="45,427,168,451 45,403,168,427 45,379,168,403 45,355,168,379", width="1.69", he
ight="1.33"];
D [label="D | (DIM_DATE) |<id> id|year = 1997", pos="363,85", rects="315
,109,411,133 315,85,411,109 315,61,411,85 315,37,411,61", width="1.33", height="
1.33"];
B:id -- P:brand_id [pos="129,355 159,338 213,309 244,293"];
G:id -- S:geography_id [pos="79,25 90,51 114,110 124,136"];
F:store_id -- S:id [pos="192,113 189,114 185,114 182,115"];
D:id -- F:date_id [pos="345,85 324,100 288,125 266,140"];
C:id -- P:product_category_id [pos="447,304 407,288 337,261 297,245"];
F:product_id -- P:id [pos="250,140 253,165 261,220 264,245"];
}
--------------------------------------
python revj.py sample.sql | fdp | gvpr -fdir.g
digraph G {
F [height="2.00",
label="F | (FACT_SALES) |<date_id> date_id|<product_id> product_
id|<store_id> store_id|
SUM(units_sold)",
pos="248,140",
rects="192,188,305,212 192,164,305,188 192,140,305,164 192,116,3
05,140 192,92,305,116 1
92,68,305,92",
shape=record,
width="1.56"];
P [height="1.67",
label="P | (DIM_PRODUCT) |<brand_id> brand_id|<id> id|<product_c
ategory_id> product_cat
egory_id",
pos="266,281",
rects="199,317,333,341 199,293,333,317 199,269,333,293 199,245,3
33,269 199,221,333,245",
shape=record,
width="1.86"];
F:product_id:e -> P:id:w;
D [height="1.33",
label="D | (DIM_DATE) |<id> id|year = 1997",
pos="363,85",
rects="315,109,411,133 315,85,411,109 315,61,411,85 315,37,411,6
1",
shape=record,
width="1.33"];
F:date_id:e -> D:id:w;
S [height="1.33",
label="S | (DIM_STORE) |<geography_id> geography_id|<id> id",
pos="129,160",
rects="77,184,182,208 77,160,182,184 77,136,182,160 77,112,182,1
36",
shape=record,
width="1.44"];
S:id:e -> F:store_id:w;
G [height="1.33",
label="G | (DIM_GEOGRAPHY) |GROUP BY country|<id> id",
pos="74,49",
rects="1,73,147,97 1,49,147,73 1,25,147,49 1,1,147,25",
shape=record,
width="2.03"];
G:id:e -> S:geography_id:w;
C [height="1.33",
label="C | (DIM_PRODUCT_CATEGORY) |<id> id|product_category = 't
v'",
pos="478,328",
rects="373,352,583,376 373,328,583,352 373,304,583,328 373,280,5
83,304",
shape=record,
width="2.92"];
P:product_category_id:e -> C:id:w;
B [height="1.33",
label="B | (DIM_BRAND) |GROUP BY brand|<id> id",
pos="106,403",
rects="45,427,168,451 45,403,168,427 45,379,168,403 45,355,168,3
79",
shape=record,
width="1.69"];
B:id:e -> P:brand_id:w;
}
-------------------
python revj.py sample.sql | fdp | gvpr -fdir.g | dot -Grankdir=LR -Edir=none -T png -o current.png
Output is attached as current.png
[ellson] Can you send the -v output from the dot command used to generate the png image?
This was not it.
[toth] Here is the output (on Windows):
python wauj_22.py sample.sql | fdp | gvpr -fdir.g | dot -Grankdir=LR -Edir=none -v -T png -o current.png
Activated plugin library: gvplugin_pango.dll
Using textlayout: textlayout:cairo
Activated plugin library: gvplugin_dot_layout.dll
Using layout: dot:dot_layout
Using render: cairo:cairo
Using device: png:cairo:cairo
The plugin configuration file:
C:Program FilesGraphviz2.20Binconfig
was successfully loaded.
render : cairo dot fig gd map ps svg tk vml vrml xdot
layout : circo dot fdp neato nop nop1 nop2 twopi
textlayout : textlayout
device : canon cmap cmapx cmapx_np dia dot eps fig gd gd2 gif hpgl ima
p imap_np ismap jpe jpeg jpg mif mp pcl pdf pic plain plain-ext png ps ps2 svg s
vgz tk vml vmlz vrml vtx wbmp xdot
loadimage : (lib) gd gd2 gif jpe jpeg jpg png ps svg
fontname: "Times-Roman" resolved to: (ps) "Times New Roman 13.9990234375"
network simplex: 7 nodes 6 edges 0 iter 0.03 sec
mincross: pass 0 iter 0 trying 0 cur_cross 1 best_cross 1
mincross: pass 0 iter 1 trying 1 cur_cross 1 best_cross 1
mincross: pass 0 iter 2 trying 2 cur_cross 1 best_cross 1
mincross: pass 0 iter 3 trying 3 cur_cross 1 best_cross 1
mincross: pass 1 iter 0 trying 0 cur_cross 1 best_cross 1
mincross: pass 1 iter 1 trying 1 cur_cross 1 best_cross 1
mincross: pass 1 iter 2 trying 2 cur_cross 1 best_cross 1
mincross: pass 1 iter 3 trying 3 cur_cross 1 best_cross 1
mincross: pass 2 iter 0 trying 0 cur_cross 1 best_cross 1
mincross: pass 2 iter 1 trying 1 cur_cross 1 best_cross 1
mincross: pass 2 iter 2 trying 2 cur_cross 1 best_cross 1
mincross: pass 2 iter 3 trying 3 cur_cross 1 best_cross 1
mincross: pass 2 iter 4 trying 4 cur_cross 1 best_cross 1
mincross: pass 2 iter 5 trying 5 cur_cross 1 best_cross 1
mincross: pass 2 iter 6 trying 6 cur_cross 1 best_cross 1
mincross: pass 2 iter 7 trying 7 cur_cross 1 best_cross 1
mincross: pass 2 iter 8 trying 8 cur_cross 1 best_cross 1
mincross G: 1 crossings, 0.00 secs.
network simplex: 13 nodes 14 edges 0 iter 0.00 sec
routesplines: 6 edges, 18 boxes 0.00 sec
Using render: cairo:cairo
Using device: png:cairo:cairo
dot: allocating a 1758K cairo image surface
[toth] Currently this is in Windows. I'll try this on Mac later today
C:USERSNoBackupwauj>dot -v -Tpng hello.dot > hello.png
Activated plugin library: gvplugin_pango.dll
Using textlayout: textlayout:cairo
Activated plugin library: gvplugin_dot_layout.dll
Using layout: dot:dot_layout
Using render: cairo:cairo
Using device: png:cairo:cairo
The plugin configuration file:
C:Program FilesGraphviz2.20Binconfig
was successfully loaded.
render : cairo dot fig gd map ps svg tk vml vrml xdot
layout : circo dot fdp neato nop nop1 nop2 twopi
textlayout : textlayout
device : canon cmap cmapx cmapx_np dia dot eps fig gd gd2 gif hpgl ima
p imap_np ismap jpe jpeg jpg mif mp pcl pdf pic plain plain-ext png ps ps2 svg s
vgz tk vml vmlz vrml vtx wbmp xdot
loadimage : (lib) gd gd2 gif jpe jpeg jpg png ps svg
fontname: "Times-Roman" resolved to: (ps) "Times New Roman 13.9990234375"
network simplex: 2 nodes 1 edges 0 iter 0.00 sec
mincross: pass 0 iter 0 trying 0 cur_cross 0 best_cross 0
mincross _anonymous_0: 0 crossings, 0.00 secs.
network simplex: 3 nodes 2 edges 0 iter 0.00 sec
routesplines: 1 edges, 3 boxes 0.00 sec
Using render: cairo:cairo
Using device: png:cairo:cairo
dot: allocating a 58K cairo image surface
with output.
[toth] Added one line "node [shape=record, fontsize=12];" and this seems to fix the line clipping. Idea came from the -v trace, where the fontsize is 13 followed by some decimals.
Owner: arif
Status: Fixed (8 Jan 2010)