import changedb

import.pl

#!perl

# import data from master to changedb
# changedb implements columns for filenumber, filedate and updateflag

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

my $dbh;
our($source_db, $dest_db, $base_fd, $base_fn, $base_uf, $drop_source, @exclude, $logfile, $help);
$logfile = 'import.log';

# filedate/filenum/updateflag defaults for seed
$base_fd = 20120620;
$base_fn = 35;
$base_uf = 'A';

# get options, start db conn, etc
init();

# get table names
my $tables_q = $dbh->prepare('select table_name from information_schema.tables');
$tables_q->execute();
my $tables_aref = $tables_q->fetchall_arrayref();
$tables_q->finish();

my $num_tables = scalar @{$tables_aref};
printp("found $num_tables tables\n\n");

my $tables_count = 0;
foreach my $table_aref (@{$tables_aref}) {
	my $table = @{$table_aref}[0];
	printp("$table\n");
	
	# don't import excluded tables
	foreach my $pattern (@exclude) {
		if ($table =~ qr($pattern)) {
			printp("\texcluding table, match: $pattern\n");
			drop_table($table);
			printp("\n");
			next;
		}	
	}
	
	# get column names
	my $columns_q = $dbh->prepare("select column_name from information_schema.columns where table_name = '$table'");
	$columns_q->execute();
	my $columns_aref = $columns_q->fetchall_arrayref();
	$columns_q->finish();
	my @columns;
	foreach my $column_aref (@{$columns_aref}) {
		push @columns, @{$column_aref}[0];
	}
	
	my $col_count = scalar @columns;
	printp("\tfound $col_count columns\n");
	
	my $select_into = $dbh->prepare("insert into [$dest_db].dbo.$table 
					select $base_fd,$base_fn,row_number() over (order by $columns[0]),'$base_uf',*
					from $table");

	$select_into->execute();
	$select_into->finish();
	
	# verify that both tables have the same number of rows before dropping
	if (verify_table($table)) {
		printp("\t$table copy checks out\n");
		drop_table($table);
	}
	else {
		printp("\t$table mismatch error!\n");
	}
	
	# print current progress
	print sprintf("progress: %.2f\n", (++$tables_count / $num_tables) * 100);
	
	printp("\n");
}

printp("done\n");

# check that both source and dest tables have same # of rows
sub verify_table {
	my $table = shift;
	my $count_orig = $dbh->prepare("select count(*) from $table");
	$count_orig->execute();
	my $co = ($count_orig->fetchrow_array())[0];
	$count_orig->finish();
	
	my $count_new = $dbh->prepare("select count(*) from [$dest_db].dbo.$table");
	$count_new->execute();
	my $cn = ($count_new->fetchrow_array())[0];
	$count_new->finish();
	
	return ($co == $cn) && (defined $co && defined $cn);
}

# drop a table from source to make space
sub drop_table {
	if (!$drop_source) {
		return;
	}
	my $table = shift;
	
	my $drop_table = $dbh->prepare("drop table $table");
	$drop_table->execute();
	$drop_table->finish();
	printp("\tdropped original $table\n");
	
	# make sure to shrink the source database
	# not sure how long this takes...
	my $shrink_db = $dbh->prepare("dbcc shrinkdatabase(0)");
	$shrink_db->execute();
	$shrink_db->finish();
	printp("\tdatabase shrunk\n");
}

# write to log and stdout
sub printp {
	my $msg = shift;
	# add time if not whitespace
	if ($msg =~ /\S+/) {
		$msg = '[' . localtime() . "]\t" . $msg;
	}
	print $msg;
	open(LOG, '>>', $logfile) or (print "logfile error for: $logfile\n$!\n" and return);
	print LOG $msg;
	close LOG;
}

# get options, open db connection
sub init {
	GetOptions(
		's|src=s' => \$source_db,
		'd|dest=s' => \$dest_db,
		'b|filedate=i' => \$base_fd,
		'n|filenum=i' => \$base_fn,
		'u|updateflag=s' => \$base_uf,
		'r|dropsource' => \$drop_source,
		'e|exclude=s' => \@exclude,
		'l|logfile=s' => \$logfile,
		'h|help' => \$help,
	) or usage();

	usage() if $help;
	
	# test logfile
	(open(LOG, '>', $logfile) and close LOG) or die "logfile error for: $logfile\n$!\n";
	
	# test source db connection
	$dbh = DBI->connect('dbi:ODBC:Driver={SQL Server};Database=$source_db;Server=localhost') or die "could not connect to source database: $source_db\n$!\n";
	
	printp("starting script for $source_db => $dest_db\n$base_fd, $base_fn, $base_uf\ndropping source tables = " . ($drop_source?'true':'false') . "\nexcluding: @exclude\n");
}

sub usage {
	print <<USAGE;
s|src => source database
d|dest => destination database
b|filedate => default filedate YYYYMMDD 
n|filenum => default filenumber
u|updateflag => default update flag A|U|D
r|dropsource => drop the source tables after xfer
e|exclude => list of exclude patterns for table names
l|logfile => logfile
h|help => this message
USAGE
	exit;
}