class UserDatabaseHelper extends SQLiteOpenHelper
{
public static final String UID = "userid";
public static final String NAME = "name";
public static final String GENDER = "gender";
public static final String BIRTHDAY = "birthday";
public static final String SIG = "sig";
public static final String TIME = "time";
public static final String ICON = "icon";
public static final String MSGID = "msgid";
public static final String MYID = "myid";
public static final String MYLON = "mylon";
public static final String MYLAT = "mylat";
public static final String HISLON = "hislon";
public static final String HISLAT = "hislat";
public static final String CONTENT = "content";
public static final String SEND = "issend";
public static final String GPSID = "gpsid";
public static final String URL = "url";
public static final String EMAIL = "email";
public static final String UPLOADED = "uploaded";
public static final String TIME2 = "time2";
public static final String DIST = "dist";
public static final String SUCCESS = "suc";
public static final String IMAGENO = "imageno";
public static final String TABLE_USER = "user";
private static final String[] TABLE_USER_COLUMNS = {UID, NAME, GENDER, BIRTHDAY, SIG, TIME, ICON, URL, EMAIL, MYLON, MYLAT, DIST, IMAGENO};
private static final String[] TABLE_USER_TYPES = {"TEXT", "TEXT", "INTEGER", "TEXT", "TEXT", "INTEGER", "BLOB", "TEXT", "TEXT", "REAL", "REAL", "TEXT", "TEXT"};
public static final String USER_INDEX_UID = "CREATE INDEX " + TABLE_USER + "_index_" + UID + " ON " + TABLE_USER + "(" + UID + ")";
public static final String TABLE_MSG = "msg";
private static final String[] TABLE_MSG_COLUMNS = {MSGID, UID, SEND, MYLON, MYLAT, HISLON, HISLAT, TIME, SUCCESS, CONTENT};
private static final String[] TABLE_MSG_TYPES = {"INTEGER PRIMARY KEY", "TEXT", "INTEGER", "REAL", "REAL", "REAL", "REAL", "INTEGER", "INTEGER", "BLOB"};
public static final String TABLE_UNREAD = "unread";
private static final String[] TABLE_UNREAD_COLUMNS = {UID, MSGID};
private static final String[] TABLE_UNREAD_TYPES = {"TEXT", "INTEGER"};
public static final String TABLE_RECENT = "recent";
private static final String[] TABLE_RECENT_COLUMNS = {UID, MSGID, GENDER, TIME};
private static final String[] TABLE_RECENT_TYPES = {"TEXT", "INTEGER", "INTEGER", "INTEGER"};
public static final String TABLE_GPS = "gps";
private static final String[] TABLE_GPS_COLUMNS = {TIME, GPSID, MYLON, MYLAT, UPLOADED, CONTENT, URL, TIME2};
private static final String[] TABLE_GPS_TYPES = {"INTEGER", "TEXT", "REAL", "REAL", "INTEGER", "TEXT", "TEXT", "INTEGER"};
public static class MsgTable
{
public static final String TABLE = "msg";
public static final String MSGID = "msgid";
public static final String UID = "userid";
public static final String SEND = "issend";
public static final String MYLON = "mylon";
public static final String MYLAT = "mylat";
public static final String HISLON = "hislon";
public static final String HISLAT = "hislat";
public static final String TIME = "time";
public static final String CONTENT = "content";
public static final String SUCCESS = "suc";
public static final String UNREAD = "unread";
public static final String[] COLUMNS = {MSGID, UID, SEND, UNREAD, MYLON, MYLAT, HISLON, HISLAT, TIME, SUCCESS, CONTENT};
public static final String[] TYPES = {"INTEGER PRIMARY KEY", "TEXT", "INTEGER", "INTEGER", "REAL", "REAL", "REAL", "REAL", "INTEGER", "INTEGER", "BLOB"};
public static final String INDEX_UID = "CREATE INDEX " + TABLE + "_index_" + UID + " ON " + TABLE + "(" + UID + ", " + TIME + " DESC)";
public static final String INDEX_UNREAD = "CREATE INDEX " + TABLE + "_index_" + UNREAD + " ON " + TABLE + "(" + UNREAD + ")";
}
public static class MoodTable
{
public static final String TABLE = "mood";
public static final String FID = "fid";
public static final String UID = "uid";
public static final String USERNAME = "username";
public static final String GENDER = "gender";
public static final String HEADURL = "headurl";
public static final String HEADTS = "headts";
public static final String CONTENT = "content";
public static final String IMAGEURL = "imageurl";
public static final String IMAGETS = "imagets";
public static final String TIME = "time";
public static final String LON = "lon";
public static final String LAT = "lat";
public static final String COUNT = "count";
public static final String DIST = "dist";
public static final String SINA = "sina";
public static final String ARROUND = "arround";
public static final String PIC = "pic";
public static final String HEADURL2 = "headurl2";
public static final String POS = "pos";
public static final String UNREAD = "unread";
public static final String LASTTIME = "lasttime";
public static final String[] COLUMNS = {FID, UID, USERNAME, GENDER, HEADURL, HEADTS, CONTENT, IMAGEURL, IMAGETS, TIME,
LON, LAT, COUNT, DIST, SINA, ARROUND, PIC, HEADURL2, POS, UNREAD, LASTTIME};
public static final String[] TYPES = {"TEXT PRIMARY KEY", "TEXT", "TEXT", "INTEGER", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "INTEGER",
"REAL", "REAL", "INTEGER", "INTEGER", "INTEGER", "INTEGER", "BLOB", "TEXT", "INTEGER", "INTEGER", "INTEGER"};
public static final String INDEX_UID = "CREATE INDEX " + TABLE + "_index_" + UID + " ON " + TABLE + "(" + UID + ")";
public static final String INDEX_TIME = "CREATE INDEX " + TABLE + "_index_" + TIME + " ON " + TABLE + "(" + TIME + " DESC)";
public static final String INDEX_FID = "CREATE INDEX " + TABLE + "_index_" + FID + " ON " + TABLE + "(" + FID + ")";
public static final String INDEX_UNREAD = "CREATE INDEX " + TABLE + "_index_" + UNREAD + " ON " + TABLE + "(" + UNREAD + ")";
public static final String INDEX_LASTTIME = "CREATE INDEX " + TABLE + "_index_" + LASTTIME + " ON " + TABLE + "(" + LASTTIME + " DESC)";
}
public static class CommentTable
{
public static final String TABLE = "comment";
/**足迹ID */
public static final String FID = "fid";
/**评论人的ID */
public static final String UID = "uid";
/**评论人的昵称 */
public static final String USERNAME = "username";
/**评论人的头像时间戳*/
public static final String HEADTIME = "headtime";
/**评论人性别*/
public static final String GENDER = "gender";
/**评论人的头像*/
public static final String URL = "url";
/**评论人的头像*/
public static final String URL2 = "url2";
/**评论ID**/
public static final String CID = "cid";
/**评论内容**/
public static final String CONTENT = "content";
/**评论时间**/
public static final String TIME = "time";
public static final String UNREAD = "unread";
public static final String[] COLUMNS = {FID, UID, USERNAME, HEADTIME, GENDER, URL, URL2, CID, CONTENT, TIME, UNREAD};
public static final String[] TYPES = {"TEXT", "TEXT", "TEXT", "TEXT", "INTEGER", "TEXT", "TEXT", "TEXT", "TEXT", "INTEGER", "INTEGER"};
public static final String INDEX_FID = "CREATE INDEX " + TABLE + "_index_" + FID + " ON " + TABLE + "(" + FID + ")";
public static final String INDEX_TIME = "CREATE INDEX " + TABLE + "_index_" + TIME + " ON " + TABLE + "(" + TIME + " DESC)";
public static final String INDEX_UNREAD = "CREATE INDEX " + TABLE + "_index_" + UNREAD + " ON " + TABLE + "(" + UNREAD + ")";
}
public static class LifeLineTable
{
public static final String TABLE = "lifeline";
public static final String TIME = "time";
public static final String COUNT = "cnt";
public static final String DAYS = "days";
public static final String[] COLUMNS = {TIME, COUNT, DAYS};
public static final String[] TYPES = {"INTEGER", "INTEGER", "INTEGER"};
public static final String INDEX_TIME = "CREATE INDEX " + TABLE + "_index_" + TIME + " ON " + TABLE + "(" + TIME + " DESC)";
}
static final int DB_VER = 16;
private static final String TAG = "db";
public UserDatabaseHelper(Context context, String name,
CursorFactory factory, int version)
{
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db)
{
String createTable = null;
createTable = DBUtils.genCreateTableString(TABLE_USER, TABLE_USER_COLUMNS, TABLE_USER_TYPES);
if (createTable != null)
{
db.execSQL(createTable);
//user table index
db.execSQL(USER_INDEX_UID);
}
createTable = DBUtils.genCreateTableString(TABLE_GPS, TABLE_GPS_COLUMNS, TABLE_GPS_TYPES);
if (createTable != null)
{
db.execSQL(createTable);
}
createTable = DBUtils.genCreateTableString(MoodTable.TABLE, MoodTable.COLUMNS, MoodTable.TYPES);
if (createTable != null)
{
db.execSQL(createTable);
db.execSQL(MoodTable.INDEX_FID);
db.execSQL(MoodTable.INDEX_UID);
db.execSQL(MoodTable.INDEX_TIME);
db.execSQL(MoodTable.INDEX_UNREAD);
db.execSQL(MoodTable.INDEX_LASTTIME);
}
createTable = DBUtils.genCreateTableString(CommentTable.TABLE, CommentTable.COLUMNS, CommentTable.TYPES);
if (createTable != null)
{
db.execSQL(createTable);
db.execSQL(CommentTable.INDEX_FID);
db.execSQL(CommentTable.INDEX_TIME);
db.execSQL(CommentTable.INDEX_UNREAD);
}
createTable = DBUtils.genCreateTableString(LifeLineTable.TABLE, LifeLineTable.COLUMNS, LifeLineTable.TYPES);
if (createTable != null)
{
db.execSQL(createTable);
db.execSQL(LifeLineTable.INDEX_TIME);
}
createTable = DBUtils.genCreateTableString(MsgTable.TABLE, MsgTable.COLUMNS, MsgTable.TYPES);
if (createTable != null)
{
db.execSQL(createTable);
db.execSQL(MsgTable.INDEX_UID);
db.execSQL(MsgTable.INDEX_UNREAD);
}
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
Log.d(TAG, "oldVersion " + oldVersion);
if (oldVersion <= 8)
{
updateFrom1To9(db);
}
if (oldVersion <= 9)
{
updateFrom9To10(db);
}
if (oldVersion <= 10)
{
updateFrom10To11(db);
}
if (oldVersion <= 11)
{
//版本12为过渡版本，直接合并到13中，这里不做任何事
}
if (oldVersion <= 12)
{
//版本13为过渡版本，直接合并到14中，这里不做任何事
}
if (oldVersion <= 13)
{
updateFrom11To14(db);
}
if (oldVersion <= 14)
{
updateFrom14To15(db);
}
if (oldVersion <= 15)
{
updateFrom15To16(db);
}
}
//版本16，消息表增加未读列，增加索引, 用户表增加索引
private void updateFrom15To16(SQLiteDatabase db)
{
Log.d(TAG, "updateFrom15To16");
try
{
db.execSQL("ALTER TABLE " + MsgTable.TABLE + " ADD COLUMN " + MsgTable.UNREAD + " INTEGER");
db.execSQL(MsgTable.INDEX_UID);
db.execSQL(MsgTable.INDEX_UNREAD);
//user table index
db.execSQL(USER_INDEX_UID);
}
catch (Exception e)
{
e.printStackTrace();
}
}
//版本15， 新增生命线表
private void updateFrom14To15(SQLiteDatabase db)
{
Log.d(TAG, "updateFrom14To15");
try
{
String createTable = DBUtils.genCreateTableString(LifeLineTable.TABLE, LifeLineTable.COLUMNS, LifeLineTable.TYPES);
if (createTable != null)
{
db.execSQL(createTable);
db.execSQL(LifeLineTable.INDEX_TIME);
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
//版本14，重建mood表,增加索引
private void updateFrom11To14(SQLiteDatabase db)
{
Log.d(TAG, "updateFrom11To14");
try
{
String dropTable = "DROP TABLE " + MoodTable.TABLE;
db.execSQL(dropTable);
}
catch (Exception e)
{
}
String createTable = DBUtils.genCreateTableString(MoodTable.TABLE, MoodTable.COLUMNS, MoodTable.TYPES);
if (createTable != null)
{
db.execSQL(createTable);
db.execSQL(MoodTable.INDEX_UID);
db.execSQL(MoodTable.INDEX_TIME);
db.execSQL(MoodTable.INDEX_FID);
db.execSQL(MoodTable.INDEX_UNREAD);
db.execSQL(MoodTable.INDEX_LASTTIME);
}
}
//版本11，增加comment表
private void updateFrom10To11(SQLiteDatabase db)
{
Log.d(TAG, "updateFrom10To11");
try
{
String createTable = DBUtils.genCreateTableString(CommentTable.TABLE, CommentTable.COLUMNS, CommentTable.TYPES);
if (createTable != null)
{
db.execSQL(createTable);
db.execSQL(CommentTable.INDEX_FID);
db.execSQL(CommentTable.INDEX_TIME);
db.execSQL(CommentTable.INDEX_UNREAD);
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
//版本10增加了索引
private void updateFrom9To10(SQLiteDatabase db)
{
Log.d(TAG, "updateFrom9To10");
try
{
db.execSQL(MoodTable.INDEX_UID);
db.execSQL(MoodTable.INDEX_TIME);
}
catch (Exception e)
{
e.printStackTrace();
}
}
private void updateFrom1To9(SQLiteDatabase db)
{
Log.d(TAG, "updateFrom1To9");
//drop the old table, and create a new one
String dropTable = "DROP TABLE " + TABLE_USER;
db.execSQL(dropTable);
dropTable = "DROP TABLE " + TABLE_MSG;
db.execSQL(dropTable);
dropTable = "DROP TABLE " + TABLE_UNREAD;
db.execSQL(dropTable);
dropTable = "DROP TABLE " + TABLE_RECENT;
db.execSQL(dropTable);
dropTable = "DROP TABLE " + TABLE_GPS;
db.execSQL(dropTable);
try
{
dropTable = "DROP TABLE " + MoodTable.TABLE;
db.execSQL(dropTable);
}
catch (Exception e)
{
}
String createTable = null;
createTable = DBUtils.genCreateTableString(TABLE_USER, TABLE_USER_COLUMNS, TABLE_USER_TYPES);
if (createTable != null)
{
db.execSQL(createTable);
}
createTable = DBUtils.genCreateTableString(TABLE_MSG, TABLE_MSG_COLUMNS, TABLE_MSG_TYPES);
if (createTable != null)
{
db.execSQL(createTable);
}
createTable = DBUtils.genCreateTableString(TABLE_UNREAD, TABLE_UNREAD_COLUMNS, TABLE_UNREAD_TYPES);
if (createTable != null)
{
db.execSQL(createTable);
}
createTable = DBUtils.genCreateTableString(TABLE_RECENT, TABLE_RECENT_COLUMNS, TABLE_RECENT_TYPES);
if (createTable != null)
{
db.execSQL(createTable);
}
createTable = DBUtils.genCreateTableString(TABLE_GPS, TABLE_GPS_COLUMNS, TABLE_GPS_TYPES);
if (createTable != null)
{
db.execSQL(createTable);
}
createTable = DBUtils.genCreateTableString(MoodTable.TABLE, MoodTable.COLUMNS, MoodTable.TYPES);
if (createTable != null)
{
db.execSQL(createTable);
}
}
}
public class UserDatabase
{
private static final String TAG = "userdb";
private static UserDatabase instance = null;
private static Object lockDatabase = new Object();
@SuppressWarnings("unused")
private Context context;
private String userName;
private SQLiteDatabase database;
private DBSecurity security;
public static void test(Context context)
{
UserDatabase db = UserDatabase.create(context, "test");
for (int i = 0; i < 5; i++)
{
UserInfoBean userInfo = new UserInfoBean();
userInfo.userId = "user" + i;
userInfo.nickName = "name" + i;
userInfo.gender = i % 2 == 0;
userInfo.headIcon = null;
db.addUserInfo(userInfo);
}
for (int i = 0; i < 5; i++)
{
UserInfoBean userInfo = db.getUserInfo("user" + i);
if (userInfo != null)
{
Log.d(TAG, userInfo.userId + ", " + userInfo.nickName);
}
}
for (int i = 0; i < 20; i++)
{
MessageBean item = new MessageBean();
item.isSend = i % 2 == 0;
item.message = "content" + i + "你好";
item.messageTime = i;
long msgId = db.addMessageItem("user" + (i % 5), item);
if (msgId % 3 == 0)
{
// db.markMessageAsRead(msgId);
}
}
Log.d(TAG, "total unread " + db.getUnreadCount());
Log.d(TAG, "total unread " + db.getUnreadCount("user0"));
Log.d(TAG, "total unread " + db.getUnreadCount("user1"));
Log.d(TAG, "total unread " + db.getUnreadCount("user2"));
Log.d(TAG, "total unread " + db.getUnreadCount("user3"));
Log.d(TAG, "total unread " + db.getUnreadCount("user4"));
for (int i = 0; i < 5; i++)
{
Log.d(TAG, "total user" + i + ", " + db.getTotalMessageCount("user" + i));
}
for (int i = 0; i < 5; i++)
{
Log.d(TAG, "user" + i);
MessageBean[] items = db.getUnreadMessageItemsDesc("user" + i, 0, 0);
if (items != null)
{
for (int j = 0; j < items.length; j++)
{
Log.d(TAG, items[j].message + ", " + items[j].isSend + ", " + items[j].messageTime);
}
}
}
for (int i = 0; i < 5; i++)
{
MessageBean[] items = db.getMessageItems("user" + i, 0, 0);
if (items != null)
{
for (int j = 0; j < items.length; j++)
{
Log.d(TAG, items[j].message + ", " + items[j].isSend + ", " + items[j].messageTime);
}
}
}
DBRecentMessageItem[] itemsFemale = db.getRecentMessageList(true, 0, 0);
if (itemsFemale != null)
{
for (int i = 0; i < itemsFemale.length; i++)
{
Log.d(TAG, itemsFemale[i].userId + ", " + itemsFemale[i].msgId + ", " + itemsFemale[i].unread + ", " + itemsFemale[i].time);
}
}
DBRecentMessageItem[] itemsMale = db.getRecentMessageList(false, 0, 0);
if (itemsMale != null)
{
for (int i = 0; i < itemsMale.length; i++)
{
Log.d(TAG, itemsMale[i].userId + ", " + itemsMale[i].msgId + ", " + itemsMale[i].unread + ", " + itemsMale[i].time);
}
}
for (int i = 0; i < 5; i++)
{
db.deleteMessageItems("user" + i);
db.deleteUserInfo("user" + i, false);
}
for (int i = 0; i < 5; i++)
{
MessageBean[] items = db.getMessageItems("user" + i, 0, 0);
if (items != null)
{
for (int j = 0; j < items.length; j++)
{
Log.d(TAG, items[j].message + ", " + items[j].isSend);
}
}
}
for (int i = 0; i < 5; i++)
{
UserInfoBean userInfo = db.getUserInfo("user" + i);
if (userInfo != null)
{
Log.d(TAG, userInfo.userId + ", " + userInfo.nickName);
}
}
db.close();
}
public static void testCreateMassMood(final Context context)
{
new Thread()
{
@Override
public void run()
{
UserDatabase db = UserDatabase.create(context, "testMassMood");
Random random = new Random();
MoodBean moodBean = new MoodBean();
for (int i = 0; i < 1000; i++)
{
Log.d(TAG, "begin " + i);
db.database.beginTransaction();
for (int j = 0; j < 100; j++)
{
moodBean.footPrintId = String.valueOf(i * 100 + j);
moodBean.userId = String.valueOf(random.nextInt(1000));
moodBean.moodContent = String.valueOf(i * j);
moodBean.createDate = random.nextInt(100000000);
moodBean.unreadCommentSize = random.nextInt(20) == 8 ? j : 0;
db.addMood(moodBean, true);
}
db.database.setTransactionSuccessful();
db.database.endTransaction();
}
db.close();
}
}.start();
}
public static void testMassMood(Context context)
{
UserDatabase db = UserDatabase.create(context, "testMassMood");
// db.deleteMoodsByUserId("81");
// ArrayList<MoodBean> list = db.getMoodListDescExceptUserId("238", System.currentTimeMillis(), 20);
//
// for (MoodBean moodBean : list)
// {
// Log.d(TAG, moodBean.footPrintId + ", " + moodBean.userId + ", " + moodBean.time);
// }
Log.d(TAG, "getUnreadCommentCount " + db.getUnreadCommentCount("11"));
Log.d(TAG, "getUnreadCommentCount " + db.getUnreadCommentCount("12"));
Log.d(TAG, "getUnreadCommentCount " + db.getUnreadCommentCount("10"));
Log.d(TAG, "getFootCountWithUnread " + db.getFootCountWithUnread("11"));
Log.d(TAG, "getFootCountWithUnread " + db.getFootCountWithUnread("12"));
Log.d(TAG, "getFootCountWithUnread " + db.getFootCountWithUnread("10"));
db.close();
}
public static void testComments(Context context)
{
UserDatabase db = UserDatabase.create(context, "testComments");
db.deleteComments("0");
db.deleteComments("1");
db.deleteComments("2");
db.deleteComments("3");
db.deleteComments("4");
db.deleteComments("5");
db.deleteComments("6");
db.deleteComments("7");
db.deleteComments("8");
db.deleteComments("9");
ArrayList<CommentBean> list = new ArrayList<CommentBean>();
for (int i = 0; i< 1000; i++)
{
CommentBean bean = new CommentBean();
bean.footID = "" + i % 10;
bean.commentContent = "content " + i % 10;
bean.commentTime = i;
list.add(bean);
}
db.addComments(list);
// Log.d(TAG, "should be 1000, result = " + db.getUnreadCommentsCount());
db.getComments("1", 0, 0);
// Log.d(TAG, "should be 900, result = " + db.getUnreadCommentsCount());
db.getComments("3", 0, 0);
// Log.d(TAG, "should be 800, result = " + db.getUnreadCommentsCount());
db.getComments("4", 0, 0);
// Log.d(TAG, "should be 700, result = " + db.getUnreadCommentsCount());
db.close();
}
public static UserDatabase create(Context context, String userName)
{
if ((userName == null) || (userName.equals("")))
{
Log.e(TAG, "userName is empty");
return null;
}
Log.d(TAG, "UserDatabase create " + userName);
if (instance == null)
{
synchronized (lockDatabase)
{
if (instance == null)
{
instance = new UserDatabase(context);
DBUtils.checkFolder(K.USER_DATA_PATH_SD);
try
{
String dbName = null;
if (SystemUtil.isHasSDCard())
{
dbName = K.USER_DATA_PATH_SD + userName + ".db";
}
else
{
dbName = userName + ".db";
}
instance.database = new UserDatabaseHelper(context, dbName, null, UserDatabaseHelper.DB_VER).getWritableDatabase();
instance.userName = userName;
instance.context = context;
instance.initSecurity();
}
catch (SQLException e)
{
Log.e(TAG, e.getMessage());
instance = null;
}
}
}
}
else
{
if (!userName.equals(instance.userName))
{
//the client must close the old user database first
Log.e(TAG, "error: the client must close the old user database first");
return null;
}
}
return instance;
}
public void close()
{
Log.d(TAG, "database closed");
if (instance != null)
{
if (instance.database != null)
{
instance.database.close();
instance.database = null;
}
}
instance = null;
}
private void initSecurity()
{
security = new DBSecurity();
}
private byte[] encrypt(String in)
{
return security.encrypt(in);
}
private String decrypt(byte[] in)
{
return security.decrypt(in);
}
private UserDatabase(Context context)
{
this.context = context;
}
private String nullToEmpty(String in)
{
return in == null ? "" : in;
}
private long addNewMsg(String hisId, boolean isSend, boolean unread, double myLon, double myLat, double hisLon, double hisLat, long time, String content, boolean sendResult)
{
long row = addMsgItem(hisId, isSend, unread, myLon, myLat, hisLon, hisLat, time, content, sendResult);
return row;
}
private long addMsgItem(String hisId, boolean isSend, boolean unread, double myLon, double myLat, double hisLon, double hisLat, long time, String content, boolean sendResult)
{
ContentValues values = new ContentValues();
values.put(MsgTable.UID, hisId);
values.put(MsgTable.SEND, booleanToInt(isSend));
values.put(MsgTable.MYLON, myLon);
values.put(MsgTable.MYLAT, myLat);
values.put(MsgTable.HISLON, hisLon);
values.put(MsgTable.HISLAT, hisLat);
values.put(MsgTable.TIME, time);
values.put(MsgTable.CONTENT, encrypt(content));
values.put(MsgTable.SUCCESS, booleanToInt(sendResult));
values.put(MsgTable.UNREAD, unread);
long row = database.insert(MsgTable.TABLE, null, values);
//we just take the system row id as the message unique id;
return row;
}
private void setMsgSendResult(long msgId, boolean sendResult)
{
ContentValues values = new ContentValues();
values.put(MsgTable.SUCCESS, sendResult);
String whereClause = MsgTable.MSGID + "=?";
String[] whereArgs = new String[]{String.valueOf(msgId)};
database.update(MsgTable.TABLE, values, whereClause, whereArgs);
}
private void deleteMySendMsgItemByMsgId(long msgId)
{
String where = MsgTable.MSGID + "=?";
String[] args = new String[]{String.valueOf(msgId)};
database.delete(MsgTable.TABLE, where, args);
}
//delete all messages of one user
private void deleteMsgItems(String userId)
{
String where = MsgTable.UID + "=?";
String[] args = new String[]{userId};
database.delete(MsgTable.TABLE, where, args);
}
private void deleteDBUserInfo(String userId, boolean deleteAllMsgs)
{
String where = UserDatabaseHelper.UID + "=?";
String[] args = new String[]{userId};
database.delete(UserDatabaseHelper.TABLE_USER, where, args);
if (deleteAllMsgs)
{
deleteMsgItems(userId);
}
}
private long addDBUserInfo(String userId, String name, boolean female, String birthday, String sig,
long time, byte[] icon, String url, String email, double lon, double lat, String dist, String imageNO)
{
ContentValues values = new ContentValues();
String where = UserDatabaseHelper.UID + "=?";
String[] args = new String[]{userId};
Cursor cursor = database.query(UserDatabaseHelper.TABLE_USER, null, where, args, null, null, null);
long row = 0;
if (cursor.moveToNext())
{
//already exist, replace the old one
Log.d(TAG, "already exist, replace the old one");
int nameIndex = cursor.getColumnIndex(UserDatabaseHelper.NAME);
int genderIndex = cursor.getColumnIndex(UserDatabaseHelper.GENDER);
int birthdayIndex = cursor.getColumnIndex(UserDatabaseHelper.BIRTHDAY);
int sigIndex = cursor.getColumnIndex(UserDatabaseHelper.SIG);
int timeIndex = cursor.getColumnIndex(UserDatabaseHelper.TIME);
int urlIndex = cursor.getColumnIndex(UserDatabaseHelper.URL);
int emailIndex = cursor.getColumnIndex(UserDatabaseHelper.EMAIL);
int lonIndex = cursor.getColumnIndex(UserDatabaseHelper.MYLON);
int latIndex = cursor.getColumnIndex(UserDatabaseHelper.MYLAT);
int distIndex = cursor.getColumnIndex(UserDatabaseHelper.DIST);
int imageNOIndex = cursor.getColumnIndex(UserDatabaseHelper.IMAGENO);
String oldName = cursor.getString(nameIndex);
boolean oldGender = intToBoolean(cursor.getInt(genderIndex));
String oldBirthday = cursor.getString(birthdayIndex);
String oldSig = cursor.getString(sigIndex);
long oldTime = cursor.getLong(timeIndex);
String oldUrl = cursor.getString(urlIndex);
String oldEmail = cursor.getString(emailIndex);
String oldDist = cursor.getString(distIndex);
String imageNo = cursor.getString(imageNOIndex);
if (!TextUtils.equals(oldName, name))
{
values.put(UserDatabaseHelper.NAME, name);
}
if (oldGender != female)
{
values.put(UserDatabaseHelper.GENDER, booleanToInt(female));
}
if (!TextUtils.equals(oldBirthday, birthday))
{
values.put(UserDatabaseHelper.BIRTHDAY, birthday);
}
if (!TextUtils.equals(oldSig, sig))
{
values.put(UserDatabaseHelper.SIG, sig);
}
//if (oldTime != time)
{
values.put(UserDatabaseHelper.TIME, time);
values.put(UserDatabaseHelper.ICON, icon);
values.put(UserDatabaseHelper.IMAGENO, imageNO);
}
if (!TextUtils.equals(oldUrl, url))
{
values.put(UserDatabaseHelper.URL, url);
}
if (!TextUtils.equals(oldEmail, email))
{
values.put(UserDatabaseHelper.EMAIL, email);
}
{
values.put(UserDatabaseHelper.MYLON, lon);
values.put(UserDatabaseHelper.MYLAT, lat);
}
if (!TextUtils.equals(oldDist, dist))
{
values.put(UserDatabaseHelper.DIST, dist);
}
if (values.size() > 0)
{
row = database.update(UserDatabaseHelper.TABLE_USER, values, where, args);
}
}
else
{
values.put(UserDatabaseHelper.UID, userId);
values.put(UserDatabaseHelper.NAME, name);
values.put(UserDatabaseHelper.GENDER, booleanToInt(female));
values.put(UserDatabaseHelper.BIRTHDAY, birthday);
values.put(UserDatabaseHelper.SIG, sig);
values.put(UserDatabaseHelper.TIME, time);
values.put(UserDatabaseHelper.ICON, icon);
values.put(UserDatabaseHelper.URL, url);
values.put(UserDatabaseHelper.EMAIL, email);
values.put(UserDatabaseHelper.MYLON, lon);
values.put(UserDatabaseHelper.MYLAT, lat);
values.put(UserDatabaseHelper.DIST, dist);
values.put(UserDatabaseHelper.IMAGENO, imageNO);
row = database.insert(UserDatabaseHelper.TABLE_USER, null, values);
}
cursor.close();
return row;
}
private UserInfoBean getDBUserInfo(String userId)
{
String where = UserDatabaseHelper.UID + "=?";
String[] args = new String[]{userId};
UserInfoBean userInfo = null;
Cursor cursor = database.query(UserDatabaseHelper.TABLE_USER, null, where, args, null, null, null);
if (cursor.moveToNext())
{
int nameIndex = cursor.getColumnIndex(UserDatabaseHelper.NAME);
int genderIndex = cursor.getColumnIndex(UserDatabaseHelper.GENDER);
int birthdayIndex = cursor.getColumnIndex(UserDatabaseHelper.BIRTHDAY);
int sigIndex = cursor.getColumnIndex(UserDatabaseHelper.SIG);
int timeIndex = cursor.getColumnIndex(UserDatabaseHelper.TIME);
int iconIndex = cursor.getColumnIndex(UserDatabaseHelper.ICON);
int urlIndex = cursor.getColumnIndex(UserDatabaseHelper.URL);
int emailIndex = cursor.getColumnIndex(UserDatabaseHelper.EMAIL);
int lonIndex = cursor.getColumnIndex(UserDatabaseHelper.MYLON);
int latIndex = cursor.getColumnIndex(UserDatabaseHelper.MYLAT);
int distIndex = cursor.getColumnIndex(UserDatabaseHelper.DIST);
int imageNOIndex = cursor.getColumnIndex(UserDatabaseHelper.IMAGENO);
userInfo = new UserInfoBean();
userInfo.userId = userId;
userInfo.nickName = cursor.getString(nameIndex);
userInfo.gender = intToBoolean(cursor.getInt(genderIndex));
userInfo.birthday = cursor.getString(birthdayIndex);
userInfo.userSign = cursor.getString(sigIndex);
//userInfo.publishSignTime = String.valueOf(cursor.getLong(timeIndex));
byte[] temp = cursor.getBlob(iconIndex);
userInfo.headIcon = temp == null ? null : ImageUtil.byteToBitmap(temp);
userInfo.headImgTimeStamp = String.valueOf(cursor.getLong(timeIndex));
userInfo.headImgUrl = cursor.getString(urlIndex);
userInfo.email = cursor.getString(emailIndex);
userInfo.longitude = cursor.getDouble(lonIndex);
userInfo.latitude = cursor.getDouble(latIndex);
userInfo.mileage = cursor.getString(distIndex);
userInfo.headImgNO = cursor.getString(imageNOIndex);
}
cursor.close();
return userInfo;
}
private DBRecentMessageItem[] getRecentMsgOfAll(int start, int count)
{
long startTime = System.currentTimeMillis();
DBRecentMessageItem[] items = null;
String sql =
"SELECT a.*, u." + UserDatabaseHelper.NAME +
" FROM " + MsgTable.TABLE + " a, " +
"(SELECT " + MsgTable.UID + ", MAX(" + MsgTable.TIME + ") tm" +
" FROM " + MsgTable.TABLE +
" GROUP BY " + MsgTable.UID + ") b" +
" LEFT JOIN " + UserDatabaseHelper.TABLE_USER + " u ON (u." + UserDatabaseHelper.UID + "=a." + MsgTable.UID + ") " +
" WHERE a." + MsgTable.MSGID + "=" +
"(SELECT MAX(" + MsgTable.MSGID + ")" +
" FROM " + MsgTable.TABLE +
" WHERE " + MsgTable.UID + "=b." + MsgTable.UID + " AND " + MsgTable.TIME + "=b.tm)" +
" ORDER BY a." + MsgTable.TIME + " DESC";
if (count > 0)
{
sql += " LIMIT " + count + " OFFSET " + start;
}
Cursor cursor = database.rawQuery(sql, null);
int userIdIndex = cursor.getColumnIndex(MsgTable.UID);
int msgIdIndex = cursor.getColumnIndex(MsgTable.MSGID);
int contentIndex = cursor.getColumnIndex(MsgTable.CONTENT);
int timeIndex = cursor.getColumnIndex(MsgTable.TIME);
int nameIndex = cursor.getColumnIndex(UserDatabaseHelper.NAME);
if (cursor.getCount() > 0)
{
items = new DBRecentMessageItem[cursor.getCount()];
int i = 0;
while (cursor.moveToNext())
{
String uid = cursor.getString(userIdIndex);
long mid = cursor.getLong(msgIdIndex);
DBRecentMessageItem item = new DBRecentMessageItem();
item.userId = uid;
item.msgId = mid;
item.content = decrypt(cursor.getBlob(contentIndex));
item.time = cursor.getLong(timeIndex);
item.name = cursor.getString(nameIndex);
item.unread = getUnreadMsgCount(item.userId);
item.icon = null;
items[i++] = item;
}
}
cursor.close();
Log.d(TAG, "getRecentMsgOfAll dt = " + (System.currentTimeMillis() - startTime));
return items;
}
private MessageBean[] getUnreadMsgItems(String userId, long startTime, boolean desc)
{
String where = MsgTable.UID + "=? AND " + MsgTable.TIME + ">=? AND " + MsgTable.UNREAD + "=?";
String[] args = new String[]{userId, String.valueOf(startTime), String.valueOf(booleanToInt(true))};
String sortBy = MsgTable.TIME;
if (desc)
{
sortBy += " DESC";
}
Cursor cursor = database.query(MsgTable.TABLE, null, where, args, null, null, sortBy);
MessageBean[] items = processGetMsgCursor(userId, cursor);
cursor.close();
return items; }
private MessageBean[] getUnreadMsgItems(String userId, int start, int count, boolean desc)
{
String where = MsgTable.UID + "=? AND " + MsgTable.UNREAD + "=?";
String[] args = new String[]{userId, String.valueOf(booleanToInt(true))};
String sortBy = MsgTable.TIME;
if (desc)
{
sortBy += " DESC";
}
if (count > 0)
{
sortBy += " LIMIT " + count + " OFFSET " + start;
}
Cursor cursor = database.query(MsgTable.TABLE, null, where, args, null, null, sortBy);
MessageBean[] items = processGetMsgCursor(userId, cursor);
cursor.close();
return items;
}
private MessageBean[] getMsgItems(String userId, int start, int count, boolean desc)
{
String where = MsgTable.UID + "=?";
String[] args = new String[]{userId};
String sortBy = MsgTable.TIME;
if (desc)
{
sortBy += " DESC";
}
if (count > 0)
{
sortBy += " LIMIT " + count + " OFFSET " + start;
}
Cursor cursor = database.query(MsgTable.TABLE, null, where, args, null, null, sortBy);
MessageBean[] items = processGetMsgCursor(userId, cursor);
cursor.close();
return items;
}
private MessageBean[] processGetMsgCursor(String userId, Cursor cursor)
{
int msgIdIndex = cursor.getColumnIndex(MsgTable.MSGID);
int sendIndex = cursor.getColumnIndex(MsgTable.SEND);
int myLonIndex = cursor.getColumnIndex(MsgTable.MYLON);
int myLatIndex = cursor.getColumnIndex(MsgTable.MYLAT);
int hisLonIndex = cursor.getColumnIndex(MsgTable.HISLON);
int hisLatIndex = cursor.getColumnIndex(MsgTable.HISLAT);
int timeIndex = cursor.getColumnIndex(MsgTable.TIME);
int contentIndex = cursor.getColumnIndex(MsgTable.CONTENT);
int sendResultIndex = cursor.getColumnIndex(MsgTable.SUCCESS);
int unreadIndex = cursor.getColumnIndex(MsgTable.UNREAD);
long msgId = 0;
boolean isSend = false;
double myLon = 0;
double myLat = 0;
double hisLon = 0;
double hisLat = 0;
long time = 0;
boolean sendResult = false;
boolean unread = false;
String content = null;
MessageBean[] items = null;
ArrayList<Long> unreadList = new ArrayList<Long>();
if (cursor.getCount() > 0)
{
items = new MessageBean[cursor.getCount()];
int i = 0;
while (cursor.moveToNext())
{
msgId = cursor.getLong(msgIdIndex);
isSend = intToBoolean(cursor.getInt(sendIndex));
myLon = cursor.getDouble(myLonIndex);
myLat = cursor.getDouble(myLatIndex);
hisLon = cursor.getDouble(hisLonIndex);
hisLat = cursor.getDouble(hisLatIndex);
time = cursor.getLong(timeIndex);
content = decrypt(cursor.getBlob(contentIndex));
sendResult = intToBoolean(cursor.getInt(sendResultIndex));
unread = intToBoolean(cursor.getInt(unreadIndex));
//Log.d(TAG, "msgId = " + msgId + ", isSend = " + isSend);
MessageBean item = new MessageBean();
item.MsgId = msgId;
//item.userId = userId;
item.isSend = isSend;
item.meLongitude = myLon;
item.meLatitude = myLat;
item.longitude = hisLon;
item.latitude = hisLat;
item.messageTime = time;
item.message = content;
item.send_fail = sendResult;
items[i++] = item;
if (unread)
{
unreadList.add(msgId);
}
}
if (unreadList.size() > 0)
{
String where = MsgTable.MSGID + "=?";
database.beginTransaction();
for (Long mid : unreadList)
{
String[] args = new String[]{String.valueOf(mid)};
ContentValues values = new ContentValues();
values.put(MsgTable.UNREAD, false);
database.update(MsgTable.TABLE, values, where, args);
}
database.setTransactionSuccessful();
database.endTransaction();
}
}
return items;
}
private byte[] getHead(String userId)
{
String where = UserDatabaseHelper.UID + "=?";
String[] args = new String[]{userId};
String[] columns = new String[]{UserDatabaseHelper.ICON};
byte[] icon = null;
Cursor cursor = database.query(UserDatabaseHelper.TABLE_USER, columns, where, args, null, null, null);
if (cursor.moveToNext())
{
icon = cursor.getBlob(0);
}
cursor.close();
return icon;
}
private boolean getGender(String userId)
{
String where = UserDatabaseHelper.UID + "=?";
String[] args = new String[]{userId};
String[] columns = new String[]{UserDatabaseHelper.GENDER};
boolean gender = false;
Cursor cursor = database.query(UserDatabaseHelper.TABLE_USER, columns, where, args, null, null, null);
if (cursor.moveToNext())
{
gender = intToBoolean(cursor.getInt(0));
}
cursor.close();
return gender;
}
private String getMsgContent(long msgId)
{
String where = UserDatabaseHelper.MSGID + "=" + msgId;
String[] columns = new String[]{UserDatabaseHelper.CONTENT};
String content = null;
Cursor cursor = database.query(UserDatabaseHelper.TABLE_MSG, columns, where, null, null, null, null);
if (cursor.moveToNext())
{
content = decrypt(cursor.getBlob(0));
}
cursor.close();
return content;
}
private int getMsgCount(String userId)
{
String sql = "SELECT COUNT(*) FROM " + MsgTable.TABLE;
String[] args = null;
int count = 0;
if (userId != null)
{
sql += " WHERE " + MsgTable.UID + "=?";
args = new String[]{userId};
}
Cursor cursor = database.rawQuery(sql, args);
if (cursor.moveToNext())
{
count = cursor.getInt(0);
}
cursor.close();
return count;
}
private int getUnreadMsgCount(String userId)
{
String sql = null;
String[] args = null;
if (TextUtils.isEmpty(userId))
{
sql = "SELECT COUNT(*) FROM " + MsgTable.TABLE + " WHERE " + MsgTable.UNREAD + "=?";
args = new String[]{String.valueOf(booleanToInt(true))};
}
else
{
sql = "SELECT COUNT(*) FROM " + MsgTable.TABLE + " WHERE " + MsgTable.UID + "=? AND " + MsgTable.UNREAD + "=?";
args = new String[]{userId, String.valueOf(booleanToInt(true))};
}
int count = 0;
Cursor cursor = database.rawQuery(sql, args);
if (cursor.moveToNext())
{
count = cursor.getInt(0);
}
cursor.close();
return count;
}
public void markAllMsgAsRead(String userId)
{
String where = MsgTable.UID + "=? AND " + MsgTable.UNREAD + "=?";
String[] args = new String[]{String.valueOf(userId), String.valueOf(booleanToInt(true))};
ContentValues values = new ContentValues();
values.put(MsgTable.UNREAD, false);
database.update(MsgTable.TABLE, values, where, args);
}
private int booleanToInt(boolean b)
{
return b ? 1 : 0;
}
private boolean intToBoolean(int i)
{
return i == 0 ? false : true;
}
/**
* add user info
* @param userInfo
*/
/* public void addUserInfo(DBUserInfo userInfo)
{
if (userInfo != null)
{
addDBUserInfo(userInfo.userId, userInfo.nickname, userInfo.gender, userInfo.birthday, userInfo.sig, userInfo.timeStamp, userInfo.icon);
}
}
*/
public void addUserInfo(UserInfoBean userInfo)
{
if (userInfo != null)
{
addDBUserInfo(
userInfo.userId,
userInfo.nickName,
userInfo.gender,
userInfo.birthday,
userInfo.userSign,
userInfo.headImgTimeStamp == null ? 0 : Long.parseLong(userInfo.headImgTimeStamp),
userInfo.headIcon == null ? null : ImageUtil.Bitmap2Bytes(userInfo.headIcon),
userInfo.headImgUrl,
userInfo.email,
userInfo.longitude,
userInfo.latitude,
userInfo.mileage,
userInfo.headImgNO
);
}
}
/**
* get the user info of the user
* @param userId
* @return the user information
*/
public UserInfoBean getUserInfo(String userId)
{
return getDBUserInfo(userId);
}
/**
* delete the user info from the database
* @param userId
*/
public void deleteUserInfo(String userId)
{
deleteDBUserInfo(userId, false);
}
/**
* delete the user info from the database
* @param userId
* @param deleteAllMsgs, whether delete the messages of this user
*/
public void deleteUserInfo(String userId, boolean deleteAllMsgs)
{
deleteDBUserInfo(userId, deleteAllMsgs);
}
/**
* add a new message
* @param messageItem
* @return
*/
public long addMessageItem(String userId, MessageBean messageItem)
{
return addNewMsg(userId, messageItem.isSend, messageItem.isSend ? false : true, messageItem.meLongitude, messageItem.meLatitude,
messageItem.longitude, messageItem.latitude, messageItem.messageTime, messageItem.message, messageItem.send_fail);
}
public long addMessageItem(String hisId, boolean isSend, double myLon, double myLat, double hisLon, double hisLat, long time, String content)
{
return addNewMsg(hisId, isSend, isSend ? false : true, myLon, myLat, hisLon, hisLat, time, content, false);
}
/**
* add more than one messages at once, the caller must ensure the messages are belong to the same user
* @param items
*/
public void addMessageItem(String userId, MessageBean[] items)
{
long row = 0;
MessageBean messageItem = null;
database.beginTransaction();
for (int i = 0; i < items.length; i++)
{
messageItem = items[i];
if (messageItem != null)
{
row = addNewMsg(userId, messageItem.isSend, messageItem.isSend ? false : true, messageItem.meLongitude, messageItem.meLatitude,
messageItem.longitude, messageItem.latitude, messageItem.messageTime, messageItem.message, messageItem.send_fail);
}
}
database.setTransactionSuccessful();
database.endTransaction();
}
/**
* get the list of one's message, order by time ASC
* @param userId
* @param start
* @param count, how many ite ms to get, if count is zero, than this function will return all the data
* @return
*/
public MessageBean[] getMessageItems(String userId, int start, int count)
{
return getMsgItems(userId, start, count, false);
}
/**
* get the list of one's message, order by time DESC
* @param userId
* @param start
* @param count, how many items to get, if count is zero, than this function will return all the data
* @return
*/
public MessageBean[] getMessageItemsDesc(String userId, int start, int count)
{
return getMsgItems(userId, start, count, true);
}
public MessageBean[] getUnreadMessageItemsDesc(String userId, long startTime)
{
return getUnreadMsgItems(userId, startTime, true);
}
public MessageBean[] getUnreadMessageItems(String userId, int start, int count)
{
return getUnreadMsgItems(userId, start, count, false);
}
public MessageBean[] getUnreadMessageItemsDesc(String userId, int start, int count)
{
return getUnreadMsgItems(userId, start, count, true);
}
/**
* delete all message of the userId
* @param userId
*/
public void deleteMessageItems(String userId)
{
deleteMsgItems(userId);
}
/**
* delete a sent failed message by the msgId
* @param msgId
*/
public void deleteMessageByMsgId(long msgId)
{
deleteMySendMsgItemByMsgId(msgId);
}
/**
* set the message as sent success or fail
*/
public void setMessageSendResult(long msgId, boolean sendResult)
{
setMsgSendResult(msgId, sendResult);
}
/**
*
* @param msgId
* @param sendResult
* @param time 消息发送时间（服务器时间）
*/
public void setMessageSendResult(long msgId, boolean sendResult, long time)
{
ContentValues values = new ContentValues();
values.put(MsgTable.SUCCESS, sendResult);
if (time > 0)
{
values.put(MsgTable.TIME, time);
}
String whereClause = MsgTable.MSGID + "=?";
String[] whereArgs = new String[]{String.valueOf(msgId)};
database.update(MsgTable.TABLE, values, whereClause, whereArgs);
}
/**
* mark a message as read
* @param msgId
*/
/* public void markMessageAsRead(long msgId)
{
markMsgAsRead(msgId);
}*/
/**
* get the recent message list according to the gender, order by time DESC
* @param female, true means woman, false means man
* @param start,
* @param count, count of items to get, 0 means get all the items
* @return
*/
public DBRecentMessageItem[] getRecentMessageList(boolean female, int start, int count)
{
return getRecentMsgOfAll( start, count);
}
public DBRecentMessageItem[] getRecentMessageList( int start, int count)
{
return getRecentMsgOfAll( start, count);
}
/**
* get the total count of the unread message
* @return count of total unread message
*/
public int getUnreadCount()
{
return getUnreadMsgCount(null);
}
/**
* get the unread message count of a user
* @param userId
* @return count of unread message
*/
public int getUnreadCount(String userId)
{
return getUnreadMsgCount(userId);
}
/**
* get the total message count of a user
* @param userId
* @return count of message
*/
public int getTotalMessageCount(String userId)
{
if (userId != null)
{
return getMsgCount(userId);
}
return 0;
}
public void addGpsInfo(LbsParam info)
{
addGpsInfo(info.footPrintId, info.longitude, info.latitude, info.time, info.isDescription, info.decContent, info.decImgPath);
}
public void addGpsInfo(String id, double lon, double lat, long time, boolean uploaded, String content, String url)
{
ContentValues values = new ContentValues();
values.put(UserDatabaseHelper.GPSID, id);
values.put(UserDatabaseHelper.MYLON, lon);
values.put(UserDatabaseHelper.MYLAT, lat);
values.put(UserDatabaseHelper.TIME, time);
values.put(UserDatabaseHelper.UPLOADED, uploaded);
values.put(UserDatabaseHelper.CONTENT, content == null ? "" : content);
values.put(UserDatabaseHelper.URL, url == null ? "" : url);
values.put(UserDatabaseHelper.TIME2, 0);
String where = UserDatabaseHelper.GPSID + "=?";
String[] args = new String[]{id};
Cursor cursor = database.query(UserDatabaseHelper.TABLE_GPS, null, where, args, null, null, null);
if (cursor.moveToNext())
{
database.update(UserDatabaseHelper.TABLE_GPS, values, where, args);
}
else
{
database.insert(UserDatabaseHelper.TABLE_GPS, null, values);
}
cursor.close();
}
public void setGpsExtraInfo(String id, String content, String path)
{
if (id != null)
{
ContentValues values = new ContentValues();
values.put(UserDatabaseHelper.UPLOADED, true);
values.put(UserDatabaseHelper.CONTENT, content);
values.put(UserDatabaseHelper.URL, path);
String whereClause = UserDatabaseHelper.GPSID + "=?";
String[] whereArgs = new String[]{id};
database.update(UserDatabaseHelper.TABLE_GPS, values, whereClause, whereArgs);
}
}
public ArrayList<LbsParam> getGpsInfos()
{
String[] columns = new String[]{
UserDatabaseHelper.TIME, UserDatabaseHelper.MYLON, UserDatabaseHelper.MYLAT,
UserDatabaseHelper.GPSID, UserDatabaseHelper.UPLOADED, UserDatabaseHelper.CONTENT,
UserDatabaseHelper.URL, UserDatabaseHelper.TIME2};
String where = null;
String order = UserDatabaseHelper.TIME;
Cursor cursor = database.query(UserDatabaseHelper.TABLE_GPS, columns, where, null, null, null, order);
ArrayList<LbsParam> info = new ArrayList<LbsParam>();
if (cursor.getCount() > 0)
{
int timeIndex = cursor.getColumnIndex(UserDatabaseHelper.TIME);
int lonIndex = cursor.getColumnIndex(UserDatabaseHelper.MYLON);
int latIndex = cursor.getColumnIndex(UserDatabaseHelper.MYLAT);
int idIndex = cursor.getColumnIndex(UserDatabaseHelper.GPSID);
int uploadedIndex = cursor.getColumnIndex(UserDatabaseHelper.UPLOADED);
int contentIndex = cursor.getColumnIndex(UserDatabaseHelper.CONTENT);
int urlIndex = cursor.getColumnIndex(UserDatabaseHelper.URL);
//int time2Index = cursor.getColumnIndex(UserDatabaseHelper.TIME2);
while (cursor.moveToNext())
{
LbsParam lbs = new LbsParam();
lbs.footPrintId = cursor.getString(idIndex);
lbs.longitude = cursor.getDouble(lonIndex);
lbs.latitude = cursor.getDouble(latIndex);
lbs.time = cursor.getLong(timeIndex);
lbs.isDescription = intToBoolean(cursor.getInt(uploadedIndex));
lbs.decContent = cursor.getString(contentIndex);
lbs.decImgPath = cursor.getString(urlIndex);
info.add(lbs);
}
}
cursor.close();
return info;
}
public void deleteGpsInfosBeforeTime(long time)
{
if (time <= 0)
{
database.delete(UserDatabaseHelper.TABLE_GPS, null, null);
}
else
{
String where = UserDatabaseHelper.TIME + "<?";
String[] whereArgs = new String[]{String.valueOf(time)};
database.delete(UserDatabaseHelper.TABLE_GPS, where, whereArgs);
}
}
/**
* get the mood shot picture by footprint id
* @return the bitmap
*/
public Bitmap getMoodShot(String footprintId)
{
String where = MoodTable.FID + "=?";
String[] args = new String[] {footprintId};
String[] columns = new String[] {MoodTable.PIC};
Cursor cursor = database.query(MoodTable.TABLE, columns, where, args, null, null, null);
Bitmap bitmap = null;
if (cursor.moveToNext())
{
byte[] temp = cursor.getBlob(0);
bitmap = temp == null ? null : ImageUtil.byteToBitmap(temp);
}
cursor.close();
return bitmap;
}
private void getMoodFromDB(ArrayList<MoodBean> list, String where, String[] args, String orderBy)
{
ArrayList<String> temp = new ArrayList<String>();
Cursor cursor = database.query(MoodTable.TABLE, null, where, args, null, null, orderBy);
int fidIndex = cursor.getColumnIndex(MoodTable.FID);
int uidIndex = cursor.getColumnIndex(MoodTable.UID);
int usernameIndex = cursor.getColumnIndex(MoodTable.USERNAME);
int genderIndex = cursor.getColumnIndex(MoodTable.GENDER);
int headUrlIndex = cursor.getColumnIndex(MoodTable.HEADURL);
int headTsIndex = cursor.getColumnIndex(MoodTable.HEADTS);
int contentIndex = cursor.getColumnIndex(MoodTable.CONTENT);
int imageUrlIndex = cursor.getColumnIndex(MoodTable.IMAGEURL);
int imageTsIndex = cursor.getColumnIndex(MoodTable.IMAGETS);
int timeIndex = cursor.getColumnIndex(MoodTable.TIME);
int lonIndex = cursor.getColumnIndex(MoodTable.LON);
int latIndex = cursor.getColumnIndex(MoodTable.LAT);
int countIndex = cursor.getColumnIndex(MoodTable.COUNT);
int distIndex = cursor.getColumnIndex(MoodTable.DIST);
int sinaIndex = cursor.getColumnIndex(MoodTable.SINA);
int arroundIndex = cursor.getColumnIndex(MoodTable.ARROUND);
int headUrl2Index = cursor.getColumnIndex(MoodTable.HEADURL2);
int posIndex = cursor.getColumnIndex(MoodTable.POS);
int unreadIndex = cursor.getColumnIndex(MoodTable.UNREAD);
int lastTimeIndex = cursor.getColumnIndex(MoodTable.LASTTIME);
while (cursor.moveToNext())
{
MoodBean mood = new MoodBean();
mood.footPrintId = cursor.getString(fidIndex);
mood.userId = cursor.getString(uidIndex);
mood.userName = cursor.getString(usernameIndex);
mood.gender = intToBoolean(cursor.getInt(genderIndex));
mood.userHeadUrl = cursor.getString(headUrlIndex);
mood.userHeadTimeStamp = cursor.getString(headTsIndex);
mood.moodContent = cursor.getString(contentIndex);
mood.imageUrl = cursor.getString(imageUrlIndex);
mood.imageTimeStamp = cursor.getString(imageTsIndex);
mood.createDate = cursor.getLong(timeIndex);
mood.longitude = cursor.getDouble(lonIndex);
mood.latitude = cursor.getDouble(latIndex);
mood.commentSize = cursor.getInt(countIndex);
mood.distance = cursor.getInt(distIndex);
mood.isShareSina = intToBoolean(cursor.getInt(sinaIndex));
mood.isShareAound = intToBoolean(cursor.getInt(arroundIndex));
mood.userHeadNO = cursor.getString(headUrl2Index);
mood.position = cursor.getInt(posIndex);
mood.unreadCommentSize = cursor.getInt(unreadIndex);
mood.lastCommentTimestamp = cursor.getLong(lastTimeIndex);
list.add(mood);
if (mood.unreadCommentSize > 0)
{
temp.add(mood.footPrintId);
}
}
cursor.close();
//获取后，同时把所有未读条数大于0的清零
// if (temp.size() > 0)
// {
// ContentValues values = new ContentValues();
//
// values.put(MoodTable.UNREAD, 0);
//
// database.beginTransaction();
//
// for (String fid : temp)
// {
// database.update(MoodTable.TABLE, values, MoodTable.FID + "=?", new String[]{fid});
// }
//
// database.setTransactionSuccessful();
// database.endTransaction();
// }
}
//获取含有未读赠言的足迹列表，按最后更新时间排序
public ArrayList<MoodBean> getUnreadMoodListDesc(String userId)
{
ArrayList<MoodBean> list = new ArrayList<MoodBean>();
if (TextUtils.isEmpty(userId))
{
return list;
}
String where = MoodTable.UID + "=? AND " + MoodTable.UNREAD + "<>0";
String[] args = new String[]{userId};
String orderBy = MoodTable.LASTTIME + " DESC";
getMoodFromDB(list, where, args, orderBy);
return list;
}
//获取两个时间点之间的所有足迹(包括两端)
public ArrayList<MoodBean> getMoodListDescBetweenTime(String userId, long timeFrom, long timeTo)
{
ArrayList<MoodBean> list = new ArrayList<MoodBean>();
if (TextUtils.isEmpty(userId))
{
return list;
}
String where = MoodTable.UID + "=? AND " + MoodTable.TIME + "<=? AND " + MoodTable.TIME + ">=?";
String[] args = new String[]{userId, String.valueOf(timeTo), String.valueOf(timeFrom)};
String orderBy = MoodTable.TIME + " DESC";
getMoodFromDB(list, where, args, orderBy);
return list;
}
/**
* get the moods list, order by time desc
* @param userId if is null, then ignore this parameter
* @param time if <= 0, then ignore this parameter
* @param count if == 0, then ignore this parameter
* @return
*/
public ArrayList<MoodBean> getMoodListDesc(String userId, long time, int count)
{
return getMoodListDescInternal(userId, time, count, false);
}
/**
* get the moods list, order by time desc, except userId
* @param userId
* @param time
* @param count
* @return
*/
public ArrayList<MoodBean> getMoodListDescExceptUserId(String userId, long time, int count)
{
return getMoodListDescInternal(userId, time, count, true);
}
private ArrayList<MoodBean> getMoodListDescInternal(String userId, long time, int count, boolean isExcept)
{
String where = "";
String[] args = null;
int t = 0;
if (userId != null)
{
t++;
if (isExcept)
{
where += MoodTable.UID + "<>?";
}
else
{
where += MoodTable.UID + "=?";
}
}
if (time > 0)
{
if (t > 0)
{
where += " AND ";
}
t++;
where += MoodTable.TIME + "<?";
}
if (t > 0)
{
args = new String[t];
t = 0;
if (userId != null)
{
args[t++] = userId;
}
if (time > 0)
{
args[t++] = String.valueOf(time);
}
}
String orderBy = MoodTable.TIME + " DESC";
if (count > 0)
{
orderBy += " LIMIT " + count;
}
ArrayList<MoodBean> list = new ArrayList<MoodBean>();
getMoodFromDB(list, where, args, orderBy);
return list;
}
/**
* get all the moods list, order by time desc
* @return
*/
public ArrayList<MoodBean> getMoodListDesc()
{
return getMoodListDesc(null, 0, 0);
}
/**
* add more than one mood to the database
* @param mood
*/
public void addMoods(String userId, ArrayList<MoodBean> moods, boolean updateUnread)
{
if ((moods == null) || (moods.size() == 0))
{
return;
}
HashMap<String, Boolean> footIdHashMap = new HashMap<String, Boolean>();
for (MoodBean moodBean : moods)
{
if ((userId != null) && (!userId.equals(moodBean.userId)))
{
footIdHashMap.put(moodBean.footPrintId, true);
}
}
database.beginTransaction();
deleteMoodsExceptFootIdAndUserId(userId, footIdHashMap);
for (MoodBean moodBean : moods)
{
addMood(moodBean, updateUnread);
}
database.setTransactionSuccessful();
database.endTransaction();
}
/**
* add a mood
* @param mood
*/
private void addMood(MoodBean mood, boolean updateUnread)
{
if (mood == null)
{
return;
}
String fid = nullToEmpty(mood.footPrintId);
ContentValues values = new ContentValues();
values.put(MoodTable.FID, fid);
values.put(MoodTable.UID, nullToEmpty(mood.userId));
values.put(MoodTable.USERNAME, nullToEmpty(mood.userName));
values.put(MoodTable.GENDER, booleanToInt(mood.gender));
values.put(MoodTable.HEADURL, nullToEmpty(mood.userHeadUrl));
values.put(MoodTable.HEADTS, nullToEmpty(mood.userHeadTimeStamp));
values.put(MoodTable.CONTENT, nullToEmpty(mood.moodContent));
values.put(MoodTable.IMAGEURL, nullToEmpty(mood.imageUrl));
values.put(MoodTable.IMAGETS, nullToEmpty(mood.imageTimeStamp));
values.put(MoodTable.TIME, mood.createDate);
values.put(MoodTable.LON, mood.longitude);
values.put(MoodTable.LAT, mood.latitude);
values.put(MoodTable.COUNT, mood.commentSize);
values.put(MoodTable.DIST, mood.distance);
values.put(MoodTable.SINA, booleanToInt(mood.isShareSina));
values.put(MoodTable.ARROUND, booleanToInt(mood.isShareAound));
values.put(MoodTable.HEADURL2, nullToEmpty(mood.userHeadNO));
values.put(MoodTable.POS, mood.position);
values.put(MoodTable.LASTTIME, mood.lastCommentTimestamp);
if (TextUtils.isEmpty(fid))
{
values.put(MoodTable.UNREAD, mood.unreadCommentSize);
database.insert(MoodTable.TABLE, null, values);
}
else
{
String where = MoodTable.FID + "=?";
String[] args = new String[]{fid};
Cursor cursor = database.query(MoodTable.TABLE, null, where, args, null, null, null);
if (cursor.moveToNext())
{
if (updateUnread)
{
//int unreadIndex = cursor.getColumnIndex(MoodTable.UNREAD);
//int oldUnread = cursor.getInt(unreadIndex);
values.put(MoodTable.UNREAD, mood.unreadCommentSize);
}
database.update(MoodTable.TABLE, values, where, args);
}
else
{
values.put(MoodTable.UNREAD, mood.unreadCommentSize);
database.insert(MoodTable.TABLE, null, values);
}
cursor.close();
}
}
/**
* delete moods by userid
* @param userId
*/
public void deleteMoodsByUserId(String userId)
{
String where = MoodTable.UID + "=?";
String[] args = new String[]{userId};
database.delete(MoodTable.TABLE, where, args);
}
private void deleteMoodsExceptFootIdAndUserId(String userId, HashMap<String, Boolean> footIdHashMap)
{
int maxCount = 20;
if (!TextUtils.isEmpty(userId) && (footIdHashMap != null) && (footIdHashMap.size() > 0))
{
String whereString = MoodTable.UID + "<>?";
String[] argsStrings = new String[]{userId};
//如果超过最大值，直接删除所有
if (footIdHashMap.size() >= maxCount)
{
database.delete(MoodTable.TABLE, whereString, argsStrings);
return;
}
String orderByString = MoodTable.TIME;
String[] columnStrings = new String[]{MoodTable.FID};
ArrayList<String> canDeleteList = new ArrayList<String>();
Cursor cursor = database.query(MoodTable.TABLE, columnStrings, whereString, argsStrings, null, null, orderByString);
while (cursor.moveToNext())
{
String footId = cursor.getString(0);
if (!footIdHashMap.containsKey(footId))
{
canDeleteList.add(footId);
}
}
cursor.close();
int count = canDeleteList.size() + footIdHashMap.size() - maxCount;
if (count > 0)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append(MoodTable.FID + " IN (");
for (int i = 0; i < count; i++)
{
stringBuilder.append(canDeleteList.get(i));
if (i < count - 1)
{
stringBuilder.append(",");
}
}
stringBuilder.append(")");
database.delete(MoodTable.TABLE, stringBuilder.toString(), null);
}
}
}
/**
* delete mood by footprint id
* @param footPrintId
*/
public void deleteMoodsByFootPrintId(String footPrintId)
{
String where = MoodTable.FID + "=?";
String[] args = new String[]{footPrintId};
database.delete(MoodTable.TABLE, where, args);
}
/**
* delete all the moods
*/
public void deleteAllMoods()
{
database.delete(MoodTable.TABLE, null, null);
}
//获取未读赠言不为0的足迹的条数
public int getFootCountWithUnread(String userId)
{
if (TextUtils.isEmpty(userId))
{
return 0;
}
int count = 0;
String[] args = new String[]{userId};
String sql = "SELECT COUNT(*) FROM " + MoodTable.TABLE + " WHERE " + MoodTable.UID + "=? AND " + MoodTable.UNREAD + "<>0";
Cursor cursor = database.rawQuery(sql, args);
if (cursor.moveToNext())
{
count = cursor.getInt(0);
}
cursor.close();
return count;
}
//获取未读赠言总数
public int getUnreadCommentCount(String userId)
{
if (TextUtils.isEmpty(userId))
{
return 0;
}
int count = 0;
String[] args = new String[]{userId};
String sql = "SELECT SUM(" + MoodTable.UNREAD + ") FROM " + MoodTable.TABLE + " WHERE " + MoodTable.UID + "=?";
Cursor cursor = database.rawQuery(sql, args);
if (cursor.moveToNext())
{
count = cursor.getInt(0);
}
cursor.close();
return count;
}
//评论接口
//添加评论
public void addComments(List<CommentBean> list)
{
if ((list == null) || (list.size() == 0))
{
return;
}
database.beginTransaction();
HashMap<String, Boolean> tempHashMap = new HashMap<String, Boolean>();
for (CommentBean commentBean : list)
{
if ((commentBean != null) && !TextUtils.isEmpty(commentBean.footID))
{
ContentValues values = new ContentValues();
values.put(CommentTable.FID, commentBean.footID);
values.put(CommentTable.UID, nullToEmpty(commentBean.userID));
values.put(CommentTable.USERNAME, nullToEmpty(commentBean.nickName));
values.put(CommentTable.HEADTIME, nullToEmpty(commentBean.userIconTimeStamp));
values.put(CommentTable.GENDER, booleanToInt(commentBean.gender));
values.put(CommentTable.URL, nullToEmpty(commentBean.userIconUrl));
values.put(CommentTable.URL2, nullToEmpty(commentBean.userIconUrl2));
values.put(CommentTable.CID, nullToEmpty(commentBean.commentID));
values.put(CommentTable.CONTENT, nullToEmpty(commentBean.commentContent));
values.put(CommentTable.TIME, commentBean.commentTime);
values.put(CommentTable.UNREAD, booleanToInt(false));
String where = CommentTable.CID + "=?";
String[] args = new String[]{commentBean.commentID};
Cursor cursor = database.query(CommentTable.TABLE, null, where, args, null, null, null);
if (cursor.moveToNext())
{
database.update(CommentTable.TABLE, values, where, args);
}
else
{
database.insert(CommentTable.TABLE, null, values);
}
cursor.close();
if (!tempHashMap.containsKey(commentBean.footID))
{
tempHashMap.put(commentBean.footID, true);
}
}
}
//保存评论的同时把对应的足迹的未读数清零
ContentValues values = new ContentValues();
values.put(MoodTable.UNREAD, 0);
String[] columns = new String[]{MoodTable.UNREAD};
String where = MoodTable.FID + "=?";
for (String fid : tempHashMap.keySet())
{
String[] args = new String[]{fid};
Cursor cursor = database.query(MoodTable.TABLE, columns, where, args, null, null, null);
if (cursor.moveToNext())
{
int unread = cursor.getInt(0);
if (unread != 0)
{
database.update(MoodTable.TABLE, values, where, args);
}
}
cursor.close();
}
database.setTransactionSuccessful();
database.endTransaction();
}
//删除评论
public void deleteComments(String footId)
{
if (TextUtils.isEmpty(footId))
{
return;
}
String whereString = CommentTable.FID + "=?";
String[] argStrings = new String[]{footId};
database.delete(CommentTable.TABLE, whereString, argStrings);
}
//获取评论列表
public ArrayList<CommentBean> getComments(String footId, long time, int count)
{
//ArrayList<String> tempArrayList = new ArrayList<String>();
ArrayList<CommentBean> list = new ArrayList<CommentBean>();
if (TextUtils.isEmpty(footId))
{
return list;
}
String selection = CommentTable.FID + "=? AND " + CommentTable.TIME + "<?";
String[] selectionArgs = new String[]{footId, String.valueOf(time)};
String orderBy = CommentTable.TIME + " DESC";
if (count > 0)
{
orderBy += " LIMIT " + count;
}
Cursor cursor = database.query(CommentTable.TABLE, null, selection, selectionArgs, null, null, orderBy);
while (cursor.moveToNext())
{
CommentBean commentBean = new CommentBean();
int footIndex = cursor.getColumnIndex(CommentTable.FID);
int userIdIndex = cursor.getColumnIndex(CommentTable.UID);
int usernameIndex = cursor.getColumnIndex(CommentTable.USERNAME);
int genderIndex = cursor.getColumnIndex(CommentTable.GENDER);
int headTimeIndex = cursor.getColumnIndex(CommentTable.HEADTIME);
int urlIndex = cursor.getColumnIndex(CommentTable.URL);
int url2Index = cursor.getColumnIndex(CommentTable.URL2);
int cidIndex = cursor.getColumnIndex(CommentTable.CID);
int contentIndex = cursor.getColumnIndex(CommentTable.CONTENT);
int timeIndex = cursor.getColumnIndex(CommentTable.TIME);
// int unreadIndex = cursor.getColumnIndex(CommentTable.UNREAD);
commentBean.footID = cursor.getString(footIndex);
commentBean.userID = cursor.getString(userIdIndex);
commentBean.nickName = cursor.getString(usernameIndex);
commentBean.gender = intToBoolean(cursor.getInt(genderIndex));
commentBean.userIconTimeStamp = cursor.getString(headTimeIndex);
commentBean.userIconUrl = cursor.getString(urlIndex);
commentBean.userIconUrl2 = cursor.getString(url2Index);
commentBean.commentID = cursor.getString(cidIndex);
commentBean.commentContent = cursor.getString(contentIndex);
commentBean.commentTime = cursor.getLong(timeIndex);
list.add(commentBean);
// boolean unread = intToBoolean(cursor.getInt(unreadIndex));
//
// if (unread)
// {
// tempArrayList.add(commentBean.footID);
// }
}
// if (!tempArrayList.isEmpty())
// {
// database.beginTransaction();
//
// for (String fid : tempArrayList)
// {
// String whereString = CommentTable.FID + "=?";
// String[] argsStrings = new String[]{fid};
//
// ContentValues values = new ContentValues();
//
// values.put(CommentTable.UNREAD, booleanToInt(false));
//
// database.update(CommentTable.TABLE, values, whereString, argsStrings);
// }
//
// database.setTransactionSuccessful();
// database.endTransaction();
// }
return list;
}
//保存生命线数据
public void addLifeLine(List<FootSumBean> list)
{
if ((list == null) || (list.size() == 0))
{
return;
}
String where = LifeLineTable.TIME + "=?";
database.beginTransaction();
for (FootSumBean footSumBean : list)
{
if (footSumBean != null)
{
ContentValues values = new ContentValues();
values.put(LifeLineTable.TIME, footSumBean.timestamp);
values.put(LifeLineTable.COUNT, footSumBean.count);
values.put(LifeLineTable.DAYS, footSumBean.daysOfLife);
String[] args = new String[]{String.valueOf(footSumBean.timestamp)};
Cursor cursor = database.query(LifeLineTable.TABLE, null, where, args, null, null, null);
if (cursor.moveToFirst())
{
database.update(LifeLineTable.TABLE, values, where, args);
}
else
{
database.insert(LifeLineTable.TABLE, null, values);
}
cursor.close();
}
}
database.setTransactionSuccessful();
database.endTransaction();
}
//获取生命线列表
public ArrayList<FootSumBean> getLifeLine()
{
ArrayList<FootSumBean> list = new ArrayList<FootSumBean>();
Cursor cursor = database.query(LifeLineTable.TABLE, null, null, null, null, null, LifeLineTable.TIME + " DESC");
int timeIndex = cursor.getColumnIndex(LifeLineTable.TIME);
int countIndex = cursor.getColumnIndex(LifeLineTable.COUNT);
int dayIndex = cursor.getColumnIndex(LifeLineTable.DAYS);
while (cursor.moveToNext())
{
FootSumBean bean = new FootSumBean();
bean.timestamp = cursor.getLong(timeIndex);
bean.count = cursor.getInt(countIndex);
bean.daysOfLife = cursor.getInt(dayIndex);
list.add(bean);
}
cursor.close();
return list;
}
//删除给定时间的生命线
public void deleteLifeLine(long time)
{
database.delete(LifeLineTable.TABLE, LifeLineTable.TIME + "=?", new String[]{String.valueOf(time)});
}
}
扫一扫
663
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
