public void deleteThread(SQLiteDatabase db, String thread) {
String jid = Message.getUserId(thread);
execSQL(
db,
"delete from " + TABLE_CHAT + " where FROMJID = ? or TOJID = ?;",
new Object[] {jid, jid});
}
public void read(SQLiteDatabase db, String fromJid) {
try {
db.execSQL(
"UPDATE " + TABLE_CHAT + " set STATE = ? where FROMJID = ? and STATE = ?",
new Object[] {Message.READ, Message.getUserId(fromJid), Message.UNREAD});
} catch (SQLException e) {
Log.e(TAG, "Error adding new message:" + e.getMessage(), e);
}
}
public List<Message> getMessages(
SQLiteDatabase db, String from, String to, boolean both, int limit, int offset) {
List<Message> list = new ArrayList<Message>();
try {
String fromJid = from.split("@")[0];
String toJid = to.split("@")[0];
String sql =
"SELECT * FROM "
+ TABLE_CHAT
+ " where (FROMJID = '"
+ fromJid
+ "' and TOJID = '"
+ toJid
+ "')";
if (both) {
sql += " OR (FROMJID = '" + toJid + "' and TOJID = '" + fromJid + "')";
}
sql += " order by TIME DESC limit " + limit + " offset " + offset + ";";
Cursor cursor = db.rawQuery(sql, null);
int count = cursor.getCount();
if (count > 0) {
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
// "(ID INTEGER PRIMARY KEY,FROM TEXT,TO TEXT,TIME LONG,TEXT TEXT, ATTACHID TEXT,
// ATTACHTYPE INTEGER, STATE INTEGER);";
Message msg = new Message(cursor.getString(2), Message.MSG_TYPE_CHAT);
msg.setId(cursor.getInt(0));
msg.setFrom(cursor.getString(1));
msg.setBody(cursor.getString(4));
msg.setTimestamp(new Date(cursor.getLong(3)));
msg.setState(cursor.getInt(7));
msg.setExtraType(cursor.getString(8));
msg.setExtraJson(cursor.getString(9));
msg.setSubject(cursor.getString(10));
list.add(msg);
cursor.moveToNext();
}
cursor.close();
return list;
} else {
cursor.close();
}
} catch (SQLException e) {
Log.e(TAG, "Error getRecords" + e.getMessage());
}
return list;
}
public int getUnreadCount(SQLiteDatabase db, String fromJid) {
int unread = 0;
try {
Cursor cursor =
db.rawQuery(
"SELECT count(ID) FROM " + TABLE_CHAT + " where STATE = ? AND FROMJID = ?",
new String[] {String.valueOf(Message.UNREAD), Message.getUserId(fromJid)});
int count = cursor.getCount();
if (count > 0 && cursor.moveToFirst()) {
unread = cursor.getInt(0);
}
cursor.close();
} catch (SQLException e) {
Log.e(TAG, "Error adding new message:" + e.getMessage(), e);
}
return unread;
}
public void addMessage(SQLiteDatabase db, Message message) {
try {
// ID INTEGER PRIMARY KEY,FROMJID TEXT,TOJID TEXT,TIME LONG,TEXT TEXT, ATTACHID TEXT,
// ATTACHTYPE INTEGER, STATE INTEGER, EXTRA_TYPE TEXT, EXTRA_JSON TEXT, SUBJECT TEXT
String fromJid = message.getFrom().split("@")[0];
String toJid = message.getTo().split("@")[0];
if (message.getId() == 0) {
db.execSQL(
"INSERT INTO "
+ TABLE_CHAT
+ "(FROMJID, TOJID, TIME, TEXT, STATE, EXTRA_TYPE, EXTRA_JSON, SUBJECT) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
new Object[] {
fromJid,
toJid,
message.getTimestamp().getTime(),
message.getBody(),
message.getState(),
message.getExtraType(),
message.getExtraJson(),
message.getSubject()
});
} else {
db.execSQL(
"INSERT INTO "
+ TABLE_CHAT
+ "(ID, FROMJID, TOJID, TIME, TEXT, STATE, EXTRA_TYPE, EXTRA_JSON, SUBJECT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
new Object[] {
message.getId(),
fromJid,
toJid,
message.getTimestamp().getTime(),
message.getBody(),
message.getState(),
message.getExtraType(),
message.getExtraJson(),
message.getSubject()
});
}
} catch (SQLException e) {
Log.e(TAG, "Error adding new message:" + e.getMessage(), e);
}
}
public List<Message> getThreads(
SQLiteDatabase db, int myUserId, boolean both, int limit, int offset) {
List<Message> list = new ArrayList<Message>();
String myId = String.valueOf(myUserId);
try {
String sql =
"(select max(id) as latest_id from "
+ TABLE_CHAT
+ " group by max(FROMJID, TOJID), "
+ "min(FROMJID, TOJID) having (FROMJID = '"
+ myId
+ "' or TOJID = '"
+ myId
+ "' "
+ ")order by latest_id desc limit "
+ limit
+ " offset "
+ offset
+ ") AS threads";
sql =
"select * from "
+ TABLE_CHAT
+ ", "
+ sql
+ " where "
+ TABLE_CHAT
+ ".id = threads.latest_id;";
Cursor cursor = db.rawQuery(sql, null);
int count = cursor.getCount();
if (count > 0) {
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
// "(ID INTEGER PRIMARY KEY,FROM TEXT,TO TEXT,TIME LONG,TEXT TEXT, ATTACHID TEXT,
// ATTACHTYPE INTEGER, STATE INTEGER);";
Message msg = new Message(cursor.getString(2), Message.MSG_TYPE_CHAT);
msg.setFrom(cursor.getString(1));
msg.setBody(cursor.getString(4));
msg.setTimestamp(new Date(cursor.getLong(3)));
msg.setState(cursor.getInt(7));
msg.setExtraType(cursor.getString(8));
msg.setExtraJson(cursor.getString(9));
msg.setSubject(cursor.getString(10));
msg.setThread(msg.getFromUserId().equals(myId) ? msg.getToUserId() : msg.getFromUserId());
list.add(msg);
cursor.moveToNext();
}
cursor.close();
return list;
} else {
cursor.close();
}
} catch (SQLException e) {
Log.e(TAG, "Error getRecords" + e.getMessage());
}
return list;
}