createDatabase.sh

#! /bin/bash
#############################
# This script creates the postgis databases.
#############################

if [ "$EUID" -ne "0" ]; then
	echo -e "\e[91mThis script must be run as root!\e[39m"
	exit 1
fi

############################
# Variable definitions     #
############################

SCRIPT="$(readlink -f ${BASH_SOURCE[0]})"
SCRIPT_DIR="$(dirname ${SCRIPT})"
if [ ! -f "${SCRIPT_DIR}/config.sh" ]; then
	# If config.properties is not besides the bash-script (most likely because vagrant uploaded it into the guest)
	# we will try to find it in the shared folder
	SCRIPT_DIR="/vagrant/bootstrap"
fi
if [ ! -f "${SCRIPT_DIR}/config.sh" ]; then
	echo -e "\e[91mNo variable declarations found (config.properties file not found)!\e[39m"
	exit 1;
fi
source "${SCRIPT_DIR}/config.sh"

fn_arg2string PG_DB_USER "${1}" "${PG_DB_USER}"
fn_arg2string PG_DB_PASSWORD "${2}" "${PG_DB_PASSWORD}"

######################
# Database creation  #
######################

fn_echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
fn_echo " Preparing the database ($(date +%H:%M:%S)):"
fn_echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"

START=$(date +%s)

mkdir -p "$WORKING_DIR"
cd "$WORKING_DIR"
touch "$WORKING_DIR/database_creation.lock"

if $AVAILABLE_POSTGRES; then
	fn_echo "Creating postgresql database \"$PG_DB_NAME\""
	sudo -u "postgres" ${EXEC_PSQL} -d "template1" -c "CREATE DATABASE \"$PG_DB_NAME\";" >> $WORKING_DIR/setup_database.log 2>&1
	sudo -u "postgres" ${EXEC_PSQL} -d "$PG_DB_NAME" -c "CREATE USER \"$PG_DB_USER\" WITH PASSWORD '$PG_DB_PASSWORD';" >> $WORKING_DIR/setup_database.log 2>&1
	sudo -u "postgres" ${EXEC_PSQL} -d "$PG_DB_NAME" -c "ALTER DATABASE \"$PG_DB_NAME\" OWNER TO \"$PG_DB_USER\";" >> $WORKING_DIR/setup_database.log 2>&1
	sudo -u "postgres" ${EXEC_PSQL} -d "$PG_DB_NAME" -c "GRANT ALL PRIVILEGES ON DATABASE \"$PG_DB_NAME\" TO \"$PG_DB_USER\";" >> $WORKING_DIR/setup_database.log 2>&1
	sudo -u "postgres" ${EXEC_PSQL} -d "$PG_DB_NAME" -c "ALTER SCHEMA public OWNER TO \"$PG_DB_USER\";" >> $WORKING_DIR/setup_database.log 2>&1
	sudo -u "postgres" ${EXEC_PSQL} -d "$PG_DB_NAME" -c "ALTER ROLE \"$PG_DB_USER\" SUPERUSER;" >> $WORKING_DIR/setup_database.log 2>&1

	fn_echo "Adding database functions/procedures"
	PGPASSWORD="$PG_DB_PASSWORD" ${EXEC_PSQL} -U "$PG_DB_USER" -h localhost -d "$PG_DB_NAME" -f $SHARED_CONF_DIR/pg_procedures.sql >> $WORKING_DIR/setup_database.log 2>&1

	if $AVAILABLE_POSTGIS; then
		fn_echo "Registering postgis extension for database \"$PG_DB_NAME\""
		PGPASSWORD="$PG_DB_PASSWORD" ${EXEC_PSQL} -U "$PG_DB_USER" -h localhost -d "$PG_DB_NAME" -c "CREATE EXTENSION postgis;" >> $WORKING_DIR/setup_database.log 2>&1
		PGPASSWORD="$PG_DB_PASSWORD" ${EXEC_PSQL} -U "$PG_DB_USER" -h localhost -d "$PG_DB_NAME" -f $SHARED_CONF_DIR/srid_82344_insert.sql >> $WORKING_DIR/setup_database.log 2>&1

		fn_echo "Adding postgis functions/procedures"
		PGPASSWORD="$PG_DB_PASSWORD" ${EXEC_PSQL} -U "$PG_DB_USER" -h localhost -d "$PG_DB_NAME" -f $SHARED_CONF_DIR/pg_procedures_postgis.sql >> $WORKING_DIR/setup_database.log 2>&1
	fi

	if $AVAILABLE_ISOCHRONE_DATAMODEL; then
		fn_echo "Initializing database schema for isochrone-datamodel (used for testing and datamodel creation)"
		PGPASSWORD="$PG_DB_PASSWORD" psql -U "$PG_DB_USER" -h localhost -d "template1" -f "${DEPLOY_DIR}/isochrone-datamodel/at/uibk/dbis/isochrone/datamodel/db/db_create.sql" >> $WORKING_DIR/setup_database.log 2>&1
		PGPASSWORD="$PG_DB_PASSWORD" psql -U "$PG_DB_USER" -h localhost -d "spatial" -f "${DEPLOY_DIR}/isochrone-datamodel/at/uibk/dbis/isochrone/datamodel/db/schema_drop.sql" >> $WORKING_DIR/setup_database.log 2>&1
		PGPASSWORD="spatial" psql -U "spatial" -h localhost -d "spatial" -f "${DEPLOY_DIR}/isochrone-datamodel/at/uibk/dbis/isochrone/datamodel/db/schema_create.sql" >> $WORKING_DIR/setup_database.log 2>&1
		PGPASSWORD="$PG_DB_PASSWORD" psql -U "$PG_DB_USER" -h localhost -d "spatial" -f "${DEPLOY_DIR}/isochrone-datamodel/at/uibk/dbis/isochrone/datamodel/db/extension_create.sql" >> $WORKING_DIR/setup_database.log 2>&1
	fi
fi

# Set status file (meaning that database creation was successful)"
rm "$WORKING_DIR/database_creation.lock"
touch "$WORKING_DIR/database_creation.done"

END=$(date +%s)
TOTAL=$(( $END - $START ))

fn_echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"
fn_echo " Database prepared"
printf '\e[92m Time to prepare the database: %dh:%dm:%ds\e[39m\n' $(($TOTAL/3600)) $(($TOTAL%3600/60)) $(($TOTAL%60))
fn_echo "~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"