Hi,
Can you tell me if there is a better way to solve this problem?
I have these tables:
CREATE TABLE IF NOT EXISTS products (
id VARCHAR(32) PRIMARY KEY,
name VARCHAR(64) NOT NULL,
quantity INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
created_at TIMESTAMP WITH TIME ZONE NOT NULL
DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(16) NOT NULL DEFAULT 'PENDING'
);
CREATE TABLE IF NOT EXISTS order_lines (
id INTEGER PRIMARY KEY AUTOINCREMENT,
product_id VARCHAR(32) REFERENCES products (id) ON DELETE SET NULL,
order_id INTEGER REFERENCES orders (id) ON DELETE CASCADE,
quantity INTEGER NOT NULL
);
My goal is to update the status of an order and the quantity of a product when:
for every line of an order, line.quantity <= product.quantity
In that case, the status is set to 'RESERVED', in the other case, it will be set
to 'BACKORDER'
I'm doing that in 3 steps:
1) Get the ids of the orders that don't satisfy the condition
2) Set those orders to `BACKORDER`
3) Set the other orders to `RESERVED`
4) Update the quantity
The first problem I encountered was that there was no `SELECT FOR UPDATE` in
sqlite3. We want to lock the quantity on our first select. To do that, I'm
starting the transaction with: `BEGIN EXCLUSIVE`.
The second problem was that I didn't really know how to translate `for every
line of an order, line.quantity <= product.quantity`. So I went for the
negation: `there exists a line such that line.quantity > product.quantity`.
Here is the code:
BEGIN EXCLUSIVE;
CREATE TEMP VIEW IF NOT EXISTS backorders AS
SELECT DISTINCT order_id FROM order_lines AS ol
JOIN products AS p ON p.id=ol.product_id
JOIN orders AS o ON o.id=ol.order_id
WHERE p.quantity < ol.quantity AND o.status = 'PENDING';
UPDATE orders
SET status='BACKORDER'
WHERE id IN (SELECT order_id FROM backorders);
CREATE TEMP VIEW IF NOT EXISTS reserved AS
SELECT o.id, ol.quantity, ol.product_id FROM orders AS o
JOIN order_lines AS ol ON o.id=ol.order_id
WHERE status = 'PENDING'
AND o.id NOT IN (SELECT order_id FROM backorders);
UPDATE orders AS o
SET status='PENDING'
WHERE o.id IN (SELECT r.id FROM reserved AS r);
UPDATE products AS p
SET quantity = (SELECT p.quantity-r.quantity FROM reserved AS r
WHERE p.id = r.product_id)
WHERE p.id IN (SELECT product_id FROM reserved);
COMMIT;