diff options
Diffstat (limited to 'src/auditordb/auditor-0001.sql')
| -rw-r--r-- | src/auditordb/auditor-0001.sql | 78 |
1 files changed, 67 insertions, 11 deletions
diff --git a/src/auditordb/auditor-0001.sql b/src/auditordb/auditor-0001.sql index 483f4f1e3..7bd5531e8 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--2020 Taler Systems SA +-- Copyright (C) 2014--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 @@ -52,16 +52,29 @@ CREATE TABLE IF NOT EXISTS auditor_progress_reserve ,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_merges_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 ,last_account_merges_serial_id INT8 NOT NULL DEFAULT 0 ,last_history_requests_serial_id INT8 NOT NULL DEFAULT 0 - ,last_close_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 auditor has processed in the exchange database. Used for SELECTing the + 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).'; @@ -93,8 +106,9 @@ CREATE TABLE IF NOT EXISTS auditor_progress_coin ,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_refunds_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 @@ -129,10 +143,20 @@ CREATE TABLE IF NOT EXISTS auditor_reserves ,master_pub BYTEA NOT NULL CONSTRAINT master_pub_ref REFERENCES auditor_exchanges(master_pub) ON DELETE CASCADE ,reserve_balance_val INT8 NOT NULL ,reserve_balance_frac INT4 NOT NULL + ,reserve_loss_val INT8 NOT NULL + ,reserve_loss_frac INT4 NOT NULL ,withdraw_fee_balance_val INT8 NOT NULL ,withdraw_fee_balance_frac INT4 NOT NULL + ,close_fee_balance_val INT8 NOT NULL + ,close_fee_balance_frac INT4 NOT NULL + ,purse_fee_balance_val INT8 NOT NULL + ,purse_fee_balance_frac INT4 NOT NULL + ,open_fee_balance_val INT8 NOT NULL + ,open_fee_balance_frac INT4 NOT NULL + ,history_fee_balance_val INT8 NOT NULL + ,history_fee_balance_frac INT4 NOT NULL ,expiration_date INT8 NOT NULL - ,auditor_reserves_rowid BIGSERIAL UNIQUE + ,auditor_reserves_rowid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE ,origin_account TEXT ); COMMENT ON TABLE auditor_reserves @@ -143,14 +167,38 @@ CREATE INDEX IF NOT EXISTS auditor_reserves_by_reserve_pub (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_val INT8 NOT NULL + ,balance_frac INT4 NOT NULL + ,target_val INT8 NOT NULL + ,target_frac INT4 NOT NULL + ,expiration_date INT8 NOT NULL + ,auditor_purses_rowid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE + ); +COMMENT ON TABLE auditor_purses + IS 'all of the purses and their respective balances that the auditor is aware of'; + +CREATE INDEX IF NOT EXISTS auditor_purses_by_purse_pub + ON auditor_purses + (purse_pub); + + 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_val INT8 NOT NULL ,reserve_balance_frac INT4 NOT NULL + ,reserve_loss_val INT8 NOT NULL + ,reserve_loss_frac INT4 NOT NULL ,withdraw_fee_balance_val INT8 NOT NULL ,withdraw_fee_balance_frac INT4 NOT NULL + ,close_fee_balance_val INT8 NOT NULL + ,close_fee_balance_frac INT4 NOT NULL ,purse_fee_balance_val INT8 NOT NULL ,purse_fee_balance_frac INT4 NOT NULL + ,open_fee_balance_val INT8 NOT NULL + ,open_fee_balance_frac INT4 NOT NULL ,history_fee_balance_val INT8 NOT NULL ,history_fee_balance_frac INT4 NOT NULL ); @@ -185,8 +233,10 @@ 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_val IS 'amount that could theoretically be lost in the future due to recoup operations'; +COMMENT ON COLUMN auditor_denomination_pending.denom_loss_val + IS 'amount that was lost due to failures by the exchange'; COMMENT ON COLUMN auditor_denomination_pending.recoup_loss_val - IS 'amount actually lost due to recoup operations past revocation'; + IS 'amount actually lost due to recoup operations after a revocation'; CREATE TABLE IF NOT EXISTS auditor_balance_summary @@ -199,15 +249,21 @@ CREATE TABLE IF NOT EXISTS auditor_balance_summary ,melt_fee_balance_frac INT4 NOT NULL ,refund_fee_balance_val INT8 NOT NULL ,refund_fee_balance_frac INT4 NOT NULL + ,purse_fee_balance_val INT8 NOT NULL + ,purse_fee_balance_frac INT4 NOT NULL + ,open_deposit_fee_balance_val INT8 NOT NULL + ,open_deposit_fee_balance_frac INT4 NOT NULL ,risk_val INT8 NOT NULL ,risk_frac INT4 NOT NULL ,loss_val INT8 NOT NULL ,loss_frac INT4 NOT NULL - ,irregular_recoup_val INT8 NOT NULL - ,irregular_recoup_frac INT4 NOT NULL + ,irregular_loss_val INT8 NOT NULL + ,irregular_loss_frac INT4 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_frac + IS 'total amount we should have in escrow for all denominations'; CREATE TABLE IF NOT EXISTS auditor_historic_denomination_revenue @@ -243,7 +299,7 @@ CREATE INDEX IF NOT EXISTS auditor_historic_reserve_summary_by_master_pub_start_ 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 BIGSERIAL NOT NULL UNIQUE + ,serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) ,h_extensions BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) ,h_wire BYTEA NOT NULL CHECK (LENGTH(h_wire)=64) |