Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Delete all fts2 index data the table becomes empty. Backports check-in (5413) from fts3. (CVS 5457) |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
4c98179be258319f441ae4e123cf59af |
User & Date: | shess 2008-07-22 23:41:26.000 |
Context
2008-07-22
| ||
23:49 | Implement optimize() function. Backports check-in (5417) from fts3. (CVS 5458) (check-in: c16900dc76 user: shess tags: trunk) | |
23:41 | Delete all fts2 index data the table becomes empty. Backports check-in (5413) from fts3. (CVS 5457) (check-in: 4c98179be2 user: shess tags: trunk) | |
23:32 | fts2 functions for testing scripts. Backports (5340) from fts3. (CVS 5456) (check-in: 4e47394be9 user: shess tags: trunk) | |
Changes
Changes to ext/fts2/fts2.c.
︙ | ︙ | |||
1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 | } QueryType; typedef enum fulltext_statement { CONTENT_INSERT_STMT, CONTENT_SELECT_STMT, CONTENT_UPDATE_STMT, CONTENT_DELETE_STMT, BLOCK_INSERT_STMT, BLOCK_SELECT_STMT, BLOCK_DELETE_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, MAX_STMT /* Always at end! */ } fulltext_statement; /* These must exactly match the enum above. */ /* TODO(shess): Is there some risk that a statement will be used in two ** cursors at once, e.g. if a query joins a virtual table to itself? ** If so perhaps we should move some of these to the cursor object. */ static const char *const fulltext_zStatement[MAX_STMT] = { /* CONTENT_INSERT */ NULL, /* generated in contentInsertStatement() */ /* CONTENT_SELECT */ "select * from %_content where rowid = ?", /* CONTENT_UPDATE */ NULL, /* generated in contentUpdateStatement() */ /* CONTENT_DELETE */ "delete from %_content where rowid = ?", /* BLOCK_INSERT */ "insert into %_segments values (?)", /* BLOCK_SELECT */ "select block from %_segments where rowid = ?", /* BLOCK_DELETE */ "delete from %_segments where rowid between ? and ?", /* 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 " " where level = ? order by idx", /* SEGDIR_SPAN */ "select min(start_block), max(end_block) from %_segdir " " where level = ? and start_block <> 0", /* SEGDIR_DELETE */ "delete from %_segdir where level = ?", /* NOTE(shess): The first three results of the following two ** statements must match. */ /* SEGDIR_SELECT_SEGMENT */ "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", }; /* ** A connection to a fulltext index is an instance of the following ** structure. The xCreate and xConnect methods create an instance ** of this structure and xDestroy and xDisconnect free that instance. ** All other methods receive a pointer to the structure as one of their | > > > > > > | 1770 1771 1772 1773 1774 1775 1776 1777 1778 1779 1780 1781 1782 1783 1784 1785 1786 1787 1788 1789 1790 1791 1792 1793 1794 1795 1796 1797 1798 1799 1800 1801 1802 1803 1804 1805 1806 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 | } QueryType; 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. */ /* TODO(shess): Is there some risk that a statement will be used in two ** cursors at once, e.g. if a query joins a virtual table to itself? ** If so perhaps we should move some of these to the cursor object. */ static const char *const fulltext_zStatement[MAX_STMT] = { /* CONTENT_INSERT */ NULL, /* generated in contentInsertStatement() */ /* CONTENT_SELECT */ "select * from %_content where rowid = ?", /* CONTENT_UPDATE */ NULL, /* generated in contentUpdateStatement() */ /* CONTENT_DELETE */ "delete from %_content where rowid = ?", /* CONTENT_EXISTS */ "select rowid from %_content limit 1", /* BLOCK_INSERT */ "insert into %_segments values (?)", /* BLOCK_SELECT */ "select block from %_segments where rowid = ?", /* BLOCK_DELETE */ "delete from %_segments where rowid 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 " " where level = ? order by idx", /* SEGDIR_SPAN */ "select min(start_block), max(end_block) from %_segdir " " where level = ? and start_block <> 0", /* SEGDIR_DELETE */ "delete from %_segdir where level = ?", /* NOTE(shess): The first three results of the following two ** statements must match. */ /* SEGDIR_SELECT_SEGMENT */ "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 ** of this structure and xDestroy and xDisconnect free that instance. ** All other methods receive a pointer to the structure as one of their |
︙ | ︙ | |||
2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 | if( rc!=SQLITE_OK ) return rc; rc = sqlite3_bind_int64(s, 1, iRow); if( rc!=SQLITE_OK ) return rc; return sql_single_step(s); } /* insert into %_segments values ([pData]) ** returns assigned rowid in *piBlockid */ static int block_insert(fulltext_vtab *v, const char *pData, int nData, sqlite_int64 *piBlockid){ sqlite3_stmt *s; | > > > > > > > > > > > > > > > > > > > | 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 2118 2119 2120 2121 2122 2123 2124 2125 2126 2127 2128 2129 2130 2131 2132 2133 | if( rc!=SQLITE_OK ) return rc; rc = sqlite3_bind_int64(s, 1, iRow); 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 rowid in *piBlockid */ static int block_insert(fulltext_vtab *v, const char *pData, int nData, sqlite_int64 *piBlockid){ sqlite3_stmt *s; |
︙ | ︙ | |||
2266 2267 2268 2269 2270 2271 2272 2273 2274 2275 2276 2277 2278 2279 | /* Delete the segment directory itself. */ rc = sql_get_statement(v, SEGDIR_DELETE_STMT, &s); if( rc!=SQLITE_OK ) return rc; rc = sqlite3_bind_int64(s, 1, iLevel); 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 ** down the file. Consider refactoring the code to move the non-vtab ** code above the vtab code so that we don't need this forward | > > > > > > > > > > > > > > > > > | 2291 2292 2293 2294 2295 2296 2297 2298 2299 2300 2301 2302 2303 2304 2305 2306 2307 2308 2309 2310 2311 2312 2313 2314 2315 2316 2317 2318 2319 2320 2321 | /* Delete the segment directory itself. */ rc = sql_get_statement(v, SEGDIR_DELETE_STMT, &s); 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 ** down the file. Consider refactoring the code to move the non-vtab ** code above the vtab code so that we don't need this forward |
︙ | ︙ | |||
5771 5772 5773 5774 5775 5776 5777 5778 5779 5780 5781 5782 5783 5784 | fulltext_vtab *v = (fulltext_vtab *) pVtab; int rc; TRACE(("FTS2 Update %p\n", pVtab)); if( nArg<2 ){ rc = index_delete(v, sqlite3_value_int64(ppArg[0])); } 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 * ppArg[2+v->nColumn] = value for magic column (we ignore this) */ | > > > > > > > > > > > > > > > > > | 5813 5814 5815 5816 5817 5818 5819 5820 5821 5822 5823 5824 5825 5826 5827 5828 5829 5830 5831 5832 5833 5834 5835 5836 5837 5838 5839 5840 5841 5842 5843 | fulltext_vtab *v = (fulltext_vtab *) pVtab; int rc; TRACE(("FTS2 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 * ppArg[2+v->nColumn] = value for magic column (we ignore this) */ |
︙ | ︙ |
Added test/fts2q.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 | # 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 FTS2 module's optimize() function. # # $Id: fts2q.test,v 1.1 2008/07/22 23:41:26 shess Exp $ # set testdir [file dirname $argv0] source $testdir/tester.tcl # If SQLITE_ENABLE_FTS2 is not defined, omit this file. ifcapable !fts2 { finish_test return } #************************************************************************* # Probe to see if support for the FTS2 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 fts2(c); INSERT INTO t1 (rowid, 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 fts2(c); INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test'); INSERT INTO t1 (rowid, c) VALUES (2, 'That was a test'); INSERT INTO t1 (rowid, c) VALUES (3, 'This is a test'); DELETE FROM t1 WHERE 1=1; -- Delete each row rather than dropping table. INSERT INTO t1 (rowid, c) VALUES (1, 'This is a test'); } # Should be a single initial segment. do_test fts2q-1.segments { execsql { SELECT level, idx FROM t1_segdir ORDER BY level, idx; } } {0 0} do_test fts2q-1.matches { execsql { SELECT OFFSETS(t1) FROM t1 WHERE t1 MATCH 'this OR that OR was OR a OR is OR test' ORDER BY rowid; } } {{0 0 0 4 0 4 5 2 0 3 8 1 0 5 10 4}} check_terms_all fts2q-1.1 {a is test this} check_doclist_all fts2q-1.1.1 a {[1 0[2]]} check_doclist_all fts2q-1.1.2 is {[1 0[1]]} check_doclist_all fts2q-1.1.3 test {[1 0[3]]} check_doclist_all fts2q-1.1.4 this {[1 0[0]]} check_terms fts2q-1.2 0 0 {a is test this} check_doclist fts2q-1.2.1 0 0 a {[1 0[2]]} check_doclist fts2q-1.2.2 0 0 is {[1 0[1]]} check_doclist fts2q-1.2.3 0 0 test {[1 0[3]]} check_doclist fts2q-1.2.4 0 0 this {[1 0[0]]} # TODO(shess): optimize() tests here. finish_test |