From 64d69a2f7f729949b9236489ab7c4ce9f9aaea86 Mon Sep 17 00:00:00 2001
From: Damyan Ivanov <dam+mobileledger@ktnx.net>
Date: Sun, 3 May 2020 11:53:26 +0300
Subject: [PATCH] faster description pop-up

replace surrogate pop-up cursor with a suitable "live" SQLite cursor

the problem with the surrogate is that it loads all matches and /then/
hands off control to the UI

the live cursor is populated incrementally and is very fast
---
 .../net/ktnx/mobileledger/utils/MLDB.java     | 46 +++++++------------
 1 file changed, 16 insertions(+), 30 deletions(-)

diff --git a/app/src/main/java/net/ktnx/mobileledger/utils/MLDB.java b/app/src/main/java/net/ktnx/mobileledger/utils/MLDB.java
index ab792ae4..07aea52b 100644
--- a/app/src/main/java/net/ktnx/mobileledger/utils/MLDB.java
+++ b/app/src/main/java/net/ktnx/mobileledger/utils/MLDB.java
@@ -20,15 +20,15 @@ package net.ktnx.mobileledger.utils;
 import android.annotation.TargetApi;
 import android.content.Context;
 import android.database.Cursor;
-import android.database.MatrixCursor;
 import android.database.sqlite.SQLiteDatabase;
 import android.os.AsyncTask;
 import android.os.Build;
-import android.provider.FontsContract;
 import android.widget.AutoCompleteTextView;
 import android.widget.FilterQueryProvider;
 import android.widget.SimpleCursorAdapter;
 
+import androidx.annotation.NonNull;
+
 import net.ktnx.mobileledger.App;
 import net.ktnx.mobileledger.async.DbOpQueue;
 import net.ktnx.mobileledger.async.DescriptionSelectedCallback;
@@ -37,8 +37,6 @@ import net.ktnx.mobileledger.model.MobileLedgerProfile;
 
 import org.jetbrains.annotations.NonNls;
 
-import java.util.Locale;
-
 import static net.ktnx.mobileledger.utils.Logger.debug;
 
 public final class MLDB {
@@ -157,46 +155,34 @@ public final class MLDB {
 
             String str = constraint.toString().toUpperCase();
             debug("autocompletion", "Looking for " + str);
-            String[] col_names = {FontsContract.Columns._ID, field};
-            MatrixCursor c = new MatrixCursor(col_names);
 
             String sql;
             String[] params;
             if (profileSpecific) {
                 MobileLedgerProfile p = (profile == null) ? Data.profile.getValue() : profile;
                 if (p == null) throw new AssertionError();
-                sql = String.format("SELECT %s as a, case when %s_upper LIKE ?||'%%' then 1 " +
-                                    "WHEN %s_upper LIKE '%%:'||?||'%%' then 2 " +
-                                    "WHEN %s_upper LIKE '%% '||?||'%%' then 3 else 9 end " +
-                                    "FROM %s " +
-                                    "WHERE profile=? AND %s_upper LIKE '%%'||?||'%%' " +
-                                    "ORDER BY 2, 1;", field, field, field, field, table, field);
+                sql = String.format(
+                        "SELECT rowid as _id, %s, CASE WHEN %s_upper LIKE ?||'%%' THEN 1 " +
+                        "WHEN %s_upper LIKE '%%:'||?||'%%' then 2 " +
+                        "WHEN %s_upper LIKE '%% '||?||'%%' THEN 3 " + "ELSE 9 END " + "FROM %s " +
+                        "WHERE profile=? AND %s_upper LIKE '%%'||?||'%%' " +
+                        "ORDER BY 3, %s_upper, 1;", field, field, field, field, table, field,
+                        field);
                 params = new String[]{str, str, str, p.getUuid(), str};
             }
             else {
-                sql = String.format("SELECT %s as a, case when %s_upper LIKE ?||'%%' then 1 " +
-                                    "WHEN %s_upper LIKE '%%:'||?||'%%' then 2 " +
-                                    "WHEN %s_upper LIKE '%% '||?||'%%' then 3 " + "else 9 end " +
-                                    "FROM %s " + "WHERE %s_upper LIKE '%%'||?||'%%' " +
-                                    "ORDER BY 2, 1;", field, field, field, field, table, field);
+                sql = String.format(
+                        "SELECT rowid as _id, %s, CASE WHEN %s_upper LIKE ?||'%%' THEN 1 " +
+                        "WHEN %s_upper LIKE '%%:'||?||'%%' THEN 2 " +
+                        "WHEN %s_upper LIKE '%% '||?||'%%' THEN 3 " + "ELSE 9 END " + "FROM %s " +
+                        "WHERE %s_upper LIKE '%%'||?||'%%' " + "ORDER BY 3, %s_upper, 1;", field,
+                        field, field, field, table, field, field);
                 params = new String[]{str, str, str, str};
             }
             debug("autocompletion", sql);
             SQLiteDatabase db = App.getDatabase();
 
-            try (Cursor matches = db.rawQuery(sql, params)) {
-                int i = 0;
-                while (matches.moveToNext()) {
-                    String match = matches.getString(0);
-                    int order = matches.getInt(1);
-                    debug("autocompletion",
-                            String.format(Locale.ENGLISH, "match: %s |%d", match, order));
-                    c.newRow().add(i++).add(match);
-                }
-            }
-
-            return c;
-
+            return db.rawQuery(sql, params);
         };
 
         adapter.setFilterQueryProvider(provider);
-- 
2.39.2