#!/usr/bin/perl

use strict;
use warnings;

use DBI;
use Getopt::Long;

my $VERSION = 0.01;

=head1 NAME

Script for getting database statistics from MySQL server.

=head1 DESCRIPTION

This script provides database statistics

=head1 COMMAND-LINE OPTIONS

Usage:  ./dbinfo.pl [Options]
OPTIONS:
    --mode=<str> -m <str>
	    Running mode: "dblist", "detailed" or "convert"
		summary  Summary for all databases (by default)
		detailed Detailed information about database tables
		convert  Table conversion mode (between storage engines)
	
    --username=<str> -u <str>
	    User name for DB connection (user 'root' by default)

    --password=<str> -p <str>
	    Pasword for DB connection (without password by default).

    --host=<str> -h <str>
	    mysql host name to connect with ('localhost' by default)

    --dbname <db> -d <db>
	    Work only with specified database (for all modes)

    --group -e
	    Enables storage enging grouping for tables (in detailed mode)
	    
	    
    --exclude-type=<engine>
	    Exclude specified storage engine from table list in detailed mode
	    
    --only-type=<engine>
	    Work only with specified storage engine in detailed mode
	    
    --new-type=<engine>
	    Possible values: myisam, innodb
	    New storage engine type for tables.
	    Used in conversion mode and anables only myisam <-> innodb conversions.
	
    --human-readable -r
	    Display numerical values in human-readable format: Kb, Mb, Gb.
	    
=head1 PREREQUISITES

This script requires C<DBI> and C<Getopt::Long> modules.

=pod OSNAMES

any

=head1 AUTHOR

Gleb Tumanov C<< <gleb at reg.ru> >> (code)
Walery Studennikov C<< <despair at cpan.org> >> (CPAN distribution)

=pod SCRIPT CATEGORIES

DB

=cut



# INIT

my $db_user = 'root';
my $db_pass = '';
my $db_host = 'localhost';

my ( $hread, $dbname, $excludetype, $onlytype, $newtype, $group_by_type );
my $mode = 'summary';

GetOptions(
    'mode|m=s'         => \$mode,
    'group|e'          => \$group_by_type,
    'dbname|d=s'       => \$dbname,
    'exclude-type=s'   => \$excludetype,
    'only-type=s'      => \$onlytype,
    'new-type=s'       => \$newtype,
    'human-readable|r' => \$hread,
    'username|u=s'         => \$db_user,
    'password|p=s'     => \$db_pass,
    'host|h=s'           => \$db_host,
);

my $dbh = DBI->connect("DBI:mysql:mysql:$db_host", $db_user, $db_pass)
    or die "Can't connect to mysql server!\n";

# RUN

main();

# MAIN

