[libeufin] branch master updated: store all bank messages in DB and ingest unknown ones

[gnunet]

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

dold pushed a commit to branch master
in repository libeufin.

The following commit(s) were added to refs/heads/master by this push:
     new e086463  store all bank messages in DB and ingest unknown ones
e086463 is described below

commit e086463eabc803d53d82d3b27387469a05f56176
Author: Florian Dold <address@hidden>
AuthorDate: Mon May 25 02:13:43 2020 +0530

    store all bank messages in DB and ingest unknown ones
---
 nexus/src/main/kotlin/tech/libeufin/nexus/DB.kt    | 46 +++++++++++----
 .../src/main/kotlin/tech/libeufin/nexus/Helpers.kt | 69 +++++++++++++++++++---
 nexus/src/main/kotlin/tech/libeufin/nexus/JSON.kt  |  9 +++
 nexus/src/main/kotlin/tech/libeufin/nexus/Main.kt  | 51 +++++++++-------
 util/src/main/kotlin/XMLUtil.kt                    | 28 ++++++++-
 5 files changed, 160 insertions(+), 43 deletions(-)

diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/DB.kt 
b/nexus/src/main/kotlin/tech/libeufin/nexus/DB.kt
index 9d486b5..72becea 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/DB.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/DB.kt
@@ -86,6 +86,26 @@ class TalerIncomingPaymentEntity(id: EntityID<Long>) : 
LongEntity(id) {
     var refunded by TalerIncomingPayments.refunded
 }
 
+/**
+ * Table that stores all messages we receive from the bank.
+ */
+object NexusBankMessagesTable : IntIdTable() {
+    val bankConnection = reference("bankConnection", NexusBankConnectionsTable)
+    // Unique identifier for the message within the bank connection
+    val messageId = text("messageId")
+    val code = text("code")
+    val message = blob("message")
+}
+
+class NexusBankMessageEntity(id: EntityID<Int>) : IntEntity(id) {
+    companion object : 
IntEntityClass<NexusBankMessageEntity>(NexusBankMessagesTable)
+
+    var bankConnection by NexusBankConnectionEntity referencedOn 
NexusBankMessagesTable.bankConnection
+    var messageId by NexusBankMessagesTable.messageId
+    var code by NexusBankMessagesTable.code
+    var message by NexusBankMessagesTable.message
+}
+
 /**
  * This table contains history "elements" as returned by the bank from a
  * CAMT message.
@@ -100,7 +120,7 @@ object RawBankTransactionsTable : LongIdTable() {
     val counterpartName = text("counterpartName")
     val bookingDate = long("bookingDate")
     val status = text("status") // BOOK or other.
-    val bankAccount = reference("bankAccount", BankAccountsTable)
+    val bankAccount = reference("bankAccount", NexusBankAccountsTable)
 }
 
 class RawBankTransactionEntity(id: EntityID<Long>) : LongEntity(id) {
@@ -115,7 +135,7 @@ class RawBankTransactionEntity(id: EntityID<Long>) : 
LongEntity(id) {
     var counterpartName by RawBankTransactionsTable.counterpartName
     var bookingDate by RawBankTransactionsTable.bookingDate
     var status by RawBankTransactionsTable.status
-    var bankAccount by BankAccountEntity referencedOn 
RawBankTransactionsTable.bankAccount
+    var bankAccount by NexusBankAccountEntity referencedOn 
RawBankTransactionsTable.bankAccount
 }
 
 /**
@@ -170,21 +190,24 @@ class PreparedPaymentEntity(id: EntityID<String>) : 
Entity<String>(id) {
 /**
  * This table holds triples of <iban, bic, holder name>.
  */
-object BankAccountsTable : IdTable<String>() {
+object NexusBankAccountsTable : IdTable<String>() {
     override val id = varchar("id", ID_MAX_LENGTH).primaryKey().entityId()
     val accountHolder = text("accountHolder")
     val iban = text("iban")
     val bankCode = text("bankCode")
     val defaultBankConnection = reference("defaultBankConnection", 
NexusBankConnectionsTable).nullable()
+    // Highest bank message ID that this bank account is aware of.
+    val highestSeenBankMessageId = integer("")
 }
 
-class BankAccountEntity(id: EntityID<String>) : Entity<String>(id) {
-    companion object : EntityClass<String, 
BankAccountEntity>(BankAccountsTable)
+class NexusBankAccountEntity(id: EntityID<String>) : Entity<String>(id) {
+    companion object : EntityClass<String, 
NexusBankAccountEntity>(NexusBankAccountsTable)
 
-    var accountHolder by BankAccountsTable.accountHolder
-    var iban by BankAccountsTable.iban
-    var bankCode by BankAccountsTable.bankCode
-    var defaultBankConnection by NexusBankConnectionEntity 
optionalReferencedOn BankAccountsTable.defaultBankConnection
+    var accountHolder by NexusBankAccountsTable.accountHolder
+    var iban by NexusBankAccountsTable.iban
+    var bankCode by NexusBankAccountsTable.bankCode
+    var defaultBankConnection by NexusBankConnectionEntity 
optionalReferencedOn NexusBankAccountsTable.defaultBankConnection
+    var highestSeenBankMessageId by 
NexusBankAccountsTable.highestSeenBankMessageId
 }
 
 object EbicsSubscribersTable : IntIdTable() {
@@ -255,11 +278,12 @@ fun dbCreateTables() {
             NexusUsersTable,
             PreparedPaymentsTable,
             EbicsSubscribersTable,
-            BankAccountsTable,
+            NexusBankAccountsTable,
             RawBankTransactionsTable,
             TalerIncomingPayments,
             TalerRequestedPayments,
-            NexusBankConnectionsTable
+            NexusBankConnectionsTable,
+            NexusBankMessagesTable
         )
     }
 }
\ No newline at end of file
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/Helpers.kt 
b/nexus/src/main/kotlin/tech/libeufin/nexus/Helpers.kt
index ab86308..e194269 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/Helpers.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/Helpers.kt
@@ -3,6 +3,7 @@ package tech.libeufin.nexus
 import io.ktor.client.HttpClient
 import io.ktor.http.HttpStatusCode
 import io.ktor.request.ApplicationRequest
+import org.jetbrains.exposed.sql.SortOrder
 import org.jetbrains.exposed.sql.and
 import org.jetbrains.exposed.sql.transactions.transaction
 import org.joda.time.DateTime
@@ -15,6 +16,7 @@ import java.time.ZoneId
 import java.time.ZonedDateTime
 import java.time.format.DateTimeFormatter
 import java.util.*
+import javax.sql.rowset.serial.SerialBlob
 
 fun isProduction(): Boolean {
     return System.getenv("NEXUS_PRODUCTION") != null
@@ -102,13 +104,12 @@ fun getEbicsSubscriberDetails(userId: String, 
transportId: String): EbicsClientS
     return getEbicsSubscriberDetailsInternal(subscriber)
 }
 
-// FIXME(dold):  This should put stuff under *fixed* bank account, not some we 
find via the IBAN.
 fun processCamtMessage(
     bankAccountId: String,
     camt53doc: Document
 ) {
     transaction {
-        val acct = BankAccountEntity.findById(bankAccountId)
+        val acct = NexusBankAccountEntity.findById(bankAccountId)
         if (acct == null) {
             throw NexusError(HttpStatusCode.NotFound, "user not found")
         }
@@ -131,10 +132,44 @@ fun processCamtMessage(
     }
 }
 
-suspend fun downloadAndPersistC5xEbics(
+/**
+ * Create new transactions for an account based on bank messages it
+ * did not see before.
+ */
+fun ingestBankMessagesIntoAccount(
+    bankConnectionId: String,
+    bankAccountId: String
+) {
+    transaction {
+        val conn = NexusBankConnectionEntity.findById(bankConnectionId)
+        if (conn == null) {
+            throw NexusError(HttpStatusCode.InternalServerError, "connection 
not found")
+        }
+        val acct = NexusBankAccountEntity.findById(bankAccountId)
+        if (acct == null) {
+            throw NexusError(HttpStatusCode.InternalServerError, "account not 
found")
+        }
+        var lastId = acct.highestSeenBankMessageId
+        NexusBankMessageEntity.find {
+            (NexusBankMessagesTable.bankConnection eq conn.id) and
+                (NexusBankMessagesTable.id greater 
acct.highestSeenBankMessageId)
+        }.orderBy(Pair(NexusBankMessagesTable.id, SortOrder.ASC)).forEach {
+            // FIXME: check if it's CAMT first!
+            val doc = 
XMLUtil.parseStringIntoDom(it.message.toByteArray().toString(Charsets.UTF_8))
+            processCamtMessage(bankAccountId, doc)
+            lastId = it.id.value
+        }
+        acct.highestSeenBankMessageId = lastId
+    }
+
+}
+
+/**
+ * Fetch EBICS C5x and store it locally, but do not update bank accounts.
+ */
+suspend fun fetchEbicsC5x(
     historyType: String,
     client: HttpClient,
-    bankAccountId: String,
     bankConnectionId: String,
     start: String?, // dashed date YYYY-MM(01-12)-DD(01-31)
     end: String?, // dashed date YYYY-MM(01-12)-DD(01-31)
@@ -159,7 +194,23 @@ suspend fun downloadAndPersistC5xEbics(
             response.orderData.unzipWithLambda {
                 logger.debug("Camt entry: ${it.second}")
                 val camt53doc = XMLUtil.parseStringIntoDom(it.second)
-                processCamtMessage(bankAccountId, camt53doc)
+                val msgId = 
camt53doc.pickStringWithRootNs("/*[1]/*[1]/root:GrpHdr/root:MsgId")
+                logger.info("msg id $msgId")
+                transaction {
+                    val conn = 
NexusBankConnectionEntity.findById(bankConnectionId)
+                    if (conn == null) {
+                        throw NexusError(HttpStatusCode.InternalServerError, 
"bank connection missing")
+                    }
+                    val oldMsg = NexusBankMessageEntity.find { 
NexusBankMessagesTable.messageId eq msgId }.firstOrNull()
+                    if (oldMsg == null) {
+                        NexusBankMessageEntity.new {
+                            this.bankConnection = conn
+                            this.code = "C53"
+                            this.messageId = msgId
+                            this.message = 
SerialBlob(it.second.toByteArray(Charsets.UTF_8))
+                        }
+                    }
+                }
             }
         }
         is EbicsDownloadBankErrorResult -> {
@@ -191,9 +242,9 @@ fun createPain001document(paymentData: 
PreparedPaymentEntity): String {
      * PAIN id types.
      */
     val debitorBankAccountLabel = transaction {
-        val debitorBankAcount = BankAccountEntity.find {
-            BankAccountsTable.iban eq paymentData.debitorIban and
-                    (BankAccountsTable.bankCode eq paymentData.debitorBic)
+        val debitorBankAcount = NexusBankAccountEntity.find {
+            NexusBankAccountsTable.iban eq paymentData.debitorIban and
+                    (NexusBankAccountsTable.bankCode eq paymentData.debitorBic)
         }.firstOrNull() ?: throw NexusError(
             HttpStatusCode.NotFound,
             "Please download bank accounts details first (HTD)"
@@ -327,7 +378,7 @@ fun getNexusUser(id: String): NexusUserEntity {
  * it will be the account whose money will pay the wire transfer being defined
  * by this pain document.
  */
-fun addPreparedPayment(paymentData: Pain001Data, debitorAccount: 
BankAccountEntity): PreparedPaymentEntity {
+fun addPreparedPayment(paymentData: Pain001Data, debitorAccount: 
NexusBankAccountEntity): PreparedPaymentEntity {
     val randomId = Random().nextLong()
     return transaction {
         PreparedPaymentEntity.new(randomId.toString()) {
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/JSON.kt 
b/nexus/src/main/kotlin/tech/libeufin/nexus/JSON.kt
index 74435ff..7becd84 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/JSON.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/JSON.kt
@@ -243,6 +243,15 @@ data class BankAccounts(
     var accounts: MutableList<BankAccount> = mutableListOf()
 )
 
+data class BankMessageList(
+    val bankMessages: MutableList<BankMessageInfo> = mutableListOf()
+)
+
+data class BankMessageInfo(
+    val messageId: String,
+    val code: String,
+    val length: Long
+)
 
 /**********************************************************************
  * Convenience types (ONLY used to gather data together in one place) *
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/Main.kt 
b/nexus/src/main/kotlin/tech/libeufin/nexus/Main.kt
index da5894b..8b3054e 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/Main.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/Main.kt
@@ -368,7 +368,7 @@ fun serverMain() {
                 transaction {
                     authenticateRequest(call.request)
                     // FIXME(dold): Only return accounts the user has at least 
read access to?
-                    BankAccountEntity.all().forEach {
+                    NexusBankAccountEntity.all().forEach {
                         
bankAccounts.accounts.add(BankAccount(it.accountHolder, it.iban, it.bankCode, 
it.id.value))
                     }
                 }
@@ -391,7 +391,7 @@ fun serverMain() {
                             "Payment ${uuid} was submitted already"
                         )
                     }
-                    val bankAccount = BankAccountEntity.findById(accountId)
+                    val bankAccount = 
NexusBankAccountEntity.findById(accountId)
                     if (bankAccount == null) {
                         throw NexusError(HttpStatusCode.NotFound, "unknown 
bank account")
                     }
@@ -465,7 +465,7 @@ fun serverMain() {
                 val accountId = ensureNonNull(call.parameters["accountid"])
                 val res = transaction {
                     authenticateRequest(call.request)
-                    val bankAccount = BankAccountEntity.findById(accountId)
+                    val bankAccount = 
NexusBankAccountEntity.findById(accountId)
                     if (bankAccount == null) {
                         throw NexusError(HttpStatusCode.NotFound, "unknown 
bank account")
                     }
@@ -506,7 +506,7 @@ fun serverMain() {
                 }
                 val res = transaction {
                     val user = authenticateRequest(call.request)
-                    val acct = BankAccountEntity.findById(accountid)
+                    val acct = NexusBankAccountEntity.findById(accountid)
                     if (acct == null) {
                         throw NexusError(
                             HttpStatusCode.NotFound,
@@ -531,15 +531,8 @@ fun serverMain() {
                 val body = call.receive<CollectedTransaction>()
                 when (res.connectionType) {
                     "ebics" -> {
-                        downloadAndPersistC5xEbics(
-                            "C53",
-                            client,
-                            accountid,
-                            res.connectionName,
-                            body.start,
-                            body.end,
-                            res.subscriberDetails
-                        )
+                        fetchEbicsC5x("C53",client, res.connectionName, 
body.start, body.end, res.subscriberDetails)
+                        ingestBankMessagesIntoAccount(res.connectionName, 
accountid)
                     }
                     else -> throw NexusError(
                         HttpStatusCode.BadRequest,
@@ -769,21 +762,34 @@ fun serverMain() {
                 call.respond(object {})
             }
 
-            post("/bank-connections/{connid}/ebics/fetch-c52") {
-                val paramsJson = 
call.receiveOrNull<EbicsStandardOrderParamsJson>()
-                val orderParams = if (paramsJson == null) {
-                    EbicsStandardOrderParams()
-                } else {
-                    paramsJson.toOrderParams()
+            get("/bank-connections/{connid}/messages") {
+                val ret = transaction {
+                    val list = BankMessageList()
+                    val conn = requireBankConnection(call, "connid")
+                    NexusBankMessageEntity.find { 
NexusBankMessagesTable.bankConnection eq conn.id }.map {
+                        list.bankMessages.add(BankMessageInfo(it.messageId, 
it.code, it.message.length()))
+                    }
+                    list
                 }
-                val subscriberDetails = transaction {
+                call.respond(ret)
+            }
+
+            post("/bank-connections/{connid}/ebics/fetch-c53") {
+                val paramsJson = call.receiveOrNull<EbicsDateRangeJson>()
+                val ret = transaction {
                     val user = authenticateRequest(call.request)
                     val conn = requireBankConnection(call, "connid")
                     if (conn.type != "ebics") {
                         throw NexusError(HttpStatusCode.BadRequest, "bank 
connection is not of type 'ebics'")
                     }
-                    getEbicsSubscriberDetails(user.id.value, conn.id.value)
+                    object {
+                        val subscriber = 
getEbicsSubscriberDetails(user.id.value, conn.id.value)
+                        val connId = conn.id.value
+                    }
+
                 }
+                fetchEbicsC5x("C53", client, ret.connId, paramsJson?.start, 
paramsJson?.end, ret.subscriber)
+                call.respond(object {})
             }
 
             post("/bank-connections/{connid}/ebics/send-ini") {
@@ -877,7 +883,7 @@ fun serverMain() {
                         transaction {
                             val conn = requireBankConnection(call, "connid")
                             payload.value.partnerInfo.accountInfoList?.forEach 
{
-                                val bankAccount = BankAccountEntity.new(id = 
it.id) {
+                                val bankAccount = 
NexusBankAccountEntity.new(id = it.id) {
                                     accountHolder = it.accountHolder ?: 
"NOT-GIVEN"
                                     iban = 
extractFirstIban(it.accountNumberList)
                                         ?: throw 
NexusError(HttpStatusCode.NotFound, reason = "bank gave no IBAN")
@@ -886,6 +892,7 @@ fun serverMain() {
                                         reason = "bank gave no BIC"
                                     )
                                     defaultBankConnection = conn
+                                    highestSeenBankMessageId = 0
                                 }
                             }
                         }
diff --git a/util/src/main/kotlin/XMLUtil.kt b/util/src/main/kotlin/XMLUtil.kt
index ecaeb35..fa472df 100644
--- a/util/src/main/kotlin/XMLUtil.kt
+++ b/util/src/main/kotlin/XMLUtil.kt
@@ -420,11 +420,37 @@ class XMLUtil private constructor() {
             if (ret.isEmpty()) {
                 throw EbicsProtocolError(HttpStatusCode.NotFound, 
"Unsuccessful XPath query string: $query")
             }
-            return ret as String
+            return ret
         }
     }
 }
 
 fun Document.pickString(xpath: String): String {
     return XMLUtil.getStringFromXpath(this, xpath)
+}
+
+fun Document.pickStringWithRootNs(xpathQuery: String): String {
+    val doc = this
+    val xpath = XPathFactory.newInstance().newXPath()
+    xpath.namespaceContext = object : NamespaceContext {
+        override fun getNamespaceURI(p0: String?): String {
+            return when (p0) {
+                "root" -> doc.documentElement.namespaceURI
+                else -> throw IllegalArgumentException()
+            }
+        }
+
+        override fun getPrefix(p0: String?): String {
+            throw UnsupportedOperationException()
+        }
+
+        override fun getPrefixes(p0: String?): MutableIterator<String> {
+            throw UnsupportedOperationException()
+        }
+    }
+    val ret = xpath.evaluate(xpathQuery, this, XPathConstants.STRING) as String
+    if (ret.isEmpty()) {
+        throw EbicsProtocolError(HttpStatusCode.NotFound, "Unsuccessful XPath 
query string: $xpathQuery")
+    }
+    return ret
 }
\ No newline at end of file

-- 
To stop receiving notification emails like this one, please contact
address@hidden.