commands

#!/bin/bash 
#set -e;

APP=$2

OLDHOME=$HOME
HOME="$DOKKU_ROOT/.postgresql"
PGADMIN=postgres
postgresql_database="${APP}"
db_image=asaushkin/postgresql

check_app() {
    if [[ -z "$APP" ]]; then
        echo "You must specify an app name"
        exit 1
    fi
}

check_container() {
    if [[ -z "$id" ]]; then
        echo "Postgresql container not started cannot continue. Start with dokku postgresql:start"
        exit 1
    fi
}

postgres_password() {
    cat "$DOKKU_ROOT/.postgresql/admin_pw"
}

appuser_password() {
    cat "$DOKKU_ROOT/.postgresql/pass_$APP"
}

# $1 - dbname (if none, then postgres with root privs)
# $2 - sql code
# $3 - psql parameters
execute() {
    local dbhost=localhost
    local dbport=5432

    if [ -z "$1" -o x"$1" = x"postgres" ]; then
        local dbname=postgres
        local dbuser=postgres
        local dbpass=$(postgres_password)
    else
        local dbname="$APP"
        local dbuser="$APP"
        local dbpass=$(appuser_password)
    fi

    if [ ! -z "$2" ]; then
        PGPASSWORD=$dbpass psql $3 -h $dbhost -p $dbport -U $dbuser -d $dbname <<SQL
$2
SQL
    else
        PGPASSWORD=$dbpass psql $3 -h $dbhost -p $dbport -U $dbuser -d $dbname
    fi
}

url() {
    echo "DATABASE_URL=\"postgres://${APP}:$(appuser_password)@${ip}:5432/${APP}\""
}

id=$(docker ps | grep "$db_image" |  awk '{print $1}')
ip=$(ifconfig docker0  | grep 'inet addr:'| grep -v '127.0.0.1' | cut -d: -f2 | awk '{ print $1}')

case "$1" in
    postgresql:psql)

        check_container
        if [ -z "$APP" ]; then    
            execute postgres
        else
            if [[ ! -r "$DOKKU_ROOT/.postgresql/pass_$APP" ]]; then
                echo "ERROR: Database $APP does not exist or not properly initialized"
                exit 1 
            fi
            execute "$APP"
        fi
        ;;

    postgresql:create)

        check_container
        check_app

        # Check already initiaized db
        if [ -r "$DOKKU_ROOT/.postgresql/pass_$APP" ]; then
            echo ERROR: Database ${postgresql_database} for application ${APP} already exists. Try \"dokku postgresql:delete "$APP"\" first.
            exit 1
        fi
        
        if [[ $(execute postgres "select count(*) from pg_roles where rolname = '${APP}'" '-At') -eq 0 ]]; then
            apg -M NCL -m 16 -x 16 -n 1 > "$DOKKU_ROOT/.postgresql/pass_$APP"
            execute postgres "create user \"${APP}\" with password '$(appuser_password)'" '-X'
        else
            echo WARN: User "$APP" already exists in the PostgreSQL instance, skipping creation.
        fi

        if [[ $(execute postgres "select count(*) from pg_database where datname = '${APP}'" '-At') -eq 0 ]]; then
            execute postgres "create database \"${postgresql_database}\" with owner \"${APP}\"" '-X'
            execute postgres "alter database \"${postgresql_database}\" set timezone to 'UTC'" '-X'
        else
            echo WARN: Database "$APP" already exists in the PostgreSQL instance, skipping creation.
        fi

        if [[ -d "$DOKKU_ROOT/$APP" ]]; then
            dokku config:set "$APP" $(url)
        else
            url
        fi
    ;;

    postgresql:url)
        check_container
        check_app
        
        url
    ;;

    postgresql:delete)
        check_container
        check_app
    
        execute postgres "drop database \"${APP}\"" "-X"
        execute postgres "drop user \"${APP}\"" "-X"

        if [[ -d "$DOKKU_ROOT/$APP" ]]; then
            dokku config:unset "$APP" DATABASE_URL
        fi

        rm "$DOKKU_ROOT/.postgresql/pass_$APP"
    ;;

    postgresql:dblist)
        check_container
        execute postgres '\list'
    ;;

    postgresql:start)
    
        if [[ "$id" != "" ]]; then
            echo "Postgresql container already running with ID: ${id}"
        else
            docker run -p 5432:5432 -d \
                -v "$DOKKU_ROOT/.postgresql/data":"/var/lib/postgresql/9.3/main" \
                -v "$DOKKU_ROOT/.postgresql/etc":"/etc/postgresql/9.3/main" \
                "$db_image" bash -c "su postgres -c '/usr/lib/postgresql/9.3/bin/postgres -D /var/lib/postgresql/9.3/main -c config_file=/etc/postgresql/9.3/main/postgresql.conf'"
        fi
    ;;

    postgresql:stop)
        check_container
        docker stop ${id}
    ;;

    postgresql:status)
        if [[ "$id" != "" ]]; then
            echo "Postgresql container running with ID: ${id}"
        else
            echo "Postgresql container not running"
        fi
    ;;

    postgresql:bash)
        check_container
        docker exec -it $id bash
    ;;

    postgresql:tsize)
        check_container
        check_app

        execute $APP "
select table_schema, table_name, 
    pg_size_pretty( pg_relation_size( quote_ident( table_schema ) || '.' || quote_ident( table_name ) ) ) as size, 
    pg_size_pretty( pg_total_relation_size( quote_ident( table_schema ) || '.' || quote_ident( table_name ) ) ) as total_size  
from information_schema.tables 
where table_type = 'BASE TABLE' and table_schema not in ('information_schema', 'pg_catalog') 
order by pg_relation_size( quote_ident( table_schema ) || '.' || quote_ident( table_name ) ) desc, table_schema, table_name"
    ;;

    postgresql:dbsize)
        check_container

        execute postgres "SELECT datname, pg_size_pretty(pg_database_size(datname)) db_size FROM pg_database ORDER BY db_size"
    ;;

    postgresql:activity)
        check_container

        execute postgres "select * from pg_stat_activity"
    ;;

    postgresql:kill)
        check_container

        if [ ! -z "$2" ]; then
            execute postgres "select pg_terminate_backend('$2')"
        else
            echo "Usage: dokku postgresql:kill <id>"
        fi
    ;;

    help)
        cat && cat<<EOF
    postgresql:psql <app>                           Launch a postgresql console for a given app or as admin user (without appname)
    postgresql:create <app>                         Create a Postgresql database
    postgresql:delete <app>                         Delete specified Postgresql database
    postgresql:start                                Start the Postgresql docker container if it isn't running
    postgresql:stop                                 Stop the Postgresql docker container
    postgresql:bash                                 Enter into bash on the PostgreSQL docker container 
    postgresql:status                               Shows status of Postgresql
    postgresql:dblist                               List all databases
    postgresql:dbsize                               Size all databases
    postgresql:tsize <app>                          Size all user tables in database <app>
    postgresql:activity                             Show current processes in the postgresql instance
    postgresql:kill <id>                            Terminate a backend process
    postgresql:url <app>                            Show an application DATABASE_URL
EOF
    ;;
esac