search work
1. introduce an FTS3 indexed table called "words"
2. put all sms messages into the words table (done in the SmsProvider's insert).
3. put all part messages which are plain text into the words table (done in the MmsProvider's insert).
4. put triggers on the delete and update for sms and part and update words appropriately.
5. remove the search recents code (mainly commented out) within the mms app.
6. implement a suggest provider which queries the words table for matches.
7. modify the search activity to use the words table rather than a LIKE clause.
8. modify the search activity to use a regular expression (rather than String.indexOf) for
generating the snippet and highlighting the appropriate words within the snippet. NOTE
that originally I wanted to compute the matching words positions using the sql offsets
function but it doesn't work within the slightly complicated query which is used for
doing the search (two joins and a union, etc.)
9. modify the highlighting code within compose message activity to use regular expressions
rather than indexof to do the highlighting.
10. what else did I forget?
diff --git a/src/com/android/providers/telephony/MmsProvider.java b/src/com/android/providers/telephony/MmsProvider.java
index 74c69f1..c0fa0f3 100644
--- a/src/com/android/providers/telephony/MmsProvider.java
+++ b/src/com/android/providers/telephony/MmsProvider.java
@@ -30,6 +30,7 @@
import android.net.Uri;
import android.os.ParcelFileDescriptor;
import android.provider.BaseColumns;
+import android.provider.Telephony;
import android.provider.Telephony.Mms;
import android.provider.Telephony.MmsSms;
import android.provider.Telephony.Mms.Addr;
@@ -53,6 +54,7 @@
static final String TABLE_PART = "part";
static final String TABLE_RATE = "rate";
static final String TABLE_DRM = "drm";
+ static final String TABLE_WORDS = "words";
@Override
public boolean onCreate() {
@@ -367,10 +369,12 @@
}
String contentType = values.getAsString("ct");
-
+
// text/plain and app application/smil store their "data" inline in the
// table so there's no need to create the file
- if (!"text/plain".equals(contentType) && !"application/smil".equals(contentType)) {
+ boolean plainText = "text/plain".equals(contentType);
+ boolean smilText = "application/smil".equals(contentType);
+ if (!plainText && !smilText) {
// Generate the '_data' field of the part with default
// permission settings.
String path = getContext().getDir("parts", 0).getPath()
@@ -399,6 +403,26 @@
}
res = Uri.parse(res + "/part/" + rowId);
+
+ // Don't use a trigger for updating the words table because of a bug
+ // in FTS3. The bug is such that the call to get the last inserted
+ // row is incorrect.
+ if (plainText) {
+ // Update the words table with a corresponding row. The words table
+ // allows us to search for words quickly, without scanning the whole
+ // table;
+ ContentValues cv = new ContentValues();
+
+ // we're using the row id of the part table row but we're also using ids
+ // from the sms table so this divides the space into two large chunks.
+ // The row ids from the part table start at 2 << 32.
+ cv.put(Telephony.MmsSms.WordsTable.ID, (2 << 32) + rowId);
+ cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, values.getAsString("text"));
+ cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, rowId);
+ cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 2);
+ db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv);
+ }
+
} else if (table.equals(TABLE_RATE)) {
long now = values.getAsLong(Rate.SENT_TIME);
long oneHourAgo = now - 1000 * 60 * 60;
diff --git a/src/com/android/providers/telephony/MmsSmsDatabaseHelper.java b/src/com/android/providers/telephony/MmsSmsDatabaseHelper.java
index 89bdc94..f8875bc 100644
--- a/src/com/android/providers/telephony/MmsSmsDatabaseHelper.java
+++ b/src/com/android/providers/telephony/MmsSmsDatabaseHelper.java
@@ -31,11 +31,13 @@
import com.google.android.mms.pdu.EncodedStringValue;
+import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
+import android.provider.Telephony;
import android.provider.Telephony.Mms;
import android.provider.Telephony.MmsSms;
import android.provider.Telephony.Sms;
@@ -330,6 +332,106 @@
createCommonTables(db);
createCommonTriggers(db);
createMmsTriggers(db);
+ createWordsTables(db);
+ }
+
+ // When upgrading the database we need to populate the words
+ // table with the rows out of sms and part.
+ private void populateWordsTable(SQLiteDatabase db) {
+ final String TABLE_WORDS = "words";
+ {
+ Cursor smsRows = db.query(
+ "sms",
+ new String[] { Sms._ID, Sms.BODY },
+ null,
+ null,
+ null,
+ null,
+ null);
+ try {
+ if (smsRows != null) {
+ smsRows.moveToPosition(-1);
+ ContentValues cv = new ContentValues();
+ while (smsRows.moveToNext()) {
+ cv.clear();
+
+ long id = smsRows.getLong(0); // 0 for Sms._ID
+ String body = smsRows.getString(1); // 1 for Sms.BODY
+
+ cv.put(Telephony.MmsSms.WordsTable.ID, id);
+ cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body);
+ cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id);
+ cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1);
+ db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv);
+ }
+ }
+ } finally {
+ if (smsRows != null) {
+ smsRows.close();
+ }
+ }
+ }
+
+ {
+ Cursor mmsRows = db.query(
+ "part",
+ new String[] { Part._ID, Part.TEXT },
+ "ct = 'text/plain'",
+ null,
+ null,
+ null,
+ null);
+ try {
+ if (mmsRows != null) {
+ mmsRows.moveToPosition(-1);
+ ContentValues cv = new ContentValues();
+ while (mmsRows.moveToNext()) {
+ cv.clear();
+
+ long id = mmsRows.getLong(0); // 0 for Part._ID
+ String body = mmsRows.getString(1); // 1 for Part.TEXT
+
+ cv.put(Telephony.MmsSms.WordsTable.ID, id);
+ cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body);
+ cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id);
+ cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1);
+ db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv);
+ }
+ }
+ } finally {
+ if (mmsRows != null) {
+ mmsRows.close();
+ }
+ }
+ }
+ }
+
+ private void createWordsTables(SQLiteDatabase db) {
+ try {
+ db.execSQL("CREATE VIRTUAL TABLE words USING FTS3 (_id INTEGER PRIMARY KEY, index_text TEXT, source_id INTEGER, table_to_use INTEGER);");
+
+ // monitor the sms table
+ // NOTE don't handle inserts using a trigger because it has an unwanted
+ // side effect: the value returned for the last row ends up being the
+ // id of one of the trigger insert not the original row insert.
+ // Handle inserts manually in the provider.
+ db.execSQL("CREATE TRIGGER sms_words_update AFTER UPDATE ON sms BEGIN UPDATE words " +
+ " SET index_text = NEW.body WHERE (source_id=NEW._id AND table_to_use=1); " +
+ " END;");
+ db.execSQL("CREATE TRIGGER sms_words_delete AFTER DELETE ON sms BEGIN DELETE FROM " +
+ " words WHERE source_id = OLD._id AND table_to_use = 1; END;");
+
+ // monitor the mms table
+ db.execSQL("CREATE TRIGGER mms_words_update AFTER UPDATE ON part BEGIN UPDATE words " +
+ " SET index_text = NEW.text WHERE (source_id=NEW._id AND table_to_use=2); " +
+ " END;");
+ db.execSQL("CREATE TRIGGER mms_words_delete AFTER DELETE ON part BEGIN DELETE FROM " +
+ " words WHERE source_id = OLD._id AND table_to_use = 2; END;");
+
+ populateWordsTable(db);
+ } catch (Exception ex) {
+ Log.e(TAG, "got exception creating words table: " + ex.toString());
+ }
}
private void createMmsTables(SQLiteDatabase db) {
@@ -870,6 +972,22 @@
} finally {
db.endTransaction();
}
+ case 48:
+ if (currentVersion <= 48) {
+ return;
+ }
+
+ db.beginTransaction();
+ try {
+ populateWordsTable(db);
+ db.setTransactionSuccessful();
+ } catch (Throwable ex) {
+ Log.e(TAG, ex.getMessage(), ex);
+ break;
+ } finally {
+ db.endTransaction();
+ }
+
return;
}
diff --git a/src/com/android/providers/telephony/MmsSmsProvider.java b/src/com/android/providers/telephony/MmsSmsProvider.java
index 802951b..84d130d 100644
--- a/src/com/android/providers/telephony/MmsSmsProvider.java
+++ b/src/com/android/providers/telephony/MmsSmsProvider.java
@@ -22,6 +22,7 @@
import java.util.List;
import java.util.Set;
+import android.app.SearchManager;
import android.content.ContentProvider;
import android.content.ContentValues;
import android.content.Context;
@@ -90,8 +91,9 @@
private static final int URI_DRAFT = 12;
private static final int URI_CANONICAL_ADDRESSES = 13;
private static final int URI_SEARCH = 14;
- private static final int URI_FIRST_LOCKED_MESSAGE_ALL = 15;
- private static final int URI_FIRST_LOCKED_MESSAGE_BY_THREAD_ID = 16;
+ private static final int URI_SEARCH_SUGGEST = 15;
+ private static final int URI_FIRST_LOCKED_MESSAGE_ALL = 16;
+ private static final int URI_FIRST_LOCKED_MESSAGE_BY_THREAD_ID = 17;
/**
* the name of the table that is used to store the queue of
@@ -209,6 +211,7 @@
URI_MATCHER.addURI(AUTHORITY, "canonical-addresses", URI_CANONICAL_ADDRESSES);
URI_MATCHER.addURI(AUTHORITY, "search", URI_SEARCH);
+ URI_MATCHER.addURI(AUTHORITY, "searchSuggest", URI_SEARCH_SUGGEST);
// In this pattern, two query parameters may be supplied:
// "protocol" and "message." For example:
@@ -321,7 +324,22 @@
null, null,
sortOrder);
break;
- case URI_SEARCH:
+ case URI_SEARCH_SUGGEST: {
+ String searchString = uri.getQueryParameter("pattern");
+ String query = String.format("SELECT _id, index_text, source_id, table_to_use, offsets(words) FROM words WHERE words MATCH '%s*' LIMIT 50;", searchString);
+ if ( sortOrder != null
+ || selection != null
+ || selectionArgs != null
+ || projection != null) {
+ throw new IllegalArgumentException(
+ "do not specify sortOrder, selection, selectionArgs, or projection" +
+ "with this query");
+ }
+
+ cursor = db.rawQuery(query, null);
+ break;
+ }
+ case URI_SEARCH: {
if ( sortOrder != null
|| selection != null
|| selectionArgs != null
@@ -337,35 +355,45 @@
// using a UNION so we have to have the same number of result columns from
// both queries.
- String searchString = "%" + uri.getQueryParameter("pattern") + "%";
- String smsProjection = "_id,thread_id,address,body,date";
- String mmsProjection = "pdu._id,thread_id,addr.address,part.text as body,pdu.date";
+ String searchString = uri.getQueryParameter("pattern") + "*";
+ String smsProjection = "sms._id as _id,thread_id,address,body,date," +
+ "index_text,words._id";
+ String mmsProjection = "pdu._id,thread_id,addr.address,part.text as " + "" +
+ "body,pdu.date,index_text,words._id";
+
+ // search on the words table but return the rows from the corresponding sms table
String smsQuery = String.format(
- "SELECT %s FROM sms WHERE (address NOTNULL AND body LIKE ?) ",
+ "SELECT %s FROM sms,words WHERE (address NOTNULL AND words MATCH ? " +
+ " AND sms._id=words.source_id AND words.table_to_use=1) ",
smsProjection);
- // TODO consider whether we're really getting the right addr here (for example, if
- // I send a message to a given phone number do I want the search result to
- // show a match on "me" or on that phone number. I suspect the latter.
+ // search on the words table but return the rows from the corresponding parts table
String mmsQuery = String.format(
- "SELECT %s FROM pdu,part,addr WHERE ((part.mid=pdu._id) AND " +
+ "SELECT %s FROM pdu,part,addr,words WHERE ((part.mid=pdu._id) AND " +
"(addr.msg_id=pdu._id) AND " +
"(addr.type=%d) AND " +
"(part.ct='text/plain') AND " +
- "(body like ?))",
+ "(words MATCH ?) AND " +
+ "(part._id = words.source_id) AND " +
+ "(words.table_to_use=2))",
mmsProjection,
PduHeaders.TO);
+ // join the results from sms and part (mms)
String rawQuery = String.format(
"%s UNION %s GROUP BY %s ORDER BY %s",
smsQuery,
mmsQuery,
"thread_id",
"thread_id ASC, date DESC");
-
- cursor = db.rawQuery(rawQuery, new String[] { searchString, searchString });
+ try {
+ cursor = db.rawQuery(rawQuery, new String[] { searchString, searchString });
+ } catch (Exception ex) {
+ Log.e(LOG_TAG, "got exception: " + ex.toString());
+ }
break;
+ }
case URI_PENDING_MSG: {
String protoName = uri.getQueryParameter("protocol");
String msgId = uri.getQueryParameter("message");
diff --git a/src/com/android/providers/telephony/SmsProvider.java b/src/com/android/providers/telephony/SmsProvider.java
index d46e829..57ac256 100644
--- a/src/com/android/providers/telephony/SmsProvider.java
+++ b/src/com/android/providers/telephony/SmsProvider.java
@@ -28,6 +28,7 @@
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.provider.Contacts;
+import android.provider.Telephony;
import android.provider.Telephony.Mms;
import android.provider.Telephony.MmsSms;
import android.provider.Telephony.Sms;
@@ -50,6 +51,7 @@
static final String TABLE_SMS = "sms";
private static final String TABLE_RAW = "raw";
private static final String TABLE_SR_PENDING = "sr_pending";
+ private static final String TABLE_WORDS = "words";
private static final Integer ONE = Integer.valueOf(1);
@@ -486,6 +488,21 @@
}
rowID = db.insert(table, "body", values);
+
+ // Don't use a trigger for updating the words table because of a bug
+ // in FTS3. The bug is such that the call to get the last inserted
+ // row is incorrect.
+ if (table == TABLE_SMS) {
+ // Update the words table with a corresponding row. The words table
+ // allows us to search for words quickly, without scanning the whole
+ // table;
+ ContentValues cv = new ContentValues();
+ cv.put(Telephony.MmsSms.WordsTable.ID, rowID);
+ cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, values.getAsString("body"));
+ cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, rowID);
+ cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1);
+ db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv);
+ }
if (rowID > 0) {
Uri uri = Uri.parse("content://" + table + "/" + rowID);