[taler-grid5k] 03/189: fix table naming

[gnunet]

This is an automated email from the git hooks/post-receive script.

marco-boss pushed a commit to branch master
in repository grid5k.

commit 3e3ee65ace5bd64a5f51338b76d271dcfdc40633
Author: Boss Marco <bossm8@bfh.ch>
AuthorDate: Thu Feb 17 22:30:59 2022 +0100

    fix table naming
---
 experiment/scripts/database.sh | 15 +++++++------
 experiment/scripts/shard.sh    |  5 ++++-
 sql/exchange-0002.sql          | 51 ++++++++++++++++++++++--------------------
 sql/exchange-shard-0000.sql    | 34 ++++++++++++++--------------
 4 files changed, 56 insertions(+), 49 deletions(-)

diff --git a/experiment/scripts/database.sh b/experiment/scripts/database.sh
index c0f749b..e8bbdb0 100755
--- a/experiment/scripts/database.sh
+++ b/experiment/scripts/database.sh
@@ -146,12 +146,13 @@ EOF
   let "i=1"
   for SHOST in ${SHARD_HOSTS//|/ }; do
     su taler-exchange-httpd -s /bin/bash << EOF
-psql -tAc SELECT create_shard_server('${SHOST}', 
-                                     5432, 
-                                     '${DB_USER}', 
-                                     '${DB_PASSWORD}', 
-                                     ${NUM_SHARDS}, 
-                                     ${i}, 
+psql -tAc SELECT create_shard_server('${SHOST}',
+                                     5432,
+                                     '${DB_USER}',
+                                     '${DB_PASSWORD}',
+                                    '$(echo ${SHOST} | cut -d "." -f 2)',
+                                     ${NUM_SHARDS},
+                                     ${i},
                                      '${DB_NAME}');
 EOF
   let "i=i+1"
@@ -183,7 +184,7 @@ psql -tAc "SELECT 1 FROM pg_database WHERE 
datname='${DB_NAME}'" | \
 EOF
  
   echo "OVERRIDING EXCHANGE SQL INIT SCRIPT"
-  mv ${G5K_HOME}/sql/exchange-0001.sql /usr/share/taler/sql/exchange/
+  cp ${G5K_HOME}/sql/exchange-0001.sql /usr/share/taler/sql/exchange/
 
   sudo -u taler-exchange-httpd taler-exchange-dbinit -r || true
   sudo -u taler-exchange-httpd taler-exchange-dbinit -s || true
diff --git a/experiment/scripts/shard.sh b/experiment/scripts/shard.sh
index f7d0d2e..caa5eee 100755
--- a/experiment/scripts/shard.sh
+++ b/experiment/scripts/shard.sh
@@ -116,8 +116,11 @@ psql -tAc "SELECT 1 FROM pg_database WHERE 
datname='${DB_NAME}'" | \
   createdb -O "${DB_USER}" "${DB_NAME}"
 EOF
   
+  cp ${G5K_HOME}/sql/exchange-shard-0000.sql /tmp
+  chmod o+r /tmp/exchange-shard-0000.sql
   su postgres << EOF
-psql -tA -d ${DB_NAME} -f ${G5K_HOME}/sql/exchange-shard-0000.sql
+psql -tA -d ${DB_NAME} -f /tmp/exchange-shard-0000.sql
+psql -tAc -d ${DB_NAME} SELECT setup_shard_tables('$(hostname | cut -d "." -f 
1)');
 EOF
   
 }
diff --git a/sql/exchange-0002.sql b/sql/exchange-0002.sql
index 50b8b7c..5eff8b3 100644
--- a/sql/exchange-0002.sql
+++ b/sql/exchange-0002.sql
@@ -55,6 +55,7 @@ $$;
 CREATE OR REPLACE FUNCTION create_partition(
     source_table VARCHAR, 
     modulus INTEGER,
+    suffix VARCHAR,
     num INTEGER
   )
   RETURNS VOID
@@ -68,7 +69,7 @@ BEGIN
     'CREATE TABLE %I '
       'PARTITION OF %I '
       'FOR VALUES WITH (MODULUS %s, REMAINDER %s)',
-    source_table || '_' || num,
+    source_table || '_' || suffix,
     source_table,
     modulus,
     num-1
@@ -121,6 +122,7 @@ $$;
 CREATE OR REPLACE FUNCTION create_foreign_table(
     source_table VARCHAR, 
     modulus INTEGER,
+    suffix VARCHAR,
     num INTEGER
   )
   RETURNS VOID
@@ -128,18 +130,18 @@ CREATE OR REPLACE FUNCTION create_foreign_table(
 AS $$
 BEGIN
 
-  RAISE NOTICE 'Creating %_% on shard_%', source_table, num, num;
+  RAISE NOTICE 'Creating %_% on shard_%', source_table, suffix, suffix;
 
   EXECUTE FORMAT(
     'CREATE FOREIGN TABLE %I '
       'PARTITION OF %I '
       'FOR VALUES WITH (MODULUS %s, REMAINDER %s) '
       'SERVER %I',     
-    source_table || '_' || num,
+    source_table || '_' || suffix,
     source_table,
     modulus,
     num-1,
-    'shard_' || num
+    'shard_' || suffix
   );
 
 END
@@ -202,6 +204,7 @@ CREATE OR REPLACE FUNCTION create_shard_server(
     port INTEGER,
     usr VARCHAR,
     passw VARCHAR,
+    suffix VARCHAR,
     num_shards INTEGER,
     shard_idx INTEGER,
     db_name VARCHAR
@@ -211,42 +214,42 @@ CREATE OR REPLACE FUNCTION create_shard_server(
 AS $$
 BEGIN
 
-  RAISE NOTICE 'Creating server shard_%', shard_idx;
+  RAISE NOTICE 'Creating server shard_%', suffix;
 
   EXECUTE FORMAT(
     'CREATE SERVER IF NOT EXISTS %I '
       'FOREIGN DATA WRAPPER postgres_fdw '
       'OPTIONS (dbname %L, host %L, port %L)',
-    'shard_' || shard_idx,
+    'shard_' || suffix,
     db_name,
-    'shard_' || shard_idx,
+    host,
     port
   );
 
   EXECUTE FORMAT(
     'CREATE USER MAPPING FOR admin SERVER %I '
       'OPTIONS (user %L, password %L)',
-    'shard_' || shard_idx,
+    'shard_' || suffix,
     usr,
     passw
   );
 
-  PERFORM create_foreign_table('wire_targets', num_shards, shard_idx);
-  PERFORM create_foreign_table('reserves', num_shards, shard_idx);
-  PERFORM create_foreign_table('reserves_in', num_shards, shard_idx);
-  PERFORM create_foreign_table('reserves_close', num_shards, shard_idx);
-  PERFORM create_foreign_table('reserves_out', num_shards, shard_idx);
-  PERFORM create_foreign_table('known_coins', num_shards, shard_idx);
-  PERFORM create_foreign_table('refresh_commitments', num_shards, shard_idx);
-  PERFORM create_foreign_table('refresh_revealed_coins', num_shards, 
shard_idx);
-  PERFORM create_foreign_table('refresh_transfer_keys', num_shards, shard_idx);
-  PERFORM create_foreign_table('deposits', num_shards, shard_idx);
-  PERFORM create_foreign_table('refunds', num_shards, shard_idx);
-  PERFORM create_foreign_table('wire_out', num_shards, shard_idx);
-  PERFORM create_foreign_table('aggregation_tracking', num_shards, shard_idx);
-  PERFORM create_foreign_table('recoup', num_shards, shard_idx);
-  PERFORM create_foreign_table('recoup_refresh', num_shards, shard_idx);
-  PERFORM create_foreign_table('prewire', num_shards, shard_idx);
+  PERFORM create_foreign_table('wire_targets', num_shards, suffix, shard_idx);
+  PERFORM create_foreign_table('reserves', num_shards, suffix, shard_idx);
+  PERFORM create_foreign_table('reserves_in', num_shards, suffix, shard_idx);
+  PERFORM create_foreign_table('reserves_close', num_shards, suffix, 
shard_idx);
+  PERFORM create_foreign_table('reserves_out', num_shards, suffix, shard_idx);
+  PERFORM create_foreign_table('known_coins', num_shards, suffix, shard_idx);
+  PERFORM create_foreign_table('refresh_commitments', num_shards, suffix, 
shard_idx);
+  PERFORM create_foreign_table('refresh_revealed_coins', num_shards, suffix, 
shard_idx);
+  PERFORM create_foreign_table('refresh_transfer_keys', num_shards, suffix, 
shard_idx);
+  PERFORM create_foreign_table('deposits', num_shards, suffix, shard_idx);
+  PERFORM create_foreign_table('refunds', num_shards, suffix, shard_idx);
+  PERFORM create_foreign_table('wire_out', num_shards, suffix, shard_idx);
+  PERFORM create_foreign_table('aggregation_tracking', num_shards, suffix, 
shard_idx);
+  PERFORM create_foreign_table('recoup', num_shards, suffix, shard_idx);
+  PERFORM create_foreign_table('recoup_refresh', num_shards, suffix, 
shard_idx);
+  PERFORM create_foreign_table('prewire', num_shards, suffix, shard_idx);
 
 END
 $$;
diff --git a/sql/exchange-shard-0000.sql b/sql/exchange-shard-0000.sql
index eae858e..0f60418 100644
--- a/sql/exchange-shard-0000.sql
+++ b/sql/exchange-shard-0000.sql
@@ -1,4 +1,4 @@
-CREATE OR REPLACE FUNCTION setup_shard_tables(shard_num INTEGER)
+CREATE OR REPLACE FUNCTION setup_shard_tables(suffix INTEGER)
   RETURNS VOID
   LANGUAGE plpgsql
 AS $$
@@ -12,7 +12,7 @@ BEGIN
       ',kyc_ok BOOLEAN NOT NULL DEFAULT (FALSE) '
       ',external_id VARCHAR '
     ')',
-    'wire_targets_' || shard_num
+    'wire_targets_' || suffix
   );
 
   EXECUTE FORMAT(
@@ -24,7 +24,7 @@ BEGIN
       ',expiration_date INT8 NOT NULL '
       ',gc_date INT8 NOT NULL '
     ')',
-    'reserves_' || shard_num
+    'reserves_' || suffix
   );
 
   EXECUTE FORMAT(
@@ -38,7 +38,7 @@ BEGIN
       ',exchange_account_section TEXT NOT NULL '
       ',execution_date INT8 NOT NULL '
     ')',
-    'reserves_in_' || shard_num
+    'reserves_in_' || suffix
   );
 
   EXECUTE FORMAT(
@@ -53,7 +53,7 @@ BEGIN
       ',closing_fee_val INT8 NOT NULL '
       ',closing_fee_frac INT4 NOT NULL '
     ') ',
-    'reserves_close_' || shard_num 
+    'reserves_close_' || suffix 
   );
 
   EXECUTE FORMAT(
@@ -68,7 +68,7 @@ BEGIN
       ',amount_with_fee_val INT8 NOT NULL '
       ',amount_with_fee_frac INT4 NOT NULL '
     ')',
-    'reserves_out_' || shard_num
+    'reserves_out_' || suffix
   );
 
   EXECUTE FORMAT(
@@ -81,7 +81,7 @@ BEGIN
       ',remaining_val INT8 NOT NULL '
       ',remaining_frac INT4 NOT NULL '
     ')',
-    'kown_coins_' || shard_num 
+    'kown_coins_' || suffix 
   );
 
   EXECUTE FORMAT(
@@ -94,7 +94,7 @@ BEGIN
       ',amount_with_fee_frac INT4 NOT NULL '
       ',noreveal_index INT4 NOT NULL '
     ')',
-    'refresh_commitments_' || shard_num
+    'refresh_commitments_' || suffix
   );
   
   EXECUTE FORMAT(
@@ -110,7 +110,7 @@ BEGIN
       ',ewv BYTEA NOT NULL '
       ',PRIMARY KEY (melt_serial_id, freshcoin_index) '
     ')',
-    'refresh_revealed_coins_' || shard_num
+    'refresh_revealed_coins_' || suffix
   );
   
   EXECUTE FORMAT(
@@ -120,7 +120,7 @@ BEGIN
       ',transfer_pub BYTEA NOT NULL CHECK(LENGTH(transfer_pub)=32) '
       ',transfer_privs BYTEA NOT NULL '
     ')',
-    'refresh_transfer_keys_' || shard_num
+    'refresh_transfer_keys_' || suffix
   );
 
   EXECUTE FORMAT(
@@ -145,7 +145,7 @@ BEGIN
       ',extension_details_serial_id INT8 '
       ',UNIQUE (shard, known_coin_id, merchant_pub, h_contract_terms) '
     ')',
-    'deposits_' || shard_num
+    'deposits_' || suffix
   );
 
   EXECUTE FORMAT(
@@ -158,7 +158,7 @@ BEGIN
       ',amount_with_fee_frac INT4 NOT NULL '
       ',PRIMARY KEY (deposit_serial_id, rtransaction_id) '
     ')',
-    'refunds_' || shard_num
+    'refunds_' || suffix
   );
 
   EXECUTE FORMAT(
@@ -171,7 +171,7 @@ BEGIN
       ',amount_val INT8 NOT NULL '
       ',amount_frac INT4 NOT NULL '
     ')',
-    'wire_out_' || shard_num
+    'wire_out_' || suffix
   );
 
   EXECUTE FORMAT(
@@ -180,7 +180,7 @@ BEGIN
       ',deposit_serial_id INT8 PRIMARY KEY '
       ',wtid_raw BYTEA ' 
     ')',
-    'aggregation_tracking_' || shard_num
+    'aggregation_tracking_' || suffix
   );
 
   EXECUTE FORMAT(
@@ -194,7 +194,7 @@ BEGIN
       ',recoup_timestamp INT8 NOT NULL '
       ',reserve_out_serial_id INT8 NOT NULL '
     ')',
-    'recoup_' || shard_num
+    'recoup_' || suffix
   );
 
   EXECUTE FORMAT(
@@ -208,7 +208,7 @@ BEGIN
       ',recoup_timestamp INT8 NOT NULL '
       ',rrc_serial INT8 NOT NULL '
     ')',
-    'recoup_refresh_' || shard_num
+    'recoup_refresh_' || suffix
   );
 
   EXECUTE FORMAT(
@@ -219,7 +219,7 @@ BEGIN
       ',failed BOOLEAN NOT NULL DEFAULT false '
       ',buf BYTEA NOT NULL '
     ')',
-    'prewire_' || shard_num
+    'prewire_' || suffix
   );
 
 END

-- 
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.