From 58eb3d95fceb0742bed029944d49b8096ee390c2 Mon Sep 17 00:00:00 2001
From: Marco Boss <bossm8@bfh.ch>
Date: Sun, 27 Feb 2022 21:23:20 +0100
Subject: towards adding shard/partitioning init functionality

---
 src/exchangedb/exchange-0001.sql | 922 ++++++++++++++++++++++++++++++++-------
 1 file changed, 761 insertions(+), 161 deletions(-)

(limited to 'src/exchangedb')

diff --git a/src/exchangedb/exchange-0001.sql b/src/exchangedb/exchange-0001.sql
index 184d62a7c..baf0056b0 100644
--- a/src/exchangedb/exchange-0001.sql
+++ b/src/exchangedb/exchange-0001.sql
@@ -96,15 +96,56 @@ CREATE INDEX IF NOT EXISTS wire_targets_serial_id_index
   (wire_target_serial_id
   );
 
-CREATE TABLE IF NOT EXISTS reserves
-  (reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY
-  ,reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)
-  ,current_balance_val INT8 NOT NULL
-  ,current_balance_frac INT4 NOT NULL
-  ,expiration_date INT8 NOT NULL
-  ,gc_date INT8 NOT NULL
-  )
-  PARTITION BY HASH (reserve_pub);
+
+CREATE OR REPLACE FUNCTION create_table_reserves(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'reserves'
+  ELSE 
+    'reserves_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (reserve_pub);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(reserve_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY'
+      ',reserve_pub BYTEA PRIMARY KEY CHECK(LENGTH(reserve_pub)=32)'
+      ',current_balance_val INT8 NOT NULL'
+      ',current_balance_frac INT4 NOT NULL'
+      ',expiration_date INT8 NOT NULL'
+      ',gc_date INT8 NOT NULL'
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_reserves
+  IS 'Create the reserves table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_reserves();
+
 COMMENT ON TABLE reserves
   IS 'Summarizes the balance of a reserve. Updated when new funds are added or withdrawn.';
 COMMENT ON COLUMN reserves.reserve_pub
@@ -137,17 +178,57 @@ COMMENT ON INDEX reserves_by_gc_date_index
   IS 'for reserve garbage collection';
 
 
-CREATE TABLE IF NOT EXISTS reserves_in
-  (reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,reserve_pub BYTEA PRIMARY KEY REFERENCES reserves (reserve_pub) ON DELETE CASCADE
-  ,wire_reference INT8 NOT NULL
-  ,credit_val INT8 NOT NULL
-  ,credit_frac INT4 NOT NULL
-  ,wire_source_serial_id INT8 NOT NULL -- REFERENCES wire_targets (wire_target_serial_id)
-  ,exchange_account_section TEXT NOT NULL
-  ,execution_date INT8 NOT NULL
-  )
-  PARTITION BY HASH (reserve_pub);
+CREATE OR REPLACE FUNCTION create_table_reserves_in(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'reserves_in'
+  ELSE 
+    'reserves_in_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (reserve_pub);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(reserve_in_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+      ',reserve_pub BYTEA PRIMARY KEY REFERENCES reserves (reserve_pub) ON DELETE CASCADE'
+      ',wire_reference INT8 NOT NULL'
+      ',credit_val INT8 NOT NULL'
+      ',credit_frac INT4 NOT NULL'
+      ',wire_source_serial_id INT8 NOT NULL' -- REFERENCES wire_targets (wire_target_serial_id)'
+      ',exchange_account_section TEXT NOT NULL'
+      ',execution_date INT8 NOT NULL'
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_reserves_in
+  IS 'Create the reserves_in table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_reserves_in();
+
 COMMENT ON TABLE reserves_in
   IS 'list of transfers of funds into the reserves, one per incoming wire transfer';
 COMMENT ON COLUMN reserves_in.wire_source_serial_id
@@ -175,17 +256,58 @@ CREATE INDEX IF NOT EXISTS reserves_in_by_exchange_account_reserve_in_serial_id_
   );
 
 
-CREATE TABLE IF NOT EXISTS reserves_close
-  (close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE / PRIMARY KEY
-  ,reserve_pub BYTEA NOT NULL REFERENCES reserves (reserve_pub) ON DELETE CASCADE
-  ,execution_date INT8 NOT NULL
-  ,wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)
-  ,wire_target_serial_id INT8 NOT NULL -- REFERENCES wire_targets (wire_target_serial_id)
-  ,amount_val INT8 NOT NULL
-  ,amount_frac INT4 NOT NULL
-  ,closing_fee_val INT8 NOT NULL
-  ,closing_fee_frac INT4 NOT NULL)
-  PARTITION BY HASH (reserve_pub);
+CREATE OR REPLACE FUNCTION create_table_reserves_close(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'reserves_close'
+  ELSE 
+    'reserves_close_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (reserve_pub);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(close_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY'
+      ',reserve_pub BYTEA NOT NULL REFERENCES reserves (reserve_pub) ON DELETE CASCADE'
+      ',execution_date INT8 NOT NULL'
+      ',wtid BYTEA NOT NULL CHECK (LENGTH(wtid)=32)'
+      ',wire_target_serial_id INT8 NOT NULL' -- REFERENCES wire_targets (wire_target_serial_id)'
+      ',amount_val INT8 NOT NULL'
+      ',amount_frac INT4 NOT NULL'
+      ',closing_fee_val INT8 NOT NULL'
+      ',closing_fee_frac INT4 NOT NULL'
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_reserves_close
+  IS 'Create the reserves_close table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_reserves_close();
+
 COMMENT ON TABLE reserves_close
   IS 'wire transfers executed by the reserve to close reserves';
 COMMENT ON COLUMN reserves_close.wire_target_serial_id
@@ -202,18 +324,58 @@ CREATE INDEX IF NOT EXISTS reserves_close_by_reserve_pub_index
   (reserve_pub);
 
 
-CREATE TABLE IF NOT EXISTS reserves_out
-  (reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE
-  ,denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial)
-  ,denom_sig BYTEA NOT NULL
-  ,reserve_uuid INT8 NOT NULL -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE
-  ,reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)
-  ,execution_date INT8 NOT NULL
-  ,amount_with_fee_val INT8 NOT NULL
-  ,amount_with_fee_frac INT4 NOT NULL
-  )
-  PARTITION BY HASH (h_blind_ev);
+CREATE OR REPLACE FUNCTION create_table_reserves_out(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'reserves_out'
+  ELSE 
+    'reserves_out_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (h_blind_ev);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(reserve_out_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+      ',h_blind_ev BYTEA CHECK (LENGTH(h_blind_ev)=64) UNIQUE'
+      ',denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial)'
+      ',denom_sig BYTEA NOT NULL'
+      ',reserve_uuid INT8 NOT NULL' -- REFERENCES reserves (reserve_uuid) ON DELETE CASCADE'
+      ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
+      ',execution_date INT8 NOT NULL'
+      ',amount_with_fee_val INT8 NOT NULL'
+      ',amount_with_fee_frac INT4 NOT NULL'
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_reserves_out
+  IS 'Create the reserves_out table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_reserves_out();
+
 COMMENT ON TABLE reserves_out
   IS 'Withdraw operations performed on reserves.';
 COMMENT ON COLUMN reserves_out.h_blind_ev
@@ -315,16 +477,56 @@ COMMENT ON COLUMN extensions.config
   IS 'Configuration of the extension as JSON-blob, maybe NULL';
 
 
-CREATE TABLE IF NOT EXISTS known_coins
-  (known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) ON DELETE CASCADE
-  ,coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)
-  ,age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)
-  ,denom_sig BYTEA NOT NULL
-  ,remaining_val INT8 NOT NULL
-  ,remaining_frac INT4 NOT NULL
-  )
-  PARTITION BY HASH (coin_pub); -- FIXME: or include denominations_serial? or multi-level partitioning?
+CREATE OR REPLACE FUNCTION create_table_known_coins(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'known_coins'
+  ELSE 
+    'known_coins_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (coin_pub);' -- FIXME: or include denominations_serial? or multi-level partitioning?
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(known_coin_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+      ',denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) ON DELETE CASCADE'
+      ',coin_pub BYTEA NOT NULL PRIMARY KEY CHECK (LENGTH(coin_pub)=32)'
+      ',age_commitment_hash BYTEA CHECK (LENGTH(age_commitment_hash)=32)'
+      ',denom_sig BYTEA NOT NULL'
+      ',remaining_val INT8 NOT NULL'
+      ',remaining_frac INT4 NOT NULL'
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_known_coins
+  IS 'Create the known_coins table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_known_coins();
+
 COMMENT ON TABLE known_coins
   IS 'information about coins and their signatures, so we do not have to store the signatures more than once if a coin is involved in multiple operations';
 COMMENT ON COLUMN known_coins.denominations_serial
@@ -346,17 +548,57 @@ CREATE INDEX IF NOT EXISTS known_coins_by_known_coin_id_index
   (known_coin_id);
 
 
-CREATE TABLE IF NOT EXISTS refresh_commitments
-  (melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)
-  ,old_coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE CASCADE
-  ,h_age_commitment BYTEA CHECK(LENGTH(h_age_commitment)=32)
-  ,old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)
-  ,amount_with_fee_val INT8 NOT NULL
-  ,amount_with_fee_frac INT4 NOT NULL
-  ,noreveal_index INT4 NOT NULL
-  )
-  PARTITION BY HASH (rc);
+CREATE OR REPLACE FUNCTION create_table_refresh_commitments(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'refresh_commitments'
+  ELSE 
+    'refresh_commitments_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (rc);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(melt_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+      ',rc BYTEA PRIMARY KEY CHECK (LENGTH(rc)=64)'
+      ',old_coin_pub BYTEA NOT NULL REFERENCES known_coins (coin_pub) ON DELETE CASCADE'
+      ',h_age_commitment BYTEA CHECK(LENGTH(h_age_commitment)=32)'
+      ',old_coin_sig BYTEA NOT NULL CHECK(LENGTH(old_coin_sig)=64)'
+      ',amount_with_fee_val INT8 NOT NULL'
+      ',amount_with_fee_frac INT4 NOT NULL'
+      ',noreveal_index INT4 NOT NULL'
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_refresh_commitments
+  IS 'Create the refresh_commitments table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_refresh_commitments();
+
 COMMENT ON TABLE refresh_commitments
   IS 'Commitments made when melting coins and the gamma value chosen by the exchange.';
 COMMENT ON COLUMN refresh_commitments.noreveal_index
@@ -379,19 +621,59 @@ CREATE INDEX IF NOT EXISTS refresh_commitments_by_old_coin_pub_index
   (old_coin_pub);
 
 
-CREATE TABLE IF NOT EXISTS refresh_revealed_coins
-  (rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,melt_serial_id INT8 NOT NULL -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE
-  ,freshcoin_index INT4 NOT NULL
-  ,link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)
-  ,denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) ON DELETE CASCADE
-  ,coin_ev BYTEA NOT NULL -- UNIQUE
-  ,h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64) -- UNIQUE
-  ,ev_sig BYTEA NOT NULL
-  ,ewv BYTEA NOT NULL
+CREATE OR REPLACE FUNCTION create_table_refresh_revealed_coins(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'refresh_revealed_coins'
+  ELSE 
+    'refresh_revealed_coins_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (melt_serial_id);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(rrc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+      ',melt_serial_id INT8 NOT NULL' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE'
+      ',freshcoin_index INT4 NOT NULL'
+      ',link_sig BYTEA NOT NULL CHECK(LENGTH(link_sig)=64)'
+      ',denominations_serial INT8 NOT NULL REFERENCES denominations (denominations_serial) ON DELETE CASCADE'
+      ',coin_ev BYTEA NOT NULL' -- UNIQUE'
+      ',h_coin_ev BYTEA NOT NULL CHECK(LENGTH(h_coin_ev)=64)' -- UNIQUE'
+      ',ev_sig BYTEA NOT NULL'
+      ',ewv BYTEA NOT NULL'
   --  ,PRIMARY KEY (melt_serial_id, freshcoin_index) -- done per shard
-  )
-  PARTITION BY HASH (melt_serial_id);
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_refresh_revealed_coins
+  IS 'Create the refresh_revealed_coins table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_refresh_revealed_coins();
+
 COMMENT ON TABLE refresh_revealed_coins
   IS 'Revelations about the new coins that are to be created during a melting session.';
 COMMENT ON COLUMN refresh_revealed_coins.rrc_serial
