1 # 2007 August 20

     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.  This

    12 # 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 

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

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

    21 ifcapable !fts3 {

    22   finish_test

    23   return

    24 }

    25 

    26 db 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.

    35 do_test fts3b-1.1 {

    36   execsql {

    37     SELECT rowid FROM t1 WHERE c MATCH 'this';

    38   }

    39 } {1 3}

    40 

    41 db eval {VACUUM}

    42 

    43 # The VACUUM renumbered the t1_content table in fts2, which breaks

    44 # this.

    45 do_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 documents

    52 # that are bigger than ROOT_MAX (1024) to force segments out of the

    53 # segdir and into %_segments.  We also need to force segment merging

    54 # to generate a hole in the %_segments table, which needs more than 16

    55 # 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 comes

    57 # from.  Which is slow, thus the set of transactions, with the 500

    58 # being a number such that 10,000/500 > 16.

    59 set text {

    60   Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Maecenas

    61   iaculis mollis ipsum. Praesent rhoncus placerat justo. Duis non quam

    62   sed turpis posuere placerat. Curabitur et lorem in lorem porttitor

    63   aliquet. Pellentesque bibendum tincidunt diam. Vestibulum blandit

    64   ante nec elit. In sapien diam, facilisis eget, dictum sed, viverra

    65   at, felis. Vestibulum magna. Sed magna dolor, vestibulum rhoncus,

    66   ornare vel, vulputate sit amet, felis. Integer malesuada, tellus at

    67   luctus gravida, diam nunc porta nibh, nec imperdiet massa metus eu

    68   lectus. Aliquam nisi. Nunc fringilla nulla at lectus. Suspendisse

    69   potenti. Cum sociis natoque penatibus et magnis dis parturient

    70   montes, nascetur ridiculus mus. Pellentesque odio nulla, feugiat eu,

    71   suscipit nec, consequat quis, risus.

    72 }

    73 append text $text

    74 

    75 db eval {CREATE VIRTUAL TABLE t2 USING fts3(c)}

    76 set res {}

    77 db eval {BEGIN}

    78 for {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 }

    88 db eval {COMMIT}

    89 

    90 do_test fts3b-2.1 {

    91   execsql {

    92     SELECT rowid FROM t2 WHERE c MATCH 'lorem';

    93   }

    94 } $res

    95 

    96 db eval {VACUUM}

    97 

    98 # The VACUUM renumbered the t2_segment table in fts2, which would

    99 # break the following.

   100 do_test fts3b-2.2 {

   101   execsql {

   102     SELECT rowid FROM t2 WHERE c MATCH 'lorem';

   103   }

   104 } $res

   105 

   106 # Since fts3 is already an API break, I've marked the table-named

   107 # column HIDDEN.

   108 

   109 db 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.

   118 do_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.

   125 do_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 name

   132 # columns.

   133 do_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 for

   140 # rowid.

   141 

   142 db 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.

   151 do_test fts3b-4.1 {

   152   execsql {

   153     SELECT rowid FROM t4 WHERE rowid <> docid;

   154   }

   155 } {}

   156 

   157 # Test that docid is hidden.

   158 do_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.

   165 do_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.

   172 do_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 name

   179 # columns.  [Yes, this is the same as fts3b-3.3, here just in case the

   180 # goals of that test change.]

   181 do_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.

   188 do_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.

   196 do_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.

   204 do_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 or missing database}}

   210 

   211 # Don't allow update of docid, to match rowid behaviour.

   212 do_test fts3b-4.9 {

   213   catchsql {

   214     UPDATE t4 SET docid = 14 WHERE docid = 12;

   215   }

   216 } {1 {SQL logic error or missing database}}

   217 

   218 finish_test