SQLite Forum

SELECT FOR UPDATE, WHERE ALL
Login

SELECT FOR UPDATE, WHERE ALL

(1) By anonymous on 2020-05-25 15:40:36 [source]

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;