@@ -422,14 +704,53 @@ CREATE INDEX IF NOT EXISTS refresh_revealed_coins_by_melt_serial_id_index
   ON refresh_revealed_coins
   (melt_serial_id);
 
+CREATE OR REPLACE FUNCTION create_table_refresh_transfer_keys(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'refresh_transfer_keys'
+  ELSE 
+    'refresh_transfer_keys_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (melt_serial_id);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+      ',melt_serial_id INT8 PRIMARY KEY' -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE'
+      ',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)'
+      ',transfer_privs BYTEA NOT NULL'
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_refresh_transfer_keys
+  IS 'Create the refresh_transfer_keys table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_refresh_transfer_keys();
 
-CREATE TABLE IF NOT EXISTS refresh_transfer_keys
-  (rtc_serial BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,melt_serial_id INT8 PRIMARY KEY -- REFERENCES refresh_commitments (melt_serial_id) ON DELETE CASCADE
-  ,transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32)
-  ,transfer_privs BYTEA NOT NULL
-  )
-  PARTITION BY HASH (melt_serial_id);
 COMMENT ON TABLE refresh_transfer_keys
   IS 'Transfer keys of a refresh operation (the data revealed to the exchange).';
 COMMENT ON COLUMN refresh_transfer_keys.rtc_serial
