#!/bin/sh

DB=postgis_reg

# TODO: get this part generated by ./configure. For now
# we must make sure this matches REGRESS_INSTALLDIR in
# Makefile.in.
SHP2PGSQL=../loader/shp2pgsql
PGSQL2SHP=../loader/pgsql2shp

# raster2pgsql is relative to the place where this script
# would be called: raster/test/regress
RASTER2PGSQL=../../loader/raster2pgsql

###################################################
#
# Usage ./run_test <testname> [<testname>]
#
# Create the spatial database 'postgis_reg'
# (or whatever $DB is set to) if it doesn't
# already exist.
#
# Run the <testname>.sql script
# Diff output against <testname>_expected
#
#
###################################################

# Set the locale to "C" so error messages match
# Save original locale to set back
ORIG_LC_ALL=$LC_ALL
ORIG_LANG=$LANG
export LC_ALL=C
export LANG=C

PGOPTIONS="${PGOPTIONS} -c lc_messages=C"
export PGOPTIONS

REGDIR=`dirname $0`
REGDIR=`cd "${REGDIR}" && pwd`
STAGED_INSTALL_DIR="${REGDIR}/00-regress-install"
STAGED_SCRIPTS_DIR="${STAGED_INSTALL_DIR}/share/contrib/postgis"

PSQL="psql"

if [ -z "$TMPDIR" ]; then
	TMPDIR=/tmp/pgis_reg
fi

mkdir -p ${TMPDIR}/tmp
chmod 777 ${TMPDIR}/tmp

VERBOSE=0
OPT_CLEAN=no
OPT_DROP=yes
OPT_CREATE=yes
OPT_UPGRADE=no
OPT_WITH_TOPO=no
OPT_WITH_RASTER=no
OPT_EXPECT=no
OPT_EXTENSIONS=no

if echo '\c' | grep c >/dev/null 2>&1; then
	ECHO_N='echo -n'
	ECHO_C=''
else
	ECHO_N='echo'
	ECHO_C='\c'
fi

###################################################
# 
# Helper functions
#
###################################################

#
# start_test <name>
#
start_test ()
{
    TEST="$1"
    ${ECHO_N} " ${TEST} ${ECHO_C}"
    RUN=`expr $RUN + 1`
    show_progress
}

# Print a single dot
echo_inline()
{
	msg="$1"
	${ECHO_N} "${msg}${ECHO_C}"
}

# Print a single dot
show_progress()
{
	echo_inline "."
}

#
# pass <msg> 
#
pass ()
{
  msg="${1}"
  echo " ok ${msg}"
}

#
# fail <msg> <log>
#
fail ()
{
	_msg="$1"
	_log="$2"

	if [ -z "$_log" ]; then
		echo " failed ($_msg)"
	elif test "$VERBOSE" -eq "1"; then
		echo " failed ($_msg: $_log)"
		echo "-----------------------------------------------------------------------------"
		cat $_log
		echo "-----------------------------------------------------------------------------"
	else
		echo " failed ($_msg: $_log)"
	fi

	FAIL=`expr $FAIL + 1`
}

#
#  run_simple_sql 
#
#  Run an sql script and hide results unless it fails.
#
#  SQL input file name is $1
#
run_simple_sql ()
{
	_sql="$1"

	if [ ! -r "$_sql" ]; then
		fail "can't read $_sql"
		return 1
	fi

	TMPFILE="${TMPDIR}/test_${RUN}_tmp"

	# Dump output to a temp file.
	${PSQL} -v "VERBOSITY=terse" -tXA < "${_sql}" ${DB} > ${TMPFILE} 2>&1
	# Check if psql errored out.
	if [ $? -gt 0 ]; then
		fail "Unable to run sql script $_sql" "${TMPFILE}"
		return 1
	fi
	# Check if psql produced any error output.
	grep "^ERROR:" "${TMPFILE}"
	if [ $? -eq 0 ]; then
		fail "Errors while running sql script $_sql" "${TMPFILE}"
		return 1
	fi
	rm ${TMPFILE}
}

