Mercurial > gemma

annotate schema/update-db.sh @ 3984:2fa052a44300

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Set schema version in update-db.sh, not in extra scripts. Also fixed handling of empty gemma_schema_version table.
author Sascha Wilde <wilde@intevation.de>
date Wed, 17 Jul 2019 13:54:01 +0200
parents 5396581cf203
children 074cf9349c71
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:
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
24 -d, --db=NAME create the database NAME. Default: "gemma"
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
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
101 for d in $BASEDIR/updates/* ; do
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
102 new_ver=$( basename $d )
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
103 if [ -d "$d" -a "$new_ver" -gt $current_ver ] ; then
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
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
106 sql=$( cat `echo "$d/"* | sort -n` )
3984
2fa052a44300 Set schema version in update-db.sh, not in extra scripts.
Sascha Wilde <wilde@intevation.de>
parents: 3979
diff changeset
107 sql+="INSERT INTO gemma_schema_version(version) VALUES ($new_ver);"
2fa052a44300 Set schema version in update-db.sh, not in extra scripts.
Sascha Wilde <wilde@intevation.de>
parents: 3979
diff changeset
108
3979
5396581cf203 Put updates in a single transaction.
Sascha Wilde <wilde@intevation.de>
parents: 3978
diff changeset
109 psql -1 -q -p "$port" -d "$db" -c "$sql"
3978
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
110 fi
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
111 done