困惑:SQLiteOpenHelper onUpgrade() 表现如何?并与旧数据库备份的导入一起? [英] Confusion: How does SQLiteOpenHelper onUpgrade() behave? And together with import of an old database backup?

问题描述

假设我在 SQLiteOpenHelper 中有一个包含 2 列的数据库表 test_table 和相应的创建脚本:

let's assume I have a database table test_table with 2 columns and a corresponding create script in the SQLiteOpenHelper:

DB_VERSION = 1:
public void onCreate(SQLiteDatabase db)
{
db.execSql("CREATE table test_table (COL_A, COL_B);
}

这是在 Play 商店中发布的初始应用版本 1.

This is the initial app version 1, which is published in the Play Store.

一段时间后，应用程序和使用的数据库会进行更新.我想 SQLiteOpenHelper 类必须像这样修改:

After a while there's an update to the app and the utilized database. I guess the SQLiteOpenHelper class has to be adapted like this:

DB_VERSION = 2:
public void onCreate(SQLiteDatabase db)
{
db.execSql("CREATE table test_table (COL_A, COL_B, COL_C)");
}

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.execSql("ALTER TABLE test_table ADD Column COL_C");
}

一段时间后，另一个应用更新:

After some time, another app update:

DB_VERSION = 3:
public void onCreate(SQLiteDatabase db)
{
db.execSql("CREATE table test_table (COL_A, COL_B, COL_C, COL_D)");
}

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
db.execSql("ALTER TABLE test_table ADD Column COL_D");
}

--> 这就是我需要建议的地方.如果用户安装应用程序版本 1，则他有 A 列和 B 列.如果他随后更新到版本 2，则 onUpgrade 会触发并添加列 C.从头安装的新用户通过 create 语句获得 3 列.如果用户随后更新到版本 3，则 onUpgrade 再次触发并添加列 D.但是如果用户安装应用程序版本 1，然后跳过版本 2 的更新并更新版本 3 呢?那么他就会错过

--> This is where I need advice. If the user installs app version 1, he has Columns A and B. If he then updates to version 2, onUpgrade fires and adds a column C. New users who install from scratch get the 3 columns via the create statement. If the user then updates to version 3, onUpgrade fires again and a column D is added. But WHAT IF the user installs app version 1, then skips the update of version 2 and updates version 3? Then he would have missed the

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

    {
    db.execSql("ALTER TABLE test_table ADD Column COL_C");
    }

部分且仅

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)

    {
    db.execSql("ALTER TABLE test_table ADD Column COL_D");
    }

会被调用，这会导致一个表 test_table(COL_A, COL_B, COL_D)??

would be called, which leads to a table test_table(COL_A, COL_B, COL_D)??

处理实时应用程序数据库升级的正确方法是什么，这样用户就不会丢失他的数据?您是否必须检查 onUpgrade() 方法中所有可能的(旧)版本并根据该版本执行不同的 alter table 语句?

What's the correct way of handling database upgrades of a live app, so the user doesn't lose his data? Do you have to check all possible (old) versions in the onUpgrade() method and execute different alter table statements based on that version?

我之所以这么问是因为在我的应用中，用户可以导出和导入数据，这无非是导出:将整个数据库复制走并导入:将应用数据库替换为备份副本数据库.

I am asking because in my app, the user has the possibility to export and import the data, which is nothing more than export: copy the whole database away and import: replace the app database with the backup copy database.

如果用户拥有应用版本 1，导出数据库，升级应用(新的数据库结构)并导入旧版本 1 备份，会发生什么情况?--> SQLiteOpenHelper 的行为如何?--> 处理数据库升级和导入/导出功能的正确方法是什么?

What happens if the user has app version 1, exports the database, upgrades the app (new database structure) and imports the old version 1 backup? --> How will SQLiteOpenHelper behave? --> What is the correct way to handle db upgrades together with import/export functionality?

推荐答案

处理实时应用程序数据库升级的正确方法是什么，这样用户就不会丢失他的数据?您是否必须检查 onUpgrade() 方法中所有可能的(旧)版本并根据该版本执行不同的 alter table 语句?

What's the correct way of handling database upgrades of a live app, so the user doesn't lose his data? Do you have to check all possible (old) versions in the onUpgrade() method and execute different alter table statements based on that version?

总的来说，是的.

一种常见的方法是进行成对升级:

A common approach to this is to do pair-wise upgrades:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  if (oldVersion<2) {
    // do upgrade from 1 to 2
  }

  if (oldVersion<3) {
    // do upgrade from 2 to 3, which will also cover 1->3,
    // since you just upgraded 1->2
  }

  // and so on
}

例如，这大致相当于 Rails 迁移.

This roughly equates to Rails migrations, for example.

如果用户拥有应用版本 1，导出数据库，升级应用(新的数据库结构)并导入旧版本 1 备份，会发生什么情况?--> SQLiteOpenHelper 的行为如何?

What happens if the user has app version 1, exports the database, upgrades the app (new database structure) and imports the old version 1 backup? --> How will SQLiteOpenHelper behave?

如果将整个数据库复制掉"，您的字面意思是 SQLite 数据库文件的完整文件副本，那么当 SQLiteOpenHelper 打开恢复的备份时，数据库将具有旧的架构版本，将像往常一样通过 onUpgrade().

If by "copy the whole database away", you literally mean a full file copy of the SQLite database file, then when SQLiteOpenHelper goes to open the restored backup, it will that the database has the old schema version and will go through onUpgrade() as normal.

处理数据库升级和导入/导出功能的正确方法是什么?

What is the correct way to handle db upgrades together with import/export functionality?

我怀疑答案是:要么通过复制整个文件进行备份，要么安排备份和恢复架构版本，您可以通过在 getVersion() 上调用 getVersion()>SQLiteDatabase 对象.话虽如此，我还没有处理太多这种情况，可能还有更多我没有想到的问题.

I suspect the answer is: either make your backup by copying the entire file, or also arrange to backup and restore the schema version, which you can get by calling getVersion() on a SQLiteDatabase object. That being said, I haven't dealt with this scenario much, and there may be more issues that I am not thinking of.

这篇关于困惑:SQLiteOpenHelper onUpgrade() 表现如何?并与旧数据库备份的导入一起?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！