-first hack at SQL fixes

13 files changed, 127 insertions, 117 deletions

diff --git a/src/auditordb/0002-auditor_balance_summary.sql b/src/auditordb/0002-auditor_balance_summary.sql
index ffb8e4fae..ea8458574 100644
--- a/src/auditordb/0002-auditor_balance_summary.sql
+++ b/src/auditordb/0002-auditor_balance_summary.sql
@@ -14,7 +14,22 @@
 -- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
 --
 
-CREATE TABLE IF NOT EXISTS auditor_balance_summary
+
+-- NEW:
+CREATE TABLE IF NOT EXISTS auditor_balance_summary (
+    balance_key TEXT PRIMARY KEY
+   ,balance_value taler_amount
+  );
+COMMENT ON TABLE auditor_balance_summary
+  IS 'table storing various global balances of the auditor';
+COMMENT ON COLUMN auditor_balance_summary.balance_key
+ IS 'unique name for the balance value';
+COMMENT ON COLUMN auditor_balance_summary.balance_value
+ IS 'balance amount';
+
+
+-- old:
+CREATE TABLE IF NOT EXISTS auditor_balance_summary (
     ,denom_balance_val INT8 NOT NULL
     ,denom_balance_frac INT4 NOT NULL
     ,deposit_fee_balance_val INT8 NOT NULL
@@ -37,4 +52,4 @@ CREATE TABLE IF NOT EXISTS auditor_balance_summary
 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';
\ No newline at end of file
+ IS 'total amount we should have in escrow for all denominations';
diff --git a/src/auditordb/0002-auditor_denomination_pending.sql b/src/auditordb/0002-auditor_denomination_pending.sql
index e03febfec..ea47dc0da 100644
--- a/src/auditordb/0002-auditor_denomination_pending.sql
+++ b/src/auditordb/0002-auditor_denomination_pending.sql
@@ -15,24 +15,20 @@
 --
 
 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
-    ,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_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_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
-  IS 'amount actually lost due to recoup operations after a revocation';
\ No newline at end of file
+COMMENT ON COLUMN auditor_denomination_pending.recoup_loss
+  IS 'amount actually lost due to recoup operations after a revocation';
diff --git a/src/auditordb/0002-auditor_exchange_signkeys.sql b/src/auditordb/0002-auditor_exchange_signkeys.sql
index dbd15cf71..cf5b01c8a 100644
--- a/src/auditordb/0002-auditor_exchange_signkeys.sql
+++ b/src/auditordb/0002-auditor_exchange_signkeys.sql
@@ -15,11 +15,21 @@
 --
 
 CREATE TABLE IF NOT EXISTS auditor_exchange_signkeys
-    ,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)
-    );
+  (exchange_pub BYTEA PRIMARY KEY CHECK (LENGTH(exchange_pub)=32)
+  ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
+  ,ep_valid_from INT8 NOT NULL
+  ,ep_expire_sign INT8 NOT NULL
+  ,ep_expire_legal INT8 NOT NULL
+  );
 COMMENT ON TABLE auditor_exchange_signkeys
-  IS 'list of the online signing keys of exchanges we are auditing';
\ No newline at end of file
+  IS 'list of the online signing keys of exchanges we are auditing';
+COMMENT ON COLUMN auditor_exchange_signkeys.exchange_pub
+  IS 'Public online signing key of the exchange.';
+COMMENT ON COLUMN auditor_exchange_signkeys.master_sig
+  IS 'Signature affirming the validity of the signing key of purpose TALER_SIGNATURE_MASTER_SIGNING_KEY_VALIDITY.';
+COMMENT ON COLUMN auditor_exchange_signkeys.ep_valid_from
+  IS 'Time when this online signing key will first be used to sign messages.';
+COMMENT ON COLUMN auditor_exchange_signkeys.ep_expire_sign
+  IS 'Time when this online signing key will no longer be used to sign.';
+COMMENT ON COLUMN auditor_exchange_signkeys.ep_expire_legal
+  IS 'Time when this online signing key legally expires.';
diff --git a/src/auditordb/0002-auditor_historic_denomination_revenue.sql b/src/auditordb/0002-auditor_historic_denomination_revenue.sql
index ac8b9c9fc..98146aae7 100644
--- a/src/auditordb/0002-auditor_historic_denomination_revenue.sql
+++ b/src/auditordb/0002-auditor_historic_denomination_revenue.sql
@@ -15,14 +15,18 @@
 --
 
 CREATE TABLE IF NOT EXISTS auditor_historic_denomination_revenue
