#!/usr/bin/perl
#
# $Id: mdu,v 1.1 2000/10/10 23:56:36 tom Exp tom $
#
# This is mdu, a cool utility to check the storage usage of a mysql
# database. Type "mdu --help" to lern about it's usage.
#
# Copyright (c) 1999-2000  Thomas Linden <tom@daemon.de>. This is
# free software with ABSOLUTELY NO WARRANTY. Use at your own risk.
#
# Feel free to contact me in case of any problems or if you have
# any feature requests or bug reports.
#

use strict;
use Data::Dumper;
use Getopt::Long;
use DBI;


my (
    $db_host,
    $db_user,
    $db_passwd,
    $db_port,
    $db_driver,
    $database, $DB,
    $table, @tables,
    $columns, @columns, %columns,
    $help, $debug, $version, $opt_v, $type, $rawmode, $usage,
    %StaticType,
    %VarType,
    %Usage,
    );


# define defaults:
$db_host    = "localhost";
$db_driver  = "mysql";
$version    = "0.1";
$type       = "all";

Getopt::Long::Configure( qw(no_ignore_case));

if (! GetOptions (
		  "host|H=s"     => \$db_host,
		  "user|U=s"     => \$db_user,
		  "passwd|P:s"   => \$db_passwd,
		  "port|p=s"     => \$db_port,
		  "database|D=s" => \$database,
		  "table|T=s"    => \$table,
		  "columns|C=s"  => \$columns,
		  "help!"        => \$help,
		  "debug|d:i"    => \$debug,
		  "version|v!"   => \$opt_v,
		  "type|t=s"     => \$type,
		  "raw|r!"       => \$rawmode,
		 )    ) {
  $usage = 1;
}


# obligatory
if ($opt_v) {
  print "This is mdu verison $version\n"
       ."Copyright (c) 1999-2000  Thomas Linden <tom\@daemon.de>. This is\n"
       ."free software with ABSOLUTELY NO WARRANTY. Use at your own risk.\n";
  exit 0;
}


# determine required options
if (!$database) {
  print "option --database (or -D) is required!\n";
  $usage = 1;
}
else {
  &connect;
}


if ($usage) {
  print qq(
Usage: mdu -D <dbname> | --datebase=<dbname> [OPTION]
Display storage usage of a mysql database in bytes.

 -D <dbname>,    --datebase=<dbname>   database to be used. required.
 -H <host>,      --host=<host>         host to connect to.
 -U <user>,      --user=<user>         user for authentication.
 -P <passwd>,    --passwd=<passwd>     password for authentication.
 -p <port>,      --port=<port>         use an alternate port.
 -T <table,...>, --table=<table,...>   only calculate the specified
                                       table(s), separated by comma. you can
                                       specify columns, i.e.: "-T table.column"
 -C <col,...>,   --columns=<col,...>   only calculate specified columns,
                                       this can only be used with 1 table.
 -t <type>,      --type=<type>         output type, either
                                       "all"(which is the default),
                                       "tables"(usage by table),
                                       "columns"(usage by column)
                                       or "sum" which prints only the summary.
 -r,             --raw                 raw output, no formatting.
 -d [level]      --debug[=level]       debugging output, optional level:1-3.
 -v              --version             print the version number.
                 --help                print usage screen.

);
  exit 1;
}



# main()

# build the %Usage base hash if there was some user input
&build_from_input;


# check if %Usage has already some content. If yes, the user supplied
# table/column names, so we don't need to investigate, otherwise do it.

if (!%Usage) {
  &lookup_tables;
}

# generate the %*Types Hashes based on __DATA__
&build_types;


# collect usage data based on %Usage hash
my $db_usage = &collect_bytes;
print "db_usage: $db_usage\n" if($debug >= 3);


# print 'em. based on format and $type
&out;


# finí

if ($debug >= 5) {
  print Dumper(\%VarType);
  print Dumper(\%StaticType);
}

if ($debug >= 3) {
  print Dumper(\%Usage);
}




exit 0;



########### all subs here after ###########


sub connect {
  #
  # does what the name implies
  #
  my($db);
  if ($db_port) {
    $db = "DBI:$db_driver:$database;host=$db_host:$db_port";
  }
  else {
    $db = "DBI:$db_driver:$database;host=$db_host";
  }
  print "Connecting to \"$db\" as \"$db_user\" identified by \"$db_passwd\"\n" if ($debug >= 1);
  $DB = DBI->connect($db, $db_user, $db_passwd) or die DBI->errstr();
}




