aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorNatanael Copa <ncopa@alpinelinux.org>2012-08-21 16:25:38 +0200
committerTed Trask <ttrask01@yahoo.com>2012-11-20 18:55:04 +0000
commite67bc8a2693d8b8ccf05ee3aa61eac2d154319ec (patch)
tree058efc71275807d62c66b2d71bfc183cbf9d33bb
parent71147bc9839a1fbaa518c650b7937a42df8acc7a (diff)
downloadaports-e67bc8a2693d8b8ccf05ee3aa61eac2d154319ec.tar.bz2
aports-e67bc8a2693d8b8ccf05ee3aa61eac2d154319ec.tar.xz
main/acf-weblog: let user manually update database schema
(cherry picked from commit 5fd2bd4ddab35efe5523a225b81160831b866f9b) (cherry picked from commit 7effd8ec60408657eb0bc33786e10a85fff0863f)
-rw-r--r--main/acf-weblog/APKBUILD12
-rw-r--r--main/acf-weblog/acf-weblog-update-schema168
-rwxr-xr-xmain/acf-weblog/acf-weblog.post-upgrade63
3 files changed, 183 insertions, 60 deletions
diff --git a/main/acf-weblog/APKBUILD b/main/acf-weblog/APKBUILD
index 52a3fbc1dd..850fa5c95d 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
+b88ba3059c0a20c5a70cb962428e166f 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 0000000000..38ebaf78b9
--- /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 18ed542fcf..78822061e4 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