#!/bin/sh
# $Id: setup 579 2014-05-17 21:23:44Z bhockney $
# Webfwlog (C) 2003-2014 by Bob Hockney <zeus@ix.netcom.com>
#
# MySQL setup script for webfwlog
#

DB="MySQL"
PROG="mysql"

SERVER="localhost"
PORT="3306"
ADMIN_USER="root"
ADMIN_PASS=""
USER="webfwlog@localhost"
PASS="password"
WFWL_DB="webfwlog"
WFWL_ADDR_TYPE=""
ULOG_DB="ulogd"
ULOG_TABLE="ulog"
ULOG_TYPE=""
ULOG_VIEW=""
ULOG_ADDR_TYPE="int"
ULOG_ADDR=""
ULOG_TABLE_SPLIT=0
ULOG_ADD_LOCAL_HOSTNAME=0
ULOG_ADD_LOCAL_TIME=0
ULOGD_USER="ulogd@localhost"
USE_SNORT=0
SNORT_DB="snort"
SNORT_DB_VIEW="webfwlog"
SNORT_TABLE="event"
SNORT_VIEW="webfwlog_snort"

HAVE_WFWL_DB=0
HAVE_WFWL_REPORTS=0
HAVE_WFWL_HOSTNAMES=0
HAVE_WFWL_SERVICES=0
HAVE_ULOG_DB=0
HAVE_ULOG_TABLE=0
HAVE_ULOG2=0
HAVE_ULOG_LOCAL_HOSTNAME=0
HAVE_ULOG_LOCAL_TIME=0
HAVE_ULOG_OOB_TIME_LOCAL=0
HAVE_SNORT_VIEW_DB=0
HAVE_SNORT_VIEW=0
HAVE_SNORT_TABLE=0
COPY_WFWL_TABLES=0
STORED_PROCEDURES=0
GRANTS=0
ADD_EXAMPLES=0
EXAMPLES_DIR="../examples"
OUTFILE="mysql_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

GREP="grep"
AWK="awk"
SED="sed"
CUT="cut"
CAT="cat"
WC="wc"
LS="ls"

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
}

test_indexes () {
	$ECHO "$1\n" | while read N
	do
		N=`$ECHO "$N" | sed -e 's/^.*KEY \`\(.*\)\` (.*$/\1/'`
		if test -z "$N"; then continue; fi;

		if test "$2" = "$N"
		then
			$ECHO "0"
			return
		fi
	done

	return
}

# Test for existence of index name on table, and returns command to add it
# if it doesn't exist
#
# Call with table_name index_name [index_name ...]
add_indexes () {
	DEF=`$PROG -e "SHOW CREATE TABLE $1" 2> /dev/null` > /dev/null 2>&1
	T="$1"
	shift
	for x in $@
	do
		if test -z "`test_indexes "$DEF" "$x"`"
		then
			case "$x" in
				ip_[sd]addr)
					$ECHO "ALTER TABLE $T ADD INDEX \`$x\` (${x}$ULOG_ADDR);\n"
				;;
				*)
					$ECHO "ALTER TABLE $T ADD INDEX \`$x\` ($x);\n"
				;;
			esac
		fi
	done

	return;
}

# Test for existence of index name on table, and returns command to drop it
# if it does exist
#
# Call with table_name index_name [index_name ...]
drop_indexes () {
	DEF=`$PROG -e "SHOW CREATE TABLE $1" 2> /dev/null` > /dev/null 2>&1
	T="$1"
	shift
	for x in $@
	do
		if test -n "`test_indexes "$DEF" "$x"`"
		then
			$ECHO "DROP INDEX \`$x\` ON $T;\n"
		fi
	done

	return;
}

$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 'root' (separate from the system root user).

Also, if you want to use webfwlog with database logs you need to set up your
logging program to use your $DB server before setting up webfwlog.  See the
documentation for your logging program for details on setting up it up for
$DB.  Using webfwlog with ulog version 2 or later, or snort database logs,
requires $DB >=5.0.

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
	PROG="$PROG -h $SERVER -P $PORT"
fi
PROG="$PROG -u $ADMIN_USER"
if test -n "$ADMIN_PASS"
then
	PROG="$PROG -p$ADMIN_PASS"
fi

if VERSION=`$PROG --batch -N -e "SELECT version() AS version"`
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

