Mercurial > gemma

annotate schema/install-db.sh @ 831:fa417ff1f355

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Extended schema to save feature style information.
author Sascha Wilde <wilde@intevation.de>
date Thu, 27 Sep 2018 23:59:36 +0200
parents ad07846b09d1
children d135274a4c92
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
377
220a893318fa Ignore only role creation errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 331
diff changeset
1 #!/bin/bash -e
244
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
331
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
18 --adminpw set the password to use for the "sysadmin" account.
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
19 Default is a random password.
463
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 436
diff changeset
20 --metapw set the password to use for the "meta_login" account.
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 436
diff changeset
21 Default is a random password.
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
22 --drop drop database and all roles
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
23 --help display this help and exit
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
24
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
25 EOF
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
26 }
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
27
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
28 fatal()
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 echo >&2 "$1"
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
31 exit 23
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
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
34 genpw()
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
35 # $1 - length
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
36 {
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
37 dd count=1 if=/dev/urandom 2>/dev/null \
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
38 | tr -cd '[:alnum:],._!?-' | tail -c "$1"
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
39 }
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
40
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
41 # Defaults:
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
42
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
43 db=gemma
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
44 port=5432
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
45 demo=0
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
46 drop=0
331
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
47 adminpw=`genpw 15`
463
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 436
diff changeset
48 metapw=`genpw 15`
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
49
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
50 # Parse options:
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
51
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
52 OPTS=`getopt \
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 470
diff changeset
53 -l help,demo,db:,port:,drop,adminpw:,metapw: \
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
54 -o Dd:p: -n "$ME" -- "$@"`
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
55 [ $? -eq 0 ] || { usage ; exit 1 ; }
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 eval set -- "$OPTS"
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
58
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
59 while true ; do
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
60 case "$1" in
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
61 --db|-d)
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
62 db="$2"
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
63 shift 2
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
64 ;;
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
65 --port|-p)
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
66 port="$2"
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
67 shift 2
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
68 ;;
331
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
69 --adminpw)
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
70 adminpw="$2"
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
71 shift 2
a85f56207d80 db-setup script: Allow to set passwords manually.
Sascha Wilde <wilde@intevation.de>
parents: 330
diff changeset
72 ;;
463
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 436
diff changeset
73 --metapw)
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 436
diff changeset
74 metapw="$2"
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 436
diff changeset
75 shift 2
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 436
diff changeset
76 ;;
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
77 --demo|-D)
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
78 demo=1
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
79 shift 1
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
80 ;;
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
81 --drop)
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
82 drop=1
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
83 shift 1
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
84 ;;
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
85 --help)
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
86 { usage ; exit 0 ; }
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
87 ;;
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
88 --)
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
89 shift
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
90 break
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
91 ;;
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
92 esac
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
93 done
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
94
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
95
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
96 # Main ------------------------------------------------------------
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
97
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
98 if [[ drop -eq 0 ]] ; then
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
99 # Default operation: create schema
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
100 createdb -p "$port" "$db"
378
88aa790d1437 Do not assume existance of a specific database in setup
Tom Gottfried <tom@intevation.de>
parents: 377
diff changeset
101 psql -q -p "$port" -f "$BASEDIR/roles.sql" -d "$db"
377
220a893318fa Ignore only role creation errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 331
diff changeset
102 psql -qtv ON_ERROR_STOP= -p "$port" -d "$db" \
259
f1280801ba8c db-setup script: Be even more quiet during creation.
Sascha Wilde <wilde@intevation.de>
parents: 256
diff changeset
103 -c "SET client_min_messages TO WARNING;" \
569
ad07846b09d1 Add function to construct isrs from text
Tom Gottfried <tom@intevation.de>
parents: 478
diff changeset
104 -f "$BASEDIR/isrs.sql" \
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
105 -f "$BASEDIR/gemma.sql" \
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
106 -f "$BASEDIR/manage_users.sql" \
377
220a893318fa Ignore only role creation errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 331
diff changeset
107 -f "$BASEDIR/auth.sql"
470
638371a0e557 Do not touch existing roles on database setup
Tom Gottfried <tom@intevation.de>
parents: 465
diff changeset
108
638371a0e557 Do not touch existing roles on database setup
Tom Gottfried <tom@intevation.de>
parents: 465
diff changeset
109 # setup initial login roles with given passwords:
377
220a893318fa Ignore only role creation errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 331
diff changeset
110 psql -qt -p "$port" -d "$db" \
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 470
diff changeset
111 -v adminpw="$adminpw" -v metapw="$metapw" \
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents: 319
diff changeset
112 -f "$BASEDIR/std_login_roles.sql"
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 244
diff changeset
113
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
114 if [[ $demo -eq 1 ]] ; then
377
220a893318fa Ignore only role creation errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 331
diff changeset
115 psql -qv ON_ERROR_STOP= -p "$port" \
831
fa417ff1f355 Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents: 569
diff changeset
116 -f "$BASEDIR/demo-data/default_sysconfig.sql" \
377
220a893318fa Ignore only role creation errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 331
diff changeset
117 -f "$BASEDIR/demo-data/responsibility_areas.sql" \
220a893318fa Ignore only role creation errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 331
diff changeset
118 -f "$BASEDIR/demo-data/users.sql" -d "$db"
220a893318fa Ignore only role creation errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 331
diff changeset
119 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
120 -d "$db"
377
220a893318fa Ignore only role creation errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 331
diff changeset
121
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
122 fi
470
638371a0e557 Do not touch existing roles on database setup
Tom Gottfried <tom@intevation.de>
parents: 465
diff changeset
123
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
124 else
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
125 # Evil mode: drop everything gemma
377
220a893318fa Ignore only role creation errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 331
diff changeset
126 echo "Really drop database '$db' and all gemma roles? [type 'yes']: "
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
127 read a
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
128 if [[ $a == "yes" ]] ; then
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
129 dropdb -p "$port" "$db"
436
8feb64128c34 Fixed install db script to cope with user roles containing whitespace.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 378
diff changeset
130 psql -p $port -A -t -c '\du' | awk -F '|' -v port=$port \
478
3af7ca761f6a Purge password reset role
Tom Gottfried <tom@intevation.de>
parents: 470
diff changeset
131 '$1 "." $3 ~ /waterway_user|waterway_admin|sys_admin|metamorph/ \
436
8feb64128c34 Fixed install db script to cope with user roles containing whitespace.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 378
diff changeset
132 { system("dropuser -p " port " \"" $1 "\"") }'
255
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
133 else
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
134 echo "No harm done."
c36bd39782c1 Added simple --drop to db setup script.
Sascha Wilde <wilde@intevation.de>
parents: 252
diff changeset
135 fi
244
4ebb791b8278 Added simple script to install db schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
136 fi