Mercurial > gemma

annotate schema/update-db.sh @ 5666:37c2354a6024 clickable-links

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Render links only to known bottlenecks
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 05 Dec 2023 15:34:31 +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