Graphing Database Schema

Table of contents

Graphing Database Schema
The Original Graphviz
Enter the Dragon^H^H^H^H^H^HPerl
The Program
The Command
Resources
Author
Licence

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.

The Original Graphviz

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

Version: 1.01 01-Jun-2006

This version disguises my email address.

Version: 1.00 18-Feb-2002

Original version.

Licence

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