diff options
| author | Christian Grothoff <christian@grothoff.org> | 2020-04-20 14:24:56 +0200 |
|---|---|---|
| committer | Christian Grothoff <christian@grothoff.org> | 2020-04-20 14:24:56 +0200 |
| commit | a01d45c4f35aa5195af542cdc2244f4940667d21 (patch) | |
| tree | 6729e4344862f32f67575a7fb22012c11e2b1a10 /src/backenddb | |
| parent | 33ad6a9f534c2b3aeed388549abd69bb8e55b38c (diff) | |
| download | merchant-a01d45c4f35aa5195af542cdc2244f4940667d21.tar.gz merchant-a01d45c4f35aa5195af542cdc2244f4940667d21.tar.bz2 merchant-a01d45c4f35aa5195af542cdc2244f4940667d21.zip | |
simplifying SQL
Diffstat (limited to 'src/backenddb')
| -rw-r--r-- | src/backenddb/plugin_merchantdb_postgres.c | 46 |
1 files changed, 17 insertions, 29 deletions
diff --git a/src/backenddb/plugin_merchantdb_postgres.c b/src/backenddb/plugin_merchantdb_postgres.c index 300aa2bf..e23649da 100644 --- a/src/backenddb/plugin_merchantdb_postgres.c +++ b/src/backenddb/plugin_merchantdb_postgres.c @@ -4112,10 +4112,15 @@ libtaler_plugin_merchantdb_postgres_init (void *cls) 14), /* for postgres_lock_product() */ - /* TODO: there MAY be a more elegant way to write this SQL - statement to *first* get the product_serial and then - re-use it in the 3 main 'AND' sub-clauses */ GNUNET_PQ_make_prepare ("lock_product", + "WITH ps AS" + " (SELECT product_serial" + " FROM merchant_inventory" + " WHERE product_id=$2" + " AND merchant_serial=" + " (SELECT merchant_serial" + " FROM merchant_instances" + " WHERE merchant_id=$1))" "INSERT INTO merchant_inventory_locks" "(product_serial" ",lock_uuid" @@ -4123,32 +4128,15 @@ libtaler_plugin_merchantdb_postgres_init (void *cls) ",expiration)" " SELECT product_serial, $3, $4, $5" " FROM merchant_inventory" - " WHERE product_id=$2" - " AND merchant_serial=" - " (SELECT merchant_serial" - " FROM merchant_instances" - " WHERE merchant_id=$1)" - " AND total_stock - total_sold - total_lost > " - " (SELECT SUM(total_locked)" - " FROM merchant_inventory_locks" - " WHERE product_serial=" - " (SELECT product_serial" - " FROM merchant_inventory" - " WHERE product_id=$2" - " AND merchant_serial=" - " (SELECT merchant_serial" - " FROM merchant_instances" - " WHERE merchant_id=$1))) +" - " (SELECT SUM(total_locked)" - " FROM merchant_order_locks" - " WHERE product_serial=" - " (SELECT product_serial" - " FROM merchant_inventory" - " WHERE product_id=$2" - " AND merchant_serial=" - " (SELECT merchant_serial" - " FROM merchant_instances" - " WHERE merchant_id=$1)))", + " JOIN ps USING (product_serial)" + " WHERE " + " total_stock - total_sold - total_lost > " + " (SELECT SUM(total_locked)" + " FROM merchant_inventory_locks" + " WHERE product_serial=ps.product_serial) + " + " (SELECT SUM(total_locked)" + " FROM merchant_order_locks" + " WHERE product_serial=ps.product_serial)", 5), /* OLD API: */ |