-more work on new DB logic

10 files changed, 139 insertions, 97 deletions

diff --git a/src/exchangedb/common-0001.sql b/src/exchangedb/common-0001.sql
index 4a0aac381..564bf3b35 100644
--- a/src/exchangedb/common-0001.sql
+++ b/src/exchangedb/common-0001.sql
@@ -459,7 +459,8 @@ BEGIN
   PERFORM create_partitioned_table(
     'CREATE TABLE IF NOT EXISTS %I'
       '(reserve_open_deposit_uuid BIGINT GENERATED BY DEFAULT AS IDENTITY' -- UNIQUE / PRIMARY KEY'
-      ',reserve_pub BYTEA NOT NULL' -- REFERENCES reserves (reserve_pub) ON DELETE CASCADE'
+      ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
+      ',reserve_pub BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=32)'
       ',request_timestamp INT8 NOT NULL'
       ',coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)'
       ',coin_sig BYTEA NOT NULL CHECK (LENGTH(coin_sig)=64)'
@@ -496,7 +497,7 @@ BEGIN
   EXECUTE FORMAT (
     'ALTER TABLE reserves_open_deposits_' || partition_suffix || ' '
       'ADD CONSTRAINT reserves_open_deposits_' || partition_suffix || '_coin_unique '
-        'PRIMARY KEY (coin_pub,reserve_pub)'
+        'PRIMARY KEY (coin_pub,coin_sig)'
   );
 END
 $$;
@@ -1749,6 +1750,9 @@ BEGIN
       ',reserve_sig BYTEA NOT NULL CHECK (LENGTH(reserve_sig)=64)'
       ',close_val INT8 NOT NULL'
       ',close_frac INT4 NOT NULL'
+      ',close_fee_val INT8 NOT NULL'
+      ',close_fee_frac INT4 NOT NULL'
+      ',payto_uri VARCHAR NOT NULL'
       ',PRIMARY KEY (reserve_pub,close_timestamp)'
     ') %s ;'
     ,table_name
diff --git a/src/exchangedb/pg_insert_close_request.c b/src/exchangedb/pg_insert_close_request.c
index 3622149a7..43ca944f4 100644
--- a/src/exchangedb/pg_insert_close_request.c
+++ b/src/exchangedb/pg_insert_close_request.c
@@ -33,32 +33,35 @@ TEH_PG_insert_close_request (
   const char *payto_uri,
   const struct TALER_ReserveSignatureP *reserve_sig,
   struct GNUNET_TIME_Timestamp request_timestamp,
-  const struct TALER_Amount *closing_fee,
-  struct TALER_Amount *final_balance)
+  const struct TALER_Amount *balance,
+  const struct TALER_Amount *closing_fee)
 {
   struct PostgresClosure *pg = cls;
-  // FIXME: deal with payto_uri and closing_fee!!
   struct GNUNET_PQ_QueryParam params[] = {
     GNUNET_PQ_query_param_auto_from_type (reserve_pub),
     GNUNET_PQ_query_param_timestamp (&request_timestamp),
     GNUNET_PQ_query_param_auto_from_type (reserve_sig),
+    TALER_PQ_query_param_amount (balance),
+    TALER_PQ_query_param_amount (closing_fee),
+    GNUNET_PQ_query_param_string (payto_uri),
     GNUNET_PQ_query_param_end
   };
-  struct GNUNET_PQ_ResultSpec rs[] = {
-    TALER_PQ_RESULT_SPEC_AMOUNT ("out_final_balance",
-                                 final_balance),
-    GNUNET_PQ_result_spec_end
-  };
 
   PREPARE (pg,
-           "call_account_close",
-           "SELECT "
-           " out_final_balance_val"
-           ",out_final_balance_frac"
-           " FROM exchange_do_close_request"
-           "  ($1, $2, $3)");
-  return GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
-                                                   "call_account_close",
-                                                   params,
-                                                   rs);
+           "insert_account_close",
+           "INSERT INTO close_requests"
+           "(reserve_pub"
+           ",close_timestamp"
+           ",reserve_sig"
+           ",close_val"
+           ",close_frac,"
+           ",close_fee_val"
+           ",close_fee_frac"
+           ",payto_uri"
+           ")"
+           "VALUES ($1, $2, $3, $4, $5, $6, $7)"
+           " ON CONFLICT DO NOTHING;");
+  return GNUNET_PQ_eval_prepared_non_select (pg->conn,
+                                             "insert_account_close",
+                                             params);
 }
