From 00c68e0a04ae25f533e261547cf0847b4ae0de06 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Wed, 1 Jun 2022 23:03:40 +0200 Subject: -work on SQL for P2P table replication --- src/exchangedb/common-0001.sql | 7 +- src/exchangedb/irbt_callbacks.c | 12 ++ src/exchangedb/lrbt_callbacks.c | 31 ++- src/exchangedb/plugin_exchangedb_postgres.c | 299 ++++++++++++++++++++++++++++ 4 files changed, 335 insertions(+), 14 deletions(-) (limited to 'src/exchangedb') diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/common-0001.sql index 31c4b68e8..1e5619db7 100644 --- a/src/exchangedb/common-0001.sql +++ b/src/exchangedb/common-0001.sql @@ -1389,7 +1389,8 @@ BEGIN PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves(reserve_pub) ON DELETE CASCADE + '(history_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves(reserve_pub) ON DELETE CASCADE ',request_timestamp INT8 NOT NULL' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' ',history_fee_val INT8 NOT NULL' @@ -1418,7 +1419,8 @@ BEGIN PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I ' - '(reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves(reserve_pub) ON DELETE CASCADE + '(close_request_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' --UNIQUE' + ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)' -- REFERENCES reserves(reserve_pub) ON DELETE CASCADE ',close_timestamp INT8 NOT NULL' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' ',close_val INT8 NOT NULL' @@ -1489,7 +1491,6 @@ END $$; ---------------------------- wads_out ------------------------------- - CREATE OR REPLACE FUNCTION create_table_wads_out( IN shard_suffix VARCHAR DEFAULT NULL ) diff --git a/src/exchangedb/irbt_callbacks.c b/src/exchangedb/irbt_callbacks.c index c1802b96b..820911956 100644 --- a/src/exchangedb/irbt_callbacks.c +++ b/src/exchangedb/irbt_callbacks.c @@ -844,6 +844,7 @@ irbt_cb_table_purse_merges (struct PostgresClosure *pg, { struct GNUNET_PQ_QueryParam params[] = { GNUNET_PQ_query_param_uint64 (&td->serial), + GNUNET_PQ_query_param_uint64 (&td->details.purse_merges.partner_serial_id), GNUNET_PQ_query_param_auto_from_type ( &td->details.purse_merges.reserve_pub), GNUNET_PQ_query_param_auto_from_type (&td->details.purse_merges.purse_pub), @@ -870,6 +871,8 @@ irbt_cb_table_purse_deposits (struct PostgresClosure *pg, { struct GNUNET_PQ_QueryParam params[] = { GNUNET_PQ_query_param_uint64 (&td->serial), + GNUNET_PQ_query_param_uint64 ( + &td->details.purse_deposits.partner_serial_id), GNUNET_PQ_query_param_auto_from_type ( &td->details.purse_deposits.purse_pub), GNUNET_PQ_query_param_auto_from_type (&td->details.purse_deposits.coin_pub), @@ -929,6 +932,8 @@ irbt_cb_table_history_requests (struct PostgresClosure *pg, &td->details.history_requests.request_timestamp), GNUNET_PQ_query_param_auto_from_type ( &td->details.history_requests.reserve_sig), + TALER_PQ_query_param_amount ( + &td->details.history_requests.history_fee), GNUNET_PQ_query_param_end }; @@ -956,6 +961,8 @@ irbt_cb_table_close_requests (struct PostgresClosure *pg, &td->details.close_requests.close_timestamp), GNUNET_PQ_query_param_auto_from_type ( &td->details.close_requests.reserve_sig), + TALER_PQ_query_param_amount ( + &td->details.close_requests.close), GNUNET_PQ_query_param_end }; @@ -978,6 +985,7 @@ irbt_cb_table_wads_out (struct PostgresClosure *pg, struct GNUNET_PQ_QueryParam params[] = { GNUNET_PQ_query_param_uint64 (&td->serial), GNUNET_PQ_query_param_auto_from_type (&td->details.wads_out.wad_id), + GNUNET_PQ_query_param_uint64 (&td->details.wads_out.partner_serial_id), TALER_PQ_query_param_amount (&td->details.wads_out.amount), GNUNET_PQ_query_param_timestamp (&td->details.wads_out.execution_time), GNUNET_PQ_query_param_end @@ -1001,6 +1009,8 @@ irbt_cb_table_wads_out_entries (struct PostgresClosure *pg, { struct GNUNET_PQ_QueryParam params[] = { GNUNET_PQ_query_param_uint64 (&td->serial), + GNUNET_PQ_query_param_uint64 ( + &td->details.wads_out_entries.wad_out_serial_id), GNUNET_PQ_query_param_auto_from_type ( &td->details.wads_out_entries.reserve_pub), GNUNET_PQ_query_param_auto_from_type ( @@ -1013,6 +1023,8 @@ irbt_cb_table_wads_out_entries (struct PostgresClosure *pg, &td->details.wads_out_entries.merge_timestamp), TALER_PQ_query_param_amount ( &td->details.wads_out_entries.amount_with_fee), + TALER_PQ_query_param_amount ( + &td->details.wads_out_entries.wad_fee), TALER_PQ_query_param_amount ( &td->details.wads_out_entries.deposit_fees), GNUNET_PQ_query_param_auto_from_type ( diff --git a/src/exchangedb/lrbt_callbacks.c b/src/exchangedb/lrbt_callbacks.c index b621b7b00..7050199e5 100644 --- a/src/exchangedb/lrbt_callbacks.c +++ b/src/exchangedb/lrbt_callbacks.c @@ -1498,7 +1498,7 @@ lrbt_cb_table_purse_requests (void *cls, { struct GNUNET_PQ_ResultSpec rs[] = { GNUNET_PQ_result_spec_uint64 ( - "extension_details_serial_id", + "purse_requests_serial_id", &td.serial), GNUNET_PQ_result_spec_auto_from_type ( "purse_pub", @@ -1570,8 +1570,11 @@ lrbt_cb_table_purse_merges (void *cls, { struct GNUNET_PQ_ResultSpec rs[] = { GNUNET_PQ_result_spec_uint64 ( - "extension_details_serial_id", + "purse_merge_request_serial_id", &td.serial), + GNUNET_PQ_result_spec_uint64 ( + "partner_serial_id", + &td.details.purse_merges.partner_serial_id), GNUNET_PQ_result_spec_auto_from_type ( "reserve_pub", &td.details.purse_merges.reserve_pub), @@ -1582,7 +1585,7 @@ lrbt_cb_table_purse_merges (void *cls, "merge_sig", &td.details.purse_merges.merge_sig), GNUNET_PQ_result_spec_timestamp ( - "purse_expiration", + "merge_timestamp", &td.details.purse_merges.merge_timestamp), GNUNET_PQ_result_spec_end }; @@ -1625,8 +1628,11 @@ lrbt_cb_table_purse_deposits (void *cls, { struct GNUNET_PQ_ResultSpec rs[] = { GNUNET_PQ_result_spec_uint64 ( - "extension_details_serial_id", + "purse_deposit_serial_id", &td.serial), + GNUNET_PQ_result_spec_uint64 ( + "partner_serial_id", + &td.details.purse_deposits.partner_serial_id), GNUNET_PQ_result_spec_auto_from_type ( "purse_pub", &td.details.purse_deposits.purse_pub), @@ -1679,7 +1685,7 @@ lrbt_cb_table_account_merges (void *cls, { struct GNUNET_PQ_ResultSpec rs[] = { GNUNET_PQ_result_spec_uint64 ( - "extension_details_serial_id", + "account_merge_request_serial_id", &td.serial), GNUNET_PQ_result_spec_auto_from_type ( "reserve_pub", @@ -1731,7 +1737,7 @@ lrbt_cb_table_history_requests (void *cls, { struct GNUNET_PQ_ResultSpec rs[] = { GNUNET_PQ_result_spec_uint64 ( - "extension_details_serial_id", + "history_request_serial_id", &td.serial), GNUNET_PQ_result_spec_auto_from_type ( "reserve_pub", @@ -1783,7 +1789,7 @@ lrbt_cb_table_close_requests (void *cls, { struct GNUNET_PQ_ResultSpec rs[] = { GNUNET_PQ_result_spec_uint64 ( - "extension_details_serial_id", + "close_request_serial_id", &td.serial), GNUNET_PQ_result_spec_auto_from_type ( "reserve_pub", @@ -1835,7 +1841,7 @@ lrbt_cb_table_wads_out (void *cls, { struct GNUNET_PQ_ResultSpec rs[] = { GNUNET_PQ_result_spec_uint64 ( - "extension_details_serial_id", + "wad_out_serial_id", &td.serial), GNUNET_PQ_result_spec_auto_from_type ( "wad_id", @@ -1890,7 +1896,7 @@ lrbt_cb_table_wads_out_entries (void *cls, { struct GNUNET_PQ_ResultSpec rs[] = { GNUNET_PQ_result_spec_uint64 ( - "extension_details_serial_id", + "wad_out_entry_serial_id", &td.serial), GNUNET_PQ_result_spec_auto_from_type ( "reserve_pub", @@ -1910,6 +1916,9 @@ lrbt_cb_table_wads_out_entries (void *cls, TALER_PQ_RESULT_SPEC_AMOUNT ( "amount_with_fee", &td.details.wads_out_entries.amount_with_fee), + TALER_PQ_RESULT_SPEC_AMOUNT ( + "wad_fee", + &td.details.wads_out_entries.wad_fee), TALER_PQ_RESULT_SPEC_AMOUNT ( "deposit_fees", &td.details.wads_out_entries.deposit_fees), @@ -1960,7 +1969,7 @@ lrbt_cb_table_wads_in (void *cls, { struct GNUNET_PQ_ResultSpec rs[] = { GNUNET_PQ_result_spec_uint64 ( - "extension_details_serial_id", + "wad_in_serial_id", &td.serial), GNUNET_PQ_result_spec_auto_from_type ( "wad_id", @@ -2015,7 +2024,7 @@ lrbt_cb_table_wads_in_entries (void *cls, { struct GNUNET_PQ_ResultSpec rs[] = { GNUNET_PQ_result_spec_uint64 ( - "extension_details_serial_id", + "wad_in_entry_serial_id", &td.serial), GNUNET_PQ_result_spec_auto_from_type ( "reserve_pub", diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c index c2f9cbfb4..61e16d30c 100644 --- a/src/exchangedb/plugin_exchangedb_postgres.c +++ b/src/exchangedb/plugin_exchangedb_postgres.c @@ -3087,6 +3087,160 @@ prepare_statements (struct PostgresClosure *pg) " WHERE recoup_refresh_uuid > $1" " ORDER BY recoup_refresh_uuid ASC;", 1), + + + GNUNET_PQ_make_prepare ( + "select_above_serial_by_table_purse_requests", + "SELECT" + " purse_requests_serial_id" + ",purse_pub" + ",merge_pub" + ",purse_creation" + ",purse_expiration" + ",h_contract_terms" + ",age_limit" + ",flags" + ",amount_with_fee_val" + ",amount_with_fee_frac" + ",purse_fee_val" + ",purse_fee_frac" + ",purse_sig" + " FROM purse_requests" + " WHERE purse_requests_serial_id > $1" + " ORDER BY purse_requests_serial_id ASC;", + 1), + GNUNET_PQ_make_prepare ( + "select_above_serial_by_table_purse_merges", + "SELECT" + " purse_merge_request_serial_id" + ",partner_serial_id" + ",reserve_pub" + ",purse_pub" + ",merge_sig" + ",merge_timestamp" + " FROM purse_merges" + " WHERE purse_merge_request_serial_id > $1" + " ORDER BY purse_merge_request_serial_id ASC;", + 1), + GNUNET_PQ_make_prepare ( + "select_above_serial_by_table_purse_deposits", + "SELECT" + " purse_deposit_serial_id" + ",partner_serial_id" + ",purse_pub" + ",coin_pub" + ",amount_with_fee_val" + ",amount_with_fee_frac" + ",coin_sig" + " FROM purse_deposits" + " WHERE purse_deposit_serial_id > $1" + " ORDER BY purse_deposit_serial_id ASC;", + 1), + GNUNET_PQ_make_prepare ( + "select_above_serial_by_table_account_merges", + "SELECT" + " account_merge_request_serial_id" + ",reserve_pub" + ",reserve_sig" + ",purse_pub" + " FROM account_merges" + " WHERE account_merge_request_serial_id > $1" + " ORDER BY account_merge_request_serial_id ASC;", + 1), + GNUNET_PQ_make_prepare ( + "select_above_serial_by_table_history_requests", + "SELECT" + " history_request_serial_id" + ",reserve_pub" + ",request_timestamp" + ",reserve_sig" + ",history_fee_val" + ",history_fee_frac" + " FROM history_requests" + " WHERE history_request_serial_id > $1" + " ORDER BY history_request_serial_id ASC;", + 1), + GNUNET_PQ_make_prepare ( + "select_above_serial_by_table_close_requests", + "SELECT" + " close_request_serial_id" + ",reserve_pub" + ",close_timestamp" + ",reserve_sig" + ",close_val" + ",close_frac" + " FROM close_requests" + " WHERE close_request_serial_id > $1" + " ORDER BY close_request_serial_id ASC;", + 1), + GNUNET_PQ_make_prepare ( + "select_above_serial_by_table_wads_out", + "SELECT" + " wad_out_serial_id" + ",wad_id" + ",partner_serial_id" + ",amount_val" + ",amount_frac" + ",execution_time" + " FROM wads_out" + " WHERE wad_out_serial_id > $1" + " ORDER BY wad_out_serial_id ASC;", + 1), + GNUNET_PQ_make_prepare ( + "select_above_serial_by_table_wads_out_entries", + "SELECT" + " wad_out_entry_serial_id" + ",reserve_pub" + ",purse_pub" + ",h_contract" + ",purse_expiration" + ",merge_timestamp" + ",amount_with_fee_val" + ",amount_with_fee_frac" + ",wad_fee_val" + ",wad_fee_frac" + ",deposit_fees_val" + ",deposit_fees_frac" + ",reserve_sig" + ",purse_sig" + " FROM wad_out_entries" + " WHERE wad_out_entry_serial_id > $1" + " ORDER BY wad_out_entry_serial_id ASC;", + 1), + GNUNET_PQ_make_prepare ( + "select_above_serial_by_table_wads_in", + "SELECT" + " wad_in_serial_id" + ",wad_id" + ",origin_exchange_url" + ",amount_val" + ",amount_frac" + ",arrival_time" + " FROM wads_in" + " WHERE wad_in_serial_id > $1" + " ORDER BY wad_in_serial_id ASC;", + 1), + GNUNET_PQ_make_prepare ( + "select_above_serial_by_table_wads_in_entries", + "SELECT" + " wad_in_entry_serial_id" + ",reserve_pub" + ",purse_pub" + ",h_contract" + ",purse_expiration" + ",merge_timestamp" + ",amount_with_fee_val" + ",amount_with_fee_frac" + ",wad_fee_val" + ",wad_fee_frac" + ",deposit_fees_val" + ",deposit_fees_frac" + ",reserve_sig" + ",purse_sig" + " FROM wad_in_entries" + " WHERE wad_in_entry_serial_id > $1" + " ORDER BY wad_in_entry_serial_id ASC;", + 1), /* For postgres_insert_records_by_table */ GNUNET_PQ_make_prepare ( "insert_into_table_denominations", @@ -3417,6 +3571,151 @@ prepare_statements (struct PostgresClosure *pg) "($1, $2);", 2), + GNUNET_PQ_make_prepare ( + "insert_into_table_purse_requests", + "INSERT INTO purse_requests" + "(purse_requests_serial_id" + ",purse_pub" + ",merge_pub" + ",purse_creation" + ",purse_expiration" + ",h_contract_terms" + ",age_limit" + ",flags" + ",amount_with_fee_val" + ",amount_with_fee_frac" + ",purse_fee_val" + ",purse_fee_frac" + ",purse_sig" + ") VALUES " + "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13);", + 13), + GNUNET_PQ_make_prepare ( + "insert_into_table_purse_merges", + "INSERT INTO purse_merges" + "(purse_merge_request_serial_id" + ",partner_serial_id" + ",reserve_pub" + ",purse_pub" + ",merge_sig" + ",merge_timestamp" + ") VALUES " + "($1, $2, $3, $4, $5, $6);", + 6), + GNUNET_PQ_make_prepare ( + "insert_into_table_purse_deposits", + "INSERT INTO purse_deposits" + "(purse_deposit_serial_id" + ",partner_serial_id" + ",purse_pub" + ",coin_pub" + ",amount_with_fee_val" + ",amount_with_fee_frac" + ",coin_sig" + ") VALUES " + "($1, $2, $3, $4, $5, $6, $7);", + 7), + GNUNET_PQ_make_prepare ( + "insert_into_table_account_merges", + "INSERT INTO account_merges" + "(account_merge_request_serial_id" + ",reserve_pub" + ",reserve_sig" + ",purse_pub" + ") VALUES " + "($1, $2, $3, $4);", + 4), + GNUNET_PQ_make_prepare ( + "insert_into_table_history_requests", + "INSERT INTO history_requests" + "(history_request_serial_id" + ",reserve_pub" + ",request_timestamp" + ",reserve_sig" + ",history_fee_val" + ",history_fee_frac" + ") VALUES " + "($1, $2, $3, $4, $5, $6);", + 6), + GNUNET_PQ_make_prepare ( + "insert_into_table_close_requests", + "INSERT INTO close_requests" + "(close_request_serial_id" + ",reserve_pub" + ",close_timestamp" + ",reserve_sig" + ",close_val" + ",close_frac" + ") VALUES " + "($1, $2, $3, $4, $5, $6);", + 6), + GNUNET_PQ_make_prepare ( + "insert_into_table_wads_out", + "INSERT INTO wads_out" + "(wad_out_serial_id" + ",wad_id" + ",partner_serial_id" + ",amount_val" + ",amount_frac" + ",execution_time" + ") VALUES " + "($1, $2, $3, $4, $5, $6);", + 6), + GNUNET_PQ_make_prepare ( + "insert_into_table_wad_out_entries", + "INSERT INTO wad_out_entries" + "(wad_out_entry_serial_id" + ",wad_out_serial_id" + ",reserve_pub" + ",purse_pub" + ",h_contract" + ",purse_expiration" + ",merge_timestamp" + ",amount_with_fee_val" + ",amount_with_fee_frac" + ",wad_fee_val" + ",wad_fee_frac" + ",deposit_fees_val" + ",deposit_fees_frac" + ",reserve_sig" + ",purse_sig" + ") VALUES " + "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15);", + 15), + GNUNET_PQ_make_prepare ( + "insert_into_table_wads_in", + "INSERT INTO wads_in" + "(wad_in_serial_id" + ",wad_id" + ",origin_exchange_url" + ",amount_val" + ",amount_frac" + ",arrival_time" + ") VALUES " + "($1, $2, $3, $4, $5, $6);", + 6), + GNUNET_PQ_make_prepare ( + "insert_into_table_wad_in_entries", + "INSERT INTO wad_in_entries" + "(wad_in_entry_serial_id" + ",wad_in_serial_id" + ",reserve_pub" + ",purse_pub" + ",h_contract" + ",purse_expiration" + ",merge_timestamp" + ",amount_with_fee_val" + ",amount_with_fee_frac" + ",wad_fee_val" + ",wad_fee_frac" + ",deposit_fees_val" + ",deposit_fees_frac" + ",reserve_sig" + ",purse_sig" + ") VALUES " + "($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15);", + 3), + /* Used in #postgres_begin_shard() */ GNUNET_PQ_make_prepare ( "get_open_shard", -- cgit v1.2.3