diff options
Diffstat (limited to 'src/exchangedb/exchange-0001.sql')
| -rw-r--r-- | src/exchangedb/exchange-0001.sql | 52 |
1 files changed, 26 insertions, 26 deletions
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql index 1d86d5d9a..3331eada2 100644 --- a/src/exchangedb/exchange-0001.sql +++ b/src/exchangedb/exchange-0001.sql @@ -68,17 +68,17 @@ COMMENT ON TABLE denomination_revocations CREATE TABLE IF NOT EXISTS wire_targets (wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE - ,h_payto BYTEA PRIMARY KEY CHECK (LENGTH(h_payto)=64) + ,wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32) ,payto_uri VARCHAR NOT NULL ,kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE) ,external_id VARCHAR ) - PARTITION BY HASH (h_payto); + PARTITION BY HASH (wire_target_h_payto); COMMENT ON TABLE wire_targets IS 'All senders and recipients of money via the exchange'; COMMENT ON COLUMN wire_targets.payto_uri IS 'Can be a regular bank account, or also be a URI identifying a reserve-account (for P2P payments)'; -COMMENT ON COLUMN wire_targets.h_payto +COMMENT ON COLUMN wire_targets.wire_target_h_payto IS 'Unsalted hash of payto_uri'; COMMENT ON COLUMN wire_targets.kyc_ok IS 'true if the KYC check was passed successfully'; @@ -106,7 +106,7 @@ $$; SELECT add_constraints_to_wire_targets_partition('default'); --- FIXME partition by serial_id rather than h_payto, +-- FIXME partition by serial_id rather than h_payto, -- it is used more in join conditions - crucial for sharding to select this. -- Author: (Boss Marco) CREATE INDEX IF NOT EXISTS wire_targets_serial_id_index @@ -160,14 +160,14 @@ CREATE TABLE IF NOT EXISTS reserves_in ,wire_reference INT8 NOT NULL ,credit_val INT8 NOT NULL ,credit_frac INT4 NOT NULL - ,wire_source_serial_id INT8 NOT NULL -- REFERENCES wire_targets (wire_target_serial_id) + ,wire_source_h_payto BYTEA CHECK (LENGTH(wire_source_h_payto)=32) ,exchange_account_section TEXT NOT NULL ,execution_date INT8 NOT NULL ) PARTITION BY HASH (reserve_pub); COMMENT ON TABLE reserves_in IS 'list of transfers of funds into the reserves, one per incoming wire transfer'; -COMMENT ON COLUMN reserves_in.wire_source_serial_id +COMMENT ON COLUMN reserves_in.wire_source_h_payto IS 'Identifies the debited bank account and KYC status'; COMMENT ON COLUMN reserves_in.reserve_pub IS 'Public key of the reserve. Private key signifies ownership of the remaining balance.'; @@ -215,7 +215,7 @@ CREATE TABLE IF NOT EXISTS reserves_close ,reserve_pub BYTEA NOT NULL REFERENCES reserves (reserve_pub) ON DELETE CASCADE ,execution_date INT8 NOT NULL ,wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32) - ,wire_target_serial_id INT8 NOT NULL -- REFERENCES wire_targets (wire_target_serial_id) + ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32) ,amount_val INT8 NOT NULL ,amount_frac INT4 NOT NULL ,closing_fee_val INT8 NOT NULL @@ -223,7 +223,7 @@ CREATE TABLE IF NOT EXISTS reserves_close PARTITION BY HASH (reserve_pub); COMMENT ON TABLE reserves_close IS 'wire transfers executed by the reserve to close reserves'; -COMMENT ON COLUMN reserves_close.wire_target_serial_id +COMMENT ON COLUMN reserves_close.wire_target_h_payto IS 'Identifies the credited bank account (and KYC status). Note that closing does not depend on KYC.'; CREATE TABLE IF NOT EXISTS reserves_close_default @@ -277,7 +277,7 @@ COMMENT ON COLUMN reserves_out.denominations_serial CREATE TABLE IF NOT EXISTS reserves_out_default PARTITION OF reserves_out FOR VALUES WITH (MODULUS 1, REMAINDER 0); - + CREATE OR REPLACE FUNCTION add_constraints_to_reserves_out_partition( IN partition_suffix VARCHAR ) @@ -618,7 +618,7 @@ CREATE TABLE IF NOT EXISTS deposits ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64) ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64) ,wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16) - ,wire_target_serial_id INT8 NOT NULL -- REFERENCES wire_targets (wire_target_serial_id) + ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32) ,tiny BOOLEAN NOT NULL DEFAULT FALSE ,done BOOLEAN NOT NULL DEFAULT FALSE ,extension_blocked BOOLEAN NOT NULL DEFAULT FALSE @@ -652,7 +652,7 @@ 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.wire_target_serial_id +COMMENT ON COLUMN deposits.wire_target_h_payto IS 'Identifies the target bank account and KYC status'; COMMENT ON COLUMN deposits.wire_salt IS 'Salt used when hashing the payto://-URI to get the h_wire'; @@ -685,7 +685,7 @@ COMMENT ON INDEX deposits_for_get_ready_index CREATE INDEX IF NOT EXISTS deposits_for_iterate_matching_index ON deposits (merchant_pub - ,wire_target_serial_id + ,wire_target_h_payto ,done ,extension_blocked ,refund_deadline ASC @@ -742,7 +742,7 @@ CREATE TABLE IF NOT EXISTS wire_out (wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY ,execution_date INT8 NOT NULL ,wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32) - ,wire_target_serial_id INT8 NOT NULL -- REFERENCES wire_targets (wire_target_serial_id) + ,wire_target_h_payto BYTEA CHECK (LENGTH(wire_target_h_payto)=32) ,exchange_account_section TEXT NOT NULL ,amount_val INT8 NOT NULL ,amount_frac INT4 NOT NULL @@ -752,7 +752,7 @@ COMMENT ON TABLE wire_out IS 'wire transfers the exchange has executed'; COMMENT ON COLUMN wire_out.exchange_account_section IS 'identifies the configuration section with the debit account of this payment'; -COMMENT ON COLUMN wire_out.wire_target_serial_id +COMMENT ON COLUMN wire_out.wire_target_h_payto IS 'Identifies the credited bank account and KYC status'; CREATE TABLE IF NOT EXISTS wire_out_default @@ -779,9 +779,9 @@ SELECT add_constraints_to_wire_out_partition('default'); CREATE INDEX IF NOT EXISTS wire_out_by_wireout_uuid_index ON wire_out (wireout_uuid); -CREATE INDEX IF NOT EXISTS wire_out_by_wire_target_serial_id_index +CREATE INDEX IF NOT EXISTS wire_out_by_wire_target_h_payto_index ON wire_out - (wire_target_serial_id); + (wire_target_h_payto); CREATE TABLE IF NOT EXISTS aggregation_tracking @@ -1143,7 +1143,7 @@ BEGIN -- reserves_out (INSERT, with CONFLICT detection) by wih -- reserves by reserve_pub (UPDATE) -- reserves_in by reserve_pub (SELECT) --- wire_targets by wire_target_serial_id +-- wire_targets by wire_target_h_payto SELECT denominations_serial INTO denom_serial @@ -1300,12 +1300,12 @@ END IF; -- this reserve. FIXME: likely not adequate for reserves that got P2P transfers! SELECT kyc_ok - ,wire_source_serial_id + ,wire_target_serial_id INTO kycok ,account_uuid FROM reserves_in - JOIN wire_targets ON (wire_source_serial_id = wire_target_serial_id) + JOIN wire_targets ON (wire_source_h_payto = wire_target_h_payto) WHERE reserve_pub=rpub LIMIT 1; -- limit 1 should not be required (without p2p transfers) @@ -1370,8 +1370,8 @@ IF EXISTS ( SELECT 1 FROM information_Schema.constraint_column_usage WHERE table_name='wire_out' - AND constraint_name='wire_out_ref') -THEN + AND constraint_name='wire_out_ref') +THEN SET CONSTRAINTS wire_out_ref DEFERRED; END IF; @@ -1424,12 +1424,12 @@ END IF; INSERT INTO wire_targets - (h_payto + (wire_target_h_payto ,payto_uri) VALUES (in_h_payto ,in_receiver_wire_account) -ON CONFLICT DO NOTHING -- for CONFLICT ON (h_payto) +ON CONFLICT DO NOTHING -- for CONFLICT ON (wire_target_h_payto) RETURNING wire_target_serial_id INTO wtsi; IF NOT FOUND @@ -1437,7 +1437,7 @@ THEN SELECT wire_target_serial_id INTO wtsi FROM wire_targets - WHERE h_payto=in_h_payto; + WHERE wire_target_h_payto=in_h_payto; END IF; @@ -1454,7 +1454,7 @@ INSERT INTO deposits ,h_contract_terms ,coin_sig ,wire_salt - ,wire_target_serial_id + ,wire_target_h_payto ,extension_blocked ,extension_details_serial_id ) @@ -1471,7 +1471,7 @@ INSERT INTO deposits ,in_h_contract_terms ,in_coin_sig ,in_wire_salt - ,wtsi + ,in_h_payto ,in_extension_blocked ,xdi) ON CONFLICT DO NOTHING; |