#
#  run_simple_test 
#
#  Run an sql script and compare results with the given expected output
#
#  SQL input is ${TEST}.sql, expected output is {$TEST}_expected
#
run_simple_test ()
{
	_sql="$1"
	_expected="$2"
	if [ -n "$3" ]; then
		_msg="$3: "
	else
		_msg=
	fi

	if [ ! -r "$_sql" ]; then
		fail "can't read $_sql"
		return 1
	fi

	if test x"$OPT_EXPECT" = "xno"; then
		if [ ! -r "$_expected" ]; then
			fail "can't read $_expected"
			return 1
		fi
	fi

	show_progress

	OUTFILE="${TMPDIR}/test_${RUN}_out"
	BETMPDIR="${TMPDIR}/pgis_reg_tmp/"
	TMPFILE="${BETMPDIR}/test_${RUN}_tmp"
	DIFFILE="${TMPDIR}/test_${RUN}_diff"

	mkdir -p "${BETMPDIR}"
	chmod 777 "${BETMPDIR}"

	# Use only one call to sed to work around MSYS buffering problem
	# and use fewer calls to grep for the same problem. MSYS only accepts
	# about 16 redirects before hanging.
	${PSQL} -v "VERBOSITY=terse" -v "tmpfile='${TMPFILE}'" -tXA ${DB} < "${_sql}" 2>&1 \
		| grep --binary-files=text -v "^$" \
		| grep --binary-files=text -v "^\(INSERT\|DELETE\|UPDATE\|SELECT\)" \
		| grep --binary-files=text -v "^\(CONTEXT\|RESET\|ANALYZE\)" \
		| grep --binary-files=text -v "^\(DROP\|CREATE\|VACUUM\)" \
		| grep --binary-files=text -v "^\(SET\|TRUNCATE\)" \
		| grep --binary-files=text -v "^LINE [0-9]" \
		| grep --binary-files=text -v "^  *^$" \
		| sed -e 's/Infinity/inf/g' -e 's/Inf/inf/g' -e 's/1\.#INF/inf/g' \
		      -e 's/[eE]\([+-]\)0\{1,\}\([0-9]\{1,\}\)/e\1\2/g' \
		      -e 's/Self-intersection .*/Self-intersection/' \
		      -e 's/^ROLLBACK/COMMIT/' \
		> "${OUTFILE}"

	rm -rf "${BETMPDIR}" # should we keep these ?

	if test x"$OPT_EXPECT" = "xyes"; then
		echo_inline " expected"
		cp "${OUTFILE}" "${_expected}"
	else
		if diff -u "${_expected}" "${OUTFILE}" > ${DIFFILE}; then
			#SUCCESS=`expr $SUCCESS + 1`
			rm "${OUTFILE}" "${DIFFILE}" # we don't need these anymore
			return 0
		else
			fail "${_msg}diff expected obtained" "${DIFFILE}"
			# rm "${OUTFILE}" # diff is enough
			return 1
		fi
	fi
}

# Drop a table if exists
drop_table_if_exists()
{
	tblname="$1"
	${PSQL} -c "DROP TABLE IF EXISTS ${tblname}" "${DB}" >> ${TMPDIR}/regress_log 2>&1
}

#
# This runs the loader once and checks the output of it.
# It will NOT run if neither the expected SQL nor the expected
# select results file exists, unless you pass true for the final
# parameter.
#
# $1 - Description of this run of the loader, used for error messages.
# $2 - Table name to load into.
# $3 - The name of the file containing what the
#      SQL generated by shp2pgsql should look like.
# $4 - The name of the file containing the expected results of
#      SELECT geom FROM _tblname should look like.
# $5 - Command line options for shp2pgsql.
# $6 - If you pass true, this will run the loader even if neither
#      of the expected results files exists (though of course
#      the results won't be compared with anything).
#
run_loader_and_check_output()
{
	_description=$1
	_tblname=$2
	_expected_sql_file=$3
	_expected_select_results_file=$4
	_loader_options=$5
	_run_always=$6
	# ON_ERROR_STOP is used by psql to return non-0 on an error
	_psql_opts="--no-psqlrc --variable ON_ERROR_STOP=true"

	if [ -n "$_run_always" -o -r "$_expected_sql_file" -o -r "$_expected_select_results_file" ]; then
		show_progress
		# Produce the output SQL file.
		${SHP2PGSQL} $_loader_options -g the_geom ${TEST}.shp $_tblname \
			> ${TMPDIR}/loader.out \
			2> ${TMPDIR}/loader.err

		if [ $? -gt 0 ]; then
			fail " $_description: running shp2pgsql" "${TMPDIR}/loader.err"
			return 1
		fi

		# Compare the output SQL file with the expected if there is one.
		if [ -r $_expected_sql_file ]; then
			show_progress
			if diff "${TMPDIR}/loader.out" "$_expected_sql_file" > /dev/null; then
				:
			else
				fail " $_description: actual SQL does not match expected.", \
					"${TMPDIR}/loader.out"
			fi
		fi

		# Run the loader SQL script.
		show_progress
		${PSQL} ${_psql_opts} -f ${TMPDIR}/loader.out "${DB}" > ${TMPDIR}/loader.err 2>&1
		if [ $? -gt 0 ]; then
			fail " $_description: running shp2pgsql output" "${TMPDIR}/loader.err"
			return 1
		fi

		# Run the select script (if there is one)
		if [ -r "${TEST}.select.sql" ]; then
			if run_simple_test "${TEST}.select.sql" "$_expected_select_results_file" "$_description"; then
				:
			else
				# That will have already called fail, so just return an error.
				return 1
			fi
		fi
	fi
	return 0
}

