[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[taler-merchant] 21/277: simplifying SQL
From: |
gnunet |
Subject: |
[taler-merchant] 21/277: simplifying SQL |
Date: |
Sun, 05 Jul 2020 20:48:54 +0200 |
This is an automated email from the git hooks/post-receive script.
grothoff pushed a commit to branch master
in repository merchant.
commit a01d45c4f35aa5195af542cdc2244f4940667d21
Author: Christian Grothoff <christian@grothoff.org>
AuthorDate: Mon Apr 20 14:24:56 2020 +0200
simplifying SQL
---
src/backenddb/plugin_merchantdb_postgres.c | 46 +++++++++++-------------------
1 file changed, 17 insertions(+), 29 deletions(-)
diff --git a/src/backenddb/plugin_merchantdb_postgres.c
b/src/backenddb/plugin_merchantdb_postgres.c
index 300aa2b..e23649d 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: */
--
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.
- [taler-merchant] 10/277: implement POST /instances, (continued)
- [taler-merchant] 10/277: implement POST /instances, gnunet, 2020/07/05
- [taler-merchant] 14/277: implement PATCH, gnunet, 2020/07/05
- [taler-merchant] 13/277: implement DELETE /instances/$ID, gnunet, 2020/07/05
- [taler-merchant] 18/277: implement POST products/lock, gnunet, 2020/07/05
- [taler-merchant] 15/277: implement GET /products, gnunet, 2020/07/05
- [taler-merchant] 17/277: implement PATCH handlers, gnunet, 2020/07/05
- [taler-merchant] 19/277: backenddb implementation work, gnunet, 2020/07/05
- [taler-merchant] 23/277: specify remaining /instance API, gnunet, 2020/07/05
- [taler-merchant] 26/277: implement GET /products, gnunet, 2020/07/05
- [taler-merchant] 30/277: implement POST /products//lock, gnunet, 2020/07/05
- [taler-merchant] 21/277: simplifying SQL,
gnunet <=
- [taler-merchant] 16/277: work on delete/get products by ID, gnunet, 2020/07/05
- [taler-merchant] 20/277: squealing, gnunet, 2020/07/05
- [taler-merchant] 25/277: finish patch, gnunet, 2020/07/05
- [taler-merchant] 24/277: implement POST /instances, gnunet, 2020/07/05
- [taler-merchant] 29/277: implement POST /products, gnunet, 2020/07/05
- [taler-merchant] 28/277: implement PATCH /products/, gnunet, 2020/07/05
- [taler-merchant] 22/277: work on /products and /instances C API, gnunet, 2020/07/05
- [taler-merchant] 31/277: implemenet DELETE /products/, gnunet, 2020/07/05
- [taler-merchant] 27/277: implement GET /products/, gnunet, 2020/07/05
- [taler-merchant] 34/277: add GET /instance CMD, gnunet, 2020/07/05