aboutsummaryrefslogtreecommitdiff
path: root/src/exchangedb/exchange-0001.sql
diff options
context:
space:
mode:
Diffstat (limited to 'src/exchangedb/exchange-0001.sql')
-rw-r--r--src/exchangedb/exchange-0001.sql79
1 files changed, 45 insertions, 34 deletions
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index a634cbf10..d2e5f3350 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -422,7 +422,7 @@ AS $$
BEGIN
EXECUTE FORMAT (
'ALTER TABLE known_coins_' || partition_suffix || ' '
- 'ADD CONSTRAINT known_coins_' || partition_suffix || '_known_coin_id_key '
+ 'ADD CONSTRAINT known_coins_' || partition_suffix || 'k_nown_coin_id_key '
'UNIQUE (known_coin_id)'
);
END
@@ -604,7 +604,8 @@ COMMENT ON COLUMN extension_details.extension_options
CREATE TABLE IF NOT EXISTS deposits
(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
,shard INT8 NOT NULL
- ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE
+ ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE
+ ,known_coin_id BIGINT NOT NULL -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE
,amount_with_fee_val INT8 NOT NULL
,amount_with_fee_frac INT4 NOT NULL
,wallet_timestamp INT8 NOT NULL
@@ -620,7 +621,7 @@ CREATE TABLE IF NOT EXISTS deposits
,done BOOLEAN NOT NULL DEFAULT FALSE
,extension_blocked BOOLEAN NOT NULL DEFAULT FALSE
,extension_details_serial_id INT8 REFERENCES extension_details (extension_details_serial_id) ON DELETE CASCADE
- ,UNIQUE (shard, known_coin_id, merchant_pub, h_contract_terms)
+ ,UNIQUE (shard, coin_pub, merchant_pub, h_contract_terms)
)
PARTITION BY HASH (shard);
@@ -649,6 +650,8 @@ COMMENT ON TABLE deposits
IS 'Deposits we have received and for which we need to make (aggregate) wire transfers (and manage refunds).';
COMMENT ON COLUMN deposits.shard
IS 'Used for load sharding. Should be set based on h_payto and merchant_pub. 64-bit value because we need an *unsigned* 32-bit value.';
+COMMENT ON COLUMN deposits.known_coin_id
+ IS 'Used for garbage collection';
COMMENT ON COLUMN deposits.wire_target_h_payto
IS 'Identifies the target bank account and KYC status';
COMMENT ON COLUMN deposits.wire_salt
@@ -704,7 +707,7 @@ CREATE TABLE IF NOT EXISTS refunds
COMMENT ON TABLE refunds
IS 'Data on coins that were refunded. Technically, refunds always apply against specific deposit operations involving a coin. The combination of coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique, and we usually select by coin_pub so that one goes first.';
COMMENT ON COLUMN refunds.deposit_serial_id
- IS 'Identifies ONLY the merchant_pub, h_contract_terms and known_coin_id. Multiple deposits may match a refund, this only identifies one of them.';
+ IS 'Identifies ONLY the merchant_pub, h_contract_terms and coin_pub. Multiple deposits may match a refund, this only identifies one of them.';
COMMENT ON COLUMN refunds.rtransaction_id
IS 'used by the merchant to make refunds unique in case the same coin for the same deposit gets a subsequent (higher) refund';
@@ -878,7 +881,7 @@ CREATE INDEX IF NOT EXISTS global_fee_by_end_date_index
CREATE TABLE IF NOT EXISTS recoup
(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id)
+ ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) -- REFERENCES known_coins (coin_pub)
,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)
,amount_val INT8 NOT NULL
@@ -886,10 +889,10 @@ CREATE TABLE IF NOT EXISTS recoup
,recoup_timestamp INT8 NOT NULL
,reserve_out_serial_id INT8 NOT NULL -- REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE
)
- PARTITION BY HASH (known_coin_id);
+ PARTITION BY HASH (coin_pub);
COMMENT ON TABLE recoup
IS 'Information about recoups that were executed between a coin and a reserve. In this type of recoup, the amount is credited back to the reserve from which the coin originated.';
-COMMENT ON COLUMN recoup.known_coin_id
+COMMENT ON COLUMN recoup.coin_pub
IS 'Coin that is being debited in the recoup. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!';
COMMENT ON COLUMN recoup.reserve_out_serial_id
IS 'Identifies the h_blind_ev of the recouped coin and provides the link to the credited reserve.';
@@ -925,9 +928,9 @@ CREATE INDEX IF NOT EXISTS recoup_by_recoup_uuid_index
CREATE INDEX IF NOT EXISTS recoup_by_reserve_out_serial_id_index
ON recoup
(reserve_out_serial_id);
-CREATE INDEX IF NOT EXISTS recoup_by_known_coin_id_index
+CREATE INDEX IF NOT EXISTS recoup_by_coin_pub_index
ON recoup
- (known_coin_id);
+ (coin_pub);
CREATE TABLE IF NOT EXISTS reserves_out_by_reserve
@@ -984,7 +987,8 @@ CREATE TRIGGER reserves_out_on_delete
CREATE TABLE IF NOT EXISTS recoup_refresh
(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
- ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id)
+ ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) -- REFERENCES known_coins (coin_pub)
+ ,known_coin_id BIGINT NOT NULL -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE
,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)
,amount_val INT8 NOT NULL
@@ -992,11 +996,13 @@ CREATE TABLE IF NOT EXISTS recoup_refresh
,recoup_timestamp INT8 NOT NULL
,rrc_serial INT8 NOT NULL -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE
)
- PARTITION BY HASH (known_coin_id);
+ PARTITION BY HASH (coin_pub);
COMMENT ON TABLE recoup_refresh
IS 'Table of coins that originated from a refresh operation and that were recouped. Links the (fresh) coin to the melted operation (and thus the old coin). A recoup on a refreshed coin credits the old coin and debits the fresh coin.';
+COMMENT ON COLUMN recoup_refresh.coin_pub
+ IS 'Refreshed coin of a revoked denomination where the residual value is credited to the old coin. Do not CASCADE ON DROP on the coin_pub, as we may keep the coin alive!';
COMMENT ON COLUMN recoup_refresh.known_coin_id
- IS 'Refreshed coin of a revoked denomination where the residual value is credited to the old coin. Do not CASCADE ON DROP on the known_coin_id, as we may keep the coin alive!';
+ IS 'FIXME: (To be) used for garbage collection (in the future)';
COMMENT ON COLUMN recoup_refresh.rrc_serial
IS 'Link to the refresh operation. Also identifies the h_blind_ev of the recouped coin (as h_coin_ev).';
COMMENT ON COLUMN recoup_refresh.coin_blind
@@ -1029,9 +1035,9 @@ CREATE INDEX IF NOT EXISTS recoup_refresh_by_recoup_refresh_uuid_index
CREATE INDEX IF NOT EXISTS recoup_refresh_by_rrc_serial_index
ON recoup_refresh
(rrc_serial);
-CREATE INDEX IF NOT EXISTS recoup_refresh_by_known_coin_id_index
+CREATE INDEX IF NOT EXISTS recoup_refresh_by_coin_pub_index
ON recoup_refresh
- (known_coin_id);
+ (coin_pub);
CREATE TABLE IF NOT EXISTS prewire
@@ -1488,7 +1494,7 @@ DECLARE
BEGIN
-- Shards: INSERT extension_details (by extension_details_serial_id)
-- INSERT wire_targets (by h_payto), on CONFLICT DO NOTHING;
--- INSERT deposits (by shard + known_coin_id, merchant_pub, h_contract_terms), ON CONFLICT DO NOTHING;
+-- INSERT deposits (by shard + coin_pub, merchant_pub, h_contract_terms), ON CONFLICT DO NOTHING;
-- UPDATE known_coins (by coin_pub)
IF NOT NULL in_extension_details
@@ -1523,6 +1529,7 @@ END IF;
INSERT INTO deposits
(shard
+ ,coin_pub
,known_coin_id
,amount_with_fee_val
,amount_with_fee_frac
@@ -1540,6 +1547,7 @@ INSERT INTO deposits
)
VALUES
(in_shard
+ ,in_coin_pub
,in_known_coin_id
,in_amount_with_fee_val
,in_amount_with_fee_frac
@@ -1570,7 +1578,7 @@ THEN
FROM deposits
WHERE
shard=in_shard AND
- known_coin_id=in_known_coin_id AND
+ coin_pub=in_coin_pub AND
merchant_pub=in_merchant_pub AND
h_contract_terms=in_h_contract_terms AND
coin_sig=in_coin_sig;
@@ -1832,7 +1840,7 @@ DECLARE
DECLARE
deposit_frac INT8; -- amount that was originally deposited
BEGIN
--- Shards: SELECT deposits (by shard, known_coin_id,h_contract_terms, merchant_pub)
+-- Shards: SELECT deposits (by shard, coin_pub, h_contract_terms, merchant_pub)
-- INSERT refunds (by deposit_serial_id, rtransaction_id) ON CONFLICT DO NOTHING
-- SELECT refunds (by deposit_serial_id)
-- UPDATE known_coins (by coin_pub)
@@ -1849,7 +1857,7 @@ INTO
,out_gone
FROM deposits
WHERE shard=in_deposit_shard
- AND known_coin_id=in_known_coin_id
+ AND coin_pub=in_coin_pub
AND h_contract_terms=in_h_contract_terms
AND merchant_pub=in_merchant_pub;
@@ -2019,10 +2027,10 @@ DECLARE
tmp_frac INT8; -- amount recouped
BEGIN
-- Shards: SELECT known_coins (by coin_pub)
--- SELECT recoup (by known_coin_id)
+-- SELECT recoup (by coin_pub)
-- UPDATE known_coins (by coin_pub)
-- UPDATE reserves (by reserve_pub)
--- INSERT recoup (by known_coin_id)
+-- INSERT recoup (by coin_pub)
out_internal_failure=FALSE;
@@ -2052,7 +2060,7 @@ THEN
INTO
out_recoup_timestamp
FROM recoup
- WHERE known_coin_id=in_known_coin_id;
+ WHERE coin_pub=in_coin_pub;
out_recoup_ok=FOUND;
RETURN;
@@ -2097,7 +2105,7 @@ END IF;
INSERT INTO recoup
- (known_coin_id
+ (coin_pub
,coin_sig
,coin_blind
,amount_val
@@ -2106,7 +2114,7 @@ INSERT INTO recoup
,reserve_out_serial_id
)
VALUES
- (in_known_coin_id
+ (in_coin_pub
,in_coin_sig
,in_coin_blind
,tmp_val
@@ -2148,9 +2156,9 @@ DECLARE
BEGIN
-- Shards: UPDATE known_coins (by coin_pub)
--- SELECT recoup_refresh (by known_coin_id)
+-- SELECT recoup_refresh (by coin_pub)
-- UPDATE known_coins (by coin_pub)
--- INSERT recoup_refresh (by known_coin_id)
+-- INSERT recoup_refresh (by coin_pub)
out_internal_failure=FALSE;
@@ -2181,7 +2189,7 @@ THEN
INTO
out_recoup_timestamp
FROM recoup_refresh
- WHERE known_coin_id=in_known_coin_id;
+ WHERE coin_pub=in_coin_pub;
out_recoup_ok=FOUND;
RETURN;
END IF;
@@ -2222,7 +2230,8 @@ END IF;
INSERT INTO recoup_refresh
- (known_coin_id
+ (coin_pub
+ ,known_coin_id
,coin_sig
,coin_blind
,amount_val
@@ -2231,7 +2240,8 @@ INSERT INTO recoup_refresh
,rrc_serial
)
VALUES
- (in_known_coin_id
+ (in_coin_pub
+ ,in_known_coin_id
,in_coin_sig
,in_coin_blind
,tmp_val
@@ -2292,7 +2302,7 @@ SELECT
DELETE FROM recoup
WHERE reserve_out_serial_id < reserve_out_min;
-
+-- FIXME: recoup_refresh lacks GC!
SELECT
reserve_uuid
@@ -2305,7 +2315,8 @@ SELECT
DELETE FROM reserves_out
WHERE reserve_uuid < reserve_uuid_min;
-
+-- FIXME: this query will be horribly slow;
+-- need to find another way to formulate it...
DELETE FROM denominations
WHERE expire_legal < in_now
AND denominations_serial NOT IN
@@ -2314,14 +2325,14 @@ DELETE FROM denominations
AND denominations_serial NOT IN
(SELECT DISTINCT denominations_serial
FROM known_coins
- WHERE known_coin_id IN
- (SELECT DISTINCT known_coin_id
+ WHERE coin_pub IN
+ (SELECT DISTINCT coin_pub
FROM recoup))
AND denominations_serial NOT IN
(SELECT DISTINCT denominations_serial
FROM known_coins
- WHERE known_coin_id IN
- (SELECT DISTINCT known_coin_id
+ WHERE coin_pub IN
+ (SELECT DISTINCT coin_pub
FROM recoup_refresh));
SELECT