难以升级SQlite表 [英] Difficulty in upgrading SQlite table

问题描述

我有一个运行有名为ANIMAL的工作表的应用程序.首次创建此表时，它仅由_id和animal_name列组成.

I have an application running with a working table called ANIMAL. Upon first creating this table it consisted simply of _id and animal_name columns.

现在我试图在其中进行扩展，包括animal_biography专栏，但是我遇到了一些困难.起初，我以为只是升级我的CREATE_TABLE语句以包含动物bio的一种情况:

Now I am trying to expand on it, including a animal_biography column, however I am having a little difficulty.At first I thought I was just a case of upgrading my CREATE_TABLE statement to include the animal bio:

private static final String DATABASE_CREATE =       
                        "create table " + ANIMALS_TABLE +
                        " (_id integer primary key autoincrement, " + 
                        "animal_name text not null, " +
                        "biography text not null);"; 

但是，看着logcat告诉我，当试图插入列传记时，该列传记不存在.

however, looking at the logcat it tells me the column biography does not exist when trying to insert into it.

现在，我尝试使用onUpgrade()并包含代码

Now, I have tried to upgrade the database by using the onUpgrade() and including the code

db.execSQL("ALTER TABLE" + DATABASE_NAME);
db.execSQL(DATABASE_CREATE);

但是这也不能解决问题.没有人对如何解决此问题有任何指示吗?

but this is not solving the problem either. Does anyone have any pointers on how to go about fixing this problem?

推荐答案

如果您使用的是SQLiteOpenHelper，则升级表很容易.您需要实现方法onCreate和onUpgrade，并在类构造函数中提供数据库的当前版本.在更新表时，只需增加数据库版本号，即可在onCreate方法中指定新的表创建查询，并将ALTER TABLE放入onUpgrade方法以更新表的先前版本.当Android检测到数据库版本不匹配时，它将自动调用onUpgrade方法.参见示例:

If you are using SQLiteOpenHelper it's easy to upgrade a table. You need to implement methods onCreate and onUpgrade and provide current version of your database in class constructor. When updating your table just increment database version number, specify new table creation query in onCreate method and put ALTER TABLE to onUpgrade method to update previous version of table. When Android detects database version mismatch, it will call onUpgrade method automatically. See the example:

public class OpenHelper extends SQLiteOpenHelper {

    private final static int    DB_VERSION = 2;

    public TracksDB(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        final String CREATE_TBL =
            "create table " + ANIMALS_TABLE +
            " (_id integer primary key autoincrement, " + 
            "animal_name text not null, " +
            "biography text not null);";
             db.execSQL(CREATE_TBL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
            final String ALTER_TBL = 
                "ALTER TABLE " + ANIMALS_TABLE +
                " ADD COLUMN biography text not null;";
            db.execSQL(ALTER_TBL);
        }
    }
}

这种升级方法是在不丢失用户数据的情况下修改表的正确方法，特别是如果该应用程序已经公开发布.

This method of upgrading is the correct way to modify tables without losing user data, especially if the app had been released to public already.

这篇关于难以升级SQlite表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！