shess | e2fe339 | 2015-02-01 07:22:47 | [diff] [blame] | 1 | # 2011 December 9 |
| 2 | # |
| 3 | # The author disclaims copyright to this source code. In place of |
| 4 | # 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 been |
| 14 | # fixed. |
| 15 | # |
| 16 | |
| 17 | set testdir [file dirname $argv0] |
| 18 | source $testdir/tester.tcl |
| 19 | set testprefix tkt-7bbfb7d442 |
| 20 | |
| 21 | do_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 BEGIN |
| 34 | 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 = c |
| 39 | ) WHERE t3_a = new.t3_a; |
| 40 | END; |
| 41 | } |
| 42 | |
| 43 | do_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 | |
| 50 | do_execsql_test 1.3 { DELETE FROM t3 } |
| 51 | |
| 52 | ifcapable 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 | # |
| 65 | do_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_AfterInsert |
| 76 | 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,DeliveredQty |
| 81 | ) SELECT |
| 82 | 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 DeliveredQty |
| 88 | FROM ( |
| 89 | SELECT |
| 90 | NEW.InventoryControlId AS InventoryControlId, |
| 91 | II.SKU AS SKU, |
| 92 | II.Variant AS Variant, |
| 93 | COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS ControlDate |
| 94 | FROM |
| 95 | InventoryItem II |
| 96 | LEFT JOIN |
| 97 | InventoryControl LastClosedIC |
| 98 | ON LastClosedIC.InventoryControlId IN ( SELECT 99999 ) |
| 99 | WHERE |
| 100 | II.SKU=NEW.SKU AND |
| 101 | II.Variant=NEW.Variant |
| 102 | ) T1 |
| 103 | LEFT JOIN ( |
| 104 | SELECT |
| 105 | TD.SKU AS SKU, |
| 106 | TD.Variant AS Variant, |
| 107 | 10 AS DeliveredQty |
| 108 | FROM |
| 109 | TransactionDetail TD |
| 110 | WHERE |
| 111 | TD.SKU=NEW.SKU AND |
| 112 | TD.Variant=NEW.Variant |
| 113 | ) T2 |
| 114 | ON T2.SKU=T1.SKU AND |
| 115 | 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 | |
| 144 | do_execsql_test 2.2 { |
| 145 | SELECT SKU, DeliveredQty FROM InventoryControl WHERE SKU=31 |
| 146 | } {31 10} |
| 147 | |
| 148 | do_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 | |
| 154 | finish_test |