#!/usr/bin/perl
# dbtabsave.pl -- Sichern von Daten aus der Datenbank
#
# Erstellt: 18.11.2004 - Alfred Mickautsch
# Geändert: 30.11.2004 - Alfred Mickautsch - Kopfinformation angepaßt und Fehlerbehebung
# Geändert: --.--.---- - ----------------- - ------------------------------------
#
# Aufrufbeispiele:
# 1. SQL Server mit ODBC
# dbtabsave.pl -D SqlServer -r ODBC -c "Driver={SQL Server};Server=sponde;Database=C033_DEV1" -s dbo -u sa -p sa -d test_sqlserver -i T065_RULE
# 2. SQL Server mit ADO über ODBC
# dbtabsave.pl -D SqlServer -r ADO -c "Provider=MSDASQL;Driver={SQL Server};Server=sponde;Database=C033_DEV1" -s dbo -u sa -p sa -d C028_DEV -i T065_RULE# dbtabsave.pl -D SqlServer -r ODBC -c "Driver={SQL Server};Server=sponde;Database=C033_DEV1" -s dbo -u sa -p sa -d test_sqlserver -i T065_RULE
# 3. SQL Server mit ADO
# dbtabsave.pl -D SqlServer -r ADO -c "Provider=SQLOLEDB;Data Source=sponde;Initial Catalog=C033_DEV1" -s dbo -u sa -p sa -d C028_DEV -i T065_RULE
# 4. DB2
# dbtabsave.pl -D DB2 -r DB2 -c coe -s INSTCOEP -u coe -p coe -d C028_DEV -i T065_RULE
# 5. Oracle
# dbtabsave.pl -D Oracle -r Oracle -c coe -s C028_DEV -u coe -p coe -d C028_DEV -i T065_RULE

use strict;
use DBI;
use DBD::DB2 qw( $attrib_dec $attrib_int $attrib_char $attrib_float $attrib_date $attrib_ts $attrib_binary $attrib_blobfile $attrib_clobfile $attrib_dbclobfile );
use DBD::DB2::Constants;
use Getopt::Long;

use vars '$CONFIG';

my $VERSION = '1.2';
$CONFIG =
{
	'db' =>
	{
		'savdir'  => 'COE',
		'driver'  => 'DB2',
		'database'  => 'DB2',
		'connect' => 'COE',
		'schema'  => 'COE',
		'user'    => 'coe',
		'pass'    => 'coe',
#		'loblen'  => 10000000,
		'loblen'  => 100000,
		'tables'  => [],
		'exclude' => [],
	},
	'xml' =>
	{
		'version'  => '1.0',
		'encoding' => 'ISO-8859-1',
		'standalone' => undef,
	},
	'appl' =>
	{
		'verbose' => 0,
	},
};

my @DATABASES = qw(Oracle DB2 SqlServer);

my $prog;
my %options;


main();


sub main
{
	$prog = $0;
	$prog =~ s/^.*\/|^.*\\//g;

	help() if not init_options();

	$CONFIG->{'db'}->{'savdir'} =~ s/\\/\//g;
	$CONFIG->{'db'}->{'savdir'} =~ s/\/$//;

	mkdirhier($CONFIG->{'db'}->{'savdir'}) unless -d $CONFIG->{'db'}->{'savdir'};
	Save($CONFIG);
}

