Perl script to run sql scripts for sql server 2005 2008 files

# C:\poc\data\backup\SqlScriptsInstaller.pl START
#DOCS AT THE END
package SqlScriptsInstaller;

use strict ;
use Exporter;
use SqlScriptsInstallerRunDefs;
use Logger ;
use DirHandle ;
use Cwd ;


our $config = new SqlScriptsInstallerRunDefs () ;
our $varHolder = $config ->getVarHolder () ;
our $logger = new Logger ( \$varHolder ) ;

sub main
{
#LOGMSG
$logger->logmsg("STARTING THE SQL INSTALLER PROGRAM " ) ;
#DUMP THE CONFIGURATION TO THE STRING
$config->dumpIni();
my $path = $varHolder->{'BASEDIR'} ;

backupPreviousStdout();
checkThatSqlCmdExists();

#GET THE CURRENT PATH FROM A DIRHANDLE
my $dh= DirHandle->new("$path") or die "Cant open $path : $! \n\n" ;
#STORE ALL THE SQL FILES IN AN ARRAY
my ( @sqlScriptFilesToRun ) =$dh->read();
#sort the array since the backup should be the first one
@sqlScriptFilesToRun = sort(@sqlScriptFilesToRun);
#FOREACH FILE DO ACTION grep { /\.[sql]$/i }
foreach my $file ( @sqlScriptFilesToRun )
{
# print $file ;
#$logger->logmsg( " I saw $file" ) ;
if ( $file =~/[[:digit:]]\.RUN/i)
{
#change the / to \ sqlcmd freaks out !!!
my $sqlCmdPath = $varHolder->{'SQLCMD'} ;
$sqlCmdPath =~ s/\//\\/gi ;
my $sqlFile = $varHolder->{'BASEDIR'} . "$file" ;
$sqlFile =~ s/\//\\/gi ;
my $sqlLogFile = $varHolder->{'BASEDIR'} . "$file.log" ;
$sqlLogFile =~ s/\//\\/gi ;

#BUILD THE COMMAND FOR EACH RUN FILE TO
my $cmd = "\"" . "$sqlCmdPath" . "\"" . " -S localhost -e -i \"" . "$sqlFile\" -o \"$sqlLogFile\"" ;
#sqlcmd -S localhost -e -i "BackUpAllMyDatabases.sql" -o Result_Of_BackUpAllMyDatabases.log
`$cmd` ;

$logger->logmsg( " Running the following command " . $cmd . "\n") ;
#debug print " I should run $cmd " ;
#debug print "This file contains run and sql $file \n" ;
sleep 1;

}
#sleep 1;
}

$logger->logmsg( " Review the results from the STDOUT LOG ") ;
$logger->logmsg( " Send all log files to the DEV team ") ;
my $cmd = $varHolder->{'STDOUTLOG'} ;
`$cmd` ;

sleep 1;
} #eof main

sub backupPreviousStdout
{
if ( -e $varHolder->{'STDOUTLOG'})
{
use File::Copy ;
my $nicetime = timestamp();
copy ($varHolder->{'STDOUTLOG'} , $varHolder->{'STDOUTLOG'} . "$nicetime" .".log" );
unlink $varHolder->{'STDOUTLOG'} ;

}
}

sub checkThatSqlCmdExists
{
#IF THE SQLCMD UTILITY DOES NOT EXIST EXIT
if ( ! -e $varHolder->{'SQLCMD'})
{
sleep 2 ;
$logger->logmsg( "\n\n\n\n I DID NOT FOUND THE SQLCMD UTILITY \n\n\n\n") ;
sleep 2 ;
$logger->logmsg( "\n\n\n\n READ CAREFULLY YOUR ACTION IS REQUIRED \n\n\n\n") ;
sleep 2 ;
$logger->logmsg( "\n\n\n\n WITHOUT sqlcmd.exe I can not run the sql scripts !!! No can do I should EXIT \n\n\n\n") ;
$logger->logmsg(" SEARCH FOR THE sqlcmd.exe EXECUTABLE under C:\\");
$logger->logmsg(" SET ITS FULL PATH IN THE SqlScriptsInstallerSettings.txt file \n\n\n\n") ;

my $cmdToStartError = $varHolder->{'STDOUTLOG'} ;
`$cmdToStartError` ;
sleep 10 ;
exit;


}
}


