summaryrefslogtreecommitdiff
path: root/src/auditordb
diff options
context:
space:
mode:
authorChristian Grothoff <christian@grothoff.org>2016-10-06 16:30:19 +0200
committerChristian Grothoff <christian@grothoff.org>2016-10-06 16:30:19 +0200
commit03940806003d08994384dc7cfd6f412df1139b35 (patch)
treeb26d2d8817ef2eb0a7caf05f636ef660d1c0ede6 /src/auditordb
parentb68adb93c6e0bcb225e115cd62e23f1318ef259b (diff)
downloadexchange-03940806003d08994384dc7cfd6f412df1139b35.tar.gz
exchange-03940806003d08994384dc7cfd6f412df1139b35.tar.bz2
exchange-03940806003d08994384dc7cfd6f412df1139b35.zip
first draft for an auditor table structure
Diffstat (limited to 'src/auditordb')
-rw-r--r--src/auditordb/plugin_auditordb_postgres.c211
1 files changed, 209 insertions, 2 deletions
diff --git a/src/auditordb/plugin_auditordb_postgres.c b/src/auditordb/plugin_auditordb_postgres.c
index f4272339..290af9db 100644
--- a/src/auditordb/plugin_auditordb_postgres.c
+++ b/src/auditordb/plugin_auditordb_postgres.c
@@ -235,9 +235,216 @@ postgres_create_tables (void *cls)
return GNUNET_SYSERR;
#define SQLEXEC(sql) SQLEXEC_(conn, sql);
#define SQLEXEC_INDEX(sql) SQLEXEC_IGNORE_ERROR_(conn, sql);
- SQLEXEC ("CREATE TABLE IF NOT EXISTS test"
- "(test_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32"
+
+ /* Table with all of the denomination keys that the auditor
+ is aware of. */
+ SQLEXEC ("CREATE TABLE IF NOT EXISTS auditor_denominations"
+ "(denom_pub BYTEA PRIMARY KEY"
+ ",master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)"
+ ",valid_from INT8 NOT NULL"
+ ",expire_withdraw INT8 NOT NULL"
+ ",expire_deposit INT8 NOT NULL"
+ ",expire_legal INT8 NOT NULL"
+ ",coin_val INT8 NOT NULL" /* value of this denom */
+ ",coin_frac INT4 NOT NULL" /* fractional value of this denom */
+ ",coin_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL" /* assuming same currency for fees */
+ ",fee_withdraw_val INT8 NOT NULL"
+ ",fee_withdraw_frac INT4 NOT NULL"
+ ",fee_withdraw_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",fee_deposit_val INT8 NOT NULL"
+ ",fee_deposit_frac INT4 NOT NULL"
+ ",fee_deposit_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",fee_refresh_val INT8 NOT NULL"
+ ",fee_refresh_frac INT4 NOT NULL"
+ ",fee_refresh_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",fee_refund_val INT8 NOT NULL"
+ ",fee_refund_frac INT4 NOT NULL"
+ ",fee_refund_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
")");
+
+ /* Table with all of the customer reserves and their respective
+ balances that the auditor is aware of.
+ "last_reserve_out_serial_id" marks the last withdrawal from
+ "reserves_out" about this reserve that the auditor is aware of,
+ and "last_reserve_in_serial_id" is the last "reserve_in"
+ operation about this reserve that the auditor is aware of. */
+ SQLEXEC ("CREATE TABLE IF NOT EXISTS auditor_reserves"
+ "(reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)"
+ ",master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)"
+ ",reserve_balance_val INT8 NOT NULL"
+ ",reserve_balance_frac INT4 NOT NULL"
+ ",reserve_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",withdraw_fee_balance_val INT8 NOT NULL"
+ ",withdraw_fee_balance_frac INT4 NOT NULL"
+ ",withdraw_fee_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",expiration_date INT8 NOT NULL"
+ ",last_reserve_in_serial_id INT8 NOT NULL"
+ ",last_reserve_out_serial_id INT8 NOT NULL"
+ ")");
+
+ /* Table with the sum of the balances of all customer reserves
+ (by exchange's master public key) */
+ SQLEXEC ("CREATE TABLE IF NOT EXISTS auditor_reserve_balance"
+ "(master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)"
+ ",reserve_balance_val INT8 NOT NULL"
+ ",reserve_balance_frac INT4 NOT NULL"
+ ",reserve_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",withdraw_fee_balance_val INT8 NOT NULL"
+ ",withdraw_fee_balance_frac INT4 NOT NULL"
+ ",withdraw_fee_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ")");
+
+ /* Table with all of the outstanding denomination coins that the
+ exchange is aware of. "last_deposit_serial_id" marks the
+ deposit_serial_id from "deposits" about this denomination key
+ that the auditor is aware of; "last_melt_serial_id" marks the
+ last melt from "refresh_sessions" that the auditor is aware
+ of; "refund_serial_id" tells us the last entry in "refunds"
+ for this denom_pub that the auditor is aware of. */
+ SQLEXEC ("CREATE TABLE IF NOT EXISTS denomination_pending"
+ "(denom_pub BYTEA NOT NULL REFERENCES denominations (denom_pub) ON DELETE CASCADE"
+ ",denom_balance_val INT8 NOT NULL"
+ ",denom_balance_frac INT4 NOT NULL"
+ ",denom_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",deposit_fee_balance_val INT8 NOT NULL"
+ ",deposit_fee_balance_frac INT4 NOT NULL"
+ ",deposit_fee_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",melt_fee_balance_val INT8 NOT NULL"
+ ",melt_fee_balance_frac INT4 NOT NULL"
+ ",melt_fee_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",refund_fee_balance_val INT8 NOT NULL"
+ ",refund_fee_balance_frac INT4 NOT NULL"
+ ",refund_fee_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",last_deposit_serial_id INT8 NOT NULL"
+ ",last_melt_serial_id INT8 NOT NULL"
+ ",last_refund INT8 NOT NULL"
+ ")");
+
+ /* Table with the sum of the outstanding coins from
+ "denomination_pending" (denom_pubs must belong
+ to the respective's exchange's master public key);
+ it represents the total_liabilities of the exchange
+ at this point (modulo unexpected historic_loss-style
+ events where denomination keys are compromised) */
+ SQLEXEC ("CREATE TABLE IF NOT EXISTS total_liabilities"
+ "(master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)"
+ ",denom_balance_val INT8 NOT NULL"
+ ",denom_balance_frac INT4 NOT NULL"
+ ",denom_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",deposit_fee_balance_val INT8 NOT NULL"
+ ",deposit_fee_balance_frac INT4 NOT NULL"
+ ",deposit_fee_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",melt_fee_balance_val INT8 NOT NULL"
+ ",melt_fee_balance_frac INT4 NOT NULL"
+ ",melt_fee_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ")");
+
+ /* Table with the sum of the generated coins all
+ denomination keys. This represents the maximum
+ additional total financial risk of the exchange
+ in case that all denomination keys are compromised
+ (and all of the deposits so far were done by
+ the successful attacker). So this is strictly an
+ upper bound on the risk exposure of the exchange.
+ (Note that this risk is in addition to the known
+ total_liabilities) */
+ SQLEXEC ("CREATE TABLE IF NOT EXISTS total_risk"
+ "(master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)"
+ ",risk_val INT8 NOT NULL"
+ ",risk_frac INT4 NOT NULL"
+ ",risk_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ")");
+
+
+ /* Table with historic profits; basically, when a denom_pub
+ is 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. "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);
+ the deposit and melt fees are given individually; the
+ delta to the revenue_balance is from coins that were withdrawn
+ but never deposited prior to expiration. */
+ SQLEXEC ("CREATE TABLE IF NOT EXISTS historic_denomination_revenue"
+ "(master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)"
+ ",denom_pub BYTEA NOT NULL"
+ ",revenue_timestamp INT8 NOT NULL"
+ ",revenue_balance_val INT8 NOT NULL"
+ ",revenue_balance_frac INT4 NOT NULL"
+ ",revenue_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",deposit_fee_balance_val INT8 NOT NULL"
+ ",deposit_fee_balance_frac INT4 NOT NULL"
+ ",deposit_fee_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ",melt_fee_balance_val INT8 NOT NULL"
+ ",melt_fee_balance_frac INT4 NOT NULL"
+ ",melt_fee_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL" ")");
+
+ /* Table with historic losses; basically, when we need to
+ invalidate a denom_pub because the denom_priv was
+ compromised, we incur a loss. These losses are totaled
+ up here. (NOTE: the 'bankrupcy' protocol is not yet
+ implemented, so right now this table is not used.) */
+ SQLEXEC ("CREATE TABLE IF NOT EXISTS historic_losses"
+ "(master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)"
+ ",denom_pub BYTEA NOT NULL"
+ ",loss_timestamp INT8 NOT NULL"
+ ",loss_balance_val INT8 NOT NULL"
+ ",loss_balance_frac INT4 NOT NULL"
+ ",loss_balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ")");
+
+ /* Table with historic profits by reserve; basically, when a
+ reserve expires, we transmit the balance back to the user, but
+ rounding gains and withdraw fees are listed here. */
+ SQLEXEC ("CREATE TABLE IF NOT EXISTS historic_reserve_revenue"
+ "(master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)"
+ ",reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_pub)=32)"
+ ",expiration_date INT8 NOT NULL"
+ ",reserve_profit_val INT8 NOT NULL"
+ ",reserve_profit_frac INT4 NOT NULL"
+ ",reserve_profit_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ")");
+
+ /* Table with historic profits from reserves; we eventually
+ GC "historic_reserve_revenue", and then store the totals
+ in here (by time intervals). */
+ SQLEXEC ("CREATE TABLE IF NOT EXISTS historic_reserve_summary"
+ "(master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)"
+ ",start_date INT8 NOT NULL"
+ ",end_date INT8 NOT NULL"
+ ",reserve_profits_val INT8 NOT NULL"
+ ",reserve_profits_frac INT4 NOT NULL"
+ ",reserve_profits_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ")");
+
+ /* Table with historic business ledger; basically, when the exchange
+ operator decides to use operating costs for anything but wire
+ transfers to merchants, it goes in here. This happens when the
+ operator users transaction fees for business expenses. "purpose"
+ is free-form but should be a human-readable wire transfer
+ identifier. */
+ SQLEXEC ("CREATE TABLE IF NOT EXISTS historic_ledger"
+ "(master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)"
+ ",purpose VARCHAR NOT NULL"
+ ",timestamp INT8 NOT NULL"
+ ",balance_val INT8 NOT NULL"
+ ",balance_frac INT4 NOT NULL"
+ ",balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ")");
+
+ /* Table with the sum of the ledger, historic_revenue,
+ historic_losses and the auditor_reserve_balance.
+ This is the final amount that the exchange should have
+ in its bank account right now. */
+ SQLEXEC ("CREATE TABLE IF NOT EXISTS predicted_result"
+ "(master_pub BYTEA NOT NULL CHECK (LENGTH(master_pub)=32)"
+ ",balance_val INT8 NOT NULL"
+ ",balance_frac INT4 NOT NULL"
+ ",balance_curr VARCHAR("TALER_CURRENCY_LEN_STR") NOT NULL"
+ ")");
+
+
SQLEXEC_INDEX("CREATE INDEX testx "
"ON test(test_pub)");
#undef SQLEXEC