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     ! 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: