#!/usr/bin/perl

# This is mysqldoc, a program designed to auto-document MySQL schema.
#
# This program is copyright (c) 2007 Ryan Lowe.
# Feedback and improvements are welcome (ryan.a.lowe@gmail.com)
#
# THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
# WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
# MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.
#
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation, version 2; OR the Perl Artistic License.  On UNIX and similar
# systems, you can issue `man perlgpl' or `man perlartistic' to read these
# licenses.
#
# You should have received a copy of the GNU General Public License along with
# this program; if not, write to the Free Software Foundation, Inc., 59 Temple
# Place, Suite 330, Boston, MA  02111-1307  USA.

use strict;
use warnings FATAL => 'all';
use Pod::Usage;
use Getopt::Long qw ( :config no_ignore_case bundling );
#use XML::Simple; # Will need for later versions
#use XML::XSLT; # Will need for later versions
use English qw ( -no_match_vars );
#use Error qw ( :try ); # Will need for later versions
use Switch;  # Bye soon!!!!!
use DBI;
use DBD::mysql;

sub get_pass;
sub build_headers;
sub build_footers;
sub get_schema_info;
sub convert_storage;
sub ascend_num;
sub get_databases;

my $PROGRAM = 'mysqldoc';
my $VERSION = '0.0.9';
my $WEBSITE = 'http://code.google.com/p/mysqldoc';

################################################################################
# Get configuration information, setting sane defaults
################################################################################

my %options;

$options { 'password' }  = '';
$options { 'verbosity' } = 0;

# To go away
$options { 'engines' } = 1;
$options { 'links' }   = 1;
$options { 'indexes' } = 0;

# Defaults
my @included_tables;
my @excluded_tables;
my $storage = 'MB';
my $storage_unit = 'MB';
my $no_storage = -1;

my $gop = new Getopt::Long::Parser;

if ( !$gop->getoptions ( 
  'databases|d=s'      => \$options { 'databases' },
  'help|?'             => \$options { 'help' },
  'host|h:s'           => \$options { 'host' },
  'html-links!'        => \$options { 'links' },
  'show-indexes!'      => \$options { 'indexes' },
  'output=s'           => sub { $options { 'output' } = uc ( $_[1] ); },,
  'password|p:s'       => sub { $options { 'password' } = $_[1] ? $_[1] : &get_pass; },
  'port=i'             => \$options { 'port' },
  'print-defaults'     => \$options { 'defaults' },
  'show-engines!'      => \$options { 'engines' },
  'show-size:s'        => \$storage,
  'no-show-size'       => \$no_storage,
  'show-triggers!'     => \$options { 'triggers' },
  'show-trigger-body!' => \$options { 'trigger_bodies' },
  'show-udf!'          => \$options { 'udfs' },
  'show-udf-body!'     => \$options { 'udf_bodies' },
  'socket=s'           => \$options { 'socket' },
  'ssl'                => \$options { 'ssl' },
  'ssl-ca=s'           => \$options { 'ssl-ca' },
  'ssl-capath=s'       => \$options { 'ssl-capath' },
  'ssl-cert=s'         => \$options { 'ssl-cert' },
  'ssl-cipher=s'       => \$options { 'ssl-cipher' },
  'ssl-key=s'          => \$options { 'ssl-key' },
  'tables=s'           => \@included_tables,
  'ignore-tables=s'    => \@excluded_tables,
  'user|u:s'           => \$options { 'user' },
  'version|V'          => \$options { 'version' } ) ) {

  pod2usage ( 2 );

}

################################################################################
# Die when necessary, then set sane defaults
################################################################################

pod2usage ( 2 ) if $options { 'help' };

if ( $options { 'version' } ) {

  print $PROGRAM . ' ' . $VERSION . ' (Check for updates at: ' . $WEBSITE . ")\n";
  exit;

}

if ( $options { 'defaults' } ) {

  print "$PROGRAM defaults are:

ATTRIBUTE                  VALUE
-------------------------- ------------------
databases                  ALL (excluding `mysql` and `information_schema`)
help                       FALSE
host                       localhost            
html-links                 TRUE
show-indexes               FALSE
output                     TXT
password                   (No default value)
port                       3306
print-defaults             FALSE
show-engines               TRUE
show-size                  TRUE (MB)
show-triggers              FALSE
show-trigger-body          FALSE
show-udf                   FALSE
show-udf-body              FALSE
socket                     (No default value)
ssl                        FALSE
ssl-ca                     (No default value)
ssl-capath                 (No default value)
ssl-cert                   (No default value)
ssl-cipher                 (No default value)
ssl-key                    (No default value)
tables                     (No default value)
ignore-tables              (No default value)
user                       Current user       
version                    FALSE
";

  exit;

}

@included_tables = split(',', join(',', @included_tables)); 
@excluded_tables = split(',', join(',', @excluded_tables));

$options { 'host' } = $options { 'host' } ? $options { 'host' } : 'localhost';
$options { 'user' } = $options { 'user' } ? $options { 'user' } : $ENV { USER };
$options { 'port' } = $options { 'port' } ? $options { 'port' } : '3306';

if ($no_storage eq -1) {

  $storage = 'MB' if ($storage eq '');
  $storage_unit = uc($storage);
  $storage = 1;

} else {

  $storage = 0;

}

my $connection_string = "dbi:mysql:;host=" . $options { 'host' } . ";port=" . $options { 'port' };

