commit e086463eabc803d53d82d3b27387469a05f56176
parent 18dd2f70ed5d87f4bcdb36028571bb5976387230
Author: Florian Dold <florian.dold@gmail.com>
Date: Mon, 25 May 2020 02:13:43 +0530
store all bank messages in DB and ingest unknown ones
Diffstat:
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
@@ -87,6 +87,26 @@ class TalerIncomingPaymentEntity(id: EntityID<Long>) : LongEntity(id) {
}
/**
+ * 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
@@ -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
@@ -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
@@ -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
@@ -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
