Return to tkt-7bbfb7d442.test CVS log

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

sqlite3

    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: