more SQL for tips

2 files changed, 505 insertions, 55 deletions

diff --git a/src/backenddb/merchant-0001.sql b/src/backenddb/merchant-0001.sql
index bd05b6bb..1aa54253 100644
--- a/src/backenddb/merchant-0001.sql
+++ b/src/backenddb/merchant-0001.sql
@@ -466,11 +466,12 @@ CREATE TABLE IF NOT EXISTS merchant_tips
      REFERENCES merchant_tip_reserves (reserve_serial) ON DELETE CASCADE
   ,tip_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(tip_id)=64)
   ,justification VARCHAR NOT NULL
+  ,next_url VARCHAR NOT NULL
   ,expiration INT8 NOT NULL
   ,amount_val INT8 NOT NULL
   ,amount_frac INT4 NOT NULL
-  ,left_val INT8 NOT NULL
-  ,left_frac INT4 NOT NULL
+  ,picked_up_val INT8 NOT NULL
+  ,picked_up_frac INT4 NOT NULL
   ,was_picked_up BOOLEAN NOT NULL DEFAULT FALSE
   );
 CREATE INDEX IF NOT EXISTS merchant_tips_by_pickup_and_expiration
@@ -480,15 +481,16 @@ COMMENT ON TABLE merchant_tips
   IS 'tips that have been authorized';
 COMMENT ON COLUMN merchant_tips.amount_val
   IS 'Overall tip amount';
-COMMENT ON COLUMN merchant_tips.left_val
-  IS 'Tip amount not yet picked up';
+COMMENT ON COLUMN merchant_tips.picked_up_val
+  IS 'Tip amount left to be picked up';
 COMMENT ON COLUMN merchant_tips.reserve_serial
   IS 'Reserve from which this tip is funded';
 COMMENT ON COLUMN merchant_tips.expiration
-  IS 'time by which the wallet has to pick up the tip before it expires';
+  IS 'by when does the client have to pick up the tip';
 
 CREATE TABLE IF NOT EXISTS merchant_tip_pickups
-  (tip_serial BIGINT NOT NULL
+  (pickup_serial BIGSERIAL PRIMARY KEY NOT NULL
+  ,tip_serial BIGINT NOT NULL
       REFERENCES merchant_tips (tip_serial) ON DELETE CASCADE
   ,pickup_id BYTEA NOT NULL UNIQUE CHECK (LENGTH(pickup_id)=64)
   ,amount_val INT8 NOT NULL
@@ -500,11 +502,11 @@ COMMENT ON COLUMN merchant_tips.amount_val
   IS 'total transaction cost for all coins including withdraw fees';
 
 CREATE TABLE IF NOT EXISTS merchant_tip_pickup_signatures
-  (pickup_id BYTEA NOT NULL
-     REFERENCES merchant_tip_pickups (pickup_id) ON DELETE CASCADE
+  (pickup_serial INT8 NOT NULL
+     REFERENCES merchant_tip_pickups (pickup_serial) ON DELETE CASCADE
   ,coin_offset INT4 NOT NULL
   ,blind_sig BYTEA NOT NULL
-  ,PRIMARY KEY (pickup_id, coin_offset)
+  ,PRIMARY KEY (pickup_serial, coin_offset)
   );
 COMMENT ON TABLE merchant_tip_pickup_signatures
   IS 'blind signatures we got from the exchange during the tip pickup';
diff --git a/src/backenddb/plugin_merchantdb_postgres.c b/src/backenddb/plugin_merchantdb_postgres.c
index 8f962953..a5105a34 100644
--- a/src/backenddb/plugin_merchantdb_postgres.c
+++ b/src/backenddb/plugin_merchantdb_postgres.c
@@ -4227,10 +4227,22 @@ struct LookupReserveForTipContext
    * #GNUNET_TIME_UNIT_FOREVER_ABS if we found none.
    */
   struct GNUNET_TIME_Absolute expiration;
+
+  /**
+   * Error status.
+   */
+  enum TALER_ErrorCode ec;
 };
 
 
 /**
+ * How long must a reserve be at least still valid before we use
+ * it for a tip?
+ */
+#define MIN_EXPIRATION GNUNET_TIME_UNIT_HOURS
+
+
+/**
  * Function to be called with the results of a SELECT statement
  * that has returned @a num_results results about accounts.
  *
@@ -4244,6 +4256,55 @@ lookup_reserve_for_tip_cb (void *cls,
                            unsigned int num_results)
 {
   struct LookupReserveForTipContext *lac = cls;
+  struct PostgresClosure *pg = lac->pg;
+
+  for (unsigned int i = 0; i < num_results; i++)
+  {
+    struct TALER_ReservePublicKeyP reserve_pub;
+    struct TALER_Amount committed_amount;
+    struct TALER_Amount remaining;
+    struct TALER_Amount initial_balance;
+    struct GNUNET_TIME_Absolute expiration;
+    struct GNUNET_PQ_ResultSpec rs[] = {
+      GNUNET_PQ_result_spec_auto_from_type ("reserve_pub",
+                                            &reserve_pub),
+      TALER_PQ_RESULT_SPEC_AMOUNT ("exchange_initial_balance",
+                                   &initial_balance),
+      TALER_PQ_RESULT_SPEC_AMOUNT ("tips_committed",
+                                   &committed_amount),
+      GNUNET_PQ_result_spec_absolute_time ("expiration",
+                                           &expiration),
+      GNUNET_PQ_result_spec_end
+    };
+
+    if (GNUNET_OK !=
+        GNUNET_PQ_extract_result (result,
+                                  rs,
+                                  i))
+    {
+      GNUNET_break (0);
+      lac->ec = GNUNET_DB_STATUS_HARD_ERROR; // FIXME
+      return;
+    }
+    if (0 >
+        TALER_amount_subtract (&remaining,
+                               &initial_balance,
+                               &committed_amount))
+    {
+      GNUNET_break (0);
+      continue;
+    }
+    if (0 >
+        TALER_amount_cmp (&remaining,
+                          &lac->required_amount))
+      continue; /* insufficient balance */
+    if ( (expiration.abs_value_us > lac->expiration.abs_value_us) &&
+         (GNUNET_TIME_absolute_get_remaining (lac->expiration).rel_value_us >
+          MIN_EXPIRATION.rel_value_us) )
+      continue;
+    lac->expiration = expiration;
+    lac->reserve_pub = reserve_pub;
+  }
 }
 
 
