#!/usr/bin/perl # Generates a graphviz relation graph of the complete SQL database, # information is parsed from sql/schema.sql # outputs the graph in dot format, usable as input to graphviz. # # Usage: # ./dbgraph.pl | dot -Tpng >dbgraph.png # # (this is a rather fast-written Perl hack, don't expect too much) use strict; use warnings; use Cwd 'abs_path'; (my $ROOT = abs_path $0) =~ s{/util/dbgraph\.pl$}{}; my %subgraphs = ( 'Producers' => [qw| FFFFCC producers producers_rev producers_relations |], 'Releases' => [qw| C8FFC8 releases releases_rev releases_media releases_platforms releases_producers releases_lang releases_vn |], 'Visual Novels' => [qw| FFE6BE vn vn_rev vn_relations vn_anime vn_screenshots |], 'Users' => [qw| CCFFFF users votes rlists wlists sessions notifications |], 'Discussion board' => [qw| FFDCDC threads threads_boards threads_posts |], 'Tags' => [qw| FFC8C8 tags tags_aliases tags_parents tags_vn |], 'Misc' => [qw| F5F5F5 changes anime screenshots stats_cache quotes relgraphs |], ); my %tables; # table_name => [ [ col1, pri ], ... ] my @rel; # 'table:col -- table:col', ... sub parse_dump { open my $R, '<', "$ROOT/util/sql/schema.sql" or die $!; my $in=''; while (<$R>) { chomp; if(/^ALTER TABLE ([a-z_]+) +ADD FOREIGN KEY \(([a-z0-9_]+)\) +REFERENCES ([a-z_]+) +\(([a-z0-9_]+)\)/) { push @rel, sprintf '%s:%s -- %s:%s', $1, $2, $3, $4; } if(!$in) { next if !/^CREATE TABLE ([a-z_]+) /; $in = $1; $tables{$in} = []; next; } if(/^\);/) { $in = ''; next; } if(/^\s+"?([a-z0-9_]+)"?\s/) { push @{$tables{$in}}, [ $1, 0 ]; $tables{$in}[$#{$tables{$in}}][1] = /PRIMARY KEY/ ? 1 : 0; next; } if(/^\s+PRIMARY KEY\((.+)\)/) { for my $c (split /,\s*/, $1) { $_->[1]=1 for (grep $_->[0] eq $c, @{$tables{$in}}); } } } close $R; } sub table_node { # table_name return $_[0].' [ label=<
'.$_[0].' |
'.$_->[0].' |