// 获取升级前表中的字段
protected String getColumnNames(SQLiteDatabase db, String tableName) {
StringBuffer sbSelect = null;
Cursor c = null;
try {
c = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);
if (null != c) {
int columnIndex = c.getColumnIndex("name");
if (-1 == columnIndex) {
return null;
}
int index = 0;
int pos = c.getCount() + 1; // 并标记最后一个不加逗号，
// 由于index从0开始，所有这里不用加2,只加1
sbSelect = new StringBuffer(c.getCount() + 2); // //字段总列数，增加2列需要加2
for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
sbSelect.append(c.getString(columnIndex));
index++;
if (index < pos) {
sbSelect.append(",");
}
}
}
} catch (Exception e) {
logger.e(e.getMessage());
} finally {
if (null != c) {
c.close();
}
}
return sbSelect.toString();
}
// 如果DATABASE_VERSION值被改为2,系统发现现有数据库版本不同,即会调用onUpgrade
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO 更改数据库版本的操作 暂时先直接删除旧表
logger.d("moguimdb onUpgrade DB");
// updateTable(db); //升级 测试可用
db.execSQL("DROP TABLE IF EXISTS " + DBHelper.TABLE_USERS);
db.execSQL("DROP TABLE IF EXISTS " + DBHelper.TABLE_CONTACTS);
db.execSQL("DROP TABLE IF EXISTS " + DBHelper.TABLE_MESSAGES);
db.execSQL("DROP TABLE IF EXISTS " + DBHelper.TABLE_EXTRA_TEXT);
db.execSQL("DROP TABLE IF EXISTS " + DBHelper.TABLE_EXTRA_IMAGE);
db.execSQL("DROP TABLE IF EXISTS " + DBHelper.TABLE_EXTRA_AUDIO);
onCreate(db);
}
@SuppressWarnings("unused")
private void updateTable(SQLiteDatabase db) {
try {
db.beginTransaction();
logger.d("moguimdb onUpgrade DB table start");
// rename the table
String alterMessageTableSql = getAlterTableSql(DBHelper.TABLE_MESSAGES);
db.execSQL(alterMessageTableSql);
logger.d("moguimdb onUpgrade DB alter table");
// creat table
db.execSQL(CREATE_MESSAGES); // TODO 升级时改成新表名
logger.d("moguimdb onUpgrade DB create new table");
// load data
logger.d("moguimdb onUpgrade DB load data");
String selectColumns = getColumnNames(db, DBHelper.TABLE_MESSAGES + TEMP_SUFFIX); // 记得用旧表名哦
String updateMessagesSql =
"insert into "
+ DBHelper.TABLE_MESSAGES
+ " ("
+ selectColumns
+ ") "
+ "select "
+ selectColumns
+ ""
+ " "
+ " from "
+ DBHelper.TABLE_MESSAGES
+ TEMP_SUFFIX;
logger.d("moguimdb onUpgrade DB updateMessagesSql : " + updateMessagesSql);
db.equals(updateMessagesSql);
// drop the oldtable
logger.d("moguimdb onUpgrade DB drop old table");
String dropMessageTableSql = getDropTableSql(DBHelper.TABLE_MESSAGES + TEMP_SUFFIX);
db.execSQL(dropMessageTableSql);
logger.d("moguimdb onUpgrade DB table end");
db.setTransactionSuccessful();
} catch (Exception e) {
logger.d("moguimdb onUpgrade DB error with reason: " + e.toString());
} finally {
db.endTransaction();
}
}
/**
* 查询DB中某表的最后一条ID
*
* @return msgId
*/
public int queryLastInsertId(String tableName) {
int lastMsgId = 0;
Cursor c = null;
SQLiteDatabase dbSlaver = null;
try {
dbSlaver = getReadableDatabase();
c = dbSlaver.rawQuery("select last_insert_rowid() from " + tableName, null);
if (c.moveToFirst()) {
lastMsgId = c.getInt(0);
}
} catch (SQLException e) {
logger.e(e.toString());
} finally {
if (null != c) {
c.close();
}
// dbSlaver.close(); //这里不能关闭哦，因为是在其它DB语句中间操作的，由其它DB操作部分关闭即可
}
return lastMsgId;
}
@Override
public void decode(DataBuffer buffer) {
if (null == buffer) return;
try {
FriendStatusNotifyResponse res = new FriendStatusNotifyResponse();
Header ResponseFriendStatusNotifyHeader = new Header();
ResponseFriendStatusNotifyHeader.decode(buffer);
res.setHeader(ResponseFriendStatusNotifyHeader);
if (ResponseFriendStatusNotifyHeader.getServiceId() != ProtocolConstant.SID_BUDDY_LIST
|| ResponseFriendStatusNotifyHeader.getCommandId()
!= ProtocolConstant.CID_CONTACT_FRIEND_STATUS_NOTIYF) return;
int len = buffer.readInt();
res.setUser_id(buffer.readString(len));
res.setUser_status(buffer.readInt());
mResponse = res;
} catch (Exception e) {
logger.e(e.getMessage());
}
}
public class FriendStatusNotifyPacket extends Packet {
private Logger logger = Logger.getLogger(FriendStatusNotifyPacket.class);
public FriendStatusNotifyPacket() {
mRequest = new FriendStatusNotifyRequest();
setNeedMonitor(false);
}
@Override
public DataBuffer encode() {
return null;
}
@Override
public void decode(DataBuffer buffer) {
if (null == buffer) return;
try {
FriendStatusNotifyResponse res = new FriendStatusNotifyResponse();
Header ResponseFriendStatusNotifyHeader = new Header();
ResponseFriendStatusNotifyHeader.decode(buffer);
res.setHeader(ResponseFriendStatusNotifyHeader);
if (ResponseFriendStatusNotifyHeader.getServiceId() != ProtocolConstant.SID_BUDDY_LIST
|| ResponseFriendStatusNotifyHeader.getCommandId()
!= ProtocolConstant.CID_CONTACT_FRIEND_STATUS_NOTIYF) return;
int len = buffer.readInt();
res.setUser_id(buffer.readString(len));
res.setUser_status(buffer.readInt());
mResponse = res;
} catch (Exception e) {
logger.e(e.getMessage());
}
}
public static class FriendStatusNotifyRequest extends Request {}
public static class FriendStatusNotifyResponse extends Response {
private String user_id;
private int user_status;
public FriendStatusNotifyResponse() {}
public String getUser_id() {
return user_id;
}
public void setUser_id(String user_id) {
this.user_id = user_id;
}
public int getUser_status() {
return user_status;
}
public void setUser_status(int user_status) {
this.user_status = user_status;
}
}
}
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
logger.d("moguimdb onDowngrade DB");
onUpgrade(db, oldVersion, newVersion);
}
@Override
public void onOpen(SQLiteDatabase db) {
logger.d("moguimdb onOpen DB");
super.onOpen(db);
// TODO 每次成功打开数据库后首先被执行
}
/** @author shuchen */
public class DBHelper extends SQLiteOpenHelper {
private final Logger logger = Logger.getLogger(DBHelper.class);
private static final String DATABASE_NAME = "MG_TT.db";
// private static final String DATABASE_NAME="/sdcard/moguim.db";
private static final int DATABASE_VERSION = 1;
// 表名
public static final String TABLE_USERS = "Users"; // 用户信息表
public static final String TABLE_CONTACTS = "Contacts"; // 最近联系人信息表
public static final String TABLE_MESSAGES = "Messages"; // 消息主表
public static final String TABLE_EXTRA_TEXT = "ExtraText"; // 文本消息附加表
public static final String TABLE_EXTRA_IMAGE = "ExtraImage"; // 图片消息附加表
public static final String TABLE_EXTRA_AUDIO = "ExtraAudio"; // 语音消息附加表
// 列名
public static final String COLUMN_ID = "id"; // 自增id列
public static final String COLUMN_USER_ID = "userId"; // 用户ID列
public static final String COLUMN_USER_NAME = "uname"; // 用户名字列
public static final String COLUMN_USER_NICKNAME = "unick"; // 用户昵称列
public static final String COLUMN_USER_AVATAR = "avtar"; // 用户头像url列
public static final String COLUMN_USER_TITLE = "title";
public static final String COLUMN_USER_POSITION = "position";
public static final String COLUMN_USER_ROLE_STATUS = "role_status";
public static final String COLUMN_USER_SEX = "sex";
public static final String COLUMN_USER_DEPART_ID = "depart_id";
public static final String COLUMN_USER_JOB_NUMBER = "job_number";
public static final String COLUMN_USER_TELPHONE = "telphone";
public static final String COLUMN_USER_EMAIL = "email";
public static final String COLUMN_RELATE_ID = "relateId"; // 两个联系人唯一联系ID
public static final String COLUMN_OWNER_ID = "ownerId"; // 联系人所属者ID
public static final String COLUMN_FRIEND_USER_ID = "friendUserId"; // 好友用户ID
public static final String COLUMN_STATUS = "status"; // 联系人当前状态
public static final String COLUMN_MESSAGE_ID = "msgId"; // 消息存储唯一ID
public static final String COLUMN_MESSAGE_PARENT_ID =
"msgParentId"; // 消息存储ID，当图文混排时，该字段有用，为该图文消息的第一条消息的ID，否则为-1
public static final String COLUMN_MESSAGE_FROM_USER_ID = "fromUserId"; // 消息发送者ID
public static final String COLUMN_MESSAGE_TO_USER_ID = "toUserId"; // 消息接受者ID
public static final String COLUMN_MESSAGE_TYPE = "msgType"; // 消息类型 图文消息 1 ；
// 语音消息 100
public static final String COLUMN_MESSAGE_DISPLAY_TYPE = "displayType"; // 消息展示类型
// 文本,
// 图片,
// 语音,
// 商品详情或更多扩展类型
public static final String COLUMN_MESSAGE_OVERVIEW = "overview"; // 消息预览
public static final String COLUMN_MESSAGE_STATUS = "status"; // 消息加载状态
public static final String COLUMN_MESSAGE_READ_STATUS = "readStatus"; // 消息是否已读或已展现
public static final String COLUMN_MESSAGE_TALKERID = "talkerId";
public static final String COLUMN_MESSAGE_EXTRA_MSG_ID = "msgId"; // 附加消息表中消息对应的消息唯一ID
public static final String COLUMN_MESSAGE_EXTRA_TEXT_CONTENT = "content"; // 文本消息内容
public static final String COLUMN_MESSAGE_EXTRA_IMAGE_SAVE_PATH = "savePath"; // 图片保存路径
public static final String COLUMN_MESSAGE_EXTRA_IMAGE_URL = "url"; // 图片原始URL
public static final String COLUMN_MESSAGE_EXTRA_AUDIO_SAVE_PATH = "savePath"; // 语音保存路径
public static final String COLUMN_MESSAGE_EXTRA_AUDIO_URL = "url"; // 语音原始URL
public static final String COLUMN_MESSAGE_EXTRA_AUDIO_PLAY_TIME = "playTime"; // 语音播放时长
public static final String COLUMN_MESSAGE_EXTRA_GOODS_ID = "goodsID"; // 商品ID
// public static final String COLUMN_MESSAGE_EXTRA_GOODS_IID = "iid"; //
// 商品ID，Item
// Id（类似与goodsID，但作用不一）
public static final String COLUMN_MESSAGE_EXTRA_GOODS_TITLE = "title"; // 商品名字
public static final String COLUMN_MESSAGE_EXTRA_GOODS_NOW_PRICE = "nowPrice"; // 商品现价
public static final String COLUMN_MESSAGE_EXTRA_GOODS_OLD_PRICE = "oldPrice"; // 商品原价
public static final String COLUMN_MESSAGE_EXTRA_GOODS_URL = "goodsUrl"; // 商品的URL
public static final String COLUMN_MESSAGE_EXTRA_GOODS_IMAGE_URL = "imgUrl"; // 商品的图片URL
public static final String COLUMN_CREATED = "created"; // 创建时间列
public static final String COLUMN_UPDATED = "updated"; // 更新时间列
public static final String TEMP_SUFFIX = "_temp_suffix"; // 数据库更新时，表名后缀
public final String CREATE_USERS =
"CREATE TABLE IF NOT EXISTS "
+ TABLE_USERS
+ " ("
+ COLUMN_ID
+ " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
+ COLUMN_USER_ID
+ " VARCHAR(20) NOT NULL, "
+ COLUMN_USER_NAME
+ " VARCHAR(20) NOT NULL, "
+ COLUMN_USER_NICKNAME
+ " VARCHAR(20) NOT NULL, "
+ COLUMN_USER_AVATAR
+ " VARCHAR(1024) NOT NULL, "
+ COLUMN_USER_TITLE
+ " VARCHAR(256) NOT NULL, "
+ COLUMN_USER_POSITION
+ " VARCHAR(256) NOT NULL, "
+ COLUMN_USER_ROLE_STATUS
+ " INTEGER DEFAULT 0, "
+ COLUMN_USER_SEX
+ " INTEGER DEFAULT 0, "
+ COLUMN_USER_DEPART_ID
+ " VARCHAR(64) NOT NULL, "
+ COLUMN_USER_JOB_NUMBER
+ " INTEGER DEFAULT 0, "
+ COLUMN_USER_TELPHONE
+ " VARCHAR(64) NOT NULL, "
+ COLUMN_USER_EMAIL
+ " VARCHAR(64) NOT NULL, "
+ COLUMN_CREATED
+ " INTEGER DEFAULT 0, "
+ COLUMN_UPDATED
+ " INTEGER DEFAULT 0);";
public final String CREATE_CONTACTS =
"CREATE TABLE IF NOT EXISTS "
+ TABLE_CONTACTS
+ " ("
+ COLUMN_RELATE_ID
+ " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
+ COLUMN_OWNER_ID
+ " VARCHAR(20) NOT NULL, "
+ COLUMN_USER_ID
+ " VARCHAR(20) NOT NULL, "
+ COLUMN_FRIEND_USER_ID
+ " VARCHAR(20) NOT NULL, "
+ COLUMN_STATUS
+ " INTEGER NOT NULL DEFAULT 0,"
+ COLUMN_CREATED
+ " INTEGER DEFAULT 0, "
+ COLUMN_UPDATED
+ " INTEGER DEFAULT 0);";
public final String CREATE_MESSAGES =
"CREATE TABLE IF NOT EXISTS "
+ TABLE_MESSAGES
+ " ("
+ COLUMN_ID
+ " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
+ COLUMN_MESSAGE_ID
+ " INTEGER NOT NULL, "
+ COLUMN_MESSAGE_PARENT_ID
+ " INTEGER NOT NULL DEFAULT -1, "
+ COLUMN_OWNER_ID
+ " VARCHAR(20) NOT NULL, "
+ COLUMN_RELATE_ID
+ " INTEGER NOT NULL, "
+ COLUMN_MESSAGE_FROM_USER_ID
+ " VARCHAR(20) NOT NULL, "
+ COLUMN_MESSAGE_TO_USER_ID
+ " VARCHAR(20) NOT NULL, "
+ COLUMN_MESSAGE_TALKERID
+ " VARCHAR(20) NOT NULL, "
+ COLUMN_MESSAGE_TYPE
+ " INTEGER NOT NULL DEFAULT 1, "
+ COLUMN_MESSAGE_DISPLAY_TYPE
+ " INTEGER NOT NULL DEFAULT 7, "
+ COLUMN_MESSAGE_OVERVIEW
+ " VARCHAR(1024), "
+ COLUMN_MESSAGE_STATUS
+ " INTEGER NOT NULL DEFAULT 0, "
+ COLUMN_MESSAGE_READ_STATUS
+ " INTEGER NOT NULL DEFAULT 0, "
+ COLUMN_CREATED
+ " INTEGER DEFAULT 0, "
+ COLUMN_UPDATED
+ " INTEGER DEFAULT 0);";
public final String CREATE_EXTRA_TEXT =
"CREATE TABLE IF NOT EXISTS "
+ TABLE_EXTRA_TEXT
+ " ("
+ COLUMN_ID
+ " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
+ COLUMN_MESSAGE_ID
+ " INTEGER NOT NULL, "
+ COLUMN_MESSAGE_EXTRA_TEXT_CONTENT
+ " VARCHAR(1024) NOT NULL, "
+ COLUMN_CREATED
+ " INTEGER DEFAULT 0, "
+ COLUMN_UPDATED
+ " INTEGER DEFAULT 0);";
public final String CREATE_EXTRA_IMAGE =
"CREATE TABLE IF NOT EXISTS "
+ TABLE_EXTRA_IMAGE
+ " ("
+ COLUMN_ID
+ " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
+ COLUMN_MESSAGE_ID
+ " INTEGER NOT NULL, "
+ COLUMN_MESSAGE_EXTRA_IMAGE_SAVE_PATH
+ " VARCHAR(256), "
+ COLUMN_MESSAGE_EXTRA_IMAGE_URL
+ " VARCHAR(1024), "
+ COLUMN_CREATED
+ " INTEGER DEFAULT 0, "
+ COLUMN_UPDATED
+ " INTEGER DEFAULT 0);";
public final String CREATE_EXTRA_AUDIO =
"CREATE TABLE IF NOT EXISTS "
+ TABLE_EXTRA_AUDIO
+ " ("
+ COLUMN_ID
+ " INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
+ COLUMN_MESSAGE_ID
+ " INTEGER NOT NULL, "
+ COLUMN_MESSAGE_EXTRA_AUDIO_SAVE_PATH
+ " VARCHAR(256), "
+ COLUMN_MESSAGE_EXTRA_AUDIO_URL
+ " VARCHAR(1024), "
+ COLUMN_MESSAGE_EXTRA_AUDIO_PLAY_TIME
+ " INTEGER NOT NULL DEFAULT 0, "
+ COLUMN_CREATED
+ " INTEGER DEFAULT 0, "
+ COLUMN_UPDATED
+ " INTEGER DEFAULT 0);";
// UserDB
public static final String INSERT_USER_SQL =
"INSERT INTO "
+ TABLE_USERS
+ "(`"
+ COLUMN_USER_ID
+ "`, `"
+ COLUMN_USER_NAME
+ "`, `"
+ COLUMN_USER_NICKNAME
+ "`, `"
+ COLUMN_USER_AVATAR
+ "`, `"
+ COLUMN_USER_TITLE
+ "`, `"
+ COLUMN_USER_POSITION
+ "`, `"
+ COLUMN_USER_ROLE_STATUS
+ "`, `"
+ COLUMN_USER_SEX
+ "`, `"
+ COLUMN_USER_DEPART_ID
+ "`, `"
+ COLUMN_USER_JOB_NUMBER
+ "`, `"
+ COLUMN_USER_TELPHONE
+ "`, `"
+ COLUMN_USER_EMAIL
+ "`, `"
+ COLUMN_CREATED
+ "`, `"
+ COLUMN_UPDATED
+ "`) VALUES(?,?,?,?,?, ?,?,?,?, ?,?, ?, ?, ?)";
public static final String SELECT_ALL_USER_SQL = "SELECT * FROM " + TABLE_USERS;
// ContactDB
public static final String INSERT_CONTACT_SQL =
"INSERT INTO "
+ TABLE_CONTACTS
+ "(`"
+ COLUMN_OWNER_ID
+ "`, `"
+ COLUMN_USER_ID
+ "`, `"
+ COLUMN_FRIEND_USER_ID
+ "`, `"
+ COLUMN_STATUS
+ "`, `"
+ COLUMN_CREATED
+ "`, `"
+ COLUMN_UPDATED
+ "`) VALUES(?, ?, ?, ?, ?, ?)";
public static final String SELECT_ALL_CONTACT_SQL = "SELECT * FROM " + TABLE_CONTACTS;
public static final String SELECT_ALL_FRIEND_USERID_SQL = "SELECT * FROM " + TABLE_CONTACTS;
public static final String LIMIT_ONE_CONTACT_SUFFIX =
" ORDER BY " + COLUMN_RELATE_ID + " asc limit 1";
// MessageDB
public static final String INSERT_MESSAGE_SQL =
"INSERT INTO "
+ TABLE_MESSAGES
+ "(`"
+ COLUMN_MESSAGE_ID
+ "`, `"
+ COLUMN_MESSAGE_PARENT_ID
+ "`, `"
+ COLUMN_OWNER_ID
+ "`, `"
+ COLUMN_RELATE_ID
+ "`, `"
+ COLUMN_MESSAGE_FROM_USER_ID
+ "`, `"
+ COLUMN_MESSAGE_TO_USER_ID
+ "`, `"
+ COLUMN_MESSAGE_TYPE
+ "`, `"
+ COLUMN_MESSAGE_DISPLAY_TYPE
+ "`, `"
+ COLUMN_MESSAGE_OVERVIEW
+ "`, `"
+ COLUMN_MESSAGE_STATUS
+ "`, `"
+ COLUMN_MESSAGE_READ_STATUS
+ "`, `"
+ COLUMN_CREATED
+ "`, `"
+ COLUMN_UPDATED
+ "`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
public static final String INSERT_MESSAGE_EXTRA_TEXT =
"INSERT INTO "
+ TABLE_EXTRA_TEXT
+ "(`"
+ COLUMN_MESSAGE_ID
+ "`, `"
+ COLUMN_MESSAGE_EXTRA_TEXT_CONTENT
+ "`, `"
+ COLUMN_CREATED
+ "`, `"
+ COLUMN_UPDATED
+ "`) VALUES(?, ?, ?, ?)";
public static final String INSERT_MESSAGE_EXTRA_IMAGE =
"INSERT INTO "
+ TABLE_EXTRA_IMAGE
+ "(`"
+ COLUMN_MESSAGE_ID
+ "`, `"
+ COLUMN_MESSAGE_EXTRA_IMAGE_SAVE_PATH
+ "`, `"
+ COLUMN_MESSAGE_EXTRA_IMAGE_URL
+ "`, `"
+ COLUMN_CREATED
+ "`, `"
+ COLUMN_UPDATED
+ "`) VALUES(?, ?, ?, ?, ?)";
public static final String INSERT_MESSAGE_EXTRA_AUDIO =
"INSERT INTO "
+ TABLE_EXTRA_AUDIO
+ "(`"
+ COLUMN_MESSAGE_ID
+ "`, `"
+ COLUMN_MESSAGE_EXTRA_AUDIO_SAVE_PATH
+ "`, `"
+ COLUMN_MESSAGE_EXTRA_AUDIO_URL
+ "`, `"
+ COLUMN_MESSAGE_EXTRA_AUDIO_PLAY_TIME
+ "`, `"
+ COLUMN_CREATED
+ "`, `"
+ COLUMN_UPDATED
+ "`) VALUES(?, ?, ?, ?, ?, ?)";
public static final String LIMIT_ONE_MESSAGE_SUFFIX =
" ORDER BY " + COLUMN_MESSAGE_ID + " desc , " + COLUMN_CREATED + " desc limit 1";
private static DBHelper instance;
public static DBHelper getInstance(Context context) {
if (null == instance) {
instance = new DBHelper(context);
}
return instance;
}
private DBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
public static int getVersion() {
return DATABASE_VERSION;
}
// 数据库第一次被创建时onCreate会被调用
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(this.CREATE_USERS);
db.execSQL(this.CREATE_CONTACTS);
db.execSQL(this.CREATE_MESSAGES);
db.execSQL(this.CREATE_EXTRA_TEXT);
db.execSQL(this.CREATE_EXTRA_IMAGE);
db.execSQL(this.CREATE_EXTRA_AUDIO);
}
// 如果DATABASE_VERSION值被改为2,系统发现现有数据库版本不同,即会调用onUpgrade
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO 更改数据库版本的操作 暂时先直接删除旧表
logger.d("moguimdb onUpgrade DB");
// updateTable(db); //升级 测试可用
db.execSQL("DROP TABLE IF EXISTS " + DBHelper.TABLE_USERS);
db.execSQL("DROP TABLE IF EXISTS " + DBHelper.TABLE_CONTACTS);
db.execSQL("DROP TABLE IF EXISTS " + DBHelper.TABLE_MESSAGES);
db.execSQL("DROP TABLE IF EXISTS " + DBHelper.TABLE_EXTRA_TEXT);
db.execSQL("DROP TABLE IF EXISTS " + DBHelper.TABLE_EXTRA_IMAGE);
db.execSQL("DROP TABLE IF EXISTS " + DBHelper.TABLE_EXTRA_AUDIO);
onCreate(db);
}
@Override
public void onOpen(SQLiteDatabase db) {
logger.d("moguimdb onOpen DB");
super.onOpen(db);
// TODO 每次成功打开数据库后首先被执行
}
@Override
public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
logger.d("moguimdb onDowngrade DB");
onUpgrade(db, oldVersion, newVersion);
}
@SuppressWarnings("unused")
private void updateTable(SQLiteDatabase db) {
try {
db.beginTransaction();
logger.d("moguimdb onUpgrade DB table start");
// rename the table
String alterMessageTableSql = getAlterTableSql(DBHelper.TABLE_MESSAGES);
db.execSQL(alterMessageTableSql);
logger.d("moguimdb onUpgrade DB alter table");
// creat table
db.execSQL(CREATE_MESSAGES); // TODO 升级时改成新表名
logger.d("moguimdb onUpgrade DB create new table");
// load data
logger.d("moguimdb onUpgrade DB load data");
String selectColumns = getColumnNames(db, DBHelper.TABLE_MESSAGES + TEMP_SUFFIX); // 记得用旧表名哦
String updateMessagesSql =
"insert into "
+ DBHelper.TABLE_MESSAGES
+ " ("
+ selectColumns
+ ") "
+ "select "
+ selectColumns
+ ""
+ " "
+ " from "
+ DBHelper.TABLE_MESSAGES
+ TEMP_SUFFIX;
logger.d("moguimdb onUpgrade DB updateMessagesSql : " + updateMessagesSql);
db.equals(updateMessagesSql);
// drop the oldtable
logger.d("moguimdb onUpgrade DB drop old table");
String dropMessageTableSql = getDropTableSql(DBHelper.TABLE_MESSAGES + TEMP_SUFFIX);
db.execSQL(dropMessageTableSql);
logger.d("moguimdb onUpgrade DB table end");
db.setTransactionSuccessful();
} catch (Exception e) {
logger.d("moguimdb onUpgrade DB error with reason: " + e.toString());
} finally {
db.endTransaction();
}
}
private String getAlterTableSql(String oldTableName) {
return "alter table " + oldTableName + " rename to " + oldTableName + TEMP_SUFFIX;
}
private String getDropTableSql(String tableName) {
return "drop table if exists " + tableName;
}
// 获取升级前表中的字段
protected String getColumnNames(SQLiteDatabase db, String tableName) {
StringBuffer sbSelect = null;
Cursor c = null;
try {
c = db.rawQuery("PRAGMA table_info(" + tableName + ")", null);
if (null != c) {
int columnIndex = c.getColumnIndex("name");
if (-1 == columnIndex) {
return null;
}
int index = 0;
int pos = c.getCount() + 1; // 并标记最后一个不加逗号，
// 由于index从0开始，所有这里不用加2,只加1
sbSelect = new StringBuffer(c.getCount() + 2); // //字段总列数，增加2列需要加2
for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
sbSelect.append(c.getString(columnIndex));
index++;
if (index < pos) {
sbSelect.append(",");
}
}
}
} catch (Exception e) {
logger.e(e.getMessage());
} finally {
if (null != c) {
c.close();
}
}
return sbSelect.toString();
}
/**
* 查询DB中某表的最后一条ID
*
* @return msgId
*/
public int queryLastInsertId(String tableName) {
int lastMsgId = 0;
Cursor c = null;
SQLiteDatabase dbSlaver = null;
try {
dbSlaver = getReadableDatabase();
c = dbSlaver.rawQuery("select last_insert_rowid() from " + tableName, null);
if (c.moveToFirst()) {
lastMsgId = c.getInt(0);
}
} catch (SQLException e) {
logger.e(e.toString());
} finally {
if (null != c) {
c.close();
}
// dbSlaver.close(); //这里不能关闭哦，因为是在其它DB语句中间操作的，由其它DB操作部分关闭即可
}
return lastMsgId;
}
}