Index: ext/fts3/fts3.c ================================================================== --- ext/fts3/fts3.c +++ ext/fts3/fts3.c @@ -1903,22 +1903,25 @@ typedef enum fulltext_statement { CONTENT_INSERT_STMT, CONTENT_SELECT_STMT, CONTENT_UPDATE_STMT, CONTENT_DELETE_STMT, + CONTENT_EXISTS_STMT, BLOCK_INSERT_STMT, BLOCK_SELECT_STMT, BLOCK_DELETE_STMT, + BLOCK_DELETE_ALL_STMT, SEGDIR_MAX_INDEX_STMT, SEGDIR_SET_STMT, SEGDIR_SELECT_LEVEL_STMT, SEGDIR_SPAN_STMT, SEGDIR_DELETE_STMT, SEGDIR_SELECT_SEGMENT_STMT, SEGDIR_SELECT_ALL_STMT, + SEGDIR_DELETE_ALL_STMT, MAX_STMT /* Always at end! */ } fulltext_statement; /* These must exactly match the enum above. */ @@ -1929,15 +1932,17 @@ static const char *const fulltext_zStatement[MAX_STMT] = { /* CONTENT_INSERT */ NULL, /* generated in contentInsertStatement() */ /* CONTENT_SELECT */ NULL, /* generated in contentSelectStatement() */ /* CONTENT_UPDATE */ NULL, /* generated in contentUpdateStatement() */ /* CONTENT_DELETE */ "delete from %_content where docid = ?", + /* CONTENT_EXISTS */ "select docid from %_content limit 1", /* BLOCK_INSERT */ "insert into %_segments (blockid, block) values (null, ?)", /* BLOCK_SELECT */ "select block from %_segments where blockid = ?", /* BLOCK_DELETE */ "delete from %_segments where blockid between ? and ?", + /* BLOCK_DELETE_ALL */ "delete from %_segments", /* SEGDIR_MAX_INDEX */ "select max(idx) from %_segdir where level = ?", /* SEGDIR_SET */ "insert into %_segdir values (?, ?, ?, ?, ?, ?)", /* SEGDIR_SELECT_LEVEL */ "select start_block, leaves_end_block, root from %_segdir " @@ -1954,11 +1959,11 @@ "select start_block, leaves_end_block, root from %_segdir " " where level = ? and idx = ?", /* SEGDIR_SELECT_ALL */ "select start_block, leaves_end_block, root from %_segdir " " order by level desc, idx asc", - + /* SEGDIR_DELETE_ALL */ "delete from %_segdir", }; /* ** A connection to a fulltext index is an instance of the following ** structure. The xCreate and xConnect methods create an instance @@ -2247,10 +2252,29 @@ rc = sqlite3_bind_int64(s, 1, iDocid); if( rc!=SQLITE_OK ) return rc; return sql_single_step(s); } + +/* Returns SQLITE_ROW if any rows exist in %_content, SQLITE_DONE if +** no rows exist, and any error in case of failure. +*/ +static int content_exists(fulltext_vtab *v){ + sqlite3_stmt *s; + int rc = sql_get_statement(v, CONTENT_EXISTS_STMT, &s); + if( rc!=SQLITE_OK ) return rc; + + rc = sqlite3_step(s); + if( rc!=SQLITE_ROW ) return rc; + + /* We expect only one row. We must execute another sqlite3_step() + * to complete the iteration; otherwise the table will remain locked. */ + rc = sqlite3_step(s); + if( rc==SQLITE_DONE ) return SQLITE_ROW; + if( rc==SQLITE_ROW ) return SQLITE_ERROR; + return rc; +} /* insert into %_segments values ([pData]) ** returns assigned blockid in *piBlockid */ static int block_insert(fulltext_vtab *v, const char *pData, int nData, @@ -2419,10 +2443,27 @@ if( rc!=SQLITE_OK ) return rc; rc = sqlite3_bind_int64(s, 1, iLevel); if( rc!=SQLITE_OK ) return rc; + return sql_single_step(s); +} + +/* Delete entire fts index, SQLITE_OK on success, relevant error on +** failure. +*/ +static int segdir_delete_all(fulltext_vtab *v){ + sqlite3_stmt *s; + int rc = sql_get_statement(v, SEGDIR_DELETE_ALL_STMT, &s); + if( rc!=SQLITE_OK ) return rc; + + rc = sql_single_step(s); + if( rc!=SQLITE_OK ) return rc; + + rc = sql_get_statement(v, BLOCK_DELETE_ALL_STMT, &s); + if( rc!=SQLITE_OK ) return rc; + return sql_single_step(s); } /* TODO(shess) clearPendingTerms() is far down the file because ** writeZeroSegment() is far down the file because LeafWriter is far @@ -6110,10 +6151,27 @@ FTSTRACE(("FTS3 Update %p\n", pVtab)); if( nArg<2 ){ rc = index_delete(v, sqlite3_value_int64(ppArg[0])); + if( rc==SQLITE_OK ){ + /* If we just deleted the last row in the table, clear out the + ** index data. + */ + rc = content_exists(v); + if( rc==SQLITE_ROW ){ + rc = SQLITE_OK; + }else if( rc==SQLITE_DONE ){ + /* Clear the pending terms so we don't flush a useless level-0 + ** segment when the transaction closes. + */ + rc = clearPendingTerms(v); + if( rc==SQLITE_OK ){ + rc = segdir_delete_all(v); + } + } + } } else if( sqlite3_value_type(ppArg[0]) != SQLITE_NULL ){ /* An update: * ppArg[0] = old rowid * ppArg[1] = new rowid * ppArg[2..2+v->nColumn-1] = values ADDED test/fts3d.test Index: test/fts3d.test ================================================================== --- /dev/null +++ test/fts3d.test @@ -0,0 +1,128 @@ +# 2008 June 26 +# +# 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 FTS3 module's optimize() function. +# +# $Id: fts3d.test,v 1.1 2008/07/14 20:43:15 shess Exp $ +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +# If SQLITE_ENABLE_FTS3 is not defined, omit this file. +ifcapable !fts3 { + finish_test + return +} + +#************************************************************************* +# Probe to see if support for the FTS3 dump_* functions is compiled in. +# TODO(shess): Change main.mk to do the right thing and remove this test. +db eval { + DROP TABLE IF EXISTS t1; + CREATE VIRTUAL TABLE t1 USING fts3(c); + INSERT INTO t1 (docid, c) VALUES (1, 'x'); +} + +set s {SELECT dump_terms(t1, 1) FROM t1 LIMIT 1} +set r {1 {unable to use function dump_terms in the requested context}} +if {[catchsql $s]==$r} { + finish_test + return +} + +#************************************************************************* +# Utility function to check for the expected terms in the segment +# level/index. _all version does same but for entire index. +proc check_terms {test level index terms} { + # TODO(shess): Figure out why uplevel in do_test can't catch + # $level and $index directly. + set ::level $level + set ::index $index + do_test $test.terms { + execsql { + SELECT dump_terms(t1, $::level, $::index) FROM t1 LIMIT 1; + } + } [list $terms] +} +proc check_terms_all {test terms} { + do_test $test.terms { + execsql { + SELECT dump_terms(t1) FROM t1 LIMIT 1; + } + } [list $terms] +} + +# Utility function to check for the expected doclist for the term in +# segment level/index. _all version does same for entire index. +proc check_doclist {test level index term doclist} { + # TODO(shess): Again, why can't the non-:: versions work? + set ::term $term + set ::level $level + set ::index $index + do_test $test { + execsql { + SELECT dump_doclist(t1, $::term, $::level, $::index) FROM t1 LIMIT 1; + } + } [list $doclist] +} +proc check_doclist_all {test term doclist} { + set ::term $term + do_test $test { + execsql { + SELECT dump_doclist(t1, $::term) FROM t1 LIMIT 1; + } + } [list $doclist] +} + +#************************************************************************* +# Test results when all rows are deleted and one is added back. +# Previously older segments would continue to exist, but now the index +# should be dropped when the table is empty. The results should look +# exactly like we never added the earlier rows in the first place. +db eval { + DROP TABLE IF EXISTS t1; + CREATE VIRTUAL TABLE t1 USING fts3(c); + INSERT INTO t1 (docid, c) VALUES (1, 'This is a test'); + INSERT INTO t1 (docid, c) VALUES (2, 'That was a test'); + INSERT INTO t1 (docid, c) VALUES (3, 'This is a test'); + DELETE FROM t1 WHERE 1=1; -- Delete each row rather than dropping table. + INSERT INTO t1 (docid, c) VALUES (1, 'This is a test'); +} + +# Should be a single initial segment. +do_test fts3d-1.segments { + execsql { + SELECT level, idx FROM t1_segdir ORDER BY level, idx; + } +} {0 0} +do_test fts3d-1.matches { + execsql { + SELECT OFFSETS(t1) FROM t1 + WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY docid; + } +} {{0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}} + +check_terms_all fts3d-1.1 {a is test this} +check_doclist_all fts3d-1.1.1 a {[1 0[2]]} +check_doclist_all fts3d-1.1.2 is {[1 0[1]]} +check_doclist_all fts3d-1.1.3 test {[1 0[3]]} +check_doclist_all fts3d-1.1.4 this {[1 0[0]]} + +check_terms fts3d-1.2 0 0 {a is test this} +check_doclist fts3d-1.2.1 0 0 a {[1 0[2]]} +check_doclist fts3d-1.2.2 0 0 is {[1 0[1]]} +check_doclist fts3d-1.2.3 0 0 test {[1 0[3]]} +check_doclist fts3d-1.2.4 0 0 this {[1 0[0]]} + +# TODO(shess): optimize() tests here. + +finish_test