Not all characters can be used for searching

[Korb]

Cards List	Search Results

---

Catima — Loyalty Card Wallet 2.39.2 (155) f-droid (Nov 07, 2025)

Model: Vivo T1 (4G) / V2153
OS version: Funtouch OS 14
Hardware version: MP_0.1
Build number: PD22010F_EX_A_14.2.14.0.W20
Baseband version: .HA.1.1.c2-00054-DIVAR_GENSP_PACK-1.29062.10 7
Kernel version: 4.19.157-perf+. 1 Thu May 8 10:36:27 CST 2025
Compile time: 8 May 2025 05:31:38
Android security update: 1 May 2025
Google Play system update: 1 April 2025
Software parameter update: 14 March 2025

[TheLastProject]

Thanks for the report. Can confirm.

Not sure what causes this, my guess would be it has to do with many emojis being multiple characters and that somehow going wrong. Would need more research.

[TheLastProject]

I found by pure chance the FTS generation, which is used for searching, seems to be broken.

For example, my grocery store "Albert Heijn" card should be stored in the FTS table as:

Albert lbert bert ert rt t Heijn eijn ijn jn n

but it is for some reason stored as

Albert lbert bert Heijn eijn ijn

I think the bug is in the fact that the variable word gets updated while looping over it, causing the loop to get cut short:

Android/app/src/main/java/protect/card_locker/DBHelper.java

Lines 515 to 545 in cabae40

	private static ContentValues generateFTSContentValues(final int id, final String store, final String note) {
	// FTS on Android is severely limited and can only search for word starting with a certain string
	// So for each word, we grab every single substring
	// This makes it possible to find Décathlon by searching both de and cat, for example
	ContentValues ftsContentValues = new ContentValues();
	StringBuilder storeString = new StringBuilder();
	for (String word : store.split(" ")) {
	for (int i = 0; i < word.length(); i++) {
	storeString.append(word);
	storeString.append(" ");
	word = word.substring(1);
	}
	}
	StringBuilder noteString = new StringBuilder();
	for (String word : note.split(" ")) {
	for (int i = 0; i < word.length(); i++) {
	noteString.append(word);
	noteString.append(" ");
	word = word.substring(1);
	}
	}
	ftsContentValues.put(LoyaltyCardDbFTS.ID, id);
	ftsContentValues.put(LoyaltyCardDbFTS.STORE, storeString.toString());
	ftsContentValues.put(LoyaltyCardDbFTS.NOTE, noteString.toString());
	return ftsContentValues;
	}

[TheLastProject]

Best way to test if this is your bug is to put the skull emoji at the start of the card name and see if it still fails to search for it.

[Korb]

to put the skull emoji at the start of the card name and see if it still fails to search for it.

@TheLastProject, should I check if the search is correct in version 2.41.5 (2026-01-16)?

[TheLastProject]

It's not fixed in 2.41.5.

I just tested myself by putting 💀 as the first character in the card and searching for it, but sadly that fails too. So this bug is unrelated to the other search bug I randomly stumbled upon sadly. So still no clue how to fix this one.

[mpaulmier]

Searching through github issues, this is not the only project to have this problem.

Here [0] is an issue with a fix for signal for example though I'm not sure how applicable it is here since it uses fts5 while Catima uses fts4.

[0] signalapp/Signal-Android#7822 (comment)

[TheLastProject]

Nice find @mpaulmier!

I don't think Catima using fts4 is a problem, especially given the limited amount of data. I'd argue we should just add a database migration that deletes the old fts4 table and recreates it with fts5 and re-inserts all the data :)

I don't think I'll have time to look into this soon, so a MR would be very helpful :)

[mpaulmier]

Ok great, I can probably check that during the weekend

[mpaulmier]

Someone with better knowledge of sqlite and android version might want to take a look at that.

I have made these changes

---
 .../java/protect/card_locker/DBHelper.java    | 20 +++++++++++++++----
 1 file changed, 16 insertions(+), 4 deletions(-)

diff --git a/app/src/main/java/protect/card_locker/DBHelper.java b/app/src/main/java/protect/card_locker/DBHelper.java
index 5f58c1a9..4584132b 100644
--- a/app/src/main/java/protect/card_locker/DBHelper.java
+++ b/app/src/main/java/protect/card_locker/DBHelper.java
@@ -11,7 +11,6 @@ import android.text.TextUtils;
 import android.util.Log;
 
 import androidx.annotation.NonNull;
