Some questions about Database/FTS sample project

from the CommonsWare Community archives

At May 11, 2018, 6:29am, Shahood asked:

Hello,

Following are some of my questions regarding Database/FTS sample project:

Why do u disable the SearchView in doSearch() when it will shortly be re-enabled in onModelLoaded()?
If FTS tables ignore constraints like PRIMARY KEY, DEFAULT, NOT NULL, do we have to write code and apply these constraints before inserting data into the table? If yes, whats the best to apply the PRIMARY KEY constraint through java code?
FTS finds only those SO posts where the full query text is matched. How do we achieve the ‘contains’ or LIKE behaviour using FTS? For instance, an SO post containing the word ‘snackbar’ will be displayed only when the search term is ‘snackbar’; how to display this post if the search term is ‘snack’?

Thanks,

At May 11, 2018, 10:54am, mmurphy replied:

Why do u disable the SearchView in doSearch() when it will shortly be re-enabled in onModelLoaded()?

IIRC, I did not want a second search to be requested while the first search was being processed. This is a fairly typical UI pattern (e.g., disabling the submit button in a Web form after the user clicks it, so the user does not accidentally submit twice).

If FTS tables ignore constraints like PRIMARY KEY, DEFAULT, NOT NULL, do we have to write code and apply these constraints before inserting data into the table?

I would try to design the FTS table to not need those things. The FTS table is there for full-text search and pretty much nothing else. Use regular tables for regular data.

How do we achieve the ‘contains’ or LIKE behaviour using FTS? For instance, an SO post containing the word ‘snackbar’ will be displayed only when the search term is ‘snackbar’; how to display this post if the search term is ‘snack’?

Quoting the SQLite FTS3 documentation, “The query expression used to search for a term prefix is the prefix itself with a ‘*’ character appended to it”. So, in your case, use snack*.

At May 11, 2018, 7:23pm, Shahood replied:

mmurphy:

IIRC, I did not want a second search to be requested while the first search was being processed. This is a fairly typical UI pattern (e.g., disabling the submit button in a Web form after the user clicks it, so the user does not accidentally submit twice).

Ok, got it

I would try to design the FTS table to not need those things. The FTS table is there for full-text search and pretty much nothing else. Use regular tables for regular data.

Umm, do u suggest to have one regular table for inserting data into and an FTS table which is a clone of the first one, for full text search? I really didn’t get the whole idea. Would appreciate some further explanation!

Quoting the SQLite FTS3 documentation, “The query expression used to search for a term prefix is the prefix itself with a ‘*’ character appended to it”. So, in your case, use snack*.

Well, would it be wise to expect the user to append a * with their query. Another way would be to hardcode a * with every query input by the user but even that doesn’t seem like an intelligent solution; not every query is a term prefix query. And what if the user expected to get ‘snackbar’ posts by entering the search term ‘bar’…a term suffix scenario?

At May 11, 2018, 8:00pm, mmurphy replied:

And what if the user expected to get ‘snackbar’ posts by entering the search term ‘bar’…a term suffix scenario?

I do not believe that is supported. Please read the SQLite FTS3 documentation, particularly “3. Full-text Index Queries”, for the supported search syntax.

At May 12, 2018, 3:41am, Shahood replied:

I would try to design the FTS table to not need those things. The FTS table is there for full-text search and pretty much nothing else. Use regular tables for regular data.

Umm, do u suggest to have one regular table for inserting data into and an FTS table which is a clone of the first one, for full text search? I really didn’t get the whole idea. Would appreciate some further explanation!

At May 12, 2018, 11:19am, mmurphy replied:

do u suggest to have one regular table for inserting data into and an FTS table which is a clone of the first one, for full text search?

If you wish for more advice regarding implementing SQLite FTS, you are better served using SQLite resources. I can help you with Android questions.