if ( $options { 'socket' } ) {

  $connection_string .= ';mysql_socket=' . $options { 'socket' };

}

if ( $options { 'ssl' } ||
     $options { 'ssl-ca' } ||
     $options { 'ssl-capath' } ||
     $options { 'ssl-cert' } ||
     $options { 'ssl-cipher' } ||
     $options { 'ssl-key' } ) {

  $connection_string .= ';mysql_ssl=1';

  $connection_string .= ';mysql_ssl_client_key=' . $options { 'ssl-key' } if ( $options { 'ssl-key' } );
  $connection_string .= ';mysql_ssl_client_cert=' . $options { 'ssl-cert' } if ( $options { 'ssl-cert' } );
  $connection_string .= ';mysql_ssl_ca_file=' . $options { 'ssl-ca' } if ( $options { 'ssl-ca' } );
  $connection_string .= ';mysql_ssl_ca_path=' . $options { 'ssl-capath' } if ( $options { 'ssl-capath' } );
  $connection_string .= ';mysql_ssl_cipher=' . $options { 'ssl-cipher' } if ( $options { 'ssl-cipher' } );

}

my $dbh = DBI->connect($connection_string, $options { 'user' }, $options { 'password' });

my $mvq = $dbh->prepare("SELECT SUBSTRING(\@\@version, 1, 3) mysql_version") 
                        or die "Couldn't prepare statement: " . $dbh->errstr;
$mvq->execute() or die "Couldn't execute statement: " . $dbh->errstr;
my $mysql_version = $mvq->fetchrow();

my $tsq = $dbh->prepare("SELECT NOW()") 
                        or die "Couldn't prepare statement: " . $dbh->errstr;
$tsq->execute() or die "Couldn't execute statement: " . $dbh->errstr;
my $ts = $tsq->fetchrow();

my %doc_info = ();

$options { 'databases' } = join ( ',', &get_databases ) if ( !$options { 'databases' } );

foreach my $database ( split/,/, join ( ',', $options{ 'databases' } ) ) {

  &getBaseInfo($database);

  if ($options { 'indexes' } eq 1) {

    for my $table_name (keys(%{$doc_info{ $database }{ 'TABLES' } })) {

      &getIndexInfo($database, $table_name);
 
    } 

  }   

  if ( $options { 'triggers' } || $options { 'trigger_bodies' } ) {

    for my $table_name (keys(%{$doc_info{ $database }{ 'TABLES' } })) {

      &getTriggerInfo($database, $table_name);

    }

  }

  &getUdfInfo ( $database ) if ( $options { 'udfs' } || $options { 'udf_bodies' } );

}

# FOR DEBUG
#use Data::Dumper;
#print Dumper(%doc_info);
#exit;

my $return_string = &buildOutputHeaders ( $options { 'output' } );

