Check postgres version and reuse connection when initialising database

7 files changed, 89 insertions, 67 deletions

diff --git a/bank/src/main/kotlin/tech/libeufin/bank/Constants.kt b/bank/src/main/kotlin/tech/libeufin/bank/Constants.kt
index 58969209..fca03215 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/Constants.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/Constants.kt
@@ -38,6 +38,9 @@ val RESERVED_ACCOUNTS = setOf("admin", "bank")
 // Security
 const val MAX_BODY_LENGTH: Long = 4 * 1024 // 4kB
 
+// DB
+const val MIN_VERSION: Int = 15
+
 // API version
 const val COREBANK_API_VERSION: String = "2:1:2"
 const val CONVERSION_API_VERSION: String = "0:0:0"
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/Main.kt b/bank/src/main/kotlin/tech/libeufin/bank/Main.kt
index 37d14f9c..5987fbcc 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/Main.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/Main.kt
@@ -234,13 +234,15 @@ class BankDbInit : CliktCommand("Initialize the libeufin-bank database", name =
     override fun run() = cliCmd(logger){
         val config = talerConfig(common.config)
         val cfg = config.loadDbConfig()
-        if (requestReset) {
-            resetDatabaseTables(cfg, sqlFilePrefix = "libeufin-bank")
-        }
-        initializeDatabaseTables(cfg, sqlFilePrefix = "libeufin-bank")
         val ctx = config.loadBankConfig();
         val db = Database(cfg.dbConnStr, ctx.regionalCurrency, ctx.fiatCurrency)
         runBlocking {
+            db.conn { conn ->
+                if (requestReset) {
+                    resetDatabaseTables(conn, cfg, sqlFilePrefix = "libeufin-bank")
+                }
+                initializeDatabaseTables(conn, cfg, sqlFilePrefix = "libeufin-bank")
+            }
             // Create admin account if missing
             val res = maybeCreateAdminAccount(db, ctx) // logs provided by the helper
             when (res) {
@@ -302,7 +304,15 @@ class ServeBank : CliktCommand("Run libeufin-bank HTTP server", name = "serve")
             }
             module { corebankWebApp(db, ctx) }
         }
-        embeddedServer(Netty, env).start(wait = true)
+        val engine = embeddedServer(Netty, env)
+        when (serverCfg) {
+            is ServerConfig.Tcp -> {
+                logger.info("Server listening on http://localhost:${serverCfg.port}")
+            }
+            is ServerConfig.Unix ->
+                throw Exception("Can only serve libeufin-bank via TCP")
+        }
+        engine.start(wait = true)
     }
 }
 
diff --git a/bank/src/main/kotlin/tech/libeufin/bank/db/Database.kt b/bank/src/main/kotlin/tech/libeufin/bank/db/Database.kt
index 48b0fd6a..9c783af2 100644
--- a/bank/src/main/kotlin/tech/libeufin/bank/db/Database.kt
+++ b/bank/src/main/kotlin/tech/libeufin/bank/db/Database.kt
@@ -70,6 +70,14 @@ class Database(dbConfig: String, internal val bankCurrency: String, internal val
         config.connectionInitSql = "SET search_path TO libeufin_bank;SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;"
         config.validate()
         dbPool = HikariDataSource(config);
+        dbPool.getConnection().use { con -> 
+            val meta = con.getMetaData();
+            val majorVersion = meta.getDatabaseMajorVersion()
+            val minorVersion = meta.getDatabaseMinorVersion()
+            if (majorVersion < MIN_VERSION) {
+                throw Exception("postgres version must be at least $MIN_VERSION.0 got $majorVersion.$minorVersion")
+            }
+        }
         notifWatcher = NotificationWatcher(pgSource)
     }
 
