什么是正确的方式升级sqllite db？

问题描述

private static final String TABLE_MAIN_CREATE = "CREATE TABLE IF NOT EXISTS " + TABLE_MAIN_NAME + " ( a INTEGER, b LONG, c TEXT, d TEXT, e DATETIME, f TEXT)";
private static final String TABLE_MAIN_UPGRADE = "ALTER TABLE " + TABLE_MAIN_NAME + " ADD Column f TEXT";

    @Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(TABLE_MAIN_CREATE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < newVersion) {
        db.execSQL(TABLE_MAIN_UPGRADE);
    }
    onCreate(db);
}

上一个db版本没有f字段。
这是升级DB的正确方法吗？
我需要在onUpgrade中的onCreate（db）？

The previous db version was without the "f" field.Is this the correct way to upgrade the DB?Do I need onCreate(db) in onUpgrade?

我做了几次，每次我有另一个异常：
1）table already存在，所以我添加了IF NOT EXISTS
2）无法读取行0，col 5错误这里我失去了....

I did this several times and each time I got another exception:1) table already exists so I added "IF NOT EXISTS"2) could not read row 0, col 5 error here I lost it....

正确的方式升级db？

我想保留我的数据

推荐答案

注意：这是一个很好的和有效的解决方案，但有很多人downvote此答案

Caution: This is a good and working solution but there is are a lot of people who downvote this answer because they don't know java (case from 10 to 12 are without break;)

您必须执行某些操作取决于 oldVersion 。

You have to do something depends on oldVersion.

fx：你可以有从10到14的数据库版本，然后10你不支持和在每个新版本，你添加一个新的代码应该看起来像（newVersion是14） ：

fx: you can have db versions from 10-14, the older then 10 you dont wana support and at every new version you add a new column the code should looks like(newVersion is 14) :

private static final String TABLE_MAIN_CREATE_14 =
    "CREATE TABLE IF NOT EXISTS " + TABLE_MAIN_NAME +
    " ( a INTEGER, b LONG, c TEXT, d TEXT, e DATETIME, f TEXT, g TEXT, h TEXT, i TEXT)";
private static final String TABLE_MAIN_UPGRADE_10_11 =
    "ALTER TABLE " + TABLE_MAIN_NAME + " ADD Column f TEXT";
private static final String TABLE_MAIN_UPGRADE_11_12 =
    "ALTER TABLE " + TABLE_MAIN_NAME + " ADD Column g TEXT";
private static final String TABLE_MAIN_UPGRADE_12_13 =
    "ALTER TABLE " + TABLE_MAIN_NAME + " ADD Column h TEXT";
private static final String TABLE_MAIN_UPGRADE_13_14 =
    "ALTER TABLE " + TABLE_MAIN_NAME + " ADD Column i TEXT";
    @Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(TABLE_MAIN_CREATE_14);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch(oldVersion){
            case 10: // from 10 to 11 and go to next case
                db.execSQL(TABLE_MAIN_UPGRADE_10_11);
            case 11: // from 11 to 12 and go to next case
                db.execSQL(TABLE_MAIN_UPGRADE_11_12);
            case 12: // from 12 to 13 and go to next case
                db.execSQL(TABLE_MAIN_UPGRADE_12_13);
            case 13: // from 13 to newVersion
                db.execSQL(TABLE_MAIN_UPGRADE_13_14);
                break;
            default:
                //not upgratable too old - so we should drop and recreate;
                db.execSQL("DROP TABLE IF EXISTS " + TABLE_MAIN_NAME  );
                onCreate(db);
                break;
    }
}

这篇关于什么是正确的方式升级sqllite db？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！