#!/usr/bin/perl
# v1.0
########################################################################
# COPYRIGHT NOTICE
# Copyright (C) 2005 by Summersault, LLC.  All rights reserved.
# Summersault / 914 East Main Street, Richmond, IN 47374
# Web: http://www.summersault.com/  E-mail: info@summersault.com
#######################################################################
#
# This program is free software; anyone can use, redistribute, and/or
# modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation (either version 2 of the
# License, or at your option, any later version) so long as this notice
# and the copyright information above remain intact and unchanged.
# Selling this code in any form for any reason is expressly forbidden.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this software; if not, write to
#   Free Software Foundation, Inc., 59 Temple Place, Suite 330,
#   Boston, MA  02111-1307 USA
# You may also find the license at:
#   http://www.gnu.org/copyleft/gpl.html
#
#######################################################################

# A script to report on MySQL database disk usage
# Example output looks like this:
#
#  | Db name                 | raw bytes     | fmted bytes  |
#  |-------------------------+---------------+--------------|
#  | johns_db                |   3681291683  |     3.43 G   |
#  | janes_db                |     82569818  |    78.74 M   |


# It expects that the user 'root' can access all the databases
# on the target server from this machine.

# This script is published at http://www.summersault.com/software/mysql_dbsize/


# The Mysql host to connect to. More connection string tweaking may be needed before.
# CHANGE THIS
our $host = 'mysqlhostname.myisp.com';

###############

use DBI;
use Number::Format;
use Perl6::Form;
use strict;
use warnings;

my $dbh = DBI->connect("dbi:mysql:database=mysql;host=$host",'root');
my $nf = Number::Format->new();

my $dbs_aref = $dbh->selectcol_arrayref("SHOW DATABASES"); 

# Store data in intermediate format to sort by bytes. 
# Structure looks like:
# my %dbs = (
#		db_name => { raw_bytes => 3, fmted_bytes => '3k' },
# );

my %dbs;
for my $db (@$dbs_aref) {
	my $db_bytes   =  _db_disk_bytes($db);
	$dbs{$db} = { 
		raw_bytes   => $db_bytes,
		fmted_bytes => $nf->format_bytes($db_bytes),

	}
}

print            "| Db name                 | raw bytes     | fmted bytes  |\n",
		         "|-------------------------+---------------+--------------|\n";
for my $db (reverse sort { $dbs{$a}{raw_bytes} <=> $dbs{$b}{raw_bytes} } keys %dbs) {
print form		 "| {<<<<<<<<<<<<<<<<<<<<<} | {>>>>>>>>>>}  | {>>>>.<<}{<} |",
					$db,                     $dbs{$db}{raw_bytes},$dbs{$db}{fmted_bytes},
					substr ($dbs{$db}{fmted_bytes},-1);
}


# return the  disk space bytes occupied by a given MySQL db name
sub _db_disk_bytes {
	my $db = shift || die "need db_name";

	my $dbh = DBI->connect("dbi:mysql:database=$db;host=$host",'root');
	my $tables_aref = $dbh->selectall_arrayref("SHOW TABLE STATUS",{ Slice=>{}}) || [];
	my $bytes = 0;
	for my $tbl (@$tables_aref) {
		$bytes += $tbl->{Data_length} + $tbl->{Index_length};
	}

	return $bytes;

}