1 package SQL::Translator::Producer::GraphViz;
3 # -------------------------------------------------------------------
4 # $Id: GraphViz.pm,v 1.14 2007-09-26 13:20:09 schiffbruechige Exp $
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002-4 SQLFairy Authors
8 # This program is free software; you can redistribute it and/or
9 # modify it under the terms of the GNU General Public License as
10 # published by the Free Software Foundation; version 2.
12 # This program is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 # General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with this program; if not, write to the Free Software
19 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
21 # -------------------------------------------------------------------
27 SQL::Translator::Producer::GraphViz - GraphViz producer for SQL::Translator
33 my $trans = new SQL::Translator(
34 from => 'MySQL', # or your db of choice
37 out_file => 'schema.png',
39 show_constraints => 1,
43 ) or die SQL::Translator->error;
45 $trans->translate or die $trans->error;
49 Creates a graph of a schema using the amazing graphviz
50 (see http://www.graphviz.org/) application (via
51 the GraphViz module). It's nifty--you should try it!
59 the name of the file where the graphviz graphic is to be written
61 =item * layout (DEFAULT: 'dot')
63 determines which layout algorithm GraphViz will use; possible
64 values are 'dot' (the default GraphViz layout for directed graph
65 layouts), 'neato' (for undirected graph layouts - spring model)
66 or 'twopi' (for undirected graph layouts - circular)
68 =item * node_shape (DEFAULT: 'record')
70 sets the node shape of each table in the graph; this can be
71 one of 'record', 'plaintext', 'ellipse', 'circle', 'egg',
72 'triangle', 'box', 'diamond', 'trapezium', 'parallelogram',
73 'house', 'hexagon', or 'octagon'
75 =item * output_type (DEFAULT: 'png')
77 sets the file type of the output graphic; possible values are
78 'ps', 'hpgl', 'pcl', 'mif', 'pic', 'gd', 'gd2', 'gif', 'jpeg',
79 'png', 'wbmp', 'cmap', 'ismap', 'imap', 'vrml', 'vtx', 'mp',
80 'fig', 'svg', 'canon', 'plain' or 'text' (see GraphViz for
81 details on each of these)
83 =item * width (DEFAULT: 8.5)
85 width (in inches) of the output graphic
87 =item * height (DEFAULT: 11)
89 height (in inches) of the output grahic
93 custom font size for node and edge labels (note that arbitrarily large
94 sizes may be ignored due to page size or graph size constraints)
98 custom font name (or full path to font file) for node, edge, and graph
103 reference to a hash of node attribute names and their values; these
104 may override general fontname or fontsize parameter
108 reference to a hash of edge attribute names and their values; these
109 may override general fontname or fontsize parameter
113 reference to a hash of graph attribute names and their values; these
114 may override the general fontname parameter
116 =item * show_fields (DEFAULT: true)
118 if set to a true value, the names of the colums in a table will
119 be displayed in each table's node
123 if set to a true value, only columns which are foreign keys
124 will be displayed in each table's node
126 =item * show_datatypes
128 if set to a true value, the datatype of each column will be
129 displayed next to each column's name; this option will have no
130 effect if the value of show_fields is set to false
134 if set to a true value, the size (in bytes) of each CHAR and
135 VARCHAR column will be displayed in parentheses next to the
136 column's name; this option will have no effect if the value of
137 show_fields is set to false
139 =item * show_constraints
141 if set to a true value, a field's constraints (i.e., its
142 primary-key-ness, its foreign-key-ness and/or its uniqueness)
143 will appear as a comma-separated list in brackets next to the
144 field's name; this option will have no effect if the value of
145 show_fields is set to false
149 if set to a true value, the graphic will have a background
150 color of 'lightgoldenrodyellow'; otherwise the background
155 if set to a true value, the make_natural_join method of
156 SQL::Translator::Schema will be called before generating the
157 graph; a true value for join_pk_only (see below) implies a
158 true value for this option
162 the value of this option will be passed as the value of the
163 like-named argument in the make_natural_join method (see
164 natural_join above) of SQL::Translator::Schema, if either the
165 value of this option or the natural_join option is set to true
169 the value of this option will be passed as the value of the
170 like-named argument in the make_natural_join method (see
171 natural_join above) of SQL::Translator::Schema, if either
172 the natural_join or join_pk_only options has a true value
181 use SQL::Translator::Schema::Constants;
182 use SQL::Translator::Utils qw(debug);
184 use vars qw[ $VERSION $DEBUG ];
185 $VERSION = sprintf "%d.%02d", q$Revision: 1.14 $ =~ /(\d+)\.(\d+)/;
186 $DEBUG = 0 unless defined $DEBUG;
188 use constant VALID_LAYOUT => {
194 use constant VALID_NODE_SHAPE => {
210 use constant VALID_OUTPUT => {
237 my $schema = $t->schema;
238 my $args = $t->producer_args;
239 local $DEBUG = $t->debug;
241 my $out_file = $args->{'out_file'} || '';
242 my $layout = $args->{'layout'} || 'dot';
243 my $node_shape = $args->{'node_shape'} || 'record';
244 my $output_type = $args->{'output_type'} || 'png';
245 my $width = defined $args->{'width'}
246 ? $args->{'width'} : 8.5;
247 my $height = defined $args->{'height'}
248 ? $args->{'height'} : 11;
249 my $fontsize = $args->{'fontsize'};
250 my $fontname = $args->{'fontname'};
251 my $edgeattrs = $args->{'edgeattrs'} || {};
252 my $graphattrs = $args->{'graphattrs'} || {};
253 my $nodeattrs = $args->{'nodeattrs'} || {};
254 my $show_fields = defined $args->{'show_fields'}
255 ? $args->{'show_fields'} : 1;
256 my $add_color = $args->{'add_color'};
257 my $natural_join = $args->{'natural_join'};
258 my $show_fk_only = $args->{'show_fk_only'};
259 my $show_datatypes = $args->{'show_datatypes'};
260 my $show_sizes = $args->{'show_sizes'};
261 my $show_constraints = $args->{'show_constraints'};
262 my $join_pk_only = $args->{'join_pk_only'};
263 my $skip_fields = $args->{'skip_fields'} || '';
264 my %skip = map { s/^\s+|\s+$//g; length $_ ? ($_, 1) : () }
265 split ( /,/, $skip_fields );
266 $natural_join ||= $join_pk_only;
268 $schema->make_natural_joins(
269 join_pk_only => $join_pk_only,
270 skip_fields => $args->{'skip_fields'},
273 die "Invalid layout '$layout'" unless VALID_LAYOUT->{ $layout };
274 die "Invalid output type: '$output_type'"
275 unless VALID_OUTPUT->{ $output_type };
276 die "Invalid node shape'$node_shape'"
277 unless VALID_NODE_SHAPE->{ $node_shape };
279 for ( $height, $width ) {
280 $_ = 0 unless $_ =~ /^\d+(.\d)?$/;
285 # Create GraphViz and see if we can produce the output type.
288 directed => $natural_join ? 0 : 1,
291 bgcolor => $add_color ? 'lightgoldenrodyellow' : 'white',
293 shape => $node_shape,
295 fillcolor => 'white',
298 $args{'width'} = $width if $width;
299 $args{'height'} = $height if $height;
300 # set fontsize for edge and node labels if specified
302 $args{'node'}->{'fontsize'} = $fontsize;
303 $args{'edge'} = {} unless $args{'edge'};
304 $args{'edge'}->{'fontsize'} = $fontsize;
306 # set the font name globally for node, edge, and graph labels if
307 # specified (use node, edge, or graph attributes for individual
308 # font specification)
310 $args{'node'}->{'fontname'} = $fontname;
311 $args{'edge'} = {} unless $args{'edge'};
312 $args{'edge'}->{'fontname'} = $fontname;
313 $args{'graph'} = {} unless $args{'graph'};
314 $args{'graph'}->{'fontname'} = $fontname;
316 # set additional node, edge, and graph attributes; these may
317 # possibly override ones set before
318 while (my ($key,$val) = each %$nodeattrs) {
319 $args{'node'}->{$key} = $val;
321 $args{'edge'} = {} if %$edgeattrs && !$args{'edge'};
322 while (my ($key,$val) = each %$edgeattrs) {
323 $args{'edge'}->{$key} = $val;
325 $args{'graph'} = {} if %$edgeattrs && !$args{'graph'};
326 while (my ($key,$val) = each %$graphattrs) {
327 $args{'graph'}->{$key} = $val;
330 my $gv = GraphViz->new( %args ) or die "Can't create GraphViz object\n";
332 my %nj_registry; # for locations of fields for natural joins
333 my @fk_registry; # for locations of fields for foreign keys
335 for my $table ( $schema->get_tables ) {
336 my $table_name = $table->name;
337 my @fields = $table->get_fields;
338 if ( $show_fk_only ) {
339 @fields = grep { $_->is_foreign_key } @fields;
342 my $field_str = join(
347 . ( $show_datatypes ? '\ ' . $_->data_type : '')
348 . ( $show_sizes && ! $show_datatypes ? '\ ' : '')
349 . ( $show_sizes && $_->data_type =~ /^(VAR)?CHAR2?$/i ? '(' . $_->size . ')' : '')
350 . ( $show_constraints ?
351 ( $_->is_primary_key || $_->is_foreign_key || $_->is_unique ? '\ [' : '' )
352 . ( $_->is_primary_key ? 'PK' : '' )
353 . ( $_->is_primary_key && ($_->is_foreign_key || $_->is_unique) ? ',' : '' )
354 . ( $_->is_foreign_key ? 'FK' : '' )
355 . ( $_->is_unique && ($_->is_primary_key || $_->is_foreign_key) ? ',' : '' )
356 . ( $_->is_unique ? 'U' : '' )
357 . ( $_->is_primary_key || $_->is_foreign_key || $_->is_unique ? ']' : '' )
362 my $label = $show_fields ? "{$table_name|$field_str}" : $table_name;
363 # $gv->add_node( $table_name, label => $label );
364 $gv->add_node( $table_name, label => $label, ($node_shape eq 'record' ? ( shape => $node_shape ) : ()) );
365 debug("Processing table '$table_name'");
367 debug("Fields = ", join(', ', map { $_->name } @fields));
369 for my $f ( @fields ) {
370 my $name = $f->name or next;
371 my $is_pk = $f->is_primary_key;
372 my $is_unique = $f->is_unique;
375 # Decide if we should skip this field.
377 if ( $natural_join ) {
378 next unless $is_pk || $f->is_foreign_key;
381 my $constraints = $f->{'constraints'};
383 if ( $natural_join && !$skip{ $name } ) {
384 push @{ $nj_registry{ $name } }, $table_name;
388 unless ( $natural_join ) {
389 for my $c ( $table->get_constraints ) {
390 next unless $c->type eq FOREIGN_KEY;
391 my $fk_table = $c->reference_table or next;
393 for my $field_name ( $c->fields ) {
394 for my $fk_field ( $c->reference_fields ) {
395 next unless defined $schema->get_table( $fk_table );
396 push @fk_registry, [ $table_name, $fk_table ];
404 # Make the connections.
407 if ( $natural_join ) {
408 for my $field_name ( keys %nj_registry ) {
409 my @table_names = @{ $nj_registry{ $field_name } || [] } or next;
410 next if scalar @table_names == 1;
411 push @table_bunches, [ @table_names ];
415 @table_bunches = @fk_registry;
419 for my $bunch ( @table_bunches ) {
420 my @tables = @$bunch;
422 for my $i ( 0 .. $#tables ) {
423 my $table1 = $tables[ $i ];
424 for my $j ( 0 .. $#tables ) {
426 my $table2 = $tables[ $j ];
427 next if $done{ $table1 }{ $table2 };
428 $gv->add_edge( $table2, $table1 );
429 $done{ $table1 }{ $table2 } = 1;
430 $done{ $table2 }{ $table1 } = 1;
438 my $output_method = "as_$output_type";
440 open my $fh, ">$out_file" or die "Can't write '$out_file': $!\n";
442 print $fh $gv->$output_method;
446 return $gv->$output_method;
452 # -------------------------------------------------------------------
458 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>
462 SQL::Translator, GraphViz