Mercurial > gemma

annotate schema/update-db.sh @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
parents c1fa4cf8a3f4
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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
5c0a99b56b2a Fix doc
Tom Gottfried <tom@intevation.de>
parents: 4108
diff changeset
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