#GET A NICE TIME
sub timestamp {
#
# Purpose: returns the time in yyyymmdd-format
#
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
#---- change 'month'- and 'year'-values to correct format ----
$min = "0$min" if ($min < 10); $hour = "0$hour" if ($hour < 10); $mon = $mon + 1; $mon = "0$mon" if ($mon < 10); $year = $year + 1900; $mday = "0$mday" if ($mday < 10); return "$year$mon$mday" . "_" . "$hour$min$sec"; } #Action main(); #sleep 10 ; 1; __END__ #SqlScriptsInstaller.pl -- the starting script for managed running of sql scripts #This script has been tested on Win Vista , Perl 5.10 , Sql Server 2008 # alternatively you might want to change the call to another RDBMS command line call #run by perl "path/to/SqlScirptsInstaller.pl #it has been also compiled to single executable #so the idea is the configure first the path to the sqlcmd # C:\poc\data\backup\SqlScriptsInstaller.pl END # C:\poc\data\backup\Logger.pm START package Logger; # This module is responsible for all the logging which is done # no module should write directly to the screen if not for # debugging purposes # interface debug(param) logmsg(param) # No \n marks at the ends of messages - each message should start # with space for better readability require SqlScriptsInstallerRundefs; require Exporter; use POSIX qw(strftime) ; my $blockFlag = 0; sub new { my $self = shift; $varHolder = ${ shift @_} ; #get the has containing all the settings return bless({}, $self); } #Starts a block this is used for writing "processbars" to the screen sub startBlock { shift; my ($msg) = @_; if($msg ne '') { logmsg($self,$msg); } $blockFlag=1; } #Prints messages, uses the OUTPUT to decide where the message #goes. SCREEN, STDOUT.LOG etc sub screenPrint { my ($msg) = @_; #ysg decide where the ouput will go 0 - screen 1- screen and file 2 my $outputFlag = $varHolder->{'OUTPUT'};
if($outputFlag <= 1) { print $msg; } } #This is used for ending a block, it basically just writes a newline sub endBlock { $blockFlag = 0; screenPrint("\n"); } #This writes to screen only, if blockflag is not set we also send a #newline sub logmsg { # --------------------------------------------------------------------- # Display on screen or write script output (log) to a file. Or both... shift; my ($msg) = @_; # FORMAT MESSAGE ---------------------------- if(!($blockFlag)) { $msg = formatMessage($msg) ; } screenPrint($msg); toStdOutLog($self,$msg); } sub formatMessage { #Purpose - to format the passed message into readable format with timestamp my $msg = shift ; my ( $year , $mon , $mday , $hour , $min , $sec ) = timestamp () ; $msg = $hour .":". $min .":". $sec . " " . $mday . "." . $mon . "." . $year . " -- " . $msg . "\n"; #^^^ 3 spaces ! ; ) return $msg ; } sub toStdOutLog { my $foo = shift; my ($msg) = @_; $stdoutlog = $varHolder->{'STDOUTLOG'};
open (LOGFILE, ">>$stdoutlog");
print LOGFILE $msg;
close(LOGFILE);
}


sub debug {
my $foo = shift;
my ($msg) = @_;
$msg = formatMessage($msg) ;

if($varHolder->{'DEBUG'}) {
$debuglog = $varHolder->{'DEBUGLOG'};
open (DEBUGFILE , ">>$debuglog" );
print DEBUGFILE $msg ;
close(DEBUGFILE);
}

print $msg ;
} #eof debug

