Return to tkt-7bbfb7d442.test CVS log

Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test

Annotation of embedaddon/sqlite3/test/tkt-7bbfb7d442.test, revision 1.1.1.1

1.1       misho       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: do_execsql_test 1.4 {
                     53:   INSERT INTO t3(t3_a) SELECT 1 UNION SELECT 2 UNION SELECT 3;
                     54:   SELECT * FROM t3;
                     55: } {1 I 2 II 3 III}
                     56: 
                     57: 
                     58: 
                     59: #-------------------------------------------------------------------------
                     60: # The following test case - 2.* - is from the original bug report as 
                     61: # posted to the mailing list.
                     62: #
                     63: do_execsql_test 2.1 {
                     64:   CREATE TABLE InventoryControl (
                     65:     InventoryControlId INTEGER PRIMARY KEY AUTOINCREMENT,
                     66:     SKU INTEGER NOT NULL,
                     67:     Variant INTEGER NOT NULL DEFAULT 0,
                     68:     ControlDate DATE NOT NULL,
                     69:     ControlState INTEGER NOT NULL DEFAULT -1,
                     70:     DeliveredQty VARCHAR(30)
                     71:   );
                     72:   
                     73:   CREATE TRIGGER TGR_InventoryControl_AfterInsert
                     74:   AFTER INSERT ON InventoryControl 
                     75:   FOR EACH ROW WHEN NEW.ControlState=-1 BEGIN 
                     76: 
                     77:   INSERT OR REPLACE INTO InventoryControl(
                     78:         InventoryControlId,SKU,Variant,ControlDate,ControlState,DeliveredQty
                     79:   ) SELECT
                     80:           T1.InventoryControlId AS InventoryControlId,
                     81:           T1.SKU AS SKU,
                     82:           T1.Variant AS Variant,
                     83:           T1.ControlDate AS ControlDate,
                     84:           1 AS ControlState,
                     85:           COALESCE(T2.DeliveredQty,0) AS DeliveredQty
                     86:       FROM (
                     87:           SELECT
                     88:               NEW.InventoryControlId AS InventoryControlId,
                     89:               II.SKU AS SKU,
                     90:               II.Variant AS Variant,
                     91:               COALESCE(LastClosedIC.ControlDate,NEW.ControlDate) AS ControlDate
                     92:           FROM
                     93:               InventoryItem II
                     94:           LEFT JOIN
                     95:               InventoryControl LastClosedIC
                     96:               ON  LastClosedIC.InventoryControlId IN ( SELECT 99999 )
                     97:           WHERE
                     98:               II.SKU=NEW.SKU AND
                     99:               II.Variant=NEW.Variant
                    100:       )   T1
                    101:       LEFT JOIN (
                    102:           SELECT
                    103:               TD.SKU AS SKU,
                    104:               TD.Variant AS Variant,
                    105:               10 AS DeliveredQty
                    106:           FROM
                    107:               TransactionDetail TD
                    108:           WHERE
                    109:               TD.SKU=NEW.SKU AND
                    110:               TD.Variant=NEW.Variant
                    111:       )   T2
                    112:       ON  T2.SKU=T1.SKU AND
                    113:           T2.Variant=T1.Variant;
                    114:   END;
                    115:   
                    116:   CREATE TABLE InventoryItem (
                    117:     SKU INTEGER NOT NULL,
                    118:     Variant INTEGER NOT NULL DEFAULT 0,
                    119:     DeptCode INTEGER NOT NULL,
                    120:     GroupCode INTEGER NOT NULL,
                    121:     ItemDescription VARCHAR(120) NOT NULL,
                    122:     PRIMARY KEY(SKU, Variant)
                    123:   );
                    124:   
                    125:   INSERT INTO InventoryItem VALUES(220,0,1,170,'Scoth Tampon Recurer');
                    126:   INSERT INTO InventoryItem VALUES(31,0,1,110,'Fromage');
                    127:   
                    128:   CREATE TABLE TransactionDetail (
                    129:     TransactionId INTEGER NOT NULL,
                    130:     SKU INTEGER NOT NULL,
                    131:     Variant INTEGER NOT NULL DEFAULT 0,
                    132:     PRIMARY KEY(TransactionId, SKU, Variant)
                    133:   );
                    134:   INSERT INTO TransactionDetail(TransactionId, SKU, Variant) VALUES(44, 31, 0);
                    135:   
                    136:   
                    137:   INSERT INTO InventoryControl(SKU, Variant, ControlDate) SELECT 
                    138:       II.SKU AS SKU, II.Variant AS Variant, '2011-08-30' AS ControlDate 
                    139:       FROM InventoryItem II;
                    140: }
                    141: 
                    142: do_execsql_test 2.2 {
                    143:   SELECT SKU, DeliveredQty FROM InventoryControl WHERE SKU=31
                    144: } {31 10}
                    145: 
                    146: do_execsql_test 2.3 {
                    147:   SELECT CASE WHEN DeliveredQty=10 THEN "TEST PASSED!" ELSE "TEST FAILED!" END 
                    148:   FROM InventoryControl WHERE SKU=31; 
                    149: } {{TEST PASSED!}}
                    150: 
                    151: 
                    152: finish_test
                    153: 
                    154: