Up to [Platon] / scripts / perl / mysql

* Add database name into table title.
* Produce *.dot header only if requeired (default yes).

#!/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