java - SQLite onUpgrade() 挫败感

Question

我正在使用预填充的数据库。在 Mozilla 中使用 SQLite Manager，现在我有一个虚拟数据库，只是想测试 onUpgrade 方法，因此我更改了其中一列中的字符串，将其导出为新数据库，正如您将在我的代码中看到的那样，尝试打开新更新的版本。另请注意，我在代码中手动将版本号更新为 1

private static final int DB_VERSION = 3;

和 onUpgrade 中

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

所以下次我会将这两个数字更新为 4。不确定我这样做是否正确或出了什么问题，但我收到了日志消息，只是没有看到应用程序中更新的数据。

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.");
        }
    }
}
}

Answer 1

升级数据库意味着对其进行适当更改，同时尽可能保持旧数据完整。因此，如果您想要添加或重命名列，则必须在 onUpgrade 回调中执行正确的 SQL 命令来执行此操作。

(注意:SQLiteAssetHelper 使使用预填充数据库变得更容易，您应该使用它，但升级仍然需要单独的 SQL 脚本。)

如果您不关心旧数据库的内容，那么您不应该升级它。只需为您的新数据库版本指定一个新文件名，以便简单地复制它，然后删除旧文件。