Graphing database schema

Graphing Database Schema

The Original Graphviz

Enter the Dragon^H^H^H^H^H^HPerl

The Program

Graphing database schema

Graphing Database Schema

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.

Enter the Dragon^H^H^H^H^H^HPerl

We need a set of modules, installed in the following order.

Note: I give the version numbers as per my machines.

Heap V 0.50
Graph V 0.20101
Graph depends on Heap.
IPC::Run V 0.75
Math::Bezier V 0.01
Test::More V 0.47
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 V 1.8
GraphViz depends on Graph, IPC::Run, Math::Bezier and Test::More.
GraphViz::DBI 0.02

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.

The Program

GraphViz::DBI comes with a fine, almost-usable program called dbigraph.pl, to which we make just a couple of tiny changes:

Replace line 9

From this:

	use GraphViz::DBI;

to this:

	use Local::GraphViz;

Replace line 46

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;

The Command

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.

Resources

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/web-site/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.

Author

Ron Savage.

Home page: http://savage.net.au/index.html

This POD was converted to HTML by /Perl.html#fancy-pom2.pl

Version: 1.01 01-Jun-2006
This version disguises my email address.
Version: 1.00 18-Feb-2002
Original version.

Licence

	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

Top of page

Graphing database schema

Table of Contents

Graphing database schema