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

问题描述

我从的logcat 说，某列（在我的 SQLiteOpenHelper 子类）不存在收到错误。我想我可以通过改变 DATABASE_CREATE 字符串升级数据库。但显然不是，所以我怎么能（一步一步）从版本1升级我的SQLite数据库到第2版？

我很抱歉，如果这个问题似乎noobish，但我仍然在学习关于An​​droid。

@ Pentium10这是我做的onUpgrade：

 私有静态最终诠释DATABASE_VERSION = 1;

....

开关（upgradeVersion）{
情况1：
    db.execSQL（ALTER TABLE任务添加正文）;
    upgradeVersion = 2;
    打破;
}

...
 

解决方案

好吧，你遇到更大的问题之前，你应该知道，SQLite是在ALTER TABLE命令的限制，它可以让添加和改名只是没有remove /下降这与表中的休闲完成。

您应该始终手边的新表的创建语句，并利用它来进行升级和转移现有数据。注：该onUpgrade方法运行一个为你的sqlite的辅助对象，你需要处理它所有的表

那么，什么是推荐onUpgrade：

• 的BeginTransaction
• 运行一表的创建与如果不存在（我们正在做一个升级，因此该表可能不存在，但它将无法修改和删除）
• 放在一个列表中的现有列名单，其中，字符串＆GT;列= DBUtils.GetColumns（DB，表名）;
• 备份表（ ALTER TABLE+表名+重命名为temp_+表名）
• 创建新表（最新表创建模式）
• 获得交叉口新列，从升级后的表采取这个时候的列（ columns.retainAll（DBUtils.GetColumns（DB，表名））; ）
• 在恢复数据（字符串COLS = StringUtils.join（列，）;             db.execSQL（的String.Format（                     INSERT INTO％S（％S）从temp_％s选择％S，                     表名，COLS，COLS，表名））; ）
• 删除备份的表（ DROP TABLEtemp_+表名）
• setTransactionSuccessful

 公共静态列表＆LT;字符串＆GT;了getColumns（SQLiteDatabase分贝，串tableName值）{
    名单＆LT;字符串＆GT;芳=无效;
    光标C = NULL;
    尝试 {
        C = db.rawQuery（选择*+ tableName值+限制1，NULL）;
        如果（C！= NULL）{
            AR =新的ArrayList＆LT;字符串＆GT;（Arrays.asList（c.getColumnNames（）））;
        }
    }赶上（例外五）{
        Log.v（tableName值，e.getMessage（），E）;
        e.printStackTrace（）;
    } 最后 {
        如果（C！= NULL）
            c.close（）;
    }
    返回AR;
}

公共静态字符串连接（名单＆LT;字符串＆GT;列表中，字符串DELIM）{
    StringBuilder的BUF =新的StringBuilder（）;
    INT NUM =则为list.size（）;
    的for（int i = 0; I＆LT;民;我++）{
        如果（ⅰ！= 0）
            buf.append（DELIM）;
        buf.append（（字符串）list.get（一））;
    }
    返回buf.toString（）;
}
 

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?

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

@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;
}

...

解决方案

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.

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.

So what is recommended onUpgrade:

• 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屋！