From f8bfc4dc9d9dcb69ccdd95258aea53dabf997246 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Sun, 22 Jan 2023 15:13:34 +0100 Subject: address DB FIXMEs for AML --- src/exchangedb/0003-aml_history.sql | 2 +- src/exchangedb/exchange_do_insert_aml_decision.sql | 102 +++++++++++++++++++++ src/exchangedb/exchange_do_insert_aml_officer.sql | 74 +++++++++++++++ src/exchangedb/pg_insert_aml_decision.c | 32 +++---- src/exchangedb/pg_insert_aml_decision.h | 2 +- src/exchangedb/pg_insert_aml_officer.c | 26 +++--- src/exchangedb/pg_persist_policy_details.c | 10 +- src/exchangedb/procedures.sql.in | 2 + 8 files changed, 218 insertions(+), 32 deletions(-) create mode 100644 src/exchangedb/exchange_do_insert_aml_decision.sql create mode 100644 src/exchangedb/exchange_do_insert_aml_officer.sql (limited to 'src/exchangedb') diff --git a/src/exchangedb/0003-aml_history.sql b/src/exchangedb/0003-aml_history.sql index c2ab532da..1c737265b 100644 --- a/src/exchangedb/0003-aml_history.sql +++ b/src/exchangedb/0003-aml_history.sql @@ -110,7 +110,7 @@ BEGIN EXECUTE FORMAT ( 'CREATE INDEX ' || table_name || '_main_index ' 'ON ' || table_name || ' ' - '(h_payto ASC, decision_time ASC);' + '(h_payto, decision_time DESC);' ); END $$; diff --git a/src/exchangedb/exchange_do_insert_aml_decision.sql b/src/exchangedb/exchange_do_insert_aml_decision.sql new file mode 100644 index 000000000..480736afa --- /dev/null +++ b/src/exchangedb/exchange_do_insert_aml_decision.sql @@ -0,0 +1,102 @@ +-- +-- 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 +-- + +CREATE OR REPLACE FUNCTION exchange_do_insert_aml_decision( + IN in_h_payto BYTEA, + IN in_new_threshold_val INT8, + IN in_new_threshold_frac INT4, + IN in_new_status INT4, + IN in_decision_time INT8, + IN in_justification VARCHAR, + IN in_decider_pub BYTEA, + IN in_decider_sig BYTEA, + OUT out_invalid_officer BOOLEAN, + OUT out_last_date INT8) +LANGUAGE plpgsql +AS $$ + +-- Check officer is eligible to make decisions. +PERFORM + FROM exchange.aml_staff + WHERE decider_pub=in_decider_pub + AND is_active + AND NOT read_only; +IF NOT FOUND +THEN + out_invalid_officer=TRUE; + out_last_date=0; + RETURN; +END IF; +out_invalid_officer=FALSE; + +-- Check no more recent decision exists. +SELECT decision_time + INTO out_last_date + FROM exchange.aml_history + WHERE h_payto=in_h_payto + ORDER BY decision_time DESC; +IF FOUND +THEN + IF out_last_date >= in_decision_time + THEN + -- Refuse to insert older decision. + RETURN; + END IF; + UPDATE exchange.aml_status + SET threshold_val=in_threshold_val + ,threshold_frac=in_threshold_frac + ,status=in_new_status + WHERE h_payto=in_h_payto; + ASSERT FOUND, 'cannot have AML decision history but no AML status'; +ELSE + out_last_date = 0; + INSERT INTO exchange.aml_status + (h_payto + ,threshold_val + ,threshold_frac + ,status) + VALUES + (in_h_payto + ,in_threshold_val + ,in_threshold_frac + ,in_new_status); +END IF; + + +INSERT INTO exchange.aml_history + (h_payto + ,new_threshold_val + ,new_threshold_frac + ,new_status + ,decision_time + ,justification + ,decider_pub + ,decider_sig + ) VALUES + (in_h_payto + ,in_new_threshold_val + ,in_new_threshold_frac + ,in_new_status + ,in_decision_time + ,in_justification + ,in_decider_pub + ,in_decider_sig); + +END $$; + + +COMMENT ON FUNCTION exchange_do_insert_aml_decision(BYTEA, INT8, INT4, INT4, INT8, VARCHAR, BYTEA, BYTEA) + IS 'Checks whether the AML officer is eligible to make AML decisions and if so inserts the decision into the table'; diff --git a/src/exchangedb/exchange_do_insert_aml_officer.sql b/src/exchangedb/exchange_do_insert_aml_officer.sql new file mode 100644 index 000000000..72f813e89 --- /dev/null +++ b/src/exchangedb/exchange_do_insert_aml_officer.sql @@ -0,0 +1,74 @@ +-- +-- 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 +-- + +CREATE OR REPLACE FUNCTION exchange_do_insert_aml_officer( + IN in_decider_pub BYTEA, + IN in_master_sig BYTEA, + IN in_decider_name VARCHAR, + IN in_is_active BOOLEAN, + IN in_read_only BOOLEAN, + IN in_last_change INT8, + OUT out_last_change INT8) +LANGUAGE plpgsql +AS $$ + +INSERT INTO exchange.aml_staff + (decider_pub + ,master_sig + ,decider_name + ,is_active + ,read_only + ,last_change + ) VALUES + (in_decider_pub + ,in_master_sig + ,in_decider_name + ,in_is_active + ,in_read_only + ,in_last_change) + ON CONFLICT DO NOTHING; +IF FOUND +THEN + out_last_change=0; + RETURN; +END IF; + +-- Check update is most recent... +SELECT last_change + INTO out_last_change + FROM exchange.aml_staff + WHERE decider_pub=in_decider_pub; +ASSERT FOUND, 'cannot have INSERT conflict but no AML staff record'; + +IF out_last_change >= in_last_change +THEN + -- Refuse to insert older status + RETURN; +END IF; + +-- We are more recent, update existing record. +UPDATE exchange.aml_staff + SET master_sig=in_master_sig + ,decider_name=in_decider_name + ,is_active=in_is_active + ,read_only=in_read_only + ,last_change=in_last_change + WHERE decider_pub=in_decider_pub; +END $$; + + +COMMENT ON FUNCTION exchange_do_insert_aml_officer(BYTEA, BYTEA, VARCHAR, BOOL, BOOL, INT8) + IS 'Inserts or updates AML staff record, making sure the update is more recent than the previous change'; diff --git a/src/exchangedb/pg_insert_aml_decision.c b/src/exchangedb/pg_insert_aml_decision.c index 421628f65..85570ed85 100644 --- a/src/exchangedb/pg_insert_aml_decision.c +++ b/src/exchangedb/pg_insert_aml_decision.c @@ -51,23 +51,23 @@ TEH_PG_insert_aml_decision ( GNUNET_PQ_query_param_auto_from_type (decider_sig), GNUNET_PQ_query_param_end }; + struct GNUNET_PQ_ResultSpec rs[] = { + GNUNET_PQ_result_spec_bool ("out_invalid_officer", + invalid_officer), + GNUNET_PQ_result_spec_timestamp ("out_last_date", + last_date), + GNUNET_PQ_result_spec_end + }; - // FIXME: set invalid_officer - // FIXME: set last_date! PREPARE (pg, - "insert_aml_decision", - "INSERT INTO aml_history " - "(h_payto" - ",new_threshold_val" - ",new_threshold_frac" - ",new_status" - ",decision_time" - ",justification" - ",decider_pub" - ",decider_sig" - ") VALUES " + "do_insert_aml_decision", + "SELECT" + " out_invalid_officer" + ",out_last_date" + " FROM exchange_do_insert_aml_decision" "($1, $2, $3, $4, $5, $6, $7, $8);"); - return GNUNET_PQ_eval_prepared_non_select (pg->conn, - "insert_aml_decision", - params); + return GNUNET_PQ_eval_prepared_singleton_select (pg->conn, + "do_insert_aml_decision", + params, + rs); } diff --git a/src/exchangedb/pg_insert_aml_decision.h b/src/exchangedb/pg_insert_aml_decision.h index 94f648fb1..b539945a7 100644 --- a/src/exchangedb/pg_insert_aml_decision.h +++ b/src/exchangedb/pg_insert_aml_decision.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 diff --git a/src/exchangedb/pg_insert_aml_officer.c b/src/exchangedb/pg_insert_aml_officer.c index 33e6c86f7..c1f635a64 100644 --- a/src/exchangedb/pg_insert_aml_officer.c +++ b/src/exchangedb/pg_insert_aml_officer.c @@ -47,20 +47,20 @@ TEH_PG_insert_aml_officer ( GNUNET_PQ_query_param_timestamp (&last_change), GNUNET_PQ_query_param_end }; + struct GNUNET_PQ_ResultSpec rs[] = { + GNUNET_PQ_result_spec_timestamp ("out_last_change", + previous_change), + GNUNET_PQ_result_spec_end + }; - // FIXME: need to check for previous record! PREPARE (pg, - "insert_aml_staff", - "INSERT INTO aml_staff " - "(decider_pub" - ",master_sig" - ",decider_name" - ",is_active" - ",read_only" - ",last_change" - ") VALUES " + "do_insert_aml_staff", + "SELECT" + " out_last_change" + " FROM exchange_do_insert_aml_officer" "($1, $2, $3, $4, $5, $6);"); - return GNUNET_PQ_eval_prepared_non_select (pg->conn, - "insert_aml_staff", - params); + return GNUNET_PQ_eval_prepared_singleton_select (pg->conn, + "do_insert_aml_staff", + params, + rs); } diff --git a/src/exchangedb/pg_persist_policy_details.c b/src/exchangedb/pg_persist_policy_details.c index 2b7787878..3bc7afa98 100644 --- a/src/exchangedb/pg_persist_policy_details.c +++ b/src/exchangedb/pg_persist_policy_details.c @@ -59,7 +59,15 @@ TEH_PG_persist_policy_details ( GNUNET_PQ_result_spec_end }; - // FIXME: prepare missing!!?! + PREPARE (pg, + "call_insert_or_update_policy_details", + "SELECT" + " out_policy_details_serial_id AS policy_details_serial_id" + ",out_accumulated_total_val AS accumulated_total_val" + ",out_accumulated_total_frac AS accumulated_total_frac" + ",out_fulfillment_state AS fulfillment_state" + " FROM exchange_do_insert_or_update_policy_details" + "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13);"); return GNUNET_PQ_eval_prepared_singleton_select (pg->conn, "call_insert_or_update_policy_details", params, diff --git a/src/exchangedb/procedures.sql.in b/src/exchangedb/procedures.sql.in index 194830248..73b75af29 100644 --- a/src/exchangedb/procedures.sql.in +++ b/src/exchangedb/procedures.sql.in @@ -37,6 +37,8 @@ SET search_path TO exchange; #include "exchange_do_reserve_open_deposit.sql" #include "exchange_do_reserve_open.sql" #include "exchange_do_insert_or_update_policy_details.sql" +#include "exchange_do_insert_aml_decision.sql" +#include "exchange_do_insert_aml_officer.sql" #include "exchange_do_batch_reserves_in_insert.sql" #include "exchange_do_batch_reserves_update.sql" #include "exchange_do_batch2_reserves_in_insert.sql" -- cgit v1.2.3