Mercurial > lada > lada-server
annotate db_schema/setup-db.sh @ 1127:997899aba991
Do not try to persist Probe objects with validation errors.
Validation errors are often in line with database constraints and
thus trying to persist will result in an Internal Server Error.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 31 Oct 2016 18:25:02 +0100 |
parents | 186d602e031a |
children | ea6b062e5305 1bc8ab13e1f7 |
rev | line source |
---|---|
933 | 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 | 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 | 10 # There will be used a remote database server if there exists the |
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 | 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 | 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 | 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 |
bc44dcda6f69
Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents:
934
diff
changeset
|
75 echo import stammdaten |
1085
1d1c50b7a23b
Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents:
1076
diff
changeset
|
76 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
|
77 |
966
bc44dcda6f69
Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents:
934
diff
changeset
|
78 echo import lada test data |
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/lada_data.sql |
1056
299f4ba86090
extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents:
966
diff
changeset
|
80 |
299f4ba86090
extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents:
966
diff
changeset
|
81 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
|
82 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
|
83 |
1076
4ce4bd48c260
Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents:
1056
diff
changeset
|
84 TS="0101" |
1056
299f4ba86090
extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents:
966
diff
changeset
|
85 cd /tmp |
1076
4ce4bd48c260
Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents:
1056
diff
changeset
|
86 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
|
87 curl -O \ |
4ce4bd48c260
Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents:
1056
diff
changeset
|
88 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
|
89 fi |
1056
299f4ba86090
extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents:
966
diff
changeset
|
90 unzip vg250_${TS}.utm32s.shape.ebenen.zip "*VG250_GEM*" |
299f4ba86090
extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents:
966
diff
changeset
|
91 cd vg250_${TS}.utm32s.shape.ebenen/vg250_ebenen/ |
1085
1d1c50b7a23b
Run psql quietly when running large SQL scripts.
Tom Gottfried <tom@intevation.de>
parents:
1076
diff
changeset
|
92 shp2pgsql VG250_GEM geo.gem_utm | psql -q $DB_CONNECT_STRING -d $DB_NAME |
1056
299f4ba86090
extend docker envireoment to load geographical data of german comunities
Michael Stanko <mstanko@bfs.de>
parents:
966
diff
changeset
|
93 cd /tmp |
1076
4ce4bd48c260
Avoid repeating potentially lengthy download of shapefiles.
Tom Gottfried <tom@intevation.de>
parents:
1056
diff
changeset
|
94 rm -rf vg250_${TS}.utm32s.shape.ebenen |
966
bc44dcda6f69
Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents:
934
diff
changeset
|
95 fi |