#!/bin/sh
# $Id: setup,v 1.6 2009/10/12 19:31:10 bhockney Exp $
# Webfwlog (C) 2003-2009 by Bob Hockney <zeus@ix.netcom.com>
#
# PostgreSQL setup script for webfwlog
#

DB="PostgreSQL"
PROG="psql"

SERVER="localhost"
PORT="5432"
ADMIN_USER="postgres"
ADMIN_PASS=""
USER="webfwlog"
PASS="password"
DATABASE="webfwlog"
WFWL_SCHEMA="webfwlog"
WFWL_OLD_SCHEMA="public"
ULOG_SCHEMA="ulogd"
ULOG_TABLE="ulog"
ULOG_OLD_SCHEMA="public"
ULOG_OLD_TABLE="ulog"
ULOGD_USER="ulogd"

HAVE_DATABASE=0
HAVE_SCHEMAS=0
HAVE_WFWL_SCHEMA=0
HAVE_WFWL_REPORTS=0
HAVE_WFWL_HOSTNAMES=0
HAVE_WFWL_SERVICES=0
HAVE_WFWL_OLD_REPORTS=0
HAVE_WFWL_OLD_HOSTNAMES=0
HAVE_WFWL_OLD_SERVICES=0
HAVE_ULOG_SCHEMA=0
HAVE_ULOG_TABLE=0
HAVE_ULOG_OLD_TABLE=0

COPY_WFWL_TABLES=0
GRANTS=0
ADD_EXAMPLES=0
EXAMPLES_DIR="../examples"
OUTFILE="pgsql_setup"

if (printf "")
then
	ECHO="printf %b"
else
	echo
	echo "The printf program could not be found."
	echo "printf is required in order to run setup."
	echo
	exit 1
fi

SCRIPT=""

if test -d scripts
then :
else
	$ECHO "Scripts directory not found\n"
	$ECHO "Please change the working directory to the directory containing setup\n"
	exit 1 
fi

# test client program existance
if ($PROG --version) > /dev/null 2>&1
then :
else
	$ECHO "Client program $PROG not found\n"
	$ECHO "Exiting...\n"
	exit 1
fi

get_param () {
	read N
	if test -n "$N"
	then
		RET="$N"
	else
		RET="$1"
	fi
	$ECHO "$RET"
	return 0
}

get_yes_or_no () {
	read N
	if test -z "$N"
	then
		RET=1
	else
		N=`$ECHO "$N" | sed 's/^[Yy].*/y/'`
		if test "$N" = "y"
		then
			RET=1
		else
			RET=0
		fi
	fi
	$ECHO "$RET"
	return 0
}

$ECHO "
Welcome to the webfwlog setup program for $DB.  You will be prompted
to answer some questions and for input needed to set up your $DB server
for use with webfwlog.  You will also be prompted before any changes are made.

PREREQUISITES

Before running this script you should have your $DB server installed and
running, and you will need to have login credentials for an admin user
for $DB such as 'postgres' or 'pgsql'.

Also, if you want to use webfwlog with ulog data you need to set up ulog
to log packets to your $DB server before setting up webfwlog.  See the
documentation for ulogd for details on setting up a ulog table.


First, the location of the $DB server and the admin user login
credentials.  If the server is not on localhost then enter the host name or
IP address where the $DB server is running.
\n"

# get database server location
# get admin user credentials

$ECHO "Enter location of $DB server (<cr>=$SERVER): "
SERVER="`get_param "$SERVER"`"

if test "$SERVER" != "localhost"
then
	$ECHO "Enter port for $DB server (<cr>=$PORT): "
	PORT="`get_param "$PORT"`"
fi

$ECHO "Enter $DB admin user (<cr>=$ADMIN_USER): "
ADMIN_USER="`get_param "$ADMIN_USER"`"

$ECHO "Enter $DB admin user password (<cr>=<blank>): "
stty -echo
ADMIN_PASS="`get_param "$ADMIN_PASS"`"
stty echo
$ECHO "\n"

# test database server existence and version

if test "$SERVER" != "localhost"
then
	export PGHOST=$SERVER
	export PGPORT=$PORT
fi
export PGUSER=$ADMIN_USER
export PGPASSWORD=$ADMIN_PASS

if VERSION=`$PROG -t -c "SELECT version() AS version" template1`
then :
else
	$ECHO "\n"
	$ECHO "Could not get $DB server version\n"
	$ECHO "Is the server running on $SERVER?\n"
	$ECHO "Are the credentials for $DB admin user \`$ADMIN_USER\` correct?\n"
	$ECHO "Exiting...\n\n"
	exit 1
