In some scenarios we might already have an existing sqlite table (let's call it "legacy table") that has some content we want to add to the search index. One use case would be: we have a table managed through the Django web framework that is already populated with data and we want to have a search index on top of this.
In this scenario, using pocketsearch as it is, we would have to create 2 additional tables, a copy of the existing (legacy) table + the fts5 table on top, which means we need to store the data in a redundant way.
It would be better, if we could re-use the existing table and build our FTS virtual table on top of that:
Check if the table already exists.
If yes, just create a virtual (contentless) table
Link the legacy table with triggers to the virtual contentless table
Populate the FTS5 index on creation with the legacy data flagged for indexing
As we cannot assume all legacy tables use the field "id" with an auto-increment primary key, we need to provide the possibility to define our own "id" (IdField) field.
We need to make sure, that the Schema definition is in-line with actual table definition and throw an error otherwise, if fields are missing or the schema defines fields not found in the legacy table.
kaykay-dv
commented
1 year ago
In some scenarios we might already have an existing sqlite table (let's call it "legacy table") that has some content we want to add to the search index. One use case would be: we have a table managed through the Django web framework that is already populated with data and we want to have a search index on top of this.
In this scenario, using pocketsearch as it is, we would have to create 2 additional tables, a copy of the existing (legacy) table + the fts5 table on top, which means we need to store the data in a redundant way. It would be better, if we could re-use the existing table and build our FTS virtual table on top of that:
As we cannot assume all legacy tables use the field "id" with an auto-increment primary key, we need to provide the possibility to define our own "id" (IdField) field.
We need to make sure, that the Schema definition is in-line with actual table definition and throw an error otherwise, if fields are missing or the schema defines fields not found in the legacy table.