#!/usr/local/bin/perl
###########################################################################################
# #
# Database ReSynchronisation Tool #
# #
# Multi-Tier Multi-Table Version 5.1 #
# #
# #
# Use requires DBI & DBD modules and BVCustomPerl.pm #
# This is a package that gives us some handy subroutines that are used here. #
# Namely: #
# GetDBvars - returns database connection variables as a hash #
# LogHeader & LogFooter - logging routines #
# #
######################################### Purpose #########################################
# #
# ReSyncDBs.pl compares data from 2 databases and will detect a mismatch based on the #
# OID and primary key ( which this script detects from the 'bv_attributes' table but #
# can be altered to use any other table containing the attributes of the table you #
# want to synchronise ). #
# The script then attempts to resynchronise the two by updating the live database based #
# on the staging database content. #
# The situation commonly arises when a user performs an insert into the live and staging #
# instances with the same primary key. Two different oids will be assigned and the #
# database staging process will subsequently fail when attempting to insert the #
# 'asynchronous' oid. #
# Updating is a tricky business and this script attempts to detect the parent table #
# and can update parent-child-grandchild relationship tables #
# #
# This tool was originally developed on a BroadVision environment, but could be #
# adapted for use on any system with a staging and live database instance. #
# If you do succesfully port it onto other environments please let me know. #
# #
# Andy Pritchard #
# #
######################################## Revisions ########################################
# #
# 5.1 Adapting for deployment to CPAN #
# 5.0 Added multi-parent table resynchronisation ability #
# 4.6 Added the '-r -all' option. Added some signal trapping to re-enable constraints #
# if it all goes pear-shaped. Altered the update pattern-matching to allow us to #
# update names with leading and trailing spaces. #
# 4.5 Altered location poper fix method to take account of mtc_xxx_code column names and #
# mtc_table names being different eg destination #
# 4.4 Had to change that to a union all select so that we get the xml_id column. If it #
# exists. #
# 4.3 Altering sql to pick up attribute_name from bv_attributes where the attribute_ids #
# dont start at zero #
# 4.2 Added check to sql to quit in cases where a table doesnt have defined referential #
# integrity column - in which case we cant automate resync #
# 4.1 Added sort to keys(% routines #
# 4.0 Contains Functionality to resynchronise failures during the location poper #
# 3.2 Allows null xml-id resynchronisation to be specified from the command line (pre 5.1)#
# 3.1 Resyncs BV_CATEGORY's #
# 3.0 Added functionality to use the creation time for restriction of hundreds of rows #
# 2.0 Resyncs parent-child-grandchild relationships #
# 1.0 Resyncs parent-child relationships #
# #
###################################### The Variables ######################################
$OneAtATime = 1; # Set to 0 to resync all the asynchronous content in one.
# Set to 1 (default) to require user intervention for each
# Now set by the -all command line flag
$StageToolDir = "/export/home/users/ninja/Resync/"; # Directory where staging files sit
$ThisDir = "/export/home/users/ninja/Resync"; # Script directory
$database_link = "PS01"; # Name of database link between live and staging instance
$Testing = 0; ## Set to zero or delete when in production mode - Set to 2 to test with a production query
$debug = 0; ## Set to zero or delete when in production mode
$useCreationDate = 0; # This is altered by the -ct command line flag later.
###################################### The Settings #######################################
$LogFile = "${ThisDir}/${0}.Process.log";
$InputOutputFile_1 = "${ThisDir}/${0}.input_output.I";
$InputOutputFile_2 = "${ThisDir}/${0}.input_output.II";
$constraintlist = "${ThisDir}/${0}.constraints";
$asynchronous_log = "${ThisDir}/${0}.asynchronous_oids.log";
$MultiParentlog = "${ThisDir}/Multiple_Parents.log";
####################################### The Program #######################################
&usage if ($#ARGV < 0);
$command = ${ARGV}[0];
use BVCustomPerl;
use DBI;
my (@Table,%FailedHierarchy,@errorreturn,@Allerrors,@FailedTableList,@location_poper_tables,$ver) = ();
my %DBvars = &BVCustomPerl::GetDBvars();
my ($LatestStgTlLoadLog);
$user = $DBvars{'USER'};
if ($command eq '-e' || $command eq '-f') {
$LatestStgTlLoadLog = qx!ls -1tr ${StageToolDir}stage_load*.log | tail -1!;
chomp($LatestStgTlLoadLog);
}
usage() unless ($command eq '-e' || $command eq '-f' || $command eq '-r' || $command eq '-t' || $command eq '-stage' || $command eq '-test' || $command eq '-clean');
if ($command eq '-e')
{
@Allerrors = &stageloaderrors;
print "\n ---------------------------";
print " Errors from ", $LatestStgTlLoadLog;
print " ---------------------------\n\n";
foreach (@Allerrors)
{
print $_, "\n";
}
exit 0;
}
if ($command eq '-f' || $command eq '-t')
{
$MethodName = "PS - PL Resynchronisation - Stage I";
}elsif ($command eq '-stage') {
$MethodName = "Staging Procedure Resynchronisation";
}else{
$MethodName = "PS - PL Resynchronisation - Stage II";
}
$Progress = $MethodName;
print "\n----------------------------- $Progress -----------------------------\n\n";
&BVCustomPerl::LogHeader($LogFile,$MethodName,$user,scalar(localtime));
$Progress = "Making Database Connection";
print "1. $Progress\n";
$dbh = DBI->connect("dbi:Oracle:host=$DBvars{'HOST'};sid=$DBvars{'SID'}",
$DBvars{'USER'}, $DBvars{'PASSWORD'},
{ RaiseError => 1, AutoCommit => 0 }) || die $DBI::err;
$exstat = $?;
LogData("Connected to: $DBvars{'DATABASE'}; as: $DBvars{'USER'}",$Progress) if ($exstat == 0);
print "2. Connected\n" if ($exstat == 0);
########################################### STAGE 1 ###########################################
if ($command eq '-f'){
qx!rm -f "$InputOutputFile_1"! if -e "$InputOutputFile_1";
qx!rm -f "$InputOutputFile_2"! if -e "$InputOutputFile_2";
$Progress = "Checking Failures in Load Log";
print"3. $Progress\n";
print"Last log is $LatestStgTlLoadLog\n";
@Allerrors = &stageloaderrors;
foreach (@Allerrors)
{
next unless /not updated\./;
s/(\w*)\s*not updated\.\s*/$1/;
push(@FailedTableList,$_);
print "$_ \t Failed\n";
LogData("Failed Table :->$_<-",$Progress);
}
unless (@FailedTableList)
{
$Progress = "Error Log Checked - No errors Found";
print "$Progress\nNothing to resynchronise.\n";
QuitCleanly("Nothing to resynchronise. Finshing.",0);
}
###### This section iterates through the failed table list
###### and finds the parent, children, and grandchildren if they exist
$Progress = "Analysing Table Hierarchy for Failed Tables";
print"3. $Progress\n";
LogData("Started",$Progress);
my($status,%FailedHierarchy) = AnalyseHierarchy(@FailedTableList);
QuitCleanly('',$status);
}elsif($command eq '-t'){
qx!rm -f "$InputOutputFile_1"! if -e "$InputOutputFile_1";
qx!rm -f "$InputOutputFile_2"! if -e "$InputOutputFile_2";
qx!rm -f "$MultiParentlog"! if -e "$MultiParentlog";
print "\n ! Must Specify a valid Parent Table Name !\n" unless ($ARGV[1]);
usage() unless ($ARGV[1]);
@Table_anycase = $ARGV[1];
foreach (@Table_anycase) { $_ = uc($_); push(@Table,$_); }
$Progress = "Analysing Table Hierarchy for @Table";
print"3. $Progress\n";
LogData("Started",$Progress);
my($status,%FailedHierarchy) = AnalyseHierarchy(@Table);
QuitCleanly("Finished Analysis for @Table",$status);
}elsif($command eq '-stage'){
$Progress = "Investigating Staging Procedure Failure";
print "3. $Progress\n";
LogData("Starting Staging Procedure Analysis",$Progress);
$Progress = "Investigating Location Poper";
print "4. $Progress\n";
LogData("Checking Location Poper Failures",$Progress);
@FailedRows = StagingFailure('locationpoper','find','');
if (@FailedRows)
{
@location_poper_tables = ();
foreach(0..$#FailedRows)
{
push(@location_poper_tables, $FailedRows[$_]{'table'});
}
@location_poper_tables = MergeArrays(@location_poper_tables);
print "\n------------------------- Location Poper Resynchronisation -------------------------\n\n";
print "\n\tFailures were found in the location poper log for:\n\t";
print join("\n\t", @location_poper_tables);
print "\n\n\tTo resynchronise :\n\n";
print "\t\t1. Table updates must occur to both Live and Stage database\n";
print "\t\t2. Each database will be queried to ensure no clashing upon update\n";
print "\t\t3. Resynchronised will be based on the assumption that\n";
print "\t\t the primary key should be of the order:\n";
foreach (@location_poper_tables)
{
print "\t\t <Tour Operator> - <Holiday Type> - <Season Code> - <Brochure Code> - <${_}_CODE>\n";
}
print "\n\t Ready to continue Y/N ?\n\n";
ExitOnDemand();
$Progress = "Resynchronising Location Poper";
print "5. $Progress\n";
LogData("Resynchronising Location Poper Failures",$Progress);
$status = StagingFailure('locationpoper','fix',@FailedRows);
print "There was a problem with this process\n" if ($status != 0);
}
QuitCleanly("Finished Staging Procedure Analysis",$status);
&BVCustomPerl::LogFooter($status,$LogFile,$MethodName,$user,$TimeLine);
########################################### STAGE 2 ###########################################
}elsif($command eq '-r'){
chomp(@ARGV);
shift(@ARGV); ## Gets rid of the -r
while(@ARGV)
{
$next = shift(@ARGV);
if($next eq '-ct') {
$useCreationDate = shift(@ARGV);
$useCreationDate =~ s/^('|")(.*)\1$/$2/; #'
$useCreationDate = uc($useCreationDate);
LogData("User specified creation-time to be >= \'$useCreationDate\'",$Progress);
}elsif($next eq '-all') {
$OneAtATime = 0;
LogData("User specified resynch of all content without intervention",$Progress);
}else{
QuitCleanly("Incorrect Flag Specified...Aborting",1);
}
}
our ($multi_table_resync,$resync_complete) = 0;
# need to ensure we dont overwrite an existing disabled constraints log
if (-e "${constraintlist}.disabled.log"){
print " Found existing\n\t ${constraintlist}.disabled.log\n cant continue or may overwrite\n";
print "\n This may be because constraints could not be replaced by $0 \n";
print " Or perhaps you did not cleanly exit the program on a previous run.\n";
print "\n It may be safe to simply delete this file to allow the program to continue,\n";
print " but you should check that the constraints listed therein are actually re-enabled before doing so...\n\n";
QuitCleanly("Aborting since ${constraintlist}.disabled.log exists",1); }
# Check whether this is a multi-parent run #
if ((-e $MultiParentlog) and (-e $InputOutputFile_2)) {
## Then it probably is #
$Progress = "Beginning Multi-Parent Resynchronisation";
LogData("Multiple Parent Table Resynchronisation",$Progress);
MultiParentHandler('load');
$multi_table_resync = 1;
}
RESYNCHRO_LOOP:
while (! $resync_complete) {
our(@DBRefData) = ();
if ($multi_table_resync) {
$resync_complete++ unless (MultiParentHandler('reload'));
last RESYNCHRO_LOOP if ($resync_complete);
next RESYNCHRO_LOOP if (MultiParentHandler('ask_user'));
}
$Progress = "Reading Input-Output File";
print "3. $Progress\n";
%ResyncTables = ReadIOFile($InputOutputFile_1); ## This makes a hash from the file fed in.
foreach $level (sort keys(%ResyncTables))
{
print "Read Input-Output; Level =>$level<= : Value =>$ResyncTables{$level}<= \n" if ($debug);
LogData("Level =>$level<= : Tables =>$ResyncTables{$level}<=",$Progress);
$levelcount1 = $level unless ($level < $levelcount1);
}
$complexity = 1 if ($ResyncTables{3}) or $complexity = 0;
if ($ResyncTables{2})
{
print "\n *** Complex Table Structure Found ***\n\n";
LogData("Complex Table Structure Found",$Progress);
}else{
print " - Normal Table Structure Found\n";
LogData("Normal Table Structure Found",$Progress);
}
if ($complexity) { QuitCleanly("Cannot use this script to resync 4-level hierarchies..",1,''); }
if ($ResyncTables{0} =~ /,/) {
print "\n*** Attempting Multiple Parent Table Resynchronisation ***\n\n";
LogData("Multiple Parent Table Resynchronisation",$Progress);
QuitCleanly("Cannot use this script to resync Multiple Parent Tables..",1,'');
}
$ParentTable = $ResyncTables{0};
$Progress = "Retreiving database linkage information";
print "4. $Progress\n";
LogData("Resynchronisation Information for $ParentTable",$Progress);
## Now split this file into hierarchy levels
foreach $level (sort keys(%ResyncTables))
{
@EachLevelTables = split(/,/, $ResyncTables{$level});
## Then split the tables up
foreach (@EachLevelTables)
{
@TableInfo = DBLinkInfo(0,${_}); # This is a hash of a hash
foreach $constraint_no ( 0 .. $#TableInfo)
{
$table = $TableInfo[$constraint_no]{'TABLE_NAME'};
print "level = $level ; table = $table ; constraint_no = $constraint_no\n" if ($debug);
$DBRefData[$level]{$table}[$constraint_no] = $TableInfo[$constraint_no];
}
}
}
###################################################################################################
## 4-Tier Data Structure is as follows:
## DBRefData -> level -> table -> constraint number -> %DBLinkage hash
## |
## Keys : TABLE_NAME, CONSTRAINT_TYPE, STATUS, COLUMN_NAME, POSITION, CONSTRAINT_NAME
##
## Remember. We just created an array of hashes of hashes
## Pretty smart yes but v tricky to compose and access
## basically its named $blahblah[index]{hash_key}[index2] = "hash_values"
## access with keys %{$blahblah[index]}
## then values are $blahblah[index]{hash_key}[index2]{hash_key3} - AP
##
###################################################################################################
$levelcount = $#DBRefData;
if ((@DBRefData) and ($levelcount == $levelcount1))
{
## can assume this was succesful
$Progress = "Retrieved Database Linkage Information";
print "5. $Progress Succesfully\n";
LogData("$Progress Succesfully",$Progress);
LogData("Produced \@DBRefData Succesfully",$Progress);
}else{
$Progress = "Database Linkage Information Retrieval Failed";
print $Progress, "\n";
QuitCleanly("Failed to produce \@DBRefData",1);
}
HashReader('print',@DBRefData) if ($debug); # Just to show us what we've got so far
$Progress = "Finding Enabled Constraints for Failed Tables";
print "6. $Progress\n";
LogData("Finding Constraints",$Progress);
## Find all enabled constraints and write them for comparison later ##
qx!rm -f "${constraintlist}.enabled.log"! if -e "${constraintlist}.enabled.log";
open(OUT,">${constraintlist}.enabled.log") or die "Cant write to ${constraintlist}.enabled.log\n";
print OUT "# Constraints which need to be turned off to resynchronise:\n";
close(OUT);
$query = "STATUS eq ENABLED";
@EnabledConstraintList = QueryHash(0,$levelcount,$query,@DBRefData);
# uses format ($startlevel,$endlevel,$query,@hash)
## Get parent constraint first
$query = "CONSTRAINT_TYPE eq P";
@EnabledPConstraint = QueryHash(0,0,$query,@EnabledConstraintList);
LogData("Writing P Key Constraint for Parent",$Progress);
WriteHash("file","${constraintlist}.enabled.log",@EnabledPConstraint);
# <type of output> <name of output> <input hash>
# That should get the parent info - Now for children
# We want all constraint types for upper level but only referential for lower
LogData("Writing R Integrity Constraints for Child Structure",$Progress);
@EnabledCConstraints = QueryHash(1,$levelcount,"CONSTRAINT_TYPE eq R",@EnabledConstraintList);
WriteHash("file","${constraintlist}.enabled.log",@EnabledCConstraints);
system("more ${constraintlist}.enabled.log") if ($debug); ## prints constraints.log if you need.
chmod(0444,"${constraintlist}.enabled.log");
$filecheck = qx!/bin/wc -l "${constraintlist}.enabled.log" !;
$filecheck =~ s/^\s*(\d+).*\n*$/$1/;
$infilecount += $levelcount1;
if ( $filecheck < $infilecount ) {
QuitCleanly("Failed when checking enabled constraint log ${constraintlist}.enabled.log only $filecheck lines long.",1);
}
print " Succesfully written applicable constraints to ${constraintlist}.enabled.log\n";
LogData("Succesfully written applicable constraints to ${constraintlist}.enabled.log",$Progress);
$Progress = "Finding Asynchrosity for Parent";
print "7. $Progress\n";
LogData("Investigating Asynchrosity for $ParentTable",$Progress);
# use $database_link here to find anything thats out
### Firstly get it into a file and then we can check this later ###
LogData("User specified creation_time must be greater than \'$useCreationDate\'",$Progress) if($useCreationDate);
@InitialAsynchronousOIDs = Asynchrosity(@DBRefData);
$Progress = "Completed $ParentTable Table and Constraint Analysis";
print "7. $Progress\n";
LogData("Asynchronous OIDs Checked for $ParentTable",$Progress);
if (@InitialAsynchronousOIDs)
{
qx!rm -f "$asynchronous_log"! if -e "$asynchronous_log";
open(ASYNCHRO,">$asynchronous_log") or die "Cant write to $asynchronous_log\n Cant continue\n";
print ASYNCHRO @InitialAsynchronousOIDs;
close(ASYNCHRO);
if ( -s "$asynchronous_log") {
print " Found the following OID information out of synchronisation:\n\n";
print @InitialAsynchronousOIDs, "\n";
#system("more $asynchronous_log"); ## prints asynchronous oid log if you need.
LogData("Succesfully written oid data to $asynchronous_log",$Progress);
print " Succesfully written oid data to $asynchronous_log\n";
chmod(0444,$asynchronous_log);
}else{
QuitCleanly("Found Errors but Failed to write to $asynchronous_log !",1);
}
}else{
$message = "Prod Live - Prod Stage $ParentTable Tables are fully synchronised.";
LogData($message,$Progress);
print " $message\n\n";
if ($multi_table_resync) {
MultiParentHandler('unload');
next RESYNCHRO_LOOP;
}
QuitCleanly("Succesfully Executed Procedure",0);
}
$status = 0;
&BVCustomPerl::LogFooter($status,$LogFile,$MethodName,$user,$TimeLine);
## If weve got here you know it means we have to resync
print "\n----------------------------- $MethodName - Results -----------------------------\n\n";
print "\tCurrently Enabled Constraints \n\t\t written to:\t${constraintlist}.enabled.log\n";
print "\tOID data written to:\t$asynchronous_log\n";
print "\n\tQuit Now if you wish to check these \n";
print "\n\n\tIf you choose to continue :\n\n";
print "\t\t1. Constraints will be disabled\n\t\t2. Child tables will be resynchronised\n";
print "\t\t3. Parent tables will be resynchronised\n\t\t4. Constraints will be re-enabled\n";
print "\t\t5. Tables will be rechecked for consistency\n\n";
print "\t Do you wish to continue Y/N ?\n\n";
&ExitOnDemand();
########################################### STAGE 3 ###########################################
#
# Remember we have the following variables, hashes, arrays - you name it
# %ResyncTables with the bare parent child table names
# @DBRefData with the 4-d constraint data
# and ${constraintlist}.enabled.log
# $ParentTable is the Parent Table
# @EnabledPConstraint and @EnabledCConstraints = parent and child constraints that need to be done over
# @InitialAsynchronousOIDs has all the parent oids that are out
#
# I'll Put more in here as I remember - Andy
#
$MethodName = "PS - PL Resynchronisation - Stage III";
$Progress = $MethodName;
print "\n----------------------------- $Progress -----------------------------\n\n";
&BVCustomPerl::LogHeader($LogFile,$MethodName,$user,scalar(localtime));
#################################### Disabling Constraints ####################################
print "\t\tNeed to disable constraints now, are you happy to do this Y/N ?\n\n";
&ExitOnDemand();
LogData("User elected to disable constraints",$Progress);
$Progress = "Disabling Constraints";
print "8. $Progress\n";
# If anything goes wrong from here on we need to enable anything we can #
$SIG{__DIE__} = $SIG{QUIT} = $SIG{INT} = \&SpannerInWorks;
###### Disable Children then parents
LogData("Disabling Constraints for Children tables",$Progress);
($success, @cDisabledList) = AlterConstraints("disable",@EnabledCConstraints);
if ($success == 0){
$message = "Disabled Child Constraints";
open(DISABLED_CONS,">${constraintlist}.disabled.log");
print DISABLED_CONS "# Succesfully Disabled the Following Constraints:\n";
print DISABLED_CONS @cDisabledList, "\n";
}else{
## I'm quitting here if we cant disable children
QuitCleanly("Failed to Disable Child Constraints. Cannot Continue, re-enabling all constraints",1,'replaceconstraints');
}
print " ", $message, "\n"; LogData("$message",$Progress);
###### Disable Parents
LogData("Disabling Constraints for Parent table",$Progress);
($success, @pDisabledList) = AlterConstraints("disable",@EnabledPConstraint);
if ($success == 0){
$message = "Disabled Parent Constraints";
print DISABLED_CONS @pDisabledList, "\n";
}else{
# If we get here and we've disabled child but not parent constraints
# we need to re-enable children and exit
close(DISABLED_CONS);
QuitCleanly("Failed to Disable Parent Constraints. Cannot Continue, re-enabling all constraints",1,'replaceconstraints');
}
print " ", $message, "\n"; LogData("$message",$Progress);
close(DISABLED_CONS);
chmod(0444,"${constraintlist}.disabled.log");
$message = "Written Disabled Constraints to ${constraintlist}.disabled.log ";
print "$message\n";
LogData("$message",$Progress);
######################################### Table Updates #########################################
## update subs here
print "\n---------------------------------- Table Updates ----------------------------------\n\n";
print "\n\t\tAbout to update any child tables followed by the parent table. " .
"\n\t\tIf you choose to exit at any point beyond here, constraints will be replaced for you.\n" .
"\t\tDo you wish to continue Y/N ?\n\n";
ExitOnDemand("replaceconstraints");
$Progress = "Table Updates";
print "9. $Progress\n";
## Cascading data connectivity realligning
## Need to pass each parent: table&oid&level; for each child: table&oid&level; for each grandchild: no pass
## I dont believe we can extend this if we ever find greatgrandchildren
foreach $OutOID (@InitialAsynchronousOIDs)
{
if($OneAtATime) {
print "\n---------------------------------- Update ----------------------------------\n\n";
print "\tAbout to Update complete structure for this asynchronous oid:\n";
print " $OutOID\n\n";
print "\tReady to continue?\n\n";
if (<STDIN> =~ m/n/i)
{
$message = "User opted to skip update of $OutOID.";
if ($debug) {
print " $message\n";
}else{
print " Skipping\n";
}
LogData("$message",$Progress);
next;
}
}
$depth = 0; # we're at parent level
@OutChildOIDs = P_C_Link($depth,$OutOID); ## Must return info in same way as InitialAsynchronousOIDs
foreach $child_oid (@OutChildOIDs) #child oid
{
$depth = 1; # child level
@OutGrandChildOIDs = P_C_Link($depth,$child_oid);
foreach $grandchild_oid (@OutGrandChildOIDs)
{
# Grand child - I think this is only ever business rules but there may be others
UpdateTable(2,$grandchild_oid); # Doing grandhchildren - level 2 ers
}
UpdateTable(1,$child_oid); # Doing children - level 1 ers
}
UpdateTable(0,$OutOID); # Doing parents - level 0 ers
}
#################################### Re-Enabling Constraints ####################################
$Progress = "Re-Enabling Constraints";
print "10. $Progress\n";
LogData("Enabling Constraints for Parent table",$Progress);
###### Enable Parent then Children
($success, @pReEnabledlist) = AlterConstraints("enable",@EnabledPConstraint);
if ($success == 0) { $message = "Enabled Parent Constraints";}else{$message = "Failed to enable Parent Constraints";}
print " ", $message, "\n"; LogData("$message",$Progress);
###### Enable Children
LogData("Enabling Constraints for Children tables",$Progress);
($success, @cReEnabledlist) = AlterConstraints("enable",@EnabledCConstraints);
if ($success == 0){ $message = "Enabled Child Constraints";}else{$message = "Failed to enable Child Constraints";}
print " ", $message, "\n"; LogData("$message",$Progress);
$SIG{__DIE__} = $SIG{QUIT} = $SIG{INT} = 'DEFAULT'; # Reset the SIG trap
$Progress = "Checking Integrities";
print "11. $Progress\n";
LogData("Checking Constraints",$Progress);
@AnyRemainingCons = &CheckConstraints();
if (@AnyRemainingCons) {
$message = "WARNING Some constraints have not been re-enabled Check Tables @AnyRemainingCons";
}else{
$message = "Constraints All Re-Enabled\n";
qx!mv -f "${constraintlist}.disabled.log" "${constraintlist}.${ParentTable}.log"!;
qx!rm -f "${constraintlist}.enabled.log" !;
}
print "$message\n";
LogData("$message",$Progress);
## If the re-enable fails we need to restore from Backups and do so here then re-enable cons
# Check here whether multi-parent resync has finished, if so increment $resync_complete
if ($multi_table_resync) {
MultiParentHandler('unload');
}else{
$resync_complete++;
}
}
print "\n------------------------------ $MethodName - Complete ------------------------------\n\n";
MultiParentHandler('finish') if ($multi_table_resync);
QuitCleanly("Completed Procedure",0);
}elsif($command eq '-clean'){
print "Tidying unnecessary files: ";
qx!rm -f "${constraintlist}"\.*.log!;
print "${constraintlist}.\*.log \n";
qx!rm -f "${InputOutputFile_1}"!;
print "${InputOutputFile_1} \n";
qx!rm -f "${asynchronous_log}"!;
print "${asynchronous_log} \n";
print "Cleaned.\n";
}elsif($command eq '-test'){
# Whatever you want to test
usage() unless ($debug);
}else{
usage();
}
############################################ SUBS ############################################
sub usage
{
print "\n------------------------------------------- $0 ------------------------------------------\n\n";
print "Purpose:\n\t This script should be used to resynchronise any prod-stage/prod-live discrepancies.\n";
print "Usage: \n\t $0 ";
print <<'EOF1';
[ -efrt -stage -clean ] [ command ] [-ct <DATE>] [ -all ]
Options:
-e - Pull error entries from latest stage_load log
-f - Format errored entries from latest stage_load log
ready for use in resynchronisation.
-r [-ct <DATE>] [ -all ]
EOF1
print "\t\t\t\t - Resynchronise tables. Reads from $0\.input_output\n";
print <<'EOF2';
Can only be run after the -f or -t options.
This option will prompt before constraints are switched off or updates are performed
-ct - Use the -ct <date> option to specify a greater than creation time
for the asynchrosity query.
The <date> format must be of the order 'dd-mmm-yyyy'.
-all - Use the -all option to bypass the need to say 'y' to each individual
update. This option will ask once and resynchronise all asynchronous data.
-stage - Staging Procedure Investigation.
Use this whenever the overnight staging procedures fail.
-t <PARENT TABLE> - Format parent and child tables from specified
parent ready for use in resynchronisation.
-clean - Removes Unnecessary files from the process after completion.
EOF2
print "\nBasic use:\nRun:";
print "\t 1. $0 -t (your database table name)\n";
print "\t 2. $0 -r \n";
print "\n";
exit 0;
} # end of usage
##############################################################################################
sub SpannerInWorks()
{
# Need to QuitCleanly in the case of some unexpected failure like a DBI die
$signame = shift;
QuitCleanly("Received SIG$signame. Cannot Continue, re-enabling all constraints",1,'replaceconstraints');
}
##############################################################################################
sub StagingFailure($stage,$method,@duplicateOID)
{
my($stage) = shift;
my($method) = shift;
my(@duplicateOID) = @_;
my($count,$rowcnt,$errorcount) = 0;
$status = 0;
if (($stage =~ /locationpoper/) and ($method =~ /find/))
{
@duplicateOID = ();
$sql = "\t\tSELECT ID, TABLE_NAME, TABLE_OID, NEW_HOLIDAY_TYPE, NEW_BROCHURE_ID, ERROR_DESCRIPTION
FROM MTC_LOCATION_POPER_LOG\@$database_link
WHERE DATE_CREATED >= sysdate - 2
";
$sql .= "OR DATE_CREATED >= sysdate - 4 --For testing only" if ($Testing);
print "$sql\n";
$sth = $dbh->prepare("$sql");
$sth->execute or die "Failed to perform SQL statement ... \n";
while($result = $sth->fetchrow_arrayref)
{
$message = "Failure Reason: $result->[5]";
LogData("$message",$Progress);
if ($result->[5] =~ m/duplicating key/)
{
# Create array with "table_name" and duplicate oid
$duplicateOID[$count] = { table => "$result->[1]",
oid => "$result->[2]" };
}else{
$message = "This failure is not due to a duplication. Please Investigate";
print "$message\n";
LogData("$message",$Progress);
}
print "Location Poper Log: ", @{ $result }, "\n" if ($debug);
$count++;
}
$sth->finish();
if ($DBI::rows == 0)
{
$message = "No rows returned, location poper was succesful";
print "$message\n";
LogData("$message",$Progress);
}
HashReader('print2d',@duplicateOID) if ($debug);
return(@duplicateOID);
# swithces here for between 'find' and 'fix' options for locationpoper
}elsif (($stage =~ /locationpoper/) and ($method =~ /fix/)) {
HashReader('print2d',@duplicateOID) if ($debug);
for($i = 0; $i <= $#duplicateOID; $i++)
{
my($table) = $duplicateOID[$i]{'table'};
my($mtc_code_3,$mtc_xxx_code);
($mtc_code_3 = $table) =~ s/^MTC_//;
$mtc_code_3 = unpack("A3", $mtc_code_3);
$exstat = 0;
# Find the MTC_XXX_CODE column name
$sql = "\t\tSELECT COLUMN_NAME
FROM USER_TAB_COLUMNS\@$database_link
WHERE TABLE_NAME = \'${table}\'
AND COLUMN_NAME LIKE \'MTC_%${mtc_code_3}%_CODE\'
";
print "$sql\n" if ($debug);
LogData("$sql",$Progress) if ($debug);
$sth = $dbh->prepare("$sql");
$sth->execute or die "Failed to perform SQL statement ... \n";
$result = $sth->fetchall_arrayref;
$sth->finish();
$mtc_xxx_code = $result->[0]->[0] if ($DBI::rows == 1);
$message = "Column name from $table is: $mtc_xxx_code \(of " . $DBI::rows . " Results)";
print "$message\n" if ($debug);
LogData("$message",$Progress) if ($debug);
unless ($mtc_xxx_code) {
$message = "Couldnt resolve mtc_xxx_code";
print "$message\n";
LogData("$message",$Progress);
return 1;
}
# This sql is ok for most of the things we use the poper on.
$sql = "\t\tSELECT ${table}_ID, TO_ID, HOLIDAY_TYPE, SEASON_CODE, BROCHURE_ID, ${mtc_xxx_code}
FROM ${table}\@$database_link
WHERE OID = \'$duplicateOID[$i]{'oid'}\'
";
print "$sql\n"; ### This will find the wrong oid info
LogData("$sql",$Progress);
$sth = $dbh->prepare("$sql");
$sth->execute or die "Failed to perform SQL statement ... \n";
$result = $sth->fetchrow_arrayref;
$sth->finish();
print "Query returns => ", join('::', @{ $result}), "\n" if ($debug);
$correctkey = "$result->[1]-$result->[2]-$result->[3]-$result->[4]-$result->[5]";
if ("$result->[0]" ne "$correctkey")
{
print "Current key =>$result->[0]<= ; should be =>$correctkey<=\n";
LogData("Found disparity. Current key is $result->[0], correct key should be $correctkey",$Progress);
print "\n------------------------- Location Poper Resynchronisation -------------------------\n\n";
print "\n\tChange Existing Primary Key:\t$result->[0]\n";
print "\n\t\t\t\t To:\t$correctkey\n";
print "\n\t Y/N ?\n\n";
$ans = <STDIN>;
chomp($ans);
next unless ($ans =~ /y/i);
LogData("User elected to update ${table} primary key",$Progress);
## query table to make sure an update wont clash
$rowcnt = DBUpdate('stage','rowcount',"${table}",'*',"WHERE ${table}_ID = \'$correctkey\'");
#$rowcnt += DBUpdate('live','rowcount',"${table}",'*',"WHERE ${table}_ID = \'$correctkey\'");
# Now that row above is a bit of a minefield quite frankly.
# If you leave it in, it will give you the impression that there are problems and stall
# everything when really were only concerned with sorting out prod stage.
# It also means that if you had previously fixed PS, but hadnt been able to fix PL
# then it will continue to throw up the error and you'd think that neither were
# fixed and keep going for ever.
# On the other hand taking it out means that if there is something up, you could fix the
# damage on PS or so you think and then when you run through it again it wont find any errors.
# But then you'll need to resync PL to PS in the normal manner.
if ($rowcnt != 0)
{
print "Ive got a row count = $rowcnt\n";
$message = "Update will cause clash with $rowcnt rows in database. Skipping...";
print "$message\n\n";
LogData("$message",$Progress);
next;
}else{
$message = "No existing rows with this key, can safely update";
print "$message\n\n";
LogData("$message",$Progress);
}
$exstat = DBUpdate('stage','update',"${table}",
"${table}_ID = \'$correctkey\'",
"WHERE ${mtc_xxx_code} = \'$result->[5]\' AND OID = \'$duplicateOID[$i]{'oid'}\' ");
$exstat += DBUpdate('live','update',"${table}",
"${table}_ID = \'$correctkey\'",
"WHERE ${mtc_xxx_code} = \'$result->[5]\' AND OID = \'$duplicateOID[$i]{'oid'}\' ");
print "\$exstat = $exstat \n" if ($debug);
if ($exstat)
{
$errorcount++;
$message = "Update was unsuccesful !!";
}else{
$message = "Update Succesful";
}
print "$message\n\n";
LogData("$message",$Progress);
## done update to both stage and live
}elsif ("$result->[0]" == "$correctkey") {
$message = "Primary Key Has Been Corrected";
print "$message\n\n";
LogData("$message",$Progress);
next;
}
}
} # end of locationpoper 'fix' option
return($errorcount);
} # end StagingFailure
##############################################################################################
sub DBUpdate($database,$query_or_update,$table,$select_clause,$where_clause)
{
my($database,$query_or_update,$table,$select_clause,$where_clause) = @_;
my($result);
${table} .= "\@$database_link" if ($database !~ /live/i);
if ($query_or_update !~ /update/i)
{
$sql = "\t\t\tSELECT $select_clause
FROM ${table}
";
}elsif ($query_or_update =~ /update/i) {
$sql = "\t\t\tUPDATE ${table}
SET $select_clause
";
}
$sql .= "$where_clause\n";
$sql .= "\t\t\tAND STATUS = 2\n" if ($Testing == 2); ## This is never going to be true
print "$sql\n";
$sth = $dbh->prepare("$sql");
$sth->execute or die "Failed to perform SQL statement ... \n";
$result = $sth->fetchall_arrayref unless ($query_or_update =~ /update/i);
$result = $result->[0];
$sth->finish();
return($DBI::rows) if ($query_or_update =~ /rowcount/i);
return($result) if ($query_or_update =~ /select/i);
return($DBI::err) if ($query_or_update =~ /update/i);
} # end
##############################################################################################
sub P_C_Link($oidish)
{
# Were only going to handle one piece of oid info at a time
# we can only do child tables with a 1 to 1 relationship - cant remember if this only applied to v 1.*
my($adultlevel) = shift;
my($oidish) = shift;
my(@oid_sturff) = ();
return() unless ($adultlevel < $levelcount);
($childlevel = $adultlevel)++;
print "\$adultlevel=$adultlevel ; \$levelcount=$levelcount\n" if ($debug);
$oidish =~ s/\s+//g;
return() unless ($oidish);
@oidishdata = split(':',$oidish);
# We need:-
# 1. To get an async oid from the info fed in
# 2. the link column name from the table which is the parent of the p-c relationship
# 3. the link column name from the table which is the child of the p-c relationship
# 4. To retreive the prodstage value for the child oid which comes from all this
# 5. To insert any extra rows that should be updated into the return package.
foreach (@oidishdata)
{
(($summat,$upper_level_table) = split('=>', $_)) if (/^PARENT_TABLE\=\>/);
(($summat,$oid_column_name) = split('=>', $_)) if (/^OID_COLUMN_NAME\=\>/);
(($summat,$refint_column_name) = split('=>', $_)) if (/^REFERENTIAL_KEY\=\>/);
(($summat,$WrongRefint_value) = split('=>', $_)) if (/^PL\.REFERENTIAL_VALUE\=\>/);
(($summat,$CorrectRefint_value) = split('=>', $_)) if (/^PS\.REFERENTIAL_VALUE\=\>/);
(($summat,$WrongValue) = split('=>', $_)) if (/^PL\.VALUE\=\>/);
(($summat,$CorrectValue) = split('=>', $_)) if (/^PS\.VALUE\=\>/);
(($summat,$referral_column_name) = split('=>', $_)) if (/^REFERRAL_COLUMN\=\>/);
(($summat,$referral_column_value) = split('=>', $_)) if (/^REFERRAL_COLUMN_VALUE\=\>/);
print "\@oidishdata :-->$_<--\n" if ($debug);
}
if ($debug) {
print "The Upper Level Table is:\t->$upper_level_table\n";
print "The OID column name is:\t->$oid_column_name\n";
print "The ref int column name is:\t->$refint_column_name\n";
print "The Wrong ref int value is:\t->$WrongRefint_value\n";
print "The Correct ref int value is:\t->$CorrectRefint_value\n";
print "The Referral Column name is:\t->$referral_column_name\n";
print "The Referral Column Value is:\t->$referral_column_value\n";
print "Wrong oid is:\t\t\t->$WrongValue\n";
print "Correct oid is:\t\t\t->$CorrectValue\n";
print "Using QueryHash($childlevel,$childlevel,\$query,\@EnabledCConstraints) childlevel = $childlevel\n";
}
# Get children of $upper_level_table
@KidList = QueryHash($childlevel,$childlevel,"CONSTRAINT_TYPE eq P",@DBRefData);
@KidList = QueryHash($childlevel,$childlevel,"KEY eq TABLE_NAME",@KidList);
@KidList = HashReader('array',@KidList);
foreach (@KidList)
{
s/\s+//g;
next if (/^$/);
(($blah,$blah1,$blah2,$child_table) = split(':',$_));
$child_table =~ s/^TABLE_NAME\=\>(.*)$/$1/;
$message = "Creating update package for child table: $child_table";
print "$message\n";
LogData("$message",$Progress);
@Childstuff = QueryHash($childlevel,$childlevel,"TABLE_NAME eq $child_table",@DBRefData);
print "Childstuff:\n" if ($debug);
HashReader('print',@Childstuff) if ($debug);
# find p - key then r - key
foreach (P,R) {
@P_or_Rkey = QueryHash($childlevel,$childlevel,"CONSTRAINT_TYPE eq $_",@Childstuff); # RKey of this child
@P_or_Rkey = QueryHash($childlevel,$childlevel,"KEY eq COLUMN_NAME",@P_or_Rkey);
@P_or_Rkey = HashReader('array',@P_or_Rkey);
foreach $P_or_Rkey (@P_or_Rkey){ (($level,$table,$constraint,$P_or_Rkey_values) = split(':', $P_or_Rkey)); }
$P_or_Rkey_values =~ s/(\s|\n)//g; # remove spaces and newline
($PrimaryKey = $P_or_Rkey_values) =~ s/^COLUMN_NAME\=\>(.*)$/$1/i if (/P/);
($ReferentialKey = $P_or_Rkey_values) =~ s/^COLUMN_NAME\=\>(.*)$/$1/i if (/R/);
}
### Only need to do any more if this is a middle level element(parent-child-grandchild set-up)
if ($childlevel < $levelcount) {
$plquery = "\t\tSELECT $PrimaryKey
FROM $child_table
WHERE $ReferentialKey in
(SELECT $oid_column_name
FROM $upper_level_table
WHERE $oid_column_name = \'$WrongValue\')
ORDER BY $PrimaryKey
";
print "$plquery\n"; ### This will find the wrong oid info
$sth = $dbh->prepare("$plquery");
$sth->execute or die "Failed to perform SQL statement ... \n";
while($result = $sth->fetchrow_arrayref)
{
$result->[0] =~ s/^\s*(.*)\s*$/$1/;
push(@plchild_results,$result->[0])
}
print "\@plchild_results = @plchild_results\n" if ($debug);
$psquery = "\t\tSELECT $PrimaryKey
FROM $child_table\@$database_link
WHERE $ReferentialKey in
(SELECT $oid_column_name
FROM $upper_level_table\@$database_link
WHERE $oid_column_name = \'$CorrectValue\')
ORDER BY $PrimaryKey
";
print "$psquery\n";
### This should find the right oid info - we can map them next to one another then
$sth = $dbh->prepare("$psquery");
$sth->execute or die "Failed to perform SQL statement ... \n";
while($result = $sth->fetchrow_arrayref)
{
$result->[0] =~ s/^\s*(.*)\s*$/$1/;
push(@pschild_results,$result->[0])
}
print "\@pschild_results = @pschild_results\n" if ($debug);
## Count both if they are different we may have to exit
$plchildren = @plchild_results;
$pschildren = @pschild_results;
if ($pschildren < $plchildren) {
$mesg = "More entries in prod live than prod stage for table: $child_table " .
"It is very unwise to try to update these automatically";
print "\n\t$mesg\n\n";
LogData("$mesg",$Progress);
QuitCleanly("Table: $child_table is not possible to update automatically... Cannot continue",1,'replaceconstraints');
}else{ ## We can carry on hopefully
for ($childcnt = 0; $childcnt < $plchildren; $childcnt++)
{
if ($debug)
{
print "We need to update the children of ->$upper_level_table<- \n";
print "Primary Key is $PrimaryKey : Ref Integrity Key is $ReferentialKey\n";
print "Where ->$upper_level_table<- = \'$WrongValue\'. " .
"The child is linked by column ->$ReferentialKey<- \n";
print "The Wrong Ref Integrity Value is ->$WrongValue<- : " .
"The Correct Ref Integrity Value is ->$CorrectValue<-\n";
}
$outputstr = "PARENT_TABLE => $child_table : OID_COLUMN_NAME => $PrimaryKey : " .
"REFERENTIAL_KEY => $ReferentialKey : " .
"PL.VALUE => $plchild_results[$childcnt] : " .
"PS.VALUE => $pschild_results[$childcnt] : " .
"REFERRAL_COLUMN => : REFERRAL_COLUMN_VALUE => : " .
"PL.REFERENTIAL_VALUE => $WrongValue : " .
"PS.REFERENTIAL_VALUE => $CorrectValue :";
print "My \$outputstr is ->$outputstr<-\n" if ($debug);
push(@oid_sturff,$outputstr);
}
}
}else{
## ie we have only got a simple hierarchy(parent, or parent-child)
if ($debug)
{
print "We need to update the children of ->$upper_level_table<- \n";
print "Primary Key is $PrimaryKey : Ref Integrity Key is $ReferentialKey\n";
print "Where ->$upper_level_table<- = \'$WrongValue\'. The child is linked by column ->$ReferentialKey<- \n";
}
$outputstr = "PARENT_TABLE => $child_table : OID_COLUMN_NAME => $PrimaryKey : " .
"REFERENTIAL_KEY => $ReferentialKey : " .
"PL.VALUE => $WrongValue : PS.VALUE => $CorrectValue : " .
"REFERRAL_COLUMN => : REFERRAL_COLUMN_VALUE => :";
print "My \$outputstr is ->$outputstr<-\n" if ($debug);
push(@oid_sturff,$outputstr);
}
}
return(@oid_sturff);
} # end of P_C_Link
##############################################################################################
sub CheckConstraints
{
# Find what was on
my(@EnabledData) = ();
%ConstraintInfo = ReadIOFile("${constraintlist}.enabled.log");
print "From constraintlist:\n" if ($debug > 1);
foreach $level (sort keys(%ConstraintInfo)){
foreach $table (sort keys(%{ $ConstraintInfo{$level} })){
foreach $constraint (sort keys(%{ $ConstraintInfo{$level}{$table} })){
$Info = $ConstraintInfo{$level}{$table}{$constraint};
print "Level->$level<- \nTable ->$table<-\n" if ($debug > 1);
print "Constraint ->$constraint<- \nInfo ->$Info<-\n" if ($debug);
@CurrentStatus = DBLinkInfo(0,$table);
print "\@CurrentStatus=>@CurrentStatus\n" if ($debug > 1);
foreach $constraint ($#CurrentStatus){
foreach $value (sort keys(%{$CurrentStatus[$constraint]})) {
$ReEnabledStatus = $CurrentStatus[$constraint]{'STATUS'};
print "CurrentStatus says constraint = $constraint vals = $CurrentStatus[$constraint]{$value}\n" if ($debug);
push(@EnabledData, "$table") if ($ReEnabledStatus =~ m/DISABLED/i);
}
}
}
}
}
@EnabledData = MergeArrays(@EnabledData); # just sorts them uniqely
return(@EnabledData);
}
##############################################################################################
sub UpdateTable($level,@asyncoids)
{
my($level) = shift;
my(@asyncoids) = @_;
my($table,$ColName,$WrongOID,$NewOID,$RefColumn,$Table_ID,$Refint_Key,$WrongRefint_value,$CorrectRefint_value);
# Children then Parent
chomp(@asyncoids);
print "\@asyncoids---->@asyncoids<----\n" if ($debug);
foreach $x (@asyncoids)
{
foreach(split(/:/, $x)){
chomp;
# Pre version 5 I tried this lot:
#s/(\s|\n)//g; ## takes out spaces -- had to alter this for cases where a name has spaces in it
#s/\s*(\=\>)\s*/$1/; ## takes out spaces each side of delimmiter -- altering again
#s/^(?:\s*)(.*\=\>.*?)(?:\s*)$/$1/; ## takes out leading and trailing spaces
# anyhow these are the right ones to use:
s/^\s*//; ## takes out spaces before variable name
s/\s*(\=\>)/$1/; ## takes out spaces before delimmiter
s/(\=\>)\s/$1/; ## takes out single leading space from the value
s/\s$//; ## takes out single trailing space from the value
# Dont alter this now - there are too many different scenarios to beat this is best
print "->$_<-\n" if ($debug); ## Displays each element of this array
(($discard,$table) = split(/=>/,$_)) if (/^PARENT_TABLE\=\>\w+/i); ## The Parent table name if we need
(($discard,$ColName) = split(/=>/,$_)) if (/^OID_COLUMN_NAME\=\>\w+/i); ## The oid column - pkey
(($discard,$WrongOID) = split(/=>/,$_)) if (/^PL\.VALUE\=\>(?:-|)\d+/i); ## The out of sync PL.OID column
(($discard,$NewOID) = split(/=>/,$_)) if (/^PS\.VALUE\=\>(?:-|)\d+/i); ## The correct PS.OID column
(($discard,$RefColumn) = split(/=>/,$_)) if (/^REFERRAL_COLUMN\=\>\w+/i);
(($discard,$Table_ID) = split(/=>/,$_)) if (/^REFERRAL_COLUMN_VALUE\=\>\w+/i);
(($discard,$Refint_Key) = split('=>', $_)) if (/^REFERENTIAL_KEY\=\>/);
(($discard,$WrongRefint_value) = split('=>', $_)) if (/^PL\.REFERENTIAL_VALUE\=\>/);
(($discard,$CorrectRefint_value) = split('=>', $_)) if (/^PS\.REFERENTIAL_VALUE\=\>/);
}
print "Synchronising $table\n";
#### Adding Check to ensure that there is no chance of oids clashing by shifting our info
#### into a temporary oid space, deleting the new and then updating from the temp space
#### into the freshly emptied space
$message = "Checking to ensure no existing oid of value:$NewOID";
print "$message\n";
LogData("$message",$Progress);
$query = "\t\tSELECT COUNT($ColName) FROM $table WHERE $ColName = $NewOID";
print "$query\n" if ($debug);
$sth = $dbh->prepare("$query");
$sth->execute or die "Failed to perform SQL statement ... \n";
$result = $sth->fetchrow_arrayref;
($result = $result->[0]) =~ s/^\s*(.*)\s*$/$1/;
print "No of rows =$result\n" if ($debug);
if ($result) {
$message = "Found existing content for $ColName value:$NewOID. Removing";
print "$message\n";
LogData("$message",$Progress);
$query = "\t\tDELETE FROM $table WHERE $ColName = $NewOID";
print "\n", $query, "\n" if ($debug);
LogData("SQL -->$query<--",$Progress);
$sth = $dbh->prepare("$query") unless ($Testing);
$sth->execute or die "Failed to perform SQL statement ... \n" unless ($Testing);
}
$message = " Level=>$level<= : Table =>$table<= Column Name =>$ColName<= Referral Column =>$RefColumn<=" .
" Wrong (current) $ColName =>$WrongOID<= New (PS) $ColName =>$NewOID<=";
print "$message\n" if ($debug);
LogData("About to Perform Update for:$message",$Progress);
if (($RefColumn) and ($Table_ID)) {
$query = "\t\tUPDATE $table
SET $ColName = $NewOID
WHERE $RefColumn = \'$Table_ID\'
AND $ColName = $WrongOID";
}elsif(($Refint_Key ne $ColName) and ($CorrectRefint_value)){
$query = "\t\tUPDATE $table
SET $ColName = $NewOID, $Refint_Key = $CorrectRefint_value
WHERE $ColName = $WrongOID
AND $Refint_Key = $WrongRefint_value";
}elsif(($Refint_Key ne $ColName) and (! $CorrectRefint_value)){
## Added this one to cater for bv_content_ref mainly which uses parent_oid not oid
$query = "\t\tUPDATE $table
SET $Refint_Key = $NewOID
WHERE $Refint_Key = $WrongOID";
}else{
$query = "\t\tUPDATE $table
SET $ColName = $NewOID
WHERE $ColName = $WrongOID";
}
print "\n", $query, "\n";
LogData("SQL -->$query<--",$Progress);
$sth = $dbh->prepare("$query") unless ($Testing);
$sth->execute or die "Failed to perform SQL statement ... \n" unless ($Testing);
$complete = $DBI::err;
print " ";
print " " . ($complete == 0) ? 'Succesful' : 'Failed !';
if ($DBI::err){ print " $ThisCon - Error: $DBI::errstr"; $success = 1;
}else{
$success = 0;
LogData("Successful Update: ->$table<- Set ->$ColName<- to ->$NewOID<- " .
"Where ->$RefColumn<- Equals ->$Table_ID<- and ->$ColName<- Was ->$WrongOID<-",$Progress);
}
print "\n";
}
} # end of UpdateTable
##############################################################################################
sub AlterConstraints($on_or_off,@list)
{
my($on_or_off) = shift;
my(@list) = @_;
my($success) = 1 if (@list); # we start with a failed status
my(@return) = ();
$incount = 0;
#HashReader('print',@list); # Comment this when testing finished
$on_or_off = 'ENABLE' if ($on_or_off =~ m/enable/i); # just in case - always better to enable
$on_or_off = 'DISABLE' if ($on_or_off =~ m/disable/i);
foreach $level ( 0 .. $#list ) {
foreach $table (sort keys(%{ $list[$level] })) {
foreach $con ( 0 .. $#{ $list[$level]{$table} }) {
next unless ($ThisCon = $list[$level]{$table}[$con]{CONSTRAINT_NAME}); # If con 0 is a Ref Int constrnt
print " Need to =>$on_or_off<= CONSTRAINT_NAME ->$ThisCon<-\n" if ($debug > 1);
$query = "\tALTER TABLE $table $on_or_off CONSTRAINT $ThisCon";
print "\n", $query, "\n";
$sth = $dbh->prepare("$query");
$sth->execute or die "Failed to perform SQL statement ... \n";
$incount++;
$complete = $DBI::err;
print " Alteration: "; print " " . ($complete == 0) ? 'Succesful' : 'Failed !';
if ($DBI::err){ print " $ThisCon - Error: $DBI::errstr"; $success = 1;
LogData("Couldnt Alter \(" . lc(${on_or_off}) . "\) -> $ThisCon for table: $table",$Progress);
}else{
$success = 0;
$ret = "LEVEL : $level : TABLE : $table : CONSTRAINT : $con : TABLE_NAME : $table : STATUS :" .
" ${on_or_off}D : CONSTRAINT_NAME : $ThisCon";
LogData("Altered\(" . lc(${on_or_off}) . "d\) -> $ret",$Progress);
push(@return,"$ret\n");
}; print "\n";
}
}
}
$outcount = @return;
$success = 1 unless ($outcount == $incount);
return($success,@return);
} # end of AlterConstraints
##############################################################################################
sub ExitOnDemand(@commands)
{
my(@commands) = @_;
while (<STDIN>){
chomp;
unless (m/(y)/i) {
print "Quitting\n";
&MultiParentHandler('finish') if ($multi_table_resync);
foreach (@commands) {
if (/replaceconstraints/i){
LogData("User opted to quit",$Progress);
QuitCleanly('Constraints are still disabled - Re-enabling',0,'replaceconstraints');
}
}
LogData("User opted to quit",$Progress);
QuitCleanly('',0);
}
last;
}
} # end of ExitOnDemand
##############################################################################################
sub QuitCleanly($message,$status,@commands)
{
my($message) = shift;
my($status) = shift;
my(@commands) = @_;
LogData("$message",$Progress) and print "$message \n" if ($message);
foreach (@commands) {
if (/replaceconstraints/i) {
($p,@plist) = &AlterConstraints("enable",@EnabledPConstraint);
($c,@clist) = &AlterConstraints("enable",@EnabledCConstraints);
print "Parent Re-Enable Success = $p : Child Re-Enable Success = $c\n";
LogData("Re-enabling Constraints :- Parent Re-Enable Success = $p : Child Re-Enable Success = $c",$Progress);
if (($p == 0) and ($c == 0)) {
qx!mv -f "${constraintlist}.disabled.log" "${constraintlist}.enabled.log"!;
print "Renamed ${constraintlist}.disabled.log\n";
}
}
}
$SIG{__DIE__} = $SIG{QUIT} = $SIG{INT} = 'DEFAULT';
$dbh->disconnect;
&BVCustomPerl::LogFooter($status,$LogFile,$MethodName,$user,$TimeLine);
if ($status =~ /^0$/){ exit 0; }elsif($status =~ /^1$/){ exit 1; }else{ exit $status;}
} # end of QuitCleanly
##############################################################################################
sub HashReader($displaytype,@inhash)
{
my ($displaytype) = shift; ## You can use 'print' Or 'array' or 'print2d'
my (@inhash) = @_;
my (@outhash) = ();
$displaytype = lc($displaytype);
for $level ( 0 .. $#inhash )
{
print "Now on level $level\n" if ($displaytype =~ m/print/);
foreach $table ( keys( %{ $inhash[$level]} )) {
print "Level => $level : Key => $table : Values => $inhash[$level]{$table}\n" if ($displaytype =~ m/print2d/);
for $constraint ( 0 .. $#{ $inhash[$level]{$table} } )
{
foreach $dbkeyinfo (keys( %{ $inhash[$level]{$table}[$constraint]} ))
{
$output = "Level => $level : Table => $table : Constraint No => $constraint : " .
"$dbkeyinfo => $inhash[$level]{$table}[$constraint]{$dbkeyinfo}";
print "$output\n" if ($displaytype =~ m/print/);
push(@outhash,"$output\n") if ($displaytype =~ m/array/);
}
}
}
}
return(@outhash) if ($displaytype =~ m/array/);
} # end of HashReader
##############################################################################################
sub Asynchrosity(@data)
{
my(@data) = @_;
my($ParentTable) = sort keys(%{ $data[0]});
my(@AsynchrousOIDs,@atts) = ();
my($ParentColumnName);
foreach $constraint_no (0 .. $#{ $data[0]{$ParentTable} })
{
$ParentColumnName = $data[0]{$ParentTable}[$constraint_no]{'COLUMN_NAME'};
last if ($data[0]{$ParentTable}[$constraint_no]{'CONSTRAINT_TYPE'} eq 'P');
}
## Get the name thats basically the mtc_blahblah_id name - occaisionally its different
## Need to find Referential Integrity column name
## and primary key for each of these tables
$query = "\t\tSELECT * FROM
( SELECT ATTRIBUTE_NAME
FROM BV_ATTRIBUTES
WHERE TABLE_NAME = \'$ParentTable\'
AND ATTRIBUTE_NAME != 'OID'
AND ROWNUM = 1
ORDER BY ATTRIBUTE_ID )
";
print $query, "\n" if ($debug);
$sth = $dbh->prepare("$query");
$sth->execute or die "Failed to perform SQL statement ... \n";
while($result = $sth->fetchrow_arrayref)
{
$result->[0] =~ s/^\s*(.*)\s*$/$1/;
push(@atts,$result->[0])
}
$ParentRefColumnName = $atts[0];
$XMLid_exists = ($atts[1]) ? "$atts[1]" : '';
$sth->finish();
# print "Asynchrosity gives \$ParentTable as :$ParentTable: and \$ParentColumnName as :$ParentColumnName:\n",
# "and \$ParentRefColumnName as :$ParentRefColumnName: XMLid_exists as :$XMLid_exists:\n";
# Need to die if there's no $ParentRefColumnName
print " Analysis Gives \'$ParentRefColumnName\' ";
print "and $XMLid_exists column exists" if ($XMLid_exists);
print "\n Using Creation Time of \'$useCreationDate\' \n" if ($useCreationDate);
print "\n\n";
&QuitCleanly('Failed to find referral column name. The following sql will be unsafe!',1) unless ($ParentRefColumnName);
LogData("Found referral column for $ParentTable =>$ParentRefColumnName<=",$Progress);
LogData("Checking XML_ID column existence =>$XMLid_exists<=",$Progress);
###### This is the query we need for actual production of this script
$query = "\t\tSELECT PL.$ParentColumnName, PS.$ParentRefColumnName, PS.$ParentColumnName, PS.$ParentRefColumnName
FROM $ParentTable PL, $ParentTable\@$database_link PS
WHERE PS.DELETED = 0
AND PL.$ParentRefColumnName = PS.$ParentRefColumnName
AND PS.$ParentColumnName <> PL.$ParentColumnName \n";
$query .= "\t\tAND PL.CREATION_TIME >= \'$useCreationDate\' \n" if ($useCreationDate);
print "$query\n";
###### Test query TAKE OUT when producing
if ($Testing == 2) {
$query = "\t\tSELECT PL.$ParentColumnName, PS.$ParentRefColumnName, PS.$ParentColumnName, PS.$ParentRefColumnName
FROM $ParentTable PL, $ParentTable\@$database_link PS
--FROM $ParentTable PL, $ParentTable PS
WHERE PS.DELETED = 0
AND PL.$ParentRefColumnName = PS.$ParentRefColumnName
--AND PS.$ParentColumnName <> PL.$ParentColumnName
AND lower(PS.$ParentRefColumnName) like '%test%'
--AND PS.$ParentRefColumnName like '5-0-S2002-%'
AND rownum < 3
";
$query .= "AND PS.XML_ID IS NOT NULL" if ($XMLid_exists);
print "Testing Query ->\n", $query, "\n";
}
LogData("Preparing sequel for oid asynchrosity check. If this fails check were running this on Prod Live " .
"and that database link has not changed from \'$database_link\'\.",$Progress);
print "\n\tIf db prepare fails below - Its possibly because you are not running this command on Production Live\n" .
"\tor because the name of the database link has changed from \'$database_link\'\.\n\n";
LogData("Sql: $query",$Progress) if ($debug);
$sth = $dbh->prepare("$query");
$sth->execute or die "Failed to perform SQL statement ... \n";
while ($result = $sth->fetchrow_arrayref)
{
$data = "PARENT_TABLE => $ParentTable : OID_COLUMN_NAME => $ParentColumnName : " .
"REFERENTIAL_KEY => $ParentColumnName : PL.VALUE => $result->[0] : PS.VALUE => $result->[2] : " .
"REFERRAL_COLUMN => $ParentRefColumnName : REFERRAL_COLUMN_VALUE => $result->[1] :";
push(@AsynchrousOIDs,"$data\n");
LogData("Asynchronous => $data",$Progress);
}
$sth->finish();
return(@AsynchrousOIDs);
} # end of Asynchrosity
##############################################################################################
sub WriteHash($outputtype,$outputname,@dbhash)
{
my($outputtype) = shift;
my($outputname) = shift;
my(@dbhash) = @_;
my(@Output) = ();
if ($outputtype =~ m/file/i) {
open(OUT,">>$outputname") or die "Cant write to $outputname\n";
LogData("Writing to: $outputname","WriteHash routine called");
}
foreach $level ( 0 .. $#dbhash ) {
foreach $table ( keys( %{ $dbhash[$level]} )) {
foreach $constraint ( 0 .. $#{ $dbhash[$level]{$table} } ) {
if ($dbhash[$level]{$table}[$constraint]) {
#@Output = (); # Take this out if need to empty the array each time
push(@Output,"LEVEL => $level : TABLE => $dbhash[$level]{$table}[$constraint]{'TABLE_NAME'} : ");
push(@Output,"CONSTRAINT => $constraint : ");
foreach $key (keys %{ $dbhash[$level]{$table}[$constraint] }) {
push(@Output,"$key => $dbhash[$level]{$table}[$constraint]{$key} : ");
}
if ($outputtype =~ m/file/i) {
print OUT @Output, "\n";
LogData("Logging: @Output","WriteHash Routine");
@Output = ();
}
}
}
}
}
close(OUT) if ($outputtype =~ m/file/i);
} # end of WriteHash
##############################################################################################
sub QueryHash($startlevel,$endlevel,$query,@inputhash)
{
my($startlevel) = shift;
my($endlevel) = shift;
my($query) = shift;
my(@inputhash) = @_;
my(@returnhash,@query) = ();
@query = split(/ /, $query);
$find = "$query[0]";
$criteria = $query[2];
foreach $level ( $startlevel .. $endlevel ) {
foreach $table ( keys( %{ $inputhash[$level]} )) {
foreach $constraint ( 0 .. $#{ $inputhash[$level]{$table}} ) {
if ($find !~ m/^KEY$/i) {
if (($query[1] =~ m/eq/) and ($inputhash[$level]{$table}[$constraint]{$find} =~ m/^$criteria$/ )) {
foreach $key ( keys( %{ $inputhash[$level]{$table}[$constraint]} )) {
$returnhash[$level]{$table}[$constraint]{$key} = $inputhash[$level]{$table}[$constraint]{$key};
}
}elsif (($query[1] =~ m/ne/) and ( $inputhash[$level]{$table}[$constraint]{$find} !~ m/^$criteria$/ )) {
foreach $key ( keys( %{ $inputhash[$level]{$table}[$constraint]} )) {
$returnhash[$level]{$table}[$constraint]{$key} = $inputhash[$level]{$table}[$constraint]{$key};
}
}elsif(($query[1] !~ m/ne/) and ($query[1] !~ m/eq/)){
print "No criteria to restrict QueryHash for $level, $table, $constraint - Returning all data\n";
foreach $key ( keys( %{ $inputhash[$level]{$table}[$constraint]} )) {
$returnhash[$level]{$table}[$constraint]{$key} = $inputhash[$level]{$table}[$constraint]{$key};
}
}
}elsif($find =~ m/^KEY$/i) {
## In this case criteria will be the key in the key/value pair
if (($query[1] =~ m/eq/) and ($inputhash[$level]{$table}[$constraint]{$criteria} )) {
$returnhash[$level]{$table}[$constraint]{$criteria} = $inputhash[$level]{$table}[$constraint]{$criteria};
}
}
}
}
}
return(@returnhash);
} # end of QueryHash
##############################################################################################
sub DBLinkInfo($printsql,@TableList)
{
my($printsql) = shift;
my(@TableList) = @_;
my(@DBreturn,@NewTableList) = ();
foreach (@TableList)
{
push(@NewTableList,"\'$_\'");
}
@TableList = join(",", @NewTableList);
## Need to find Referential Integrity column name
## and primary key for each of these tables
$query = "\t\tSELECT COL.TABLE_NAME, CON.CONSTRAINT_TYPE, CON.STATUS, COL.COLUMN_NAME, COL.POSITION, COL.CONSTRAINT_NAME
FROM USER_CONSTRAINTS CON, USER_CONS_COLUMNS COL
WHERE CON.CONSTRAINT_NAME = COL.CONSTRAINT_NAME
AND CON.CONSTRAINT_TYPE in ('P','R')
AND COL.POSITION = 1
AND COL.CONSTRAINT_NAME in
(SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME in (@TableList))";
print $query, "\n" if ($printsql);
$sth = $dbh->prepare("$query");
$sth->execute or die "Failed to perform SQL statement ... \n";
$constraint_number = 0;
while ($result = $sth->fetchrow_arrayref)
{
foreach $column ($result)
{
#print "Constraint=>$constraint_number : ";
#print "$column->[0] : $column->[1] : $column->[2] : $column->[3] : $column->[4] : $column->[5]\n";
$DBreturn[$constraint_number]
= {
TABLE_NAME => "$column->[0]",
CONSTRAINT_TYPE => "$column->[1]",
STATUS => "$column->[2]",
COLUMN_NAME => "$column->[3]",
POSITION => "$column->[4]",
CONSTRAINT_NAME => "$column->[5]",
};
$constraint_number++;
}
}
$sth->finish();
foreach ($DBreturn{'CONSTRAINT_NAME'}) { print " Analysed DB links for: $_[0] \n"; }
return(@DBreturn);
} # end of DBLinkInfo
##############################################################################################
sub ReadIOFile($file)
{
my($file) = shift;
my(%data) = ();
open(IN,"<${file}") or die "Unable to read $file\n";
while(<IN>)
{
chomp;
print "Reading $_\n" if ($debug);
s/#.*//g;
s/\s*(\S+)\s*/$1/g;
next unless ($_);
@rowdata = split(/:/,$_);
($discard, $levelvalue) = split(/=>/, shift(@rowdata));
($discard, $tablevalue) = split(/=>/, shift(@rowdata));
$data{$levelvalue} = $tablevalue unless (@rowdata);
next unless (@rowdata);
($discard, $constraintvalue) = split(/=>/, shift(@rowdata));
($rest_of_data = join(':', (@rowdata)) );
$data{$levelvalue}{$tablevalue}{$constraintvalue} = $rest_of_data;
}
close(IN);
return(%data);
} # end of ReadIOFile
##############################################################################################
sub MultiParentHandler($action,%hierarchy)
{
my($action) = shift;
my(%hierarchy) = @_;
my($FileRow,@TableData,$level,$genericheader,@return);
chomp(my $D8 = `date`);
chomp(my $USER = `env | grep LOGNAME | cut -f2 -d=`);
if ($#{$hierarchy{0}} > 0) { print "You arent using this subroutine correctly\n"; return; }
if ($action eq 'new') {
%main::MultiTblDets = &MultiParentHandler('load');
qx!rm -f $InputOutputFile_2! if (-e $InputOutputFile_2);
open(MULTIPARENT,">$MultiParentlog") or die "Cant write to $MultiParentlog";
print MULTIPARENT $header1, $header_a, $header2;
close(MULTIPARENT);
open(IOFILE_2,">$InputOutputFile_2") or die "Cant write to $InputOutputFile_2";
print IOFILE_2 $header1, $header_b, $header2;
close(IOFILE_2);
return(%MultiTblDets);
}elsif ($action eq 'load') {
$genericheader = "\n-------------------------- Multiple-Table Session --------------------------\n\n";
$genericheader = "\n-------------------------- Multiple-Table Session --------------------------\n\n";
$header1 = "# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #\n";
$header_a = "# # # # # # # # # # # # # # Multiple Parent Log # # # # # # # # # # # # # #\n";
$header_b = "# # # # # # # # # # # # # # Input-Ouput File II # # # # # # # # # # # # # #\n";
$header2 = "# Created by: $USER\n" .
"# On: $D8\n" .
"# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #\n";
$MultiTblDets{'io2'}{'header'} = $header1 . $header_b . $header2;
$MultiTblDets{'io2'}{'separator'} = [ 'PARENT','STATUS' ];
return(%MultiTblDets);
}elsif ($action eq 'add') {
# There should only be one parent - level 0
my $parent = $hierarchy{0}[0];
## add this to the multi-parent log and an entry into iofile2
open(MULTIPARENT,">>$MultiParentlog") or die "Cant append to $MultiParentlog";
open(IOFILE_2,">>$InputOutputFile_2") or die "Cant append to $InputOutputFile_2";
print MULTIPARENT "# Table Data Start:$parent\n";
foreach $level (sort keys(%hierarchy)) {
print MULTIPARENT "LEVEL => $level : TABLES => ", join(',', @{$hierarchy{$level}}), "\n";
print "Parent: $parent : Hierarchy level $level = ", join(',', @{$hierarchy{$level}}), "\n" if ($debug);
#LogData("Hierarchy level $level = $FailedHierarchy{$level}",$Progress);
}
print MULTIPARENT "# Table Data End:$parent\n";
print MULTIPARENT "# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #\n";
print IOFILE_2 "PARENT => $parent : STATUS => Unstarted\n";
close(MULTIPARENT);
close(IOFILE_2);
}elsif ($action eq 'reload') {
# Check which parent were doing next.
my($parent);
%{$MultiTblDets{'state'}} = &ReadIOFile($InputOutputFile_2);
foreach (sort keys(%{$MultiTblDets{'state'}})) {
print "\'load\' state -> \$_=$_ - contents -> $MultiTblDets{'state'}{$_}\n" if ($debug > 1);
if ($MultiTblDets{'state'}{$_} =~ /Unstarted/i) {
$parent = $_;
last;
}
}
if (! $parent) {
# No more unstarted ones:
# 1. check that there arent any skipped ones
# 2. if so tell user to redo these
# 3. reset all of them to Unstarted and exit cleanly
print $genericheader;
print "\tAll Tables Have now been processed.\n";
LogData("All Tables Have now been processed",$Progress);
if (@rv = &MultiParentHandler('check')) {
print "\t User skipped resynchronisation of: @rv\n";
print "\t It would be advisable to re-run the process\n\n";
LogData("User skipped resynchronisation of: @rv",$Progress);
}
return(0);
}
## read from multi-parent log
open(MULTIPARENT,"<$MultiParentlog") or die "Cant read $MultiParentlog";
print "Reading $MultiParentlog\n" if ($debug);
my ($keep) = 0;
while ($FileRow = <MULTIPARENT>) {
$keep++ if ($FileRow =~ /Table Data Start\:$parent/);
next unless ($keep);
last if ($FileRow =~ /Table Data End\:$parent/);
push(@TableData,$FileRow);
}
close(MULTIPARENT);
$TableData[0] = "# Failed Tables (Multiple Resynchronisation):\n";
# Remove and re-write the input-output file
chmod(0777,$InputOutputFile_1) if (-e $InputOutputFile_1);
open(IOFILEOUT,">$InputOutputFile_1") or die "Cant write to $InputOutputFile_1";
print IOFILEOUT "@TableData\n";
close(IOFILEOUT);
$MultiTblDets{'io1'}{'data'} = join('', @TableData);
&WriteIOFile($InputOutputFile_1,'multiple',\%{$MultiTblDets{'io1'}});
$MultiTblDets{'state'}{$parent} = 'Started';
$MultiTblDets{'current'} = $parent;
&WriteIOFile($InputOutputFile_2,'multiple',\%{$MultiTblDets{'io2'}},%{$MultiTblDets{'state'}});
return(1);
}elsif ($action eq 'unload') {
%{$MultiTblDets{'state'}} = &ReadIOFile($InputOutputFile_2);
$parent = $MultiTblDets{'current'};
$MultiTblDets{'state'}{$parent} = 'Finished';
&WriteIOFile($InputOutputFile_2,'multiple',\%{$MultiTblDets{'io2'}},%{$MultiTblDets{'state'}});
}elsif ($action eq 'ask_user') {
print $genericheader;
print "\tReady to check for asynchronisation of: $MultiTblDets{'current'}\n\n";
print "\tContinue?\n\n";
if (<STDIN> =~ m/n/i) {
%{$MultiTblDets{'state'}} = &ReadIOFile($InputOutputFile_2);
$parent = $MultiTblDets{'current'};
print " Skipping resynchronisation of $parent.\n";
LogData("User opted to skip resynchronisation of $parent.",$Progress);
$MultiTblDets{'state'}{$parent} = 'Skipped';
&WriteIOFile($InputOutputFile_2,'multiple',\%{$MultiTblDets{'io2'}},%{$MultiTblDets{'state'}});
return(1);
}
}elsif ($action eq 'check') {
@return=();
foreach (sort keys(%{$MultiTblDets{'state'}})) {
print "\'check\' state -> \$_=$_ - contents -> $MultiTblDets{'state'}{$_}\n" if ($debug > 1);
push(@return,$_) if ($MultiTblDets{'state'}{$_} =~ /Skipped/i);
}
print "\'check\' \@return=@return\n" if ($debug > 1);
return(@return);
}elsif ($action eq 'finish') {
%{$MultiTblDets{'state'}} = &ReadIOFile($InputOutputFile_2);
unlink("$InputOutputFile_2.old") if (-e "$InputOutputFile_2.old");
rename("$InputOutputFile_2","$InputOutputFile_2.old");
foreach (sort keys(%{$MultiTblDets{'state'}})) {
print "\'finish\' state -> \$_=$_ - contents -> $MultiTblDets{'state'}{$_}\n" if ($debug);
$MultiTblDets{'state'}{$_} = 'Unstarted';
}
&WriteIOFile($InputOutputFile_2,'multiple',\%{$MultiTblDets{'io2'}},%{$MultiTblDets{'state'}});
}
} # end of MultiParentHandler
##############################################################################################
sub WriteIOFile($iofile,$type,$extra_info,%IOHash)
{
my($iofile) = shift;
my($type) = shift;
my($extra_info) = shift;
my(%IOHash) = @_;
my($header,@separator,$parenttable,$level);
if ($debug > 1) {
foreach (keys (%IOHash)) {
print "WriteIOFile->\$IOHash{$_} = $IOHash{$_}\n";
}
print "WriteIOFile->\$\$extra_info{'data'}=$$extra_info{'data'}\n";
print "WriteIOFile->\$\$extra_info{'header'}=$$extra_info{'header'}\n";
print "WriteIOFile->\@separator=@separator\n";
foreach (keys (%{$extra_info})) {
print "WriteIOFile->\$extra_info{$_} = $extra_info->{$_}\n" unless (/^(data|header|separator)$/);
}
}
chmod(0777,$iofile) if (-e $iofile);
open(IOFILE,">$iofile") or die "Cant write to $iofile\n";
if ((! $type) or ($type eq 'normal')) {
$header = "# Input-Output File: $iofile\n" . "# Failed Tables:\n";
@separator = ('LEVEL','TABLES');
}elsif($type eq 'multiple') {
$header = $$extra_info{'header'};
for (@$extra_info{'separator'}) { @separator = @$_; }
}else{
print "Incorrect use of \&WriteIOFile\n"; return();
}
if ($$extra_info{'data'}) {
print IOFILE $$extra_info{'data'};
}else{
print IOFILE $header;
foreach $parenttable (sort keys(%IOHash)) {
if ($type eq 'multiple') {
print IOFILE "$separator[0] => $parenttable : $separator[1] => $IOHash{$parenttable}\n";
}
foreach $level (sort keys(%{$IOHash{$parenttable}})) {
print "Writing IO File Hierarchy level $level = @{$IOHash{$parenttable}{$level}}\n" if ($debug);
LogData("Hierarchy level $level = @{$IOHash{$parenttable}{$level}}",$Progress);
#print IOFILE "LEVEL => $level : TABLES => ", join(',', @{$IOHash{$parenttable}{$level}}), "\n";
print IOFILE "$separator[0] => $level : $separator[1] => ",
join(',', @{$IOHash{$parenttable}{$level}}), "\n";
}
}
}
close(IOFILE);
chmod(0444,$iofile);
return();
} # end of WriteIOFile
##############################################################################################
sub AnalyseHierarchy(@RawTables)
{
###### This section iterates through the failed table list
###### and finds the parent, children, and grandchildren if they exist
my(@RawTables) = @_;
my(%Parent_List,$ParentTable) = ();
print"Finding Hierarchy Levels\n\n";
@RemainingTables = @RawTables;
# Do parent, child, grandchild
print "Finding Parent Tables\n";
LogData("Finding Parent Tables from @RawTables",$Progress);
@ParentList = findRelationTable('parent',@RawTables);
while ($ParentTable = shift(@ParentList)) {
## Check that these dont have grandparents
my(@GrandParentTable) = ();
print "Checking for Grandparents of $ParentTable ";
@GrandParentTable = findRelationTable('grandma',$ParentTable) unless ($ParentTable eq 'BV_COLLECTION');
# we know that we can resynch from bv_collection downwards
# even though it has greatgrandparents
if ($GrandParentTable[0]) {
print "\nFound Grandparent: @GrandParentTable\n";
LogData("Found Grandparent for $ParentTable: @GrandParentTable",$Progress);
# One grandparent is fine - multiple is not good
if ($GrandParentTable[1]) {
print "Got multiple Grandparents!!!\n" .
"Unable to determine which one to use. This must be done manually using the:\n" .
"\t\t $0 -t $ParentTable \n option.\n";
LogData("Found Multiple Grandparents for $ParentTable - Removing and continuing.",$Progress);
@ParentList = reduceArray($ParentTable,@ParentList);
next;
}
push(@ParentList,$GrandParentTable[0]);
next;
}else{
print "- No\n";
}
@{$FailedHierarchy{$ParentTable}{'0'}} = $ParentTable;
@ChildTables = findRelationTable('child',$ParentTable);
@{$FailedHierarchy{$ParentTable}{'1'}} = @ChildTables if (@ChildTables);
foreach $child (@ChildTables) {
@GrandChildTables = findRelationTable('child',$child);
@{$FailedHierarchy{$ParentTable}{'2'}} = @GrandChildTables if (@GrandChildTables);
foreach $grandchild (@GrandChildTables) {
## Just check we havent got great grand-children
@GreatGrandChildTables = findRelationTable('child',$grandchild);
@{$FailedHierarchy{$ParentTable}{'3'}} = @GreatGrandChildTables if (@GreatGrandChildTables);
@RemainingTables = reduceArray($grandchild,@RemainingTables); # Keep a tab on what we have left
}
@RemainingTables = reduceArray($child,@RemainingTables); # Keep a tab on what we have left
}
@RemainingTables = reduceArray($ParentTable,@RemainingTables); # Keep a tab on what we have left
}
# Now @ReducedArray should be totally empty
if (@RemainingTables) {
print "These tables were not correctly analysed: @RemainingTables\n" .
"Please run:\n";
LogData("These tables failed but were not correctly analysed: @RemainingTables",$Progress);
foreach (@RemainingTables) {
print "\t\t $0 -t $_\n";
}
print "To analyse these.\n\n";
}
@Parent = sort keys(%FailedHierarchy);
if ($#Parent > 0) {
$message = "Starting multi-table session";
print "$message\n";
LogData("$message",$Progress);
&MultiParentHandler('new');
foreach $parent (sort keys(%FailedHierarchy)) {
&MultiParentHandler('add',%{$FailedHierarchy{$parent}});
}
}else{
print"Parent Table : $Parent[0]\n";
LogData("Parent Table is: $Parent[0]",$Progress);
&WriteIOFile($InputOutputFile_1,'normal','',%FailedHierarchy);
}
$Progress = "Found Parent Child Linkages";
print "$Progress\n";
if ((-s "$InputOutputFile_1" or (-s "$InputOutputFile_2" and -s $MultiParentlog))
&& (%FailedHierarchy) && $Parent[0])
{
$status = 0;
LogData("Program Finishing awaiting user consent to resynchronise tables listed in Input-Output log",$Progress);
print "\n----------------------------- $MethodName - Results -----------------------------\n\n";
print "\tResults written to : $InputOutputFile_1 \n" if (-e $InputOutputFile_1);
print "\tResults written to : $InputOutputFile_2 and \n",
"\t $MultiParentlog \n" if (-e $InputOutputFile_2);
print "\t (You should check ", (-e $InputOutputFile_1) ? 'this file' : 'these files',
" before carrying on)\n\n";
print "\t- Modifying the contents could put us in a whole load of trouble -\n";
print "\t As it contains essential parent-child linkage information.\n\n";
print "\tRun:\t $0 -r\n\t to begin resynchronisation of these tables.\n\n";
print "\t\t(This will prompt before making any changes to the database)\n\n";
}else{
$status = 1;
print " Problem with Analysis\n Exit Status : 1\tCheck and re-run\n";
LogData("Program Finishing Problem with Analysis",$Progress);
}
return($status,%FailedHierarchy);
} # end of AnalyseHierarchy
##############################################################################################
sub MergeArrays(@Array1,@Array2)
{
my(@CompleteList) = @_;
my(%seen,@uniq) = ();
foreach $table (@CompleteList)
{
push(@uniq, $table) unless $seen{$table}++;
}
@MergedArray = @uniq;
return(@MergedArray);
} # end of MergeArrays
##############################################################################################
sub stageloaderrors()
{
# This gets the errors from the latest stage_load log #
# Which are detectable by the 3 !!! marks
$linecnt = 0;
open(LOG,"<${LatestStgTlLoadLog}");
@Log = (<LOG>);
chomp(@Log);
foreach (@Log)
{
chomp;
$linecnt++;
push(@errorreturn, $Log[$linecnt], $Log[$linecnt + 1], $Log[$linecnt + 2]) if /!!!/g;
}
return(@errorreturn);
close(LOG);
} # end of stageloaderrors
##############################################################################################
sub findRelationTable($g_p_or_c,@unquotedtablelist)
{
my ($g_p_or_c) = shift;
my (@unquotedtablelist) = @_;
my (@TableList,@DBreturn) = ();
foreach (@unquotedtablelist) {
push(@TableList,"\'$_\'");
}
print "findRelationTable -> finding \'$g_p_or_c\' from: @TableList\n" if ($debug > 1);
@TableList = join(",", @TableList);
if ($g_p_or_c =~ /parent/)
{
LogData("Finding Parents from: @TableList",$Progress);
$query = "\t\tSELECT distinct TABLE_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME in (@TableList)
AND CONSTRAINT_TYPE in (\'P\',\'R\')
AND TABLE_NAME not in
(SELECT TABLE_NAME
FROM USER_CONSTRAINTS
WHERE R_CONSTRAINT_NAME in
(SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME in (@TableList))
)";
}elsif ($g_p_or_c =~ /child/){
LogData("Finding Children of: @TableList",$Progress);
$query = "\t\tSELECT distinct TABLE_NAME
FROM USER_CONSTRAINTS
WHERE R_CONSTRAINT_NAME in
(SELECT CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME in (@TableList))";
}elsif ($g_p_or_c =~ /grandma/){
## Only run this for one at a time ##
LogData("Checking for Grandparent from: @TableList",$Progress);
$query = "\t\tSELECT distinct TABLE_NAME
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME in
(SELECT R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME in (@TableList))";
}
print $query, "\n" if ($debug);
$sth = $dbh->prepare("$query");
$sth->execute or die "Failed to perform SQL statement ... \n";
while ($result = $sth->fetchrow)
{
print "findRelationTable <- \'$g_p_or_c\' found $result\n" if ($debug > 1);
push(@DBreturn,$result);
}
$sth->finish();
return(@DBreturn);
} # end of findRelationTable
##############################################################################################
sub LogData($message,$Progress)
{
my($message,$Progress) = @_;
$TIME = scalar(localtime);
&BVCustomPerl::LogMessage($message, $LogFile, $Progress, $user, $TIME);
die "Cant write to ${LogFile}\n" unless -w "${LogFile}";
} # end of LogData
##############################################################################################
sub reduceArray($remove,@allTables)
{
my($remove) = shift;
my(@allTables) = @_;
my(@ReducedArray) = ();
foreach(@allTables)
{
push(@ReducedArray,$_) unless /^$remove$/;
}
return(@ReducedArray);
} # end of reduceArray
########################################## The END ###########################################