fi

VERSION=`echo $VERSION |sed -e 's/^ *PostgreSQL \([^ ]*\) .*$/\1/'`

$ECHO "\n"
$ECHO "$DB server version $VERSION found on $SERVER!\n"

MAJOR=`$ECHO "$VERSION\n" | cut -d. -f1`
MINOR=`$ECHO "$VERSION\n" | cut -d. -f2`
EXTRA=`$ECHO "$VERSION\n" | cut -d. -f3 | sed -e 's/\(^[0-9]\{1,\}\).*/\1/'` 

if test $MAJOR -lt 7 -o \( $MAJOR -eq 7 -a $MINOR -lt 1 \)
then
	$ECHO "\n"
	$ECHO "$DB version >= 7.1 is required for use with webfwlog\n"
	$ECHO "Exiting...\n\n"
	exit 1
fi

# query re: data layout desired (database and table names)

if ($PROG -c "SELECT count(*) FROM pg_catalog.pg_namespace" template1) > /dev/null 2>&1
then
	HAVE_SCHEMAS=1
fi

$ECHO "
Enter the database you want to use with webfwlog.  If the database does not
exist it will be created.  Note that if you are using webfwlog with ulog data
the same database must be used for webfwlog's tables and the ulog table.
\n"
if test $HAVE_SCHEMAS -eq 0
then
$ECHO "
If the database exists and existing webfwlog tables are found they will be
used, otherwise new webfwlog tables will be created in this database.
\n"
fi

$ECHO "Enter database for webfwlog (<cr>=$DATABASE): "
DATABASE="`get_param "$DATABASE"`"

if ($PROG -c "SELECT 1;" $DATABASE) > /dev/null 2>&1
then 
	$ECHO "\n"
	$ECHO "Using existing database $DATABASE for webfwlog\n"
	HAVE_DATABASE=1
else
	$ECHO "\n"
	$ECHO "Creating new database $DATABASE for webfwlog\n"
	HAVE_DATABASE=0
fi

export PGDATABASE=$DATABASE

if test $HAVE_SCHEMAS -eq 1
then
$ECHO "
Enter the schema you want to use for the webfwlog tables.  Your $DB
server supports schemas and it is recomended to put the webfwlog tables in a
separate schema such as 'webfwlog'.  If the schema does not exist it will be
created.  If the schema exists and existing webfwlog tables are found they
will be used, otherwise new webfwlog tables will be created in this database.
\n"
	$ECHO "Enter the schema to use for the webfwlog tables (<cr>=$WFWL_SCHEMA): "
	WFWL_SCHEMA="`get_param "$WFWL_SCHEMA"`"

	if test $HAVE_DATABASE -eq 1 -a -n "`$PROG -tc "SELECT * FROM pg_catalog.pg_namespace WHERE nspname='$WFWL_SCHEMA'" 2> /dev/null`"
	then 
		$ECHO "\n"
		$ECHO "Using existing schema $WFWL_SCHEMA for webfwlog tables\n"
		HAVE_WFWL_SCHEMA=1
	else
		$ECHO "\n"
		$ECHO "Creating new schema $WFWL_SCHEMA for webfwlog tables\n"
		HAVE_WFWL_SCHEMA=0
	fi
fi

if test $HAVE_SCHEMAS -eq 1
then
	WFWL_REPORTS="${WFWL_SCHEMA}.reports"
	WFWL_HOSTNAMES="${WFWL_SCHEMA}.hostnames"
	WFWL_SERVICES="${WFWL_SCHEMA}.services"
else
	WFWL_REPORTS="reports"
	WFWL_HOSTNAMES="hostnames"
	WFWL_SERVICES="services"
fi

# query re: existing webfwlog tables

if test $HAVE_DATABASE -ne 0
then
	if ($PROG -c "SELECT count(*) FROM $WFWL_REPORTS;") > /dev/null 2>&1
	then 
		$ECHO "Using existing table $WFWL_REPORTS for webfwlog report definitions\n"
		HAVE_WFWL_REPORTS=1
	fi

	if ($PROG -c "SELECT count(*) FROM $WFWL_HOSTNAMES;") > /dev/null 2>&1
	then 
		$ECHO "Using existing table $WFWL_HOSTNAMES for webfwlog hostnames cache\n"
		HAVE_WFWL_HOSTNAMES=1
	fi

	if ($PROG -c "SELECT count(*) FROM $WFWL_SERVICES;") > /dev/null 2>&1
	then 
		$ECHO "Using existing table $WFWL_SERVICES for webfwlog services cache\n"
		HAVE_WFWL_SERVICES=1
	fi
