Database versioning.

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

11 files changed, 211 insertions, 35 deletions

diff --git a/cli/tests/launch_services_with_xlibeufinbank.sh b/cli/tests/launch_services_with_xlibeufinbank.sh
index 2a5e10c5..accf7d08 100755
--- a/cli/tests/launch_services_with_xlibeufinbank.sh
+++ b/cli/tests/launch_services_with_xlibeufinbank.sh
@@ -22,7 +22,8 @@ echo RUNNING SANDBOX-NEXUS EBICS PAIR
 jq --version &> /dev/null || (echo "'jq' command not found"; exit 77)
 curl --version &> /dev/null || (echo "'curl' command not found"; exit 77)
 
-DB_CONN="jdbc:postgresql://localhost:5432/libeufincheck?user=$(whoami)"
+DB_CONN="jdbc:postgresql://localhost/libeufincheck?socketFactory=org.newsclub.net.unix.AFUNIXSocketFactory\$FactoryArg&socketFactoryArg=/var/run/postgresql/.s.PGSQL.5432"
+
 export LIBEUFIN_SANDBOX_DB_CONNECTION=$DB_CONN
 export LIBEUFIN_NEXUS_DB_CONNECTION=$DB_CONN
 
diff --git a/contrib/libeufin-load-sql b/contrib/libeufin-load-sql
index eaf3e534..963ad22e 100755
--- a/contrib/libeufin-load-sql
+++ b/contrib/libeufin-load-sql
@@ -10,6 +10,22 @@ fail () {
   exit 1
 }
 
+usage_and_exit () {
+  echo Usage: libeufin-load-sql OPTIONS
+  echo
+  echo By default, this command creates and/or patches the LibEuFin tables.
+  echo One particular LibEuFin service could be selected via the '-s' option.
+  echo Pass '-r' to delete tables and schemas.
+  echo
+  echo 'Supported options:'
+  echo "  -s SERVICE  -- specify 'sandbox' or 'nexus', according to which set of tables are to be setup or dropped.  If missing both sets will be setup or dropped on the same database."
+  echo '  -d DB_CONN  -- required.  Pass DB_CONN as the postgres connection string.  Passed verbatim to Psql'
+  echo '  -l LOC      -- required.  Pass LOC as the SQL files location.  Typically $prefix/share/libeufin/sql'
+  echo '  -h           -- print this help'
+  echo '  -r           -- drop all the tables and schema(s)'
+  exit 0
+}
+
 run_sql_file () {
   # -q doesn't hide all the output, hence the
   # redirection to /dev/null.
@@ -19,6 +35,10 @@ run_sql_file () {
     --set ON_ERROR_STOP=1 > /dev/null
 }
 
+get_patch_path () {
+  echo "$PATCHES_LOCATION/$1"
+}
+
 # The real check happens (by the caller)
 # by checking the returned text.
 check_patch_applied () {
@@ -30,13 +50,10 @@ check_patch_applied () {
 # Iterates over the .sql migration files and applies
 # the new ones.
 iterate_over_patches () {
-  if test "$1" != sandbox -a "$1" != nexus; then
-    fail "iterate_over_patches: only 'sandbox' and 'nexus' are acceptable arguments."
-  fi
   component="$1"
   cd $PATCHES_LOCATION
   for patch_filename in $(ls -1 -v $component-[0-9][0-9][0-9][0-9].sql); do
-    patch_name=$(echo $patch_filename | cut -f1 -d.)
+    patch_name=$(echo $patch_filename | cut -f1 -d.) # drops the final .sql
     echo Checking patch: "$patch_name"
     maybe_applied=$(check_patch_applied "$patch_name")
     if test -n "$maybe_applied"; then continue; fi
@@ -46,26 +63,30 @@ iterate_over_patches () {
   done
   cd - > /dev/null # cd to previous location.
 }
-while getopts ":d:l:h" OPTION; do
+
+if test $# -eq 0; then
+  usage_and_exit
+fi
+
+while getopts ":d:l:hs:r" OPTION; do
   case "$OPTION" in 
     d)
-      DB_CONNECTION="$OPTARG"
+      DB_CONNECTION="$OPTARG" # only one required.
       ;;
     l)
       PATCHES_LOCATION="$OPTARG"
       ;;
     s)
-      SERVICE="${OPTARG:-}"
+      if test "$OPTARG" != sandbox -a "$OPTARG" != nexus; then
+        fail "Invalid -s value: $OPTARG.  Please pass 'sandbox' or 'nexus'."
+      fi
+      SERVICE="$OPTARG"
+      ;;
+    r)
+      DROP="YES"
       ;;
     h)
