From c457a1cdb6bc7f359559ed0b73269a84f4b45b4f Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Sun, 7 Jan 2024 14:41:55 +0100 Subject: properly deploy procedures.sql, add trigger for taler-merchant-depositcheck, rename field to match contract terms --- src/backenddb/Makefile.am | 1 + src/backenddb/merchant-0002.sql | 6 ++-- src/backenddb/pg_insert_deposit_confirmation.c | 32 ++++++++++++++++++---- src/backenddb/pg_insert_deposit_confirmation.h | 2 ++ src/backenddb/pg_insert_deposit_to_transfer.c | 10 ++++++- src/backenddb/pg_insert_deposit_to_transfer.sql | 5 +++- src/backenddb/pg_lookup_pending_deposits.c | 4 +-- .../pg_update_deposit_confirmation_status.c | 2 +- src/backenddb/pg_update_webhook.c | 1 - src/backenddb/plugin_merchantdb_postgres.c | 5 +++- src/backenddb/test_merchantdb.c | 1 + 11 files changed, 53 insertions(+), 16 deletions(-) (limited to 'src/backenddb') diff --git a/src/backenddb/Makefile.am b/src/backenddb/Makefile.am index bb559ced..149a43ed 100644 --- a/src/backenddb/Makefile.am +++ b/src/backenddb/Makefile.am @@ -16,6 +16,7 @@ sqlinputs = \ sql_DATA = \ versioning.sql \ + procedures.sql \ merchant-0001.sql \ merchant-0002.sql \ merchant-0003.sql \ diff --git a/src/backenddb/merchant-0002.sql b/src/backenddb/merchant-0002.sql index e5e91631..76371a42 100644 --- a/src/backenddb/merchant-0002.sql +++ b/src/backenddb/merchant-0002.sql @@ -51,11 +51,11 @@ CREATE INDEX IF NOT EXISTS merchant_contract_terms_by_merchant_and_session ALTER TABLE merchant_deposit_confirmations - ADD COLUMN deposit_deadline INT8 DEFAULT (0) NOT NULL, + ADD COLUMN wire_transfer_deadline INT8 DEFAULT (0) NOT NULL, ADD COLUMN wire_pending BOOL DEFAULT (TRUE) NOT NULL, ADD COLUMN exchange_failure TEXT DEFAULT NULL; -COMMENT ON COLUMN merchant_deposit_confirmations.deposit_deadline +COMMENT ON COLUMN merchant_deposit_confirmations.wire_transfer_deadline IS 'when should the exchange make the wire transfer at the latest'; COMMENT ON COLUMN merchant_deposit_confirmations.wire_pending IS 'true if we are awaiting wire details for a deposit of this purchase and are not blocked on KYC'; @@ -64,7 +64,7 @@ COMMENT ON COLUMN merchant_deposit_confirmations.exchange_failure CREATE INDEX IF NOT EXISTS merchant_deposit_confirmations_by_pending_wire ON merchant_deposit_confirmations - (exchange_url,deposit_deadline) + (exchange_url,wire_transfer_deadline) WHERE wire_pending; CREATE INDEX IF NOT EXISTS merchant_deposits_by_deposit_confirmation_serial diff --git a/src/backenddb/pg_insert_deposit_confirmation.c b/src/backenddb/pg_insert_deposit_confirmation.c index 377ba2d8..6ad0f5fb 100644 --- a/src/backenddb/pg_insert_deposit_confirmation.c +++ b/src/backenddb/pg_insert_deposit_confirmation.c @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022, 2023 Taler Systems SA + Copyright (C) 2022, 2023, 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 @@ -33,6 +33,7 @@ TMH_PG_insert_deposit_confirmation ( struct GNUNET_TIME_Timestamp deposit_timestamp, const struct TALER_PrivateContractHashP *h_contract_terms, const char *exchange_url, + struct GNUNET_TIME_Timestamp wire_transfer_deadline, const struct TALER_Amount *total_without_fees, const struct TALER_Amount *wire_fee, const struct TALER_MerchantWireHashP *h_wire, @@ -40,6 +41,10 @@ TMH_PG_insert_deposit_confirmation ( const struct TALER_ExchangePublicKeyP *exchange_pub, uint64_t *deposit_confirmation_serial_id) { + struct GNUNET_DB_EventHeaderP es = { + .size = htons (sizeof (es)), + .type = htons (TALER_DBEVENT_MERCHANT_NEW_WIRE_DEADLINE) + }; struct PostgresClosure *pg = cls; struct GNUNET_PQ_QueryParam params[] = { GNUNET_PQ_query_param_string (instance_id), @@ -53,6 +58,7 @@ TMH_PG_insert_deposit_confirmation ( GNUNET_PQ_query_param_auto_from_type (h_wire), /* 7 */ GNUNET_PQ_query_param_auto_from_type (exchange_sig), GNUNET_PQ_query_param_auto_from_type (exchange_pub), + GNUNET_PQ_query_param_timestamp (&wire_transfer_deadline), GNUNET_PQ_query_param_end }; struct GNUNET_PQ_ResultSpec rs[] = { @@ -60,6 +66,7 @@ TMH_PG_insert_deposit_confirmation ( deposit_confirmation_serial_id), GNUNET_PQ_result_spec_end }; + enum GNUNET_DB_QueryStatus qs; /* no preflight check here, run in transaction by caller! */ GNUNET_log (GNUNET_ERROR_TYPE_DEBUG, @@ -91,11 +98,12 @@ TMH_PG_insert_deposit_confirmation ( ",total_without_fee" ",wire_fee" ",exchange_sig" + ",wire_transfer_deadline" ",signkey_serial" ",account_serial)" " SELECT " " order_serial" - " ,$3, $4, $5, $6, $8" + " ,$3, $4, $5, $6, $8, $10" " ,ed.signkey_serial" " ,md.account_serial" " FROM merchant_contract_terms" @@ -103,8 +111,20 @@ TMH_PG_insert_deposit_confirmation ( " FULL OUTER JOIN ed ON TRUE" " WHERE h_contract_terms=$2" " RETURNING deposit_confirmation_serial"); - return GNUNET_PQ_eval_prepared_singleton_select (pg->conn, - "insert_deposit_confirmation", - params, - rs); + qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn, + "insert_deposit_confirmation", + params, + rs); + if (GNUNET_DB_STATUS_SUCCESS_ONE_RESULT == qs) + { + /* inform taler-merchant-depositcheck about new deadline */ + struct GNUNET_TIME_AbsoluteNBO nbo; + + nbo = GNUNET_TIME_absolute_hton (wire_transfer_deadline.abs_time); + GNUNET_PQ_event_notify (pg->conn, + &es, + &nbo, + sizeof (nbo)); + } + return qs; } diff --git a/src/backenddb/pg_insert_deposit_confirmation.h b/src/backenddb/pg_insert_deposit_confirmation.h index 3d9802e2..243d5a4d 100644 --- a/src/backenddb/pg_insert_deposit_confirmation.h +++ b/src/backenddb/pg_insert_deposit_confirmation.h @@ -33,6 +33,7 @@ * @param deposit_timestamp time when the exchange generated the deposit confirmation * @param h_contract_terms proposal data's hashcode * @param exchange_url URL of the exchange that issued @a coin_pub + * @param wire_transfer_deadline when do we expect the wire transfer from the exchange * @param total_without_fees deposited total in the batch without fees * @param wire_fee wire fee the exchange charges * @param h_wire hash of the wire details of the target account of the merchant @@ -48,6 +49,7 @@ TMH_PG_insert_deposit_confirmation ( struct GNUNET_TIME_Timestamp deposit_timestamp, const struct TALER_PrivateContractHashP *h_contract_terms, const char *exchange_url, + struct GNUNET_TIME_Timestamp wire_transfer_deadline, const struct TALER_Amount *total_without_fees, const struct TALER_Amount *wire_fee, const struct TALER_MerchantWireHashP *h_wire, diff --git a/src/backenddb/pg_insert_deposit_to_transfer.c b/src/backenddb/pg_insert_deposit_to_transfer.c index 1baf8963..7e02070f 100644 --- a/src/backenddb/pg_insert_deposit_to_transfer.c +++ b/src/backenddb/pg_insert_deposit_to_transfer.c @@ -44,24 +44,32 @@ TMH_PG_insert_deposit_to_transfer ( GNUNET_PQ_query_param_end }; bool wpc; + bool conflict; struct GNUNET_PQ_ResultSpec rs[] = { GNUNET_PQ_result_spec_bool ("out_wire_pending_cleared", &wpc), + GNUNET_PQ_result_spec_bool ("out_conflict", + &conflict), GNUNET_PQ_result_spec_end }; enum GNUNET_DB_QueryStatus qs; PREPARE (pg, "insert_deposit_to_transfer", - "SELECT out_wire_cleared" + "SELECT out_wire_pending_cleared" + " ,out_conflict" " FROM merchant_insert_deposit_to_transfer" " ($1,$2,$3,$4,$5,$6);"); qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn, "insert_deposit_to_transfer", params, rs); + if (qs <= 0) + return qs; if (wpc) GNUNET_log (GNUNET_ERROR_TYPE_INFO, "Wire pending flag cleared (good!)\n"); + if (conflict) + return GNUNET_DB_STATUS_SUCCESS_NO_RESULTS; return qs; } diff --git a/src/backenddb/pg_insert_deposit_to_transfer.sql b/src/backenddb/pg_insert_deposit_to_transfer.sql index ddc291c3..bd3a5943 100644 --- a/src/backenddb/pg_insert_deposit_to_transfer.sql +++ b/src/backenddb/pg_insert_deposit_to_transfer.sql @@ -22,7 +22,8 @@ CREATE OR REPLACE FUNCTION merchant_insert_deposit_to_transfer ( IN in_exchange_sig BYTEA, IN in_exchange_pub BYTEA, IN in_wtid BYTEA, - OUT out_wire_pending_cleared BOOL) + OUT out_wire_pending_cleared BOOL, + OUT out_conflict BOOL) LANGUAGE plpgsql AS $$ DECLARE @@ -51,6 +52,8 @@ INSERT INTO merchant_deposit_to_transfer AND wtid=in_wtid ON CONFLICT DO NOTHING; +out_conflict = NOT FOUND; + IF NOT FOUND THEN SELECT deposit_confirmation_serial diff --git a/src/backenddb/pg_lookup_pending_deposits.c b/src/backenddb/pg_lookup_pending_deposits.c index 27509f36..2f4cf1a4 100644 --- a/src/backenddb/pg_lookup_pending_deposits.c +++ b/src/backenddb/pg_lookup_pending_deposits.c @@ -177,10 +177,10 @@ TMH_PG_lookup_pending_deposits ( " USING (deposit_confirmation_serial)" " WHERE mdc.wire_pending" " AND (mdc.exchange_url=$1)" - " AND ($4 OR (mdc.deposit_deadline < $2))" + " AND ($4 OR (mdc.wire_transfer_deadline < $2))" " AND kyc.kyc_ok" " AND (0=kyc.aml_decision)" - " ORDER BY mdc.deposit_deadline ASC" + " ORDER BY mdc.wire_transfer_deadline ASC" " LIMIT $3"); qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn, "lookup_pending_deposits", diff --git a/src/backenddb/pg_update_deposit_confirmation_status.c b/src/backenddb/pg_update_deposit_confirmation_status.c index cd0e827b..61bda33b 100644 --- a/src/backenddb/pg_update_deposit_confirmation_status.c +++ b/src/backenddb/pg_update_deposit_confirmation_status.c @@ -45,7 +45,7 @@ TMH_PG_update_deposit_confirmation_status ( PREPARE (pg, "update_deposit_confirmation_status", "UPDATE merchant_deposit_confirmations SET" - " deposit_deadline_type=$2" + " wire_transfer_deadline=$2" ",exchange_failure=$3" " WHERE deposit_confirmation_serial=" " (SELECT deposit_confirmation_serial" diff --git a/src/backenddb/pg_update_webhook.c b/src/backenddb/pg_update_webhook.c index c12ae9e3..087feb2a 100644 --- a/src/backenddb/pg_update_webhook.c +++ b/src/backenddb/pg_update_webhook.c @@ -47,7 +47,6 @@ TMH_PG_update_webhook (void *cls, GNUNET_PQ_query_param_end }; - check_connection (pg); PREPARE (pg, "update_webhook", diff --git a/src/backenddb/plugin_merchantdb_postgres.c b/src/backenddb/plugin_merchantdb_postgres.c index 291008d0..40fb1d19 100644 --- a/src/backenddb/plugin_merchantdb_postgres.c +++ b/src/backenddb/plugin_merchantdb_postgres.c @@ -202,6 +202,7 @@ postgres_create_tables (void *cls) GNUNET_PQ_make_try_execute ("SET search_path TO merchant;"), GNUNET_PQ_EXECUTE_STATEMENT_END }; + enum GNUNET_GenericReturnValue ret; conn = GNUNET_PQ_connect_with_cfg (pc->cfg, "merchantdb-postgres", @@ -210,8 +211,10 @@ postgres_create_tables (void *cls) NULL); if (NULL == conn) return GNUNET_SYSERR; + ret = GNUNET_PQ_exec_sql (conn, + "procedures"); GNUNET_PQ_disconnect (conn); - return GNUNET_OK; + return ret; } diff --git a/src/backenddb/test_merchantdb.c b/src/backenddb/test_merchantdb.c index d85bccd3..8d684371 100644 --- a/src/backenddb/test_merchantdb.c +++ b/src/backenddb/test_merchantdb.c @@ -2504,6 +2504,7 @@ test_insert_deposit (const struct InstanceData *instance, deposit->timestamp, &deposit->h_contract_terms, deposit->exchange_url, + deposit->timestamp, &awf, &deposit->wire_fee, &deposit->h_wire, -- cgit v1.2.3