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;

    81   }

    82 } {1 103 2 203 3 305 4 404 5 504 6 603 7 705 8 805 9 904 10 1003}

    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 file delete -force test.db-triggerA

   209 file copy 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   file delete -force test.db test.db-journal

   217   file copy -force 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 file delete -force test.db-triggerA

   229 

   230 finish_test