“Full Text Search” with SQLite by@baazzilhassan

“Full Text Search” with SQLite

July 29th 2017 10,628 reads
Read on Terminal Reader
react to story with heart
react to story with light
react to story with boat
react to story with money
image
Lhassan BAAZZI HackerNoon profile picture

Lhassan BAAZZI

image

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.

image

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';

image

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;

image

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

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

react to story with heart
react to story with light
react to story with boat
react to story with money
L O A D I N G
. . . comments & more!