commit e8990e5b404a43ca612cd6c6e41e3d3fdac74221
parent 9291f59c6bc8c5c4502b2e732f8a7b9f22304b6e
Author: Christian Grothoff <grothoff@gnunet.org>
Date: Wed, 29 Oct 2025 12:21:15 +0100
draft implementation of #9510
Diffstat:
8 files changed, 244 insertions(+), 6 deletions(-)
diff --git a/src/backend/taler-merchant-httpd_private-get-orders.c b/src/backend/taler-merchant-httpd_private-get-orders.c
@@ -1,6 +1,6 @@
/*
This file is part of TALER
- (C) 2019-2021 Taler Systems SA
+ (C) 2019--2025 Taler Systems SA
TALER is free software; you can redistribute it and/or modify it under the
terms of the GNU Affero General Public License as published by the Free Software
@@ -85,6 +85,11 @@ struct TMH_PendingOrder
const char *instance_id;
/**
+ * Alias of @a of.summary_filter, but with memory to be released (owner).
+ */
+ char *summary_filter;
+
+ /**
* The result after adding the orders (#TALER_EC_NONE for okay, anything else for an error).
*/
enum TALER_ErrorCode result;
@@ -221,6 +226,7 @@ cleanup (void *ctx)
GNUNET_assert (po ==
GNUNET_CONTAINER_heap_remove_node (po->hn));
json_decref (po->pa);
+ GNUNET_free (po->summary_filter);
GNUNET_free (po);
}
@@ -682,6 +688,42 @@ TMH_notify_order_change (struct TMH_MerchantInstance *mi,
/**
+ * Transforms an (untrusted) input filter into a Postgresql LIKE filter.
+ * Escapes "%" and "_" in the @a input and adds "%" at the beginning
+ * and the end to turn the @a input into a suitable Postgresql argument.
+ *
+ * @param input text to turn into a substring match expression, or NULL
+ * @return NULL if @a input was NULL, otherwise transformed @a input
+ */
+static char *
+tr (const char *input)
+{
+ char *out;
+ size_t slen;
+ size_t wpos;
+
+ if (NULL == input)
+ return NULL;
+ slen = strlen (input);
+ out = GNUNET_malloc (slen * 2 + 3);
+ wpos = 0;
+ out[wpos++] = '%';
+ for (size_t i = 0; i<slen; i++)
+ {
+ char c = input[i];
+
+ if ( (c == '%') ||
+ (c == '_') )
+ out[wpos++] = '\\';
+ out[wpos++] = c;
+ }
+ out[wpos++] = '%';
+ GNUNET_assert (wpos < slen);
+ return out;
+}
+
+
+/**
* Handle a GET "/orders" request.
*
* @param rh context of the handler
@@ -838,6 +880,10 @@ TMH_private_get_orders (const struct TMH_RequestHandler *rh,
TALER_EC_GENERIC_PARAMETER_MALFORMED,
"offset");
}
+ pf->summary_filter = tr (MHD_lookup_connection_value (connection,
+ MHD_GET_ARGUMENT_KIND,
+ "summary_filter"));
+ pf->of.summary_filter = pf->summary_filter; /* just an alias! */
po->of.session_id
= MHD_lookup_connection_value (connection,
MHD_GET_ARGUMENT_KIND,
diff --git a/src/backend/taler-merchant-httpd_private-get-products.c b/src/backend/taler-merchant-httpd_private-get-products.c
@@ -1,6 +1,6 @@
/*
This file is part of TALER
- (C) 2019, 2020, 2021, 2024 Taler Systems SA
+ (C) 2019, 2020, 2021, 2024, 2025 Taler Systems SA
TALER is free software; you can redistribute it and/or modify it under the
terms of the GNU Affero General Public License as published by the Free Software
@@ -47,6 +47,42 @@ add_product (void *cls,
}
+/**
+ * Transforms an (untrusted) input filter into a Postgresql LIKE filter.
+ * Escapes "%" and "_" in the @a input and adds "%" at the beginning
+ * and the end to turn the @a input into a suitable Postgresql argument.
+ *
+ * @param input text to turn into a substring match expression, or NULL
+ * @return NULL if @a input was NULL, otherwise transformed @a input
+ */
+static char *
+tr (const char *input)
+{
+ char *out;
+ size_t slen;
+ size_t wpos;
+
+ if (NULL == input)
+ return NULL;
+ slen = strlen (input);
+ out = GNUNET_malloc (slen * 2 + 3);
+ wpos = 0;
+ out[wpos++] = '%';
+ for (size_t i = 0; i<slen; i++)
+ {
+ char c = input[i];
+
+ if ( (c == '%') ||
+ (c == '_') )
+ out[wpos++] = '\\';
+ out[wpos++] = c;
+ }
+ out[wpos++] = '%';
+ GNUNET_assert (wpos < slen);
+ return out;
+}
+
+
MHD_RESULT
TMH_private_get_products (const struct TMH_RequestHandler *rh,
struct MHD_Connection *connection,
@@ -54,6 +90,9 @@ TMH_private_get_products (const struct TMH_RequestHandler *rh,
{
json_t *pa;
enum GNUNET_DB_QueryStatus qs;
+ char *category_filter;
+ char *name_filter;
+ char *description_filter;
int64_t limit;
uint64_t offset;
@@ -68,14 +107,29 @@ TMH_private_get_products (const struct TMH_RequestHandler *rh,
TALER_MHD_parse_request_number (connection,
"offset",
&offset);
+ category_filter = tr (MHD_lookup_connection_value (connection,
+ MHD_GET_ARGUMENT_KIND,
+ "category_filter"));
+ name_filter = tr (MHD_lookup_connection_value (connection,
+ MHD_GET_ARGUMENT_KIND,
+ "name_filter"));
+ description_filter = tr (MHD_lookup_connection_value (connection,
+ MHD_GET_ARGUMENT_KIND,
+ "description_filter"));
pa = json_array ();
GNUNET_assert (NULL != pa);
qs = TMH_db->lookup_products (TMH_db->cls,
hc->instance->settings.id,
offset,
limit,
+ category_filter,
+ name_filter,
+ description_filter,
&add_product,
pa);
+ GNUNET_free (category_filter);
+ GNUNET_free (name_filter);
+ GNUNET_free (description_filter);
if (0 > qs)
{
GNUNET_break (0);
diff --git a/src/backenddb/merchant-0025.sql b/src/backenddb/merchant-0025.sql
@@ -26,6 +26,8 @@ SELECT _v.register_patch('merchant-0025', NULL, NULL);
SET search_path TO merchant;
+-- We will need this for LIKE-style pattern matching "gin" indices.
+CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- See enum GNUNET_TIME_RounderInterval
CREATE TYPE time_rounder_interval
@@ -37,4 +39,37 @@ ALTER TABLE merchant_instances
COMMENT ON COLUMN merchant_instances.default_wire_transfer_rounding_interval
IS 'To what round value do we round up wire transfer deadlines computed on the basis of the default_wire_transfer_delay.';
+-- The following indices are added to support efficient product filtering.
+-- Use a Generalized Inverted Index (GIN) on the respective columns.
+-- We use "LOWER" so we can do case-insensitive searches.
+-- "gin_trgm_ops" is the operator class for the index, specifically "trigram indexing" from pg_trgm
+CREATE INDEX trgm_idx_products_by_name
+ ON merchant_inventory
+ USING gin (LOWER(product_name) gin_trgm_ops);
+
+CREATE INDEX trgm_idx_products_by_description
+ ON merchant_inventory
+ USING gin (LOWER(description) gin_trgm_ops);
+
+CREATE INDEX trgm_idx_categories_by_name
+ ON merchant_categories
+ USING gin (LOWER(category_name) gin_trgm_ops);
+
+CREATE INDEX trgm_idx_contract_summaries
+ ON merchant_contract_terms
+ USING gin (LOWER((contract_terms::jsonb ->> 'summary')) gin_trgm_ops);
+-- NOTE: Query must use exactly
+-- WHERE LOWER(contract_terms::jsonb ->> 'summary') LIKE LOWER($1);
+
+CREATE INDEX trgm_idx_order_summaries
+ ON merchant_orders
+ USING gin (LOWER((contract_terms::jsonb ->> 'summary')) gin_trgm_ops);
+-- NOTE: Query must use exactly
+-- WHERE LOWER(contract_terms::jsonb ->> 'summary') LIKE LOWER($1);
+
+-- FIXME: consider
+-- ALTER TABLE merchant_contract_terms ALTER COLUMN contract_terms TYPE JSONB USING contract_terms::jsonb;
+-- ALTER TABLE merchant_orders ALTER COLUMN contract_terms TYPE JSONB USING contract_terms::jsonb;
+-- --- alas, requires modifications across the plugin...
+
COMMIT;
diff --git a/src/backenddb/pg_lookup_orders.c b/src/backenddb/pg_lookup_orders.c
@@ -1,6 +1,6 @@
/*
This file is part of TALER
- Copyright (C) 2022,2023 Taler Systems SA
+ Copyright (C) 2022, 2023, 2025 Taler Systems SA
TALER is free software; you can redistribute it and/or modify it under the
terms of the GNU General Public License as published by the Free Software
@@ -131,13 +131,16 @@ TMH_PG_lookup_orders (void *cls,
NULL == of->fulfillment_url
? GNUNET_PQ_query_param_null ()
: GNUNET_PQ_query_param_string (of->fulfillment_url),
+ NULL == of->summary_filter
+ ? GNUNET_PQ_query_param_null ()
+ : GNUNET_PQ_query_param_string (of->summary_filter),
GNUNET_PQ_query_param_end
};
enum GNUNET_DB_QueryStatus qs;
char stmt[128];
GNUNET_log (GNUNET_ERROR_TYPE_INFO,
- "Looking up orders, using filter paid:%d, refunded: %d, wired: %d\n",
+ "Looking up orders, using filter paid: %d, refunded: %d, wired: %d\n",
of->paid,
of->refunded,
of->wired);
@@ -174,6 +177,8 @@ TMH_PG_lookup_orders (void *cls,
" ($12 = session_id))"
" AND ($13 OR "
" ($14 = fulfillment_url))"
+ " AND ( ($15 IS NULL) OR "
+ " (LOWER(contract_terms::jsonb ->> 'summary') LIKE LOWER($15)) )"
" ORDER BY order_serial DESC"
" LIMIT $2)"
"UNION " /* union ensures elements are distinct! */
@@ -202,6 +207,8 @@ TMH_PG_lookup_orders (void *cls,
" ($12 = session_id))"
" AND ($13 OR "
" ($14 = fulfillment_url))"
+ " AND ( ($15 IS NULL) OR "
+ " (LOWER(contract_terms::jsonb ->> 'summary') LIKE LOWER($15)) )"
" ORDER BY order_serial DESC"
" LIMIT $2)"
" ORDER BY order_serial DESC"
@@ -236,6 +243,8 @@ TMH_PG_lookup_orders (void *cls,
" ($12 = session_id))"
" AND ($13 OR "
" ($14 = fulfillment_url))"
+ " AND ( ($15 IS NULL) OR "
+ " (LOWER(contract_terms::jsonb ->> 'summary') LIKE LOWER($15)) )"
" ORDER BY order_serial ASC"
" LIMIT $2)"
"UNION " /* union ensures elements are distinct! */
@@ -264,6 +273,8 @@ TMH_PG_lookup_orders (void *cls,
" ($12 = session_id))"
" AND ($13 OR "
" ($14 = fulfillment_url))"
+ " AND ( ($15 IS NULL) OR "
+ " (LOWER(contract_terms::jsonb ->> 'summary') LIKE LOWER($15)) )"
" ORDER BY order_serial ASC"
" LIMIT $2)"
" ORDER BY order_serial ASC"
diff --git a/src/backenddb/pg_lookup_products.c b/src/backenddb/pg_lookup_products.c
@@ -96,6 +96,9 @@ TMH_PG_lookup_products (void *cls,
const char *instance_id,
uint64_t offset,
int64_t limit,
+ const char *category_filter,
+ const char *name_filter,
+ const char *description_filter,
TALER_MERCHANTDB_ProductsCallback cb,
void *cb_cls)
{
@@ -111,6 +114,15 @@ TMH_PG_lookup_products (void *cls,
GNUNET_PQ_query_param_string (instance_id),
GNUNET_PQ_query_param_uint64 (&offset),
GNUNET_PQ_query_param_uint64 (&plimit),
+ NULL == category_filter
+ ? GNUNET_PQ_query_param_null ()
+ : GNUNET_PQ_query_param_string (category_filter),
+ NULL == name_filter
+ ? GNUNET_PQ_query_param_null ()
+ : GNUNET_PQ_query_param_string (name_filter),
+ NULL == description_filter
+ ? GNUNET_PQ_query_param_null ()
+ : GNUNET_PQ_query_param_string (description_filter),
GNUNET_PQ_query_param_end
};
enum GNUNET_DB_QueryStatus qs;
@@ -126,8 +138,20 @@ TMH_PG_lookup_products (void *cls,
" USING (merchant_serial)"
" WHERE merchant_instances.merchant_id=$1"
" AND product_serial > $2"
+ " AND ( ($3 IS NULL) OR"
+ " (product_serial IN"
+ " (SELECT product_serial"
+ " FROM merchant_product_categories"
+ " WHERE category_serial IN"
+ " (SELECT category_serial"
+ " FROM merchant_categories"
+ " WHERE category_name LIKE LOWER($3)))) )"
+ " AND ( ($4 IS NULL) OR"
+ " (product_name LIKE LOWER($4)) )"
+ " AND ( ($5 IS NULL) OR"
+ " (description LIKE LOWER($5)) )"
" ORDER BY product_serial ASC"
- " LIMIT $3");
+ " LIMIT $6");
PREPARE (pg,
"lookup_products_desc",
"SELECT"
@@ -138,8 +162,20 @@ TMH_PG_lookup_products (void *cls,
" USING (merchant_serial)"
" WHERE merchant_instances.merchant_id=$1"
" AND product_serial < $2"
+ " AND ( ($3 IS NULL) OR"
+ " (product_serial IN"
+ " (SELECT product_serial"
+ " FROM merchant_product_categories"
+ " WHERE category_serial IN"
+ " (SELECT category_serial"
+ " FROM merchant_categories"
+ " WHERE category_name LIKE LOWER($3)))) )"
+ " AND ( ($4 IS NULL) OR"
+ " (product_name LIKE LOWER($4)) )"
+ " AND ( ($5 IS NULL) OR"
+ " (description LIKE LOWER($5)) )"
" ORDER BY product_serial DESC"
- " LIMIT $3");
+ " LIMIT $6");
qs = GNUNET_PQ_eval_prepared_multi_select (
pg->conn,
(limit > 0)
diff --git a/src/backenddb/pg_lookup_products.h b/src/backenddb/pg_lookup_products.h
@@ -33,6 +33,27 @@
* @param offset transfer_serial number of the transfer we want to offset from
* @param limit number of entries to return, negative for descending,
* positive for ascending
+ * @param category_filter filter products by category, NULL to not filter;
+ * uses the Postgresql "LIKE" pattern matcher, so
+ * "%" stands for any sequence of zero or more characters,
+ * "_" stands for any single character;
+ * use "\%" and "\_" to exactly match "%" or "_".
+ * We will always use case-insensitive searches, for case-sensitive
+ * matching the client should filter the result set.
+ * @param name_filter filter products by name, NULL to not filter
+ * uses the Postgresql "LIKE" pattern matcher, so
+ * "%" stands for any sequence of zero or more characters,
+ * "_" stands for any single character
+ * use "\%" and "\_" to exactly match "%" or "_".
+ * We will always use case-insensitive searches, for case-sensitive
+ * matching the client should filter the result set.
+ * @param description_filter filter products by description, NULL to not filter
+ * uses the Postgresql "LIKE" pattern matcher, so
+ * "%" stands for any sequence of zero or more characters,
+ * "_" stands for any single character
+ * use "\%" and "\_" to exactly match "%" or "_".
+ * We will always use case-insensitive searches, for case-sensitive
+ * matching the client should filter the result set.
* @param cb function to call on all products found
* @param cb_cls closure for @a cb
* @return database result code
@@ -42,6 +63,9 @@ TMH_PG_lookup_products (void *cls,
const char *instance_id,
uint64_t offset,
int64_t limit,
+ const char *category_filter,
+ const char *name_filter,
+ const char *description_filter,
TALER_MERCHANTDB_ProductsCallback cb,
void *cb_cls);
diff --git a/src/backenddb/test_merchantdb.c b/src/backenddb/test_merchantdb.c
@@ -1034,6 +1034,9 @@ test_lookup_products (const struct InstanceData *instance,
instance->instance.id,
0,
20,
+ NULL,
+ NULL,
+ NULL,
&lookup_products_cb,
&cls))
{
diff --git a/src/include/taler_merchantdb_plugin.h b/src/include/taler_merchantdb_plugin.h
@@ -747,6 +747,11 @@ struct TALER_MERCHANTDB_OrderFilter
*/
struct GNUNET_TIME_Relative timeout;
+ /**
+ * Filter to apply on the summary of the order.
+ */
+ const char *summary_filter;
+
};
@@ -2134,6 +2139,27 @@ struct TALER_MERCHANTDB_Plugin
* @param offset transfer_serial number of the transfer we want to offset from
* @param limit number of entries to return, negative for descending,
* positive for ascending
+ * @param category_filter filter products by category, NULL to not filter;
+ * uses the Postgresql "LIKE" pattern matcher, so
+ * "%" stands for any sequence of zero or more characters,
+ * "_" stands for any single character;
+ * use "\%" and "\_" to exactly match "%" or "_".
+ * We will always use case-insensitive searches, for case-sensitive
+ * matching the client should filter the result set.
+ * @param name_filter filter products by name, NULL to not filter
+ * uses the Postgresql "LIKE" pattern matcher, so
+ * "%" stands for any sequence of zero or more characters,
+ * "_" stands for any single character
+ * use "\%" and "\_" to exactly match "%" or "_".
+ * We will always use case-insensitive searches, for case-sensitive
+ * matching the client should filter the result set.
+ * @param description_filter filter products by description, NULL to not filter
+ * uses the Postgresql "LIKE" pattern matcher, so
+ * "%" stands for any sequence of zero or more characters,
+ * "_" stands for any single character
+ * use "\%" and "\_" to exactly match "%" or "_".
+ * We will always use case-insensitive searches, for case-sensitive
+ * matching the client should filter the result set.
* @param cb function to call on all products found
* @param cb_cls closure for @a cb
* @return database result code
@@ -2143,6 +2169,9 @@ struct TALER_MERCHANTDB_Plugin
const char *instance_id,
uint64_t offset,
int64_t limit,
+ const char *category_filter,
+ const char *name_filter,
+ const char *description_filter,
TALER_MERCHANTDB_ProductsCallback cb,
void *cb_cls);
