[GNUnet-SVN] [taler-merchant] branch master updated: helper db methods for /history. Prepared statements have still problems with 'FETCH FIRST'.. To be fixed.

[gnunet]

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

marcello pushed a commit to branch master
in repository merchant.

The following commit(s) were added to refs/heads/master by this push:
     new c7d8e10  helper db methods for /history. Prepared statements have 
still problems with 'FETCH FIRST'.. To be fixed.
c7d8e10 is described below

commit c7d8e10b4d6491377076927b9222025afa26d8e5
Author: Marcello Stanisci <address@hidden>
AuthorDate: Fri Mar 17 19:01:26 2017 +0100

    helper db methods for /history. Prepared statements have
    still problems with 'FETCH FIRST'.. To be fixed.
---
 src/backend/taler-merchant-httpd_history.c |  49 ++++++-----
 src/backenddb/plugin_merchantdb_postgres.c | 132 +++++++++++++++++++++++++++--
 src/backenddb/test_merchantdb.c            |  13 +++
 src/include/taler_merchantdb_plugin.h      |  46 ++++++++--
 4 files changed, 208 insertions(+), 32 deletions(-)

diff --git a/src/backend/taler-merchant-httpd_history.c 
b/src/backend/taler-merchant-httpd_history.c
index 2e530e9..44e8216 100644
--- a/src/backend/taler-merchant-httpd_history.c
+++ b/src/backend/taler-merchant-httpd_history.c
@@ -28,34 +28,31 @@
 /**
  * Index to the first row to return in response to /history.
  */
-unsigned int start;
+static int start = -1;
 
 /**
  * How many rows we are to return in response to /history.
  */
-unsigned int delta;
+static unsigned int delta;
 
 /**
  * Index to the current row being processed.
  */
-unsigned int current = 0;
+static unsigned int current = 0;
 
 
 /**
  * Function called with information about a transaction.
  *
  * @param cls closure
- * @param merchant_pub merchant's public key
- * @param exchange_uri URI of the exchange
- * @param transaction_id proposal's transaction id
- * @param h_wire hash of our wire details
- * @param timestamp time of the confirmation
- * @param refund refund deadline
- * @param total_amount total amount we receive for the contract after fees
+ * @param order_id transaction's order ID.
+ * @param row_id serial numer of the transaction in the table,
+ * used as index by the frontend to skip previous results.
  */
 static void
 pd_cb (void *cls,
        const char *order_id,
+       unsigned int row_id,
        const json_t *proposal_data)
 {
   json_t *response = cls;
@@ -65,10 +62,11 @@ pd_cb (void *cls,
   json_t *instance;
 
   GNUNET_assert (-1 != json_unpack ((json_t *) proposal_data,
-                                    "{s:o, s:o, s:{s:o}}",
+                                    "{s:o, s:o, s:{s:o}, s:I}",
                                     "amount", &amount,
                                     "timestamp", &timestamp,
-                                    "merchant", "instance", &instance));
+                                    "merchant", "instance", &instance,
+                                    "row_id", (json_int_t) row_id));
 
   if ( (current >= start) &&
        (current < start + delta) )
@@ -88,7 +86,6 @@ pd_cb (void *cls,
                                               entry));
   }
 
-  // FIXME to zero after returned.
   current++;
 }
 
@@ -160,7 +157,7 @@ MH_handler_history (struct TMH_RequestHandler *rh,
                                          TALER_EC_HISTORY_INSTANCE_UNKNOWN,
                                          "instance");
   }