fi

# test existing webfwlog reports table for last accessed/last saved
# reset indexes and column defaults on all existing webfwlog tables
# create webfwlog database/tables if they do not exist

test_indexes () {
	$ECHO "$1\n" | while read N
	do
		N=`$ECHO "$N" | sed -e 's/^\(Ind[a-z]*:\)* *"*\([a-z_]*\)"* *[^|]*$/\2/'`
		if test -z "$N"; then continue; fi;

		if test "$x" = "`echo $N | sed -e 's/\(.*\)\(_[a-z]*\)/\1/'`" \
			-o "webfwlog_code" = "$N"
		then
			$ECHO "0"
			return
		fi
	done

	return
}

if test $HAVE_DATABASE -eq 0
then
	SCRIPT="${SCRIPT}--\n-- Create new database $DATABASE\n--\nCREATE DATABASE $DATABASE;\n--\n"
fi

SCRIPT="${SCRIPT}\\\\connect $DATABASE $ADMIN_USER\n"

if test $HAVE_SCHEMAS -eq 1 -a $HAVE_WFWL_SCHEMA -eq 0
then
	SCRIPT="${SCRIPT}--\n-- Create new schema $WFWL_SCHEMA\n--\nCREATE SCHEMA $WFWL_SCHEMA;\n"
fi

