tokubass/mysqldump_xml_to_html.pl

## mysqldump_xml_to_html.pl
#!/usr/bin/env perl
use 5.014;
use XML::Simple;
use utf8;
use Data::Dumper;
binmode STDIN, ":encoding(utf8)";
binmode STDOUT, ":encoding(utf8)";

#$ mysqldump -uroot --no-data --xml db_name > db.xml
#$ mysqldump_xml_to_html.pl db.xml > db.html

$XML::Simple::PREFERRED_PARSER = 'XML::Parser';

my $file_path = shift;
my $parser = XML::Simple->new( SuppressEmpty => undef, forcearray => [qw/table_structure key field/]);
my $parsed_xml =  $parser->XMLin($file_path);

my @tables;
my %tables = %{$parsed_xml->{database}{table_structure}};

for my $table_name (sort keys %tables) {
    my $table_data;
    $table_data->{name} = $table_name;
    $table_data->{columns} = $tables{$table_name}{field};

    for my $index (@{$tables{$table_name}{key}}) {
        my $index_name = $index->{Key_name};
        my $seq_num = $index->{Seq_in_index};
        $table_data->{index}{$index_name}{seq}{$seq_num} = $index->{Column_name};
        if ($index->{Key_name} eq 'PRIMARY') {
            $table_data->{index}{$index_name}{index_type} = 'PRIMARY';
        }
        elsif($index->{Non_unique} eq '0') {
            $table_data->{index}{$index_name}{index_type} = 'UNIQUEUE';
        }else{
            $table_data->{index}{$index_name}{index_type} = 'KEY';
        }
    }
    my %option = %{$tables{$table_name}{options}};
    $table_data->{comment} = $option{Comment};

    push @tables, $table_data;
}
#warn Dumper \@tables;

use Template;
my $tt = Template->new;
my $output = '';
$tt->process(\*DATA, { tables => \@tables },\$output)
    || die $tt->error(), "\n";

print $output;

__DATA__
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf8">
<meta charset="utf-8">
<title>テーブル定義</title>
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap-theme.min.css">
<style>
table.htable {
    margin: 3em auto 1em auto !important;
}
table.htable th {
    border-left: 10px solid #e5e5e5 !important;
}
footer {
    border-top: 1px solid #e5e5e5;
    padding: 0.5em;
}
</style>
</head>
<body>
<div class="container">
  <h1 class="page-header">テーブル定義</h1>
  [% FOREACH table IN tables %]
    <h4>[% table.name %]  ([% table.comment %]) </h4>
    <table class="table table-condensed table-striped table-bordered">
      <thead>
        <tr>
          <th>カラム名</th>
          <th>型</th>
          <th>NULL</th>
          <th>デフォルト値</th>
          <th>キー</th>
          <th>コメント</th>
        </tr>
      </thead>
      <tbody>
    [% FOREACH col IN table.columns  %]
        <tr>
          <td>[% col.Field %]</td>
          <td>[% col.Type %]</td>
          <td>[% col.Null %]</td>
          <td>[% col.Default %]</td>
          <td>[% col.Key %]</td>
          <td>[% col.Comment %]</td>
        </tr>
    [% END %]
      </tbody>
    </table>

    <h4>index</h4>
    <ul>
    [% FOREACH index_name IN table.index.keys.sort -%]
    <li>
    [% table.index.$index_name.index_type %] [% UNLESS index_name == 'PRIMARY' %][% index_name %][% END %]
      ([% FOREACH seq IN table.index.$index_name.seq.keys.sort -%]
      '[% table.index.$index_name.seq.$seq -%]',
      [% END -%])
    </li>
    [% END -%]
    </ul>
    <br />
  [% END %]
</div>
</body>
	#!/usr/bin/env perl
	use 5.014;
	use XML::Simple;
	use utf8;
	use Data::Dumper;
	binmode STDIN, ":encoding(utf8)";
	binmode STDOUT, ":encoding(utf8)";

	#$ mysqldump -uroot --no-data --xml db_name > db.xml
	#$ mysqldump_xml_to_html.pl db.xml > db.html

	$XML::Simple::PREFERRED_PARSER = 'XML::Parser';

	my $file_path = shift;
	my $parser = XML::Simple->new( SuppressEmpty => undef, forcearray => [qw/table_structure key field/]);
	my $parsed_xml = $parser->XMLin($file_path);

	my @tables;
	my %tables = %{$parsed_xml->{database}{table_structure}};

	for my $table_name (sort keys %tables) {
	my $table_data;
	$table_data->{name} = $table_name;
	$table_data->{columns} = $tables{$table_name}{field};

	for my $index (@{$tables{$table_name}{key}}) {
	my $index_name = $index->{Key_name};
	my $seq_num = $index->{Seq_in_index};
	$table_data->{index}{$index_name}{seq}{$seq_num} = $index->{Column_name};
	if ($index->{Key_name} eq 'PRIMARY') {
	$table_data->{index}{$index_name}{index_type} = 'PRIMARY';
	}
	elsif($index->{Non_unique} eq '0') {
	$table_data->{index}{$index_name}{index_type} = 'UNIQUEUE';
	}else{
	$table_data->{index}{$index_name}{index_type} = 'KEY';
	}
	}
	my %option = %{$tables{$table_name}{options}};
	$table_data->{comment} = $option{Comment};

	push @tables, $table_data;
	}
	#warn Dumper \@tables;

	use Template;
	my $tt = Template->new;
	my $output = '';
	$tt->process(\*DATA, { tables => \@tables },\$output)
	\|\| die $tt->error(), "\n";

	print $output;

	__DATA__
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN">
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=utf8">
	<meta charset="utf-8">
	<title>テーブル定義</title>
	<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap.min.css">
	<link rel="stylesheet" href="http://maxcdn.bootstrapcdn.com/bootstrap/3.2.0/css/bootstrap-theme.min.css">
	<style>
	table.htable {
	margin: 3em auto 1em auto !important;
	}
	table.htable th {
	border-left: 10px solid #e5e5e5 !important;
	}
	footer {
	border-top: 1px solid #e5e5e5;
	padding: 0.5em;
	}
	</style>
	</head>
	<body>
	<div class="container">
	<h1 class="page-header">テーブル定義</h1>
	[% FOREACH table IN tables %]
	<h4>[% table.name %] ([% table.comment %]) </h4>
	<table class="table table-condensed table-striped table-bordered">
	<thead>
	<tr>
	<th>カラム名</th>
	<th>型</th>
	<th>NULL</th>
	<th>デフォルト値</th>
	<th>キー</th>
	<th>コメント</th>
	</tr>
	</thead>
	<tbody>
	[% FOREACH col IN table.columns %]
	<tr>
	<td>[% col.Field %]</td>
	<td>[% col.Type %]</td>
	<td>[% col.Null %]</td>
	<td>[% col.Default %]</td>
	<td>[% col.Key %]</td>
	<td>[% col.Comment %]</td>
	</tr>
	[% END %]
	</tbody>
	</table>

	<h4>index</h4>
	<ul>
	[% FOREACH index_name IN table.index.keys.sort -%]
	<li>
	[% table.index.$index_name.index_type %] [% UNLESS index_name == 'PRIMARY' %][% index_name %][% END %]
	([% FOREACH seq IN table.index.$index_name.seq.keys.sort -%]
	'[% table.index.$index_name.seq.$seq -%]',
	[% END -%])
	</li>
	[% END -%]
	</ul>
	<br />
	[% END %]
	</div>
	</body>