The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
GraphViz-DBI-FromSchema-0.5
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.

Note if you simply wish to create a diagram for a database and save it to a file, the provided schema_diagram command does this. You only need to use this module if you want to manipulate the diagram programmatically.

GraphViz::DBI provides functionality for creating database diagrams. 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 can suffer from a couple of problems:

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

  • With MySQL they are surrounded by backticks. 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. It's currently hacky and fragile; I'm planning on improving this.

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 and tried out with Postgres and MySQL. It should work with other database software, but given there are some differences between MySQL and Postgres, others may have further differences which are not yet taken into account.

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 Webfusion, http://www.webfusion.com/.

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, 2008-2011 Webfusion Ltd

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