Aggregation optimization: email lookup, name lookup, phone lookup
diff --git a/src/com/android/providers/contacts/ContactAggregator.java b/src/com/android/providers/contacts/ContactAggregator.java
index fd25406..1a0a0a0 100644
--- a/src/com/android/providers/contacts/ContactAggregator.java
+++ b/src/com/android/providers/contacts/ContactAggregator.java
@@ -18,7 +18,6 @@
import com.android.providers.contacts.ContactMatcher.MatchScore;
import com.android.providers.contacts.OpenHelper.AggregationExceptionColumns;
-import com.android.providers.contacts.OpenHelper.Clauses;
import com.android.providers.contacts.OpenHelper.ContactsColumns;
import com.android.providers.contacts.OpenHelper.DataColumns;
import com.android.providers.contacts.OpenHelper.MimetypesColumns;
@@ -113,6 +112,16 @@
private static final String[] CONTACT_ID_COLUMN = new String[] { RawContacts._ID };
+ private interface EmailLookupQuery {
+ String TABLE = Tables.DATA_JOIN_RAW_CONTACTS;
+
+ String[] COLUMNS = new String[] {
+ RawContacts.CONTACT_ID
+ };
+
+ int CONTACT_ID = 0;
+ }
+
private static final String[] CONTACT_ID_COLUMNS = new String[]{ RawContacts.CONTACT_ID };
private static final int COL_CONTACT_ID = 0;
@@ -362,9 +371,9 @@
mOpenHelper.setContactId(rawContactId, contactId);
computeAggregateData(db, RawContacts.CONTACT_ID + "=" + contactId, values);
db.update(Tables.CONTACTS, values, Contacts._ID + "=" + contactId, null);
- mOpenHelper.updateContactVisible(contactId);
}
+ mOpenHelper.updateContactVisible(contactId);
updateContactAggregationData(db, rawContactId, candidates, values);
}
@@ -685,9 +694,7 @@
// Yank the last comma
selection.setLength(selection.length() - 1);
- selection.append(") AND ");
- selection.append(RawContacts.CONTACT_ID);
- selection.append(" NOT NULL");
+ selection.append(")");
matchAllCandidates(db, selection.toString(), candidates, matcher, false);
}
@@ -706,7 +713,7 @@
if (!firstLetters.contains(firstLetter)) {
firstLetters.add(firstLetter);
final String selection = "(" + NameLookupColumns.NORMALIZED_NAME + " GLOB '"
- + firstLetter + "*') AND " + RawContacts.CONTACT_ID + " NOT NULL";
+ + firstLetter + "*')";
matchAllCandidates(db, selection, candidates, matcher, true);
}
}
@@ -742,7 +749,7 @@
private void lookupPhoneMatches(SQLiteDatabase db, String phoneNumber, ContactMatcher matcher) {
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
- OpenHelper.buildPhoneLookupQuery(qb, phoneNumber);
+ OpenHelper.buildPhoneLookupQuery(qb, phoneNumber, false /* join mimetypes */);
Cursor c = qb.query(db, CONTACT_ID_COLUMNS,
RawContacts.CONTACT_ID + " NOT NULL", null, null, null, null);
try {
@@ -759,12 +766,15 @@
* Finds exact email matches and updates their match scores.
*/
private void lookupEmailMatches(SQLiteDatabase db, String address, ContactMatcher matcher) {
- Cursor c = db.query(Tables.DATA_JOIN_MIMETYPE_RAW_CONTACTS, CONTACT_ID_COLUMNS,
- Clauses.WHERE_EMAIL_MATCHES + " AND " + RawContacts.CONTACT_ID + " NOT NULL",
- new String[]{address}, null, null, null);
+ long mimetypeId = mOpenHelper.getMimeTypeId(Email.CONTENT_ITEM_TYPE);
+ Cursor c = db.query(EmailLookupQuery.TABLE, EmailLookupQuery.COLUMNS,
+ DataColumns.MIMETYPE_ID + "=" + mimetypeId
+ + " AND " + Email.DATA + "=?"
+ + " AND " + RawContacts.CONTACT_ID + " NOT NULL",
+ new String[] {address}, null, null, null);
try {
while (c.moveToNext()) {
- long contactId = c.getLong(COL_CONTACT_ID);
+ long contactId = c.getLong(EmailLookupQuery.CONTACT_ID);
matcher.updateScoreWithEmailMatch(contactId);
}
} finally {
@@ -824,6 +834,8 @@
c.close();
}
+ db.execSQL("DELETE FROM " + Tables.NAME_LOOKUP + " WHERE "
+ + NameLookupColumns.RAW_CONTACT_ID + "=" + rawContactId + ";");
for (int i = 0; i < candidates.mCount; i++) {
NameMatchCandidate candidate = candidates.mList.get(i);
mOpenHelper.insertNameLookup(rawContactId, candidate.mLookupType, candidate.mName);
diff --git a/src/com/android/providers/contacts/ContactsProvider2.java b/src/com/android/providers/contacts/ContactsProvider2.java
index 39d380f..802b1b8 100644
--- a/src/com/android/providers/contacts/ContactsProvider2.java
+++ b/src/com/android/providers/contacts/ContactsProvider2.java
@@ -23,6 +23,7 @@
import com.android.providers.contacts.OpenHelper.DataColumns;
import com.android.providers.contacts.OpenHelper.GroupsColumns;
import com.android.providers.contacts.OpenHelper.MimetypesColumns;
+import com.android.providers.contacts.OpenHelper.NameLookupColumns;
import com.android.providers.contacts.OpenHelper.PackagesColumns;
import com.android.providers.contacts.OpenHelper.PhoneColumns;
import com.android.providers.contacts.OpenHelper.PhoneLookupColumns;
@@ -1651,6 +1652,11 @@
mDb.delete(Tables.PRESENCE, Presence.RAW_CONTACT_ID + "=" + rawContactId, null);
return mDb.delete(Tables.RAW_CONTACTS, RawContacts._ID + "=" + rawContactId, null);
} else {
+
+ // Clear out data used for aggregation - this deleted contact should not be aggregated
+ mDb.execSQL("DELETE FROM " + Tables.NAME_LOOKUP + " WHERE "
+ + NameLookupColumns.RAW_CONTACT_ID + "=" + rawContactId);
+
mValues.clear();
mValues.put(RawContacts.DELETED, 1);
mValues.put(RawContacts.AGGREGATION_MODE, RawContacts.AGGREGATION_MODE_DISABLED);
@@ -2234,7 +2240,7 @@
}
final String number = uri.getLastPathSegment();
- OpenHelper.buildPhoneLookupQuery(qb, number);
+ OpenHelper.buildPhoneLookupQuery(qb, number, true /* join mimetype */);
qb.setProjectionMap(sDataRawContactsProjectionMap);
break;
}
diff --git a/src/com/android/providers/contacts/OpenHelper.java b/src/com/android/providers/contacts/OpenHelper.java
index 23913e4..05cf678 100644
--- a/src/com/android/providers/contacts/OpenHelper.java
+++ b/src/com/android/providers/contacts/OpenHelper.java
@@ -90,6 +90,9 @@
public static final String DATA_JOIN_MIMETYPES = "data "
+ "LEFT OUTER JOIN mimetypes ON (data.mimetype_id = mimetypes._id)";
+ public static final String DATA_JOIN_RAW_CONTACTS = "data "
+ + "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id)";
+
public static final String DATA_JOIN_MIMETYPE_RAW_CONTACTS = "data "
+ "JOIN mimetypes ON (data.mimetype_id = mimetypes._id) "
+ "JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id)";
@@ -194,11 +197,11 @@
}
public interface Clauses {
- public static final String WHERE_IM_MATCHES = MimetypesColumns.MIMETYPE + "=" + Im.MIMETYPE
- + " AND " + Im.PROTOCOL + "=? AND " + Im.DATA + "=?";
+ public static final String WHERE_IM_MATCHES = MimetypesColumns.MIMETYPE + "='"
+ + Im.CONTENT_ITEM_TYPE + "' AND " + Im.PROTOCOL + "=? AND " + Im.DATA + "=?";
- public static final String WHERE_EMAIL_MATCHES = MimetypesColumns.MIMETYPE + "="
- + Email.MIMETYPE + " AND " + Email.DATA + "=?";
+ public static final String WHERE_EMAIL_MATCHES = MimetypesColumns.MIMETYPE + "='"
+ + Email.CONTENT_ITEM_TYPE + "' AND " + Email.DATA + "=?";
public static final String MIMETYPE_IS_GROUP_MEMBERSHIP = MimetypesColumns.CONCRETE_MIMETYPE
+ "='" + GroupMembership.CONTENT_ITEM_TYPE + "'";
@@ -476,7 +479,7 @@
mActivitiesMimetypeQuery = db.compileStatement("SELECT " + MimetypesColumns.MIMETYPE
+ " FROM " + Tables.ACTIVITIES_JOIN_MIMETYPES + " WHERE " + Tables.ACTIVITIES + "."
+ Activities._ID + "=?");
- mNameLookupInsert = db.compileStatement("INSERT INTO " + Tables.NAME_LOOKUP + "("
+ mNameLookupInsert = db.compileStatement("INSERT OR IGNORE INTO " + Tables.NAME_LOOKUP + "("
+ NameLookupColumns.RAW_CONTACT_ID + "," + NameLookupColumns.NAME_TYPE + ","
+ NameLookupColumns.NORMALIZED_NAME + ") VALUES (?,?,?)");
@@ -572,7 +575,7 @@
Data._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
DataColumns.PACKAGE_ID + " INTEGER REFERENCES package(_id)," +
DataColumns.MIMETYPE_ID + " INTEGER REFERENCES mimetype(_id) NOT NULL," +
- Data.RAW_CONTACT_ID + " INTEGER NOT NULL," +
+ Data.RAW_CONTACT_ID + " INTEGER REFERENCES raw_contacts(_id) NOT NULL," +
Data.IS_PRIMARY + " INTEGER NOT NULL DEFAULT 0," +
Data.IS_SUPER_PRIMARY + " INTEGER NOT NULL DEFAULT 0," +
Data.DATA_VERSION + " INTEGER NOT NULL DEFAULT 0," +
@@ -597,6 +600,18 @@
Data.SYNC4 + " TEXT " +
");");
+ db.execSQL("CREATE INDEX data_raw_contact_id ON " + Tables.DATA + " (" +
+ Data.RAW_CONTACT_ID +
+ ");");
+
+ /**
+ * For email lookup and similar queries.
+ */
+ db.execSQL("CREATE INDEX data_mimetype_data2_index ON " + Tables.DATA + " (" +
+ DataColumns.MIMETYPE_ID + "," +
+ Data.DATA2 +
+ ");");
+
/**
* Automatically delete Data rows when a raw contact is deleted.
*/
@@ -667,11 +682,13 @@
// Private name/nickname table used for lookup
db.execSQL("CREATE TABLE " + Tables.NAME_LOOKUP + " (" +
- NameLookupColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
NameLookupColumns.RAW_CONTACT_ID
+ " INTEGER REFERENCES raw_contacts(_id) NOT NULL," +
- NameLookupColumns.NORMALIZED_NAME + " TEXT," +
- NameLookupColumns.NAME_TYPE + " INTEGER" +
+ NameLookupColumns.NORMALIZED_NAME + " TEXT NOT NULL," +
+ NameLookupColumns.NAME_TYPE + " INTEGER NOT NULL," +
+ "PRIMARY KEY (" + NameLookupColumns.RAW_CONTACT_ID + ", "
+ + NameLookupColumns.NORMALIZED_NAME + ", "
+ + NameLookupColumns.NAME_TYPE + ")" +
");");
db.execSQL("CREATE INDEX name_lookup_index ON " + Tables.NAME_LOOKUP + " (" +
@@ -1214,13 +1231,19 @@
mNameLookupInsert.executeInsert();
}
- public static void buildPhoneLookupQuery(SQLiteQueryBuilder qb, final String number) {
+ public static void buildPhoneLookupQuery(SQLiteQueryBuilder qb, String number,
+ boolean joinWithMimetypes) {
final String normalizedNumber = PhoneNumberUtils.toCallerIDMinMatch(number);
final StringBuilder tables = new StringBuilder();
tables.append(Tables.RAW_CONTACTS + ", (SELECT data_id FROM phone_lookup "
+ "WHERE (phone_lookup.normalized_number GLOB '");
tables.append(normalizedNumber);
- tables.append("*')) AS lookup, " + Tables.DATA_JOIN_MIMETYPES);
+ tables.append("*')) AS lookup, ");
+ if (joinWithMimetypes) {
+ tables.append(Tables.DATA_JOIN_MIMETYPES);
+ } else {
+ tables.append(Tables.DATA);
+ }
qb.setTables(tables.toString());
qb.appendWhere("lookup.data_id=data._id AND data.raw_contact_id=raw_contacts._id AND ");
qb.appendWhere("PHONE_NUMBERS_EQUAL(data." + Phone.NUMBER + ", ");
diff --git a/tests/src/com/android/providers/contacts/SynchronousContactsProvider2.java b/tests/src/com/android/providers/contacts/SynchronousContactsProvider2.java
index f5b42c0..c146774 100644
--- a/tests/src/com/android/providers/contacts/SynchronousContactsProvider2.java
+++ b/tests/src/com/android/providers/contacts/SynchronousContactsProvider2.java
@@ -80,6 +80,7 @@
SQLiteDatabase db = getOpenHelper().getWritableDatabase();
db.execSQL("UPDATE raw_contacts SET contact_id = NULL, aggregation_mode=0;");
db.execSQL("DELETE FROM contacts;");
+ db.execSQL("DELETE FROM name_lookup;");
long rowId =
db.compileStatement("SELECT _id FROM raw_contacts LIMIT 1 OFFSET " + maxContact)
.simpleQueryForLong();