Introduction to the problem
SQLite has a number of issues that challenges a programmer to control how the database processes data with triggers. To illustrate the behavior of the optimizer function I’ve created a script with a deliberate error in one of the triggers. The script creates two tables and two update triggers. Here is the script a2.sql:
-- Automatic ANALYZE
PRAGMA optimize;
CREATE TABLE IF NOT EXISTS A1 (
A1_INDEX VARCHAR(20),
A1_CAT VARCHAR(1) DEFAULT 'W',
A1_STOCK_MIN INTEGER,
A1_STOCK_LEVEL INTEGER,
A1_PRODUCT TEXT,
A1_STATUS NUMBER
CHECK (A1_STATUS IN (1, 2, 4, 8, 16, 32, 64, 128)) DEFAULT 1,
A1_REF VARCHAR(20) CHECK (A1_REF != A1_INDEX),
A1_DTS_CREATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
A1_DTS_UPDATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
CONSTRAINT A1_PK PRIMARY KEY (
A1_INDEX,
A1_CAT
)
-- UNIQUE (
-- A1_INDEX,
-- A1_CAT,
-- ) ON CONFLICT ROLLBACK
);
-- PRODUCTION STATUS:
-- 1 → PREPARED FOR PRODUCTION
-- 2 → IN PRODUCTIION
-- 4 → PRODUCTION FINISHED
-- 128 → PRODUCTION BATCH REJECTED
--
-- STATUS
-- 1 → PLANNING PROCESSING
-- 2 → PROCESSING
-- 4 → EVALUTION
-- 8 → PROCESSED
CREATE TABLE IF NOT EXISTS C1 (
C1_INDEX VARCHAR(20),
C1_PRODUCTION_ORDER VARCHAR(20),
C1_QTY_TO_PRODUCE NUMBER DEFAULT 0,
C1_QTY_PRODUCED NUMBER DEFAULT 0,
C1_PRODUCTION_STATUS NUMBER
CHECK (C1_PRODUCTION_STATUS IN (1, 2, 4, 128)),
C1_STATUS NUMBER
CHECK (C1_STATUS IN (1, 2, 4, 8)),
C1_DTS_CREATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
C1_DTS_UPDATED TEXT NOT NULL DEFAULT (DATETIME('NOW', 'UTC')),
CONSTRAINT C1_PK PRIMARY KEY (
C1_INDEX
) ON CONFLICT ROLLBACK,
CONSTRAINT C1_INDEX_FK FOREIGN KEY (
C1_INDEX
)
REFERENCES A1 (
A1_INDEX
)
);
-- Create stock
INSERT INTO A1 (A1_INDEX, A1_STOCK_MIN, A1_STOCK_LEVEL, A1_PRODUCT, A1_REF)
VALUES
(12, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Drei im Weggla', NULL),
( 7, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Blaue Zipfel mit Brezel', NULL),
(17, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Nürnberger Rostbratwurst', NULL),
(14, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Brätwurst', 17),
(19, cast(abs((0.0661 * random()) % 997 +1) as integer), ABS(RANDOM() % 14731) + 1, 'Brühwürste', NULL);
CREATE TRIGGER IF NOT EXISTS A1_100 BEFORE UPDATE ON A1
WHEN (NEW.A1_STATUS = 2) AND (NEW.A1_REF ISNULL)
BEGIN
SELECT CASE 1
WHEN (NEW.A1_STATUS == 2)
THEN CASE 1
WHEN (NOT (OLD.A1_STATUS IN (1, 8, 32, 64))) OR (((OLD.A1_REF NOT NULL)) AND (NOT (OLD.A1_STATUS == 4)))
THEN RAISE (ABORT, 'Error: someone goofed')
WHEN ((SELECT C1_STATUS FROM C1 WHERE C1_INDEX == NEW.A1_INDEX) == 1)
THEN RAISE (ABORT, 'Error you are not allowed to change the status')
END
END
END;
UPDATE OR ROLLBACK A1 SET
A1_STATUS = NEW.A1_STATUS
WHERE A1_INDEX = NEW.A1_INDEX;
END;
-- Finished the production of sauceges
CREATE TRIGGER IF NOT EXISTS C1_100 BEFORE UPDATE ON A1
WHEN (NEW.C1_PRODUCTIONS_STATUS = 8)
BEGIN
SELECT CASE 1
WHEN (NEW.C1_QUANTITY_PRODUCED == 0)
THEN RAISE (ABORT, 'Error: someone goofed')
WHEN (NEW.C1_QTY__TO_PRODUCE == 0)
THEN RAISE (ABORT, 'Error there no sausages are ordered!')
END;
UPDATE OR ROLLBACK A1 SET
A1_STOCK_LEVEL = A1_STOCK_LEVEL + C1_QTY_PRODUCED
WHERE A1_INDEX = NEW.C1_INDEX;
UPDATE OR ROLLBACK C1 SET
C1_QTY_TO_PRODUCE = 0,
C1_QTY_PRODUCED = 0,
C1_STATUS = 1,
C1_PRODUCTION_STATUS = 1 -- CLEAN AND RESET THE PRODUCTION CHAIN
WHERE C1_INDEX == NEW.C1_INDEX;
END;
-- Create a production order
INSERT INTO C1 (C1_INDEX, C1_PRODUCTION_ORDER, C1_QTY_TO_PRODUCE, C1_QTY_PRODUCED, C1_PRODUCTION_STATUS, C1_STATUS)
VALUES
(12, '07072020_C', ABS(RANDOM() % 2137) + 1, 0, 1, 1),
(19, '07072020_B', ABS(RANDOM() % 2137) + 1, 0, 1, 1);
The trigger C1_100 has a syntax error. The variable NEW.C1_PRODUCTIONS_STATUS doesn’t exist. This deliberate error is part of the test.
Build the database with the following command:
sqlite3 a2.db < a2.sql
∎
Test a1.sql
This test updates the product status in table A1 to indicate that the production is being planned. The script for test_a1.sql looks like this:
.selecttrace 0xfffff
UPDATE A1 SET
A1_STATUS = 1
WHERE A1_INDEX == 14;
Execute the script as follows:
sqlite3 a2.db < test_a1.sql | tee result_test_a1.txt
SQLite abends the execution of the test and shows the following freaky error messages:
Error: unknown command or invalid arguments: "selecttrace". Enter ".help" for help
Error: near line 3: no such column: NEW.C1_PRODUCTIONS_STATUS
∎
Observations
I’ve made the following observations:
• The syntax error in trigger C1_100 should have been detected while SQLite is building the database;
• The .selecttrace was initially fixed but after update Ubuntu the command is no longer working;
• Why is SQLite scanning also trigger C_100 when an update effects only table A1.