main/acf-weblog: let user manually update database schema

3 files changed, 183 insertions, 60 deletions

diff --git a/main/acf-weblog/APKBUILD b/main/acf-weblog/APKBUILD
index 52a3fbc1dd8..8c1a9f23518 100644
--- a/main/acf-weblog/APKBUILD
+++ b/main/acf-weblog/APKBUILD
@@ -2,7 +2,7 @@
 # Maintainer: Ted Trask <ttrask01@yahoo.com>
 pkgname=acf-weblog
 pkgver=0.6.2
-pkgrel=0
+pkgrel=1
 pkgdesc="ACF for web proxy (squid and dansguardian) logfiles"
 url="http://git.alpinelinux.org/cgit/acf-weblog"
 arch="noarch"
@@ -11,11 +11,15 @@ depends="acf-core lua lua-sql-postgres wget postgresql-client"
 makedepends=""
 install="$pkgname.post-upgrade"
 subpackages=""
-source="http://git.alpinelinux.org/cgit/$pkgname/snapshot/$pkgname-$pkgver.tar.bz2"
+source="http://git.alpinelinux.org/cgit/$pkgname/snapshot/$pkgname-$pkgver.tar.bz2
+	acf-weblog-update-schema"
 
 package() {
 	cd "$srcdir"/$pkgname-$pkgver
-	make DESTDIR="$pkgdir" install
+	make DESTDIR="$pkgdir" install || return 1
+	install -Dm755 "$srcdir"/acf-weblog-update-schema \
+		"$pkgdir"/usr/sbin/acf-weblog-update-schema
 }
 
