Add migration test and improve SQL drop script

9 files changed, 203 insertions, 143 deletions

diff --git a/bank/src/main/kotlin/tech/libeufin/bank/Main.kt b/bank/src/main/kotlin/tech/libeufin/bank/Main.kt
index 0663e49e..31488c03 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/Main.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/Main.kt
@@ -261,13 +261,14 @@ class BankDbInit : CliktCommand("Initialize the libeufin-bank database", name =
         val config = talerConfig(common.config)
         val cfg = config.loadDbConfig()
         val ctx = config.loadBankConfig()
-        Database(cfg.dbConnStr, ctx.regionalCurrency, ctx.fiatCurrency).use { db -> 
+        Database(cfg.dbConnStr, ctx.regionalCurrency, ctx.fiatCurrency).use { db ->
             db.conn { conn ->
                 if (requestReset) {
                     resetDatabaseTables(conn, cfg, sqlFilePrefix = "libeufin-bank")
                 }
                 initializeDatabaseTables(conn, cfg, sqlFilePrefix = "libeufin-bank")
             }
+            
             // Create admin account if missing
             val res = createAdminAccount(db, ctx) // logs provided by the helper
             when (res) {
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/db/NotificationWatcher.kt b/bank/src/main/kotlin/tech/libeufin/bank/db/NotificationWatcher.kt
index eb95e2c4..cd64e77f 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/db/NotificationWatcher.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/db/NotificationWatcher.kt
@@ -52,7 +52,7 @@ internal class NotificationWatcher(private val pgSource: PGSimpleDataSource) {
             runBlocking {
                 while (true) {
                     try {
-                        val conn = pgSource.pgConnection()
+                        val conn = pgSource.pgConnection("libeufin_bank")
 
                         // Listen to all notifications channels
                         conn.execSQLUpdate("LISTEN bank_tx")
diff --git a/bank/src/test/kotlin/helpers.kt b/bank/src/test/kotlin/helpers.kt
index bb720197..0b29a0b0 100644
--- a/bank/src/test/kotlin/helpers.kt
+++ b/bank/src/test/kotlin/helpers.kt
@@ -57,112 +57,107 @@ fun genTmpPayTo(): IbanPayto {
 fun setup(
     conf: String = "test.conf",
     lambda: suspend (Database, BankConfig) -> Unit
-) {
+) = runBlocking {
     val config = talerConfig(Path("conf/$conf"))
     val dbCfg = config.loadDbConfig()
     val ctx = config.loadBankConfig()
     Database(dbCfg.dbConnStr, ctx.regionalCurrency, ctx.fiatCurrency).use {
-        runBlocking {
-            it.conn { conn ->
-                resetDatabaseTables(conn, dbCfg, "libeufin-nexus")
-                initializeDatabaseTables(conn, dbCfg, "libeufin-nexus")
-                resetDatabaseTables(conn, dbCfg, "libeufin-bank")
-                initializeDatabaseTables(conn, dbCfg, "libeufin-bank")
-                val sqlProcedures = Path("${dbCfg.sqlDir}/libeufin-conversion-setup.sql")
-                conn.execSQLUpdate(sqlProcedures.readText())
-            }
-            lambda(it, ctx)
+        it.conn { conn ->
+            resetDatabaseTables(conn, dbCfg, "libeufin-nexus")
+            initializeDatabaseTables(conn, dbCfg, "libeufin-nexus")
+            resetDatabaseTables(conn, dbCfg, "libeufin-bank")
+            initializeDatabaseTables(conn, dbCfg, "libeufin-bank")
+            val sqlProcedures = Path("${dbCfg.sqlDir}/libeufin-conversion-setup.sql")
+            conn.execSQLUpdate(sqlProcedures.readText())
         }
+        lambda(it, ctx)
     }
 }
 
 fun bankSetup(
     conf: String = "test.conf",    
     lambda: suspend ApplicationTestBuilder.(Database) -> Unit
-) {
-    setup(conf) { db, cfg -> 
-        // Creating the exchange and merchant accounts first.
-        val bonus = TalerAmount("KUDOS:0")
-        assertIs<AccountCreationResult.Success>(db.account.create(
-            login = "merchant",
-            password = "merchant-password",
-            name = "Merchant",
-            internalPayto = merchantPayto,
-            maxDebt = TalerAmount("KUDOS:10"),
-            isTalerExchange = false,
-            isPublic = false,
-            bonus = bonus,
-            checkPaytoIdempotent = false,
-            email = null,
-            phone = null,
-            cashoutPayto = null,
-            tanChannel = null,
-            ctx = cfg.payto  
-        ))
-        assertIs<AccountCreationResult.Success>(db.account.create(
-            login = "exchange",
-            password = "exchange-password",
-            name = "Exchange",
-            internalPayto = exchangePayto,
-            maxDebt = TalerAmount("KUDOS:10"),
-            isTalerExchange = true,
-            isPublic = false,
-            bonus = bonus,
-            checkPaytoIdempotent = false,
-            email = null,
-            phone = null,
-            cashoutPayto = null,
-            tanChannel = null,
-            ctx = cfg.payto   
-        ))
-        assertIs<AccountCreationResult.Success>(db.account.create(
-            login = "customer",
-            password = "customer-password",
-            name = "Customer",
-            internalPayto = customerPayto,
-            maxDebt = TalerAmount("KUDOS:10"),
-            isTalerExchange = false,
-            isPublic = false,
-            bonus = bonus,
-            checkPaytoIdempotent = false,
-            email = null,
-            phone = null,
-            cashoutPayto = null,
-            tanChannel = null,
-            ctx = cfg.payto
-        ))
-        // Create admin account
-        assertIs<AccountCreationResult.Success>(createAdminAccount(db, cfg, "admin-password"))
-        testApplication {
-            application {
-                corebankWebApp(db, cfg)
-            }
-            if (cfg.allowConversion) {
-                // Set conversion rates
-                client.post("/conversion-info/conversion-rate") {
-                    pwAuth("admin")
-                    json {
-                        "cashin_ratio" to "0.8"
-                        "cashin_fee" to "KUDOS:0.02"
-                        "cashin_tiny_amount" to "KUDOS:0.01"
-                        "cashin_rounding_mode" to "nearest"
-                        "cashin_min_amount" to "EUR:0"
-                        "cashout_ratio" to "1.25"
-                        "cashout_fee" to "EUR:0.003"
-                        "cashout_tiny_amount" to "EUR:0.00000001"
-                        "cashout_rounding_mode" to "zero"
-                        "cashout_min_amount" to "KUDOS:0.1"
-                    }
-                }.assertNoContent()
-            }
-            lambda(db)
+) = setup(conf) { db, cfg -> 
+    // Creating the exchange and merchant accounts first.
+    val bonus = TalerAmount("KUDOS:0")
+    assertIs<AccountCreationResult.Success>(db.account.create(
+        login = "merchant",
+        password = "merchant-password",
+        name = "Merchant",
+        internalPayto = merchantPayto,
+        maxDebt = TalerAmount("KUDOS:10"),
+        isTalerExchange = false,
+        isPublic = false,
+        bonus = bonus,
+        checkPaytoIdempotent = false,
+        email = null,
+        phone = null,
+        cashoutPayto = null,
+        tanChannel = null,
+        ctx = cfg.payto  
+    ))
+    assertIs<AccountCreationResult.Success>(db.account.create(
+        login = "exchange",
+        password = "exchange-password",
+        name = "Exchange",
+        internalPayto = exchangePayto,
+        maxDebt = TalerAmount("KUDOS:10"),
+        isTalerExchange = true,
+        isPublic = false,
+        bonus = bonus,
+        checkPaytoIdempotent = false,
+        email = null,
+        phone = null,
+        cashoutPayto = null,
+        tanChannel = null,
+        ctx = cfg.payto   
+    ))
+    assertIs<AccountCreationResult.Success>(db.account.create(
+        login = "customer",
+        password = "customer-password",
+        name = "Customer",
+        internalPayto = customerPayto,
+        maxDebt = TalerAmount("KUDOS:10"),
+        isTalerExchange = false,
+        isPublic = false,
+        bonus = bonus,
+        checkPaytoIdempotent = false,
+        email = null,
+        phone = null,
+        cashoutPayto = null,
+        tanChannel = null,
+        ctx = cfg.payto
+    ))
+    // Create admin account
+    assertIs<AccountCreationResult.Success>(createAdminAccount(db, cfg, "admin-password"))
+    testApplication {
+        application {
+            corebankWebApp(db, cfg)
         }
+        if (cfg.allowConversion) {
+            // Set conversion rates
+            client.post("/conversion-info/conversion-rate") {
+                pwAuth("admin")
+                json {
+                    "cashin_ratio" to "0.8"
+                    "cashin_fee" to "KUDOS:0.02"
+                    "cashin_tiny_amount" to "KUDOS:0.01"
+                    "cashin_rounding_mode" to "nearest"
+                    "cashin_min_amount" to "EUR:0"
+                    "cashout_ratio" to "1.25"
+                    "cashout_fee" to "EUR:0.003"
+                    "cashout_tiny_amount" to "EUR:0.00000001"
+                    "cashout_rounding_mode" to "zero"
+                    "cashout_min_amount" to "KUDOS:0.1"
+                }
+            }.assertNoContent()
+        }
+        lambda(db)
     }
 }
 
-fun dbSetup(lambda: suspend (Database) -> Unit) {
+fun dbSetup(lambda: suspend (Database) -> Unit) =
     setup { db, _ -> lambda(db) }
-}
 
 /* ----- Common actions ----- */
 
diff --git a/common/src/main/kotlin/DB.kt b/common/src/main/kotlin/DB.kt
index 27b8eabe..e36182b7 100644
--- a/common/src/main/kotlin/DB.kt
+++ b/common/src/main/kotlin/DB.kt
@@ -116,10 +116,9 @@ fun pgDataSource(dbConfig: String): PGSimpleDataSource {
     return pgSource
 }
 
-fun PGSimpleDataSource.pgConnection(): PgConnection {
+fun PGSimpleDataSource.pgConnection(schema: String? = null): PgConnection {
     val conn = connection.unwrap(PgConnection::class.java)
-    // FIXME: bring the DB schema to a function argument.
-    conn.execSQLUpdate("SET search_path TO libeufin_bank;")
+    if (schema != null) conn.execSQLUpdate("SET search_path TO $schema")
     return conn
 }
 
@@ -271,22 +270,11 @@ fun initializeDatabaseTables(conn: PgConnection, cfg: DatabaseConfig, sqlFilePre
 // sqlFilePrefix is, for example, "libeufin-bank" or "libeufin-nexus" (no trailing dash).
 fun resetDatabaseTables(conn: PgConnection, cfg: DatabaseConfig, sqlFilePrefix: String) {
     logger.info("reset DB, sqldir ${cfg.sqlDir}")
-    val isInitialized = conn.prepareStatement(
-        """
-        SELECT EXISTS(SELECT 1 FROM information_schema.schemata WHERE schema_name='_v') AND
-            EXISTS(SELECT 1 FROM information_schema.schemata WHERE schema_name='${sqlFilePrefix.replace("-", "_")}')
-        """
-    ).one{ it.getBoolean(1) }
-    if (!isInitialized) {
-        logger.info("versioning schema not present, not running drop sql")
-        return
-    }
-
     val sqlDrop = Path("${cfg.sqlDir}/$sqlFilePrefix-drop.sql").readText()
     conn.execSQLUpdate(sqlDrop)
 }
 
-abstract class DbPool(cfg: String, schema: String) : java.io.Closeable {
+open class DbPool(cfg: String, schema: String) : java.io.Closeable {
     val pgSource = pgDataSource(cfg)
     private val pool: HikariDataSource
 
diff --git a/contrib/bank.conf b/contrib/bank.conf
index 372143e6..cb95d8c0 100644
--- a/contrib/bank.conf
+++ b/contrib/bank.conf
@@ -72,13 +72,13 @@ SPA = $DATADIR/spa/
 # Exchange that is suggested to wallets when withdrawing.
 # SUGGESTED_WITHDRAWAL_EXCHANGE = https://exchange.demo.taler.net/
 
-# Time after which pending operations are aborted
+# Time after which pending operations are aborted during garbage collection
 GC_ABORT_AFTER = 15m
 
-# Time after which aborted operations and expired items are deleted
+# Time after which aborted operations and expired items are deleted during garbage collection
 GC_CLEAN_AFTER = 14d
 
-# Time after which all bank transactions, operations and deleted accounts are deleted
+# Time after which all bank transactions, operations and deleted accounts are deleted during garbage collection
 GC_DELETE_AFTER = 10year
 
 [libeufin-bankdb-postgres]
diff --git a/database-versioning/libeufin-bank-drop.sql b/database-versioning/libeufin-bank-drop.sql
index 52ef772b..2b120b24 100644
--- a/database-versioning/libeufin-bank-drop.sql
+++ b/database-versioning/libeufin-bank-drop.sql
@@ -5,12 +5,14 @@ $do$
 DECLARE
     patch text;
 BEGIN
-    for patch in SELECT patch_name FROM _v.patches WHERE patch_name LIKE 'libeufin_bank_%' loop 
-        PERFORM _v.unregister_patch(patch);
-    end loop;
+    IF EXISTS(SELECT 1 FROM information_schema.schemata WHERE schema_name='_v') THEN
+        FOR patch IN SELECT patch_name FROM _v.patches WHERE patch_name LIKE 'libeufin_bank_%' LOOP 
+            PERFORM _v.unregister_patch(patch);
+        END LOOP;
+    END IF;
 END
 $do$;
 
-DROP SCHEMA libeufin_bank CASCADE;
+DROP SCHEMA IF EXISTS libeufin_bank CASCADE;
 
 COMMIT;
diff --git a/database-versioning/libeufin-nexus-drop.sql b/database-versioning/libeufin-nexus-drop.sql
index 199f1cb9..4eed7f3c 100644
--- a/database-versioning/libeufin-nexus-drop.sql
+++ b/database-versioning/libeufin-nexus-drop.sql
@@ -5,11 +5,14 @@ $do$
 DECLARE
     patch text;
 BEGIN
-    for patch in SELECT patch_name FROM _v.patches WHERE patch_name LIKE 'libeufin_nexus_%' loop 
-        PERFORM _v.unregister_patch(patch);
-    end loop;
+    IF EXISTS(SELECT 1 FROM information_schema.schemata WHERE schema_name='_v') THEN
+        FOR patch IN SELECT patch_name FROM _v.patches WHERE patch_name LIKE 'libeufin_nexus_%' LOOP 
+            PERFORM _v.unregister_patch(patch);
+        END LOOP;
+    END IF;
 END
 $do$;
-DROP SCHEMA libeufin_nexus CASCADE;
+
+DROP SCHEMA IF EXISTS libeufin_nexus CASCADE;
 
 COMMIT;
diff --git a/nexus/src/test/kotlin/Common.kt b/nexus/src/test/kotlin/helpers.kt
index 8925a6a8..57a1d2da 100644
--- a/nexus/src/test/kotlin/Common.kt
+++ b/nexus/src/test/kotlin/helpers.kt
@@ -21,10 +21,7 @@ import io.ktor.client.*
 import io.ktor.client.engine.mock.*
 import io.ktor.client.request.*
 import kotlinx.coroutines.runBlocking
-import tech.libeufin.common.TalerAmount
-import tech.libeufin.common.fromFile
-import tech.libeufin.common.initializeDatabaseTables
-import tech.libeufin.common.resetDatabaseTables
+import tech.libeufin.common.*
 import tech.libeufin.nexus.*
 import tech.libeufin.nexus.db.*
 import java.time.Instant
@@ -33,29 +30,25 @@ import kotlin.io.path.Path
 fun conf(
     conf: String = "test.conf",
     lambda: suspend (EbicsSetupConfig) -> Unit
-) {
+) = runBlocking {
     val config = NEXUS_CONFIG_SOURCE.fromFile(Path("conf/$conf"))
     val ctx = EbicsSetupConfig(config)
-    runBlocking {
-        lambda(ctx)
-    }
+    lambda(ctx) 
 }
 
 fun setup(
     conf: String = "test.conf",
     lambda: suspend (Database, EbicsSetupConfig) -> Unit
-) {
+) = runBlocking {
     val config = NEXUS_CONFIG_SOURCE.fromFile(Path("conf/$conf"))
     val dbCfg = config.dbConfig()
     val ctx = EbicsSetupConfig(config)
-    Database(dbCfg.dbConnStr).use {
-        runBlocking {
-            it.conn { conn ->
-                resetDatabaseTables(conn, dbCfg, "libeufin-nexus")
-                initializeDatabaseTables(conn, dbCfg, "libeufin-nexus")
-            }
-            lambda(it, ctx)
+    Database(dbCfg.dbConnStr).use { 
+        it.conn { conn ->
+            resetDatabaseTables(conn, dbCfg, "libeufin-nexus")
+            initializeDatabaseTables(conn, dbCfg, "libeufin-nexus")
         }
+        lambda(it, ctx)
     }
 }
 
@@ -64,13 +57,11 @@ val clientKeys = generateNewKeys()
 // Gets an HTTP client whose requests are going to be served by 'handler'.
 fun getMockedClient(
     handler: MockRequestHandleScope.(HttpRequestData) -> HttpResponseData
-): HttpClient {
-    return HttpClient(MockEngine) {
-        followRedirects = false
-        engine {
-            addHandler {
-                    request -> handler(request)
-            }
+): HttpClient = HttpClient(MockEngine) {
+    followRedirects = false
+    engine {
+        addHandler {
+                request -> handler(request)
         }
     }
 }
@@ -79,8 +70,7 @@ fun getMockedClient(
 fun genInitPay(
     subject: String = "init payment",
     requestUid: String = "unique"
-) =
-    InitiatedPayment(
+) = InitiatedPayment(
         id = -1,
         amount = TalerAmount(44, 0, "KUDOS"),
         creditPaytoUri = "payto://iban/CH9300762011623852957?receiver-name=Test",
diff --git a/testbench/src/test/kotlin/MigrationTest.kt b/testbench/src/test/kotlin/MigrationTest.kt
new file mode 100644
index 00000000..9b93557a
--- /dev/null
+++ b/testbench/src/test/kotlin/MigrationTest.kt
@@ -0,0 +1,81 @@
+/*
+ * 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/>
+ */
+
+import org.junit.Test
+import tech.libeufin.bank.db.TransactionDAO.BankTransactionResult
+import tech.libeufin.bank.db.WithdrawalDAO.WithdrawalCreationResult
+import tech.libeufin.bank.db.*
+import tech.libeufin.bank.*
+import tech.libeufin.common.*
+import java.time.Instant
+import java.util.*
+import org.postgresql.jdbc.PgConnection
+import kotlin.test.assertEquals
+import kotlinx.coroutines.runBlocking
+import java.nio.file.Path
+import kotlin.io.path.*
+
+class MigrationTest {
+    @Test
+    fun test() = runBlocking {
+        val conn = pgDataSource("postgres:///libeufincheck").pgConnection()
+
+        // Drop current schemas
+        conn.execSQLUpdate(Path("../database-versioning/libeufin-bank-drop.sql").readText())
+        conn.execSQLUpdate(Path("../database-versioning/libeufin-nexus-drop.sql").readText())
+
+        // libeufin-bank-0001
+        conn.execSQLUpdate(Path("../database-versioning/libeufin-bank-0001.sql").readText())
+        conn.execSQLUpdate("""
+            INSERT INTO customers (login, password_hash) VALUES
+                ('account_0', 'fack_hash'), ('account_1', 'fack_hash');
+            INSERT INTO bank_accounts (internal_payto_uri, owning_customer_id) VALUES
+                ('payto_0', 1), ('payto_1', 2);
+            INSERT INTO bank_account_transactions(creditor_payto_uri, creditor_name, debtor_payto_uri, debtor_name, subject, amount, transaction_date, direction, bank_account_id) VALUES
+                ('payto_0', 'account_0', 'payto_1', 'account_1', 'subject', (0, 0)::taler_amount, 42, 'credit'::direction_enum, 1);
+            INSERT INTO challenges(code, creation_date, expiration_date, retry_counter) VALUES
+                ('secret_code', 42, 42, 42),
+                ('secret_code', 42, 42, 42);
+            INSERT INTO cashout_operations(request_uid, amount_debit, amount_credit, subject, creation_time, bank_account, challenge, local_transaction) VALUES
+                ('\x6ca1ab1a76a484d7424064c51c49c1947405f42f7d185d052dbf6718d845ec6b'::bytea, (0, 0)::taler_amount, (0, 0)::taler_amount, 'subject', 42, 1, 1, 1),
+                ('\xa605637a4852684e4957e6177f41311eacf8661a6a74b90178c487fe347b9918'::bytea, (0, 0)::taler_amount, (0, 0)::taler_amount, 'subject', 42, 1, 2, NULL);
+            INSERT INTO taler_withdrawal_operations(withdrawal_uuid, amount, reserve_pub, wallet_bank_account) VALUES
+                (gen_random_uuid(), (0, 0)::taler_amount, '\x6ca1ab1a76a484d7424064c51c49c1947405f42f7d185d052dbf6718d845ec6b'::bytea, 1),
+                (gen_random_uuid(), (0, 0)::taler_amount, '\xa605637a4852684e4957e6177f41311eacf8661a6a74b90178c487fe347b9918'::bytea, 2);
+        """)
+
+        // libeufin-bank-0002
+        conn.execSQLUpdate(Path("../database-versioning/libeufin-bank-0002.sql").readText())
+
+        // libeufin-bank-0003
+        conn.execSQLUpdate(Path("../database-versioning/libeufin-bank-0003.sql").readText())
+
+        // libeufin-nexus-0001
+        conn.execSQLUpdate(Path("../database-versioning/libeufin-nexus-0001.sql").readText())
+        conn.execSQLUpdate("""
+            INSERT INTO outgoing_transactions(amount, execution_time, message_id) VALUES
+                ((0, 0)::taler_amount, 42, 'id');
+            INSERT INTO initiated_outgoing_transactions(amount, wire_transfer_subject, initiation_time, credit_payto_uri, outgoing_transaction_id, request_uid) VALUES
+                ((0, 0)::taler_amount, 'subject', 42, 'payto_0', 1, 'request_uid');
+        """)
+
+        // libeufin-nexus-0002
+        conn.execSQLUpdate(Path("../database-versioning/libeufin-nexus-0002.sql").readText())
+    }
+}
\ No newline at end of file