Android SQLite升级而不会丢失数据 [英] Android SQLite Upgrade without losing data
问题描述
我已经成功创建了SQLite数据库，并且工作正常。但是，当调用onUpgrade方法时，我希望这样做不会丢失数据。我正在开发的应用是测验应用。简而言之，当调用onCreate方法时，我将使用问题，答案等创建并预填充数据库。最后一列是他们是否已将问题设置为收藏夹。我想做的是，当调用onUpgrade方法时，我想暂时保存该列，删除整个数据库，使用我对旧问题所做的任何编辑重新创建它，并添加任何新问题，然后重新-添加回它们设置为收藏夹的问题。
I have created a SQLite database successfully and it works fine. However when the onUpgrade method is called, I'd like to do so without losing data. The app I'm developing is a quiz app. Simply, when the onCreate method is called I create and prepopulate a database with questions, answers etc. The last column is whether they have set the question as a favourite or not. What I would like to do is that when the onUpgrade method is called, I'd like to temporarily save that one column, drop the whole database, recreate it with any edits I've made to old questions and add any new questions then re-add back the questions that they set as favourites.
所以我尝试了以下一种选择：
So one option I tried was the following:
db.execSQL("ALTER TABLE quiz RENAME TO temp_quiz");
onCreate(db);
db.execSQL("INSERT INTO quiz (favouries) SELECT favourites FROM temp_quiz");
db.execSQL("DROP TABLE IF EXISTS temp_quiz");
但是，由于INSERT INTO只是添加新行而不是替换现有行，因此无法使用行。我还尝试了REPLACE INTO，INSERT或REPLACE INTO和
However this doesn't work owing to the fact INSERT INTO just adds new rows rather than replacing the existing rows. I have also tried REPLACE INTO, INSERT OR REPLACE INTO and
db.execSQL("INSERT INTO quiz (_id, favouries) SELECT _id, favourites FROM temp_quiz");
其中没有一个有效。
当前，我确实通过更改表名，调用onCreate（db）方法然后设置一个可读取每一行并使用db.update（）方法，如下所示：
Currently I do have it set up to work by altering the name of the table, calling the onCreate(db) method and then setting up a cursor which reads each row and uses the db.update() method as shown below:
int place = 1;
int TOTAL_NUMBER_OF_ROWS = 500;
while (place < TOTAL_NUMBER_OF_ROWS) {
String[] columns = new String[] { "_id", ..........., "FAVOURITES" };
// not included all the middle columns
Cursor c = db.query("temp_quiz", columns, "_id=" + place, null, null, null, null);
c.moveToFirst();
String s = c.getString(10);
// gets the value from the FAVOURITES column
ContentValues values = new ContentValues();
values.put(KEY_FLAG, s);
String where = KEY_ROWID + "=" + place;
db.update(DATABASE_TABLE, values, where, null);
place++;
c.close();
}
但是，尽管这样做很慢，但只会变得随着我的问题数量增加，情况变得更糟。有快速的方法来完成所有这些工作吗？
However whilst this works it is extremely slow and will only get worse as my number of questions increases. Is there a quick way to do all this?
谢谢！附言理想情况下，仅应在存在该行的情况下更新该行。因此，如果在升级中我决定删除一个问题，则应将此问题考虑在内，如果该行不包含任何其他数据，则不要添加新行。删除没有问题数据的行比阻止添加这些行可能更容易。
Thank you! P.S. Ideally it should only update the row if the row is present. So if in an upgrade I decide to remove a question, it should take this into account and not add a new row if the row doesn't contain any other data. It might be easier to get it to remove rows that don't have question data rather than prevent them being added.
推荐答案
将其更改为：
db.execSQL("UPDATE new_quiz SET favourites = ( SELECT old_quiz.favourites
FROM old_quiz WHERE new_quiz._id = old_quiz._id) WHERE EXISTS
( SELECT old_quiz.favourites FROM old_quiz WHERE new_quiz._id = old_quiz._id)");
哪个作品：D
这篇关于Android SQLite升级而不会丢失数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