-    ,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
-    );
+  (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_val
-  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';
+COMMENT ON COLUMN auditor_historic_denomination_revenue.denom_pub_hash
+  IS 'hash of the denomination public key that created this revenue';
+COMMENT ON COLUMN auditor_historic_denomination_revenue.revenue_timestamp
+  IS 'when was this revenue realized (by the denomination public key expiring)';
+COMMENT ON COLUMN auditor_historic_denomination_revenue.revenue_balance
+  IS 'the sum of all of the profits we made on the denomination except for withdraw fees (which are in historic_reserve_revenue); so this includes the deposit, melt and refund fees';
+COMMENT ON COLUMN auditor_historic_denomination_revenue.loss_balance
+  IS 'the sum of all of the losses we made on the denomination (for example, because the signing key was compromised and thus we redeemed coins we never issued); of course should be zero in practice in most cases';
diff --git a/src/auditordb/0002-auditor_historic_reserve_summary.sql b/src/auditordb/0002-auditor_historic_reserve_summary.sql
index 78bffd325..e71ae8b7a 100644
--- a/src/auditordb/0002-auditor_historic_reserve_summary.sql
+++ b/src/auditordb/0002-auditor_historic_reserve_summary.sql
@@ -15,14 +15,15 @@
 --
 
 CREATE TABLE IF NOT EXISTS auditor_historic_reserve_summary
-    ,start_date INT8 NOT NULL
-    ,end_date INT8 NOT NULL
-    ,reserve_profits_val INT8 NOT NULL
-    ,reserve_profits_frac INT4 NOT NULL
-    );
+  (start_date INT8 PRIMARY KEY
+  ,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
-    (start_date);
\ No newline at end of file
+COMMENT ON COLUMN auditor_historic_reserve_summary.start_date
+  IS 'start date of the time interval over which we made these profits from reserves';
+COMMENT ON COLUMN auditor_historic_reserve_summary.end_date
+  IS 'end date (exclusive) of the time interval over which we made these profits from reserves';
+COMMENT ON COLUMN auditor_historic_reserve_summary.reserve_profits
+  IS 'total amount in profits made';
diff --git a/src/auditordb/0002-auditor_predicted_result.sql b/src/auditordb/0002-auditor_predicted_result.sql
index cc39afb1c..5792313c6 100644
--- a/src/auditordb/0002-auditor_predicted_result.sql
+++ b/src/auditordb/0002-auditor_predicted_result.sql
@@ -14,11 +14,11 @@
 -- TALER; see the file COPYING.  If not, see <http://www.gnu.org/licenses/>
 --
 
+
+-- FIXME: should be folded into the new general balances table!
 CREATE TABLE IF NOT EXISTS auditor_predicted_result
-    ,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).';
diff --git a/src/auditordb/0002-auditor_progress_reserve.sql b/src/auditordb/0002-auditor_progress_reserve.sql
index 5b392f42e..e1b9b4817 100644
--- a/src/auditordb/0002-auditor_progress_reserve.sql
+++ b/src/auditordb/0002-auditor_progress_reserve.sql
@@ -15,7 +15,7 @@
 --
 
 CREATE TABLE IF NOT EXISTS auditor_progress_reserve
-    ,reserve_name TEXT NOT NULL
+    ,reserve_name TEXT NOT NULL -- FIXME: I think you missunderstood how the progress points should work in the future...
     ,serial_id INT8 NOT NULL
    --  ,PRIMARY KEY (master_pub)
     );
diff --git a/src/auditordb/0002-auditor_purse_summary.sql b/src/auditordb/0002-auditor_purse_summary.sql
index 7a3a1bda7..7c21271c1 100644
--- a/src/auditordb/0002-auditor_purse_summary.sql
+++ b/src/auditordb/0002-auditor_purse_summary.sql
@@ -17,7 +17,7 @@
 CREATE TABLE IF NOT EXISTS auditor_purse_summary
     ,balance_val INT8 NOT NULL
     ,balance_frac INT4 NOT NULL
-    ,open_purses INT8 NOT NULL
+    ,open_purses INT8 NOT NULL -- FIXME: do we need this column?
     );
 COMMENT ON TABLE auditor_purse_summary