#
# This runs the dumper once and checks the output of it.
# It will NOT run if the expected shp file does not exist, unless
# you pass true for the final parameter.
#
# $1 - Description of this run of the dumper, used for error messages.
# $2 - Table name to dump from.
# $3 - "Expected" .shp file to compare with.
# $3 - If you pass true, this will run the loader even if neither
#      of the expected results files exists (though of course
#      the results won't be compared with anything).
#
run_dumper_and_check_output()
{
	_description=$1
	_tblname=$2
	_expected_shp_file=$3
	_run_always=$4

	if [ -n "$_run_always" -o -r "$_expected_shp_file" ]; then
		show_progress
		${PGSQL2SHP} -f ${TMPDIR}/dumper ${DB} "${_tblname}" > "${TMPDIR}/dumper.err" 2>&1
		if [ $? -gt 0 ]; then
			fail "$_description: dumping loaded table" "${TMPDIR}/dumper.err"
			return 1
		fi

		# Compare with expected output if there is any.
		if [ -r $_expected_shp_file ]; then
			show_progress
			if diff "${TMPDIR}"/dumper.shp "$_expected_shp_file" > /dev/null; then
				:
			else
				ls -lL "${TMPDIR}"/dumper.shp "$_expected_shp_file" > "${TMPDIR}"/dumper.diff
				fail "$_description: dumping loaded table" "${TMPDIR}/dumper.diff"
				return 1
			fi
		fi
	fi
	return 0
}

#
#  run_loader_test 
#
#  Load a shapefile with different methods, create a 'select *' SQL
#  test and run simple test with provided expected output. 
#
#  SHP input is ${TEST}.shp, expected output is {$TEST}.expected
#
run_loader_test ()
{
	# See if there is a custom command-line options file
	_custom_opts=""
	if [ -r ${TEST}.opts ]; then
		_custom_opts=`grep -v ^\s*# ${TEST}.opts`
	fi

	tblname="loadedshp"

	# If we have some expected files to compare with, run in wkt mode.
	if run_loader_and_check_output "wkt test" "${tblname}" "${TEST}-w.sql.expected" "${TEST}-w.select.expected" \
		"-w $_custom_opts"; then
		:
	else
		return 1
	fi
	drop_table_if_exists "${tblname}" >> ${TMPDIR}/regress_log 2>&1

	# If we have some expected files to compare with, run in geography mode.
	if run_loader_and_check_output "geog test" "${tblname}" "${TEST}-G.sql.expected" "${TEST}-G.select.expected" \
		"-G $_custom_opts"; then
		:
	else
		return 1
	fi
	# If we have some expected files to compare with, run the dumper and compare shape files.
	if run_dumper_and_check_output "dumper geog test" "${tblname}" "${TEST}-G.shp.expected"; then
		:
	else
		return 1
	fi
	drop_table_if_exists "${tblname}" >> ${TMPDIR}/regress_log 2>&1

	# Always run in wkb ("normal") mode, even if there are no expected files to compare with.
	if run_loader_and_check_output "wkb test" "${tblname}" "${TEST}.sql.expected" "${TEST}.select.expected" \
		"$_custom_opts" "true"; then
		:
	else
		return 1
	fi
	# If we have some expected files to compare with, run the dumper and compare shape files.
	if run_dumper_and_check_output "dumper wkb test" "${tblname}" "${TEST}.shp.expected"; then
		:
	else
		return 1
	fi
	drop_table_if_exists "${tblname}" >> ${TMPDIR}/regress_log 2>&1

	# Some custom parameters can be incompatible with -D.
	if [ -z "$_custom_opts" ]; then
		# If we have some expected files to compare with, run in wkt dump mode.
		if run_loader_and_check_output "wkt dump test" "${tblname}" "${TEST}-wD.sql.expected" \
			"${TEST}-w.select.expected" "-w -D"; then
			:
		else
			return 1
		fi
		drop_table_if_exists "${tblname}" >> ${TMPDIR}/regress_log 2>&1

		# If we have some expected files to compare with, run in wkt dump mode.
		if run_loader_and_check_output "geog dump test" "${tblname}" "${TEST}-GD.sql.expected" \
			"${TEST}-G.select.expected" "-G -D"; then
			:
		else
			return 1
		fi
		drop_table_if_exists "${tblname}" >> ${TMPDIR}/regress_log 2>&1

		# If we have some expected files to compare with, run in wkb dump mode.
		if run_loader_and_check_output "wkb dump test" "${tblname}" "${TEST}-D.sql.expected" \
			"${TEST}.select.expected" "-D"; then
			:
		else
			return 1
		fi
		drop_table_if_exists "${tblname}" >> ${TMPDIR}/regress_log 2>&1
	fi

	return 0
}

#
# This runs the loader once and checks the output of it.
# It will NOT run if neither the expected SQL nor the expected
# select results file exists, unless you pass true for the final
# parameter.
#
# $1 - Description of this run of the loader, used for error messages.
# $2 - Table name to load into.
# $3 - The name of the file containing what the
#      SQL generated by shp2pgsql should look like.
# $4 - The name of the file containing the expected results of
#      SELECT rast FROM _tblname should look like.
# $5 - Command line options for raster2pgsql.
# $6 - If you pass true, this will run the loader even if neither
#      of the expected results files exists (though of course
#      the results won't be compared with anything).
#
run_raster_loader_and_check_output()
{
	_description=$1
	_tblname=$2
	_expected_sql_file=$3
	_expected_select_results_file=$4
	_loader_options=$5
	_run_always=$6
	# ON_ERROR_STOP is used by psql to return non-0 on an error
	_psql_opts="--no-psqlrc --variable ON_ERROR_STOP=true"

	if [ -n "$_run_always" -o -r "$_expected_sql_file" -o -r "$_expected_select_results_file" ]; then
		show_progress

		# Produce the output SQL file.
		${RASTER2PGSQL} $_loader_options -C -f the_rast ${TEST}.tif $_tblname \
			> ${TMPDIR}/loader.out \
			2> ${TMPDIR}/loader.err

		if [ $? -gt 0 ]; then
			fail " $_description: running raster2pgsql" "${TMPDIR}/loader.err"
			return 1
		fi

		# Compare the output SQL file with the expected if there is one.
		if [ -r $_expected_sql_file ]; then
			show_progress
			if diff -w "${TMPDIR}/loader.out" "$_expected_sql_file" > /dev/null; then
				:
			else
				fail " $_description: actual SQL does not match expected.", \
					"${TMPDIR}/loader.out"
			fi
		fi

		# Run the loader SQL script.
		show_progress
		${PSQL} ${_psql_opts} -f ${TMPDIR}/loader.out "${DB}" > ${TMPDIR}/loader.err 2>&1
		if [ $? -gt 0 ]; then
			fail " $_description: running raster2pgsql output" "${TMPDIR}/loader.err"
			return 1
		fi

		# Run the select script (if there is one)
		if [ -r "${TEST}.select.sql" ]; then
			if run_simple_test "${TEST}.select.sql" "$_expected_select_results_file" "$_description"; then
				:
			else
				# That will have already called fail, so just return an error.
				return 1
			fi
		fi
	fi
	return 0
}

#
#  run_raster_loader_test 
#
#  Load a raster with different methods
#
#  raster input is ${TEST}.tif, expected output is {$TEST}.expected
#
run_raster_loader_test ()
{
	# See if there is a custom command-line options file
	_custom_opts=""
	if [ -r ${TEST}.opts ]; then
		_custom_opts=`grep -v ^\s*# ${TEST}.opts`
	fi

	tblname="loadedrast"

	if run_raster_loader_and_check_output "test" "${tblname}" "${TEST}.sql.expected" "${TEST}.select.expected" \
		"$_custom_opts" "true"; then
		:
	else
		return 1
	fi
	drop_table_if_exists "${tblname}" >> ${TMPDIR}/regress_log 2>&1

	return 0
}

