[libeufin] branch master updated: bank DB refactoring.

[gnunet]

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 3c5dc5b3 bank DB refactoring.
3c5dc5b3 is described below

commit 3c5dc5b3c2c5da54f4c26651a28ab3fff042b68d
Author: MS <ms@taler.net>
AuthorDate: Wed Aug 30 19:28:25 2023 +0200

    bank DB refactoring.
    
    Testing customer and bank account tables.
---
 database-versioning/new/libeufin-bank-0001.sql     |  2 +-
 .../main/kotlin/tech/libeufin/sandbox/Database.kt  | 80 +++++++++++++++++-----
 sandbox/src/test/kotlin/DatabaseTest.kt            | 44 +++++++++++-
 3 files changed, 105 insertions(+), 21 deletions(-)

diff --git a/database-versioning/new/libeufin-bank-0001.sql 
b/database-versioning/new/libeufin-bank-0001.sql
index b5492041..5497b793 100644
--- a/database-versioning/new/libeufin-bank-0001.sql
+++ b/database-versioning/new/libeufin-bank-0001.sql
@@ -60,7 +60,7 @@ CREATE TABLE IF NOT EXISTS configuration
 
 CREATE TABLE IF NOT EXISTS customers
   (customer_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
-  ,login TEXT NOT NULL
+  ,login TEXT NOT NULL UNIQUE
   ,password_hash TEXT NOT NULL
   ,name TEXT
   ,email TEXT
diff --git a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt 
b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Database.kt
index 9842c7ce..6eae4aff 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.sql.SQLException
 import java.util.*
 
 private const val DB_CTR_LIMIT = 1000000
@@ -21,8 +22,8 @@ data class Customer(
 )
 
 data class TalerAmount(
-    val value: Int, // maps to INT4
-    val frac: Long // maps to INT8
+    val value: Long,
+    val frac: Int
 )
 
 data class BankAccount(
@@ -32,7 +33,7 @@ data class BankAccount(
     val owningCustomerId: Long,
     val isPublic: Boolean = false,
     val lastNexusFetchRowId: Long,
-    val balance: TalerAmount
+    val balance: TalerAmount? = null
 )
 
 enum class TransactionDirection {
@@ -98,6 +99,23 @@ class Database(private val dbConfig: String) {
         return ps
     }
 
+    /**
+     * Helper that returns false if the row to be inserted
+     * hits a unique key constraint violation, true when it
+     * succeeds.  Any other error throws exception.
+     */
+    private fun myExecute(stmt: PreparedStatement): Boolean {
+        try {
+            stmt.execute()
+        } catch (e: SQLException) {
+            logger.error(e.message)
+            if (e.errorCode == 0) return false // unique key violation.
+            // rethrowing, not to hide other types of errors.
+            throw e
+        }
+        return true
+    }
+
     // CONFIG
     fun configGet(configKey: String): String? {
         reconnect()
@@ -118,7 +136,7 @@ class Database(private val dbConfig: String) {
     }
 
     // CUSTOMERS
-    fun customerCreate(customer: Customer) {
+    fun customerCreate(customer: Customer): Boolean {
         reconnect()
         val stmt = prepare("""
             INSERT INTO customers (
@@ -140,7 +158,8 @@ class Database(private val dbConfig: String) {
         stmt.setString(5, customer.phone)
         stmt.setString(6, customer.cashoutPayto)
         stmt.setString(7, customer.cashoutCurrency)
-        stmt.execute()
+
+        return myExecute(stmt)
     }
     fun customerGetFromLogin(login: String): Customer? {
         reconnect()
@@ -173,38 +192,65 @@ class Database(private val dbConfig: String) {
     // Possibly more "customerGetFrom*()" to come.
 
     // BANK ACCOUNTS
-
-    /*
     // Returns false on conflicts.
     fun bankAccountCreate(bankAccount: BankAccount): Boolean {
         reconnect()
         val stmt = prepare("""
-            INSERT INTO bank_accounts (col, col, ..) VALUES (?, ?, ?, ?, ?, ?, 
?)
+            INSERT INTO bank_accounts
+              (iban
+              ,bic
+              ,bank_account_label
+              ,owning_customer_id
+              ,is_public
+              ,last_nexus_fetch_row_id
+              )
+            VALUES (?, ?, ?, ?, ?, ?)
         """)
         stmt.setString(1, bankAccount.iban)
         stmt.setString(2, bankAccount.bic)
         stmt.setString(3, bankAccount.bankAccountLabel)
         stmt.setLong(4, bankAccount.owningCustomerId)
-        stmt.setLong(5, bankAccount.lastNexusFetchRowId)
+        stmt.setBoolean(5, bankAccount.isPublic)
+        stmt.setLong(6, 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.
+        return myExecute(stmt)
     }
 
-    fun bankAccountGetFromLabel(bankAccountLabel: String): BankAccount {
+    fun bankAccountGetFromLabel(bankAccountLabel: String): BankAccount? {
         reconnect()
         val stmt = prepare("""
-            SELECT * FROM bank_accounts WHERE bank_account_label=?
+            SELECT
+             iban
+             ,bic
+             ,owning_customer_id
+             ,is_public
+             ,last_nexus_fetch_row_id
+             ,(balance).val AS balance_value
+             ,(balance).frac AS balance_frac
+            FROM bank_accounts
+            WHERE bank_account_label=?
         """)
         stmt.setString(1, bankAccountLabel)
-        if (!stmt.execute()) return
-        stmt.use { // why .use{} and not directly access .resultSet?
-            cb(stmt.resultSet)
+
+        val rs = stmt.executeQuery()
+        rs.use {
+            if (!it.next()) return null
+            return BankAccount(
+                iban = it.getString("iban"),
+                bic = it.getString("bic"),
+                balance = TalerAmount(
+                    it.getLong("balance_value"),
+                    it.getInt("balance_frac")
+                ),
+                bankAccountLabel = bankAccountLabel,
+                lastNexusFetchRowId = it.getLong("last_nexus_fetch_row_id"),
+                owningCustomerId = it.getLong("owning_customer_id")
+            )
         }
     }
     // More bankAccountGetFrom*() to come, on a needed basis.
 
+    /*
     // BANK ACCOUNT TRANSACTIONS
     enum class BankTransactionResult {
         NO_CREDITOR,
diff --git a/sandbox/src/test/kotlin/DatabaseTest.kt 
b/sandbox/src/test/kotlin/DatabaseTest.kt
index 77a85312..ead97a33 100644
--- a/sandbox/src/test/kotlin/DatabaseTest.kt
+++ b/sandbox/src/test/kotlin/DatabaseTest.kt
@@ -1,9 +1,21 @@
 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.util.execCommand
 
 class DatabaseTest {
-    fun initDb() {
+    private val c = Customer(
+        login = "foo",
+        passwordHash = "hash",
+        name = "Foo",
+        phone = "+00",
+        email = "foo@b.ar",
+        cashoutPayto = "payto://external-IBAN",
+        cashoutCurrency = "KUDOS"
+    )
+    fun initDb(): Database {
         execCommand(
             listOf(
                 "libeufin-bank-dbinit",
@@ -13,14 +25,40 @@ class DatabaseTest {
             ),
             throwIfFails = true
         )
+        return Database("jdbc:postgresql:///libeufincheck")
+    }
+    @Test
+    fun customerCreationTest() {
+        val db = initDb()
+        assert(db.customerGetFromLogin("foo") == null)
+        db.customerCreate(c)
+        assert(db.customerGetFromLogin("foo")?.name == "Foo")
+        // Trigger conflict.
+        assert(!db.customerCreate(c))
     }
     @Test
     fun configTest() {
-        initDb()
-        val db = Database("jdbc:postgresql:///libeufincheck")
+        val db = initDb()
         assert(db.configGet("bar") == null)
         assert(db.configGet("bar") == null)
         db.configSet("foo", "bar")
         assert(db.configGet("foo") == "bar")
     }
+    @Test
+    fun bankAccountTest() {
+        val db = initDb()
+        assert(db.bankAccountGetFromLabel("foo") == null)
+        val bankAccount = BankAccount(
+            iban = "not used",
+            bic = "not used",
+            bankAccountLabel = "foo",
+            lastNexusFetchRowId = 1L,
+            owningCustomerId = 1L
+        )
+        db.customerCreate(c) // Satisfies the REFERENCE
+        assert(db.bankAccountCreate(bankAccount))
+        assert(!db.bankAccountCreate(bankAccount)) // Triggers conflict.
+        assert(db.bankAccountGetFromLabel("foo")?.bankAccountLabel == "foo")
+        
assert(db.bankAccountGetFromLabel("foo")?.balance?.equals(TalerAmount(0, 0)) == 
true)
+    }
 }
\ No newline at end of file

-- 
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.