AryaWu/sqlite

1# 2007 August 202#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.  This12# script tests for the fts2 rowid-versus-vacuum problem (ticket #2566).13#14# $Id: fts3b.test,v 1.3 2007/09/13 18:14:49 shess Exp $15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20# If SQLITE_ENABLE_FTS3 is not defined, omit this file.21ifcapable !fts3 {22  finish_test23  return24}25 26db eval {27  CREATE VIRTUAL TABLE t1 USING fts3(c);28  INSERT INTO t1 (c) VALUES('this is a test');29  INSERT INTO t1 (c) VALUES('that was a test');30  INSERT INTO t1 (c) VALUES('this is fun');31  DELETE FROM t1 WHERE c = 'that was a test';32}33 34# Baseline test.35do_test fts3b-1.1 {36  execsql {37    SELECT rowid FROM t1 WHERE c MATCH 'this';38  }39} {1 3}40 41db eval {VACUUM}42 43# The VACUUM renumbered the t1_content table in fts2, which breaks44# this.45do_test fts3b-1.2 {46  execsql {47    SELECT rowid FROM t1 WHERE c MATCH 'this';48  }49} {1 3}50 51# The t2 table is unfortunately pretty contrived.  We need documents52# that are bigger than ROOT_MAX (1024) to force segments out of the53# segdir and into %_segments.  We also need to force segment merging54# to generate a hole in the %_segments table, which needs more than 1655# docs.  Beyond that, to test correct operation of BLOCK_SELECT_STMT,56# we need to merge a mult-level tree, which is where the 10,000 comes57# from.  Which is slow, thus the set of transactions, with the 50058# being a number such that 10,000/500 > 16.59set text {60  Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Maecenas61  iaculis mollis ipsum. Praesent rhoncus placerat justo. Duis non quam62  sed turpis posuere placerat. Curabitur et lorem in lorem porttitor63  aliquet. Pellentesque bibendum tincidunt diam. Vestibulum blandit64  ante nec elit. In sapien diam, facilisis eget, dictum sed, viverra65  at, felis. Vestibulum magna. Sed magna dolor, vestibulum rhoncus,66  ornare vel, vulputate sit amet, felis. Integer malesuada, tellus at67  luctus gravida, diam nunc porta nibh, nec imperdiet massa metus eu68  lectus. Aliquam nisi. Nunc fringilla nulla at lectus. Suspendisse69  potenti. Cum sociis natoque penatibus et magnis dis parturient70  montes, nascetur ridiculus mus. Pellentesque odio nulla, feugiat eu,71  suscipit nec, consequat quis, risus.72}73append text $text74 75db eval {CREATE VIRTUAL TABLE t2 USING fts3(c)}76set res {}77db eval {BEGIN}78for {set ii 0} {$ii<10000} {incr ii} {79  db eval {INSERT INTO t2 (c) VALUES ($text)}80  lappend res [expr {$ii+1}]81  if {($ii%500)==0} {82    db eval {83      COMMIT;84      BEGIN;85    }86  }87}88db eval {COMMIT}89 90do_test fts3b-2.1 {91  execsql {92    SELECT rowid FROM t2 WHERE c MATCH 'lorem';93  }94} $res95 96db eval {VACUUM}97 98# The VACUUM renumbered the t2_segment table in fts2, which would99# break the following.100do_test fts3b-2.2 {101  execsql {102    SELECT rowid FROM t2 WHERE c MATCH 'lorem';103  }104} $res105 106# Since fts3 is already an API break, I've marked the table-named107# column HIDDEN.108 109db eval {110  CREATE VIRTUAL TABLE t3 USING fts3(c);111  INSERT INTO t3 (c) VALUES('this is a test');112  INSERT INTO t3 (c) VALUES('that was a test');113  INSERT INTO t3 (c) VALUES('this is fun');114  DELETE FROM t3 WHERE c = 'that was a test';115}116 117# Test that the table-named column still works.118do_test fts3b-3.1 {119  execsql {120    SELECT snippet(t3) FROM t3 WHERE t3 MATCH 'test';121  }122} {{this is a <b>test</b>}}123 124# Test that the column doesn't appear when selecting all columns.125do_test fts3b-3.2 {126  execsql {127    SELECT * FROM t3 WHERE rowid = 1;128  }129} {{this is a test}}130 131# Test that the column doesn't conflict with inserts that don't name132# columns.133do_test fts3b-3.3 {134  execsql {135    INSERT INTO t3 VALUES ('another test');136  }137} {}138 139# fts3 adds a new implicit column, docid, which acts as an alias for140# rowid.141 142db eval {143  CREATE VIRTUAL TABLE t4 USING fts3(c);144  INSERT INTO t4 (c) VALUES('this is a test');145  INSERT INTO t4 (c) VALUES('that was a test');146  INSERT INTO t4 (c) VALUES('this is fun');147  DELETE FROM t4 WHERE c = 'that was a test';148}149 150# Test that docid is present and identical to rowid.151do_test fts3b-4.1 {152  execsql {153    SELECT rowid FROM t4 WHERE rowid <> docid;154  }155} {}156 157# Test that docid is hidden.158do_test fts3b-4.2 {159  execsql {160    SELECT * FROM t4 WHERE rowid = 1;161  }162} {{this is a test}}163 164# Test that docid can be selected.165do_test fts3b-4.3 {166  execsql {167    SELECT docid, * FROM t4 WHERE rowid = 1;168  }169} {1 {this is a test}}170 171# Test that docid can be used in WHERE.172do_test fts3b-4.4 {173  execsql {174    SELECT docid, * FROM t4 WHERE docid = 1;175  }176} {1 {this is a test}}177 178# Test that the column doesn't conflict with inserts that don't name179# columns.  [Yes, this is the same as fts3b-3.3, here just in case the180# goals of that test change.]181do_test fts3b-4.5 {182  execsql {183    INSERT INTO t4 VALUES ('another test');184  }185} {}186 187# Test that the docid can be forced on insert.188do_test fts3b-4.6 {189  execsql {190    INSERT INTO t4 (docid, c) VALUES (10, 'yet another test');191    SELECT * FROM t4 WHERE docid = 10;192  }193} {{yet another test}}194 195# Test that rowid can also be forced.196do_test fts3b-4.7 {197  execsql {198    INSERT INTO t4 (docid, c) VALUES (12, 'still testing');199    SELECT * FROM t4 WHERE docid = 12;200  }201} {{still testing}}202 203# If an insert tries to set both docid and rowid, require an error.204do_test fts3b-4.8 {205  catchsql {206    INSERT INTO t4 (rowid, docid, c) VALUES (14, 15, 'bad test');207    SELECT * FROM t4 WHERE docid = 14;208  }209} {1 {SQL logic error}}210 211do_test fts3b-4.9 {212  execsql { SELECT docid FROM t4 WHERE t4 MATCH 'testing' }213} {12}214do_test fts3b-4.10 {215  execsql { 216    UPDATE t4 SET docid = 14 WHERE docid = 12;217    SELECT docid FROM t4 WHERE t4 MATCH 'testing';218  }219} {14}220do_test fts3b-4.11 {221  execsql { SELECT * FROM t4 WHERE rowid = 14; }222} {{still testing}}223do_test fts3b-4.12 {224  execsql { SELECT * FROM t4 WHERE rowid = 12; }225} {}226do_test fts3b-4.13 {227  execsql { SELECT docid FROM t4 WHERE t4 MATCH 'still'; }228} {14}229 230finish_test231