tom@933: #!/bin/sh -e
mstanko@930: # SYNOPSIS
tom@966: # ./setup-db.sh [-cn] [ROLE_NAME] [ROLE_PW] [DB_NAME]
mstanko@930: #   -c         clean - drop an existing database
tom@966: #   -n         no data - do not import example data
tom@934: #   ROLE_NAME  name 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: #
tom@934: # There will be used a remote database server if there exists the
tom@934: # enviroment variable DB_SRV and optional DB_PORT
mstanko@930: 
tom@1200: DIR=$(readlink -f $(dirname $0))
tom@742: 
tom@966: while getopts "cn" opt; do
tom@932:     case "$opt" in
tom@932:         c)
tom@932:             DROP_DB="true"
tom@932:             ;;
tom@966:         n)
tom@966:             NO_DATA="true"
tom@966:             ;;
tom@932:     esac
tom@932: done
tom@932: 
tom@932: shift $((OPTIND-1))
mstanko@930: 
tom@854: ROLE_NAME=${1:-lada}
tom@934: echo "ROLE_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: 
tom@1313: # Stop on error any execution of SQL via psql
tom@1313: DB_CONNECT_STRING="-v ON_ERROR_STOP=on "
tom@1313: 
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
tom@934: 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@1319: if [ `psql $DB_CONNECT_STRING -t --quiet --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: 
tom@932: if [ "$DROP_DB" = "true" ] && psql $DB_CONNECT_STRING -l | grep -q "^ $DB_NAME " ; then
tom@932:   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: 
tom@934: echo create postgis extension
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
tom@1085: psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_schema.sql
mstanko@925: 
mstanko@925: echo create lada schema
tom@1085: psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_schema.sql
raimund@1298: 
raimund@1298: echo create audit-trail table/trigger/views
raimund@1298: psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/audit.sql
raimund@1298: 
mstanko@925: echo set grants
mstanko@925: psql $DB_CONNECT_STRING -d $DB_NAME --command \
tom@1097:      "GRANT USAGE ON SCHEMA stammdaten, land TO $ROLE_NAME;
tom@764:       GRANT USAGE
tom@1097:             ON ALL SEQUENCES IN SCHEMA stammdaten, land TO $ROLE_NAME;
tom@742:       GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES
tom@1097:             ON ALL TABLES IN SCHEMA stammdaten, land TO $ROLE_NAME;"
raimund@908: 
tom@966: if [ "$NO_DATA" != "true" ]; then
mstanko@1188:     echo import stammdaten.verwaltungseinheit
mstanko@1188:     psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_data_verwaltungseinheit.sql
mstanko@1188: 
tom@966:     echo import stammdaten
mstanko@1191:     for file in \
mstanko@1191:         stammdaten_data_netzbetreiber.sql \
mstanko@1191:         stammdaten_data_mess_stelle.sql \
mstanko@1191:         stammdaten_data_auth.sql \
mstanko@1191:         stammdaten_data_mess_einheit.sql \
mstanko@1191:         stammdaten_data_umwelt.sql \
mstanko@1191:         stammdaten_data_auth_lst_umw.sql \
mstanko@1191:         stammdaten_data_datenbasis.sql \
mstanko@1191:         stammdaten_data_datensatz_erzeuger.sql \
mstanko@1191:         stammdaten_data_deskriptor_umwelt.sql \
mstanko@1191:         stammdaten_data_deskriptoren.sql \
mstanko@1191:         stammdaten_data_koordinaten_art.sql \
mstanko@1191:         stammdaten_data_messmethode.sql \
mstanko@1191:         stammdaten_data_messgroesse.sql \
mstanko@1191:         stammdaten_data_messgroessen_gruppe.sql \
mstanko@1191:         stammdaten_data_ort_typ.sql \
mstanko@1191:         stammdaten_data_staat.sql \
mstanko@1191:         stammdaten_data_kta.sql \
mstanko@1191:         stammdaten_data_ortszuordnung_typ.sql \
mstanko@1191:         stammdaten_data_pflicht_messgroesse.sql \
mstanko@1191:         stammdaten_data_proben_zusatz.sql \
mstanko@1191:         stammdaten_data_probenart.sql \
mstanko@1191:         stammdaten_data_messprogramm_transfer.sql \
mstanko@1191:         stammdaten_data_ortszusatz.sql \
mstanko@1191:         stammdaten_data_messprogramm_kategorie.sql \
mstanko@1191:         stammdaten_data_ort.sql \
mstanko@1191:         stammdaten_data_probenehmer.sql \
mstanko@1191:         stammdaten_data_query.sql \
mstanko@1191:         stammdaten_data_user_context.sql
mstanko@1191:     do
mstanko@1191:         echo ${file%.sql}
mstanko@1191:         psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/$file
mstanko@1191:     done
mstanko@1191: 
mstanko@1191:     echo init sequences
mstanko@1191:     psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_init_sequences.sql
mstanko@925: 
tom@966:     echo import lada test data
tom@1085:     psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_data.sql
mstanko@1056: 
mstanko@1320:     echo import lada messprogramm
mstanko@1320:     psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_messprogramm.sql
mstanko@1320: 
mstanko@1191:     echo create schema geo
mstanko@1056:     psql $DB_CONNECT_STRING -d $DB_NAME --command "CREATE SCHEMA geo AUTHORIZATION $ROLE_NAME"
mstanko@1056: 
mstanko@1188:     echo downlaod and import german administrative borders
tom@1076:     TS="0101"
tom@1207:     cd /tmp
tom@1076:     if [ ! -f vg250_${TS}.utm32s.shape.ebenen.zip ]; then
tom@1076:         curl -O \
tom@1076:             http://sg.geodatenzentrum.de/web_download/vg/vg250_${TS}/utm32s/shape/vg250_${TS}.utm32s.shape.ebenen.zip
tom@1076:     fi
mstanko@1188:     unzip -u vg250_${TS}.utm32s.shape.ebenen.zip "*VG250_GEM*"
mstanko@1319: 
raimund@1193:     shp2pgsql -s 25832:4326 vg250_${TS}.utm32s.shape.ebenen/vg250_ebenen/VG250_GEM geo.gem_utm | psql -q $DB_CONNECT_STRING -d $DB_NAME
mstanko@1188: 
mstanko@1188:     echo fille stammdaten.verwaltungsgrenze
mstanko@1188:     psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_fill_verwaltungsgrenze.sql
mstanko@1188: 
mstanko@1188:     if [ -f $DIR/lada_auth.sql ]; then
mstanko@1188:         echo load private auth configuration
mstanko@1188:         psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_auth.sql
mstanko@1188:     fi
tom@966: fi