3 # $Id: auto-dia.pl,v 1.7 2003-04-02 01:45:45 kycl4rk Exp $
7 auto-dia.pl - Automatically create a diagram from a database schema
11 ./auto-dia.pl -d|--db=db_parser [options] schema.sql
15 -o|--output Output file name (default STDOUT)
16 -i|--image Output image type (default PNG)
17 -t|--title Title to give schema
18 -c|--cols Number of columns
19 -n|--no-lines Don't draw lines
20 -f|--font-size "small," "medium," "large," or "huge"
24 --natural-join Perform natural joins
25 --natural-join-pk-only Perform natural joins from primary keys only
26 -s|--skip Fields to skip in natural joins
30 This script will create a picture of your schema. Only the database
31 driver argument (for SQL::Translator) is required. If no output file
32 name is given, then image will be printed to STDOUT, so you should
33 redirect the output into a file.
35 The default action is to assume the presence of foreign key
36 relationships defined via "REFERNCES" or "FOREIGN KEY" constraints on
37 the tables. If you are parsing the schema of a file that does not
38 have these, you will find the natural join options helpful. With
39 natural joins, like-named fields will be considered foreign keys.
40 This can prove too permissive, however, as you probably don't want a
41 field called "name" to be considered a foreign key, so you could
42 include it in the "skip" option, and all fields called "name" will be
43 excluded from natural joins. A more efficient method, however, might
44 be to simply deduce the foriegn keys from primary keys to other fields
45 named the same in other tables. Use the "natural-join-pk-only" option
56 my $VERSION = (qw$Revision: 1.7 $)[-1];
58 use constant VALID_FONT_SIZE => {
69 $out_file, $image_type, $db_driver, $title, $no_columns,
70 $no_lines, $font_size, $add_color,
71 $natural_join, $join_pk_only, $skip_fields
75 'd|db=s' => \$db_driver,
76 'o|output:s' => \$out_file,
77 'i|image:s' => \$image_type,
78 't|title:s' => \$title,
79 'c|columns:i' => \$no_columns,
80 'n|no-lines' => \$no_lines,
81 'f|font-size:s' => \$font_size,
82 'color' => \$add_color,
83 'natural-join' => \$natural_join,
84 'natural-join-pk-only' => \$join_pk_only,
85 's|skip:s' => \$skip_fields,
87 my $file = shift @ARGV or pod2usage( -message => 'No input file' );
89 pod2usage( -message => "No db driver specified" ) unless $db_driver;
90 $image_type = $image_type ? lc $image_type : 'png';
92 $font_size = 'medium' unless VALID_FONT_SIZE->{ $font_size };
93 my %skip = map { $_, 1 } split ( /,/, $skip_fields );
94 $natural_join ||= $join_pk_only;
99 my $t = SQL::Translator->new( parser => $db_driver, producer => 'Raw' );
100 my $data = $t->translate( $file ) or die $t->error;
106 $font_size eq 'small' ? gdTinyFont :
107 $font_size eq 'medium' ? gdSmallFont :
108 $font_size eq 'large' ? gdLargeFont : gdGiantFont;
109 my $no_tables = scalar keys %$data;
110 $no_columns ||= sprintf( "%.0f", sqrt( $no_tables ) + .5 );
111 my $no_per_col = sprintf( "%.0f", $no_tables/$no_columns + .5 );
114 my ( $max_x, $max_y ); # the furthest x and y used
115 my $orig_y = 40; # used to reset y for each column
116 my ( $x, $y ) = (20, $orig_y); # where to start
117 my $cur_col = 1; # the current column
118 my $no_this_col = 0; # number of tables in current column
119 my $this_col_x = $x; # current column's x
120 my $gutter = 30; # distance b/w columns
121 my %nj_registry; # for locations of fields for natural joins
122 my @fk_registry; # for locations of fields for foreign keys
123 my %table_x; # for max x of each table
124 my $field_no; # counter to give distinct no. to each field
129 sort { $a->[0] <=> $b->[0] }
130 map { [ $_->{'order'}, $_ ] }
133 my $table_name = $table->{'table_name'};
135 push @shapes, [ 'string', $font, $this_col_x, $y, $table_name, 'black' ];
137 $y += $font->height + 2;
138 my $below_table_name = $y;
140 my $this_max_x = $this_col_x + ($font->width * length($table_name));
144 sort { $a->[0] <=> $b->[0] }
145 map { [ $_->{'order'}, $_ ] }
146 values %{ $table->{'fields'} };
149 for my $index ( @{ $table->{'indices'} || [] } ) {
150 my @fields = @{ $index->{'fields'} || [] } or next;
151 if ( $index->{'type'} eq 'primary_key' ) {
152 $pk{ $_ } = 1 for @fields;
154 elsif ( $index->{'type'} eq 'unique' ) {
155 $unique{ $_ } = 1 for @fields;
159 my ( @fld_desc, $max_name );
160 for my $f ( @fields ) {
161 my $name = $f->{'name'} or next;
162 my $is_pk = $pk{ $name };
163 my $is_unique = $unique{ $name };
166 $legend{'Primary key'} = '*';
168 elsif ( $is_unique ) {
170 $legend{'Unique constraint'} = '[U]';
173 my $size = @{ $f->{'size'} || [] }
174 ? '(' . join( ',', @{ $f->{'size'} } ) . ')'
176 my $desc = join( ' ', map { $_ || () } $f->{'data_type'}, $size );
178 my $nlen = length $name;
179 $max_name = $nlen if $nlen > $max_name;
180 push @fld_desc, [ $name, $desc, $f->{'name'}, $is_pk ];
184 for my $fld_desc ( @fld_desc ) {
185 my ( $name, $desc, $orig_name, $is_pk ) = @$fld_desc;
186 my $diff = $max_name - length $name;
187 $name .= ' ' x $diff;
188 $desc = $name . $desc;
190 push @shapes, [ 'string', $font, $this_col_x, $y, $desc, 'black' ];
191 $y += $font->height + 2;
192 my $length = $this_col_x + ( $font->width * length( $desc ) );
193 $this_max_x = $length if $length > $this_max_x;
195 my $constraints = $table->{'fields'}{ $orig_name }{'constraints'};
197 if ( $natural_join && !$skip{ $orig_name } ) {
198 push @{ $nj_registry{ $orig_name } }, $table_name;
200 elsif ( @{ $constraints || [] } ) {
201 for my $constraint ( @$constraints ) {
202 next unless $constraint->{'type'} eq 'foreign_key';
204 @{ $constraint->{'reference_fields'} || [] }
207 [ $constraint->{'reference_table'}, $fk_field ],
208 [ $table_name, $orig_name ],
214 my $y_link = $y - $font->height * .75;
215 $table->{'fields'}{ $orig_name }{'coords'} = {
216 left => [ $this_col_x - 2, $y_link ],
217 right => [ $length, $y_link ],
218 table => $table_name,
219 field_no => ++$field_no,
225 $table_x{ $table_name } = $this_max_x + 5;
226 push @shapes, [ 'line', $this_col_x - 5, $below_table_name,
227 $this_max_x, $below_table_name, 'black' ];
228 my @bounds = ( $this_col_x - 5, $top - 5, $this_max_x, $y + 5 );
232 $bounds[0], $bounds[1],
233 $this_max_x, $below_table_name,
236 unshift @shapes, [ 'filledRectangle', @bounds, 'white' ];
238 push @shapes, [ 'rectangle', @bounds, 'black' ];
239 $max_x = $this_max_x if $this_max_x > $max_x;
242 if ( ++$no_this_col == $no_per_col ) { # if we've filled up this column
243 $cur_col++; # up the column number
244 $no_this_col = 0; # reset the number of tables
245 $max_x += $gutter; # push the x over for next column
246 $this_col_x = $max_x; # remember the max x for this column
247 $max_y = $y if $y > $max_y; # note the max y
248 $y = $orig_y; # reset the y for next column
257 unless ( $no_lines ) {
258 my @position_bunches;
260 if ( $natural_join ) {
261 for my $field_name ( keys %nj_registry ) {
263 my @table_names = @{ $nj_registry{ $field_name } || [] } or next;
264 next if scalar @table_names == 1;
266 for my $table_name ( @table_names ) {
268 $data->{ $table_name }{'fields'}{ $field_name }{'coords'};
271 push @position_bunches, [ @positions ];
275 for my $pair ( @fk_registry ) {
277 $data->{ $pair->[0][0] }{'fields'}{ $pair->[0][1] }{'coords'};
279 $data->{ $pair->[1][0] }{'fields'}{ $pair->[1][1] }{'coords'};
280 next unless %{ $c1 || {} } && %{ $c1 || {} };
281 push @position_bunches, [ $c1, $c2 ];
285 for my $bunch ( @position_bunches ) {
286 my @positions = @$bunch;
288 for my $i ( 0 .. $#positions ) {
289 my $pos1 = $positions[ $i ];
290 my ( $ax, $ay ) = @{ $pos1->{'left'} };
291 my ( $bx, $by ) = @{ $pos1->{'right'} };
292 my $table1 = $pos1->{'table'};
293 my $fno1 = $pos1->{'field_no'};
294 my $is_pk = $pos1->{'is_pk'};
295 next if $join_pk_only and !$is_pk;
297 for my $j ( 0 .. $#positions ) {
298 my $pos2 = $positions[ $j ];
299 my ( $cx, $cy ) = @{ $pos2->{'left'} };
300 my ( $dx, $dy ) = @{ $pos2->{'right'} };
301 my $table2 = $pos2->{'table'};
302 my $fno2 = $pos2->{'field_no'};
303 next if $done{ $fno1 }{ $fno2 };
304 next if $fno1 == $fno2;
308 abs ( $ax - $cx ) + abs ( $ay - $cy ),
309 [ $ax, $ay, $cx, $cy ],
313 abs ( $ax - $dx ) + abs ( $ay - $dy ),
314 [ $ax, $ay, $dx, $dy ],
318 abs ( $bx - $cx ) + abs ( $by - $cy ),
319 [ $bx, $by, $cx, $cy ],
323 abs ( $bx - $dx ) + abs ( $by - $dy ),
324 [ $bx, $by, $dx, $dy ],
325 [ 'right', 'right' ],
327 @distances = sort { $a->[0] <=> $b->[0] } @distances;
328 my $shortest = $distances[0];
329 my ( $x1, $y1, $x2, $y2 ) = @{ $shortest->[1] };
330 my ( $side1, $side2 ) = @{ $shortest->[2] };
333 my $col1_right = $table_x{ $table1 };
334 my $col2_right = $table_x{ $table2 };
338 while ( $horz_taken{ $x1 + $diff } ) {
339 $diff = $side1 eq 'left' ? $diff - 2 : $diff + 2;
341 $horz_taken{ $x1 + $diff } = 1;
344 if ( $side1 eq 'left' ) {
345 $start = $x1 - $offset + $diff;
348 $start = $col1_right + $diff;
351 if ( $side2 eq 'left' ) {
352 $end = $x2 - $offset + $diff;
355 $end = $col2_right + $diff;
358 push @shapes, [ 'line', $x1, $y1, $start, $y1, 'lightblue' ];
359 push @shapes, [ 'line', $start, $y1, $end, $y2, 'lightblue' ];
360 push @shapes, [ 'line', $end, $y2, $x2, $y2, 'lightblue' ];
361 $done{ $fno1 }{ $fno2 } = 1;
362 $done{ $fno2 }{ $fno1 } = 1;
369 # Add the title, legend and signature.
371 my $large_font = gdLargeFont;
372 my $title_len = $large_font->width * length $title;
374 'string', $large_font, $max_x/2 - $title_len/2, 10, $title, 'black'
380 'string', $font, $x, $max_y - $font->height - 4, 'Legend', 'black'
382 $max_y += $font->height + 4;
385 for my $len ( map { length $_ } values %legend ) {
386 $longest = $len if $len > $longest;
390 while ( my ( $key, $shape ) = each %legend ) {
391 my $space = $longest - length $shape;
393 'string', $font, $x, $max_y - $font->height - 4,
394 join( '', $shape, ' ' x $space, $key ), 'black'
397 $max_y += $font->height + 4;
401 my $sig = "auto-dia.pl $VERSION";
402 my $sig_len = $font->width * length $sig;
404 'string', $font, $max_x - $sig_len, $max_y - $font->height - 4,
411 my $gd = GD::Image->new( $max_x + 10, $max_y );
412 unless ( $gd->can( $image_type ) ) {
413 die "GD can't create images of type '$image_type'\n";
415 my %colors = map { $_->[0], $gd->colorAllocate( @{$_->[1]} ) } (
416 [ white => [ 255, 255, 255 ] ],
417 [ beige => [ 245, 245, 220 ] ],
418 [ black => [ 0, 0, 0 ] ],
419 [ lightblue => [ 173, 216, 230 ] ],
420 [ lightgoldenrodyellow => [ 250, 250, 210 ] ],
421 [ khaki => [ 240, 230, 140 ] ],
423 $gd->interlaced( 'true' );
424 my $background_color = $add_color ? 'lightgoldenrodyellow' : 'white';
425 $gd->fill( 0, 0, $colors{ $background_color } );
426 for my $shape ( @shapes ) {
427 my $method = shift @$shape;
428 my $color = pop @$shape;
429 $gd->$method( @$shape, $colors{ $color } );
436 open my $fh, ">$out_file" or die "Can't write '$out_file': $!\n";
437 print $fh $gd->$image_type;
439 print "Image written to '$out_file'. Done.\n";
442 print $gd->$image_type;
449 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>