AryaWu/sqlite

1# 2015 Sep 272#3# The author disclaims copyright to this source code.  In place of4# 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#12 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15source $testdir/fts3_common.tcl16set ::testprefix fts4onepass17 18# If SQLITE_ENABLE_FTS3 is defined, omit this file.19ifcapable !fts3 {20  finish_test21  return22}23 24do_execsql_test 1.0 {25  CREATE VIRTUAL TABLE ft USING fts3;26  INSERT INTO ft(rowid, content) VALUES(1, '1 2 3');27  INSERT INTO ft(rowid, content) VALUES(2, '4 5 6');28  INSERT INTO ft(rowid, content) VALUES(3, '7 8 9');29}30 31#-------------------------------------------------------------------------32# Check that UPDATE and DELETE statements that feature "WHERE rowid=?" or 33# or "WHERE docid=?" clauses do not use statement journals. But that other34# DELETE and UPDATE statements do.35#36# Note: "MATCH ? AND docid=?" does use a statement journal.37#38foreach {tn sql uses} {39  1.1 { DELETE FROM ft } 140  1.2 { DELETE FROM ft WHERE docid=? } 041  1.3 { DELETE FROM ft WHERE rowid=? } 042  1.4 { DELETE FROM ft WHERE ft MATCH '1' } 143  1.5 { DELETE FROM ft WHERE ft MATCH '1' AND docid=? } 144  1.6 { DELETE FROM ft WHERE ft MATCH '1' AND rowid=? } 145 46  2.1 { UPDATE ft SET content='a b c' } 147  2.2 { UPDATE ft SET content='a b c' WHERE docid=? } 048  2.3 { UPDATE ft SET content='a b c' WHERE rowid=? } 049  2.4 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' } 150  2.5 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' AND docid=? } 151  2.6 { UPDATE ft SET content='a b c' WHERE ft MATCH '1' AND rowid=? } 152} {53  do_test 1.$tn { sql_uses_stmt db $sql } $uses54}55 56#-------------------------------------------------------------------------57# Check that putting a "DELETE/UPDATE ... WHERE rowid=?" statement in a58# trigger program does not prevent the VM from using a statement 59# transaction. Even if the calling statement cannot hit a constraint.60#61do_execsql_test 2.0 {62  CREATE TABLE t1(x);63 64  CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN65    DELETE FROM ft WHERE rowid=new.x;66  END;67 68  CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN69    UPDATE ft SET content = 'a b c' WHERE rowid=old.x;70  END;71 72  CREATE TRIGGER t1_bu BEFORE UPDATE ON t1 BEGIN73    DELETE FROM ft WHERE rowid=old.x;74  END;75}76 77foreach {tn sql uses} {78  1 { INSERT INTO t1 VALUES(1)      } 179  2 { DELETE FROM t1 WHERE x=4      } 180  3 { UPDATE t1 SET x=10 WHERE x=11 } 181} {82  do_test 2.$tn { sql_uses_stmt db $sql } $uses83}84 85#-------------------------------------------------------------------------86# Test that an "UPDATE ... WHERE rowid=?" works and does not corrupt the87# index when it strikes a constraint. Both inside and outside a 88# transaction.89#90foreach {tn tcl1 tcl2}  {91  1 {} {}92 93  2 {94    execsql BEGIN95  } {96    if {[sqlite3_get_autocommit db]==1} { error "transaction rolled back!" }97    execsql COMMIT98  }99} {100 101  do_execsql_test 3.$tn.0 {102    DROP TABLE IF EXISTS ft2;103    CREATE VIRTUAL TABLE ft2 USING fts4;104    INSERT INTO ft2(rowid, content) VALUES(1, 'a b c');105    INSERT INTO ft2(rowid, content) VALUES(2, 'a b d');106    INSERT INTO ft2(rowid, content) VALUES(3, 'a b e');107  }108 109  eval $tcl1110  foreach {tn2 sql content} {111    1 { UPDATE ft2 SET docid=2 WHERE docid=1 }112      { 1 {a b c} 2 {a b d} 3 {a b e} }113 114    2 { 115      INSERT INTO ft2(rowid, content) VALUES(4, 'a b f');116      UPDATE ft2 SET docid=5 WHERE docid=4;117      UPDATE ft2 SET docid=3 WHERE docid=5;118    } { 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }119 120    3 {121      UPDATE ft2 SET docid=3 WHERE docid=4;           -- matches 0 rows122      UPDATE ft2 SET docid=2 WHERE docid=3;123    } { 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }124 125    4 {126      INSERT INTO ft2(rowid, content) VALUES(4, 'a b g');127      UPDATE ft2 SET docid=-1 WHERE docid=4;128      UPDATE ft2 SET docid=3 WHERE docid=-1;129    } {-1 {a b g} 1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }130 131    5 {132      DELETE FROM ft2 WHERE rowid=451;133      DELETE FROM ft2 WHERE rowid=-1;134      UPDATE ft2 SET docid = 2 WHERE docid = 1;135    } {1 {a b c} 2 {a b d} 3 {a b e} 5 {a b f} }136  } {137    do_catchsql_test 3.$tn.$tn2.a $sql {1 {constraint failed}}138    do_execsql_test  3.$tn.$tn2.b { SELECT rowid, content FROM ft2 } $content139    do_execsql_test  3.$tn.$tn2.c { 140      INSERT INTO ft2(ft2) VALUES('integrity-check');141    }142  }143  eval $tcl2144}145 146do_execsql_test 4.0 {147  CREATE VIRTUAL TABLE zt USING fts4(a, b);148  INSERT INTO zt(rowid, a, b) VALUES(1, 'unus duo', NULL);149  INSERT INTO zt(rowid, a, b) VALUES(2, NULL, NULL);150 151  BEGIN;152    UPDATE zt SET b='septum' WHERE rowid = 1;153    UPDATE zt SET b='octo' WHERE rowid = 1;154  COMMIT;155 156  SELECT count(*) FROM zt_segdir;157} {3}158 159 160finish_test161