@@ -458,28 +779,68 @@ COMMENT ON COLUMN extension_details.extension_options
   IS 'JSON object with options set that the exchange needs to consider when executing a deposit. Supported details depend on the extensions supported by the exchange.';
 
 
-CREATE TABLE IF NOT EXISTS deposits
-  (deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
-  ,shard INT8 NOT NULL
-  ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE
-  ,amount_with_fee_val INT8 NOT NULL
-  ,amount_with_fee_frac INT4 NOT NULL
-  ,wallet_timestamp INT8 NOT NULL
-  ,exchange_timestamp INT8 NOT NULL
-  ,refund_deadline INT8 NOT NULL
-  ,wire_deadline INT8 NOT NULL
-  ,merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)
-  ,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
-  ,coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)
-  ,wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)
-  ,wire_target_serial_id INT8 NOT NULL -- REFERENCES wire_targets (wire_target_serial_id)
-  ,tiny BOOLEAN NOT NULL DEFAULT FALSE
-  ,done BOOLEAN NOT NULL DEFAULT FALSE
-  ,extension_blocked BOOLEAN NOT NULL DEFAULT FALSE
-  ,extension_details_serial_id INT8 REFERENCES extension_details (extension_details_serial_id) ON DELETE CASCADE
-  ,UNIQUE (shard, known_coin_id, merchant_pub, h_contract_terms)
-  )
-  PARTITION BY HASH (shard);
+CREATE OR REPLACE FUNCTION create_table_deposits(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'deposits'
+  ELSE 
+    'deposits_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (shard);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(deposit_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY'
+      ',shard INT8 NOT NULL'
+      ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id) ON DELETE CASCADE'
+      ',amount_with_fee_val INT8 NOT NULL'
+      ',amount_with_fee_frac INT4 NOT NULL'
+      ',wallet_timestamp INT8 NOT NULL'
+      ',exchange_timestamp INT8 NOT NULL'
+      ',refund_deadline INT8 NOT NULL'
+      ',wire_deadline INT8 NOT NULL'
+      ',merchant_pub BYTEA NOT NULL CHECK (LENGTH(merchant_pub)=32)'
+      ',h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)'
+      ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)'
+      ',wire_salt BYTEA NOT NULL CHECK (LENGTH(wire_salt)=16)'
+      ',wire_target_serial_id INT8 NOT NULL' -- REFERENCES wire_targets (wire_target_serial_id)'
+      ',tiny BOOLEAN NOT NULL DEFAULT FALSE'
+      ',done BOOLEAN NOT NULL DEFAULT FALSE'
+      ',extension_blocked BOOLEAN NOT NULL DEFAULT FALSE'
+      ',extension_details_serial_id INT8 REFERENCES extension_details (extension_details_serial_id) ON DELETE CASCADE'
+      ',UNIQUE (shard, known_coin_id, merchant_pub, h_contract_terms)'
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_deposits
+  IS 'Create the deposits table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_deposits();
+
 CREATE TABLE IF NOT EXISTS deposits_default
   PARTITION OF deposits
   FOR VALUES WITH (MODULUS 1, REMAINDER 0);