# Log an initialization error and optionally drop the db
init_db_error ()
{
	echo
	echo " Something went wrong during db initialization ($1)." 
	if test "$VERBOSE" -eq "1"; then
	  echo " Last 5 lines of ${TMPDIR}/regress_log follow:"
		echo "-----------------------------------------------------------------------------"
		tail -5 ${TMPDIR}/regress_log
		echo "-----------------------------------------------------------------------------"
	else
	  echo " For details, check ${TMPDIR}/regress_log"
	fi
	echo

	if test x"$OPT_DROP" = "xyes" -a x"$OPT_CREATE" = "xyes"; then
		dropdb "${DB}" > /dev/null
	else
		: echo "Drop database ${DB} manually"
	fi

	exit 1
}

# Count database objects
count_db_objects ()
{
	${PSQL} ${_psql_opts} -tAc "WITH counts as (
		select count(*) from pg_type union all 
		select count(*) from pg_proc union all 
		select count(*) from pg_cast union all
		select count(*) from pg_aggregate union all
		select count(*) from pg_operator union all
		select count(*) from pg_opclass union all
		select count(*) from pg_namespace
			where nspname NOT LIKE 'pg_%' union all
		select count(*) from pg_opfamily ) 
		select sum(count) from counts;" "${DB}"
	if [ $? -gt 0 ]; then
		init_db_error $1
	fi
}

# Prepare the database for spatial operations (extension method)
prepare_spatial_extensions ()
{
	echo "Preparing spatial db ${DB} using EXTENSION" 

	# ON_ERROR_STOP is used by psql to return non-0 on an error
	_psql_opts="--no-psqlrc --variable ON_ERROR_STOP=true"

	${PSQL} ${_psql_opts} -c "CREATE EXTENSION postgis" "${DB}" \
    >> ${TMPDIR}/regress_log 2>&1 || init_db_error "core extension"

  # NOTE: "postgis" extension includes raster...

	if test x"$OPT_WITH_TOPO" = "xyes"; then # {
    ${PSQL} ${_psql_opts} -c "CREATE EXTENSION postgis_topology" "${DB}" \
      >> ${TMPDIR}/regress_log 2>&1 || init_db_error "topology extension"
	fi # }

}

# Prepare the database for spatial operations (old method)
prepare_spatial ()
{
	echo "Preparing spatial db ${DB} " 

	# ON_ERROR_STOP is used by psql to return non-0 on an error
	_psql_opts="--no-psqlrc --variable ON_ERROR_STOP=true"

	${PSQL} ${_psql_opts} -Xf ${STAGED_SCRIPTS_DIR}/postgis.sql "${DB}" \
    >> ${TMPDIR}/regress_log 2>&1 || init_db_error "core module"

	if test -e ${STAGED_SCRIPTS_DIR}/postgis_comments.sql; then # {
		${PSQL} ${_psql_opts} -Xf ${STAGED_SCRIPTS_DIR}/postgis_comments.sql \
      "${DB}" >> ${TMPDIR}/regress_log 2>&1 || init_db_error "core comments"
	fi # }

	if test x"$OPT_WITH_TOPO" = "xyes"; then # {
		SCRIPT=${STAGED_SCRIPTS_DIR}/topology.sql
		if test -e ${SCRIPT}; then
			echo "Adding topology support" 
			${PSQL} ${_psql_opts} -Xf ${REGDIR}/../topology/topology.sql "${DB}" \
        >> ${TMPDIR}/regress_log 2>&1 || init_db_error "topology module"
		else
			echo "${SCRIPT} not found" >&2
			exit 1
		fi
		if test -e ${STAGED_SCRIPTS_DIR}/topology_comments.sql; then
			${PSQL} ${_psql_opts} -Xf ${STAGED_SCRIPTS_DIR}/topology_comments.sql \
        "${DB}" >> ${TMPDIR}/regress_log 2>&1 || \
        init_db_error "topology comments"
		fi
	fi # }

	if test x"$OPT_WITH_RASTER" = "xyes"; then # {
		SCRIPT=${STAGED_SCRIPTS_DIR}/rtpostgis.sql
		if test -e ${SCRIPT}; then
			echo "Adding raster support"
			${PSQL} ${_psql_opts} -Xf ${SCRIPT} "${DB}" \
        >> ${TMPDIR}/regress_log 2>&1 || init_db_error "raster module"
		else
			echo "${SCRIPT} not found" >&2
			exit 1
		fi
		if test -e ${STAGED_SCRIPTS_DIR}/raster_comments.sql; then
			${PSQL} ${_psql_opts} -Xf ${STAGED_SCRIPTS_DIR}/raster_comments.sql \
        "${DB}" >> ${TMPDIR}/regress_log 2>&1 || \
        init_db_error "raster comments"
		fi
	fi # }
}