-md5sums="ceb9d0f32a4ce457ced0f442a52a1797  acf-weblog-0.6.2.tar.bz2"
+md5sums="ceb9d0f32a4ce457ced0f442a52a1797  acf-weblog-0.6.2.tar.bz2
+f9835fc6d17241c71e4dff1ffa11d72c  acf-weblog-update-schema"
diff --git a/main/acf-weblog/acf-weblog-update-schema b/main/acf-weblog/acf-weblog-update-schema
new file mode 100644
index 00000000000..38ebaf78b9a
--- /dev/null
+++ b/main/acf-weblog/acf-weblog-update-schema
@@ -0,0 +1,168 @@
+#!/bin/sh
+
+DB=webproxylog
+DBUSER=weblogowner
+
+psql_args="$@"
+
+runsql() {
+	local cmd="$1"
+	shift
+	psql -U $DBUSER --dbname=$DB --no-align --tuples-only -c "$cmd" $psql_args
+}
+
+# check if table exists or not
+# based on http://www.peterbe.com/plog/pg_class/
+has_table() {
+	local tbl="$1"
+	local res=$(runsql "
+		SELECT count(relname) FROM pg_class
+		WHERE relname = '$tbl'")
+	test $res -ge 1
+}
+
+# test if column exist in given table
+# based on http://www.tequilafish.com/2007/02/23/postgresql-determine-if-a-column-exists-or-not/
+has_column() {
+	local tbl=$1 col=$2
+	local res=$(runsql "
+	SELECT count(attname) FROM pg_attribute
+	WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = '$tbl')
+	AND attname = '$col';")
+	test $res -ge 1
+}
+
+# add column if its missing
+check_column() {
+	local tbl="$1" col="$2" datatype="$3"
+	echo -n "Checking column '$col' in table '$tbl' ... "
+	if has_column "$tbl" "$col"; then
+		echo "Ok"
+	else
+		runsql "ALTER TABLE $tbl ADD COLUMN $col $datatype"
+	fi
+}
+
+# get data type for column
+# based on http://stackoverflow.com/questions/2146705/select-datatype-of-the-field-in-postgres
+get_data_type() {
+	local tbl="$1" col="$2"
+	runsql "SELECT data_type FROM information_schema.columns
+		WHERE table_name = '$tbl' AND column_name = '$col'"
+}
+
+# get data precision for column
+get_data_precision() {
+	local tbl="$1" col="$2" datatype="$3"
+	runsql "SELECT ${datatype}_precision FROM information_schema.columns
+		WHERE table_name = '$tbl' AND column_name = '$col'"
+}
+
+
+check_column_timestamp() {
+	local tbl="$1" col="$2"
+	local precision=3
+	echo -n "Checking column '$col' in table '$tbl' ... "
+	if [ "$(get_data_type $tbl $col)" = "timestamp without time zone" ] \
+		&& [ "$(get_data_precision $tbl $col datetime)" = "$precision" ]; then
+		echo "Ok"
+	else
+		runsql "ALTER TABLE $tbl ALTER $col
+			TYPE timestamp($precision) without time zone"
+	fi
+}
+
+check_column_type() {
+	local tbl="$1" col="$2" datatype="$3"
+	echo -n "Checking column '$col' in table '$tbl' ... "
+	if [ "$(get_data_type $tbl $col)" = "$datatype" ]; then
+		echo "Ok"
+	else
+		runsql "ALTER TABLE $tbl ALTER $col TYPE $datatype"
+	fi
+}
+
+drop_table() {
+	local tbl="$1"
+	echo -n  "Dropping table '$tbl' ... "
+	runsql "DROP TABLE $tbl"
+}
+
+# for older than 0.4.0
+check_column weblog shortreason text
+check_column pubweblog shortreason text
+check_column pubblocklog shortreason text
+check_column blocklog shortreason text
+
+# for older than 0.4.5
+check_column_timestamp dbhistlog logdatetime
+
+# for older than 0.5.4
+check_column_type weblog bytes bigint
+check_column_type pubweblog bytes bigint
+check_column_type blocklog bytes bigint
+check_column_type pubblocklog bytes bigint
+
+# for older than 0.6.0
+check_column pubweblog badyesno int
+check_column pubweblog deniedyesno int
+check_column pubweblog bypassyesno int
+check_column pubweblog wordloc text
+check_column pubweblog goodwordloc text
+check_column pubweblog selected boolean
+check_column pubweblog id "SERIAL PRIMARY KEY"
+
+if has_table pubblocklog; then
+	echo -n "Importing pubblocklog ... "
+	runsql "INSERT INTO pubweblog SELECT *,'0','1','0','','','false'
+		FROM pubblocklog"
+	drop_table "pubblocklog"
+fi
+
+if has_table blocklog; then
+	echo -n "Importing blocklog ... "
+	runsql "INSERT INTO pubweblog SELECT *,'0','1','0','','','false'
+		FROM blocklog"
+	drop_table blocklog
+fi
+
+# we dont import this
+if has_table watchlist; then
+	drop_table watchlist
+fi
+
+if ! has_column weblog badyesno; then
+	echo -n "Importing weblog ..."
+	runsql "INSERT INTO pubweblog SELECT *,'0','0','0','','','false'
+		FROM weblog"
+fi
+check_column weblog badyesno int
+check_column weblog deniedyesno int
+check_column weblog bypassyesno int
+check_column weblog wordloc text
+check_column weblog goodwordloc text
+
+if ! has_table pubweblog_history; then
+	echo -n "Creating table 'pubweblog_history' ... "
+	runsql "
+	CREATE TABLE pubweblog_history(
+		sourcename character varying(40),
+		clientip inet NOT NULL,
+		clientuserid character varying(64) NOT NULL,
+		logdatetime timestamp(3) without time zone NOT NULL,
+		uri text NOT NULL,
+		bytes bigint NOT NULL,
+		reason text,
+		score integer,
+		shortreason text,
+		badyesno int,
+		deniedyesno int,
+		bypassyesno int,
+		wordloc text,
+		goodwordloc text,
+		selected boolean,
+		id int)"
+	echo -n "Setting permissions on pubweblog_history ... "
+	runsql "GRANT SELECT ON pubweblog_history TO webloguser"
+fi
+
diff --git a/main/acf-weblog/acf-weblog.post-upgrade b/main/acf-weblog/acf-weblog.post-upgrade
index 18ed542fcff..78822061e43 100755
--- a/main/acf-weblog/acf-weblog.post-upgrade
+++ b/main/acf-weblog/acf-weblog.post-upgrade
@@ -3,63 +3,14 @@
 new=$1
 old=$2
 
-# if current is older than 0.4.0 we update.
-if [ "$(apk version -t $old 0.4.0)" = "<" ]; then
-
-psql -U postgres -c "ALTER TABLE weblog ADD COLUMN shortreason text" \
-	webproxylog
-psql -U postgres -c "ALTER TABLE pubweblog ADD COLUMN shortreason text" \
-	webproxylog
-psql -U postgres -c "ALTER TABLE pubblocklog ADD COLUMN shortreason text" \
-	webproxylog  
-psql -U postgres -c "ALTER TABLE blocklog ADD COLUMN shortreason text" \
-	webproxylog
-
-fi
-
-# if current is older than 0.4.5 we update.
-if [ "$(apk version -t $old 0.4.5)" = "<" ]; then
-
-psql -U postgres -c "ALTER TABLE dbhistlog ALTER logdatetime TYPE timestamp(3)"\
-	webproxylog
-
-fi
-
-
-# if current is older than 0.5.4 we update.
-if [ "$(apk version -t $old 0.5.4)" = "<" ]; then
-
-psql -U postgres -c "ALTER TABLE weblog ALTER bytes TYPE bigint"\
-	webproxylog
-psql -U postgres -c "ALTER TABLE pubweblog ALTER bytes TYPE bigint"\
-	webproxylog
-psql -U postgres -c "ALTER TABLE blocklog ALTER bytes TYPE bigint"\
-	webproxylog
-psql -U postgres -c "ALTER TABLE pubblocklog ALTER bytes TYPE bigint"\
-	webproxylog
-
-fi
-
-# if current is older than 0.6.0 we update.
 if [ "$(apk version -t $old 0.6.0)" = "<" ]; then
-
-mkdir /etc/weblog
-mv /etc/weblog.conf /etc/weblog/
-echo "Executing db updates"
-psql -U postgres -c "ALTER TABLE pubweblog ADD COLUMN badyesno int, ADD COLUMN deniedyesno int, ADD COLUMN bypassyesno int, ADD COLUMN wordloc text, ADD COLUMN goodwordloc text, ADD COLUMN selected boolean, ADD COLUMN id int UNIQUE" -d webproxylog
-psql -U weblogowner -c "CREATE SEQUENCE pubweblog_id_seq" -d webproxylog
-psql -U postgres -c "ALTER TABLE pubweblog ALTER COLUMN id SET DEFAULT NEXTVAL('pubweblog_id_seq')" -d webproxylog
-psql -U postgres -c "UPDATE pubweblog SET id = NEXTVAL('pubweblog_id_seq')" -d webproxylog
-psql -U postgres -c "INSERT INTO pubweblog SELECT *,'0','1','0','','','false' FROM pubblocklog" -d webproxylog
-psql -U postgres -c "INSERT INTO pubweblog SELECT *,'0','0','0','','','false' FROM weblog" -d webproxylog
-psql -U postgres -c "INSERT INTO pubweblog SELECT *,'0','1','0','','','false' FROM blocklog" -d webproxylog
-psql -U postgres -c "ALTER TABLE weblog ADD COLUMN badyesno int, ADD COLUMN deniedyesno int, ADD COLUMN bypassyesno int, ADD COLUMN wordloc text, ADD COLUMN goodwordloc text" -d webproxylog
-psql -U weblogowner -c "CREATE TABLE pubweblog_history(sourcename character varying(40), clientip inet NOT NULL, clientuserid character varying(64) NOT NULL, logdatetime timestamp(3) without time zone NOT NULL, uri text NOT NULL, bytes bigint NOT NULL, reason text, score integer, shortreason text, badyesno int, deniedyesno int, bypassyesno int, wordloc text, goodwordloc text, selected boolean, id int)" -d webproxylog
-psql -U postgres -c "GRANT SELECT ON pubweblog_history TO webloguser" -d webproxylog
-psql -U postgres -c "DROP TABLE pubblocklog" -d	webproxylog
-psql -U postgres -c "DROP TABLE watchlist" -d webproxylog
-psql -U postgres -c "DROP TABLE blocklog" -d webproxylog
-
+	mkdir /etc/weblog
+	mv /etc/weblog.conf /etc/weblog/
+	cat <<EOF
+*
+* NOTE: You need to manually run /usr/sbin/acf-weblog-update-schema
+*
+EOF
 fi
 
 exit 0