#!/usr/bin/perl

# odbc_dump_dbi.pl
#
# dumps ODBC-accessible database tables to tab-delimited text files
# using DBI and DBD::ODBC
#
# by Jon Jensen <jon@redhat.com>
# 2002-07-16
# 2002-08-01

use DBI;
use strict;
no warnings;

my $progress_interval = 100;
my $long_read_len = 1_000_000;

if (! @ARGV or grep /^--?(?:h(?:elp)?|\?)$/, @ARGV) {
	print STDERR <<EOF;
Usage: access_dump.pl --dsn=DBname [--dsn=DBname2 ...]
       access_dump.pl --dsn=DBname table1 table2 [--dsn=DBname2 ...]
EOF
	exit 1;
}

my (%D, @D);
my $d;
for (@ARGV) {
	/^--dsn=(.+)/i and $d = $1 and push @D, $d and next;
	die "No DSN specified!\n" if ! $d;
	push @{$D{$d}}, $_;
}

print "Processing " . @D . " DSN" . plural(\@D) . "\n";

for my $dsn (@D) {
	print "\n* * *\n\nConnecting to DSN $dsn\n";
	my $dbh = DBI->connect("dbi:ODBC:$dsn");
	$dbh->{LongReadLen} = $long_read_len;
	$dbh->{LongTruncOk} = undef;
	$dbh->{PrintError} = undef;
	my @tables;
	if (ref $D{$dsn} eq 'ARRAY') {
		@tables = @{$D{$dsn}};
		print "Selected ";
	}
	else {
		@tables = $dbh->tables;
		print "Found ";
	}
	print @tables . " table" . plural(\@tables) . "\n";

	for my $table (@tables) {
		print "\nDumping table $table\n";
		my $sql;
		my $table_q = $table;
		$table_q =~ s/"/""/g;
		$table_q = qq{"$table_q"} if $table_q =~ /\W/;
		$sql = "SELECT COUNT(*) FROM $table_q";
		my ($count) = $dbh->selectrow_array($sql);
		print "Table has $count rows\n";
		next unless $count >= 1;

		$sql = "SELECT * FROM $table_q";
		print "Query: $sql\n";
		my $sth = $dbh->prepare($sql);
		my $rv = $sth->execute;
		unless ($rv) {
			print "Error executing statement: " . $dbh->errstr . "\n";
			next;
		}
		my @fields = @{$sth->{NAME}};
		print "Found " . @fields . " field name" . plural(\@fields) . "\n";

		my $file = "$table.tab";
		-d $dsn or mkdir $dsn;
		-d $dsn and $file = "$dsn/$file";
		print "Writing to file $file\n";
		open OUT, "> $file" or die "Couldn't open $file for writing: $!\n";
		print OUT join("\t", @fields), "\n";

		my $i = 0;
		while (my $row = $sth->fetch) {
			for (@$row) {
				s/\x0d\x0a/\x0d/g;
				s/\x0a//g;
				s/\t/ /g;
			}
			print OUT join("\t", @$row), "\x0a";
			++$i;
			print "Processed $i rows\n" if $i % $progress_interval == 0;
		}

		close OUT or die "Error closing $file: $!\n";
	}
	$dbh->disconnect;
}

print "All done\n";


sub plural { @{$_[0]} == 1 ? '' : 's' }