diff --git a/bank/src/test/kotlin/helpers.kt b/bank/src/test/kotlin/helpers.kt
index 3cef3f2c..c298fcb2 100644
--- a/bank/src/test/kotlin/helpers.kt
+++ b/bank/src/test/kotlin/helpers.kt
@@ -52,11 +52,13 @@ fun setup(
 ) {
     val config = talerConfig("conf/$conf")
     val dbCfg = config.loadDbConfig()
-    resetDatabaseTables(dbCfg, "libeufin-bank")
-    initializeDatabaseTables(dbCfg, "libeufin-bank")
     val ctx = config.loadBankConfig()
     Database(dbCfg.dbConnStr, ctx.regionalCurrency, ctx.fiatCurrency).use {
         runBlocking {
+            it.conn { conn ->
+                resetDatabaseTables(conn, dbCfg, "libeufin-bank")
+                initializeDatabaseTables(conn, dbCfg, "libeufin-bank")
+            }
             lambda(it, ctx)
         }
     }
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/DbInit.kt b/nexus/src/main/kotlin/tech/libeufin/nexus/DbInit.kt
index 7a823624..a3cb3199 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/DbInit.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/DbInit.kt
@@ -3,8 +3,7 @@ package tech.libeufin.nexus
 import com.github.ajalt.clikt.core.CliktCommand
 import com.github.ajalt.clikt.parameters.options.flag
 import com.github.ajalt.clikt.parameters.options.option
-import tech.libeufin.util.initializeDatabaseTables
-import tech.libeufin.util.resetDatabaseTables
+import tech.libeufin.util.*
 import kotlin.system.exitProcess
 
 /**
@@ -25,10 +24,12 @@ class DbInit : CliktCommand("Initialize the libeufin-nexus database", name = "db
     override fun run() {
         val cfg = loadConfigOrFail(configFile).extractDbConfigOrFail()
         doOrFail {
-            if (requestReset) {
-                resetDatabaseTables(cfg, sqlFilePrefix = "libeufin-nexus")
+            pgDataSource(cfg.dbConnStr).pgConnection().use { conn ->
+                if (requestReset) {
+                    resetDatabaseTables(conn, cfg, sqlFilePrefix = "libeufin-nexus")
+                }
+                initializeDatabaseTables(conn, cfg, sqlFilePrefix = "libeufin-nexus")
             }
-            initializeDatabaseTables(cfg, sqlFilePrefix = "libeufin-nexus")
         }
     }
 }
\ No newline at end of file
diff --git a/nexus/src/test/kotlin/Common.kt b/nexus/src/test/kotlin/Common.kt
index ea9e67fd..82d31feb 100644
--- a/nexus/src/test/kotlin/Common.kt
+++ b/nexus/src/test/kotlin/Common.kt
@@ -4,9 +4,7 @@ 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 tech.libeufin.util.*
 import java.security.interfaces.RSAPrivateCrtKey
 import java.time.Instant
 
@@ -28,14 +26,17 @@ fun prepDb(cfg: TalerConfig): Database {
         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)
+    pgDataSource(dbCfg.dbConnStr).pgConnection().use { conn ->
+        println("SQL dir for testing: ${dbCfg.sqlDir}")
+        try {
+            resetDatabaseTables(conn, dbCfg, "libeufin-nexus")
+        } catch (e: Exception) {
+            logger.warn("Resetting an empty database throws, tolerating this...")
+            logger.warn(e.message)
+        }
+        initializeDatabaseTables(conn, dbCfg, "libeufin-nexus")
     }
-    initializeDatabaseTables(dbCfg, "libeufin-nexus")
+   
     return Database(dbCfg.dbConnStr)
 }
 
diff --git a/util/src/main/kotlin/DB.kt b/util/src/main/kotlin/DB.kt
index ccdd09c3..cfb204bf 100644
--- a/util/src/main/kotlin/DB.kt
+++ b/util/src/main/kotlin/DB.kt
@@ -224,60 +224,57 @@ fun maybeApplyV(conn: PgConnection, cfg: DatabaseConfig) {
 }
 
 // sqlFilePrefix is, for example, "libeufin-bank" or "libeufin-nexus" (no trailing dash).
-fun initializeDatabaseTables(cfg: DatabaseConfig, sqlFilePrefix: String) {
-    logger.info("doing DB initialization, sqldir ${cfg.sqlDir}, dbConnStr ${cfg.dbConnStr}")
-    pgDataSource(cfg.dbConnStr).pgConnection().use { conn ->
-        maybeApplyV(conn, cfg)
-        conn.transaction {
-            val checkStmt = conn.prepareStatement("SELECT count(*) as n FROM _v.patches where patch_name = ?")
-
-            for (n in 1..9999) {
-                val numStr = n.toString().padStart(4, '0')
-                val patchName = "$sqlFilePrefix-$numStr"
+fun initializeDatabaseTables(conn: PgConnection, cfg: DatabaseConfig, sqlFilePrefix: String) {
+    logger.info("doing DB initialization, sqldir ${cfg.sqlDir}")
+    maybeApplyV(conn, cfg)
+    conn.transaction {
+        val checkStmt = conn.prepareStatement("SELECT count(*) as n FROM _v.patches where patch_name = ?")
 
-                checkStmt.setString(1, patchName)
-                val patchCount = checkStmt.oneOrNull { it.getInt(1) } ?: throw Error("unable to query patches");
-                if (patchCount >= 1) {
-                    logger.info("patch $patchName already applied")
-                    continue
-                }
+        for (n in 1..9999) {
+            val numStr = n.toString().padStart(4, '0')
+            val patchName = "$sqlFilePrefix-$numStr"
 
-                val path = File("${cfg.sqlDir}/$sqlFilePrefix-$numStr.sql")
-                if (!path.exists()) {
-                    logger.info("path $path doesn't exist anymore, stopping")
-                    break
-                }
-                logger.info("applying patch $path")
-                val sqlPatchText = path.readText()
-                conn.execSQLUpdate(sqlPatchText)
+            checkStmt.setString(1, patchName)
+            val patchCount = checkStmt.oneOrNull { it.getInt(1) } ?: throw Error("unable to query patches");
+            if (patchCount >= 1) {
+                logger.info("patch $patchName already applied")
+                continue
             }
-            val sqlProcedures = File("${cfg.sqlDir}/$sqlFilePrefix-procedures.sql")
-            if (!sqlProcedures.exists()) {
-                logger.info("No procedures.sql for the SQL collection: $sqlFilePrefix")
-                return@transaction
+
+            val path = File("${cfg.sqlDir}/$sqlFilePrefix-$numStr.sql")
+            if (!path.exists()) {
+                logger.info("path $path doesn't exist anymore, stopping")
+                break
             }
-            conn.execSQLUpdate(sqlProcedures.readText())
+            logger.info("applying patch $path")
+            val sqlPatchText = path.readText()
+            conn.execSQLUpdate(sqlPatchText)
+        }
+        val sqlProcedures = File("${cfg.sqlDir}/$sqlFilePrefix-procedures.sql")
+        if (!sqlProcedures.exists()) {
+            logger.info("no procedures.sql for the SQL collection: $sqlFilePrefix")
+            return@transaction
         }
+        logger.info("run procedure.sql")
+        conn.execSQLUpdate(sqlProcedures.readText())
     }
 }
 
 // sqlFilePrefix is, for example, "libeufin-bank" or "libeufin-nexus" (no trailing dash).
-fun resetDatabaseTables(cfg: DatabaseConfig, sqlFilePrefix: String) {
-    logger.info("reset DB, sqldir ${cfg.sqlDir}, dbConnStr ${cfg.dbConnStr}")
-    pgDataSource(cfg.dbConnStr).pgConnection().use { conn ->
-        val isInitialized = conn.prepareStatement("SELECT EXISTS(SELECT 1 FROM information_schema.schemata WHERE schema_name='_v')").oneOrNull {
-            it.getBoolean(1)
-        }!!
-        if (!isInitialized) {
-            logger.info("versioning schema not present, not running drop sql")
-            return
-        }
+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')").oneOrNull {
+        it.getBoolean(1)
+    }!!
+    if (!isInitialized) {
+        logger.info("versioning schema not present, not running drop sql")
+        return
+    }
 
-        val sqlDrop = File("${cfg.sqlDir}/$sqlFilePrefix-drop.sql").readText()
-        try {
-            conn.execSQLUpdate(sqlDrop)
-        } catch (e: Exception) {
-            // TODO Fix fail because precense of _v does not mean patch is applied
-        }
+    val sqlDrop = File("${cfg.sqlDir}/$sqlFilePrefix-drop.sql").readText()
+    try {
+        conn.execSQLUpdate(sqlDrop)
+    } catch (e: Exception) {
+        // TODO Fix fail because precense of _v does not mean patch is applied
     }
 }
\ No newline at end of file