diff options
Diffstat (limited to 'src/auditordb/auditor-0001.sql')
| -rw-r--r-- | src/auditordb/auditor-0001.sql | 581 |
1 files changed, 266 insertions, 315 deletions
diff --git a/src/auditordb/auditor-0001.sql b/src/auditordb/auditor-0001.sql index 779f61aaa..0b7823cec 100644 --- a/src/auditordb/auditor-0001.sql +++ b/src/auditordb/auditor-0001.sql @@ -1,6 +1,6 @@ -- -- This file is part of TALER --- Copyright (C) 2014--2023 Taler Systems SA +-- Copyright (C) 2014--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 @@ -14,10 +14,8 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- --- Everything in one big transaction BEGIN; --- Check patch versioning is in place. SELECT _v.register_patch('auditor-0001', NULL, NULL); CREATE SCHEMA auditor; @@ -25,321 +23,274 @@ COMMENT ON SCHEMA auditor IS 'taler-auditor data'; SET search_path TO auditor; -CREATE TYPE taler_amount - AS - (val INT8 - ,frac INT4 +--------------------------------------------------------------------------- +-- General procedures for DB setup +--------------------------------------------------------------------------- + +CREATE TABLE auditor_tables + (table_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY + ,name TEXT NOT NULL + ,version TEXT NOT NULL + ,action TEXT NOT NULL + ,partitioned BOOL NOT NULL + ,by_range BOOL NOT NULL + ,finished BOOL NOT NULL DEFAULT(FALSE)); +COMMENT ON TABLE auditor_tables + IS 'Tables of the auditor and their status'; +COMMENT ON COLUMN auditor_tables.name + IS 'Base name of the table (without partition/shard)'; +COMMENT ON COLUMN auditor_tables.version + IS 'Version of the DB in which the given action happened'; +COMMENT ON COLUMN auditor_tables.action + IS 'Action to take on the table (e.g. create, constrain, or foreign). Create is done for the master table and each partition; constrain is only for partitions or for master if there are no partitions; master only on master (takes no argument); foreign only on master if there are no partitions.'; +COMMENT ON COLUMN auditor_tables.partitioned + IS 'TRUE if the table is partitioned'; +COMMENT ON COLUMN auditor_tables.by_range + IS 'TRUE if the table is partitioned by range'; +COMMENT ON COLUMN auditor_tables.finished + IS 'TRUE if the respective migration has been run'; + + +CREATE FUNCTION create_partitioned_table( + IN table_definition TEXT -- SQL template for table creation + ,IN table_name TEXT -- base name of the table + ,IN main_table_partition_str TEXT -- declaration for how to partition the table + ,IN partition_suffix TEXT DEFAULT NULL -- NULL: no partitioning, 0: yes partitioning, no sharding, >0: sharding +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + IF (partition_suffix IS NULL) + THEN + -- no partitioning, disable option + main_table_partition_str = ''; + ELSE + IF (partition_suffix::int > 0) + THEN + -- sharding, add shard name + table_name=table_name || '_' || partition_suffix; + END IF; + END IF; + EXECUTE FORMAT( + table_definition, + table_name, + main_table_partition_str ); -COMMENT ON TYPE taler_amount - IS 'Stores an amount, fraction is in units of 1/100000000 of the base value'; - - -CREATE TABLE IF NOT EXISTS auditor_exchanges - (master_pub BYTEA PRIMARY KEY CHECK (LENGTH(master_pub)=32) - ,exchange_url TEXT NOT NULL - ); -COMMENT ON TABLE auditor_exchanges - IS 'list of the exchanges we are auditing'; - - -CREATE TABLE IF NOT EXISTS auditor_exchange_signkeys - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,ep_start INT8 NOT NULL - ,ep_expire INT8 NOT NULL - ,ep_end INT8 NOT NULL - ,exchange_pub BYTEA NOT NULL CHECK (LENGTH(exchange_pub)=32) - ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) - ); -COMMENT ON TABLE auditor_exchange_signkeys - IS 'list of the online signing keys of exchanges we are auditing'; - - -CREATE TABLE IF NOT EXISTS auditor_progress_reserve - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,last_reserve_in_serial_id INT8 NOT NULL DEFAULT 0 - ,last_reserve_out_serial_id INT8 NOT NULL DEFAULT 0 - ,last_reserve_recoup_serial_id INT8 NOT NULL DEFAULT 0 - ,last_reserve_open_serial_id INT8 NOT NULL DEFAULT 0 - ,last_reserve_close_serial_id INT8 NOT NULL DEFAULT 0 - ,last_purse_decision_serial_id INT8 NOT NULL DEFAULT 0 - ,last_account_merges_serial_id INT8 NOT NULL DEFAULT 0 - ,last_history_requests_serial_id INT8 NOT NULL DEFAULT 0 - ,PRIMARY KEY (master_pub) - ); -COMMENT ON TABLE auditor_progress_reserve - IS 'information as to which transactions the reserve auditor has processed in the exchange database. Used for SELECTing the - statements to process. The indices include the last serial ID from the respective tables that we have processed. Thus, we need to select those table entries that are strictly larger (and process in monotonically increasing order).'; - - -CREATE TABLE IF NOT EXISTS auditor_progress_purse - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,last_purse_request_serial_id INT8 NOT NULL DEFAULT 0 - ,last_purse_decision_serial_id INT8 NOT NULL DEFAULT 0 - ,last_purse_merges_serial_id INT8 NOT NULL DEFAULT 0 - ,last_account_merges_serial_id INT8 NOT NULL DEFAULT 0 - ,last_purse_deposits_serial_id INT8 NOT NULL DEFAULT 0 - ,PRIMARY KEY (master_pub) - ); -COMMENT ON TABLE auditor_progress_purse - IS 'information as to which purses the purse auditor has processed in the exchange database. Used for SELECTing the - statements to process. The indices include the last serial ID from the respective tables that we have processed. Thus, we need to select those table entries that are strictly larger (and process in monotonically increasing order).'; - - -CREATE TABLE IF NOT EXISTS auditor_progress_aggregation - (master_pub BYTEA CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,last_wire_out_serial_id INT8 NOT NULL DEFAULT 0 - ,PRIMARY KEY (master_pub) - ); -COMMENT ON TABLE auditor_progress_aggregation - IS 'information as to which transactions the auditor has processed in the exchange database. Used for SELECTing the - statements to process. The indices include the last serial ID from the respective tables that we have processed. Thus, we need to select those table entries that are strictly larger (and process in monotonically increasing order).'; - - -CREATE TABLE IF NOT EXISTS auditor_progress_deposit_confirmation - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,last_deposit_confirmation_serial_id INT8 NOT NULL DEFAULT 0 - ,PRIMARY KEY (master_pub) - ); -COMMENT ON TABLE auditor_progress_deposit_confirmation - IS 'information as to which transactions the auditor has processed in the exchange database. Used for SELECTing the - statements to process. The indices include the last serial ID from the respective tables that we have processed. Thus, we need to select those table entries that are strictly larger (and process in monotonically increasing order).'; - - -CREATE TABLE IF NOT EXISTS auditor_progress_coin - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,last_withdraw_serial_id INT8 NOT NULL DEFAULT 0 - ,last_deposit_serial_id INT8 NOT NULL DEFAULT 0 - ,last_melt_serial_id INT8 NOT NULL DEFAULT 0 - ,last_refund_serial_id INT8 NOT NULL DEFAULT 0 - ,last_recoup_serial_id INT8 NOT NULL DEFAULT 0 - ,last_recoup_refresh_serial_id INT8 NOT NULL DEFAULT 0 - ,last_open_deposits_serial_id INT8 NOT NULL DEFAULT 0 - ,last_purse_deposits_serial_id INT8 NOT NULL DEFAULT 0 - ,last_purse_decision_serial_id INT8 NOT NULL DEFAULT 0 - ,PRIMARY KEY (master_pub) - ); -COMMENT ON TABLE auditor_progress_coin - IS 'information as to which transactions the auditor has processed in the exchange database. Used for SELECTing the - statements to process. The indices include the last serial ID from the respective tables that we have processed. Thus, we need to select those table entries that are strictly larger (and process in monotonically increasing order).'; - - -CREATE TABLE IF NOT EXISTS wire_auditor_account_progress - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,account_name TEXT NOT NULL - ,last_wire_reserve_in_serial_id INT8 NOT NULL DEFAULT 0 - ,last_wire_wire_out_serial_id INT8 NOT NULL DEFAULT 0 - ,wire_in_off INT8 NOT NULL - ,wire_out_off INT8 NOT NULL - ,PRIMARY KEY (master_pub,account_name) - ); -COMMENT ON TABLE wire_auditor_account_progress - IS 'information as to which transactions the auditor has processed in the exchange database. Used for SELECTing the - statements to process. The indices include the last serial ID from the respective tables that we have processed. Thus, we need to select those table entries that are strictly larger (and process in monotonically increasing order).'; - - -CREATE TABLE IF NOT EXISTS wire_auditor_progress - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,last_reserve_close_uuid INT8 NOT NULL - ,last_batch_deposit_uuid INT8 NOT NULL - ,last_aggregation_serial INT8 NOT NULL - ,PRIMARY KEY (master_pub) - ); - - -CREATE TABLE IF NOT EXISTS auditor_reserves - (reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32) - ,master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,reserve_balance taler_amount NOT NULL - ,reserve_loss taler_amount NOT NULL - ,withdraw_fee_balance taler_amount NOT NULL - ,close_fee_balance taler_amount NOT NULL - ,purse_fee_balance taler_amount NOT NULL - ,open_fee_balance taler_amount NOT NULL - ,history_fee_balance taler_amount NOT NULL - ,expiration_date INT8 NOT NULL - ,auditor_reserves_rowid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE - ,origin_account TEXT +END $$; + +COMMENT ON FUNCTION create_partitioned_table + IS 'Generic function to create a table that is partitioned or sharded.'; + + +CREATE FUNCTION comment_partitioned_table( + IN table_comment TEXT + ,IN table_name TEXT + ,IN partition_suffix TEXT DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + IF ( (partition_suffix IS NOT NULL) AND + (partition_suffix::int > 0) ) + THEN + -- sharding, add shard name + table_name=table_name || '_' || partition_suffix; + END IF; + EXECUTE FORMAT( + 'COMMENT ON TABLE %s IS %s' + ,table_name + ,quote_literal(table_comment) ); -COMMENT ON TABLE auditor_reserves - IS 'all of the customer reserves and their respective balances that the auditor is aware of'; - -CREATE INDEX IF NOT EXISTS auditor_reserves_by_reserve_pub - ON auditor_reserves - (reserve_pub); - - -CREATE TABLE IF NOT EXISTS auditor_purses - (purse_pub BYTEA NOT NULL CHECK(LENGTH(purse_pub)=32) - ,master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,balance taler_amount NOT NULL DEFAULT(0,0) - ,target taler_amount NOT NULL - ,expiration_date INT8 NOT NULL - ,auditor_purses_rowid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE +END $$; + +COMMENT ON FUNCTION comment_partitioned_table + IS 'Generic function to create a comment on table that is partitioned.'; + + +CREATE FUNCTION comment_partitioned_column( + IN table_comment TEXT + ,IN column_name TEXT + ,IN table_name TEXT + ,IN partition_suffix TEXT DEFAULT NULL +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + IF ( (partition_suffix IS NOT NULL) AND + (partition_suffix::int > 0) ) + THEN + -- sharding, add shard name + table_name=table_name || '_' || partition_suffix; + END IF; + EXECUTE FORMAT( + 'COMMENT ON COLUMN %s.%s IS %s' + ,table_name + ,column_name + ,quote_literal(table_comment) ); -COMMENT ON TABLE auditor_purses - IS 'all of the purses and their respective balances that the auditor is aware of'; +END $$; + +COMMENT ON FUNCTION comment_partitioned_column + IS 'Generic function to create a comment on column of a table that is partitioned.'; + + +--------------------------------------------------------------------------- +-- Main DB setup loop +--------------------------------------------------------------------------- + +CREATE FUNCTION do_create_tables( + num_partitions INTEGER +-- NULL: no partitions, add foreign constraints +-- 0: no partitions, no foreign constraints +-- 1: only 1 default partition +-- > 1: normal partitions +) + RETURNS VOID + LANGUAGE plpgsql +AS $$ +DECLARE + tc CURSOR FOR + SELECT table_serial_id + ,name + ,action + ,partitioned + ,by_range + FROM auditor.auditor_tables + WHERE NOT finished + ORDER BY table_serial_id ASC; +BEGIN + FOR rec IN tc + LOOP + CASE rec.action + -- "create" actions apply to master and partitions + WHEN 'create' + THEN + IF (rec.partitioned AND + (num_partitions IS NOT NULL)) + THEN + -- Create master table with partitioning. + EXECUTE FORMAT( + 'SELECT auditor.%s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,quote_literal('0') + ); + IF (rec.by_range OR + (num_partitions = 0)) + THEN + -- Create default partition. + IF (rec.by_range) + THEN + -- Range partition + EXECUTE FORMAT( + 'CREATE TABLE auditor.%s_default' + ' PARTITION OF %s' + ' DEFAULT' + ,rec.name + ,rec.name + ); + ELSE + -- Hash partition + EXECUTE FORMAT( + 'CREATE TABLE auditor.%s_default' + ' PARTITION OF %s' + ' FOR VALUES WITH (MODULUS 1, REMAINDER 0)' + ,rec.name + ,rec.name + ); + END IF; + ELSE + FOR i IN 1..num_partitions LOOP + -- Create num_partitions + EXECUTE FORMAT( + 'CREATE TABLE auditor.%I' + ' PARTITION OF %I' + ' FOR VALUES WITH (MODULUS %s, REMAINDER %s)' + ,rec.name || '_' || i + ,rec.name + ,num_partitions + ,i-1 + ); + END LOOP; + END IF; + ELSE + -- Only create master table. No partitions. + EXECUTE FORMAT( + 'SELECT auditor.%s_table_%s ()'::text + ,rec.action + ,rec.name + ); + END IF; + -- Constrain action apply to master OR each partition + WHEN 'constrain' + THEN + ASSERT rec.partitioned, 'constrain action only applies to partitioned tables'; + IF (num_partitions IS NULL) + THEN + -- Constrain master table + EXECUTE FORMAT( + 'SELECT auditor.%s_table_%s (NULL)'::text + ,rec.action + ,rec.name + ); + ELSE + IF ( (num_partitions = 0) OR + (rec.by_range) ) + THEN + -- Constrain default table + EXECUTE FORMAT( + 'SELECT auditor.%s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,quote_literal('default') + ); + ELSE + -- Constrain each partition + FOR i IN 1..num_partitions LOOP + EXECUTE FORMAT( + 'SELECT auditor.%s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,quote_literal(i) + ); + END LOOP; + END IF; + END IF; + -- Foreign actions only apply if partitioning is off + WHEN 'foreign' + THEN + IF (num_partitions IS NULL) + THEN + -- Add foreign constraints + EXECUTE FORMAT( + 'SELECT auditor.%s_table_%s (%s)'::text + ,rec.action + ,rec.name + ,NULL + ); + END IF; + WHEN 'master' + THEN + EXECUTE FORMAT( + 'SELECT auditor.%s_table_%s ()'::text + ,rec.action + ,rec.name + ); + ELSE + ASSERT FALSE, 'unsupported action type: ' || rec.action; + END CASE; -- END CASE (rec.action) + -- Mark as finished + UPDATE auditor.auditor_tables + SET finished=TRUE + WHERE table_serial_id=rec.table_serial_id; + END LOOP; -- create/alter/drop actions +END $$; + +COMMENT ON FUNCTION do_create_tables + IS 'Creates all tables for the given number of partitions that need creating. Does NOT support sharding.'; -CREATE INDEX IF NOT EXISTS auditor_purses_by_purse_pub - ON auditor_purses - (purse_pub); - - -CREATE TABLE IF NOT EXISTS auditor_purse_summary - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,balance taler_amount NOT NULL - ,open_purses INT8 NOT NULL - ); -COMMENT ON TABLE auditor_purse_summary - IS 'sum of the balances in open purses'; - -CREATE TABLE IF NOT EXISTS auditor_reserve_balance - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,reserve_balance taler_amount NOT NULL - ,reserve_loss taler_amount NOT NULL - ,withdraw_fee_balance taler_amount NOT NULL - ,close_fee_balance taler_amount NOT NULL - ,purse_fee_balance taler_amount NOT NULL - ,open_fee_balance taler_amount NOT NULL - ,history_fee_balance taler_amount NOT NULL - ); -COMMENT ON TABLE auditor_reserve_balance - IS 'sum of the balances of all customer reserves (by exchange master public key)'; - - -CREATE TABLE IF NOT EXISTS auditor_wire_fee_balance - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,wire_fee_balance taler_amount NOT NULL - ); -COMMENT ON TABLE auditor_wire_fee_balance - IS 'sum of the balances of all wire fees (by exchange master public key)'; - - -CREATE TABLE IF NOT EXISTS auditor_denomination_pending - (denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64) - ,denom_balance taler_amount NOT NULL - ,denom_loss taler_amount NOT NULL - ,num_issued INT8 NOT NULL - ,denom_risk taler_amount NOT NULL - ,recoup_loss taler_amount NOT NULL - ); -COMMENT ON TABLE auditor_denomination_pending - IS 'outstanding denomination coins that the exchange is aware of and what the respective balances are (outstanding as well as issued overall which implies the maximum value at risk).'; -COMMENT ON COLUMN auditor_denomination_pending.num_issued - IS 'counts the number of coins issued (withdraw, refresh) of this denomination'; -COMMENT ON COLUMN auditor_denomination_pending.denom_risk - IS 'amount that could theoretically be lost in the future due to recoup operations'; -COMMENT ON COLUMN auditor_denomination_pending.denom_loss - IS 'amount that was lost due to failures by the exchange'; -COMMENT ON COLUMN auditor_denomination_pending.recoup_loss - IS 'amount actually lost due to recoup operations after a revocation'; - - -CREATE TABLE IF NOT EXISTS auditor_balance_summary - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,denom_balance taler_amount NOT NULL - ,deposit_fee_balance taler_amount NOT NULL - ,melt_fee_balance taler_amount NOT NULL - ,refund_fee_balance taler_amount NOT NULL - ,purse_fee_balance taler_amount NOT NULL - ,open_deposit_fee_balance taler_amount NOT NULL - ,risk taler_amount NOT NULL - ,loss taler_amount NOT NULL - ,irregular_loss taler_amount NOT NULL - ); -COMMENT ON TABLE auditor_balance_summary - IS 'the sum of the outstanding coins from auditor_denomination_pending (denom_pubs must belong to the respectives exchange master public key); it represents the auditor_balance_summary of the exchange at this point (modulo unexpected historic_loss-style events where denomination keys are compromised)'; -COMMENT ON COLUMN auditor_balance_summary.denom_balance - IS 'total amount we should have in escrow for all denominations'; - - -CREATE TABLE IF NOT EXISTS auditor_historic_denomination_revenue - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64) - ,revenue_timestamp INT8 NOT NULL - ,revenue_balance taler_amount NOT NULL - ,loss_balance taler_amount NOT NULL - ); -COMMENT ON TABLE auditor_historic_denomination_revenue - IS 'Table with historic profits; basically, when a denom_pub has expired and everything associated with it is garbage collected, the final profits end up in here; note that the denom_pub here is not a foreign key, we just keep it as a reference point.'; -COMMENT ON COLUMN auditor_historic_denomination_revenue.revenue_balance - IS 'the sum of all of the profits we made on the coin except for withdraw fees (which are in historic_reserve_revenue); so this includes the deposit, melt and refund fees'; - - -CREATE TABLE IF NOT EXISTS auditor_historic_reserve_summary - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,start_date INT8 NOT NULL - ,end_date INT8 NOT NULL - ,reserve_profits taler_amount NOT NULL - ); -COMMENT ON TABLE auditor_historic_reserve_summary - IS 'historic profits from reserves; we eventually GC auditor_historic_reserve_revenue, and then store the totals in here (by time intervals).'; - -CREATE INDEX IF NOT EXISTS auditor_historic_reserve_summary_by_master_pub_start_date - ON auditor_historic_reserve_summary - (master_pub - ,start_date); - - -CREATE TABLE IF NOT EXISTS deposit_confirmations - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE - ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) - ,h_policy BYTEA NOT NULL CHECK (LENGTH(h_policy)=64) - ,h_wire BYTEA NOT NULL CHECK (LENGTH(h_wire)=64) - ,exchange_timestamp INT8 NOT NULL - ,refund_deadline INT8 NOT NULL - ,wire_deadline INT8 NOT NULL - ,total_without_fee taler_amount NOT NULL - ,coin_pubs BYTEA[] NOT NULL CHECK (CARDINALITY(coin_pubs)>0) - ,coin_sigs BYTEA[] NOT NULL CHECK (CARDINALITY(coin_sigs)=CARDINALITY(coin_pubs)) - ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32) - ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) - ,exchange_pub BYTEA NOT NULL CHECK (LENGTH(exchange_pub)=32) - ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64) - ,suppressed BOOLEAN NOT NULL DEFAULT FALSE - ,ancient BOOLEAN NOT NULL DEFAULT FALSE - ,PRIMARY KEY (h_contract_terms,h_wire,merchant_pub,exchange_sig,exchange_pub,master_sig) - ); -COMMENT ON TABLE deposit_confirmations - IS 'deposit confirmation sent to us by merchants; we must check that the exchange reported these properly.'; - -CREATE INDEX IF NOT EXISTS auditor_deposit_confirmations_not_ancient - ON deposit_confirmations - (exchange_timestamp ASC) - WHERE NOT ancient; - - -CREATE TABLE IF NOT EXISTS auditor_predicted_result - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,balance taler_amount NOT NULL - ,drained taler_amount NOT NULL - ); -COMMENT ON TABLE auditor_predicted_result - IS 'Table with the sum of the ledger, auditor_historic_revenue and the auditor_reserve_balance and the drained profits. This is the final amount that the exchange should have in its bank account right now (and the total amount drained as profits to non-escrow accounts).'; - - -CREATE TABLE IF NOT EXISTS auditor_pending_deposits - (master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE - ,total_amount taler_amount NOT NULL - ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32) - ,batch_deposit_serial_id INT8 NOT NULL - ,deadline INT8 NOT NULL - ,PRIMARY KEY(master_pub, batch_deposit_serial_id) - ); -COMMENT ON TABLE auditor_pending_deposits - IS 'Table with the sum of the (batch) deposits we have seen but not yet checked that they have been aggregated and wired for a particular target bank account'; -COMMENT ON COLUMN auditor_pending_deposits.total_amount - IS 'Amount we expect to be wired in total for the batch. Includes deposit fees, not the actual expected net wire transfer amount.'; -COMMENT ON COLUMN auditor_pending_deposits.wire_target_h_payto - IS 'Hash of the payto URI of the bank account to be credited by the deadline'; -COMMENT ON COLUMN auditor_pending_deposits.batch_deposit_serial_id - IS 'Entry in the batch_deposits table of the exchange this entry is for'; -COMMENT ON COLUMN auditor_pending_deposits.deadline - IS 'Deadline by which funds should be wired (may be in the future)'; -CREATE INDEX IF NOT EXISTS auditor_pending_deposits_by_deadline - ON auditor_pending_deposits - (master_pub - ,deadline ASC); --- Finally, commit everything COMMIT; |