# Querier

package Querier;

use strict;
#use warnings;

use Carp;
use POSIX;
use DBI;

use base 'Exporter';
our $VERSION = sprintf "%d.%03d", q$Revision: 1.10 $ =~ /(\d+)/g;

our @EXPORT = ();
our %EXPORT_TAGS = ();
our @EXPORT_OK = qw($col_delim);

our $col_delim = pack("c",29);	# nonalphanumeric column data separator

my %dbhandles = ();	# dbh's keyed by dbname

my $dbh;		# database handle
my $username = undef;
my $password = undef;
my $drh;		# driver handle
my $verbose = 0;

my %PreparedQuery = ();
my %PreparedStmt  = ();
my $PrepareCounter = 0;

my %TableColumns = (); 	# cache of table descriptions

my $dbVendor = $ENV{DBVENDOR};	# e.g. Postgres, Informix
my $defaultdbalias = 'tracker';

$DBI::dbi_debug = 3;

#===============================================#
# for communicating with multiple databases		#
# using standard aliases for them					#
#===============================================#
my %dbnames = ();

if (`hostname` =~ /pair/)
{
	%dbnames = (
		"tracker"	=> "jhook_tracker",
		"SF511"		=> "jhook_SF511",
		"config"		=> "config",
		"permanent"	=> "permanent",
	);
}

#$SIG{__DIE__} = \&error_handler;
$SIG{'INT'} = \&error_handler;
$SIG{'QUIT'} = \&error_handler;

#==================================================

sub verbose
{
	if (@_)
	{
		$verbose = shift;
	}
	return $verbose;
}

#==================================================

sub defaultdbalias
{
	$defaultdbalias = shift if @_;
	$defaultdbalias;
}

#==================================================

sub dbhandleFor
{
	my $dbname = shift;
	return $dbhandles{$dbname} if exists($dbhandles{$dbname});
	my $server_dbname = exists($dbnames{$dbname}) ? $dbnames{$dbname} :
		die "No server database name defined in Querier for '$dbname'\n";
	
	my $dbh;
	$dbVendor = $ENV{DBVENDOR};	# e.g. Postgres, Informix
	if ($dbVendor =~ /MySQL/i)
	{
		my $driver = "mysql";
		my $dsn;
		$dbname eq 'tracker' && do
		{
			$dsn = "DBI:$driver:database=$server_dbname;host=db103b.pair.com";
			$dbh = DBI->connect($dsn, 'jhook_2', '9w4CCEbs');
		};
		$dbname eq 'SF511' && do
		{
			$dsn = "DBI:$driver:database=$server_dbname;host=db103a.pair.com";
			$dbh = DBI->connect($dsn, 'jhook', 'fHavS3cY');
		};
	}
	elsif ($dbVendor =~ /Postgres/i)
	{
		$dbh = DBI->connect("dbi:Pg:dbname=$dbname", "", "");
	}
	elsif ($dbVendor =~ /Informix/i)
	{ 
		$dbh = DBI->connect("dbi:Informix:$dbname", undef, undef);
	}
	
	if (!$dbh) 
	{
		report("No connection privilege to $dbname\n",1);
	}
	else
	{
		$dbh->{RaiseError} = 1;
		$dbhandles{$dbname} = $dbh;
	}

	$dbh;
}


#==================================================

sub error_handler
{
	#-----------------------------------------#
	# don't do anything from within	eval		#
	# blocks - we only want to handle fatal	#
	# errors outside of these						#
	#-----------------------------------------#
	die @_ if defined $^S;	# $^S is interpreter state; true only inside evals
	
	my ($sig) = @_;
	report(&getlocaltime(),1);
	Carp::confess("Caught a SIG: $sig!\n");
}

#==================================================

sub getlocaltime
{
	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime;
	return sprintf "[%04d-%02d-%02d %02d:%02d:%02d] ",
		$year+1900, $mon+1, $mday, $hour, $min, $sec;
}

#===============================================================

