SQLite onUpgrade()令人沮丧 [英] SQLite onUpgrade() frustration

问题描述

我正在使用预先填充的数据库.现在，在Mozilla中使用SQLite Manager，我有一个虚拟数据库，只想测试onUpgrade方法，因此我更改了其中一列中的字符串，将其导出为新数据库，如您在代码中所见，尝试打开新更新的版本.同样重要的一点是，我正在代码

I'm working with a prepopulated database. Using SQLite Manager in Mozilla, right now I have a dummy database and just wanted to test out the onUpgrade method, so I altered a string in one of the columns, exported it as a new database and as you'll see in my code, tried to open the newly updated version. Also important note, I'm updating the version number by one manually in the code

private static final int DB_VERSION = 3;

和onUpgrade

and in the onUpgrade

if (newVersion == 3) {
        Log.e("WORKED!!", "onUpgrade executed");

因此，下一次我将把这两个数字更新为4.不知道我是在做对还是做错了，但是我收到了日志消息，只是看不到应用程序中已更新的数据.

so next time I would update these two numbers to 4. Not sure if I'm doing this right or what's wrong but I'm getting the log message, just not seeing the data updated in the app.

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.util.Log;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class DataBaseHelper extends SQLiteOpenHelper {
    private static String DB_PATH;
    private static final String DB_NAME = "DummyTestOne.sqlite";
    private static final int DB_VERSION = 3;
    private static final String DB_NAME2 = "DummyTestFive.sqlite";
    private SQLiteDatabase mDataBase;
    private final Context mContext;

public static final String DBTABLENAME = "questiontable";
public static final String DBATABLENAME = "answertable";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_NAME = "question";
public static final String COLUMN_CATEGORY = "category";
public static final String COLUMN_FID = "fid";

public static final String COLUMN_ANSWER = "answer";

public DataBaseHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
    this.mContext = context;

    DB_PATH = context.getDatabasePath(DB_NAME).getPath();


}

public void createDataBase() {
    boolean dbExist = checkDataBase();

    if (dbExist) {

    } else {
        this.getReadableDatabase();
        try {
            copyDataBase();
        } catch (IOException e) {
            throw new Error("Error copying database");
        }
    }
}

private boolean checkDataBase() {
    SQLiteDatabase checkDB = null;

    try {
        checkDB = SQLiteDatabase.openDatabase(DB_PATH, null, SQLiteDatabase.OPEN_READWRITE);
    } catch (SQLiteException e) {
        Log.e(this.getClass().toString(), "Error while checking db");
    }

    if (checkDB != null) {
        checkDB.close();
    }

    return checkDB != null ? true : false;
}

private void copyDataBase() throws IOException {
    InputStream externalDbStream = mContext.getAssets().open(DB_NAME);
    OutputStream localDbStream = new FileOutputStream(DB_PATH);

    byte[] buffer = new byte[1024];
    int bytesRead;
    while ((bytesRead = externalDbStream.read(buffer)) > 0) {
        localDbStream.write(buffer, 0, bytesRead);
    }


    localDbStream.flush();
    localDbStream.close();
    externalDbStream.close();
}

public void openDataBase() throws SQLException {


    mDataBase = this.getWritableDatabase();

}

@Override
public synchronized void close() {
    if (mDataBase != null) {
        mDataBase.close();
    }
    super.close();
}

public Cursor getCursorForAllQs() {
    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
    queryBuilder.setTables(DBTABLENAME);

    String[] asColumnsToReturn = new String[]{COLUMN_ID, COLUMN_NAME, COLUMN_CATEGORY, COLUMN_FID};

    Cursor mCursor = queryBuilder.query(mDataBase, asColumnsToReturn, null,
            null, null, null, "_id");

    return mCursor;
}

public List<String> getAnswersForQ(int questionFid) {
    List<String> answers = new ArrayList<>();
    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
    queryBuilder.setTables(DBATABLENAME);
    String[] tableColumns = new String[]{DataBaseHelper.COLUMN_ANSWER};
    String where = "fid = ?";
    String[] selectionArgs = new String[]{String.valueOf(questionFid)};
    String orderBy = DataBaseHelper.COLUMN_ID;


    Cursor c = queryBuilder.query(mDataBase, tableColumns, where, selectionArgs, null, null, orderBy);
    if (c.moveToFirst()) {
        do {
            try{
                answers.add(c.getString(c.getColumnIndex(DataBaseHelper.COLUMN_ANSWER)));
            } catch (Exception e) {
                Log.e("FAILED", c.getString((c.getColumnIndex(DataBaseHelper.COLUMN_ANSWER))));
            }

        } while (c.moveToNext());
    }
    Log.d("getAnswersForQ", answers.toString());
    return answers;

}

public String getName(Cursor c) {
    return (c.getString(1));
}



@Override
public void onCreate(SQLiteDatabase db) {

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (newVersion == 3) {
        Log.e("WORKED!!", "onUpgrade executed");
    }
    if (newVersion > oldVersion) {
        InputStream inputStream = null;
        OutputStream outputStream = null;
        String dbFilePath = DB_PATH + DB_NAME;


        try {
            inputStream = mContext.getAssets().open(DB_NAME2);

            outputStream = new FileOutputStream(dbFilePath);

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

            outputStream.flush();
            outputStream.close();
            inputStream.close();
        } catch (IOException e) {
            throw new Error("Problem copying database from resource file.");
        }
    }
}
}

推荐答案

升级意味着在适当的位置更改数据库，同时尽可能保留完整的旧数据. 因此，如果要添加或重命名列，则必须执行正确的SQL命令以在onUpgrade回调中执行此操作.

Upgrading a database means changing it in place while keeping the old data intact as much as possible. So if you want to add or rename a column, you have to execute the proper SQL command to do this in the onUpgrade callback.

(注意: SQLiteAssetHelper 使使用预先填充的数据库更加容易，并且您应该使用它，但升级仍需要单独的SQL脚本.)

(Note: SQLiteAssetHelper makes using a prepopulated database easier, and you should use it, but upgrading still needs a separate SQL script.)

如果您不关心旧数据库的内容，则不应升级它.只需为新的数据库版本提供一个新的文件名，以便将其简单地复制过来，然后删除旧文件.

If you do not care about the contents of the old database, then you should not upgrade it. Just give your new database version a new file name, so that it is simply copied over, and delete the old file.

这篇关于SQLite onUpgrade()令人沮丧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！