将 SQLite 数据库从一个版本升级到另一个版本? [英] Upgrade SQLite database from one version to another?

问题描述

我收到来自 Logcat 的错误消息，指出某个列(在我的 SQLiteOpenHelper 子类中)不存在.我想我可以通过更改 DATABASE_CREATE 字符串来升级数据库.但显然不是，那么我如何(逐步)将我的 SQLite 数据库从版本 1 升级到版本 2?

I am getting an error from Logcat saying that a certain column (in my SQLiteOpenHelper subclass) does not exist. I thought I could upgrade the database by changing the DATABASE_CREATE string. But apparently not, so how can I (step-by-step) upgrade my SQLite Database from version 1 to version 2?

如果问题看起来很菜鸟"，我深表歉意，但我仍在学习 Android.

I apologize if the question seems "noobish", but I am still learning about Android.

@Pentium10 这就是我在 onUpgrade 中所做的:

@Pentium10 This is what I do in onUpgrade:

private static final int DATABASE_VERSION = 1;

....

switch (upgradeVersion) {
case 1:
    db.execSQL("ALTER TABLE task ADD body TEXT");
    upgradeVersion = 2;
    break;
}

...

推荐答案

好的，在你遇到更大的问题之前，你应该知道 SQLite 对 ALTER TABLE 命令的限制，它允许 add 和 rename 只有没有删除/删除，这是通过重新创建表来完成的.

Ok, before you run into bigger problems you should know that SQLite is limited on the ALTER TABLE command, it allows add and rename only no remove/drop which is done with recreation of the table.

您应该始终手头有新的表创建查询，并将其用于升级和传输任何现有数据.注意:onUpgrade 方法为您的 sqlite 助手对象运行一个，您需要处理其中的所有表.

You should always have the new table creation query at hand, and use that for upgrade and transfer any existing data. Note: that the onUpgrade methods runs one for your sqlite helper object and you need to handle all the tables in it.

那么推荐 onUpgrade:

So what is recommended onUpgrade:

• 开始交易
• 使用if not exists 运行一个表创建(我们正在进行升级，所以该表可能还不存在，它会失败并删除)
• 将现有列放入一个列表 List;columns = DBUtils.GetColumns(db, TableName);
• 备份表 (ALTER table " + TableName + " RENAME TO 'temp_" + TableName)
• 创建新表(最新的表创建模式)
• 获取与新列的交集，这次是从升级表中提取的列 (columns.retainAll(DBUtils.GetColumns(db, TableName));)
• 恢复数据(String cols = StringUtils.join(columns, ",");db.execSQL(String.format("INSERT INTO %s (%s) SELECT %s from temp_%s",TableName, cols, cols, TableName));)
• 删除备份表(DROP table 'temp_" + TableName)
• setTransactionSuccessful

• beginTransaction
• run a table creation with if not exists (we are doing an upgrade, so the table might not exists yet, it will fail alter and drop)
• put in a list the existing columns List<String> columns = DBUtils.GetColumns(db, TableName);
• backup table (ALTER table " + TableName + " RENAME TO 'temp_" + TableName)
• create new table (the newest table creation schema)
• get the intersection with the new columns, this time columns taken from the upgraded table (columns.retainAll(DBUtils.GetColumns(db, TableName));)
• restore data (String cols = StringUtils.join(columns, ","); db.execSQL(String.format( "INSERT INTO %s (%s) SELECT %s from temp_%s", TableName, cols, cols, TableName)); )
• remove backup table (DROP table 'temp_" + TableName)
• setTransactionSuccessful

.

public static List<String> GetColumns(SQLiteDatabase db, String tableName) {
    List<String> ar = null;
    Cursor c = null;
    try {
        c = db.rawQuery("select * from " + tableName + " limit 1", null);
        if (c != null) {
            ar = new ArrayList<String>(Arrays.asList(c.getColumnNames()));
        }
    } catch (Exception e) {
        Log.v(tableName, e.getMessage(), e);
        e.printStackTrace();
    } finally {
        if (c != null)
            c.close();
    }
    return ar;
}

public static String join(List<String> list, String delim) {
    StringBuilder buf = new StringBuilder();
    int num = list.size();
    for (int i = 0; i < num; i++) {
        if (i != 0)
            buf.append(delim);
        buf.append((String) list.get(i));
    }
    return buf.toString();
}

这篇关于将 SQLite 数据库从一个版本升级到另一个版本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！