for my $database (keys %doc_info) {

  switch ( $options { 'output' } ) {

    case 'XML' {

      $return_string .= "\n<database name='$database'";

      $return_string .= " timestamp='$ts'";

      $return_string .= ">\n";
 
      for my $table (keys(%{$doc_info{ $database }{ 'TABLES' } })) {

        $return_string .= "  <table name='$table' type='$doc_info{ $database }{ 'TABLES' }{ $table }{ 'TABLE_TYPE' }'"; 

        if ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'TABLE_TYPE' } ne 'VIEW') {

          $return_string .= " engine='$doc_info{ $database }{ 'TABLES' }{ $table }{ 'ENGINE' }'" if ($options { 'engines' } eq 1);
          $return_string .= " total_size='" . &convertStorage($doc_info{ $database }{ 'TABLES' }{ $table }{ 'TOTAL_SIZE' }) . " $storage_unit'" if ($storage eq 1);
          $return_string .= " data_size='" . &convertStorage($doc_info{ $database }{ 'TABLES' }{ $table }{ 'DATA_LENGTH' }) . " $storage_unit'" if ($storage eq 1);
          $return_string .= " index_size='" . &convertStorage($doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEX_LENGTH' }) . " $storage_unit'" if ($storage eq 1); 

        }

        $return_string .= ">\n";

        for my $column (sort ascend_num keys %{$doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' } }) {

          $return_string .= "    <column name='$doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'COLUMN_NAME' }'>\n";
          $return_string .= "      <data_type>";

          $return_string .= $doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'DATA_TYPE' };

          $return_string .= "</data_type>\n";
          $return_string .= "      <default_value>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'DEFAULT' }</default_value>\n"; 
          $return_string .= "      <comment>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'COMMENT' }</comment>\n";
          $return_string .= "    </column>\n";

        }

        if ($options { 'indexes' } eq 1) {

          for my $index (keys(%{$doc_info{ $database }{ 'TABLES' }{ $table }->{ 'INDEXES' } })) {

           $return_string .= "    <index name='$index'>\n";
           $return_string .= "      <index_type>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }{ 'INDEX_TYPE' }</index_type>\n";

           my $column_count = keys %{$doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }{ 'COLUMNS' } };

           if ($column_count gt 1) {

             for (my $i = 1; $i < $column_count + 1; $i++) {

               for my $i_col (keys(%{$doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }->{ 'COLUMNS' } })) {

                 if ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }{ 'COLUMNS' }{ $i_col }->{ 'SEQ_IN_INDEX' } eq $i) {

                   $return_string .= "      <indexed_column seq_in_index='$doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }{ 'COLUMNS' }{ $i_col }->{ 'SEQ_IN_INDEX' }'>$i_col</indexed_column>\n";

                 }

               }
 
             }

           } else {

             # This sucks, but I don't know another way
             for my $s (keys(%{$doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }{ 'COLUMNS' } })) {

               $return_string .= "      <indexed_column seq_in_index='1'>$s</indexed_column>\n";

             }

           }

          $return_string .= "    </index>\n";

        }

      }

      if ( $options { 'triggers' } || $options { 'trigger_bodies' } ) {

        for my $trigger (keys(%{$doc_info{ $database }{ 'TABLES' }{ $table }->{ 'TRIGGERS' } })) {

          $return_string .= "    <trigger name='$trigger'>\n";
          $return_string .= "      <action_timing>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'TRIGGERS' }{ $trigger }{ 'ACTION_TIMING' }</action_timing>\n";
          $return_string .= "      <event_manipulation>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'TRIGGERS' }{ $trigger }{ 'EVENT_MANIPULATION' }</event_manipulation>\n";
          $return_string .= "      <action_statement>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'TRIGGERS' }{ $trigger }{ 'ACTION_STATEMENT' }</action_statement>\n" if ( $options { 'trigger_bodies' } );
          $return_string .= "    </trigger>\n";

        }

      }

      $return_string .= "  </table>\n\n";

    }

    if ( $options { 'udfs' } || $options { 'udf_bodies' } ) {

      for my $udf (keys(%{$doc_info{ $database }{ 'PROCEDURES' } })) {

        $return_string .= "  <procedure name='$udf' type='$doc_info{ $database }{ 'PROCEDURES' }{ $udf }{ 'ROUTINE_TYPE' }'>\n";

        $return_string .= "      <td><pre>$doc_info{ $database }{ 'PROCEDURES' }{ $udf }{ 'ROUTINE_DEFINITION' }</pre></td>\n" if ( $options { 'udf_bodies' } );

        $return_string .= "\n  </procedure>\n";

      }

    }

    $return_string .= "</database>\n";

  } case 'HTML' {


    $return_string .= "  <h1><strong>DATA DICTIONARY FOR: $database";
    $return_string .= " ($ts)";
    $return_string .= "</strong></h1>\n";

    for my $table (keys(%{$doc_info{ $database }{ 'TABLES' } })) {

      $return_string .= ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'TABLE_TYPE' } ne 'VIEW' ? "  <dl>\n    <dt>Table Name</dt>\n      <dd>$table " : "  <dl>\n    <dt>View Name</dt>\n      <dd>$table ");

      $return_string .= "- ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'TABLE_TYPE' }";

      if ($options { 'engines' } eq 1 && $doc_info{ $database }{ 'TABLES' }{ $table }{ 'TABLE_TYPE' } ne 'VIEW') {

        $return_string .= " ";

        if ($options { 'links' } eq 1) {

          switch ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'ENGINE' }) {

            case 'InnoDB' { $return_string .= "<a href='http://dev.mysql.com/doc/refman/$mysql_version/en/innodb.html'>"; }
            case 'MyISAM' { $return_string .= "<a href='http://dev.mysql.com/doc/refman/$mysql_version/en/myisam-storage-engine.html'>"; }
            case 'MEMORY' { $return_string .= "<a href='http://dev.mysql.com/doc/refman/$mysql_version/en/memory-storage-engine.html'>"; } 
            case 'MERGE' { $return_string .= "<a href='http://dev.mysql.com/doc/refman/$mysql_version/en/merge-storage-engine.html'>"; }
            case 'BDB' { $return_string .= "<a href='http://dev.mysql.com/doc/refman/$mysql_version/en/bdb-storage-engine.html'>"; }
            case 'EXAMPLE' { $return_string .= "<a href='http://dev.mysql.com/doc/refman/$mysql_version/en/example-storage-engine.html'>"; }
            case 'FEDERATED' { $return_string .= "<a href='http://dev.mysql.com/doc/refman/$mysql_version/en/federated-storage-engine.html'>"; }
            case 'ARCHIVE' { $return_string .= "<a href='http://dev.mysql.com/doc/refman/$mysql_version/en/archive-storage-engine.html'>"; }
            case 'CSV' { $return_string .= "<a href='http://dev.mysql.com/doc/refman/$mysql_version/en/csv-storage-engine.html'>"; }
            case 'BLACKHOLE' { $return_string .= "<a href='http://dev.mysql.com/doc/refman/$mysql_version/en/blackhole-storage-engine.html'>"; }
          
          }
     
        }

        $return_string .= "$doc_info{ $database }{ 'TABLES' }{ $table }{ 'ENGINE' }";

        $return_string .= "</a>" if ($options { 'links' } eq 1);

      }

      $return_string .= ")</dd>\n";

      if ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'TABLE_TYPE' } ne 'VIEW') {

        $return_string .= "    <dt>Table Stats</dt>";
        $return_string .= "\n      <dd>" . &convertStorage($doc_info{ $database }{ 'TABLES' }{ $table }{ 'TOTAL_SIZE' }) . " $storage_unit (DATA: " . 
                          &convertStorage($doc_info{ $database }{ 'TABLES' }{ $table }{ 'DATA_LENGTH' }) . " $storage_unit, INDEX: " . 
                          &convertStorage($doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEX_LENGTH' }) . " $storage_unit)</dd>" if ($storage eq 1);

        $return_string .= "\n    <dt>Table Contents</dt>\n      <dd>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'COMMENT' }</dd>\n" if ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'COMMENT' });

      }

      $return_string .= "  </dl>\n  <table border=\"0\" cellpadding=\"0\" cellspacing=\"0\">
    <tr>
      <th>Column Name</th>
      <th>Data Type</th>
      <th>Default Value</th>
      <th>Description</th>
    </tr>\n";
 
      for my $column (sort ascend_num keys %{$doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' } }) {

        $return_string .= "    <tr>\n      <td>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'COLUMN_NAME' }</td>\n      <td>"; 
        $return_string .= $doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'DATA_TYPE' };
        $return_string .= "</td>\n      <td>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'DEFAULT' }" if ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'DEFAULT' });

        $return_string .= "</td>\n";

        $return_string .= "      <td>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'COMMENT' }</td>\n" if ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'COMMENT' });
        $return_string .= "    </tr>\n";

      }

      $return_string .= "  </table>\n";

      if ($options { 'indexes' } eq 1) {

        $return_string .= "  <table border=\"0\" cellpadding=\"0\" cellspacing=\"0\">
    <tr>
      <th>Index Name</th>
      <th>Index Type</th>
      <th>Affected Column(s)</th>
    </tr>\n";

        for my $index (keys(%{$doc_info{ $database }{ 'TABLES' }{ $table }->{ 'INDEXES' } })) {

          $return_string .= "    <tr>\n     <td>$index</td>\n      <td>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }{ 'INDEX_TYPE' }</td>\n      <td>";

          my $column_count = keys %{$doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }{ 'COLUMNS' } };

          if ($column_count gt 1) {

            for (my $i = 1; $i < $column_count + 1; $i++) {

              for my $i_col (keys(%{$doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }->{ 'COLUMNS' } })) {

                if ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }{ 'COLUMNS' }{ $i_col }->{ 'SEQ_IN_INDEX' } eq $i) {

                  $return_string .= "$i_col";

                  $return_string .= ($i < $column_count) ? ", " : "</td>\n    </tr>\n";

                }

              }

            }

          } else {

            # This sucks, but I don't know another way
            for my $s (keys(%{$doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }{ 'COLUMNS' } })) {

              $return_string .= "$s</td>\n    </tr>\n\n";

            }

          }

        }

        $return_string .= "  </table>\n";

      }

      if ( $options { 'triggers' } || $options { 'trigger_bodies' } ) {

        my $trigger_count = keys %{$doc_info{ $database }{ 'TABLES' }{ $table }{ 'TRIGGERS' } };

        if ($trigger_count gt 0) {

          $return_string .= "  <table border=\"0\" cellpadding=\"0\" cellspacing=\"0\">
    <tr>
      <th>Trigger Name</th>
      <th>Action Timing</th>
      <th>Event Manipulation</th>\n";
        $return_string .= "      <th>Body</th>\n" if ( $options { 'trigger_bodies' } );
        $return_string .= "    </tr>\n";

          for my $trigger (keys(%{$doc_info{ $database }{ 'TABLES' }{ $table }->{ 'TRIGGERS' } })) {

            $return_string .= "    <tr>\n      <td>$trigger</td>\n";
            $return_string .= "      <td>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'TRIGGERS' }{ $trigger }{ 'ACTION_TIMING' }</td>\n";
            $return_string .= "      <td>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'TRIGGERS' }{ $trigger }{ 'EVENT_MANIPULATION' }</td>\n";
            $return_string .= "      <td><pre>$doc_info{ $database }{ 'TABLES' }{ $table }{ 'TRIGGERS' }{ $trigger }{ 'ACTION_STATEMENT' }</pre></td>\n" if ( $options { 'trigger_bodies' } );
            $return_string .= "    </tr>\n";

          }

          $return_string .= "  </table>\n";

        }

        if ( $options { 'udfs' } || $options { 'udf_bodies' } ) {

          $return_string .= "  <table border=\"0\" cellpadding=\"0\" cellspacing=\"0\">
    <tr>
      <th>UDF Name</th>
      <th>UDF Type</th>\n";
          $return_string .= "      <th>Event Manipulation</th>\n" if ( $options { 'udf_bodies' } );

          for my $udf (keys(%{$doc_info{ $database }{ 'PROCEDURES' } })) {

            $return_string .= "    <tr>\n      <td>$udf</td>\n       <td>$doc_info{ $database }{ 'PROCEDURES' }{ $udf }{ 'ROUTINE_TYPE' }</td>\n";
            $return_string .= "      <td><pre>$doc_info{ $database }{ 'PROCEDURES' }{ $udf }{ 'ROUTINE_DEFINITION' }</pre></td>\n" if ( $options { 'udf_bodies' } );

          }

          $return_string .= "  </table>\n";

        }

      } 

      }

    } else {

      $return_string .= "\nDATA DICTIONARY FOR: $database";
      $return_string .= " ($ts)";
      $return_string .= "\n\n";

      for my $table (keys(%{$doc_info{ $database }{ 'TABLES' } })) {

        $return_string .= "$table - ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'TABLE_TYPE' }";

        if ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'TABLE_TYPE' } ne 'VIEW') {

          $return_string .= " $doc_info{ $database }{ 'TABLES' }{ $table }{ 'ENGINE' }" if ($options { 'engines' } eq 1);

          $return_string .= ") ";

          $return_string .= &convertStorage($doc_info{ $database }{ 'TABLES' }{ $table }{ 'TOTAL_SIZE' }) . " $storage_unit (DATA: " .
                            &convertStorage($doc_info{ $database }{ 'TABLES' }{ $table }{ 'DATA_LENGTH' }) . " $storage_unit, INDEX: " .
                            &convertStorage($doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEX_LENGTH' }) . " $storage_unit)\n" if ($storage eq 1);
        }

        $return_string .= "  $doc_info{ $database }{ 'TABLES' }{ $table }{ 'COMMENT' }\n";
       
        foreach my $column (sort ascend_num keys %{$doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' } }) {

          $return_string .= "  $doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'COLUMN_NAME' } - ";
          $return_string .= $doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'DATA_TYPE' };

          $return_string .= "  DEFAULT $doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'DEFAULT' }" if ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'DEFAULT' });

          $return_string .= "\n      $doc_info{ $database }{ 'TABLES' }{ $table }{ 'COLUMNS' }{ $column }{ 'COMMENT' }\n";

        }

       if ($options { 'indexes' } eq 1) {

         for my $index (keys(%{$doc_info{ $database }{ 'TABLES' }{ $table }->{ 'INDEXES' } })) {

            $return_string .= "  $index ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }{ 'INDEX_TYPE' } INDEX)\n      ";

            my $column_count = keys %{$doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }{ 'COLUMNS' } };

            if ($column_count gt 1) {
 
              for (my $i = 1; $i < $column_count + 1; $i++) {

                for my $i_col (keys(%{$doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }->{ 'COLUMNS' } })) {

                  if ($doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }{ 'COLUMNS' }{ $i_col }->{ 'SEQ_IN_INDEX' } eq $i) {

                    $return_string .= "$i_col";

                    $return_string .= ($i < $column_count) ? ", " : "\n";

                  }

                }

              }

            } else {

              # This sucks, but I don't know another way
              for my $s (keys(%{$doc_info{ $database }{ 'TABLES' }{ $table }{ 'INDEXES' }{ $index }{ 'COLUMNS' } })) {

                $return_string .= "$s\n";

              }

            }
 
          }

        }

        if ( $options { 'triggers' } || $options { 'trigger_bodies' } ) {

          for my $trigger (keys(%{$doc_info{ $database }{ 'TABLES' }{ $table }->{ 'TRIGGERS' } })) {

            $return_string .= "  $trigger (TRIGGER) - $doc_info{ $database }{ 'TABLES' }{ $table }{ 'TRIGGERS' }{ $trigger }{ 'ACTION_TIMING' } $doc_info{ $database }{ 'TABLES' }{ $table }{ 'TRIGGERS' }{ $trigger }{ 'EVENT_MANIPULATION' }\n";
            $return_string .= "    $doc_info{ $database }{ 'TABLES' }{ $table }{ 'TRIGGERS' }{ $trigger }{ 'ACTION_STATEMENT' }\n" if ( $options { 'trigger_bodies' } );

          }

        }

      }

      if ( $options { 'udfs' } || $options { 'udf_bodies' } ) {
  
        for my $udf (keys(%{$doc_info{ $database }{ 'PROCEDURES' } })) {

          $return_string .= "\n  $udf ($doc_info{ $database }{ 'PROCEDURES' }{ $udf }{ 'ROUTINE_TYPE' })\n";
          $return_string .= "      $doc_info{ $database }{ 'PROCEDURES' }{ $udf }{ 'ROUTINE_DEFINITION' }\n" if ( $options { 'udf_bodies' } ); 

        }     

      }

    }

  }

}

