Mercurial > gemma
annotate schema/install-db.sh @ 261:ab9859981ee3
If a user got renamed kick her/him from the connection pool.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 27 Jul 2018 14:55:47 +0200 |
parents | c2cef3ce8853 |
children | 61f1374f0c44 |
rev | line source |
---|---|
244
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 #!/bin/bash |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
2 # Author(s): |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
3 # Sascha Wilde <wilde@intevation.de> |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
4 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
5 ME=`basename "$0"` |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
6 BASEDIR=`dirname "$0"` |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
7 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 usage() |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
9 { |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
10 cat <<EOF |
260
c2cef3ce8853
db-setup script: Improved help message.
Sascha Wilde <wilde@intevation.de>
parents:
259
diff
changeset
|
11 $ME [OPTION]... |
244
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
12 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 Options: |
260
c2cef3ce8853
db-setup script: Improved help message.
Sascha Wilde <wilde@intevation.de>
parents:
259
diff
changeset
|
14 -d, --db=NAME create the database NAME. Default: "gemma" |
c2cef3ce8853
db-setup script: Improved help message.
Sascha Wilde <wilde@intevation.de>
parents:
259
diff
changeset
|
15 -p, --port=PORT connect do the postgresql cluster at PORT. |
c2cef3ce8853
db-setup script: Improved help message.
Sascha Wilde <wilde@intevation.de>
parents:
259
diff
changeset
|
16 Default is the postgresql standard port 5432 |
c2cef3ce8853
db-setup script: Improved help message.
Sascha Wilde <wilde@intevation.de>
parents:
259
diff
changeset
|
17 -D, --demo also install demo accounts and data |
255
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
18 --drop drop database and all roles |
244
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 --help display this help and exit |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 EOF |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
22 } |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
23 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
24 fatal() |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
25 { |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
26 echo >&2 "$1" |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
27 exit 23 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
28 } |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
30 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
31 # Defaults: |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
32 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
33 db=gemma |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
34 port=5432 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
35 demo=0 |
255
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
36 drop=0 |
244
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
37 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
38 # Parse options: |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
39 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
40 OPTS=`getopt \ |
255
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
41 -l help,demo,db:,port:,drop \ |
244
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
42 -o Dd:p: -n "$ME" -- "$@"` |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
43 [ $? -eq 0 ] || { usage ; exit 1 ; } |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
44 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
45 eval set -- "$OPTS" |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
46 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
47 while true ; do |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
48 case "$1" in |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
49 --db|-d) |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
50 db="$2" |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
51 shift 2 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
52 ;; |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
53 --port|-p) |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
54 port="$2" |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
55 shift 2 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
56 ;; |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
57 --demo|-D) |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
58 demo=1 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
59 shift 1 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
60 ;; |
255
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
61 --drop) |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
62 drop=1 |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
63 shift 1 |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
64 ;; |
244
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
65 --help) |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
66 { usage ; exit 0 ; } |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
67 ;; |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
68 --) |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
69 shift |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
70 break |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
71 ;; |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
72 esac |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
73 done |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
74 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
75 |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
76 # Main ------------------------------------------------------------ |
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
77 |
255
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
78 if [[ drop -eq 0 ]] ; then |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
79 # Default operation: create schema |
256
1ff9aec9326f
db-setup script: Be more quiet during creation.
Sascha Wilde <wilde@intevation.de>
parents:
255
diff
changeset
|
80 psql -q -p "$port" -f "$BASEDIR/roles.sql" |
255
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
81 createdb -p "$port" "$db" |
259
f1280801ba8c
db-setup script: Be even more quiet during creation.
Sascha Wilde <wilde@intevation.de>
parents:
256
diff
changeset
|
82 psql -qt -p "$port" -d "$db" \ |
f1280801ba8c
db-setup script: Be even more quiet during creation.
Sascha Wilde <wilde@intevation.de>
parents:
256
diff
changeset
|
83 -c "SET client_min_messages TO WARNING;" \ |
255
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
84 -f "$BASEDIR/gemma.sql" \ |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
85 -f "$BASEDIR/manage_users.sql" \ |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
86 -f "$BASEDIR/auth.sql" |
247
946baea3d280
Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents:
244
diff
changeset
|
87 |
255
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
88 if [[ $demo -eq 1 ]] ; then |
256
1ff9aec9326f
db-setup script: Be more quiet during creation.
Sascha Wilde <wilde@intevation.de>
parents:
255
diff
changeset
|
89 psql -q -p "$port" -f "$BASEDIR/demo-data/responsibility_areas.sql" \ |
255
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
90 -d "$db" |
256
1ff9aec9326f
db-setup script: Be more quiet during creation.
Sascha Wilde <wilde@intevation.de>
parents:
255
diff
changeset
|
91 psql -q -p "$port" -f "$BASEDIR/demo-data/roles.sql" \ |
255
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
92 -f "$BASEDIR/demo-data/users.sql" -d "$db" |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
93 fi |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
94 else |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
95 # Evil mode: drop everything gemma |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
96 echo "Really drop database '$db' and alle gemma roles? [type 'yes']: " |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
97 read a |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
98 if [[ $a == "yes" ]] ; then |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
99 dropdb -p "$port" "$db" |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
100 for r in `psql -p 5433 -t -c '\du' | awk -F '|' \ |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
101 '$3 ~/waterway_user|waterway_admin|sys_admin/ \ |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
102 || $1 ~/waterway_user|waterway_admin|sys_admin/ \ |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
103 {print $1}'` |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
104 do |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
105 dropuser -p "$port" "$r" |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
106 done |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
107 else |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
108 echo "No harm done." |
c36bd39782c1
Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents:
252
diff
changeset
|
109 fi |
244
4ebb791b8278
Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
110 fi |