diff options
Diffstat (limited to 'nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt')
| -rw-r--r-- | nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt | 527 |
1 files changed, 0 insertions, 527 deletions
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt b/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt deleted file mode 100644 index 8a2b9185..00000000 --- a/nexus/src/main/kotlin/tech/libeufin/nexus/Database.kt +++ /dev/null @@ -1,527 +0,0 @@ -/* - * This file is part of LibEuFin. - * Copyright (C) 2024 Taler Systems S.A. - - * LibEuFin is free software; you can redistribute it and/or modify - * it under the terms of the GNU Affero General Public License as - * published by the Free Software Foundation; either version 3, or - * (at your option) any later version. - - * LibEuFin is distributed in the hope that it will be useful, but - * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY - * or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Affero General - * Public License for more details. - - * You should have received a copy of the GNU Affero General Public - * License along with LibEuFin; see the file COPYING. If not, see - * <http://www.gnu.org/licenses/> - */ -package tech.libeufin.nexus - -import org.postgresql.util.PSQLState -import tech.libeufin.common.* -import java.sql.PreparedStatement -import java.sql.SQLException -import java.text.SimpleDateFormat -import java.time.Instant -import java.util.* - -fun Instant.fmtDate(): String { - val formatter = SimpleDateFormat("yyyy-MM-dd") - return formatter.format(Date.from(this)) -} - -fun Instant.fmtDateTime(): String { - val formatter = SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS") - return formatter.format(Date.from(this)) -} - -// INCOMING PAYMENTS STRUCTS - -/** - * Represents an incoming payment in the database. - */ -data class IncomingPayment( - val amount: TalerAmount, - val wireTransferSubject: String, - val debitPaytoUri: String, - val executionTime: Instant, - /** ISO20022 AccountServicerReference */ - val bankId: String -) { - override fun toString(): String { - return "IN ${executionTime.fmtDate()} $amount '$bankId' debitor=$debitPaytoUri subject=$wireTransferSubject" - } -} - - -// INITIATED PAYMENTS STRUCTS - -enum class DatabaseSubmissionState { - /** - * Submission got both EBICS_OK. - */ - success, - /** - * Submission can be retried (network issue, for example) - */ - transient_failure, - /** - * Submission got at least one error code which was not - * EBICS_OK. - */ - permanent_failure, - /** - * The submitted payment was never witnessed by a camt.5x - * or pain.002 report. - */ - never_heard_back -} - -/** - * Minimal set of information to initiate a new payment in - * the database. - */ -data class InitiatedPayment( - val id: Long, - val amount: TalerAmount, - val wireTransferSubject: String, - val creditPaytoUri: String, - val initiationTime: Instant, - val requestUid: String -) - -/** - * Possible outcomes for inserting a initiated payment - * into the database. - */ -enum class PaymentInitiationOutcome { - - /** - * The row contains a client_request_uid that exists - * already in the database. - */ - UNIQUE_CONSTRAINT_VIOLATION, - /** - * Record successfully created. - */ - SUCCESS -} - -// OUTGOING PAYMENTS STRUCTS - -/** - * Collects data of a booked outgoing payment. - */ -data class OutgoingPayment( - val amount: TalerAmount, - val executionTime: Instant, - /** ISO20022 MessageIdentification */ - val messageId: String, - val creditPaytoUri: String? = null, // not showing in camt.054 - val wireTransferSubject: String? = null // not showing in camt.054 -) { - override fun toString(): String { - return "OUT ${executionTime.fmtDate()} $amount '$messageId' creditor=$creditPaytoUri subject=$wireTransferSubject" - } -} - -/** Outgoing payments registration result */ -data class OutgoingRegistrationResult( - val id: Long, - val initiated: Boolean, - val new: Boolean -) - -/** Incoming payments registration result */ -data class IncomingRegistrationResult( - val id: Long, - val new: Boolean -) - -/** Incoming payments bounce registration result */ -data class IncomingBounceRegistrationResult( - val id: Long, - val bounceId: String, - val new: Boolean -) - -/** - * Performs a INSERT, UPDATE, or DELETE operation. - * - * @return true if at least one row was affected by this operation, - * false on unique constraint violation or no rows were affected. - * - */ -private fun PreparedStatement.maybeUpdate(): Boolean { - try { - this.executeUpdate() - } catch (e: SQLException) { - logger.error(e.message) - if (e.sqlState == PSQLState.UNIQUE_VIOLATION.state) return false - throw e // rethrowing, not to hide other types of errors. - } - return updateCount > 0 -} - -/** - * Collects database connection steps and any operation on the Nexus tables. - */ -class Database(dbConfig: String): DbPool(dbConfig, "libeufin_nexus") { - - // Temporary in memory database to store EBICS order status until we modify the schema to actually store it in the database - var mem: MutableMap<String, String> = mutableMapOf() - - // OUTGOING PAYMENTS METHODS - - /** - * Register an outgoing payment OPTIONALLY reconciling it with its - * initiated payment counterpart. - * - * @param paymentData information about the outgoing payment. - * @return operation outcome enum. - */ - suspend fun registerOutgoing(paymentData: OutgoingPayment): OutgoingRegistrationResult = conn { - val stmt = it.prepareStatement(""" - SELECT out_tx_id, out_initiated, out_found - FROM register_outgoing( - (?,?)::taler_amount - ,? - ,? - ,? - ,? - )""" - ) - val executionTime = paymentData.executionTime.toDbMicros() - ?: throw Exception("Could not convert outgoing payment execution_time to microseconds") - stmt.setLong(1, paymentData.amount.value) - stmt.setInt(2, paymentData.amount.frac) - stmt.setString(3, paymentData.wireTransferSubject) - stmt.setLong(4, executionTime) - stmt.setString(5, paymentData.creditPaytoUri) - stmt.setString(6, paymentData.messageId) - - stmt.executeQuery().use { - when { - !it.next() -> throw Exception("Inserting outgoing payment gave no outcome.") - else -> OutgoingRegistrationResult( - it.getLong("out_tx_id"), - it.getBoolean("out_initiated"), - !it.getBoolean("out_found") - ) - } - } - } - - // INCOMING PAYMENTS METHODS - - /** - * Register an incoming payment and bounce it - * - * @param paymentData information about the incoming payment - * @param requestUid unique identifier of the bounce outgoing payment to - * initiate - * @param bounceAmount amount to send back to the original debtor - * @param bounceSubject subject of the bounce outhoing payment - * @return true if new - */ - suspend fun registerMalformedIncoming( - paymentData: IncomingPayment, - bounceAmount: TalerAmount, - now: Instant - ): IncomingBounceRegistrationResult = conn { - val stmt = it.prepareStatement(""" - SELECT out_found, out_tx_id, out_bounce_id - FROM register_incoming_and_bounce( - (?,?)::taler_amount - ,? - ,? - ,? - ,? - ,(?,?)::taler_amount - ,? - )""" - ) - val refundTimestamp = now.toDbMicros() - ?: throw Exception("Could not convert refund execution time from Instant.now() to microsends.") - val executionTime = paymentData.executionTime.toDbMicros() - ?: throw Exception("Could not convert payment execution time from Instant to microseconds.") - stmt.setLong(1, paymentData.amount.value) - stmt.setInt(2, paymentData.amount.frac) - stmt.setString(3, paymentData.wireTransferSubject) - stmt.setLong(4, executionTime) - stmt.setString(5, paymentData.debitPaytoUri) - stmt.setString(6, paymentData.bankId) - stmt.setLong(7, bounceAmount.value) - stmt.setInt(8, bounceAmount.frac) - stmt.setLong(9, refundTimestamp) - stmt.executeQuery().use { - when { - !it.next() -> throw Exception("Inserting malformed incoming payment gave no outcome") - else -> IncomingBounceRegistrationResult( - it.getLong("out_tx_id"), - it.getString("out_bounce_id"), - !it.getBoolean("out_found") - ) - } - } - } - - /** - * Register an talerable incoming payment - * - * @param paymentData incoming talerable payment. - * @param reservePub reserve public key. The caller is - * responsible to check it. - */ - suspend fun registerTalerableIncoming( - paymentData: IncomingPayment, - reservePub: EddsaPublicKey - ): IncomingRegistrationResult = conn { conn -> - val stmt = conn.prepareStatement(""" - SELECT out_found, out_tx_id - FROM register_incoming_and_talerable( - (?,?)::taler_amount - ,? - ,? - ,? - ,? - ,? - )""" - ) - val executionTime = paymentData.executionTime.toDbMicros() - ?: throw Exception("Could not convert payment execution time from Instant to microseconds.") - stmt.setLong(1, paymentData.amount.value) - stmt.setInt(2, paymentData.amount.frac) - stmt.setString(3, paymentData.wireTransferSubject) - stmt.setLong(4, executionTime) - stmt.setString(5, paymentData.debitPaytoUri) - stmt.setString(6, paymentData.bankId) - stmt.setBytes(7, reservePub.raw) - stmt.executeQuery().use { - when { - !it.next() -> throw Exception("Inserting talerable incoming payment gave no outcome") - else -> IncomingRegistrationResult( - it.getLong("out_tx_id"), - !it.getBoolean("out_found") - ) - } - } - } - - /** - * Get the last execution time of outgoing transactions. - * - * @return [Instant] or null if no results were found - */ - suspend fun outgoingPaymentLastExecTime(): Instant? = conn { conn -> - val stmt = conn.prepareStatement( - "SELECT MAX(execution_time) as latest_execution_time FROM outgoing_transactions" - ) - stmt.executeQuery().use { - if (!it.next()) return@conn null - val timestamp = it.getLong("latest_execution_time") - if (timestamp == 0L) return@conn null - return@conn timestamp.microsToJavaInstant() - ?: throw Exception("Could not convert latest_execution_time to Instant") - } - } - - /** - * Get the last execution time of an incoming transaction. - * - * @return [Instant] or null if no results were found - */ - suspend fun incomingPaymentLastExecTime(): Instant? = conn { conn -> - val stmt = conn.prepareStatement( - "SELECT MAX(execution_time) as latest_execution_time FROM incoming_transactions" - ) - stmt.executeQuery().use { - if (!it.next()) return@conn null - val timestamp = it.getLong("latest_execution_time") - if (timestamp == 0L) return@conn null - return@conn timestamp.microsToJavaInstant() - ?: throw Exception("Could not convert latest_execution_time to Instant") - } - } - - /** - * Checks if the reserve public key already exists. - * - * @param maybeReservePub reserve public key to look up - * @return true if found, false otherwise - */ - suspend fun isReservePubFound(maybeReservePub: EddsaPublicKey): Boolean = conn { conn -> - val stmt = conn.prepareStatement(""" - SELECT 1 - FROM talerable_incoming_transactions - WHERE reserve_public_key = ?; - """) - stmt.setBytes(1, maybeReservePub.raw) - val res = stmt.executeQuery() - res.use { - return@conn it.next() - } - } - - // INITIATED PAYMENTS METHODS - - /** - * Represents all the states but "unsubmitted" related to an - * initiated payment. Unsubmitted gets set by default by the - * database and there's no case where it has to be reset to an - * initiated payment. - */ - - /** - * Sets the submission state of an initiated payment. Transparently - * sets the last_submission_time column too, as this corresponds to the - * time when we set the state. - * - * @param rowId row ID of the record to set. - * @param submissionState which state to set. - * @return true on success, false if no payment was affected. - */ - suspend fun initiatedPaymentSetSubmittedState( - rowId: Long, - submissionState: DatabaseSubmissionState - ): Boolean = conn { conn -> - val stmt = conn.prepareStatement(""" - UPDATE initiated_outgoing_transactions - SET submitted = submission_state(?), last_submission_time = ? - WHERE initiated_outgoing_transaction_id = ? - """ - ) - val now = Instant.now() - stmt.setString(1, submissionState.name) - stmt.setLong(2, now.toDbMicros() ?: run { - throw Exception("Submission time could not be converted to microseconds for the database.") - }) - stmt.setLong(3, rowId) - return@conn stmt.maybeUpdate() - } - - /** - * Sets the failure reason to an initiated payment. - * - * @param rowId row ID of the record to set. - * @param failureMessage error associated to this initiated payment. - * @return true on success, false if no payment was affected. - */ - suspend fun initiatedPaymentSetFailureMessage(rowId: Long, failureMessage: String): Boolean = conn { conn -> - val stmt = conn.prepareStatement(""" - UPDATE initiated_outgoing_transactions - SET failure_message = ? - WHERE initiated_outgoing_transaction_id=? - """ - ) - stmt.setString(1, failureMessage) - stmt.setLong(2, rowId) - return@conn stmt.maybeUpdate() - } - - /** - * Gets any initiated payment that was not submitted to the - * bank yet. - * - * @param currency in which currency should the payment be submitted to the bank. - * @return [Map] of the initiated payment row ID and [InitiatedPayment] - */ - suspend fun initiatedPaymentsSubmittableGet(currency: String): List<InitiatedPayment> = conn { conn -> - val stmt = conn.prepareStatement(""" - SELECT - initiated_outgoing_transaction_id - ,(amount).val as amount_val - ,(amount).frac as amount_frac - ,wire_transfer_subject - ,credit_payto_uri - ,initiation_time - ,request_uid - FROM initiated_outgoing_transactions - WHERE (submitted='unsubmitted' OR submitted='transient_failure') - AND ((amount).val != 0 OR (amount).frac != 0); - """) - stmt.all { - val rowId = it.getLong("initiated_outgoing_transaction_id") - val initiationTime = it.getLong("initiation_time").microsToJavaInstant() - if (initiationTime == null) { // nexus fault - throw Exception("Found invalid timestamp at initiated payment with ID: $rowId") - } - InitiatedPayment( - id = it.getLong("initiated_outgoing_transaction_id"), - amount = it.getAmount("amount", currency), - creditPaytoUri = it.getString("credit_payto_uri"), - wireTransferSubject = it.getString("wire_transfer_subject"), - initiationTime = initiationTime, - requestUid = it.getString("request_uid") - ) - } - } - /** - * Initiate a payment in the database. The "submit" - * command is then responsible to pick it up and submit - * it to 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 initiatedPaymentCreate(paymentData: InitiatedPayment): PaymentInitiationOutcome = conn { conn -> - val stmt = conn.prepareStatement(""" - INSERT INTO initiated_outgoing_transactions ( - amount - ,wire_transfer_subject - ,credit_payto_uri - ,initiation_time - ,request_uid - ) VALUES ( - (?,?)::taler_amount - ,? - ,? - ,? - ,? - ) - """) - stmt.setLong(1, paymentData.amount.value) - stmt.setInt(2, paymentData.amount.frac) - stmt.setString(3, paymentData.wireTransferSubject) - stmt.setString(4, paymentData.creditPaytoUri.toString()) - val initiationTime = paymentData.initiationTime.toDbMicros() ?: run { - throw Exception("Initiation time could not be converted to microseconds for the database.") - } - stmt.setLong(5, initiationTime) - stmt.setString(6, paymentData.requestUid) // can be null. - if (stmt.maybeUpdate()) - return@conn PaymentInitiationOutcome.SUCCESS - /** - * _very_ likely, Nexus didn't check the request idempotency, - * as the row ID would never fall into the following problem. - */ - return@conn PaymentInitiationOutcome.UNIQUE_CONSTRAINT_VIOLATION - } - - /** - * Gets the ID of an initiated payment. Useful to link it to its - * outgoing payment witnessed in a bank record. - * - * @param uid UID as given by Nexus when it initiated the payment. - * This value then gets specified as the MsgId of pain.001, - * and it gets associated by the bank to the booked entries - * in camt.05x reports. - * @return the initiated payment row ID, or null if not found. NOTE: - * null gets returned even when the initiated payment exists, - * *but* it was NOT flagged as submitted. - */ - suspend fun initiatedPaymentGetFromUid(uid: String): Long? = conn { conn -> - val stmt = conn.prepareStatement(""" - SELECT initiated_outgoing_transaction_id - FROM initiated_outgoing_transactions - WHERE request_uid = ? AND submitted = 'success'; - """) - stmt.setString(1, uid) - val res = stmt.executeQuery() - res.use { - if (!it.next()) return@conn null - return@conn it.getLong("initiated_outgoing_transaction_id") - } - } -}
\ No newline at end of file |