/ 
GraphViz-DBI-FromSchema-0.01
River stage zero No dependents
/ GraphViz::DBI::FromSchema

NAME

GraphViz::DBI::FromSchema - Create a diagram of database tables, using the foreign key information in the schema

SYNOPSIS

use DBI;
use GraphViz::DBI::FromSchema;

my $db = DBI->connect(@dsn_etc);

my $filename = 'DB_diagram.ps';
open my $file, '>', $filename or die "Opening $filename failed: $!\n";
print $file GraphViz::DBI::FromSchema->new($db)->graph_tables->as_ps;

DESCRIPTION

This module creates a diagram of the tables in a database, listing the fields in each table and with arrows indicating foreign keys between tables.

GraphViz::DBI provides functionality for doing this. By default it identifies foreign keys based on fields being named in a particular way, and suggests subclassing it to implement different heuristics. This module is a subclass which uses the DBI to interrogate the database about the foreign keys defined for each table -- which, for databases which support referential integrity, should work irrespective of your naming scheme.

The interface is identical to GraphViz::DBI's, so see its documentation for details.

Printing Large Diagrams

For reasonably sized databases, the diagrams generated by this module can be too large to fit on to paper that fits in your printer. Unix has a poster command which can help with this, splitting a large diagram up into 'tiles' printed on separate sheets, complete with crop marks for trimming and assembling into a giant poster. Sample usage:

$ poster -m A4 -s 0.45 DB_diagram_big.ps > DB_diagram_A4.ps

Fixing Table Names

The table names retrieved by GraphViz::DBI appear to suffer from a couple of problems:

  • They are prefixed by the database name (and a dot).

  • They are surrounded by the appropriate quote marks used for identifiers in that sort of database. There are several reports of this in the GraphViz::DBI RT queue.

Both of these get in the way of matching up foreign keys with the tables they reference, so this module overrides fetching the list of table names to remove them.

FUTURE PLANS

In the common case where you have a DBI object and you want a diagram (like in the "SYNOPSIS") it's irritating to have deal with the GraphViz::DBI::FromSchema object, which is really an implementation detail. So it may be worth creating a functional interface to hide this.

It may further make sense to have a function which saves the diagram to a file as well, since that's likely to be what people want to do with it.

CAVEATS

This has been developed using MySQL. There isn't anything MySQL-specific in it, and it should work with other database software, but that hasn't been tried. The only thing required is that the DBI driver implements the foreign_key_info method.

The table-name 'fixing' described above may be a bad idea, or not work in some circumstances. Arguably this should be done in GraphViz::DBI rather than here.

This module is lacking substantive tests, because of the difficulty of automatically testing something which needs a database and generates graphical output. Suggestions on what to do about this welcome.

SEE ALSO

CREDITS

Written by Ovid and Smylers at Pipex Communications UK Ltd trading as Donhost, http://www.donhost.co.uk.

Maintained by Smylers <smylers@cpan.org>

Thanks to Marcel Grünauer for writing GraphViz::DBI.

COPYRIGHT & LICENCE

Copyright 2007-2008 by Pipex Communications UK Ltd

This library is software libre; you may redistribute it and modify it under the terms of any of these licences: