protected Cursor getThreadedMessages(
String accountUuid,
final String[] projection,
final String selection,
final String[] selectionArgs,
final String sortOrder) {
Account account = getAccount(accountUuid);
LockableDatabase database = getDatabase(account);
try {
return database.execute(
false,
new DbCallback<Cursor>() {
@Override
public Cursor doDbWork(SQLiteDatabase db)
throws WrappedException, UnavailableStorageException {
StringBuilder query = new StringBuilder();
query.append("SELECT ");
boolean first = true;
for (String columnName : projection) {
if (!first) {
query.append(",");
} else {
first = false;
}
if (MessageColumns.DATE.equals(columnName)) {
query.append("MAX(m.date) AS " + MessageColumns.DATE);
} else if (MessageColumns.THREAD_COUNT.equals(columnName)) {
query.append("COUNT(h.id) AS " + MessageColumns.THREAD_COUNT);
} else if (SpecialColumns.FOLDER_NAME.equals(columnName)) {
query.append(
"f." + SpecialColumns.FOLDER_NAME + " AS " + SpecialColumns.FOLDER_NAME);
} else if (SpecialColumns.INTEGRATE.equals(columnName)) {
query.append("f." + SpecialColumns.INTEGRATE + " AS " + SpecialColumns.INTEGRATE);
} else {
query.append("m.");
query.append(columnName);
query.append(" AS ");
query.append(columnName);
}
}
query.append(
" FROM messages h JOIN messages m "
+ "ON (h.id = m.thread_root OR h.id = m.id) ");
// TODO: check projection and selection for folder columns
if (Utility.arrayContains(projection, SpecialColumns.FOLDER_NAME)) {
query.append("LEFT JOIN folders f ON (m.folder_id = f.id) ");
}
query.append(
"WHERE "
+ "(h.deleted = 0 AND m.deleted = 0 AND "
+ "(m.empty IS NULL OR m.empty != 1) AND "
+ "h.thread_root IS NULL) ");
if (!StringUtils.isNullOrEmpty(selection)) {
query.append("AND (");
query.append(
SqlQueryBuilder.addPrefixToSelection(MESSAGES_COLUMNS, "h.", selection));
query.append(") ");
}
query.append("GROUP BY h.id");
if (!StringUtils.isNullOrEmpty(sortOrder)) {
query.append(" ORDER BY ");
query.append(
SqlQueryBuilder.addPrefixToSelection(MESSAGES_COLUMNS, "m.", sortOrder));
}
return db.rawQuery(query.toString(), selectionArgs);
}
});
} catch (UnavailableStorageException e) {
throw new RuntimeException("Storage not available", e);
}
}
protected Cursor getMessages(
String accountUuid,
final String[] projection,
final String selection,
final String[] selectionArgs,
final String sortOrder) {
Account account = getAccount(accountUuid);
LockableDatabase database = getDatabase(account);
try {
return database.execute(
false,
new DbCallback<Cursor>() {
@Override
public Cursor doDbWork(SQLiteDatabase db)
throws WrappedException, UnavailableStorageException {
String where;
if (StringUtils.isNullOrEmpty(selection)) {
where =
InternalMessageColumns.DELETED
+ "=0 AND ("
+ InternalMessageColumns.EMPTY
+ " IS NULL OR "
+ InternalMessageColumns.EMPTY
+ "!=1)";
} else {
where =
"("
+ selection
+ ") AND "
+ InternalMessageColumns.DELETED
+ "=0 AND ("
+ InternalMessageColumns.EMPTY
+ " IS NULL OR "
+ InternalMessageColumns.EMPTY
+ "!=1)";
}
final Cursor cursor;
// TODO: check projection and selection for folder columns
if (Utility.arrayContains(projection, SpecialColumns.FOLDER_NAME)) {
StringBuilder query = new StringBuilder();
query.append("SELECT ");
boolean first = true;
for (String columnName : projection) {
if (!first) {
query.append(",");
} else {
first = false;
}
if (MessageColumns.ID.equals(columnName)) {
query.append("m.");
query.append(MessageColumns.ID);
query.append(" AS ");
query.append(MessageColumns.ID);
} else {
query.append(columnName);
}
}
query.append(
" FROM messages m "
+ "LEFT JOIN folders f ON (m.folder_id = f.id) "
+ "WHERE ");
query.append(SqlQueryBuilder.addPrefixToSelection(MESSAGES_COLUMNS, "m.", where));
query.append(" ORDER BY ");
query.append(
SqlQueryBuilder.addPrefixToSelection(MESSAGES_COLUMNS, "m.", sortOrder));
cursor = db.rawQuery(query.toString(), selectionArgs);
} else {
cursor =
db.query(
MESSAGES_TABLE, projection, where, selectionArgs, null, null, sortOrder);
}
return cursor;
}
});
} catch (UnavailableStorageException e) {
throw new RuntimeException("Storage not available", e);
}
}