Googling on the internet and I found that SQLite already support full text searching natively.
FTS1 and FTS2 are obsolete full-text search modules for SQLite. There are known issues with these older modules and their use should be avoided.
To manipulate the SQLite database, I use the sqlite3 command in ubuntu, but we need to update it as doesn’t support the FTS5, so, to update it run the following commands lines on terminal:
sudo add-apt-repository ppa:jonathonf/backports
sudo apt-get update && sudo apt-get install sqlite3
After update, then go to your workspace and create the SQLite database file by running:
Now, copy and paste every SQL part on this gist and run it on the sqlite3 prompt, please read comments to understand more:
After you have set properly your database, now, we can try to execute some queries searches:
SELECT rowid, review FROM hotels_reviews_index WHERE hotels_reviews_index MATCH 'review:help';
You can also order by relevance which is the rank score:
SELECT rowid, rank, review FROM hotels_reviews_index WHERE hotels_reviews_index MATCH 'review:square AND location' ORDER BY rank;
Please check the official documentation for syntax and operators supported by the FTS5 extension by following this link: https://www.sqlite.org/fts5.html#full_text_query_syntax
You can read more about this topic here:
Create your free account to unlock your custom reading experience.