AryaWu/sqlite

1# 2011 December 92#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library.12#13# This file implements tests to verify that ticket [7bbfb7d442] has been14# fixed.  15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19set testprefix tkt-7bbfb7d44220 21do_execsql_test 1.1 {22  CREATE TABLE t1(a, b);23  INSERT INTO t1 VALUES(1, 'one');24  INSERT INTO t1 VALUES(2, 'two');25  INSERT INTO t1 VALUES(3, 'three');26 27  CREATE TABLE t2(c, d);28  INSERT INTO t2 VALUES('one', 'I');29  INSERT INTO t2 VALUES('two', 'II');30  INSERT INTO t2 VALUES('three', 'III');31 32  CREATE TABLE t3(t3_a PRIMARY KEY, t3_d);33  CREATE TRIGGER t3t AFTER INSERT ON t3 WHEN new.t3_d IS NULL BEGIN34    UPDATE t3 SET t3_d = (35      SELECT d FROM 36        (SELECT * FROM t2 WHERE (new.t3_a%2)=(rowid%2) LIMIT 10),37        (SELECT * FROM t1 WHERE (new.t3_a%2)=(rowid%2) LIMIT 10)38      WHERE a = new.t3_a AND b = c39    ) WHERE t3_a = new.t3_a;40  END;41}42 43do_execsql_test 1.2 {44  INSERT INTO t3(t3_a) VALUES(1);45  INSERT INTO t3(t3_a) VALUES(2);46  INSERT INTO t3(t3_a) VALUES(3);47  SELECT * FROM t3;48} {1 I 2 II 3 III}49 50do_execsql_test 1.3 { DELETE FROM t3 }51 52ifcapable compound {53  do_execsql_test 1.4 {54    INSERT INTO t3(t3_a) SELECT 1 UNION SELECT 2 UNION SELECT 3;55    SELECT * FROM t3;56  } {1 I 2 II 3 III}57}58 59 60 61#-------------------------------------------------------------------------62# The following test case - 2.* - is from the original bug report as 63# posted to the mailing list.64#65do_execsql_test 2.1 {66  CREATE TABLE InventoryControl (67    InventoryControlId INTEGER PRIMARY KEY AUTOINCREMENT,68    SKU INTEGER NOT NULL,69    Variant INTEGER NOT NULL DEFAULT 0,70    ControlDate DATE NOT NULL,71    ControlState INTEGER NOT NULL DEFAULT -1,72    DeliveredQty VARCHAR(30)73  );74  75  CREATE TRIGGER TGR_InventoryControl_AfterInsert76  AFTER INSERT ON InventoryControl 77  FOR EACH ROW WHEN NEW.ControlState=-1 BEGIN 78 79  INSERT OR REPLACE INTO InventoryControl(80        InventoryControlId,SKU,Variant,ControlDate,ControlState,DeliveredQty81  ) SELECT82          T1.InventoryControlId AS InventoryControlId,83          T1.SKU AS SKU,84          T1.Variant AS Variant,85          T1.ControlDate AS ControlDate,86          1 AS ControlState,87          COALESCE(T2.DeliveredQty,0) AS DeliveredQty88      FROM (89          SELECT90              NEW.InventoryControlId AS InventoryControlId,91              II.SKU AS SKU,92              II.Variant AS Variant,93              COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS ControlDate94          FROM95              InventoryItem II96          LEFT JOIN97              InventoryControl LastClosedIC98              ON  LastClosedIC.InventoryControlId IN ( SELECT 99999 )99          WHERE100              II.SKU=NEW.SKU AND101              II.Variant=NEW.Variant102      )   T1103      LEFT JOIN (104          SELECT105              TD.SKU AS SKU,106              TD.Variant AS Variant,107              10 AS DeliveredQty108          FROM109              TransactionDetail TD110          WHERE111              TD.SKU=NEW.SKU AND112              TD.Variant=NEW.Variant113      )   T2114      ON  T2.SKU=T1.SKU AND115          T2.Variant=T1.Variant;116  END;117  118  CREATE TABLE InventoryItem (119    SKU INTEGER NOT NULL,120    Variant INTEGER NOT NULL DEFAULT 0,121    DeptCode INTEGER NOT NULL,122    GroupCode INTEGER NOT NULL,123    ItemDescription VARCHAR(120) NOT NULL,124    PRIMARY KEY(SKU, Variant)125  );126  127  INSERT INTO InventoryItem VALUES(220,0,1,170,'Scoth Tampon Recurer');128  INSERT INTO InventoryItem VALUES(31,0,1,110,'Fromage');129  130  CREATE TABLE TransactionDetail (131    TransactionId INTEGER NOT NULL,132    SKU INTEGER NOT NULL,133    Variant INTEGER NOT NULL DEFAULT 0,134    PRIMARY KEY(TransactionId, SKU, Variant)135  );136  INSERT INTO TransactionDetail(TransactionId, SKU, Variant) VALUES(44, 31, 0);137  138  139  INSERT INTO InventoryControl(SKU, Variant, ControlDate) SELECT 140      II.SKU AS SKU, II.Variant AS Variant, '2011-08-30' AS ControlDate 141      FROM InventoryItem II;142}143 144do_execsql_test 2.2 {145  SELECT SKU, DeliveredQty FROM InventoryControl WHERE SKU=31146} {31 10}147 148do_execsql_test 2.3 {149  SELECT CASE WHEN DeliveredQty=10 THEN 'TEST PASSED!' ELSE 'TEST FAILED!' END 150  FROM InventoryControl WHERE SKU=31; 151} {{TEST PASSED!}}152 153 154finish_test155