diff options
Diffstat (limited to 'src/auditordb/auditor-0001.sql')
| -rw-r--r-- | src/auditordb/auditor-0001.sql | 132 |
1 files changed, 51 insertions, 81 deletions
diff --git a/src/auditordb/auditor-0001.sql b/src/auditordb/auditor-0001.sql index 01214d53e..f397f0569 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--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 @@ -20,12 +20,19 @@ BEGIN; -- Check patch versioning is in place. SELECT _v.register_patch('auditor-0001', NULL, NULL); - CREATE SCHEMA auditor; COMMENT ON SCHEMA auditor IS 'taler-auditor data'; SET search_path TO auditor; +CREATE TYPE taler_amount + AS + (val INT8 + ,frac INT4 + ); +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) @@ -141,20 +148,13 @@ CREATE TABLE IF NOT EXISTS wire_auditor_progress 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_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 + ,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 @@ -170,10 +170,8 @@ CREATE INDEX IF NOT EXISTS auditor_reserves_by_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 DEFAULT(0) - ,balance_frac INT4 NOT NULL DEFAULT(0) - ,target_val INT8 NOT NULL - ,target_frac INT4 NOT NULL + ,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 ); @@ -187,8 +185,7 @@ CREATE INDEX IF NOT EXISTS auditor_purses_by_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_val INT8 NOT NULL - ,balance_frac INT4 NOT NULL + ,balance taler_amount NOT NULL ,open_purses INT8 NOT NULL ); COMMENT ON TABLE auditor_purse_summary @@ -196,20 +193,13 @@ COMMENT ON TABLE auditor_purse_summary 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 + ,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)'; @@ -217,8 +207,7 @@ COMMENT ON TABLE auditor_reserve_balance 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_val INT8 NOT NULL - ,wire_fee_balance_frac INT4 NOT NULL + ,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)'; @@ -226,52 +215,39 @@ COMMENT ON TABLE auditor_wire_fee_balance CREATE TABLE IF NOT EXISTS auditor_denomination_pending (denom_pub_hash BYTEA PRIMARY KEY CHECK (LENGTH(denom_pub_hash)=64) - ,denom_balance_val INT8 NOT NULL - ,denom_balance_frac INT4 NOT NULL - ,denom_loss_val INT8 NOT NULL - ,denom_loss_frac INT4 NOT NULL + ,denom_balance taler_amount NOT NULL + ,denom_loss taler_amount NOT NULL ,num_issued INT8 NOT NULL - ,denom_risk_val INT8 NOT NULL - ,denom_risk_frac INT4 NOT NULL - ,recoup_loss_val INT8 NOT NULL - ,recoup_loss_frac INT4 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_val +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_val +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_val +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_val INT8 NOT NULL - ,denom_balance_frac INT4 NOT NULL - ,deposit_fee_balance_val INT8 NOT NULL - ,deposit_fee_balance_frac INT4 NOT NULL - ,melt_fee_balance_val INT8 NOT NULL - ,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_loss_val INT8 NOT NULL - ,irregular_loss_frac INT4 NOT NULL + ,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_frac +COMMENT ON COLUMN auditor_balance_summary.denom_balance IS 'total amount we should have in escrow for all denominations'; @@ -279,14 +255,12 @@ 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_val INT8 NOT NULL - ,revenue_balance_frac INT4 NOT NULL - ,loss_balance_val INT8 NOT NULL - ,loss_balance_frac INT4 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_val +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'; @@ -294,8 +268,7 @@ 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_val INT8 NOT NULL - ,reserve_profits_frac INT4 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).'; @@ -315,8 +288,7 @@ CREATE TABLE IF NOT EXISTS deposit_confirmations ,exchange_timestamp INT8 NOT NULL ,refund_deadline INT8 NOT NULL ,wire_deadline INT8 NOT NULL - ,amount_without_fee_val INT8 NOT NULL - ,amount_without_fee_frac INT4 NOT NULL + ,amount_without_fee taler_amount NOT NULL ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32) ,exchange_sig BYTEA NOT NULL CHECK (LENGTH(exchange_sig)=64) @@ -330,10 +302,8 @@ COMMENT ON TABLE deposit_confirmations 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_val INT8 NOT NULL - ,balance_frac INT4 NOT NULL - ,drained_val INT8 NOT NULL - ,drained_frac INT4 NOT NULL + ,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).'; |