sub main {
    
    # Получить список баз данных и таблиц
    get_table_data();
    
    my $databases = get_databases();
    my $dbsize = get_dbsize();
    my $dbtypesize = get_dbtypesize();
    my $totaltypesize = get_totaltypesize();
    #my $dbtables = get_dbtables();
    my $wholetables = get_wholetables();
    my $enginetables = get_enginetables();
    
    # Вывести сводную информацию по базам данных
    if ( $mode eq 'summary' ) {
	print db_header($hread);
	print db_separator($hread);
	
	my @sorted_dbs = sort { $dbsize->{$b} <=> $dbsize->{$a} } @$databases;
	for my $db ( @sorted_dbs ) {
	    print db_line($db, $dbsize->{$db}, $dbtypesize->{$db}, $hread);
	}
	
	if ( ! $dbname ) {
	    my $total_size =
		$totaltypesize->{other} +
		$totaltypesize->{innodb} +
		$totaltypesize->{myisam};
	    print db_separator($hread);
	    print db_line('Total', $total_size, $totaltypesize, $hread);
	}
    }

    # Вывести подробную информацию по таблицам
    elsif ( $mode eq 'detailed' ) {
	
	# С группировкой по типу
	if ( $group_by_type ) {
	    my $engines = get_storage_engines();
	    
	    for my $engine ( @$engines ) {
		next unless exists $enginetables->{$engine};
		next if $excludetype && $engine =~ m/^$excludetype$/i;
		next if $onlytype && $engine !~ m/^$onlytype$/i;

		my @sorted_by_size =
		    sort { $b->{size} <=> $a->{size} }
		    @{ $enginetables->{$engine} };
		    
		my $total = 0;
		$total += $_->{size} for @sorted_by_size;
		    
		print "$engine:\n";
		print tab_group_header($hread);
		print tab_group_separator($hread);

		for my $tabdata ( @sorted_by_size ) {
		    print tab_group_line($tabdata->{db}, $tabdata->{table},
			$tabdata->{size}, $hread);
		}
		
		print tab_group_separator($hread);
		print tab_group_total($total, $hread);
	    }
	}
	
	# Без группировки по типу
	else {
	    print tab_header($hread);
	    print tab_separator($hread);
	    my @sorted_by_size = ();
	    
	    if ( $excludetype ) {
		@sorted_by_size =
		    sort { $b->{size} <=> $a->{size} }
		    grep { $_->{exp_type} !~ m/^$excludetype$/i }
		    @$wholetables;
	    }
	    elsif ( $onlytype ) {
		@sorted_by_size =
		    sort { $b->{size} <=> $a->{size} }
		    grep { $_->{exp_type} =~ m/^$onlytype$/i }
		    @$wholetables;
	    }
	    else {
		@sorted_by_size =
		    sort { $b->{size} <=> $a->{size} }
		    @$wholetables;
	    }
	    
	    my $total = 0;
	    $total += $_->{size} for @sorted_by_size;

	    for my $tabdata ( @sorted_by_size ) {
		print tab_line($tabdata->{db}, $tabdata->{table},
		    $tabdata->{exp_type}, $tabdata->{size}, $hread);
	    }
	    
	    print tab_separator($hread);
	    print tab_total($total, $hread);
	}
    }
    
    # Конвертация таблиц
    elsif ( $mode eq 'convert' ) {
	my $myisamtables = get_myisamtables();
	my $innodbtables = get_innodbtables();
	$newtype = lc( $newtype );
	
	unless ( $newtype && $newtype =~ m/^(myisam|innodb)$/ ) {
	    $newtype ||= 'undef';
	    print "Wron type to convert: $newtype\n";
	    exit;
	}
	
	my $where = $dbname ? " in $dbname" : '';
	my $ans = '';
	
	while ( $ans !~ m/^(y|n)$/i ) {
	    print "Sure alter all tables$where to $newtype? (y/n): ";
	    $ans = <STDIN>; chomp $ans;
	    lc( $ans ) eq 'n' && exit 0;
	}
	
	my @table_to_convert = $newtype eq 'myisam' ?
	    @$innodbtables : @$myisamtables;
	    
	my $total = scalar @table_to_convert;
	my $n = 1;
	for my $tabdata ( @table_to_convert ) {
	    local $| = 1;
	    print "Convert $tabdata->{db}.$tabdata->{table} (".
		human_readable( $tabdata->{size}, 1 )
		.") to $newtype ... ";
	    $dbh->do( "ALTER TABLE `$tabdata->{db}`.`$tabdata->{table}` ENGINE = $newtype" );
	    print "Done ($n of $total)\n";
	    ++$n;
	}
    }
}

# GET DATA

{
    my ( $dbtables, $databases, $dbsize, $dbtypesize,
	$totaltypesize, $wholetables, $enginetables,
	$myisamtables, $innodbtables);
    
    #sub get_dbtables { return $dbtables };
    sub get_databases { return $databases };
    sub get_dbsize { return $dbsize };
    sub get_dbtypesize { return $dbtypesize };
    sub get_totaltypesize { return $totaltypesize };
    sub get_wholetables { return $wholetables };
    sub get_enginetables { return $enginetables };
    sub get_myisamtables { return $myisamtables };
    sub get_innodbtables { return $innodbtables };
    
    sub get_table_data {
	$totaltypesize->{ $_ } = 0 for qw/myisam innodb other/;
	$myisamtables = [];
	$innodbtables = [];
    
	my $dbs = $dbh->selectcol_arrayref( "SHOW DATABASES" );
	for my $db ( @$dbs ) {
	    next if $db eq 'information_schema';
	    next if $db eq 'mysql';
	    next if $dbname && lc($dbname) ne lc($db);
	    push @$databases, $db;
	    $dbsize->{ $db } = 0;
	    
	    $dbh->do( "USE `$db`" );
	    
	    my $sth = $dbh->prepare( "SHOW TABLE STATUS" );
	    $sth->execute();
	    
	    $dbtypesize->{ $db }->{ $_ } = 0
		for qw/myisam innodb other/;
	    
	    while ( my ($table, $engine, undef, undef, undef, undef, $data_size,
		undef, $index_size) = $sth->fetchrow_array() ) {
		
		next unless $engine; # possible VIEW
		my $table_size = ($data_size + $index_size);
		
		my $set_engine = lc( $engine );
		$set_engine = 'other' if $engine !~ m/^(myisam|innodb)$/i;
		
		$dbsize->{ $db } += $table_size;
		$dbtypesize->{ $db }->{ $set_engine } += $table_size;
		$totaltypesize->{ $set_engine } += $table_size;
    
		if ( $mode eq 'detailed' ) {
		    #push @{ $dbtables->{ $db } }, {
		    #    table    => $table,
		    #    size     => $table_size,
		    #    exp_type => $engine,
		    #};
		    
		    push @{ $wholetables }, {
			db       => $db,
			table    => $table,
			size     => $table_size,
			exp_type => $engine,
		    };

		    push @{ $enginetables->{ $engine } }, {
			db       => $db,
			table    => $table,
			size     => $table_size,
		    };
		}
		elsif ( $mode eq 'convert' ) {
		    if ( $set_engine eq 'myisam' ) {
			push @{ $myisamtables }, {
			    db       => $db,
			    table    => $table,
			    size     => $table_size,
			};
		    }
		    elsif ( $set_engine eq 'innodb' ) {
			push @{ $innodbtables }, {
			    db       => $db,
			    table    => $table,
			    size     => $table_size,
			};
		    }
		}
	    }
	}
    }
}

