starting point for NG exchange DB schema

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';
+