-      echo Usage: libeufin-load-sql OPTIONS
-      echo
-      echo 'Supported options:'
-      echo "  -s SERVICE  -- specify 'sandbox' or 'nexus', according to which set of tables are to be setup.  If missing both sets will be setup on the same database."
-      echo '  -d DB_CONN  -- required.  Pass DB_CONN as the postgres connection string.  Passed verbatim to Psql'
-      echo '  -l LOC      -- required.  Pass LOC as the SQL files location.  Typically $prefix/share/libeufin/sql'
-      echo '  -h           -- print this help'
-      exit 0
+      usage_and_exit
       ;;
     ?)
       fail 'Unrecognized command line option'
@@ -82,10 +103,23 @@ if test -z "${DB_CONNECTION:-}"; then
   fail "Required option '-d' was missing."
 fi
 
-run_sql_file "$PATCHES_LOCATION/versioning.sql"
-if test -z "${SERVICE:-}"; then # both table sets.
+run_sql_file $(get_patch_path "versioning.sql")
+
+if test -z "${SERVICE:-}"; then # impact both services.
+  # Maybe drop.
+  if test "${DROP:-}" = "YES"; then
+    run_sql_file $(get_patch_path "sandbox-drop.sql")
+    run_sql_file $(get_patch_path "nexus-drop.sql")
+    exit 0
+  fi
   iterate_over_patches sandbox
   iterate_over_patches nexus
   exit 0
 fi
+
+# Maybe drop
+if test "${DROP:-}" = "YES"; then
+  run_sql_file $(get_patch_path "${SERVICE}-drop.sql")
+  exit 0
+fi
 iterate_over_patches $SERVICE # helper checks the argument sanity.
