Symaptic: os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/fts2q.test@bde4ae8d615e

     1 # 2008 June 26

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.  The focus

    12 # of this script is testing the FTS2 module's optimize() function.

    13 #

    14 # $Id: fts2q.test,v 1.2 2008/07/22 23:49:44 shess Exp $

    15 #

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    20 # If SQLITE_ENABLE_FTS2 is not defined, omit this file.

    21 ifcapable !fts2 {

    22   finish_test

    23   return

    24 }

    26 #*************************************************************************

    27 # Probe to see if support for the FTS2 dump_* functions is compiled in.

    28 # TODO(shess): Change main.mk to do the right thing and remove this test.

    29 db eval {

    30   DROP TABLE IF EXISTS t1;

    31   CREATE VIRTUAL TABLE t1 USING fts2(c);

    32   INSERT INTO t1 (rowid, c) VALUES (1, 'x');

    33 }

    35 set s {SELECT dump_terms(t1, 1) FROM t1 LIMIT 1}

    36 set r {1 {unable to use function dump_terms in the requested context}}

    37 if {[catchsql $s]==$r} {

    38   finish_test

    39   return

    40 }

    42 #*************************************************************************

    43 # Utility function to check for the expected terms in the segment

    44 # level/index.  _all version does same but for entire index.

    45 proc check_terms {test level index terms} {

    46   # TODO(shess): Figure out why uplevel in do_test can't catch

    47   # $level and $index directly.

    48   set ::level $level

    49   set ::index $index

    50   do_test $test.terms {

    51     execsql {

    52       SELECT dump_terms(t1, $::level, $::index) FROM t1 LIMIT 1;

    53     }

    54   } [list $terms]

    55 }

    56 proc check_terms_all {test terms} {

    57   do_test $test.terms {

    58     execsql {

    59       SELECT dump_terms(t1) FROM t1 LIMIT 1;

    60     }

    61   } [list $terms]

    62 }

    64 # Utility function to check for the expected doclist for the term in

    65 # segment level/index.  _all version does same for entire index.

    66 proc check_doclist {test level index term doclist} {

    67   # TODO(shess): Again, why can't the non-:: versions work?

    68   set ::term $term

    69   set ::level $level

    70   set ::index $index

    71   do_test $test {

    72     execsql {

    73       SELECT dump_doclist(t1, $::term, $::level, $::index) FROM t1 LIMIT 1;

    74     }

    75   } [list $doclist]

    76 }

    77 proc check_doclist_all {test term doclist} {

    78   set ::term $term

    79   do_test $test {

    80     execsql {

    81       SELECT dump_doclist(t1, $::term) FROM t1 LIMIT 1;

    82     }

    83   } [list $doclist]

    84 }

    86 #*************************************************************************

    87 # Test results when all rows are deleted and one is added back.

    88 # Previously older segments would continue to exist, but now the index

    89 # should be dropped when the table is empty.  The results should look

    90 # exactly like we never added the earlier rows in the first place.

    91 db eval {

    92   DROP TABLE IF EXISTS t1;

    93   CREATE VIRTUAL TABLE t1 USING fts2(c);

    94   INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test');

    95   INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test');

    96   INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test');

    97   DELETE FROM t1 WHERE 1=1; -- Delete each row rather than dropping table.

    98   INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test');

    99 }

   101 # Should be a single initial segment.

   102 do_test fts2q-1.segments {

   103   execsql {

   104     SELECT level, idx FROM t1_segdir ORDER BY level, idx;

   105   }

   106 } {0 0}

   107 do_test fts2q-1.matches {

   108   execsql {

   109     SELECT OFFSETS(t1) FROM t1

   110      WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY rowid;

   111   }

   112 } {{0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}}

   114 check_terms_all fts2q-1.1 {a is test this}

   115 check_doclist_all fts2q-1.1.1 a {[1 0[2]]}

   116 check_doclist_all fts2q-1.1.2 is {[1 0[1]]}

   117 check_doclist_all fts2q-1.1.3 test {[1 0[3]]}

   118 check_doclist_all fts2q-1.1.4 this {[1 0[0]]}

   120 check_terms   fts2q-1.2   0 0 {a is test this}

   121 check_doclist fts2q-1.2.1 0 0 a {[1 0[2]]}

   122 check_doclist fts2q-1.2.2 0 0 is {[1 0[1]]}

   123 check_doclist fts2q-1.2.3 0 0 test {[1 0[3]]}

   124 check_doclist fts2q-1.2.4 0 0 this {[1 0[0]]}

   126 #*************************************************************************

   127 # Test results when everything is optimized manually.

   128 # NOTE(shess): This is a copy of fts2c-1.3.  I've pulled a copy here

   129 # because fts2q-2 and fts2q-3 should have identical results.

   130 db eval {

   131   DROP TABLE IF EXISTS t1;

   132   CREATE VIRTUAL TABLE t1 USING fts2(c);

   133   INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test');

   134   INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test');

   135   INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test');

   136   DELETE FROM t1 WHERE rowid IN (1,3);

   137   DROP TABLE IF EXISTS t1old;

   138   ALTER TABLE t1 RENAME TO t1old;

   139   CREATE VIRTUAL TABLE t1 USING fts2(c);

   140   INSERT INTO t1 (rowid, c) SELECT rowid, c FROM t1old;

   141   DROP TABLE t1old;

   142 }

   144 # Should be a single optimal segment with the same logical results.

   145 do_test fts2q-2.segments {

   146   execsql {

   147     SELECT level, idx FROM t1_segdir ORDER BY level, idx;

   148   }

   149 } {0 0}

   150 do_test fts2q-2.matches {

   151   execsql {

   152     SELECT OFFSETS(t1) FROM t1

   153      WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY rowid;

   154   }

   155 } {{0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4}}

   157 check_terms_all fts2q-2.1 {a test that was}

   158 check_doclist_all fts2q-2.1.1 a {[2 0[2]]}

   159 check_doclist_all fts2q-2.1.2 test {[2 0[3]]}

   160 check_doclist_all fts2q-2.1.3 that {[2 0[0]]}

   161 check_doclist_all fts2q-2.1.4 was {[2 0[1]]}

   163 check_terms fts2q-2.2 0 0 {a test that was}

   164 check_doclist fts2q-2.2.1 0 0 a {[2 0[2]]}

   165 check_doclist fts2q-2.2.2 0 0 test {[2 0[3]]}

   166 check_doclist fts2q-2.2.3 0 0 that {[2 0[0]]}

   167 check_doclist fts2q-2.2.4 0 0 was {[2 0[1]]}

   169 #*************************************************************************

   170 # Test results when everything is optimized via optimize().

   171 db eval {

   172   DROP TABLE IF EXISTS t1;

   173   CREATE VIRTUAL TABLE t1 USING fts2(c);

   174   INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test');

   175   INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test');

   176   INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test');

   177   DELETE FROM t1 WHERE rowid IN (1,3);

   178   SELECT OPTIMIZE(t1) FROM t1 LIMIT 1;

   179 }

   181 # Should be a single optimal segment with the same logical results.

   182 do_test fts2q-3.segments {

   183   execsql {

   184     SELECT level, idx FROM t1_segdir ORDER BY level, idx;

   185   }

   186 } {0 0}

   187 do_test fts2q-3.matches {

   188   execsql {

   189     SELECT OFFSETS(t1) FROM t1

   190      WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY rowid;

   191   }

   192 } {{0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4}}

   194 check_terms_all fts2q-3.1 {a test that was}

   195 check_doclist_all fts2q-3.1.1 a {[2 0[2]]}

   196 check_doclist_all fts2q-3.1.2 test {[2 0[3]]}

   197 check_doclist_all fts2q-3.1.3 that {[2 0[0]]}

   198 check_doclist_all fts2q-3.1.4 was {[2 0[1]]}

   200 check_terms fts2q-3.2 0 0 {a test that was}

   201 check_doclist fts2q-3.2.1 0 0 a {[2 0[2]]}

   202 check_doclist fts2q-3.2.2 0 0 test {[2 0[3]]}

   203 check_doclist fts2q-3.2.3 0 0 that {[2 0[0]]}

   204 check_doclist fts2q-3.2.4 0 0 was {[2 0[1]]}

   206 #*************************************************************************

   207 # Test optimize() against a table involving segment merges.

   208 # NOTE(shess): Since there's no transaction, each of the INSERT/UPDATE

   209 # statements generates a segment.

   210 db eval {

   211   DROP TABLE IF EXISTS t1;

   212   CREATE VIRTUAL TABLE t1 USING fts2(c);

   214   INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test');

   215   INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test');

   216   INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test');

   218   UPDATE t1 SET c = 'This is a test one' WHERE rowid = 1;

   219   UPDATE t1 SET c = 'That was a test one' WHERE rowid = 2;

   220   UPDATE t1 SET c = 'This is a test one' WHERE rowid = 3;

   222   UPDATE t1 SET c = 'This is a test two' WHERE rowid = 1;

   223   UPDATE t1 SET c = 'That was a test two' WHERE rowid = 2;

   224   UPDATE t1 SET c = 'This is a test two' WHERE rowid = 3;

   226   UPDATE t1 SET c = 'This is a test three' WHERE rowid = 1;

   227   UPDATE t1 SET c = 'That was a test three' WHERE rowid = 2;

   228   UPDATE t1 SET c = 'This is a test three' WHERE rowid = 3;

   230   UPDATE t1 SET c = 'This is a test four' WHERE rowid = 1;

   231   UPDATE t1 SET c = 'That was a test four' WHERE rowid = 2;

   232   UPDATE t1 SET c = 'This is a test four' WHERE rowid = 3;

   234   UPDATE t1 SET c = 'This is a test' WHERE rowid = 1;

   235   UPDATE t1 SET c = 'That was a test' WHERE rowid = 2;

   236   UPDATE t1 SET c = 'This is a test' WHERE rowid = 3;

   237 }

   239 # 2 segments in level 0, 1 in level 1 (18 segments created, 16

   240 # merged).

   241 do_test fts2q-4.segments {

   242   execsql {

   243     SELECT level, idx FROM t1_segdir ORDER BY level, idx;

   244   }

   245 } {0 0 0 1 1 0}

   247 do_test fts2q-4.matches {

   248   execsql {

   249     SELECT OFFSETS(t1) FROM t1

   250      WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY rowid;

   251   }

   252 } [list {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4} \

   253         {0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4} \

   254         {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}]

   256 check_terms_all fts2q-4.1      {a four is one test that this three two was}

   257 check_doclist_all fts2q-4.1.1  a {[1 0[2]] [2 0[2]] [3 0[2]]}

   258 check_doclist_all fts2q-4.1.2  four {}

   259 check_doclist_all fts2q-4.1.3  is {[1 0[1]] [3 0[1]]}

   260 check_doclist_all fts2q-4.1.4  one {}

   261 check_doclist_all fts2q-4.1.5  test {[1 0[3]] [2 0[3]] [3 0[3]]}

   262 check_doclist_all fts2q-4.1.6  that {[2 0[0]]}

   263 check_doclist_all fts2q-4.1.7  this {[1 0[0]] [3 0[0]]}

   264 check_doclist_all fts2q-4.1.8  three {}

   265 check_doclist_all fts2q-4.1.9  two {}

   266 check_doclist_all fts2q-4.1.10 was {[2 0[1]]}

   268 check_terms fts2q-4.2     0 0 {a four test that was}

   269 check_doclist fts2q-4.2.1 0 0 a {[2 0[2]]}

   270 check_doclist fts2q-4.2.2 0 0 four {[2]}

   271 check_doclist fts2q-4.2.3 0 0 test {[2 0[3]]}

   272 check_doclist fts2q-4.2.4 0 0 that {[2 0[0]]}

   273 check_doclist fts2q-4.2.5 0 0 was {[2 0[1]]}

   275 check_terms fts2q-4.3     0 1 {a four is test this}

   276 check_doclist fts2q-4.3.1 0 1 a {[3 0[2]]}

   277 check_doclist fts2q-4.3.2 0 1 four {[3]}

   278 check_doclist fts2q-4.3.3 0 1 is {[3 0[1]]}

   279 check_doclist fts2q-4.3.4 0 1 test {[3 0[3]]}

   280 check_doclist fts2q-4.3.5 0 1 this {[3 0[0]]}

   282 check_terms fts2q-4.4      1 0 {a four is one test that this three two was}

   283 check_doclist fts2q-4.4.1  1 0 a {[1 0[2]] [2 0[2]] [3 0[2]]}

   284 check_doclist fts2q-4.4.2  1 0 four {[1] [2 0[4]] [3 0[4]]}

   285 check_doclist fts2q-4.4.3  1 0 is {[1 0[1]] [3 0[1]]}

   286 check_doclist fts2q-4.4.4  1 0 one {[1] [2] [3]}

   287 check_doclist fts2q-4.4.5  1 0 test {[1 0[3]] [2 0[3]] [3 0[3]]}

   288 check_doclist fts2q-4.4.6  1 0 that {[2 0[0]]}

   289 check_doclist fts2q-4.4.7  1 0 this {[1 0[0]] [3 0[0]]}

   290 check_doclist fts2q-4.4.8  1 0 three {[1] [2] [3]}

   291 check_doclist fts2q-4.4.9  1 0 two {[1] [2] [3]}

   292 check_doclist fts2q-4.4.10 1 0 was {[2 0[1]]}

   294 # Optimize should leave the result in the level of the highest-level

   295 # prior segment.

   296 do_test fts2q-4.5 {

   297   execsql {

   298     SELECT OPTIMIZE(t1) FROM t1 LIMIT 1;

   299     SELECT level, idx FROM t1_segdir ORDER BY level, idx;

   300   }

   301 } {{Index optimized} 1 0}

   303 # Identical to fts2q-4.matches.

   304 do_test fts2q-4.5.matches {

   305   execsql {

   306     SELECT OFFSETS(t1) FROM t1

   307      WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY rowid;

   308   }

   309 } [list {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4} \

   310         {0 1 0 4 0 2 5 3 0 3 9 1 0 5 11 4} \

   311         {0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}]

   313 check_terms_all fts2q-4.5.1     {a is test that this was}

   314 check_doclist_all fts2q-4.5.1.1 a {[1 0[2]] [2 0[2]] [3 0[2]]}

   315 check_doclist_all fts2q-4.5.1.2 is {[1 0[1]] [3 0[1]]}

   316 check_doclist_all fts2q-4.5.1.3 test {[1 0[3]] [2 0[3]] [3 0[3]]}

   317 check_doclist_all fts2q-4.5.1.4 that {[2 0[0]]}

   318 check_doclist_all fts2q-4.5.1.5 this {[1 0[0]] [3 0[0]]}

   319 check_doclist_all fts2q-4.5.1.6 was {[2 0[1]]}

   321 check_terms fts2q-4.5.2     1 0 {a is test that this was}

   322 check_doclist fts2q-4.5.2.1 1 0 a {[1 0[2]] [2 0[2]] [3 0[2]]}

   323 check_doclist fts2q-4.5.2.2 1 0 is {[1 0[1]] [3 0[1]]}

   324 check_doclist fts2q-4.5.2.3 1 0 test {[1 0[3]] [2 0[3]] [3 0[3]]}

   325 check_doclist fts2q-4.5.2.4 1 0 that {[2 0[0]]}

   326 check_doclist fts2q-4.5.2.5 1 0 this {[1 0[0]] [3 0[0]]}

   327 check_doclist fts2q-4.5.2.6 1 0 was {[2 0[1]]}

   329 # Re-optimizing does nothing.

   330 do_test fts2q-5.0 {

   331   execsql {

   332     SELECT OPTIMIZE(t1) FROM t1 LIMIT 1;

   333     SELECT level, idx FROM t1_segdir ORDER BY level, idx;

   334   }

   335 } {{Index already optimal} 1 0}

   337 # Even if we move things around, still does nothing.

   338 do_test fts2q-5.1 {

   339   execsql {

   340     UPDATE t1_segdir SET level = 2 WHERE level = 1 AND idx = 0;

   341     SELECT OPTIMIZE(t1) FROM t1 LIMIT 1;

   342     SELECT level, idx FROM t1_segdir ORDER BY level, idx;

   343   }

   344 } {{Index already optimal} 2 0}

   346 finish_test

author	sl@SLION-WIN7.fritz.box
	Fri, 15 Jun 2012 03:10:57 +0200
changeset 0	bde4ae8d615e
permissions	-rw-r--r--