Mercurial > lada > lada-server
comparison db_schema/setup-db.sh @ 925:2b746f4e97d6
fix db-setup and stammdaten_data.sql
| author | Michael Stanko <mstanko@bfs.de> |
|---|---|
| date | Thu, 28 Apr 2016 15:26:12 +0200 |
| parents | 51051e85348f |
| children | 6ab17d8b5558 |
comparison
equal
deleted
inserted
replaced
| 924:34c04b0557ac | 925:2b746f4e97d6 |
|---|---|
| 1 #!/bin/sh | 1 #!/bin/sh |
| 2 DIR=`dirname $0` | 2 DIR=`dirname $0` |
| 3 | 3 |
| 4 ROLE_NAME=${1:-lada} | 4 ROLE_NAME=${1:-lada} |
| 5 echo "DROLE_NAME = $ROLE_NAME=" | |
| 5 ROLE_PW=${2:-$ROLE_NAME} | 6 ROLE_PW=${2:-$ROLE_NAME} |
| 7 echo "ROLE_PW = $ROLE_PW" | |
| 6 DB_NAME=${3:-$ROLE_NAME} | 8 DB_NAME=${3:-$ROLE_NAME} |
| 9 echo "DB_NAME = $DB_NAME" | |
| 7 | 10 |
| 8 psql --command "CREATE USER $ROLE_NAME PASSWORD '$ROLE_PW';" | 11 # if variable DB_SRV and otional DB_PORT is set a remote database connection will be used |
| 9 createdb -E UTF-8 $DB_NAME | 12 if [ -n "$DB_SRV" ] ; then DB_CONNECT_STRING="-h $DB_SRV" ; fi |
| 13 if [ -n "$DB_SRV" -a -n "$DB_PORT" ] ; then | |
| 14 DB_CONNECT_STRING="$DB_CONNECT_STRING -p $DB_PORT" | |
| 15 fi | |
| 16 DB_CONNECT_STRING="$DB_CONNECT_STRING -U postgres" | |
| 17 echo "DB_CONNECT_STRING = $DB_CONNECT_STRING" | |
| 10 | 18 |
| 11 psql -d $DB_NAME --command \ | 19 if [ `psql $DB_CONNECT_STRING -t --command "SELECT count(*) FROM pg_catalog.pg_user WHERE usename = '$ROLE_NAME'"` -eq 0 ] ; then |
| 20 echo create user $ROLE_NAME | |
| 21 psql $DB_CONNECT_STRING --command "CREATE USER $ROLE_NAME PASSWORD '$ROLE_PW';" | |
| 22 fi | |
| 23 exit | |
| 24 | |
| 25 if psql -h test-pgsql1-fr.lab.bfs.de -U postgres -l | grep -q "^ $DB_NAME " ; then | |
| 26 echo drop db $DB_NAME | |
| 27 psql $DB_CONNECT_STRING --command "DROP DATABASE $DB_NAME" | |
| 28 fi | |
| 29 | |
| 30 echo create db $DB_NAME | |
| 31 psql $DB_CONNECT_STRING --command \ | |
| 32 "CREATE DATABASE $DB_NAME WITH OWNER = $ROLE_NAME ENCODING = 'UTF8'" | |
| 33 exit | |
| 34 | |
| 35 echo create postgis extention | |
| 36 psql $DB_CONNECT_STRING -d $DB_NAME --command \ | |
| 12 "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public" | 37 "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public" |
| 13 | 38 |
| 14 psql -d $DB_NAME -f $DIR/stammdaten_schema.sql | 39 echo create stammdaten schema |
| 15 psql -d $DB_NAME -f $DIR/lada_schema.sql | 40 psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_schema.sql |
| 16 psql -d $DB_NAME --command \ | 41 |
| 42 echo create lada schema | |
| 43 psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_schema.sql | |
| 44 echo set grants | |
| 45 psql $DB_CONNECT_STRING -d $DB_NAME --command \ | |
| 17 "GRANT USAGE ON SCHEMA stammdaten, bund, land TO $ROLE_NAME; | 46 "GRANT USAGE ON SCHEMA stammdaten, bund, land TO $ROLE_NAME; |
| 18 GRANT USAGE | 47 GRANT USAGE |
| 19 ON ALL SEQUENCES IN SCHEMA stammdaten, bund, land TO $ROLE_NAME; | 48 ON ALL SEQUENCES IN SCHEMA stammdaten, bund, land TO $ROLE_NAME; |
| 20 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES | 49 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES |
| 21 ON ALL TABLES IN SCHEMA stammdaten, bund, land TO $ROLE_NAME;" | 50 ON ALL TABLES IN SCHEMA stammdaten, bund, land TO $ROLE_NAME;" |
| 22 | 51 |
| 23 psql -d $DB_NAME -f $DIR/stammdaten_data.sql | 52 echo import stammdaten |
| 24 psql -d $DB_NAME -f $DIR/lada_data.sql | 53 psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_data.sql |
| 54 | |
| 55 echo import lada test data | |
| 56 psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_data.sql |