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