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 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 set ::testprefix fts3ao 27 28 #--------------------------------------------------------------------- 29 # These tests, fts3ao-1.*, test that ticket #2429 is fixed. 30 # 31 db 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 } 35 do_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}} 40 do_test fts3ao-1.2 { 41 execsql { 42 SELECT rowid, snippet(t1) FROM t1 WHERE b MATCH 'four'; 43 } 44 } {1 {one <b>four</b>}} 45 do_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 # 54 do_test fts3ao-2.1 { 55 execsql { SELECT tbl_name FROM sqlite_master WHERE type = 'table'} 56 } {t1 t1_content t1_segments t1_segdir} 57 do_test fts3ao-2.2 { 58 execsql { ALTER TABLE t1 RENAME to fts_t1; } 59 } {} 60 do_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>}} 63 do_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 # 69 do_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}} 75 do_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>}} 78 do_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 # 84 do_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 } {} 90 do_test fts3ao-2.9 { 91 catchsql { 92 ALTER TABLE fts_t1 RENAME to t1; 93 } 94 } {1 {SQL logic error or missing database}} 95 do_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>}} 98 do_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} 101 do_test fts3ao-2.12 { 102 execsql COMMIT 103 execsql {SELECT a FROM fts_t1} 104 } {{one three four} {one two three}} 105 do_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 # 113 db close 114 file delete -force test.db test.db-journal 115 sqlite3 db test.db 116 117 do_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 # 127 do_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 # 139 file delete -force test2.db test2.db-journal 140 141 do_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 151 do_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 155 do_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 159 do_test fts3ao-3.4 { 160 execsql { ALTER TABLE aux.t1 RENAME TO t2 } 161 } {} 162 163 do_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 167 do_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 # 175 do_test fts3ao-4.1 { 176 execsql { 177 CREATE VIRTUAL TABLE t4 USING fts3; 178 INSERT INTO t4 VALUES('the quick brown fox'); 179 } 180 } {} 181 do_test fts3ao-4.2 { 182 execsql { 183 BEGIN; 184 INSERT INTO t4 VALUES('jumped over the'); 185 } 186 } {} 187 do_test fts3ao-4.3 { execsql { ALTER TABLE t4 RENAME TO t5; } } {} 188 do_test fts3ao-4.4 { execsql { INSERT INTO t5 VALUES('lazy dog'); } } {} 189 do_test fts3ao-4.5 { execsql COMMIT } {} 190 do_test fts3ao-4.6 { 191 execsql { SELECT * FROM t5 } 192 } {{the quick brown fox} {jumped over the} {lazy dog}} 193 do_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 # 207 do_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} 213 do_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 219 finish_test 220 221
