# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1531405801 -7200
# Node ID a422471db08a8e08885beb986ef731865cf10f3a
# Parent  61f6c0add24639a7e5070602cba840612265c9bb
Automate running DB-tests with an extra database

run_tests.sh allows to run database tests easily without
affecting other existing databases and on a clean basis.
Because roles are cluster-wide, they cannot be easily droped
and recreated without affecting other databases. Thus,
moved their creation to roles.sql and in passing removed
unnecessary role 'gemma'.

diff -r 61f6c0add246 -r a422471db08a schema/Dockerfile
--- a/schema/Dockerfile	Tue Jul 10 14:30:06 2018 +0200
+++ b/schema/Dockerfile	Thu Jul 12 16:30:01 2018 +0200
@@ -28,7 +28,7 @@
 COPY *.sql ./
 COPY demo-data/*.sql ./
 RUN $PGBIN/pg_ctl start -wo "--config_file=$PGCONF" && \
-    psql -c "CREATE USER gemma PASSWORD 'gemma'" && \
+    psql -f roles.sql && \
     createdb gemma && \
     psql -f gemma.sql -d gemma && \
     psql -f auth.sql -d gemma && \
diff -r 61f6c0add246 -r a422471db08a schema/README
--- a/schema/README	Tue Jul 10 14:30:06 2018 +0200
+++ b/schema/README	Thu Jul 12 16:30:01 2018 +0200
@@ -5,20 +5,20 @@
 $ docker run --name gemma -d -p 54321:5432 -v $PWD:/opt/gemma gemma
 
 Run tests for RLS policies as database superuser (within container) with:
-$ psql -d gemma -Xf tap_tests.sql
+$ ./run_tests.sh
 
 Create ER diagrams with e.g.:
 Auxiliary tables:
 $ postgresql_autodoc -p 54321 -h $dockerhost \
-      -U gemma --password=gemma -t dot -l . -s 'gemma'
+      -d gemma -U sophie --password=so2Phie4 -t dot -l . -s 'gemma'
 $ dot -Tpdf gemma.dot > gemma_auxiliary.pdf
 
 Waterway related tables:
 $ postgresql_autodoc -p 54321 -h $dockerhost \
-      -U gemma --password=gemma -t dot -l . -s 'gemma_waterway'
+      -d gemma -U sophie --password=so2Phie4 -t dot -l . -s 'gemma_waterway'
 $ dot -Tpdf gemma.dot > gemma_waterway.pdf
 
 Fairway/bottleneck related tables:
 $ postgresql_autodoc -p 54321 -h $dockerhost \
-      -U gemma --password=gemma -t dot -l . -s 'gemma_fairway'
+      -d gemma -U sophie --password=so2Phie4 -t dot -l . -s 'gemma_fairway'
 $ dot -Tpdf gemma.dot > gemma_fairway.pdf
diff -r 61f6c0add246 -r a422471db08a schema/auth.sql
--- a/schema/auth.sql	Tue Jul 10 14:30:06 2018 +0200
+++ b/schema/auth.sql	Thu Jul 12 16:30:01 2018 +0200
@@ -9,13 +9,6 @@
 REVOKE CREATE ON SCHEMA public FROM PUBLIC;
 
 --
--- Primary GEMMA roles (SRS table 3)
---
-CREATE ROLE waterway_user;
-CREATE ROLE waterway_admin IN ROLE waterway_user;
-CREATE ROLE sys_admin CREATEROLE BYPASSRLS IN ROLE waterway_admin;
-
---
 -- Privileges for waterway_user
 --
 GRANT USAGE ON SCHEMA gemma, gemma_waterway, gemma_fairway TO waterway_user;
diff -r 61f6c0add246 -r a422471db08a schema/roles.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/roles.sql	Thu Jul 12 16:30:01 2018 +0200
@@ -0,0 +1,6 @@
+--
+-- Primary GEMMA roles
+--
+CREATE ROLE waterway_user;
+CREATE ROLE waterway_admin IN ROLE waterway_user;
+CREATE ROLE sys_admin CREATEROLE BYPASSRLS IN ROLE waterway_admin;
diff -r 61f6c0add246 -r a422471db08a schema/run_tests.sh
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/run_tests.sh	Thu Jul 12 16:30:01 2018 +0200
@@ -0,0 +1,9 @@
+#!/bin/sh -e
+
+dropdb --if-exists gemma_test
+createdb gemma_test
+
+psql -qv ON_ERROR_STOP= -f gemma.sql -d gemma_test
+psql -qv ON_ERROR_STOP= -f auth.sql -d gemma_test
+
+psql -Xf tap_tests.sql -d gemma_test