diff options
author | Christian Grothoff <grothoff@gnunet.org> | 2022-11-24 12:23:55 +0100 |
---|---|---|
committer | Christian Grothoff <grothoff@gnunet.org> | 2022-11-24 12:23:55 +0100 |
commit | c2bb6551cf453115884d35e2c440fc44797addf2 (patch) | |
tree | eefaa1c62fb7c9556ad31b9766bfdcb04d875ea8 /src/exchangedb/common-0002.sql | |
parent | 0429b0cede718e3306e088993023dbe3b47c3382 (diff) | |
download | exchange-c2bb6551cf453115884d35e2c440fc44797addf2.tar.xz |
starting point for NG exchange DB schema
Diffstat (limited to 'src/exchangedb/common-0002.sql')
-rw-r--r-- | src/exchangedb/common-0002.sql | 174 |
1 files changed, 174 insertions, 0 deletions
diff --git a/src/exchangedb/common-0002.sql b/src/exchangedb/common-0002.sql new file mode 100644 index 000000000..a9c9cd1fc --- /dev/null +++ b/src/exchangedb/common-0002.sql @@ -0,0 +1,174 @@ +-- +-- This file is part of TALER +-- Copyright (C) 2014--2022 Taler Systems SA +-- +-- TALER is free software; you can redistribute it and/or modify it under the +-- terms of the GNU General Public License as published by the Free Software +-- Foundation; either version 3, or (at your option) any later version. +-- +-- TALER is distributed in the hope that it will be useful, but WITHOUT ANY +-- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +-- A PARTICULAR PURPOSE. See the GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License along with +-- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> +-- + +-------------------- Tables ---------------------------- + +CREATE OR REPLACE FUNCTION create_table_wire_targets( + 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' + ') %s ;' + ,'wire_targets' + ,'PARTITION BY HASH (wire_target_h_payto)' + ,shard_suffix + ); + +END +$$; + +-- 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( + 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)' + ); +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' +) +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 + ); +END +$$; + +COMMENT ON FUNCTION create_shard_server2 + IS 'Create a shard server on the master + node with all foreign tables and user mappings'; + |