@@ -530,16 +891,56 @@ COMMENT ON INDEX deposits_for_iterate_matching_index
   IS 'for deposits_iterate_matching';
 
 
-CREATE TABLE IF NOT EXISTS refunds
-  (refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,deposit_serial_id INT8 NOT NULL -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE
-  ,merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)
-  ,rtransaction_id INT8 NOT NULL
-  ,amount_with_fee_val INT8 NOT NULL
-  ,amount_with_fee_frac INT4 NOT NULL
-  -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard!
-  )
-  PARTITION BY HASH (deposit_serial_id);
+CREATE OR REPLACE FUNCTION create_table_refunds(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'refunds'
+  ELSE 
+    'refunds_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (deposit_serial_id);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(refund_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+      ',deposit_serial_id INT8 NOT NULL' -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE'
+      ',merchant_sig BYTEA NOT NULL CHECK(LENGTH(merchant_sig)=64)'
+      ',rtransaction_id INT8 NOT NULL'
+      ',amount_with_fee_val INT8 NOT NULL'
+      ',amount_with_fee_frac INT4 NOT NULL'
+      -- ,PRIMARY KEY (deposit_serial_id, rtransaction_id) -- done per shard!
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_refunds
+  IS 'Create the refunds table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_refunds();
+
 COMMENT ON TABLE refunds
   IS 'Data on coins that were refunded. Technically, refunds always apply against specific deposit operations involving a coin. The combination of coin_pub, merchant_pub, h_contract_terms and rtransaction_id MUST be unique, and we usually select by coin_pub so that one goes first.';
 COMMENT ON COLUMN refunds.deposit_serial_id
@@ -557,16 +958,56 @@ CREATE INDEX IF NOT EXISTS refunds_by_refund_serial_id_index
   (refund_serial_id);
 
 
-CREATE TABLE IF NOT EXISTS wire_out
-  (wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- PRIMARY KEY
-  ,execution_date INT8 NOT NULL
-  ,wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)
-  ,wire_target_serial_id INT8 NOT NULL -- REFERENCES wire_targets (wire_target_serial_id)
-  ,exchange_account_section TEXT NOT NULL
-  ,amount_val INT8 NOT NULL
-  ,amount_frac INT4 NOT NULL
-  )
-  PARTITION BY HASH (wtid_raw);
+CREATE OR REPLACE FUNCTION create_table_wire_out(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'wire_out'
+  ELSE 
+    'wire_out_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (wtid_raw);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(wireout_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- PRIMARY KEY'
+      ',execution_date INT8 NOT NULL'
+      ',wtid_raw BYTEA UNIQUE NOT NULL CHECK (LENGTH(wtid_raw)=32)'
+      ',wire_target_serial_id INT8 NOT NULL' -- REFERENCES wire_targets (wire_target_serial_id)'
+      ',exchange_account_section TEXT NOT NULL'
+      ',amount_val INT8 NOT NULL'
+      ',amount_frac INT4 NOT NULL'
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_wire_out
+  IS 'Create the wire_out table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_wire_out();
+
 COMMENT ON TABLE wire_out
   IS 'wire transfers the exchange has executed';
 COMMENT ON COLUMN wire_out.exchange_account_section
@@ -585,13 +1026,52 @@ CREATE INDEX IF NOT EXISTS wire_out_by_wire_target_serial_id_index
   (wire_target_serial_id);
 
 
+CREATE OR REPLACE FUNCTION create_table_aggregation_tracking(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'aggregation_tracking'
+  ELSE 
+    'aggregation_tracking_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (deposit_serial_id);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    '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'
+      ',wtid_raw BYTEA NOT NULL CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE'
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_aggregation_tracking
+  IS 'Create the aggregation_tracking table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_aggregation_tracking();
 
-CREATE TABLE IF NOT EXISTS aggregation_tracking
-  (aggregation_serial_id BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,deposit_serial_id INT8 PRIMARY KEY -- REFERENCES deposits (deposit_serial_id) ON DELETE CASCADE
-  ,wtid_raw BYTEA NOT NULL CONSTRAINT wire_out_ref REFERENCES wire_out(wtid_raw) ON DELETE CASCADE DEFERRABLE
-  )
-  PARTITION BY HASH (deposit_serial_id);
 COMMENT ON TABLE aggregation_tracking
   IS 'mapping from wire transfer identifiers (WTID) to deposits (and back)';
 COMMENT ON COLUMN aggregation_tracking.wtid_raw
@@ -632,17 +1112,57 @@ CREATE INDEX IF NOT EXISTS wire_fee_by_end_date_index
   (end_date);
 
 
-CREATE TABLE IF NOT EXISTS recoup
-  (recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id)
-  ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
-  ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)
-  ,amount_val INT8 NOT NULL
-  ,amount_frac INT4 NOT NULL
-  ,recoup_timestamp INT8 NOT NULL
-  ,reserve_out_serial_id INT8 NOT NULL -- REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE
-  )
-  PARTITION BY HASH (known_coin_id);
+CREATE OR REPLACE FUNCTION create_table_recoup(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'recoup'
+  ELSE 
+    'recoup_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (known_coin_id);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(recoup_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+      ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id)'
+      ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
+      ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
+      ',amount_val INT8 NOT NULL'
+      ',amount_frac INT4 NOT NULL'
+      ',recoup_timestamp INT8 NOT NULL'
+      ',reserve_out_serial_id INT8 NOT NULL' -- REFERENCES reserves_out (reserve_out_serial_id) ON DELETE CASCADE'
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_recoup
+  IS 'Create the recoup table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_recoup();
+
 COMMENT ON TABLE recoup
   IS 'Information about recoups that were executed between a coin and a reserve. In this type of recoup, the amount is credited back to the reserve from which the coin originated.';
 COMMENT ON COLUMN recoup.known_coin_id
@@ -668,17 +1188,57 @@ CREATE INDEX IF NOT EXISTS recoup_by_known_coin_id_index
   (known_coin_id);
 
 
-CREATE TABLE IF NOT EXISTS recoup_refresh
-  (recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY -- UNIQUE
-  ,known_coin_id INT8 NOT NULL -- REFERENCES known_coins (known_coin_id)
-  ,coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)
-  ,coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)
-  ,amount_val INT8 NOT NULL
-  ,amount_frac INT4 NOT NULL
-  ,recoup_timestamp INT8 NOT NULL
-  ,rrc_serial INT8 NOT NULL -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE
-  )
-  PARTITION BY HASH (known_coin_id);
+CREATE OR REPLACE FUNCTION create_table_recoup_refresh(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'recoup_refresh'
+  ELSE 
+    'recoup_refresh_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (known_coin_id);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(recoup_refresh_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE'
+      ',known_coin_id INT8 NOT NULL' -- REFERENCES known_coins (known_coin_id)'
+      ',coin_sig BYTEA NOT NULL CHECK(LENGTH(coin_sig)=64)'
+      ',coin_blind BYTEA NOT NULL CHECK(LENGTH(coin_blind)=32)'
+      ',amount_val INT8 NOT NULL'
+      ',amount_frac INT4 NOT NULL'
+      ',recoup_timestamp INT8 NOT NULL'
+      ',rrc_serial INT8 NOT NULL' -- REFERENCES refresh_revealed_coins (rrc_serial) ON DELETE CASCADE -- UNIQUE'
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_recoup_refresh
+  IS 'Create the recoup_refresh table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_recoup_refresh();
+
 COMMENT ON TABLE recoup_refresh
   IS 'Table of coins that originated from a refresh operation and that were recouped. Links the (fresh) coin to the melted operation (and thus the old coin). A recoup on a refreshed coin credits the old coin and debits the fresh coin.';
 COMMENT ON COLUMN recoup_refresh.known_coin_id
@@ -702,14 +1262,54 @@ CREATE INDEX IF NOT EXISTS recoup_refresh_by_known_coin_id_index
   (known_coin_id);
 
 
-CREATE TABLE IF NOT EXISTS prewire
-  (prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
-  ,wire_method TEXT NOT NULL
-  ,finished BOOLEAN NOT NULL DEFAULT false
-  ,failed BOOLEAN NOT NULL DEFAULT false
-  ,buf BYTEA NOT NULL
-  )
-  PARTITION BY HASH (prewire_uuid);
+CREATE OR REPLACE FUNCTION create_table_prewire(
+  IN shard_suffix VARCHAR DEFAULT ''
+)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+  table_name VARCHAR;
+  partition_str VARCHAR;
+BEGIN
+
+  table_name = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'prewire'
+  ELSE 
+    'prewire_' || shard_suffix
+  END;
+
+  partition_str = CASE 
+  shard_suffix
+  WHEN '' THEN 
+    'PARTITION BY HASH (prewire_uuid);'
+  ELSE 
+    ';'
+  END;
+
+  EXECUTE FORMAT(
+    'CREATE TABLE IF NOT EXISTS %I'
+      '(prewire_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY'
+      ',wire_method TEXT NOT NULL'
+      ',finished BOOLEAN NOT NULL DEFAULT false'
+      ',failed BOOLEAN NOT NULL DEFAULT false'
+      ',buf BYTEA NOT NULL'
+      ') %s'
+      ,table_name
+      ,partition_str
+  );
+
+END
+$$;
+
+COMMENT ON FUNCTION create_table_prewire
+  IS 'Create the prewire table, if argument `shard_suffix` is empty, a partitioned master table
+      without partitions will be created. If not empty, a shard table will be created';
+
+SELECT create_table_prewire();
+
 COMMENT ON TABLE prewire
   IS 'pre-commit data for wire transfers we are about to execute';
 COMMENT ON COLUMN prewire.failed
-- 
cgit v1.2.3