if test $HAVE_WFWL_REPORTS -eq 0
then
	$ECHO "Creating new table $WFWL_REPORTS for webfwlog report definitions\n"
	SCRIPT="$SCRIPT`awk "{ if (NR != 1) print | \\\"sed 's/\\\$WFWL_SCHEMA.reports/$WFWL_REPORTS/'\\\" }" scripts/wfwl_reports_create`\n"
fi

if test $HAVE_WFWL_HOSTNAMES -eq 0
then
	$ECHO "Creating new table $WFWL_HOSTNAMES for webfwlog hostnames cache\n"
	SCRIPT="$SCRIPT`awk "{ if (NR != 1) print | \\\"sed 's/\\\$WFWL_SCHEMA.hostnames/$WFWL_HOSTNAMES/'\\\" }" scripts/wfwl_hostnames_create`\n"
fi

if test $HAVE_WFWL_SERVICES -eq 0
then
	$ECHO "Creating new table $WFWL_HOSTNAMES for webfwlog hostnames cache\n"
	SCRIPT="$SCRIPT`awk "{ if (NR != 1) print | \\\"sed 's/\\\$WFWL_SCHEMA.services/$WFWL_SERVICES/'\\\" }" scripts/wfwl_services_create`\n"
fi

# TODO
# move data if requested/drop old tables/database

if test $HAVE_DATABASE -eq 1 -a $HAVE_SCHEMAS -eq 1 -a $HAVE_WFWL_REPORTS -eq 0
then
	$ECHO "
If you have existing Webfwlog tables in a different schema and answer yes to
the next question you can copy existing report definitions and hostname and
service name caches from the tables in the old schema to the tables in the new
schema.  The existing tables will NOT be dropped, so be sure to update your
webfwlog.conf file to point to the new schema.
\n"
	$ECHO "Do you want to copy existing webfwlog data to the new tables? [Y/n]: "
	COPY_WFWL_TABLES="`get_yes_or_no`"

	until test $COPY_WFWL_TABLES -eq 0 -o $HAVE_WFWL_OLD_REPORTS -eq 1 \
		-o $HAVE_WFWL_OLD_HOSTNAMES -eq 1 -o $HAVE_WFWL_OLD_SERVICES -eq 1
	do
		$ECHO "\nEnter schema with existing webfwlog tables (<cr>=$WFWL_OLD_SCHEMA): "
		WFWL_OLD_SCHEMA="`get_param "$WFWL_OLD_SCHEMA"`"

		WFWL_OLD_REPORTS="${WFWL_OLD_SCHEMA}.reports"
		WFWL_OLD_HOSTNAMES="${WFWL_OLD_SCHEMA}.hostnames"
		WFWL_OLD_SERVICES="${WFWL_OLD_SCHEMA}.services"

		if ($PROG -c "SELECT count(*) FROM $WFWL_OLD_REPORTS;") > /dev/null 2>&1
		then 
			HAVE_WFWL_OLD_REPORTS=1
		fi
		if ($PROG -c "SELECT count(*) FROM $WFWL_OLD_HOSTNAMES;") > /dev/null 2>&1
		then 
			HAVE_WFWL_OLD_HOSTNAMES=1
		fi
		if ($PROG -c "SELECT count(*) FROM $WFWL_OLD_SERVICES;") > /dev/null 2>&1
		then 
			HAVE_WFWL_OLD_SERVICES=1
		fi
	done
fi

if test $HAVE_SCHEMAS -eq 0
then
	WFWL_OLD_REPORTS="reports"
	WFWL_OLD_HOSTNAMES="hostnames"
	WFWL_OLD_SERVICES="services"
fi

if test $HAVE_WFWL_OLD_REPORTS -eq 1 -a $HAVE_WFWL_REPORTS -eq 0
then R="$WFWL_OLD_REPORTS"
else R="$WFWL_REPORTS"
fi

if test $HAVE_WFWL_REPORTS -ne 1 -a $HAVE_WFWL_OLD_REPORTS -ne 1 || ($PROG -c "SELECT last_saved, last_accessed FROM $R;") > /dev/null 2>&1
then :
else
	SCRIPT="$SCRIPT`awk "{ if (NR != 1) print | \\\"sed 's/\\\$WFWL_SCHEMA.reports/$R/'\\\" }" scripts/wfwl_reports_addcol`\n"
fi

if test $HAVE_WFWL_OLD_REPORTS -eq 1
then
	SCRIPT="${SCRIPT}--\n-- Copy webfwlog report definitions\n-- and hostname and service name caches to new tables\n--\n"
	SCRIPT="${SCRIPT}INSERT INTO $WFWL_REPORTS (code, description, definition, last_accessed, last_saved)
	SELECT code, description, definition, last_accessed, last_saved FROM $WFWL_OLD_REPORTS;\n"
fi

if test $HAVE_WFWL_OLD_HOSTNAMES -eq 1
then
	SCRIPT="${SCRIPT}INSERT INTO $WFWL_HOSTNAMES (refresh, ip_addr, hostname)
	SELECT refresh, ip_addr, hostname FROM $WFWL_OLD_HOSTNAMES;\n"
fi

if test $HAVE_WFWL_OLD_SERVICES -eq 1
then
	SCRIPT="${SCRIPT}INSERT INTO $WFWL_SERVICES (refresh, ip_protocol, port, service)
	SELECT refresh, ip_protocol, port, service FROM $WFWL_OLD_SERVICES;\n"
fi

# check reports table for unique constraint on code column
if test $HAVE_WFWL_REPORTS -eq 1
then
	DEF=`$PROG -c "\d $WFWL_REPORTS" 2> /dev/null` > /dev/null 2>&1
	x=webfwlog_code
	if test -z `test_indexes "$DEF"`
	then
		SCRIPT="${SCRIPT}--\n-- Add unique constraint on code column of reports table\n--\n"
		SCRIPT="${SCRIPT}CREATE UNIQUE INDEX webfwlog_code ON $WFWL_REPORTS (code);\n"
	fi
fi

# drop / add functions

if test $HAVE_SCHEMAS -eq 1
then
	W_SCHEMA="$WFWL_SCHEMA."
else
	W_SCHEMA=
fi

SCRIPT="${SCRIPT}--\n-- Add / replace postgresql inet address conversion functions\n--\n"

if ($PROG -c "SELECT ${W_SCHEMA}webfwlog_ntoa(0::int8);") > /dev/null 2>&1
then
	SCRIPT="${SCRIPT}DROP FUNCTION ${W_SCHEMA}webfwlog_ntoa (int8);\n"
fi
if ($PROG -c "SELECT ${W_SCHEMA}webfwlog_aton('1.2.3.4'::inet);") > /dev/null 2>&1
then
	SCRIPT="${SCRIPT}DROP FUNCTION ${W_SCHEMA}webfwlog_aton (inet);\n"
fi
if ($PROG -c "SELECT ${W_SCHEMA}webfwlog_aton('1.2.3.4'::text);") > /dev/null 2>&1
then
	SCRIPT="${SCRIPT}DROP FUNCTION ${W_SCHEMA}webfwlog_aton (text);\n"
fi
if ($PROG -c "SELECT ${W_SCHEMA}next_part('1.2.3'::text);") > /dev/null 2>&1
then
	SCRIPT="${SCRIPT}DROP FUNCTION ${W_SCHEMA}next_part (text);\n"
fi

SCRIPT="$SCRIPT`awk "{ if (NR != 1) print | \\\"sed 's/\\\$WFWL_SCHEMA./$W_SCHEMA/g'\\\" }" scripts/wfwl_functions`\n"

# query re: data source(s) (database, table name)
# create new database/move data if requested.

$ECHO "
If you want to use webfwlog with ulog data answer yes to the next question.
You will then be prompted for the schema (if required) and table name for your
logs.

When found, the ulog table will be checked for indexes used to improve
performance with webfwlog, which will be added if not found.  The ulog table
will also be checked for the local_time and local_hostname columns used by
the LOCAL plugin of ulogd, which will also be added if not found.
The use of the LOCAL plugin of ulogd is highly recommended.

NOTE: A ulog table must already exist before it can be set up for use with
webfwlog.  See the documentation of ulogd for more information on setting
up a ulog table.

If you do not want to use webfwlog with ulog then answer no to this question.
\n"

$ECHO "Do you want to set up webfwlog for use with ulog data? [Y/n]: "
USE_ULOG="`get_yes_or_no`"

if test $USE_ULOG -eq 1 -a $HAVE_SCHEMAS -eq 1
then
	$ECHO "
Enter the schema you want to use for your ulog table.  Your $DB server
supports schemas and it is recomended to put the ulog table in a separate
schema such as 'ulogd'.  However, when you set up the ulog table it was
probably created in the public schema and setup can move it to the schema you
enter here if the new schema does not already contain a ulog table.
The schema will be created if it does not exist.

If the new schema does not exist or a ulog table cannot be found in this schema
you will be prompted for a schema containing a ulog table, which must already
exist before you can set up webfwlog for use with ulog data.  See the
documentation of ulogd for more information on setting up a ulog table.

IMPORTANT: In order to use a schema other than 'public' with ulogd you will
need to do one of the following:
  - Use ulogd >= 1.22
  - Set the default search_path in postgresql.conf to include the schema with
      your data
  - Use the same name for the ulogd schema as for the $DB ulogd user.
      This works because the default search_path is '\$USER,public'.

Also, unless you are using ulogd >=1.22 you can only have one table named
'ulog' in all schemas in the database, so if data is moved to a new schema the
existing table will be dropped.

Before moving your ulog data you should read the README in this directory for
additional considerations.
\n"
	$ECHO "Enter schema to use for the ulog tables (<cr>=$ULOG_SCHEMA): "
	ULOG_SCHEMA="`get_param "$ULOG_SCHEMA"`"

	if test $HAVE_DATABASE -eq 1 -a -n "`$PROG -tc "SELECT * FROM pg_catalog.pg_namespace WHERE nspname='$ULOG_SCHEMA'"`"
	then 
		$ECHO "\n"
		$ECHO "Using existing schema $ULOG_SCHEMA for ulog\n"
		HAVE_ULOG_SCHEMA=1
	fi
fi

if test $HAVE_SCHEMAS -eq 1
then
	U_SCHEMA="$ULOG_SCHEMA."
else
	U_SCHEMA=
fi

add_ulog_indexes () {
	for x in saddr daddr tcp_d tcp_s udp_d udp_s 
	do
		if test -z "`test_indexes "$1"`"
		then
			if test "$x" = "saddr" -o "$x" = "daddr" -o "$x" = "tcp_d" \
				-o "$x" = "tcp_s" -o "$x" = "udp_d" -o "$x" = "udp_s"
			then
				if test -z `$ECHO "$x" | sed -e 's/.addr//'`
				then
					$ECHO "CREATE INDEX ${x}_$ULOG_TABLE ON $ULOG (ip_$x);\n"
				else
					$ECHO "CREATE INDEX ${x}_$ULOG_TABLE ON $ULOG (${x}port, ip_protocol);\n"
				fi
			fi
		fi
	done

	return 0;

}

if test $USE_ULOG -eq 1
then
	until test $HAVE_ULOG_TABLE -eq 1 -o $HAVE_ULOG_OLD_TABLE -eq 1
	do
		$ECHO "\n"
		$ECHO "Enter name of ulog table (<cr>=$ULOG_TABLE): "
		ULOG_TABLE="`get_param "$ULOG_TABLE"`"

		if test $HAVE_SCHEMAS -eq 1
		then
			ULOG="$ULOG_SCHEMA.$ULOG_TABLE"
		else
			ULOG="$ULOG_TABLE"
		fi

		if ($PROG -c "SELECT count(*) FROM $ULOG;") > /dev/null 2>&1
		then 
			$ECHO "\nUsing ulog table $ULOG\n"
			HAVE_ULOG_TABLE=1
		else
			if test $HAVE_SCHEMAS -eq 1
			then
				$ECHO "
Creating new ulog table $ULOG from existing ulog data.

You will be prompted for the schema and table name for your current data, which
must already exist.  You will also be prompted for the $DB user name
used by the ulogd daemon, which also must already exist.

NOTE: After the table has been copied, you will need to modify your ulogd.conf
file to point to the new schema and restart the ulogd daemon.  Also the
existing ulog table in the existing schema will be dropped.
BE SURE TO BACK UP YOUR DATA FIRST!!
"
				until test $HAVE_ULOG_OLD_TABLE -eq 1
				do
					$ECHO "\n"
					$ECHO "Enter schema for existing ulog table (<cr>=$ULOG_OLD_SCHEMA): "
					ULOG_OLD_SCHEMA="`get_param "$ULOG_OLD_SCHEMA"`"
					$ECHO "Enter name of existing ulog table (<cr>=$ULOG_OLD_TABLE): "
					ULOG_OLD_TABLE="`get_param "$ULOG_OLD_TABLE"`"

					if test $HAVE_SCHEMAS -eq 1
					then
						OLD_ULOG="$ULOG_OLD_SCHEMA.$ULOG_OLD_TABLE"
					else
						OLD_ULOG="$ULOG_OLD_TABLE"
					fi

					if ($PROG -c "SELECT count(*) FROM $OLD_ULOG;") > /dev/null 2>&1
					then 
						HAVE_ULOG_OLD_TABLE=1
					fi
				done
				$ECHO "\nUsing existing ulog table $OLD_ULOG\n\n"
				$ECHO "Enter $DB user name for the ulogd daemon (<cr>=$ULOGD_USER): "
				ULOGD_USER="`get_param "$ULOGD_USER"`"
				$ECHO "\nUsing $DB user $ULOGD_USER for the ulogd daemon\n"
			fi
		fi
	done

	#   test existing ulog table for local _hostname and local_time and update if needed.
	if test $HAVE_ULOG_OLD_TABLE -eq 1 -a $HAVE_ULOG_TABLE -eq 0
	then T="$OLD_ULOG"
	else T="$ULOG"
	fi

	$ECHO "\n"
	$ECHO "Checking table $T ... "

	if ($PROG -c "SELECT local_hostname, local_time FROM $T;") > /dev/null 2>&1
	then :
	else
		SCRIPT="$SCRIPT`awk "{ if (NR != 1) print | \\\"sed 's/\\\$ULOG_TABLE/$T/'\\\" }" scripts/ulog_local`\n"
	fi

	if test $HAVE_ULOG_OLD_TABLE -eq 1 -a $HAVE_ULOG_TABLE -eq 0
	then
		if test $HAVE_ULOG_SCHEMA -eq 0
		then
			SCRIPT="${SCRIPT}--\n-- Create new schema $ULOG_SCHEMA\n--\nCREATE SCHEMA $ULOG_SCHEMA;\n"
		fi
		SCRIPT="$SCRIPT`awk "{ if (NR != 1) print | \\\"sed 's/\\\$ULOG_TABLE/$ULOG_TABLE/g' | sed 's/\\\$ULOG_SCHEMA/$ULOG_SCHEMA/g' | sed 's/\\\$ULOG_OLD_TABLE/$ULOG_OLD_TABLE/g' | sed 's/\\\$USER/$ULOGD_USER/g' | sed 's/\\\$ULOG_OLD_SCHEMA/$ULOG_OLD_SCHEMA/g'\\\" }" scripts/ulog_copy`\n"
	fi

	#   reset indexes and column defaults on existing ulog data
	DEF=`$PROG -c "\d $ULOG" 2> /dev/null` > /dev/null 2>&1
	SCRIPT_ADD="`add_ulog_indexes "$DEF"`"
	if test -n "$SCRIPT_ADD"
	then
		SCRIPT="${SCRIPT}--\n-- Add indexes to ulog table\n--\n$SCRIPT_ADD\n"
	fi

	SCRIPT="${SCRIPT}--\n-- Run VACUUM ANALYZE on Ulog table\n--\nVACUUM ANALYZE $ULOG;\n"

	$ECHO "Done!\n"

fi

# set rights

$ECHO "
Answer yes to the following question set the rights for the $DB
user you want to use with webfwlog.  You must do this the first time you set up
webfwlog, and it is recommended to do this if you are upgrading from a previous
version of webfwlog.  You can also use this to change the password for the
$DB user for webfwlog.  Be sure to update your webfwlog.conf file
after setting or changing the webfwlog user's password.
\n"

$ECHO "Do you want to set the $DB user rights? [Y/n]: "
GRANTS="`get_yes_or_no`"

if test $GRANTS -eq 1
then
	# get webfwlog user credentials
	$ECHO "\n"
	$ECHO "Enter $DB user for webfwlog (<cr>=$USER): "
	USER="`get_param "$USER"`"

	$ECHO "Enter password for $DB user for webfwlog (<cr>=$PASS): "
	PASS="`get_param "$PASS"`"

	if ($PROG -c "ALTER USER $USER;" template1) > /dev/null 2>&1
	then
		SCRIPT="${SCRIPT}--\n-- Set password for $DB user $USER\n--\n"
		SCRIPT="${SCRIPT}ALTER USER $USER WITH PASSWORD '$PASS';\n"
	else
		SCRIPT="${SCRIPT}--\n-- Create $DB user $USER\n--\n"
		SCRIPT="${SCRIPT}CREATE USER $USER WITH PASSWORD '$PASS';\n"
	fi

	if test $HAVE_SCHEMAS -eq 1
	then
		SCRIPT="$SCRIPT`awk "{ if (NR != 1) print | \\\"sed 's/\\\$USER/$USER/' | sed 's/\\\$WFWL_SCHEMA/$WFWL_SCHEMA/' | sed 's/\\\$DB/$DATABASE/'\\\" }" scripts/wfwl_73_grants`\n"
	fi

	SCRIPT="$SCRIPT`awk "{ if (NR != 1) print | \\\"sed 's/\\\$USER/$USER/' | sed 's/\\\$WFWL_SCHEMA./$W_SCHEMA/'\\\" }" scripts/wfwl_grants`\n"
	
	if test $USE_ULOG -eq 1
	then
		if test $HAVE_SCHEMAS -eq 1
		then
			SCRIPT="$SCRIPT`awk "{ if (NR != 1) print | \\\"sed 's/\\\$USER/$USER/' | sed 's/\\\$ULOG_SCHEMA/$ULOG_SCHEMA/'\\\" }" scripts/ulogd_73_grants`\n"
		fi
		SCRIPT="$SCRIPT`awk "{ if (NR != 1) print | \\\"sed 's/\\\$USER/$USER/' | sed 's/\\\$ULOG_SCHEMA./$U_SCHEMA/' | sed 's/\\\$ULOG_TABLE/$ULOG_TABLE/'\\\" }" scripts/ulogd_grants`\n"
	fi
	
	$ECHO "\n"
	$ECHO "Using $DB user \`$USER\`\n"
fi

# query regarding import of definitions

create_def () {
	cat $1 | while read N 
	do
		LINE=`$ECHO "$N" |sed 's/^ *#.*//'`
		if test -z "$LINE"; then return; fi
		KEY=`$ECHO "$LINE" | cut -d= -f1`
		VALUE=`$ECHO "$LINE" | cut -d= -f2`
		LEN=`$ECHO "$VALUE"|wc -c`
		LEN=`expr $LEN - 2` 
		$ECHO "s:`$ECHO "$KEY"|wc -c`:\"$KEY\";s:$LEN:$VALUE;"
	done;
}

get_def () {
	$ECHO "`grep ^description $1|cut -d= -f2|sed 's/\"//g'`\n"
	$ECHO "a:`cat $1 |grep -v "^ *#"|grep -v "^$"|wc -l|cut -d" " -f1`:{"
	$ECHO "`create_def $1`"}
}

$ECHO "
You can populate the webfwlog reports table with report definitions in the
examples directory by answering yes below.  Existing definitions will not
be overwritten.
\n"
$ECHO "Do you want to use populate the reports table with example reports? [Y/n]: "
ADD_EXAMPLES="`get_yes_or_no`"

if test $ADD_EXAMPLES -eq 1
then
	if test -d $EXAMPLES_DIR
	then
		$ECHO "\nCreating INSERT statements for example reports ... "
		SCRIPT="${SCRIPT}--\n-- Insert example report definitions\n--\n"
		SCRIPT="${SCRIPT}CREATE TEMPORARY TABLE examples AS SELECT * FROM $WFWL_REPORTS WHERE false;\n"
		for x in `ls $EXAMPLES_DIR`
		do
			if test -f $EXAMPLES_DIR/$x
			then
				DEF=`get_def $EXAMPLES_DIR/$x`
				SCRIPT="${SCRIPT}INSERT INTO examples (code, description, definition, last_saved, last_accessed)\nVALUES ('$x', '`$ECHO "$DEF"|head -n1`', '`$ECHO "$DEF"|awk "{ if (NR != 1) print }"`', extract(EPOCH FROM TIMESTAMP '`date`'), extract(EPOCH FROM TIMESTAMP '`date`'));\n"
			fi
		done
		SCRIPT="${SCRIPT}INSERT INTO $WFWL_REPORTS (code, description, definition, last_saved, last_accessed)\n  SELECT s.code, s.description, s.definition, s.last_saved, s.last_accessed\n  FROM examples s LEFT JOIN $WFWL_REPORTS r ON r.code=s.code\n  WHERE r.code IS NULL;\n"
		SCRIPT="${SCRIPT}DROP TABLE examples;\n"
		$ECHO "done!\n"
	else
		$ECHO "Could not find examples directory $EXAMPLES_DIR\n"
	fi
fi

$ECHO "\n"
$ECHO "$DB server location:     $SERVER\n"
if test "$SERVER" != "localhost"
then
	$ECHO "$DB server port:         $PORT\n"
fi
$ECHO "$DB server version:      $VERSION\n"
$ECHO "$DB admin user:          $ADMIN_USER\n"
#$ECHO "$DB admin user password: $ADMIN_PASS\n"
if test $GRANTS -eq 1
then
	$ECHO "$DB webfwlog user:       $USER\n"
	$ECHO "$DB user password:       $PASS\n"
fi
$ECHO "Database:                       $DATABASE\n"
if test $HAVE_SCHEMAS -eq 1
then
	$ECHO "Webfwlog schema:                $WFWL_SCHEMA\n"
fi
if test $USE_ULOG -eq 1
then
	if test $HAVE_SCHEMAS -eq 1
	then
		if test $HAVE_ULOG_OLD_TABLE -eq 1 -a $HAVE_ULOG_TABLE -eq 0
		then
			$ECHO "$DB Ulogd user:          $ULOGD_USER\n"
		fi
		$ECHO "Ulog schema:                    $ULOG_SCHEMA\n"
	fi
	$ECHO "Ulog table:                     $ULOG_TABLE\n"
fi

OUTPUT="V"
until test "$OUTPUT" = "q" -o "$OUTPUT" = "Q"
do
	$ECHO "
===========================================================================
The setup script for $DB is now complete.  You can do the following:

- View the script
- Save the script to a file
- Run the script
- Quit

You will be returned to this menu when done
\n"
	$ECHO "What do you want to do? [V/s/r/q]: "
	OUTPUT="`get_param "$OUTPUT"`"

	if test "$OUTPUT" = "v" -o "$OUTPUT" = "V"
	then
		$ECHO "\n" 
		$ECHO "$SCRIPT\n"
		$ECHO "Press CR to continue ..."
		read N
	fi
	
	if test "$OUTPUT" = "s" -o "$OUTPUT" = "S"
	then
		$ECHO "Enter filename (<cr>=$OUTFILE): "
		OUTFILE="`get_param "$OUTFILE"`"
		if test -d `dirname "$OUTFILE"` -a -w `dirname "$OUTFILE"` && touch "$OUTFILE"
		then
			$ECHO "$SCRIPT" > "$OUTFILE"
			$ECHO "\nWrote $DB script $OUTFILE\n"
			$ECHO "Press CR to continue ..."
			read N
		else
			$ECHO "\nCannot write to $OUTFILE\n"
			$ECHO "Press CR to continue ..."
			read N
		fi
	fi
	
	if test "$OUTPUT" = "r" -o "$OUTPUT" = "R"
	then
		if ($ECHO "$SCRIPT\n" | $PROG template1)
		then
			$ECHO "\n$DB script complete successfully!\n"
			$ECHO "Press CR to continue ..."
			read N
		else
			$ECHO "\n$DB script failed!\n"
			$ECHO "Press CR to continue ..."
			read N
		fi
	fi
done

exit 0

# vim:set ts=4:
