예제 #1
0
  // 获取升级前表中的字段
  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();
  }
예제 #2
0
  // 如果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);
  }
예제 #3
0
  @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();
    }
  }
예제 #4
0
  /**
   * 查询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;
    }
  }
}
예제 #7
0
 @Override
 public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   logger.d("moguimdb onDowngrade DB");
   onUpgrade(db, oldVersion, newVersion);
 }
예제 #8
0
 @Override
 public void onOpen(SQLiteDatabase db) {
   logger.d("moguimdb onOpen DB");
   super.onOpen(db);
   // TODO 每次成功打开数据库后首先被执行
 }
예제 #9
0
/** @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;
  }
}