sub build_from_input {
  #
  # build %Usage base hash from user input
  # if there was any
  #

  # fill arrays
  @tables = split /,/, $table;

  if ($#tables != 0 && $columns) {
    print "option mismatch: you can only use --columns with one table!\n"
      ."use the \"--table=table.columnname\" notation instead.\n";
    exit 1;
  }
  elsif ($#tables == 0 && $columns && $tables[0] =~ /\./) {
    print "option mismatch: you cannot specify columns using the . notation\n"
      ."while --columns is specified.\n";
    exit 1;
  }
  elsif ($#tables == -1 && $columns) {
    print "option mismatch: you cannot specify columns while specifiying\n"
      ."no table(s)\n";
    exit 1;
  }
  else {
    # build %Usage table/columns based on user input
    if ($columns) {
      # consider $tables[0] as dot free, doh
      @columns = split /,/, $columns;
      my %columns = &lookup_columns($tables[0]);
      foreach my $_C (@columns) {
	die "Column $tables[0].$_C does not exist!\n" if(!$columns{$_C});
	$Usage{$tables[0]}->{columns}->{$_C} = { -usage => "", -type => $columns{$_C} };
      }
    }
    else {
      # ok, no $columns but one or more in @tables
      foreach my $_T (@tables) {
	%columns = ();
	my($_table, $_column) = split /\./, $_T, 2;
	if (!$_column || $_column eq "*") {
	  # ok, look @ all cols
	  %columns = &lookup_columns($_table);
	}
	else {
	  my %cols = &lookup_columns($_table);
	  die "Column $_table.$_column does not exist!\n" if(!$cols{$_column});
	  $columns{$_column} = $cols{$_column};
	}
	foreach my $_C (keys %columns) {
	  $Usage{$_table}->{columns}->{$_C} = { -usage => "", -type => $columns{$_C} };
	}
      }
    }
  }
}