-  IS 'sum of the balances in open purses';
\ No newline at end of file
+  IS 'sum of the balances in open purses';
diff --git a/src/auditordb/0002-auditor_purses.sql b/src/auditordb/0002-auditor_purses.sql
index 9dd1286f3..9ba5955eb 100644
--- a/src/auditordb/0002-auditor_purses.sql
+++ b/src/auditordb/0002-auditor_purses.sql
@@ -15,17 +15,11 @@
 --
 
 CREATE TABLE IF NOT EXISTS auditor_purses
-(purse_pub BYTEA NOT NULL CHECK(LENGTH(purse_pub)=32)
-    ,balance_val INT8 NOT NULL DEFAULT(0)
-    ,balance_frac INT4 NOT NULL DEFAULT(0)
-    ,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
+  (auditor_purses_rowid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+  ,purse_pub BYTEA PRIMARY KEY CHECK(LENGTH(purse_pub)=32)
+  ,balance taler_amount NOT NULL DEFAULT(0)
+  ,target taler_amount NOT NULL
+  ,expiration_date INT8 NOT NULL
+  );
+COMMENT ON TABLE 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);
\ No newline at end of file
diff --git a/src/auditordb/0002-auditor_reserve_balance.sql b/src/auditordb/0002-auditor_reserve_balance.sql
index 8267a3a28..46c18ec9d 100644
--- a/src/auditordb/0002-auditor_reserve_balance.sql
+++ b/src/auditordb/0002-auditor_reserve_balance.sql
@@ -15,20 +15,13 @@
 --
 
 CREATE TABLE IF NOT EXISTS auditor_reserve_balance
-    ,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';
\ No newline at end of file
+  IS 'sum of the balances of all customer reserves';
diff --git a/src/auditordb/0002-auditor_reserves.sql b/src/auditordb/0002-auditor_reserves.sql
index 5da886dce..834d245ef 100644
--- a/src/auditordb/0002-auditor_reserves.sql
+++ b/src/auditordb/0002-auditor_reserves.sql
@@ -15,28 +15,17 @@
 --
 
 CREATE TABLE IF NOT EXISTS auditor_reserves
-(reserve_pub BYTEA NOT NULL CHECK(LENGTH(reserve_pub)=32)
-    ,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 BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
-    ,origin_account TEXT
-    );
+  (auditor_reserves_rowid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE
+  ,reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)
+  ,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
+  ,origin_account TEXT
+  );
 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);
\ No newline at end of file
diff --git a/src/auditordb/0002-deposit_confirmations.sql b/src/auditordb/0002-deposit_confirmations.sql
index 340799cd5..1d63bdb6e 100644
--- a/src/auditordb/0002-deposit_confirmations.sql
+++ b/src/auditordb/0002-deposit_confirmations.sql
@@ -15,21 +15,21 @@
 --
 
 CREATE TABLE IF NOT EXISTS deposit_confirmations
-    ,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
-    ,amount_without_fee_val INT8 NOT NULL
-    ,amount_without_fee_frac INT4 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)
-    ,exchange_pub BYTEA NOT NULL CHECK (LENGTH(exchange_pub)=32)
-    ,master_sig BYTEA NOT NULL CHECK (LENGTH(master_sig)=64)
-    ,PRIMARY KEY (h_contract_terms,h_wire,coin_pub,merchant_pub,exchange_sig,exchange_pub,master_sig)
-    );
+  (deposit_confirmation_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)
+  ,PRIMARY KEY (h_contract_terms,h_wire,coin_pub,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.';
diff --git a/src/auditordb/auditor-0002.sql.in b/src/auditordb/auditor-0002.sql.in
index d662bbad8..891b61b4e 100644
--- a/src/auditordb/auditor-0002.sql.in
+++ b/src/auditordb/auditor-0002.sql.in
@@ -19,6 +19,14 @@ BEGIN;
 SELECT _v.register_patch('auditor-0002', NULL, NULL);
 SET search_path TO auditordb;
 
+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';
+
 #include "0002-auditor_denomination_pending.sql"
 #include "0002-auditor_wire_fee_balance.sql"
 #include "0002-auditor_balance_summary.sql"
@@ -39,4 +47,4 @@ SET search_path TO auditordb;
 #include "0002-wire_auditor_account_progress.sql"
 #include "0002-wire_auditor_progress.sql"
 
-COMMIT;
\ No newline at end of file
+COMMIT;