sub execute_sql
{
	my ($querytype,$query,$dbalias) = @_;
	
	my $dbname = $dbalias && length($dbalias) ? $dbalias : $defaultdbalias;	

 	# local exception catcher so we can put query into $@ 
	# before passing the exception up to the caller
	my $resStr = eval
	{	
		$dbh = &dbhandleFor($dbname);
		
		#============================================#
		# 		multiple row select 							#
		#============================================#
		if ($querytype =~ /^mr/i)
		{
			my $start = (POSIX::times())[0];
			my $results = &exec_select_all(\$query);
			my $end = (POSIX::times())[0];
			report(sprintf "Query Execution Time:%.2f CPU seconds\n\n", ($end - $start)/100);
			return $results;
		}
		#============================================#
		# 			execute parametric						#
		#============================================#
		elsif ($querytype =~ /^xp$/i)
		{
			my ($stmthandle,@params) = split($col_delim,$query);
			my $start = (POSIX::times())[0];
			my $results = &exec_parametric($stmthandle,@params);
			my $end = (POSIX::times())[0];
			report(sprintf "Query Execution Time:%.2f CPU seconds\n\n", ($end - $start)/100);
			return $results;
		}
		#============================================#
		# 			prepare parametric						#
		#============================================#
		elsif ($querytype =~ /^pr$/i)
		{
			my $start = (POSIX::times())[0];
			my $results = &prepare_parametric($query);
			my $end = (POSIX::times())[0];
			report(sprintf "Query Execution Time:%.2f CPU seconds\n\n", ($end - $start)/100);
			return $results;
		}
		#===============================================#
		# 		multiple row parametric select				#
		# This is to test if parametric execution works	#
		#===============================================#
		elsif ($querytype =~ /^mpr$/i)
		{
			my ($pquery,@params) = split($col_delim,$query);
			my $start = (POSIX::times())[0];
			my $results = &exec_select_parametric($pquery,@params);
			my $end = (POSIX::times())[0];
			report(sprintf "Query Execution Time:%.2f CPU seconds\n\n", ($end - $start)/100);
			return $results;
		}
		#============================================#
		# 		immediate execution 							#
		#============================================#
		elsif ($querytype =~ /^im$/i)
		{
			my $start = (POSIX::times())[0];
			my $results = &exec_do($query);
			my $end = (POSIX::times())[0];
			report(sprintf "Query Execution Time:%.2f CPU seconds\n\n", ($end - $start)/100);
			return $results;
		}
		#===========================================#
		# 		blobload (TEXT column update)			  #
		# the arguments to blobload are just those  #
		# specified by informix' blobload. The blob #
		# text is in a filename following -f 		  #
		#===========================================#
		elsif ($querytype =~ /^blobload$/i)
		{
			my $_cmd = "blobload " . $query;
			my $rc = 0xffff & system $_cmd;

			my $results = sprintf "system(%s) returned %#04x: ", $_cmd, $rc;
			if ($rc == 0) {
				$results .= "ran with normal exit";
			}
			elsif ($rc == 0xff00) {
				$results .= "command failed: $!";
			}
			elsif ($rc > 0x80) {
				$rc >>= 8;
				$results .= "ran with non-zero exit status $rc";
			}
			else {
				$results .= "ran with ";
				if ($rc &   0x80) {
					$rc &= ~0x80;
					$results .= "coredump from  ";
				}
				$results .= "signal $rc";
			}
			return $results;
		}
		#===========================================#
		# 		database metadata commands				  #
		#===========================================#
		elsif ($querytype =~ /^tableAttributes$/i)
		{
			my $table = $query;
			my $aref = $dbh->func($table,'table_attributes');
			my $help = <<ENDHELP;
		This method returns for the given table a reference to
		an array of hashes:
		NAME        attribute name
		TYPE        attribute type
		SIZE        attribute size (-1 for variable size)
		NULLABLE    flag nullable
		DEFAULT     default value
		CONSTRAINT  constraint
		PRIMARY_KEY flag is_primary_key
ENDHELP
			return $aref;
		}
		#===========================================#
		# 		database connection commands			  #
		#===========================================#
		elsif ($querytype =~ /^getdatabases$/i)	# RETURN LIST OF AVAIL DATABASES
		{
			return join($;,sort keys (%dbhandles));
		}
		else # UNKNOWN COMMAND
		{
			die ("Querier: Unknown command received: |$querytype|\n");
		}
	};
	if ($@)
	{
		$@ .= "\n$dbname:$querytype:\n$query\n";
		die $@;
	}
	$resStr;
}

#=====================================================================

sub columnsFor
{
	my ($table,$dbalias) = @_;
	my $dbname = $dbalias ? $dbalias : $defaultdbalias; # !!FIXME for informix

	if (exists($TableColumns{$dbname}->{$table}))
	{
		return $TableColumns{$dbname}->{$table};
	}

	$dbh = &dbhandleFor($dbname);
	my $cols;
	
	if ($dbh)
	{
		if ($dbVendor =~ /MySQL/i)
		{
			$cols=columnsFor_mysql($table); 
		}
		  elsif ($dbVendor =~ /Postgres/i)
		{
			$cols=columnsFor_pg($table); 
		}
		elsif ($dbVendor =~ /Informix/i)
		{ 
#			$cols=getTableSchema(@_);
		}
		$TableColumns{$dbname}->{$table} = $cols;
	}
}

#=====================================================================

sub columnsFor_mysql
{
	my $table = shift;
	my $query = qq/
		describe $table/;
	my $qres = exec_select_all(\$query);
	my @ares = split($;,$qres);
	my @cols;
	foreach my $row (@ares)
	{
		my @fields = split($col_delim,$row);
		my $col = shift(@fields);
		push(@cols,$col);
	}
	return \@cols;
}

#=====================================================================

sub columnsFor_pg
{
	my $table = shift;
	my $query = qq/
		select 	attname 
		from 	pg_attribute a,	pg_type t
		where	a.attrelid = t.typrelid
			and t.typname = '$table'
			and attnum > 0
		order by attnum/;
	my $qres = exec_select_all(\$query);
	my @cols = split($;,$qres);
	return \@cols;
}

#=====================================================================

sub connectInformix
{
	my ($_handle,$dbname);
	
	report("Attempting to connect to Informix...\n",1);
	my(@dbnames) = DBI->data_sources('Informix');
	
	foreach $dbname (@dbnames) 
	{
		$dbname =~ s/dbi:Informix://;
		
		$_handle = DBI->connect("dbi:Informix:$dbname", $username, $password);
		
		if (!$_handle) 
		{
			report("No connection privilege to $dbname\n",1);
			next;
		}
		
		$_handle->{RaiseError} = 1;
	
		report( "Database Information\n",1);
		# Type is always 'db'
		report( "    Type:                    $_handle->{Type}\n",1);
		# Name is the name of the database specified at connect
		report( "    Database Name:           $_handle->{Name}\n",1);
		# AutoCommit is 1 (true) if the database commits each statement.
		report( "    AutoCommit:              $_handle->{AutoCommit}\n",1);
	
		# ix_InformixOnLine is 1 (true) if the handle is connected to an
		# Informix-OnLine server.
		report( "    Informix-OnLine:         $_handle->{ix_InformixOnLine}\n",1);
		# ix_LoggedDatabase is 1 (true) if the database has
		# transactions.
		report( "    Logged Database:         $_handle->{ix_LoggedDatabase}\n",1);
		# ix_ModeAnsiDatabase is 1 (true) if the database is MODE ANSI.
		report( "    Mode ANSI Database:      $_handle->{ix_ModeAnsiDatabase}\n",1);
		# ix_AutoErrorReport is 1 (true) if errors are reported as they
		# are detected.  This is now deprecated -- use $dbh->{PrintError}.
		report( "    AutoErrorReport:         $_handle->{PrintError}\n",1);
		# ix_InTransaction is 1 (true) if the database is in a transaction
		report( "    Transaction Active:      $_handle->{ix_InTransaction}\n",1);
		# ix_ConnectionName is the name of the ESQL/C connection.
		# Mainly applicable with Informix-ESQL/C 6.00 and later.
		report( "    Connection Name:         $_handle->{ix_ConnectionName}\n",1);
		
		$dbhandles{$dbname} = $_handle;
	}
}

#=====================================================================

sub exec_do
{
	my $query = shift;
	my @results = ();
	
	if ($dbVendor =~ /Postgres|mysql/i)
	{
		$query =~ s/current/CURRENT_TIMESTAMP/g;
	
		# Insert INTO TEMP queries require the INTO TEMP to come
		# between the columns and the from, whereas
		# for INFORMIX, it comes as the last line
		if ($query =~ /into\s*temp/i)
		{
			my ($sel,$from,$rest) = split(/(\bfrom\b)/i,$query,2);
			my ($preinto,$into) = split(/(\binto\s+temp.*$)/i,$rest);
			$query = $sel . ' ' . $into . ' ' . $from . $preinto;
		}		
	}
#	my $filter = '';
#	$filter .= 'T' if $query =~ /\wtemp\w/im;
#	$filter .= 'U' if $query =~ /\wupdate\w/i;
#	$filter .= 'D' if $query =~ /\w(delete)|(drop)\w/i;
#	$filter .= 'I' if $query =~ /\winsert\w/i;
	
	# don't die on database errors by using an eval block
	eval {
		$dbh->do($query);
	};
	#-----------------------------------#
	# Do not die on Drop Table errors:	#
	# They are usually caused by the		#
	# table not existing, because of a 	#
	# previous drop. Die on all others.	#
	#-----------------------------------#
	if ($@ && $query !~ /drop/i) 
	{
		die($@ . "\n\nquery=$query");
	}
	return 0;		# all went OK
}

