#!/usr/bin/perl -wT

# dump_mysql.pl v.0.3
# 
# 2002/04/12
# - updated to fix a duplicate my $dsn and
#   put a semi-colon at end of SHOW TABLE statement
#   as suggested by crazyinsomniac
#
# 2002/03/07 
# - updated to use Getopt::Long as suggested by gmax
#
# To do/wishlist:
#   unbuffer STDIN so password is hidden when entered
#   implement other flags that are also used by the 'mysqldump' utility
#   make use of default mysql config file as also suggested by gmax
#   use Pod::Usage that can produce help messages/info for 
#    various options now in this script
#

use strict;
use DBI;
use Getopt::Long;

my %dbinfo = (
                'username'      => 'dbuser',
                'password'      => 'dbpass',
                'hostname'      => 'localhost',
                );

#  END of configurable options

$dbinfo{database} = shift @ARGV;

unless( defined($dbinfo{database}) ){
        die &usage($0);
}

my ($verbose, $drop_table, $opt, $username, $password, $hostname, 
	$complete, $lock, $extended, $nodata, $nocreate);

GetOptions (
	'opt' => \$opt,
	'add-drop-table' => \$drop_table,
	'c|complete-insert' => \$complete,
#	'e|extended-insert' => \$extended,
	'add-locks' => \$lock,
	'u|username=s' => \$username,
#	'p|password=s' => \$password,
	'h|host=s' => \$hostname,
	'd|no-data' => \$nodata,
	't|no-create-info' => \$nocreate,
	);


my @tables = @ARGV;

unless( defined($dbinfo{database}) ){
	die &usage($0);
}

unless($hostname){ $hostname = 'localhost'; }
unless( defined($password) ){
	print STDERR "Password: ";
	chomp($password = <STDIN>);
}

my ($dsn) = sprintf("DBI:mysql:%s:%s", $dbinfo{database}, $dbinfo{hostname});

my $dbh = DBI->connect(
		$dsn, 
		$username, 
		$password, 
		{
			RaiseError => 1,
			AutoCommit => 0,
		}
		) or die $DBI::errstr;

my ($sth, %userdata, $sql, $table, $listing, %listing_hash, $colnames);

unless( scalar(@tables) > 0){
        @tables = @{GetTableList($dbh)};
}

foreach $table (@tables){
	my $row;

	if(!$nocreate || $opt){
		print "\n#\n# Table structure for table '$table'\n#\n\n";
		if($drop_table || $opt){
			print "\nDROP TABLE IF EXISTS $table;\n";
		}
		print $dbh->selectall_arrayref("SHOW CREATE TABLE $table;")->[0][1],"\n\n";
	}

	if(!$nodata || $opt){
		print "\n#\n# Dumping data for table '$table'\n#\n\n";

		if($lock || $opt){ print "LOCK TABLES $table WRITE;\n"; }

		$sth = $dbh->prepare("SELECT * FROM $table");
		$sth->execute();

		while( $row = $sth->fetchrow_hashref ){
			my $insert;
			$insert = GetInsertSQL($row, $dbh, $table);
			print $insert, ";\n";
		}

		if($lock || $opt){ print "UNLOCK TABLES;\n"; }
		$sth->finish();
	}
}
$dbh->disconnect();

exit;

sub GetInsertSQL{
	my ($data, $dbh, $table) = @_;
	my ($sql, $cols, $col);

	$cols = GetTableCols($dbh, $table);
	$sql = "INSERT INTO $table ";
	if($complete){
		$sql.= "( ";
		$sql.= join(', ', @$cols);
		$sql.= " ) ";
	}
	$sql.= "VALUES ( ";
	foreach $col (@$cols){
		$sql.= ' '.$dbh->quote( ${$data}{$col} );
		$sql.= ',';
	}
	$sql =~ s/\,$//; # remove trailing comma
	$sql.= " )";
}

sub GetTableCols{
        my ($dbh, $table) = @_;
        my ($rs);
        $rs = $dbh->selectcol_arrayref("DESCRIBE $table");
        return $rs;
}

sub GetTableList{
        my ($dbh) = @_;
        my ($rs, $sql);
        $rs = $dbh->selectcol_arrayref("SHOW TABLES");
        return $rs;
}

sub usage{
        my ($cmd) = @_;
        die "Usage:\n" .
        " $cmd [options] [database] [tables...]\n" .
        " Example:\n" .
        " $cmd --opt -u userfoo -p phpbb messages users > mybackup.sql\n" .
        " Try redirecting it to a file, as shown above.\n\n";
}