@@ -4306,11 +4367,12 @@ RETRY:
   {
     struct GNUNET_PQ_QueryParam params[] = {
       GNUNET_PQ_query_param_string (instance_id),
+      GNUNET_PQ_query_param_absolute_time (expiration),
       TALER_PQ_query_param_amount (amount),
       GNUNET_PQ_query_param_end
     };
     qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn,
-                                               "lookup_reserve", // FIXME: write SQL!
+                                               "lookup_reserve_for_tip",
                                                params,
                                                &lookup_reserve_for_tip_cb,
                                                &lac);
@@ -4338,10 +4400,6 @@ RETRY:
     struct GNUNET_PQ_QueryParam params[] = {
       GNUNET_PQ_query_param_string (instance_id),
       GNUNET_PQ_query_param_auto_from_type (reserve_pubp),
-      TALER_PQ_query_param_amount (amount),
-      GNUNET_PQ_query_param_string (justification),
-      GNUNET_PQ_query_param_string (next_url),
-      GNUNET_PQ_query_param_auto_from_type (tip_id),
       GNUNET_PQ_query_param_end
     };
     struct GNUNET_PQ_ResultSpec rs[] = {
@@ -4355,7 +4413,7 @@ RETRY:
     };
 
     qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
-                                                   "lookup_reserve_status", // FIXME: write SQL!
+                                                   "lookup_reserve_status",
                                                    params,
                                                    rs);
     if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