$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 3 \
	|| test $MAJOR -eq 3 -a $MINOR -lt 23 \
	|| test $MAJOR -eq 3 -a $MINOR -eq 23 -a $EXTRA -lt 52 \
	|| test $MAJOR -eq 4 -a $MINOR -eq 0 -a $EXTRA -lt 12 \
	|| test $MAJOR -eq 4 -a $MINOR -eq 1 -a $EXTRA -lt 7 \
	|| test $MAJOR -eq 5 -a $MINOR -eq 0 -a $EXTRA -lt 15 \
	|| test $MAJOR -eq 5 -a $MINOR -eq 1 -a $EXTRA -lt 23 \
	|| test $MAJOR -eq 5 -a $MINOR -eq 5 -a $EXTRA -lt 8 \
	|| test $MAJOR -eq 5 -a $MINOR -eq 6 -a $EXTRA -lt 10
then
	$ECHO "\n"
	$ECHO "$DB version 3.23.xx >= 3.23.52 or version 4.0.x >= 4.0.12\n"
	$ECHO "        or version 4.1.x >= 4.1.7 or version 5.0 >= 5.0.15\n"
	$ECHO "        or version 5.1 >= 5.1.23 or version 5.5 >= 5.5.8\n"
	$ECHO "        or version 5.6 >= 5.6.10 or version > 5.6\n"
	$ECHO "        is required for use with webfwlog\n"
	$ECHO "Exiting...\n\n"
	exit 1
fi

# query re: data layout desired (database and table names)
$ECHO "
Enter the database you want to use for the webfwlog tables.  It is recomended
to put the webfwlog tables in a separate database such as 'webfwlog'.  If the
database does not exist it will be created.  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"
$ECHO "Enter the database to use for the webfwlog tables (<cr>=$WFWL_DB): "
WFWL_DB="`get_param "$WFWL_DB"`"

if test -n "`$PROG --batch -e "SELECT 1;" $WFWL_DB 2> /dev/null`"
then 
	$ECHO "\n"
	$ECHO "Using existing database $WFWL_DB for webfwlog tables\n"
	HAVE_WFWL_DB=1
else
	$ECHO "\n"
	$ECHO "Creating new database $WFWL_DB for webfwlog tables\n"
	HAVE_WFWL_DB=0
fi

WFWL_REPORTS="$WFWL_DB.reports"
WFWL_HOSTNAMES="$WFWL_DB.hostnames"
WFWL_SERVICES="$WFWL_DB.services"

