diff options
Diffstat (limited to 'src/exchangedb/0002-aggregation_tracking.sql')
-rw-r--r-- | src/exchangedb/0002-aggregation_tracking.sql | 68 |
1 files changed, 53 insertions, 15 deletions
diff --git a/src/exchangedb/0002-aggregation_tracking.sql b/src/exchangedb/0002-aggregation_tracking.sql index 25c394d26..d5c852e87 100644 --- a/src/exchangedb/0002-aggregation_tracking.sql +++ b/src/exchangedb/0002-aggregation_tracking.sql @@ -14,7 +14,7 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- -CREATE OR REPLACE FUNCTION create_table_aggregation_tracking( +CREATE FUNCTION create_table_aggregation_tracking( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID @@ -23,22 +23,43 @@ AS $$ DECLARE table_name VARCHAR DEFAULT 'aggregation_tracking'; BEGIN - PERFORM create_partitioned_table( - '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' -- FIXME change to coin_pub + deposit_serial_id for more efficient depost -- or something else ??? - ',wtid_raw BYTEA NOT NULL' -- CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE' + 'CREATE TABLE %I' + '(aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' + ',deposit_serial_id INT8 PRIMARY KEY' + ',wtid_raw BYTEA NOT NULL' ') %s ;' ,table_name ,'PARTITION BY HASH (deposit_serial_id)' ,shard_suffix ); + PERFORM comment_partitioned_table( + 'mapping from wire transfer identifiers (WTID) to deposits (and back)' + ,table_name + ,shard_suffix + ); + PERFORM comment_partitioned_column( + 'identifier of the wire transfer' + ,'wtid_raw' + ,table_name + ,shard_suffix + ); +END +$$; - table_name = concat_ws('_', table_name, shard_suffix); +CREATE FUNCTION constrain_table_aggregation_tracking( + IN partition_suffix VARCHAR +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_tracking'; +BEGIN + table_name = concat_ws('_', table_name, shard_suffix); EXECUTE FORMAT ( - 'CREATE INDEX IF NOT EXISTS ' || table_name || '_by_wtid_raw_index ' + 'CREATE INDEX ' || table_name || '_by_wtid_raw_index ' 'ON ' || table_name || ' ' '(wtid_raw);' ); @@ -46,21 +67,28 @@ BEGIN 'COMMENT ON INDEX ' || table_name || '_by_wtid_raw_index ' 'IS ' || quote_literal('for lookup_transactions') || ';' ); - + EXECUTE FORMAT ( + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_aggregation_serial_id_key' + ' UNIQUE (aggregation_serial_id) ' + ); END $$; -CREATE OR REPLACE FUNCTION add_constraints_to_aggregation_tracking_partition( - IN partition_suffix VARCHAR -) + +CREATE FUNCTION foreign_table_aggregation_tracking() RETURNS VOID LANGUAGE plpgsql AS $$ +DECLARE + table_name VARCHAR DEFAULT 'aggregation_tracking'; BEGIN EXECUTE FORMAT ( - 'ALTER TABLE aggregation_tracking_' || partition_suffix || ' ' - 'ADD CONSTRAINT aggregation_tracking_' || partition_suffix || '_aggregation_serial_id_key ' - 'UNIQUE (aggregation_serial_id) ' + 'ALTER TABLE ' || table_name || + ' ADD CONSTRAINT ' || table_name || '_foreign_deposit' + ' REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE' -- FIXME change to coin_pub + deposit_serial_id for more efficient deposit??? + ',ADD CONSTRAINT ' || table_name || '_foreign_wtid_raw' + ' REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE' ); END $$; @@ -77,4 +105,14 @@ INSERT INTO exchange_tables ,'exchange-0002' ,'create' ,TRUE + ,FALSE), + ('aggregation_tracking' + ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('aggregation_tracking' + ,'exchange-0002' + ,'foreign' + ,TRUE ,FALSE); |