-import androidx.annotation.Nullable;
 
 import java.io.FileNotFoundException;
 import java.math.BigDecimal;
@@ -27,7 +26,7 @@ import java.util.Set;
 public class DBHelper extends SQLiteOpenHelper {
     public static final String DATABASE_NAME = "Catima.db";
     public static final int ORIGINAL_DATABASE_VERSION = 1;
-    public static final int DATABASE_VERSION = 20;
+    public static final int DATABASE_VERSION = 21;
 
     // NB: changing these values requires a migration
     public static final int DEFAULT_ZOOM_LEVEL = 100;
@@ -131,9 +130,9 @@ public class DBHelper extends SQLiteOpenHelper {
                 "primary key (" + LoyaltyCardDbIdsGroups.cardID + "," + LoyaltyCardDbIdsGroups.groupID + "))");
 
         // create FTS search table
-        db.execSQL("CREATE VIRTUAL TABLE " + LoyaltyCardDbFTS.TABLE + " USING fts4(" +
+        db.execSQL("CREATE VIRTUAL TABLE " + LoyaltyCardDbFTS.TABLE + " USING fts5(" +
                 LoyaltyCardDbFTS.STORE + ", " + LoyaltyCardDbFTS.NOTE + ", " +
-                "tokenize=unicode61);");
+                "tokenize = \"unicode61 categories 'L* N* Co Sc So'\");");
     }
 
     @Override
@@ -495,6 +494,19 @@ public class DBHelper extends SQLiteOpenHelper {
             db.endTransaction();
         }
 
+        if (oldVersion < 21 && newVersion >= 21) {
+            db.execSQL("CREATE VIRTUAL TABLE fts_new USING fts5(" +
+                    LoyaltyCardDbFTS.STORE + ", " + LoyaltyCardDbFTS.NOTE + ", " +
+                    "tokenize = \"unicode61 categories 'L* N* Co Sc So'\");");
+
+            db.execSQL("INSERT INTO fts_new(store, note) SELECT " +
+                    LoyaltyCardDbFTS.STORE + ", " + LoyaltyCardDbFTS.NOTE +
+                    " FROM fts");
+
+            db.execSQL("DROP TABLE fts");
+
+            db.execSQL("ALTER TABLE fts_new RENAME TO fts");
+        }
     }
 
     public static Set<String> imageFiles(Context context, final SQLiteDatabase database) {
-- 
2.50.1 (Apple Git-155)

Which seem simple enough, I've tested it in the sqlite console, it looks like it works as expected.
However the migration fails because the fts5 module doesn't exist.

Caused by: android.database.sqlite.SQLiteException: no such module: fts5 (code 1 SQLITE_ERROR)
    at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
    at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:947)
    at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:791)
    at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:67)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:2233)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:2152)
    at protect.card_locker.DBHelper.onUpgrade(DBHelper.java:498)
    at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:433)
    at android.database.sqlite.SQLiteOpenHelper.getWritableDatabase(SQLiteOpenHelper.java:332)
    at protect.card_locker.MainActivity.onCreate(MainActivity.kt:259)
    at android.app.Activity.performCreate(Activity.java:9306)
    at android.app.Activity.performCreate(Activity.java:9284)
    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1541)
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:4480)
    ... 14 more

From what I understand the version shipped with android 16 doesn't support it but I cannot find reliable info on which version would support it. Also I'm afraid that would exclude some current users if we upgrade the min SDK version :(
I don't know how the signal app handles that, I will try to take a look later this week if that can help.

EDIT: After some reading I see they also user android's minSdk 23. They have specific dependencies to sqlite that may explain why fts5 is available for them. I'll do some more tests during the week

[mpaulmier]

Small update just so you know, I haven't given up on this but the first approach I took, which consists of using this package, requires a lot of changes to the codebase everywhere. I still haven't managed to make it work. Maybe I'll try a different approach.

[TheLastProject]

If fts5 is this complex, maybe it's better to just see if this can be made working in fts4 :)

[mpaulmier]

For fts4 we can always use ilike/like to match strings but we loose the interest of having full text search match in the first place