#!/bin/bash

export PGHOST=localhost

get_num_connections () {
    CONNECTIONSQL="SELECT GREATEST(COUNT(*)-1,0) FROM pg_stat_activity"
    /usr/lib/postgresql/9.1/bin//psql -d fred -U fred -At -p 5432 \
                    -c "$CONNECTIONSQL" 2>/dev/null;
}

get_db_version () {
    VERSIONSQL="SELECT val FROM enum_parameters WHERE id=1"
    /usr/lib/postgresql/9.1/bin//psql -d fred -U fred -At -p 5432 \
                    -c "$VERSIONSQL" 2>/dev/null;
}

test_db () {
    /usr/lib/postgresql/9.1/bin//psql -U fred -l -p 5432 fred | grep 'fred' > /dev/null
    return $?
}

test_user() {
    # check if user has administrator privileges
    # only this test works for both situations supported:
    # either system wide user 'postgres' or local postgres installation
    /usr/lib/postgresql/9.1/bin//psql -p 5432 postgres -c "" > /dev/null 2>&1
    return $?
}

f_usage () {
    echo "usage: fred-dbmanager [argument]
arguments:
    install - install FRED database
    uninstall - uninstall FRED database
    upgrade - upgrade FRED database with upgrade script
    status - print status of FRED database
    testuser - check if script can be run as current_user or postgres
    create_partitions - create partitions for fred-logd
    list_partitions - list partitions
    drop_partitions - drop all partitions
    help - print this help"
}

init_pghba () {
    test_user;
    if [ $? -ne 0 ]
    then
        echo "Current user doesn't have required administrator privileges "
        echo "or server is not running. Start server or run this script "
        echo "under 'postgres' user"
        exit 1
    fi
    if [ "$PGDATA" = "" ]
    then
        PGHBA=/etc/postgresql/9.1/main//pg_hba.conf
        PGDATA=/var/lib/postgresql/9.1/main/
    else
        PGHBA=$PGDATA/pg_hba.conf
    fi

    if [ ! -w $PGHBA ]
    then
        if [ "$1" = "" -o ! -f "$1" ]
        then
            echo "Cannot locate pg_hba.conf file or it's not accessible, run"
            echo "this script under adminstrator user (usually postgres)"
        exit 2
        else
            PGHBA=$1
        fi
    fi
}

