From e9d4790da895a92adbb525f4c02bbdde90af5d6f Mon Sep 17 00:00:00 2001 From: MS Date: Thu, 31 Aug 2023 21:05:56 +0200 Subject: progress on bank DB refactoring --- database-versioning/new/libeufin-bank-0001.sql | 3 +- database-versioning/new/procedures.sql | 237 ++++++++++++--------- .../main/kotlin/tech/libeufin/sandbox/Database.kt | 66 ++++-- sandbox/src/test/kotlin/DatabaseTest.kt | 48 ++++- 4 files changed, 227 insertions(+), 127 deletions(-) diff --git a/database-versioning/new/libeufin-bank-0001.sql b/database-versioning/new/libeufin-bank-0001.sql index 2e211316..339852ac 100644 --- a/database-versioning/new/libeufin-bank-0001.sql +++ b/database-versioning/new/libeufin-bank-0001.sql @@ -86,7 +86,8 @@ CREATE TABLE IF NOT EXISTS bank_accounts ,is_public BOOLEAN DEFAULT FALSE NOT NULL -- privacy by default ,last_nexus_fetch_row_id BIGINT ,balance taler_amount DEFAULT (0, 0) - ,has_debt BOOLEAN NON NULL DEFAULT TO FALSE + ,max_debt taler_amount DEFAULT (0, 0) + ,has_debt BOOLEAN NOT NULL DEFAULT FALSE ,UNIQUE (owning_customer_id, bank_account_label) ); diff --git a/database-versioning/new/procedures.sql b/database-versioning/new/procedures.sql index 798ac53f..ca64f357 100644 --- a/database-versioning/new/procedures.sql +++ b/database-versioning/new/procedures.sql @@ -85,7 +85,7 @@ END $$; COMMENT ON PROCEDURE bank_set_config(TEXT, TEXT) IS 'Update or insert configuration values'; -CREATE OR REPLACE PROCEDURE bank_wire_transfer( +CREATE OR REPLACE FUNCTION bank_wire_transfer( IN in_creditor_account_id BIGINT, IN in_debtor_account_id BIGINT, IN in_subject TEXT, @@ -101,140 +101,159 @@ CREATE OR REPLACE PROCEDURE bank_wire_transfer( LANGUAGE plpgsql AS $$ DECLARE -debtor_account RECORD; -creditor_account RECORD; +debtor_has_debt BOOLEAN; +debtor_balance taler_amount; +debtor_max_debt taler_amount; +creditor_has_debt BOOLEAN; +creditor_balance taler_amount; +potential_balance taler_amount; +potential_balance_check BOOLEAN; +new_debtor_balance taler_amount; +new_creditor_balance taler_amount; +will_debtor_have_debt BOOLEAN; +will_creditor_have_debt BOOLEAN; +spending_capacity taler_amount; +potential_balance_ok BOOLEAN; BEGIN -- check debtor exists. SELECT - INTO debtor_account + has_debt, + (balance).val, (balance).frac, + (max_debt).val, (max_debt).frac + INTO + debtor_has_debt, + debtor_balance.val, debtor_balance.frac, + debtor_max_debt.val, debtor_max_debt.frac FROM bank_accounts - WHERE bank_account_id=in_debtor_account_id + WHERE bank_account_id=in_debtor_account_id; IF NOT FOUND - out_nx_debtor=FALSE - out_nx_creditor=NULL - out_balance_insufficient=NULL +THEN + out_nx_debtor=TRUE; RETURN; END IF; +out_nx_debtor=FALSE; -- check creditor exists. Future versions may skip this -- due to creditors being hosted at other banks. SELECT - INTO creditor_account + has_debt, + (balance).val, (balance).frac + INTO + creditor_has_debt, + creditor_balance.val, creditor_balance.frac FROM bank_accounts - WHERE bank_account_id=in_creditor_account_id + WHERE bank_account_id=in_creditor_account_id; IF NOT FOUND - out_nx_debtor=TRUE - out_nx_creditor=FALSE - out_balance_insufficient=NULL +THEN + out_nx_creditor=TRUE; RETURN; END IF; +out_nx_creditor=FALSE; -- DEBTOR SIDE -- check debtor has enough funds. -IF (debtor_account.has_debt) +IF (debtor_has_debt) THEN -- debt case: simply checking against the max debt allowed. -SELECT - INTO potential_balance - FROM amount_add(debtor_account.balance - in_amount); -SELECT * -INTO potential_balance_check -FROM amount_left_minus_right(debtor_account.max_debt, - potential_balance); -IF (NOT potential_balance_check.ok) -THEN -out_nx_creditor=TRUE; -out_nx_debtor=TRUE; -out_balance_insufficient=TRUE; -RETURN; -new_debtor_balance=potential_balance_check.diff; -will_debtor_have_debt=TRUE; -END IF; + SELECT + (sum).val, (sum).frac + INTO + potential_balance.val, potential_balance.frac + FROM amount_add(debtor_balance, + in_amount); + SELECT ok + INTO potential_balance_check + FROM amount_left_minus_right(debtor_max_debt, + potential_balance); + IF (NOT potential_balance_check) + THEN + out_balance_insufficient=TRUE; + RETURN; + END IF; + new_debtor_balance=potential_balance; + will_debtor_have_debt=TRUE; ELSE -- not a debt account -SELECT -- checking first funds availability. - INTO spending_capacity - FROM amount_add(debtor_account.balance, - debtor_account.max_debt); -IF (NOT spending_capacity.ok) -THEN -out_nx_creditor=TRUE; -out_nx_debtor=TRUE; -out_balance_insufficient=TRUE; -RETURN; + SELECT + ok, + (diff).val, (diff).frac + INTO + potential_balance_ok, + potential_balance.val, + potential_balance.frac + FROM amount_left_minus_right(debtor_balance, + in_amount); + IF (potential_balance_ok) -- debtor has enough funds in the (positive) balance. + THEN + new_debtor_balance=potential_balance; + will_debtor_have_debt=FALSE; + ELSE -- debtor will switch to debt: determine their new negative balance. + SELECT + (diff).val, (diff).frac + INTO + new_debtor_balance.val, new_debtor_balance.frac + FROM amount_left_minus_right(in_amount, + debtor_balance); + will_debtor_have_debt=TRUE; + SELECT ok + INTO potential_balance_check + FROM amount_left_minus_right(debtor_max_debt, + new_debtor_balance); + IF (NOT potential_balance_check) + THEN + out_balance_insufficient=TRUE; + RETURN; + END IF; + END IF; END IF; --- debtor has enough funds, now determine the new --- balance and whether they go to debit. -SELECT - INTO potential_balance - FROM amount_left_minus_right(debtor_account.balance, - in_amount); -IF (potential_balance.ok) -- debtor has enough funds in the (positive) balance. -THEN -new_debtor_balance=potential_balance.diff; -will_debtor_have_debt=FALSE; -ELSE -- debtor will switch to debt: determine their new negative balance. -SELECT diff - INTO new_debtor_balance - FROM amount_left_minus_right(in_amount, - debtor_account.balance); -will_debtor_have_debt=TRUE; -END IF; -- closes has_debt. + -- CREDITOR SIDE. -- Here we figure out whether the creditor would switch -- from debit to a credit situation, and adjust the balance -- accordingly. -IF (NOT creditor_account.has_debt) -- easy case. +IF (NOT creditor_has_debt) -- easy case. THEN -SELECT - INTO new_creditor_balance - FROM amount_add(creditor_account.balance, - in_amount); -will_creditor_have_debit=FALSE; + SELECT + (sum).val, (sum).frac + INTO new_creditor_balance.val, new_creditor_balance.frac + FROM amount_add(creditor_balance, + in_amount); + will_creditor_have_debt=FALSE; ELSE -- creditor had debit but MIGHT switch to credit. -SELECT - INTO new_creditor_balance - FROM amount_left_minus_right(creditor_account.balance, - in_amount); -IF (new_debtor_balance.ok) --- the debt is bigger than the amount, keep --- this last calculated balance but stay debt. -will_creditor_have_debit=TRUE; + SELECT + (diff).val, (diff).frac + INTO new_creditor_balance.val, new_creditor_balance.frac + FROM amount_left_minus_right(creditor_account.balance, + in_amount); + IF (new_debtor_balance.ok) + -- the debt is bigger than the amount, keep + -- this last calculated balance but stay debt. + THEN + will_creditor_have_debt=TRUE; + ELSE + -- the amount would bring the account back to credit, + -- determine by how much. + 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; + END IF; END IF; --- the amount would bring the account back to credit, --- determine by how much. -SELECT - INTO new_creditor_balance - FROM amount_left_minus_right(in_amount, - creditor_account.balance); -will_creditor_have_debit=FALSE; - --- checks and balances set up, now update bank accounts. -UPDATE bank_accounts -SET - balance=new_debtor_balance - has_debt=will_debtor_have_debt -WHERE bank_account_id=in_debtor_account_id; - -UPDATE bank_accounts -SET - balance=new_creditor_balance - has_debt=will_creditor_have_debt -WHERE bank_account_id=in_creditor_account_id; - +out_balance_insufficient=FALSE; -- now actually create the bank transaction. -- debtor side: INSERT INTO bank_account_transactions ( - ,creditor_iban + creditor_iban ,creditor_bic ,creditor_name - ,debtor_iban + ,debtor_iban ,debtor_bic ,debtor_name ,subject - ,amount taler_amount + ,amount ,transaction_date ,account_servicer_reference ,payment_information_id ,end_to_end_id - ,direction direction_enum + ,direction ,bank_account_id ) VALUES ( @@ -256,19 +275,19 @@ VALUES ( -- debtor side: INSERT INTO bank_account_transactions ( - ,creditor_iban + creditor_iban ,creditor_bic ,creditor_name ,debtor_iban ,debtor_bic ,debtor_name ,subject - ,amount taler_amount + ,amount ,transaction_date ,account_servicer_reference ,payment_information_id ,end_to_end_id - ,direction direction_enum + ,direction ,bank_account_id ) VALUES ( @@ -287,8 +306,18 @@ VALUES ( "credit", in_creditor_account_id ); -out_nx_debtor=TRUE; -out_nx_creditor=TRUE; -out_balance_insufficient=FALSE; +-- checks and balances set up, now update bank accounts. +UPDATE bank_accounts +SET + balance=new_debtor_balance, + has_debt=will_debtor_have_debt +WHERE bank_account_id=in_debtor_account_id; + +UPDATE bank_accounts +SET + balance=new_creditor_balance, + has_debt=will_creditor_have_debt +WHERE bank_account_id=in_creditor_account_id; +RETURN; END $$; -COMMIT; +COMMIT; \ No newline at end of file diff --git a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt index 76b99b8a..e7ca959a 100644 --- a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt +++ b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt @@ -40,6 +40,17 @@ enum class TransactionDirection { Credit, Debit } +data class BankInternalTransaction( + val creditorAccountId: Long, + val debtorAccountId: Long, + val subject: String, + val amount: TalerAmount, + val transactionDate: Long, + val accountServicerReference: String, + val endToEndId: String, + val paymentInformationId: String +) + data class BankAccountTransaction( val creditorIban: String, val creditorBic: String, @@ -216,6 +227,22 @@ class Database(private val dbConfig: String) { return myExecute(stmt) } + fun bankAccountSetMaxDebt( + bankAccountLabel: String, + maxDebt: TalerAmount + ): Boolean { + reconnect() + val stmt = prepare(""" + UPDATE bank_accounts + SET max_debt=(?,?)::taler_amount + WHERE bank_account_label=? + """) + stmt.setLong(1, maxDebt.value) + stmt.setInt(2, maxDebt.frac) + stmt.setString(3, bankAccountLabel) + return myExecute(stmt) + } + fun bankAccountGetFromLabel(bankAccountLabel: String): BankAccount? { reconnect() val stmt = prepare(""" @@ -250,7 +277,6 @@ class Database(private val dbConfig: String) { } // More bankAccountGetFrom*() to come, on a needed basis. - /* // BANK ACCOUNT TRANSACTIONS enum class BankTransactionResult { NO_CREDITOR, @@ -259,30 +285,34 @@ class Database(private val dbConfig: String) { CONFLICT } fun bankTransactionCreate( - // tx: BankInternalTransaction - creditTx: BankAccountTransaction, - debitTx: BankAccountTransaction + tx: BankInternalTransaction ): BankTransactionResult { reconnect() val stmt = prepare(""" SELECT out_nx_creditor, out_nx_debtor, out_balance_insufficient - FROM bank_wire_transfer(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) - """ // FIXME: adjust balances. + FROM bank_wire_transfer(?,?,TEXT(?),(?,?)::taler_amount,?,TEXT(?),TEXT(?),TEXT(?)) + """ ) - // 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.execute() + stmt.setLong(1, tx.creditorAccountId) + stmt.setLong(2, tx.debtorAccountId) + stmt.setString(3, tx.subject) + stmt.setLong(4, tx.amount.value) + stmt.setInt(5, tx.amount.frac) + stmt.setLong(6, tx.transactionDate) + stmt.setString(7, tx.accountServicerReference) + stmt.setString(8, tx.paymentInformationId) + stmt.setString(9, tx.endToEndId) + val rs = stmt.executeQuery() + rs.use { + if (!rs.next()) throw internalServerError("Bank transaction didn't properly return") + if (rs.getBoolean("out_nx_debtor")) return BankTransactionResult.NO_DEBTOR + if (rs.getBoolean("out_nx_creditor")) return BankTransactionResult.NO_CREDITOR + if (rs.getBoolean("out_balance_insufficient")) return BankTransactionResult.CONFLICT + return BankTransactionResult.SUCCESS + } } + /* fun bankTransactionGetForHistoryPage( upperBound: Long, bankAccountId: Long, diff --git a/sandbox/src/test/kotlin/DatabaseTest.kt b/sandbox/src/test/kotlin/DatabaseTest.kt index ead97a33..5c43698c 100644 --- a/sandbox/src/test/kotlin/DatabaseTest.kt +++ b/sandbox/src/test/kotlin/DatabaseTest.kt @@ -1,8 +1,5 @@ import org.junit.Test -import tech.libeufin.sandbox.BankAccount -import tech.libeufin.sandbox.Customer -import tech.libeufin.sandbox.Database -import tech.libeufin.sandbox.TalerAmount +import tech.libeufin.sandbox.* import tech.libeufin.util.execCommand class DatabaseTest { @@ -15,6 +12,15 @@ class DatabaseTest { cashoutPayto = "payto://external-IBAN", cashoutCurrency = "KUDOS" ) + private val c1 = Customer( + login = "bar", + passwordHash = "hash", + name = "Bar", + phone = "+00", + email = "foo@b.ar", + cashoutPayto = "payto://external-IBAN", + cashoutCurrency = "KUDOS" + ) fun initDb(): Database { execCommand( listOf( @@ -27,6 +33,40 @@ class DatabaseTest { ) return Database("jdbc:postgresql:///libeufincheck") } + + @Test + fun bankTransactionTest() { + val db = initDb() + // Need accounts first. + db.customerCreate(c) + db.customerCreate(c1) + db.bankAccountCreate(BankAccount( + iban = "XYZ", + bic = "not used", + bankAccountLabel = "foo", + lastNexusFetchRowId = 1L, + owningCustomerId = 1L + )) + db.bankAccountCreate(BankAccount( + iban = "ABC", + bic = "not used", + bankAccountLabel = "bar", + lastNexusFetchRowId = 1L, + owningCustomerId = 2L + )) + db.bankAccountSetMaxDebt("foo", TalerAmount(100, 0)) + val res = db.bankTransactionCreate(BankInternalTransaction( + creditorAccountId = 2, + debtorAccountId = 1, + subject = "test", + amount = TalerAmount(3, 333), + accountServicerReference = "acct-svcr-ref", + endToEndId = "end-to-end-id", + paymentInformationId = "pmtinfid", + transactionDate = 100000L + )) + assert(res == Database.BankTransactionResult.SUCCESS) + } @Test fun customerCreationTest() { val db = initDb() -- cgit v1.2.3