--- 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);

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1000/99.set_version.sql	Wed Jul 17 12:05:06 2019 +0200
@@ -0,0 +1,1 @@
+INSERT INTO gemma_schema_version(version) VALUES (1000);