Mercurial > lada > lada-server

annotate db_schema/setup-db.sh @ 1115:2f42a7607bbd

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Client and server now expect these combinations to be modelled, too.
author Tom Gottfried <tom@intevation.de>
date Thu, 27 Oct 2016 19:30:32 +0200
parents 186d602e031a
children ea6b062e5305 1bc8ab13e1f7
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
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
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)