diff options
Diffstat (limited to 'src/exchangedb/common-0002.sql')
| -rw-r--r-- | src/exchangedb/common-0002.sql | 288 |
1 files changed, 135 insertions, 153 deletions
diff --git a/src/exchangedb/common-0002.sql b/src/exchangedb/common-0002.sql index 0f7b1f52..388d5547 100644 --- a/src/exchangedb/common-0002.sql +++ b/src/exchangedb/common-0002.sql @@ -14,192 +14,174 @@ -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> -- +-------------------------------- + +CREATE TABLE IF NOT EXISTS exchange_tables + (table_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY + ,name VARCHAR NOT NULL + ,version VARCHAR NOT NULL + ,action VARCHAR NOT NULL + ,partitioned BOOL NOT NULL + ,by_range BOOL NOT NULL); +COMMENT ON TABLE exchange_tables + IS 'Tables of the exchange and their status'; +COMMENT ON COLUMN exchange_tables.name + IS 'Base name of the table (without partition/shard)'; +COMMENT ON COLUMN exchange_tables.version + IS 'Version of the DB in which the given action happened'; +COMMENT ON COLUMN exchange_tables.action + IS 'Action to take on the table (e.g. create, alter, constrain, unconstrain, or drop)'; +COMMENT ON COLUMN exchange_tables.partitioned + IS 'TRUE if the table is partitioned'; +COMMENT ON COLUMN exchange_tables.by_range + IS 'TRUE if the table is partitioned by range'; + + +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) + VALUES + ('denominations', 'exchange-0001', 'create', FALSE, FALSE) + ,('denomination_revocations', 'exchange-0001', 'create', FALSE, FALSE) + ,('kyc_alerts', 'exchange-0001', 'create', FALSE, FALSE) + ,('profit_drains', 'exchange-0001', 'create', FALSE, FALSE) + ,('auditors', 'exchange-0001', 'create', FALSE, FALSE) + ,('auditor_denom_sigs', 'exchange-0001', 'create', FALSE, FALSE) + ,('exchange_sign_keys', 'exchange-0001', 'create', FALSE, FALSE) + ,('signkey_revocations', 'exchange-0001', 'create', FALSE, FALSE) + ,('extensions', 'exchange-0001', 'create', FALSE, FALSE) + ,('wire_fee', 'exchange-0001', 'create', FALSE, FALSE) + ,('global_fee', 'exchange-0001', 'create', FALSE, FALSE) + ,('wire_accounts', 'exchange-0001', 'create', FALSE, FALSE) + ,('work_shards', 'exchange-0001', 'create', FALSE, FALSE) + ,('revolving_work_shards', 'exchange-0001', 'create', FALSE, FALSE) + ,('partners', 'exchange-0001', 'create', FALSE, FALSE) + ,('partner_accounts', 'exchange-0001', 'create', FALSE, FALSE) + ,('purse_actions', 'exchange-0001', 'create', FALSE, FALSE) + ,('policy_fulfillments', 'exchange-0001', 'create', FALSE, FALSE) -- bad! + ,('policy_details', 'exchange-0001', 'create', FALSE, FALSE) -- bad! + ,('wire_targets''exchange-0001', 'create', TRUE, FALSE) + ,('legitimization_processes', 'exchange-0001', 'create', TRUE, FALSE) + ,('legitimization_requirements', 'exchange-0001', 'create', TRUE, FALSE) + ,('reserves', 'exchange-0001', 'create', TRUE, FALSE) + ,('reserves_in', 'exchange-0001', 'create', TRUE, FALSE) + ,('reserves_close', 'exchange-0001', 'create', TRUE, FALSE) + ,('reserves_open_requests', 'exchange-0001', 'create', TRUE, FALSE) + ,('reserves_open_deposits', 'exchange-0001', 'create', TRUE, FALSE) + ,('reserves_out', 'exchange-0001', 'create', TRUE, FALSE) + ,('reserves_out_by_reserve', 'exchange-0001', 'create', TRUE, FALSE) + ,('known_coins', 'exchange-0001', 'create', TRUE, FALSE) + ,('refresh_commitments', 'exchange-0001', 'create', TRUE, FALSE) + ,('refresh_revealed_coins', 'exchange-0001', 'create', TRUE, FALSE) + ,('refresh_transfer_keys', 'exchange-0001', 'create', TRUE, FALSE) + ,('refunds', 'exchange-0001', 'create', TRUE, FALSE) + ,('deposits', 'exchange-0001', 'create', TRUE, FALSE) + ,('deposits_by_ready', 'exchange-0001', 'create', TRUE, TRUE) + ,('deposits_for_matching', 'exchange-0001', 'create', TRUE, TRUE) + ,('wire_out', 'exchange-0001', 'create', TRUE, FALSE) + ,('aggregation_transient', 'exchange-0001', 'create', TRUE, FALSE) + ,('aggregation_tracking', 'exchange-0001', 'create', TRUE, FALSE) + ,('recoup', 'exchange-0001', 'create', TRUE, FALSE) + ,('recoup_by_reserve', 'exchange-0001', 'create', TRUE, FALSE) + ,('recoup_refresh', 'exchange-0001', 'create', TRUE, FALSE) + ,('prewire', 'exchange-0001', 'create', TRUE, FALSE) + ,('cs_nonce_locks', 'exchange-0001', 'create', TRUE, FALSE) + ,('purse_requests', 'exchange-0001', 'create', TRUE, FALSE) + ,('purse_decision', 'exchange-0001', 'create', TRUE, FALSE) + ,('purse_merges', 'exchange-0001', 'create', TRUE, FALSE) + ,('account_merges', 'exchange-0001', 'create', TRUE, FALSE) + ,('contracts', 'exchange-0001', 'create', TRUE, FALSE) + ,('history_requests', 'exchange-0001', 'create', TRUE, FALSE) + ,('close_requests', 'exchange-0001', 'create', TRUE, FALSE) + ,('purse_deposists', 'exchange-0001', 'create', TRUE, FALSE) + ,('wads_out', 'exchange-0001', 'create', TRUE, FALSE) + ,('wads_out_entries', 'exchange-0001', 'create', TRUE, FALSE) + ,('wads_in', 'exchange-0001', 'create', TRUE, FALSE) + ,('wads_in_entries', 'exchange-0001', 'create', TRUE, FALSE) + ON CONFLICT DO NOTHING; + + + -------------------- Tables ---------------------------- -CREATE OR REPLACE FUNCTION create_table_wire_targets( +CREATE OR REPLACE FUNCTION create_table_purse_deletion( IN shard_suffix VARCHAR DEFAULT NULL ) RETURNS VOID LANGUAGE plpgsql AS $$ BEGIN - PERFORM create_partitioned_table( 'CREATE TABLE IF NOT EXISTS %I' - '(wire_target_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' - ',wire_target_h_payto BYTEA PRIMARY KEY CHECK (LENGTH(wire_target_h_payto)=32)' - ',payto_uri VARCHAR NOT NULL' + '(purse_deletion_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE' + ',purse_sig BYTEA CHECK (LENGTH(purse_sig)=64)' + ',XXX VARCHAR NOT NULL' ') %s ;' - ,'wire_targets' - ,'PARTITION BY HASH (wire_target_h_payto)' + ,'purse_deletion' + ,'PARTITION BY HASH (XXX)' ,shard_suffix ); - + COMMENT ON TABLE purse_deletion + IS 'signatures affirming explicit purse deletions'; + COMMENT ON COLUMN purse_deletion.purse_sig + IS 'signature of type XXX'; END $$; +COMMENT ON FUNCTION create_table_purse_deletion + IS 'Creates the purse_deletion table'; --- We need a separate function for this, as we call create_table only once but need to add --- those constraints to each partition which gets created -CREATE OR REPLACE FUNCTION add_constraints_to_wire_targets_partition( +CREATE OR REPLACE FUNCTION constrain_table_purse_deletion( IN partition_suffix VARCHAR ) RETURNS void LANGUAGE plpgsql AS $$ BEGIN - EXECUTE FORMAT ( - 'ALTER TABLE wire_targets_' || partition_suffix || ' ' - 'ADD CONSTRAINT wire_targets_' || partition_suffix || '_wire_target_serial_id_key ' - 'UNIQUE (wire_target_serial_id)' + 'ALTER TABLE purse_deletion_' || partition_suffix || ' ' + 'ADD CONSTRAINT purse_deletion_' || partition_suffix || '_XXX ' + 'UNIQUE (XXX)' ); END $$; - -CREATE OR REPLACE FUNCTION detach_default_partitions2() - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - - RAISE NOTICE 'Detaching all default table partitions'; - - ALTER TABLE IF EXISTS wire_targets - DETACH PARTITION wire_targets_default; - -END -$$; - -COMMENT ON FUNCTION detach_default_partitions2 - IS 'We need to drop default and create new one before deleting the default partitions - otherwise constraints get lost too. Might be needed in sharding too'; - - -CREATE OR REPLACE FUNCTION drop_default_partitions2() - RETURNS VOID - LANGUAGE plpgsql -AS $$ -BEGIN - - RAISE NOTICE 'Dropping default table partitions'; - - DROP TABLE IF EXISTS wire_targets_default; -END -$$; - -COMMENT ON FUNCTION drop_default_partitions2 - IS 'Drop all default partitions once other partitions are attached. - Might be needed in sharding too.'; - - -CREATE OR REPLACE FUNCTION create_partitions2( - num_partitions INTEGER -) -RETURNS VOID -LANGUAGE plpgsql -AS $$ -DECLARE - modulus INTEGER; -BEGIN - - modulus := num_partitions; - - PERFORM detach_default_partitions2(); - - LOOP - - PERFORM create_hash_partition( - 'wire_targets' - ,modulus - ,num_partitions - ); - PERFORM add_constraints_to_wire_targets_partition(num_partitions::varchar); - - END LOOP; - - PERFORM drop_default_partitions2(); - -END -$$; - - -CREATE OR REPLACE FUNCTION prepare_sharding2() -RETURNS VOID -LANGUAGE plpgsql -AS $$ -BEGIN - - PERFORM detach_default_partitions2(); - - ALTER TABLE IF EXISTS wire_targets - DROP CONSTRAINT IF EXISTS wire_targets_pkey CASCADE - ; - -END -$$; - - -CREATE OR REPLACE FUNCTION create_shard_server2( - shard_suffix VARCHAR - ,total_num_shards INTEGER - ,current_shard_num INTEGER - ,remote_host VARCHAR - ,remote_user VARCHAR - ,remote_user_password VARCHAR - ,remote_db_name VARCHAR DEFAULT 'taler-exchange' - ,remote_port INTEGER DEFAULT '5432' - ,local_user VARCHAR DEFAULT 'taler-exchange-httpd' +CREATE OR REPLACE FUNCTION alter_create_was_deleted_table_purse_requests ( + IN partition_suffix VARCHAR ) -RETURNS VOID +RETURNS void LANGUAGE plpgsql AS $$ BEGIN - - RAISE NOTICE 'Creating server %', remote_host; - - PERFORM create_foreign_hash_partition( - 'wire_targets' - ,total_num_shards - ,shard_suffix - ,current_shard_num - ,local_user + EXECUTE FORMAT ( + 'ALTER TABLE purse_requests_' || partition_suffix || + ' ADD COLUMN' + ' was_deleted BOOLEAN NOT NULL DEFAULT(FALSE)' ); END $$; -COMMENT ON FUNCTION create_shard_server2 - IS 'Create a shard server on the master - node with all foreign tables and user mappings'; - - - --------------------------------- - -CREATE TABLE IF NOT EXISTS partitioned_tables - (name VARCHAR PRIMARY KEY NOT NULL); - -INSERT INTO partitioned_tables - (name) +INSERT INTO exchange_tables + (name + ,version + ,action + ,partitioned + ,by_range) VALUES - ('wire_targets') - ,('refunds') - ON CONFLICT DO NOTHING; - - -CREATE OR REPLACE FUNCTION drop_default_partitions() - RETURNS VOID - LANGUAGE plpgsql -AS $$ -DECLARE - tc CURSOR FOR SELECT name FROM partitioned_tables; -BEGIN - - RAISE NOTICE 'Dropping default table partitions'; - FOR rec IN tc - LOOP - EXECUTE FORMAT ( - 'DROP TABLE IF EXISTS %s_default ;'::text, - rec.name; -END -$$; + ('purse_deletion' + ,'exchange-0002' + ,'create' + ,TRUE + ,FALSE), + ('purse_deletion' + ,'exchange-0002' + ,'constrain' + ,TRUE + ,FALSE), + ('purse_requests' + ,'exchange-0002' + ,'alter_create_was_deleted' + ,TRUE + ,FALSE); |