.| Graphing Database Schema |
| The Original Graphviz |
| Enter the Dragon^H^H^H^H^H^HPerl |
| The Program |
| The Command |
| Resources |
| Author |
| Licence |
This article explains how to install the software necessary to be able to run a tiny Perl program to output a picture of a database schema to a image file, eg a *.png file.
There are various packages called /GraphViz/i. Here I refer to the one from the famous AT&T research labs. The link to Graphviz is in the last section. Read up on Graphviz, and then hit the download link.
The file I downloaded was graphviz-1.10.exe, of size 2.5 Mb.
Install it and you're half-way there.
We need a set of modules, installed in the following order.
Note: I give the version numbers as per my machines.
Graph depends on Heap.
If you go to CPAN central, http://search.cpan.org/, and search for a module called Test::More, you'll be directed to Perl 5.8.1. Perhaps this is a hint.
Further, the module Test::More is delivered inside Test::Simple. So, the simple solution is to go to via the author - Michael G Schwern - to http://search.cpan.org/~mschwern/, and download Test::Simple.
GraphViz depends on Graph, IPC::Run, Math::Bezier and Test::More.
We have now assembled all the Perl modules required.
All that's needed now is a little program to run, and a command line to drive it.
GraphViz::DBI comes with a fine, almost-usable program called dbigraph.pl, to which we make just a couple of tiny changes:
From this:
use GraphViz::DBI;
to this:
use Local::GraphViz;
From this:
print GraphViz::DBI->new($dbh)->graph_tables->$as;
to this:
binmode STDOUT;
print Local::GraphViz->new($dbh)->graph_tables->$as;
This binmode is just a pain, as is normal for MS Windows-like platforms.
The replacement module requires a bit more explanation.
In GraphViz::DBI is a sub is_foreign_key(), which determines whether or not a column is a foreign key. The plotter needs this in order to know when to draw a line between 2 boxes representing tables, which line represents the fact that there exists a relationship between the 2 tables.
The default implementation of GraphViz::DBI assumes that for a column called t_id, then it is a foreign key if t is the name of a table. This does not match my convention, so I wrote a replacement for the sub, which uses my own table and column naming convention to determine if a column is in fact a foreign key. That convention is documented elsewhere. See below for the link.
Remember: TMTOWTDI. My Local::GraphViz contains this code:
package Local::GraphViz;
use strict;
use warnings;
use base qw/GraphViz::DBI/;
# -----------------------------------------------
sub get_tables
{
my $self = shift; # SQL_IDENTIFIER_QUOTE_CHAR.
my($quote) = $self -> get_dbh() -> get_info(29) || '';
$$self{'tables'} ||=
[
map{s/$quote(.+)$quote/$1/; $_} $self->get_dbh()->tables()
];
@{$$self{'tables'} };
} # End of get_tables.
# -----------------------------------------------
sub is_foreign_key
{
my($self, $table, $column) = @_;
my($result) = '';
if ($column =~ /^$table[-_](.+)$/i)
{
$result = ($1 =~ /^(.+)[-_]id$/i) ? $1 : '';
$result = '' if (! $self -> is_table($result) );
}
$result;
} # End of is_foreign_key.
# -----------------------------------------------
1;
All we need now is to run this:
shell>perl dbigraph.pl --dbd=mysql --user=root --pass=? --as=png --dbname=x > x.png
where you supply the password instead of the ?, and you replace the 2 'x's with the name of any database. Change the driver from mysql to Pg, or whatever, if necessary.
When you run this, you'll get 2 warnings (under Perl 5.8.0), which can be ignored:
Prototype mismatch: sub Socket::IPPROTO_TCP () vs none at C:/Perl/lib/Socket.pm line 395.
AT&T's Graphviz: http://www.research.att.com/sw/tools/graphviz/.
An image of a schema as described above: http://savage.net.au/assets/images/local/aussi.png.
The process of choosing names for a database schema is documented here: http://savage.net.au/Ron/html/naming-database-objects.html.
Other database drawing tools: http://savage.net.au/Ron/html/drawing-tools.html.
Ron Savage .
Home page: http://savage.net.au/index.html
This version disguises my email address.
Original version.
Australian Copyright © 2002 Ron Savage. All rights reserved.
All Programs of mine are 'OSI Certified Open Source Software';
you can redistribute them and/or modify them under the terms of
The Artistic License, a copy of which is available at:
http://www.opensource.org/licenses/index.html