[Android] FTS3 virtual table to speed up search

[Geoffrey Métais]

vlc-ports/android | branch: master | Geoffrey Métais <geoffrey.metais at gmail.com> | Thu Mar  5 11:22:06 2015 +0100| [6e1976d54474d4dda4e450dfc5da9b70d794ddce] | committer: Geoffrey Métais

FTS3 virtual table to speed up search

> http://git.videolan.org/gitweb.cgi/vlc-ports/android.git/?a=commit;h=6e1976d54474d4dda4e450dfc5da9b70d794ddce
---

 .../src/org/videolan/vlc/MediaDatabase.java        |   47 +++++++++++++-------
 vlc-android/src/org/videolan/vlc/MediaLibrary.java |    4 +-
 .../src/org/videolan/vlc/gui/MainActivity.java     |    2 +-
 3 files changed, 35 insertions(+), 18 deletions(-)

diff --git a/vlc-android/src/org/videolan/vlc/MediaDatabase.java b/vlc-android/src/org/videolan/vlc/MediaDatabase.java
index 375477c..eb50962 100644
--- a/vlc-android/src/org/videolan/vlc/MediaDatabase.java
+++ b/vlc-android/src/org/videolan/vlc/MediaDatabase.java
@@ -50,13 +50,14 @@ public class MediaDatabase {
 
     private SQLiteDatabase mDb;
     private static final String DB_NAME = "vlc_database";
-    private static final int DB_VERSION = 16;
+    private static final int DB_VERSION = 17;
     private static final int CHUNK_SIZE = 50;
 
     private static final String DIR_TABLE_NAME = "directories_table";
     private static final String DIR_ROW_PATH = "path";
 
     private static final String MEDIA_TABLE_NAME = "media_table";
+    private static final String MEDIA_VIRTUAL_TABLE_NAME = "media_table_fts";
     public static final String MEDIA_LOCATION = "_id"; //standard key for primary key, needed for search suggestions
     private static final String MEDIA_TIME = "time";
     private static final String MEDIA_LENGTH = "length";
@@ -185,6 +186,29 @@ public class MediaDatabase {
                     + MEDIA_DISCNUMBER + " INTEGER"
                     + ");";
             db.execSQL(query);
+            db.execSQL("PRAGMA recursive_triggers='ON'"); //Needed for delete trigger
+            query = "CREATE VIRTUAL TABLE "
+                    + MEDIA_VIRTUAL_TABLE_NAME + " USING FTS3 ("
+                    + MEDIA_LOCATION + ", "
+                    + MEDIA_TITLE + ", "
+                    + MEDIA_ARTIST + ", "
+                    + MEDIA_GENRE + ", "
+                    + MEDIA_ALBUM + ", "
+                    + MEDIA_ALBUMARTIST
+                    + ");";
+            db.execSQL(query);
+            query = " CREATE TRIGGER media_insert_trigger AFTER INSERT ON "+
+                    MEDIA_TABLE_NAME+ " BEGIN "+
+                        "INSERT INTO "+MEDIA_VIRTUAL_TABLE_NAME+" ("+MEDIA_LOCATION+", "+MEDIA_TITLE+
+                        ", "+MEDIA_ARTIST+", "+MEDIA_GENRE+", "+MEDIA_ALBUM+", "+MEDIA_ALBUMARTIST+" )"+
+                        " VALUES (new."+MEDIA_LOCATION+", new."+MEDIA_TITLE+", new."+MEDIA_ARTIST+
+                        ", new."+MEDIA_GENRE+", new."+MEDIA_ALBUM+", new."+MEDIA_ALBUMARTIST+
+                        "); END;";
+            db.execSQL(query);
+            query = " CREATE TRIGGER media_delete_trigger AFTER DELETE ON "+MEDIA_TABLE_NAME+ " BEGIN "+
+                        "DELETE FROM "+MEDIA_VIRTUAL_TABLE_NAME+" WHERE "+MEDIA_LOCATION+" = old."+MEDIA_LOCATION+";"+
+                        " END;";
+            db.execSQL(query);
         }
 
         private void createPlaylistTablesQuery(SQLiteDatabase db) {
@@ -571,27 +595,20 @@ public class MediaDatabase {
         return files;
     }
 
-    public synchronized Cursor queryMedia(String query, int type){
-        String[] queryColumns = new String[]{MEDIA_LOCATION, MEDIA_TITLE, MEDIA_ALBUM, MEDIA_ARTIST, MEDIA_TYPE};
-        String queryString = MEDIA_TITLE+" LIKE ? OR "+MEDIA_ALBUM+" LIKE ? OR "+MEDIA_ARTIST+" LIKE ?";
-        String [] queryArgs;
-        if (type != MediaWrapper.TYPE_ALL) {
-            queryString = "( " + queryString + " ) AND " + MEDIA_TYPE + "=?";
-            queryArgs = new String[]{"%"+query+"%", "%"+query+"%", "%"+query+"%", String.valueOf(type)};
-        } else
-            queryArgs = new String[]{"%"+query+"%", "%"+query+"%", "%"+query+"%"};
-
-        return mDb.query(MEDIA_TABLE_NAME, queryColumns, queryString, queryArgs, null, null, null, null);
+    public synchronized Cursor queryMedia(String query){
+        String[] queryColumns = new String[]{MEDIA_LOCATION, MEDIA_TITLE};
+        return mDb.query(MEDIA_VIRTUAL_TABLE_NAME, queryColumns, MEDIA_VIRTUAL_TABLE_NAME+" MATCH ?",
+                new String[]{query + "*"}, null, null, null, null);
     }
 
-    public synchronized ArrayList<String> searchMedia(String filter, int type){
+    public synchronized ArrayList<String> searchMedia(String filter){
 
         ArrayList<String> mediaList = new ArrayList<String>();
-        Cursor cursor = queryMedia(filter, type);
+        Cursor cursor = queryMedia(filter);
         if (cursor.moveToFirst()){
             do {
                 mediaList.add(cursor.getString(0));
-            }while (cursor.moveToNext());
+            } while (cursor.moveToNext());
         }
         cursor.close();
         return mediaList;
diff --git a/vlc-android/src/org/videolan/vlc/MediaLibrary.java b/vlc-android/src/org/videolan/vlc/MediaLibrary.java
index 307323b..6a7948c 100644
--- a/vlc-android/src/org/videolan/vlc/MediaLibrary.java
+++ b/vlc-android/src/org/videolan/vlc/MediaLibrary.java
@@ -134,9 +134,9 @@ public class MediaLibrary {
         mUpdateHandler.remove(handler);
     }
 
-    public ArrayList<MediaWrapper> searchMedia(String query, int type){
+    public ArrayList<MediaWrapper> searchMedia(String query){
         ArrayList<MediaWrapper> mediaList = new ArrayList<MediaWrapper>();
-        ArrayList<String> pathList = MediaDatabase.getInstance().searchMedia(query, type);
+        ArrayList<String> pathList = MediaDatabase.getInstance().searchMedia(query);
         if (!pathList.isEmpty()){
             for (String path : pathList) {
                 mediaList.add(getMediaItem(path));
diff --git a/vlc-android/src/org/videolan/vlc/gui/MainActivity.java b/vlc-android/src/org/videolan/vlc/gui/MainActivity.java
index 164309d..ed08ff2 100644
--- a/vlc-android/src/org/videolan/vlc/gui/MainActivity.java
+++ b/vlc-android/src/org/videolan/vlc/gui/MainActivity.java
@@ -835,7 +835,7 @@ public class MainActivity extends ActionBarActivity implements OnItemClickListen
     public boolean onQueryTextChange(String newText) {
         if (newText.length() < 3)
             return false;
-        Cursor cursor = MediaDatabase.getInstance().queryMedia(newText, MediaWrapper.TYPE_ALL);
+        Cursor cursor = MediaDatabase.getInstance().queryMedia(newText);
         mSearchView.getSuggestionsAdapter().changeCursor(cursor);
         return true;
     }