#GetLatest.pl
use strict;
use warnings;
use Win32;
use Win32::OLE qw(in);
use Win32::OLE::Const 'Microsoft ActiveX Data Objects 2.1 Library';


# prototypes
sub BuildDatabase();
sub ExecuteScriptsDirectory($);
sub AddTables();
sub ExecuteCommand($);
sub LogCommand($$);
sub ExecuteNonOsqlScript($);
sub ExecuteNonOsqlScriptsDirectory($);
sub HandleWin32Err($);

# constants
use constant DB_NAME => "<your db name here>";
use constant LOG_PATH => "Progress.log";

# <<<<>>>> entry point
BuildDatabase();

print ("\n\nlatest database installed complete, press any key\n");
my $input = <STDIN>;
chomp($input);	

#----------------------------------------------------------------------
# Builds the database for the site
#----------------------------------------------------------------------
sub BuildDatabase()
{
	my $Output;
	
	# stop and start the service to kick everyone off the databases
	ExecuteCommand("net stop SQLServerAgent");
	ExecuteCommand("net stop mssqlserver");
	ExecuteCommand("net start mssqlserver");
	ExecuteCommand("net start SQLServerAgent");
	
	# drop the database
	ExecuteCommand("osql -S 127.0.0.1 -E -Q \"DROP DATABASE " . DB_NAME . "\"\n");

	# recreate the database
	ExecuteCommand("osql -S 127.0.0.1 -E -Q \"CREATE DATABASE " . DB_NAME . "\"\n");

	# add the tables
	AddTables();
	
	# add the procs
	ExecuteScriptsDirectory("..\\Database\\Procs");
	
	# add the triggers
	ExecuteScriptsDirectory("..\\Database\\Triggers");
	
	# here you might add some base data
}


#----------------------------------------------------------------------
# Given a directory of scripts (.sql) it executes them all
#----------------------------------------------------------------------
sub ExecuteScriptsDirectory($)
{
	my $path = $_[0];
	
	opendir(SCRIPTSDIR, $path) or die "Couldn't open dir: $path\n";

	while (defined(my $file = readdir SCRIPTSDIR))
	{
		if ( !(-d "$path\\$file") && $file =~ /\.sql\z/ ) 
		{
			ExecuteCommand("osql -S 127.0.0.1 -E -d " .DB_NAME . " -i \"$path\\$file\"\n");
		}
	}
}

#----------------------------------------------------------------------
# Given a directory of scripts (.sql) it executes them all
#----------------------------------------------------------------------
sub ExecuteNonOsqlScriptsDirectory($)
{
	my $path = $_[0];
	
	opendir(SCRIPTSDIR, $path) or die "Couldn't open dir: $path\n";

	while (defined(my $file = readdir SCRIPTSDIR))
	{
		if ( !(-d "$path\\$file") && $file =~ /\.sql\z/ ) 
		{
			print("\n$path\\$file");
			ExecuteNonOsqlScript("$path\\$file");
		}
	}
}

#----------------------------------------------------------------------
# Execute a script that doesn't play well with osql (ie is unicode)
#----------------------------------------------------------------------
sub ExecuteNonOsqlScript($)
{
	my $path = $_[0];
	
	# open connection to database
	my $cn = Win32::OLE->new("ADODB.Connection") or die "Couldn't create the connection\n";
	$cn->Open("Provider=sqloledb; Data Source=127.0.0.1; Initial Catalog=" . DB_NAME . "; Integrated Security=SSPI");
	
	HandleWin32Err('');
		
	open(INFILE, "< $path") or return 0;

	my $sql = "";
	foreach my $line (<INFILE>)
	{
		if ($line =~ /GO.*/)
		{
			$cn->Execute($sql);
			HandleWin32Err($path);
		}
		else
		{
			$sql = "$sql\n$line";
		}
	}
	
	if ($sql =~ /.*/)
	{
		$cn->Execute($sql);
		HandleWin32Err($path);	
	}
	
	HandleWin32Err($path);
	
	$cn->Close();
}

#----------------------------------------------------------------------
# Handle a win32 error
#----------------------------------------------------------------------
sub HandleWin32Err($)
{
	my $insert = "";
	if ((scalar @_) == 1)
	{
		$insert = "$_[0] - ";
	}
	
	my $str = Win32::OLE->LastError();
	if($str)
	{
		my @str = split(/\n/, $str);
		LogCommand("Conn Error:", "$insert\n$str[0]\n$str[1]\n$str[2]\n");
		die "$insert Error: \n$str[0]\n$str[1]\n$str[2]\n";
	}
}

#----------------------------------------------------------------------
# Executes a single command
#----------------------------------------------------------------------
sub ExecuteCommand ($)
{	
	my $Output = qx/$_[0]/;
	$Output =~ s/\d{0,5}> //gi;
	$Output =~ s/\(\d{1,10} row[s]{0,1} affected\)//g;
	$Output =~ s/Cannot add rows to sysdepends for the current stored procedure because it[ \n]depends on the missing object '[a-zA-Z0-9]*'\.[ \n]The[ \n]stored[ \n]procedure[ \n]will[ \n]still[ \n]be[ \n]created\.\n//g;
	
	LogCommand($_[0], $Output);
}

#----------------------------------------------------------------------
# Writes details to a log
#----------------------------------------------------------------------
sub LogCommand($$)
{
	open(OUTFILE, ">>" . LOG_PATH) or die "unable to open output file " . LOG_PATH;
	
	my ($cmd, $Output) = @_;
	
	print ".";
		
	if ( length($Output) )
	{
		print OUTFILE "\n$cmd\n$Output";
		print "\n$cmd\n$Output";
	}
	
	close(OUTFILE);
}


#----------------------------------------------------------------------
# Adds all the tables in the database in the correct order
#----------------------------------------------------------------------
sub AddTables()
{
	my $Output;
	
	my @tables = (
				# <insert table names here>
				);
	
	# create them
	foreach my $tbl (@tables)
	{
		ExecuteCommand("osql -S 127.0.0.1 -E -d " .DB_NAME . " -i \"..\\Database\\Tables\\$tbl.sql\"\n");
	}
}