Mercurial > gemma
annotate schema/update-db.sh @ 5184:445daeefca7b new-fwa
Added controller stub for new fairway availability.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Mon, 23 Mar 2020 15:29:55 +0100 |
parents | c1fa4cf8a3f4 |
children |
rev | line source |
---|---|
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 #!/bin/bash -e |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
2 # This is Free Software under GNU Affero General Public License v >= 3.0 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
3 # without warranty, see README.md and license for details. |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
4 # |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
5 # SPDX-License-Identifier: AGPL-3.0-or-later |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
6 # License-Filename: LICENSES/AGPL-3.0.txt |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
7 # |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 # Copyright (C) 2019 by via donau |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
9 # – Österreichische Wasserstraßen-Gesellschaft mbH |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
10 # Software engineering by Intevation GmbH |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
11 # |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
12 # Author(s): |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 # * Sascha Wilde <wilde@intevation.de> |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
14 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
15 ME=`basename "$0"` |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
16 BASEDIR=`dirname "$0"` |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
17 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
18 usage() |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 { |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 cat <<EOF |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 $ME [OPTION]... |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
22 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
23 Options: |
4115 | 24 -d, --db=NAME update the database NAME. Default: "gemma" |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
25 -p, --port=PORT connect do the postgresql cluster at PORT. |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
26 Default is the postgresql standard port 5432 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
27 --help display this help and exit |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
28 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 EOF |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
30 } |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
31 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
32 fatal() |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
33 { |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
34 echo >&2 "$1" |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
35 exit 23 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
36 } |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
37 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
38 genpw() |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
39 # $1 - length |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
40 { |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
41 PW='' |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
42 until [ "$(grep '[^[:alnum:]]' <<<$PW)" -a "$(grep '[[:digit:]]' <<<$PW)" ] |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
43 do |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
44 PW=$(dd count=1 if=/dev/urandom 2>/dev/null \ |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
45 | tr -cd '[:alnum:],._!?-' | tail -c "$1") |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
46 done |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
47 echo "$PW" |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
48 } |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
49 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
50 # Defaults: |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
51 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
52 db=gemma |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
53 port=5432 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
54 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
55 # Parse options: |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
56 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
57 OPTS=`getopt \ |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
58 -l help,db:,port: \ |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
59 -o d:p: -n "$ME" -- "$@"` |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
60 [ $? -eq 0 ] || { usage ; exit 1 ; } |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
61 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
62 eval set -- "$OPTS" |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
63 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
64 while true ; do |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
65 case "$1" in |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
66 --db|-d) |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
67 db="$2" |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
68 shift 2 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
69 ;; |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
70 --port|-p) |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
71 port="$2" |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
72 shift 2 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
73 ;; |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
74 --help) |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
75 { usage ; exit 0 ; } |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
76 ;; |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
77 --) |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
78 shift |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
79 break |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
80 ;; |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
81 esac |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
82 done |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
83 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
84 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
85 get_version() |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
86 { |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
87 local ver |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
88 if ver=$( psql -qtA -p "$port" -d "$db" \ |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
89 -c 'SELECT get_schema_version()' 2>/dev/null ) |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
90 then |
3984
2fa052a44300
Set schema version in update-db.sh, not in extra scripts.
Sascha Wilde <wilde@intevation.de>
parents:
3979
diff
changeset
|
91 echo ${ver:--1} |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
92 else |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
93 echo '-1' |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
94 fi |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
95 } |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
96 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
97 # Main ------------------------------------------------------------ |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
98 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
99 current_ver=$( get_version ) |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
100 |
4108
6ee5523967ec
Fixed update-db.sh: guarantee order of update scripts.
Sascha Wilde <wilde@intevation.de>
parents:
4004
diff
changeset
|
101 for d in "$BASEDIR"/updates/* ; do |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
102 new_ver=$( basename $d ) |
3995
074cf9349c71
Fixed schema update-script to ignore text files.
Sascha Wilde <wilde@intevation.de>
parents:
3984
diff
changeset
|
103 if [ -d "$d" ] && [ "$new_ver" -gt $current_ver ] ; then |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
104 echo "Running updates for $new_ver ..." |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
105 |
4108
6ee5523967ec
Fixed update-db.sh: guarantee order of update scripts.
Sascha Wilde <wilde@intevation.de>
parents:
4004
diff
changeset
|
106 file_args="" |
4661
c1fa4cf8a3f4
Added an SQL script to be manually executed after the 1302 update.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4115
diff
changeset
|
107 for f in "$d"/*.sql ; do |
4108
6ee5523967ec
Fixed update-db.sh: guarantee order of update scripts.
Sascha Wilde <wilde@intevation.de>
parents:
4004
diff
changeset
|
108 file_args+=" -f $f" |
6ee5523967ec
Fixed update-db.sh: guarantee order of update scripts.
Sascha Wilde <wilde@intevation.de>
parents:
4004
diff
changeset
|
109 done |
6ee5523967ec
Fixed update-db.sh: guarantee order of update scripts.
Sascha Wilde <wilde@intevation.de>
parents:
4004
diff
changeset
|
110 |
6ee5523967ec
Fixed update-db.sh: guarantee order of update scripts.
Sascha Wilde <wilde@intevation.de>
parents:
4004
diff
changeset
|
111 psql -1qv ON_ERROR_STOP= -p "$port" -d "$db" $file_args \ |
4004
79adff625ed8
Let psql treat each schema update script as a file
Tom Gottfried <tom@intevation.de>
parents:
3995
diff
changeset
|
112 -c "INSERT INTO gemma_schema_version(version) VALUES ($new_ver)" |
3984
2fa052a44300
Set schema version in update-db.sh, not in extra scripts.
Sascha Wilde <wilde@intevation.de>
parents:
3979
diff
changeset
|
113 |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
114 fi |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
115 done |