diff --git a/src/exchangedb/pg_insert_close_request.h b/src/exchangedb/pg_insert_close_request.h
index 09404094f..c014a10b9 100644
--- a/src/exchangedb/pg_insert_close_request.h
+++ b/src/exchangedb/pg_insert_close_request.h
@@ -34,8 +34,8 @@
  * @param payto_uri where to wire the funds
  * @param reserve_sig signature affiming that the account is to be closed
  * @param request_timestamp time of the close request (client-side?)
+ * @param balance final balance in the reserve
  * @param closing_fee closing fee to charge
- * @param[out] final_balance set to the final balance in the account that will be wired back to the origin account
  * @return transaction status code
  */
 enum GNUNET_DB_QueryStatus
@@ -45,8 +45,8 @@ TEH_PG_insert_close_request (
   const char *payto_uri,
   const struct TALER_ReserveSignatureP *reserve_sig,
   struct GNUNET_TIME_Timestamp request_timestamp,
-  const struct TALER_Amount *closing_fee,
-  struct TALER_Amount *final_balance);
+  const struct TALER_Amount *balance,
+  const struct TALER_Amount *closing_fee);
 
 
 #endif
diff --git a/src/exchangedb/pg_insert_reserve_open_deposit.c b/src/exchangedb/pg_insert_reserve_open_deposit.c
index c767bfeee..8bf70e7b2 100644
--- a/src/exchangedb/pg_insert_reserve_open_deposit.c
+++ b/src/exchangedb/pg_insert_reserve_open_deposit.c
@@ -34,6 +34,7 @@ TEH_PG_insert_reserve_open_deposit (
   uint64_t known_coin_id,
   const struct TALER_Amount *coin_total,
   const struct TALER_ReserveSignatureP *reserve_sig,
+  const struct TALER_ReservePublicKeyP *reserve_pub,
   bool *insufficient_funds)
 {
   struct PostgresClosure *pg = cls;
@@ -42,11 +43,12 @@ TEH_PG_insert_reserve_open_deposit (
     GNUNET_PQ_query_param_uint64 (&known_coin_id),
     GNUNET_PQ_query_param_auto_from_type (coin_sig),
     GNUNET_PQ_query_param_auto_from_type (reserve_sig),
+    GNUNET_PQ_query_param_auto_from_type (reserve_pub),
     TALER_PQ_query_param_amount (coin_total),
     GNUNET_PQ_query_param_end
   };
   struct GNUNET_PQ_ResultSpec rs[] = {
-    GNUNET_PQ_result_spec_bool ("insufficient_funds",
+    GNUNET_PQ_result_spec_bool ("out_insufficient_funds",
                                 insufficient_funds),
     GNUNET_PQ_result_spec_end
   };
@@ -54,9 +56,9 @@ TEH_PG_insert_reserve_open_deposit (
   PREPARE (pg,
            "insert_reserve_open_deposit",
            "SELECT "
-           " insufficient_funds"
+           " out_insufficient_funds"
            " FROM exchange_do_reserve_open_deposit"
-           " ($1,$2,$3,$4,$5,$6);");
+           " ($1,$2,$3,$4,$5,$6,$7);");
   return GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
                                                    "insert_reserve_open_deposit",
                                                    params,
diff --git a/src/exchangedb/pg_insert_reserve_open_deposit.h b/src/exchangedb/pg_insert_reserve_open_deposit.h
index 335fda52d..7eb2fe093 100644
--- a/src/exchangedb/pg_insert_reserve_open_deposit.h
+++ b/src/exchangedb/pg_insert_reserve_open_deposit.h
@@ -36,6 +36,7 @@
  * @param known_coin_id ID of the coin in the known_coins table
  * @param coin_total amount to be spent of the coin (including deposit fee)
  * @param reserve_sig signature by the reserve affirming the open operation
+ * @param reserve_pub public key of the reserve being opened
  * @param[out] insufficient_funds set to true if the coin's balance is insufficient, otherwise to false
  * @return transaction status code, 0 if operation is already in the DB
  */
@@ -47,6 +48,7 @@ TEH_PG_insert_reserve_open_deposit (
   uint64_t known_coin_id,
   const struct TALER_Amount *coin_total,
   const struct TALER_ReserveSignatureP *reserve_sig,
+  const struct TALER_ReservePublicKeyP *reserve_pub,
   bool *insufficient_funds);
 
 #endif
diff --git a/src/exchangedb/pg_iterate_kyc_reference.c b/src/exchangedb/pg_iterate_kyc_reference.c
index 4a94722c4..772c51e2c 100644
--- a/src/exchangedb/pg_iterate_kyc_reference.c
+++ b/src/exchangedb/pg_iterate_kyc_reference.c
@@ -69,11 +69,11 @@ iterate_kyc_reference_cb (void *cls,
     char *provider_user_id;
     char *legitimization_id;
     struct GNUNET_PQ_ResultSpec rs[] = {
-      GNUNET_PQ_result_spec_string ("section_name",
+      GNUNET_PQ_result_spec_string ("provider_section",
                                     &kyc_provider_section_name),
       GNUNET_PQ_result_spec_string ("provider_user_id",
                                     &provider_user_id),
-      GNUNET_PQ_result_spec_string ("legi_id",
+      GNUNET_PQ_result_spec_string ("provider_legitimization_id",
                                     &legitimization_id),
       GNUNET_PQ_result_spec_end
     };
@@ -116,10 +116,10 @@ TEH_PG_iterate_kyc_reference (
   PREPARE (pg,
            "iterate_kyc_reference",
            "SELECT "
-           " section_name"
+           " provider_section"
            ",provider_user_id"
-           ",legi_id"
-           " FROM FIXME"
+           ",provider_legitimization_id"
+           " FROM legitimization_processes"
            " WHERE h_payto=$1;");
   return GNUNET_PQ_eval_prepared_multi_select (pg->conn,
                                                "iterate_kyc_reference",
diff --git a/src/exchangedb/pg_iterate_reserve_close_info.c b/src/exchangedb/pg_iterate_reserve_close_info.c
index c5ff61322..f1b2d452b 100644
--- a/src/exchangedb/pg_iterate_reserve_close_info.c
+++ b/src/exchangedb/pg_iterate_reserve_close_info.c
@@ -68,7 +68,7 @@ iterate_reserve_close_info_cb (void *cls,
     struct TALER_Amount amount;
     struct GNUNET_TIME_Absolute ts;
     struct GNUNET_PQ_ResultSpec rs[] = {
-      GNUNET_PQ_result_spec_absolute_time ("timestamp",
+      GNUNET_PQ_result_spec_absolute_time ("execution_date",
                                            &ts),
       TALER_PQ_RESULT_SPEC_AMOUNT ("amount",
                                    &amount),
@@ -115,14 +115,15 @@ TEH_PG_iterate_reserve_close_info (
            "SELECT"
            " amount_val"
            ",amount_frac"
-           ",timestamp"
-           " FROM FIXME"
-           " WHERE h_payto=$1"
-           "   AND timestamp >= $2"
-           " ORDER BY timestamp DESC");
-  return GNUNET_PQ_eval_prepared_multi_select (pg->conn,
-                                               "iterate_reserve_close_info",
-                                               params,
-                                               &iterate_reserve_close_info_cb,
-                                               &ic);
+           ",execution_date"
+           " FROM reserves_close"
+           " WHERE wire_target_h_payto=$1"
+           "   AND execution_date >= $2"
+           " ORDER BY execution_date DESC");
+  return GNUNET_PQ_eval_prepared_multi_select (
+    pg->conn,
+    "iterate_reserve_close_info",
+    params,
+    &iterate_reserve_close_info_cb,
+    &ic);
 }
diff --git a/src/exchangedb/pg_select_reserve_close_info.c b/src/exchangedb/pg_select_reserve_close_info.c
index 53ea45682..0b373b7bb 100644
--- a/src/exchangedb/pg_select_reserve_close_info.c
+++ b/src/exchangedb/pg_select_reserve_close_info.c
@@ -39,7 +39,7 @@ TEH_PG_select_reserve_close_info (
     GNUNET_PQ_query_param_end
   };
   struct GNUNET_PQ_ResultSpec rs[] = {
-    TALER_PQ_RESULT_SPEC_AMOUNT ("balance",
+    TALER_PQ_RESULT_SPEC_AMOUNT ("close",
                                  balance),
     GNUNET_PQ_result_spec_string ("payto_uri",
                                   payto_uri),
@@ -49,10 +49,10 @@ TEH_PG_select_reserve_close_info (
   PREPARE (pg,
            "select_reserve_close_info",
            "SELECT "
-           " balance_frac"
-           ",balance_val"
+           " close_frac"
+           ",close_val"
            ",payto_uri"
-           " FROM FIXME"
+           " FROM close_requests"
            " WHERE reserve_pub=$1;");
   return GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
                                                    "select_reserve_close_info",
diff --git a/src/exchangedb/plugin_exchangedb_postgres.c b/src/exchangedb/plugin_exchangedb_postgres.c
index 341364f9f..d2e2eb5df 100644
--- a/src/exchangedb/plugin_exchangedb_postgres.c
+++ b/src/exchangedb/plugin_exchangedb_postgres.c
@@ -32,6 +32,9 @@
 #include "pg_helper.h"
 #include "pg_insert_close_request.h"
 #include "pg_insert_reserve_open_deposit.h"
+#include "pg_iterate_kyc_reference.h"
+#include "pg_iterate_reserve_close_info.h"
+#include "pg_select_reserve_close_info.h"
 #include <poll.h>
 #include <pthread.h>
 #include <libpq-fe.h>
@@ -17264,8 +17267,6 @@ libtaler_plugin_exchangedb_postgres_init (void *cls)
     = &postgres_select_purse_merge;
   plugin->insert_history_request
     = &postgres_insert_history_request;
-  plugin->insert_close_request
-    = &TEH_PG_insert_close_request;
   plugin->insert_drain_profit
     = &postgres_insert_drain_profit;
   plugin->profit_drains_get_pending
@@ -17294,6 +17295,16 @@ libtaler_plugin_exchangedb_postgres_init (void *cls)
     = &postgres_select_aggregation_amounts_for_kyc_check;
   plugin->select_merge_amounts_for_kyc_check
     = &postgres_select_merge_amounts_for_kyc_check;
+  /* NEW style, sort alphabetically! */
+  plugin->insert_close_request
+    = &TEH_PG_insert_close_request;
+  plugin->iterate_reserve_close_info
+    = &TEH_PG_iterate_reserve_close_info;
+  plugin->iterate_kyc_reference
+    = &TEH_PG_iterate_kyc_reference;
+  plugin->select_reserve_close_info
+    = &TEH_PG_select_reserve_close_info;
+
   return plugin;
 }
 
diff --git a/src/exchangedb/procedures.sql b/src/exchangedb/procedures.sql
index 1940fa7b8..8407f20c7 100644
--- a/src/exchangedb/procedures.sql
+++ b/src/exchangedb/procedures.sql
@@ -1778,7 +1778,7 @@ ELSE
   my_amount_val = my_amount_val + my_amount_frac / 100000000;
   my_amount_frac = my_amount_frac % 100000000;
 
-  UPDATE reserves
+  UPDATE exchange.reserves
   SET
     current_balance_frac=current_balance_frac+my_amount_frac
        - CASE
@@ -1795,7 +1795,7 @@ ELSE
   WHERE reserve_pub=in_reserve_pub;
 
   -- ... and mark purse as finished.
-  UPDATE purse_requests
+  UPDATE exchange.purse_requests
      SET finished=true
   WHERE purse_pub=in_purse_pub;
 END IF;
@@ -1881,7 +1881,7 @@ THEN
     out_no_funds=TRUE;
     RETURN;
   END IF;
-  UPDATE reserves
+  UPDATE exchange.reserves
      SET purses_active=purses_active+1
    WHERE reserve_pub=in_reserve_pub
      AND purses_active < purses_allowed;
@@ -1901,7 +1901,7 @@ ELSE
       RETURN;
     END IF;
   ELSE
-    UPDATE reserves
+    UPDATE exchange.reserves
       SET
         current_balance_frac=current_balance_frac-in_purse_fee_frac
          + CASE
@@ -1993,7 +1993,7 @@ THEN
   RETURN;
 END IF;
 
-UPDATE purse_requests
+UPDATE exchange.purse_requests
  SET refunded=TRUE,
      finished=TRUE
  WHERE purse_pub=my_purse_pub;
@@ -2011,7 +2011,7 @@ FOR my_deposit IN
     FROM exchange.purse_deposits
   WHERE purse_pub = my_purse_pub
 LOOP
-  UPDATE known_coins SET
+  UPDATE exchange.known_coins SET
     remaining_frac=remaining_frac+my_deposit.amount_with_fee_frac
      - CASE
        WHEN remaining_frac+my_deposit.amount_with_fee_frac >= 100000000
@@ -2071,7 +2071,7 @@ BEGIN
   out_idempotent=FALSE;
 
   -- Update reserve balance.
-  UPDATE reserves
+  UPDATE exchange.reserves
    SET
     current_balance_frac=current_balance_frac-in_history_fee_frac
        + CASE
@@ -2103,57 +2103,76 @@ BEGIN
 END $$;
 
 
-CREATE OR REPLACE FUNCTION exchange_do_close_request(
-  IN in_reserve_pub BYTEA,
-  IN in_close_timestamp INT8,
+CREATE OR REPLACE FUNCTION exchange_do_reserve_open_deposit(
+  IN in_coin_pub BYTEA,
+  IN in_known_coin_id INT8,
+  IN in_coin_sig BYTEA,
   IN in_reserve_sig BYTEA,
-  OUT out_final_balance_val INT8,
-  OUT out_final_balance_frac INT4,
-  OUT out_balance_ok BOOLEAN,
-  OUT out_conflict BOOLEAN)
+  IN in_reserve_pub BYTEA,
+  IN in_coin_total_val INT8,
+  IN in_coin_total_frac INT4,
+  OUT out_insufficient_funds BOOLEAN)
 LANGUAGE plpgsql
 AS $$
 BEGIN
 
-  SELECT
-    current_balance_val
-   ,current_balance_frac
-  INTO
-    out_final_balance_val
-   ,out_final_balance_frac
-  FROM exchange.reserves
-  WHERE reserve_pub=in_reserve_pub;
+INSERT INTO exchange.reserves_open_deposits
+  (reserve_sig
+  ,reserve_pub
+  ,request_timestamp
+  ,coin_pub
+  ,coin_sig
+  ,contribution_val
+  ,contribution_frac
+  )
+  VALUES
+  (in_reserve_sig
+  ,in_reserve_pub
+  ,in_request_timestamp
+  ,in_coin_pub
+  ,in_coin_sig
+  ,in_coin_total_val
+  ,in_coin_total_frac)
+  ON CONFLICT DO NOTHING;
 
-  IF NOT FOUND
-  THEN
-    out_final_balance_val=0;
-    out_final_balance_frac=0;
-    out_balance_ok = FALSE;
-    out_conflict = FALSE;
-  END IF;
+IF NOT FOUND
+THEN
+  -- Idempotent request known, return success.
+  out_insufficient_funds=FALSE;
+  RETURN;
+END IF;
 
-  INSERT INTO exchange.close_requests
-    (reserve_pub
-    ,close_timestamp
-    ,reserve_sig
-    ,close_val
-    ,close_frac)
-    VALUES
-    (in_reserve_pub
-    ,in_close_timestamp
-    ,in_reserve_sig
-    ,out_final_balance_val
-    ,out_final_balance_frac)
-  ON CONFLICT DO NOTHING;
-  out_conflict = NOT FOUND;
 
-  UPDATE reserves SET
-    current_balance_val=0
-   ,current_balance_frac=0
-  WHERE reserve_pub=in_reserve_pub;
-  out_balance_ok = TRUE;
+-- Check and update balance of the coin.
+UPDATE exchange.known_coins
+  SET
+    remaining_frac=remaining_frac-in_coin_total_frac
+       + CASE
+         WHEN remaining_frac < in_coin_total_frac
+         THEN 100000000
+         ELSE 0
+         END,
+    remaining_val=remaining_val-in_coin_total_val
+       - CASE
+         WHEN remaining_frac < in_coin_total_frac
+         THEN 1
+         ELSE 0
+         END
+  WHERE coin_pub=in_coin_pub
+    AND ( (remaining_val > in_coin_total_val) OR
+          ( (remaining_frac >= in_coin_total_frac) AND
+            (remaining_val >= in_coin_total_val) ) );
 
-END $$;
+IF NOT FOUND
+THEN
+  -- Insufficient balance.
+  out_insufficient_funds=TRUE;
+  RETURN;
+END IF;
 
+-- Everything fine, return success!
+out_insufficient_funds=FALSE;
+
+END $$;
 
 COMMIT;