# 2011 December 9 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests to verify that ticket [7bbfb7d442] has been # fixed. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix tkt-7bbfb7d442 do_execsql_test 1.1 { CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two'); INSERT INTO t1 VALUES(3, 'three'); CREATE TABLE t2(c, d); INSERT INTO t2 VALUES('one', 'I'); INSERT INTO t2 VALUES('two', 'II'); INSERT INTO t2 VALUES('three', 'III'); CREATE TABLE t3(t3_a PRIMARY KEY, t3_d); CREATE TRIGGER t3t AFTER INSERT ON t3 WHEN new.t3_d IS NULL BEGIN UPDATE t3 SET t3_d = ( SELECT d FROM (SELECT * FROM t2 WHERE (new.t3_a%2)=(rowid%2) LIMIT 10), (SELECT * FROM t1 WHERE (new.t3_a%2)=(rowid%2) LIMIT 10) WHERE a = new.t3_a AND b = c ) WHERE t3_a = new.t3_a; END; } do_execsql_test 1.2 { INSERT INTO t3(t3_a) VALUES(1); INSERT INTO t3(t3_a) VALUES(2); INSERT INTO t3(t3_a) VALUES(3); SELECT * FROM t3; } {1 I 2 II 3 III} do_execsql_test 1.3 { DELETE FROM t3 } do_execsql_test 1.4 { INSERT INTO t3(t3_a) SELECT 1 UNION SELECT 2 UNION SELECT 3; SELECT * FROM t3; } {1 I 2 II 3 III} #------------------------------------------------------------------------- # The following test case - 2.* - is from the original bug report as # posted to the mailing list. # do_execsql_test 2.1 { CREATE TABLE InventoryControl ( InventoryControlId INTEGER PRIMARY KEY AUTOINCREMENT, SKU INTEGER NOT NULL, Variant INTEGER NOT NULL DEFAULT 0, ControlDate DATE NOT NULL, ControlState INTEGER NOT NULL DEFAULT -1, DeliveredQty VARCHAR(30) ); CREATE TRIGGER TGR_InventoryControl_AfterInsert AFTER INSERT ON InventoryControl FOR EACH ROW WHEN NEW.ControlState=-1 BEGIN INSERT OR REPLACE INTO InventoryControl( InventoryControlId,SKU,Variant,ControlDate,ControlState,DeliveredQty ) SELECT T1.InventoryControlId AS InventoryControlId, T1.SKU AS SKU, T1.Variant AS Variant, T1.ControlDate AS ControlDate, 1 AS ControlState, COALESCE(T2.DeliveredQty,0) AS DeliveredQty FROM ( SELECT NEW.InventoryControlId AS InventoryControlId, II.SKU AS SKU, II.Variant AS Variant, COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS ControlDate FROM InventoryItem II LEFT JOIN InventoryControl LastClosedIC ON LastClosedIC.InventoryControlId IN ( SELECT 99999 ) WHERE II.SKU=NEW.SKU AND II.Variant=NEW.Variant ) T1 LEFT JOIN ( SELECT TD.SKU AS SKU, TD.Variant AS Variant, 10 AS DeliveredQty FROM TransactionDetail TD WHERE TD.SKU=NEW.SKU AND TD.Variant=NEW.Variant ) T2 ON T2.SKU=T1.SKU AND T2.Variant=T1.Variant; END; CREATE TABLE InventoryItem ( SKU INTEGER NOT NULL, Variant INTEGER NOT NULL DEFAULT 0, DeptCode INTEGER NOT NULL, GroupCode INTEGER NOT NULL, ItemDescription VARCHAR(120) NOT NULL, PRIMARY KEY(SKU, Variant) ); INSERT INTO InventoryItem VALUES(220,0,1,170,'Scoth Tampon Recurer'); INSERT INTO InventoryItem VALUES(31,0,1,110,'Fromage'); CREATE TABLE TransactionDetail ( TransactionId INTEGER NOT NULL, SKU INTEGER NOT NULL, Variant INTEGER NOT NULL DEFAULT 0, PRIMARY KEY(TransactionId, SKU, Variant) ); INSERT INTO TransactionDetail(TransactionId, SKU, Variant) VALUES(44, 31, 0); INSERT INTO InventoryControl(SKU, Variant, ControlDate) SELECT II.SKU AS SKU, II.Variant AS Variant, '2011-08-30' AS ControlDate FROM InventoryItem II; } do_execsql_test 2.2 { SELECT SKU, DeliveredQty FROM InventoryControl WHERE SKU=31 } {31 10} do_execsql_test 2.3 { SELECT CASE WHEN DeliveredQty=10 THEN "TEST PASSED!" ELSE "TEST FAILED!" END FROM InventoryControl WHERE SKU=31; } {{TEST PASSED!}} finish_test