# FUNCTIONS

sub tab_group_total {
    my ($total, $hread) = @_;
    my $width = $hread ? 7 : 12;

    return sprintf "%-15.15s %-20.20s %${width}s\n",
	'Total:', '',
	human_readable( $total, $hread );
}

sub tab_group_line {
    my ($dbname, $tabname, $size, $hread) = @_;
    my $width = $hread ? 7 : 12;

    return sprintf "%-15.15s %-20.20s %${width}s\n",
	$dbname, $tabname,
	human_readable( $size, $hread );
}

sub tab_group_header {
    my ($hread) = @_;
    my $width = $hread ? 7 : 12;
    
    return sprintf "%-15.15s %-20.20s %${width}s\n",
	'DATABASE',
	'TABLE',
	'SIZE';
}

sub tab_group_separator {
    my ($hread) = @_;
    return "-" x (length( tab_group_header($hread) ) - 1), "\n";
}

# --

sub tab_total {
    my ($total, $hread) = @_;
    my $width = $hread ? 7 : 12;

    return sprintf "%-15.15s %-20.20s %-10s %${width}s\n",
	'Total:', '', '',
	human_readable( $total, $hread );
}

sub tab_line {
    my ($dbname, $tabname, $engine, $size, $hread) = @_;
    my $width = $hread ? 7 : 12;

    return sprintf "%-15.15s %-20.20s %-10s %${width}s\n",
	$dbname, $tabname, $engine,
	human_readable( $size, $hread );
}

sub tab_header {
    my ($hread) = @_;
    my $width = $hread ? 7 : 12;
    
    return sprintf "%-15.15s %-20.20s %-10s %${width}s\n",
	'DATABASE',
	'TABLE',
	'ENGINE',
	'SIZE';
}

sub tab_separator {
    my ($hread) = @_;
    return "-" x (length( tab_header($hread) ) - 1), "\n";
}

# --

# scalar, scalar, hashref, bool
sub db_line {
    my ($dbname, $dbsize, $type_size, $hread) = @_;
    my $width = $hread ? 7 : 12;
    
    return sprintf "%-15.15s %${width}s %${width}s %${width}s %${width}s\n",
	$dbname,
	human_readable( $dbsize, $hread ),
	human_readable( $type_size->{myisam}, $hread ),
	human_readable( $type_size->{innodb}, $hread ),
	human_readable( $type_size->{other},  $hread );
}

sub db_header {
    my ($hread) = @_;
    my $width = $hread ? 7 : 12;
    
    return sprintf "%-15.15s %${width}s %${width}s %${width}s %${width}s\n",
	'DATABASE',
	'SIZE',
	'MYISAM',
	'INNODB',
	'OTHER';
}

sub db_separator {
    my ($hread) = @_;
    return "-" x (length( db_header($hread) ) - 1), "\n";
}

# --

sub get_storage_engines {
    return [ 'MyISAM', 'InnoDB',
	grep { ! /^(:?myisam|innodb)$/i }
	@{ $dbh->selectcol_arrayref( "SHOW STORAGE ENGINES" ) } ];
}

sub human_readable {
    my ($bytes, $hread) = @_;
    return $bytes unless $hread;
    
    my $length = length($bytes);
    
    if ( $length <= 3 ) {
	return $bytes . "B";
    }
    elsif ( $length <= 6 ) {
	return sprintf "%.3gK", $bytes / 1024;
    }
    elsif ( $length <= 9 ) {
	return sprintf "%.3gM", $bytes / 1048576;
    }
    else {
	return sprintf "%.3gG", $bytes / 1073741824;
    }
}

1;