mstanko@930: #!/bin/sh -x
mstanko@930: # SYNOPSIS
mstanko@930: # ./setup-db.sh [-c] [ROLE_NAME] [ROLE_PW] [DB_NAME]
mstanko@930: #   -c         clean - drop an existing database
mstanko@930: #   ROLE_NAME  nema of db user (default = lada)
mstanko@930: #   ROLE_PW    login password  (default = ROLE_NAME)
mstanko@930: #   DB_NAME    name of the databaes (default = ROLE_NAME)
mstanko@930: #
mstanko@930: # There will be used a remote database server if there exists the  enviroment variable DB_SRV 
mstanko@930: # and optional DB_PORT 
mstanko@930: 
tom@742: DIR=`dirname $0`
tom@742: 
mstanko@930: if [ " $1" == " -c" ] ; then 
mstanko@930:    DROP_DB="true"
mstanko@930:    shift
mstanko@930: fi
mstanko@930: 
tom@854: ROLE_NAME=${1:-lada}
mstanko@928: echo "DROLE_NAME = $ROLE_NAME"
tom@854: ROLE_PW=${2:-$ROLE_NAME}
mstanko@925: echo "ROLE_PW = $ROLE_PW"
tom@854: DB_NAME=${3:-$ROLE_NAME}
mstanko@925: echo "DB_NAME = $DB_NAME"
tom@742: 
mstanko@925: # if variable DB_SRV and otional DB_PORT is set a remote database connection will be used
mstanko@925: if [ -n "$DB_SRV" ] ; then DB_CONNECT_STRING="-h $DB_SRV" ; fi
mstanko@925: if [ -n "$DB_SRV" -a -n "$DB_PORT"  ] ; then 
mstanko@925:   DB_CONNECT_STRING="$DB_CONNECT_STRING -p $DB_PORT"
mstanko@925: fi
mstanko@925: DB_CONNECT_STRING="$DB_CONNECT_STRING -U postgres"
mstanko@928: echo "DB_CONNECT_STRING = $DB_CONNECT_STRING"
tom@866: 
mstanko@925: if [ `psql $DB_CONNECT_STRING -t --command "SELECT count(*) FROM pg_catalog.pg_user WHERE usename = '$ROLE_NAME'"` -eq 0 ] ; then
mstanko@925:   echo create user $ROLE_NAME
mstanko@925:   psql $DB_CONNECT_STRING --command "CREATE USER $ROLE_NAME PASSWORD '$ROLE_PW';"
mstanko@925: fi
mstanko@925: 
mstanko@930: if [ "$DROP_DB" == "true" ] && psql $DB_CONNECT_STRING -l | grep -q "^ $DB_NAME " ; then
mstanko@925:   echo drop db $DB_NAME 
mstanko@925:   psql $DB_CONNECT_STRING --command "DROP DATABASE $DB_NAME"
mstanko@925: fi
mstanko@925: 
mstanko@925: echo create db $DB_NAME
mstanko@925: psql $DB_CONNECT_STRING --command \
mstanko@925:      "CREATE DATABASE $DB_NAME WITH OWNER = $ROLE_NAME ENCODING = 'UTF8'"
mstanko@925: 
mstanko@925: echo create postgis extention
mstanko@925: psql $DB_CONNECT_STRING -d $DB_NAME  --command  \
tom@866:      "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public"
tom@866: 
mstanko@925: echo create stammdaten schema
mstanko@925: psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_schema.sql
mstanko@925: 
mstanko@925: echo create lada schema
mstanko@925: psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_schema.sql
mstanko@925: echo set grants
mstanko@925: psql $DB_CONNECT_STRING -d $DB_NAME --command \
tom@742:      "GRANT USAGE ON SCHEMA stammdaten, bund, land TO $ROLE_NAME;
tom@764:       GRANT USAGE
raimund@752:             ON ALL SEQUENCES IN SCHEMA stammdaten, bund, land TO $ROLE_NAME;
tom@742:       GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES
tom@742:             ON ALL TABLES IN SCHEMA stammdaten, bund, land TO $ROLE_NAME;"
raimund@908: 
mstanko@925: echo import stammdaten
mstanko@925: psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_data.sql
mstanko@925: 
mstanko@925: echo import lada test data
mstanko@925: psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_data.sql