|
This article will try to combine the power of Perl and GraphViz in order to partly visualize a DBMS. I will provide a quick introduction to GraphViz, and then present the use of GraphViz for illustrating Entity Relation (ER) diagrams. Finally, I will demonstrate a Perl script for visualizing all the tables of a user in an Oracle DBMS using GraphViz.
May 24, 2005
URL:http://www.drdobbs.com/using-graphviz-for-database-schema-visua/199102082
Mihalis Tsoukalos
This article will try to combine the power of Perl and GraphViz in order to partly visualize a DBMS. I will provide a quick introduction to GraphViz, and then present the use of GraphViz for illustrating Entity Relation (ER) diagrams. Finally, I will demonstrate a Perl script for visualizing all the tables of a user in an Oracle DBMS using GraphViz.
As you may already know, the very rich collection of Perl modules available from CPAN includes modules that utilize and produce GraphViz output. The basic Perl module is called GraphViz (the Interface to the GraphViz graphing tools), which is also the base of GraphViz::DBI module, and it is used for visualizing database tables and relations. This article utilizes plain PL/SQL and the Perl programming language to graphically represent all the tables of a user including their fields as well as their relationships with the tablespaces of the DBMS.
An Introduction to GraphViz
GraphViz consists of a set of command-line tools, as well as a language called "dot" that can draw either directed or undirected graphs. There are also some GUI tools for GraphViz, but I will not discuss those in this article. GraphViz is available both compiled for most UNIX distributions and in source code format.
The tools included in GraphViz are "dot", which is for directed graphs; "neato", which is for undirected graph layouts; and "twopi", which is for undirected graph layouts in a radial form. The general way of executing each of the tools is as follows:
toolname -Tps filename -o output.pswhere "toolname" is the name of the command-line tool, and "-Tps" is a parameter that denotes that the output should be in postscript. GraphViz also supports, among others, GIF, PNG, and JPEG formats as its output. "filename" is the name of the file to process, and "-o output.ps" denotes that the output is going to be stored in a file called output.ps. Using GraphViz for Entity Relation Diagrams In this section, an Entity Relation (ER) diagram will be constructed as a simple example of GraphViz capabilities. An ER diagram is a graph that represents entity sets, attributes, and relations. It is a common practice to represent entity sets as rectangles, attributes as ovals, and relations as diamonds. GraphViz can painlessly cope with ER diagrams. Figure 1 shows the ER diagram made using the "neato" code in Listing 1 as follows:
graph G
{
rotate = 90;
B_ISBN[label="ISBN"];
B_Title[label="Title"];
B_Price[label="Price"];
O_id[label="id"];
O_TC[label="Total Cost"];
O_DC[label="Delivery Cost"];
node[shape=rectangle];
Book[label="Book"];
Order[label="Order"];
BO[label="Book Order relation", shape=diamond];
B_ISBN -- Book[len=1.2];
B_Title -- Book[len=1.2];
B_Price -- Book[len=1.2];
O_id -- Order[len=1.2];
O_TC -- Order[len=1.2];
O_DC -- Order[len=1.2];
Book -- BO[label = "1", len=2];
Order -- BO[label = "m", len=2];
}
GraphViz tools are also able of decorating their nodes. There are many parameters
that can change the look of the nodes. Table
1 shows some of the node attributes, whereas Table
2 shows some of the edge attributes.
A Perl Script for RDBMS Visualization
This script will not use the famous DBD and DBI Perl modules, because they add complexity to our process, although these modules are very handy and reliable. Plain PL/SQL will be used to extract information from the Oracle 10g DBMS as text. It is possible to use Perl DBD and DBI modules to produce the same text output.
Listing 3 shows the PL/SQL code that extracts the required information from Oracle 10g. It makes use of Oracle USER_TAB_COLUMNS and USER_TABLES tables. A graphical description of these two tables is shown in Figure 2.
The Perl script used is shown in Listing 2. It accepts the results of Listing 3 as input and produces a GraphViz file as its output, which must then be manually processed by the dot utility. After extracting the information about the tables and their respective fields, the aforementioned Perl script writes the word TABLESPACE in the output and continues with the information about the tablespace/table relations. The TABLESPACE word helps us separate the table-field relations part from the tablespace-table relations part.
A simple graph produced by the Perl script is shown in Figure 3. Depending on the total number of tables, the output may be huge, but this gives a good picture of the user tables and their respective tablespaces. It is important to understand that the presented PL/SQL and Perl scripts can be easily modified to display different information according to your needs. USER_TAB_COLUMNS and USER_TABLES tables contain much useful information that we can easily take advantage of.
Conclusion
GraphViz is a very useful set of tools for producing both simple and complex graphs. In this article, I've shown how to use GraphViz command-line tools for DBMS visualization. However, GraphViz can be also used in other areas such as the production of UML diagrams as well as network, directory, and source code structure visualization.
Acknowledgments
I thank Antonis Kouroupis for sharing a part of his Oracle DBMS schema with me.
This article has been written using Oracle 10g Enterprise Edition Release 10.1.0.3.0 for Mac OS X Panther (Server edition) and Perl v5.8.1-RC3 for Mac OS X Panther.
Resources
1. GraphViz Web Site -- http://www.graphviz.org/
2. GraphViz DBI -- http://search.cpan.org/~teejay/GraphViz-DBI-0.02/
3. GraphViz README -- http://search.cpan.org/src/LBROCARD/GraphViz-2.02/README
4. Jünger, M. and P. Mutzel, eds. 2004. Graph Drawing Software, Series: Mathematics and Visualization. Springer. ISBN 3-540-00881-0.
5. Kamada, T., and S. Kawai. 1989. An algorithm for drawing general undirected graphs. Information Processing Letters, 31(1):7-15.
6. Gansner, E.R., Koutsofios, E., North, S.C. and Vo, K.P. 1993. A Technique for Drawing Directed Graphs. IEEE Trans. on Software Engineering, 19(3):214-230.
7. GraphViz for Mac OS X -- http://www.pixelglow.com/graphviz/
8. Oracle Technology Network -- http://otn.oracle.com/
Mihalis Tsoukalos lives in Greece with his wife, Eugenia, and works as a High School Teacher. He holds a B.Sc. in Mathematics and a M.Sc. in IT from University College London. Before teaching, he worked as a Unix systems administrator and an Oracle DBA. Mihalis can be reached at: [email protected].
graph G
{
rotate = 90;
B_ISBN[label="ISBN"];
B_Title[label="Title"];
B_Price[label="Price"];
O_id[label="id"];
O_TC[label="Total Cost"];
O_DC[label="Delivery Cost"];
node[shape=rectangle];
Book[label="Book"];
Order[label="Order"];
BO[label="Book Order relation", shape=diamond];
B_ISBN -- Book[len=1.2];
B_Title -- Book[len=1.2];
B_Price -- Book[len=1.2];
O_id -- Order[len=1.2];
O_TC -- Order[len=1.2];
O_DC -- Order[len=1.2];
Book -- BO[label = "1", len=2];
Order -- BO[label = "m", len=2];
}
#!/usr/bin/perl -w
# $Id: convert.pl,v 1.5 2005/03/07 21:48:50 mtsouk Exp mtsouk $
#
use strict;
my $tablespace_found = 0;
my $line="";
# The following two hashes will hold the
# names of the tables and the tablespaces.
my %TABLE = ();
my %TABLESPACE = ();
my $firsttable = 1;
die <<Thanatos unless @ARGV;
usage:
$0 inputfile.log outputfilename
inputfile.log: The input filename
outputfilename: The filename of the output
Thanatos
if ( @ARGV != 2 )
{
die <<Thanatos
usage info:
Please use exactly 2 arguments!
Thanatos
}
my $input = $ARGV[0];
open(INPUT, "< $input " ) ||
die "Cannot read $input: $!\n";
my $output = $ARGV[1].".dot";
open(OUTPUT, "> $output " ) ||
die "Cannot write $output: $!\n";
print OUTPUT "digraph G\n";
print OUTPUT "{\n";
print OUTPUT "\tgraph [rankdir = \"LR\" ]\;\n";
print OUTPUT "\tnode[fontsize = \"14\" style=bold]\;";
print OUTPUT "\n\n";
print OUTPUT "\# Table-field connection part.\n";
# Read the input file
while ($line = <INPUT>)
{
chomp $line;
# Drop empty lines.
if ( $line =~ /^$/ )
{
next;
}
if ( $line =~ /^TABLESPACE/ )
{
# Close the previous table node.
print OUTPUT " \"\n\tshape = \"record\"\n\t]\;\n\n";
print OUTPUT "\n\# TABLESPACE-table connection part.\n";
$tablespace_found = 1;
next;
}
if ( $tablespace_found )
{
my $tablespace = (split " ", $line)[1];
my $table = (split " ", $line)[0];
# Connect tables with their Tablespaces
print OUTPUT "\t$table:tb -> TB_$tablespace:tb\;\n";
if ( !defined $TABLESPACE{$tablespace} )
{
$TABLESPACE{$tablespace} = 1;
}
}
else
{
my $table = (split " ", $line)[0];
my $column = (split " ", $line)[1];
if ( defined $TABLE{$table} )
{
print OUTPUT " | ".lc($column);
}
else
{
# The very first table has to be treated differently.
if ( $firsttable )
{
$firsttable = 0;
}
else
{
print OUTPUT " \"\n\tshape = \"record\"\n\t]\;\n\n";
}
$TABLE{$table} = 1;
print OUTPUT "\t$table [label = \"<tb> $table";
print OUTPUT " | ".lc($column);
}
}
}
print OUTPUT "\n\# Tablespace decoration part.\n";
my $tablespace = "";
foreach $tablespace ( keys %TABLESPACE )
{
print OUTPUT "\tTB_".$tablespace;
print OUTPUT '[label="<tb> '.$tablespace.'"';
print OUTPUT ' shape = "record" style=filled color="red"];';
print OUTPUT "\n";
}
my $first = 1;
my $tb_first = "";
foreach $tablespace ( keys %TABLESPACE )
{
print OUTPUT "\tTB_$tablespace ->";
if ( $first )
{
$tb_first = $tablespace;
$first = 0;
}
}
print OUTPUT "TB_$tb_first;\n";
print OUTPUT "}\n";
# Close INPUT and OUTPUT
close(INPUT) ||
die "Cannot close $input: $!\n";
close(OUTPUT) ||
die "Cannot close $output: $!\n";
exit 0;
REM REM Author: Mihalis Tsoukalos REM Date: Friday 04 March 2005 REM set echo off set heading off embedded off verify off set feedback off spool table_col.log btitle off ttitle off set termout off SELECT table_name, column_name FROM user_tab_columns ORDER BY table_name / PROMPT TABLESPACES SELECT table_name, tablespace_name FROM user_tables / spool off
Terms of Service | Privacy Statement | Copyright © 2024 UBM Tech, All rights reserved.