commit 7a68b2f0a0603b2bcb34110019254d4d85d91321
parent 297998ad9f093c7c593406dba6d5510fb0c7a2fb
Author: MS <ms@taler.net>
Date: Fri, 1 Sep 2023 10:42:07 +0200
Bank DB refactoring.
Fixing off-by-one when bringing one account
from debit to the credit state.
Diffstat:
3 files changed, 131 insertions(+), 47 deletions(-)
diff --git a/database-versioning/new/procedures.sql b/database-versioning/new/procedures.sql
@@ -116,10 +116,11 @@ creditor_balance taler_amount;
potential_balance taler_amount;
potential_balance_check BOOLEAN;
new_debtor_balance taler_amount;
+new_debtor_balance_ok BOOLEAN;
new_creditor_balance taler_amount;
will_debtor_have_debt BOOLEAN;
will_creditor_have_debt BOOLEAN;
-spending_capacity taler_amount;
+amount_at_least_debit BOOLEAN;
potential_balance_ok BOOLEAN;
BEGIN
-- check debtor exists.
@@ -165,7 +166,9 @@ out_nx_creditor=FALSE;
-- check debtor has enough funds.
IF (debtor_has_debt)
THEN -- debt case: simply checking against the max debt allowed.
- CALL amount_add(debtor_balance, in_amount, potential_balance);
+ CALL amount_add(debtor_balance,
+ in_amount,
+ potential_balance);
SELECT ok
INTO potential_balance_check
FROM amount_left_minus_right(debtor_max_debt,
@@ -221,24 +224,27 @@ THEN
will_creditor_have_debt=FALSE;
ELSE -- creditor had debit but MIGHT switch to credit.
SELECT
- (diff).val, (diff).frac
- INTO new_creditor_balance.val, new_creditor_balance.frac
- FROM amount_left_minus_right(creditor_balance,
- in_amount);
- IF (new_debtor_balance.ok)
- -- the debt is bigger than the amount, keep
- -- this last calculated balance but stay debt.
+ (diff).val, (diff).frac,
+ ok
+ INTO
+ new_creditor_balance.val, new_creditor_balance.frac,
+ amount_at_least_debit
+ FROM amount_left_minus_right(in_amount,
+ creditor_balance);
+ IF (amount_at_least_debit)
+ -- the amount is at least as big as the debit, can switch to credit then.
THEN
- will_creditor_have_debt=TRUE;
+ will_creditor_have_debt=FALSE;
+ -- compute new balance.
ELSE
- -- the amount would bring the account back to credit,
- -- determine by how much.
+ -- the amount is not enough to bring the receiver
+ -- to a credit state, switch operators to calculate the new balance.
SELECT
(diff).val, (diff).frac
INTO new_creditor_balance.val, new_creditor_balance.frac
- FROM amount_left_minus_right(in_amount,
- creditor_balance);
- will_creditor_have_debt=FALSE;
+ FROM amount_left_minus_right(creditor_balance,
+ in_amount);
+ will_creditor_have_debt=TRUE;
END IF;
END IF;
out_balance_insufficient=FALSE;
diff --git a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
@@ -33,7 +33,8 @@ data class BankAccount(
val owningCustomerId: Long,
val isPublic: Boolean = false,
val lastNexusFetchRowId: Long,
- val balance: TalerAmount? = null
+ val balance: TalerAmount? = null,
+ val hasDebt: Boolean
)
enum class TransactionDirection {
@@ -254,6 +255,7 @@ class Database(private val dbConfig: String) {
,last_nexus_fetch_row_id
,(balance).val AS balance_value
,(balance).frac AS balance_frac
+ ,has_debt
FROM bank_accounts
WHERE bank_account_label=?
""")
@@ -271,7 +273,8 @@ class Database(private val dbConfig: String) {
),
bankAccountLabel = bankAccountLabel,
lastNexusFetchRowId = it.getLong("last_nexus_fetch_row_id"),
- owningCustomerId = it.getLong("owning_customer_id")
+ owningCustomerId = it.getLong("owning_customer_id"),
+ hasDebt = it.getBoolean("has_debt")
)
}
}
diff --git a/sandbox/src/test/kotlin/DatabaseTest.kt b/sandbox/src/test/kotlin/DatabaseTest.kt
@@ -3,7 +3,7 @@ import tech.libeufin.sandbox.*
import tech.libeufin.util.execCommand
class DatabaseTest {
- private val c = Customer(
+ private val customerFoo = Customer(
login = "foo",
passwordHash = "hash",
name = "Foo",
@@ -12,7 +12,7 @@ class DatabaseTest {
cashoutPayto = "payto://external-IBAN",
cashoutCurrency = "KUDOS"
)
- private val c1 = Customer(
+ private val customerBar = Customer(
login = "bar",
passwordHash = "hash",
name = "Bar",
@@ -21,6 +21,23 @@ class DatabaseTest {
cashoutPayto = "payto://external-IBAN",
cashoutCurrency = "KUDOS"
)
+ private val bankAccountFoo = BankAccount(
+ iban = "FOO-IBAN-XYZ",
+ bic = "FOO-BIC",
+ bankAccountLabel = "foo",
+ lastNexusFetchRowId = 1L,
+ owningCustomerId = 1L,
+ hasDebt = false
+ )
+ private val bankAccountBar = BankAccount(
+ iban = "BAR-IBAN-ABC",
+ bic = "BAR-BIC",
+ bankAccountLabel = "bar",
+ lastNexusFetchRowId = 1L,
+ owningCustomerId = 2L,
+ hasDebt = false
+ )
+
fun initDb(): Database {
execCommand(
listOf(
@@ -31,50 +48,107 @@ class DatabaseTest {
),
throwIfFails = true
)
- return Database("jdbc:postgresql:///libeufincheck")
+ val db = Database("jdbc:postgresql:///libeufincheck")
+ // Need accounts first.
+ db.customerCreate(customerFoo)
+ db.customerCreate(customerBar)
+ db.bankAccountCreate(bankAccountFoo)
+ db.bankAccountCreate(bankAccountBar)
+ db.bankAccountSetMaxDebt(
+ "foo",
+ TalerAmount(100, 0)
+ )
+ db.bankAccountSetMaxDebt(
+ "bar",
+ TalerAmount(50, 0)
+ )
+ return db
}
@Test
- fun bankTransactionTest() {
+ fun bankTransactionsTest() {
val db = initDb()
- // Need accounts first.
- db.customerCreate(c)
- db.customerCreate(c1)
- db.bankAccountCreate(BankAccount(
- iban = "FOO-IBAN-XYZ",
- bic = "FOO-BIC",
- bankAccountLabel = "foo",
- lastNexusFetchRowId = 1L,
- owningCustomerId = 1L
- ))
- db.bankAccountCreate(BankAccount(
- iban = "BAR-IBAN-ABC",
- bic = "BAR-BIC",
- bankAccountLabel = "bar",
- lastNexusFetchRowId = 1L,
- owningCustomerId = 2L
- ))
- db.bankAccountSetMaxDebt("foo", TalerAmount(100, 0))
- val res = db.bankTransactionCreate(BankInternalTransaction(
+ var fooAccount = db.bankAccountGetFromLabel("foo")
+ assert(fooAccount?.hasDebt == false) // Foo has NO debit.
+ // Preparing the payment data.
+ val fooPaysBar = BankInternalTransaction(
creditorAccountId = 2,
debtorAccountId = 1,
subject = "test",
- amount = TalerAmount(3, 333),
+ amount = TalerAmount(10, 0),
accountServicerReference = "acct-svcr-ref",
endToEndId = "end-to-end-id",
paymentInformationId = "pmtinfid",
transactionDate = 100000L
- ))
- assert(res == Database.BankTransactionResult.SUCCESS)
+ )
+ val firstSpending = db.bankTransactionCreate(fooPaysBar) // Foo pays Bar and goes debit.
+ assert(firstSpending == Database.BankTransactionResult.SUCCESS)
+ fooAccount = db.bankAccountGetFromLabel("foo")
+ // Foo: credit -> debit
+ assert(fooAccount?.hasDebt == true) // Asserting Foo's debit.
+ // Now checking that more spending doesn't get Foo out of debit.
+ val secondSpending = db.bankTransactionCreate(fooPaysBar)
+ assert(secondSpending == Database.BankTransactionResult.SUCCESS)
+ fooAccount = db.bankAccountGetFromLabel("foo")
+ // Checking that Foo's debit is two times the paid amount
+ // Foo: debit -> debit
+ assert(fooAccount?.balance?.value == 20L
+ && fooAccount.balance?.frac == 0
+ && fooAccount.hasDebt
+ )
+ // Asserting Bar has a positive balance and what Foo paid so far.
+ var barAccount = db.bankAccountGetFromLabel("bar")
+ val barBalance: TalerAmount? = barAccount?.balance
+ assert(
+ barAccount?.hasDebt == false
+ && barBalance?.value == 20L && barBalance.frac == 0
+ )
+ // Bar pays so that its balance remains positive.
+ val barPaysFoo = BankInternalTransaction(
+ creditorAccountId = 1,
+ debtorAccountId = 2,
+ subject = "test",
+ amount = TalerAmount(10, 0),
+ accountServicerReference = "acct-svcr-ref",
+ endToEndId = "end-to-end-id",
+ paymentInformationId = "pmtinfid",
+ transactionDate = 100000L
+ )
+ val barPays = db.bankTransactionCreate(barPaysFoo)
+ assert(barPays == Database.BankTransactionResult.SUCCESS)
+ barAccount = db.bankAccountGetFromLabel("bar")
+ val barBalanceTen: TalerAmount? = barAccount?.balance
+ // Bar: credit -> credit
+ assert(barAccount?.hasDebt == false && barBalanceTen?.value == 10L && barBalanceTen.frac == 0)
+ // Bar pays again to let Foo return in credit.
+ val barPaysAgain = db.bankTransactionCreate(barPaysFoo)
+ assert(barPaysAgain == Database.BankTransactionResult.SUCCESS)
+ // Refreshing the two accounts.
+ barAccount = db.bankAccountGetFromLabel("bar")
+ fooAccount = db.bankAccountGetFromLabel("foo")
+ // Foo should have returned to zero and no debt, same for Bar.
+ // Foo: debit -> credit
+ assert(fooAccount?.hasDebt == false && barAccount?.hasDebt == false)
+ assert(fooAccount?.balance?.equals(TalerAmount(0, 0)) == true)
+ assert(barAccount?.balance?.equals(TalerAmount(0, 0)) == true)
+ // Bringing Bar to debit.
+ val barPaysMore = db.bankTransactionCreate(barPaysFoo)
+ assert(barPaysAgain == Database.BankTransactionResult.SUCCESS)
+ barAccount = db.bankAccountGetFromLabel("bar")
+ fooAccount = db.bankAccountGetFromLabel("foo")
+ // Bar: credit -> debit
+ assert(fooAccount?.hasDebt == false && barAccount?.hasDebt == true)
+ assert(fooAccount?.balance?.equals(TalerAmount(10, 0)) == true)
+ assert(barAccount?.balance?.equals(TalerAmount(10, 0)) == true)
}
@Test
fun customerCreationTest() {
val db = initDb()
assert(db.customerGetFromLogin("foo") == null)
- db.customerCreate(c)
+ db.customerCreate(customerFoo)
assert(db.customerGetFromLogin("foo")?.name == "Foo")
// Trigger conflict.
- assert(!db.customerCreate(c))
+ assert(!db.customerCreate(customerFoo))
}
@Test
fun configTest() {
@@ -93,9 +167,10 @@ class DatabaseTest {
bic = "not used",
bankAccountLabel = "foo",
lastNexusFetchRowId = 1L,
- owningCustomerId = 1L
+ owningCustomerId = 1L,
+ hasDebt = false
)
- db.customerCreate(c) // Satisfies the REFERENCE
+ db.customerCreate(customerFoo) // Satisfies the REFERENCE
assert(db.bankAccountCreate(bankAccount))
assert(!db.bankAccountCreate(bankAccount)) // Triggers conflict.
assert(db.bankAccountGetFromLabel("foo")?.bankAccountLabel == "foo")
