#!/usr/bin/perl

#
# mysql-graphviz-schema.pl - creates database graph from mysqldump output
#
# Usage: 
#
#   mysqldump -u user Database -h dbserver.provider.com | ./mysql-graphviz-schema.pl > Database-schema.dot
#   [dot|neato|fdp|twopi|circo] -Tpng Database-schema.dot > Database-schema.png
#
# dot neato fdp twopi circo - utilities from 'graphviz' package, http://www.graphviz.org
#
# Developed by Lubomir Host 'rajo' <rajo AT platon.sk>
# Copyright (c) 2004 Platon SDG, http://platon.sk/
# Licensed under terms of GNU General Public License.
# All rights reserved.
#
# Changelog:
# 2004-11-22 - created
#

# $Platon: scripts/perl/mysql/mysql-graphviz-schema.pl,v 1.2 2004/11/30 19:20:37 rajo Exp $

use strict;

$| = 1;

my $create_digraph = shift;
$create_digraph = defined($create_digraph) ? $create_digraph : 'yes';

if ($create_digraph eq 'yes') {
	print <<EOF

digraph "DB structure" {
	node [ shape = record ]; // sprav vsetky boxy hranate
	fontsize = 24;
	ranksep = 2.5;
	ratio = 0.7;
	rankdir = global;
EOF
;
}

my $database = '';
my $table = '';
my @references;

while (my $line = <STDIN>) {
	chomp $line;
	$line =~ s/^\s+//g;

	if ($line =~ m/^--.*Database:\s+(\S+)\s*$/) {
		print "\n};\n" if ($database ne '');
		$database = $1;
	}

	if ($line =~m/^CREATE\s+TABLE\s+(\S+)/i) {
		$table = $1;
		$table =~ s/`//g;

		my @info;
		my $table_type;
		while ($line = <STDIN>) {
			chomp $line;
			$line =~ s/^\s+//g;
			if ($line !~ m/^\)/) { # this is not end of table
				my ($column) = ($line =~ m/^((?:(?:PRIMARY|UNIQUE)?\s*(KEY)?\s*)\S+)/i);
				$column =~ s/[(),`]//g;
				$column =~ s/\s+/_/g;

				if ($line =~ m/^CONSTRAINT/i) { # foreign InnoDB keys
					my ($ref_column, $ref_table, $ref_table_column) = ($line =~ m/FOREIGN\s+KEY\s+(\S+)\s+REFERENCES\s+(\S+)\s+(\S+)/i);
					$ref_column			=~ s/[(),`]//g;
					$ref_table			=~ s/[(),`]//g;
					$ref_table_column	=~ s/[(),`]//g;
					push @references, "\t\ttable_${database}_$table : $ref_column "
						. "-> table_${database}_$ref_table : $ref_table_column [ fontcolor=blue, label = \"$ref_column\" ];\n";

				}
				else {
					push @info, "<$column> $line";
				}
			}
			else { # end of table, remember table type
				($table_type) = ($line =~ m/^\)\s*TYPE=(\S+)\s*.*;/i);
				last;
			}
		}
		print "\t\tsubgraph \"cluster_table_${database}_$table\"  {\n\t\t\tfontsize = 20;\n\t\t\tstyle = bold;\n\t\t\tfontcolor = \"red\";\n\t\t\tlabel=\"$database.$table\";\n";
		#print "\t\t\t\"table_type_${database}_$table\" [ fontsize = 12 , label = \"$table_type\" ];\n";
		print "\t\t\t\"table_${database}_$table\" [ fontsize = 12, label=\"{" . join('\n|', @info) . "}\"";
	}
	print " ];\n\t\t}\n" if ($line =~ m/^\)/);
			
}

foreach (@references) {
	print;
}

if ($create_digraph eq 'yes') {
	print <<EOF
}

EOF
;
}

# vim: ts=4
# vim600: fdm=marker fdl=0 fdc=3