为什么SQLiteOpenHelper在“onUpgrade”方法中删除表？

I learned a way which use SQLite3 on Android OS recently. But it has a non acceptable part.

我学会了最近在Android OS上使用SQLite3的方法。但它有一个不可接受的部分。

I can't understand why drop the table when called "onUpgrade" method of SQLiteOpenHelper. Why need "onUpgarde" method?

我无法理解为什么在调用SQLiteOpenHelper的“onUpgrade”方法时删除表。为什么需要“onUpgarde”方法？

If code executes "drop table", table data of old version DB will be removed, isn't it?

如果代码执行“drop table”，旧版本DB的表数据将被删除，不是吗？

Why delete existing data of old DB?

为什么要删除旧数据库的现有数据？

How to restore existing DB data when drop the table?

删除表时如何恢复现有的DB数据？

[Here is learned code]

[这是学习代码]

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

    final String CONFIRMED_SHEETS_TABLE = "confirmed_sheets";

    public MySQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        String sql = "create table" +
                CONFIRMED_SHEETS_TABLE +
                "_id integer primary key autoincrement, " +
                "group text, " +
                "num001 text, " +
                "num002 text, " +
                "num003 text, " +
                "num004 text, " +
                "num005 text, " +
                "num006 text, " +
                "date text)";
        sqLiteDatabase.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {

        // non acceptable part //
        String sql = "drop table if exists " + CONFIRMED_SHEETS_TABLE;
        sqLiteDatabase.execSQL(sql);

        onCreate(sqLiteDatabase);
    }
}

3 个解决方案

#1

If code executes "drop table", DB table data of old version DB will be removed, isn't it?

如果代码执行“drop table”，旧版本DB的DB表数据将被删除，不是吗？

Yup

对

Why need "onUpgrade" method?

为什么需要“onUpgrade”方法？

If you are switching databases (for example because you added a new column), your app (usually) now depends on that change. Increasing the database version in your Helper class calls onUpgrade(), which allows you to take care of any migration to prepare the app to use your new schema.

如果要切换数据库（例如，因为您添加了新列），您的应用程序（通常）现在取决于该更改。增加Helper类中的数据库版本会调用onUpgrade（），这允许您处理任何迁移以准备应用程序以使用新架构。

Did you know why learned code executes "drop table"?

您知道为什么学习代码会执行“drop table”吗？

Convenience. It's not necessarily the right approach, but a database change can make it hard to take old data and merge it in the new table. Thus, it is easier logic-wise to simply start anew.

方便。它不一定是正确的方法，但数据库更改可能会使旧数据难以合并并将其合并到新表中。因此，简单地重新开始就更容易逻辑化。

If you want to merge an existing and new table, have a look at this question.

如果要合并现有表和新表，请查看此问题。

#2

You do not need to perform a DROP TABLE in onUpgrade(), as it is currently written in your code. The purpose of onUpgrade() is for your app to check if a new version of your app's database is being installed on a user's device, and if so, if there are any changes to your database, such as adding a new column, you can make those changes within onUpgrade(). If you never change your database schema, you never need to do anything in onUpgrade().

您不需要在onUpgrade（）中执行DROP TABLE，因为它当前已在您的代码中编写。 onUpgrade（）的目的是让您的应用程序检查是否在用户的设备上安装了新版本的应用程序数据库，如果是，如果您的数据库有任何更改，例如添加新列，则可以在onUpgrade（）中进行这些更改。如果您从未更改过数据库模式，则无需在onUpgrade（）中执行任何操作。

For more information and a introductory tutorial on Databases in Android, refer to the Notepad sample code here.

有关Android中数据库的更多信息和入门教程，请参阅此处的记事本示例代码。

EDIT: also, here's an example of an onUpgrade() I wrote for one of my apps:

编辑：另外，这是我为我的一个应用程序写的onUpgrade（）的一个例子：

  /**
    * Handle upgrades to the database.
    */
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
       if(DBG) Utils.log(TAG, "DBHelper: onUpgrade()...");

       // Logs that the database is being upgraded
       Utils.log(TAG, "w", "Upgrading database from version " + oldVersion + " to "
               + newVersion);

       /*
        * Every time you add new columns to the database in the phone, you will want
        * to increment the Database version above and then add a condition in here for
        * upgrading to it. Otherwise it will cause upgrading users to be nontrivial and
        * lead to unnecessary crashes or upgrade instructions.
        */
       if (newVersion > oldVersion && newVersion <= 4) {
           db.execSQL("ALTER TABLE " + TrackerDb.SomeTable.TABLE_NAME + " ADD COLUMN "
                   + TrackerDb.SomeTable.COLUMN_NAME_DATE_MODIFIED + " TEXT");
       }
   }

#3

OnUpgrade() is called when db versions "old and new" does not match; which means that the user wants to change Database Structure (Dropping a table, Adding new Table, Modifying Table definition).

当db版本“old and new”不匹配时调用OnUpgrade（）;这意味着用户想要更改数据库结构（删除表，添加新表，修改表定义）。

So the OnUpgrade() should contain the logic as:

所以OnUpgrade（）应该包含以下逻辑：

1. Take Backup of the existing data (e.g. Generate insert statements for existing data).
2. 备份现有数据（例如，为现有数据生成插入语句）。
3. Modify Database Structure accordingly.
4. 相应地修改数据库结构
5. Restore the data from the backup.
6. 从备份还原数据。