Android:升级DB版本，添加新表

I've already created sqlite tables for my app, but now I want to add a new table to the database.

我已经为我的应用程序创建了sqlite表，但是现在我想向数据库添加一个新表。

I changed the DB version as below

我更改了DB版本，如下所示。

private static final int DATABASE_VERSION = 2;

and Added string to create table

并添加字符串来创建表

private static final String DATABASE_CREATE_color = 
   "CREATE TABLE IF NOT EXISTS files(color text, incident_id text)";

onCreate and onUpgrade as below:

onCreate和onUpgrade as below:

@Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE_incident);
        database.execSQL(DATABASE_CREATE_audio);
        database.execSQL(DATABASE_CREATE_video);
        database.execSQL(DATABASE_CREATE_image);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //drop table and add new tables when version 2 released.
        db.execSQL(DATABASE_CREATE_color);

    }

But for some reason the new table is not being created. What am I doing wrong?

但是由于某种原因，新的表没有被创建。我做错了什么?

5 个解决方案

#1

250

1. About onCreate() and onUpgrade()

1。关于onCreate()和onUpgrade()

onCreate(..) is called whenever the app is freshly installed. onUpgrade is called whenever the app is upgraded and launched and the database version is not the same.

onCreate(..)在应用程序刚安装时就被调用。每当应用程序升级和启动时，都会调用onUpgrade，并且数据库版本不相同。

2. Incrementing the db version

2。增加数据库的版本

You need a constructor like:

你需要一个构造函数，比如:

MyOpenHelper(Context context) {
   super(context, "dbname", null, 2); // 2 is the database version
}

IMPORTANT: Incrementing the app version alone is not enough for onUpgrade to be called!

重要提示:仅增加应用程序版本是不足以调用onUpgrade的!

3. Don't forget your new users!

3所示。不要忘记你的新用户!

Don't forget to add

不要忘记添加

database.execSQL(DATABASE_CREATE_color);

to your onCreate() method as well or newly installed apps will lack the table.

对于onCreate()方法或新安装的应用程序，将缺少该表。

4. How to deal with multiple database changes over time

4所示。如何处理随时间变化的多个数据库更改

When you have successive app upgrades, several of which have database upgrades, you want to be sure to check oldVersion:

当你有连续的应用程序升级时，其中有几个已经进行了数据库升级，你要确保检查旧版本:

onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   switch(oldVersion) {
   case 1:
       db.execSQL(DATABASE_CREATE_color);
       // we want both updates, so no break statement here...
   case 2:
       db.execSQL(DATABASE_CREATE_someothertable); 
   }
}

This way when a user upgrades from version 1 to version 3, they get both updates. When a user upgrades from version 2 to 3, they just get the revision 3 update... After all, you can't count on 100% of your user base to upgrade each time you release an update. Sometimes they skip an update or 12 :)

这样，当用户从版本1升级到版本3时，他们会同时获得两个更新。当用户从版本2升级到版本3时，他们只会得到版本3的更新……毕竟，每次发布更新时，您不能指望100%的用户基础进行升级。有时他们会跳过更新或12:)

5. Keeping your revision numbers under control while developing

5。在开发过程中控制您的修订号。

And finally... calling

最后……调用

adb uninstall <yourpackagename>

totally uninstalls the app. When you install again, you are guaranteed to hit onCreate which keeps you from having to keep incrementing the database version into the stratosphere as you develop...

完全卸载该应用程序。当您再次安装时，您肯定会点击onCreate，这使您不必在开发过程中不断增加数据库版本到同温层中……

#2

Your code looks correct. My suggestion is that the database already thinks it's upgraded. If you executed the project after incrementing the version number, but before adding the execSQL call, the database on your test device/emulator may already believe it's at version 2.

你的代码是正确的。我的建议是数据库已经认为它已经升级了。如果在增加版本号之后执行该项目，但是在添加execSQL调用之前，您的测试设备/模拟器上的数据库可能已经相信它在版本2中。

A quick way to verify this would be to change the version number to 3 -- if it upgrades after that, you know it was just because your device believed it was already upgraded.

