commit 6fd464170627c9bf76ae25573006418eacdf445f
parent ca62ed41b3bdca9368cdf28e8c79ff0faf5995ec
Author: Christian Grothoff <christian@grothoff.org>
Date: Sat, 21 Jun 2025 19:02:37 +0200
combine two SQL statements into one using (LEFT) JOIN
Diffstat:
1 file changed, 129 insertions(+), 168 deletions(-)
diff --git a/src/backenddb/pg_lookup_instances.c b/src/backenddb/pg_lookup_instances.c
@@ -46,108 +46,14 @@ struct LookupInstancesContext
struct PostgresClosure *pg;
/**
- * Instance settings, valid only during find_instances_cb().
- */
- struct TALER_MERCHANTDB_InstanceSettings is;
-
- /**
- * Instance authentication settings, valid only during find_instances_cb().
- */
- struct TALER_MERCHANTDB_InstanceAuthSettings ias;
-
- /**
- * Instance serial number, valid only during find_instances_cb().
- */
- uint64_t instance_serial;
-
- /**
- * Public key of the current instance, valid only during find_instances_cb().
- */
- struct TALER_MerchantPublicKeyP merchant_pub;
-
- /**
* Set to the return value on errors.
*/
enum GNUNET_DB_QueryStatus qs;
- /**
- * true if we only are interested in instances for which we have the private key.
- */
- bool active_only;
};
/**
- * Helper function to run PREPARE() macro.
- *
- * @param pg closure to pass
- * @return status of the preparation
- */
-static enum GNUNET_DB_QueryStatus
-prepare (struct PostgresClosure *pg)
-{
- // FIXME: use a LEFT JOIN here!
- PREPARE (pg,
- "lookup_instance_private_key",
- "SELECT"
- " merchant_priv"
- " FROM merchant_keys"
- " WHERE merchant_serial=$1");
- return GNUNET_DB_STATUS_SUCCESS_NO_RESULTS;
-}
-
-
-/**
- * We are processing an instances lookup and have the @a accounts.
- * Find the private key if possible, and invoke the callback.
- *
- * @param lic context we are handling
- */
-static void
-call_cb (struct LookupInstancesContext *lic)
-{
- struct PostgresClosure *pg = lic->pg;
- enum GNUNET_DB_QueryStatus qs;
- struct GNUNET_PQ_QueryParam params[] = {
- GNUNET_PQ_query_param_uint64 (&lic->instance_serial),
- GNUNET_PQ_query_param_end
- };
- struct TALER_MerchantPrivateKeyP merchant_priv;
- struct GNUNET_PQ_ResultSpec rs[] = {
- GNUNET_PQ_result_spec_auto_from_type ("merchant_priv",
- &merchant_priv),
- GNUNET_PQ_result_spec_end
- };
-
- qs = prepare (pg);
- if (qs < 0)
- {
- GNUNET_break (0);
- lic->qs = GNUNET_DB_STATUS_HARD_ERROR;
- return;
- }
- qs = GNUNET_PQ_eval_prepared_singleton_select (pg->conn,
- "lookup_instance_private_key",
- params,
- rs);
- if (qs < 0)
- {
- GNUNET_break (0);
- lic->qs = GNUNET_DB_STATUS_HARD_ERROR;
- return;
- }
- if ( (0 == qs) &&
- (lic->active_only) )
- return; /* skip, not interesting */
- lic->cb (lic->cb_cls,
- &lic->merchant_pub,
- (0 == qs) ? NULL : &merchant_priv,
- &lic->is,
- &lic->ias);
-}
-
-
-/**
* Function to be called with the results of a SELECT statement
* that has returned @a num_results results about instances.
*
@@ -161,68 +67,70 @@ lookup_instances_cb (void *cls,
unsigned int num_results)
{
struct LookupInstancesContext *lic = cls;
- struct PostgresClosure *pg = lic->pg;
-
- lic->qs = prepare (pg);
- if (lic->qs < 0)
- {
- GNUNET_break (0);
- return;
- }
for (unsigned int i = 0; i < num_results; i++)
{
+ struct TALER_MERCHANTDB_InstanceSettings is;
+ struct TALER_MERCHANTDB_InstanceAuthSettings ias;
+ uint64_t instance_serial;
+ struct TALER_MerchantPublicKeyP merchant_pub;
+ struct TALER_MerchantPrivateKeyP merchant_priv;
bool no_auth;
bool no_salt;
+ bool no_priv;
struct GNUNET_PQ_ResultSpec rs[] = {
GNUNET_PQ_result_spec_uint64 ("merchant_serial",
- &lic->instance_serial),
+ &instance_serial),
GNUNET_PQ_result_spec_auto_from_type ("merchant_pub",
- &lic->merchant_pub),
+ &merchant_pub),
GNUNET_PQ_result_spec_allow_null (
GNUNET_PQ_result_spec_auto_from_type ("auth_hash",
- &lic->ias.auth_hash),
+ &ias.auth_hash),
&no_auth),
GNUNET_PQ_result_spec_allow_null (
GNUNET_PQ_result_spec_auto_from_type ("auth_salt",
- &lic->ias.auth_salt),
+ &ias.auth_salt),
&no_salt),
+ GNUNET_PQ_result_spec_allow_null (
+ GNUNET_PQ_result_spec_auto_from_type ("merchant_priv",
+ &merchant_priv),
+ &no_priv),
GNUNET_PQ_result_spec_string ("merchant_id",
- &lic->is.id),
+ &is.id),
GNUNET_PQ_result_spec_string ("merchant_name",
- &lic->is.name),
+ &is.name),
TALER_PQ_result_spec_json ("address",
- &lic->is.address),
+ &is.address),
TALER_PQ_result_spec_json ("jurisdiction",
- &lic->is.jurisdiction),
+ &is.jurisdiction),
GNUNET_PQ_result_spec_bool ("use_stefan",
- &lic->is.use_stefan),
- GNUNET_PQ_result_spec_relative_time ("default_wire_transfer_delay",
- &lic->is.default_wire_transfer_delay)
- ,
+ &is.use_stefan),
+ GNUNET_PQ_result_spec_relative_time (
+ "default_wire_transfer_delay",
+ &is.default_wire_transfer_delay),
GNUNET_PQ_result_spec_relative_time ("default_pay_delay",
- &lic->is.default_pay_delay),
+ &is.default_pay_delay),
GNUNET_PQ_result_spec_allow_null (
GNUNET_PQ_result_spec_string ("website",
- &lic->is.website),
+ &is.website),
NULL),
GNUNET_PQ_result_spec_allow_null (
GNUNET_PQ_result_spec_string ("email",
- &lic->is.email),
+ &is.email),
NULL),
GNUNET_PQ_result_spec_allow_null (
GNUNET_PQ_result_spec_string ("logo",
- &lic->is.logo),
+ &is.logo),
NULL),
GNUNET_PQ_result_spec_end
};
- memset (&lic->ias.auth_salt,
+ memset (&ias.auth_salt,
0,
- sizeof (lic->ias.auth_salt));
- memset (&lic->ias.auth_hash,
+ sizeof (ias.auth_salt));
+ memset (&ias.auth_hash,
0,
- sizeof (lic->ias.auth_hash));
+ sizeof (ias.auth_hash));
if (GNUNET_OK !=
GNUNET_PQ_extract_result (result,
rs,
@@ -232,10 +140,12 @@ lookup_instances_cb (void *cls,
lic->qs = GNUNET_DB_STATUS_HARD_ERROR;
return;
}
- call_cb (lic);
+ lic->cb (lic->cb_cls,
+ &merchant_pub,
+ (no_priv) ? NULL : &merchant_priv,
+ &is,
+ &ias);
GNUNET_PQ_cleanup_result (rs);
- if (0 > lic->qs)
- break;
}
}
@@ -250,7 +160,6 @@ TMH_PG_lookup_instances (void *cls,
struct LookupInstancesContext lic = {
.cb = cb,
.cb_cls = cb_cls,
- .active_only = active_only,
.pg = pg
};
struct GNUNET_PQ_QueryParam params[] = {
@@ -262,26 +171,51 @@ TMH_PG_lookup_instances (void *cls,
PREPARE (pg,
"lookup_instances",
"SELECT"
- " merchant_serial"
- ",merchant_pub"
- ",auth_hash"
- ",auth_salt"
- ",merchant_id"
- ",merchant_name"
- ",address"
- ",jurisdiction"
- ",use_stefan"
- ",default_wire_transfer_delay"
- ",default_pay_delay"
- ",website"
- ",email"
- ",logo"
- " FROM merchant_instances");
- qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn,
- "lookup_instances",
- params,
- &lookup_instances_cb,
- &lic);
+ " mi.merchant_serial"
+ ",mi.merchant_pub"
+ ",mi.auth_hash"
+ ",mi.auth_salt"
+ ",mi.merchant_id"
+ ",mi.merchant_name"
+ ",mi.address"
+ ",mi.jurisdiction"
+ ",mi.use_stefan"
+ ",mi.default_wire_transfer_delay"
+ ",mi.default_pay_delay"
+ ",mi.website"
+ ",mi.email"
+ ",mi.logo"
+ " FROM merchant_instances mi"
+ " LEFT JOIN merchant_keys mk"
+ " USING (merchant_serial)");
+ PREPARE (pg,
+ "lookup_active_instances",
+ "SELECT "
+ " mi.merchant_serial"
+ ",mi.merchant_pub"
+ ",mi.auth_hash"
+ ",mi.auth_salt"
+ ",mi.merchant_id"
+ ",mi.merchant_name"
+ ",mi.address"
+ ",mi.jurisdiction"
+ ",mi.use_stefan"
+ ",mi.default_wire_transfer_delay"
+ ",mi.default_pay_delay"
+ ",mi.website"
+ ",mi.email"
+ ",mi.logo"
+ " FROM merchant_instances mi"
+ " JOIN merchant_keys mk"
+ " USING (merchant_serial)");
+ qs = GNUNET_PQ_eval_prepared_multi_select (
+ pg->conn,
+ active_only
+ ? "lookup_active_instances"
+ : "lookup_instances",
+ params,
+ &lookup_instances_cb,
+ &lic);
if (0 > lic.qs)
return lic.qs;
return qs;
@@ -299,7 +233,6 @@ TMH_PG_lookup_instance (void *cls,
struct LookupInstancesContext lic = {
.cb = cb,
.cb_cls = cb_cls,
- .active_only = active_only,
.pg = pg
};
struct GNUNET_PQ_QueryParam params[] = {
@@ -312,28 +245,56 @@ TMH_PG_lookup_instance (void *cls,
PREPARE (pg,
"lookup_instance",
"SELECT"
- " merchant_serial"
- ",merchant_pub"
- ",auth_hash"
- ",auth_salt"
- ",merchant_id"
- ",merchant_name"
- ",user_type"
- ",address"
- ",jurisdiction"
- ",use_stefan"
- ",default_wire_transfer_delay"
- ",default_pay_delay"
- ",website"
- ",email"
- ",logo"
- " FROM merchant_instances"
+ " mi.merchant_serial"
+ ",mi.merchant_pub"
+ ",mi.auth_hash"
+ ",mi.auth_salt"
+ ",mi.merchant_id"
+ ",mi.merchant_name"
+ ",mi.address"
+ ",mi.jurisdiction"
+ ",mi.use_stefan"
+ ",mi.default_wire_transfer_delay"
+ ",mi.default_pay_delay"
+ ",mi.website"
+ ",mi.email"
+ ",mi.logo"
+ ",mk.merchant_priv"
+ " FROM merchant_instances mi"
+ " LEFT JOIN merchant_keys mk"
+ " USING (merchant_serial)"
+ " WHERE merchant_id=$1");
+ PREPARE (pg,
+ "lookup_active_instance",
+ "SELECT"
+ " mi.merchant_serial"
+ ",mi.merchant_pub"
+ ",mi.auth_hash"
+ ",mi.auth_salt"
+ ",mi.merchant_id"
+ ",mi.merchant_name"
+ ",mi.user_type"
+ ",mi.address"
+ ",mi.jurisdiction"
+ ",mi.use_stefan"
+ ",mi.default_wire_transfer_delay"
+ ",mi.default_pay_delay"
+ ",mi.website"
+ ",mi.email"
+ ",mi.logo"
+ ",mk.merchant_priv"
+ " FROM merchant_instances mi"
+ " JOIN merchant_keys mk"
+ " USING (merchant_serial)"
" WHERE merchant_id=$1");
- qs = GNUNET_PQ_eval_prepared_multi_select (pg->conn,
- "lookup_instance",
- params,
- &lookup_instances_cb,
- &lic);
+ qs = GNUNET_PQ_eval_prepared_multi_select (
+ pg->conn,
+ active_only
+ ? "lookup_active_instance"
+ : "lookup_instance",
+ params,
+ &lookup_instances_cb,
+ &lic);
if (0 > lic.qs)
return lic.qs;
return qs;
