SQLiteOpenHelper onUpgrade() 混淆 Android

SQLiteOpenHelper onUpgrade() Confusion Android

提问by Mike

I am doing my first app with a database and I am having a little trouble understanding the onUpgrade function. My database has a table with an items and a favorite column so that the user can favorite an item. Most implementations I see simply drop the table and reconstruct it but I don't want to do this. I want to be able to add more items to the table.

我正在使用数据库开发我的第一个应用程序，但在理解 onUpgrade 函数时遇到了一些麻烦。我的数据库有一个表，其中包含一个项目和一个最喜欢的列，以便用户可以收藏一个项目。我看到的大多数实现只是删除表并重建它，但我不想这样做。我希望能够向表格中添加更多项目。

When the app is upgraded through the android marketplace does the database know its version number? So could I increment the version number in the code and then export it to the marketplace and when the user boots up the upgraded version for the first time then onUpgrade will be called?

当应用程序通过 android 市场升级时，数据库是否知道它的版本号？那么我可以增加代码中的版本号，然后将其导出到市场，当用户第一次启动升级版本时，将调用 onUpgrade 吗？

If this is the case my onUpgrade would simply pull from a file and add the database items in. Is this a standard way of doing things or is there a better way of handling this in Android. I am trying to stay as standard as possible.

如果是这种情况，我的 onUpgrade 将简单地从文件中提取并添加数据库项。这是一种标准的做事方式还是在 Android 中处理这个问题的更好方法。我尽量保持标准。

Thanks

谢谢

回答by Pentium10

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

好的，在遇到更大的问题之前，您应该知道 SQLite 仅限于 ALTER TABLE 命令，它允许add并且rename只允许不通过重新创建表来完成删除/删除。

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 方法为您的 sqlite helper 对象运行一个，您需要处理其中的所有表。

So what is recommended onUpgrade:

那么什么是推荐 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

• 开始交易
• 运行一个表创建if not exists（我们正在进行升级，所以该表可能还不存在，它会失败并删除）
• 将现有列放入列表中 List<String> 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)
• 设置交易成功

(This doesn't handle table downgrade, if you rename a column, you don't get the existing data transfered as the column names do not match).

（这不处理表降级，如果您重命名列，则不会因为列名不匹配而传输现有数据）。

.

.

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();
}

回答by pjv

Next to Pentium10's excellent answer, here are some good examples from living code:

在 Pentium10 的出色回答旁边，这里有一些来自活代码的很好的例子：

• Android AOSP: com.android.providers.calendar.CalendarDatabaseHelper.java

• Android AOSP: com.android.browser.BrowserProvider.java

• OpenIntents Notepad: org.openintents.notepad.NotePadProvider.java

• Android AOSP：com.android.providers.calendar.CalendarDatabaseHelper.java

• Android AOSP：com.android.browser.BrowserProvider.java

• OpenIntents 记事本：org.openintents.notepad.NotePadProvider.java

回答by lazyList

Thank you for clarifying that onUpgrade() will not support Remove/Drop statements @Pentium 10

感谢您澄清 onUpgrade() 将不支持 Remove/Drop 语句@Pentium 10

For those of you who would like to know the exact moment when onUpgrade() gets called, it is during a call to either getReadableDatabase() or getWriteableDatabase().

对于那些想知道 onUpgrade() 被调用的确切时刻的人来说，它是在调用 getReadableDatabase() 或 getWriteableDatabase() 期间。

To those who are not clear how it ensure it gets triggered...the answer is: It is triggered when the database version provided to the constructor of SqLiteOpenHelper is updated. Here is a example

对于那些不清楚它如何确保它被触发的人......答案是：它在提供给 SqlLiteOpenHelper 的构造函数的数据库版本更新时触发。这是一个例子

public class dbSchemaHelper extends SQLiteOpenHelper {

private String sql;
private final String D_TAG = "FundExpense";
//update this to get onUpgrade() method of sqliteopenhelper class called
static final int DB_VERSION = 2; 
static final String DB_NAME = "fundExpenseManager";

public dbSchemaHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
    // TODO Auto-generated constructor stub
}

now to...onUpgrade()

现在到...onUpgrade()

@Override
public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
    sql = "ALTER TABLE " + fundExpenseSchema.Expense.TABLE_NAME + " ADD COLUMN " + fundExpenseSchema.Expense.FUNDID + " INTEGER";
    arg0.execSQL(sql);
}

回答by JavierSP1209

I've been using the solution proposed by @Pentium10 for a long time but today i had a problem, after doing alter table, getColumns from the original table still returns the same columns (in the new version of the db the table suffer mayor structure changes, some columns added some others), really i don't know why select statement does not reflect the structure changes, more over before creating my table again, select statement still returns the columns! When the table is not re-created yet!

我一直在使用@Pentium10 提出的解决方案很长时间了，但今天我遇到了一个问题，在执行alter table 之后，原始表中的getColumns 仍然返回相同的列（在新版本的db 中，表遭受了市长结构更改，一些列添加了一些其他列），我真的不知道为什么 select 语句没有反映结构更改，在再次创建我的表之前，select 语句仍然返回列！当表尚未重新创建时！

So i manage solving this issue updating getColumns method using pragma table_info, like this:

所以我设法解决这个问题，使用更新 getColumns 方法pragma table_info，如下所示：

    /**
 * Get a list of column base_dictionary for the selected table
 *
 * @param db
 *  Database that contains the table
 * @param tableName
 *  Table name to be used
 * @return A List of column name
 */
public static List<String> getColumns(SQLiteDatabase db, String tableName) {
    List<String> ar = null;
    Cursor c = null;
    try {
        c = db.rawQuery("pragma table_info(" + tableName + ")", null);

        ar = new ArrayList<String>();
        if (c != null && c.moveToFirst()) {
            do {
                ar.add(c.getString(c.getColumnIndexOrThrow("name")));
            } while (c.moveToNext());
            c.close();
        }

    } catch (Exception e) {
        Log.v(tableName, e.getMessage(), e);
        e.printStackTrace();
    } finally {
        if (c != null) c.close();
    }
    return ar;
}