We have a startup called Zaki which is an Arab social media for cooking and one of our best features on Zaki is favorite where user add recipes to it and back for it later. One of the important things about the Arab world that the internet cost is high and not always available So we choose to make favorite recipes working offline.

In the early days of this feature storing these favorite recipes in SQLite and showing them on a list was more than enough and users are so happy to have that but after while users start adding more and more recipes to their favorites and that convert finding a recipe process to a pain.

To solve this problem we need to make favorite recipes searchable sounds easy right? Let us dig deep and see if it is that easy.

Current Situation

First, we must know what is the current situation and how the data stored on SQLite we choose to store the recipe data as a JSON on the database.

We do not have relations or foreign keys and that help to make the query superfast.

We are using ORMLite which is an ORM created before Room comes to Android (I do not recommend anyone to use it nowadays Room a lot better).

How to search on SQL

Anyone with a database background will tell you that doing

Select * from recipes_table where recipe_content like ‘%searchWord%’

Is a very bad solution someone asks me even if I have an index on recipe_content column yes even if you have that index. Because starting with wildcard and ending with a wildcard will make your index without any benefit and make the query to slow

This happens if you using regular index. But if you are using Full-Text Index that will work perfectly for you.

So does SQLite have a Full-Text Index on their packages? Sorry, they do not have that but instead of that, it has an FTS module based on what they called a virtual table you can know more about that here. That means the regular table can not cover this problem and you need to move to the virtual table

Solution

FTS version

Based on the previous explanation our solution is FTS there are three versions of it 3 4 and 5 you can choose between them based on what is your minimum android SDK you support

I choose 4 it has good support reach to Android 11 which is approximately 100% of the android market.

ORMlite does not have direct support for FTS on query level so we need to create a custom code for that. By the way, Room version 2.1 has full support for FTS

Migration

Now we have the solution ready to implement and we know all the details to implement it All is good. But wait we still have migration problem. As I told you before we have a table already has the data of users and those data is important for users on some cases it is the thing keeping users on the app.

getDAO(context).executeRaw("CREATE VIRTUAL TABLE recipes_table USING fts3(addedDate VARCHAR DEFAULT 'Fri Jan 01 00:00:00 GMT+00:00 2016' , _id BIGINT ,recipe_content VARCHAR);");

getDAO(context).executeRaw("INSERT INTO recipes_table1 (docid,_id, addedDate, recipe_content) SELECT _id, _id, addedDate, recipe_content FROM recipes_table");

getDAO(context).executeRaw("DROP TABLE recipes_table;");

getDAO(context).executeRaw("ALTER TABLE recipes_table1 RENAME TO recipes_table;");

Simply we create a virtual table with the same structure of the original table insert the data using a select statement from the original table, drop the original table and finally altering the new table name to be the replaced for the original table

Here is something not good about FTS on SQLite if you have FTS doing a simple query turn to a very slow query. You can only use this type of query using match keyword like the following:

SELECT * FROM recipes_table where recipe_content match ‘cake%’

or

SELECT * FROM recipes_table where docid=recipe_id

This will explain why we put the old _id column into docid

Finally, be sure to execute the migration code on a thread, not the main thread because it may take a lot of time and cause ANR. I prefer using a dedicated splash screen as on the second approach in this article.

Bonus

If you are using ORMLite you can use the following snippet to match query

SelectArg selectArg = **new **SelectArg();

selectArg.setMetaInfo("recipe_content ");

query *= s + *"*";

selectArg.setValue(query);

QueryBuilder favoritePostLongQueryBuilder = DatabaseHelper.getHelper(getActivity()).getPostFragment(getActivity()).queryBuilder().selectColumns("docid", "_id", "addedDate", "recipe_content ");

favoritePostLongQueryBuilder.where().raw("recipe_content match ?", selectArg);

favoritePostLongQueryBuilder.orderBy("addedDate", false).prepare()

Conclusion

Android has good tools for helping developers to create really awesome and complicated solutions.

FTS is a really nice solution for searching in offline content.

Migration data from old schema to new one really complex and you need to test and be sure everything is working well.

Finally, you can use FTS in search and autocomplete functionality.

Thank you for your time and I love to hear more from you and if you find this article good for you please let me know that will support me to continue writing.

About Me

I’m Alaa Alsalehi a professional backend & mobile developer. I have a food recipe content startup with more than a quarter of a million active users.