1 package SQL::Translator::Producer::GraphViz;
3 # -------------------------------------------------------------------
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002-2009 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 resulting GraphViz output will be
60 written. Alternatively an open filehandle can be supplied. If
61 undefined (the default) - the result is returned as a string.
63 =item * layout (DEFAULT: 'dot')
65 determines which layout algorithm GraphViz will use; possible
66 values are 'dot' (the default GraphViz layout for directed graph
67 layouts), 'neato' (for undirected graph layouts - spring model)
68 or 'twopi' (for undirected graph layouts - circular)
70 =item * node_shape (DEFAULT: 'record')
72 sets the node shape of each table in the graph; this can be
73 one of 'record', 'plaintext', 'ellipse', 'circle', 'egg',
74 'triangle', 'box', 'diamond', 'trapezium', 'parallelogram',
75 'house', 'hexagon', or 'octagon'
77 =item * output_type (DEFAULT: 'png')
79 sets the file type of the output graphic; possible values are
80 'ps', 'hpgl', 'pcl', 'mif', 'pic', 'gd', 'gd2', 'gif', 'jpeg',
81 'png', 'wbmp', 'cmap', 'ismap', 'imap', 'vrml', 'vtx', 'mp',
82 'fig', 'svg', 'canon', 'plain' or 'text' (see GraphViz for
83 details on each of these)
85 =item * width (DEFAULT: 8.5)
87 width (in inches) of the output graphic
89 =item * height (DEFAULT: 11)
91 height (in inches) of the output grahic
95 custom font size for node and edge labels (note that arbitrarily large
96 sizes may be ignored due to page size or graph size constraints)
100 custom font name (or full path to font file) for node, edge, and graph
105 reference to a hash of node attribute names and their values; these
106 may override general fontname or fontsize parameter
110 reference to a hash of edge attribute names and their values; these
111 may override general fontname or fontsize parameter
115 reference to a hash of graph attribute names and their values; these
116 may override the general fontname parameter
118 =item * show_fields (DEFAULT: true)
120 if set to a true value, the names of the colums in a table will
121 be displayed in each table's node
125 if set to a true value, only columns which are foreign keys
126 will be displayed in each table's node
128 =item * show_datatypes
130 if set to a true value, the datatype of each column will be
131 displayed next to each column's name; this option will have no
132 effect if the value of show_fields is set to false
136 if set to a true value, the size (in bytes) of each CHAR and
137 VARCHAR column will be displayed in parentheses next to the
138 column's name; this option will have no effect if the value of
139 show_fields is set to false
141 =item * show_constraints
143 if set to a true value, a field's constraints (i.e., its
144 primary-key-ness, its foreign-key-ness and/or its uniqueness)
145 will appear as a comma-separated list in brackets next to the
146 field's name; this option will have no effect if the value of
147 show_fields is set to false
151 if set to a true value, the graphic will have a background
152 color of 'lightgoldenrodyellow'; otherwise the background
157 if set to a true value, the make_natural_join method of
158 SQL::Translator::Schema will be called before generating the
159 graph; a true value for join_pk_only (see below) implies a
160 true value for this option
164 the value of this option will be passed as the value of the
165 like-named argument in the make_natural_join method (see
166 natural_join above) of SQL::Translator::Schema, if either the
167 value of this option or the natural_join option is set to true
171 the value of this option will be passed as the value of the
172 like-named argument in the make_natural_join method (see
173 natural_join above) of SQL::Translator::Schema, if either
174 the natural_join or join_pk_only options has a true value
178 if set to a true value, each record will also show the indexes
179 set on each table. it describes the index types along with
180 which columns are included in the index. this option requires
181 that show_fields is a true value as well
183 =item * show_index_names
185 if show_indexes is set to a true value, then the value of this
186 parameter determines whether or not to print names of indexes.
187 if show_index_names is false, then a list of indexed columns
188 will appear below the field list. otherwise, it will be a list
189 prefixed with the name of each index. it defaults to true.
191 =item * friendly_ints
193 if set to a true value, each integer type field will be displayed
194 as a smallint, integer or bigint depending on the field's
195 associated size parameter. this only applies for the 'integer'
196 type (and not the lowercase 'int' type, which is assumed to be a
199 =item * friendly_ints_extended
201 if set to a true value, the friendly ints displayed will take into
202 account the non-standard types, 'tinyint' and 'mediumint' (which,
203 as far as I am aware, is only implemented in MySQL)
211 use SQL::Translator::Schema::Constants;
212 use SQL::Translator::Utils qw(debug);
213 use Scalar::Util qw/openhandle/;
215 use vars qw[ $VERSION $DEBUG ];
217 $DEBUG = 0 unless defined $DEBUG;
219 use constant VALID_LAYOUT => {
225 use constant VALID_NODE_SHAPE => {
243 my $schema = $t->schema;
244 my $args = $t->producer_args;
245 local $DEBUG = $t->debug;
247 my $out_file = $args->{'out_file'} || '';
248 my $layout = $args->{'layout'} || 'dot';
249 my $node_shape = $args->{'node_shape'} || 'record';
250 my $output_type = $args->{'output_type'} || 'png';
251 my $width = defined $args->{'width'}
252 ? $args->{'width'} : 8.5;
253 my $height = defined $args->{'height'}
254 ? $args->{'height'} : 11;
255 my $fontsize = $args->{'fontsize'};
256 my $fontname = $args->{'fontname'};
257 my $edgeattrs = $args->{'edgeattrs'} || {};
258 my $graphattrs = $args->{'graphattrs'} || {};
259 my $nodeattrs = $args->{'nodeattrs'} || {};
260 my $show_fields = defined $args->{'show_fields'}
261 ? $args->{'show_fields'} : 1;
262 my $add_color = $args->{'add_color'};
263 my $natural_join = $args->{'natural_join'};
264 my $show_fk_only = $args->{'show_fk_only'};
265 my $show_datatypes = $args->{'show_datatypes'};
266 my $show_sizes = $args->{'show_sizes'};
267 my $show_indexes = $args->{'show_indexes'};
268 my $show_index_names = defined $args->{'show_index_names'} ? $args->{'show_index_names'} : 1;
269 my $friendly_ints = $args->{'friendly_ints'};
270 my $friendly_ints_ex = $args->{'friendly_ints_extended'};
271 my $show_constraints = $args->{'show_constraints'};
272 my $join_pk_only = $args->{'join_pk_only'};
273 my $skip_fields = $args->{'skip_fields'} || '';
274 my %skip = map { s/^\s+|\s+$//g; length $_ ? ($_, 1) : () }
275 split ( /,/, $skip_fields );
276 $natural_join ||= $join_pk_only;
278 $schema->make_natural_joins(
279 join_pk_only => $join_pk_only,
280 skip_fields => $args->{'skip_fields'},
283 die "Invalid layout '$layout'" unless VALID_LAYOUT->{ $layout };
284 die "Invalid node shape'$node_shape'"
285 unless VALID_NODE_SHAPE->{ $node_shape };
287 for ( $height, $width ) {
288 $_ = 0 unless $_ =~ /^\d+(.\d)?$/;
293 directed => $natural_join ? 0 : 1,
296 bgcolor => $add_color ? 'lightgoldenrodyellow' : 'white',
298 shape => $node_shape,
300 fillcolor => 'white',
303 $args{'width'} = $width if $width;
304 $args{'height'} = $height if $height;
305 # set fontsize for edge and node labels if specified
307 $args{'node'}->{'fontsize'} = $fontsize;
308 $args{'edge'} = {} unless $args{'edge'};
309 $args{'edge'}->{'fontsize'} = $fontsize;
311 # set the font name globally for node, edge, and graph labels if
312 # specified (use node, edge, or graph attributes for individual
313 # font specification)
315 $args{'node'}->{'fontname'} = $fontname;
316 $args{'edge'} = {} unless $args{'edge'};
317 $args{'edge'}->{'fontname'} = $fontname;
318 $args{'graph'} = {} unless $args{'graph'};
319 $args{'graph'}->{'fontname'} = $fontname;
321 # set additional node, edge, and graph attributes; these may
322 # possibly override ones set before
323 while (my ($key,$val) = each %$nodeattrs) {
324 $args{'node'}->{$key} = $val;
326 $args{'edge'} = {} if %$edgeattrs && !$args{'edge'};
327 while (my ($key,$val) = each %$edgeattrs) {
328 $args{'edge'}->{$key} = $val;
330 $args{'graph'} = {} if %$edgeattrs && !$args{'graph'};
331 while (my ($key,$val) = each %$graphattrs) {
332 $args{'graph'}->{$key} = $val;
336 # Create a blank GraphViz object and see if we can produce the output type.
338 my $gv = GraphViz->new( %args ) or die "Can't create GraphViz object\n";
339 my $output_method = "as_$output_type";
341 # the generators are AUTOLOADed so can't use ->can ($output_method)
342 eval { $gv->$output_method };
343 die "Invalid output type: '$output_type'" if $@;
346 my %nj_registry; # for locations of fields for natural joins
347 my @fk_registry; # for locations of fields for foreign keys
349 for my $table ( $schema->get_tables ) {
350 my @fields = $table->get_fields;
351 if ( $show_fk_only ) {
352 @fields = grep { $_->is_foreign_key } @fields;
359 foreach my $field (@fields) {
362 if ($show_datatypes) {
364 $field_type = $field->data_type;
366 # For the integer type, transform into different types based on
367 # requested size, if a size is given.
368 if ($field->size and $friendly_ints and (lc $field_type) eq 'integer') {
369 # Automatically translate to int2, int4, int8
370 # Type (Bits) Max. Signed/Unsigned Length
373 # smallint (16) 32767 5
375 # mediumint* (24) 8388607 7
377 # int (32) 2147483647 10
379 # bigint (64) 9223372036854775807 19
380 # 18446744073709551615 20
382 # * tinyint and mediumint are nonstandard extensions which are
383 # only available under MySQL (to my knowledge)
384 my $size = $field->size;
385 if ($size <= 3 and $friendly_ints_ex) {
386 $field_type = 'tinyint',
389 $field_type = 'smallint';
391 elsif ($size <= 8 and $friendly_ints_ex) {
392 $field_type = 'mediumint';
394 elsif ($size <= 11) {
395 $field_type = 'integer';
398 $field_type = 'bigint';
407 ($field_type =~ /^(var)?char2?$/ or $field_type eq 'numeric' or $field_type eq 'decimal')
409 $field_type .= '(' . $field->size . ')';
414 if ($show_constraints) {
416 push(@constraints, 'PK') if $field->is_primary_key;
417 push(@constraints, 'FK') if $field->is_foreign_key;
418 push(@constraints, 'U') if $field->is_unique;
420 $constraints = join (',', @constraints);
423 # construct the field line from all info gathered so far
424 push @fmt_fields, join (' ',
428 $constraints ? "[$constraints]" : (),
433 # join field lines with graphviz formatting
434 $field_str = join ('\l', @fmt_fields) . '\l';
441 foreach my $index ($table->get_indices) {
442 next unless $index->is_valid;
444 push @fmt_indexes, join (' ',
446 $show_index_names ? $index->name . ':' : (),
447 join (', ', $index->fields),
448 ($index->type eq 'UNIQUE') ? '[U]' : (),
452 # join index lines with graphviz formatting (if any indexes at all)
453 $index_str = join ('\l', @fmt_indexes) . '\l' if @fmt_indexes;
456 my $table_name = $table->name;
457 my $name_str = $table_name . '\n';
460 for ($name_str, $field_str, $index_str) {
465 # only the 'record' type supports nice formatting
466 if ($node_shape eq 'record') {
468 # the necessity to supply shape => 'record' is a graphviz bug
469 $gv->add_node( $table_name,
471 label => sprintf ('{%s}',
481 my $sep = sprintf ('%s\n',
482 '-' x ( (length $table_name) + 2)
485 $gv->add_node( $table_name,
495 debug("Processing table '$table_name'");
497 debug("Fields = ", join(', ', map { $_->name } @fields));
499 for my $f ( @fields ) {
500 my $name = $f->name or next;
501 my $is_pk = $f->is_primary_key;
502 my $is_unique = $f->is_unique;
505 # Decide if we should skip this field.
507 if ( $natural_join ) {
508 next unless $is_pk || $f->is_foreign_key;
511 my $constraints = $f->{'constraints'};
513 if ( $natural_join && !$skip{ $name } ) {
514 push @{ $nj_registry{ $name } }, $table_name;
518 unless ( $natural_join ) {
519 for my $c ( $table->get_constraints ) {
520 next unless $c->type eq FOREIGN_KEY;
521 my $fk_table = $c->reference_table or next;
523 for my $field_name ( $c->fields ) {
524 for my $fk_field ( $c->reference_fields ) {
525 next unless defined $schema->get_table( $fk_table );
526 push @fk_registry, [ $table_name, $fk_table ];
534 # Make the connections.
537 if ( $natural_join ) {
538 for my $field_name ( keys %nj_registry ) {
539 my @table_names = @{ $nj_registry{ $field_name } || [] } or next;
540 next if scalar @table_names == 1;
541 push @table_bunches, [ @table_names ];
545 @table_bunches = @fk_registry;
549 for my $bunch ( @table_bunches ) {
550 my @tables = @$bunch;
552 for my $i ( 0 .. $#tables ) {
553 my $table1 = $tables[ $i ];
554 for my $j ( 0 .. $#tables ) {
556 my $table2 = $tables[ $j ];
557 next if $done{ $table1 }{ $table2 };
558 $gv->add_edge( $table2, $table1 );
559 $done{ $table1 }{ $table2 } = 1;
560 $done{ $table2 }{ $table1 } = 1;
569 if (openhandle ($out_file)) {
570 print $out_file $gv->$output_method;
573 open my $fh, ">$out_file" or die "Can't write '$out_file': $!\n";
575 print $fh $gv->$output_method;
580 return $gv->$output_method;
586 # -------------------------------------------------------------------
592 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>
596 Jonathan Yu E<lt>frequency@cpan.orgE<gt>
600 SQL::Translator, GraphViz