Hackernoon logo“Full Text Search” with SQLite by@baazzilhassan

“Full Text Search” with SQLite

Author profile picture

@baazzilhassanLhassan BAAZZI

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 RiadRiad 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/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:

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:

❤ If this post was helpful, please hit the little green heart and follow me using the follow buttons below!


Join Hacker Noon

Create your free account to unlock your custom reading experience.