Mercurial > gemma
changeset 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 | 3eab3fc113b7 |
children | 5396581cf203 |
files | schema/gemma.sql schema/update-db.sh schema/updates/0000/01.add_schema_version.sql schema/updates/0000/99.set_version.sql schema/updates/0301/99.set_version.sql schema/updates/1000/01.pwreset.sql schema/updates/1000/99.set_version.sql |
diffstat | 7 files changed, 143 insertions(+), 5 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Wed Jul 17 11:38:24 2019 +0200 +++ b/schema/gemma.sql Wed Jul 17 12:05:06 2019 +0200 @@ -206,6 +206,23 @@ -- +-- GEMA meta data +-- +CREATE TABLE gemma_schema_version ( + version int PRIMARY KEY, + update_date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE OR REPLACE FUNCTION get_schema_version() RETURNS int + LANGUAGE plpgsql + AS $$ + BEGIN + RETURN ( SELECT max(version) FROM gemma_schema_version ); + END; +$$; + + +-- -- GEMMA data --
--- /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
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/0000/01.add_schema_version.sql Wed Jul 17 12:05:06 2019 +0200 @@ -0,0 +1,12 @@ +CREATE TABLE gemma_schema_version ( + version int PRIMARY KEY, + update_date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP +); + +CREATE OR REPLACE FUNCTION get_schema_version() RETURNS int + LANGUAGE plpgsql + AS $$ + BEGIN + RETURN ( SELECT max(version) FROM gemma_schema_version ); + END; +$$;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/0000/99.set_version.sql Wed Jul 17 12:05:06 2019 +0200 @@ -0,0 +1,1 @@ +INSERT INTO gemma_schema_version(version) VALUES (0);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/0301/99.set_version.sql Wed Jul 17 12:05:06 2019 +0200 @@ -0,0 +1,1 @@ +INSERT INTO gemma_schema_version(version) VALUES (301);
--- a/schema/updates/1000/01.pwreset.sql Wed Jul 17 11:38:24 2019 +0200 +++ b/schema/updates/1000/01.pwreset.sql Wed Jul 17 12:05:06 2019 +0200 @@ -1,7 +1,4 @@ -BEGIN; - GRANT INSERT, DELETE, UPDATE ON sys_admin.password_reset_requests TO sys_admin; -ALTER TABLE sys_admin.password_reset_requests ADD CONSTRAINT password_reset_requests_username_key UNIQUE(username); - -COMMIT; +ALTER TABLE sys_admin.password_reset_requests + ADD CONSTRAINT password_reset_requests_username_key UNIQUE(username);