[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[libeufin] 01/02: DB migration building blocks.
|
From: |
gnunet |
|
Subject: |
[libeufin] 01/02: DB migration building blocks. |
|
Date: |
Tue, 13 Jun 2023 14:57:28 +0200 |
This is an automated email from the git hooks/post-receive script.
ms pushed a commit to branch master
in repository libeufin.
commit 22a2824ffcda97e8a734fb4bef0f4baa150c1721
Author: MS <ms@taler.net>
AuthorDate: Tue Jun 13 14:52:16 2023 +0200
DB migration building blocks.
---
Makefile | 15 +-
contrib/indent-sql-sh | 39 +++++
contrib/libeufin-load-sql | 91 +++++++++++
database-versioning/nexus-0001.sql | 241 ++++++++++++++++++++++++++++
database-versioning/sandbox-0001.sql | 250 ++++++++++++++++++++++++++++++
database-versioning/versioning.sql | 293 +++++++++++++++++++++++++++++++++++
util/src/main/kotlin/DB.kt | 12 ++
util/src/main/kotlin/exec.kt | 33 ++++
8 files changed, 967 insertions(+), 7 deletions(-)
diff --git a/Makefile b/Makefile
index 6fdbfaff..1d96dac8 100644
--- a/Makefile
+++ b/Makefile
@@ -3,7 +3,7 @@ include build-system/config.mk
escaped_pwd = $(shell pwd | sed 's/\//\\\//g')
all: assemble
-install: install-nexus install-sandbox install-cli
+install: install-nexus install-sandbox install-cli install-db-versioning
git-archive-all =
./build-system/taler-build-scripts/archive-with-submodules/git_archive_all.py
git_tag=$(shell git describe --tags)
gradle_version=$(shell ./gradlew -q libeufinVersion)
@@ -35,7 +35,6 @@ get-spa:
deb: exec-arch copy-spa
@dpkg-buildpackage -rfakeroot -b -uc -us
-
.PHONY: install-sandbox
install-sandbox:
@./gradlew -q -Pprefix=$(prefix) sandbox:installToPrefix; cd ..
@@ -49,6 +48,13 @@ install-cli:
@./gradlew -q replaceVersionCli
@install -D cli/bin/libeufin-cli $(prefix)/bin
+.PHONY: install-db-versioning
+install-db-versioning:
+ $(eval LOAD_SQL_SCRIPT_NAME := libeufin-load-sql)
+ @sed "s|__STATIC_PATCHES_LOCATION__|$(prefix)/share/libeufin/sql|" <
contrib/$(LOAD_SQL_SCRIPT_NAME) > build/$(LOAD_SQL_SCRIPT_NAME)
+ @install -D database-versioning/*.sql -t $(prefix)/share/libeufin/sql
+ @install -D build/$(LOAD_SQL_SCRIPT_NAME) -t $(prefix)/bin
+
.PHONY: assemble
assemble:
@./gradlew assemble
@@ -61,8 +67,3 @@ check:
check-cli:
@cd ./cli/tests && ./circuit_test.sh
@cd ./cli/tests && ./debit_test.sh
-
-
-# .PHONY: parse
-# parse:
-# @cd parsing-tests; py.test -s checks.py
diff --git a/contrib/indent-sql-sh b/contrib/indent-sql-sh
new file mode 100755
index 00000000..d702859d
--- /dev/null
+++ b/contrib/indent-sql-sh
@@ -0,0 +1,39 @@
+#!/bin/bash
+
+set -eu
+
+# This script indents the output of Exposed SQL logger.
+
+# Usage: ./indent.sh filename
+
+# Remove leading "^SQL: " that Exposed uses.
+crop_leading_sql () {
+ sed 's/^SQL: //'
+}
+
+# Inserts new line & two spaces before the first "("
+# and last ")", and before each comma. Only triggers on
+# "CREATE TABLE"-lines.
+indent_create_table () {
+ sed '/^CREATE/s/, /,/g' \
+ | sed '/^CREATE/s/\(,\|)$\)/\n \1/g' \
+ | sed '/^CREATE/s/(/\n (/'
+}
+
+
+# Inserts new line & two spaces before each "ALTER TABLE"
+# statement
+indent_alter_table () {
+ sed 's/^ALTER TABLE \(.*\)/ALTER TABLE\n \1/'
+}
+
+# Inserts a blank line after between each CREATE/ALTER TABLE statement.
+blank_line_after_statement () {
+ sed '/^CREATE TABLE/s/\(.*\)/\n\1/' \
+ | sed '/^ALTER TABLE/s/\(.*\)/\n\1/'
+}
+
+crop_leading_sql < $1 \
+ | indent_create_table \
+ | indent_alter_table \
+ | blank_line_after_statement
diff --git a/contrib/libeufin-load-sql b/contrib/libeufin-load-sql
new file mode 100755
index 00000000..eaf3e534
--- /dev/null
+++ b/contrib/libeufin-load-sql
@@ -0,0 +1,91 @@
+#!/bin/bash
+
+set -eu
+
+# The only CLI argument is 'nexus' or 'sandbox',
+# indicating which service will get its database prepared.
+
+fail () {
+ echo $1
+ exit 1
+}
+
+run_sql_file () {
+ # -q doesn't hide all the output, hence the
+ # redirection to /dev/null.
+ psql -d $DB_CONNECTION \
+ -q \
+ -f $1 \
+ --set ON_ERROR_STOP=1 > /dev/null
+}
+
+# The real check happens (by the caller)
+# by checking the returned text.
+check_patch_applied () {
+ psql -d $DB_CONNECTION \
+ -t \
+ -c "SELECT applied_by FROM _v.patches WHERE patch_name = '$1' LIMIT 1"
+}
+
+# Iterates over the .sql migration files and applies
+# the new ones.
+iterate_over_patches () {
+ if test "$1" != sandbox -a "$1" != nexus; then
+ fail "iterate_over_patches: only 'sandbox' and 'nexus' are acceptable
arguments."
+ fi
+ component="$1"
+ cd $PATCHES_LOCATION
+ for patch_filename in $(ls -1 -v $component-[0-9][0-9][0-9][0-9].sql); do
+ patch_name=$(echo $patch_filename | cut -f1 -d.)
+ echo Checking patch: "$patch_name"
+ maybe_applied=$(check_patch_applied "$patch_name")
+ if test -n "$maybe_applied"; then continue; fi
+ # patch not applied, apply it.
+ echo Patch $patch_name not applied, applying it.
+ run_sql_file $patch_filename
+ done
+ cd - > /dev/null # cd to previous location.
+}
+while getopts ":d:l:h" OPTION; do
+ case "$OPTION" in
+ d)
+ DB_CONNECTION="$OPTARG"
+ ;;
+ l)
+ PATCHES_LOCATION="$OPTARG"
+ ;;
+ s)
+ SERVICE="${OPTARG:-}"
+ ;;
+ h)
+ echo Usage: libeufin-load-sql OPTIONS
+ echo
+ echo 'Supported options:'
+ echo " -s SERVICE -- specify 'sandbox' or 'nexus', according to which
set of tables are to be setup. If missing both sets will be setup on the same
database."
+ echo ' -d DB_CONN -- required. Pass DB_CONN as the postgres
connection string. Passed verbatim to Psql'
+ echo ' -l LOC -- required. Pass LOC as the SQL files location.
Typically $prefix/share/libeufin/sql'
+ echo ' -h -- print this help'
+ exit 0
+ ;;
+ ?)
+ fail 'Unrecognized command line option'
+ ;;
+ esac
+done
+
+# Checking required options.
+if test -z "${PATCHES_LOCATION:-}"; then
+ # This value is substituted by GNU make at installation time.
+ PATCHES_LOCATION=__STATIC_PATCHES_LOCATION__
+fi
+if test -z "${DB_CONNECTION:-}"; then
+ fail "Required option '-d' was missing."
+fi
+
+run_sql_file "$PATCHES_LOCATION/versioning.sql"
+if test -z "${SERVICE:-}"; then # both table sets.
+ iterate_over_patches sandbox
+ iterate_over_patches nexus
+ exit 0
+fi
+iterate_over_patches $SERVICE # helper checks the argument sanity.
diff --git a/database-versioning/nexus-0001.sql
b/database-versioning/nexus-0001.sql
new file mode 100644
index 00000000..c18a909f
--- /dev/null
+++ b/database-versioning/nexus-0001.sql
@@ -0,0 +1,241 @@
+-- Compatible with LibEuFin version: 1fe2687aaf696c8566367fe7ed082f1d78e6b78d
+
+BEGIN;
+
+SELECT _v.register_patch('nexus-0001', NULL, NULL);
+
+CREATE TABLE IF NOT EXISTS nexususers
+ (id BIGSERIAL PRIMARY KEY
+ ,username TEXT NOT NULL
+ ,"password" TEXT NOT NULL
+ ,superuser BOOLEAN NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS nexusbankconnections
+ (id BIGSERIAL PRIMARY KEY
+ ,"connectionId" TEXT NOT NULL
+ ,"type" TEXT NOT NULL
+ ,dialect TEXT NULL
+ ,"user" BIGINT NOT NULL
+ ,CONSTRAINT fk_nexusbankconnections_user_id FOREIGN KEY ("user") REFERENCES
nexususers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS xlibeufinbankusers
+ (id BIGSERIAL PRIMARY KEY
+ ,username TEXT NOT NULL
+ ,"password" TEXT NOT NULL
+ ,"baseUrl" TEXT NOT NULL
+ ,"nexusBankConnection" BIGINT NOT NULL
+ ,CONSTRAINT fk_xlibeufinbankusers_nexusbankconnection_id FOREIGN KEY
("nexusBankConnection") REFERENCES nexusbankconnections(id) ON DELETE RESTRICT
ON UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS nexusscheduledtasks
+ (id BIGSERIAL PRIMARY KEY
+ ,"resourceType" TEXT NOT NULL
+ ,"resourceId" TEXT NOT NULL
+ ,"taskName" TEXT NOT NULL
+ ,"taskType" TEXT NOT NULL
+ ,"taskCronspec" TEXT NOT NULL
+ ,"taskParams" TEXT NOT NULL
+ ,"nextScheduledExecutionSec" BIGINT NULL
+ ,"lastScheduledExecutionSec" BIGINT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS nexusbankaccounts
+ (id BIGSERIAL PRIMARY KEY
+ ,"bankAccountId" TEXT NOT NULL
+ ,"accountHolder" TEXT NOT NULL
+ ,iban TEXT NOT NULL
+ ,"bankCode" TEXT NOT NULL
+ ,"defaultBankConnection" BIGINT NULL
+ ,"lastStatementCreationTimestamp" BIGINT NULL
+ ,"lastReportCreationTimestamp" BIGINT NULL
+ ,"lastNotificationCreationTimestamp" BIGINT NULL
+ ,"highestSeenBankMessageSerialId" BIGINT NOT NULL
+ ,pain001counter BIGINT DEFAULT 1 NOT NULL
+ ,CONSTRAINT fk_nexusbankaccounts_defaultbankconnection_id FOREIGN KEY
("defaultBankConnection") REFERENCES nexusbankconnections(id) ON DELETE
RESTRICT ON UPDATE RESTRICT
+ );
+
+ALTER TABLE
+ nexusbankaccounts ADD CONSTRAINT nexusbankaccounts_bankaccountid_unique
UNIQUE ("bankAccountId");
+
+CREATE TABLE IF NOT EXISTS nexusbanktransactions
+ (id BIGSERIAL PRIMARY KEY
+ ,"accountTransactionId" TEXT NOT NULL
+ ,"bankAccount" BIGINT NOT NULL
+ ,"creditDebitIndicator" TEXT NOT NULL
+ ,currency TEXT NOT NULL
+ ,amount TEXT NOT NULL
+ ,status VARCHAR(16) NOT NULL
+ ,"updatedBy" BIGINT NULL
+ ,"transactionJson" TEXT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS paymentinitiations
+ (id BIGSERIAL PRIMARY KEY
+ ,"bankAccount" BIGINT NOT NULL
+ ,"preparationDate" BIGINT NOT NULL
+ ,"submissionDate" BIGINT NULL
+ ,"sum" TEXT NOT NULL
+ ,currency TEXT NOT NULL
+ ,"endToEndId" TEXT NOT NULL
+ ,"paymentInformationId" TEXT NOT NULL
+ ,"instructionId" TEXT NOT NULL
+ ,subject TEXT NOT NULL
+ ,"creditorIban" TEXT NOT NULL
+ ,"creditorBic" TEXT NULL
+ ,"creditorName" TEXT NOT NULL
+ ,submitted BOOLEAN DEFAULT false NOT NULL
+ ,invalid BOOLEAN NULL
+ ,"messageId" TEXT NOT NULL
+ ,"rawConfirmation" BIGINT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS nexusebicssubscribers
+ (id BIGSERIAL PRIMARY KEY
+ ,"ebicsURL" TEXT NOT NULL
+ ,"hostID" TEXT NOT NULL
+ ,"partnerID" TEXT NOT NULL
+ ,"userID" TEXT NOT NULL
+ ,"systemID" TEXT NULL
+ ,"signaturePrivateKey" bytea NOT NULL
+ ,"encryptionPrivateKey" bytea NOT NULL
+ ,"authenticationPrivateKey" bytea NOT NULL
+ ,"bankEncryptionPublicKey" bytea NULL
+ ,"bankAuthenticationPublicKey" bytea NULL
+ ,"nexusBankConnection" BIGINT NOT NULL
+ ,"ebicsIniState" VARCHAR(16) NOT NULL
+ ,"ebicsHiaState" VARCHAR(16) NOT NULL
+ ,CONSTRAINT fk_nexusebicssubscribers_nexusbankconnection_id FOREIGN KEY
("nexusBankConnection") REFERENCES nexusbankconnections(id) ON DELETE RESTRICT
ON UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS nexusbankbalances
+ (id BIGSERIAL PRIMARY KEY
+ ,balance TEXT NOT NULL
+ ,"creditDebitIndicator" TEXT NOT NULL
+ ,"bankAccount" BIGINT NOT NULL
+ ,"date" TEXT NOT NULL
+ ,CONSTRAINT fk_nexusbankbalances_bankaccount_id FOREIGN KEY ("bankAccount")
REFERENCES nexusbankaccounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS anastasisincomingpayments
+ (id BIGSERIAL PRIMARY KEY
+ ,payment BIGINT NOT NULL
+ ,subject TEXT NOT NULL
+ ,"timestampMs" BIGINT NOT NULL
+ ,"incomingPaytoUri" TEXT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS talerincomingpayments
+ (id BIGSERIAL PRIMARY KEY
+ ,payment BIGINT NOT NULL
+ ,"reservePublicKey" TEXT NOT NULL
+ ,"timestampMs" BIGINT NOT NULL
+ ,"incomingPaytoUri" TEXT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS facades
+ (id BIGSERIAL PRIMARY KEY
+ ,"facadeName" TEXT NOT NULL
+ ,"type" TEXT NOT NULL
+ ,creator BIGINT NOT NULL
+ ,CONSTRAINT fk_facades_creator_id FOREIGN KEY (creator) REFERENCES
nexususers(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ );
+
+ALTER TABLE
+ facades ADD CONSTRAINT facades_facadename_unique UNIQUE ("facadeName");
+
+CREATE TABLE IF NOT EXISTS talerrequestedpayments
+ (id BIGSERIAL PRIMARY KEY
+ ,facade BIGINT NOT NULL
+ ,payment BIGINT NOT NULL
+ ,"requestUid" TEXT NOT NULL
+ ,amount TEXT NOT NULL
+ ,"exchangeBaseUrl" TEXT NOT NULL
+ ,wtid TEXT NOT NULL
+ ,"creditAccount" TEXT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS facadestate
+ (id BIGSERIAL PRIMARY KEY
+ ,"bankAccount" TEXT NOT NULL
+ ,"bankConnection" TEXT NOT NULL
+ ,currency TEXT NOT NULL
+ ,"reserveTransferLevel" TEXT NOT NULL
+ ,facade BIGINT NOT NULL
+ ,"highestSeenMessageSerialId" BIGINT DEFAULT 0 NOT NULL
+ ,CONSTRAINT fk_facadestate_facade_id FOREIGN KEY (facade) REFERENCES
facades(id) ON DELETE CASCADE ON UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS talerinvalidincomingpayments
+ (id BIGSERIAL PRIMARY KEY
+ ,payment BIGINT NOT NULL
+ ,"timestampMs" BIGINT NOT NULL
+ ,refunded BOOLEAN DEFAULT false NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS nexusbankmessages
+ (id BIGSERIAL PRIMARY KEY
+ ,"bankConnection" BIGINT NOT NULL
+ ,message bytea NOT NULL
+ ,"messageId" TEXT NULL
+ ,"fetchLevel" VARCHAR(16) NOT NULL
+ ,errors BOOLEAN DEFAULT false NOT NULL
+ ,CONSTRAINT fk_nexusbankmessages_bankconnection_id FOREIGN KEY
("bankConnection") REFERENCES nexusbankconnections(id) ON DELETE RESTRICT ON
UPDATE RESTRICT
+ );
+
+CREATE TABLE IF NOT EXISTS offeredbankaccounts
+ (id BIGSERIAL PRIMARY KEY
+ ,"offeredAccountId" TEXT NOT NULL
+ ,"bankConnection" BIGINT NOT NULL
+ ,iban TEXT NOT NULL
+ ,"bankCode" TEXT NOT NULL
+ ,"holderName" TEXT NOT NULL
+ ,imported BIGINT NULL
+ ,CONSTRAINT fk_offeredbankaccounts_bankconnection_id FOREIGN KEY
("bankConnection") REFERENCES nexusbankconnections(id) ON DELETE RESTRICT ON
UPDATE RESTRICT
+ ,CONSTRAINT fk_offeredbankaccounts_imported_id FOREIGN KEY (imported)
REFERENCES nexusbankaccounts(id) ON DELETE RESTRICT ON UPDATE RESTRICT
+ );
+
+ALTER TABLE
+ offeredbankaccounts ADD CONSTRAINT
offeredbankaccounts_offeredaccountid_bankconnection_unique UNIQUE
("offeredAccountId", "bankConnection");
+
+CREATE TABLE IF NOT EXISTS nexuspermissions
+ (id BIGSERIAL PRIMARY KEY
+ ,"resourceType" TEXT NOT NULL
+ ,"resourceId" TEXT NOT NULL
+ ,"subjectType" TEXT NOT NULL
+ ,"subjectName" TEXT NOT NULL
+ ,"permissionName" TEXT NOT NULL
+ );
+
+ALTER TABLE
+ nexuspermissions ADD CONSTRAINT
nexuspermissions_resourcetype_resourceid_subjecttype_subjectnam UNIQUE
("resourceType", "resourceId", "subjectType", "subjectName", "permissionName");
+
+ALTER TABLE
+ nexusbanktransactions ADD CONSTRAINT fk_nexusbanktransactions_bankaccount_id
FOREIGN KEY ("bankAccount") REFERENCES nexusbankaccounts(id) ON DELETE RESTRICT
ON UPDATE RESTRICT;
+
+ALTER TABLE
+ nexusbanktransactions ADD CONSTRAINT fk_nexusbanktransactions_updatedby_id
FOREIGN KEY ("updatedBy") REFERENCES nexusbanktransactions(id) ON DELETE
RESTRICT ON UPDATE RESTRICT;
+
+ALTER TABLE
+ paymentinitiations ADD CONSTRAINT fk_paymentinitiations_bankaccount_id
FOREIGN KEY ("bankAccount") REFERENCES nexusbankaccounts(id) ON DELETE RESTRICT
ON UPDATE RESTRICT;
+
+ALTER TABLE
+ paymentinitiations ADD CONSTRAINT fk_paymentinitiations_rawconfirmation_id
FOREIGN KEY ("rawConfirmation") REFERENCES nexusbanktransactions(id) ON DELETE
RESTRICT ON UPDATE RESTRICT;
+
+ALTER TABLE
+ anastasisincomingpayments ADD CONSTRAINT
fk_anastasisincomingpayments_payment_id FOREIGN KEY (payment) REFERENCES
nexusbanktransactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT;
+
+ALTER TABLE
+ talerincomingpayments ADD CONSTRAINT fk_talerincomingpayments_payment_id
FOREIGN KEY (payment) REFERENCES nexusbanktransactions(id) ON DELETE RESTRICT
ON UPDATE RESTRICT;
+
+ALTER TABLE
+ talerrequestedpayments ADD CONSTRAINT fk_talerrequestedpayments_facade_id
FOREIGN KEY (facade) REFERENCES facades(id) ON DELETE RESTRICT ON UPDATE
RESTRICT;
+
+ALTER TABLE
+ talerrequestedpayments ADD CONSTRAINT fk_talerrequestedpayments_payment_id
FOREIGN KEY (payment) REFERENCES paymentinitiations(id) ON DELETE RESTRICT ON
UPDATE RESTRICT;
+
+ALTER TABLE
+ talerinvalidincomingpayments ADD CONSTRAINT
fk_talerinvalidincomingpayments_payment_id FOREIGN KEY (payment) REFERENCES
nexusbanktransactions(id) ON DELETE RESTRICT ON UPDATE RESTRICT;
+
+COMMIT
diff --git a/database-versioning/sandbox-0001.sql
b/database-versioning/sandbox-0001.sql
new file mode 100644
index 00000000..b6ed53ef
--- /dev/null
+++ b/database-versioning/sandbox-0001.sql
@@ -0,0 +1,250 @@
+-- Compatible with LibEuFin version: 1fe2687aaf696c8566367fe7ed082f1d78e6b78d
+
+BEGIN;
+
+SELECT _v.register_patch('sandbox-0001', NULL, NULL);
+
+CREATE TABLE IF NOT EXISTS demobankconfigs
+ (id BIGSERIAL PRIMARY KEY
+ ,hostname TEXT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS bankaccounts
+ (id SERIAL PRIMARY KEY
+ ,iban TEXT NOT NULL
+ ,bic TEXT DEFAULT 'SANDBOXX' NOT NULL
+ ,"label" TEXT NOT NULL
+ ,"owner" TEXT NOT NULL
+ ,"isPublic" BOOLEAN DEFAULT false NOT NULL
+ ,"demoBank" BIGINT NOT NULL
+ ,"lastTransaction" BIGINT NULL
+ ,"lastFiatSubmission" BIGINT NULL
+ ,"lastFiatFetch" TEXT DEFAULT '0' NOT NULL
+ );
+
+ALTER TABLE
+ bankaccounts ADD CONSTRAINT accountLabelIndex UNIQUE ("label");
+
+CREATE TABLE IF NOT EXISTS bankaccounttransactions
+ (id BIGSERIAL PRIMARY KEY
+ ,"creditorIban" TEXT NOT NULL
+ ,"creditorBic" TEXT NULL
+ ,"creditorName" TEXT NOT NULL
+ ,"debtorIban" TEXT NOT NULL
+ ,"debtorBic" TEXT NULL
+ ,"debtorName" TEXT NOT NULL
+ ,subject TEXT NOT NULL
+ ,amount TEXT NOT NULL
+ ,currency TEXT NOT NULL
+ ,"date" BIGINT NOT NULL
+ ,"accountServicerReference" TEXT NOT NULL
+ ,"pmtInfId" TEXT NULL
+ ,"EndToEndId" TEXT NULL
+ ,direction TEXT NOT NULL
+ ,account INT NOT NULL
+ ,demobank BIGINT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS cashoutsubmissions
+ (id BIGSERIAL PRIMARY KEY
+ ,"localTransaction" BIGINT NOT NULL
+ ,"maybeNexusResponse" TEXT NULL
+ ,"submissionTime" BIGINT NULL
+ );
+
+ALTER TABLE
+ cashoutsubmissions ADD CONSTRAINT cashoutsubmissions_localtransaction_unique
UNIQUE ("localTransaction");
+
+CREATE TABLE IF NOT EXISTS demobankconfigpairs
+ (id BIGSERIAL PRIMARY KEY
+ ,"demobankName" TEXT NOT NULL
+ ,"configKey" TEXT NOT NULL
+ ,"configValue" TEXT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS ebicssubscribers
+ (id SERIAL PRIMARY KEY
+ ,"userID" TEXT NOT NULL
+ ,"partnerID" TEXT NOT NULL
+ ,"systemID" TEXT NULL
+ ,"hostID" TEXT NOT NULL
+ ,"signatureKey" INT NULL
+ ,"encryptionKey" INT NULL
+ ,"authorizationKey" INT NULL
+ ,"nextOrderID" INT NOT NULL
+ ,"state" INT NOT NULL
+ ,"bankAccount" INT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS ebicssubscriberpublickeys
+ (id SERIAL PRIMARY KEY
+ ,"rsaPublicKey" bytea NOT NULL
+ ,"state" INT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS ebicshosts
+ (id SERIAL PRIMARY KEY
+ ,"hostID" TEXT NOT NULL
+ ,"ebicsVersion" TEXT NOT NULL
+ ,"signaturePrivateKey" bytea NOT NULL
+ ,"encryptionPrivateKey" bytea NOT NULL
+ ,"authenticationPrivateKey" bytea NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS ebicsdownloadtransactions
+ ("transactionID" TEXT NOT NULL
+ ,"orderType" TEXT NOT NULL
+ ,host INT NOT NULL
+ ,subscriber INT NOT NULL
+ ,"encodedResponse" TEXT NOT NULL
+ ,"transactionKeyEnc" bytea NOT NULL
+ ,"numSegments" INT NOT NULL
+ ,"segmentSize" INT NOT NULL
+ ,"receiptReceived" BOOLEAN NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS ebicsuploadtransactions
+ ("transactionID" TEXT NOT NULL
+ ,"orderType" TEXT NOT NULL
+ ,"orderID" TEXT NOT NULL
+ ,host INT NOT NULL
+ ,subscriber INT NOT NULL
+ ,"numSegments" INT NOT NULL
+ ,"lastSeenSegment" INT NOT NULL
+ ,"transactionKeyEnc" bytea NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS ebicsuploadtransactionchunks
+ ("transactionID" TEXT NOT NULL
+ ,"chunkIndex" INT NOT NULL
+ ,"chunkContent" bytea NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS ebicsordersignatures
+ (id SERIAL PRIMARY KEY
+ ,"orderID" TEXT NOT NULL
+ ,"orderType" TEXT NOT NULL
+ ,"partnerID" TEXT NOT NULL
+ ,"userID" TEXT NOT NULL
+ ,"signatureAlgorithm" TEXT NOT NULL
+ ,"signatureValue" bytea NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS bankaccountfreshtransactions
+ (id BIGSERIAL PRIMARY KEY
+ ,"transaction" BIGINT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS bankaccountreports
+ (id SERIAL PRIMARY KEY
+ ,"reportId" TEXT NOT NULL
+ ,"creationTime" BIGINT NOT NULL
+ ,"xmlMessage" TEXT NOT NULL
+ ,"bankAccount" INT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS bankaccountstatements
+ (id SERIAL PRIMARY KEY
+ ,"statementId" TEXT NOT NULL
+ ,"creationTime" BIGINT NOT NULL
+ ,"xmlMessage" TEXT NOT NULL
+ ,"bankAccount" INT NOT NULL
+ ,"balanceClbd" TEXT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS talerwithdrawals
+ (id BIGSERIAL PRIMARY KEY
+ ,wopid uuid NOT NULL
+ ,amount TEXT NOT NULL
+ ,"selectionDone" BOOLEAN DEFAULT false NOT NULL
+ ,aborted BOOLEAN DEFAULT false NOT NULL
+ ,"confirmationDone" BOOLEAN DEFAULT false NOT NULL
+ ,"reservePub" TEXT NULL
+ ,"selectedExchangePayto" TEXT NULL
+ ,"walletBankAccount" INT NOT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS demobankcustomers
+ (id BIGSERIAL PRIMARY KEY
+ ,username TEXT NOT NULL
+ ,"passwordHash" TEXT NOT NULL
+ ,"name" TEXT NULL
+ ,email TEXT NULL
+ ,phone TEXT NULL
+ ,cashout_address TEXT NULL
+ );
+
+CREATE TABLE IF NOT EXISTS cashoutoperations
+ (id BIGSERIAL PRIMARY KEY
+ ,uuid uuid NOT NULL
+ ,"amountDebit" TEXT NOT NULL
+ ,"amountCredit" TEXT NOT NULL
+ ,"buyAtRatio" TEXT NOT NULL
+ ,"buyInFee" TEXT NOT NULL
+ ,"sellAtRatio" TEXT NOT NULL
+ ,"sellOutFee" TEXT NOT NULL
+ ,subject TEXT NOT NULL
+ ,"creationTime" BIGINT NOT NULL
+ ,"confirmationTime" BIGINT NULL
+ ,"tanChannel" INT NOT NULL
+ ,account TEXT NOT NULL
+ ,"cashoutAddress" TEXT NOT NULL
+ ,tan TEXT NOT NULL
+ ,status INT DEFAULT 1 NOT NULL
+ );
+
+ALTER TABLE
+ bankaccounts ADD CONSTRAINT fk_bankaccounts_demobank_id FOREIGN KEY
("demoBank") REFERENCES demobankconfigs(id) ON DELETE RESTRICT ON UPDATE
RESTRICT;
+
+ALTER TABLE
+ bankaccounts ADD CONSTRAINT fk_bankaccounts_lasttransaction_id FOREIGN KEY
("lastTransaction") REFERENCES bankaccounttransactions(id) ON DELETE RESTRICT
ON UPDATE RESTRICT;
+
+ALTER TABLE
+ bankaccounts ADD CONSTRAINT fk_bankaccounts_lastfiatsubmission_id FOREIGN
KEY ("lastFiatSubmission") REFERENCES bankaccounttransactions(id) ON DELETE
RESTRICT ON UPDATE RESTRICT;
+
+ALTER TABLE
+ bankaccounttransactions ADD CONSTRAINT fk_bankaccounttransactions_account_id
FOREIGN KEY (account) REFERENCES bankaccounts(id) ON DELETE CASCADE ON UPDATE
RESTRICT;
+
+ALTER TABLE
+ bankaccounttransactions ADD CONSTRAINT
fk_bankaccounttransactions_demobank_id FOREIGN KEY (demobank) REFERENCES
demobankconfigs(id) ON DELETE RESTRICT ON UPDATE RESTRICT;
+
+ALTER TABLE
+ cashoutsubmissions ADD CONSTRAINT fk_cashoutsubmissions_localtransaction_id
FOREIGN KEY ("localTransaction") REFERENCES bankaccounttransactions(id) ON
DELETE RESTRICT ON UPDATE RESTRICT;
+
+ALTER TABLE
+ ebicssubscribers ADD CONSTRAINT fk_ebicssubscribers_signaturekey_id FOREIGN
KEY ("signatureKey") REFERENCES ebicssubscriberpublickeys(id) ON DELETE
RESTRICT ON UPDATE RESTRICT;
+
+ALTER TABLE
+ ebicssubscribers ADD CONSTRAINT fk_ebicssubscribers_encryptionkey_id FOREIGN
KEY ("encryptionKey") REFERENCES ebicssubscriberpublickeys(id) ON DELETE
RESTRICT ON UPDATE RESTRICT;
+
+ALTER TABLE
+ ebicssubscribers ADD CONSTRAINT fk_ebicssubscribers_authorizationkey_id
FOREIGN KEY ("authorizationKey") REFERENCES ebicssubscriberpublickeys(id) ON
DELETE RESTRICT ON UPDATE RESTRICT;
+
+ALTER TABLE
+ ebicssubscribers ADD CONSTRAINT fk_ebicssubscribers_bankaccount_id FOREIGN
KEY ("bankAccount") REFERENCES bankaccounts(id) ON DELETE CASCADE ON UPDATE
RESTRICT;
+
+ALTER TABLE
+ ebicsdownloadtransactions ADD CONSTRAINT
fk_ebicsdownloadtransactions_host_id FOREIGN KEY (host) REFERENCES
ebicshosts(id) ON DELETE RESTRICT ON UPDATE RESTRICT;
+
+ALTER TABLE
+ ebicsdownloadtransactions ADD CONSTRAINT
fk_ebicsdownloadtransactions_subscriber_id FOREIGN KEY (subscriber) REFERENCES
ebicssubscribers(id) ON DELETE RESTRICT ON UPDATE RESTRICT;
+
+ALTER TABLE
+ ebicsuploadtransactions ADD CONSTRAINT fk_ebicsuploadtransactions_host_id
FOREIGN KEY (host) REFERENCES ebicshosts(id) ON DELETE RESTRICT ON UPDATE
RESTRICT;
+
+ALTER TABLE
+ ebicsuploadtransactions ADD CONSTRAINT
fk_ebicsuploadtransactions_subscriber_id FOREIGN KEY (subscriber) REFERENCES
ebicssubscribers(id) ON DELETE RESTRICT ON UPDATE RESTRICT;
+
+ALTER TABLE
+ bankaccountfreshtransactions ADD CONSTRAINT
fk_bankaccountfreshtransactions_transaction_id FOREIGN KEY ("transaction")
REFERENCES bankaccounttransactions(id) ON DELETE CASCADE ON UPDATE RESTRICT;
+
+ALTER TABLE
+ bankaccountreports ADD CONSTRAINT fk_bankaccountreports_bankaccount_id
FOREIGN KEY ("bankAccount") REFERENCES bankaccounts(id) ON DELETE RESTRICT ON
UPDATE RESTRICT;
+
+ALTER TABLE
+ bankaccountstatements ADD CONSTRAINT fk_bankaccountstatements_bankaccount_id
FOREIGN KEY ("bankAccount") REFERENCES bankaccounts(id) ON DELETE RESTRICT ON
UPDATE RESTRICT;
+
+ALTER TABLE
+ talerwithdrawals ADD CONSTRAINT fk_talerwithdrawals_walletbankaccount_id
FOREIGN KEY ("walletBankAccount") REFERENCES bankaccounts(id) ON DELETE
RESTRICT ON UPDATE RESTRICT;
+
+COMMIT;
diff --git a/database-versioning/versioning.sql
b/database-versioning/versioning.sql
new file mode 100644
index 00000000..98e7f661
--- /dev/null
+++ b/database-versioning/versioning.sql
@@ -0,0 +1,293 @@
+-- LICENSE AND COPYRIGHT
+--
+-- Copyright (C) 2010 Hubert depesz Lubaczewski
+--
+-- This program is distributed under the (Revised) BSD License:
+-- L<http://www.opensource.org/licenses/bsd-license.php>
+--
+-- Redistribution and use in source and binary forms, with or without
+-- modification, are permitted provided that the following conditions
+-- are met:
+--
+-- * Redistributions of source code must retain the above copyright
+-- notice, this list of conditions and the following disclaimer.
+--
+-- * Redistributions in binary form must reproduce the above copyright
+-- notice, this list of conditions and the following disclaimer in the
+-- documentation and/or other materials provided with the distribution.
+--
+-- * Neither the name of Hubert depesz Lubaczewski's Organization
+-- nor the names of its contributors may be used to endorse or
+-- promote products derived from this software without specific
+-- prior written permission.
+--
+-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
+-- AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
+-- IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
ARE
+-- DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE
+-- FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
+-- DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
+-- SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
+-- CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
LIABILITY,
+-- OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
USE
+-- OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
+--
+-- Code origin:
https://gitlab.com/depesz/Versioning/blob/master/install.versioning.sql
+--
+--
+-- # NAME
+--
+-- **Versioning** - simplistic take on tracking and applying changes to
databases.
+--
+-- # DESCRIPTION
+--
+-- This project strives to provide simple way to manage changes to
+-- database.
+--
+-- Instead of making changes on development server, then finding
+-- differences between production and development, deciding which ones
+-- should be installed on production, and finding a way to install them -
+-- you start with writing diffs themselves!
+--
+-- # INSTALLATION
+--
+-- To install versioning simply run install.versioning.sql in your database
+-- (all of them: production, stage, test, devel, ...).
+--
+-- # USAGE
+--
+-- In your files with patches to database, put whole logic in single
+-- transaction, and use \_v.\* functions - usually \_v.register_patch() at
+-- least to make sure everything is OK.
+--
+-- For example. Let's assume you have patch files:
+--
+-- ## 0001.sql:
+--
+-- ```
+-- create table users (id serial primary key, username text);
+-- ```
+--
+-- ## 0002.sql:
+--
+-- ```
+-- insert into users (username) values ('depesz');
+-- ```
+-- To change it to use versioning you would change the files, to this
+-- state:
+--
+-- 0000.sql:
+--
+-- ```
+-- BEGIN;
+-- select _v.register_patch('000-base', NULL, NULL);
+-- create table users (id serial primary key, username text);
+-- COMMIT;
+-- ```
+--
+-- ## 0002.sql:
+--
+-- ```
+-- BEGIN;
+-- select _v.register_patch('001-users', ARRAY['000-base'], NULL);
+-- insert into users (username) values ('depesz');
+-- COMMIT;
+-- ```
+--
+-- This will make sure that patch 001-users can only be applied after
+-- 000-base.
+--
+-- # AVAILABLE FUNCTIONS
+--
+-- ## \_v.register_patch( TEXT )
+--
+-- Registers named patch, or dies if it is already registered.
+--
+-- Returns integer which is id of patch in \_v.patches table - only if it
+-- succeeded.
+--
+-- ## \_v.register_patch( TEXT, TEXT[] )
+--
+-- Same as \_v.register_patch( TEXT ), but checks is all given patches (given
as
+-- array in second argument) are already registered.
+--
+-- ## \_v.register_patch( TEXT, TEXT[], TEXT[] )
+--
+-- Same as \_v.register_patch( TEXT, TEXT[] ), but also checks if there are no
conflicts with preexisting patches.
+--
+-- Third argument is array of names of patches that conflict with current one.
So
+-- if any of them is installed - register_patch will error out.
+--
+-- ## \_v.unregister_patch( TEXT )
+--
+-- Removes information about given patch from the versioning data.
+--
+-- It doesn't remove objects that were created by this patch - just removes
+-- metainformation.
+--
+-- ## \_v.assert_user_is_superuser()
+--
+-- Make sure that current patch is being loaded by superuser.
+--
+-- If it's not - it will raise exception, and break transaction.
+--
+-- ## \_v.assert_user_is_not_superuser()
+--
+-- Make sure that current patch is not being loaded by superuser.
+--
+-- If it is - it will raise exception, and break transaction.
+--
+-- ## \_v.assert_user_is_one_of(TEXT, TEXT, ... )
+--
+-- Make sure that current patch is being loaded by one of listed users.
+--
+-- If ```current_user``` is not listed as one of arguments - function will
raise
+-- exception and break the transaction.
+
+BEGIN;
+
+-- This file adds versioning support to database it will be loaded to.
+-- It requires that PL/pgSQL is already loaded - will raise exception
otherwise.
+-- All versioning "stuff" (tables, functions) is in "_v" schema.
+
+-- All functions are defined as 'RETURNS SETOF INT4' to be able to make them
to RETURN literally nothing (0 rows).
+-- >> RETURNS VOID<< IS similar, but it still outputs "empty line" in psql
when calling.
+CREATE SCHEMA IF NOT EXISTS _v;
+COMMENT ON SCHEMA _v IS 'Schema for versioning data and functionality.';
+
+CREATE TABLE IF NOT EXISTS _v.patches (
+ patch_name TEXT PRIMARY KEY,
+ applied_tsz TIMESTAMPTZ NOT NULL DEFAULT now(),
+ applied_by TEXT NOT NULL,
+ requires TEXT[],
+ conflicts TEXT[]
+);
+COMMENT ON TABLE _v.patches IS 'Contains information about what
patches are currently applied on database.';
+COMMENT ON COLUMN _v.patches.patch_name IS 'Name of patch, has to be unique
for every patch.';
+COMMENT ON COLUMN _v.patches.applied_tsz IS 'When the patch was applied.';
+COMMENT ON COLUMN _v.patches.applied_by IS 'Who applied this patch
(PostgreSQL username)';
+COMMENT ON COLUMN _v.patches.requires IS 'List of patches that are required
for given patch.';
+COMMENT ON COLUMN _v.patches.conflicts IS 'List of patches that conflict
with given patch.';
+
+CREATE OR REPLACE FUNCTION _v.register_patch( IN in_patch_name TEXT, IN
in_requirements TEXT[], in_conflicts TEXT[], OUT versioning INT4 ) RETURNS
setof INT4 AS $$
+DECLARE
+ t_text TEXT;
+ t_text_a TEXT[];
+ i INT4;
+BEGIN
+ -- Thanks to this we know only one patch will be applied at a time
+ LOCK TABLE _v.patches IN EXCLUSIVE MODE;
+
+ SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name =
in_patch_name;
+ IF FOUND THEN
+ RAISE EXCEPTION 'Patch % is already applied!', in_patch_name;
+ END IF;
+
+ t_text_a := ARRAY( SELECT patch_name FROM _v.patches WHERE patch_name =
any( in_conflicts ) );
+ IF array_upper( t_text_a, 1 ) IS NOT NULL THEN
+ RAISE EXCEPTION 'Versioning patches conflict. Conflicting patche(s)
installed: %.', array_to_string( t_text_a, ', ' );
+ END IF;
+
+ IF array_upper( in_requirements, 1 ) IS NOT NULL THEN
+ t_text_a := '{}';
+ FOR i IN array_lower( in_requirements, 1 ) .. array_upper(
in_requirements, 1 ) LOOP
+ SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name =
in_requirements[i];
+ IF NOT FOUND THEN
+ t_text_a := t_text_a || in_requirements[i];
+ END IF;
+ END LOOP;
+ IF array_upper( t_text_a, 1 ) IS NOT NULL THEN
+ RAISE EXCEPTION 'Missing prerequisite(s): %.', array_to_string(
t_text_a, ', ' );
+ END IF;
+ END IF;
+
+ INSERT INTO _v.patches (patch_name, applied_tsz, applied_by, requires,
conflicts ) VALUES ( in_patch_name, now(), current_user, coalesce(
in_requirements, '{}' ), coalesce( in_conflicts, '{}' ) );
+ RETURN;
+END;
+$$ language plpgsql;
+COMMENT ON FUNCTION _v.register_patch( TEXT, TEXT[], TEXT[]) IS 'Function to
register patches in database. Raises exception if there are conflicts,
prerequisites are not installed or the migration has already been installed.';
+
+CREATE OR REPLACE FUNCTION _v.register_patch( TEXT, TEXT[] ) RETURNS setof
INT4 AS $$
+ SELECT _v.register_patch( $1, $2, NULL );
+$$ language sql;
+COMMENT ON FUNCTION _v.register_patch( TEXT, TEXT[] ) IS 'Wrapper to allow
registration of patches without conflicts.';
+CREATE OR REPLACE FUNCTION _v.register_patch( TEXT ) RETURNS setof INT4 AS $$
+ SELECT _v.register_patch( $1, NULL, NULL );
+$$ language sql;
+COMMENT ON FUNCTION _v.register_patch( TEXT ) IS 'Wrapper to allow
registration of patches without requirements and conflicts.';
+
+CREATE OR REPLACE FUNCTION _v.unregister_patch( IN in_patch_name TEXT, OUT
versioning INT4 ) RETURNS setof INT4 AS $$
+DECLARE
+ i INT4;
+ t_text_a TEXT[];
+BEGIN
+ -- Thanks to this we know only one patch will be applied at a time
+ LOCK TABLE _v.patches IN EXCLUSIVE MODE;
+
+ t_text_a := ARRAY( SELECT patch_name FROM _v.patches WHERE in_patch_name =
ANY( requires ) );
+ IF array_upper( t_text_a, 1 ) IS NOT NULL THEN
+ RAISE EXCEPTION 'Cannot uninstall %, as it is required by: %.',
in_patch_name, array_to_string( t_text_a, ', ' );
+ END IF;
+
+ DELETE FROM _v.patches WHERE patch_name = in_patch_name;
+ GET DIAGNOSTICS i = ROW_COUNT;
+ IF i < 1 THEN
+ RAISE EXCEPTION 'Patch % is not installed, so it can''t be
uninstalled!', in_patch_name;
+ END IF;
+
+ RETURN;
+END;
+$$ language plpgsql;
+COMMENT ON FUNCTION _v.unregister_patch( TEXT ) IS 'Function to unregister
patches in database. Dies if the patch is not registered, or if unregistering
it would break dependencies.';
+
+CREATE OR REPLACE FUNCTION _v.assert_patch_is_applied( IN in_patch_name TEXT )
RETURNS TEXT as $$
+DECLARE
+ t_text TEXT;
+BEGIN
+ SELECT patch_name INTO t_text FROM _v.patches WHERE patch_name =
in_patch_name;
+ IF NOT FOUND THEN
+ RAISE EXCEPTION 'Patch % is not applied!', in_patch_name;
+ END IF;
+ RETURN format('Patch %s is applied.', in_patch_name);
+END;
+$$ language plpgsql;
+COMMENT ON FUNCTION _v.assert_patch_is_applied( TEXT ) IS 'Function that can
be used to make sure that patch has been applied.';
+
+CREATE OR REPLACE FUNCTION _v.assert_user_is_superuser() RETURNS TEXT as $$
+DECLARE
+ v_super bool;
+BEGIN
+ SELECT usesuper INTO v_super FROM pg_user WHERE usename = current_user;
+ IF v_super THEN
+ RETURN 'assert_user_is_superuser: OK';
+ END IF;
+ RAISE EXCEPTION 'Current user is not superuser - cannot continue.';
+END;
+$$ language plpgsql;
+COMMENT ON FUNCTION _v.assert_user_is_superuser() IS 'Function that can be
used to make sure that patch is being applied using superuser account.';
+
+CREATE OR REPLACE FUNCTION _v.assert_user_is_not_superuser() RETURNS TEXT as $$
+DECLARE
+ v_super bool;
+BEGIN
+ SELECT usesuper INTO v_super FROM pg_user WHERE usename = current_user;
+ IF v_super THEN
+ RAISE EXCEPTION 'Current user is superuser - cannot continue.';
+ END IF;
+ RETURN 'assert_user_is_not_superuser: OK';
+END;
+$$ language plpgsql;
+COMMENT ON FUNCTION _v.assert_user_is_not_superuser() IS 'Function that can be
used to make sure that patch is being applied using normal (not superuser)
account.';
+
+CREATE OR REPLACE FUNCTION _v.assert_user_is_one_of(VARIADIC
p_acceptable_users TEXT[] ) RETURNS TEXT as $$
+DECLARE
+BEGIN
+ IF current_user = any( p_acceptable_users ) THEN
+ RETURN 'assert_user_is_one_of: OK';
+ END IF;
+ RAISE EXCEPTION 'User is not one of: % - cannot continue.',
p_acceptable_users;
+END;
+$$ language plpgsql;
+COMMENT ON FUNCTION _v.assert_user_is_one_of(TEXT[]) IS 'Function that can be
used to make sure that patch is being applied by one of defined users.';
+
+COMMIT;
diff --git a/util/src/main/kotlin/DB.kt b/util/src/main/kotlin/DB.kt
index 90065529..d8ac3fbe 100644
--- a/util/src/main/kotlin/DB.kt
+++ b/util/src/main/kotlin/DB.kt
@@ -25,6 +25,7 @@ import logger
import net.taler.wallet.crypto.Base32Crockford
import org.jetbrains.exposed.sql.Transaction
import org.jetbrains.exposed.sql.transactions.TransactionManager
+import org.jetbrains.exposed.sql.transactions.transaction
import org.postgresql.jdbc.PgConnection
fun Transaction.isPostgres(): Boolean {
@@ -217,4 +218,15 @@ class PostgresListenHandle(val channelName: String) {
conn.close()
return false
}
+}
+
+fun getDatabaseName(): String {
+ var maybe_db_name: String? = null
+ transaction {
+ this.exec("SELECT current_database() AS database_name;") { oneLineRes
->
+ if (oneLineRes.next())
+ maybe_db_name = oneLineRes.getString("database_name")
+ }
+ }
+ return maybe_db_name ?: throw internalServerError("Could not find current
DB name")
}
\ No newline at end of file
diff --git a/util/src/main/kotlin/exec.kt b/util/src/main/kotlin/exec.kt
new file mode 100644
index 00000000..c29d5b04
--- /dev/null
+++ b/util/src/main/kotlin/exec.kt
@@ -0,0 +1,33 @@
+/*
+ * This file is part of LibEuFin.
+ * Copyright (C) 2019 Stanisci and Dold.
+
+ * LibEuFin is free software; you can redistribute it and/or modify
+ * it under the terms of the GNU Affero General Public License as
+ * published by the Free Software Foundation; either version 3, or
+ * (at your option) any later version.
+
+ * LibEuFin is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
+ * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General
+ * Public License for more details.
+
+ * You should have received a copy of the GNU Affero General Public
+ * License along with LibEuFin; see the file COPYING. If not, see
+ * <http://www.gnu.org/licenses/>
+ */
+
+package tech.libeufin.util
+
+/**
+ * Wrapper around the ProcessBuilder API. It executes a
+ * command and throws exception if the result is not zero.
+ */
+fun execCommand(cmd: List<String>) {
+ val result: Int = ProcessBuilder(cmd)
+ .redirectOutput(ProcessBuilder.Redirect.INHERIT)
+ .redirectError(ProcessBuilder.Redirect.INHERIT)
+ .start()
+ .waitFor()
+ if (result != 0) throw internalServerError("Command '$cmd' failed.")
+}
\ No newline at end of file
--
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.