AryaWu/sqlite

1# 2012 March 262#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# This file implements regression tests for SQLite library. The12# focus of this script is testing the FTS 'integrity-check' function,13# used to check if the current FTS index accurately reflects the content14# of the table.15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19source $testdir/fts3_common.tcl20set ::testprefix fts4check21 22# If SQLITE_ENABLE_FTS3 is defined, omit this file.23ifcapable !fts3 {24  finish_test25  return26}27 28# Run the integrity-check on FTS table $tbl using database handle $db. If29# the integrity-check passes, return "ok". Otherwise, throw an exception.30#31proc fts_integrity {db tbl} {32  $db eval "INSERT INTO $tbl ($tbl) VALUES('integrity-check')"33  return "ok"34}35 36#-------------------------------------------------------------------------37# Test cases 1.*38#39#   1.0: Build a reasonably sized FTS table (5000 rows).40#41#   1.1: Run the integrity check code to check it passes.42#43#   1.2: Make a series of minor changes to the underlying FTS data structures44#        (e.g. delete or insert a row from the %_content table). Check that45#        this causes the integrity-check code to fail.46#47 48# Build an FTS table and check the integrity-check passes.49#50do_test 1.0 { fts3_build_db_1 5000 } {}51do_test 1.1 { fts_integrity db t1 } {ok}52 53# Mess around with the underlying tables. Check that this causes the54# integrity-check test to fail.55#56foreach {tn disruption} {57  1 {58    INSERT INTO t1_content(docid, c0x, c1y) VALUES(NULL, 'a', 'b');59  }60  2 {61    DELETE FROM t1_content WHERE docid = (SELECT max(docid) FROM t1_content);62  }63  3 {64    DELETE FROM t1_segdir WHERE level=0 AND idx=(65      SELECT max(idx) FROM t1_segdir WHERE level=066    );67  }68} {69  sqlite3_db_config db DEFENSIVE 070  do_execsql_test  1.2.1.$tn "BEGIN; $disruption"71  do_catchsql_test 1.2.2.$tn {72    INSERT INTO t1 (t1) VALUES('integrity-check')73  } {1 {database disk image is malformed}}74  do_execsql_test 1.2.3.$tn {75    PRAGMA integrity_check;76  } {{malformed inverted index for FTS4 table main.t1}}77  do_execsql_test  1.2.4.$tn "ROLLBACK"78}79 80do_test 1.3 { fts_integrity db t1 } {ok}81 82#-------------------------------------------------------------------------83# Test cases 2.*84#85#   2.0: Build a reasonably sized FTS table (20000 rows) that includes86#        prefix indexes.87#88#   2.1: Run the integrity check code to check it passes.89#90#   2.2: Make a series of minor changes to the underlying FTS data structures91#        (e.g. delete or insert a row from the %_content table). Check that92#        this causes the integrity-check code to fail.93#94 95do_test 2.0 { fts3_build_db_2 -extra {prefix="3,1"} 20000 } {}96do_test 2.1 { fts_integrity db t2 } {ok}97foreach {tn disruption} {98  1 {99    INSERT INTO t2_content VALUES(NULL, 'xyz')100  }101  3 {102    DELETE FROM t2_segdir WHERE level=0 AND idx=(103      SELECT max(idx) FROM t2_segdir WHERE level=1024104    );105  }106} {107  sqlite3_db_config db DEFENSIVE 0108  do_execsql_test  2.2.1.$tn "BEGIN; $disruption"109  do_catchsql_test 2.2.2.$tn {110    INSERT INTO t2 (t2) VALUES('integrity-check')111  } {1 {database disk image is malformed}}112  do_test 2.2.3.$tn {113    db eval {PRAGMA integrity_check(t2);}114  } {{malformed inverted index for FTS4 table main.t2}}115  do_execsql_test  2.2.4.$tn "ROLLBACK"116}117 118 119#-------------------------------------------------------------------------120# Test cases 3.*121#122#   3.0: Build a reasonably sized FTS table (5000 rows) that includes123#        prefix indexes and uses the languageid= feature.124#125#   3.1: Run the integrity check code to check it passes.126#127#   3.2: Make a series of minor changes to the underlying FTS data structures128#        (e.g. delete or insert a row from the %_content table). Check that129#        this causes the integrity-check code to fail.130#131do_test 3.0 {132  reset_db133  fts3_build_db_1 5000134  execsql {135    CREATE VIRTUAL TABLE t3 USING fts4(x, y, prefix="2,3", languageid=langid);136  }137  foreach docid [execsql {SELECT docid FROM t1 ORDER BY 1 ASC}] {138    execsql {139      INSERT INTO t3(x, y, langid) 140      SELECT x, y, (docid%9)*4 FROM t1 WHERE docid=$docid;141    }142  }143} {}144do_test 3.1 { fts_integrity db t3 } {ok}145 146foreach {tn disruption} {147  1 {148    INSERT INTO t3_content(c0x, c1y, langid) VALUES(NULL, 'a', 0);149  }150  2 {151    UPDATE t3_content SET langid=langid+1 WHERE rowid = (152      SELECT max(rowid) FROM t3_content153    )154  }155} {156  sqlite3_db_config db DEFENSIVE 0157  do_execsql_test  3.2.1.$tn "BEGIN; $disruption"158  do_catchsql_test 3.2.2.$tn {159    INSERT INTO t3 (t3) VALUES('integrity-check')160  } {1 {database disk image is malformed}}161  do_execsql_test  3.2.3.$tn "ROLLBACK"162}163 164#--------------------------------------------------------------------------165# Test case 4.*166#167# Test that the integrity-check works if there are "notindexed" columns.168#169do_execsql_test 4.0 {170  CREATE VIRTUAL TABLE t4 USING fts4(a, b, c, notindexed=b);171  INSERT INTO t4 VALUES('text one', 'text two', 'text three');172  INSERT INTO t4(t4) VALUES('integrity-check');173}174 175sqlite3_db_config db DEFENSIVE 0176do_execsql_test 4.1 {177  PRAGMA writable_schema = 1;178  UPDATE sqlite_master 179    SET sql = 'CREATE VIRTUAL TABLE t4 USING fts4(a, b, c)' 180    WHERE name = 't4';181}182 183do_test 4.2 {184  db close185  sqlite3 db test.db186  catchsql {187    INSERT INTO t4(t4) VALUES('integrity-check');188  }189} {1 {database disk image is malformed}}190reset_db191 192#--------------------------------------------------------------------------193# Test case 5.*194#195# Test that the integrity-check works if there is uncommitted data.196#197do_execsql_test 5.0 {198  BEGIN;199  CREATE VIRTUAL TABLE t5 USING fts4(a, prefix="1,2,3");200  INSERT INTO t5 VALUES('And down by Kosiosko, where the reed-banks sweep');201  INSERT INTO t5 VALUES('and sway, and the rolling plains are wide, the');202  INSERT INTO t5 VALUES('man from snowy river is a household name today,');203  INSERT INTO t5 VALUES('and the stockmen tell the story of his ride');204}205 206do_execsql_test 5.1 {207  INSERT INTO t5(t5) VALUES('integrity-check');208} {}209 210sqlite3_db_config db DEFENSIVE 0211do_catchsql_test 5.2 {212  INSERT INTO t5_content VALUES(5, 'his hardy mountain pony');213  INSERT INTO t5(t5) VALUES('integrity-check');214} {1 {database disk image is malformed}}215 216do_execsql_test 5.3 ROLLBACK217 218do_execsql_test 5.4 {219  CREATE VIRTUAL TABLE t5 USING fts4(a, prefix="1,2,3");220  INSERT INTO t5(t5) VALUES('integrity-check');221} {}222 223finish_test224