Mercurial > gemma
diff schema/update-db.sh @ 3978:7b7c324b318f
Added database schema versioning and db update script.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 17 Jul 2019 12:05:06 +0200 |
parents | |
children | 5396581cf203 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/update-db.sh Wed Jul 17 12:05:06 2019 +0200 @@ -0,0 +1,109 @@ +#!/bin/bash -e +# This is Free Software under GNU Affero General Public License v >= 3.0 +# without warranty, see README.md and license for details. +# +# SPDX-License-Identifier: AGPL-3.0-or-later +# License-Filename: LICENSES/AGPL-3.0.txt +# +# Copyright (C) 2019 by via donau +# – Österreichische Wasserstraßen-Gesellschaft mbH +# Software engineering by Intevation GmbH +# +# Author(s): +# * Sascha Wilde <wilde@intevation.de> + +ME=`basename "$0"` +BASEDIR=`dirname "$0"` + +usage() +{ + cat <<EOF +$ME [OPTION]... + +Options: + -d, --db=NAME create the database NAME. Default: "gemma" + -p, --port=PORT connect do the postgresql cluster at PORT. + Default is the postgresql standard port 5432 + --help display this help and exit + +EOF +} + +fatal() +{ + echo >&2 "$1" + exit 23 +} + +genpw() +# $1 - length +{ + PW='' + until [ "$(grep '[^[:alnum:]]' <<<$PW)" -a "$(grep '[[:digit:]]' <<<$PW)" ] + do + PW=$(dd count=1 if=/dev/urandom 2>/dev/null \ + | tr -cd '[:alnum:],._!?-' | tail -c "$1") + done + echo "$PW" +} + +# Defaults: + +db=gemma +port=5432 + +# Parse options: + +OPTS=`getopt \ + -l help,db:,port: \ + -o d:p: -n "$ME" -- "$@"` +[ $? -eq 0 ] || { usage ; exit 1 ; } + +eval set -- "$OPTS" + +while true ; do + case "$1" in + --db|-d) + db="$2" + shift 2 + ;; + --port|-p) + port="$2" + shift 2 + ;; + --help) + { usage ; exit 0 ; } + ;; + --) + shift + break + ;; + esac +done + + +get_version() +{ + local ver + if ver=$( psql -qtA -p "$port" -d "$db" \ + -c 'SELECT get_schema_version()' 2>/dev/null ) + then + echo $ver + else + echo '-1' + fi +} + +# Main ------------------------------------------------------------ + +current_ver=$( get_version ) + +for d in $BASEDIR/updates/* ; do + new_ver=$( basename $d ) + if [ -d "$d" -a "$new_ver" -gt $current_ver ] ; then + echo "Running updates for $new_ver ..." + + sql=$( cat `echo "$d/"* | sort -n` ) + psql -q -p "$port" -d "$db" -c "$sql" + fi +done