From 0eed0c0de21648453c7abc1e999c5740dacde0d7 Mon Sep 17 00:00:00 2001 From: Marco Boss Date: Tue, 1 Mar 2022 14:01:42 +0100 Subject: tested shard creation locally --- src/exchangedb/exchange-0001.sql | 97 ++++++++++++++++++++++++++++++++++------ 1 file changed, 84 insertions(+), 13 deletions(-) diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql index 158ec2eb5..62660349d 100644 --- a/src/exchangedb/exchange-0001.sql +++ b/src/exchangedb/exchange-0001.sql @@ -173,7 +173,7 @@ BEGIN PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I' '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',reserve_pub BYTEA PRIMARY KEY REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',reserve_pub BYTEA PRIMARY KEY' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE' ',wire_reference INT8 NOT NULL' ',credit_val INT8 NOT NULL' ',credit_frac INT4 NOT NULL' @@ -191,6 +191,11 @@ BEGIN ADD CONSTRAINT reserves_in_reserve_serial_id_key UNIQUE (reserve_in_serial_id) ; + ELSE + ALTER TABLE IF EXISTS reserves_in + ADD CONSTRAINT reserves_in_reserve_pub_fkey + FOREIGN KEY (reserve_pub) REFERENCES reserves (reserve_pub) ON DELETE CASCADE + ; END IF; END @@ -212,7 +217,7 @@ BEGIN PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I' '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY' - ',reserve_pub BYTEA NOT NULL REFERENCES reserves (reserve_pub) ON DELETE CASCADE' + ',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)' @@ -231,6 +236,11 @@ BEGIN ADD CONSTRAINT reserves_close_close_uuid_pkey PRIMARY KEY (close_uuid) ; + ELSE + ALTER TABLE IF EXISTS reserves_close + ADD CONSTRAINT reserves_close_reserve_pub_fkey + FOREIGN KEY (reserve_pub) REFERENCES reserves (reserve_pub) ON DELETE CASCADE + ; END IF; END @@ -253,7 +263,7 @@ BEGIN 'CREATE TABLE IF NOT EXISTS %I' '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE' - ',denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial)' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial)' ',denom_sig BYTEA NOT NULL' ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE' ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)' @@ -271,6 +281,12 @@ BEGIN ADD CONSTRAINT reserves_out_reserve_out_serial_id_key UNIQUE (reserve_out_serial_id) ; + ELSE + -- FIXME once denominations are replicated we can safely add the fkey on table creation + ALTER TABLE IF EXISTS reserves_out + ADD CONSTRAINT reserves_out_denominations_serial_fkey + FOREIGN KEY (denominations_serial) REFERENCES denominations (denominations_serial) + ; END IF; END @@ -375,7 +391,7 @@ BEGIN PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I' '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) ON DELETE CASCADE' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE' ',coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)' ',age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)' ',denom_sig BYTEA NOT NULL' @@ -392,6 +408,12 @@ BEGIN ADD CONSTRAINT known_coins_known_coin_id_key UNIQUE (known_coin_id) ; + ELSE + -- FIXME once denominations are replicated we can safely add the fkey on table creation + ALTER TABLE IF EXISTS known_coins + ADD CONSTRAINT known_coins_denominations_serial_fkey + FOREIGN KEY (denominations_serial) REFERENCES denominations (denominations_serial) ON DELETE CASCADE + ; END IF; END @@ -414,7 +436,7 @@ BEGIN 'CREATE TABLE IF NOT EXISTS %I' '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)' - ',old_coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE CASCADE' + ',old_coin_pub BYTEA NOT NULL' -- REFERENCES known_coins (coin_pub) ON DELETE CASCADE' ',h_age_commitment BYTEA CHECK(LENGTH(h_age_commitment)=32)' ',old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)' ',amount_with_fee_val INT8 NOT NULL' @@ -431,6 +453,11 @@ BEGIN ADD CONSTRAINT refresh_commitments_melt_serial_id_key UNIQUE (melt_serial_id) ; + ELSE + ALTER TABLE IF EXISTS refresh_commitments + ADD CONSTRAINT refresh_commitments_old_coin_pub_fkey + FOREIGN KEY (old_coin_pub) REFERENCES known_coins (coin_pub) ON DELETE CASCADE + ; END IF; END @@ -455,7 +482,7 @@ BEGIN ',melt_serial_id INT8 NOT NULL' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE' ',freshcoin_index INT4 NOT NULL' ',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)' - ',denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) ON DELETE CASCADE' + ',denominations_serial INT8 NOT NULL' -- REFERENCES denominations (denominations_serial) ON DELETE CASCADE' ',coin_ev BYTEA NOT NULL' -- UNIQUE' ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' -- UNIQUE' ',ev_sig BYTEA NOT NULL' @@ -478,6 +505,12 @@ BEGIN ,ADD CONSTRAINT refresh_revealed_coins_melt_serial_id_freshcoin_index_pkey PRIMARY KEY (melt_serial_id, freshcoin_index) ; + ELSE + -- FIXME once denominations are replicated we can safely add the fkey on table creation + ALTER TABLE IF EXISTS refresh_revealed_coins + ADD CONSTRAINT refresh_revealed_coins_denominations_serial_fkey + FOREIGN KEY (denominations_serial) REFERENCES denominations (denominations_serial) ON DELETE CASCADE + ; END IF; END @@ -563,7 +596,7 @@ BEGIN ',tiny BOOLEAN NOT NULL DEFAULT FALSE' ',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' + ',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)' ') %s ;' ,'deposits' @@ -576,6 +609,11 @@ BEGIN ADD CONSTRAINT deposits_deposit_by_serial_id_pkey PRIMARY KEY (deposit_serial_id) ; + ELSE + ALTER TABLE IF EXISTS Deposits + ADD CONSTRAINT deposits_extension_details_serial_id_fkey + FOREIGN KEY (extension_details_serial_id) REFERENCES extension_details (extension_details_serial_id) ON DELETE CASCADE + ; END IF; END @@ -673,7 +711,7 @@ BEGIN 'CREATE TABLE IF NOT EXISTS %I' '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' ',deposit_serial_id INT8 PRIMARY KEY' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' - ',wtid_raw BYTEA NOT NULL CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE' + ',wtid_raw BYTEA NOT NULL' -- CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE' ') %s ;' ,'aggregation_tracking' ,'PARTITION BY HASH (deposit_serial_id)' @@ -685,6 +723,11 @@ BEGIN ADD CONSTRAINT aggregation_tracking_aggregation_tracking_serial_id_key UNIQUE (aggergation_tracking_serial_id) ; + ELSE + ALTER TABLE IF EXISTS aggregation_tracking + ADD CONSTRAINT wire_out_ref + FOREIGN KEY (wtid_raw) REFERENCES wire_out (wtid_raw) ON DELETE CASCADE DEFERRABLE + ; END IF; END @@ -913,15 +956,14 @@ CREATE OR REPLACE FUNCTION create_foreign_table( source_table_name VARCHAR ,modulus INTEGER ,shard_suffix VARCHAR - ,partition_num INTEGER - ,server_name VARCHAR + ,current_shard_num INTEGER ) RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN - RAISE NOTICE 'Creating %_% on %', source_table_name, shard_suffix, server_name; + RAISE NOTICE 'Creating %_% on %', source_table_name, shard_suffix, shard_suffix; EXECUTE FORMAT( 'CREATE FOREIGN TABLE IF NOT EXISTS %I ' @@ -931,8 +973,8 @@ BEGIN ,source_table_name || '_' || shard_suffix ,source_table_name ,modulus - ,partition_num-1 - ,server_name + ,current_shard_num-1 + ,shard_suffix ); EXECUTE FORMAT( @@ -1186,6 +1228,7 @@ BEGIN ALTER TABLE IF EXISTS reserves_out DROP CONSTRAINT IF EXISTS reserves_out_pkey CASCADE ,DROP CONSTRAINT IF EXISTS reserves_out_denominations_serial_fkey + ,DROP CONSTRAINT IF EXISTS reserves_out_h_blind_ev_key ; ALTER TABLE IF EXISTS known_coins @@ -1389,5 +1432,33 @@ BEGIN END $$; +CREATE OR REPLACE FUNCTION setup_shard_db( + shard_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +BEGIN + + PERFORM create_table_wire_targets(shard_suffix); + PERFORM create_table_reserves(shard_suffix); + PERFORM create_table_reserves_in(shard_suffix); + PERFORM create_table_reserves_out(shard_suffix); + PERFORM create_table_reserves_close(shard_suffix); + PERFORM create_table_known_coins(shard_suffix); + PERFORM create_table_refresh_commitments(shard_suffix); + PERFORM create_table_refresh_revealed_coins(shard_suffix); + PERFORM create_table_refresh_transfer_keys(shard_suffix); + PERFORM create_table_deposits(shard_suffix); + PERFORM create_table_refunds(shard_suffix); + PERFORM create_table_wire_out(shard_suffix); + PERFORM create_table_aggregation_tracking(shard_suffix); + PERFORM create_table_recoup(shard_suffix); + PERFORM create_table_recoup_refresh(shard_suffix); + PERFORM create_table_prewire(shard_suffix); + PERFORM create_table_cs_nonce_locks(shard_suffix); + +END +$$; COMMIT; -- cgit v1.2.3