-  start = 0;
+
   delta = 20;
 
   str = MHD_lookup_connection_value (connection,
@@ -194,11 +191,25 @@ MH_handler_history (struct TMH_RequestHandler *rh,
               "Querying history back to %s\n",
               GNUNET_STRINGS_absolute_time_to_string (date));
 
-  ret = db->find_proposal_data_by_date (db->cls,
-                                        date,
-                                        &mi->pubkey,
-                                        pd_cb,
-                                        response);
+  if (0 > start)
+    ret = db->find_proposal_data_by_date (db->cls,
+                                          date,
+                                          &mi->pubkey,
+                                          delta,
+                                          pd_cb,
+                                          response);
+  else
+    ret = db->find_proposal_data_by_date_and_range (db->cls,
+                                                    date,
+                                                    &mi->pubkey,
+                                                    start,
+                                                    delta,
+                                                    pd_cb,
+                                                    response);
+    
+
+
+
   current = 0;
   if (GNUNET_SYSERR == ret)
   {
diff --git a/src/backenddb/plugin_merchantdb_postgres.c 
b/src/backenddb/plugin_merchantdb_postgres.c
index 5381888..257f65c 100644
--- a/src/backenddb/plugin_merchantdb_postgres.c
+++ b/src/backenddb/plugin_merchantdb_postgres.c
@@ -170,6 +170,7 @@ postgres_initialize (void *cls)
            ",proposal_data BYTEA NOT NULL"
            ",h_proposal_data BYTEA NOT NULL"
            ",timestamp INT8 NOT NULL"
+           ",row_id SERIAL"
           ",PRIMARY KEY (order_id, merchant_pub)"
            ");");
 
@@ -316,12 +317,29 @@ postgres_initialize (void *cls)
               "SELECT"
               " proposal_data"
               ",order_id"
+              ",row_id"
               " FROM merchant_proposal_data"
               " WHERE"
-              " timestamp>=$1"
+              " timestamp<$1"
               " AND merchant_pub=$2"
-              " ORDER BY timestamp DESC",
-              2);
+              " ORDER BY timestamp DESC, row_id DESC"
+              " FETCH FIRST $3 ROWS ONLY",
+              3);
+
+  PG_PREPARE (pg,
+              "find_proposal_data_by_date_and_range",
+              "SELECT"
+              " proposal_data"
+              ",order_id"
+              ",row_id"
+              " FROM merchant_proposal_data"
+              " WHERE"
+              " timestamp<$1"
+              " AND merchant_pub=$2"
+              " AND row_id<$3"
+              " ORDER BY timestamp DESC, row_id DESC"
+              " FETCH FIRST $4 ROWS ONLY",
+              4);
 
   /* Setup prepared "SELECT" statements */
   PG_PREPARE (pg,
@@ -798,18 +816,112 @@ postgres_store_transfer_to_proof (void *cls,
 }
 
 /**
- * Return transactions younger than the given date
+ * Return proposals whose timestamp are older than `date`.
+ * Among those proposals, only those ones being between the
+ * start-th and (start-nrows)-th record are returned.  The rows
+ * are sorted having the youngest first.
  *
- * @param cls our plugin handle
- * @param date limit to transactions' age
- * @param cb function to call with transaction data, can be NULL
+ * @param cls our plugin handle.
+ * @param date only results older than this date are returned.
+ * @param merchant_pub instance's public key; only rows related to this
+ * instance are returned.
+ * @param start only rows with serial id less than start are returned.
+ * @param nrows only nrows rows are returned.
+ * @param cb function to call with transaction data, can be NULL.
  * @param cb_cls closure for @a cb
  * @return numer of found tuples, #GNUNET_SYSERR upon error
  */
 static int
+postgres_find_proposal_data_by_date_and_range (void *cls,
+                                               struct GNUNET_TIME_Absolute 
date,
+                                               const struct 
TALER_MerchantPublicKeyP *merchant_pub,
+                                               unsigned int start,
+                                               unsigned int nrows,
+                                               
TALER_MERCHANTDB_ProposalDataCallback cb,
+                                               void *cb_cls)
+{
+
+  struct PostgresClosure *pg = cls;
+  PGresult *result;
+  unsigned int n;
+  unsigned int i;
+
+  struct GNUNET_PQ_QueryParam params[] = {
+    GNUNET_PQ_query_param_absolute_time (&date),
+    GNUNET_PQ_query_param_auto_from_type (merchant_pub),
+    GNUNET_PQ_query_param_uint32 (&start),
+    GNUNET_PQ_query_param_uint32 (&nrows),
+    GNUNET_PQ_query_param_end
+  };
+  result = GNUNET_PQ_exec_prepared (pg->conn,
+                                    "find_proposal_data_by_date_and_range",
+                                    params);
+      if (PGRES_TUPLES_OK != PQresultStatus (result))
+      {
+        BREAK_DB_ERR (result);
+        PQclear (result);
+        return GNUNET_SYSERR;
+      }
+      if (0 == (n = PQntuples (result)) || NULL == cb)
+      {
+        PQclear (result);
+        return n;
+      }
+      for (i = 0; i < n; i++)
+      {
+        char *order_id;
+        json_t *proposal_data;
+        unsigned int row_id;
+
+        struct GNUNET_PQ_ResultSpec rs[] = {
+          GNUNET_PQ_result_spec_string ("order_id",
+                                        &order_id),
+          TALER_PQ_result_spec_json ("proposal_data",
+                                     &proposal_data),
+          GNUNET_PQ_result_spec_uint32 ("row_id",
+                                        &row_id),
+          GNUNET_PQ_result_spec_end
+        };
+
+        if (GNUNET_OK !=
+            GNUNET_PQ_extract_result (result,
+                                      rs,
+                                      i))
+        {
+          GNUNET_break (0);
+          PQclear (result);
+          return GNUNET_SYSERR;
+        }
+        cb (cb_cls,
+            order_id,
+            row_id,
+            proposal_data);
+
+        GNUNET_PQ_cleanup_result (rs);
+      }
+      PQclear (result);
+      return n;
+    }
+
+
+    /**
+     * Return proposals whose timestamp are older than `date`.
+     * The rows are sorted having the youngest first.
+     *
+     * @param cls our plugin handle.
+     * @param date only results older than this date are returned.
+     * @param merchant_pub instance's public key; only rows related to this
+     * instance are returned.
+     * @param nrows at most nrows rows are returned.
+     * @param cb function to call with transaction data, can be NULL.
+     * @param cb_cls closure for @a cb
+ * @return numer of found tuples, #GNUNET_SYSERR upon error
+ */
+static int
 postgres_find_proposal_data_by_date (void *cls,
                                      struct GNUNET_TIME_Absolute date,
                                      const struct TALER_MerchantPublicKeyP 
*merchant_pub,
+                                     unsigned int nrows,
                                      TALER_MERCHANTDB_ProposalDataCallback cb,
                                      void *cb_cls)
 {
@@ -822,6 +934,7 @@ postgres_find_proposal_data_by_date (void *cls,
   struct GNUNET_PQ_QueryParam params[] = {
     GNUNET_PQ_query_param_absolute_time (&date),
     GNUNET_PQ_query_param_auto_from_type (merchant_pub),
+    GNUNET_PQ_query_param_uint32 (&nrows),
     GNUNET_PQ_query_param_end
   };
   result = GNUNET_PQ_exec_prepared (pg->conn,
@@ -842,12 +955,15 @@ postgres_find_proposal_data_by_date (void *cls,
   {
     char *order_id;
     json_t *proposal_data;
+    unsigned int row_id;
 
     struct GNUNET_PQ_ResultSpec rs[] = {
       GNUNET_PQ_result_spec_string ("order_id",
                                     &order_id),
       TALER_PQ_result_spec_json ("proposal_data",
                                  &proposal_data),
+      GNUNET_PQ_result_spec_uint32 ("row_id",
+                                    &row_id),
       GNUNET_PQ_result_spec_end
     };
 
@@ -862,6 +978,7 @@ postgres_find_proposal_data_by_date (void *cls,
     }
     cb (cb_cls,
         order_id,
+        row_id,
         proposal_data);
 
     GNUNET_PQ_cleanup_result (rs);
@@ -1448,6 +1565,7 @@ libtaler_plugin_merchantdb_postgres_init (void *cls)
   plugin->insert_proposal_data = &postgres_insert_proposal_data;
   plugin->find_proposal_data = &postgres_find_proposal_data;
   plugin->find_proposal_data_by_date = &postgres_find_proposal_data_by_date;
+  plugin->find_proposal_data_by_date_and_range = 
&postgres_find_proposal_data_by_date_and_range;
   plugin->find_proposal_data_from_hash = 
&postgres_find_proposal_data_from_hash;
 
   return plugin;
diff --git a/src/backenddb/test_merchantdb.c b/src/backenddb/test_merchantdb.c
index a18b807..de01023 100644
--- a/src/backenddb/test_merchantdb.c
+++ b/src/backenddb/test_merchantdb.c
@@ -193,11 +193,13 @@ transaction_cb (void *cls,
  *
  * @param cls closure
  * @param order_id order id
+ * @param row_id row id in db
  * @param proposal_data proposal data
  */
 static void
 pd_cb (void *cls,
        const char *order_id,
+       unsigned int row_id,
        const json_t *proposal_data)
 {
   return;
@@ -377,11 +379,22 @@ run (void *cls)
                                                 &h_proposal_data2,
                                                 &merchant_pub));
   FAILIF (1 !=
+          plugin->find_proposal_data_by_date_and_range (plugin->cls,
+                                                        fake_now,
+                                                        &merchant_pub,
+                                                        0,
+                                                        5,
+                                                        pd_cb,
+                                                        NULL));
+
+  FAILIF (1 !=
           plugin->find_proposal_data_by_date (plugin->cls,
                                               fake_now,
                                               &merchant_pub,
+                                              5,
                                               pd_cb,
                                               NULL));
+
   FAILIF (GNUNET_OK !=
           plugin->store_transaction (plugin->cls,
                                      &h_proposal_data,
diff --git a/src/include/taler_merchantdb_plugin.h 
b/src/include/taler_merchantdb_plugin.h
index 0dfbea8..f6cbcd3 100644
--- a/src/include/taler_merchantdb_plugin.h
+++ b/src/include/taler_merchantdb_plugin.h
@@ -36,11 +36,13 @@ struct TALER_MERCHANTDB_Plugin;
  *
  * @param cls closure
  * @param order_id order id
+ * @param row_id serial numer of the transaction in the table,
  * @param proposal_data proposal data related to order id
  */
  typedef void
  (*TALER_MERCHANTDB_ProposalDataCallback)(void *cls,
                                           const char *order_id,
+                                          unsigned int row_id,
                                           const json_t *proposal_data);
 
 /**
@@ -209,19 +211,51 @@ struct TALER_MERCHANTDB_Plugin
                                    const struct TALER_MerchantPublicKeyP 
*merchant_pub);
 
 
+
   /**
-   * Return proposal data and order id for all proposals younger than
-   * date.
+   * Return proposals whose timestamp are older than `date`.
+   * Among those proposals, only those ones being between the
+   * start-th and (start-nrows)-th record are returned.  The rows
+   * are sorted having the youngest first.
    *
-   * @param cls closure
-   * @param date limit to the oldest record
-   * @param cb callback called with proposal data and order id
-   * @param cb_cls closure for cb
+   * @param cls our plugin handle.
+   * @param date only results older than this date are returned.
+   * @param merchant_pub instance's public key; only rows related to this
+   * instance are returned.
+   * @param start only rows with serial id less than start are returned.
+   * @param nrows only nrows rows are returned.
+   * @param cb function to call with transaction data, can be NULL.
+   * @param cb_cls closure for @a cb
+   * @return numer of found tuples, #GNUNET_SYSERR upon error
+   */
+  int
+  (*find_proposal_data_by_date_and_range) (void *cls,
+                                           struct GNUNET_TIME_Absolute date,
+                                           const struct 
TALER_MerchantPublicKeyP *merchant_pub,
+                                           unsigned int start,
+                                           unsigned int nrows,
+                                           
TALER_MERCHANTDB_ProposalDataCallback cb,
+                                           void *cb_cls);
+
+  /**
+   * Return proposals whose timestamp are older than `date`.
+   * The rows are sorted having the youngest first.*
+   *
+   * @param cls our plugin handle.
+   * @param date only results older than this date are returned.
+   * @param merchant_pub instance's public key; only rows related to this
+   * instance are returned.
+   * @param start the first start-1 rows are not returned.
+   * @param nrows only nrows rows are returned.
+   * @param cb function to call with transaction data, can be NULL.
+   * @param cb_cls closure for @a cb
+   * @return numer of found tuples, #GNUNET_SYSERR upon error
    */
   int
   (*find_proposal_data_by_date) (void *cls,
                                  struct GNUNET_TIME_Absolute date,
                                  const struct TALER_MerchantPublicKeyP 
*merchant_pub,
+                                 unsigned int nrows,
                                  TALER_MERCHANTDB_ProposalDataCallback cb,
                                  void *cb_cls);
 

-- 
To stop receiving notification emails like this one, please contact
address@hidden