diff --git a/database-versioning/nexus-drop.sql b/database-versioning/nexus-drop.sql
new file mode 100644
index 00000000..2b661c82
--- /dev/null
+++ b/database-versioning/nexus-drop.sql
@@ -0,0 +1,30 @@
+BEGIN;
+
+-- This script DROPs all of the tables we create, including the
+-- versioning schema!
+--
+-- Unlike the other SQL files, it SHOULD be updated to reflect the
+-- latest requirements for dropping tables.
+
+SELECT _v.unregister_patch('nexus-0001');
+
+DROP TABLE IF EXISTS nexususers CASCADE; 
+DROP TABLE IF EXISTS nexusbankconnections CASCADE;
+DROP TABLE IF EXISTS xlibeufinbankusers CASCADE;
+DROP TABLE IF EXISTS nexusscheduledtasks CASCADE;
+DROP TABLE IF EXISTS nexusbankaccounts CASCADE;
+DROP TABLE IF EXISTS nexusbanktransactions CASCADE;
+DROP TABLE IF EXISTS paymentinitiations CASCADE;
+DROP TABLE IF EXISTS nexusebicssubscribers CASCADE;
+DROP TABLE IF EXISTS nexusbankbalances CASCADE;
+DROP TABLE IF EXISTS anastasisincomingpayments CASCADE;
+DROP TABLE IF EXISTS talerincomingpayments CASCADE;
+DROP TABLE IF EXISTS facades CASCADE;
+DROP TABLE IF EXISTS talerrequestedpayments CASCADE;
+DROP TABLE IF EXISTS facadestate CASCADE;
+DROP TABLE IF EXISTS talerinvalidincomingpayments CASCADE;
+DROP TABLE IF EXISTS nexusbankmessages CASCADE;
+DROP TABLE IF EXISTS offeredbankaccounts CASCADE;
+DROP TABLE IF EXISTS nexuspermissions CASCADE;
+
+COMMIT;
diff --git a/database-versioning/sandbox-drop.sql b/database-versioning/sandbox-drop.sql
new file mode 100644
index 00000000..de226b09
--- /dev/null
+++ b/database-versioning/sandbox-drop.sql
@@ -0,0 +1,30 @@
+BEGIN;
+
+-- This script DROPs all of the tables we create, including the
+-- versioning schema!
+--
+-- Unlike the other SQL files, it SHOULD be updated to reflect the
+-- latest requirements for dropping tables.
+
+SELECT _v.unregister_patch('sandbox-0001');
+
+DROP TABLE IF EXISTS demobankconfigs CASCADE;
+DROP TABLE IF EXISTS bankaccounts CASCADE;
+DROP TABLE IF EXISTS bankaccounttransactions CASCADE;
+DROP TABLE IF EXISTS cashoutsubmissions CASCADE;
+DROP TABLE IF EXISTS demobankconfigpairs CASCADE;
+DROP TABLE IF EXISTS ebicssubscribers CASCADE;
+DROP TABLE IF EXISTS ebicssubscriberpublickeysCASCADE;
+DROP TABLE IF EXISTS ebicshosts CASCADE;
+DROP TABLE IF EXISTS ebicsdownloadtransactions CASCADE;
+DROP TABLE IF EXISTS ebicsuploadtransactions CASCADE;
+DROP TABLE IF EXISTS ebicsuploadtransactionchunks CASCADE;
+DROP TABLE IF EXISTS ebicsordersignatures CASCADE;
+DROP TABLE IF EXISTS bankaccountfreshtransactions CASCADE;
+DROP TABLE IF EXISTS bankaccountreports CASCADE;
+DROP TABLE IF EXISTS bankaccountstatements CASCADE;
+DROP TABLE IF EXISTS talerwithdrawals CASCADE;
+DROP TABLE IF EXISTS demobankcustomers CASCADE;
+DROP TABLE IF EXISTS cashoutoperations CASCADE;
+
+COMMIT;
diff --git a/nexus/src/main/kotlin/tech/libeufin/nexus/DB.kt b/nexus/src/main/kotlin/tech/libeufin/nexus/DB.kt
index 086d6fe6..89ef421e 100644
--- a/nexus/src/main/kotlin/tech/libeufin/nexus/DB.kt
+++ b/nexus/src/main/kotlin/tech/libeufin/nexus/DB.kt
@@ -522,7 +522,23 @@ class NexusPermissionEntity(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",
+                "nexus",
+                "-r"
+            ),
+            throwIfFails = false
+        )
+        if (ret != 0)
+            logger.warn("Dropping the nexus tables failed.  Was the DB filled before?")
+        return
+    }
     transaction {
         SchemaUtils.drop(
             NexusUsersTable,
@@ -548,8 +564,19 @@ fun dbDropTables(dbConnectionString: String) {
 }
 
 fun dbCreateTables(dbConnectionString: String) {
-    Database.connect(dbConnectionString, user = getCurrentUser())
-    TransactionManager.manager.defaultIsolationLevel = Connection.TRANSACTION_SERIALIZABLE
+    connectWithSchema(dbConnectionString)
+    val databaseName = getDatabaseName()
+    if (isPostgres()) {
+        execCommand(listOf(
+            "libeufin-load-sql",
+            "-d",
+            databaseName,
+            "-s",
+            "nexus"
+        ))
+        return
+    }
+    // Still using the legacy way for other DBMSs, like SQLite.
     transaction {
         SchemaUtils.create(
             XLibeufinBankUsersTable,
@@ -572,4 +599,4 @@ fun dbCreateTables(dbConnectionString: String) {
             NexusPermissionsTable
         )
     }
-}
+}
\ No newline at end of file
diff --git a/nexus/src/test/kotlin/DbEventTest.kt b/nexus/src/test/kotlin/DbEventTest.kt
index 57075882..d3922a76 100644
--- a/nexus/src/test/kotlin/DbEventTest.kt
+++ b/nexus/src/test/kotlin/DbEventTest.kt
@@ -11,7 +11,6 @@ import tech.libeufin.util.postgresNotify
 
 
 class DbEventTest {
-
     /**
      * LISTENs to one DB channel but only wakes up
      * if the payload is how expected.
diff --git a/nexus/src/test/kotlin/MakeEnv.kt b/nexus/src/test/kotlin/MakeEnv.kt
index be8102a7..9b079415 100644
--- a/nexus/src/test/kotlin/MakeEnv.kt
+++ b/nexus/src/test/kotlin/MakeEnv.kt
@@ -69,8 +69,6 @@ inline fun <reified ExceptionType> assertException(
  * Cleans up the DB file afterwards.
  */
 fun withTestDatabase(keepData: Boolean = false, f: () -> Unit) {
-    Database.connect(TEST_DB_CONN, user = currentUser)
-    TransactionManager.manager.defaultIsolationLevel = java.sql.Connection.TRANSACTION_SERIALIZABLE
     if (!keepData) {
         dbDropTables(TEST_DB_CONN)
         tech.libeufin.sandbox.dbDropTables(TEST_DB_CONN)
@@ -202,6 +200,7 @@ fun prepSandboxDb(
     cashoutCurrency: String = "EUR"
 ) {
     tech.libeufin.sandbox.dbCreateTables(TEST_DB_CONN)
+    connectWithSchema(TEST_DB_CONN)
     transaction {
         val config = DemobankConfig(
             currency = currency,
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) {
diff --git a/util/src/main/kotlin/DB.kt b/util/src/main/kotlin/DB.kt
index d8ac3fbe..b5147fa6 100644
--- a/util/src/main/kotlin/DB.kt
+++ b/util/src/main/kotlin/DB.kt
@@ -23,6 +23,7 @@ import kotlinx.coroutines.async
 import kotlinx.coroutines.coroutineScope
 import logger
 import net.taler.wallet.crypto.Base32Crockford
+import org.jetbrains.exposed.sql.Database
 import org.jetbrains.exposed.sql.Transaction
 import org.jetbrains.exposed.sql.transactions.TransactionManager
 import org.jetbrains.exposed.sql.transactions.transaction
@@ -229,4 +230,20 @@ fun getDatabaseName(): String {
         }
     }
     return maybe_db_name ?: throw internalServerError("Could not find current DB name")
+}
+
+/**
+ * Abstracts over the Exposed details to connect
+ * to a database and ONLY use the passed schema
+ * WHEN PostgreSQL is the DBMS.
+ */
+fun connectWithSchema(dbConn: String, schemaName: String? = null) {
+    Database.connect(
+        dbConn,
+        user = getCurrentUser(),
+        setupConnection = { conn ->
+            if (isPostgres() && schemaName != null)
+                conn.schema = schemaName
+        }
+    )
 }
\ No newline at end of file
diff --git a/util/src/main/kotlin/exec.kt b/util/src/main/kotlin/exec.kt
index c29d5b04..653110e8 100644
--- a/util/src/main/kotlin/exec.kt
+++ b/util/src/main/kotlin/exec.kt
@@ -21,13 +21,16 @@ package tech.libeufin.util
 
 /**
  * Wrapper around the ProcessBuilder API.  It executes a
- * command and throws exception if the result is not zero.
+ * command and (by default) throws exception if the result is not zero.
+ * It returns the exit code.
  */
-fun execCommand(cmd: List<String>) {
+fun execCommand(cmd: List<String>, throwIfFails: Boolean = true): Int {
     val result: Int = ProcessBuilder(cmd)
         .redirectOutput(ProcessBuilder.Redirect.INHERIT)
         .redirectError(ProcessBuilder.Redirect.INHERIT)
         .start()
         .waitFor()
-    if (result != 0) throw internalServerError("Command '$cmd' failed.")
+    if (result != 0 && throwIfFails)
+        throw internalServerError("Command '$cmd' failed.")
+    return result
 }
\ No newline at end of file