sub error {
# -------------------------------------------------------------
# Log errors...
#
my $msg;
my $do_not_exit;
shift;
if(scalar(@_) == 1)
{
($msg) = @_;
$do_not_exit = 0;
}
else
{
($msg,$do_not_exit) = @_;
}

my $ERRORLOG = $varHolder->{'ERRORLOG'};
open (LOGFILE, ">>$ERRORLOG");
print LOGFILE $msg;
close (LOGFILE);
#Errors never exit
# if (!$do_not_exit) { exit(0); }
}


sub runlog {
# -------------------------------------------------------------
# Log program output...
#

my ($msg,$debug) = @_;
my $debuglog;

if ($debug) {
$debuglog = $varHolder->{'RUNLOG'};
$debuglog =~ s/program/debug/gi;

open (LOGFILE, ">>$debuglog");
} else {
open (LOGFILE, ">>$PROGRAMLOG");
}
print LOGFILE $msg;
close (LOGFILE);

}

sub timestamp {
# my $class = shift ;
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
#---- change 'month'- and 'year'-values to correct format ----
$sec = "0$sec" if ($sec < 10); $min = "0$min" if ($min < 10); $hour = "0$hour" if ($hour < 10); $mon = $mon + 1; $mon = "0$mon" if ($mon < 10); $year = $year + 1900; $mday = "0$mday" if ($mday < 10); return $year , $mon , $mday , $hour , $min , $sec ; } sub statusBarTick { print "="; } sub get { shift; my $name_l = shift; return $varHolder->{$name_l};
}

sub set
{
shift;
my $name_l = shift;
my $value_l = shift;
$varHolder->{$name_l}=$value_l;
}

sub dumpHash {
my $self = shift ;
foreach $key (keys %$varHolder)
{
print "$key = $varHolder->{$key}\n";
}
}

1;

__END__
# C:\poc\data\backup\Logger.pm END
# C:\poc\data\backup\SqlScriptsInstallerRunDefs.pm START
#This package is responsible for reading the ini-files
#it is used by every other application and module in the project
#use vars qw(%variables);

package SqlScriptsInstallerRunDefs;
require Exporter;

# @ISA = qw(AutoLoader Exporter);
@EXPORT = qw(getVarHolder);

my $iniFile = 'SqlScriptsInstallerSettings.txt'; #something has to be hardcoded!!!
my $varHolder = {

BASEDIR => 'D:/poc/poc_dev', # directory, where the scripts residue
SQLCMD =>'C:/Program Files/Microsoft SQL Server/100/Tools/Binn/sqlcmd.exe' #the default time to force the agent to boot before the cycle end

}; # 1 = reboot cv agent, 2 = reboot machine, 0 = don't reboot

#ysg CONSTRUCTOR
sub new
{
my $package=shift;
doParametrisation();
readIni();
return bless({}, $package);
}
# This function goes through the varHolder-struct, it searches for
# strings that look like %NAMEOFPARAM%, and replaces it with the
# possible value
# ex
#BASEDIR=c:/temp/
#LOGDIR=%BASEDIR%logs/
#is changed to
#LOGDIR=c:/temp/logs/
sub doParametrisation
{
my $class = shift;
my $value_l = shift;
if($value_l=~/\%(\w+)\%/)
{

($prt1,$value,$prt2)=/(.*)\%(\w+)\%(.*)/;

if($value ne 'TMPLVL' && $value ne 'ERRORLEVEL')
{
$value_l=~s/\%$value\%/$varHolder->{$value}/gi;
}

}
return $value_l;
}

#Reads the inifile, converts the %param% into param and produces the
#varHolder structure
sub readIni {
shift;
my $inifile_l = $iniFile;
if(scalar(@_)>0)
{
$inifile_l = shift;
}
open (INIFILE,$inifile_l);
while()
{
if ($_ =~ /^[a-zA-Z]/)
{
($param_l,$val_l) = split(/=/,$_);
chomp($param_l);
chomp($val_l);
$val_l = doParametrisation($self,$val_l);
$varHolder->{$param_l} = $val_l;
}
}
close(INIFILE);
return 1;
} #eof sub readIni