验证这一点的一个快速方法是将版本号更改为3——如果在此之后升级，您知道这只是因为您的设备认为它已经升级了。

#3

You can use SQLiteOpenHelper's onUpgrade method. In the onUpgrade method, you get the oldVersion as one of the parameters.

您可以使用SQLiteOpenHelper的onUpgrade方法。在onUpgrade方法中，您将获得旧版本作为参数之一。

In the onUpgrade use a switch and in each of the cases use the version number to keep track of the current version of database.

在onUpgrade中使用一个交换机，并且在每种情况下都使用版本号来跟踪数据库的当前版本。

It's best that you loop over from oldVersion to newVersion, incrementing version by 1 at a time and then upgrade the database step by step. This is very helpful when someone with database version 1 upgrades the app after a long time, to a version using database version 7 and the app starts crashing because of certain incompatible changes.

最好从旧版本循环到新版本，每次递增1个版本，然后逐步升级数据库。这对于使用数据库版本1的人在很长一段时间后升级应用程序非常有用，对于使用数据库版本7的版本来说，由于某些不兼容的更改，应用程序开始崩溃。

Then the updates in the database will be done step-wise, covering all possible cases, i.e. incorporating the changes in the database done for each new version and thereby preventing your application from crashing.

然后，数据库中的更新将按步骤进行，包括所有可能的情况，例如将每个新版本在数据库中所做的更改合并在一起，从而防止应用程序崩溃。

For example:

例如:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch (oldVersion) {
    case 1:
        String sql = "ALTER TABLE " + TABLE_SECRET + " ADD COLUMN " + "name_of_column_to_be_added" + " INTEGER";
        db.execSQL(sql);
        break;

    case 2:
        String sql = "SOME_QUERY";
        db.execSQL(sql);
        break;
    }

}

#4

@jkschneider's answer is right. However there is a better approach.

@jkschneider的回答是正确的。然而，还有更好的方法。

Write the needed changes in an sql file for each update as described in the link https://riggaroo.co.za/android-sqlite-database-use-onupgrade-correctly/

按照链接https://riggaroo.co.za/android-sqlite-database- database-use- onupgradecorrectly/中的描述，为每次更新在sql文件中编写所需的更改

from_1_to_2.sql

ALTER TABLE books ADD COLUMN book_rating INTEGER;

from_2_to_3.sql

ALTER TABLE books RENAME TO book_information;

from_3_to_4.sql

ALTER TABLE book_information ADD COLUMN calculated_pages_times_rating INTEGER;
UPDATE book_information SET calculated_pages_times_rating = (book_pages * book_rating) ;

These .sql files will be executed in onUpgrade() method according to the version of the database.

这些.sql文件将根据数据库的版本在onUpgrade()方法中执行。

DatabaseHelper.java

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 4;

    private static final String DATABASE_NAME = "database.db";
    private static final String TAG = DatabaseHelper.class.getName();

    private static DatabaseHelper mInstance = null;
    private final Context context;

    private DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

    public static synchronized DatabaseHelper getInstance(Context ctx) {
        if (mInstance == null) {
            mInstance = new DatabaseHelper(ctx.getApplicationContext());
        }
        return mInstance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
        // The rest of your create scripts go here.

    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.e(TAG, "Updating table from " + oldVersion + " to " + newVersion);
        // You will not need to modify this unless you need to do some android specific things.
        // When upgrading the database, all you need to do is add a file to the assets folder and name it:
        // from_1_to_2.sql with the version that you are upgrading to as the last version.
        try {
            for (int i = oldVersion; i < newVersion; ++i) {
                String migrationName = String.format("from_%d_to_%d.sql", i, (i + 1));
                Log.d(TAG, "Looking for migration file: " + migrationName);
                readAndExecuteSQLScript(db, context, migrationName);
            }
        } catch (Exception exception) {
            Log.e(TAG, "Exception running upgrade script:", exception);
        }

    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    private void readAndExecuteSQLScript(SQLiteDatabase db, Context ctx, String fileName) {
        if (TextUtils.isEmpty(fileName)) {
            Log.d(TAG, "SQL script file name is empty");
            return;
        }

        Log.d(TAG, "Script found. Executing...");
        AssetManager assetManager = ctx.getAssets();
        BufferedReader reader = null;

        try {
            InputStream is = assetManager.open(fileName);
            InputStreamReader isr = new InputStreamReader(is);
            reader = new BufferedReader(isr);
            executeSQLScript(db, reader);
        } catch (IOException e) {
            Log.e(TAG, "IOException:", e);
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e) {
                    Log.e(TAG, "IOException:", e);
                }
            }
        }

    }

    private void executeSQLScript(SQLiteDatabase db, BufferedReader reader) throws IOException {
        String line;
        StringBuilder statement = new StringBuilder();
        while ((line = reader.readLine()) != null) {
            statement.append(line);
            statement.append("\n");
            if (line.endsWith(";")) {
                db.execSQL(statement.toString());
                statement = new StringBuilder();
            }
        }
    }
}