sub build_types {
  #
  # generate the %*Types Hashes based on __DATA__
  #
  while (<DATA>) {
    chomp;
    next if(/^\s*#+/ || /^\s*$/);
    $_ =~ s/^(.+?)\s*#.*$/$1/;
    my($type, $size) = split /\s\s*=\s*/, $_, 2;
    if ($size !~ /^\d+$/) {
      #$size = "";
    }
    if ($type =~ /(char|blob|text)/i) {
      $VarType{$type}     = { -size => "", -add => $size };
    }
    else {
      $StaticType{$type}  = { -size => $size, -add => "" };
    }
  }
}



sub lookup_columns {
  #
  # show columns from $@[0]
  #
  my($table) = shift;
  my $sql = "SHOW COLUMNS FROM $table";
  print "$sql\n" if ($debug >= 2);
  my $hdl = $DB->prepare($sql) or die $DB->errstr;
  my $res = $hdl->execute or die $DB->errstr;
  return map { $_->[0] => uc($_->[1]) } @{$hdl->fetchall_arrayref};
}



sub lookup_tables {
  #
  # show tables; and &look_columns(table)
  # end up with global prebuild %Usage
  #

  # begin with the tables
  print "SHOW TABLES\n" if ($debug >= 2);
  my $hdl = $DB->prepare("SHOW TABLES") or die $DB->errstr;
  my $res = $hdl->execute or die $DB->errstr;
  my @tables = map {$_->[0]} @{$hdl->fetchall_arrayref};
  foreach my $table (@tables) {
    my %columns = &lookup_columns($table);
    foreach my $_C (keys %columns) {
      $Usage{$table}->{columns}->{$_C} = { -usage => "", -type => $columns{$_C} };
    }
  }
}



sub collect_bytes {
  #
  # collect and summarize all columns in each table in %Usage
  #
  my $db_usage;
  foreach my $table (keys %Usage) {
    my $table_usage;
    foreach my $column (keys %{$Usage{$table}->{columns}}) {
      my($usage, $width);
      my $type = $Usage{$table}->{columns}->{$column}->{-type};
      if ($type =~ /^(.*)\((.*)\)$/) {
	$type = $1;
	$width = $2; # useless IMHO
      }
      # now switch over known data types and gather storage usage
      if ($type =~ /(INT|DOUBLE|REAL|DATE|TIME|YEAR)/) {
	# fixed sized types
	$usage = &get_fixedsize($table, $column, $StaticType{$type}->{-size});
      }
      elsif ($type =~ /FLOAT/) {
	# special fixed sized types
	$usage = &get_fixedsize($table, $column, $StaticType{$type}->{-size});
	my($size,$decnum) = split /,/, $width;
	if ($size > 24) {
	  # -size is stored as 4 bytes so we double it to 8 bytes if larger than 24
	  # according to mysql documentation.
	  $usage += $usage;
	}
      }
      elsif ($type =~ /(CHAR|TEXT|BLOB)/) {
	# variable sized types
	$usage = &get_varsize($table, $column, $VarType{$type}->{-add} );
      }
      elsif ($type =~ /(ENUM|SET|DEC|NUMERIC)/) {
	# very special types, much more complicated to calculate
	# and therefore not supported (@ the moment only!)
	if ($type =~ /ENUM/) {
	  $usage = &get_enumsize($table, $column);
	}
	elsif ($type =~ /(NUMERIC|DEC)/) {
	  $usage = &get_decsize($table, $column, $width);
	}
	elsif ($type =~ /SET/) {
	  $usage = &get_setsize($table, $column, $width);
	}
      }
      else {
	die "Unknown data type found: \"$type\". Please report this bug to \"tom\@daemon.de\"!\n";
      }
      # store it!
      $Usage{$table}->{columns}->{$column}->{-usage} = $usage;
      $table_usage += $usage;
    }
    $Usage{$table}->{-usage} = $table_usage;
    $db_usage += $table_usage;
  }
  return $db_usage;
}



sub get_varsize {
  #
  # select sum(length(t2)+1) from phat;
  # and the like
  #
  my($table, $column, $fieldsize) = @_;
  $fieldsize = "0" if(!$fieldsize);
  my $sql = "SELECT SUM(LENGTH($column)+$fieldsize) FROM $table";
  print "$sql\n" if($debug >= 2);
  my $hdl = $DB->prepare($sql) or die $DB->errstr;
  my $res = $hdl->execute or die $DB->errstr;
  return $hdl->fetchrow_array;
}



sub get_fixedsize {
  #
  # select count(t1)*4 from phat;
  # and the like
  #
  my($table, $column, $size) = @_;
  my $sql = "SELECT COUNT($column)*$size FROM $table";
  print "$sql\n" if($debug >= 2);
  my $hdl = $DB->prepare($sql) or die $DB->errstr;
  my $res = $hdl->execute or die $DB->errstr;
  return $hdl->fetchrow_array;
}



sub get_enumsize {
  #
  # return emun type field size
  #
  my($table, $column) = @_;
  my $count = &get_rowcount($table, $column);
  return ($count <= 255 ? $count : ($count * 2));
}



sub get_rowcount {
  #
  # simply get the number of rows of a table
  #
  my($table, $column) = @_;
  my $sql = "SELECT COUNT($column) FROM $table";
  print "$sql\n" if($debug >= 2);
  my $hdl = $DB->prepare($sql) or die $DB->errstr;
  my $res = $hdl->execute or die $DB->errstr;
  return $hdl->fetchrow_array;
}



sub get_setsize {
  #
  # get size of enum object based of number of members
  # stored in $members
  #
  my ($table, $column, $members) = @_;
  my @members = split /,/, $members;
  my $count = $#members + 1;
  my $enum_count = &get_rowcount($table, $column);
  my $byte = ($count + 7) / 8;
  print "byte: $byte\n" if($debug >= 3);
  if ($byte <= 1) {
    return $enum_count;
  }
  elsif ($byte > 1 && $byte <= 2) {
    return 2 * $enum_count;
  }
  elsif ($byte > 2 && $byte <= 4) {
    return 4 * $enum_count;
  }
  elsif ($byte > 4) {
    return 8 * $enum_count;
  }
}



sub get_decsize {
  #
  # return size of decimal value
  #
  my($table, $column, $size) = @_;
  my($size, $decnum) = split /,/, $size;
  my $dec_count = &get_rowcount($table, $column);
  if ($size < $decnum) {
    # this should normally not be the case, since mysql does this for us,
    # but who knows ;-)
    $size = $decnum + 2;
  }
  return $dec_count * $size;
}


sub out {
  #
  # print data based on %Usage and $type
  # use perl format for formatting
  #
  my @data;
  if ($type eq "tables") {
    if ($rawmode) {
      foreach my $table ( sort keys %Usage) {
	print "$table ". $Usage{$table}->{-usage} . "\n";
      }
    }
    else {
      format STDOUT_TOP =
+----------------------+-----------------------+
| TableName            | Storage Usage (bytes) |
+----------------------+-----------------------+
.
;
      format STDOUT =
@ @<<<<<<<<<<<<<<<<<<< @ @>>>>>>>>>>>>>>>>>>>> @
@data
.
;
      my $bottom = "+----------------------+-----------------------+\n";
      foreach my $table ( sort keys %Usage) {
	@data = (
		 "|",
		 $table,
		 "|",
		 $Usage{$table}->{-usage},
		 "|",
		);
	write;
      }
      if ($type eq "all") {
	print $bottom;
	@data = ("|", "db: $database", "|", $db_usage, "|",);
	write;
      }
      print $bottom;
    }
  }
  elsif ($type eq "columns" || $type eq "all") {
    if ($rawmode) {
      foreach my $table (sort keys %Usage) {
	foreach my $column (sort keys %{$Usage{$table}->{columns}}) {
	  print "$table.$column ". $Usage{$table}->{columns}->{$column}->{-usage} . "\n";
	}
      }
    }
    else {
      format STDOUT_TOP =
+----------------------+-----------------------+
| TableName            | Storage Usage (bytes) |
+----------------------+-----------------------+
.
;
      format STDOUT =
@ @<<<<<<<<<<<<<<<<<<< @ @>>>>>>>>>>>>>>>>>>>> @
@data
.
;
      my $bottom = "+----------------------+-----------------------+\n";
      foreach my $table (sort keys %Usage) {
	foreach my $column (sort keys %{$Usage{$table}->{columns}}) {
	  @data = (
		   "|",
		   "$table.$column",
		   "|",
		   $Usage{$table}->{columns}->{$column}->{-usage},
		   "|",
		  );
	  write;
	}
      }
      if ($type eq "all") {
	print $bottom;
	@data = ("|", "db: $database", "|", $db_usage, "|",);
	write;
      }
      print $bottom;
    }
  }
  elsif ($type eq "sum") {
    if ($rawmode) {
      print "$database $db_usage\n";
    }
    else {
      print "Database \"$database\" uses $db_usage bytes storage.\n";
    }
  }
  else {
    print "Unknown output type: \"$type\"! Possible values are:\n"
         ."all, sum, columns or tables\n";
    exit 1;
  }
}





# do not edit the line below
1;


__DATA__
### 7.3.2 Numeric types ########
TINYINT		 =	1
SMALLINT	 =	2
MEDIUMINT	 =	3
INT		 =	4
INTEGER		 =	4
BIGINT		 =	8
FLOAT		 =	4       # 4 if X <= 24 or 8 if 25 <= X <= 53  => Caution!
DOUBLE		 =	8
DOUBLE PRECISION =	8
REAL		 =	8


### 7.3.3 Date and time types ###
DATE		 =	3
DATETIME	 =	8
TIMESTAMP	 =	4
TIME		 =	3
YEAR		 =	1


### 7.3.4 String types ##########
# the number is the additor here! not the size!
CHAR		 =		# M bytes, 1 <= M <= 255 
VARCHAR		 =	1	# L+1 bytes, where L <= M and 1 <= M <= 255 
TINYBLOB	 =	1	# L+1 bytes, where L < 2^8
TINYTEXT	 =	1	# L+1 bytes, where L < 2^8
BLOB		 =	2	# L+2 bytes, where L < 2^16
TEXT		 =	2	# L+2 bytes, where L < 2^16
MEDIUMBLOB	 =	3	# L+3 bytes, where L < 2^24
MEDIUMTEXT	 =	3	# L+3 bytes, where L < 2^24
LONGBLOB	 =	4	# L+4 bytes, where L < 2^32
LONGTEXT	 =	4	# L+4 bytes, where L < 2^32

ENUM		 =	0	# 1 or 2 bytes, depending on the number of enumeration values (65535 values maximum) 
SET		 =	0	# 1, 2, 3, 4 or 8 bytes, depending on the number of set members (64 members maximum) 
DECIMAL		 =		# DECIMAL(M,D) = M bytes (D+2, if M < D) => Caution! 
DEC		 =		# DECIMAL(M,D) = M bytes (D+2, if M < D) => Caution!
NUMERIC		 =		# NUMERIC(M,D) = M bytes (D+2, if M < D) => Caution!



