Test failure in test_rebuild_fts

[simonw]

Not sure why this has only just started failing, but I'm getting this: https://github.com/simonw/sqlite-utils/runs/4488687639

E           sqlite3.DatabaseError: database disk image is malformed

sqlite_utils/db.py:425: DatabaseError
_______________________ test_rebuild_fts[searchable_fts] _______________________

fresh_db = <Database <sqlite3.Connection object at 0x1084ea9d0>>
table_to_fix = 'searchable_fts'

    @pytest.mark.parametrize("table_to_fix", ["searchable", "searchable_fts"])
    def test_rebuild_fts(fresh_db, table_to_fix):
        table = fresh_db["searchable"]
        table.insert(search_records[0])
        table.enable_fts(["text", "country"])
        # Run a search
        rows = list(table.search("tanuki"))
        assert len(rows) == 1
        assert {
            "rowid": 1,
            "text": "tanuki are running tricksters",
            "country": "Japan",
            "not_searchable": "foo",
        }.items() <= rows[0].items()
        # Delete from searchable_fts_data
        fresh_db["searchable_fts_data"].delete_where()
        # This should have broken the index
        with pytest.raises(sqlite3.DatabaseError):
            list(table.search("tanuki"))
        # Running rebuild_fts() should fix it
>       fresh_db[table_to_fix].rebuild_fts()

[simonw]

Failures started with this commit, which only touches documentation so is completely unrelated: e328db8

[simonw]

Here's the test in question. The way it works is a bit weird (deleting everything in the _fts_data table in order to force errors that can be fixed with .rebuild_fts()):

sqlite-utils/tests/test_fts.py

Lines 257 to 285 in 8ae77a6

	@pytest.mark.parametrize("table_to_fix", ["searchable", "searchable_fts"])
	def test_rebuild_fts(fresh_db, table_to_fix):
	table = fresh_db["searchable"]
	table.insert(search_records[0])
	table.enable_fts(["text", "country"])
	# Run a search
	rows = list(table.search("tanuki"))
	assert len(rows) == 1
	assert {
	"rowid": 1,
	"text": "tanuki are running tricksters",
	"country": "Japan",
	"not_searchable": "foo",
	}.items() <= rows[0].items()
	# Delete from searchable_fts_data
	fresh_db["searchable_fts_data"].delete_where()
	# This should have broken the index
	with pytest.raises(sqlite3.DatabaseError):
	list(table.search("tanuki"))
	# Running rebuild_fts() should fix it
	fresh_db[table_to_fix].rebuild_fts()
	rows2 = list(table.search("tanuki"))
	assert len(rows2) == 1
	assert {
	"rowid": 1,
	"text": "tanuki are running tricksters",
	"country": "Japan",
	"not_searchable": "foo",
	}.items() <= rows2[0].items()

[simonw]

Here's what the method does:

sqlite-utils/sqlite_utils/db.py

Lines 1941 to 1952 in e328db8

	def rebuild_fts(self):
	"Run the ``rebuild`` operation against the associated full-text search index table."
	fts_table = self.detect_fts()
	if fts_table is None:
	# Assume this is itself an FTS table
	fts_table = self.name
	self.db.execute(
	"INSERT INTO [{table}]([{table}]) VALUES('rebuild');".format(
	table=fts_table
	)
	)
	return self

Maybe I don't need a test that deliberately corrupts the database here? Not sure how to test that rebuild has been called though.

[simonw]

https://www.sqlite.org/fts5.html#the_rebuild_command says:

This command first deletes the entire full-text index, then rebuilds it based on the contents of the table or content table. It is not available with contentless tables.

INSERT INTO ft(ft) VALUES('rebuild');

[simonw]

It turns out rebuild does indeed work against content tables, so I can put that in the test instead.

[simonw]

The new test:

sqlite-utils/tests/test_fts.py

Lines 270 to 277 in ee13f98

	# Insert another record
	table.insert(search_records[1])
	# This should NOT show up in a searchs
	assert len(list(table.search("are"))) == 1
	# Running rebuild_fts() should fix it
	table.rebuild_fts()
	rows2 = list(table.search("are"))
	assert len(rows2) == 2

[simonw]

That passed!