Commit | Line | Data |
dd9550a4 |
1 | #!/usr/bin/perl |
2 | |
d21faa48 |
3 | # $Id: auto-dia.pl,v 1.6 2003-04-01 18:08:02 kycl4rk Exp $ |
dd9550a4 |
4 | |
5 | =head1 NAME |
6 | |
7 | auto-dia.pl - Automatically create a diagram from a database schema |
8 | |
9 | =head1 SYNOPSIS |
10 | |
11 | ./auto-dia.pl -d|--db=db_parser [options] schema.sql |
12 | |
13 | Options: |
14 | |
7256996b |
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 |
22cb6863 |
21 | -f|--font-size "small," "medium," "large," or "huge" (default "medium") |
d21faa48 |
22 | --color Add colors |
7256996b |
23 | --join-pk-only Perform natural joins from primary keys only |
dd9550a4 |
24 | |
25 | =head1 DESCRIPTION |
26 | |
27 | This script will create a picture of your schema. Only the database |
aa0a19b9 |
28 | driver argument (for SQL::Translator) is required. If no output file |
29 | name is given, then image will be printed to STDOUT, so you should |
30 | redirect the output into a file. |
dd9550a4 |
31 | |
32 | =cut |
33 | |
34 | use strict; |
35 | use Getopt::Long; |
36 | use GD; |
37 | use Pod::Usage; |
38 | use SQL::Translator; |
39 | |
d21faa48 |
40 | my $VERSION = (qw$Revision: 1.6 $)[-1]; |
22cb6863 |
41 | |
42 | use constant VALID_FONT_SIZE => { |
43 | small => 1, |
44 | medium => 1, |
45 | large => 1, |
46 | huge => 1, |
47 | }; |
dd9550a4 |
48 | |
aa0a19b9 |
49 | # |
50 | # Get arguments. |
51 | # |
52 | my ( $out_file, $image_type, $db_driver, $title, $no_columns, |
d21faa48 |
53 | $no_lines, $skip_fields, $font_size, $add_color, $join_pk_only ); |
dd9550a4 |
54 | GetOptions( |
d21faa48 |
55 | 'd|db=s' => \$db_driver, |
56 | 'o|output:s' => \$out_file, |
57 | 'i|image:s' => \$image_type, |
58 | 't|title:s' => \$title, |
59 | 'c|columns:i' => \$no_columns, |
60 | 'n|no-lines' => \$no_lines, |
61 | 's|skip:s' => \$skip_fields, |
62 | 'f|font-size:s' => \$font_size, |
63 | 'color' => \$add_color, |
64 | 'join-pk-only' => \$join_pk_only, |
dd9550a4 |
65 | ) or die pod2usage; |
66 | my $file = shift @ARGV or pod2usage( -message => 'No input file' ); |
67 | |
68 | pod2usage( -message => "No db driver specified" ) unless $db_driver; |
22cb6863 |
69 | $image_type = $image_type ? lc $image_type : 'png'; |
70 | $title ||= $file; |
71 | $font_size = 'medium' unless VALID_FONT_SIZE->{ $font_size }; |
72 | my %skip = map { $_, 1 } split ( /,/, $skip_fields ); |
dd9550a4 |
73 | |
aa0a19b9 |
74 | # |
75 | # Parse file. |
76 | # |
dd9550a4 |
77 | my $t = SQL::Translator->new( parser => $db_driver, producer => 'Raw' ); |
78 | my $data = $t->translate( $file ) or die $t->error; |
dd9550a4 |
79 | |
aa0a19b9 |
80 | # |
81 | # Layout the image. |
82 | # |
22cb6863 |
83 | my $font = |
84 | $font_size eq 'small' ? gdTinyFont : |
85 | $font_size eq 'medium' ? gdSmallFont : |
86 | $font_size eq 'large' ? gdLargeFont : gdGiantFont; |
dd9550a4 |
87 | my $no_tables = scalar keys %$data; |
88 | $no_columns ||= sprintf( "%.0f", sqrt( $no_tables ) + .5 ); |
89 | my $no_per_col = sprintf( "%.0f", $no_tables/$no_columns + .5 ); |
dd9550a4 |
90 | |
7256996b |
91 | my @shapes; |
92 | my ( $max_x, $max_y ); # the furthest x and y used |
93 | my $orig_y = 40; # used to reset y for each column |
94 | my ( $x, $y ) = (20, $orig_y); # where to start |
95 | my $cur_col = 1; # the current column |
96 | my $no_this_col = 0; # number of tables in current column |
97 | my $this_col_x = $x; # current column's x |
98 | my $gutter = 30; # distance b/w columns |
99 | my %registry; # for locations of fields |
100 | my %table_x; # for max x of each table |
101 | my $field_no; # counter to give distinct no. to each field |
b18a0413 |
102 | my %legend; |
dd9550a4 |
103 | |
104 | for my $table ( |
105 | map { $_->[1] } |
106 | sort { $a->[0] <=> $b->[0] } |
107 | map { [ $_->{'order'}, $_ ] } |
108 | values %$data |
109 | ) { |
110 | my $table_name = $table->{'table_name'}; |
111 | my $top = $y; |
aa0a19b9 |
112 | push @shapes, [ 'string', $font, $this_col_x, $y, $table_name, 'black' ]; |
dd9550a4 |
113 | |
114 | $y += $font->height + 2; |
115 | my $below_table_name = $y; |
116 | $y += 2; |
117 | my $this_max_x = $this_col_x + ($font->width * length($table_name)); |
118 | |
119 | my @fields = |
120 | map { $_->[1] } |
121 | sort { $a->[0] <=> $b->[0] } |
122 | map { [ $_->{'order'}, $_ ] } |
123 | values %{ $table->{'fields'} }; |
124 | |
b18a0413 |
125 | my ( %pk, %unique ); |
dd9550a4 |
126 | for my $index ( @{ $table->{'indices'} || [] } ) { |
dd9550a4 |
127 | my @fields = @{ $index->{'fields'} || [] } or next; |
b18a0413 |
128 | if ( $index->{'type'} eq 'primary_key' ) { |
129 | $pk{ $_ } = 1 for @fields; |
130 | } |
131 | elsif ( $index->{'type'} eq 'unique' ) { |
132 | $unique{ $_ } = 1 for @fields; |
133 | } |
dd9550a4 |
134 | } |
135 | |
136 | my ( @fld_desc, $max_name ); |
137 | for my $f ( @fields ) { |
b18a0413 |
138 | my $name = $f->{'name'} or next; |
139 | my $is_pk = $pk{ $name }; |
140 | my $is_unique = $unique{ $name }; |
141 | if ( $is_pk ) { |
142 | $name .= ' *'; |
143 | $legend{'Primary key'} = '*'; |
144 | } |
145 | elsif ( $is_unique ) { |
146 | $name .= ' [U]'; |
147 | $legend{'Unique constraint'} = '[U]'; |
148 | } |
dd9550a4 |
149 | |
150 | my $size = @{ $f->{'size'} || [] } |
151 | ? '(' . join( ',', @{ $f->{'size'} } ) . ')' |
152 | : ''; |
153 | my $desc = join( ' ', map { $_ || () } $f->{'data_type'}, $size ); |
154 | |
155 | my $nlen = length $name; |
156 | $max_name = $nlen if $nlen > $max_name; |
7256996b |
157 | push @fld_desc, [ $name, $desc, $f->{'name'}, $is_pk ]; |
dd9550a4 |
158 | } |
159 | |
160 | $max_name += 4; |
161 | for my $fld_desc ( @fld_desc ) { |
7256996b |
162 | my ( $name, $desc, $orig_name, $is_pk ) = @$fld_desc; |
dd9550a4 |
163 | my $diff = $max_name - length $name; |
164 | $name .= ' ' x $diff; |
165 | $desc = $name . $desc; |
166 | |
aa0a19b9 |
167 | push @shapes, [ 'string', $font, $this_col_x, $y, $desc, 'black' ]; |
dd9550a4 |
168 | $y += $font->height + 2; |
169 | my $length = $this_col_x + ( $font->width * length( $desc ) ); |
170 | $this_max_x = $length if $length > $this_max_x; |
aa0a19b9 |
171 | |
172 | unless ( $skip{ $orig_name } ) { |
173 | my $y_link = $y - $font->height * .75; |
174 | push @{ $registry{ $orig_name } }, { |
175 | left => [ $this_col_x - 2, $y_link ], |
176 | right => [ $length, $y_link ], |
177 | table => $table_name, |
178 | field_no => ++$field_no, |
7256996b |
179 | is_pk => $is_pk, |
aa0a19b9 |
180 | }; |
181 | } |
dd9550a4 |
182 | } |
183 | |
184 | $this_max_x += 5; |
aa0a19b9 |
185 | $table_x{ $table_name } = $this_max_x + 5; |
dd9550a4 |
186 | push @shapes, [ 'line', $this_col_x - 5, $below_table_name, |
aa0a19b9 |
187 | $this_max_x, $below_table_name, 'black' ]; |
d21faa48 |
188 | my @bounds = ( $this_col_x - 5, $top - 5, $this_max_x, $y + 5 ); |
189 | if ( $add_color ) { |
190 | unshift @shapes, [ |
191 | 'filledRectangle', |
192 | $bounds[0], $bounds[1], |
193 | $this_max_x, $below_table_name, |
194 | 'khaki' |
195 | ]; |
196 | unshift @shapes, [ 'filledRectangle', @bounds, 'white' ]; |
197 | } |
198 | push @shapes, [ 'rectangle', @bounds, 'black' ]; |
dd9550a4 |
199 | $max_x = $this_max_x if $this_max_x > $max_x; |
200 | $y += 25; |
201 | |
7256996b |
202 | if ( ++$no_this_col == $no_per_col ) { # if we've filled up this column |
203 | $cur_col++; # up the column number |
204 | $no_this_col = 0; # reset the number of tables |
205 | $max_x += $gutter; # push the x over for next column |
206 | $this_col_x = $max_x; # remember the max x for this column |
207 | $max_y = $y if $y > $max_y; # note the max y |
208 | $y = $orig_y; # reset the y for next column |
dd9550a4 |
209 | } |
210 | } |
211 | |
212 | # |
aa0a19b9 |
213 | # Connect the lines. |
214 | # |
215 | my %horz_taken; |
216 | my %done; |
217 | unless ( $no_lines ) { |
218 | for my $field_name ( keys %registry ) { |
219 | my @positions = @{ $registry{ $field_name } || [] } or next; |
220 | next if scalar @positions == 1; |
221 | |
222 | for my $i ( 0 .. $#positions ) { |
223 | my $pos1 = $positions[ $i ]; |
224 | my ( $ax, $ay ) = @{ $pos1->{'left'} }; |
225 | my ( $bx, $by ) = @{ $pos1->{'right'} }; |
226 | my $table1 = $pos1->{'table'}; |
227 | my $fno1 = $pos1->{'field_no'}; |
7256996b |
228 | my $is_pk = $pos1->{'is_pk'}; |
229 | next if $join_pk_only and !$is_pk; |
aa0a19b9 |
230 | |
7256996b |
231 | for my $j ( 0 .. $#positions ) { |
aa0a19b9 |
232 | my $pos2 = $positions[ $j ]; |
233 | my ( $cx, $cy ) = @{ $pos2->{'left'} }; |
234 | my ( $dx, $dy ) = @{ $pos2->{'right'} }; |
235 | my $table2 = $pos2->{'table'}; |
236 | my $fno2 = $pos2->{'field_no'}; |
237 | next if $done{ $fno1 }{ $fno2 }; |
7256996b |
238 | next if $fno1 == $fno2; |
aa0a19b9 |
239 | |
240 | my @distances = (); |
241 | push @distances, [ |
242 | abs ( $ax - $cx ) + abs ( $ay - $cy ), |
243 | [ $ax, $ay, $cx, $cy ], |
244 | [ 'left', 'left' ] |
245 | ]; |
246 | push @distances, [ |
247 | abs ( $ax - $dx ) + abs ( $ay - $dy ), |
248 | [ $ax, $ay, $dx, $dy ], |
249 | [ 'left', 'right' ], |
250 | ]; |
251 | push @distances, [ |
252 | abs ( $bx - $cx ) + abs ( $by - $cy ), |
253 | [ $bx, $by, $cx, $cy ], |
254 | [ 'right', 'left' ], |
255 | ]; |
256 | push @distances, [ |
257 | abs ( $bx - $dx ) + abs ( $by - $dy ), |
258 | [ $bx, $by, $dx, $dy ], |
259 | [ 'right', 'right' ], |
260 | ]; |
261 | @distances = sort { $a->[0] <=> $b->[0] } @distances; |
262 | my $shortest = $distances[0]; |
263 | my ( $x1, $y1, $x2, $y2 ) = @{ $shortest->[1] }; |
264 | my ( $side1, $side2 ) = @{ $shortest->[2] }; |
265 | my ( $start, $end ); |
7256996b |
266 | my $offset = 9; |
aa0a19b9 |
267 | my $col1_right = $table_x{ $table1 }; |
268 | my $col2_right = $table_x{ $table2 }; |
269 | |
270 | my $diff = 0; |
271 | if ( $x1 == $x2 ) { |
272 | while ( $horz_taken{ $x1 + $diff } ) { |
273 | $diff = $side1 eq 'left' ? $diff - 2 : $diff + 2; |
274 | } |
275 | $horz_taken{ $x1 + $diff } = 1; |
276 | } |
277 | |
278 | if ( $side1 eq 'left' ) { |
279 | $start = $x1 - $offset + $diff; |
280 | } |
281 | else { |
282 | $start = $col1_right + $diff; |
283 | } |
284 | |
285 | if ( $side2 eq 'left' ) { |
286 | $end = $x2 - $offset + $diff; |
287 | } |
288 | else { |
289 | $end = $col2_right + $diff; |
290 | } |
291 | |
292 | push @shapes, [ 'line', $x1, $y1, $start, $y1, 'lightblue' ]; |
293 | push @shapes, [ 'line', $start, $y1, $end, $y2, 'lightblue' ]; |
294 | push @shapes, [ 'line', $end, $y2, $x2, $y2, 'lightblue' ]; |
295 | $done{ $fno1 }{ $fno2 } = 1; |
296 | $done{ $fno2 }{ $fno1 } = 1; |
297 | } |
298 | } |
299 | } |
300 | } |
301 | |
302 | # |
b18a0413 |
303 | # Add the title, legend and signature. |
dd9550a4 |
304 | # |
305 | my $large_font = gdLargeFont; |
306 | my $title_len = $large_font->width * length $title; |
aa0a19b9 |
307 | push @shapes, [ |
308 | 'string', $large_font, $max_x/2 - $title_len/2, 10, $title, 'black' |
309 | ]; |
dd9550a4 |
310 | |
b18a0413 |
311 | if ( %legend ) { |
312 | $max_y += 5; |
313 | push @shapes, [ |
314 | 'string', $font, $x, $max_y - $font->height - 4, 'Legend', 'black' |
315 | ]; |
316 | $max_y += $font->height + 4; |
317 | |
318 | my $longest; |
319 | for my $len ( map { length $_ } values %legend ) { |
320 | $longest = $len if $len > $longest; |
321 | } |
322 | $longest += 2; |
323 | |
324 | while ( my ( $key, $shape ) = each %legend ) { |
325 | my $space = $longest - length $shape; |
326 | push @shapes, [ |
327 | 'string', $font, $x, $max_y - $font->height - 4, |
328 | join( '', $shape, ' ' x $space, $key ), 'black' |
329 | ]; |
330 | |
331 | $max_y += $font->height + 4; |
332 | } |
333 | } |
334 | |
aa0a19b9 |
335 | my $sig = "auto-dia.pl $VERSION"; |
336 | my $sig_len = $font->width * length $sig; |
337 | push @shapes, [ |
338 | 'string', $font, $max_x - $sig_len, $max_y - $font->height - 4, |
339 | $sig, 'black' |
340 | ]; |
dd9550a4 |
341 | |
aa0a19b9 |
342 | # |
343 | # Render the image. |
344 | # |
dd9550a4 |
345 | my $gd = GD::Image->new( $max_x + 10, $max_y ); |
346 | unless ( $gd->can( $image_type ) ) { |
347 | die "GD can't create images of type '$image_type'\n"; |
348 | } |
aa0a19b9 |
349 | my %colors = map { $_->[0], $gd->colorAllocate( @{$_->[1]} ) } ( |
d21faa48 |
350 | [ white => [ 255, 255, 255 ] ], |
351 | [ beige => [ 245, 245, 220 ] ], |
352 | [ black => [ 0, 0, 0 ] ], |
353 | [ lightblue => [ 173, 216, 230 ] ], |
354 | [ lightgoldenrodyellow => [ 250, 250, 210 ] ], |
355 | [ khaki => [ 240, 230, 140 ] ], |
aa0a19b9 |
356 | ); |
dd9550a4 |
357 | $gd->interlaced( 'true' ); |
d21faa48 |
358 | my $background_color = $add_color ? 'lightgoldenrodyellow' : 'white'; |
359 | $gd->fill( 0, 0, $colors{ $background_color } ); |
dd9550a4 |
360 | for my $shape ( @shapes ) { |
361 | my $method = shift @$shape; |
aa0a19b9 |
362 | my $color = pop @$shape; |
363 | $gd->$method( @$shape, $colors{ $color } ); |
dd9550a4 |
364 | } |
365 | |
aa0a19b9 |
366 | # |
367 | # Print the image. |
368 | # |
dd9550a4 |
369 | if ( $out_file ) { |
370 | open my $fh, ">$out_file" or die "Can't write '$out_file': $!\n"; |
371 | print $fh $gd->$image_type; |
372 | close $fh; |
373 | print "Image written to '$out_file'. Done.\n"; |
374 | } |
375 | else { |
376 | print $gd->$image_type; |
377 | } |
378 | |
379 | =pod |
380 | |
381 | =head1 AUTHOR |
382 | |
383 | Ken Y. Clark E<lt>kclark@cpan.orgE<gt> |
384 | |
385 | =cut |