Mercurial > lada > lada-server

annotate db_schema/setup-db.sh @ 1194:289edf060550

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Removed table de_vg and added verwaltungsgrenze.
author Raimund Renkert <raimund.renkert@intevation.de>
date Tue, 29 Nov 2016 15:57:09 +0100
parents 00323d9e642c
children fffd66b6ee8a
rev   line source
933
56d2dd058906 Stop on error.
Tom Gottfried <tom@intevation.de>
parents: 932
diff changeset
1 #!/bin/sh -e
930
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
2 # SYNOPSIS
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
3 # ./setup-db.sh [-cn] [ROLE_NAME] [ROLE_PW] [DB_NAME]
930
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
4 # -c clean - drop an existing database
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
5 # -n no data - do not import example data
934
4d8b8c849935 A bit of cleanup.
Tom Gottfried <tom@intevation.de>
parents: 933
diff changeset
6 # ROLE_NAME name of db user (default = lada)
930
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
7 # ROLE_PW login password (default = ROLE_NAME)
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
8 # DB_NAME name of the databaes (default = ROLE_NAME)
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
9 #
934
4d8b8c849935 A bit of cleanup.
Tom Gottfried <tom@intevation.de>
parents: 933
diff changeset
10 # There will be used a remote database server if there exists the
4d8b8c849935 A bit of cleanup.
Tom Gottfried <tom@intevation.de>
parents: 933
diff changeset
11 # enviroment variable DB_SRV and optional DB_PORT
930
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
12
742
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 DIR=`dirname $0`
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
15 while getopts "cn" opt; do
932
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
16 case "$opt" in
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
17 c)
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
18 DROP_DB="true"
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
19 ;;
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
20 n)
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
21 NO_DATA="true"
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
22 ;;
932
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
23 esac
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
24 done
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
25
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
26 shift $((OPTIND-1))
930
d3d9cd5e18f8 add initialze sequences in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 928
diff changeset
27
854
e797391714a0 Allow setting variables via commandline.
Tom Gottfried <tom@intevation.de>
parents: 764
diff changeset
28 ROLE_NAME=${1:-lada}
934
4d8b8c849935 A bit of cleanup.
Tom Gottfried <tom@intevation.de>
parents: 933
diff changeset
29 echo "ROLE_NAME = $ROLE_NAME"
854
e797391714a0 Allow setting variables via commandline.
Tom Gottfried <tom@intevation.de>
parents: 764
diff changeset
30 ROLE_PW=${2:-$ROLE_NAME}
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
31 echo "ROLE_PW = $ROLE_PW"
854
e797391714a0 Allow setting variables via commandline.
Tom Gottfried <tom@intevation.de>
parents: 764
diff changeset
32 DB_NAME=${3:-$ROLE_NAME}
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
33 echo "DB_NAME = $DB_NAME"
742
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
35 # if variable DB_SRV and otional DB_PORT is set a remote database connection will be used
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
36 if [ -n "$DB_SRV" ] ; then DB_CONNECT_STRING="-h $DB_SRV" ; fi
934
4d8b8c849935 A bit of cleanup.
Tom Gottfried <tom@intevation.de>
parents: 933
diff changeset
37 if [ -n "$DB_SRV" -a -n "$DB_PORT" ] ; then
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
38 DB_CONNECT_STRING="$DB_CONNECT_STRING -p $DB_PORT"
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
39 fi
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
40 DB_CONNECT_STRING="$DB_CONNECT_STRING -U postgres"
928
6ab17d8b5558 erase mpr_id values in lada_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 925
diff changeset
41 echo "DB_CONNECT_STRING = $DB_CONNECT_STRING"
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents: 854
diff changeset
42
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
43 if [ `psql $DB_CONNECT_STRING -t --command "SELECT count(*) FROM pg_catalog.pg_user WHERE usename = '$ROLE_NAME'"` -eq 0 ] ; then
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
44 echo create user $ROLE_NAME
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
45 psql $DB_CONNECT_STRING --command "CREATE USER $ROLE_NAME PASSWORD '$ROLE_PW';"
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
46 fi
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
47
932
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
48 if [ "$DROP_DB" = "true" ] && psql $DB_CONNECT_STRING -l | grep -q "^ $DB_NAME " ; then
f5d5cc08966a Remove wrong test operator and prepare for more options.
Tom Gottfried <tom@intevation.de>
parents: 931
diff changeset
49 echo drop db $DB_NAME
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
50 psql $DB_CONNECT_STRING --command "DROP DATABASE $DB_NAME"
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
51 fi
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
52
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
53 echo create db $DB_NAME
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
54 psql $DB_CONNECT_STRING --command \
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
55 "CREATE DATABASE $DB_NAME WITH OWNER = $ROLE_NAME ENCODING = 'UTF8'"
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
56
934
4d8b8c849935 A bit of cleanup.
Tom Gottfried <tom@intevation.de>
parents: 933
diff changeset
57 echo create postgis extension
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
58 psql $DB_CONNECT_STRING -d $DB_NAME --command \
866
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents: 854
diff changeset
59 "CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public"
d47e6b8f3897 Reorganise database setup scripts.
Tom Gottfried <tom@intevation.de>
parents: 854
diff changeset
60
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
61 echo create stammdaten schema
1085
1d1c50b7a23b Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents: 1076
diff changeset
62 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_schema.sql
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
63
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
64 echo create lada schema
1085
1d1c50b7a23b Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents: 1076
diff changeset
65 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_schema.sql
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
66 echo set grants
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
67 psql $DB_CONNECT_STRING -d $DB_NAME --command \
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1085
diff changeset
68 "GRANT USAGE ON SCHEMA stammdaten, land TO $ROLE_NAME;
764
9d2070b84238 Hibernate needs nextval to insert new datasets.
Tom Gottfried <tom@intevation.de>
parents: 752
diff changeset
69 GRANT USAGE
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1085
diff changeset
70 ON ALL SEQUENCES IN SCHEMA stammdaten, land TO $ROLE_NAME;
742
17165e710631 Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES
1097
186d602e031a Merged branch schema-update into default.
Tom Gottfried <tom@intevation.de>
parents: 1085
diff changeset
72 ON ALL TABLES IN SCHEMA stammdaten, land TO $ROLE_NAME;"
908
51051e85348f Added data dumps.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 866
diff changeset
73
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
74 if [ "$NO_DATA" != "true" ]; then
1188
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
75 echo import stammdaten.verwaltungseinheit
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
76 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_data_verwaltungseinheit.sql
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
77
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
78 echo import stammdaten
1085
1d1c50b7a23b Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents: 1076
diff changeset
79 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_data.sql
925
2b746f4e97d6 fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents: 908
diff changeset
80
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
81 echo import lada test data
1085
1d1c50b7a23b Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents: 1076
diff changeset
82 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_data.sql
1056
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
83
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
84 echo create user $ROLE_NAME
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
85 psql $DB_CONNECT_STRING -d $DB_NAME --command "CREATE SCHEMA geo AUTHORIZATION $ROLE_NAME"
299f4ba86090 extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents: 966
diff changeset
86
1188
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
87 echo downlaod and import german administrative borders
1076
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
88 TS="0101"
1192
d667092cc788 Fixed setup-db script for use with docker.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1188
diff changeset
89 cd /tmp
1076
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
90 if [ ! -f vg250_${TS}.utm32s.shape.ebenen.zip ]; then
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
91 curl -O \
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
92 http://sg.geodatenzentrum.de/web_download/vg/vg250_${TS}/utm32s/shape/vg250_${TS}.utm32s.shape.ebenen.zip
4ce4bd48c260 Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents: 1056
diff changeset
93 fi
1188
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
94 unzip -u vg250_${TS}.utm32s.shape.ebenen.zip "*VG250_GEM*"
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
95 # cd vg250_${TS}.utm32s.shape.ebenen/vg250_ebenen/
1193
00323d9e642c Import german administrative borders using EPSG:4326.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1192
diff changeset
96 shp2pgsql -s 25832:4326 vg250_${TS}.utm32s.shape.ebenen/vg250_ebenen/VG250_GEM geo.gem_utm | psql -q $DB_CONNECT_STRING -d $DB_NAME
1188
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
97 # rm -rf vg250_${TS}.utm32s.shape.ebenen
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
98
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
99 echo fille stammdaten.verwaltungsgrenze
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
100 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_fill_verwaltungsgrenze.sql
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
101
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
102 if [ -f $DIR/lada_auth.sql ]; then
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
103 echo load private auth configuration
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
104 psql -q $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_auth.sql
1bc8ab13e1f7 redesign stammdaten.verwaltungseinheit
Michael Stanko <mstanko@bfs.de>
parents: 1097
diff changeset
105 fi
966
bc44dcda6f69 Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents: 934
diff changeset
106 fi
1193
00323d9e642c Import german administrative borders using EPSG:4326.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1192
diff changeset
107
00323d9e642c Import german administrative borders using EPSG:4326.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1192
diff changeset
108 exec /opt/lada_sql/alter_owner.sh
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)