#=====================================================================

sub exec_select_all
{
	#-----------------------------------------------------------#
	# Errors here will cause a die (exception).  Handling them	#
	# is the responsibility of the calling application.			#
	#-----------------------------------------------------------#
	my $queryptr = shift;
	my ($sth, $rv, $rva);
	my (@results) = ();
	
	report("$$queryptr\n");
	$sth = $dbh->prepare($$queryptr);
	$sth->execute;	
	while ($rva = $sth->fetch)
	{
		no warnings 'uninitialized';
		grep { s/\s+$// } @$rva;		# remove trailing blanks
		$rv = join($col_delim,@$rva);		
		push(@results,$rv);
	}
	$sth->finish;

	report(scalar(@results) . " rows returned\n");
	return join("$;",@results);
}

#=====================================================================

sub exec_select_parametric
{
	#-----------------------------------------------------------#
	# Errors here will cause a die (exception).  Handling them	#
	# is the responsibility of the calling application.			#
	#-----------------------------------------------------------#
	my ($query,@params) = @_;
	my ($sth, $rv, $rva);
	my (@results) = ();
	
	report("$query(" . join(",",@params) . ")\n");
	$sth = $dbh->prepare($query);
	$sth->execute(@params);	
	while ($rva = $sth->fetch)
	{
		grep { s/\s+$// } @$rva;		# remove trailing blanks
		$rv = join($col_delim,@$rva);		
		push(@results,$rv);
	}
	$sth->finish;

	report(scalar(@results) . " rows returned\n");
	return join("$;",@results);
}

#=====================================================================

sub prepare_parametric
{
	#-----------------------------------------------------------#
	# Errors here will cause a die (exception).  Handling them	#
	# is the responsibility of the calling application.			#
	#-----------------------------------------------------------#
	my $query = shift;
	my $sth;
	my $stmthandle;
	
	# first see if we've already prepared this query
	if (exists($PreparedQuery{$query}))
	{
		$stmthandle = $PreparedQuery{$query};
		report("Cached prepared statement handle = $stmthandle\n");
		return $stmthandle;
	}
	
	$sth = $dbh->prepare($query);
	$stmthandle = 'PR' . ++$PrepareCounter;
	$PreparedQuery{$query} = $stmthandle;
	$PreparedStmt{$stmthandle} = $sth;
	report("Newly prepared statement handle = $stmthandle\n");
	return $stmthandle;
}

#=====================================================================

sub exec_parametric
{
	#-----------------------------------------------------------#
	# Errors here will cause a die (exception).  Handling them	#
	# is the responsibility of the calling application.			#
	#-----------------------------------------------------------#
	my ($stmthandle,@params) = @_;
	my ($sth, $rv, $rva);
	my (@results) = ();
	
	if ($dbVendor =~ /Postgres|mysql/i)
	{
		map { $_ =~ s/current/CURRENT_TIMESTAMP/g} @params;
	}
	
	if (!exists($PreparedStmt{$stmthandle}))
	{
		my $errstr = "No prepared statement for handle $stmthandle\n";
		report($errstr);
		return $errstr;
	}
	$sth = $PreparedStmt{$stmthandle};

	report("$stmthandle(" . join(",",@params) . ")\n");
	$sth->execute(@params);
	if ($sth->{ix_Fetchable} > 0)	# DBD::Informix specific!!!
	{
		my $msg = join(",",$stmthandle,@params);
		while ($rva = $sth->fetch)
		{
			grep { s/\s+$// } @$rva;		# remove trailing blanks
			$rv = join($col_delim,@$rva);		
			push(@results,$rv);
		}
	}
	report(scalar(@results) . " rows returned\n");
	return join("$;",@results);
}

#====================================================================

sub report
{
	my ($msg,$error) = @_;
	
	if ($error)
	{
		print STDERR $msg;
	}
	
	elsif ($verbose)
	{
		print STDERR $msg;
	}
}

1;