From c7d8e10b4d6491377076927b9222025afa26d8e5 Mon Sep 17 00:00:00 2001 From: Marcello Stanisci Date: Fri, 17 Mar 2017 19:01:26 +0100 Subject: 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 2e530e9a..44e8216c 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", ×tamp, - "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 5381888f..257f65c6 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 a18b8079..de01023b 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; @@ -376,12 +378,23 @@ run (void *cls) &out, // plain data &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 0dfbea8b..f6cbcd3d 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); -- cgit v1.2.3