diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/exchangedb/exchange-0001.sql | 168 |
1 files changed, 96 insertions, 72 deletions
diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql index ba40a91ce..5b6921fd8 100644 --- a/src/exchangedb/exchange-0001.sql +++ b/src/exchangedb/exchange-0001.sql @@ -227,6 +227,13 @@ COMMENT ON TABLE reserves_close 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 INDEX IF NOT EXISTS reserves_close_by_close_uuid_index + ON reserves_close + (close_uuid); +CREATE INDEX IF NOT EXISTS reserves_close_by_reserve_pub_index + ON reserves_close + (reserve_pub); + CREATE TABLE IF NOT EXISTS reserves_close_default PARTITION OF reserves_close FOR VALUES WITH (MODULUS 1, REMAINDER 0); @@ -248,12 +255,6 @@ $$; SELECT add_constraints_to_reserves_close_partition('default'); -CREATE INDEX IF NOT EXISTS reserves_close_by_close_uuid_index - ON reserves_close - (close_uuid); -CREATE INDEX IF NOT EXISTS reserves_close_by_reserve_pub_index - ON reserves_close - (reserve_pub); CREATE TABLE IF NOT EXISTS reserves_out @@ -275,6 +276,17 @@ COMMENT ON COLUMN reserves_out.h_blind_ev COMMENT ON COLUMN reserves_out.denominations_serial IS 'We do not CASCADE ON DELETE here, we may keep the denomination data alive'; +CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_out_serial_id_index + ON reserves_out + (reserve_out_serial_id); +CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_uuid_and_execution_date_index + ON reserves_out + (reserve_uuid, execution_date); +COMMENT ON INDEX reserves_out_by_reserve_uuid_and_execution_date_index + IS 'for get_reserves_out and exchange_do_withdraw_limit_check'; + + + CREATE TABLE IF NOT EXISTS reserves_out_default PARTITION OF reserves_out FOR VALUES WITH (MODULUS 1, REMAINDER 0); @@ -296,15 +308,6 @@ $$; SELECT add_constraints_to_reserves_out_partition('default'); -CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_out_serial_id_index - ON reserves_out - (reserve_out_serial_id); -CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_uuid_and_execution_date_index - ON reserves_out - (reserve_uuid, execution_date); -COMMENT ON INDEX reserves_out_by_reserve_uuid_and_execution_date_index - IS 'for get_reserves_out and exchange_do_withdraw_limit_check'; - CREATE TABLE IF NOT EXISTS auditors (auditor_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY UNIQUE @@ -514,6 +517,13 @@ COMMENT ON COLUMN refresh_revealed_coins.h_coin_ev COMMENT ON COLUMN refresh_revealed_coins.ev_sig IS 'exchange signature over the envelope'; +CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_rrc_serial_index + ON refresh_revealed_coins + (rrc_serial); +CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_melt_serial_id_index + ON refresh_revealed_coins + (melt_serial_id); + CREATE TABLE IF NOT EXISTS refresh_revealed_coins_default PARTITION OF refresh_revealed_coins FOR VALUES WITH (MODULUS 1, REMAINDER 0); @@ -541,12 +551,6 @@ $$; SELECT add_constraints_to_refresh_revealed_coins_partition('default'); -CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_rrc_serial_index - ON refresh_revealed_coins - (rrc_serial); -CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_melt_serial_id_index - ON refresh_revealed_coins - (melt_serial_id); CREATE TABLE IF NOT EXISTS refresh_transfer_keys @@ -626,27 +630,6 @@ CREATE TABLE IF NOT EXISTS deposits ) PARTITION BY HASH (shard); -- FIXME: why not BY RANGE? RANGE would seem better for 'deposits_get_ready'! -CREATE TABLE IF NOT EXISTS deposits_default - PARTITION OF deposits - FOR VALUES WITH (MODULUS 1, REMAINDER 0); - -CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition( - IN partition_suffix VARCHAR -) -RETURNS void -LANGUAGE plpgsql -AS $$ -BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE deposits_' || partition_suffix || ' ' - 'ADD CONSTRAINT deposits_' || partition_suffix || '_deposit_serial_id_pkey ' - 'PRIMARY KEY (deposit_serial_id)' - ); -END -$$; - -SELECT add_constraints_to_deposits_partition('default'); - 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 @@ -679,6 +662,7 @@ CREATE INDEX IF NOT EXISTS deposits_for_get_ready_index ); COMMENT ON INDEX deposits_for_get_ready_index IS 'for deposits_get_ready'; + CREATE INDEX IF NOT EXISTS deposits_for_iterate_matching_index ON deposits (shard @@ -692,6 +676,29 @@ COMMENT ON INDEX deposits_for_iterate_matching_index IS 'for deposits_iterate_matching'; +CREATE TABLE IF NOT EXISTS deposits_default + PARTITION OF deposits + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +CREATE OR REPLACE FUNCTION add_constraints_to_deposits_partition( + IN partition_suffix VARCHAR +) +RETURNS void +LANGUAGE plpgsql +AS $$ +BEGIN + EXECUTE FORMAT ( + 'ALTER TABLE deposits_' || partition_suffix || ' ' + 'ADD CONSTRAINT deposits_' || partition_suffix || '_deposit_serial_id_pkey ' + 'PRIMARY KEY (deposit_serial_id)' + ); +END +$$; + +SELECT add_constraints_to_deposits_partition('default'); + + + CREATE TABLE IF NOT EXISTS deposits_by_coin (deposit_serial_id BIGINT ,shard INT8 NOT NULL @@ -701,11 +708,14 @@ CREATE TABLE IF NOT EXISTS deposits_by_coin COMMENT ON TABLE deposits_by_coin IS 'Enables fast lookups of deposit by coin_pub, auto-populated via TRIGGER below'; +CREATE INDEX IF NOT EXISTS deposits_by_coin_main_index + ON deposits_by_coin + (coin_pub); + CREATE TABLE IF NOT EXISTS deposits_by_coin_default PARTITION OF deposits_by_coin FOR VALUES WITH (MODULUS 1, REMAINDER 0); - CREATE OR REPLACE FUNCTION deposits_by_coin_insert_trigger() RETURNS trigger LANGUAGE plpgsql @@ -729,7 +739,6 @@ CREATE TRIGGER deposits_on_insert ON deposits FOR EACH ROW EXECUTE FUNCTION deposits_by_coin_insert_trigger(); - CREATE OR REPLACE FUNCTION deposits_by_coin_delete_trigger() RETURNS trigger LANGUAGE plpgsql @@ -817,6 +826,14 @@ COMMENT ON COLUMN wire_out.exchange_account_section COMMENT ON COLUMN wire_out.wire_target_h_payto IS 'Identifies the credited bank account and KYC status'; +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_h_payto_index + ON wire_out + (wire_target_h_payto); + + CREATE TABLE IF NOT EXISTS wire_out_default PARTITION OF wire_out FOR VALUES WITH (MODULUS 1, REMAINDER 0); @@ -838,12 +855,6 @@ $$; 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_h_payto_index - ON wire_out - (wire_target_h_payto); CREATE TABLE IF NOT EXISTS aggregation_tracking @@ -963,6 +974,17 @@ COMMENT ON COLUMN recoup.coin_sig COMMENT ON COLUMN recoup.coin_blind IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the withdraw operation.'; +CREATE INDEX IF NOT EXISTS recoup_by_recoup_uuid_index + ON recoup + (recoup_uuid); +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_coin_pub_index + ON recoup + (coin_pub); + + CREATE TABLE IF NOT EXISTS recoup_default PARTITION OF recoup FOR VALUES WITH (MODULUS 1, REMAINDER 0); @@ -984,16 +1006,6 @@ $$; SELECT add_constraints_to_recoup_partition('default'); -CREATE INDEX IF NOT EXISTS recoup_by_recoup_uuid_index - ON recoup - (recoup_uuid); -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_coin_pub_index - ON recoup - (coin_pub); - CREATE TABLE IF NOT EXISTS reserves_out_by_reserve (reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE @@ -1003,6 +1015,11 @@ CREATE TABLE IF NOT EXISTS reserves_out_by_reserve COMMENT ON TABLE reserves_out_by_reserve IS 'Information in this table is strictly redundant with that of reserves_out, but saved by a different primary key for fast lookups by reserve public key/uuid.'; +CREATE INDEX IF NOT EXISTS reserves_out_by_reserve_main_index + ON reserves_out_by_reserve + (reserve_uuid); + + CREATE TABLE IF NOT EXISTS reserves_out_by_reserve_default PARTITION OF reserves_out_by_reserve FOR VALUES WITH (MODULUS 1, REMAINDER 0); @@ -1047,6 +1064,9 @@ CREATE TRIGGER reserves_out_on_delete FOR EACH ROW EXECUTE FUNCTION reserves_out_by_reserve_delete_trigger(); + + + CREATE TABLE IF NOT EXISTS recoup_refresh (recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE ,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32) -- REFERENCES known_coins (coin_pub) @@ -1070,6 +1090,17 @@ COMMENT ON COLUMN recoup_refresh.rrc_serial COMMENT ON COLUMN recoup_refresh.coin_blind IS 'Denomination blinding key used when creating the blinded coin from the planchet. Secret revealed during the recoup to provide the linkage between the coin and the refresh operation.'; +CREATE INDEX IF NOT EXISTS recoup_refresh_by_recoup_refresh_uuid_index + ON recoup_refresh + (recoup_refresh_uuid); +CREATE INDEX IF NOT EXISTS recoup_refresh_by_rrc_serial_index + ON recoup_refresh + (rrc_serial); +CREATE INDEX IF NOT EXISTS recoup_refresh_by_coin_pub_index + ON recoup_refresh + (coin_pub); + + CREATE TABLE IF NOT EXISTS recoup_refresh_default PARTITION OF recoup_refresh FOR VALUES WITH (MODULUS 1, REMAINDER 0); @@ -1091,15 +1122,6 @@ $$; SELECT add_constraints_to_recoup_refresh_partition('default'); -CREATE INDEX IF NOT EXISTS recoup_refresh_by_recoup_refresh_uuid_index - ON recoup_refresh - (recoup_refresh_uuid); -CREATE INDEX IF NOT EXISTS recoup_refresh_by_rrc_serial_index - ON recoup_refresh - (rrc_serial); -CREATE INDEX IF NOT EXISTS recoup_refresh_by_coin_pub_index - ON recoup_refresh - (coin_pub); CREATE TABLE IF NOT EXISTS prewire @@ -1118,9 +1140,6 @@ COMMENT ON COLUMN prewire.finished IS 'set to TRUE once bank confirmed receiving the wire transfer request'; COMMENT ON COLUMN prewire.buf IS 'serialized data to send to the bank to execute the wire transfer'; -CREATE TABLE IF NOT EXISTS prewire_default - PARTITION OF prewire - FOR VALUES WITH (MODULUS 1, REMAINDER 0); CREATE INDEX IF NOT EXISTS prewire_by_finished_index ON prewire @@ -1134,6 +1153,11 @@ CREATE INDEX IF NOT EXISTS prewire_by_failed_finished_index COMMENT ON INDEX prewire_by_failed_finished_index IS 'for wire_prepare_data_get'; +CREATE TABLE IF NOT EXISTS prewire_default + PARTITION OF prewire + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + + CREATE TABLE IF NOT EXISTS wire_accounts (payto_uri VARCHAR PRIMARY KEY |