Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Enable fts3 tables to use incremental merge by automatically creating the %_stat table when it is needed. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | fts4-incr-merge |
Files: | files | file ages | folders |
SHA1: |
cc051fc0b2d89603b27b94cf2afdbda4 |
User & Date: | drh 2012-03-24 17:29:05.827 |
Context
2012-03-24
| ||
19:44 | Add SQLITE_DBSTATUS_CACHE_WRITE. Used to query a database connection for the cumulative number of database pages written. (check-in: 3cb6a879f1 user: dan tags: fts4-incr-merge) | |
17:29 | Enable fts3 tables to use incremental merge by automatically creating the %_stat table when it is needed. (check-in: cc051fc0b2 user: drh tags: fts4-incr-merge) | |
17:09 | Fix a failing assert() in the FTS3_LOG_MERGES related code. (check-in: 4220d52cb3 user: dan tags: fts4-incr-merge) | |
Changes
Changes to ext/fts3/fts3.c.
︙ | ︙ | |||
565 566 567 568 569 570 571 572 573 574 575 576 577 578 | } sqlite3_free(zSql); sqlite3_free(zCols); *pRc = rc; } } /* ** Create the backing store tables (%_content, %_segments and %_segdir) ** required by the FTS3 table passed as the only argument. This is done ** as part of the vtab xCreate() method. ** ** If the p->bHasDocsize boolean is true (indicating that this is an | > > > > > > > > > > > > | 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 | } sqlite3_free(zSql); sqlite3_free(zCols); *pRc = rc; } } /* ** Create the %_stat table if it does not already exist. */ void sqlite3Fts3CreateStatTable(int *pRc, Fts3Table *p){ fts3DbExec(pRc, p->db, "CREATE TABLE IF NOT EXISTS %Q.'%q_stat'" "(id INTEGER PRIMARY KEY, value BLOB);", p->zDb, p->zName ); if( (*pRc)==SQLITE_OK ) p->bHasStat = 1; } /* ** Create the backing store tables (%_content, %_segments and %_segdir) ** required by the FTS3 table passed as the only argument. This is done ** as part of the vtab xCreate() method. ** ** If the p->bHasDocsize boolean is true (indicating that this is an |
︙ | ︙ | |||
627 628 629 630 631 632 633 | if( p->bHasDocsize ){ fts3DbExec(&rc, db, "CREATE TABLE %Q.'%q_docsize'(docid INTEGER PRIMARY KEY, size BLOB);", p->zDb, p->zName ); } if( p->bHasStat ){ | | < < < | 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 | if( p->bHasDocsize ){ fts3DbExec(&rc, db, "CREATE TABLE %Q.'%q_docsize'(docid INTEGER PRIMARY KEY, size BLOB);", p->zDb, p->zName ); } if( p->bHasStat ){ sqlite3Fts3CreateStatTable(&rc, p); } return rc; } /* ** Store the current database page-size in bytes in p->nPgsz. ** |
︙ | ︙ | |||
1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 | /* If this is an xCreate call, create the underlying tables in the ** database. TODO: For xConnect(), it could verify that said tables exist. */ if( isCreate ){ rc = fts3CreateTables(p); } /* Figure out the page-size for the database. This is required in order to ** estimate the cost of loading large doclists from the database. */ fts3DatabasePageSize(&rc, p); p->nNodeSize = p->nPgsz-35; /* Declare the table schema to SQLite. */ | > > > > > > > > > > | 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 | /* If this is an xCreate call, create the underlying tables in the ** database. TODO: For xConnect(), it could verify that said tables exist. */ if( isCreate ){ rc = fts3CreateTables(p); } /* Check to see if a legacy fts3 table has been "upgraded" by the ** addition of a %_stat table so that it can use incremental merge. */ if( !isFts4 && !isCreate ){ int rc2 = SQLITE_OK; fts3DbExec(&rc2, db, "SELECT 1 FROM %Q.'%q_stat' WHERE id=2", p->zDb, p->zName); if( rc2==SQLITE_OK ) p->bHasStat = 1; } /* Figure out the page-size for the database. This is required in order to ** estimate the cost of loading large doclists from the database. */ fts3DatabasePageSize(&rc, p); p->nNodeSize = p->nPgsz-35; /* Declare the table schema to SQLite. */ |
︙ | ︙ |
Changes to ext/fts3/fts3Int.h.
︙ | ︙ | |||
489 490 491 492 493 494 495 496 497 498 499 500 501 502 | int sqlite3Fts3GetVarint(const char *, sqlite_int64 *); int sqlite3Fts3GetVarint32(const char *, int *); int sqlite3Fts3VarintLen(sqlite3_uint64); void sqlite3Fts3Dequote(char *); void sqlite3Fts3DoclistPrev(int,char*,int,char**,sqlite3_int64*,int*,u8*); int sqlite3Fts3EvalPhraseStats(Fts3Cursor *, Fts3Expr *, u32 *); int sqlite3Fts3FirstFilter(sqlite3_int64, char *, int, char *); /* fts3_tokenizer.c */ const char *sqlite3Fts3NextToken(const char *, int *); int sqlite3Fts3InitHashTable(sqlite3 *, Fts3Hash *, const char *); int sqlite3Fts3InitTokenizer(Fts3Hash *pHash, const char *, sqlite3_tokenizer **, char ** ); | > | 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 | int sqlite3Fts3GetVarint(const char *, sqlite_int64 *); int sqlite3Fts3GetVarint32(const char *, int *); int sqlite3Fts3VarintLen(sqlite3_uint64); void sqlite3Fts3Dequote(char *); void sqlite3Fts3DoclistPrev(int,char*,int,char**,sqlite3_int64*,int*,u8*); int sqlite3Fts3EvalPhraseStats(Fts3Cursor *, Fts3Expr *, u32 *); int sqlite3Fts3FirstFilter(sqlite3_int64, char *, int, char *); void sqlite3Fts3CreateStatTable(int*, Fts3Table*); /* fts3_tokenizer.c */ const char *sqlite3Fts3NextToken(const char *, int *); int sqlite3Fts3InitHashTable(sqlite3 *, Fts3Hash *, const char *); int sqlite3Fts3InitTokenizer(Fts3Hash *pHash, const char *, sqlite3_tokenizer **, char ** ); |
︙ | ︙ |
Changes to ext/fts3/fts3_write.c.
︙ | ︙ | |||
4664 4665 4666 4667 4668 4669 4670 | z++; nMin = fts3Getint(&z); } if( z[0]!='\0' || nMin<2 ){ rc = SQLITE_ERROR; }else{ | > > > | > | > > > > | 4664 4665 4666 4667 4668 4669 4670 4671 4672 4673 4674 4675 4676 4677 4678 4679 4680 4681 4682 4683 4684 4685 4686 4687 4688 4689 4690 4691 4692 4693 4694 4695 4696 4697 4698 4699 4700 4701 4702 4703 4704 4705 4706 | z++; nMin = fts3Getint(&z); } if( z[0]!='\0' || nMin<2 ){ rc = SQLITE_ERROR; }else{ rc = SQLITE_OK; if( !p->bHasStat ) sqlite3Fts3CreateStatTable(&rc, p); if( rc==SQLITE_OK ){ rc = sqlite3Fts3Incrmerge(p, nMerge, nMin); } sqlite3Fts3SegmentsClose(p); } return rc; } /* ** Process statements of the form: ** ** INSERT INTO table(table) VALUES('automerge=X'); ** ** where X is an integer. X==0 means to turn automerge off. X!=0 means ** turn it on. The setting is persistent. */ static int fts3DoAutoincrmerge( Fts3Table *p, /* FTS3 table handle */ const char *zParam /* Nul-terminated string containing boolean */ ){ int rc = SQLITE_OK; sqlite3_stmt *pStmt = 0; p->bAutoincrmerge = fts3Getint(&zParam)!=0; if( !p->bHasStat ){ sqlite3Fts3CreateStatTable(&rc, p); if( rc ) return rc; } rc = fts3SqlStmt(p, SQL_REPLACE_STAT, &pStmt, 0); if( rc ) return rc;; sqlite3_bind_int(pStmt, 1, FTS_STAT_AUTOINCRMERGE); sqlite3_bind_int(pStmt, 2, p->bAutoincrmerge); sqlite3_step(pStmt); rc = sqlite3_reset(pStmt); return rc; |
︙ | ︙ |
Added test/fts3merge.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 | # 2012 March 06 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #************************************************************************* # This file implements regression tests for SQLite library. The # focus of this script is testing the incremental merge function. # set testdir [file dirname $argv0] source $testdir/tester.tcl source $testdir/fts3_common.tcl set ::testprefix fts3merge # If SQLITE_ENABLE_FTS3 is defined, omit this file. ifcapable !fts3 { finish_test return } #------------------------------------------------------------------------- # Test cases 1.* # do_test 1.0 { fts3_build_db_1 1004 } {} do_test 1.1 { fts3_integrity_check t1 } {ok} do_execsql_test 1.1 { SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level } { 0 {0 1 2 3 4 5 6 7 8 9 10 11} 1 {0 1 2 3 4 5 6 7 8 9 10 11 12 13} 2 {0 1 2} } for {set i 0} {$i<20} {incr i} { do_execsql_test 1.2.$i.1 { INSERT INTO t1(t1) VALUES('merge=1') } do_test 1.2.$i.2 { fts3_integrity_check t1 } ok do_execsql_test 1.2.$i.3 { SELECT docid FROM t1 WHERE t1 MATCH 'zero one two three' } {123 132 213 231 312 321} } do_execsql_test 1.3 { SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level } { 0 {0 1 2 3} 1 {0 1 2 3 4 5 6} 2 {0 1 2 3} } for {set i 0} {$i<100} {incr i} { do_execsql_test 1.4.$i { INSERT INTO t1(t1) VALUES('merge=1,4') } do_test 1.4.$i.2 { fts3_integrity_check t1 } ok do_execsql_test 1.4.$i.3 { SELECT docid FROM t1 WHERE t1 MATCH 'zero one two three' } {123 132 213 231 312 321} } do_execsql_test 1.5 { SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level } { 2 {0 1} 3 0 } #------------------------------------------------------------------------- # Test cases 2.* test that errors in the xxx part of the 'merge=xxx' are # handled correctly. # do_execsql_test 2.0 { CREATE VIRTUAL TABLE t2 USING fts3 } foreach {tn arg} { 1 {merge=abc} 2 {merge=%%%} 3 {merge=,} 4 {merge=5,} 5 {merge=6,%} 6 {merge=6,six} 7 {merge=6,1} 8 {merge=6,0} } { do_catchsql_test 2.$tn { INSERT INTO t2(t2) VALUES($arg); } {1 {SQL logic error or missing database}} } #------------------------------------------------------------------------- # Test cases 3.* # do_test 3.0 { reset_db execsql { PRAGMA page_size = 512 } fts3_build_db_2 30040 } {} do_test 3.1 { fts3_integrity_check t2 } {ok} do_execsql_test 3.2 { SELECT level, group_concat(idx, ' ') FROM t2_segdir GROUP BY level } { 0 {0 1 2 3 4 5 6} 1 {0 1 2 3 4} 2 {0 1 2 3 4} 3 {0 1 2 3 4 5 6} } do_execsql_test 3.3 { INSERT INTO t2(t2) VALUES('merge=1000000,2'); SELECT level, group_concat(idx, ' ') FROM t2_segdir GROUP BY level } { 0 0 2 0 3 0 4 0 6 0 } #------------------------------------------------------------------------- # Test cases 4.* # reset_db do_execsql_test 4.1 { PRAGMA page_size = 512; CREATE VIRTUAL TABLE t4 USING fts3; PRAGMA main.page_size; } {512} do_test 4.2 { foreach x {a c b d e f g h i j k l m n o p} { execsql "INSERT INTO t4 VALUES('[string repeat $x 600]')" } execsql {SELECT level, group_concat(idx, ' ') FROM t4_segdir GROUP BY level} } {0 {0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15}} foreach {tn expect} { 1 "0 {0 1 2 3 4 5 6 7 8 9 10 11 12 13} 1 0" 2 "0 {0 1 2 3 4 5 6 7 8 9 10 11 12} 1 0" 3 "0 {0 1 2 3 4 5 6 7 8 9 10 11} 1 0" 4 "0 {0 1 2 3 4 5 6 7 8 9 10} 1 0" 5 "0 {0 1 2 3 4 5 6 7 8 9} 1 0" 6 "0 {0 1 2 3 4 5 6 7 8} 1 0" 7 "0 {0 1 2 3 4 5 6 7} 1 0" 8 "0 {0 1 2 3 4 5 6} 1 0" 9 "0 {0 1 2 3 4 5} 1 0" } { do_execsql_test 4.3.$tn { INSERT INTO t4(t4) VALUES('merge=1,16'); SELECT level, group_concat(idx, ' ') FROM t4_segdir GROUP BY level; } $expect } do_execsql_test 4.4.1 { SELECT quote(value) FROM t4_stat WHERE rowid=1 } {X'0006'} do_execsql_test 4.4.2 { DELETE FROM t4_stat WHERE rowid=1; INSERT INTO t4(t4) VALUES('merge=1,12'); SELECT level, group_concat(idx, ' ') FROM t4_segdir GROUP BY level; } "0 {0 1 2 3 4 5} 1 0" #------------------------------------------------------------------------- # Test cases 5.* # # Test that if a crisis-merge occurs that disrupts an ongoing incremental # merge, the next call to "merge=A,B" identifies this and starts a new # incremental merge. There are two scenarios: # # * There are less segments on the input level that the disrupted # incremental merge operated on, or # # * Sufficient segments exist on the input level but the segments # contain keys smaller than the largest key in the potential output # segment. # do_test 5.1 { reset_db fts3_build_db_1 1000 } {} do_execsql_test 5.2 { SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level; } { 0 {0 1 2 3 4 5 6 7} 1 {0 1 2 3 4 5 6 7 8 9 10 11 12 13} 2 {0 1 2} } do_execsql_test 5.3 { INSERT INTO t1(t1) VALUES('merge=1,4'); SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level; } { 0 {0 1 2 3 4 5 6 7} 1 {0 1 2 3 4 5 6 7 8 9 10 11 12 13} 2 {0 1 2 3} } do_execsql_test 5.4 {SELECT quote(value) from t1_stat WHERE rowid=1} {X'0104'} do_test 5.5 { foreach docid [execsql {SELECT docid FROM t1}] { execsql {INSERT INTO t1 SELECT * FROM t1 WHERE docid=$docid} } } {} do_execsql_test 5.6 {SELECT quote(value) from t1_stat WHERE rowid=1} {X'0104'} do_execsql_test 5.7 { SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level; SELECT quote(value) from t1_stat WHERE rowid=1; } { 0 {0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15} 1 {0 1 2 3 4 5 6 7 8 9 10 11} 2 {0 1 2 3 4 5 6 7} X'0104' } do_execsql_test 5.8 { INSERT INTO t1(t1) VALUES('merge=1,4'); SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level; SELECT quote(value) from t1_stat WHERE rowid=1; } { 0 {0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15} 1 {0 1 2 3 4 5 6 7 8 9 10 11} 2 {0 1 2 3 4 5 6 7} 3 {0} X'0204' } do_test 5.9 { set L [expr 16*16*8 + 16*4 + 1] foreach docid [execsql { SELECT docid FROM t1 UNION ALL SELECT docid FROM t1 LIMIT $L }] { execsql {INSERT INTO t1 SELECT * FROM t1 WHERE docid=$docid} } } {} do_execsql_test 5.10 { SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level; SELECT quote(value) from t1_stat WHERE rowid=1; } { 0 0 1 0 2 0 3 {0 1} X'0204' } do_execsql_test 5.11 { INSERT INTO t1(t1) VALUES('merge=10,4'); SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level; SELECT quote(value) from t1_stat WHERE rowid=1; } { 0 0 1 0 2 0 3 {0 1} X'0000' } #------------------------------------------------------------------------- # Test cases 6.* # # At one point the following test caused an assert() to fail (because the # second 'merge=1,2' operation below actually "merges" a single input # segment, which was unexpected). # do_test 6.1 { reset_db set a [string repeat a 900] set b [string repeat b 900] set c [string repeat c 900] set d [string repeat d 900] execsql { CREATE VIRTUAL TABLE t1 USING fts3; BEGIN; INSERT INTO t1 VALUES($a); INSERT INTO t1 VALUES($b); COMMIT; BEGIN; INSERT INTO t1 VALUES($c); INSERT INTO t1 VALUES($d); COMMIT; } execsql { INSERT INTO t1(t1) VALUES('merge=1,2'); INSERT INTO t1(t1) VALUES('merge=1,2'); } } {} #------------------------------------------------------------------------- # Test cases 7.* # # Test that the value returned by sqlite3_total_changes() increases by # 1 following a no-op "merge=A,B", or by more than 1 if actual work is # performed. # do_test 7.0 { reset_db fts3_build_db_1 1000 } {} do_execsql_test 7.1 { SELECT level, group_concat(idx, ' ') FROM t1_segdir GROUP BY level } { 0 {0 1 2 3 4 5 6 7} 1 {0 1 2 3 4 5 6 7 8 9 10 11 12 13} 2 {0 1 2} } do_test 7.2 { set x [db total_changes] execsql { INSERT INTO t1(t1) VALUES('merge=2,10') } expr { ([db total_changes] - $x)>1 } } {1} do_test 7.3 { set x [db total_changes] execsql { INSERT INTO t1(t1) VALUES('merge=200,10') } expr { ([db total_changes] - $x)>1 } } {1} do_test 7.4 { set x [db total_changes] execsql { INSERT INTO t1(t1) VALUES('merge=200,10') } expr { ([db total_changes] - $x)>1 } } {0} do_test 7.5 { set x [db total_changes] execsql { INSERT INTO t1(t1) VALUES('merge=200,10') } expr { ([db total_changes] - $x)>1 } } {0} finish_test |