migrating auditordb to use amount tuples

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).';