000001  # 2004 Jan 14
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  # This file implements regression tests for TCL interface to the
000012  # SQLite library. 
000013  #
000014  # The focus of the tests in this file is the  following interface:
000015  #
000016  #      sqlite_commit_hook    (tests hook-1..hook-3 inclusive)
000017  #      sqlite_update_hook    (tests hook-4-*)
000018  #      sqlite_rollback_hook  (tests hook-5.*)
000019  #      sqlite_preupdate_hook  (tests hook-7..hook-12)
000020  #
000021  # $Id: hook.test,v 1.15 2009/04/07 14:14:23 danielk1977 Exp $
000022  
000023  set testdir [file dirname $argv0]
000024  source $testdir/tester.tcl
000025  set ::testprefix hook
000026  
000027  do_test hook-1.2 {
000028    db commit_hook
000029  } {}
000030  
000031  
000032  do_test hook-3.1 {
000033    set commit_cnt 0
000034    proc commit_hook {} {
000035      incr ::commit_cnt
000036      return 0
000037    }
000038    db commit_hook ::commit_hook
000039    db commit_hook
000040  } {::commit_hook}
000041  do_test hook-3.2 {
000042    set commit_cnt
000043  } {0}
000044  do_test hook-3.3 {
000045    execsql {
000046      CREATE TABLE t2(a,b);
000047    }
000048    set commit_cnt
000049  } {1}
000050  do_test hook-3.4 {
000051    execsql {
000052      INSERT INTO t2 VALUES(1,2);
000053      INSERT INTO t2 SELECT a+1, b+1 FROM t2;
000054      INSERT INTO t2 SELECT a+2, b+2 FROM t2;
000055    }
000056    set commit_cnt
000057  } {4}
000058  do_test hook-3.5 {
000059    set commit_cnt {}
000060    proc commit_hook {} {
000061      set ::commit_cnt [execsql {SELECT * FROM t2}]
000062      return 0
000063    }
000064    execsql {
000065      INSERT INTO t2 VALUES(5,6);
000066    }
000067    set commit_cnt
000068  } {1 2 2 3 3 4 4 5 5 6}
000069  do_test hook-3.6 {
000070    set commit_cnt {}
000071    proc commit_hook {} {
000072      set ::commit_cnt [execsql {SELECT * FROM t2}] 
000073      return 1
000074    }
000075    catchsql {
000076      INSERT INTO t2 VALUES(6,7);
000077    }
000078  } {1 {constraint failed}}
000079  verify_ex_errcode hook-3.6b SQLITE_CONSTRAINT_COMMITHOOK
000080  do_test hook-3.7 {
000081    set ::commit_cnt
000082  } {1 2 2 3 3 4 4 5 5 6 6 7}
000083  do_test hook-3.8 {
000084    execsql {SELECT * FROM t2}
000085  } {1 2 2 3 3 4 4 5 5 6}
000086  
000087  # Test turnning off the commit hook
000088  #
000089  do_test hook-3.9 {
000090    db commit_hook {}
000091    set ::commit_cnt {}
000092    execsql {
000093      INSERT INTO t2 VALUES(7,8);
000094    }
000095    set ::commit_cnt
000096  } {}
000097  
000098  # Ticket #3564.
000099  #
000100  do_test hook-3.10 {
000101    forcedelete test2.db test2.db-journal
000102    sqlite3 db2 test2.db
000103    proc commit_hook {} {
000104      set y [db2 one {SELECT y FROM t3 WHERE y>10}]
000105      return [expr {$y>10}]
000106    }
000107    db2 eval {CREATE TABLE t3(x,y)}
000108    db2 commit_hook commit_hook
000109    catchsql {INSERT INTO t3 VALUES(1,2)} db2
000110    catchsql {INSERT INTO t3 VALUES(11,12)} db2
000111    catchsql {INSERT INTO t3 VALUES(3,4)} db2
000112    db2 eval {
000113      SELECT * FROM t3 ORDER BY x;
000114    }
000115  } {1 2 3 4}
000116  db2 close
000117  
000118  
000119  #----------------------------------------------------------------------------
000120  # Tests for the update-hook.
000121  #
000122  # 4.1.* - Very simple tests. Test that the update hook is invoked correctly 
000123  #         for INSERT, DELETE and UPDATE statements, including DELETE 
000124  #         statements with no WHERE clause.
000125  # 4.2.* - Check that the update-hook is invoked for rows modified by trigger
000126  #         bodies. Also that the database name is correctly reported when 
000127  #         an attached database is modified.
000128  # 4.3.* - Do some sorting, grouping, compound queries, population and 
000129  #         depopulation of indices, to make sure the update-hook is not 
000130  #         invoked incorrectly.
000131  #
000132  # EVIDENCE-OF: R-21999-45122 The sqlite3_update_hook() interface
000133  # registers a callback function with the database connection identified
000134  # by the first argument to be invoked whenever a row is updated,
000135  # inserted or deleted in a rowid table.
000136  
000137  # Simple tests
000138  do_test hook-4.1.1a {
000139    catchsql {
000140      DROP TABLE t1;
000141    }
000142    unset -nocomplain ::update_hook
000143    set ::update_hook {}
000144    db update_hook [list lappend ::update_hook]
000145    #
000146    # EVIDENCE-OF: R-24531-54682 The update hook is not invoked when
000147    # internal system tables are modified (i.e. sqlite_sequence).
000148    #
000149    execsql {
000150      CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
000151      CREATE TABLE t1w(a INT PRIMARY KEY, b) WITHOUT ROWID;
000152    }
000153    set ::update_hook
000154  } {}
000155  do_test hook-4.1.1b {
000156    execsql {
000157      INSERT INTO t1 VALUES(1, 'one');
000158      INSERT INTO t1 VALUES(2, 'two');
000159      INSERT INTO t1 VALUES(3, 'three');
000160      INSERT INTO t1w SELECT * FROM t1;
000161    }
000162  } {}
000163  
000164  # EVIDENCE-OF: R-15506-57666 The second callback argument is one of
000165  # SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE, depending on the
000166  # operation that caused the callback to be invoked.
000167  #
000168  # EVIDENCE-OF: R-29213-61195 The third and fourth arguments to the
000169  # callback contain pointers to the database and table name containing
000170  # the affected row.
000171  #
000172  # EVIDENCE-OF: R-30809-57812 The final callback parameter is the rowid
000173  # of the row.
000174  #
000175  do_test hook-4.1.2 {
000176    set ::update_hook {}
000177    execsql {
000178      INSERT INTO t1 VALUES(4, 'four');
000179      DELETE FROM t1 WHERE b = 'two';
000180      UPDATE t1 SET b = '' WHERE a = 1 OR a = 3;
000181      DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now)
000182    }
000183    set ::update_hook
000184  } [list \
000185      INSERT main t1 4 \
000186      DELETE main t1 2 \
000187      UPDATE main t1 1 \
000188      UPDATE main t1 3 \
000189      DELETE main t1 1 \
000190      DELETE main t1 3 \
000191      DELETE main t1 4 \
000192  ]
000193  
000194  # EVIDENCE-OF: R-61808-14344 The sqlite3_update_hook() interface does
000195  # not fire callbacks for changes to a WITHOUT ROWID table.
000196  #
000197  # EVIDENCE-OF: R-33257-44249 The update hook is not invoked when WITHOUT
000198  # ROWID tables are modified.
000199  #
000200  do_test hook-4.1.2w {
000201    set ::update_hook {}
000202    execsql {
000203      INSERT INTO t1w VALUES(4, 'four');
000204      DELETE FROM t1w WHERE b = 'two';
000205      UPDATE t1w SET b = '' WHERE a = 1 OR a = 3;
000206      DELETE FROM t1w WHERE 1; -- Avoid the truncate optimization (for now)
000207    }
000208    set ::update_hook
000209  } {}
000210  
000211  ifcapable trigger {
000212    # Update hook is not invoked for changes to sqlite_master
000213    #
000214    do_test hook-4.1.3 {
000215      set ::update_hook {}
000216      execsql {
000217        CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END;
000218      }
000219      set ::update_hook
000220    } {}
000221    do_test hook-4.1.4 {
000222      set ::update_hook {}
000223      execsql {
000224        DROP TRIGGER r1;
000225      }
000226      set ::update_hook
000227    } {}
000228    
000229    set ::update_hook {}
000230    do_test hook-4.2.1 {
000231      catchsql {
000232        DROP TABLE t2;
000233      }
000234      execsql {
000235        CREATE TABLE t2(c INTEGER PRIMARY KEY, d);
000236        CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN
000237          INSERT INTO t2 VALUES(new.a, new.b);
000238          UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c;
000239          DELETE FROM t2 WHERE new.a = c;
000240        END;
000241      }
000242    } {}
000243    do_test hook-4.2.2 {
000244      execsql {
000245        INSERT INTO t1 VALUES(1, 'one');
000246        INSERT INTO t1 VALUES(2, 'two');
000247      }
000248      set ::update_hook
000249    } [list \
000250        INSERT main t1 1 \
000251        INSERT main t2 1 \
000252        UPDATE main t2 1 \
000253        DELETE main t2 1 \
000254        INSERT main t1 2 \
000255        INSERT main t2 2 \
000256        UPDATE main t2 2 \
000257        DELETE main t2 2 \
000258    ]
000259  } else {
000260    execsql {
000261      INSERT INTO t1 VALUES(1, 'one');
000262      INSERT INTO t1 VALUES(2, 'two');
000263    }
000264  }
000265  
000266  # Update-hook + ATTACH
000267  set ::update_hook {}
000268  ifcapable attach {
000269    do_test hook-4.2.3 {
000270      forcedelete test2.db
000271      execsql {
000272        ATTACH 'test2.db' AS aux;
000273        CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b);
000274        INSERT INTO aux.t3 SELECT * FROM t1;
000275        UPDATE t3 SET b = 'two or so' WHERE a = 2;
000276        DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now)
000277      }
000278      set ::update_hook
000279    } [list \
000280        INSERT aux t3 1 \
000281        INSERT aux t3 2 \
000282        UPDATE aux t3 2 \
000283        DELETE aux t3 1 \
000284        DELETE aux t3 2 \
000285    ]
000286  }
000287  
000288  ifcapable trigger {
000289    execsql {
000290      DROP TRIGGER t1_trigger;
000291    }
000292  }
000293  
000294  # Test that other vdbe operations involving btree structures do not 
000295  # incorrectly invoke the update-hook.
000296  set ::update_hook {}
000297  do_test hook-4.3.1 {
000298    execsql {
000299      CREATE INDEX t1_i ON t1(b);
000300      INSERT INTO t1 VALUES(3, 'three');
000301      UPDATE t1 SET b = '';
000302      DELETE FROM t1 WHERE a > 1;
000303    }
000304    set ::update_hook
000305  } [list \
000306      INSERT main t1 3 \
000307      UPDATE main t1 1 \
000308      UPDATE main t1 2 \
000309      UPDATE main t1 3 \
000310      DELETE main t1 2 \
000311      DELETE main t1 3 \
000312  ]
000313  set ::update_hook {}
000314  ifcapable compound&&attach {
000315    do_test hook-4.3.2 {
000316      execsql {
000317        SELECT * FROM t1 UNION SELECT * FROM t3;
000318        SELECT * FROM t1 UNION ALL SELECT * FROM t3;
000319        SELECT * FROM t1 INTERSECT SELECT * FROM t3;
000320        SELECT * FROM t1 EXCEPT SELECT * FROM t3;
000321        SELECT * FROM t1 ORDER BY b;
000322        SELECT * FROM t1 GROUP BY b;
000323      }
000324      set ::update_hook
000325    } [list]
000326  }
000327  
000328  do_test hook-4.4 {
000329    execsql {
000330      CREATE TABLE t4(a UNIQUE, b);
000331      INSERT INTO t4 VALUES(1, 'a');
000332      INSERT INTO t4 VALUES(2, 'b');
000333    }
000334    set ::update_hook [list]
000335    execsql {
000336      REPLACE INTO t4 VALUES(1, 'c');
000337    }
000338    set ::update_hook
000339  } [list INSERT main t4 3 ]
000340  do_execsql_test hook-4.4.1 {
000341    SELECT * FROM t4 ORDER BY a;
000342  } {1 c 2 b}
000343  do_test hook-4.4.2 {
000344    set ::update_hook [list]
000345    execsql {
000346      PRAGMA recursive_triggers = on;
000347      REPLACE INTO t4 VALUES(1, 'd');
000348    }
000349    set ::update_hook
000350  } [list INSERT main t4 4 ]
000351  do_execsql_test hook-4.4.3 {
000352    SELECT * FROM t4 ORDER BY a;
000353  } {1 d 2 b}
000354  
000355  db update_hook {}
000356  #
000357  #----------------------------------------------------------------------------
000358  
000359  #----------------------------------------------------------------------------
000360  # Test the rollback-hook. The rollback-hook is a bit more complicated than
000361  # either the commit or update hooks because a rollback can happen 
000362  # explicitly (an sql ROLLBACK statement) or implicitly (a constraint or 
000363  # error condition).
000364  #
000365  # hook-5.1.* - Test explicit rollbacks.
000366  # hook-5.2.* - Test implicit rollbacks caused by constraint failure.
000367  #
000368  # hook-5.3.* - Test implicit rollbacks caused by IO errors.
000369  # hook-5.4.* - Test implicit rollbacks caused by malloc() failure.
000370  # hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook 
000371  #              not be called for these?
000372  #
000373  
000374  do_test hook-5.0 {
000375    # Configure the rollback hook to increment global variable 
000376    # $::rollback_hook each time it is invoked.
000377    set ::rollback_hook 0
000378    db rollback_hook [list incr ::rollback_hook]
000379  } {}
000380  
000381  # Test explicit rollbacks. Not much can really go wrong here.
000382  #
000383  do_test hook-5.1.1 {
000384    set ::rollback_hook 0
000385    execsql {
000386      BEGIN;
000387      ROLLBACK;
000388    }
000389    set ::rollback_hook
000390  } {1}
000391  
000392  # Test implicit rollbacks caused by constraints.
000393  #
000394  do_test hook-5.2.1 {
000395    set ::rollback_hook 0
000396    catchsql {
000397      DROP TABLE t1;
000398      CREATE TABLE t1(a PRIMARY KEY, b);
000399      INSERT INTO t1 VALUES('one', 'I');
000400      INSERT INTO t1 VALUES('one', 'I');
000401    }
000402    set ::rollback_hook
000403  } {1}
000404  do_test hook-5.2.2 {
000405    # Check that the INSERT transaction above really was rolled back.
000406    execsql {
000407      SELECT count(*) FROM t1;
000408    }
000409  } {1}
000410  
000411  #
000412  # End rollback-hook testing.
000413  #----------------------------------------------------------------------------
000414  
000415  #----------------------------------------------------------------------------
000416  # Test that if a commit-hook returns non-zero (causing a rollback), the
000417  # rollback-hook is invoked.
000418  #
000419  proc commit_hook {} {
000420    lappend ::hooks COMMIT
000421    return 1
000422  }
000423  proc rollback_hook {} {
000424    lappend ::hooks ROLLBACK
000425  }
000426  do_test hook-6.1 {
000427    set ::hooks [list]
000428    db commit_hook commit_hook
000429    db rollback_hook rollback_hook
000430    catchsql {
000431      BEGIN;
000432        INSERT INTO t1 VALUES('two', 'II');
000433      COMMIT;
000434    }
000435    execsql { SELECT * FROM t1 }
000436  } {one I}
000437  do_test hook-6.2 {
000438    set ::hooks
000439  } {COMMIT ROLLBACK}
000440  unset ::hooks
000441  
000442  #----------------------------------------------------------------------------
000443  # The following tests - hook-7.* - test the pre-update hook.
000444  #
000445  ifcapable !preupdate {
000446    finish_test
000447    return
000448  }
000449  #
000450  # 7.1.1 - INSERT statement.
000451  # 7.1.2 - INSERT INTO ... SELECT statement.
000452  # 7.1.3 - REPLACE INTO ... (rowid conflict)
000453  # 7.1.4 - REPLACE INTO ... (other index conflicts)
000454  # 7.1.5 - REPLACE INTO ... (both rowid and other index conflicts)
000455  #
000456  # 7.2.1 - DELETE statement.
000457  # 7.2.2 - DELETE statement that uses the truncate optimization.
000458  #
000459  # 7.3.1 - UPDATE statement.
000460  # 7.3.2 - UPDATE statement that modifies the rowid.
000461  # 7.3.3 - UPDATE OR REPLACE ... (rowid conflict).
000462  # 7.3.4 - UPDATE OR REPLACE ... (other index conflicts)
000463  # 7.3.4 - UPDATE OR REPLACE ... (both rowid and other index conflicts)
000464  #
000465  # 7.4.1 - Test that the pre-update-hook is invoked only once if a row being
000466  #         deleted is removed by a BEFORE trigger.
000467  #
000468  # 7.4.2 - Test that the pre-update-hook is invoked if a BEFORE trigger 
000469  #         removes a row being updated. In this case the update hook should
000470  #         be invoked with SQLITE_INSERT as the opcode when inserting the
000471  #         new version of the row.
000472  #         
000473  # TODO: Short records (those created before a column is added to a table 
000474  #       using ALTER TABLE)
000475  #
000476  
000477  proc do_preupdate_test {tn sql x} {
000478    set X [list]
000479    foreach elem $x {lappend X $elem}
000480    uplevel do_test $tn [list "
000481      set ::preupdate \[list\]
000482      execsql { $sql }
000483      set ::preupdate
000484    "] [list $X]
000485  }
000486  
000487  proc preupdate_hook {args} {
000488    set type [lindex $args 0]
000489    eval lappend ::preupdate $args
000490    if {$type != "INSERT"} {
000491      for {set i 0} {$i < [db preupdate count]} {incr i} {
000492        lappend ::preupdate [db preupdate old $i]
000493      }
000494    }
000495    if {$type != "DELETE"} {
000496      for {set i 0} {$i < [db preupdate count]} {incr i} {
000497        set rc [catch { db preupdate new $i } v]
000498        lappend ::preupdate $v
000499      }
000500    }
000501  }
000502  
000503  db close
000504  forcedelete test.db
000505  sqlite3 db test.db
000506  db preupdate hook preupdate_hook
000507  
000508  # Set up a schema to use for tests 7.1.* to 7.3.*.
000509  do_execsql_test 7.0 { 
000510    CREATE TABLE t1(a, b); 
000511    CREATE TABLE t2(x, y); 
000512    CREATE TABLE t3(i, j, UNIQUE(i));
000513  
000514    INSERT INTO t2 VALUES('a', 'b');
000515    INSERT INTO t2 VALUES('c', 'd');
000516  
000517    INSERT INTO t3 VALUES(4, 16);
000518    INSERT INTO t3 VALUES(5, 25);
000519    INSERT INTO t3 VALUES(6, 36);
000520  } 
000521  
000522  do_preupdate_test 7.1.1 {
000523    INSERT INTO t1 VALUES('x', 'y')
000524  } {INSERT main t1 1 1  x y}
000525  
000526  # 7.1.2.1 does not use the xfer optimization. 7.1.2.2 does.
000527  do_preupdate_test 7.1.2.1 {
000528    INSERT INTO t1 SELECT y, x FROM t2;
000529  } {INSERT main t1 2 2 b a   INSERT main t1 3 3 d c}
000530  do_preupdate_test 7.1.2.2 {
000531    INSERT INTO t1 SELECT * FROM t2;
000532  } {INSERT main t1 4 4 a b   INSERT main t1 5 5 c d}
000533  
000534  do_preupdate_test 7.1.3 {
000535    REPLACE INTO t1(rowid, a, b) VALUES(1, 1, 1);
000536  } {
000537    DELETE main t1 1 1   x y
000538    INSERT main t1 1 1   1 1
000539  }
000540  
000541  do_preupdate_test 7.1.4 {
000542    REPLACE INTO t3 VALUES(4, NULL);
000543  } {
000544    DELETE main t3 1 1   4 16
000545    INSERT main t3 4 4   4 {}
000546  }
000547  
000548  do_preupdate_test 7.1.5 {
000549    REPLACE INTO t3(rowid, i, j) VALUES(2, 6, NULL);
000550  } {
000551    DELETE main t3 2 2  5 25
000552    DELETE main t3 3 3  6 36
000553    INSERT main t3 2 2  6 {}
000554  }
000555  
000556  do_execsql_test 7.2.0 { SELECT rowid FROM t1 } {1 2 3 4 5}
000557  
000558  do_preupdate_test 7.2.1 {
000559    DELETE FROM t1 WHERE rowid = 3
000560  } {
000561    DELETE main t1 3 3  d c
000562  }
000563  do_preupdate_test 7.2.2 {
000564    DELETE FROM t1
000565  } {
000566    DELETE main t1 1 1   1 1
000567    DELETE main t1 2 2   b a
000568    DELETE main t1 4 4   a b
000569    DELETE main t1 5 5   c d
000570  }
000571  
000572  do_execsql_test 7.3.0 { 
000573    DELETE FROM t1;
000574    DELETE FROM t2;
000575    DELETE FROM t3;
000576  
000577    INSERT INTO t2 VALUES('a', 'b');
000578    INSERT INTO t2 VALUES('c', 'd');
000579  
000580    INSERT INTO t3 VALUES(4, 16);
000581    INSERT INTO t3 VALUES(5, 25);
000582    INSERT INTO t3 VALUES(6, 36);
000583  }
000584  
000585  do_preupdate_test 7.3.1 {
000586    UPDATE t2 SET y = y||y;
000587  } {
000588    UPDATE main t2 1 1   a b  a bb
000589    UPDATE main t2 2 2   c d  c dd
000590  }
000591  
000592  do_preupdate_test 7.3.2 {
000593    UPDATE t2 SET rowid = rowid-1;
000594  } {
000595    UPDATE main t2 1 0   a bb  a bb
000596    UPDATE main t2 2 1   c dd  c dd
000597  }
000598  
000599  do_preupdate_test 7.3.3 {
000600    UPDATE OR REPLACE t2 SET rowid = 1 WHERE x = 'a'
000601  } {
000602    DELETE main t2 1 1   c dd
000603    UPDATE main t2 0 1   a bb  a bb
000604  }
000605  
000606  do_preupdate_test 7.3.4.1 {
000607    UPDATE OR REPLACE t3 SET i = 5 WHERE i = 6
000608  } {
000609    DELETE main t3 2 2   5 25
000610    UPDATE main t3 3 3   6 36  5 36
000611  }
000612  
000613  do_execsql_test 7.3.4.2 {
000614    INSERT INTO t3 VALUES(10, 100);
000615    SELECT rowid, * FROM t3;
000616  } {1 4 16   3 5 36   4 10 100}
000617  
000618  do_preupdate_test 7.3.5 {
000619    UPDATE OR REPLACE t3 SET rowid = 1, i = 5 WHERE j = 100;
000620  } {
000621    DELETE main t3 1 1    4  16
000622    DELETE main t3 3 3    5  36
000623    UPDATE main t3 4 1   10 100  5 100
000624  }
000625  
000626  do_execsql_test 7.4.1.0 {
000627    CREATE TABLE t4(a, b);
000628    INSERT INTO t4 VALUES('a', 1);
000629    INSERT INTO t4 VALUES('b', 2);
000630    INSERT INTO t4 VALUES('c', 3);
000631  
000632    CREATE TRIGGER t4t BEFORE DELETE ON t4 BEGIN
000633      DELETE FROM t4 WHERE b = 1;
000634    END;
000635  }
000636  
000637  do_preupdate_test 7.4.1.1 {
000638    DELETE FROM t4 WHERE b = 3
000639  } {
000640    DELETE main t4 1 1   a 1
000641    DELETE main t4 3 3   c 3
000642  }
000643  
000644  do_execsql_test 7.4.1.2 {
000645    INSERT INTO t4(rowid, a, b) VALUES(1, 'a', 1);
000646    INSERT INTO t4(rowid, a, b) VALUES(3, 'c', 3);
000647  }
000648  do_preupdate_test 7.4.1.3 {
000649    DELETE FROM t4 WHERE b = 1
000650  } {
000651    DELETE main t4 1 1   a 1
000652  }
000653  
000654  do_execsql_test 7.4.2.0 {
000655    CREATE TABLE t5(a, b);
000656    INSERT INTO t5 VALUES('a', 1);
000657    INSERT INTO t5 VALUES('b', 2);
000658    INSERT INTO t5 VALUES('c', 3);
000659  
000660    CREATE TRIGGER t5t BEFORE UPDATE ON t5 BEGIN
000661      DELETE FROM t5 WHERE b = 1;
000662    END;
000663  }
000664  do_preupdate_test 7.4.2.1 {
000665    UPDATE t5 SET b = 4 WHERE a = 'c'
000666  } {
000667    DELETE main t5 1 1   a 1
000668    UPDATE main t5 3 3   c 3  c 4
000669  }
000670  
000671  do_execsql_test 7.4.2.2 {
000672    INSERT INTO t5(rowid, a, b) VALUES(1, 'a', 1);
000673  }
000674  
000675  do_preupdate_test 7.4.2.3 {
000676    UPDATE t5 SET b = 5 WHERE a = 'a'
000677  } {
000678    DELETE main t5 1 1   a 1
000679  }
000680  
000681  ifcapable altertable {
000682    do_execsql_test 7.5.1.0 {
000683      CREATE TABLE t7(a, b);
000684      INSERT INTO t7 VALUES('one', 'two');
000685      INSERT INTO t7 VALUES('three', 'four');
000686      ALTER TABLE t7 ADD COLUMN c DEFAULT NULL;
000687    }
000688    
000689    do_preupdate_test 7.5.1.1 {
000690      DELETE FROM t7 WHERE a = 'one'
000691    } {
000692      DELETE main t7 1 1   one two {}
000693    }
000694    
000695    do_preupdate_test 7.5.1.2 {
000696      UPDATE t7 SET b = 'five'
000697    } {
000698      UPDATE main t7 2 2   three four {}  three five {}
000699    }
000700    
000701    do_execsql_test 7.5.2.0 {
000702      CREATE TABLE t8(a, b);
000703      INSERT INTO t8 VALUES('one', 'two');
000704      INSERT INTO t8 VALUES('three', 'four');
000705      ALTER TABLE t8 ADD COLUMN c DEFAULT 'xxx';
000706    }
000707  }
000708  
000709  if 0 {
000710    # At time of writing, these two are broken. They demonstrate that the
000711    # sqlite3_preupdate_old() method does not handle the case where ALTER TABLE
000712    # has been used to add a column with a default value other than NULL.
000713    #
000714    do_preupdate_test 7.5.2.1 {
000715      DELETE FROM t8 WHERE a = 'one'
000716    } {
000717      DELETE main t8 1 1   one two xxx
000718    }
000719    do_preupdate_test 7.5.2.2 {
000720      UPDATE t8 SET b = 'five'
000721    } {
000722      UPDATE main t8 2 2   three four xxx  three five xxx
000723    }
000724  }
000725  
000726  # This block of tests verifies that IPK values are correctly reported
000727  # by the sqlite3_preupdate_old() and sqlite3_preupdate_new() functions.
000728  #
000729  do_execsql_test 7.6.1 { CREATE TABLE t9(a, b INTEGER PRIMARY KEY, c) }
000730  do_preupdate_test 7.6.2 {
000731    INSERT INTO t9 VALUES(1, 2, 3);
000732    UPDATE t9 SET b = b+1, c = c+1;
000733    DELETE FROM t9 WHERE a = 1;
000734  } {
000735    INSERT main t9 2 2   1 2 3
000736    UPDATE main t9 2 3   1 2 3   1 3 4
000737    DELETE main t9 3 3   1 3 4
000738  }
000739  
000740  #--------------------------------------------------------------------------
000741  # Test that the sqlite3_preupdate_depth() API seems to work.
000742  #
000743  proc preupdate_hook {args} {
000744    set type [lindex $args 0]
000745    eval lappend ::preupdate $args
000746    eval lappend ::preupdate [db preupdate depth]
000747  
000748    if {$type != "INSERT"} {
000749      for {set i 0} {$i < [db preupdate count]} {incr i} {
000750        lappend ::preupdate [db preupdate old $i]
000751      }
000752    }
000753    if {$type != "DELETE"} {
000754      for {set i 0} {$i < [db preupdate count]} {incr i} {
000755        set rc [catch { db preupdate new $i } v]
000756        lappend ::preupdate $v
000757      }
000758    }
000759  }
000760  
000761  db close
000762  forcedelete test.db
000763  sqlite3 db test.db
000764  db preupdate hook preupdate_hook
000765  
000766  do_execsql_test 7.6.1 { 
000767    CREATE TABLE t1(x PRIMARY KEY);
000768    CREATE TABLE t2(x PRIMARY KEY);
000769    CREATE TABLE t3(x PRIMARY KEY);
000770    CREATE TABLE t4(x PRIMARY KEY);
000771  
000772    CREATE TRIGGER a AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END;
000773    CREATE TRIGGER b AFTER INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END;
000774    CREATE TRIGGER c AFTER INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END;
000775  
000776    CREATE TRIGGER d AFTER UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END;
000777    CREATE TRIGGER e AFTER UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END;
000778    CREATE TRIGGER f AFTER UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END;
000779  
000780    CREATE TRIGGER g AFTER DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END;
000781    CREATE TRIGGER h AFTER DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END;
000782    CREATE TRIGGER i AFTER DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END;
000783  }
000784  
000785  do_preupdate_test 7.6.2 {
000786    INSERT INTO t1 VALUES('xyz');
000787  } {
000788    INSERT main t1 1 1   0      xyz
000789    INSERT main t2 1 1   1      xyz
000790    INSERT main t3 1 1   2      xyz
000791    INSERT main t4 1 1   3      xyz
000792  }
000793  do_preupdate_test 7.6.3 {
000794    UPDATE t1 SET x = 'abc';
000795  } {
000796    UPDATE main t1 1 1   0      xyz abc
000797    UPDATE main t2 1 1   1      xyz abc
000798    UPDATE main t3 1 1   2      xyz abc
000799    UPDATE main t4 1 1   3      xyz abc
000800  }
000801  do_preupdate_test 7.6.4 {
000802    DELETE FROM t1 WHERE 1;
000803  } {
000804    DELETE main t1 1 1   0      abc
000805    DELETE main t2 1 1   1      abc
000806    DELETE main t3 1 1   2      abc
000807    DELETE main t4 1 1   3      abc
000808  }
000809  
000810  do_execsql_test 7.6.5 { 
000811    DROP TRIGGER a; DROP TRIGGER b; DROP TRIGGER c;
000812    DROP TRIGGER d; DROP TRIGGER e; DROP TRIGGER f;
000813    DROP TRIGGER g; DROP TRIGGER h; DROP TRIGGER i;
000814  
000815    CREATE TRIGGER a BEFORE INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END;
000816    CREATE TRIGGER b BEFORE INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END;
000817    CREATE TRIGGER c BEFORE INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END;
000818  
000819    CREATE TRIGGER d BEFORE UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END;
000820    CREATE TRIGGER e BEFORE UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END;
000821    CREATE TRIGGER f BEFORE UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END;
000822  
000823    CREATE TRIGGER g BEFORE DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END;
000824    CREATE TRIGGER h BEFORE DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END;
000825    CREATE TRIGGER i BEFORE DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END;
000826  }
000827  
000828  do_preupdate_test 7.6.6 {
000829    INSERT INTO t1 VALUES('xyz');
000830  } {
000831    INSERT main t4 1 1   3      xyz
000832    INSERT main t3 1 1   2      xyz
000833    INSERT main t2 1 1   1      xyz
000834    INSERT main t1 1 1   0      xyz
000835  }
000836  do_preupdate_test 7.6.3 {
000837    UPDATE t1 SET x = 'abc';
000838  } {
000839    UPDATE main t4 1 1   3      xyz abc
000840    UPDATE main t3 1 1   2      xyz abc
000841    UPDATE main t2 1 1   1      xyz abc
000842    UPDATE main t1 1 1   0      xyz abc
000843  }
000844  do_preupdate_test 7.6.4 {
000845    DELETE FROM t1 WHERE 1;
000846  } {
000847    DELETE main t4 1 1   3      abc
000848    DELETE main t3 1 1   2      abc
000849    DELETE main t2 1 1   1      abc
000850    DELETE main t1 1 1   0      abc
000851  }
000852  
000853  # No preupdate callbacks for modifying sqlite_master.
000854  ifcapable altertable {
000855    do_preupdate_test 8.1 { CREATE TABLE x1(x, y); } { }
000856    do_preupdate_test 8.2 { ALTER TABLE x1 ADD COLUMN z } { }
000857    do_preupdate_test 8.3 { ALTER TABLE x1 RENAME TO y1 } { }
000858    do_preupdate_test 8.4 { CREATE INDEX y1x ON y1(x) } { }
000859    do_preupdate_test 8.5 { CREATE VIEW v1 AS SELECT * FROM y1 } { }
000860    do_preupdate_test 8.6 { DROP TABLE y1 } { }
000861  }
000862  
000863  #-------------------------------------------------------------------------
000864  reset_db
000865  db preupdate hook preupdate_hook
000866  
000867  ifcapable altertable {
000868    do_execsql_test 9.0 {
000869      CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
000870      CREATE TABLE t2(a, b INTEGER PRIMARY KEY);
000871    }
000872    do_preupdate_test 9.1 {
000873      INSERT INTO t1 VALUES(456, NULL, NULL);
000874    } {
000875      INSERT main t1 456 456  0  456 {} {}
000876    }
000877    do_execsql_test 9.2 {
000878      ALTER TABLE t1 ADD COLUMN d;
000879    }
000880    do_preupdate_test 9.3 {
000881      INSERT INTO t1(a, b, c) VALUES(457, NULL, NULL);
000882    } {
000883      INSERT main t1 457 457  0  457 {} {} {}
000884    }
000885    do_preupdate_test 9.4 {
000886      DELETE FROM t1 WHERE a=456
000887    } {
000888      DELETE main t1 456 456  0  456 {} {} {}
000889    }
000890    do_preupdate_test 9.5 {
000891      INSERT INTO t2 DEFAULT VALUES;
000892    } {
000893      INSERT main t2 1 1  0  {} 1
000894    } 
000895    do_preupdate_test 9.6 {
000896      INSERT INTO t1 DEFAULT VALUES;
000897    } {
000898      INSERT main t1 458 458  0  458 {} {} {}
000899    } 
000900  }
000901  
000902  
000903  do_execsql_test 10.0 {
000904    CREATE TABLE t3(a, b INTEGER PRIMARY KEY);
000905  }
000906  do_preupdate_test 10.1 {
000907    INSERT INTO t3 DEFAULT VALUES
000908  } {
000909    INSERT main t3 1 1 0 {} 1
000910  }
000911  do_execsql_test 10.2 { SELECT * FROM t3 } {{} 1}
000912  do_preupdate_test 10.3 {
000913    DELETE FROM t3 WHERE b=1
000914  } {DELETE main t3 1 1 0 {} 1}
000915  
000916  #-------------------------------------------------------------------------
000917  # Test that the "update" hook is not fired for operations on the 
000918  # sqlite_stat1 table performed by ANALYZE, even if a pre-update hook is
000919  # registered.
000920  ifcapable analyze {
000921    reset_db
000922    do_execsql_test 11.1 {
000923      CREATE TABLE t1(a, b);
000924      CREATE INDEX idx1 ON t1(a);
000925      CREATE INDEX idx2 ON t1(b);
000926  
000927      INSERT INTO t1 VALUES(1, 2);
000928      INSERT INTO t1 VALUES(3, 4);
000929      INSERT INTO t1 VALUES(5, 6);
000930      INSERT INTO t1 VALUES(7, 8);
000931    }
000932  
000933    db preupdate hook preupdate_cb
000934    db update_hook update_cb
000935  
000936    proc preupdate_cb {args} { lappend ::res "preupdate" $args }
000937    proc update_cb {args} { lappend ::res "update" $args }
000938  
000939    set ::res [list]
000940    do_test 11.2 {
000941      execsql ANALYZE
000942      set ::res
000943    } [list {*}{
000944      preupdate {INSERT main sqlite_stat1 1 1}
000945      preupdate {INSERT main sqlite_stat1 2 2}
000946    }]
000947  
000948    do_execsql_test 11.3 {
000949      INSERT INTO t1 VALUES(9, 10);
000950      INSERT INTO t1 VALUES(11, 12);
000951      INSERT INTO t1 VALUES(13, 14);
000952      INSERT INTO t1 VALUES(15, 16);
000953    }
000954  
000955    set ::res [list]
000956    do_test 11.4 {
000957      execsql ANALYZE
000958      set ::res
000959    } [list {*}{
000960      preupdate {DELETE main sqlite_stat1 1 1}
000961      preupdate {DELETE main sqlite_stat1 2 2}
000962      preupdate {INSERT main sqlite_stat1 1 1}
000963      preupdate {INSERT main sqlite_stat1 2 2}
000964    }]
000965  }
000966  
000967  #-------------------------------------------------------------------------
000968  # Test that the pre-update hook is fired for INSERT statements that use
000969  # the xfer optimization on without rowid tables.
000970  #
000971  reset_db
000972  do_execsql_test 12.1 {
000973    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
000974    CREATE TABLE t2(a INTEGER PRIMARY KEY, b) WITHOUT ROWID;
000975  
000976    INSERT INTO t1 VALUES(1, 2);
000977    INSERT INTO t1 VALUES(3, 4);
000978    INSERT INTO t2 VALUES(5, 6);
000979    INSERT INTO t2 VALUES(7, 8);
000980  
000981    CREATE TABLE t3 (a INTEGER PRIMARY KEY, b) WITHOUT ROWID;
000982  }
000983  
000984  db preupdate hook preupdate_cb
000985  db update_hook update_cb
000986  
000987  proc preupdate_cb {args} { lappend ::res "preupdate" $args }
000988  proc update_cb {args} { lappend ::res "update" $args }
000989  
000990  set ::res [list]
000991  do_test 12.2 {
000992    execsql VACUUM
000993    set ::res
000994  } {}
000995  
000996  do_test 12.3 {
000997    set ::res [list]
000998    execsql { INSERT INTO t3 SELECT a, b FROM t2 }
000999    set ::res
001000  } {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}}
001001  
001002  do_test 12.4 {
001003    execsql { DELETE FROM t3 }
001004    set ::res [list]
001005    execsql { INSERT INTO t3 SELECT * FROM t2 }
001006    set ::res
001007  } {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}}
001008  
001009  do_execsql_test 12.5 {
001010    CREATE TABLE t4(a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID;
001011    INSERT INTO t4 VALUES('abc', 1);
001012    INSERT INTO t4 VALUES('DEF', 2);
001013  }
001014  
001015  set ::res [list]
001016  do_test 12.6 {
001017    execsql VACUUM
001018    set ::res
001019  } {}
001020  
001021  do_catchsql_test 12.6 {
001022    INSERT INTO t4 VALUES('def', 3);
001023  } {1 {UNIQUE constraint failed: t4.a}}
001024  
001025  finish_test