SQLite Logo
Recently, I was working on a LinkedIn spy tool, I have used the SQLite database to store data , this tool need to support a full text search fonctionnality for application.
Googling on the internet and I found that SQLite already support full text searching natively.
This one done via three evolutions of the full text search extension FTS3 / FTS4 / FTS5. The original FTS3 code were contributed to the SQLite project by Scott Hess of Google.
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.
Let’s play with this extension, I choose this Moroccan beautiful Riad “Riad Al Mamoune”, the idea is to extract some reviews and search on them.
Riad Al Mamoune — TripAdvisor
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/backportssudo apt-get update && sudo apt-get install sqlite3
After update, then go to your workspace and create the SQLite database file by running:
sqlite3 hotels_reviews.db
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:
Using SQLite Full-Text Search with Python_In this post I will show how to use SQLite full-text search with Python (and a lot of help from peewee ORM). We will…_charlesleifer.com
Using full text search with python and sqlite for tutorial, beginner, sqlite3, fts, database, query…_Using full text search with python and sqlite for tutorial, beginner, sqlite3, fts, database, query and fts4 Code…_code.runnable.com