#! /usr/bin/env perl

# dbs_dumptabdata - creates file set with SQL table data

# This script queries a SQL database and creates one file for each
# table in the database containing the data of the table.

# Copyright (C) 1999-2012 Stefan Hornburg

# Author: Stefan Hornburg (Racke) <racke@linuxia.de>
# Maintainer: Stefan Hornburg (Racke) <racke@linuxia.de>
# Version: 0.19

# This file 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; either version 2, or (at your option) any
# later version.

# This file 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 file; see the file COPYING.  If not, write to the Free
# Software Foundation, 675 Mass Ave, Cambridge, MA 02139, USA.

use strict;
use warnings;

use DBIx::Easy;
use FindBin;
use Getopt::Long;
use Term::ReadKey;

# process commandline parameters
my %opts;
my $printtabopts = '';
my $whandler = $SIG{__WARN__};
$SIG{__WARN__} = sub {print STDERR "$0: @_";};
unless (GetOptions (\%opts, 'exclude-matching-tables=s',
					'sort=s',
					'tables|t=s',
                    'use-printtab')) {
    exit 1;
}
$SIG{__WARN__} = $whandler;

# sanity check on commandline parameters
if (exists $opts{'exclude-matching-tables'}) {
    eval {'' =~ /$opts{'exclude-matching-tables'}/;};
    if ($@) {
        $@ =~ s%at /.*$%%;
        die "$0: $@";
    }
}

my $dbif;
my $pwdused = 0;
my $pwd;
my @tables;

my ($driver, $database, $user) = @ARGV;
$dbif = new DBIx::Easy ($driver, $database, $user);

# handler for DBI error messages and missing password
$dbif -> install_handler (\&fatal);

# we need to explicitly establish the connection
# for the case that a password is needed
$dbif -> connect;

# customize dump procedure
my $dumpproc;

if ($opts{'use-printtab'}) {
	# generic dump procedure
	$dumpproc = sub {
		my ($database, $table, $user) = @_;
        
        unless (defined $user) {$user = ''};
		print $dbif->view ($table);
	}
} else {
	# dump procedure provided by DBMS
	if ($driver eq 'mysql') {
		$dumpproc = sub {
			my ($database, $table) = @_;
    
			my $header;
            my $optstr = '';

            if ($dbif -> {USER}) {
                $optstr = " -u " . $dbif -> {USER};
            }
            if ($dbif -> {HOST}) {
                $optstr .= " -h " . $dbif -> {HOST};
            }
            if (defined $pwd) {
                $optstr .= " -p";
            }
			# -c: Use complete insert statements.
			open (DUMP, "mysqldump -t -c $optstr $database $table |")
				|| die ("$0: Couldn't launch mysqldump: $!\n");
			while (<DUMP>) {
				if ($header) {
					print;
					next;
				}
				# skip header
				$header = 1 unless (/\S/);
			}
			close (DUMP)
				|| die ("$0: mysqldump execution errors\n");
		}
	} elsif ($driver eq 'Pg') {
		$dumpproc = sub {
			my ($database, $table) = @_;

			# -D => dump data as inserts with attribute names
			open (DUMP, "pg_dump $database -a -D -t $table |")
				|| die ("$0: Couldn't launch pg_dump: $!\n");
			while (<DUMP>) {
				print;
			}
			close (DUMP)
				|| die ("$0: pg_dump execution errors\n");
		}
	}    
}

if ($opts{'tables'}) {
    @tables = split (/,/, $opts{'tables'});
} else {
    @tables = $dbif->tables ();
}

foreach my $table (@tables) {
    my $outfile = lc($table) . '.sql';

    # skip tables unwanted by user
    if (exists $opts{'exclude-matching-tables'}) {
        next if $table =~ /$opts{'exclude-matching-tables'}/;
    }
    # call driver dependent dump procedure and fill
    # output file with the results
    open (OUT, ">$outfile") || die ("$0: Couldn't open $outfile: $!\n");
    select (OUT);
    &$dumpproc ($database, $table, $user);
    close (OUT);
}

# Destroy Database Object as written in manpage
undef $dbif;

# -----------------------------------
# FUNCTION: fatal
#
# Error handler called by DBIx::Easy.
# -----------------------------------

sub fatal {
  my ($statement, $err, $msg) = @_;

  if ($dbif->is_auth_error ($err)) {
    unless ($pwdused) {
      print "We need a password.\n";
      $pwd = querypwd();
      $pwdused = 1;
    
      # retry the connection
      if (length ($pwd)) {
        $dbif = new DBIx::Easy ($driver, $database, $user, $pwd);
        $dbif -> install_handler (\&fatal);
        $dbif -> connect ();
        return;
      } else {
        die ("$statement.\n");
      }
    }
  }
  die ("$statement.\n");
}

# ----------------------------
# FUNCTION: querypwd
#
# Queries user for a password.
# ----------------------------

sub querypwd () {
  my $pwd;

  print "Password: ";
  ReadMode ('noecho');  # turn echo off
  $pwd = ReadLine (0);
  ReadMode ('restore'); # restore terminal
  print "\n";
  chomp ($pwd);
  $pwd;
}

=head1 NAME

dbs_dumptabdata - Creates file set with SQL table data

=head1 DESCRIPTION

dbs_dumptabdata is an utility to create a file set with SQL table data.
For each table in the database dbs_dumptabdata calls the appropriate dumper
utility with the output directed to a file named I<table>.sql in the
current directory. dbs_dumptabdata asks for a password if necessary.

=head1 COMMAND LINE PARAMETERS

Required command line parameters are the DBI driver
(C<Pg> for Postgres or C<mysql> for MySQL)
and the database name. The third parameter is optionally
and specifies the database user and/or the host where the
database resides (C<racke>, C<racke@linuxia.de> or C<@linuxia.de>).

=head1 COMMAND LINE OPTIONS

=head2 B<-t> TABLE[,TABLE,...], B<--tables>=TABLE[,TABLE,...]

Comma-separated list of tables to dump.

=head2 B<--exclude-matching-tables>=REGEXP

Excludes any table matching the regular expression REGEXP from dumping.

=head2 B<--use-printtab>

Uses generic dump (similar to C<dbs_printtab>) instead of the dumper
utility provided by the DBMS.

=head1 BUGS

msql is not supported.

=head1 AUTHOR

Stefan Hornburg (Racke), racke@linuxia.de

=head1 SEE ALSO

perl(1), DBIx::Easy(3)

=cut