# Create the spatial database
create_spatial ()
{
	echo "Creating spatial db ${DB} " 

	createdb --encoding=UTF-8 --template=template0 --lc-collate="C" "${DB}" > ${TMPDIR}/regress_log 2>&1 
	if [ $? -gt 0 ]; then # {
		fail "createdb failed" "${TMPDIR}/regress_log"
		exit 1
	fi # }
	createlang plpgsql "${DB}" >> ${TMPDIR}/regress_log

	# Count database objects before installing anything
	object_count_pre=$(count_db_objects "counting object before postgis install")

	if test x"$OPT_EXTENSIONS" = "xyes"; then # {
    prepare_spatial_extensions
  else # }{
    prepare_spatial
  fi # }
}

# Upgrade an existing database (soft upgrade)
upgrade_spatial ()
{
  echo "Upgrading spatial db ${DB} " 

  SCRIPT=${STAGED_SCRIPTS_DIR}/postgis_upgrade_*_minor.sql
  if test -e ${SCRIPT}; then
    echo "Upgrading core"
    ${PSQL} ${_psql_opts} -Xf ${SCRIPT} "${DB}" >> ${TMPDIR}/regress_log 2>&1 || init_db_error "core upgrade"
  else
    echo "${SCRIPT} not found" >&2
      exit 1
  fi

  if test x"$OPT_WITH_TOPO" = "xyes"; then
    SCRIPT=${STAGED_SCRIPTS_DIR}/topology_upgrade_*_minor.sql
    if test -e ${SCRIPT}; then
      echo "Upgrading topology"
      ${PSQL} ${_psql_opts} -Xf ${SCRIPT} "${DB}" >> ${TMPDIR}/regress_log 2>&1 || init_db_error "topology upgrade"
    else
      echo "${SCRIPT} not found" >&2
        exit 1
    fi
  fi

  if test x"$OPT_WITH_RASTER" = "xyes"; then
    SCRIPT=${STAGED_SCRIPTS_DIR}/rtpostgis_upgrade_*_minor.sql
    if test -e ${SCRIPT}; then
      echo "Upgrading raster"
      ${PSQL} ${_psql_opts} -Xf ${SCRIPT} "${DB}" >> ${TMPDIR}/regress_log 2>&1 || init_db_error "raster upgrade"
    else
      echo "${SCRIPT} not found" >&2
        exit 1
    fi
  fi
}

drop_spatial_extensions()
{
	#echo "Dropping spatial from ${DB} using EXTENSION" 

	# ON_ERROR_STOP is used by psql to return non-0 on an error
	_psql_opts="--no-psqlrc --variable ON_ERROR_STOP=true"

  if test x"$OPT_WITH_TOPO" = "xyes"; then
    ${PSQL} ${_psql_opts} -c "DROP EXTENSION postgis_topology;" \
      "${DB}" > ${TMPDIR}/uninstall.log 2> ${TMPDIR}/uninstall.err
    if [ $? -gt 0 ]; then # {
      fail "DROP EXTENSION postgis_topology failed" "${TMPDIR}/uninstall.err"
      ok=no
    fi # }
    show_progress # on to core uninstall
  fi

  ${PSQL} ${_psql_opts} -c "DROP EXTENSION postgis;" \
    "${DB}" > ${TMPDIR}/uninstall.log 2> ${TMPDIR}/uninstall.err
  if [ $? -gt 0 ]; then # {
    fail "DROP EXTENSION postgis failed" "${TMPDIR}/uninstall.err"
  fi # }

  test x"$ok" = "xyes"
  return $?
}

drop_spatial()
{
	#echo "Dropping spatial from ${DB}" 

  ok=yes

  if test x"$OPT_WITH_TOPO" = "xyes"; then
    ${PSQL} ${_psql_opts} -Xf ${STAGED_SCRIPTS_DIR}/uninstall_topology.sql \
      "${DB}" > ${TMPDIR}/uninstall.log 2> ${TMPDIR}/uninstall.err
    if [ $? -gt 0 ]; then # {
      fail "uninstall_topology.sql failed" "${TMPDIR}/uninstall.err"
      ok=no
    fi # }
    show_progress # on to raster uninstall
  fi

  if test x"$OPT_WITH_RASTER" = "xyes"; then
    ${PSQL} ${_psql_opts} -Xf ${STAGED_SCRIPTS_DIR}/uninstall_rtpostgis.sql \
      "${DB}" > ${TMPDIR}/uninstall.log 2> ${TMPDIR}/uninstall.err
    if [ $? -gt 0 ]; then # {
      fail "uninstall_rtpostgis.sql failed" "${TMPDIR}/uninstall.err"
      ok=no
    fi # }
    show_progress # on to postgis uninstall
  fi

  ${PSQL} ${_psql_opts} -Xf ${STAGED_SCRIPTS_DIR}/uninstall_postgis.sql \
    "${DB}" > ${TMPDIR}/uninstall.log 2> ${TMPDIR}/uninstall.err
  if [ $? -gt 0 ]; then # {
    fail "uninstall_postgis.sql failed" "${TMPDIR}/uninstall.err"
  fi # }

  test x"$ok" = "xyes"
  return $?
}

