[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[libeufin] branch master updated: Bank DB refactoring.
|
From: |
gnunet |
|
Subject: |
[libeufin] branch master updated: Bank DB refactoring. |
|
Date: |
Wed, 30 Aug 2023 14:02:32 +0200 |
This is an automated email from the git hooks/post-receive script.
ms pushed a commit to branch master
in repository libeufin.
The following commit(s) were added to refs/heads/master by this push:
new 5b6585f0 Bank DB refactoring.
5b6585f0 is described below
commit 5b6585f089768b773b4f6c2cbd030da9302d8a8b
Author: MS <ms@taler.net>
AuthorDate: Wed Aug 30 13:58:40 2023 +0200
Bank DB refactoring.
Up to having a Database() object that connects
to Postgres + gets and sets config values, and a
init-db tool that gets called for tests.
---
Makefile | 6 +
contrib/libeufin-bank-dbinit | 107 ++++++++
database-versioning/new/drop-sandbox.sql | 7 -
...ox-0001-refactor.sql => libeufin-bank-0001.sql} | 0
database-versioning/new/libeufin-bank-drop.sql | 9 +
database-versioning/new/procedures.sql | 87 ++++++
database-versioning/new/versioning.sql | 298 +++++++++++++++++++++
.../main/kotlin/tech/libeufin/nexus/DB_helpers.kt | 123 +++++++++
.../tech/libeufin/nexus/bankaccount/BankAccount.kt | 76 +-----
.../kotlin/tech/libeufin/nexus/ebics/EbicsNexus.kt | 1 -
.../kotlin/tech/libeufin/nexus/server/Helpers.kt | 72 -----
nexus/src/test/kotlin/Iso20022Test.kt | 3 -
nexus/src/test/kotlin/PostFinance.kt | 5 +-
nexus/src/test/kotlin/SandboxAccessApiTest.kt | 1 -
.../main/kotlin/tech/libeufin/sandbox/Database.kt | 185 +++++++++----
sandbox/src/test/kotlin/DatabaseTest.kt | 24 ++
16 files changed, 797 insertions(+), 207 deletions(-)
diff --git a/Makefile b/Makefile
index 920a8038..6ecb01f0 100644
--- a/Makefile
+++ b/Makefile
@@ -54,6 +54,12 @@ install-db-versioning:
@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
+ $(eval SANDBOX_DBINIT_SCRIPT := libeufin-bank-dbinit)
+ @sed
"s|__SANDBOX_STATIC_PATCHES_LOCATION__|$(prefix)/share/libeufin/sql/bank|" <
contrib/$(SANDBOX_DBINIT_SCRIPT) > build/$(SANDBOX_DBINIT_SCRIPT)
+ @install -D database-versioning/new/libeufin-bank*.sql -t
$(prefix)/share/libeufin/sql/bank
+ @install -D database-versioning/new/versioning.sql -t
$(prefix)/share/libeufin/sql/bank
+ @install -D database-versioning/new/procedures.sql -t
$(prefix)/share/libeufin/sql/bank
+ @install -D build/$(SANDBOX_DBINIT_SCRIPT) -t $(prefix)/bin
.PHONY: assemble
assemble:
diff --git a/contrib/libeufin-bank-dbinit b/contrib/libeufin-bank-dbinit
new file mode 100755
index 00000000..ae973e86
--- /dev/null
+++ b/contrib/libeufin-bank-dbinit
@@ -0,0 +1,107 @@
+#!/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
+}
+
+usage_and_exit () {
+ echo Usage: libeufin-bank-dbinit OPTIONS
+ echo
+ echo By default, this command creates and/or patches the Sandbox tables.
+ echo Pass '-r' to drop the tables before creating them again.
+ echo
+ echo 'Supported options:'
+ 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'
+ echo ' -r -- drop all the tables before creating them again'
+ exit 0
+}
+
+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
+}
+
+get_patch_path () {
+ echo "$PATCHES_LOCATION/$1"
+}
+
+# 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 () {
+ cd $PATCHES_LOCATION
+ for patch_filename in $(ls -1 -v libeufin-bank-[0-9][0-9][0-9][0-9].sql); do
+ patch_name=$(echo $patch_filename | cut -f1 -d.) # drops the final .sql
+ 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.
+}
+
+if test $# -eq 0; then
+ usage_and_exit
+fi
+
+while getopts ":d:l:hr" OPTION; do
+ case "$OPTION" in
+ d)
+ DB_CONNECTION="$OPTARG" # only one required.
+ ;;
+ l)
+ PATCHES_LOCATION="$OPTARG"
+ ;;
+ r)
+ DROP="YES"
+ ;;
+ h)
+ usage_and_exit
+ ;;
+ ?)
+ 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=__SANDBOX_STATIC_PATCHES_LOCATION__
+fi
+if test -z "${DB_CONNECTION:-}"; then
+ fail "Required option '-d' was missing."
+fi
+
+run_sql_file $(get_patch_path "versioning.sql")
+if test "${DROP:-}" = "YES"; then
+ maybe_applied=$(check_patch_applied "libeufin-bank-0001")
+ if test -n "$maybe_applied"; then
+ run_sql_file $(get_patch_path "libeufin-bank-drop.sql")
+ else
+ echo "Nothing to drop"
+ fi
+fi
+iterate_over_patches
+run_sql_file $(get_patch_path "procedures.sql")
diff --git a/database-versioning/new/drop-sandbox.sql
b/database-versioning/new/drop-sandbox.sql
deleted file mode 100644
index a1ab9765..00000000
--- a/database-versioning/new/drop-sandbox.sql
+++ /dev/null
@@ -1,7 +0,0 @@
-BEGIN;
-
-SELECT _v.unregister_patch('sandbox-0001');
-
-DROP SCHEMA sandbox CASCADE;
-
-COMMIT;
diff --git a/database-versioning/new/sandbox-0001-refactor.sql
b/database-versioning/new/libeufin-bank-0001.sql
similarity index 100%
rename from database-versioning/new/sandbox-0001-refactor.sql
rename to database-versioning/new/libeufin-bank-0001.sql
diff --git a/database-versioning/new/libeufin-bank-drop.sql
b/database-versioning/new/libeufin-bank-drop.sql
new file mode 100644
index 00000000..123481a1
--- /dev/null
+++ b/database-versioning/new/libeufin-bank-drop.sql
@@ -0,0 +1,9 @@
+BEGIN;
+
+-- NOTE: The following unregistration would affect the
+-- legacy database schema too. That's acceptable as the
+-- legacy schema is being removed.
+SELECT _v.unregister_patch('libeufin-bank-0001');
+DROP SCHEMA libeufin_bank CASCADE;
+
+COMMIT;
diff --git a/database-versioning/new/procedures.sql
b/database-versioning/new/procedures.sql
new file mode 100644
index 00000000..5798c5fd
--- /dev/null
+++ b/database-versioning/new/procedures.sql
@@ -0,0 +1,87 @@
+BEGIN;
+SET search_path TO libeufin_bank;
+
+CREATE OR REPLACE FUNCTION amount_normalize(
+ IN amount taler_amount
+ ,OUT normalized taler_amount
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ normalized.val = amount.val + amount.frac / 100000000;
+ normalized.frac = amount.frac % 100000000;
+END $$;
+COMMENT ON FUNCTION amount_normalize
+ IS 'Returns the normalized amount by adding to the .val the value of (.frac
/ 100000000) and removing the modulus 100000000 from .frac.';
+
+CREATE OR REPLACE FUNCTION amount_add(
+ IN a taler_amount
+ ,IN b taler_amount
+ ,OUT sum taler_amount
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+ sum = (a.val + b.val, a.frac + b.frac);
+ CALL amount_normalize(sum ,sum);
+ IF (sum.val > (1<<52))
+ THEN
+ RAISE EXCEPTION 'addition overflow';
+ END IF;
+END $$;
+COMMENT ON FUNCTION amount_add
+ IS 'Returns the normalized sum of two amounts. It raises an exception when
the resulting .val is larger than 2^52';
+
+CREATE OR REPLACE FUNCTION amount_left_minus_right(
+ IN l taler_amount
+ ,IN r taler_amount
+ ,OUT diff taler_amount
+ ,OUT ok BOOLEAN
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+IF (l.val > r.val)
+THEN
+ ok = TRUE;
+ IF (l.frac >= r.frac)
+ THEN
+ diff.val = l.val - r.val;
+ diff.frac = l.frac - r.frac;
+ ELSE
+ diff.val = l.val - r.val - 1;
+ diff.frac = l.frac + 100000000 - r.frac;
+ END IF;
+ELSE
+ IF (l.val = r.val) AND (l.frac >= r.frac)
+ THEN
+ diff.val = 0;
+ diff.frac = l.frac - r.frac;
+ ok = TRUE;
+ ELSE
+ diff = (-1, -1);
+ ok = FALSE;
+ END IF;
+END IF;
+RETURN;
+END $$;
+COMMENT ON FUNCTION amount_left_minus_right
+ IS 'Subtracts the right amount from the left and returns the difference and
TRUE, if the left amount is larger than the right, or an invalid amount and
FALSE otherwise.';
+
+CREATE OR REPLACE PROCEDURE bank_set_config(
+ IN in_key TEXT,
+ IN in_value TEXT
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+UPDATE configuration SET config_value=in_value WHERE config_key=in_key;
+IF NOT FOUND
+THEN
+ INSERT INTO configuration (config_key, config_value) VALUES (in_key,
in_value);
+END IF;
+
+END $$;
+COMMENT ON PROCEDURE bank_set_config(TEXT, TEXT)
+ IS 'Update or insert configuration values';
+COMMIT;
\ No newline at end of file
diff --git a/database-versioning/new/versioning.sql
b/database-versioning/new/versioning.sql
new file mode 100644
index 00000000..c7fa8121
--- /dev/null
+++ b/database-versioning/new/versioning.sql
@@ -0,0 +1,298 @@
+-- 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;
+
+-- Added by Christian Grothoff to support concurrency, see
+--
https://stackoverflow.com/questions/29900845/create-schema-if-not-exists-raises-duplicate-key-error?rq=4
+LOCK TABLE pg_catalog.pg_namespace;
+
+
+-- 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/nexus/src/main/kotlin/tech/libeufin/nexus/DB_helpers.kt
b/nexus/src/main/kotlin/tech/libeufin/nexus/DB_helpers.kt
new file mode 100644
index 00000000..b39a05f2
--- /dev/null
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/DB_helpers.kt
@@ -0,0 +1,123 @@
+package tech.libeufin.nexus
+
+import com.fasterxml.jackson.databind.JsonNode
+import com.fasterxml.jackson.databind.node.ObjectNode
+import com.fasterxml.jackson.module.kotlin.jacksonObjectMapper
+import io.ktor.http.*
+import org.jetbrains.exposed.sql.SqlExpressionBuilder.eq
+import org.jetbrains.exposed.sql.and
+import org.jetbrains.exposed.sql.transactions.transaction
+import tech.libeufin.nexus.server.GetTransactionsParams
+import tech.libeufin.nexus.server.Pain001Data
+import tech.libeufin.util.notFound
+import java.time.Instant
+
+fun getBankAccount(label: String): NexusBankAccountEntity {
+ val maybeBankAccount = transaction {
+ NexusBankAccountEntity.findByName(label)
+ }
+ return maybeBankAccount ?:
+ throw NexusError(
+ HttpStatusCode.NotFound,
+ "Account $label not found"
+ )
+}
+
+/**
+ * Queries the database according to the GET /transactions
+ * parameters.
+ */
+fun getIngestedTransactions(params: GetTransactionsParams): List<JsonNode> =
+ transaction {
+ val bankAccount = getBankAccount(params.bankAccountId)
+ val maybeResult = NexusBankTransactionEntity.find {
+ NexusBankTransactionsTable.bankAccount eq bankAccount.id.value and
(
+ NexusBankTransactionsTable.id greaterEq params.startIndex
+ )
+ }.sortedBy { it.id.value }.take(params.resultSize.toInt()) // Smallest
index (= earliest transaction) first
+ // Converting the result to the HTTP response type.
+ maybeResult.map {
+ val element: ObjectNode = jacksonObjectMapper().createObjectNode()
+ element.put("index", it.id.value.toString())
+ val txObj: JsonNode =
jacksonObjectMapper().readTree(it.transactionJson)
+ element.set<JsonNode>("camtData", txObj)
+ return@map element
+ }
+ }
+
+// Gets connection or throws.
+fun getBankConnection(connId: String): NexusBankConnectionEntity {
+ val maybeConn = transaction {
+ NexusBankConnectionEntity.find {
+ NexusBankConnectionsTable.connectionId eq connId
+ }.firstOrNull()
+ }
+ if (maybeConn == null) throw notFound("Bank connection $connId not found")
+ return maybeConn
+}
+
+/**
+ * Retrieve payment initiation from database, raising exception if not found.
+ */
+fun getPaymentInitiation(uuid: Long): PaymentInitiationEntity {
+ return transaction {
+ PaymentInitiationEntity.findById(uuid)
+ } ?: throw NexusError(
+ HttpStatusCode.NotFound,
+ "Payment '$uuid' not found"
+ )
+}
+
+/**
+ * Gets a prepared payment starting from its 'payment information id'.
+ * Note: although the terminology comes from CaMt, a 'payment information id'
+ * is indeed any UID that identifies the payment. For this reason, also
+ * the x-libeufin-bank logic uses this helper.
+ *
+ * Returns the prepared payment, or null if that's not found. Not throwing
+ * any exception because the null case is common: not every transaction being
+ * processed by Neuxs was prepared/initiated here; incoming transactions are
+ * one example.
+ */
+fun getPaymentInitiation(pmtInfId: String): PaymentInitiationEntity? =
+ transaction {
+ PaymentInitiationEntity.find(
+ PaymentInitiationsTable.paymentInformationId.eq(pmtInfId)
+ ).firstOrNull()
+ }
+
+/**
+ * Insert one row in the database, and leaves it marked as non-submitted.
+ * @param debtorAccount the mnemonic id assigned by the bank to one bank
+ * account of the subscriber that is creating the pain entity. In this case,
+ * it will be the account whose money will pay the wire transfer being defined
+ * by this pain document.
+ */
+fun addPaymentInitiation(
+ paymentData: Pain001Data,
+ debtorAccount: NexusBankAccountEntity
+): PaymentInitiationEntity {
+ return transaction {
+
+ val now = Instant.now().toEpochMilli()
+ val nowHex = now.toString(16)
+ val painCounter = debtorAccount.pain001Counter++
+ val painHex = painCounter.toString(16)
+ val acctHex = debtorAccount.id.value.toString(16)
+
+ PaymentInitiationEntity.new {
+ currency = paymentData.currency
+ bankAccount = debtorAccount
+ subject = paymentData.subject
+ sum = paymentData.sum
+ creditorName = paymentData.creditorName
+ creditorBic = paymentData.creditorBic
+ creditorIban = paymentData.creditorIban
+ preparationDate = now
+ endToEndId = paymentData.endToEndId ?:
"leuf-e-$nowHex-$painHex-$acctHex"
+ messageId = "leuf-mp1-$nowHex-$painHex-$acctHex"
+ paymentInformationId = "leuf-p-$nowHex-$painHex-$acctHex"
+ instructionId = "leuf-i-$nowHex-$painHex-$acctHex"
+ }
+ }
+}
diff --git
a/nexus/src/main/kotlin/tech/libeufin/nexus/bankaccount/BankAccount.kt
b/nexus/src/main/kotlin/tech/libeufin/nexus/bankaccount/BankAccount.kt
index 26453fac..5e398d7e 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/bankaccount/BankAccount.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/bankaccount/BankAccount.kt
@@ -38,23 +38,6 @@ import java.time.ZonedDateTime
private val keepBankMessages: String? =
System.getenv("LIBEUFIN_NEXUS_KEEP_BANK_MESSAGES")
-/**
- * Gets a prepared payment starting from its 'payment information id'.
- * Note: although the terminology comes from CaMt, a 'payment information id'
- * is indeed any UID that identifies the payment. For this reason, also
- * the x-libeufin-bank logic uses this helper.
- *
- * Returns the prepared payment, or null if that's not found. Not throwing
- * any exception because the null case is common: not every transaction being
- * processed by Neuxs was prepared/initiated here; incoming transactions are
- * one example.
- */
-fun getPaymentInitiation(pmtInfId: String): PaymentInitiationEntity? =
- transaction {
- PaymentInitiationEntity.find(
- PaymentInitiationsTable.paymentInformationId.eq(pmtInfId)
- ).firstOrNull()
- }
fun requireBankAccount(call: ApplicationCall, parameterKey: String):
NexusBankAccountEntity {
val name = call.parameters[parameterKey]
if (name == null)
@@ -283,18 +266,6 @@ fun ingestBankMessagesIntoAccount(
)
}
-/**
- * Retrieve payment initiation from database, raising exception if not found.
- */
-fun getPaymentInitiation(uuid: Long): PaymentInitiationEntity {
- return transaction {
- PaymentInitiationEntity.findById(uuid)
- } ?: throw NexusError(
- HttpStatusCode.NotFound,
- "Payment '$uuid' not found"
- )
-}
-
data class LastMessagesTimes(
val lastStatement: ZonedDateTime?,
val lastReport: ZonedDateTime?,
@@ -323,57 +294,12 @@ fun getLastMessagesTimes(acct: NexusBankAccountEntity):
LastMessagesTimes {
}
)
}
-fun getBankAccount(label: String): NexusBankAccountEntity {
- val maybeBankAccount = transaction {
- NexusBankAccountEntity.findByName(label)
- }
- return maybeBankAccount ?:
- throw NexusError(
- HttpStatusCode.NotFound,
- "Account $label not found"
- )
-}
+
fun addPaymentInitiation(paymentData: Pain001Data, debtorAccount: String):
PaymentInitiationEntity {
val bankAccount = getBankAccount(debtorAccount)
return addPaymentInitiation(paymentData, bankAccount)
}
-/**
- * Insert one row in the database, and leaves it marked as non-submitted.
- * @param debtorAccount the mnemonic id assigned by the bank to one bank
- * account of the subscriber that is creating the pain entity. In this case,
- * it will be the account whose money will pay the wire transfer being defined
- * by this pain document.
- */
-fun addPaymentInitiation(
- paymentData: Pain001Data,
- debtorAccount: NexusBankAccountEntity
-): PaymentInitiationEntity {
- return transaction {
-
- val now = Instant.now().toEpochMilli()
- val nowHex = now.toString(16)
- val painCounter = debtorAccount.pain001Counter++
- val painHex = painCounter.toString(16)
- val acctHex = debtorAccount.id.value.toString(16)
-
- PaymentInitiationEntity.new {
- currency = paymentData.currency
- bankAccount = debtorAccount
- subject = paymentData.subject
- sum = paymentData.sum
- creditorName = paymentData.creditorName
- creditorBic = paymentData.creditorBic
- creditorIban = paymentData.creditorIban
- preparationDate = now
- endToEndId = paymentData.endToEndId ?:
"leuf-e-$nowHex-$painHex-$acctHex"
- messageId = "leuf-mp1-$nowHex-$painHex-$acctHex"
- paymentInformationId = "leuf-p-$nowHex-$painHex-$acctHex"
- instructionId = "leuf-i-$nowHex-$painHex-$acctHex"
- }
- }
-}
-
suspend fun fetchBankAccountTransactions(
client: HttpClient,
fetchSpec: FetchSpecJson,
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/ebics/EbicsNexus.kt
b/nexus/src/main/kotlin/tech/libeufin/nexus/ebics/EbicsNexus.kt
index 18e1bb08..7af1db69 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/ebics/EbicsNexus.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/ebics/EbicsNexus.kt
@@ -47,7 +47,6 @@ import org.jetbrains.exposed.sql.statements.api.ExposedBlob
import org.jetbrains.exposed.sql.transactions.transaction
import tech.libeufin.nexus.*
import tech.libeufin.nexus.bankaccount.getLastMessagesTimes
-import tech.libeufin.nexus.bankaccount.getPaymentInitiation
import tech.libeufin.nexus.iso20022.NexusPaymentInitiationData
import tech.libeufin.nexus.iso20022.createPain001document
import tech.libeufin.nexus.logger
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/server/Helpers.kt
b/nexus/src/main/kotlin/tech/libeufin/nexus/server/Helpers.kt
index a55cbbd2..175509cd 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/server/Helpers.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/server/Helpers.kt
@@ -1,15 +1,8 @@
package tech.libeufin.nexus.server
-import com.fasterxml.jackson.databind.JsonNode
-import com.fasterxml.jackson.databind.node.ObjectNode
-import com.fasterxml.jackson.module.kotlin.jacksonObjectMapper
import io.ktor.http.*
-import org.jetbrains.exposed.sql.and
-import org.jetbrains.exposed.sql.transactions.transaction
import tech.libeufin.nexus.*
-import tech.libeufin.nexus.bankaccount.getBankAccount
import tech.libeufin.util.internalServerError
-import tech.libeufin.util.notFound
// Type holding parameters of GET /transactions.
data class GetTransactionsParams(
@@ -18,28 +11,6 @@ data class GetTransactionsParams(
val resultSize: Long
)
-/**
- * Queries the database according to the GET /transactions
- * parameters.
- */
-fun getIngestedTransactions(params: GetTransactionsParams): List<JsonNode> =
- transaction {
- val bankAccount = getBankAccount(params.bankAccountId)
- val maybeResult = NexusBankTransactionEntity.find {
- NexusBankTransactionsTable.bankAccount eq bankAccount.id.value and
(
- NexusBankTransactionsTable.id greaterEq params.startIndex
- )
- }.sortedBy { it.id.value }.take(params.resultSize.toInt()) // Smallest
index (= earliest transaction) first
- // Converting the result to the HTTP response type.
- maybeResult.map {
- val element: ObjectNode = jacksonObjectMapper().createObjectNode()
- element.put("index", it.id.value.toString())
- val txObj: JsonNode =
jacksonObjectMapper().readTree(it.transactionJson)
- element.set<JsonNode>("camtData", txObj)
- return@map element
- }
- }
-
fun unknownBankAccount(bankAccountLabel: String): NexusError {
return NexusError(
HttpStatusCode.NotFound,
@@ -53,7 +24,6 @@ fun unknownBankAccount(bankAccountLabel: String): NexusError {
* be employed wherever now type names are passed as plain
* strings.
*/
-
enum class EbicsDialects(val dialectName: String) {
POSTFINANCE("pf")
}
@@ -106,46 +76,4 @@ enum class BankConnectionType(val typeName: String) {
enum class NexusFacadeType(val facadeType: String) {
TALER("taler-wire-gateway"),
ANASTASIS("anastasis")
-}
-
-/**
- * These types point at the _content_ brought by bank connections.
- * The following stack depicts the layering of banking communication
- * as modeled here in Nexus. On top the most inner layer.
- *
- * --------------------
- * Banking data type
- * --------------------
- * Bank connection type
- * --------------------
- * HTTP
- * --------------------
- *
- * Once the banking data type arrives to the local database, facades
- * types MAY apply further processing to it.
- *
- * For example, a Taler facade WILL look for Taler-meaningful wire
- * subjects and act accordingly. Even without a facade, the Nexus
- * native HTTP API picks instances of banking data and extracts its
- * details to serve to the client.
- *
- * NOTE: this type MAY help but is NOT essential, as each connection
- * is USUALLY tied with the same banking data type. For example, EBICS
- * brings CaMt, and x-libeufin-bank bring its own (same-named x-libeufin-bank)
- * banking data type.
- */
-enum class BankingDataType {
- X_LIBEUFIN_BANK,
- CAMT
-}
-
-// Gets connection or throws.
-fun getBankConnection(connId: String): NexusBankConnectionEntity {
- val maybeConn = transaction {
- NexusBankConnectionEntity.find {
- NexusBankConnectionsTable.connectionId eq connId
- }.firstOrNull()
- }
- if (maybeConn == null) throw notFound("Bank connection $connId not found")
- return maybeConn
}
\ No newline at end of file
diff --git a/nexus/src/test/kotlin/Iso20022Test.kt
b/nexus/src/test/kotlin/Iso20022Test.kt
index 848e162a..06d14ce1 100644
--- a/nexus/src/test/kotlin/Iso20022Test.kt
+++ b/nexus/src/test/kotlin/Iso20022Test.kt
@@ -12,12 +12,9 @@ import org.w3c.dom.Document
import poFiCamt054_2019_incoming
import poFiCamt054_2019_outgoing
import prepNexusDb
-import tech.libeufin.nexus.bankaccount.getBankAccount
import tech.libeufin.nexus.iso20022.*
import tech.libeufin.nexus.server.EbicsDialects
import tech.libeufin.nexus.server.FetchLevel
-import tech.libeufin.nexus.server.getBankConnection
-import tech.libeufin.nexus.server.nexusApp
import tech.libeufin.util.DestructionError
import tech.libeufin.util.XMLUtil
import tech.libeufin.util.destructXml
diff --git a/nexus/src/test/kotlin/PostFinance.kt
b/nexus/src/test/kotlin/PostFinance.kt
index 1dce6d62..8c392487 100644
--- a/nexus/src/test/kotlin/PostFinance.kt
+++ b/nexus/src/test/kotlin/PostFinance.kt
@@ -7,10 +7,11 @@ import kotlinx.coroutines.runBlocking
import org.jetbrains.exposed.sql.transactions.transaction
import tech.libeufin.nexus.bankaccount.addPaymentInitiation
import tech.libeufin.nexus.bankaccount.fetchBankAccountTransactions
-import tech.libeufin.nexus.bankaccount.getBankAccount
import tech.libeufin.nexus.ebics.EbicsUploadSpec
import tech.libeufin.nexus.ebics.doEbicsUploadTransaction
import tech.libeufin.nexus.ebics.getEbicsSubscriberDetails
+import tech.libeufin.nexus.getBankAccount
+import tech.libeufin.nexus.getBankConnection
import tech.libeufin.nexus.getConnectionPlugin
import tech.libeufin.nexus.getNexusUser
import tech.libeufin.nexus.server.*
@@ -92,7 +93,7 @@ private fun uploadPain001Payment(
subject = subject,
endToEndId = "Zufall"
),
- getBankAccount("foo")
+ getBankAccount("foo").bankAccountName
)
}
val ebicsConn = getConnectionPlugin("ebics")
diff --git a/nexus/src/test/kotlin/SandboxAccessApiTest.kt
b/nexus/src/test/kotlin/SandboxAccessApiTest.kt
index ac64d327..4236e0ce 100644
--- a/nexus/src/test/kotlin/SandboxAccessApiTest.kt
+++ b/nexus/src/test/kotlin/SandboxAccessApiTest.kt
@@ -13,7 +13,6 @@ import org.jetbrains.exposed.sql.and
import org.jetbrains.exposed.sql.transactions.transaction
import org.junit.Ignore
import org.junit.Test
-import tech.libeufin.nexus.bankaccount.getBankAccount
import tech.libeufin.sandbox.*
import tech.libeufin.util.getDatabaseName
import java.util.*
diff --git a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
index 5a978180..9842c7ce 100644
--- a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
+++ b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
@@ -6,6 +6,7 @@ import tech.libeufin.util.internalServerError
import java.sql.DriverManager
import java.sql.PreparedStatement
import java.sql.ResultSet
+import java.util.*
private const val DB_CTR_LIMIT = 1000000
@@ -56,6 +57,17 @@ data class BankAccountTransaction(
val bankAccountId: Long,
)
+data class TalerWithdrawalOperation(
+ val withdrawalId: UUID,
+ val amount: TalerAmount,
+ val selectionDone: Boolean = false,
+ val aborted: Boolean = false,
+ val confirmationDone: Boolean = false,
+ val reservePub: ByteArray?,
+ val selectedExchangePayto: String?,
+ val walletBankAccount: Long
+)
+
class Database(private val dbConfig: String) {
private var dbConn: PgConnection? = null
private var dbCtr: Int = 0
@@ -73,6 +85,7 @@ class Database(private val dbConfig: String) {
preparedStatements.clear()
dbConn =
DriverManager.getConnection(dbConfig).unwrap(PgConnection::class.java)
dbCtr = 0
+ dbConn?.execSQLUpdate("SET search_path TO libeufin_bank;")
}
private fun prepare(sql: String): PreparedStatement {
@@ -88,22 +101,19 @@ class Database(private val dbConfig: String) {
// CONFIG
fun configGet(configKey: String): String? {
reconnect()
- val stmt = prepare("""
- SELECT value FROM configuration WHERE key=?;
- """.trimIndent())
+ val stmt = prepare("SELECT config_value FROM configuration WHERE
config_key=?;")
stmt.setString(1, configKey)
- if (!stmt.execute()) return null
- stmt.use {
- return stmt.resultSet.getString("value")
+ val rs = stmt.executeQuery()
+ rs.use {
+ if(!it.next()) return null
+ return it.getString("config_value")
}
}
fun configSet(configKey: String, configValue: String) {
reconnect()
- val stmt = prepare("""
- UPDATE configuration SET value=? WHERE key=?
- """.trimIndent())
- stmt.setString(1, configValue)
- stmt.setString(2, configKey)
+ val stmt = prepare("CALL bank_set_config(TEXT(?), TEXT(?))")
+ stmt.setString(1, configKey)
+ stmt.setString(2, configValue)
stmt.execute()
}
@@ -111,8 +121,17 @@ class Database(private val dbConfig: String) {
fun customerCreate(customer: Customer) {
reconnect()
val stmt = prepare("""
- INSERT INTO customers VALUES (?, ?, ?, ?, ?, ?, ?)
- """.trimIndent()
+ INSERT INTO customers (
+ login
+ ,password_hash
+ ,name
+ ,email
+ ,phone
+ ,cashout_payto
+ ,cashout_currency
+ )
+ VALUES (?, ?, ?, ?, ?, ?, ?)
+ """
)
stmt.setString(1, customer.login)
stmt.setString(2, customer.passwordHash)
@@ -123,40 +142,61 @@ class Database(private val dbConfig: String) {
stmt.setString(7, customer.cashoutCurrency)
stmt.execute()
}
- fun customerGetFromLogin(login: String, cb: (ResultSet)->Unit) {
+ fun customerGetFromLogin(login: String): Customer? {
reconnect()
val stmt = prepare("""
- SELECT * FROM customers WHERE login=?
- """.trimIndent())
+ SELECT
+ password_hash,
+ name,
+ email,
+ phone,
+ cashout_payto,
+ cashout_currency
+ FROM customers
+ WHERE login=?
+ """)
stmt.setString(1, login)
- if (!stmt.execute()) return
- stmt.use { // why .use{} and not directly access .resultSet?
- cb(stmt.resultSet)
+ val rs = stmt.executeQuery()
+ rs.use {
+ if (!rs.next()) return null
+ return Customer(
+ login = login,
+ passwordHash = it.getString("password_hash"),
+ name = it.getString("name"),
+ phone = it.getString("phone"),
+ email = it.getString("email"),
+ cashoutCurrency = it.getString("cashout_currency"),
+ cashoutPayto = it.getString("cashout_payto")
+ )
}
}
// Possibly more "customerGetFrom*()" to come.
// BANK ACCOUNTS
- fun bankAccountCreate(bankAccount: BankAccount) {
+
+ /*
+ // Returns false on conflicts.
+ fun bankAccountCreate(bankAccount: BankAccount): Boolean {
reconnect()
val stmt = prepare("""
- INSERT INTO bank_accounts VALUES (?, ?, ?, ?, ?, ?, ?)
- """.trimIndent())
+ INSERT INTO bank_accounts (col, col, ..) VALUES (?, ?, ?, ?, ?, ?,
?)
+ """)
stmt.setString(1, bankAccount.iban)
- stmt.setString(1, bankAccount.bic)
- stmt.setString(1, bankAccount.bankAccountLabel)
- stmt.setLong(1, bankAccount.owningCustomerId)
- stmt.setLong(1, bankAccount.lastNexusFetchRowId)
- // Following might fail and need a "?::taler_amount" on the last
parameter.
- // See:
https://stackoverflow.com/questions/10571821/inserting-into-custom-sql-types-with-prepared-statements-in-java
- stmt.setObject(1, bankAccount.balance)
- stmt.execute()
+ stmt.setString(2, bankAccount.bic)
+ stmt.setString(3, bankAccount.bankAccountLabel)
+ stmt.setLong(4, bankAccount.owningCustomerId)
+ stmt.setLong(5, bankAccount.lastNexusFetchRowId)
+ // using the default zero value for the balance.
+ val ret = stmt.execute()
+ // FIXME: investigate the failure cause: DBMS vs Unique constraint
violation.
+ // FIXME: need test case to trigger such violation.
}
- fun bankAccountGetFromLabel(bankAccountLabel: String, cb: (ResultSet) ->
Unit) {
+
+ fun bankAccountGetFromLabel(bankAccountLabel: String): BankAccount {
reconnect()
val stmt = prepare("""
SELECT * FROM bank_accounts WHERE bank_account_label=?
- """.trimIndent())
+ """)
stmt.setString(1, bankAccountLabel)
if (!stmt.execute()) return
stmt.use { // why .use{} and not directly access .resultSet?
@@ -166,26 +206,34 @@ class Database(private val dbConfig: String) {
// More bankAccountGetFrom*() to come, on a needed basis.
// BANK ACCOUNT TRANSACTIONS
- fun bankTransactionCreate(tx: BankAccountTransaction) {
+ enum class BankTransactionResult {
+ NO_CREDITOR,
+ NO_DEBTOR,
+ SUCCESS,
+ CONFLICT
+ }
+ fun bankTransactionCreate(
+ // tx: BankInternalTransaction
+ creditTx: BankAccountTransaction,
+ debitTx: BankAccountTransaction
+ ): BankTransactionResult {
reconnect()
val stmt = prepare("""
- INSERT INTO bank_account_transactions VALUES
(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
- """.trimIndent())
- stmt.setString(1, tx.creditorIban)
- stmt.setString(2, tx.creditorBic)
- stmt.setString(3, tx.creditorName)
- stmt.setString(4, tx.debtorIban)
- stmt.setString(5, tx.debtorBic)
- stmt.setString(6, tx.debtorName)
+ SELECT out_nx_creditor, out_nx_debitor, out_balance_insufficient
+ FROM bank_wire_transfer(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
+ """ // FIXME: adjust balances.
+ )
+ // FIXME: implement this operation with a stored procedure.
+ // Credit side
+ stmt.setString(1, tx.creditorAccountId)
+ stmt.setString(1, tx.debitorAccountId)
stmt.setString(7, tx.subject)
stmt.setObject(8, tx.amount)
stmt.setLong(9, tx.transactionDate)
stmt.setString(10, tx.accountServicerReference)
stmt.setString(11, tx.paymentInformationId)
stmt.setString(12, tx.endToEndId)
- stmt.setBoolean(13, tx.isPending)
- stmt.setObject(14, tx.direction)
- stmt.setLong(15, tx.bankAccountId)
+
stmt.execute()
}
@@ -202,7 +250,7 @@ class Database(private val dbConfig: String) {
bankAccountTransactionId < ?
AND bank_account_id=?
AND transaction_date BETWEEN ? AND ?
- """.trimIndent())
+ """)
stmt.setLong(1, upperBound)
stmt.setLong(2, bankAccountId)
stmt.setLong(3, fromMs)
@@ -212,7 +260,52 @@ class Database(private val dbConfig: String) {
cb(stmt.resultSet)
}
}
- // NOTE: to run BFH, EBICS and cash-out tables can be postponed.
+ // WITHDRAWALS
+ fun talerWithdrawalCreate(opUUID: UUID, walletBankAccount: Long) {
+ reconnect()
+ val stmt = prepare("""
+ INSERT INTO taler_withdrawals_operations (withdrawal_id,
wallet_bank_account)
+ VALUES (?,?)
+ """) // Take all defaults from the SQL.
+ stmt.setObject(1, opUUID)
+ stmt.setObject(2, walletBankAccount)
+ stmt.execute()
+ }
+
+ // Values coming from the wallet.
+ fun talerWithdrawalSetDetails(
+ opUUID: UUID,
+ exchangePayto: String,
+ reservePub: ByteArray
+ ) {
+ reconnect()
+ val stmt = prepare("""
+ UPDATE taler_withdrawal_operations
+ SET selected_exchange_payto = ?, reserve_pub = ?, selection_done =
true
+ WHERE withdrawal_id=?
+ """
+ )
+ stmt.setString(1, exchangePayto)
+ stmt.setBytes(2, reservePub)
+ stmt.setObject(3, opUUID)
+ stmt.execute()
+ }
+
+ fun talerWithdrawalConfirm(opUUID: UUID) {
+ reconnect()
+ val stmt = prepare("""
+ UPDATE taler_withdrawal_operations
+ SET confirmation_done = true
+ WHERE withdrawal_id=?
+ """
+ )
+ stmt.setObject(1, opUUID)
+ stmt.execute()
+ }
+
+
+ // NOTE: to run BFH, EBICS and cash-out tables can be postponed.
+*/
}
diff --git a/sandbox/src/test/kotlin/DatabaseTest.kt
b/sandbox/src/test/kotlin/DatabaseTest.kt
index 5223cdf6..77a85312 100644
--- a/sandbox/src/test/kotlin/DatabaseTest.kt
+++ b/sandbox/src/test/kotlin/DatabaseTest.kt
@@ -1,2 +1,26 @@
+import org.junit.Test
+import tech.libeufin.sandbox.Database
+import tech.libeufin.util.execCommand
+
class DatabaseTest {
+ fun initDb() {
+ execCommand(
+ listOf(
+ "libeufin-bank-dbinit",
+ "-d",
+ "libeufincheck",
+ "-r"
+ ),
+ throwIfFails = true
+ )
+ }
+ @Test
+ fun configTest() {
+ initDb()
+ val db = Database("jdbc:postgresql:///libeufincheck")
+ assert(db.configGet("bar") == null)
+ assert(db.configGet("bar") == null)
+ db.configSet("foo", "bar")
+ assert(db.configGet("foo") == "bar")
+ }
}
\ No newline at end of file
--
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.
- [libeufin] branch master updated: Bank DB refactoring.,
gnunet <=