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;
 }