Deleted records stay in the search index

[simonw]

Here's why:

dogsheep-beta/dogsheep_beta/utils.py

Lines 44 to 53 in 24f7898

	sql_rest = sql.split("select", 1)[1]
	sql = "select '{}/{}' as [table],{}".format(db_name, table, sql_rest)
	columns = derive_columns(other_db, sql)
	with other_db.conn:
	other_db.conn.execute(
	"REPLACE INTO index1.search_index ({}) {}".format(
	", ".join("[{}]".format(column) for column in columns), sql
	)
	)
	other_db.conn.close()

That should probably do DELETE FROM index1.search_index WHERE [table] = ? first.

[simonw]

Alternatively it could run as it does now but add a DELETE FROM index1.search_index WHERE key not in (select key from ...).

I'm not sure which would be more efficient.

[simonw]

I'm inclined to go with the first, simpler option. I have longer term plans for efficient incremental index updates based on clever trickery with triggers.