aboutsummaryrefslogtreecommitdiffstats
path: root/src
diff options
context:
space:
mode:
authorChristian Schneppe <christian@pix-art.de>2018-05-02 21:43:41 +0200
committerChristian Schneppe <christian@pix-art.de>2018-05-02 21:43:41 +0200
commit536106b0793495a3635d641c78cedf65ec1723f7 (patch)
treef71702f4257bf9e2e3fd7b0c1dcf264e30095a53 /src
parentec81d7b2e1c428069bfb11c861b76b36aad23fe7 (diff)
create fts table
Diffstat (limited to 'src')
-rw-r--r--src/main/java/de/pixart/messenger/persistance/DatabaseBackend.java18
1 files changed, 16 insertions, 2 deletions
diff --git a/src/main/java/de/pixart/messenger/persistance/DatabaseBackend.java b/src/main/java/de/pixart/messenger/persistance/DatabaseBackend.java
index b407de339..16f0f6908 100644
--- a/src/main/java/de/pixart/messenger/persistance/DatabaseBackend.java
+++ b/src/main/java/de/pixart/messenger/persistance/DatabaseBackend.java
@@ -59,7 +59,7 @@ public class DatabaseBackend extends SQLiteOpenHelper {
private static DatabaseBackend instance = null;
public static final String DATABASE_NAME = "history";
- public static final int DATABASE_VERSION = 41; // = Conversations DATABASE_VERSION + 1
+ public static final int DATABASE_VERSION = 42; // = Conversations DATABASE_VERSION + 1
private static String CREATE_CONTATCS_STATEMENT = "create table "
+ Contact.TABLENAME + "(" + Contact.ACCOUNT + " TEXT, "
@@ -160,6 +160,12 @@ public class DatabaseBackend extends SQLiteOpenHelper {
private static String CREATE_MESSAGE_TIME_INDEX = "create INDEX message_time_index ON " + Message.TABLENAME + "(" + Message.TIME_SENT + ")";
private static String CREATE_MESSAGE_CONVERSATION_INDEX = "create INDEX message_conversation_index ON " + Message.TABLENAME + "(" + Message.CONVERSATION + ")";
+ private static String CREATE_MESSAGE_INDEX_TABLE = "CREATE VIRTUAL TABLE messages_index USING FTS4(uuid, body)";
+ private static String CREATE_MESSAGE_INSERT_TRIGGER = "CREATE TRIGGER after_message_insert AFTER INSERT ON " + Message.TABLENAME + " BEGIN INSERT INTO messages_index (uuid,body) VALUES (new.uuid,new.body); END;";
+ private static String CREATE_MESSAGE_UPDATE_TRIGGER = "CREATE TRIGGER after_message_update UPDATE of uuid,body ON " + Message.TABLENAME + " BEGIN update messages_index set body=new.body,uuid=new.uuid WHERE uuid=old.uuid; END;";
+ private static String CREATE_MESSAGE_DELETE_TRIGGER = "CREATE TRIGGER after_message_delete AFTER DELETE ON " + Message.TABLENAME + " BEGIN DELETE from messages_index where uuid=old.uuid; END;";
+ private static String COPY_PREEXISTING_ENTRIES = "INSERT into messages_index(uuid,body) select uuid,body FROM " + Message.TABLENAME + ";";
+
private DatabaseBackend(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@@ -490,6 +496,14 @@ public class DatabaseBackend extends SQLiteOpenHelper {
if (oldVersion < 40 && newVersion >= 40) {
db.execSQL(CREATE_RESOLVER_RESULTS_TABLE);
}
+
+ if (oldVersion < 42 && newVersion >= 42) {
+ db.execSQL(CREATE_MESSAGE_INDEX_TABLE);
+ db.execSQL(CREATE_MESSAGE_INSERT_TRIGGER);
+ db.execSQL(CREATE_MESSAGE_UPDATE_TRIGGER);
+ db.execSQL(CREATE_MESSAGE_DELETE_TRIGGER);
+ db.execSQL(COPY_PREEXISTING_ENTRIES);
+ }
}
private static ContentValues createFingerprintStatusContentValues(FingerprintStatus.Trust trust, boolean active) {
@@ -719,7 +733,7 @@ public class DatabaseBackend extends SQLiteOpenHelper {
public Cursor getMessageSearchCursor(String term) {
SQLiteDatabase db = this.getReadableDatabase();
- String SQL = "SELECT " + Message.TABLENAME + ".*," + Conversation.TABLENAME + '.' + Conversation.CONTACTJID + ',' + Conversation.TABLENAME + '.' + Conversation.ACCOUNT + ',' + Conversation.TABLENAME + '.' + Conversation.MODE + " FROM " + Message.TABLENAME + " join " + Conversation.TABLENAME + " on " + Message.TABLENAME + '.' + Message.CONVERSATION + '=' + Conversation.TABLENAME + '.' + Conversation.UUID + " where " + Message.ENCRYPTION + " NOT IN(" + Message.ENCRYPTION_AXOLOTL_NOT_FOR_THIS_DEVICE + ',' + Message.ENCRYPTION_PGP + ',' + Message.ENCRYPTION_DECRYPTION_FAILED + ") AND " + Message.BODY + " LIKE ? ORDER BY " + Message.TIME_SENT + " DESC limit " + Config.MAX_SEARCH_RESULTS;
+ String SQL = "SELECT " + Message.TABLENAME + ".*," + Conversation.TABLENAME + '.' + Conversation.CONTACTJID + ',' + Conversation.TABLENAME + '.' + Conversation.ACCOUNT + ',' + Conversation.TABLENAME + '.' + Conversation.MODE + " FROM " + Message.TABLENAME + " join " + Conversation.TABLENAME + " on " + Message.TABLENAME + '.' + Message.CONVERSATION + '=' + Conversation.TABLENAME + '.' + Conversation.UUID + " join messages_index ON messages_index.uuid=messages.uuid where " + Message.ENCRYPTION + " NOT IN(" + Message.ENCRYPTION_AXOLOTL_NOT_FOR_THIS_DEVICE + ',' + Message.ENCRYPTION_PGP + ',' + Message.ENCRYPTION_DECRYPTION_FAILED + ") AND messages_index.body MATCH ? ORDER BY " + Message.TIME_SENT + " DESC limit " + Config.MAX_SEARCH_RESULTS;
return db.rawQuery(SQL, new String[]{'%' + term + '%'});
}