diff options
Diffstat (limited to 'src/exchangedb/exchange-0001.sql')
| -rw-r--r-- | src/exchangedb/exchange-0001.sql | 79 |
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 |