From c2bb6551cf453115884d35e2c440fc44797addf2 Mon Sep 17 00:00:00 2001 From: Christian Grothoff Date: Thu, 24 Nov 2022 12:23:55 +0100 Subject: starting point for NG exchange DB schema --- src/exchangedb/.gitignore | 2 + src/exchangedb/Makefile.am | 21 +++- src/exchangedb/common-0002.sql | 174 ++++++++++++++++++++++++++ src/exchangedb/exchange-0002-part.sql | 33 +++++ src/exchangedb/exchange-0002.sql.in | 30 +++++ src/exchangedb/shard-0002-part.sql | 31 +++++ src/exchangedb/shard-0002.sql.in | 33 +++++ src/exchangedb/test-exchange-db-postgres.conf | 4 +- 8 files changed, 324 insertions(+), 4 deletions(-) create mode 100644 src/exchangedb/common-0002.sql create mode 100644 src/exchangedb/exchange-0002-part.sql create mode 100644 src/exchangedb/exchange-0002.sql.in create mode 100644 src/exchangedb/shard-0002-part.sql create mode 100644 src/exchangedb/shard-0002.sql.in (limited to 'src/exchangedb') diff --git a/src/exchangedb/.gitignore b/src/exchangedb/.gitignore index 50f4e80a0..540724b83 100644 --- a/src/exchangedb/.gitignore +++ b/src/exchangedb/.gitignore @@ -10,3 +10,5 @@ shard-0000.sql shard-0001.sql drop0001.sql shard-drop0001.sqltest-exchangedb-by-j-postgres +test-exchangedb-by-j-postgres +perf-exchangedb-reserves-in-insert-postgres diff --git a/src/exchangedb/Makefile.am b/src/exchangedb/Makefile.am index 59aeb3212..043b438c7 100644 --- a/src/exchangedb/Makefile.am +++ b/src/exchangedb/Makefile.am @@ -19,7 +19,12 @@ sqlinputs = \ exchange-0001.sql.in \ exchange-0001-part.sql \ shard-0001-part.sql \ - shard-0001.sql.in + shard-0001.sql.in \ + common-0002.sql \ + exchange-0002.sql.in \ + exchange-0002-part.sql \ + shard-0002-part.sql \ + shard-0002.sql.in sql_DATA = \ benchmark-0001.sql \ @@ -37,18 +42,30 @@ BUILT_SOURCES = \ CLEANFILES = \ shard-0001.sql \ - exchange-0001.sql + exchange-0001.sql \ + shard-0002.sql \ + exchange-0002.sql exchange-0001.sql: common-0001.sql exchange-0001-part.sql exchange-0001.sql.in chmod +w $@ || true gcc -E -P -undef - < exchange-0001.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ chmod ugo-w $@ +exchange-0002.sql: common-0002.sql exchange-0002-part.sql exchange-0002.sql.in + chmod +w $@ || true + gcc -E -P -undef - < exchange-0002.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ + chmod ugo-w $@ + shard-0001.sql: common-0001.sql shard-0001-part.sql exchange-0001.sql.in chmod +w $@ || true gcc -E -P -undef - < shard-0001.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ chmod ugo-w $@ +shard-0002.sql: common-0002.sql shard-0002-part.sql exchange-0002.sql.in + chmod +w $@ || true + gcc -E -P -undef - < shard-0002.sql.in 2>/dev/null | sed -e "s/--.*//" | awk 'NF' - >$@ + chmod ugo-w $@ + EXTRA_DIST = \ exchangedb.conf \ exchangedb-postgres.conf \ 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 +-- + +-------------------- 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'; + diff --git a/src/exchangedb/exchange-0002-part.sql b/src/exchangedb/exchange-0002-part.sql new file mode 100644 index 000000000..1697a3755 --- /dev/null +++ b/src/exchangedb/exchange-0002-part.sql @@ -0,0 +1,33 @@ +-- +-- 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 +-- + +-- ------------------------------ wire_targets ---------------------------------------- + +SELECT create_table_wire_targets(); + +COMMENT ON TABLE wire_targets + IS 'All senders and recipients of money via the exchange'; +COMMENT ON COLUMN wire_targets.payto_uri + IS 'Can be a regular bank account, or also be a URI identifying a reserve-account (for P2P payments)'; +COMMENT ON COLUMN wire_targets.wire_target_h_payto + IS 'Unsalted hash of payto_uri'; + +CREATE TABLE IF NOT EXISTS wire_targets_default + PARTITION OF wire_targets + FOR VALUES WITH (MODULUS 1, REMAINDER 0); + +SELECT add_constraints_to_wire_targets_partition('default'); + diff --git a/src/exchangedb/exchange-0002.sql.in b/src/exchangedb/exchange-0002.sql.in new file mode 100644 index 000000000..b25555ce9 --- /dev/null +++ b/src/exchangedb/exchange-0002.sql.in @@ -0,0 +1,30 @@ +-- +-- 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 +-- + +-- Everything in one big transaction +BEGIN; + +-- Check patch versioning is in place. +SELECT _v.register_patch('exchange-0002', NULL, NULL); + +-------------------- Schema ---------------------------- + +SET search_path TO exchange; + +#include "common-0002.sql" +#include "exchange-0002-part.sql" + +COMMIT; diff --git a/src/exchangedb/shard-0002-part.sql b/src/exchangedb/shard-0002-part.sql new file mode 100644 index 000000000..439d672a6 --- /dev/null +++ b/src/exchangedb/shard-0002-part.sql @@ -0,0 +1,31 @@ +-- +-- 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 +-- + +CREATE OR REPLACE FUNCTION setup_shard2( + shard_idx INTEGER +) +RETURNS VOID +LANGUAGE plpgsql +AS $$ +DECLARE + shard_suffix VARCHAR; +BEGIN + + shard_suffix = shard_idx::varchar; + + PERFORM create_table_wire_targets(shard_suffix); +END +$$; diff --git a/src/exchangedb/shard-0002.sql.in b/src/exchangedb/shard-0002.sql.in new file mode 100644 index 000000000..552fe447f --- /dev/null +++ b/src/exchangedb/shard-0002.sql.in @@ -0,0 +1,33 @@ +-- +-- 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 +-- + +-- Everything in one big transaction +BEGIN; + +-- Check patch versioning is in place. +SELECT _v.register_patch('shard-0002', NULL, NULL); + +-------------------- Schema ---------------------------- + +CREATE SCHEMA exchange; +COMMENT ON SCHEMA exchange IS 'taler-exchange data'; + +SET search_path TO exchange; + +#include "common-0002.sql" +#include "shard-0002-part.sql" + +COMMIT; diff --git a/src/exchangedb/test-exchange-db-postgres.conf b/src/exchangedb/test-exchange-db-postgres.conf index 92bdde393..7f0332686 100644 --- a/src/exchangedb/test-exchange-db-postgres.conf +++ b/src/exchangedb/test-exchange-db-postgres.conf @@ -7,7 +7,7 @@ BASE_URL = http://localhost/ [exchangedb-postgres] #The connection string the plugin has to use for connecting to the database -CONFIG = postgres://dab:test@localhost/talercheck +CONFIG = postgres:///talercheck # Where are the SQL files to setup our tables? SQL_DIR = $DATADIR/sql/exchange/ @@ -33,4 +33,4 @@ LEGAL_RESERVE_EXPIRATION_TIME = 7 years AGGREGATOR_SHIFT = 1s # Number of purses per account by default. -DEFAULT_PURSE_LIMIT = 1 \ No newline at end of file +DEFAULT_PURSE_LIMIT = 1 -- cgit v1.2.3