sl@0: # 2008 February 12
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library. Specifically,
sl@0: # it tests issues relating to firing an INSTEAD OF trigger on a VIEW
sl@0: # when one tries to UPDATE or DELETE from the view.  Does the WHERE
sl@0: # clause of the UPDATE or DELETE statement get passed down correctly 
sl@0: # into the query that manifests the view?
sl@0: #
sl@0: # Ticket #2938
sl@0: #
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: ifcapable !trigger||!compound {
sl@0:   finish_test
sl@0:   return
sl@0: }
sl@0: 
sl@0: # Create two table containing some sample data
sl@0: #
sl@0: do_test triggerA-1.1 {
sl@0:   db eval {
sl@0:     CREATE TABLE t1(x INTEGER PRIMARY KEY, y TEXT UNIQUE);
sl@0:     CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c TEXT);
sl@0:   }
sl@0:   set i 1
sl@0:   foreach word {one two three four five six seven eight nine ten} {
sl@0:     set j [expr {$i*100 + [string length $word]}]
sl@0:     db eval {
sl@0:        INSERT INTO t1 VALUES($i,$word);
sl@0:        INSERT INTO t2 VALUES(20-$i,$j,$word);
sl@0:     }
sl@0:     incr i
sl@0:   }
sl@0:   db eval {
sl@0:     SELECT count(*) FROM t1 UNION ALL SELECT count(*) FROM t2;
sl@0:   }
sl@0: } {10 10}
sl@0: 
sl@0: # Create views of various forms against one or both of the two tables.
sl@0: #
sl@0: do_test triggerA-1.2 {
sl@0:   db eval {
sl@0:      CREATE VIEW v1 AS SELECT y, x FROM t1;
sl@0:      SELECT * FROM v1 ORDER BY 1;
sl@0:   }
sl@0: } {eight 8 five 5 four 4 nine 9 one 1 seven 7 six 6 ten 10 three 3 two 2}
sl@0: do_test triggerA-1.3 {
sl@0:   db eval {
sl@0:      CREATE VIEW v2 AS SELECT x, y FROM t1 WHERE y GLOB '*e*';
sl@0:      SELECT * FROM v2 ORDER BY 1;
sl@0:   }
sl@0: } {1 one 3 three 5 five 7 seven 8 eight 9 nine 10 ten}
sl@0: do_test triggerA-1.4 {
sl@0:   db eval {
sl@0:      CREATE VIEW v3 AS
sl@0:        SELECT CAST(x AS TEXT) AS c1 FROM t1 UNION SELECT y FROM t1;
sl@0:      SELECT * FROM v3 ORDER BY c1;
sl@0:   }
sl@0: } {1 10 2 3 4 5 6 7 8 9 eight five four nine one seven six ten three two}
sl@0: do_test triggerA-1.5 {
sl@0:   db eval {
sl@0:      CREATE VIEW v4 AS
sl@0:         SELECT CAST(x AS TEXT) AS c1 FROM t1
sl@0:         UNION SELECT y FROM t1 WHERE x BETWEEN 3 and 5;
sl@0:      SELECT * FROM v4 ORDER BY 1;
sl@0:   }
sl@0: } {1 10 2 3 4 5 6 7 8 9 five four three}
sl@0: do_test triggerA-1.6 {
sl@0:   db eval {
sl@0:      CREATE VIEW v5 AS SELECT x, b FROM t1, t2 WHERE y=c;
sl@0:      SELECT * FROM v5;
sl@0:   }
sl@0: } {1 103 2 203 3 305 4 404 5 504 6 603 7 705 8 805 9 904 10 1003}
sl@0: 
sl@0: # Create INSTEAD OF triggers on the views.  Run UPDATE and DELETE statements
sl@0: # using those triggers.  Verify correct operation.
sl@0: #
sl@0: do_test triggerA-2.1 {
sl@0:   db eval {
sl@0:      CREATE TABLE result2(a,b);
sl@0:      CREATE TRIGGER r1d INSTEAD OF DELETE ON v1 BEGIN
sl@0:        INSERT INTO result2(a,b) VALUES(old.y, old.x);
sl@0:      END;
sl@0:      DELETE FROM v1 WHERE x=5;
sl@0:      SELECT * FROM result2;
sl@0:   }
sl@0: } {five 5}
sl@0: do_test triggerA-2.2 {
sl@0:   db eval {
sl@0:      CREATE TABLE result4(a,b,c,d);
sl@0:      CREATE TRIGGER r1u INSTEAD OF UPDATE ON v1 BEGIN
sl@0:        INSERT INTO result4(a,b,c,d) VALUES(old.y, old.x, new.y, new.x);
sl@0:      END;
sl@0:      UPDATE v1 SET y=y||'-extra' WHERE x BETWEEN 3 AND 5;
sl@0:      SELECT * FROM result4 ORDER BY a;
sl@0:   }
sl@0: } {five 5 five-extra 5 four 4 four-extra 4 three 3 three-extra 3}
sl@0: 
sl@0: 
sl@0: do_test triggerA-2.3 {
sl@0:   db eval {
sl@0:      DELETE FROM result2;
sl@0:      CREATE TRIGGER r2d INSTEAD OF DELETE ON v2 BEGIN
sl@0:        INSERT INTO result2(a,b) VALUES(old.y, old.x);
sl@0:      END;
sl@0:      DELETE FROM v2 WHERE x=5;
sl@0:      SELECT * FROM result2;
sl@0:   }
sl@0: } {five 5}
sl@0: do_test triggerA-2.4 {
sl@0:   db eval {
sl@0:      DELETE FROM result4;
sl@0:      CREATE TRIGGER r2u INSTEAD OF UPDATE ON v2 BEGIN
sl@0:        INSERT INTO result4(a,b,c,d) VALUES(old.y, old.x, new.y, new.x);
sl@0:      END;
sl@0:      UPDATE v2 SET y=y||'-extra' WHERE x BETWEEN 3 AND 5;
sl@0:      SELECT * FROM result4 ORDER BY a;
sl@0:   }
sl@0: } {five 5 five-extra 5 three 3 three-extra 3}
sl@0: 
sl@0: 
sl@0: do_test triggerA-2.5 {
sl@0:   db eval {
sl@0:      CREATE TABLE result1(a);
sl@0:      CREATE TRIGGER r3d INSTEAD OF DELETE ON v3 BEGIN
sl@0:        INSERT INTO result1(a) VALUES(old.c1);
sl@0:      END;
sl@0:      DELETE FROM v3 WHERE c1 BETWEEN '8' AND 'eight';
sl@0:      SELECT * FROM result1 ORDER BY a;
sl@0:   }
sl@0: } {8 9 eight}
sl@0: do_test triggerA-2.6 {
sl@0:   db eval {
sl@0:      DELETE FROM result2;
sl@0:      CREATE TRIGGER r3u INSTEAD OF UPDATE ON v3 BEGIN
sl@0:        INSERT INTO result2(a,b) VALUES(old.c1, new.c1);
sl@0:      END;
sl@0:      UPDATE v3 SET c1 = c1 || '-extra' WHERE c1 BETWEEN '8' and 'eight';
sl@0:      SELECT * FROM result2 ORDER BY a;
sl@0:   }
sl@0: } {8 8-extra 9 9-extra eight eight-extra}
sl@0: 
sl@0: 
sl@0: do_test triggerA-2.7 {
sl@0:   db eval {
sl@0:      DELETE FROM result1;
sl@0:      CREATE TRIGGER r4d INSTEAD OF DELETE ON v4 BEGIN
sl@0:        INSERT INTO result1(a) VALUES(old.c1);
sl@0:      END;
sl@0:      DELETE FROM v4 WHERE c1 BETWEEN '8' AND 'eight';
sl@0:      SELECT * FROM result1 ORDER BY a;
sl@0:   }
sl@0: } {8 9}
sl@0: do_test triggerA-2.8 {
sl@0:   db eval {
sl@0:      DELETE FROM result2;
sl@0:      CREATE TRIGGER r4u INSTEAD OF UPDATE ON v4 BEGIN
sl@0:        INSERT INTO result2(a,b) VALUES(old.c1, new.c1);
sl@0:      END;
sl@0:      UPDATE v4 SET c1 = c1 || '-extra' WHERE c1 BETWEEN '8' and 'eight';
sl@0:      SELECT * FROM result2 ORDER BY a;
sl@0:   }
sl@0: } {8 8-extra 9 9-extra}
sl@0: 
sl@0: 
sl@0: do_test triggerA-2.9 {
sl@0:   db eval {
sl@0:      DELETE FROM result2;
sl@0:      CREATE TRIGGER r5d INSTEAD OF DELETE ON v5 BEGIN
sl@0:        INSERT INTO result2(a,b) VALUES(old.x, old.b);
sl@0:      END;
sl@0:      DELETE FROM v5 WHERE x=5;
sl@0:      SELECT * FROM result2;
sl@0:   }
sl@0: } {5 504}
sl@0: do_test triggerA-2.10 {
sl@0:   db eval {
sl@0:      DELETE FROM result4;
sl@0:      CREATE TRIGGER r5u INSTEAD OF UPDATE ON v5 BEGIN
sl@0:        INSERT INTO result4(a,b,c,d) VALUES(old.x, old.b, new.x, new.b);
sl@0:      END;
sl@0:      UPDATE v5 SET b = b+9900000 WHERE x BETWEEN 3 AND 5;
sl@0:      SELECT * FROM result4 ORDER BY a;
sl@0:   }
sl@0: } {3 305 3 9900305 4 404 4 9900404 5 504 5 9900504}
sl@0: 
sl@0: # Only run the reamining tests if memory debugging is turned on.
sl@0: #
sl@0: ifcapable !memdebug {
sl@0:    puts "Skipping triggerA malloc tests: not compiled with -DSQLITE_MEMDEBUG..."
sl@0:    finish_test
sl@0:    return
sl@0: }
sl@0: source $testdir/malloc_common.tcl
sl@0: 
sl@0: # Save a copy of the current database configuration.
sl@0: #
sl@0: db close
sl@0: file delete -force test.db-triggerA
sl@0: file copy test.db test.db-triggerA
sl@0: sqlite3 db test.db
sl@0: 
sl@0: # Run malloc tests on the INSTEAD OF trigger firing.
sl@0: #
sl@0: do_malloc_test triggerA-3 -tclprep {
sl@0:   db close
sl@0:   file delete -force test.db test.db-journal
sl@0:   file copy -force test.db-triggerA test.db
sl@0:   sqlite3 db test.db
sl@0:   sqlite3_extended_result_codes db 1  
sl@0:   db eval {SELECT * FROM v5; -- warm up the cache}
sl@0: } -sqlbody {
sl@0:    DELETE FROM v5 WHERE x=5;
sl@0:    UPDATE v5 SET b=b+9900000 WHERE x BETWEEN 3 AND 5;
sl@0: }
sl@0: 
sl@0: # Clean up the saved database copy.
sl@0: #
sl@0: file delete -force test.db-triggerA
sl@0: 
sl@0: finish_test