sub Save
{
	my($cfg) = @_;
	my $tables;

	my $dbh = DBI->connect("dbi:$cfg->{'db'}->{'driver'}:$cfg->{'db'}->{'connect'}", $cfg->{'db'}->{'user'}, $cfg->{'db'}->{'pass'}) or
		die "Unable to connect to database '$cfg->{'db'}->{'connect'}: $DBI::errstr\n";

	$dbh->{'AutoCommit'} = 0;
	$dbh->{'LongReadLen'} = $cfg->{'db'}->{'loblen'};
	$dbh->{'LongTruncOk'} = 1;
	$dbh->{'PrintError'} = 1;
	$dbh->{'RaiseError'} = 1;
	if($cfg->{'db'}->{'database'} =~ /Oracle/i)
	{
		my $sql = "alter session set NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'";

		eval
		{
			my $sth = $dbh->prepare($sql);
			my $rv = $sth->execute();
			my $rc = $sth->finish();
		};
		print $@ if $@;
	}


	# All table names go into array $tables
	$tables = $cfg->{'db'}->{'tables'};
	$tables = GetTableNamesFromDB($cfg, $dbh) if @$tables == 0;

	$tables = remove_excludes($tables, $cfg->{'db'}->{'exclude'});

	DoSave($cfg, $dbh, $cfg->{'db'}->{'schema'}, $tables);

	$dbh->rollback();
	$dbh->disconnect();
}

sub GetTableNamesFromDB
{
	my ($cfg, $dbh) = @_;
	my $sql;
	my $rows;
	my $tables;

	if($cfg->{'db'}->{'database'} =~ /Oracle/i)
	{
		$sql = "select TABLE_NAME from ALL_TABLES where OWNER = UPPER('$cfg->{'db'}->{'schema'}') order by 1";
	}
	elsif($cfg->{'db'}->{'database'} =~ /DB2/i)
	{
		$sql = "select TABNAME from SYSCAT.TABLES where TABSCHEMA = UPPER('$cfg->{'db'}->{'schema'}') order by 1";
	}
	elsif($cfg->{'db'}->{'database'} =~ /SqlServer/i)
	{
		$sql = "select a.name from sysobjects a, sysusers b where a.xtype = 'U' and b.name = 'dbo' and a.uid = b.uid order by 1";
	}

	eval
	{
		my $sth = $dbh->prepare($sql);
		my $rv = $sth->execute();
	
#       DBI::dump_results($sth);
	
		# All table names go into array $tables
		$rows = $sth->fetchall_arrayref();

		my $rc = $sth->finish();
	};
	print $@ if $@;

	foreach my $item (@$rows)
	{
		push @$tables, $item->[0];
	}

	return $tables;
}

sub DoSave
{
	my ($cfg, $dbh, $schema, $tables) = @_;
	my $tabfile;

	$tabfile = $cfg->{'db'}->{'savdir'} . "/tables.txt";
	open(TABFILE, ">$tabfile")  or die "Fehler beim Öffnen der Datei '$tabfile' im Schreib-Modus: $!\n";
	binmode(TABFILE);
	
	foreach (@$tables)
	{
		my $rows;
		my $savefile;

		{
			local $| = 1;
			print "$_: ";
		}

		print TABFILE "$_\n";

		$savefile = $cfg->{'db'}->{'savdir'} . "/$_.xml";
		open(SAVEFILE,">$savefile") or die "Fehler beim Öffnen der Datei '$savefile' im Schreib-Modus: $!\n";
		binmode(SAVEFILE);

		WriteHeaderToFile(\*SAVEFILE, $cfg->{'xml'}->{'version'}, $cfg->{'xml'}->{'encoding'}, $cfg->{'xml'}->{'standalone'}, $VERSION,
							$cfg->{'db'}->{'driver'}, $cfg->{'db'}->{'connect'}, $cfg->{'db'}->{'schema'}, $cfg->{'db'}->{'user'});

		print SAVEFILE "<TABLE name=\"$_\">\n";

		$rows = SaveRowsFromDB($cfg, $dbh, \*SAVEFILE, $schema, $_);

		print SAVEFILE "</TABLE>\n";
		print SAVEFILE "</COEDATA>\n";
		print "$rows rows\n";

		close(SAVEFILE);
	}

	close(TABFILE);
	print "OK\n";
}

sub SaveRowsFromDB
{
	my ($cfg, $dbh, $SAVEFILE, $schema, $table) = @_;
	my $i;

	my $row;
	my $sql;
	my $sth;
	my $rc;
	my $cols;
	my $types;
	my $count;

	$count = 0;

	$sql = "select * from $schema.$table";
	eval
	{
		$sth = $dbh->prepare($sql);
		$rc = $sth->execute();
	};
	print $@ if $@;

	$cols = $sth->{NAME};
	$types = $sth->{TYPE};

	print $SAVEFILE "<FIELDS>\n";
	for($i = 0;$i < @$cols;$i++)
	{
		print $SAVEFILE "<FIELD id=\"$i\" name =\"$cols->[$i]\" type=\"$types->[$i]";
		eval
		{
			my $tinfo;
			my $catalog;
			my $col_sth;
			my $coldesc;
	
			$tinfo = $dbh->type_info($types->[$i]);
	
			$catalog = undef;
			$col_sth = $dbh->column_info($catalog, $schema, $table, $cols->[$i]);
			$coldesc = $col_sth->fetchrow_hashref();
			$col_sth->finish();

			print $SAVEFILE "\" type_name=\"";
			print $SAVEFILE $tinfo->{'TYPE_NAME'};
			print $SAVEFILE "\" sql_type=\"";
			print $SAVEFILE $tinfo->{'SQL_DATA_TYPE'};
			print $SAVEFILE "\" length=\"";
			print $SAVEFILE $coldesc->{'COLUMN_SIZE'};
			print $SAVEFILE "\" precision=\"";
			print $SAVEFILE $coldesc->{'DECIMAL_DIGITS'};
			print $SAVEFILE "\" nullable=\"";
			print $SAVEFILE $coldesc->{'IS_NULLABLE'};
		};
		print $@ if $@;
		print $SAVEFILE "\"></FIELD>\n";
	}
	print $SAVEFILE "</FIELDS>\n";
	print $SAVEFILE "<ROWS>\n";

	eval
	{
		while($row = $sth->fetchrow_arrayref())
		{
			last if $row == undef;
		
			print $SAVEFILE "<ROW id=\"$count\">\n";
			$count++;
	
			if($cfg->{'appl'}->{'verbose'})
			{
				my $num = "$count";
				print STDERR $num, "\b" x length($num);
			}	
	
			for($i = 0; $i < @$row; $i++)
			{
				print $SAVEFILE "<FIELD id=\"$i\" null=";
				print $SAVEFILE (defined $row->[$i])? "\"no\"": "\"yes\"";
				print $SAVEFILE '>';
	
				if(defined $row->[$i])
				{
					if(($types->[$i] == -98) or ($types->[$i] == -99) or ($types->[$i] == -1) or ($types->[$i] == -4))
					{
						my $file;
	
						$file = $cfg->{'db'}->{'savdir'} . "/$table\_$i\_$count.lob";
						open(LOBOUT, ">$file") or die "error at open \"$file\" for writing: $!\n";
						binmode LOBOUT;
						print LOBOUT $row->[$i];
						close LOBOUT;
						$row->[$i] = "$table\_$i\_$count.lob";
					}
		
					$row->[$i] =~ s/\&/\&amp;/g;
					$row->[$i] =~ s/</\&lt;/g;
					$row->[$i] =~ s/>/\&gt;/g;
		
					print $SAVEFILE "$row->[$i]";
				}
				print $SAVEFILE "</FIELD>\n";
			}
			print $SAVEFILE "</ROW>\n";
		}
		$rc = $sth->finish;
	};
	print $@ if $@;
	print $SAVEFILE "</ROWS>\n";

	return $count;
}

sub WriteHeaderToFile
{
	my ($SAVEFILE, $xmlversion, $encoding, $standalone, $coeversion, $driver, $db, $schema, $user) = @_;
	my $timestamp;
	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime();

	$year += 1900;
	$mon += 1;
	$hour += $isdst;
	$timestamp = sprintf("%04d-%02d-%02d %02d:%02d:%02d",$year,$mon,$mday,$hour,$min,$sec);
	
	print $SAVEFILE '<?xml version="', $xmlversion , '" encoding="' , $encoding, '"';
	print($SAVEFILE ' standalone="', $standalone, '"') if defined $standalone;
	print $SAVEFILE '?>', "\n";
	print $SAVEFILE "<COEDATA version=\"$coeversion\" dbms=\"$driver\" database=\"$db\" schema=\"$schema\" user=\"$user\" date=\"$timestamp\">\n";
	print $SAVEFILE '<!--', "\n";
	print $SAVEFILE "##############################\n";
	print $SAVEFILE "# VERSION: $coeversion\n";
	print $SAVEFILE "# DBMS:    $driver\n";
	print $SAVEFILE "# DB:      $db\n";
	print $SAVEFILE "# SCHEMA:  $schema\n";
	print $SAVEFILE "# USER:    $user\n";
	print $SAVEFILE "# DATE:    $timestamp\n";
	print $SAVEFILE "##############################\n";
	print $SAVEFILE '-->', "\n";
}

sub init_options
{
	Getopt::Long::Configure ("bundling");
	GetOptions(
		\%options,
		'help|h|?' => \&help,
		'version|V' => \&version,
		'verbose|v!',
		'schema|s=s',
		'tabledir|d=s',
		'connect|c=s',
		'user|u=s',
		'pass|p=s',
		'database|D=s',
		'dbdriver|r=s',
		'config|f=s',
		'include|i=s@',
		'exclude|x=s@',
		'infile|I=s',
		'exfile|X=s',
		'maxloblen|m=i');

	read_options_from_file($options{'config'}) if defined $options{'config'};

	read_tables_from_file($CONFIG->{'db'}->{'tables'}, $options{'infile'}) if defined $options{'infile'};
	read_tables_from_file($CONFIG->{'db'}->{'exclude'}, $options{'exfile'}) if defined $options{'exfile'};

	push(@{$CONFIG->{'db'}->{'tables'}}, @{$options{'include'}}) if defined $options{'include'};
	push(@{$CONFIG->{'db'}->{'exclude'}}, @{$options{'exclude'}}) if defined $options{'exclude'};

	$CONFIG->{'db'}->{'savdir'} = $options{'tabledir'} if defined $options{'tabledir'};
	$CONFIG->{'db'}->{'driver'} = $options{'dbdriver'} if defined $options{'dbdriver'};
	$CONFIG->{'db'}->{'database'} = $options{'database'} if defined $options{'database'};
	$CONFIG->{'db'}->{'database'} = $options{'dbdriver'} if !defined $CONFIG->{'db'}->{'database'};

	if(!grep /$CONFIG->{'db'}->{'database'}/i, @DATABASES)
	{
		print "Database \"$CONFIG->{'db'}->{'database'}\" not supported\n";
		return 0;
	}

	$CONFIG->{'db'}->{'connect'} = $options{'connect'} if defined $options{'connect'};
	$CONFIG->{'db'}->{'schema'} = $options{'schema'} if defined $options{'schema'};
	$CONFIG->{'db'}->{'user'} = $options{'user'} if defined $options{'user'};
	$CONFIG->{'db'}->{'pass'} = $options{'pass'} if defined $options{'pass'};
	$CONFIG->{'db'}->{'loblen'} = $options{'maxloblen'} if defined $options{'maxloblen'};
	$CONFIG->{'appl'}->{'verbose'} = $options{'verbose'} if defined $options{'verbose'};

	if($CONFIG->{'appl'}->{'verbose'})
	{
		local $| = 1;

		foreach my $key (keys %$CONFIG)
		{
			print "$key:\n";
			foreach(keys %{$CONFIG->{$key}})
			{
				print "\t$_";
				if(ref $CONFIG->{$key}->{$_})
				{
					print ":\n";
					foreach my $item (@{$CONFIG->{$key}->{$_}})
					{
						print "\t\t\"$item\"\n";
					}
				}
				else
				{
					print " = \"$CONFIG->{$key}->{$_}\"\n";
				}
			}
		}
	}
	return scalar keys %options;
}

sub read_options_from_file
{
	my($cfgfile) = @_;
	my $return;

	unless($return = do $cfgfile)
	{
		warn "$0: read_options_from_file: couldn't parse '$cfgfile': $@" if $@;
		warn "$0: read_options_from_file: couldn't do '$cfgfile': $!"    unless defined $return;
		warn "$0: read_options_from_file: couldn't run '$cfgfile'"       unless $return;
		die "$0: can't continue\n";
	}

	# Fehlende Parameter in $CONFIG eintragen
#	foreach(keys %$config)
#	{
#		$cfg->{$_} = $config->{$_} if !defined $cfg->{$_};
#	}
}

sub read_tables_from_file
{
	my($tables, $file) = @_;

	open(IN, "<$file") or die "Fehler beim Öffnen der Datei \"$file\" zum Lesen: $!n"; 

	@$tables = <IN>;
	chomp(@$tables);

	close(IN);

	return scalar @$tables;
}

sub remove_excludes
{
	my($tables, $exclude) = @_;
	my %seen;
	my @diff;

	@seen{@$exclude} = ();

	foreach my $item (@$tables)
	{
		push(@diff, $item) unless exists $seen{$item};
	}

	return \@diff;
}

sub mkdirhier
{
	my($directory, $mode) = @_;
    my @dirs;
    my $path;
    my $result;

	$directory =~ s/\\/\//g;
	@dirs = split(/\//, $directory);
	$path = shift(@dirs);   # build it as we go
	$result = 1;   # assume it will work

	$result &&= mkdir($path, $mode) unless -d $path;

	foreach(@dirs)
	{
		$path .= "/$_";

		if(! -d $path)
		{
			$result &&= mkdir($path, $mode);
		}
	}

	return $result;
}

#misc
sub help
{
	print "Aufruf: $prog <Optionen>\n";
	print "<Optionen> =\n";
	print "\t--database=DATENBANK, -D DATENBANK:\tDatenbank (Oracle, DB2, SqlServer)\n";
	print "\t--dbdriver=DATENBANKTREIBER, -r DATENBANKTREIBER:\tDBI Datenbanktreiber (Oracle, DB2, ADO, ODBC)\n";
	print "\t--connect=DBALIAS, -c DBALIAS:\tDatenbankalias\n";
	print "\t--schema=SCHEMA, -s SCHEMA:\tDatenabnkschema\n";
	print "\t--user=BENUTZER, -u BENUTZER:\tBenutzer\n";
	print "\t--pass=PASSWORT, -p PASSWORT:\tPasswort\n";
	print "\t--maxloblen=ZAHL, -m ZAHL:\tMaximale Größe eines LOBs\n";
	print "\t--tabledir=VERZEICHNIS, -d VERZEICHNIS:\Sicherungsverzeichnis\n";
	print "\t--config=DATEI,-f DATEI:\tKonfiguration aus Datei DATEI lesen\n";
	print "\t--include=NAME, -i NAME:\tName der Tabelle\n";
	print "\t--exclude=NAME, -x NAME:\tTabelle, die nicht gesichert werden soll\n";
	print "\t--infile=DATEI, -I DATEI:\tListe der Tabellen aus DATEI lesen\n";
	print "\t--exfile=DATEI, -X DATEI:\tListe der Tabellen, die nicht gesichert werden sollen\n";
	print "\t--help, -h, -?:\tdiese Hilfe\n";
	print "\t--version, -V:\tVersionsinfo ausgeben\n";
	print "\t--verbose, -v:\tMehr Daten ausgeben\n";
	exit(0);
}

sub version
{
	print "$prog V$VERSION -- (c) 2004 schuler business solutions AG\n";
}