diff options
Diffstat (limited to 'src/exchangedb')
19 files changed, 518 insertions, 261 deletions
diff --git a/src/exchangedb/0002-kyc_attributes.sql b/src/exchangedb/0002-kyc_attributes.sql index aa2e3288a..af0b55c3a 100644 --- a/src/exchangedb/0002-kyc_attributes.sql +++ b/src/exchangedb/0002-kyc_attributes.sql @@ -29,6 +29,7 @@ BEGIN ',h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=32)' ',kyc_prox BYTEA NOT NULL CHECK (LENGTH(kyc_prox)=32)' ',provider TEXT NOT NULL' + ',satisfied_checks TEXT[] NOT NULL' ',collection_time INT8 NOT NULL' ',expiration_time INT8 NOT NULL' ',encrypted_attributes BYTEA NOT NULL' diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am index 4ffc574cf..50f9e768a 100644 --- a/src/exchangedb/Makefile.am +++ b/src/exchangedb/Makefile.am @@ -181,6 +181,8 @@ libtaler_plugin_exchangedb_postgres_la_SOURCES = \ pg_insert_denomination_revocation.h pg_insert_denomination_revocation.c \ pg_get_denomination_revocation.h pg_get_denomination_revocation.c \ pg_select_batch_deposits_missing_wire.h pg_select_batch_deposits_missing_wire.c \ + pg_select_justification_for_missing_wire.h pg_select_justification_for_missing_wire.c \ + pg_select_aggregations_above_serial.h pg_select_aggregations_above_serial.c \ pg_lookup_auditor_timestamp.h pg_lookup_auditor_timestamp.c \ pg_lookup_auditor_status.h pg_lookup_auditor_status.c \ pg_insert_auditor.h pg_insert_auditor.c \ diff --git a/src/exchangedb/exchange-0002.sql.in b/src/exchangedb/exchange-0002.sql.in index b202d75ff..6e0d5ae58 100644 --- a/src/exchangedb/exchange-0002.sql.in +++ b/src/exchangedb/exchange-0002.sql.in @@ -1,6 +1,6 @@ -- -- This file is part of TALER --- Copyright (C) 2014--2022 Taler Systems SA +-- Copyright (C) 2014--2023 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 @@ -38,12 +38,10 @@ COMMENT ON TYPE exchange_do_array_reserve_insert_return_type CREATE TYPE exchange_do_select_deposits_missing_wire_return_type AS ( - total_amount_with_fee taler_amount, - payto_uri TEXT, - kyc_pending TEXT, -- can be NULL - wire_deadline INT8, - aml_status INT4, - aml_limit taler_amount + batch_deposit_serial_id INT8, + total_amount taler_amount, + wire_target_h_payto BYTEA, + deadline INT8 ); COMMENT ON TYPE exchange_do_select_deposits_missing_wire_return_type IS 'Return type for exchange_do_select_deposits_missing_wire'; diff --git a/src/exchangedb/exchange_do_insert_kyc_attributes.sql b/src/exchangedb/exchange_do_insert_kyc_attributes.sql index d9818c882..2efc0aec5 100644 --- a/src/exchangedb/exchange_do_insert_kyc_attributes.sql +++ b/src/exchangedb/exchange_do_insert_kyc_attributes.sql @@ -19,6 +19,7 @@ CREATE OR REPLACE FUNCTION exchange_do_insert_kyc_attributes( IN in_h_payto BYTEA, IN in_kyc_prox BYTEA, IN in_provider_section TEXT, + IN in_satisfied_checks TEXT[], IN in_birthday INT4, IN in_provider_account_id TEXT, IN in_provider_legitimization_id TEXT, @@ -40,6 +41,7 @@ INSERT INTO exchange.kyc_attributes (h_payto ,kyc_prox ,provider + ,satisfied_checks ,collection_time ,expiration_time ,encrypted_attributes @@ -48,6 +50,7 @@ INSERT INTO exchange.kyc_attributes (in_h_payto ,in_kyc_prox ,in_provider_section + ,in_satisfied_checks ,in_collection_time_ts ,in_expiration_time_ts ,in_enc_attributes @@ -68,7 +71,8 @@ out_ok = FOUND; SELECT reserve_pub INTO orig_reserve_pub FROM exchange.legitimization_requirements - WHERE h_payto=in_h_payto AND NOT reserve_pub IS NULL; + WHERE h_payto=in_h_payto + AND NOT reserve_pub IS NULL; orig_reserve_found = FOUND; IF orig_reserve_found @@ -105,5 +109,5 @@ INSERT INTO kyc_alerts END $$; -COMMENT ON FUNCTION exchange_do_insert_kyc_attributes(INT8, BYTEA, BYTEA, TEXT, INT4, TEXT, TEXT, INT8, INT8, INT8, BYTEA, BOOL, TEXT) +COMMENT ON FUNCTION exchange_do_insert_kyc_attributes(INT8, BYTEA, BYTEA, TEXT, TEXT[], INT4, TEXT, TEXT, INT8, INT8, INT8, BYTEA, BOOL, TEXT) IS 'Inserts new KYC attributes and updates the status of the legitimization process and the AML status for the account'; diff --git a/src/exchangedb/exchange_do_select_deposits_missing_wire.sql b/src/exchangedb/exchange_do_select_deposits_missing_wire.sql index 9132e5b12..40ebd8b88 100644 --- a/src/exchangedb/exchange_do_select_deposits_missing_wire.sql +++ b/src/exchangedb/exchange_do_select_deposits_missing_wire.sql @@ -15,21 +15,8 @@ -- -- @author: Christian Grothoff ---CREATE TYPE exchange_do_select_deposits_missing_wire_return_type --- AS --- ( --- total_amount taler_amount, --- payto_uri TEXT, --- kyc_pending TEXT, --- wire_deadline INT8, --- aml_status INT4 --- ); - --- FIXME: this function is not working as intended at all yet, work in progress! - CREATE OR REPLACE FUNCTION exchange_do_select_deposits_missing_wire( - IN in_start_date INT8, - IN in_end_date INT8) + IN in_min_serial_id INT8) RETURNS SETOF exchange_do_select_deposits_missing_wire_return_type LANGUAGE plpgsql AS $$ @@ -37,44 +24,20 @@ DECLARE missing CURSOR FOR SELECT - bdep.batch_deposit_serial_id - ,bdep.wire_target_h_payto - ,bdep.wire_deadline - FROM batch_deposits bdep - WHERE bdep.wire_deadline >= in_start_date - AND bdep.wire_deadline < in_end_date - AND NOT EXISTS (SELECT 1 - FROM aggregation_tracking atr - WHERE (atr.batch_deposit_serial_id = bdep.batch_deposit_serial_id)); -DECLARE - my_earliest_deadline INT8; -- earliest deadline that was missed + batch_deposit_serial_id + ,wire_target_h_payto + ,wire_deadline + FROM batch_deposits + WHERE batch_deposit_serial_id > in_min_serial_id; DECLARE my_total_val INT8; -- all deposits without wire DECLARE my_total_frac INT8; -- all deposits without wire (fraction, not normalized) DECLARE - my_refund_val INT8; -- all refunds without wire -DECLARE - my_refund_frac INT8; -- all refunds without wire (fraction, not normalized) -DECLARE - my_wire_target_h_payto BYTEA; -- hash of the target account -DECLARE - my_payto_uri TEXT; -- the target account -DECLARE - my_kyc_pending TEXT; -- pending KYC operations -DECLARE - my_required_checks TEXT[]; -DECLARE - my_aml_status INT4; -- AML status (0: normal) -DECLARE my_total taler_amount; -- amount that was originally deposited DECLARE my_batch_record RECORD; DECLARE - my_aml_data RECORD; -DECLARE - my_aml_threshold taler_amount; -- threshold above which AML is triggered -DECLARE i RECORD; BEGIN @@ -83,133 +46,25 @@ LOOP FETCH NEXT FROM missing INTO i; EXIT WHEN NOT FOUND; - IF ( (my_earliest_deadline IS NULL) OR - (my_earliest_deadline > i.wire_deadline) ) - THEN - my_earliest_deadline = i.wire_deadline; - END IF; SELECT SUM((cdep.amount_with_fee).val) AS total_val ,SUM((cdep.amount_with_fee).frac::INT8) AS total_frac - ,SUM((r.amount_with_fee).val) AS refund_val - ,SUM((r.amount_with_fee).frac::INT8) AS refund_frac INTO my_batch_record FROM coin_deposits cdep - LEFT JOIN refunds r - ON ( (r.coin_pub = cdep.coin_pub) AND - (r.batch_deposit_serial_id = cdep.batch_deposit_serial_id) ) WHERE cdep.batch_deposit_serial_id = i.batch_deposit_serial_id; --- GROUP BY bdep.wire_target_h_payto; -- maybe use temporary table intead of cursor, or accumulate C-side? my_total_val=my_batch_record.total_val; my_total_frac=my_batch_record.total_frac; - my_refund_val=my_batch_record.refund_val; - my_refund_frac=my_batch_record.refund_frac; - RAISE WARNING 'tval: %', my_total_val; - RAISE WARNING 'tfrac: %', my_total_frac; - RAISE WARNING 'rval: %', my_refund_val; - RAISE WARNING 'rfrac: %', my_refund_frac; - - IF my_refund_val IS NOT NULL - THEN - -- subtract refunds from total - my_total_val = my_total_val - my_refund_val; - -- note: frac could go negative here, that's OK - my_total_frac = my_total_frac - my_refund_frac; - END IF; -- Normalize total amount - IF my_total_frac < 0 - THEN - my_total.val = my_total_val - 1 + my_total_frac / 100000000; - my_total.frac = 100000000 + my_total_frac % 100000000; - ELSE - my_total.val = my_total_val + my_total_frac / 100000000; - my_total.frac = my_total_frac % 100000000; - END IF; - RAISE WARNING 'val: %', my_total.val; - RAISE WARNING 'frac: %', my_total.frac; - ASSERT my_total.frac >= 0, 'Normalized amount fraction must be non-negative'; - ASSERT my_total.frac < 100000000, 'Normalized amount fraction must be below 100000000'; - - IF (my_total.val < 0) - THEN - -- Refunds above deposits. That's a problem, but not one for this auditor pass. - CONTINUE; - END IF; - - -- Note: total amount here is NOT the exact amount due for the - -- wire transfer, as we did not consider deposit, refund and wire fees. - -- The amount given in the report is thus ONLY indicative of the non-refunded - -- gross amount, not the net transfer amount. - - IF 0 = my_total_val + my_total_frac - THEN - -- full refund, skip report entirely - CONTINUE; - END IF; - - -- Fetch payto URI - -- NOTE: we want to group by my_wire_target_h_payto and not do this repeatedly per batch deposit! - my_payto_uri = NULL; - SELECT payto_uri - INTO my_payto_uri - FROM wire_targets - WHERE wire_target_h_payto=my_wire_target_h_payto; - - -- Get last AML decision - SELECT - new_threshold - ,kyc_requirements - ,new_status - INTO - my_aml_data - FROM aml_history - WHERE h_payto=my_wire_target_h_payto - ORDER BY aml_history_serial_id -- get last decision - DESC LIMIT 1; - IF FOUND - THEN - my_aml_threshold=my_aml_data.new_threshold; - my_kyc_pending=my_aml_data.kyc_requirements; - my_aml_status=my_aml_data.kyc_status; - ELSE - my_aml_threshold=NULL; - my_kyc_pending=NULL; - my_aml_status=0; - END IF; - IF 0 != my_aml_status - THEN - RETURN NEXT ( - my_total - ,my_payto_uri - ,my_kyc_pending - ,my_earliest_deadline - ,my_aml_status - ,NULL); - END IF; - - -- Check KYC status - SELECT string_to_array (required_checks, ' ') - INTO my_required_checks - FROM legitimization_requirements - WHERE h_payto=my_wire_target_h_payto; - - --- PERFORM -- provider --- FROM kyc_attributes --- WHERE legitimization_serial=my_legitimization_serial; - -- FIXME: can't tell if providers cover all required checks from DB!!! - -- Idea: expand kyc_attributes table with list of satisfied checks!??! - - RETURN NEXT ( - my_total - ,my_payto_uri - ,my_kyc_pending - ,my_earliest_deadline - ,my_aml_status - ,NULL::taler_amount); + my_total.val = my_total_val + my_total_frac / 100000000; + my_total.frac = my_total_frac % 100000000; + RETURN NEXT ( + i.batch_deposit_serial_id + ,my_total + ,i.wire_target_h_payto + ,i.wire_deadline); END LOOP; CLOSE missing; diff --git a/src/exchangedb/exchange_do_select_justification_for_missing_wire.sql b/src/exchangedb/exchange_do_select_justification_for_missing_wire.sql new file mode 100644 index 000000000..5c53e6286 --- /dev/null +++ b/src/exchangedb/exchange_do_select_justification_for_missing_wire.sql @@ -0,0 +1,104 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2023 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/> +-- +-- @author: Christian Grothoff + +-- FIXME: this function is not working as intended at all yet, work in progress! + +CREATE OR REPLACE FUNCTION exchange_do_select_justification_missing_wire( + IN in_wire_target_h_payto BYTEA, + IN in_current_time INT8, + OUT out_payto_uri TEXT, -- NULL allowed + OUT out_kyc_pending TEXT, -- NULL allowed + OUT out_aml_status INT4, -- NULL allowed + OUT out_aml_limit taler_amount) -- NULL allowed! +LANGUAGE plpgsql +AS $$ +DECLARE + my_required_checks TEXT[]; +DECLARE + my_aml_data RECORD; +DECLARE + satisfied CURSOR FOR + SELECT satisfied_checks + FROM kyc_attributes + WHERE h_payto=in_wire_target_h_payto + AND expiration_time < in_current_time; +DECLARE + i RECORD; +BEGIN + + -- Fetch payto URI + out_payto_uri = NULL; + SELECT payto_uri + INTO out_payto_uri + FROM wire_targets + WHERE wire_target_h_payto=my_wire_target_h_payto; + + -- Check KYC status + my_required_checks = NULL; + SELECT string_to_array (required_checks, ' ') + INTO my_required_checks + FROM legitimization_requirements + WHERE h_payto=my_wire_target_h_payto; + + -- Get last AML decision + SELECT + new_threshold + ,kyc_requirements + ,new_status + INTO + my_aml_data + FROM aml_history + WHERE h_payto=in_wire_target_h_payto + ORDER BY aml_history_serial_id -- get last decision + DESC LIMIT 1; + IF FOUND + THEN + out_aml_limit=my_aml_data.new_threshold; + out_aml_status=my_aml_data.kyc_status; + -- Combine KYC requirements + my_required_checks + = array_cat (my_required_checks, + my_aml_data.kyc_requirements); + ELSE + out_aml_limit=NULL; + out_aml_status=0; -- or NULL? Style question! + END IF; + + OPEN satisfied; + LOOP + FETCH NEXT FROM satisfied INTO i; + EXIT WHEN NOT FOUND; + + -- remove all satisfied checks from the list + FOR i in 1..array_length(i.satisfied_checks) + LOOP + my_required_checks + = array_remove (my_required_checks, + i.satisfied_checks[i]); + END LOOP; + END LOOP; + + -- Return remaining required checks as one string + IF ( (my_required_checks IS NOT NULL) AND + (0 < array_length(my_satisfied_checks)) ) + THEN + out_kyc_pending + = array_to_string (my_required_checks, ' '); + END IF; + + RETURN; +END $$; diff --git a/src/exchangedb/pg_insert_kyc_attributes.c b/src/exchangedb/pg_insert_kyc_attributes.c index e3c246e5c..bdddd020c 100644 --- a/src/exchangedb/pg_insert_kyc_attributes.c +++ b/src/exchangedb/pg_insert_kyc_attributes.c @@ -25,9 +25,6 @@ #include "pg_insert_kyc_attributes.h" #include "pg_helper.h" -void -event_do_poll (struct GNUNET_PQ_Context *db); - enum GNUNET_DB_QueryStatus TEH_PG_insert_kyc_attributes ( @@ -36,6 +33,8 @@ TEH_PG_insert_kyc_attributes ( const struct TALER_PaytoHashP *h_payto, const struct GNUNET_ShortHashCode *kyc_prox, const char *provider_section, + unsigned int num_checks, + const char *satisfied_checks[static num_checks], uint32_t birthday, struct GNUNET_TIME_Timestamp collection_time, const char *provider_account_id, @@ -60,6 +59,9 @@ TEH_PG_insert_kyc_attributes ( GNUNET_PQ_query_param_auto_from_type (h_payto), GNUNET_PQ_query_param_auto_from_type (kyc_prox), GNUNET_PQ_query_param_string (provider_section), + GNUNET_PQ_query_param_array_ptrs_string (num_checks, + satisfied_checks, + pg->conn), GNUNET_PQ_query_param_uint32 (&birthday), (NULL == provider_account_id) ? GNUNET_PQ_query_param_null () @@ -92,7 +94,7 @@ TEH_PG_insert_kyc_attributes ( "SELECT " " out_ok" " FROM exchange_do_insert_kyc_attributes " - "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13);"); + "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14);"); qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn, "insert_kyc_attributes", params, diff --git a/src/exchangedb/pg_insert_kyc_attributes.h b/src/exchangedb/pg_insert_kyc_attributes.h index c1aad0eb5..35b25bdc8 100644 --- a/src/exchangedb/pg_insert_kyc_attributes.h +++ b/src/exchangedb/pg_insert_kyc_attributes.h @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022 Taler Systems SA + Copyright (C) 2022, 2023 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 @@ -35,6 +35,8 @@ * @param h_payto account for which the attribute data is stored * @param kyc_prox key for similarity search * @param provider_section provider that must be checked + * @param num_checks how many checks do these attributes satisfy + * @param satisfied_checks array of checks satisfied by these attributes * @param provider_account_id provider account ID * @param provider_legitimization_id provider legitimization ID * @param birthday birthdate of user, in days after 1990, or 0 if unknown or definitively adult @@ -52,6 +54,8 @@ TEH_PG_insert_kyc_attributes ( const struct TALER_PaytoHashP *h_payto, const struct GNUNET_ShortHashCode *kyc_prox, const char *provider_section, + unsigned int num_checks, + const char *satisfied_checks[static num_checks], uint32_t birthday, struct GNUNET_TIME_Timestamp collection_time, const char *provider_account_id, diff --git a/src/exchangedb/pg_select_aggregations_above_serial.c b/src/exchangedb/pg_select_aggregations_above_serial.c new file mode 100644 index 000000000..52d202702 --- /dev/null +++ b/src/exchangedb/pg_select_aggregations_above_serial.c @@ -0,0 +1,137 @@ +/* + This file is part of TALER + Copyright (C) 2023 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/> + */ +/** + * @file exchangedb/pg_select_aggregations_above_serial.c + * @brief Implementation of the select_aggregations_above_serial function for Postgres + * @author Christian Grothoff + */ +#include "platform.h" +#include "taler_error_codes.h" +#include "taler_dbevents.h" +#include "taler_pq_lib.h" +#include "pg_select_aggregations_above_serial.h" +#include "pg_helper.h" + +/** + * Closure for #aggregation_serial_helper_cb(). + */ +struct AggregationSerialContext +{ + + /** + * Callback to call. + */ + TALER_EXCHANGEDB_AggregationCallback cb; + + /** + * Closure for @e cb. + */ + void *cb_cls; + + /** + * Plugin context. + */ + struct PostgresClosure *pg; + + /** + * Status code, set to #GNUNET_SYSERR on hard errors. + */ + enum GNUNET_GenericReturnValue status; +}; + + +/** + * Helper function to be called with the results of a SELECT statement + * that has returned @a num_results results. + * + * @param cls closure of type `struct AggregationSerialContext` + * @param result the postgres result + * @param num_results the number of results in @a result + */ +static void +aggregation_serial_helper_cb (void *cls, + PGresult *result, + unsigned int num_results) +{ + struct AggregationSerialContext *dsc = cls; + + for (unsigned int i = 0; i<num_results; i++) + { + uint64_t tracking_rowid; + uint64_t batch_deposit_serial_id; + struct GNUNET_PQ_ResultSpec rs[] = { + GNUNET_PQ_result_spec_uint64 ("aggregation_serial_id", + &tracking_rowid), + GNUNET_PQ_result_spec_uint64 ("batch_deposit_serial_id", + &batch_deposit_serial_id), + GNUNET_PQ_result_spec_end + }; + + if (GNUNET_OK != + GNUNET_PQ_extract_result (result, + rs, + i)) + { + GNUNET_break (0); + dsc->status = GNUNET_SYSERR; + return; + } + dsc->cb (dsc->cb_cls, + tracking_rowid, + batch_deposit_serial_id); + GNUNET_PQ_cleanup_result (rs); + } +} + + +enum GNUNET_DB_QueryStatus +TEH_PG_select_aggregations_above_serial ( + void *cls, + uint64_t min_tracking_serial_id, + TALER_EXCHANGEDB_AggregationCallback cb, + void *cb_cls) +{ + struct PostgresClosure *pg = cls; + struct GNUNET_PQ_QueryParam params[] = { + GNUNET_PQ_query_param_uint64 (&min_tracking_serial_id), + GNUNET_PQ_query_param_end + }; + struct AggregationSerialContext asc = { + .cb = cb, + .cb_cls = cb_cls, + .pg = pg, + .status = GNUNET_OK + }; + enum GNUNET_DB_QueryStatus qs; + + /* Fetch aggregations with rowid '\geq' the given parameter */ + PREPARE (pg, + "select_aggregations_above_serial", + "SELECT" + " aggregation_serial_id" + ",batch_deposit_serial_id" + " FROM aggregation_tracking" + " WHERE aggregation_serial_id>=$1" + " ORDER BY aggregation_serial_id ASC;"); + qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn, + "select_aggregations_above_serial", + params, + &aggregation_serial_helper_cb, + &asc); + if (GNUNET_OK != asc.status) + return GNUNET_DB_STATUS_HARD_ERROR; + return qs; +} diff --git a/src/exchangedb/pg_select_aggregations_above_serial.h b/src/exchangedb/pg_select_aggregations_above_serial.h new file mode 100644 index 000000000..3950d11fb --- /dev/null +++ b/src/exchangedb/pg_select_aggregations_above_serial.h @@ -0,0 +1,47 @@ +/* + This file is part of TALER + Copyright (C) 2023 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/> + */ +/** + * @file exchangedb/pg_select_aggregations_above_serial.h + * @brief implementation of the select_aggregations_above_serial function for Postgres + * @author Christian Grothoff + */ +#ifndef PG_SELECT_AGGREGATIONS_ABOVE_SERIAL_H +#define PG_SELECT_AGGREGATIONS_ABOVE_SERIAL_H + +#include "taler_util.h" +#include "taler_json_lib.h" +#include "taler_exchangedb_plugin.h" + + +/** + * Select all aggregation tracking IDs in the database + * above a given @a min_tracking_serial_id. + * + * @param cls closure + * @param min_tracking_serial_id only return entries stricly above this row (and in order) + * @param cb function to call on all such aggregations + * @param cb_cls closure for @a cb + * @return transaction status code + */ +enum GNUNET_DB_QueryStatus +TEH_PG_select_aggregations_above_serial ( + void *cls, + uint64_t min_tracking_serial_id, + TALER_EXCHANGEDB_AggregationCallback cb, + void *cb_cls); + + +#endif diff --git a/src/exchangedb/pg_select_batch_deposits_missing_wire.c b/src/exchangedb/pg_select_batch_deposits_missing_wire.c index 25b532ad6..1ab0a4b3b 100644 --- a/src/exchangedb/pg_select_batch_deposits_missing_wire.c +++ b/src/exchangedb/pg_select_batch_deposits_missing_wire.c @@ -69,34 +69,19 @@ missing_wire_cb (void *cls, while (0 < num_results) { - struct TALER_Amount total_amount; - struct TALER_Amount aml_limit; - char *payto_uri = NULL; - char *kyc_pending = NULL; - uint32_t aml_status32 = TALER_AML_NORMAL; + uint64_t batch_deposit_serial_id; struct GNUNET_TIME_Timestamp deadline; - bool no_aml_limit; + struct TALER_PaytoHashP wire_target_h_payto; + struct TALER_Amount total_amount; struct GNUNET_PQ_ResultSpec rs[] = { - TALER_PQ_RESULT_SPEC_AMOUNT ("total_amount_with_fee", - &total_amount), - GNUNET_PQ_result_spec_allow_null ( - GNUNET_PQ_result_spec_string ("payto_uri", - &payto_uri), - NULL), - GNUNET_PQ_result_spec_allow_null ( - GNUNET_PQ_result_spec_string ("kyc_pending", - &kyc_pending), - NULL), - GNUNET_PQ_result_spec_timestamp ("wire_deadline", + GNUNET_PQ_result_spec_uint64 ("batch_deposit_serial_id", + &batch_deposit_serial_id), + GNUNET_PQ_result_spec_auto_from_type ("wire_target_h_payto", + &wire_target_h_payto), + GNUNET_PQ_result_spec_timestamp ("deadline", &deadline), - GNUNET_PQ_result_spec_allow_null ( - GNUNET_PQ_result_spec_uint32 ("aml_status", - &aml_status32), - NULL), - GNUNET_PQ_result_spec_allow_null ( - TALER_PQ_RESULT_SPEC_AMOUNT ("aml_limit", - &aml_limit), - &no_aml_limit), + TALER_PQ_RESULT_SPEC_AMOUNT ("total_amount", + &total_amount), GNUNET_PQ_result_spec_end }; @@ -110,12 +95,10 @@ missing_wire_cb (void *cls, return; } mwc->cb (mwc->cb_cls, + batch_deposit_serial_id, &total_amount, - payto_uri, - deadline, - kyc_pending, - (enum TALER_AmlDecisionState) aml_status32, - no_aml_limit ? NULL : &aml_limit); + &wire_target_h_payto, + deadline); GNUNET_PQ_cleanup_result (rs); } } @@ -124,15 +107,13 @@ missing_wire_cb (void *cls, enum GNUNET_DB_QueryStatus TEH_PG_select_batch_deposits_missing_wire ( void *cls, - struct GNUNET_TIME_Timestamp start_date, - struct GNUNET_TIME_Timestamp end_date, + uint64_t min_batch_deposit_serial_id, TALER_EXCHANGEDB_WireMissingCallback cb, void *cb_cls) { struct PostgresClosure *pg = cls; struct GNUNET_PQ_QueryParam params[] = { - GNUNET_PQ_query_param_timestamp (&start_date), - GNUNET_PQ_query_param_timestamp (&end_date), + GNUNET_PQ_query_param_uint64 (&min_batch_deposit_serial_id), GNUNET_PQ_query_param_end }; struct MissingWireContext mwc = { @@ -144,18 +125,16 @@ TEH_PG_select_batch_deposits_missing_wire ( enum GNUNET_DB_QueryStatus qs; PREPARE (pg, - "deposits_get_overdue", + "deposits_get_deposits_missing_wire", "SELECT" - " total_amount_with_fee" - ",payto_uri" - ",kyc_pending" - ",wire_deadline" - ",aml_status" - ",aml_limit" + " batch_deposit_serial_id" + ",wire_target_h_payto" + ",deadline" + ",total_amount" " FROM exchange_do_select_deposits_missing_wire" - " ($1,$2);"); + " ($1);"); qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn, - "deposits_get_overdue", + "deposits_get_deposits_missing_wire", params, &missing_wire_cb, &mwc); diff --git a/src/exchangedb/pg_select_batch_deposits_missing_wire.h b/src/exchangedb/pg_select_batch_deposits_missing_wire.h index 27e6b0850..16f1d0cb3 100644 --- a/src/exchangedb/pg_select_batch_deposits_missing_wire.h +++ b/src/exchangedb/pg_select_batch_deposits_missing_wire.h @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022 Taler Systems SA + Copyright (C) 2022-2023 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 @@ -25,13 +25,11 @@ #include "taler_json_lib.h" #include "taler_exchangedb_plugin.h" /** - * Select all of those deposits in the database for which we do - * not have a wire transfer (or a refund) and which should have - * been deposited between @a start_date and @a end_date. + * Select all of those batch deposits in the database + * above the given serial ID. * * @param cls closure - * @param start_date lower bound on the requested wire execution date - * @param end_date upper bound on the requested wire execution date + * @param min_batch_deposit_serial_id select all batch deposits above this ID * @param cb function to call on all such deposits * @param cb_cls closure for @a cb * @return transaction status code @@ -39,10 +37,8 @@ enum GNUNET_DB_QueryStatus TEH_PG_select_batch_deposits_missing_wire ( void *cls, - struct GNUNET_TIME_Timestamp start_date, - struct GNUNET_TIME_Timestamp end_date, - TALER_EXCHANGEDB_WireMissingCallback - cb, + uint64_t min_batch_deposit_serial_id, + TALER_EXCHANGEDB_WireMissingCallback cb, void *cb_cls); #endif diff --git a/src/exchangedb/pg_select_justification_for_missing_wire.c b/src/exchangedb/pg_select_justification_for_missing_wire.c new file mode 100644 index 000000000..77d5b4de7 --- /dev/null +++ b/src/exchangedb/pg_select_justification_for_missing_wire.c @@ -0,0 +1,89 @@ +/* + This file is part of TALER + Copyright (C) 2022-2023 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/> + */ +/** + * @file exchangedb/pg_select_batch_deposits_missing_wire.c + * @brief Implementation of the select_batch_deposits_missing_wire function for Postgres + * @author Christian Grothoff + */ +#include "platform.h" +#include "taler_error_codes.h" +#include "taler_dbevents.h" +#include "taler_pq_lib.h" +#include "pg_select_batch_deposits_missing_wire.h" +#include "pg_helper.h" + + +enum GNUNET_DB_QueryStatus +TEH_PG_select_justification_for_missing_wire ( + void *cls, + const struct TALER_PaytoHashP *wire_target_h_payto, + char **payto_uri, + char **kyc_pending, + enum TALER_AmlDecisionState *status, + struct TALER_Amount *aml_limit) +{ + struct PostgresClosure *pg = cls; + struct GNUNET_TIME_Absolute now + = GNUNET_TIME_absolute_get (); + struct GNUNET_PQ_QueryParam params[] = { + GNUNET_PQ_query_param_auto_from_type (wire_target_h_payto), + GNUNET_PQ_query_param_absolute_time (&now), + GNUNET_PQ_query_param_end + }; + uint32_t aml_status32; + struct GNUNET_PQ_ResultSpec rs[] = { + GNUNET_PQ_result_spec_allow_null ( + GNUNET_PQ_result_spec_string ("payto_uri", + payto_uri), + NULL), + GNUNET_PQ_result_spec_allow_null ( + GNUNET_PQ_result_spec_string ("kyc_pending", + kyc_pending), + NULL), + GNUNET_PQ_result_spec_allow_null ( + GNUNET_PQ_result_spec_uint32 ("aml_status", + &aml_status32), + NULL), + GNUNET_PQ_result_spec_allow_null ( + TALER_PQ_RESULT_SPEC_AMOUNT ("aml_limit", + aml_limit), + NULL), + GNUNET_PQ_result_spec_end + }; + enum GNUNET_DB_QueryStatus qs; + + PREPARE (pg, + "deposits_get_overdue", + "SELECT" + " out_payto_uri AS payto_uri" + ",out_kyc_pending AS kyc_pending" + ",out_deadline AS deadline" + ",out_aml_status AS aml_status" + ",out_aml_limit AS aml_limit" + " FROM exchange_do_select_justification_missing_wire" + " ($1, $2);"); + memset (aml_limit, + 0, + sizeof (*aml_limit)); + qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn, + "", + params, + rs); + if (qs <= 0) + return qs; + *status = (enum TALER_AmlDecisionState) aml_status32; + return qs; +} diff --git a/src/exchangedb/pg_select_justification_for_missing_wire.h b/src/exchangedb/pg_select_justification_for_missing_wire.h new file mode 100644 index 000000000..7f73eb511 --- /dev/null +++ b/src/exchangedb/pg_select_justification_for_missing_wire.h @@ -0,0 +1,49 @@ +/* + This file is part of TALER + Copyright (C) 2022 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/> + */ +/** + * @file exchangedb/pg_select_justification_for_missing_wire.h + * @brief implementation of the select_justification_for_missing_wire function for Postgres + * @author Christian Grothoff + */ +#ifndef PG_SELECT_JUSTIFICATION_FOR_MISSING_WIRE_H +#define PG_SELECT_JUSTIFICATION_FOR_MISSING_WIRE_H + +#include "taler_util.h" +#include "taler_json_lib.h" +#include "taler_exchangedb_plugin.h" + +/** + * Select all of those justifications for why we might not have + * done a wire transfer from in the database for a particular target account. + * + * @param cls closure + * @param wire_target_h_payto effected target account + * @param[out] payto_uri target account URI, set to NULL if unknown + * @param[out] kyc_pending set to string describing missing KYC data + * @param[out] status set to AML status + * @param[out] aml_limit set to AML limit, or invalid amount for none + * @return transaction status code + */ +enum GNUNET_DB_QueryStatus +TEH_PG_select_justification_for_missing_wire ( + void *cls, + const struct TALER_PaytoHashP *wire_target_h_payto, + char **payto_uri, + char **kyc_pending, + enum TALER_AmlDecisionState *status, + struct TALER_Amount *aml_limit); + +#endif diff --git a/src/exchangedb/pg_template.c b/src/exchangedb/pg_template.c index 095d89615..be54970f0 100644 --- a/src/exchangedb/pg_template.c +++ b/src/exchangedb/pg_template.c @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022 Taler Systems SA + Copyright (C) 2023 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 diff --git a/src/exchangedb/pg_template.h b/src/exchangedb/pg_template.h index 88bb930d3..7387b4883 100644 --- a/src/exchangedb/pg_template.h +++ b/src/exchangedb/pg_template.h @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2022 Taler Systems SA + Copyright (C) 2023 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 diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c index 067e859b8..c6e55d017 100644 --- a/src/exchangedb/plugin_exchangedb_postgres.c +++ b/src/exchangedb/plugin_exchangedb_postgres.c @@ -1,6 +1,6 @@ /* This file is part of TALER - Copyright (C) 2014--2022 Taler Systems SA + Copyright (C) 2014--2023 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 @@ -177,6 +177,8 @@ #include "pg_insert_denomination_revocation.h" #include "pg_get_denomination_revocation.h" #include "pg_select_batch_deposits_missing_wire.h" +#include "pg_select_justification_for_missing_wire.h" +#include "pg_select_aggregations_above_serial.h" #include "pg_lookup_auditor_timestamp.h" #include "pg_lookup_auditor_status.h" #include "pg_insert_auditor.h" @@ -699,6 +701,10 @@ libtaler_plugin_exchangedb_postgres_init (void *cls) = &TEH_PG_get_denomination_revocation; plugin->select_batch_deposits_missing_wire = &TEH_PG_select_batch_deposits_missing_wire; + plugin->select_justification_for_missing_wire + = &TEH_PG_select_justification_for_missing_wire; + plugin->select_aggregations_above_serial + = &TEH_PG_select_aggregations_above_serial; plugin->lookup_auditor_timestamp = &TEH_PG_lookup_auditor_timestamp; plugin->lookup_auditor_status diff --git a/src/exchangedb/procedures.sql.in b/src/exchangedb/procedures.sql.in index 4a23ec742..b963900c9 100644 --- a/src/exchangedb/procedures.sql.in +++ b/src/exchangedb/procedures.sql.in @@ -27,6 +27,7 @@ SET search_path TO exchange; #include "exchange_do_deposit.sql" #include "exchange_do_melt.sql" #include "exchange_do_select_deposits_missing_wire.sql" +#include "exchange_do_select_justification_for_missing_wire.sql" #include "exchange_do_refund.sql" #include "exchange_do_recoup_to_reserve.sql" #include "exchange_do_recoup_to_coin.sql" diff --git a/src/exchangedb/test_exchangedb.c b/src/exchangedb/test_exchangedb.c index 081126637..90c539a98 100644 --- a/src/exchangedb/test_exchangedb.c +++ b/src/exchangedb/test_exchangedb.c @@ -1113,40 +1113,32 @@ drop: /** - * Function called on deposits that are past their due date - * and have not yet seen a wire transfer. + * Function called on batch deposits that may require a + * wire transfer. * * @param cls closure a `struct TALER_EXCHANGEDB_Deposit *` + * @param batch_deposit_serial_id where in the table are we * @param total_amount value of all missing deposits, including fees - * @param payto_uri where should the funds be wired; URI in payto://-format + * @param wire_target_h_payto hash of the recipient account's payto URI * @param deadline what was the earliest requested wire transfer deadline - * @param kyc_pending NULL if no KYC requirement is pending, otherwise text describing the missing KYC requirement - * @param aml_status status of AML possibly blocking the transfer - * @param aml_limit current monthly AML limit */ static void wire_missing_cb ( void *cls, + uint64_t batch_deposit_serial_id, const struct TALER_Amount *total_amount, - const char *payto_uri, - struct GNUNET_TIME_Timestamp deadline, - const char *kyc_pending, - enum TALER_AmlDecisionState status, - const struct TALER_Amount *aml_limit) + const struct TALER_PaytoHashP *wire_target_h_payto, + struct GNUNET_TIME_Timestamp deadline) { const struct TALER_EXCHANGEDB_CoinDepositInformation *deposit = cls; - (void) payto_uri; + (void) batch_deposit_serial_id; (void) deadline; - (void) kyc_pending; - (void) status; - (void) aml_limit; - if (0 != TALER_amount_cmp (total_amount, - &deposit->amount_with_fee)) - { - GNUNET_break (0); - result = 66; - } + (void) wire_target_h_payto; + if (0 == + TALER_amount_cmp (total_amount, + &deposit->amount_with_fee)) + result = 8; } @@ -2162,19 +2154,10 @@ run (void *cls) r)); } { - struct GNUNET_TIME_Timestamp start_range; - struct GNUNET_TIME_Timestamp end_range; - - start_range = GNUNET_TIME_absolute_to_timestamp ( - GNUNET_TIME_absolute_subtract (deadline.abs_time, - GNUNET_TIME_UNIT_SECONDS)); - end_range = GNUNET_TIME_absolute_to_timestamp ( - GNUNET_TIME_absolute_add (deadline.abs_time, - GNUNET_TIME_UNIT_SECONDS)); - FAILIF (GNUNET_DB_STATUS_SUCCESS_ONE_RESULT != + result = 66; + FAILIF (0 >= plugin->select_batch_deposits_missing_wire (plugin->cls, - start_range, - end_range, + 0, &wire_missing_cb, &deposit)); FAILIF (8 != result); |