3 # $Id: auto-dia.pl,v 1.4 2003-04-01 16:43:34 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 -s|--skip Fields to skip in natural joins
21 -f|--font-size "small," "medium," "large," or "huge" (default "medium")
22 --join-pk-only Perform natural joins from primary keys only
26 This script will create a picture of your schema. Only the database
27 driver argument (for SQL::Translator) is required. If no output file
28 name is given, then image will be printed to STDOUT, so you should
29 redirect the output into a file.
39 my $VERSION = (qw$Revision: 1.4 $)[-1];
41 use constant VALID_FONT_SIZE => {
51 my ( $out_file, $image_type, $db_driver, $title, $no_columns,
52 $no_lines, $skip_fields, $font_size, $join_pk_only );
54 'd|db=s' => \$db_driver,
55 'o|output:s' => \$out_file,
56 'i|image:s' => \$image_type,
57 't|title:s' => \$title,
58 'c|columns:i' => \$no_columns,
59 'n|no-lines' => \$no_lines,
60 's|skip:s' => \$skip_fields,
61 'f|font-size:s' => \$font_size,
62 '--join-pk-only' => \$join_pk_only,
64 my $file = shift @ARGV or pod2usage( -message => 'No input file' );
66 pod2usage( -message => "No db driver specified" ) unless $db_driver;
67 $image_type = $image_type ? lc $image_type : 'png';
69 $font_size = 'medium' unless VALID_FONT_SIZE->{ $font_size };
70 my %skip = map { $_, 1 } split ( /,/, $skip_fields );
75 my $t = SQL::Translator->new( parser => $db_driver, producer => 'Raw' );
76 my $data = $t->translate( $file ) or die $t->error;
82 $font_size eq 'small' ? gdTinyFont :
83 $font_size eq 'medium' ? gdSmallFont :
84 $font_size eq 'large' ? gdLargeFont : gdGiantFont;
85 my $no_tables = scalar keys %$data;
86 $no_columns ||= sprintf( "%.0f", sqrt( $no_tables ) + .5 );
87 my $no_per_col = sprintf( "%.0f", $no_tables/$no_columns + .5 );
90 my ( $max_x, $max_y ); # the furthest x and y used
91 my $orig_y = 40; # used to reset y for each column
92 my ( $x, $y ) = (20, $orig_y); # where to start
93 my $cur_col = 1; # the current column
94 my $no_this_col = 0; # number of tables in current column
95 my $this_col_x = $x; # current column's x
96 my $gutter = 30; # distance b/w columns
97 my %registry; # for locations of fields
98 my %table_x; # for max x of each table
99 my $field_no; # counter to give distinct no. to each field
103 sort { $a->[0] <=> $b->[0] }
104 map { [ $_->{'order'}, $_ ] }
107 my $table_name = $table->{'table_name'};
109 push @shapes, [ 'string', $font, $this_col_x, $y, $table_name, 'black' ];
111 $y += $font->height + 2;
112 my $below_table_name = $y;
114 my $this_max_x = $this_col_x + ($font->width * length($table_name));
118 sort { $a->[0] <=> $b->[0] }
119 map { [ $_->{'order'}, $_ ] }
120 values %{ $table->{'fields'} };
123 for my $index ( @{ $table->{'indices'} || [] } ) {
124 next unless $index->{'type'} eq 'primary_key';
125 my @fields = @{ $index->{'fields'} || [] } or next;
126 $pk{ $_ } = 1 for @fields;
129 my ( @fld_desc, $max_name );
130 for my $f ( @fields ) {
131 my $name = $f->{'name'} or next;
132 my $is_pk = $pk{ $name };
133 $name .= ' *' if $is_pk;
135 my $size = @{ $f->{'size'} || [] }
136 ? '(' . join( ',', @{ $f->{'size'} } ) . ')'
138 my $desc = join( ' ', map { $_ || () } $f->{'data_type'}, $size );
140 my $nlen = length $name;
141 $max_name = $nlen if $nlen > $max_name;
142 push @fld_desc, [ $name, $desc, $f->{'name'}, $is_pk ];
146 for my $fld_desc ( @fld_desc ) {
147 my ( $name, $desc, $orig_name, $is_pk ) = @$fld_desc;
148 my $diff = $max_name - length $name;
149 $name .= ' ' x $diff;
150 $desc = $name . $desc;
152 push @shapes, [ 'string', $font, $this_col_x, $y, $desc, 'black' ];
153 $y += $font->height + 2;
154 my $length = $this_col_x + ( $font->width * length( $desc ) );
155 $this_max_x = $length if $length > $this_max_x;
157 unless ( $skip{ $orig_name } ) {
158 my $y_link = $y - $font->height * .75;
159 push @{ $registry{ $orig_name } }, {
160 left => [ $this_col_x - 2, $y_link ],
161 right => [ $length, $y_link ],
162 table => $table_name,
163 field_no => ++$field_no,
170 $table_x{ $table_name } = $this_max_x + 5;
171 push @shapes, [ 'line', $this_col_x - 5, $below_table_name,
172 $this_max_x, $below_table_name, 'black' ];
174 'rectangle', $this_col_x - 5, $top - 5, $this_max_x, $y + 5, 'black'
176 $max_x = $this_max_x if $this_max_x > $max_x;
179 if ( ++$no_this_col == $no_per_col ) { # if we've filled up this column
180 $cur_col++; # up the column number
181 $no_this_col = 0; # reset the number of tables
182 $max_x += $gutter; # push the x over for next column
183 $this_col_x = $max_x; # remember the max x for this column
184 $max_y = $y if $y > $max_y; # note the max y
185 $y = $orig_y; # reset the y for next column
194 unless ( $no_lines ) {
195 for my $field_name ( keys %registry ) {
196 my @positions = @{ $registry{ $field_name } || [] } or next;
197 next if scalar @positions == 1;
199 for my $i ( 0 .. $#positions ) {
200 my $pos1 = $positions[ $i ];
201 my ( $ax, $ay ) = @{ $pos1->{'left'} };
202 my ( $bx, $by ) = @{ $pos1->{'right'} };
203 my $table1 = $pos1->{'table'};
204 my $fno1 = $pos1->{'field_no'};
205 my $is_pk = $pos1->{'is_pk'};
206 next if $join_pk_only and !$is_pk;
208 for my $j ( 0 .. $#positions ) {
209 my $pos2 = $positions[ $j ];
210 my ( $cx, $cy ) = @{ $pos2->{'left'} };
211 my ( $dx, $dy ) = @{ $pos2->{'right'} };
212 my $table2 = $pos2->{'table'};
213 my $fno2 = $pos2->{'field_no'};
214 next if $done{ $fno1 }{ $fno2 };
215 next if $fno1 == $fno2;
219 abs ( $ax - $cx ) + abs ( $ay - $cy ),
220 [ $ax, $ay, $cx, $cy ],
224 abs ( $ax - $dx ) + abs ( $ay - $dy ),
225 [ $ax, $ay, $dx, $dy ],
229 abs ( $bx - $cx ) + abs ( $by - $cy ),
230 [ $bx, $by, $cx, $cy ],
234 abs ( $bx - $dx ) + abs ( $by - $dy ),
235 [ $bx, $by, $dx, $dy ],
236 [ 'right', 'right' ],
238 @distances = sort { $a->[0] <=> $b->[0] } @distances;
239 my $shortest = $distances[0];
240 my ( $x1, $y1, $x2, $y2 ) = @{ $shortest->[1] };
241 my ( $side1, $side2 ) = @{ $shortest->[2] };
244 my $col1_right = $table_x{ $table1 };
245 my $col2_right = $table_x{ $table2 };
249 while ( $horz_taken{ $x1 + $diff } ) {
250 $diff = $side1 eq 'left' ? $diff - 2 : $diff + 2;
252 $horz_taken{ $x1 + $diff } = 1;
255 if ( $side1 eq 'left' ) {
256 $start = $x1 - $offset + $diff;
259 $start = $col1_right + $diff;
262 if ( $side2 eq 'left' ) {
263 $end = $x2 - $offset + $diff;
266 $end = $col2_right + $diff;
269 push @shapes, [ 'line', $x1, $y1, $start, $y1, 'lightblue' ];
270 push @shapes, [ 'line', $start, $y1, $end, $y2, 'lightblue' ];
271 push @shapes, [ 'line', $end, $y2, $x2, $y2, 'lightblue' ];
272 $done{ $fno1 }{ $fno2 } = 1;
273 $done{ $fno2 }{ $fno1 } = 1;
280 # Add the title and signature.
282 my $large_font = gdLargeFont;
283 my $title_len = $large_font->width * length $title;
285 'string', $large_font, $max_x/2 - $title_len/2, 10, $title, 'black'
288 my $sig = "auto-dia.pl $VERSION";
289 my $sig_len = $font->width * length $sig;
291 'string', $font, $max_x - $sig_len, $max_y - $font->height - 4,
298 my $gd = GD::Image->new( $max_x + 10, $max_y );
299 unless ( $gd->can( $image_type ) ) {
300 die "GD can't create images of type '$image_type'\n";
302 my %colors = map { $_->[0], $gd->colorAllocate( @{$_->[1]} ) } (
303 [ white => [ 255, 255, 255 ] ],
304 [ black => [ 0, 0, 0 ] ],
305 [ lightblue => [ 173, 216, 230 ] ],
307 $gd->interlaced( 'true' );
308 $gd->fill( 0, 0, $colors{ 'white' } );
309 for my $shape ( @shapes ) {
310 my $method = shift @$shape;
311 my $color = pop @$shape;
312 $gd->$method( @$shape, $colors{ $color } );
319 open my $fh, ">$out_file" or die "Can't write '$out_file': $!\n";
320 print $fh $gd->$image_type;
322 print "Image written to '$out_file'. Done.\n";
325 print $gd->$image_type;
332 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>