From c741c1fa718e144548b5508e85e469ec93f73b54 Mon Sep 17 00:00:00 2001
From: Christian Grothoff <grothoff@gnunet.org>
Date: Tue, 17 Oct 2023 11:01:20 +0200
Subject: kill another dead index, beautify SQL

---
 src/exchangedb/0002-account_merges.sql             |  1 -
 src/exchangedb/0002-purse_deposits.sql             |  5 -----
 src/exchangedb/exchange_do_purse_deposit.sql       |  8 ++++----
 src/exchangedb/pg_get_coin_transactions.c          |  4 ++--
 src/exchangedb/pg_get_purse_deposit.c              | 16 ++++++++--------
 src/exchangedb/pg_select_purse_deposits_by_purse.c |  7 ++++---
 6 files changed, 18 insertions(+), 23 deletions(-)

(limited to 'src')

diff --git a/src/exchangedb/0002-account_merges.sql b/src/exchangedb/0002-account_merges.sql
index 1ea9e9298..e791b60d0 100644
--- a/src/exchangedb/0002-account_merges.sql
+++ b/src/exchangedb/0002-account_merges.sql
@@ -74,7 +74,6 @@ DECLARE
 BEGIN
   table_name = concat_ws('_', table_name, partition_suffix);
 
-  -- FIXME: change to materialized index by reserve_pub!?
   EXECUTE FORMAT (
     'CREATE INDEX ' || table_name || '_by_reserve_pub '
     'ON ' || table_name || ' '
diff --git a/src/exchangedb/0002-purse_deposits.sql b/src/exchangedb/0002-purse_deposits.sql
index 2d2be5152..6a07c4b62 100644
--- a/src/exchangedb/0002-purse_deposits.sql
+++ b/src/exchangedb/0002-purse_deposits.sql
@@ -87,11 +87,6 @@ DECLARE
 BEGIN
   table_name = concat_ws('_', table_name, partition_suffix);
 
-  -- FIXME: change to materialized index by coin_pub!
-  EXECUTE FORMAT (
-    'CREATE INDEX ' || table_name || '_by_coin_pub'
-    ' ON ' || table_name || ' (coin_pub);'
-  );
   EXECUTE FORMAT (
     'ALTER TABLE ' || table_name ||
     ' ADD CONSTRAINT ' || table_name || '_purse_deposit_serial_id_key'
diff --git a/src/exchangedb/exchange_do_purse_deposit.sql b/src/exchangedb/exchange_do_purse_deposit.sql
index 0127ad23f..1e97e2a17 100644
--- a/src/exchangedb/exchange_do_purse_deposit.sql
+++ b/src/exchangedb/exchange_do_purse_deposit.sql
@@ -45,7 +45,7 @@ DECLARE
 BEGIN
 
 -- Store the deposit request.
-INSERT INTO exchange.purse_deposits
+INSERT INTO purse_deposits
   (partner_serial_id
   ,purse_pub
   ,coin_pub
@@ -65,9 +65,9 @@ THEN
   -- if so, success, otherwise conflict!
 
   PERFORM
-  FROM exchange.purse_deposits
-  WHERE coin_pub = in_coin_pub
-    AND purse_pub = in_purse_pub
+  FROM purse_deposits
+  WHERE purse_pub = in_purse_pub
+    AND coin_pub = in_coin_pub
     AND coin_sig = in_coin_sig;
   IF NOT FOUND
   THEN
diff --git a/src/exchangedb/pg_get_coin_transactions.c b/src/exchangedb/pg_get_coin_transactions.c
index 012c4784f..421fb4428 100644
--- a/src/exchangedb/pg_get_coin_transactions.c
+++ b/src/exchangedb/pg_get_coin_transactions.c
@@ -925,8 +925,8 @@ TEH_PG_get_coin_transactions (
            "   ON (pd.coin_pub = kc.coin_pub)"
            " JOIN denominations denoms"
            "   USING (denominations_serial)"
-           " WHERE pd.coin_pub=$1"
-           "   AND pd.purse_deposit_serial_id=$2;");
+           " WHERE pd.purse_deposit_serial_id=$2"
+           "   AND pd.coin_pub=$1;");
   PREPARE (pg,
            "get_purse_decision_by_coin_pub",
            "SELECT"
diff --git a/src/exchangedb/pg_get_purse_deposit.c b/src/exchangedb/pg_get_purse_deposit.c
index c09d7578d..cb24855a1 100644
--- a/src/exchangedb/pg_get_purse_deposit.c
+++ b/src/exchangedb/pg_get_purse_deposit.c
@@ -59,9 +59,7 @@ TEH_PG_get_purse_deposit (
     GNUNET_PQ_result_spec_end
   };
 
-
   *partner_url = NULL;
-  /* Used in #postgres_get_purse_deposit */
   PREPARE (pg,
            "select_purse_deposit_by_coin_pub",
            "SELECT "
@@ -71,12 +69,14 @@ TEH_PG_get_purse_deposit (
            ",age_commitment_hash"
            ",partner_base_url"
            " FROM purse_deposits"
-           " LEFT JOIN partners USING (partner_serial_id)"
-           " JOIN known_coins kc USING (coin_pub)"
-           " JOIN denominations USING (denominations_serial)"
-           " WHERE coin_pub=$2"
-           "   AND purse_pub=$1;");
-
+           " LEFT JOIN partners"
+           "   USING (partner_serial_id)"
+           " JOIN known_coins kc"
+           "   USING (coin_pub)"
+           " JOIN denominations"
+           "   USING (denominations_serial)"
+           " WHERE purse_pub=$1"
+           "   AND coin_pub=$2;");
   return GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
                                                    "select_purse_deposit_by_coin_pub",
                                                    params,
diff --git a/src/exchangedb/pg_select_purse_deposits_by_purse.c b/src/exchangedb/pg_select_purse_deposits_by_purse.c
index cfc72a440..94b935cb4 100644
--- a/src/exchangedb/pg_select_purse_deposits_by_purse.c
+++ b/src/exchangedb/pg_select_purse_deposits_by_purse.c
@@ -137,10 +137,11 @@ TEH_PG_select_purse_deposits_by_purse (
            ",pd.coin_pub"
            ",denom.denom_pub"
            " FROM purse_deposits pd"
-           " JOIN known_coins kc USING (coin_pub)"
-           " JOIN denominations denom USING (denominations_serial)"
+           " JOIN known_coins kc"
+           "   USING (coin_pub)"
+           " JOIN denominations denom"
+           "   USING (denominations_serial)"
            " WHERE purse_pub=$1;");
-
   qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn,
                                              "audit_get_purse_deposits_by_purse",
                                              params,
-- 
cgit v1.2.3