/trunk/src/sqlite/test/fts3ao.test

  1# 2007 June 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.  The
 12# focus of this script is testing the FTS3 module.
 13#
 14# $Id: fts3ao.test,v 1.1 2007/08/20 17:38:42 shess Exp $
 15#
 16
 17set testdir [file dirname $argv0]
 18source $testdir/tester.tcl
 19
 20# If SQLITE_ENABLE_FTS3 is not defined, omit this file.
 21ifcapable !fts3 {
 22  finish_test
 23  return
 24}
 25
 26set ::testprefix fts3ao
 27
 28#---------------------------------------------------------------------
 29# These tests, fts3ao-1.*, test that ticket #2429 is fixed.
 30#
 31db eval {
 32  CREATE VIRTUAL TABLE t1 USING fts3(a, b, c);
 33  INSERT INTO t1(a, b, c) VALUES('one three four', 'one four', 'one four two');
 34}
 35do_test fts3ao-1.1 {
 36  execsql {
 37    SELECT rowid, snippet(t1) FROM t1 WHERE c MATCH 'four';
 38  }
 39} {1 {one <b>four</b> two}}
 40do_test fts3ao-1.2 {
 41  execsql {
 42    SELECT rowid, snippet(t1) FROM t1 WHERE b MATCH 'four';
 43  }
 44} {1 {one <b>four</b>}}
 45do_test fts3ao-1.3 {
 46  execsql {
 47    SELECT rowid, snippet(t1) FROM t1 WHERE a MATCH 'four';
 48  }
 49} {1 {one three <b>four</b>}}
 50
 51#---------------------------------------------------------------------
 52# Test that it is possible to rename an fts3 table.
 53#
 54do_test fts3ao-2.1 {
 55  execsql { SELECT tbl_name FROM sqlite_master WHERE type = 'table'}
 56} {t1 t1_content t1_segments t1_segdir}
 57do_test fts3ao-2.2 {
 58  execsql { ALTER TABLE t1 RENAME to fts_t1; }
 59} {}
 60do_test fts3ao-2.3 {
 61  execsql { SELECT rowid, snippet(fts_t1) FROM fts_t1 WHERE a MATCH 'four'; }
 62} {1 {one three <b>four</b>}}
 63do_test fts3ao-2.4 {
 64  execsql { SELECT tbl_name FROM sqlite_master WHERE type = 'table'}
 65} {fts_t1 fts_t1_content fts_t1_segments fts_t1_segdir}
 66
 67# See what happens when renaming the fts3 table fails.
 68#
 69do_test fts3ao-2.5 {
 70  catchsql {
 71    CREATE TABLE t1_segdir(a, b, c);
 72    ALTER TABLE fts_t1 RENAME to t1;
 73  }
 74} {1 {SQL logic error or missing database}}
 75do_test fts3ao-2.6 {
 76  execsql { SELECT rowid, snippet(fts_t1) FROM fts_t1 WHERE a MATCH 'four'; }
 77} {1 {one three <b>four</b>}}
 78do_test fts3ao-2.7 {
 79  execsql { SELECT tbl_name FROM sqlite_master WHERE type = 'table'}
 80} {fts_t1 fts_t1_content fts_t1_segments fts_t1_segdir t1_segdir}
 81
 82# See what happens when renaming the fts3 table fails inside a transaction.
 83#
 84do_test fts3ao-2.8 {
 85  execsql {
 86    BEGIN;
 87    INSERT INTO fts_t1(a, b, c) VALUES('one two three', 'one four', 'one two');
 88  }
 89} {}
 90do_test fts3ao-2.9 {
 91  catchsql {
 92    ALTER TABLE fts_t1 RENAME to t1;
 93  }
 94} {1 {SQL logic error or missing database}}
 95do_test fts3ao-2.10 {
 96  execsql { SELECT rowid, snippet(fts_t1) FROM fts_t1 WHERE a MATCH 'four'; }
 97} {1 {one three <b>four</b>}}
 98do_test fts3ao-2.11 {
 99  execsql { SELECT tbl_name FROM sqlite_master WHERE type = 'table'}
100} {fts_t1 fts_t1_content fts_t1_segments fts_t1_segdir t1_segdir}
101do_test fts3ao-2.12 {
102  execsql COMMIT
103  execsql {SELECT a FROM fts_t1}
104} {{one three four} {one two three}}
105do_test fts3ao-2.12 {
106  execsql { SELECT a, b, c FROM fts_t1 WHERE c MATCH 'four'; }
107} {{one three four} {one four} {one four two}}
108
109#-------------------------------------------------------------------
110# Close, delete and reopen the database. The following test should 
111# be run on an initially empty db.
112#
113db close
114file delete -force test.db test.db-journal
115sqlite3 db test.db
116
117do_test fts3ao-3.1 {
118  execsql {
119    CREATE VIRTUAL TABLE t1 USING fts3(a, b, c);
120    INSERT INTO t1(a, b, c) VALUES('one three four', 'one four', 'one two');
121    SELECT a, b, c FROM t1 WHERE c MATCH 'two';
122  }
123} {{one three four} {one four} {one two}}
124
125# This test was crashing at one point.
126#
127do_test fts3ao-3.2 {
128  execsql {
129    SELECT a, b, c FROM t1 WHERE c MATCH 'two';
130    CREATE TABLE t3(a, b, c);
131    SELECT a, b, c FROM t1 WHERE  c  MATCH 'two';
132  }
133} {{one three four} {one four} {one two} {one three four} {one four} {one two}}
134
135#---------------------------------------------------------------------
136# Test that it is possible to rename an fts3 table in an attached 
137# database.
138#
139file delete -force test2.db test2.db-journal
140
141do_test fts3ao-3.1 {
142  execsql {
143    ATTACH 'test2.db' AS aux;
144    CREATE VIRTUAL TABLE aux.t1 USING fts3(a, b, c);
145    INSERT INTO aux.t1(a, b, c) VALUES(
146      'neung song sahm', 'neung see', 'neung see song'
147    );
148  }
149} {}
150
151do_test fts3ao-3.2 {
152  execsql { SELECT a, b, c FROM aux.t1 WHERE a MATCH 'song'; }
153} {{neung song sahm} {neung see} {neung see song}}
154
155do_test fts3ao-3.3 {
156  execsql { SELECT a, b, c FROM t1 WHERE c MATCH 'two'; }
157} {{one three four} {one four} {one two}}
158
159do_test fts3ao-3.4 {
160  execsql { ALTER TABLE aux.t1 RENAME TO t2 }
161} {}
162
163do_test fts3ao-3.2 {
164  execsql { SELECT a, b, c FROM t2 WHERE a MATCH 'song'; }
165} {{neung song sahm} {neung see} {neung see song}}
166
167do_test fts3ao-3.3 {
168  execsql { SELECT a, b, c FROM t1 WHERE c MATCH 'two'; }
169} {{one three four} {one four} {one two}}
170
171#---------------------------------------------------------------------
172# Test that it is possible to rename an fts3 table within a 
173# transaction.
174#
175do_test fts3ao-4.1 {
176  execsql {
177    CREATE VIRTUAL TABLE t4 USING fts3;
178    INSERT INTO t4 VALUES('the quick brown fox');
179  }
180} {}
181do_test fts3ao-4.2 {
182  execsql {
183    BEGIN;
184      INSERT INTO t4 VALUES('jumped over the');
185  }
186} {}
187do_test fts3ao-4.3 { execsql { ALTER TABLE t4 RENAME TO t5; } } {}
188do_test fts3ao-4.4 { execsql { INSERT INTO t5 VALUES('lazy dog'); } } {}
189do_test fts3ao-4.5 { execsql COMMIT } {}
190do_test fts3ao-4.6 {
191  execsql { SELECT * FROM t5 }
192} {{the quick brown fox} {jumped over the} {lazy dog}}
193do_test fts3ao-4.7 {
194  execsql {
195    BEGIN;
196      INSERT INTO t5 VALUES('Down came a jumbuck to drink at that billabong');
197      ALTER TABLE t5 RENAME TO t6;
198      INSERT INTO t6 VALUES('Down came the troopers, one, two, three');
199    ROLLBACK;
200    SELECT * FROM t5;
201  }
202} {{the quick brown fox} {jumped over the} {lazy dog}}
203
204# Test that it is possible to rename an FTS4 table. Renaming an FTS4 table
205# involves renaming the extra %_docsize and %_stat tables.
206#
207do_execsql_test 5.1 {
208  CREATE VIRTUAL TABLE t7 USING FTS4;
209  INSERT INTO t7 VALUES('coined by a German clinician');
210  SELECT count(*) FROM sqlite_master WHERE name LIKE 't7%';
211  SELECT count(*) FROM sqlite_master WHERE name LIKE 't8%';
212} {6 0}
213do_execsql_test 5.2 {
214  ALTER TABLE t7 RENAME TO t8;
215  SELECT count(*) FROM sqlite_master WHERE name LIKE 't7%';
216  SELECT count(*) FROM sqlite_master WHERE name LIKE 't8%';
217} {0 6}
218
219finish_test
220