An example project is provided in the same link also : https://github.com/riggaroo/AndroidDatabaseUpgrades

在同一链接中还提供了一个示例项目:https://github.com/riggaroo/AndroidDatabaseUpgrades

#5

Handling database versions is very important part of application development. I assume that you already have class AppDbHelper extending SQLiteOpenHelper. When you extend it you will need to implement onCreate and onUpgrade method.

处理数据库版本是应用程序开发的重要部分。我假设您已经有了扩展SQLiteOpenHelper的类AppDbHelper。扩展时，需要实现onCreate和onUpgrade方法。

When onCreate and onUpgrade methods called

当onCreate和onUpgrade方法调用时。
- onCreate called when app newly installed.
- onCreate在app新安装时调用。
- onUpgrade called when app updated.
- onUpgrade在应用程序更新时调用。
Organizing Database versions I manage versions in a class methods. Create implementation of interface Migration. E.g. For first version create MigrationV1 class, second version create MigrationV1ToV2 (these are my naming convention)

组织数据库版本，我在类方法中管理版本。创建接口迁移的实现。例如，对于第一个版本创建MigrationV1类，第二个版本创建MigrationV1ToV2(这是我的命名约定)


    public interface Migration {
        void run(SQLiteDatabase db);//create tables, alter tables
    }

Example migration:

示例迁移:

public class MigrationV1ToV2 implements Migration{
      public void run(SQLiteDatabase db){
        //create new tables
        //alter existing tables(add column, add/remove constraint)
        //etc.
     }
   }

Using Migration classes
使用迁移类

onCreate: Since onCreate will be called when application freshly installed, we also need to execute all migrations(database version updates). So onCreate will looks like this:

onCreate:由于onCreate将在新安装应用程序时调用，所以我们还需要执行所有迁移(数据库版本更新)。onCreate是这样的

public void onCreate(SQLiteDatabase db){
        Migration mV1=new MigrationV1();
       //put your first database schema in this class
        mV1.run(db);
        Migration mV1ToV2=new MigrationV1ToV2();
        mV1ToV2.run(db);
        //other migration if any
  }

onUpgrade: This method will be called when application is already installed and it is updated to new application version. If application contains any database changes then put all database changes in new Migration class and increment database version.

onUpgrade:当应用程序已经安装并更新为新的应用程序版本时，将调用此方法。如果应用程序包含任何数据库更改，则将所有数据库更改放入新的迁移类和增量数据库版本中。

For example, lets say user has installed application which has database version 1, and now database version is updated to 2(all schema updates kept in MigrationV1ToV2). Now when application upgraded, we need to upgrade database by applying database schema changes in MigrationV1ToV2 like this:

例如，假设用户已经安装了具有数据库版本1的应用程序，现在数据库版本更新为2(所有模式更新都保存在MigrationV1ToV2中)。现在，当应用程序升级时，我们需要在MigrationV1ToV2中应用数据库模式更改来升级数据库:

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2) {
        //means old version is 1
        Migration migration = new MigrationV1ToV2();
        migration.run(db);
    }
    if (oldVersion < 3) {
        //means old version is 2
    }
}

Note: All upgrades (mentioned in onUpgrade) in to database schema should be executed in onCreate

注意:数据库模式的所有升级(在onUpgrade中提到)都应该在onCreate中执行。

#1

250