$return_string .= &buildOutputFooters ( $options { 'output' } );

print $return_string;

sub get_pass {

  print STDERR "Password: ";
  system "stty -echo";
  chomp(my $passwd = <STDIN>);
  system "stty echo";
  print "\n";
  return $passwd;

}

sub buildOutputHeaders ($$) {

  my $rs;

  switch ($_[0]) {

    case 'HTML' { 
 
      $rs  = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">

<head>
  <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
  <title>DATA DICTIONARY FOR: ' .  join(', ', $options{ 'databases' } ); 

      $rs .= " ($ts)";

      $rs .= "</title>
  <style type=\"text/css\">
    body {font-family:Arial;}
    h1 {text-align:center;}
    table {margin-bottom:1em;}
    tr td {border-bottom:1px solid #ccc; 
           border-right:1px solid #ccc;
           padding:3px 8px; 
           border-collapse: collapse;}
    tr:hover {background-color:#FFFFCC;}
    th {text-align:left;padding:3px 8px;}
   </style>\n</head>\n\n<body>\n\n";

    } case 'XML' { 

      $rs = '<?xml version="1.0" encoding="utf-8" ?>

<!DOCTYPE databases [
  <!ELEMENT databases (database*)>
  <!ELEMENT database (table*)>
  <!ELEMENT table (column*, index*, trigger*)>
  <!ELEMENT column (data_type, default_value, comment)>
  <!ELEMENT index (index_type, indexed_column*)>
  <!ELEMENT trigger (action_timing, event_manipulation, action_statement?)>

  <!ELEMENT data_type (#PCDATA)>
  <!ELEMENT default_value (#PCDATA)>
  <!ELEMENT comment (#PCDATA)>
  <!ELEMENT index_type (#PCDATA)>
  <!ELEMENT indexed_column (#PCDATA)>
  <!ELEMENT action_timing (#PCDATA)>
  <!ELEMENT event_manipulation (#PCDATA)>
  <!ELEMENT action_statement (#PCDATA)>

  <!ATTLIST database name CDATA #REQUIRED
                     timestamp CDATA #IMPLIED>
  <!ATTLIST table name CDATA #REQUIRED
                  type CDATA #REQUIRED
                  engine CDATA #IMPLIED
                  total_size CDATA #IMPLIED
                  data_size CDATA #IMPLIED
                  index_size CDATA #IMPLIED>
  <!ATTLIST column name CDATA #REQUIRED>
  <!ATTLIST index name CDATA #REQUIRED>
  <!ATTLIST indexed_column seq_in_index CDATA #REQUIRED>
  <!ATTLIST trigger name CDATA #REQUIRED>

]>

<databases>
';

    } case 'TXT' { }

  }

  return $rs;

}

sub buildOutputFooters ($$) {

  my $rs = '';

  switch ($_[0]) {

    case 'HTML' {

      $rs = "\n\n<p>Generated By: <a href='$WEBSITE'>$PROGRAM</a> $VERSION</p>\n\n</body>\n\n</html>";

    } case 'XML' {

      $rs = '</databases>';

    }

  }

  return $rs;
 
}

sub getBaseInfo($$) {

  my $database = $_[0];

  my $sql = "SELECT `t`.`TABLE_NAME`,
                    `t`.`TABLE_TYPE`,
                    `t`.`ENGINE`,
                    IFNULL(`t`.`DATA_LENGTH`, 0) AS `DATA_LENGTH`,
                    IFNULL(`t`.`INDEX_LENGTH`, 0) AS `INDEX_LENGTH`,
                    `t`.`TABLE_COMMENT`,
                    `c`.`COLUMN_NAME`,
                    `c`.`COLUMN_TYPE`,
                    IFNULL(`c`.`CHARACTER_MAXIMUM_LENGTH`, `c`.`NUMERIC_PRECISION`) AS `MAX_LENGTH`,
                    IFNULL(`c`.`COLUMN_DEFAULT`, IF(`c`.`IS_NULLABLE` = 'NO','','NULL')) AS `COLUMN_DEFAULT`,
                    `c`.`COLUMN_COMMENT`,
                    `c`.`ORDINAL_POSITION`
               FROM `INFORMATION_SCHEMA`.`TABLES` `t`
               JOIN `INFORMATION_SCHEMA`.`COLUMNS` `c` ON (`t`.`TABLE_SCHEMA` = `c`.`TABLE_SCHEMA` AND `t`.`TABLE_NAME` = `c`.`TABLE_NAME`)
                 WHERE `t`.`TABLE_SCHEMA` = \"$database\"";

  if (scalar @included_tables gt 0) {

    my $asql;
    my $i = 0;

    foreach my $combo (@included_tables) {

      if ($combo =~ /\./ && $database eq substr($combo, 0, index($combo,'.'))) {

        if ($i gt 0) { $asql .= " OR "; }
        $asql .= " (`t`.`TABLE_SCHEMA` = \"" . substr($combo, 0, index($combo,'.')) . "\" AND `t`.`TABLE_NAME` = \"" . substr($combo, index($combo,'.') + 1) . "\")";  
        $i++;
 
      } 

    }  

    if ($i gt 0) {

      $sql .= ' AND (' . $asql . ')';

    }

  } elsif (scalar @excluded_tables gt 0) {

    my $asql;
    my $i = 0;

    foreach my $combo (@excluded_tables) {

      if ($combo =~ /./ && $database eq substr($combo, 0, index($combo,'.'))) {

        if ($i gt 0) { $asql .= " AND "; }
        $asql .= " (`t`.`TABLE_SCHEMA` = \"" . substr($combo, 0, index($combo,'.')) . "\" AND `t`.`TABLE_NAME` != \"" . substr($combo, index($combo,'.') + 1) . "\")"; 
        $i++;

      }

    }

    if ($i gt 0) {

      $sql .= ' AND (' . $asql . ')';

    }

  }

  $sql .= ' ORDER BY `t`.`TABLE_NAME`, `c`.`ORDINAL_POSITION` DESC';

  my $query = $dbh->prepare($sql) or die "Couldn't prepare statement: " . $dbh->errstr;

  $query->execute() or die "Couldn't execute statement: " . $dbh->errstr;

  while (my $ref = $query->fetchrow_hashref()) {

    unless (exists($doc_info{ $database }{ 'TABLES' }{ $ref->{ 'TABLE_NAME' } })) {

      $doc_info{ $database }{ 'TABLES' }{ $ref->{ 'TABLE_NAME' } } = { 'TABLE_TYPE'   => $ref->{ 'TABLE_TYPE' },
                                                                       'ENGINE'       => $ref->{ 'ENGINE' },
                                                                       'TOTAL_SIZE'   => $ref->{ 'DATA_LENGTH' } + $ref->{ 'INDEX_LENGTH' },
                                                                       'DATA_LENGTH'  => $ref->{ 'DATA_LENGTH' },
                                                                       'INDEX_LENGTH' => $ref->{ 'INDEX_LENGTH' },
                                                                       'COMMENT'      => $ref->{ 'TABLE_COMMENT' } };

    }

    $doc_info{ $database }{ 'TABLES' }{ $ref->{ 'TABLE_NAME' } }{ 'COLUMNS' }{ $ref->{ 'ORDINAL_POSITION' } } = { 'COLUMN_NAME'      => $ref->{ 'COLUMN_NAME' },
                                                                                                                  'DATA_TYPE'        => $ref->{ 'COLUMN_TYPE' },
                                                                                                                  'MAX_LENGTH'       => $ref->{ 'MAX_LENGTH' },
                                                                                                                  'DEFAULT'          => $ref->{ 'COLUMN_DEFAULT' },
                                                                                                                  'COMMENT'          => $ref->{ 'COLUMN_COMMENT' } };
  
  }

}

sub getIndexInfo($$) {

  my $database = $_[0];
  my $table_name = $_[1];

  my $index_query = $dbh->prepare("SELECT `INDEX_NAME`, `COLUMN_NAME`, `SEQ_IN_INDEX` FROM `INFORMATION_SCHEMA`.`STATISTICS`
                                   WHERE `TABLE_SCHEMA` = \"$database\" AND `TABLE_NAME` = \"$table_name\" GROUP BY `INDEX_NAME`,
                                   `COLUMN_NAME` ORDER BY `INDEX_NAME`, `SEQ_IN_INDEX`") or die "Couldn't prepare statement: " . $dbh->errstr;

  $index_query->execute() or die "Couldn't execute statement: " . $dbh->errstr;

  while (my $ref = $index_query->fetchrow_hashref()) {

    $doc_info{ $database }{ 'TABLES' }{ $table_name }{ 'INDEXES '}{ $ref->{ 'INDEX_NAME' } }{ 'COLUMNS' }{ $ref->{ 'COLUMN_NAME' } } = { 'SEQ_IN_INDEX' => $ref->{ 'SEQ_IN_INDEX' } };

  }

  for my $index (keys(%{$doc_info{ $database }{ 'TABLES' }{ $table_name }{ 'INDEXES' } })) {

    my $itq = $dbh->prepare("SELECT DISTINCT `INDEX_TYPE` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = \"$database\" AND
                            `TABLE_NAME` = \"$table_name\" AND `INDEX_NAME` = \"$index\"") or die "Couldn't prepare statement: " . $dbh->errstr;

    $itq->execute() or die "Couldn't execute statement: " . $dbh->errstr;

    $doc_info{ $database }{ 'TABLES' }{ $table_name }{ 'INDEXES' }{ $index }{ 'INDEX_TYPE' } =  $itq->fetchrow();

  }

}

sub getTriggerInfo($$) {

  my $database = $_[0];
  my $table_name = $_[1];

  my $trigger_query = $dbh->prepare("SELECT `trigger_name`, `action_timing`, `event_manipulation`, `action_statement` FROM `INFORMATION_SCHEMA`.`TRIGGERS`
                                    WHERE `TRIGGER_SCHEMA` = \"$database\" AND `EVENT_OBJECT_TABLE` = \"$table_name\"") or die "Couldn't prepare statement: " . $dbh->errstr;

  $trigger_query->execute() or die "Couldn't execute statement: " . $dbh->errstr;

  while (my $ref = $trigger_query->fetchrow_hashref()) {

    $doc_info{ $database }{ 'TABLES' }{ $table_name }{ 'TRIGGERS' }{ $ref->{ 'trigger_name' } } = { 'ACTION_TIMING'      => $ref->{ 'action_timing' },
                                                                                                    'EVENT_MANIPULATION' => $ref->{ 'event_manipulation' },
                                                                                                    'ACTION_STATEMENT'   => $ref->{ 'action_statement' } };

  }

}

sub getUdfInfo($$) {

  my $database = $_[0];

  my $udf_query = $dbh->prepare("SELECT `ROUTINE_NAME`, `ROUTINE_TYPE`, `ROUTINE_DEFINITION` FROM `INFORMATION_SCHEMA`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = \"$database\"") or die "Couldn't prepare statement: " . $dbh->errstr;

  $udf_query->execute() or die "Couldn't execute statement: " . $dbh->errstr;

  while (my $ref = $udf_query->fetchrow_hashref()) {

    $doc_info{ $database }{ 'PROCEDURES' }{ $ref->{ 'ROUTINE_NAME' } } = { 'ROUTINE_TYPE'       => $ref->{ 'ROUTINE_TYPE' },
                                                                           'ROUTINE_DEFINITION' => $ref->{ 'ROUTINE_DEFINITION' } };

  }

}

sub ascend_num {$a <=> $b}

sub convertStorage($$) {

  my $size = $_[0];
  my $convert_to = $_[1];

  switch ($convert_to) {

    case 'B' { return $size; }
    case 'KB' { return ($size / 1024); }
    case 'MB' { return ($size / 1024 / 1024); }
    case 'GB' { return ($size / 1024 / 1024); }
    case 'TB' { return ($size / 1024 / 1204 / 1204); }
    else { return ($size / 1024 / 1024); }

  }

}

sub get_databases {

  my $sql = 'SELECT DISTINCT `schemata`.`schema_name` FROM `information_schema`.`schemata` WHERE `schemata`.`schema_name` NOT IN ("mysql", "information_schema")';

  my $query = $dbh->prepare ( $sql ) or die "Couldn't prepare statement: " . $dbh->errstr;
  $query->execute ( ) or die "Couldn't execute statement: " . $dbh->errstr;

  my @ra;
  while ( my $db = $query->fetchrow ) {

    push ( @ra, $db ); 

  }

  return @ra;

}

################################################################################
# Documentation
################################################################################

=pod

=head1 NAME

mysqldoc - Auto-documentation for mysql schema

=head1 SYNOPSIS

mysqldoc [options]

  Options:
    --databases=database[,<database>[,...]]
                                 Database(s) to use.  Comma-Separated, if more than one.
    --help                       Display this help and exit.
    --host=<hostname>            Connect to host.
    --html-links                 Include HTML hyperlinks (only in HTML output) to
                                 relevant MySQL documentation.
    --no-html-links              Do not include links to MySQL documentation.
    --output=[XML | HTML | TXT]  The format in which to generate the output.
    --password[=<password>]      Password to use when connecting to server. If password is
                                 not given, it is asked from the tty.
    --port=#                     Port number to use for connection.
    --print-defaults             Print the program argument list and exit.
    --show-engines               Show the storage engine for each table.
    --no-show-engines            Do not show the storage engine for each table.
    --show-indexes               Show the indexes on each table.
    --no-show-indexes            Do not show the indexes on each table.
    --show-size[=B | KB | MB | GB | TB]
                                 Show storage size (for tables, indexes, and databases).
    --no-show-size               Do not show storage size.
    --show-triggers              Show TRIGGERS associated with each table.
    --no-show-triggers           Do not show TRIGGERS associated with each table.
    --show-trigger-body          Show TRIGGER bodies (implies --show-triggers).
    --no-show-trigger-body       Do not show TRIGGER bodies.
    --show-udf                   Show User-Defined Functions.
    --no-show-udf                Do not show User-Defined Functions.
    --show-udf-body              Show UDF bodies (implies --show-udf).
    --no-show-udf-body           Do not show UDF bodies.
    --socket                     Socket file to use for connection.
    --ssl                        Enable SSL for connection (automatically enabled with
                                 other flags). 
    --ssl-ca=name                CA file in PEM format (check OpenSSL docs, implies
                                 --ssl).
    --ssl-capath=name            CA directory (check OpenSSL docs, implies --ssl).
    --ssl-cert=name              X509 cert in PEM format (implies --ssl).
    --ssl-cipher=name            SSL cipher to use (implies --ssl).
    --ssl-key=name               X509 key in PEM format (implies --ssl).
    --tables=<table>[,<table>[,...]]
                                 Enumerated list of tables for the script to be run against.
                                 Each table must be specified with both database and table
                                 names, e.g. database_name.table_name.
    --ignore-tables=<table>[,<table>[,...]]
                                 Enumerated list of tables to be skipped (all unnamed tables
                                 will be included).  Each table must be specified with both
                                 database and table names, e.g. database_name.table_name.
    --user=<username>            Username to use when connecting to server.
    --version                    Output version information and exit.

 defaults are: 

 ATTRIBUTE                  VALUE
 -------------------------- ------------------
 databases                  ALL (excluding `mysql` and `information_schema`)
 help                       FALSE
 host                       localhost     
 html-links                 TRUE 
 show-indexes               FALSE
 output                     TXT  
 password                   (No default value)
 port                       3306 
 print-defaults             FALSE
 show-engines               TRUE 
 show-size                  TRUE (MB) 
 show-triggers              FALSE
 show-trigger-body          FALSE
 show-udf                   FALSE
 show-udf-body              FALSE
 socket                     (No default value)
 ssl                        FALSE
 ssl-ca                     (No default value)
 ssl-capath                 (No default value)
 ssl-cert                   (No default value)
 ssl-cipher                 (No default value)
 ssl-key                    (No default value)
 tables                     (No default value)
 ignore-tables              (No default value)
 user                       Current user 
 version                    FALSE

=head1 DESCRIPTION

mysqldoc is a tool for auto-documentation generation for MySQL 5.0 and above

=head1 ENVIRONMENT

The environment variable C<MYSQLDOC_HOME> points to the parent directory of the XSL files.

=head1 SYSTEM REQUIREMENTS

mysqldoc 0.0.9 requires the following Perl modules:

  DBI
  DBD::mysql
  Pod::Usage
  Getopt::Long
  Switch

=head1 BUGS

Please report all bugs and feature requests to L<$WEBSITE>.

=head1 LICENSE

This program is copyright (c) 2007 Ryan Lowe.
Feedback and improvements are welcome (ryan.a.lowe@gmail.com).

THIS PROGRAM IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
MERCHANTIBILITY AND FITNESS FOR A PARTICULAR PURPOSE.

This program is free software; you can redistribute it and/or modify it under
the terms of the GNU General Public License as published by the Free Software
Foundation, version 2; OR the Perl Artistic License.  On UNIX and similar
systems, you can issue `man perlgpl' or `man perlartistic' to read these
licenses.

You should have received a copy of the GNU General Public License along with
this program; if not, write to the Free Software Foundation, Inc., 59 Temple
Place, Suite 330, Boston, MA  02111-1307  USA.

=head1 AUTHOR

Ryan Lowe (ryan.a.lowe@gmail.com)

=head1 VERSION

This manual page documents 0.0.9 of mysqldoc.

=cut

