libeufin

commit ebf6da1073887634da0fbb2117db625f62274f1f
parent 38ce7fa7d360786da460493fb1887daf19199976
Author: Antoine A <>
Date:   Tue,  4 Mar 2025 13:43:42 +0100

nexus: unique id db migration

Diffstat:
M
database-versioning/libeufin-nexus-0011.sql
 | 
12
++++++++----
M
testbench/src/test/kotlin/MigrationTest.kt
 | 
13
++++++++++---

2 files changed, 18 insertions(+), 7 deletions(-)
diff --git a/database-versioning/libeufin-nexus-0011.sql b/database-versioning/libeufin-nexus-0011.sql
@@ -19,18 +19,22 @@ SELECT _v.register_patch('libeufin-nexus-0011', NULL, NULL);
 
 SET search_path TO libeufin_nexus;
 
--- TODO migrate existing ids
-
 ALTER TABLE outgoing_transactions
   ADD COLUMN acct_svcr_ref TEXT UNIQUE,
   ALTER COLUMN end_to_end_id DROP NOT NULL,
   ADD CONSTRAINT unique_id CHECK(COALESCE(end_to_end_id, acct_svcr_ref) IS NOT NULL);
 
 ALTER TABLE incoming_transactions
-  DROP COLUMN bank_id,
   ADD COLUMN uetr UUID UNIQUE,
   ADD COLUMN tx_id TEXT UNIQUE,
-  ADD COLUMN acct_svcr_ref TEXT UNIQUE,
+  ADD COLUMN acct_svcr_ref TEXT UNIQUE;
+
+UPDATE incoming_transactions SET 
+  uetr = CASE WHEN bank_id ~ E'^[[:xdigit:]]{8}-([[:xdigit:]]{4}-){3}[[:xdigit:]]{12}$' THEN bank_id::uuid ELSE NULL END,
+  tx_id = CASE WHEN bank_id ~ E'^[[:xdigit:]]{8}-([[:xdigit:]]{4}-){3}[[:xdigit:]]{12}$' THEN NULL ELSE bank_id END;
+
+ALTER TABLE incoming_transactions
+  DROP COLUMN bank_id,
   ALTER COLUMN subject DROP NOT NULL,
   ALTER COLUMN debit_payto DROP NOT NULL,
   ADD CONSTRAINT unique_id CHECK(COALESCE(uetr::text, tx_id, acct_svcr_ref) IS NOT NULL);
diff --git a/testbench/src/test/kotlin/MigrationTest.kt b/testbench/src/test/kotlin/MigrationTest.kt
@@ -23,6 +23,7 @@ import tech.libeufin.common.db.pgConnection
 import tech.libeufin.common.db.pgDataSource
 import kotlin.io.path.Path
 import kotlin.io.path.readText
+import java.util.UUID
 
 class MigrationTest {
     @Test
@@ -132,9 +133,12 @@ class MigrationTest {
 
         conn.execSQLUpdate("""
             INSERT INTO incoming_transactions(amount, subject, execution_time, debit_payto, bank_id) VALUES
-                ((0, 0)::taler_amount, 'simple', 42, 'debit_payto', 'first'),
-                ((0, 0)::taler_amount, 'reserve', 42, 'debit_payto', 'second'),
-                ((0, 0)::taler_amount, 'kyc', 42, 'debit_payto', 'third');
+                ((1, 0)::taler_amount, 'simple', 42, 'debit_payto', 'first'),
+                ((2, 0)::taler_amount, 'reserve', 42, 'debit_payto', 'second'),
+                ((3, 0)::taler_amount, 'kyc', 42, 'debit_payto', 'third'),
+                ((4, 0)::taler_amount, 'simple', 42, 'debit_payto', '${UUID.randomUUID()}'),
+                ((5, 0)::taler_amount, 'reserve', 42, 'debit_payto', '${UUID.randomUUID()}'),
+                ((6, 0)::taler_amount, 'kyc', 42, 'debit_payto', '${UUID.randomUUID()}');;
             INSERT INTO talerable_incoming_transactions(incoming_transaction_id, type, reserve_public_key, account_pub) VALUES
                 (2, 'reserve', '\x6ca1ab1a76a484d7424064c51c49c1947405f42f7d185d052dbf6718d845ec6b'::bytea, null),
                 (3, 'kyc', null, '\x6ca1ab1a76a484d7424064c51c49c1947405f42f7d185d052dbf6718d845ec6b'::bytea);
@@ -142,5 +146,8 @@ class MigrationTest {
 
         // libeufin-nexus-0010
         conn.execSQLUpdate(Path("../database-versioning/libeufin-nexus-0010.sql").readText())
+
+        // libeufin-nexus-0011
+        conn.execSQLUpdate(Path("../database-versioning/libeufin-nexus-0011.sql").readText())
     }
 }
 \ No newline at end of file