#!/bin/perl

#	Update_DB_test
#
#	PIRL CVS ID: Update_DB_test,v 1.6 2004/09/09 19:11:28 castalia Exp

################################################################################
#
# 	Copyright (C) 2003  Arizona Board of Regents on behalf of the Planetary
# 	Image Research Laboratory, Lunar and Planetary Laboratory at the University
# 	of Arizona.
# 
# 	This library is free software; you can redistribute it and/or modify it
# 	under the terms of the GNU Lesser General Public License, version 2.1, as
# 	published by the Free Software Foundation.
# 
# 	This library is distributed in the hope that it will be useful, but WITHOUT
# 	ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
# 	FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License
# 	for more details.
# 
# 	You should have received a copy of the GNU Lesser General Public License
# 	along with this library; if not, write to the Free Software Foundation,
# 	Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307 USA.
#
################################################################################

#	This procedure performs unit tests for the Update_DB Java application.
#
#	The test is performed using a local MySQL database. Access to the
#	database must be configured in the Update_DB_test.conf file as well as
#	the for the mysql client application used to create the test table and
#	confirm the database operations.
#	
#	The test.update_db table is intially created anew. The user must have
#	privileges to create and drop this table, including the creation of the
#	containing catalog if it doesn't exist.
#	
#	A sequence of recrod insert tests is followed by a sequence of record
#	update tests. At the end of the tests the test table is dropped, but
#	not the containing catalog.

use English;

print "--- Update_DB test ----\n";

#	Update_DB configuration file.
my $Config_File	= "./Update_DB_test.conf";
if (! -r $Config_File)
	{
	print "The required $Config_File configuration file is missing!\n";
	exit (1);
	}

#	Database client for submitting SQL statements.
my $SQL_Tool	= "mysql";
#	Database catalog where the test table will be located.
my $Catalog		= "test";
#	Database table on which the test will be performed.
my $Table		= "$Catalog.update_db";

#	Java runtime environment.
my $Java_Tool	= "java";
my $classpath;
#		Provide the classpath for the database driver.
$classpath		= "/opt/java/mysql_connector"
	unless ($classpath = $ENV{"CLASSPATH"});
#		Add the classpath for the Update_DB class.
$Java_Tool .= " -classpath ../../..:$classpath";
#		Add the Update_DB class.
$Java_Tool .= " PIRL.Database.Update_DB";
#		Add the Update_DB verbose option.
$Java_Tool .= " -verbose";
#		Add the Update_DB configuration file.
$Java_Tool .= " -configure $Config_File";

#-------------------------------------------------------------------------------
#	Create the test table

my $Create_DB;
$Exit_Status;

print "--- Create the $Table table.\n";
$Create_DB = "$SQL_Tool -e ";
$Create_DB .= "'CREATE DATABASE IF NOT EXISTS $Catalog;'";
print "$Create_DB\n";
if ($Exit_Status = system ("$Create_DB") >> 8)
	{
	print "*** FAIL: You may not have the database privilege to do this.\n";
	exit (1);
	}
$Create_DB = "$SQL_Tool -e ";
$Create_DB .= "'DROP TABLE IF EXISTS $Table;'";
print "$Create_DB\n";
if ($Exit_Status = system ("$Create_DB") >> 8)
	{
	print "*** FAIL: You may not have the database privilege to do this.\n";
	exit (1);
	}
$Create_DB = "$SQL_Tool -e ";
$Create_DB .= "'CREATE TABLE $Table (";
$Create_DB .= "id INT AUTO_INCREMENT PRIMARY KEY,";
$Create_DB .= "text_1 TEXT,";
$Create_DB .= "text_2 TEXT,";
$Create_DB .= "int_1 INT,";
$Create_DB .= "int_2 INT);'";
print "$Create_DB\n";
if ($Exit_Status = system ("$Create_DB") >> 8)
	{
	print "**** FAIL: You may not have the database privilege to do this.\n";
	exit (1);
	}

#-------------------------------------------------------------------------------
#	Insert
print "\n>>> Insert\n";

my $Update_DB;
$Expected;
$Got;
$Total_Tests = 0;
$Passed_Tests = 0;

print "\n--- Insert one record, all fields (except auto-increment id).\n";
$Update_DB = "$Java_Tool";
$Update_DB .= " -Reference $Table";
$Update_DB .= " text_1=\"Value 1\"";
$Update_DB .= " text_2=\"Value 2\"";
$Update_DB .= " int_1=1000";
$Update_DB .= " int_2=2000";
print "$Update_DB\n";
system ("$Update_DB");
&Done (1)
	if ! &Check_Status (1);
$Expected = <<EOM;
id	text_1	text_2	int_1	int_2
1	Value 1	Value 2	1000	2000
EOM
$Got = qx/echo "select * from $Table" | $SQL_Tool/;
&Done (1)
	if ! &Check_Expected;

print "\n--- Insert another record, all fields.\n";
$Update_DB = "$Java_Tool";
$Update_DB .= " -Reference $Table";
$Update_DB .= " text_1=\"First\"";
$Update_DB .= " text_2=\"Second\"";
$Update_DB .= " int_1=1001";
$Update_DB .= " int_2=2002";
print "$Update_DB\n";
system ("$Update_DB");
&Check_Status (1);
$Expected = <<EOM;
id	text_1	text_2	int_1	int_2
1	Value 1	Value 2	1000	2000
2	First	Second	1001	2002
EOM
$Got = qx/echo "select * from $Table" | $SQL_Tool/;
&Check_Expected;

