#!/usr/local/bin/perl
#
# Joe Bryant
# HiUCImOn@Yahoo.com
#
use BigBrother;
use Socket;
$keyword='oracle';				# The function we are looking for
$items=BigBrother->Items($keyword);		# Get the list of items we need to look at
foreach (split(/\s+/,$items)) {			# For oracle, we need the instances split off the host
	push @groups,$_;			# We also want to keep track of all of it
	($name,$service)=/^(.*?)\.(.*)/;	# Grab host, every thing before first '.'
	push @services,$service;		# Stack up the instance names
	$host{$service}=$name;			# And record what host each service is on
}
$services=join(' ',@services);			# For performance, I want to only call OraclePassword once for them all
foreach (`OraclePassword $services`) {		# OraclePassword is a script that returns instance passwords in a psudo secure fassion
	($sid,$user,$pass)=split;		# Each line wil have the sid, user, and password
	$user{$sid}=$user;			# Set our values...
	$pass{$sid}=$pass;			# ...from the data extracted
	$user{$sid}=BigBrother->Parms("$sid~$keyword~user",$user);	# But, override if the gave a user= 
	$pass{$sid}=BigBrother->Parms("$sid~$keyword~pass",$pass);	# or pass= keyword in the parms
}
use DBI;					# Load Database support
$SQL='select * from dual';			# Our simple test to be sure the DB is working
foreach $inst (@services) {				# Now process each instance
	$host=$host{$inst};			# Simplify typeing, not really needed
	$user=$user{$inst};
	$pass=$pass{$inst};
	$down=BigBrother->IsDown("$host.$inst")||BigBrother->IsDown($host);	# Check to see if this instance(or host) is scheduled down in $BBHOME/ext/down.cfg
	if ($pid=fork) {				# We do them all at once, you may want to throttle it back
		$pids{$pid}=1;				# Track pids, for clean up
	} else {					# Then do child process
		if(my $dbConn = DBI->connect("dbi:Oracle:$inst", $user, $pass)) {	# Can we connect?
			$result="$inst\nConnect successful";				# Yes, start result string
			my $dbCommand = $dbConn->prepare($SQL);				# Prepare our query
			if($dbCommand->execute()) {					# Does it run?
				$color='GREEN';						# Yes, show it as up
				$result.="\nExecute successful\n".$SQL."\n".BuildResultAsTabulatedString($dbCommand);	# and set results
			} else {
				if ($down) {						# No, is it scheduled down?
					$color='BLUE';					# Yes, show as BLUE
					$result="\nScheduled down\n";			# with appropriate message
				} else {
					$color='YELLOW';				# No, show it YELLOW
					$result.="\nExecute failed\n".$SQL."\n due to error: $DBI::errstr";
				}
			}
		} else {
			if ($down) {							# No, is it scheduled down?
				$color='BLUE';						# Yes, show BLUE
				$result="\nScheduled down\n";
			} else {
				$color='RED';						# No, show RED
				$result="Connect failed: $DBI::errstr";
			}
		}
		BigBrother->Report($host,$inst,$color,$result);				# Tell Big Brother the status
		exit;
	}
}
while (keys %pids) {				# while we still have children running
	$pid=wait;				# Wait for the next one
	delete $pids{$pid};			# and remove it from our list
}
exit;


sub BuildResultAsTabulatedString {
	my($Result)= @_ ;
	
	my @Row;
	my $output = '';
	
	# Count rows returned
	my $i=0;
	# For each row
	while (@Row = $Result->fetchrow_array()) {
		# For each cell
		$output.=join("\t",@Row)."\n";
		#	for my $Cell (@Row) {
		#		# append the cell to the output
		#		$output .= "$Cell\t";
		#	}	 
		# Write the end of row
		# $output .= "\n";
		$i++;
	}
	
	# Print the number of rows
	$output .= "\t$i rows.\n";
	
	# Return the string with the tabulated data
	return $output ;
}