[libeufin] 02/02: nexus database

[gnunet]

This is an automated email from the git hooks/post-receive script.

ms pushed a commit to branch master
in repository libeufin.

commit 51d8d79ab4683ef406f6445ffa627958028ddf51
Author: MS <ms@taler.net>
AuthorDate: Fri Oct 20 17:19:28 2023 +0200

    nexus database
    
    loading SQL files from disk, connecting to the database,
    and inserting initiated payments
---
 bank/src/main/kotlin/tech/libeufin/bank/helpers.kt |   2 +-
 database-versioning/libeufin-nexus-0001.sql        |   6 +-
 nexus/build.gradle                                 |   4 +-
 .../main/kotlin/tech/libeufin/nexus/Database.kt    | 143 +++++++++++++++++++++
 nexus/src/test/kotlin/Common.kt                    |  20 +++
 nexus/src/test/kotlin/DatabaseTest.kt              |  27 ++++
 6 files changed, 197 insertions(+), 5 deletions(-)

diff --git a/bank/src/main/kotlin/tech/libeufin/bank/helpers.kt 
b/bank/src/main/kotlin/tech/libeufin/bank/helpers.kt
index d8123ed2..ce283be7 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/helpers.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/helpers.kt
@@ -1,6 +1,6 @@
 /*
  * This file is part of LibEuFin.
- * Copyright (C) 2019 Stanisci and Dold.
+ * Copyright (C) 2023 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
diff --git a/database-versioning/libeufin-nexus-0001.sql 
b/database-versioning/libeufin-nexus-0001.sql
index f12c3fde..39f3bf5c 100644
--- a/database-versioning/libeufin-nexus-0001.sql
+++ b/database-versioning/libeufin-nexus-0001.sql
@@ -55,9 +55,9 @@ CREATE TABLE IF NOT EXISTS initiated_outgoing_transactions
   ,credit_payto_uri TEXT NOT NULL
   ,outgoing_transaction_id INT8 REFERENCES outgoing_transactions 
(outgoing_transaction_id)
   ,submitted BOOL DEFAULT FALSE 
-  ,hidden BOOL DEFAULT FALSE -- FIXME: exaplain this.
-  ,client_request_uuid TEXT NOT NULL UNIQUE
-    ,failure_message TEXT -- NOTE: that may mix soon failures (those found at 
initiation time), or late failures (those found out along a fetch operation)
+  ,hidden BOOL DEFAULT FALSE -- FIXME: explain this.
+  ,client_request_uuid TEXT UNIQUE
+  ,failure_message TEXT -- NOTE: that may mix soon failures (those found at 
initiation time), or late failures (those found out along a fetch operation)
     );
 
 COMMENT ON COLUMN initiated_outgoing_transactions.outgoing_transaction_id
diff --git a/nexus/build.gradle b/nexus/build.gradle
index 28fecdc3..8a6ea2f3 100644
--- a/nexus/build.gradle
+++ b/nexus/build.gradle
@@ -61,7 +61,9 @@ dependencies {
 
     // Database connection driver
     implementation group: 'org.xerial', name: 'sqlite-jdbc', version: 
'3.36.0.1'
-    implementation 'org.postgresql:postgresql:42.2.23.jre7'
+    // implementation 'org.postgresql:postgresql:42.2.23.jre7'
+    implementation 'org.postgresql:postgresql:42.6.0'
+    implementation 'com.zaxxer:HikariCP:5.0.1'
 
     // Ktor, an HTTP client and server library (no need for nexus-setup)
     implementation "io.ktor:ktor-server-core:$ktor_version"
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt 
b/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt
index c786e98e..9e15ad89 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt
@@ -1,2 +1,145 @@
 package tech.libeufin.nexus
 
+import kotlinx.coroutines.Dispatchers
+import kotlinx.coroutines.withContext
+import org.postgresql.jdbc.PgConnection
+import tech.libeufin.util.pgDataSource
+import com.zaxxer.hikari.*
+import tech.libeufin.util.stripIbanPayto
+import tech.libeufin.util.toDbMicros
+import java.sql.PreparedStatement
+import java.sql.SQLException
+import java.time.Instant
+
+/* only importing TalerAmount from bank ONCE that Nexus has
+* its httpd component.  */
+data class TalerAmount(
+    val value: Long,
+    val fraction: Int,
+    val currency: String
+)
+
+/**
+ * Minimal set of information to initiate a new payment in
+ * the database.
+ */
+data class InitiatedPayment(
+    val amount: TalerAmount,
+    val wireTransferSubject: String,
+    val executionTime: Instant,
+    val creditPaytoUri: String,
+    val clientRequestUuid: String? = null
+)
+
+/**
+ * Possible outcomes for inserting a initiated payment
+ * into the database.
+ */
+enum class PaymentInitiationOutcome {
+    BAD_TIMESTAMP,
+    BAD_CREDIT_PAYTO,
+    UNIQUE_CONSTRAINT_VIOLATION,
+    SUCCESS
+}
+
+/**
+ * Performs a INSERT, UPDATE, or DELETE operation.
+ *
+ * @return true on success, false on unique constraint violation,
+ *         rethrows on any other issue.
+ */
+private fun PreparedStatement.maybeUpdate(): Boolean {
+    try {
+        this.executeUpdate()
+    } catch (e: SQLException) {
+        logger.error(e.message)
+        if (e.sqlState == "23505") return false // unique_violation
+        throw e // rethrowing, not to hide other types of errors.
+    }
+    return true
+}
+
+/**
+ * Collects database connection steps and any operation on the Nexus tables.
+ */
+class Database(dbConfig: String): java.io.Closeable {
+    val dbPool: HikariDataSource
+
+    init {
+        val pgSource = pgDataSource(dbConfig)
+        val config = HikariConfig();
+        config.dataSource = pgSource
+        config.connectionInitSql = "SET search_path TO libeufin_nexus;"
+        config.validate()
+        dbPool = HikariDataSource(config);
+    }
+
+    /**
+     * Closes the database connection.
+     */
+    override fun close() {
+        dbPool.close()
+    }
+
+    /**
+     * Moves the database operations where they can block, without
+     * blocking the whole process.
+     *
+     * @param lambda actual statement preparation and execution logic.
+     * @return what lambda returns.
+     */
+    suspend fun <R> runConn(lambda: suspend (PgConnection) -> R): R {
+        // Use a coroutine dispatcher that we can block as JDBC API is blocking
+        return withContext(Dispatchers.IO) {
+            val conn = dbPool.getConnection()
+            conn.use { it -> lambda(it.unwrap(PgConnection::class.java)) }
+        }
+    }
+
+    /**
+     * Initiate a payment in the database.  The "submit"
+     * command is then responsible to pick it up and submit
+     * it at the bank.
+     *
+     * @param paymentData any data that's used to prepare the payment.
+     * @return true if the insertion went through, false in case of errors.
+     */
+    suspend fun initiatePayment(paymentData: InitiatedPayment): 
PaymentInitiationOutcome = runConn { conn ->
+        val stmt = conn.prepareStatement("""
+           INSERT INTO initiated_outgoing_transactions (
+             amount
+             ,wire_transfer_subject
+             ,execution_time
+             ,credit_payto_uri
+             ,client_request_uuid
+           ) VALUES (
+             (?,?)::taler_amount
+             ,?
+             ,?
+             ,?
+             ,?           
+           )
+        """)
+        stmt.setLong(1, paymentData.amount.value)
+        stmt.setInt(2, paymentData.amount.fraction)
+        stmt.setString(3, paymentData.wireTransferSubject)
+        val executionTime = paymentData.executionTime.toDbMicros() ?: run {
+            logger.error("Execution time could not be converted to 
microseconds for the database.")
+            return@runConn PaymentInitiationOutcome.BAD_TIMESTAMP // nexus 
fault.
+        }
+        stmt.setLong(4, executionTime)
+        val paytoOnlyIban = stripIbanPayto(paymentData.creditPaytoUri) ?: run {
+            logger.error("Credit Payto address is invalid.")
+            return@runConn PaymentInitiationOutcome.BAD_CREDIT_PAYTO // client 
fault.
+        }
+        stmt.setString(5, paytoOnlyIban)
+        stmt.setString(6, paymentData.clientRequestUuid) // can be null.
+        if (stmt.maybeUpdate())
+            return@runConn PaymentInitiationOutcome.SUCCESS
+        /**
+         * _very_ likely, Nexus didn't check the request idempotency,
+         * as the row ID would never fall into the following problem.
+         */
+        return@runConn PaymentInitiationOutcome.UNIQUE_CONSTRAINT_VIOLATION
+    }
+}
\ No newline at end of file
diff --git a/nexus/src/test/kotlin/Common.kt b/nexus/src/test/kotlin/Common.kt
index b9284822..4845aaa8 100644
--- a/nexus/src/test/kotlin/Common.kt
+++ b/nexus/src/test/kotlin/Common.kt
@@ -4,6 +4,9 @@ import io.ktor.client.request.*
 import kotlinx.serialization.json.Json
 import kotlinx.serialization.modules.SerializersModule
 import tech.libeufin.nexus.*
+import tech.libeufin.util.DatabaseConfig
+import tech.libeufin.util.initializeDatabaseTables
+import tech.libeufin.util.resetDatabaseTables
 import java.security.interfaces.RSAPrivateCrtKey
 
 val j = Json {
@@ -18,6 +21,23 @@ val config: EbicsSetupConfig = run {
     EbicsSetupConfig(handle)
 }
 
+fun prepDb(cfg: TalerConfig): Database {
+    cfg.loadDefaults()
+    val dbCfg = DatabaseConfig(
+        dbConnStr = "postgresql:///libeufincheck",
+        sqlDir = cfg.requirePath("paths", "datadir") + "sql"
+    )
+    println("SQL dir for testing: ${dbCfg.sqlDir}")
+    try {
+        resetDatabaseTables(dbCfg, "libeufin-nexus")
+    } catch (e: Exception) {
+        logger.warn("Resetting an empty database throws, tolerating this...")
+        logger.warn(e.message)
+    }
+    initializeDatabaseTables(dbCfg, "libeufin-nexus")
+    return Database(dbCfg.dbConnStr)
+}
+
 val clientKeys = generateNewKeys()
 
 // Gets an HTTP client whose requests are going to be served by 'handler'.
diff --git a/nexus/src/test/kotlin/DatabaseTest.kt 
b/nexus/src/test/kotlin/DatabaseTest.kt
new file mode 100644
index 00000000..2858af8e
--- /dev/null
+++ b/nexus/src/test/kotlin/DatabaseTest.kt
@@ -0,0 +1,27 @@
+import kotlinx.coroutines.runBlocking
+import org.junit.Test
+import tech.libeufin.nexus.InitiatedPayment
+import tech.libeufin.nexus.NEXUS_CONFIG_SOURCE
+import tech.libeufin.nexus.PaymentInitiationOutcome
+import tech.libeufin.nexus.TalerAmount
+import java.time.Instant
+import kotlin.test.assertEquals
+
+class DatabaseTest {
+
+    @Test
+    fun paymentInitiation() {
+        val db = prepDb(TalerConfig(NEXUS_CONFIG_SOURCE))
+        val initPay = InitiatedPayment(
+            amount = TalerAmount(44, 0, "KUDOS"),
+            creditPaytoUri = "payto://iban/not-used",
+            executionTime = Instant.now(),
+            wireTransferSubject = "test",
+            clientRequestUuid = "unique"
+        )
+        runBlocking {
+            assertEquals(db.initiatePayment(initPay), 
PaymentInitiationOutcome.SUCCESS)
+            assertEquals(db.initiatePayment(initPay), 
PaymentInitiationOutcome.UNIQUE_CONSTRAINT_VIOLATION)
+        }
+    }
+}
\ No newline at end of file

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