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}} 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}} 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 114forcedelete 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# 139forcedelete 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}} 203do_execsql_test fts3ao-4.8 { 204 SELECT snippet(t5, '[', ']') FROM t5 WHERE t5 MATCH 'the' 205} {{[the] quick brown fox} {jumped over [the]}} 206 207# Test that it is possible to rename an FTS4 table. Renaming an FTS4 table 208# involves renaming the extra %_docsize and %_stat tables. 209# 210do_execsql_test 5.1 { 211 CREATE VIRTUAL TABLE t7 USING FTS4; 212 INSERT INTO t7 VALUES('coined by a German clinician'); 213 SELECT count(*) FROM sqlite_master WHERE name LIKE 't7%'; 214 SELECT count(*) FROM sqlite_master WHERE name LIKE 't8%'; 215} {6 0} 216do_execsql_test 5.2 { 217 ALTER TABLE t7 RENAME TO t8; 218 SELECT count(*) FROM sqlite_master WHERE name LIKE 't7%'; 219 SELECT count(*) FROM sqlite_master WHERE name LIKE 't8%'; 220} {0 6} 221 222# At one point this was causing a memory leak. 223# 224foreach {tn sql} { 225 1 {} 226 2 { INSERT INTO ft(ft) VALUES('merge=2,2'); } 227} { 228 reset_db 229 do_execsql_test 6.$tn.1 " 230 CREATE TABLE t1(x); 231 CREATE VIRTUAL TABLE ft USING fts3; 232 INSERT INTO ft VALUES('hello world'); 233 $sql 234 " 235 236 db close 237 sqlite3 db test.db 238 do_execsql_test 6.$tn.2 { SELECT * FROM t1 } {} 239 240 do_test 6.$tn.3 { 241 sqlite3 db2 test.db 242 db2 eval { DROP TABLE t1 } 243 db2 close 244 set stmt [sqlite3_prepare db { SELECT * FROM ft } -1 dummy] 245 sqlite3_finalize $stmt 246 } {SQLITE_OK} 247 db close 248} 249 250finish_test 251