Android数据库升级解决方案

一、SQLiteOpenHelper介绍

    SQLiteOpenHelper有两个方法， public void onCreate(SQLiteDatabase db)以及public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)。第一次安装App时onCreate(SQLiteDatabase db)会被系统回调，通常在onCreate(SQLiteDatabase db)中创建数据表；当数据库版本升级时，系统会回调onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)，通常在onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)中做数据库升级方案。数据库的版本号在SQLiteOpenHelper的构造方法中传入。

二、数据库升级方案

1、onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)详细介绍

在创建SQLiteOpenHelper时传入的数据库版本号如果大于当前版本App数据库文件的版本号onUpgrade会被系统回调。oldVersion为当前版本App数据库文件的版本号， newVersion为SQLiteOpenHelper传入的数据库版本号，newVersion肯定大于oldVersion。

2、举例说明

假设App已经发版过V1、V2，即将发版V3。V1创建了数据表feedback，数据库版本为1；V2在V1的feedback基础上增加了一个字段username，数据库版本为2；V3在V2的基础上新增了一个表crash，数据库版本为3。V1发版时SQLiteOpenHelper如下：

public class DBOpenHelper extends SQLiteOpenHelper {
    private static final String TAG = DBOpenHelper.class.getSimpleName();

    public DBOpenHelper(Context context) {
        super(context, "feedback.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        LogUtil.log(TAG, "onCreate");
        String createSql = "create table feedback (" +
                "  online integer," +
                "  speechtype integer," +
                "  priority integer ," +
                "  domain char(100)," +
                "  keyparams char(200)," +
                "  success integer," +
                "  errorcode integer," +
                "  errormsg char(100)," +
                "  network integer," +
                "  networktype integer," +
                "  ip char(32)," +
                "  userid char(100)," +
                "  userphone char(20)," +
                "  useraddress char(200)," +
                "  starttime bigint," +
                "  finishtime bigint" + ")";
        db.execSQL(createSql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        LogUtil.log(TAG, "onUpgrade");
    }

}

V2需要将feedback增加一个字段username，用户从V1升级到V2时，需要改变库表结构并且将原来的feedback进行迁移，V2发版时SQLiteOpenHelper如下：

public class DBOpenHelper extends SQLiteOpenHelper {
    private static final String TAG = DBOpenHelper.class.getSimpleName();

    public DBOpenHelper(Context context) {
        super(context, "feedback.db", null, 2);
    }

    /**
     * 新增字段username
     * @param db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        LogUtil.log(TAG, "onCreate");
        String createSql = "create table feedback (" +
                "  online integer," +
                "  speechtype integer," +
                "  priority integer ," +
                "  domain char(100)," +
                "  keyparams char(200)," +
                "  success integer," +
                "  errorcode integer," +
                "  errormsg char(100)," +
                "  network integer," +
                "  networktype integer," +
                "  ip char(32)," +
                "  userid char(100)," +
                "  username char(100),"+
                "  userphone char(20)," +
                "  useraddress char(200)," +
                "  starttime bigint," +
                "  finishtime bigint" + ")";
        db.execSQL(createSql);
    }

    /**
     * 用户从V1升级到V2时改变库表结构并将V1数据库中的数据进行迁移
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        LogUtil.log(TAG, "onUpgrade");
        if (oldVersion==1) {//用户当前是V1版本
            //重命名
            String rename = "alter TABLE feedback RENAME TO feedback_temp";
            db.execSQL(rename);
            //建立新表
            onCreate(db);
            //数据拷贝
            String copy = "insert into feedback(online,speechtype,priority,domain,keyparams,success,errorcode,errormsg,network,networktype,ip,userid,userphone,useraddress,starttime,finishtime) " +
                    "select (online,speechtype,priority,domain,keyparams,success,errorcode,errormsg,network,networktype,ip,userid,userphone,useraddress,starttime,finishtime) from feedback_temp";
            db.execSQL(copy);
            //删除原表
            String delete = "drop table feedback_temp";
            db.execSQL(delete);
        }
    }

}

V3在V2的基础上增加了表crash，用户升级方案可能是：V1-V3,V2-V3，V3发版时SQLiteOpenHelper如下：

public class DBOpenHelper extends SQLiteOpenHelper {
    private static final String TAG = DBOpenHelper.class.getSimpleName();

    public DBOpenHelper(Context context) {
        super(context, "feedback.db", null, 3);
    }

    /**
     * 建表feedback、crash
     *
     * @param db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        LogUtil.log(TAG, "onCreate");
        String createFeedbackSql = "create table feedback (" +
                "  online integer," +
                "  speechtype integer," +
                "  priority integer ," +
                "  domain char(100)," +
                "  keyparams char(200)," +
                "  success integer," +
                "  errorcode integer," +
                "  errormsg char(100)," +
                "  network integer," +
                "  networktype integer," +
                "  ip char(32)," +
                "  userid char(100)," +
                "  username char(100)," +
                "  userphone char(20)," +
                "  useraddress char(200)," +
                "  starttime bigint," +
                "  finishtime bigint" + ")";
        String createCrashSql = "create table crash (starttime bigint,finishtime bigint)";
        db.execSQL(createFeedbackSql);
        db.execSQL(createCrashSql);
    }

     /**
     * 用户的升级方案可能为V1-V3；V2-V3
     *
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        LogUtil.log(TAG, "onUpgrade");
        if(oldVersion==1) {//v1升级到v3
            upgradeToV2(db);
            upgradeToV3(db);
        }else if (oldVersion==2) {//v2升级到v3
            upgradeToV3(db);
        }

    }

    private void upgradeToV2(SQLiteDatabase db) {
        //重命名
        String rename = "alter TABLE feedback RENAME TO feedback_temp";
        db.execSQL(rename);
        //建立新表
        String createFeedbackSql = "create table feedback (" +
                "  online integer," +
                "  speechtype integer," +
                "  priority integer ," +
                "  domain char(100)," +
                "  keyparams char(200)," +
                "  success integer," +
                "  errorcode integer," +
                "  errormsg char(100)," +
                "  network integer," +
                "  networktype integer," +
                "  ip char(32)," +
                "  userid char(100)," +
                "  username char(100)," +
                "  userphone char(20)," +
                "  useraddress char(200)," +
                "  starttime bigint," +
                "  finishtime bigint" + ")";
        db.execSQL(createFeedbackSql);

        //数据拷贝
        String copy = "insert into feedback(online,speechtype,priority,domain,keyparams,success,errorcode,errormsg,network,networktype,ip,userid,userphone,useraddress,starttime,finishtime) " +
                "select * from feedback_temp";
        db.execSQL(copy);
        //删除原表
        String delete = "drop table feedback_temp";
        db.execSQL(delete);
    }

    private void upgradeToV3(SQLiteDatabase db) {
        String createCrashSql = "create table crash (starttime bigint,finishtime bigint)";
        db.execSQL(createCrashSql);
    }