[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[taler-merchant] 59/277: sql-ing for GET /orders
From: |
gnunet |
Subject: |
[taler-merchant] 59/277: sql-ing for GET /orders |
Date: |
Sun, 05 Jul 2020 20:49:32 +0200 |
This is an automated email from the git hooks/post-receive script.
grothoff pushed a commit to branch master
in repository merchant.
commit 5152270454463960330a28638661420fecff0b82
Author: Christian Grothoff <christian@grothoff.org>
AuthorDate: Sun Apr 26 23:14:21 2020 +0200
sql-ing for GET /orders
---
src/backenddb/merchant-0001.sql | 28 +-
src/backenddb/plugin_merchantdb_postgres.c | 840 ++++++++++++++++++++++++++++-
src/include/taler_merchantdb_plugin.h | 6 +-
3 files changed, 861 insertions(+), 13 deletions(-)
diff --git a/src/backenddb/merchant-0001.sql b/src/backenddb/merchant-0001.sql
index b92cd6f..0682da9 100644
--- a/src/backenddb/merchant-0001.sql
+++ b/src/backenddb/merchant-0001.sql
@@ -189,6 +189,7 @@ CREATE TABLE IF NOT EXISTS merchant_orders
REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
,order_id VARCHAR NOT NULL
,pay_deadline INT8 NOT NULL
+ ,creation_time INT8 NOT NULL
,contract_terms BYTEA NOT NULL
,UNIQUE (merchant_serial, order_id)
);
@@ -203,6 +204,9 @@ COMMENT ON COLUMN merchant_orders.pay_deadline
CREATE INDEX IF NOT EXISTS merchant_orders_by_expiration
ON merchant_orders
(pay_deadline);
+CREATE INDEX IF NOT EXISTS merchant_orders_by_creation_time
+ ON merchant_orders
+ (creation_time);
CREATE TABLE IF NOT EXISTS merchant_order_locks
(product_serial BIGINT NOT NULL
@@ -220,23 +224,25 @@ COMMENT ON COLUMN merchant_order_locks.total_locked
IS 'how many units of the product does this lock reserve';
CREATE TABLE IF NOT EXISTS merchant_contract_terms
- (contract_serial BIGSERIAL PRIMARY KEY
+ (order_serial BIGINT PRIMARY KEY
,merchant_serial BIGINT NOT NULL
REFERENCES merchant_instances (merchant_serial) ON DELETE CASCADE
- ,contract_id VARCHAR NOT NULL
+ ,order_id VARCHAR NOT NULL
,contract_terms BYTEA NOT NULL
,h_contract_terms BYTEA NOT NULL CHECK (LENGTH(h_contract_terms)=64)
+ ,creation_time INT8 NOT NULL
,pay_deadline INT8 NOT NULL
,refund_deadline INT8 NOT NULL
,paid BOOLEAN DEFAULT FALSE NOT NULL
+ ,wired BOOLEAN DEFAULT FALSE NOT NULL
,fulfillment_url VARCHAR NOT NULL
,session_id VARCHAR NOT NULL
- ,UNIQUE (merchant_serial, contract_id)
+ ,UNIQUE (merchant_serial, order_id)
,UNIQUE (merchant_serial, h_contract_terms)
);
COMMENT ON TABLE merchant_contract_terms
IS 'Contracts are orders that have been claimed by a wallet';
-COMMENT ON COLUMN merchant_contract_terms.contract_id
+COMMENT ON COLUMN merchant_contract_terms.order_id
IS 'Not a foreign key into merchant_orders because paid contracts persist
after expiration';
COMMENT ON COLUMN merchant_contract_terms.merchant_serial
IS 'Identifies the instance offering the contract';
@@ -248,6 +254,8 @@ COMMENT ON COLUMN merchant_contract_terms.refund_deadline
IS 'By what times do refunds have to be approved (useful to reject refund
requests)';
COMMENT ON COLUMN merchant_contract_terms.paid
IS 'true implies the customer paid for this contract; order should be
DELETEd from merchant_orders once paid is set to release merchant_order_locks;
paid remains true even if the payment was later refunded';
+COMMENT ON COLUMN merchant_contract_terms.wired
+ IS 'true implies the exchange wired us the full amount for all non-refunded
payments under this contract';
COMMENT ON COLUMN merchant_contract_terms.fulfillment_url
IS 'also included in contract_terms, but we need it here to SELECT on it
during repurchase detection';
COMMENT ON COLUMN merchant_contract_terms.session_id
@@ -269,8 +277,8 @@ CREATE INDEX IF NOT EXISTS
merchant_contract_terms_by_merchant_session_and_fulfi
CREATE TABLE IF NOT EXISTS merchant_deposits
(deposit_serial BIGSERIAL PRIMARY KEY
- ,contract_serial BIGINT
- REFERENCES merchant_contract_terms (contract_serial) ON DELETE CASCADE
+ ,order_serial BIGINT
+ REFERENCES merchant_contract_terms (order_serial) ON DELETE CASCADE
,coin_pub BYTEA NOT NULL CHECK (LENGTH(coin_pub)=32)
,exchange_url VARCHAR NOT NULL
,amount_with_fee_val INT8 NOT NULL
@@ -287,7 +295,7 @@ CREATE TABLE IF NOT EXISTS merchant_deposits
,exchange_timestamp INT8 NOT NULL
,account_serial BIGINT NOT NULL
REFERENCES merchant_accounts (account_serial) ON DELETE CASCADE
- ,UNIQUE (contract_serial, coin_pub)
+ ,UNIQUE (order_serial, coin_pub)
);
COMMENT ON TABLE merchant_deposits
IS 'Table with the deposit confirmations for each coin we deposited at the
exchange';
@@ -300,14 +308,14 @@ COMMENT ON COLUMN merchant_deposits.wire_fee_val
CREATE TABLE IF NOT EXISTS merchant_refunds
(refund_serial BIGSERIAL PRIMARY KEY
- ,contract_serial BIGINT NOT NULL
- REFERENCES merchant_contract_terms (contract_serial) ON DELETE CASCADE
+ ,order_serial BIGINT NOT NULL
+ REFERENCES merchant_contract_terms (order_serial) ON DELETE CASCADE
,rtransaction_id BIGINT NOT NULL
,coin_pub BYTEA NOT NULL
,reason VARCHAR NOT NULL
,refund_amount_val INT8 NOT NULL
,refund_amount_frac INT4 NOT NULL
- ,UNIQUE (contract_serial, coin_pub, rtransaction_id)
+ ,UNIQUE (order_serial, coin_pub, rtransaction_id)
);
COMMENT ON TABLE merchant_deposits
IS 'Refunds approved by the merchant (backoffice) logic, excludes abort
refunds';
diff --git a/src/backenddb/plugin_merchantdb_postgres.c
b/src/backenddb/plugin_merchantdb_postgres.c
index 280b9f5..bb43fd4 100644
--- a/src/backenddb/plugin_merchantdb_postgres.c
+++ b/src/backenddb/plugin_merchantdb_postgres.c
@@ -1114,6 +1114,140 @@ postgres_lookup_order (void *cls,
}
+/**
+ * Context used for postgres_lookup_orders().
+ */
+struct LookupOrdersContext
+{
+ /**
+ * Function to call with the results.
+ */
+ TALER_MERCHANTDB_OrdersCallback cb;
+
+ /**
+ * Closure for @a cb.
+ */
+ void *cb_cls;
+
+ /**
+ * Internal result.
+ */
+ enum GNUNET_DB_QueryStatus qs;
+};
+
+
+/**
+ * Function to be called with the results of a SELECT statement
+ * that has returned @a num_results results about orders.
+ *
+ * @param[in,out] cls of type `struct LookupOrdersContext *`
+ * @param result the postgres result
+ * @param num_result the number of results in @a result
+ */
+static void
+lookup_orders_cb (void *cls,
+ PGresult *result,
+ unsigned int num_results)
+{
+ struct LookupOrdersContext *plc = cls;
+
+ for (unsigned int i = 0; i < num_results; i++)
+ {
+ char *order_id;
+ uint64_t order_serial;
+ struct GNUNET_TIME_Absolute ts;
+ struct GNUNET_PQ_ResultSpec rs[] = {
+ GNUNET_PQ_result_spec_string ("order_id",
+ &order_id),
+ GNUNET_PQ_result_spec_uint64 ("order_serial",
+ &order_serial),
+ GNUNET_PQ_result_spec_absolute_time ("creation_time",
+ &ts),
+ GNUNET_PQ_result_spec_end
+ };
+
+ if (GNUNET_OK !=
+ GNUNET_PQ_extract_result (result,
+ rs,
+ i))
+ {
+ GNUNET_break (0);
+ plc->qs = GNUNET_DB_STATUS_HARD_ERROR;
+ return;
+ }
+ plc->cb (plc->cb_cls,
+ order_id,
+ order_serial,
+ ts);
+ GNUNET_PQ_cleanup_result (rs);
+ }
+}
+
+
+/**
+ * Retrieve orders given the @a instance_id.
+ *
+ * @param cls closure
+ * @param instance_id instance to obtain order of
+ * @param of filter to apply when looking up orders
+ * @param[out] contract_terms where to store the retrieved contract terms,
+ * NULL to only test if the order exists
+ * @return transaction status
+ */
+static enum GNUNET_DB_QueryStatus
+postgres_lookup_orders (void *cls,
+ const char *instance_id,
+ const struct TALER_MERCHANTDB_OrderFilter *of,
+ TALER_MERCHANTDB_OrdersCallback cb,
+ void *cb_cls)
+{
+ struct PostgresClosure *pg = cls;
+ struct LookupOrdersContext plc = {
+ .cb = cb,
+ .cb_cls = cb_cls
+ };
+ uint64_t limit = (of->delta > 0) ? of->delta : -of->delta;
+ uint8_t paid;
+ uint8_t refunded;
+ uint8_t wired;
+ struct GNUNET_PQ_QueryParam params[] = {
+ GNUNET_PQ_query_param_string (instance_id),
+ GNUNET_PQ_query_param_uint64 (&limit),
+ GNUNET_PQ_query_param_uint64 (&of->start_row),
+ GNUNET_PQ_query_param_absolute_time (&of->date),
+ GNUNET_PQ_query_param_auto_from_type (&paid),
+ GNUNET_PQ_query_param_auto_from_type (&refunded),
+ GNUNET_PQ_query_param_auto_from_type (&wired),
+ GNUNET_PQ_query_param_end
+ };
+ enum GNUNET_DB_QueryStatus qs;
+ char stmt[128];
+
+ paid = (TALER_MERCHANTDB_YNA_YES == of->paid);
+ refunded = (TALER_MERCHANTDB_YNA_YES == of->paid);
+ wired = (TALER_MERCHANTDB_YNA_YES == of->paid);
+ /* painfully many cases..., note that "_xxx" being present in 'stmt' merely
+ means that we filter by that variable, the value we filter for is
+ computed above */
+ GNUNET_snprintf (stmt,
+ sizeof (stmt),
+ "lookup_orders_%s%s%s%s",
+ (of->delta > 0) ? "inc" : "dec",
+ (TALER_MERCHANTDB_YNA_ALL == of->paid) ? "" : "_paid",
+ (TALER_MERCHANTDB_YNA_ALL == of->refunded) ? "" :
+ "_refunded",
+ (TALER_MERCHANTDB_YNA_ALL == of->wired) ? "" : "_wired");
+ qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn,
+ stmt,
+ params,
+ &lookup_orders_cb,
+ &plc);
+ if (0 != plc.qs)
+ return plc.qs;
+ return qs;
+}
+
+
/**
* Insert order into the DB.
*
@@ -1132,14 +1266,18 @@ postgres_insert_order (void *cls,
const json_t *contract_terms)
{
struct PostgresClosure *pg = cls;
+ struct GNUNET_TIME_Absolute now;
struct GNUNET_PQ_QueryParam params[] = {
GNUNET_PQ_query_param_string (instance_id),
GNUNET_PQ_query_param_string (order_id),
GNUNET_PQ_query_param_absolute_time (&pay_deadline),
+ GNUNET_PQ_query_param_absolute_time (&now),
TALER_PQ_query_param_json (contract_terms),
GNUNET_PQ_query_param_end
};
+ now = GNUNET_TIME_absolute_get ();
+ (void) GNUNET_TIME_round_abs (&now);
GNUNET_log (GNUNET_ERROR_TYPE_DEBUG,
"inserting order: order_id: %s, instance_id: %s.\n",
order_id,
@@ -4255,22 +4393,719 @@ libtaler_plugin_merchantdb_postgres_init (void *cls)
" WHERE merchant_id=$1)"
" AND merchant_orders.order_id=$2",
2),
+ /* for postgres_lookup_orders() */
+ GNUNET_PQ_make_prepare ("lookup_orders_inc",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_inc_paid",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " NOT BOOL($5)" /* unclaimed orders are never
paid */
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " BOOL($5) = paid"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_inc_refunded",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($5)" /* otherwise $5 is unused and Postgres
unhappy */
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " NOT BOOL ($6)"/* unclaimed orders are never
refunded */
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($5)" /* otherwise $5 is unused and Postgres
unhappy */
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " BOOL($6) = (order_serial IN"
+ " (SELECT order_serial "
+ " FROM merchant_refunds))"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_inc_wired",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($5)" /* otherwise $5 is unused and Postgres
unhappy */
+ ",BOOL($6)" /* otherwise $6 is unused and Postgres
unhappy */
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " NOT BOOL ($7)" /* unclaimed orders are never
wired */
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($5)" /* otherwise $5 is unused and Postgres
unhappy */
+ ",BOOL($6)" /* otherwise $6 is unused and Postgres
unhappy */
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " BOOL($7) = wired"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_inc_paid_refunded",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " NOT BOOL($5)" /* unclaimed orders are never
paid */
+ " AND"
+ " NOT BOOL ($6)"/* unclaimed orders are never
refunded */
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " BOOL($5) = paid"
+ " AND"
+ " BOOL($6) = (order_serial IN"
+ " (SELECT order_serial "
+ " FROM merchant_refunds))"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_inc_paid_wired",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($6)" /* otherwise $6 is unused and Postgres
unhappy */
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " NOT BOOL($5)" /* unclaimed orders are never
paid */
+ " AND"
+ " NOT BOOL ($7)" /* unclaimed orders are never
wired */
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($6)" /* otherwise $6 is unused and Postgres
unhappy */
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " BOOL($5) = paid"
+ " AND"
+ " BOOL($7) = wired"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_inc_refunded_wired",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($5)" /* otherwise $5 is unused and Postgres
unhappy */
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " NOT BOOL ($6)"/* unclaimed orders are never
refunded */
+ " AND"
+ " NOT BOOL ($7)" /* unclaimed orders are never
wired */
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($5)" /* otherwise $5 is unused and Postgres
unhappy */
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " BOOL($6) = (order_serial IN"
+ " (SELECT order_serial "
+ " FROM merchant_refunds))"
+ " AND"
+ " BOOL($7) = wired"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_inc_paid_refunded_wired",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " NOT BOOL($5)" /* unclaimed orders are never
paid */
+ " AND"
+ " NOT BOOL ($6)"/* unclaimed orders are never
refunded */
+ " AND"
+ " NOT BOOL ($7)" /* unclaimed orders are never
wired */
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial > $3"
+ " AND"
+ " creation_time > $4"
+ " AND"
+ " BOOL($5) = paid"
+ " AND"
+ " BOOL($6) = (order_serial IN"
+ " (SELECT order_serial "
+ " FROM merchant_refunds))"
+ " AND"
+ " BOOL($7) = wired"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2)"
+ " ORDER BY order_serial ASC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_dec",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_dec_paid",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " NOT BOOL($5)" /* unclaimed orders are never
paid */
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " BOOL($5) = paid"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_dec_refunded",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($5)" /* otherwise $5 is unused and Postgres
unhappy */
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " NOT BOOL ($6)"/* unclaimed orders are never
refunded */
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($5)" /* otherwise $5 is unused and Postgres
unhappy */
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " BOOL($6) = (order_serial IN"
+ " (SELECT order_serial "
+ " FROM merchant_refunds))"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_dec_wired",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($5)" /* otherwise $5 is unused and Postgres
unhappy */
+ ",BOOL($6)" /* otherwise $6 is unused and Postgres
unhappy */
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " NOT BOOL ($7)" /* unclaimed orders are never
wired */
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($5)" /* otherwise $5 is unused and Postgres
unhappy */
+ ",BOOL($6)" /* otherwise $6 is unused and Postgres
unhappy */
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " BOOL($7) = wired"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_dec_paid_refunded",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " NOT BOOL($5)" /* unclaimed orders are never
paid */
+ " AND"
+ " NOT BOOL ($6)"/* unclaimed orders are never
refunded */
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " BOOL($5) = paid"
+ " AND"
+ " BOOL($6) = (order_serial IN"
+ " (SELECT order_serial "
+ " FROM merchant_refunds))"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_dec_paid_wired",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($6)" /* otherwise $6 is unused and Postgres
unhappy */
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " NOT BOOL($5)" /* unclaimed orders are never
paid */
+ " AND"
+ " NOT BOOL ($7)" /* unclaimed orders are never
wired */
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($6)" /* otherwise $6 is unused and Postgres
unhappy */
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " BOOL($5) = paid"
+ " AND"
+ " BOOL($7) = wired"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_dec_refunded_wired",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($5)" /* otherwise $5 is unused and Postgres
unhappy */
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " NOT BOOL ($6)"/* unclaimed orders are never
refunded */
+ " AND"
+ " NOT BOOL ($7)" /* unclaimed orders are never
wired */
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ ",BOOL($5)" /* otherwise $5 is unused and Postgres
unhappy */
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " BOOL($6) = (order_serial IN"
+ " (SELECT order_serial "
+ " FROM merchant_refunds))"
+ " AND"
+ " BOOL($7) = wired"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2",
+ 7),
+ GNUNET_PQ_make_prepare ("lookup_orders_dec_paid_refunded_wired",
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_orders"
+ " WHERE merchant_orders.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " NOT BOOL($5)" /* unclaimed orders are never
paid */
+ " AND"
+ " NOT BOOL ($6)"/* unclaimed orders are never
refunded */
+ " AND"
+ " NOT BOOL ($7)" /* unclaimed orders are never
wired */
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ "UNION " /* union ensures elements are distinct! */
+ "(SELECT"
+ " order_id"
+ ",order_serial"
+ ",creation_time"
+ " FROM merchant_contract_terms"
+ " WHERE merchant_contract_terms.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND"
+ " order_serial < $3"
+ " AND"
+ " creation_time < $4"
+ " AND"
+ " BOOL($5) = paid"
+ " AND"
+ " BOOL($6) = (order_serial IN"
+ " (SELECT order_serial "
+ " FROM merchant_refunds))"
+ " AND"
+ " BOOL($7) = wired"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2)"
+ " ORDER BY order_serial DESC"
+ " LIMIT $2",
+ 7),
+ /* for postgres_insert_order() */
GNUNET_PQ_make_prepare ("insert_order",
"INSERT INTO merchant_orders"
"(merchant_serial"
",order_id"
",pay_deadline"
+ ",creation_time"
",contract_terms)"
" SELECT merchant_serial,"
- " $2, $3, $4"
+ " $2, $3, $4, $5"
" FROM merchant_instances"
" WHERE merchant_id=$1",
- 4),
+ 5),
+ /* for postgres_unlock_inventory() */
GNUNET_PQ_make_prepare ("unlock_inventory",
"DELETE"
" FROM merchant_inventory_locks"
" WHERE lock_uuid=$1",
1),
+ /* for postgres_insert_order_lock() */
GNUNET_PQ_make_prepare ("insert_order_lock",
"WITH tmp AS"
" (SELECT "
@@ -4795,6 +5630,7 @@ libtaler_plugin_merchantdb_postgres_init (void *cls)
plugin->lock_product = &postgres_lock_product;
plugin->delete_order = &postgres_delete_order;
plugin->lookup_order = &postgres_lookup_order;
+ plugin->lookup_orders = &postgres_lookup_orders;
plugin->insert_order = &postgres_insert_order;
plugin->unlock_inventory = &postgres_unlock_inventory;
plugin->insert_order_lock = &postgres_insert_order_lock;
diff --git a/src/include/taler_merchantdb_plugin.h
b/src/include/taler_merchantdb_plugin.h
index 956c526..3d36a1c 100644
--- a/src/include/taler_merchantdb_plugin.h
+++ b/src/include/taler_merchantdb_plugin.h
@@ -289,10 +289,14 @@ struct TALER_MERCHANTDB_OrderFilter
*
* @param cls a `json_t *` JSON array to build
* @param order_id ID of the order
+ * @param order_serial row of the order in the database
+ * @param timestamp creation time of the order in the database
*/
typedef void
(*TALER_MERCHANTDB_OrdersCallback)(void *cls,
- const char *order_id);
+ const char *order_id,
+ uint64_t order_serial,
+ struct GNUNET_TIME_Absolute timestamp);
/* **************** OLD: ******************** */
--
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.
- [taler-merchant] 74/277: DB API for /abort, (continued)
- [taler-merchant] 74/277: DB API for /abort, gnunet, 2020/07/05
- [taler-merchant] 47/277: typo, gnunet, 2020/07/05
- [taler-merchant] 40/277: add PATCH /instances/ID command, gnunet, 2020/07/05
- [taler-merchant] 51/277: misc. fixes, gnunet, 2020/07/05
- [taler-merchant] 52/277: misc bugfixes, gnunet, 2020/07/05
- [taler-merchant] 64/277: work on POST /orders/ID/claim (unfinished), gnunet, 2020/07/05
- [taler-merchant] 80/277: implement /refund handling, gnunet, 2020/07/05
- [taler-merchant] 81/277: fix/complete inventory management logic, gnunet, 2020/07/05
- [taler-merchant] 67/277: work on /pay API revision, gnunet, 2020/07/05
- [taler-merchant] 65/277: db implementation of claiming, gnunet, 2020/07/05
- [taler-merchant] 59/277: sql-ing for GET /orders,
gnunet <=
- [taler-merchant] 70/277: work on /pay and /abort processing, gnunet, 2020/07/05
- [taler-merchant] 72/277: DB API for /abort, gnunet, 2020/07/05
- [taler-merchant] 77/277: POST /orders/ID/refund implementation, gnunet, 2020/07/05
- [taler-merchant] 79/277: backend code for /refund handling, gnunet, 2020/07/05
- [taler-merchant] 85/277: update testing logic for order CMDs, gnunet, 2020/07/05
- [taler-merchant] 89/277: fix benchmark FTBFS, gnunet, 2020/07/05
- [taler-merchant] 63/277: remove duplicate, gnunet, 2020/07/05
- [taler-merchant] 71/277: work on /abort logic, gnunet, 2020/07/05
- [taler-merchant] 58/277: implement GET /orders in libtalermerchant, gnunet, 2020/07/05
- [taler-merchant] 66/277: implement order claiming, gnunet, 2020/07/05