f_install () {
    init_pghba

    prefix=/usr
    datarootdir=${prefix}/share
    STRUCTSQL=${datarootdir}/fred-db/structure.sql
    if [ ! -f $STRUCTSQL ]; then
        echo "SQL script file $STRUCTSQL does not exists. Install terminated."
        exit 1
    fi

    if grep "begin-fred-fred--" $PGHBA >/dev/null 2>&1
    then
        /bin/true
    else
        cp $PGHBA /tmp/pg_hba.conf.$$
        echo -n "" > $PGHBA
        # There is no evidence about user fred in authentication file so
        # it can be suspected that user fred will not be allowed to connect
        # This file will be modified to allow local connection for user
        # fred. If this is not intended modify database access options in
        # server fred server configuration files to suite you needs
        sed -i '1i# --begin-fred-fred-- \
# Next three lines were added by script '$0' \
# to allow local connection of user fred to database fred \
# beware: lines between begin and end marks \
# will be deleted by `fred-dbmanager uninstall` script \
local fred fred trust \
host fred fred 127.0.0.1/32 trust \
host fred fred ::1/128 trust \
# --end-fred-fred-- ' /tmp/pg_hba.conf.$$

        cat /tmp/pg_hba.conf.$$ >> $PGHBA
        rm /tmp/pg_hba.conf.$$

        # reload new file into postgres
        /usr/lib/postgresql/9.1/bin//pg_ctl reload -D$PGDATA >/dev/null 2>&1
        if [ $? -ne 0 ]
        then
            echo "Reload of updated configuration failed, try to reload manualy"
            exit 3
        fi
    fi

    if [ "$(get_db_version)" = "" ]; then
        /usr/lib/postgresql/9.1/bin//createuser fred -SDR -p 5432 > /dev/null 2>&1
        /usr/lib/postgresql/9.1/bin//createdb fred -O fred -E UTF8 \
                            -p 5432 > /dev/null 2>&1
        /usr/lib/postgresql/9.1/bin//createlang plpgsql fred \
                              -p 5432 > /dev/null 2>&1
        error_msg=$(( /usr/lib/postgresql/9.1/bin//psql fred -U fred -f $STRUCTSQL -p 5432 ) 2>&1 | grep "ERROR" )
	if [ "$error_msg" != "" ]; then
	    echo -e "There are errors in SQL script file $STRUCTSQL. Installation terminated.\nError message: $error_msg"
	    exit 1
	fi
    fi
}

f_uninstall () {
    init_pghba

    NUM_CON=$(get_num_connections)
    if [ "$NUM_CON" -gt 0 ]
    then
        echo "Still $NUM_CON active connections - uninstallation aborted"
        exit 1;
    fi
    /usr/lib/postgresql/9.1/bin//dropdb fred -p 5432 > /dev/null 2>&1
    /usr/lib/postgresql/9.1/bin//dropuser fred -p 5432 > /dev/null 2>&1
    if grep 'begin-fred-fred--' $PGHBA > /dev/null 2>&1
    then
        cp $PGHBA /tmp/pg_hba.conf.$$
        echo -n "" > $PGHBA
        sed -i '/--begin-fred-fred--/,/--end-fred-fred--/d' /tmp/pg_hba.conf.$$
        cat /tmp/pg_hba.conf.$$ >> $PGHBA
        rm /tmp/pg_hba.conf.$$
    fi
}

# return values:
# 0 if equal
# 1 if first version is bigger than second
# 2 if second version is bigger than first
# e.g. `compare_versions "1.2" "1.2.4"' return 2
compare_versions () {
    if [ "$3" = "" ]
    then
        separator="."
    else
        separator=$3
    fi
    ii=1
    while /bin/true; do
        verA=`echo $1 | cut -d"$separator" -f $ii 2> /dev/null`
        verB=`echo $2 | cut -d"$separator" -f $ii 2> /dev/null`

        if [ "$verA" = "" -a "$verB" = "" ]
        then
            return 0
        fi

        if [ "$verA" = "" ]
        then
            verA=0
        fi
        if [ "$verB" = "" ]
        then
            verB=0
        fi

        if [ `expr $verA '=' $verB` -eq 0 ]
        then
            if [ `expr $verA '>' $verB` -eq 1 ]
            then
                return 1
            elif [ `expr $verA '<' $verB` -eq 1 ]
            then
                return 2
            fi
        fi

        ii=`expr $ii + 1`
    done
    return 0
}

f_upgrade () {
    test_db
    if [ $? != 0 ]
    then
        echo "fred database does not exist or postgres server does not running."
        exit 1
    fi
    ver=$(get_db_version)
    upgrade_script="/tmp/fred-upgrade.sql.$$"
    prefix=/usr
    datarootdir=${prefix}/share
    upgradesqldir=${datarootdir}/fred-db/upgrades/
    curr_dir=`pwd`
    cd $upgradesqldir
    for FILE in `ls *.sql | sort -V`
    do
        FILE_VER=`echo $FILE | sed -r 's/^([0-9.]+)-([0-9.]+).*\.sql/\1/'`
	if [ "$FILE_VER" = "$FILE" ]; then break; fi
        compare_versions $ver $FILE_VER
        if [ $? != 1 ]
        then
	    # if filename contains "superuser", psql will be running as
	    # superuser
	    USER="-U fred"
	    echo $FILE | grep "superuser" > /dev/null && USER=""
	    /usr/lib/postgresql/9.1/bin//psql --set ON_ERROR_STOP=1 fred $USER -p 5432 -f $FILE > /tmp/aaa 2>&1
	    res=$?
            echo -n $FILE
	    if [ "$res" = "0" ]; then 
		echo " OK";
	    else
		echo " Failed (check /tmp/aaa)"
		grep -i error /tmp/aaa
		break;
	    fi
        fi
    done
    cd $curr_dir
}

f_status () {
    test_db
    if [ $? != 0 ]
    then
        echo "fred database does not exist or postgres server does not running."
        exit 1
    else
        echo -n "fred database version: $(get_db_version), "
        echo "active connections: $(get_num_connections) "
        exit 0
    fi
}

list_parts_worker() {
    SERVICES=`/usr/lib/postgresql/9.1/bin//psql -P tuples_only=on -d fred -U fred -At -p 5432 \
                    -c "select partition_postfix from service order by partition_postfix" 2>/dev/null;`

    # add monitoring postfix
    SERVICES="$SERVICES mon_"

    echo ----- List of services:
    echo $SERVICES

    > table_list.txt

    if [ ! -w table_list.txt ]; then
        echo File table_list.txt not accessible for writing, action cancelled.
        exit 1
    fi

    # for table_base in log_entry log_raw_content log_property_value log_session; do
    for table_base in request request_data request_property_value request_object_ref session; do
            for postfix in $SERVICES; do
                    /usr/lib/postgresql/9.1/bin//psql -P tuples_only=on -d fred -U fred -At -p 5432 \
                    -c "\dt ${table_base}_${postfix}*" | grep fred | cut -d '|' -f 2 >> table_list.txt
            done
    done

    /usr/lib/postgresql/9.1/bin//psql -P tuples_only=on -d fred -U fred -At -p 5432 \
        -c "\dt session_*" | grep fred | cut -d '|' -f 2 >> table_list.txt

    echo ------ List of partitions:
    cat table_list.txt
}

f_list_parts() {
    list_parts_worker

    rm -f table_list.txt

}

f_drop_parts() {
    list_parts_worker

    echo "Type yes if you're sure you want to drop these tables"
    read ans

    if [ "$ans" != "yes" ]; then
        echo Not confirmed.
        exit 0
    fi

    # in bash ! only works with [[ command
    if [[ ! -a table_list.txt ]]; then
        echo File table_list.txt not found, action cancelled.
        exit 1
    fi

    for table in `cat table_list.txt`; do
            /usr/lib/postgresql/9.1/bin//psql -P tuples_only=on -d fred -U fred -At -p 5432 \
                    -c "drop table ${table} cascade" || break;
    done;

    rm -f table_list.txt

}

f_create_parts() {
    test_db
    if [ $? != 0 ]
    then
        echo "fred database does not exist or postgres server does not runnint."
        exit 1
    fi

    if [ $# -lt 2 ]	
    then
        echo "Not enough arguments, Usage: $0 <from_date> <to_date>"
        exit 1
    fi	

    echo
    echo Creating partitions from: $1 to: $2

    /usr/lib/postgresql/9.1/bin//psql -d fred -U fred -At -p 5432 \
                    -c "select create_parts('$1')"  > error_out.txt 2>&1


    if [ $? != 0 ]; then
        echo "----- SQL failed, error ouput (file error_out.txt): "
        cat error_out.txt
        exit 1
    else
        rm -f error_out.txt
    fi
}

f_shell() {
    test_db
    shift
    /usr/lib/postgresql/9.1/bin//psql -d fred -U fred -p 5432 $@
}

f_schema() {
    test_db
    /usr/lib/postgresql/9.1/bin//pg_dump -U fred -p 5432 fred
}

case $1 in
    "install") f_install;;
    "uninstall") f_uninstall;;
    "upgrade" ) f_upgrade;;
    "status" ) f_status;;
    "testuser" ) test_user;;
    "create_partitions") f_create_parts $2 $3;;
    "list_partitions") list_parts_worker;;
    "drop_partitions") f_drop_parts;;
    "shell" ) f_shell $@;;
    "schema" ) f_schema;;
    "help" ) f_usage;;
    "") f_usage;;
    *)
        echo "Unknown argument";
        f_usage;
        exit 1;;
esac