print "\n--- Insert two records, only one field.\n";
$Update_DB = "$Java_Tool";
$Update_DB .= " -Reference $Table";
$Update_DB .= " int_1=3000";
$Update_DB .= " -Reference $Table";
$Update_DB .= " int_1=4000";
print "$Update_DB\n";
system ("$Update_DB");
&Check_Status (2);
$Expected = <<EOM;
id	text_1	text_2	int_1	int_2
1	Value 1	Value 2	1000	2000
2	First	Second	1001	2002
3	NULL	NULL	3000	NULL
4	NULL	NULL	4000	NULL
EOM
$Got = qx/echo "select * from $Table" | $SQL_Tool/;
&Check_Expected ();

#-------------------------------------------------------------------------------
#	Update
print "\n>>> Update\n";

print "\n--- Update the first record, all fields.\n";
$Update_DB = "$Java_Tool";
$Update_DB .= " -Reference $Table:id=1";
$Update_DB .= " text_1=\"Field 1\"";
$Update_DB .= " text_2=\"Field 2\"";
$Update_DB .= " int_1=4001";
$Update_DB .= " int_2=20001";
print "$Update_DB\n";
system ("$Update_DB");
&Check_Status (1);
$Expected = <<EOM;
id	text_1	text_2	int_1	int_2
1	Field 1	Field 2	4001	20001
2	First	Second	1001	2002
3	NULL	NULL	3000	NULL
4	NULL	NULL	4000	NULL
EOM
$Got = qx/echo "select * from $Table" | $SQL_Tool/;
&Check_Expected;

print "\n--- Update the third record, only one field.\n";
$Update_DB = "$Java_Tool";
$Update_DB .= " -Reference $Table:id=3";
$Update_DB .= " int_1=30000";
print "$Update_DB\n";
system ("$Update_DB");
&Check_Status (1);
$Expected = <<EOM;
id	text_1	text_2	int_1	int_2
1	Field 1	Field 2	4001	20001
2	First	Second	1001	2002
3	NULL	NULL	30000	NULL
4	NULL	NULL	4000	NULL
EOM
$Got = qx/echo "select * from $Table" | $SQL_Tool/;
&Check_Expected;

print "\n--- Update the first and fourth record, only one field.\n";
$Update_DB = "$Java_Tool";
$Update_DB .= " -Reference '$Table:int_1 >= 4000 and id != 3'";
$Update_DB .= " int_1=40000";
print "$Update_DB\n";
system ("$Update_DB");
&Check_Status (2);
$Expected = <<EOM;
id	text_1	text_2	int_1	int_2
1	Field 1	Field 2	40000	20001
2	First	Second	1001	2002
3	NULL	NULL	30000	NULL
4	NULL	NULL	40000	NULL
EOM
$Got = qx/echo "select * from $Table" | $SQL_Tool/;
&Check_Expected;

print "\n--- Update multiple records, but only one allowed.\n";
$Update_DB = "$Java_Tool";
$Update_DB .= " -1";
$Update_DB .= " -Reference $Table:int_1=40000";
$Update_DB .= " int_1=0";
print "$Update_DB\n";
system ("$Update_DB");
&Check_Status (-4);
$Expected = <<EOM;
id	text_1	text_2	int_1	int_2
1	Field 1	Field 2	40000	20001
2	First	Second	1001	2002
3	NULL	NULL	30000	NULL
4	NULL	NULL	40000	NULL
EOM
$Got = qx/echo "select * from $Table" | $SQL_Tool/;
&Check_Expected;

print "\n--- Update four records, three separate sets.\n";
$Update_DB = "$Java_Tool";
$Update_DB .= " -Reference $Table:id=1";
$Update_DB .= " int_1=10000";
$Update_DB .= " -Reference $Table:int_1=1001";
$Update_DB .= " int_1=20000";
$Update_DB .= " int_2=20002";
$Update_DB .= " -Reference '$Table:int_1 >= 30000'";
$Update_DB .= " int_2=int_1+id";
print "$Update_DB\n";
system ("$Update_DB");
&Check_Status (4);
$Expected = <<EOM;
id	text_1	text_2	int_1	int_2
1	Field 1	Field 2	10000	20001
2	First	Second	20000	20002
3	NULL	NULL	30000	30003
4	NULL	NULL	40000	40004
EOM
$Got = qx/echo "select * from $Table" | $SQL_Tool/;
&Check_Expected;

&Done (0);

#-------------------------------------------------------------------------------

sub Check_Status {

my ($expected) = @_;
$expected &= 0xFF;
my $status = $? >> 8;
my $match = ($status == $expected);
print "*** ", ($match ? "PASS" : "FAIL"),
	": exit status $status, expected $expected.\n";
$Total_Tests++;
$Passed_Tests++
	if $match;
return $match;
}


sub Check_Expected {

my $match = $Got eq $Expected;
print ($match ? "*** PASS\n" : "*** FAIL\n");
print "    expected:\n$Expected";
print "    got:\n$Got";
$Total_Tests++;
$Passed_Tests++
	if $match;
return $match;
}


sub Done {

my ($status) = @_;

print "\n",
	$Passed_Tests, " test", ($Passed_Tests == 1 ? "" : "s"), " passed.\n",
	$Total_Tests, " test", ($Total_Tests == 1 ? "" : "s"), " tried.\n";

#	Remove the test table
$Create_DB = "$SQL_Tool -e ";
$Create_DB .= "'DROP TABLE IF EXISTS $Table;'";
print "\n$Create_DB\n";
if ($Exit_Status = system ("$Create_DB") >> 8)
	{
	print "*** FAIL: You may not have the database privilege to do this.";
	exit (1);
	}
exit ($status);
}
