NAME

GraphViz::DBI::General - graph table relationships from a DBI handle

SYNOPSIS

   use GraphViz::DBI::General;

   my $gbdh = GraphViz::DBI::General->new($dbh);
   $gbdh->schema('public');  # default used by Postgresql
   $gbdh->catalog( undef );  
   open my $fh, ">", $diagram_file or die "Couldn't open $diagram_file: $!";
   $gbdh->graph_tables->as_png($fh);

DESCRIPTION

This is a subclass of GraphViz::DBI. It can be used to generate a graph of foreign key relationships between the tables of a database, given a database handle (and perhaps a schema name and/or a catalog name). It should work for any database with a driver that supports the foreign_key_info method (such as Postgresql, versions 7.3 and later).

Note that foreign_key_info is labeled as ``experimental'' in the DBI documentation: if it's behavior changes in the future, it may cause problems for this code.

Known Compatibility

Currently this module has been tested with:

               Version    
  -----------|--------            
  Postgresql |  8.0.1
  DBI        |  1.48
  DBD::Pg    |  1.40
  Linux      |  2.4.20

If you're so inclined, please do report your experiences with using it in other environments. Future versions will include a summary of this information.

Please send the output of the version_report method along with your reports:

  my $gbdh = GraphViz::DBI::General->new($dbh);
  print $gbdh->version_report;

Schema and Catalog settings

The settings you will most likely want to use with the postgresql database are as indicated in the SYNOPSIS above:

   $gbdh->schema('public');  
   $gbdh->catalog( undef );

You might, however have a different schema name you need to work with (your login name is a common choice on many systems).

In postgresql there is not concept of the ``catalog'', so it's set to undef, this may be different for your own database.

For some databases you might not need any schema or catalog setting, and both should be undef.

MOTIVATION

This module was inspired by GraphViz::DBI, which generates a graph of table relationships given only a DBI handle. Unfortunately, however, it relies on a naming convention to find foreign key relationships, and has no concept of schemas (or catalogs), which makes it unusable with a database such as Postgresql (for Postgresql, it draws tables for the entire pg_catalog and information_schema, generating huge output graphs).

GraphViz::DBI::General behaves exactly like GraphViz::DBI, except that it restricts it's scope to a given catalog and schema (if these are applicable), and also uses the DBI method foreign_key_info to find foreign keys rather than relying on some arbitrary naming convention.

In theory, this makes GraphViz::DBI::General more general, and it should be usable with any database with a fully-featured DBD driver; but in fact, I'm not certain how widespread foreign_key_info support is (or will be). At the very least GraphViz::DBI::General works with Postgresql, which the original GraphViz::DBI definitely does not.

METHODS

GraphViz::DBI::General provides:

 o  Methods for specifying the schema and catalog (when applicable).
 o  A get_tables method with scope restricted by schema & catalog.
 o  A version of the is_foreign_key method that does not rely on naming conventions.

See the GraphViz::DBI manpage and the GraphViz manpage for documentation of the other available methods.

In detail, the methods provided by GraphViz::DBI::General are:

set_schema - set the schema attribute (only required for some databases, e.g. typically 'public' for Postgresql)
set_catalog - set the catalog attribute (not needed if the database in use does not support the feature, e.g. Postgresql does not).
get_schema - returns the value of the schema attribute.
get_catalog - returns the value of the catalog attribute.
get_tables - determines a listing of all tables (for the current schema and/or catalog, which should be specified if applicable to the database in use). Returns the list, and saves a reference to it as the attribute ``tables''.
is_foreign_key - given two args ``table'' and ``field'' determines if table.field is a foreign key for some other table, and if so returns the name of the table, otherwise a false value (''). This version should override the one in GraphViz::DBI.
version_report - report on the versions of different software packages in use by this module.

TODO

This module is sadly lacking in tests, because it's difficult to write portable tests for it: What databases exist? Do I have access permissions to do a ``CREATE DATABASE''? (On what databases is a ``CREATE DATABASE'' supported?) Does the system have the necessary fonts to generate the same graph image that I have? (Do fonts of the same name always look the same on different systems? I've seen signs that the answer to that is ``no, not always''.) Still, I ought to be able to do better than no tests at all, e.g. I ought to be able to write postgresql specific tests that are just skipped if not applicable.

AUTHOR

Joseph Brenner, <doom@kzsu.stanford.edu>

COPYRIGHT AND LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.2 or, at your option, any later version of Perl 5 you may have available.

BUGS

None reported... yet.

GraphViz::DBI::General - graph table relationships from a DBI handle