@@ -4403,7 +4461,7 @@ RETRY:
     };
 
     qs = GNUNET_PQ_eval_prepared_non_select (pg->conn,
-                                             "update_reserve_tips_committed", // FIXME: write SQL!
+                                             "update_reserve_tips_committed",
                                              params);
     if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
     {
@@ -4424,15 +4482,15 @@ RETRY:
     struct GNUNET_PQ_QueryParam params[] = {
       GNUNET_PQ_query_param_string (instance_id),
       GNUNET_PQ_query_param_auto_from_type (reserve_pubp),
-      TALER_PQ_query_param_amount (amount),
+      GNUNET_PQ_query_param_auto_from_type (tip_id),
       GNUNET_PQ_query_param_string (justification),
       GNUNET_PQ_query_param_string (next_url),
-      GNUNET_PQ_query_param_auto_from_type (tip_id),
+      TALER_PQ_query_param_amount (amount),
       GNUNET_PQ_query_param_end
     };
 
     qs = GNUNET_PQ_eval_prepared_non_select (pg->conn,
-                                             "update_reserve_tips_committed",// FIXME: write SQL!
+                                             "insert_tip",
                                              params);
     if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
     {
@@ -4460,6 +4518,73 @@ RETRY:
 
 
 /**
+ * Closure for #lookup_signatures_cb().
+ */
+struct LookupSignaturesContext
+{
+  /**
+   * Length of the @e sigs array
+   */
+  unsigned int sigs_length;
+
+  /**
+   * Where to store the signatures.
+   */
+  struct GNUNET_CRYPTO_RsaSignature **sigs;
+};
+
+
+/**
+ * Function to be called with the results of a SELECT statement
+ * that has returned @a num_results results about accounts.
+ *
+ * @param[in,out] cls of type `struct LookupSignaturesContext *`
+ * @param result the postgres result
+ * @param num_result the number of results in @a result
+ */
+static void
+lookup_signatures_cb (void *cls,
+                      PGresult *result,
+                      unsigned int num_results)
+{
+  struct LookupSignaturesContext *lsc = cls;
+
+  for (unsigned int i = 0; i < num_results; i++)
+  {
+    uint32_t offset;
+    struct GNUNET_CRYPTO_RsaSignature *bsig;
+    struct GNUNET_PQ_ResultSpec rs[] = {
+      GNUNET_PQ_result_spec_uint32 ("coin_offset",
+                                    &offset),
+      GNUNET_PQ_result_spec_rsa_signature ("blind_sig",
+                                           &bsig),
+      GNUNET_PQ_result_spec_end
+    };
+
+    if (GNUNET_OK !=
+        GNUNET_PQ_extract_result (result,
+                                  rs,
+                                  i))
+    {
+      GNUNET_break (0);
+      return;
+    }
+    if (offset >= lsc->sigs_length)
+    {
+      GNUNET_break_op (0);
+      GNUNET_PQ_cleanup_result (rs);
+      continue;
+    }
+    /* Must be NULL due to UNIQUE constraint on offset and
+       requirement that client launched us with 'sigs'
+       pre-initialized to NULL. */
+    GNUNET_assert (NULL == lsc->sigs[offset]);
+    lsc->sigs[offset] = bsig;
+  }
+}
+
+
+/**
  * Lookup pickup details for pickup @a pickup_id.
  *
  * @param cls closure, typically a connection to the db
@@ -4483,29 +4608,67 @@ postgres_lookup_pickup (void *cls,
                         unsigned int sigs_length,
                         struct GNUNET_CRYPTO_RsaSignature *sigs[])
 {
+  struct PostgresClosure *pg = cls;
+  uint64_t pickup_serial;
+
+  {
+    struct GNUNET_PQ_QueryParam params[] = {
+      GNUNET_PQ_query_param_string (instance_id),
+      GNUNET_PQ_query_param_auto_from_type (tip_id),
+      GNUNET_PQ_query_param_auto_from_type (pickup_id),
+      GNUNET_PQ_query_param_end
+    };
+    struct GNUNET_PQ_ResultSpec rs[] = {
+      GNUNET_PQ_result_spec_string ("exchange_url",
+                                    exchange_url),
+      GNUNET_PQ_result_spec_auto_from_type ("reserve_priv",
+                                            reserve_priv),
+      GNUNET_PQ_result_spec_uint64 ("pickup_serial",
+                                    &pickup_serial),
+      GNUNET_PQ_result_spec_end
+    };
+    enum GNUNET_DB_QueryStatus qs;
+
+    qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
+                                                   "lookup_pickup",
+                                                   params,
+                                                   rs);
+    if (qs <= 0)
+      return qs;
+  }
+  {
+    struct GNUNET_PQ_QueryParam params[] = {
+      GNUNET_PQ_query_param_uint64 (&pickup_serial),
+      GNUNET_PQ_query_param_end
+    };
+    struct LookupSignaturesContext lsc = {
+      .sigs_length = sigs_length,
+      .sigs = sigs
+    };
+
+    return GNUNET_PQ_eval_prepared_multi_select (pg->conn,
+                                                 "lookup_pickup_signatures",
+                                                 params,
+                                                 &lookup_signatures_cb,
+                                                 &lsc);
+  }
 }
 
 
 /**
-* Lookup tip details for tip @a tip_id.
-*
-* @param cls closure, typically a connection to the db
-* @param instance_id which instance should we lookup tip details for
-* @param tip_id which tip should we lookup details on
-* @param[out] total_authorized amount how high is the tip (with fees)
-* @param[out] total_picked_up how much of the tip was so far picked up (with fees)
-* @param[out] expiration set to when the tip expires
-* @param[out] exchange_url set to the exchange URL where the reserve is
-* @param[out] reserve_priv set to private key of reserve to be debited
-* @return transaction status,
-*      #TALER_EC_TIP_AUTHORIZE_RESERVE_EXPIRED if the reserve is known but has expired
-*      #TALER_EC_TIP_AUTHORIZE_RESERVE_UNKNOWN if the reserve is not known
-*      #TALER_EC_TIP_AUTHORIZE_INSUFFICIENT_FUNDS if the reserve has insufficient funds left
-*      #TALER_EC_TIP_AUTHORIZE_DB_HARD_ERROR on hard DB errors
-*      #TALER_EC_TIP_AUTHORIZE_DB_SOFT_ERROR on soft DB errors (client should retry)
-*      #TALER_EC_NONE upon success
-*/
-static enum TALER_ErrorCode
+ * Lookup tip details for tip @a tip_id.
+ *
+ * @param cls closure, typically a connection to the db
+ * @param instance_id which instance should we lookup tip details for
+ * @param tip_id which tip should we lookup details on
+ * @param[out] total_authorized amount how high is the tip (with fees)
+ * @param[out] total_picked_up how much of the tip was so far picked up (with fees)
+ * @param[out] expiration set to when the tip expires
+ * @param[out] exchange_url set to the exchange URL where the reserve is
+ * @param[out] reserve_priv set to private key of reserve to be debited
+ * @return transaction status
+ */
+static enum GNUNET_DB_QueryStatus
 postgres_lookup_tip (void *cls,
                      const char *instance_id,
                      const struct GNUNET_HashCode *tip_id,
@@ -4522,9 +4685,9 @@ postgres_lookup_tip (void *cls,
     GNUNET_PQ_query_param_end
   };
   struct GNUNET_PQ_ResultSpec rs[] = {
-    TALER_PQ_RESULT_SPEC_AMOUNT ("total_authorized",
+    TALER_PQ_RESULT_SPEC_AMOUNT ("amount",
                                  total_authorized),
-    TALER_PQ_RESULT_SPEC_AMOUNT ("total_picked_up",
+    TALER_PQ_RESULT_SPEC_AMOUNT ("picked_up",
                                  total_picked_up),
     GNUNET_PQ_result_spec_absolute_time ("expiration",
                                          expiration),
@@ -4537,7 +4700,7 @@ postgres_lookup_tip (void *cls,
 
   check_connection (pg);
   return GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
-                                                   "lookup_tip", // FIXME: write SQL!
+                                                   "lookup_tip",
                                                    params,
                                                    rs);
 }
@@ -4578,6 +4741,7 @@ postgres_lookup_tip_details (void *cls,
                              unsigned int *pickups_length,
                              struct TALER_MERCHANTDB_PickupDetails **pickups)
 {
+  // FIXME!
 }
 
 
@@ -4592,7 +4756,7 @@ postgres_lookup_tip_details (void *cls,
  * @param cls closure, typically a connection to the db
  * @param tip_id the unique ID for the tip
  * @param total_picked_up how much was picked up overall at this
- *          point (includes @total_requested)
+ *          point (includes @a total_requested)
  * @param pickup_id unique ID for the operation
  * @param total_requested how much is being picked up in this operation
  * @return transaction status, usually
@@ -4608,19 +4772,132 @@ postgres_insert_pickup (void *cls,
                         const struct TALER_Amount *total_requested)
 {
   struct PostgresClosure *pg = cls;
-  struct GNUNET_PQ_QueryParam params[] = {
-    GNUNET_PQ_query_param_string (instance_id),
-    GNUNET_PQ_query_param_auto_from_type (tip_id),
-    TALER_PQ_query_param_amount (total_picked_up),
-    GNUNET_PQ_query_param_auto_from_type (pickup_id),
-    TALER_PQ_query_param_amount (total_requested),
-    GNUNET_PQ_query_param_end
-  };
+  enum GNUNET_DB_QueryStatus qs;
+  unsigned int retries = 0;
 
   check_connection (pg);
-  return GNUNET_PQ_eval_prepared_non_select (pg->conn,
-                                             "insert_pickup", // FIXME: write SQL!
+RETRY:
+  if (MAX_RETRIES < ++retries)
+    return GNUNET_DB_STATUS_SOFT_ERROR;
+  if (GNUNET_OK !=
+      postgres_start (pg,
+                      "insert pickup"))
+  {
+    GNUNET_break (0);
+    return GNUNET_DB_STATUS_HARD_ERROR;
+  }
+
+  {
+    struct GNUNET_PQ_QueryParam params[] = {
+      GNUNET_PQ_query_param_string (instance_id),
+      GNUNET_PQ_query_param_auto_from_type (tip_id),
+      GNUNET_PQ_query_param_auto_from_type (pickup_id),
+      TALER_PQ_query_param_amount (total_requested),
+      GNUNET_PQ_query_param_end
+    };
+
+    check_connection (pg);
+    qs = GNUNET_PQ_eval_prepared_non_select (pg->conn,
+                                             "insert_pickup",
                                              params);
+    if (0 > qs)
+    {
+      GNUNET_break (GNUNET_DB_STATUS_SOFT_ERROR == qs);
+      postgres_rollback (pg);
+      if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
+        goto RETRY;
+      return qs;
+    }
+  }
+
+  {
+    struct GNUNET_PQ_QueryParam params[] = {
+      GNUNET_PQ_query_param_auto_from_type (tip_id),
+      TALER_PQ_query_param_amount (total_picked_up),
+      GNUNET_PQ_query_param_end
+    };
+
+    check_connection (pg);
+    qs = GNUNET_PQ_eval_prepared_non_select (pg->conn,
+                                             "update_picked_up_tip",
+                                             params);
+    if (0 > qs)
+    {
+      GNUNET_break (GNUNET_DB_STATUS_SOFT_ERROR == qs);
+      postgres_rollback (pg);
+      if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
+        goto RETRY;
+      return qs;
+    }
+  }
+  {
+    uint64_t reserve_serial;
+    struct TALER_Amount reserve_picked_up;
+    {
+      struct GNUNET_PQ_QueryParam params[] = {
+        GNUNET_PQ_query_param_string (instance_id),
+        GNUNET_PQ_query_param_auto_from_type (&tip_id),
+        GNUNET_PQ_query_param_end
+      };
+      struct GNUNET_PQ_ResultSpec rs[] = {
+        TALER_PQ_RESULT_SPEC_AMOUNT ("tips_picked_up",
+                                     &reserve_picked_up),
+        GNUNET_PQ_result_spec_uint64 ("reserve_serial",
+                                      &reserve_serial),
+        GNUNET_PQ_result_spec_end
+
+      };
+
+      qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
+                                                     "lookup_picked_up_reserve",
+                                                     params,
+                                                     rs);
+      if (0 > qs)
+      {
+        GNUNET_break (GNUNET_DB_STATUS_SOFT_ERROR == qs);
+        postgres_rollback (pg);
+        if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
+          goto RETRY;
+        return qs;
+      }
+    }
+    if (0 <=
+        TALER_amount_add (&reserve_picked_up,
+                          &reserve_picked_up,
+                          total_requested))
+    {
+      GNUNET_break (0);
+      return GNUNET_DB_STATUS_HARD_ERROR;
+    }
+
+    {
+      struct GNUNET_PQ_QueryParam params[] = {
+        GNUNET_PQ_query_param_uint64 (&reserve_serial),
+        TALER_PQ_query_param_amount (&reserve_picked_up),
+        GNUNET_PQ_query_param_end
+      };
+
+      check_connection (pg);
+      qs = GNUNET_PQ_eval_prepared_non_select (pg->conn,
+                                               "update_picked_up_reserve",
+                                               params);
+      if (0 > qs)
+      {
+        GNUNET_break (GNUNET_DB_STATUS_SOFT_ERROR == qs);
+        postgres_rollback (pg);
+        if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
+          goto RETRY;
+        return qs;
+      }
+    }
+  }
+  qs = postgres_commit (pg);
+  if (GNUNET_DB_STATUS_SUCCESS_NO_RESULTS == qs)
+    return GNUNET_DB_STATUS_SUCCESS_ONE_RESULT;
+  if (GNUNET_DB_STATUS_SOFT_ERROR == qs)
+    goto RETRY;
+  GNUNET_break (GNUNET_DB_STATUS_HARD_ERROR != qs);
+  return qs;
 }
 
 
@@ -4653,7 +4930,7 @@ postgres_insert_pickup_blind_signature (
 
   check_connection (pg);
   return GNUNET_PQ_eval_prepared_non_select (pg->conn,
-                                             "insert_pickup_blind_signature", // FIXME: write SQL!
+                                             "insert_pickup_blind_signature",
                                              params);
 }
 
@@ -4949,9 +5226,8 @@ postgres_enable_tip_reserve_TR (void *cls,
   enum GNUNET_DB_QueryStatus qs;
   struct GNUNET_TIME_Absolute new_expiration;
   struct TALER_Amount new_balance;
-  unsigned int retries;
+  unsigned int retries = 0;
 
-  retries = 0;
   check_connection (pg);
 RETRY:
   if (MAX_RETRIES < ++retries)
@@ -7224,6 +7500,178 @@ libtaler_plugin_merchantdb_postgres_init (void *cls)
                             "       FROM merchant_instances"
                             "      WHERE merchant_id=$1)",
                             2),
+    /* For postgres_authorize_tip() */
+    GNUNET_PQ_make_prepare ("lookup_reserve_for_tip",
+                            "SELECT"
+                            " reserve_pub"
+                            ",expiration"
+                            ",exchange_initial_balance_val"
+                            ",exchange_initial_balance_frac"
+                            ",tips_committed_val"
+                            ",tips_committed_frac"
+                            " FROM merchant_tip_reserves"
+                            " WHERE expiration < $2 "
+                            "  AND exchange_initial_balance_val - tips_committed_val >= $3"
+                            "  AND merchant_serial ="
+                            "     (SELECT merchant_serial"
+                            "        FROM merchant_instances"
+                            "       WHERE merchant_id=$1)",
+                            3),
+
+    /* For postgres_authorize_tip() */
+    GNUNET_PQ_make_prepare ("lookup_reserve_status",
+                            "SELECT"
+                            " expiration"
+                            ",exchange_initial_balance_val"
+                            ",exchange_initial_balance_frac"
+                            ",tips_committed_val"
+                            ",tips_committed_frac"
+                            " FROM merchant_tip_reserves"
+                            " WHERE reserve_pub = $2"
+                            "   AND merchant_serial ="
+                            "     (SELECT merchant_serial"
+                            "        FROM merchant_instances"
+                            "       WHERE merchant_id=$1)",
+                            2),
+    /* For postgres_authorize_tip() */
+    GNUNET_PQ_make_prepare ("update_reserve_tips_committed",
+                            "UPDATE merchant_tip_reserves SET"
+                            " tips_committed_val=$3"
+                            ",tips_committed_frac=$4"
+                            " WHERE reserve_pub = $2"
+                            "   AND merchant_serial ="
+                            "     (SELECT merchant_serial"
+                            "        FROM merchant_instances"
+                            "       WHERE merchant_id=$1)",
+                            4),
+    /* For postgres_authorize_tip() */
+    GNUNET_PQ_make_prepare ("insert_tip",
+                            "INSERT INTO merchant_tips"
+                            "(reserve_serial"
+                            ",justification"
+                            ",next_url"
+                            ",expiration"
+                            ",amount_val"
+                            ",amount_frac"
+                            ") "
+                            "SELECT"
+                            " reserve_serial, $3, $4, $5, $6, $7"
+                            " FROM merchant_tip_reserves"
+                            " WHERE reserve_pub=$2"
+                            "  AND reserve_serial = "
+                            "     (SELECT merchant_serial"
+                            "        FROM merchant_instances"
+                            "       WHERE merchant_id=$1)",
+                            7),
+    /* For postgres_lookup_pickup() */
+    GNUNET_PQ_make_prepare ("lookup_pickup",
+                            "SELECT"
+                            " exchange_url"
+                            ",reserve_priv"
+                            ",pickup_serial"
+                            " FROM merchant_tip_pickups"
+                            " JOIN merchant_tips USING (tip_serial)"
+                            " JOIN merchant_tip_reserves USING (reserve_serial)"
+                            " JOIN merchant_tip_reserve_keys USING (reserve_serial)"
+                            " WHERE pickup_id = $2"
+                            "   AND tip_id = $3"
+                            "   AND merchant_serial ="
+                            "     (SELECT merchant_serial"
+                            "        FROM merchant_instances"
+                            "       WHERE merchant_id=$1)",
+                            2),
+    /* For postgres_lookup_pickup() */
+    GNUNET_PQ_make_prepare ("lookup_pickup_signatures",
+                            "SELECT"
+                            " coin_offset"
+                            ",blind_sig"
+                            " FROM merchant_tip_pickup_signatures"
+                            " WHERE pickup_serial = $1",
+                            1),
+
+    /* For postgres_lookup_tip() */
+    GNUNET_PQ_make_prepare ("lookup_tip",
+                            "SELECT"
+                            " amount_val"
+                            ",amount_frac"
+                            ",picked_up_val"
+                            ",picked_up_frac"
+                            ",merchant_tips.expiration"
+                            ",exchange_url"
+                            ",reserve_priv"
+                            " FROM merchant_tips"
+                            " JOIN merchant_tip_reserves USING (reserve_serial)"
+                            " JOIN merchant_tip_reserve_keys USING (reserve_serial)"
+                            " WHERE tip_id = $2"
+                            "   AND merchant_serial ="
+                            "     (SELECT merchant_serial"
+                            "        FROM merchant_instances"
+                            "       WHERE merchant_id=$1)",
+                            2),
+    /* for postgres_lookup_tip_details() */
+    // FIXME!
+    /* for postgres_lookup_tip_details() */
+    // FIXME!
+
+    /* for postgres_insert_pickup() */
+    GNUNET_PQ_make_prepare ("insert_pickup",
+                            "INSERT INTO merchant_tip_pickups"
+                            "(tip_serial"
+                            ",pickup_id"
+                            ",amount_val"
+                            ",amount_frac"
+                            ") "
+                            "SELECT"
+                            " tip_serial, $3, $4, $5"
+                            " FROM merchant_tips"
+                            " WHERE tip_id=$2"
+                            "  AND reserve_serial = "
+                            "     (SELECT merchant_serial"
+                            "        FROM merchant_instances"
+                            "       WHERE merchant_id=$1)",
+                            5),
+    /* for postgres_insert_pickup() */
+    GNUNET_PQ_make_prepare ("update_picked_up_tip",
+                            "UPDATE merchant_tips SET"
+                            " picked_up_val=$2"
+                            ",picked_up_frac=$3"
+                            ",was_picked_up = ($2 = amount_val AND $3 = amount_frac)"
+                            " WHERE tip_id = $1",
+                            3),
+    /* for postgres_insert_pickup() */
+    GNUNET_PQ_make_prepare ("lookup_picked_up_reserve",
+                            "SELECT"
+                            " reserve_serial"
+                            ",tips_picked_up_val"
+                            ",tips_picked_up_frac"
+                            " FROM merchant_tip_reserves"
+                            " JOIN merchant_tips USING (reserve_serial)"
+                            " WHERE tip_id=$2"
+                            "   AND merchant_serial ="
+                            "     (SELECT merchant_serial"
+                            "        FROM merchant_instances"
+                            "       WHERE merchant_id=$1)",
+                            2),
+    /* for postgres_insert_pickup() */
+    GNUNET_PQ_make_prepare ("update_picked_up_reserve",
+                            "UPDATE merchant_tip_reserves SET"
+                            " tips_picked_up_val=$2"
+                            ",tips_picked_up_frac=$3"
+                            " WHERE reserve_serial = $1",
+                            3),
+    /* for postgres_insert_pickup_blind_signature() */
+    GNUNET_PQ_make_prepare ("insert_pickup_blind_signature",
+                            "INSERT INTO merchant_tip_pickup_signatures"
+                            "(pickup_serial"
+                            ",coin_offset"
+                            ",blind_sig"
+                            ") "
+                            "SELECT"
+                            " pickup_serial, $2, $3"
+                            " FROM merchant_tip_pickups"
+                            " WHERE pickup_id=$1",
+                            3),
+
     /* OLD API: */
 #if 0
     GNUNET_PQ_make_prepare ("insert_contract_terms",