# Drop spatial from an existing database
uninstall_spatial()
{
  start_test "uninstall"
	if test x"$OPT_EXTENSIONS" = "xyes"; then # {
    ok=drop_spatial_extensions
  else # }{
    ok=drop_spatial
  fi # }
  

  if $ok; then # {
      show_progress # on to objects count
      object_count_post=$(count_db_objects "counting object after postgis uninstall")
      if test ${object_count_pre} != ${object_count_post}; then # {
            fail "Count of object before install (${object_count_pre}) != count after uninstall (${object_count_post})"
      else
            pass "(${object_count_pre})"
      fi # }
  
  fi # }
}

###################################################
# 
# Parse command line opts
#
###################################################

while [ -n "$1" ]; do

	if test "$1" = "-v"; then
		VERBOSE=1
		shift
		continue
	elif test "$1" = "--nodrop"; then
		OPT_DROP=no
		shift
		continue
	elif test "$1" = "--nocreate"; then
		OPT_CREATE=no
		shift
		continue
	elif test "$1" = "--expect"; then
		OPT_EXPECT=yes
		shift
		continue
	elif test "$1" = "--extensions"; then
		OPT_EXTENSIONS=yes
		shift
		continue
	elif test "$1" = "--upgrade"; then
		OPT_UPGRADE=yes
		shift
		continue
	elif test "$1" = "--topology"; then
		OPT_WITH_TOPO=yes
		shift
		continue
	elif test "$1" = "--raster"; then
		OPT_WITH_RASTER=yes
		shift
		continue
	elif test "$1" = "--clean"; then
		OPT_CLEAN=yes
		shift
		continue
	else
		break
	fi
done

if [ -z "$1" ]; then
	echo "Usage: $0 [<options>] <test> [<test>]" >&2
	echo "Options:" >&2
	echo " -v           be verbose about failures" >&2
	echo " --nocreate   do not create the regression database on start" >&2
	echo " --upgrade    source the upgrade scripts on start" >&2
	echo " --nodrop     do not drop the regression database on exit" >&2
	echo " --raster     load also raster extension" >&2
	echo " --topology   load also topology extension" >&2
	echo " --clean      cleanup test logs on exit" >&2
	echo " --expect     save obtained output as expected" >&2
	exit 1
fi

###################################################
# 
# Prepare the database
#
###################################################

db_exists=`${PSQL} -l | grep -w ${DB}`

if test -z "$db_exists"; then

	if test x"$OPT_CREATE" = "xyes"; then
		create_spatial
	else

		echo "Database ${DB} does not exist" >&2
		echo "Run w/out the --nocreate flag to create it" >&2
		exit 1
	fi
else
	if test x"$OPT_CREATE" = "xyes"; then
		echo "Database ${DB} already exist." >&2
		echo "Run with the --nocreate flag to use it " \
			"or drop it and try again." >&2
		exit 1
	else
		echo "Using existing database ${DB}"
	fi
fi

if test x"$OPT_UPGRADE" = "xyes"; then
	upgrade_spatial
fi

libver=`${PSQL} -tAc "select postgis_lib_version()" "${DB}"`

if [ -z "$libver" ]; then
	dropdb "${DB}"
	echo
	echo " Something went wrong (no postgis installed in ${DB})." 
	if [ -z "$db_exists" ]; then
		echo " For details, check ${TMPDIR}/regress_log"
	fi
	echo
	exit 1
fi

###################################################
# 
# Report runtime environment 
#
###################################################

geosver=`${PSQL} -tAc "select postgis_geos_version()" "${DB}"`
projver=`${PSQL} -tAc "select postgis_proj_version()" "${DB}"`
if test x"$OPT_WITH_RASTER" = "xyes"; then
  gdalver=`${PSQL} -tAc "select postgis_gdal_version()" "${DB}"`