#Just dumps the ini to the screen used for debugging only
sub dumpIni
{
my $self = shift ;
readIni();
foreach $key (keys %$varHolder)
{
print "$key = $varHolder->{$key} \n";
}
}


sub get
{
shift;
my $name_l = shift;
return $varHolder->{$name_l};
}

sub set
{
shift;
my $name_l = shift;
my $value_l = shift;
$varHolder->{$name_l}=$value_l;
}

sub getVarHolder
{ #Return the only the reference to the varHolder
return $varHolder ;
}

1;

__END__
# C:\poc\data\backup\SqlScriptsInstallerRunDefs.pm END
# C:\poc\data\backup\ReadMe.txt START
How-to use it:
- Unpack it to a directory such as C:\temp
- Open the SqlScriptsInstallerSettings.txt file which accepts settingName=SettingValue syntax
- set the path to the sqlcmd utility SQLCMD
- should you want to use another name such as SQL*Plus of Oracle change the name
- cd to the directory: cd C:\temp
- place all your sql scripts in it as follows: 0.RUN.scriptNameToRunFirs.sql , 1.RUN.scirptNameToRunSecond.sql etc (Note that the format [[:digit:]].RUN is obligatory
- run with perl C:\temp\SqlScriptsInstaller.pl
- check the logs afterwards

alternatively
-run by calling the SqlScriptsInstaller.exe

TODO:
implement configurable calls for Oracle , MySql etc.
embed in NSIS


* Copyright (c) 2009, Yordan Georgiev http://ysgitdiary.blogspot.com/
* All rights reserved.
*
* Redistribution and use in source and binary forms, with or without
* modification, are permitted provided that the following conditions are met:
* * Redistributions of source code must retain the above copyright
* notice, this list of conditions and the following disclaimer.
* * Redistributions in binary form must reproduce the above copyright
* notice, this list of conditions and the following disclaimer in the
* documentation and/or other materials provided with the distribution.
* * Neither the name of the nor the
* names of its contributors may be used to endorse or promote products
* derived from this software without specific prior written permission.
*
* THIS SOFTWARE IS PROVIDED BY Yordan Georgiev ''AS IS'' AND ANY
* EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
* WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
* DISCLAIMED. IN NO EVENT SHALL Yordan Georgiev BE LIABLE FOR ANY
* DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
* (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
* LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
* ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
* SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
# C:\poc\data\backup\ReadMe.txt END
# C:\poc\data\backup\SqlScriptsInstallerSettings.txt START
#SqlScriptsInstallerSettings.txt
#This file contains the settings for the SqlScriptInstaller executable
#DO NOT CHANGE THE / SLASHES TO / SLASHES !!!
#this is just comment
#The syntax is key=value

#The path where the sqlcmd.exe is located on this computer
#The default path is REMEMBER TO CHANGE THE / to / !!!
SQLCMD=C:/Program Files/Microsoft SQL Server/100/Tools/Binn/sqlcmd.exe
#debug temporarily changed to seconds for debugging, default value = 120 seconds = 2 min

#Basedir
BASEDIR=D:/poc/data/backup/
#ysg with big L and /
LOGDIR=D:/poc/data/backup/


# Debug = 1 -> will create a debug.log with detailed debug info
DEBUG=1

#DEBUGLOG
DEBUGLOG=%BASEDIR%debug.log


#ERRORLOG
ERRORLOG=%LOGDIR%error.log

#STDOUT PLACE (0 - Screen, 1 - Screen + File, 2 - File Only)
OUTPUT=1

#StdoutLog
STDOUTLOG=%LOGDIR%stdout.log
# C:\poc\data\backup\SqlScriptsInstallerSettings.txt END