Annotation of embedaddon/sqlite3/test/triggerA.test, revision 1.1
1.1 ! misho 1: # 2008 February 12
! 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. Specifically,
! 12: # it tests issues relating to firing an INSTEAD OF trigger on a VIEW
! 13: # when one tries to UPDATE or DELETE from the view. Does the WHERE
! 14: # clause of the UPDATE or DELETE statement get passed down correctly
! 15: # into the query that manifests the view?
! 16: #
! 17: # Ticket #2938
! 18: #
! 19:
! 20: set testdir [file dirname $argv0]
! 21: source $testdir/tester.tcl
! 22: ifcapable !trigger||!compound {
! 23: finish_test
! 24: return
! 25: }
! 26:
! 27: # Create two table containing some sample data
! 28: #
! 29: do_test triggerA-1.1 {
! 30: db eval {
! 31: CREATE TABLE t1(x INTEGER PRIMARY KEY, y TEXT UNIQUE);
! 32: CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c TEXT);
! 33: }
! 34: set i 1
! 35: foreach word {one two three four five six seven eight nine ten} {
! 36: set j [expr {$i*100 + [string length $word]}]
! 37: db eval {
! 38: INSERT INTO t1 VALUES($i,$word);
! 39: INSERT INTO t2 VALUES(20-$i,$j,$word);
! 40: }
! 41: incr i
! 42: }
! 43: db eval {
! 44: SELECT count(*) FROM t1 UNION ALL SELECT count(*) FROM t2;
! 45: }
! 46: } {10 10}
! 47:
! 48: # Create views of various forms against one or both of the two tables.
! 49: #
! 50: do_test triggerA-1.2 {
! 51: db eval {
! 52: CREATE VIEW v1 AS SELECT y, x FROM t1;
! 53: SELECT * FROM v1 ORDER BY 1;
! 54: }
! 55: } {eight 8 five 5 four 4 nine 9 one 1 seven 7 six 6 ten 10 three 3 two 2}
! 56: do_test triggerA-1.3 {
! 57: db eval {
! 58: CREATE VIEW v2 AS SELECT x, y FROM t1 WHERE y GLOB '*e*';
! 59: SELECT * FROM v2 ORDER BY 1;
! 60: }
! 61: } {1 one 3 three 5 five 7 seven 8 eight 9 nine 10 ten}
! 62: do_test triggerA-1.4 {
! 63: db eval {
! 64: CREATE VIEW v3 AS
! 65: SELECT CAST(x AS TEXT) AS c1 FROM t1 UNION SELECT y FROM t1;
! 66: SELECT * FROM v3 ORDER BY c1;
! 67: }
! 68: } {1 10 2 3 4 5 6 7 8 9 eight five four nine one seven six ten three two}
! 69: do_test triggerA-1.5 {
! 70: db eval {
! 71: CREATE VIEW v4 AS
! 72: SELECT CAST(x AS TEXT) AS c1 FROM t1
! 73: UNION SELECT y FROM t1 WHERE x BETWEEN 3 and 5;
! 74: SELECT * FROM v4 ORDER BY 1;
! 75: }
! 76: } {1 10 2 3 4 5 6 7 8 9 five four three}
! 77: do_test triggerA-1.6 {
! 78: db eval {
! 79: CREATE VIEW v5 AS SELECT x, b FROM t1, t2 WHERE y=c;
! 80: SELECT * FROM v5 ORDER BY x DESC;
! 81: }
! 82: } {10 1003 9 904 8 805 7 705 6 603 5 504 4 404 3 305 2 203 1 103}
! 83:
! 84: # Create INSTEAD OF triggers on the views. Run UPDATE and DELETE statements
! 85: # using those triggers. Verify correct operation.
! 86: #
! 87: do_test triggerA-2.1 {
! 88: db eval {
! 89: CREATE TABLE result2(a,b);
! 90: CREATE TRIGGER r1d INSTEAD OF DELETE ON v1 BEGIN
! 91: INSERT INTO result2(a,b) VALUES(old.y, old.x);
! 92: END;
! 93: DELETE FROM v1 WHERE x=5;
! 94: SELECT * FROM result2;
! 95: }
! 96: } {five 5}
! 97: do_test triggerA-2.2 {
! 98: db eval {
! 99: CREATE TABLE result4(a,b,c,d);
! 100: CREATE TRIGGER r1u INSTEAD OF UPDATE ON v1 BEGIN
! 101: INSERT INTO result4(a,b,c,d) VALUES(old.y, old.x, new.y, new.x);
! 102: END;
! 103: UPDATE v1 SET y=y||'-extra' WHERE x BETWEEN 3 AND 5;
! 104: SELECT * FROM result4 ORDER BY a;
! 105: }
! 106: } {five 5 five-extra 5 four 4 four-extra 4 three 3 three-extra 3}
! 107:
! 108:
! 109: do_test triggerA-2.3 {
! 110: db eval {
! 111: DELETE FROM result2;
! 112: CREATE TRIGGER r2d INSTEAD OF DELETE ON v2 BEGIN
! 113: INSERT INTO result2(a,b) VALUES(old.y, old.x);
! 114: END;
! 115: DELETE FROM v2 WHERE x=5;
! 116: SELECT * FROM result2;
! 117: }
! 118: } {five 5}
! 119: do_test triggerA-2.4 {
! 120: db eval {
! 121: DELETE FROM result4;
! 122: CREATE TRIGGER r2u INSTEAD OF UPDATE ON v2 BEGIN
! 123: INSERT INTO result4(a,b,c,d) VALUES(old.y, old.x, new.y, new.x);
! 124: END;
! 125: UPDATE v2 SET y=y||'-extra' WHERE x BETWEEN 3 AND 5;
! 126: SELECT * FROM result4 ORDER BY a;
! 127: }
! 128: } {five 5 five-extra 5 three 3 three-extra 3}
! 129:
! 130:
! 131: do_test triggerA-2.5 {
! 132: db eval {
! 133: CREATE TABLE result1(a);
! 134: CREATE TRIGGER r3d INSTEAD OF DELETE ON v3 BEGIN
! 135: INSERT INTO result1(a) VALUES(old.c1);
! 136: END;
! 137: DELETE FROM v3 WHERE c1 BETWEEN '8' AND 'eight';
! 138: SELECT * FROM result1 ORDER BY a;
! 139: }
! 140: } {8 9 eight}
! 141: do_test triggerA-2.6 {
! 142: db eval {
! 143: DELETE FROM result2;
! 144: CREATE TRIGGER r3u INSTEAD OF UPDATE ON v3 BEGIN
! 145: INSERT INTO result2(a,b) VALUES(old.c1, new.c1);
! 146: END;
! 147: UPDATE v3 SET c1 = c1 || '-extra' WHERE c1 BETWEEN '8' and 'eight';
! 148: SELECT * FROM result2 ORDER BY a;
! 149: }
! 150: } {8 8-extra 9 9-extra eight eight-extra}
! 151:
! 152:
! 153: do_test triggerA-2.7 {
! 154: db eval {
! 155: DELETE FROM result1;
! 156: CREATE TRIGGER r4d INSTEAD OF DELETE ON v4 BEGIN
! 157: INSERT INTO result1(a) VALUES(old.c1);
! 158: END;
! 159: DELETE FROM v4 WHERE c1 BETWEEN '8' AND 'eight';
! 160: SELECT * FROM result1 ORDER BY a;
! 161: }
! 162: } {8 9}
! 163: do_test triggerA-2.8 {
! 164: db eval {
! 165: DELETE FROM result2;
! 166: CREATE TRIGGER r4u INSTEAD OF UPDATE ON v4 BEGIN
! 167: INSERT INTO result2(a,b) VALUES(old.c1, new.c1);
! 168: END;
! 169: UPDATE v4 SET c1 = c1 || '-extra' WHERE c1 BETWEEN '8' and 'eight';
! 170: SELECT * FROM result2 ORDER BY a;
! 171: }
! 172: } {8 8-extra 9 9-extra}
! 173:
! 174:
! 175: do_test triggerA-2.9 {
! 176: db eval {
! 177: DELETE FROM result2;
! 178: CREATE TRIGGER r5d INSTEAD OF DELETE ON v5 BEGIN
! 179: INSERT INTO result2(a,b) VALUES(old.x, old.b);
! 180: END;
! 181: DELETE FROM v5 WHERE x=5;
! 182: SELECT * FROM result2;
! 183: }
! 184: } {5 504}
! 185: do_test triggerA-2.10 {
! 186: db eval {
! 187: DELETE FROM result4;
! 188: CREATE TRIGGER r5u INSTEAD OF UPDATE ON v5 BEGIN
! 189: INSERT INTO result4(a,b,c,d) VALUES(old.x, old.b, new.x, new.b);
! 190: END;
! 191: UPDATE v5 SET b = b+9900000 WHERE x BETWEEN 3 AND 5;
! 192: SELECT * FROM result4 ORDER BY a;
! 193: }
! 194: } {3 305 3 9900305 4 404 4 9900404 5 504 5 9900504}
! 195:
! 196: # Only run the reamining tests if memory debugging is turned on.
! 197: #
! 198: ifcapable !memdebug {
! 199: puts "Skipping triggerA malloc tests: not compiled with -DSQLITE_MEMDEBUG..."
! 200: finish_test
! 201: return
! 202: }
! 203: source $testdir/malloc_common.tcl
! 204:
! 205: # Save a copy of the current database configuration.
! 206: #
! 207: db close
! 208: forcedelete test.db-triggerA
! 209: copy_file test.db test.db-triggerA
! 210: sqlite3 db test.db
! 211:
! 212: # Run malloc tests on the INSTEAD OF trigger firing.
! 213: #
! 214: do_malloc_test triggerA-3 -tclprep {
! 215: db close
! 216: forcedelete test.db test.db-journal
! 217: forcecopy test.db-triggerA test.db
! 218: sqlite3 db test.db
! 219: sqlite3_extended_result_codes db 1
! 220: db eval {SELECT * FROM v5; -- warm up the cache}
! 221: } -sqlbody {
! 222: DELETE FROM v5 WHERE x=5;
! 223: UPDATE v5 SET b=b+9900000 WHERE x BETWEEN 3 AND 5;
! 224: }
! 225:
! 226: # Clean up the saved database copy.
! 227: #
! 228: forcedelete test.db-triggerA
! 229:
! 230: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to triggerA.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test