java 将 SQLite 数据库从一个版本升级到另一个版本？

Question

提问by Mohit Deshpande

I am getting an error from Logcatsaying that a certain column (in my SQLiteOpenHelpersubclass) does not exist. I thought I could upgrade the database by changing the DATABASE_CREATEstring. But apparently not, so how can I (step-by-step) upgrade my SQLite Database from version 1 to version 2?

我收到一个错误，Logcat说某个列（在我的SQLiteOpenHelper子类中）不存在。我以为我可以通过更改DATABASE_CREATE字符串来升级数据库。但显然不是，那么我如何（逐步）将我的 SQLite 数据库从版本 1 升级到版本 2？

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

如果问题看起来很“菜鸟”，我深表歉意，但我仍在学习 Android。

@Pentium10 This is what I do in onUpgrade:

@Pentium10 这就是我在 onUpgrade 中所做的：

private static final int DATABASE_VERSION = 1;

....

switch (upgradeVersion) {
case 1:
    db.execSQL("ALTER TABLE task ADD body TEXT");
    upgradeVersion = 2;
    break;
}

...

Answer 1

回答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)
设置交易成功

.

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

Answer 2

回答by BJ Peter DeLaCruz

Here is how I upgrade my database.

这是我升级数据库的方法。

In a previous version of my app, the gameTypecolumn doesn't exist. In the new version, it does.

在我的应用程序的先前版本中，该gameType列不存在。在新版本中，确实如此。

  void upgradeDatabase() throws IOException {
    try {
      String column = DatabaseConstants.GAME_TYPE_COLUMN_NAME; // gameType
      String table = DatabaseConstants.RECORDS_TABLE;
      String query = String.format("SELECT %s FROM %s LIMIT 1", column, table);
      database.rawQuery(query, null);
      return;
    }
    catch (Exception e) {
      // Column doesn't exist. User had old version of app installed, so upgrade database.
    }

    // Save all old data
    String query = "SELECT * FROM " + DatabaseConstants.USERS_TABLE;
    Cursor c = database.rawQuery(query, null);
    List<List<Object>> values1 = new ArrayList<List<Object>>();
    if (c.moveToFirst()) {
      while (!c.isAfterLast()) {
        List<Object> record = new ArrayList<Object>();
        record.add(c.getInt(0));
        record.add(c.getString(1));
        values1.add(record);
        c.moveToNext();
      }
    }
    c.close();

    query = "SELECT * FROM " + DatabaseConstants.RECORDS_TABLE;
    c = database.rawQuery(query, null);
    List<List<Object>> values2 = new ArrayList<List<Object>>();
    if (c.moveToFirst()) {
      while (!c.isAfterLast()) {
        List<Object> record = new ArrayList<Object>();
        record.add(c.getInt(0));
        record.add(c.getInt(1));
        record.add(c.getInt(2));
        record.add(c.getInt(3));
        values2.add(record);
        c.moveToNext();
      }
    }
    c.close();

    // Copy empty database with new schema
    copyDatabase();

    // Restore all old data
    for (List<Object> record : values1) {
      ContentValues cv = new ContentValues();
      cv.put(DatabaseConstants.ID_COLUMN_NAME, (Integer) record.get(0));
      cv.put(DatabaseConstants.USERNAME_COLUMN_NAME, record.get(1).toString());
      database.insert(DatabaseConstants.USERS_TABLE, null, cv);
    }
    for (List<Object> record : values2) {
      ContentValues cv = new ContentValues();
      cv.put(DatabaseConstants.USER_ID_COLUMN_NAME, (Integer) record.get(0));
      cv.put(DatabaseConstants.GAME_TYPE_COLUMN_NAME, GameType.CLASSIC.name());
      cv.put(DatabaseConstants.WINS_COLUMN_NAME, (Integer) record.get(1));
      cv.put(DatabaseConstants.LOSSES_COLUMN_NAME, (Integer) record.get(2));
      cv.put(DatabaseConstants.TIES_COLUMN_NAME, (Integer) record.get(3));
      database.insert(DatabaseConstants.RECORDS_TABLE, null, cv);
    }
  }

Here's the code to copy the database file. The database is initially empty, and I created it outside my app. (I used a program called Navicat for SQLite.)

这是复制数据库文件的代码。数据库最初是空的，我在我的应用程序之外创建了它。（我为 SQLite 使用了一个名为 Navicat 的程序。）

  public DatabaseHelper(Context context) {
    super(context, DatabaseConstants.DATABASE_NAME, null, 1);
    this.context = context;
    databasePath = context.getDatabasePath(DatabaseConstants.DATABASE_NAME).getPath();
  }

  void copyDatabase() throws IOException {
    InputStream is = context.getAssets().open(DatabaseConstants.DATABASE_NAME); // data.db
    OutputStream os = new FileOutputStream(databasePath);

    byte[] buffer = new byte[1024];
    int length;
    while ((length = is.read(buffer)) > 0) {
      os.write(buffer, 0, length);
    }

    // Close the streams.
    os.flush();
    os.close();
    is.close();
  }

Answer 3

回答by PFranchise

Wouldn't something like the following be easier for the vast majority of cases? Just add the new column for each version upgrade:

对于绝大多数情况，以下类似的事情不会更容易吗？只需为每个版本升级添加新列：

private static final String DATABASE_ALTER_TEAM_1 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_COACH + " string;";

private static final String DATABASE_ALTER_TEAM_2 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_STADIUM + " string;";

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2) {
         db.execSQL(DATABASE_ALTER_TEAM_1);
    }
    if (oldVersion < 3) {
         db.execSQL(DATABASE_ALTER_TEAM_2);
    }
}

For a bit more on this, check out this blog.

有关更多信息，请查看此博客。

java 将 SQLite 数据库从一个版本升级到另一个版本？

提问by Mohit Deshpande

回答by Pentium10

回答by BJ Peter DeLaCruz

回答by PFranchise

相关推荐

最近更新

标签

java 将 SQLite 数据库从一个版本升级到另一个版本？

提问by Mohit Deshpande

回答by Pentium10

回答by BJ Peter DeLaCruz

回答by PFranchise

相关推荐

java 我们必须将 .class 文件放在 Tomcat 目录中的位置

java 如何将参数化 JUnit 测试运行程序与使用 Spring 注入的字段一起使用？

java 使用 SAX 解析常见的 XML 元素

java 如何在 Swing 中逐渐旋转图像？

相关推荐

最近更新

标签