if ($PROG -e "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 -e "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 -e "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

if test $MAJOR -ge 5; then WFWL_ADDR_TYPE="_bin"; fi

if test $HAVE_WFWL_DB -eq 0
then
	SCRIPT="${SCRIPT}--\n-- Create new database $WFWL_DB\n--\nCREATE DATABASE $WFWL_DB;\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_DB.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_DB.hostnames/$WFWL_HOSTNAMES/'\\\" }" "scripts/wfwl_hostnames_create$WFWL_ADDR_TYPE"`\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_DB.services/$WFWL_SERVICES/'\\\" }" scripts/wfwl_services_create`\n"
fi

R="$WFWL_REPORTS"

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

D="$WFWL_DB"

if ($PROG -e "SELECT count(*) from $D.protocols;") > /dev/null 2>&1
then
	SCRIPT="${SCRIPT}--\n-- Drop unused table protocols\n--\nDROP TABLE $D.protocols;\n"
fi

if test $MAJOR -ge 5
then

# IPv6
	$ECHO "
IPv6 support with webfwlog in $DB is achieved using columns with a column
type of varbinary(16).  Ulogd version 2.0 and higher also use this column type
for mysql.  $DB does not include functions that can convert human-readable
addresses to binary strings and back again.  This webfwlog distribution
includes the needed functions in two forms:

    - user-defined functions(UDF) compiled in a shared library
    - stored procedures

See the INSTALL file for a discussion of the advantages and disadvantages of
each.  If you want to use the shared library you must compile and install it
before running this setup program.
\n"

$ECHO "Do you want to attempt to load the functions from a shared library? [Y/n]: "
if test `get_yes_or_no` -eq 1;
then
	$ECHO "\nAttempting to load functions from shared library.\n\n"

	for f in wfwl_inet_ntop wfwl_inet_pton wfwl_inet_contains wfwl_inet_ntop_ipv4;
	do
		FUNC_DB=`$PROG --batch -N -e "SELECT routine_schema from INFORMATION_SCHEMA.routines WHERE routine_name='$f' LIMIT 1" 2>/dev/null`
		if test -n "$FUNC_DB";
		then $PROG -e "DROP FUNCTION IF EXISTS $FUNC_DB.$f;" > /dev/null 2>&1
		else $PROG -e "DROP FUNCTION $f;" > /dev/null 2>&1
		fi
	done

	for f in wfwl_inet_ntop wfwl_inet_pton wfwl_inet_contains;
	do
		$ECHO "Loading function $f ... "
		if ($PROG -e "CREATE FUNCTION $f RETURNS string SONAME 'libwebfwlog_udf.so';" mysql) > /dev/null 2>&1;
		then
			$ECHO "Done!\n"
		else
			$ECHO "FAILIED!\n
Could not load function $f from shared library.  The shared
library must be compiled and installed before running setup, see the
instructions in the INSTALL file.  Rerun setup when installed, or answer no to
the load functions from a shared library question.  Exiting ...
"
			exit
		fi
	done
else
	STORED_PROCEDURES=1
	$ECHO "\nUsing stored procedures\n"
	SCRIPT="$SCRIPT--\nUSE $WFWL_DB;\n--\n`$AWK "{ if (NR != 1 && ! /vim:/) print }" scripts/wfwl_functions`\n"
fi

if test $HAVE_WFWL_HOSTNAMES -eq 1 -a -n "`$PROG --batch -N -e \
	"SHOW COLUMNS FROM $WFWL_DB.hostnames;" 2>/dev/null|$GREP '^ip_addr	*int('`"
then
	SCRIPT="${SCRIPT}\n--\n-- Change ip_addr column type to varbinary\n--\n"
	SCRIPT="${SCRIPT}BEGIN;\n"
	SCRIPT="${SCRIPT}  ALTER TABLE $WFWL_HOSTNAMES ADD COLUMN (ip_addr_bin varbinary(16) DEFAULT NULL);\n"
	SCRIPT="${SCRIPT}  UPDATE $WFWL_HOSTNAMES SET ip_addr_bin=wfwl_inet_pton(inet_ntoa(ip_addr));\n"
	SCRIPT="${SCRIPT}  ALTER TABLE $WFWL_HOSTNAMES DROP COLUMN ip_addr;\n"
	SCRIPT="${SCRIPT}  ALTER TABLE $WFWL_HOSTNAMES CHANGE COLUMN ip_addr_bin ip_addr varbinary(16) NOT NULL DEFAULT '' UNIQUE;\n"
	SCRIPT="${SCRIPT}  ALTER TABLE $WFWL_HOSTNAMES ADD UNIQUE INDEX ip_addr (ip_addr);\n"
	SCRIPT="${SCRIPT}COMMIT;\n"
fi

fi # if test $MAJOR -ge 5

SCRIPT_ADD=""
if test $HAVE_WFWL_HOSTNAMES -eq 1
then
	SCRIPT_ADD="`add_indexes "$WFWL_HOSTNAMES" refresh`"
	if test -n "$SCRIPT_ADD"
	then
		SCRIPT_ADD="${SCRIPT_ADD}\n"
	fi
fi
if test $HAVE_WFWL_SERVICES -eq 1
then
	SCRIPT_ADD="${SCRIPT_ADD}`add_indexes "$WFWL_SERVICES" refresh`"
fi

if test -n "$SCRIPT_ADD"
then
	SCRIPT="${SCRIPT}--\n-- Add indexes to webfwlog table(s)\n--\n$SCRIPT_ADD\n"
fi

$ECHO "\n"

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

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
then
	$ECHO "
Enter the database you use for your ulog table. It is recomended to have the
ulog table in a separate database such as 'ulogd'.  You will be prompted for
a database 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.
\n"
fi

if test $USE_ULOG -eq 1
then
	until test $HAVE_ULOG_TABLE -eq 1
	do
		$ECHO "Enter database containing the ulog table (<cr>=$ULOG_DB): "
		ULOG_DB="`get_param "$ULOG_DB"`"

		if test -n "`$PROG --batch -e "SELECT 1;" $ULOG_DB 2> /dev/null`"
		then 
			$ECHO "\n"
			$ECHO "Found existing database $ULOG_DB for ulog\n"
			HAVE_ULOG_DB=1
		else
			$ECHO "\n"
			$ECHO "Database $ULOG_DB does not exist!\n\n"
			continue
		fi

		$ECHO "\n"

		if test $HAVE_ULOG_DB -eq 1
		then
			$ECHO "Enter name of ulog table (<cr>=$ULOG_TABLE): "
			ULOG_TABLE="`get_param "$ULOG_TABLE"`"

			ULOG="$ULOG_DB.$ULOG_TABLE"

			T="`$PROG --batch -N -e "SHOW COLUMNS FROM $ULOG_TABLE LIKE 'ip\_saddr%'" $ULOG_DB`"
			if test -n "$T" 
			then
	 			ULOG_TYPE="`$PROG --batch -N -e "SELECT table_type from INFORMATION_SCHEMA.tables WHERE table_schema='$ULOG_DB' AND table_name='$ULOG_TABLE';" 2>/dev/null`"
				if test $MAJOR -lt 5 -a -n "`$ECHO "$T" | sed -e 's/^[^	]*	\([^	]*\).*$/\1/' | $GREP binary`"
				then
					$ECHO "\nTable $ULOG_TABLE has ip_saddr of binary type, which is not supported with $DB < 5.0\n\n"
				elif test "x$ULOG_TYPE" = "xVIEW"
				then
					T="`$PROG --batch -N -e "SHOW CREATE TABLE $ULOG;"|sed -e 's/^.* from ((*\([^ ]*\).*/\1/'`"
					if test -n "`$PROG --batch -N -e "SHOW COLUMNS FROM $T LIKE 'ip\__addr%'"`"
					then
						ULOG_VIEW="$ULOG_TABLE"
						ULOG="$T"
						ULOG_TABLE="`$ECHO $T|sed -e 's/^[^\.]*\.\`*\([^\`]*\)\`*$/\1/'`"
						$ECHO "\nRelation ${ULOG_DB}.$ULOG_VIEW is a view\n"
						$ECHO "Underyling table is $ULOG\n"
						HAVE_ULOG_TABLE=1
					fi
				else
					$ECHO "\nUsing ulog table $ULOG\n"
					HAVE_ULOG_TABLE=1
				fi
			else
				$ECHO "\n"
				$ECHO "Table $ULOG does not exist or is not a ulog table!\n\n"
			fi
		fi
	done

	$ECHO "\n"
	if test -n "$ULOG_VIEW"
	then
		$ECHO "Checking underlying table $ULOG ... "
	else
		$ECHO "Checking table $ULOG ... "
	fi

	# test for version 2 ulog table
	ULOG_ADDR_TYPE=`$PROG --batch -N -e "SELECT data_type FROM INFORMATION_SCHEMA.columns \
			WHERE table_schema='$ULOG_DB' AND table_name='$ULOG_TABLE' AND column_name LIKE 'ip\_saddr%'" 2>/dev/null` 2> /dev/null
	if test "x$ULOG_ADDR_TYPE" = "xbinary"
	then
		HAVE_ULOG2=1
		ULOG_ADDR="_bin"
		if ! ($PROG --batch -N -e "SELECT udp_dport FROM $ULOG LIMIT 1") >/dev/null 2>&1
		then
			ULOG_TABLE_SPLIT=1
			ULOG_ADDR=""
		fi
	fi

	# test ulog table for local _hostname and local_time and update if needed.
	if ($PROG -e "SELECT local_hostname FROM $ULOG LIMIT 1;") > /dev/null 2>&1
	then
		HAVE_ULOG_LOCAL_HOSTNAME=1
	fi
	if ($PROG -e "SELECT local_time FROM $ULOG LIMIT 1;") > /dev/null 2>&1
	then
		HAVE_ULOG_LOCAL_TIME=1
	fi
	if test `$PROG --batch -N -e "SELECT count(*) FROM $ULOG WHERE oob_time_sec IS NULL OR oob_time_sec=0" $ULOG_DB` -eq 0
	then
		HAVE_ULOG_OOB_TIME_LOCAL=1
	fi

	# reset indexes and column defaults on existing ulog data
	SCRIPT_ADD="`drop_indexes $ULOG saddr daddr udp_sservices udp_dservices tcp_sservices tcp_dservices \
					saddr_$ULOG_TABLE daddr_$ULOG_TABLE udp_s_$ULOG_TABLE udp_d_$ULOG_TABLE tcp_s_$ULOG_TABLE tcp_d_$ULOG_TABLE`" 
	if test -n "$SCRIPT_ADD"
	then
		SCRIPT="${SCRIPT}--\n-- Drop old index names on $ULOG_TABLE\n--\n$SCRIPT_ADD\n"
	fi

	SCRIPT_ADD=""
	S="oob_time_sec ip_saddr ip_daddr ip_protocol"
	if test $HAVE_ULOG2 -eq 1
	then
		S="$S oob_family"
	fi
	SA="`add_indexes "$ULOG" $S`"
	if test -n "$SA"; then SCRIPT_ADD="${SA}\n"; fi

	if test $ULOG_TABLE_SPLIT -eq 1 
	then
		SA="`add_indexes "$ULOG_DB.tcp" tcp_sport tcp_dport`"
		if test -n "$SA"; then SCRIPT_ADD="${SCRIPT_ADD}$SA\n"; fi
		SA="`add_indexes "$ULOG_DB.udp" udp_sport udp_dport`"
		if test -n "$SA"; then SCRIPT_ADD="${SCRIPT_ADD}$SA\n"; fi
	else
		SA="`add_indexes "$ULOG" tcp_dport tcp_sport udp_dport udp_sport`"
		if test -n "$SA"; then SCRIPT_ADD="${SCRIPT_ADD}$SA\n"; fi
	fi

	if test -n "$SCRIPT_ADD"
	then
		SCRIPT="${SCRIPT}--\n-- Add indexes to ulog table\n--\n$SCRIPT_ADD"
	fi

	# Check for additional tables to analyze
	if test -n "$ULOG_VIEW"
	then
		A=""
		T="`$PROG --batch -N -e "SHOW TABLES IN $ULOG_DB"`"
		for x in tcp udp icmp icmpv6 mac hwhdr sctp
		do
			if test -n "`$ECHO "$T" | $GREP ^${x}$`"
			then
				A="${A}, $ULOG_DB.$x"
			fi
		done
	fi

	$ECHO "Done!\n"

	if test $HAVE_ULOG_LOCAL_HOSTNAME -eq 0
	then
		$ECHO "
By default, ulog does not record the name of the logging host, but this can
be logged with the LOCAL plugin of ulogd into the local_hostname column, which
will be added if you answer yes to the following question.
\n"
	$ECHO "Do you want to add a local_hostname column? [Y/n]: "
	ULOG_ADD_LOCAL_HOSTNAME="`get_yes_or_no`"
	fi

	if test $HAVE_ULOG_LOCAL_TIME -eq 0 -a $HAVE_ULOG_OOB_TIME_LOCAL -eq 0
	then
		$ECHO "
With older linux kernels ulog did not record a time stamp for locally
generated packets, and you appear to have logged packets with a time stamp
of 0.  The workaround for this is to use the LOCAL plugin of ulog which will
record a time stamp for all packets in the local_time column, which will be
added if you answer yes to the following question.  The use of the LOCAL
plugin of ulogd is highly recommended so that you will be able to select
packets by date/time.
\n"
	$ECHO "Do you want to add a local_time column? [Y/n]: "
	ULOG_ADD_LOCAL_TIME="`get_yes_or_no`"
	fi

	if test $ULOG_ADD_LOCAL_HOSTNAME -eq 1
	then
		SCRIPT="${SCRIPT}--\n-- Add local_hostname column to table $ULOG\n--\nALTER TABLE $ULOG ADD COLUMN local_hostname  varchar(32) DEFAULT NULL;\n"
		if test -n "$ULOG_VIEW"
		then
			ULOG_CREATE_VIEW="`$PROG --batch -N -e "SHOW CREATE TABLE $ULOG_DB.$ULOG_VIEW"|sed -e 's/^.*\(CREATE\)\( .*\)\( from (*.*)\).*$/\1 OR REPLACE\2,local_hostname\3;/'`"
			SCRIPT="${SCRIPT}$ULOG_CREATE_VIEW\n"
		fi
	fi
	if test $ULOG_ADD_LOCAL_TIME -eq 1
	then
		SCRIPT="${SCRIPT}--\n-- Add local_time column to table $ULOG\n--\nALTER TABLE $ULOG ADD COLUMN local_time int(10) UNSIGNED DEFAULT NULL;\n"
	fi

	SCRIPT="${SCRIPT}--\n-- Run ANALYZE TABLE on Ulog table(s)\n--\nANALYZE TABLE ${ULOG}${A};\n"

	$ECHO "\nDone with Ulog setup!\n"
fi

$ECHO "\n"

# snort
if test $MAJOR -ge 5
then
$ECHO "
If you want to use webfwlog with snort data answer yes to the next question.
You will then be prompted for the schema (if required) for your logs.

NOTE: Snort tables must already exist before it they can be set up for use
with webfwlog.  See the documentation of snort for more information on setting
up snort tables.

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

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

if test $USE_SNORT -eq 1
then
	$ECHO "
Enter the database you want to use for your snort view for the use of webfwlog.
It is recomended to put the snort view in the same database as the webfwlog tables.
The database will be created if it does not exist.
\n"

	$ECHO "Enter database where you want the snort view for webfwlog (<cr>=$SNORT_DB_VIEW): "
	SNORT_DB_VIEW="`get_param "$SNORT_DB_VIEW"`"

	if test -n "`$PROG --batch -e "SELECT 1;" $SNORT_DB_VIEW 2> /dev/null`"
	then 
		$ECHO "\n"
		$ECHO "Using existing database $SNORT_DB_VIEW for snort view\n"
		HAVE_SNORT_VIEW_DB=1
	else
		$ECHO "\n"
		$ECHO "Creatine new database $SNORT_DB_VIEW for snort view\n"
	fi
	SNORT_VIEW="$SNORT_DB_VIEW.$SNORT_VIEW"

	until test $HAVE_SNORT_TABLE -eq 1
	do
		$ECHO "\nEnter database containing your snort tables (<cr>=$SNORT_DB): "
		SNORT_DB="`get_param "$SNORT_DB"`"
		SNORT="$SNORT_DB.$SNORT_TABLE"

		if ($PROG -e "SELECT count(*) FROM $SNORT;") > /dev/null 2>&1
		then 
			$ECHO "\nUsing snort table $SNORT\n\n"
			HAVE_SNORT_TABLE=1
		else
			$ECHO "\n\aSnort table $SNORT NOT found.\nSnort tables must exist before setting up for use with webfwlog\n"
		fi
	done

	if ($PROG -e "SELECT count(*) FROM $SNORT_VIEW;") > /dev/null 2>&1
	then
		$ECHO "Replacing existing snort view $SNORT_VIEW\n"
		HAVE_SNORT_VIEW=1
	else
		$ECHO "Creating snort view $SNORT_VIEW\n"
	fi

	SCRIPT="${SCRIPT}--\n-- Add snort view for webfwlog\n--\nBEGIN;\n" 

	if test $HAVE_SNORT_VIEW_DB -eq 0 -a "$SNORT_DB_VIEW" != "$WFWL_DB" -a "$SNORT_DB_VIEW" != "$ULOG_DB"
	then 
		SCRIPT="${SCRIPT}CREATE DATABASE $SNORT_DB_VIEW;\n"
	fi

	if test $HAVE_SNORT_VIEW -eq 1
	then
		SCRIPT="${SCRIPT}DROP VIEW $SNORT_VIEW;\n" 
	fi

	SCRIPT="$SCRIPT`$AWK "{ if (NR != 1) print | \\\"sed 's/\\\$SNORT_DB/$SNORT_DB/g' | sed 's/\\\$SNORT_TABLE/$SNORT_TABLE/g' | sed 's/\\\$SNORT_VIEW/$SNORT_VIEW/g'\\\" }" scripts/snort_view`\n"
	SCRIPT="${SCRIPT}COMMIT;\n" 

	# Check for snort tables to analyze
	A="$SNORT"
	T="`$PROG --batch -N -e "SHOW TABLES IN $SNORT_DB"`"
	for x in sensor iphdr tcphdr udphdr icmphdr signature sig_class
	do
		if test -n "`$ECHO "$T" | $GREP ^${x}$`"
		then
			A="${A}, $SNORT_DB.$x"
		fi
	done

	SCRIPT="${SCRIPT}--\n-- Run ANALYZE TABLE on snort table(s)\n--\nANALYZE TABLE $A;\n"

fi # if test $USE_SNORT -eq 1

fi # if test $MAJOR -ge 5

# set/reset rights
if test $STORED_PROCEDURES -eq 1
then
	GRANTS=1
else
	$ECHO "
Answer yes to the following question set or reset 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/reset the $DB user rights? [Y/n]: "
	GRANTS="`get_yes_or_no`"
fi

if test $GRANTS -eq 1
then
	# get webfwlog user credentials
	$ECHO "
Enter username and password you want to user for webfwlog.  Note that
\`webfwlog\` is a different user than \`webfwlog\`@\`localhost\`, which is a different
user than \`webfwlog\`@\`127.0.0.1\` as far as $DB is concerned.
"
	$ECHO "\n"
	$ECHO "Enter $DB user for webfwlog (<cr>=$USER): "
	USER="`get_param "$USER"`"

	$ECHO "Enter password for $DB user for webfwlog (<cr>=$PASS): "
	stty -echo
	PASS="`get_param "$PASS"`"
	stty echo
	PASS="`$PROG --batch -N -e "SELECT password('$PASS')"`"

	SCRIPT="${SCRIPT}--\n-- Grant usage to $DB user\n--\n"
	SCRIPT="${SCRIPT}GRANT USAGE ON *.* TO $USER IDENTIFIED BY PASSWORD '$PASS';\n"

	SCRIPT="$SCRIPT`$AWK "{ if (NR != 1) print | \\\"sed 's/\\\$USER/$USER/' | sed 's/\\\$WFWL_DB/$WFWL_DB/'\\\" }" scripts/wfwl_grants`\n"

	if test $MAJOR -ge 4
	then
		SCRIPT="${SCRIPT}GRANT CREATE TEMPORARY TABLES ON ${WFWL_DB}.* TO $USER;\n"
	fi

	if test $STORED_PROCEDURES -eq 1
	then
		SCRIPT="$SCRIPT`$AWK "{ if (NR != 1) print | \\\"sed 's/\\\$USER/$USER/' | sed 's/\\\$WFWL_DB/$WFWL_DB/'\\\" }" scripts/wfwl_function_grants`\n"
	fi

	if test $USE_ULOG -eq 1
	then
		SCRIPT="${SCRIPT}--\n-- Add grants to MySQL user for ulog tables\n--\nGRANT SELECT ON ${ULOG_DB}.* TO $USER;\n"
	fi
	
	if test $USE_SNORT -eq 1
	then
		SCRIPT="${SCRIPT}--\n-- Add grants to MySQL user for snort view\n--\nGRANT SELECT ON $SNORT_VIEW TO $USER;\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\n" | $CUT -d= -f1`
		VALUE=`$ECHO "$LINE\n" | $CUT -d= -f2`
		LEN=`$ECHO "$VALUE"|$WC -c`
		LEN=`expr $LEN - 2`
		$ECHO "s:`$ECHO "$KEY"|$WC -c | sed -e 's/^ *//'`:\"$KEY\";s:$LEN:$VALUE;"
	done;
}

get_def () {
	$ECHO "`$GREP ^description $1|$CUT -d= -f2|sed 's/\"//g'`\n"
	$ECHO "a:`$AWK "{ print }" $1 |$GREP -v "^ *#"|$GREP -v "^$"|$WC -l|sed -e 's/^[[:space:]]*\([0-9]*\)[[:space:]]*$/\1/'`:{"
	$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"
		for x in `$LS $EXAMPLES_DIR`
		do
			if test -f $EXAMPLES_DIR/$x
			then
				DEF=`get_def $EXAMPLES_DIR/$x`
				SCRIPT="${SCRIPT}INSERT IGNORE INTO $WFWL_REPORTS (code, description, definition, last_saved, last_accessed)\nVALUES ('$x', '`$ECHO "$DEF"|head -n1`', '`$ECHO "$DEF"|$AWK "{ if (NR != 1) print }"`', unix_timestamp(), unix_timestamp());\n"
			fi
		done
		$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 hash:  $PASS\n"
fi
$ECHO "Webfwlog database:         $WFWL_DB\n"
if test $USE_ULOG -eq 1
then
	$ECHO "Ulog database:             $ULOG_DB\n"
	if test -n "$ULOG_VIEW"; then $ECHO "Ulog view                  $ULOG_VIEW\n"; fi
	$ECHO "Ulog table:                $ULOG_TABLE\n"
fi
if test $USE_SNORT -eq 1
then
	$ECHO "Snort table:               $SNORT\n"
	$ECHO "Snort view:                $SNORT_VIEW\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"
		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 -v )
		then
			$ECHO "\n$DB script completed 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:
