Database versioning.

Getting to 'make check' to pass with
the SQL files loaded from the filesystem.

2 files changed, 43 insertions, 7 deletions

diff --git a/sandbox/src/main/kotlin/tech/libeufin/sandbox/DB.kt b/sandbox/src/main/kotlin/tech/libeufin/sandbox/DB.kt
index bacb4149..f05480cf 100644
--- a/sandbox/src/main/kotlin/tech/libeufin/sandbox/DB.kt
+++ b/sandbox/src/main/kotlin/tech/libeufin/sandbox/DB.kt
@@ -33,8 +33,8 @@ import org.jetbrains.exposed.dao.id.LongIdTable
 import org.jetbrains.exposed.sql.*
 import org.jetbrains.exposed.sql.transactions.TransactionManager
 import org.jetbrains.exposed.sql.transactions.transaction
-import tech.libeufin.util.getCurrentUser
-import tech.libeufin.util.internalServerError
+import org.jetbrains.exposed.sql.transactions.transactionManager
+import tech.libeufin.util.*
 import java.sql.Connection
 import kotlin.reflect.*
 import kotlin.reflect.full.*
@@ -667,7 +667,27 @@ class CashoutSubmissionEntity(id: EntityID<Long>) : LongEntity(id) {
 }
 
 fun dbDropTables(dbConnectionString: String) {
-    Database.connect(dbConnectionString, user = getCurrentUser())
+    connectWithSchema(dbConnectionString)
+    if (isPostgres()) {
+        val ret = execCommand(
+            listOf(
+                "libeufin-load-sql",
+                "-d",
+                getDatabaseName(),
+                "-s",
+                "sandbox",
+                "-r" // the drop option
+            ),
+            /**
+             * Tolerating a failure here helps to manage the case
+             * where an empty database is attempted to be dropped.
+             */
+            throwIfFails = false
+        )
+        if (ret != 0)
+            logger.warn("Dropping the sandbox tables failed.  Was the DB filled before?")
+        return
+    }
     transaction {
         SchemaUtils.drop(
             CashoutSubmissionsTable,
@@ -690,11 +710,23 @@ fun dbDropTables(dbConnectionString: String) {
             CashoutOperationsTable
         )
     }
+
 }
 
 fun dbCreateTables(dbConnectionString: String) {
-    Database.connect(dbConnectionString, user = getCurrentUser())
-    TransactionManager.manager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE
+    connectWithSchema(dbConnectionString)
+    if (isPostgres()) {
+        val databaseName = getDatabaseName()
+        execCommand(listOf(
+            "libeufin-load-sql",
+            "-d",
+            databaseName,
+            "-s",
+            "sandbox"
+        ))
+        return
+    }
+    // Still using the legacy way for other DBMSs, like SQLite.
     transaction {
         SchemaUtils.create(
             CashoutSubmissionsTable,
diff --git a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Main.kt b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Main.kt
index b20ba00f..ad9417f1 100644
--- a/sandbox/src/main/kotlin/tech/libeufin/sandbox/Main.kt
+++ b/sandbox/src/main/kotlin/tech/libeufin/sandbox/Main.kt
@@ -231,7 +231,6 @@ class Camt053Tick : CliktCommand(
 ) {
     override fun run() {
         val dbConnString = getDbConnFromEnv(SANDBOX_DB_ENV_VAR_NAME)
-        Database.connect(dbConnString, user = getCurrentUser())
         dbCreateTables(dbConnString)
         val newStatements = mutableMapOf<String, MutableList<XLibeufinBankTransaction>>()
         /**
@@ -300,7 +299,12 @@ class MakeTransaction : CliktCommand("Wire-transfer money between Sandbox bank a
 
     override fun run() {
         val dbConnString = getDbConnFromEnv(SANDBOX_DB_ENV_VAR_NAME)
-        Database.connect(dbConnString, user = getCurrentUser())
+        /**
+         * Merely connecting here (and NOT creating any table) because this
+         * command should only be run after actual bank accounts exist in the
+         * system, meaning therefore that the database got already set up.
+         */
+        connectWithSchema(dbConnString)
         // Refuse to operate without a default demobank.
         val demobank = getDemobank("default")
         if (demobank == null) {