Mercurial > lada > lada-server
annotate db_schema/setup-db.sh @ 991:ce1ec3cbde54
Make ID for Messregime an integer.
Using VARCHAR(1) has drawbacks such as an empty string being
a valid value.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 01 Jul 2016 17:36:06 +0200 |
parents | bc44dcda6f69 |
children | 2977a7ac414f 299f4ba86090 |
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 |
2b746f4e97d6
fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents:
908
diff
changeset
|
62 psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/stammdaten_schema.sql |
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 |
2b746f4e97d6
fix db-setup and stammdaten_data.sql
Michael Stanko <mstanko@bfs.de>
parents:
908
diff
changeset
|
65 psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_schema.sql |
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 \ |
742
17165e710631
Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
68 "GRANT USAGE ON SCHEMA stammdaten, bund, 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 |
752
0cf9387c8da2
Added grant to all sequences.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
742
diff
changeset
|
70 ON ALL SEQUENCES IN SCHEMA stammdaten, bund, 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 |
17165e710631
Add script to set up database as user postgres.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
72 ON ALL TABLES IN SCHEMA stammdaten, bund, 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 |
bc44dcda6f69
Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents:
934
diff
changeset
|
76 psql $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 |
bc44dcda6f69
Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents:
934
diff
changeset
|
79 psql $DB_CONNECT_STRING -d $DB_NAME -f $DIR/lada_data.sql |
bc44dcda6f69
Add option to create database with schema only.
Tom Gottfried <tom@intevation.de>
parents:
934
diff
changeset
|
80 fi |