Commit | Line | Data |
37503827 |
1 | package SQL::Translator::Producer::Diagram; |
2 | |
3 | # ------------------------------------------------------------------- |
dcac44eb |
4 | # $Id: Diagram.pm,v 1.13 2006-05-13 01:25:44 kycl4rk Exp $ |
37503827 |
5 | # ------------------------------------------------------------------- |
977651a5 |
6 | # Copyright (C) 2002-4 SQLFairy Authors |
37503827 |
7 | # |
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. |
11 | # |
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. |
16 | # |
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 |
20 | # 02111-1307 USA |
21 | # ------------------------------------------------------------------- |
22 | |
e966b2b0 |
23 | =head1 NAME |
24 | |
25 | SQL::Translator::Producer::Diagram - ER diagram producer for SQL::Translator |
26 | |
27 | =head1 SYNOPSIS |
28 | |
29 | Use via SQL::Translator: |
30 | |
31 | use SQL::Translator; |
32 | |
9f61e9f2 |
33 | my $t = SQL::Translator->new( producer => 'Diagram', '...' ); |
e966b2b0 |
34 | $t->translate; |
35 | |
e966b2b0 |
36 | =cut |
37 | |
37503827 |
38 | use strict; |
39 | use GD; |
40 | use Data::Dumper; |
761fa621 |
41 | use SQL::Translator::Schema::Constants; |
37503827 |
42 | use SQL::Translator::Utils qw(debug); |
43 | |
44 | use vars qw[ $VERSION $DEBUG ]; |
dcac44eb |
45 | $VERSION = sprintf "%d.%02d", q$Revision: 1.13 $ =~ /(\d+)\.(\d+)/; |
37503827 |
46 | $DEBUG = 0 unless defined $DEBUG; |
47 | |
48 | use constant VALID_FONT_SIZE => { |
49 | small => 1, |
50 | medium => 1, |
51 | large => 1, |
52 | huge => 1, |
53 | }; |
54 | |
55 | use constant VALID_IMAGE_TYPE => { |
56 | png => 1, |
57 | jpeg => 1, |
58 | }; |
59 | |
60 | sub produce { |
761fa621 |
61 | my $t = shift; |
62 | my $schema = $t->schema; |
37503827 |
63 | my $args = $t->producer_args; |
64 | local $DEBUG = $t->debug; |
761fa621 |
65 | debug("Schema =\n", Dumper( $schema )); |
37503827 |
66 | debug("Producer args =\n", Dumper( $args )); |
67 | |
6a20798b |
68 | my $out_file = $args->{'out_file'} || ''; |
7652721a |
69 | my $output_type = $args->{'output_type'} || 'png'; |
6a20798b |
70 | my $title = $args->{'title'} || $t->filename; |
71 | my $font_size = $args->{'font_size'} || 'medium'; |
72 | my $imap_file = $args->{'imap_file'} || ''; |
73 | my $imap_url = $args->{'imap_url'} || ''; |
01a182ab |
74 | my $gutter = $args->{'gutter'} || 30; # distance b/w columns |
37503827 |
75 | my $no_columns = $args->{'no_columns'}; |
76 | my $no_lines = $args->{'no_lines'}; |
77 | my $add_color = $args->{'add_color'}; |
78 | my $show_fk_only = $args->{'show_fk_only'}; |
79 | my $join_pk_only = $args->{'join_pk_only'}; |
80 | my $natural_join = $args->{'natural_join'} || $join_pk_only; |
81 | my $skip_fields = $args->{'skip_fields'}; |
761fa621 |
82 | my %skip = map { s/^\s+|\s+$//g; $_,1 } split (/,/, $skip_fields); |
83 | |
dcac44eb |
84 | # my @tables = $schema->get_tables; |
85 | my @table_names; |
86 | if ( $natural_join ) { |
87 | $schema->make_natural_joins( |
88 | join_pk_only => $join_pk_only, |
89 | skip_fields => $args->{'skip_fields'}, |
90 | ); |
91 | |
92 | my $g = $schema->as_graph_pm; |
93 | my $d = Graph::Traversal::DFS->new( $g, next_alphabetic => 1 ); |
94 | $d->preorder; |
95 | |
96 | @table_names = $d->dfs; |
97 | } |
98 | else { |
99 | @table_names = map { $_->name } $schema->get_tables; |
100 | } |
37503827 |
101 | |
7652721a |
102 | die "Invalid image type '$output_type'" |
103 | unless VALID_IMAGE_TYPE ->{ $output_type }; |
37503827 |
104 | die "Invalid font size '$font_size'" |
105 | unless VALID_FONT_SIZE->{ $font_size }; |
106 | |
107 | # |
108 | # Layout the image. |
109 | # |
dcac44eb |
110 | my $font |
111 | = $font_size eq 'small' ? gdTinyFont |
112 | : $font_size eq 'medium' ? gdSmallFont |
113 | : $font_size eq 'large' ? gdLargeFont |
114 | : gdGiantFont; |
115 | |
116 | my $no_tables = scalar @table_names; |
550022e5 |
117 | $no_columns = 0 unless $no_columns =~ /^\d+$/; |
37503827 |
118 | $no_columns ||= sprintf( "%.0f", sqrt( $no_tables ) + .5 ); |
119 | $no_columns ||= .5; |
120 | my $no_per_col = sprintf( "%.0f", $no_tables/$no_columns + .5 ); |
121 | |
122 | my @shapes; |
123 | my ( $max_x, $max_y ); # the furthest x and y used |
124 | my $orig_y = 40; # used to reset y for each column |
125 | my ( $x, $y ) = (30,$orig_y); # where to start |
126 | my $cur_col = 1; # the current column |
127 | my $no_this_col = 0; # number of tables in current column |
128 | my $this_col_x = $x; # current column's x |
37503827 |
129 | my %nj_registry; # for locations of fields for natural joins |
130 | my @fk_registry; # for locations of fields for foreign keys |
131 | my %table_x; # for max x of each table |
132 | my $field_no; # counter to give distinct no. to each field |
761fa621 |
133 | my %coords; # holds fields coordinates |
6a20798b |
134 | my @imap_coords; # for making clickable image map |
37503827 |
135 | my %legend; |
136 | |
dcac44eb |
137 | for my $table_name ( @table_names ) { |
138 | my $table = $schema->get_table( $table_name ); |
139 | my $top = $y; |
37503827 |
140 | push @shapes, |
141 | [ 'string', $font, $this_col_x, $y, $table_name, 'black' ]; |
37503827 |
142 | $y += $font->height + 2; |
143 | my $below_table_name = $y; |
144 | $y += 2; |
145 | my $this_max_x = |
146 | $this_col_x + ($font->width * length($table_name)); |
147 | |
148 | debug("Processing table '$table_name'"); |
149 | |
761fa621 |
150 | my @fields = $table->get_fields; |
151 | debug("Fields = ", join(', ', map { $_->name } @fields)); |
37503827 |
152 | |
60dcc980 |
153 | my ( @fld_desc, $max_name, $max_desc ); |
37503827 |
154 | for my $f ( @fields ) { |
761fa621 |
155 | my $name = $f->name or next; |
156 | my $is_pk = $f->is_primary_key; |
37503827 |
157 | |
60dcc980 |
158 | my @attr; |
159 | |
37503827 |
160 | # |
161 | # Decide if we should skip this field. |
162 | # |
163 | if ( $show_fk_only ) { |
761fa621 |
164 | next unless $is_pk || $f->is_foreign_key; |
37503827 |
165 | } |
166 | |
167 | if ( $is_pk ) { |
60dcc980 |
168 | push @attr, 'PK'; |
169 | $legend{'Primary key'} = '[PK]'; |
37503827 |
170 | } |
cd9ccb3e |
171 | |
172 | if ( $f->is_unique ) { |
60dcc980 |
173 | push @attr, 'U'; |
37503827 |
174 | $legend{'Unique constraint'} = '[U]'; |
175 | } |
176 | |
60dcc980 |
177 | if ( $f->is_foreign_key ) { |
178 | push @attr, 'FK'; |
179 | $legend{'Foreign Key'} = '[FK]'; |
180 | } |
181 | |
182 | my $attr = ''; |
183 | if ( @attr ) { |
184 | $attr .= '[' . join(', ', @attr) . ']'; |
185 | } |
186 | |
761fa621 |
187 | my $desc = $f->data_type; |
60dcc980 |
188 | $desc .= '('.$f->size.')' if $f->size && |
189 | $f->data_type =~ /^(VAR)?CHAR2?$/i; |
37503827 |
190 | |
191 | my $nlen = length $name; |
60dcc980 |
192 | my $dlen = length $desc; |
37503827 |
193 | $max_name = $nlen if $nlen > $max_name; |
60dcc980 |
194 | $max_desc = $dlen if $dlen > $max_desc; |
195 | push @fld_desc, [ $name, $desc, $f->{'name'}, $is_pk, $attr ]; |
37503827 |
196 | } |
197 | |
60dcc980 |
198 | $max_name += 2; |
199 | $max_desc += 2; |
37503827 |
200 | for my $fld_desc ( @fld_desc ) { |
60dcc980 |
201 | my ( $name, $desc, $orig_name, $is_pk, $attr ) = @$fld_desc; |
202 | my $diff1 = $max_name - length $name; |
203 | my $diff2 = $max_desc - length $desc; |
204 | $name .= ' ' x $diff1; |
205 | $desc .= ' ' x $diff2; |
206 | $desc = $name . $desc . $attr; |
37503827 |
207 | |
208 | push @shapes, [ 'string', $font, $this_col_x, $y, $desc, 'black' ]; |
209 | $y += $font->height + 2; |
210 | my $length = $this_col_x + ( $font->width * length( $desc ) ); |
211 | $this_max_x = $length if $length > $this_max_x; |
212 | |
213 | my $constraints = $table->{'fields'}{ $orig_name }{'constraints'}; |
214 | |
215 | if ( $natural_join && !$skip{ $orig_name } ) { |
216 | push @{ $nj_registry{ $orig_name } }, $table_name; |
217 | } |
37503827 |
218 | |
219 | my $y_link = $y - $font->height/2; |
761fa621 |
220 | $coords{ $table_name }{ $orig_name }{'coords'} = { |
37503827 |
221 | left => [ $this_col_x - 6, $y_link ], |
222 | right => [ $length + 2 , $y_link ], |
223 | table => $table_name, |
224 | field_no => ++$field_no, |
225 | is_pk => $is_pk, |
226 | fld_name => $orig_name, |
227 | }; |
6a20798b |
228 | |
229 | push @imap_coords, [ |
230 | $imap_url."#$table_name-$orig_name", |
231 | $this_col_x, $y - $font->height, $length, $y_link, |
232 | ]; |
37503827 |
233 | } |
234 | |
761fa621 |
235 | unless ( $natural_join ) { |
236 | for my $c ( $table->get_constraints ) { |
237 | next unless $c->type eq FOREIGN_KEY; |
238 | my $fk_table = $c->reference_table or next; |
239 | |
240 | for my $field_name ( $c->fields ) { |
241 | for my $fk_field ( $c->reference_fields ) { |
242 | next unless defined $schema->get_table( $fk_table ); |
243 | push @fk_registry, [ |
761fa621 |
244 | [ $fk_table , $fk_field ], |
761a28bb |
245 | [ $table_name, $field_name ], |
761fa621 |
246 | ]; |
247 | } |
248 | } |
249 | } |
250 | } |
251 | |
37503827 |
252 | $this_max_x += 5; |
253 | $table_x{ $table_name } = $this_max_x + 5; |
254 | push @shapes, [ 'line', $this_col_x - 5, $below_table_name, |
255 | $this_max_x, $below_table_name, 'black' ]; |
256 | my @bounds = ( $this_col_x - 5, $top - 5, $this_max_x, $y + 5 ); |
257 | if ( $add_color ) { |
258 | unshift @shapes, [ |
259 | 'filledRectangle', |
260 | $bounds[0], $bounds[1], |
261 | $this_max_x, $below_table_name, |
262 | 'khaki' |
263 | ]; |
264 | unshift @shapes, [ 'filledRectangle', @bounds, 'white' ]; |
265 | } |
6a20798b |
266 | |
267 | push @imap_coords, [ |
268 | $imap_url."#$table_name", |
269 | $bounds[0], $bounds[1], $this_max_x, $below_table_name, |
270 | ]; |
271 | |
37503827 |
272 | push @shapes, [ 'rectangle', @bounds, 'black' ]; |
273 | $max_x = $this_max_x if $this_max_x > $max_x; |
274 | $y += 25; |
275 | |
276 | if ( ++$no_this_col == $no_per_col ) {# if we've filled up this column |
277 | $cur_col++; # up the column number |
278 | $no_this_col = 0; # reset the number of tables |
279 | $max_x += $gutter; # push the x over for next column |
280 | $this_col_x = $max_x; # remember the max x for this col |
281 | $max_y = $y if $y > $max_y; # note the max y |
282 | $y = $orig_y; # reset the y for next column |
283 | } |
284 | } |
285 | |
286 | # |
287 | # Connect the lines. |
288 | # |
289 | my %horz_taken; |
290 | my %done; |
291 | unless ( $no_lines ) { |
292 | my @position_bunches; |
293 | |
294 | if ( $natural_join ) { |
295 | for my $field_name ( keys %nj_registry ) { |
296 | my @positions; |
297 | my @table_names = |
298 | @{ $nj_registry{ $field_name } || [] } or next; |
299 | next if scalar @table_names == 1; |
300 | |
301 | for my $table_name ( @table_names ) { |
302 | push @positions, |
761fa621 |
303 | $coords{ $table_name }{ $field_name }{'coords'}; |
37503827 |
304 | } |
305 | |
306 | push @position_bunches, [ @positions ]; |
307 | } |
308 | } |
309 | else { |
310 | for my $pair ( @fk_registry ) { |
311 | push @position_bunches, [ |
761fa621 |
312 | $coords{$pair->[0][0]}{ $pair->[0][1] }{'coords'}, |
313 | $coords{$pair->[1][0]}{ $pair->[1][1] }{'coords'}, |
37503827 |
314 | ]; |
315 | } |
316 | } |
317 | |
318 | my $is_directed = $natural_join ? 0 : 1; |
319 | |
320 | for my $bunch ( @position_bunches ) { |
321 | my @positions = @$bunch; |
322 | |
323 | for my $i ( 0 .. $#positions ) { |
324 | my $pos1 = $positions[ $i ]; |
325 | my ( $ax, $ay ) = @{ $pos1->{'left'} || [] } or next; |
326 | my ( $bx, $by ) = @{ $pos1->{'right'} || [] } or next; |
327 | my $table1 = $pos1->{'table'}; |
328 | my $fno1 = $pos1->{'field_no'}; |
329 | my $is_pk = $pos1->{'is_pk'}; |
330 | next if $join_pk_only and !$is_pk; |
331 | |
332 | for my $j ( 0 .. $#positions ) { |
333 | my $pos2 = $positions[ $j ]; |
334 | my ( $cx, $cy ) = @{ $pos2->{'left'} || [] } or next; |
335 | my ( $dx, $dy ) = @{ $pos2->{'right'} || [] } or next; |
336 | my $table2 = $pos2->{'table'}; |
337 | my $fno2 = $pos2->{'field_no'}; |
338 | next if $table1 eq $table2; |
339 | next if $done{ $fno1 }{ $fno2 }; |
340 | next if $fno1 == $fno2; |
341 | |
342 | my @distances = (); |
343 | push @distances, [ |
344 | abs ( $ax - $cx ) + abs ( $ay - $cy ), |
345 | [ $ax, $ay, $cx, $cy ], |
346 | [ 'left', 'left' ] |
347 | ]; |
348 | push @distances, [ |
349 | abs ( $ax - $dx ) + abs ( $ay - $dy ), |
350 | [ $ax, $ay, $dx, $dy ], |
351 | [ 'left', 'right' ], |
352 | ]; |
353 | push @distances, [ |
354 | abs ( $bx - $cx ) + abs ( $by - $cy ), |
355 | [ $bx, $by, $cx, $cy ], |
356 | [ 'right', 'left' ], |
357 | ]; |
358 | push @distances, [ |
359 | abs ( $bx - $dx ) + abs ( $by - $dy ), |
360 | [ $bx, $by, $dx, $dy ], |
361 | [ 'right', 'right' ], |
362 | ]; |
363 | @distances = sort { $a->[0] <=> $b->[0] } @distances; |
364 | my $shortest = $distances[0]; |
365 | my ( $x1, $y1, $x2, $y2 ) = @{ $shortest->[1] }; |
366 | my ( $side1, $side2 ) = @{ $shortest->[2] }; |
367 | my ( $start, $end ); |
368 | my $offset = 9; |
369 | my $col1_right = $table_x{ $table1 }; |
370 | my $col2_right = $table_x{ $table2 }; |
371 | |
372 | my $diff = 0; |
373 | if ( $x1 == $x2 ) { |
374 | while ( $horz_taken{ $x1 + $diff } ) { |
375 | $diff = $side1 eq 'left' ? $diff - 2 : $diff + 2; |
376 | } |
377 | $horz_taken{ $x1 + $diff } = 1; |
378 | } |
379 | |
380 | if ( $side1 eq 'left' ) { |
381 | $start = $x1 - $offset + $diff; |
382 | } |
383 | else { |
384 | $start = $col1_right + $diff; |
385 | } |
386 | |
387 | if ( $side2 eq 'left' ) { |
388 | $end = $x2 - $offset + $diff; |
389 | } |
390 | else { |
391 | $end = $col2_right + $diff; |
392 | } |
393 | |
394 | push @shapes, |
395 | [ 'line', $x1, $y1, $start, $y1, 'cadetblue' ]; |
396 | push @shapes, |
397 | [ 'line', $start, $y1, $end, $y2, 'cadetblue' ]; |
398 | push @shapes, |
399 | [ 'line', $end, $y2, $x2, $y2, 'cadetblue' ]; |
400 | |
401 | if ( $is_directed ) { |
402 | if ( |
403 | $side1 eq 'right' && $side2 eq 'left' |
404 | || |
405 | $side1 eq 'left' && $side2 eq 'left' |
406 | ) { |
407 | push @shapes, [ |
408 | 'line', $x2 - 3, $y2 - 3, $x2, $y2, 'cadetblue' |
409 | ]; |
410 | push @shapes, [ |
411 | 'line', $x2 - 3, $y2 + 3, $x2, $y2, 'cadetblue' |
412 | ]; |
413 | push @shapes, [ |
414 | 'line', $x2 - 3, $y2 - 3, $x2 - 3, $y2 +3, |
415 | 'cadetblue' |
416 | ]; |
417 | } |
418 | else { |
419 | push @shapes, [ |
420 | 'line', $x2 + 3, $y2 - 3, $x2, $y2, 'cadetblue' |
421 | ]; |
422 | push @shapes, [ |
423 | 'line', $x2 + 3, $y2 + 3, $x2, $y2, 'cadetblue' |
424 | ]; |
425 | push @shapes, [ |
426 | 'line', $x2 + 3, $y2 - 3, $x2 + 3, $y2 +3, |
427 | 'cadetblue' |
428 | ]; |
429 | } |
430 | } |
431 | |
432 | $done{ $fno1 }{ $fno2 } = 1; |
433 | $done{ $fno2 }{ $fno1 } = 1; |
434 | } |
435 | } |
436 | } |
437 | } |
438 | |
439 | # |
440 | # Add the title, legend and signature. |
441 | # |
442 | my $large_font = gdLargeFont; |
443 | my $title_len = $large_font->width * length $title; |
444 | push @shapes, [ |
445 | 'string', $large_font, $max_x/2 - $title_len/2, 10, $title, 'black' |
446 | ]; |
447 | |
448 | if ( %legend ) { |
449 | $max_y += 5; |
450 | push @shapes, [ |
451 | 'string', $font, $x, $max_y - $font->height - 4, 'Legend', 'black' |
452 | ]; |
453 | $max_y += $font->height + 4; |
454 | |
455 | my $longest; |
456 | for my $len ( map { length $_ } values %legend ) { |
457 | $longest = $len if $len > $longest; |
458 | } |
459 | $longest += 2; |
460 | |
461 | while ( my ( $key, $shape ) = each %legend ) { |
462 | my $space = $longest - length $shape; |
463 | push @shapes, [ |
464 | 'string', $font, $x, $max_y - $font->height - 4, |
465 | join( '', $shape, ' ' x $space, $key ), 'black' |
466 | ]; |
467 | |
468 | $max_y += $font->height + 4; |
469 | } |
470 | } |
471 | |
60dcc980 |
472 | my $sig = 'Created by SQL::Translator ' . $t->version; |
37503827 |
473 | my $sig_len = $font->width * length $sig; |
474 | push @shapes, [ |
475 | 'string', $font, $max_x - $sig_len, $max_y - $font->height - 4, |
476 | $sig, 'black' |
477 | ]; |
478 | |
479 | # |
480 | # Render the image. |
481 | # |
482 | my $gd = GD::Image->new( $max_x + 30, $max_y ); |
7652721a |
483 | unless ( $gd->can( $output_type ) ) { |
484 | die "GD can't create images of type '$output_type'\n"; |
37503827 |
485 | } |
486 | my %colors = map { $_->[0], $gd->colorAllocate( @{$_->[1]} ) } ( |
487 | [ white => [ 255, 255, 255 ] ], |
488 | [ beige => [ 245, 245, 220 ] ], |
489 | [ black => [ 0, 0, 0 ] ], |
490 | [ lightblue => [ 173, 216, 230 ] ], |
491 | [ cadetblue => [ 95, 158, 160 ] ], |
492 | [ lightgoldenrodyellow => [ 250, 250, 210 ] ], |
493 | [ khaki => [ 240, 230, 140 ] ], |
494 | [ red => [ 255, 0, 0 ] ], |
495 | ); |
496 | $gd->interlaced( 'true' ); |
497 | my $background_color = $add_color ? 'lightgoldenrodyellow' : 'white'; |
498 | $gd->fill( 0, 0, $colors{ $background_color } ); |
499 | for my $shape ( @shapes ) { |
500 | my $method = shift @$shape; |
501 | my $color = pop @$shape; |
502 | $gd->$method( @$shape, $colors{ $color } ); |
503 | } |
504 | |
505 | # |
6a20798b |
506 | # Make image map. |
507 | # |
508 | debug("imap file = '$imap_file'"); |
509 | if ( $imap_file && @imap_coords ) { |
510 | open my $fh, ">$imap_file" or die "Can't write '$imap_file': $!\n"; |
511 | print $fh qq[<html><body><img src="" usemap="#imap" border="0">\n]. |
512 | qq[<map name="imap">\n]; |
513 | for my $rec ( @imap_coords ) { |
514 | my $href = shift @$rec; |
515 | print $fh q[<area coords="].join(',', @$rec).qq[" href="$href">\n]; |
516 | } |
517 | print $fh qq[</body></html>]; |
518 | close $fh; |
519 | } |
520 | |
521 | # |
37503827 |
522 | # Print the image. |
523 | # |
524 | if ( $out_file ) { |
525 | open my $fh, ">$out_file" or die "Can't write '$out_file': $!\n"; |
7652721a |
526 | print $fh $gd->$output_type; |
37503827 |
527 | close $fh; |
528 | } |
529 | else { |
7652721a |
530 | return $gd->$output_type; |
37503827 |
531 | } |
532 | } |
533 | |
534 | 1; |
535 | |
977651a5 |
536 | # ------------------------------------------------------------------- |
537 | |
37503827 |
538 | =pod |
539 | |
37503827 |
540 | =head1 AUTHOR |
541 | |
e966b2b0 |
542 | Ken Y. Clark E<lt>kclark@cpan.orgE<gt>. |
37503827 |
543 | |
544 | =cut |