#!perl

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

use Win32::ODBC;
use strict;
no warnings;

my $progress_interval = 100;

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 ($db) = new Win32::ODBC($dsn);
	my @tables;
	if (ref $D{$dsn} eq 'ARRAY') {
		@tables = @{$D{$dsn}};
		print "Selected ";
	}
	else {
		@tables = $db->TableList;
		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";
		$db->sql($sql);
		$db->FetchRow();
		my ($count) = $db->Data();
		print "Table has $count rows\n";
		next unless $count >= 1;

		$sql = "SELECT * FROM $table_q";
		print "Query: $sql\n";
		$db->sql($sql);
		my @fields = $db->FieldNames;
		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 ($db->FetchRow()) {
			my @row = $db->Data();
			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";
	}
}

print "All done\n";


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