convert insert_transfer_details into stored procedure, may still need some debugging, but tests (still) pass

1 files changed, 220 insertions, 0 deletions

diff --git a/src/backenddb/pg_insert_transfer_details.sql b/src/backenddb/pg_insert_transfer_details.sql
new file mode 100644
index 00000000..bf57bc74
--- /dev/null
+++ b/src/backenddb/pg_insert_transfer_details.sql
@@ -0,0 +1,220 @@
+--
+-- This file is part of TALER
+-- Copyright (C) 2024 Taler Systems SA
+--
+-- TALER is free software; you can redistribute it and/or modify it under the
+-- terms of the GNU General Public License as published by the Free Software
+-- Foundation; either version 3, or (at your option) any later version.
+--
+-- TALER 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 General Public License for more details.
+--
+-- You should have received a copy of the GNU General Public License along with
+-- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
+--
+
+
+CREATE OR REPLACE FUNCTION merchant_do_insert_transfer_details (
+  IN in_instance_id TEXT,
+  IN in_exchange_url TEXT,
+  IN in_payto_uri TEXT,
+  IN in_wtid BYTEA,
+  IN in_execution_time INT8,
+  IN in_exchange_pub BYTEA,
+  IN in_exchange_sig BYTEA,
+  IN in_total_amount taler_amount_currency,
+  IN in_wire_fee taler_amount_currency,
+  IN ina_coin_values taler_amount_currency[],
+  IN ina_deposit_fees taler_amount_currency[],
+  IN ina_coin_pubs BYTEA[],
+  IN ina_contract_terms BYTEA[],
+  OUT out_no_instance BOOL,
+  OUT out_no_account BOOL,
+  OUT out_no_exchange BOOL,
+  OUT out_duplicate BOOL,
+  OUT out_conflict BOOL)
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  my_merchant_id INT8;
+  my_signkey_serial INT8;
+  my_credit_serial INT8;
+  my_affected_orders RECORD;
+  i INT8;
+  curs CURSOR (arg_coin_pub BYTEA) FOR
+    SELECT mcon.order_serial
+      FROM merchant_deposits dep
+      -- Next 2 joins ensure transfers exist in the first place
+      JOIN merchant_deposit_to_transfer
+        USING (deposit_serial)
+      JOIN merchant_transfers mtrans
+        USING (credit_serial)
+      JOIN merchant_deposit_confirmations mcon
+        USING (deposit_confirmation_serial)
+      WHERE dep.coin_pub=arg_coin_pub;
+  ini_coin_pub BYTEA;
+  ini_contract_term BYTEA;
+  ini_coin_value taler_amount_currency;
+  ini_deposit_fee taler_amount_currency;
+BEGIN
+
+-- Which instance are we using?
+SELECT merchant_serial
+  INTO my_merchant_id
+  FROM merchant_instances
+ WHERE merchant_id=in_instance_id;
+
+IF NOT FOUND
+THEN
+  out_no_instance=TRUE;
+  out_no_account=FALSE;
+  out_no_exchange=FALSE;
+  out_duplicate=FALSE;
+  out_conflict=FALSE;
+  RETURN;
+END IF;
+out_no_instance=FALSE;
+
+-- Determine account that was credited.
+SELECT credit_serial
+  INTO my_credit_serial
+  FROM merchant_transfers
+ WHERE exchange_url=in_exchange_url
+     AND wtid=in_wtid
+     AND account_serial=
+     (SELECT account_serial
+        FROM merchant_accounts
+       WHERE payto_uri=in_payto_uri
+         AND exchange_url=in_exchange_url
+         AND merchant_serial=my_merchant_id);
+
+IF NOT FOUND
+THEN
+  out_no_account=TRUE;
+  out_no_exchange=FALSE;
+  out_duplicate=FALSE;
+  out_conflict=FALSE;
+  RETURN;
+END IF;
+out_no_account=FALSE;
+
+-- Find exchange sign key
+SELECT signkey_serial
+  INTO my_signkey_serial
+  FROM merchant_exchange_signing_keys
+ WHERE exchange_pub=in_exchange_pub
+   ORDER BY start_date DESC
+   LIMIT 1;
+
+IF NOT FOUND
+THEN
+  out_no_exchange=TRUE;
+  out_conflict=FALSE;
+  out_duplicate=FALSE;
+  RETURN;
+END IF;
+out_no_exchange=FALSE;
+
+-- Add signature first, check for idempotent request
+INSERT INTO merchant_transfer_signatures
+  (credit_serial
+  ,signkey_serial
+  ,credit_amount
+  ,wire_fee
+  ,execution_time
+  ,exchange_sig)
+  VALUES
+   (my_credit_serial
+   ,my_signkey_serial
+   ,in_total_amount
+   ,in_wire_fee
+   ,in_execution_time
+   ,in_exchange_sig)
+  ON CONFLICT DO NOTHING;
+
+IF NOT FOUND
+THEN
+  PERFORM 1
+    FROM merchant_transfer_signatures
+    WHERE credit_serial=my_credit_serial
+      AND signkey_serial=my_signkey_serial
+      AND credit_amount=in_credit_amount
+      AND wire_fee=in_wire_fee
+      AND execution_time=in_execution_time
+      AND exchange_sig=in_exchange_sig;
+  IF FOUND
+  THEN
+    -- duplicate case
+    out_duplicate=TRUE;
+    out_conflict=FALSE;
+    RETURN;
+  END IF;
+  -- conflict case
+  out_duplicate=FALSE;
+  out_conflict=TRUE;
+  RETURN;
+END IF;
+
+out_duplicate=FALSE;
+out_conflict=FALSE;
+
+
+FOR i IN 1..array_length(ina_coin_pubs,1)
+LOOP
+  ini_coin_value=ina_coin_values[i];
+  ini_deposit_fee=ina_deposit_fees[i];
+  ini_coin_pub=ina_coin_pubs[i];
+  ini_contract_term=ina_contract_terms[i];
+
+  INSERT INTO merchant_transfer_to_coin
+    (deposit_serial
+    ,credit_serial
+    ,offset_in_exchange_list
+    ,exchange_deposit_value
+    ,exchange_deposit_fee)
+    SELECT
+        dep.deposit_serial
+       ,my_credit_serial
+       ,i
+       ,ini_coin_value
+       ,ini_deposit_fee
+      FROM merchant_deposits dep
+      JOIN merchant_deposit_confirmations dcon
+        USING (deposit_confirmation_serial)
+      JOIN merchant_contract_terms cterm
+        USING (order_serial)
+      WHERE dep.coin_pub=ini_coin_pub
+        AND cterm.h_contract_terms=ini_contract_term
+        AND cterm.merchant_serial=my_merchant_id;
+
+  RAISE NOTICE 'iterating over affected orders';
+  OPEN curs (arg_coin_pub:=ini_coin_pub);
+  LOOP
+    FETCH NEXT FROM curs INTO my_affected_orders;
+    EXIT WHEN NOT FOUND;
+
+    -- FIXME: statement below is messy, likely buggy (as we don't set WIRED to TRUE correctly...)
+    RAISE NOTICE 'checking affected order for completion';
+    UPDATE merchant_contract_terms
+       SET wired=TRUE
+     WHERE order_serial IN
+       (SELECT order_serial
+          FROM merchant_deposit_confirmations dcon
+         WHERE
+           order_serial=my_affected_orders.order_serial
+           AND NOT EXISTS
+           (SELECT 1
+              FROM merchant_deposits dep
+              JOIN merchant_deposit_to_transfer
+                USING (deposit_serial)
+              JOIN merchant_transfers mtrans
+                USING (credit_serial)
+             WHERE
+               dep.deposit_confirmation_serial = dcon.deposit_confirmation_serial
+               AND NOT mtrans.confirmed));
+  END LOOP; -- END curs LOOP
+  CLOSE curs;
+END LOOP; -- END FOR loop
+
+END $$;