[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[taler-merchant] 20/277: squealing
From: |
gnunet |
Subject: |
[taler-merchant] 20/277: squealing |
Date: |
Sun, 05 Jul 2020 20:48:53 +0200 |
This is an automated email from the git hooks/post-receive script.
grothoff pushed a commit to branch master
in repository merchant.
commit 33ad6a9f534c2b3aeed388549abd69bb8e55b38c
Author: Christian Grothoff <christian@grothoff.org>
AuthorDate: Mon Apr 20 14:17:44 2020 +0200
squealing
---
src/backenddb/merchant-0001.sql | 6 +-
src/backenddb/plugin_merchantdb_postgres.c | 135 ++++++++++++++++++++++++++++-
2 files changed, 137 insertions(+), 4 deletions(-)
diff --git a/src/backenddb/merchant-0001.sql b/src/backenddb/merchant-0001.sql
index 104ea47..ad39e79 100644
--- a/src/backenddb/merchant-0001.sql
+++ b/src/backenddb/merchant-0001.sql
@@ -127,7 +127,7 @@ CREATE TABLE IF NOT EXISTS merchant_inventory
,image BYTEA NOT NULL
,taxes BYTEA NOT NULL
,price_val INT8 NOT NULL
- ,price_frac INT4 NOT NULL
+ ,price_frac cINT4 NOT NULL
,total_stock BIGINT NOT NULL
,total_sold BIGINT NOT NULL
,total_lost BIGINT NOT NULL
@@ -162,7 +162,7 @@ COMMENT ON COLUMN merchant_inventory.next_restock
CREATE TABLE IF NOT EXISTS merchant_inventory_locks
(product_serial BIGINT NOT NULL
- REFERENCES merchant_inventory (product_serial) ON DELETE CASCADE
+ REFERENCES merchant_inventory (product_serial) -- NO "ON DELETE CASCADE":
locks prevent deletion!
,lock_uuid BYTEA NOT NULL CHECK (LENGTH(lock_uuid)=32)
,total_locked BIGINT NOT NULL
,expiration TIMESTAMP NOT NULL
@@ -206,7 +206,7 @@ CREATE INDEX IF NOT EXISTS merchant_orders_by_expiration
CREATE TABLE IF NOT EXISTS merchant_order_locks
(product_serial BIGINT NOT NULL
- REFERENCES merchant_inventory (product_serial) ON DELETE CASCADE
+ REFERENCES merchant_inventory (product_serial) -- NO "ON DELETE CASCADE":
locks prevent deletion!
,total_locked BIGINT NOT NULL
,order_serial BIGINT NOT NULL
REFERENCES merchant_orders (order_serial) ON DELETE CASCADE
diff --git a/src/backenddb/plugin_merchantdb_postgres.c
b/src/backenddb/plugin_merchantdb_postgres.c
index 08f8093..300aa2b 100644
--- a/src/backenddb/plugin_merchantdb_postgres.c
+++ b/src/backenddb/plugin_merchantdb_postgres.c
@@ -760,7 +760,7 @@ lookup_products_cb (void *cls,
struct GNUNET_PQ_ResultSpec rs[] = {
GNUNET_PQ_result_spec_string ("product_id",
&product_id),
- GNUNET_PQ_result_spec_uint64 ("in_stock",
+ GNUNET_PQ_result_spec_uint64 ("total_stock",
&in_stock),
GNUNET_PQ_result_spec_string ("unit",
&unit),
@@ -4018,6 +4018,139 @@ libtaler_plugin_merchantdb_postgres_init (void *cls)
" active=FALSE"
" WHERE h_wire = $1",
1),
+ /* for postgres_lookup_products() */
+ GNUNET_PQ_make_prepare ("lookup_products",
+ "SELECT"
+ " product_id"
+ ",total_stock"
+ ",unit"
+ " FROM merchant_inventory"
+ " JOIN merchant_instances"
+ " USING (merchant_serial)"
+ " WHERE merchant_instances.merchant_id=$1",
+ 1),
+ /* for postgres_lookup_product() */
+ GNUNET_PQ_make_prepare ("lookup_product",
+ "SELECT"
+ " description"
+ ",description_i18n"
+ ",unit"
+ ",price_val"
+ ",price_frac"
+ ",taxes"
+ ",total_stock"
+ ",total_sold"
+ ",total_lost"
+ ",image"
+ ",merchant_inventory.address"
+ ",next_restock"
+ " FROM merchant_inventory"
+ " JOIN merchant_instances"
+ " USING (merchant_serial)"
+ " WHERE merchant_instances.merchant_id=$1"
+ " AND merchant_inventory.product_id=$2",
+ 2),
+ /* for postgres_delete_product() */
+ GNUNET_PQ_make_prepare ("delete_product",
+ "DELETE"
+ " FROM merchant_inventory"
+ " WHERE merchant_inventory.merchant_serial="
+ " (SELECT merchant_serial "
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND merchant_inventory.product_id=$2"
+ " AND product_serial NOT IN "
+ " (SELECT product_serial FROM
merchant_order_locks)"
+ " AND product_serial NOT IN "
+ " (SELECT product_serial FROM
merchant_inventory_locks)",
+ 2),
+ /* for postgres_insert_product() */
+ GNUNET_PQ_make_prepare ("insert_product",
+ "INSERT INTO merchant_inventory"
+ "(merchant_serial"
+ ",product_id"
+ ",description"
+ ",description_i18n"
+ ",unit"
+ ",image"
+ ",taxes"
+ ",price_val"
+ ",price_frac"
+ ",total_stock"
+ ",total_sold"
+ ",total_lost"
+ ",address"
+ ",next_restock)"
+ " SELECT merchant_serial,"
+ " $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12,
$13, $14"
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1",
+ 14),
+ /* for postgres_update_product() */
+ GNUNET_PQ_make_prepare ("update_product",
+ "UPDATE merchant_inventory SET"
+ " description=$3"
+ ",description_i18n=$4"
+ ",unit=$5"
+ ",image=$6"
+ ",taxes=$7"
+ ",price_val=$8"
+ ",price_frac=$9"
+ ",total_stock=$10"
+ ",total_sold=$11"
+ ",total_lost=$12"
+ ",address=$13"
+ ",next_restock=$14"
+ " WHERE merchant_serial="
+ " (SELECT merchant_serial"
+ " FROM merchant_instances"
+ " WHERE merchant_id=$1)"
+ " AND product_id=$2"
+ " AND total_stock <= $10"
+ " AND total_sold <= $11"
+ " AND total_lost <= $12",
+ 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",
+ "INSERT INTO merchant_inventory_locks"
+ "(product_serial"
+ ",lock_uuid"
+ ",total_locked"
+ ",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)))",
+ 5),
+
/* OLD API: */
#if 0
GNUNET_PQ_make_prepare ("insert_deposit",
--
To stop receiving notification emails like this one, please contact
gnunet@gnunet.org.
- [taler-merchant] 13/277: implement DELETE /instances/$ID, (continued)
- [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, 2020/07/05
- [taler-merchant] 16/277: work on delete/get products by ID, gnunet, 2020/07/05
- [taler-merchant] 20/277: squealing,
gnunet <=
- [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
- [taler-merchant] 32/277: move libtalermerchanttesting and test cases to src/testing/, gnunet, 2020/07/05
- [taler-merchant] 35/277: add CMD for GET /instances, gnunet, 2020/07/05