fi
svnrev=`${PSQL} -tAc "select postgis_svn_version()" "${DB}"`
libbuilddate=`${PSQL} -tAc "select postgis_lib_build_date()" "${DB}"`
pgsqlver=`${PSQL} -tAc "select version()" "${DB}"`

echo "TMPDIR is ${TMPDIR}"
echo "PATH is ${PATH}"

echo
echo " $pgsqlver"
echo " Postgis $libver - r${svnrev} - $libbuilddate"
if [ -n "$geosver" ]; then
	echo "   GEOS: $geosver"
fi
if [ -n "$projver" ]; then
	echo "   PROJ: $projver"
fi
if [ -n "$gdalver" ]; then
	echo "   GDAL: $gdalver"
fi

###################################################
# 
# Run the tests
#
###################################################

echo 
echo "Running tests"
echo

RUN=0
SKIP=0
FAIL=0
#SUCCESS=0
while [ -n "$1" ]; do
	TEST="$1"; shift;

	# catch a common mistake (strip trailing .sql)
	TEST=`echo "$TEST" | sed 's/\.sql$//'`

	start_test "${TEST}"

	# Check for a "-pre.sh" file in case there are non-SQL setup tasks needed before
	# the test can be run.
        if [ -r "${TEST}-pre.sh" ]; then
		"${TEST}-pre.sh"
		if [ $? -gt 0 ]; then
			fail " setup script failed"
			continue
		else
			show_progress
		fi
        fi

	# Check for a "-pre.sql" file in case there is setup SQL needed before
	# the test can be run.
        if [ -r "${TEST}-pre.sql" ]; then
		if run_simple_sql "${TEST}-pre.sql"; then
			show_progress
		else
			continue
		fi
        fi

	# Check .dbf *before* .sql as loader test could
	# create the .sql
	# Check for .dbf not just .shp since the loader can load
	# .dbf files without a .shp.
	if [ -r "${TEST}.dbf" ]; then
		if run_loader_test; then
			pass
		fi
	# Check .tif *before* .sql as loader test could
	# create the .sql
	elif [ -r "${TEST}.tif" ]; then
		if run_raster_loader_test; then
			pass
		fi
	elif [ -r "${TEST}.sql" ]; then
		if run_simple_test ${TEST}.sql ${TEST}_expected; then
			pass
		fi
	else
		echo " skipped (can't read ${TEST}.sql)"
		SKIP=`expr $SKIP + 1`
		continue
	fi

	# Check for a "-post.sql" file in case there is teardown SQL needed after
	# the test has been run.
        if [ -r "${TEST}-post.sql" ]; then
		if ! run_simple_sql "${TEST}-post.sql"; then
			echo " ... but cleanup sql failed!"
		fi
        fi

	# Check for a "-post.sh" file in case there are non-SQL teardown tasks needed after
	# the test has been run.
        if [ -r "${TEST}-post.sh" ]; then
		"${TEST}-post.sh"
		if [ $? -gt 0 ]; then
			echo " ... but cleanup script failed!"
		fi
        fi

done

###################################################
# 
# Uninstall postgis (serves as an uninstall test)
#
###################################################

# We only test uninstall if we've been asked to drop 
# and we did create
# and nobody requested raster or topology
# (until they have an uninstall script themself)
if test x"$OPT_DROP" = "xyes" \
     -a x"$object_count_pre" != "x"
then # {
    uninstall_spatial
fi # }

###################################################
# 
# Summary report
#
###################################################

echo
echo "Run tests: $RUN"
#echo "Skipped: $SKIP"
#echo "Successful: $SUCCESS"
echo "Failed: $FAIL"

if test x"$OPT_CLEAN" = "xyes"; then
	rm -f "${TMPDIR}"/dumper.*
	rm -f "${TMPDIR}/loader.*"
	rm -f "${TMPDIR}/regress_log"
	rm -f "${TMPDIR}"/uninstall.*
	rm -f "${TMPDIR}"/tmp/test_*
	rmdir -p "${TMPDIR}/tmp/" 2> /dev/null
	rmdir -p "${TMPDIR}" 2> /dev/null
fi

if test x"$OPT_DROP" = "xyes" -a x"$OPT_CREATE" = "xyes"; then
	sleep 1
	dropdb "${DB}" > /dev/null
else
	: echo "Drop database ${DB} manually"
fi

# Set the locale back to the original
export LC_ALL=$ORIG_LC_ALL
export LANG=$ORIG_LANG

exit $FAIL