如何在SQLite android中手动执行检查点? [英] How to manually perform checkpoint in SQLite android?

问题描述

我正在尝试创建我的sqlite数据库的备份，我想先刷新db中WAL文件的内容.

I'm trying to create a backup of my sqlite database and I want to flush the content of the WAL file in the db first.

这是我的SQLiteOpenHelper:

Here is my SQLiteOpenHelper:

public class MyDBHelper extends SQLiteOpenHelper {

private Context mContext;
private static MyDBHelper mInstance = null;

private MyDBHelper(final Context context, String databaseName) {
    super(new MYDB(context), databaseName, null, DATABASE_VERSION);
    this.mContext = context;
}

@Override
public void onCreate(SQLiteDatabase db) {

}

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

}
   public static MyDBHelper getInstance(Context context) {

    if (mInstance == null) {
        mInstance = new MyDBHelper(context, DATABASE_NAME);
    }
    return mInstance;
}

  private void closeDataBase(Context context) {
    getInstance(context).close();
}

}

现在，我的理解是，在完成检查点之后，mydb.db-wal文件应该为空.正确吗?

Now, my understanding is that after a checkpoint is completed, the mydb.db-wal file should be empty. Is that correct?

这是到目前为止我尝试过的: 1.

Here is what I've tried so far: 1.

    public Completable flushWalInDB() {
    return Completable.fromAction(new Action() {
        @Override
        public void run() throws Exception {
            getInstance(mContext).getReadableDatabase().rawQuery("pragma wal_checkpoint;", null);
        }
    });
}

这不会引发错误，但是似乎什么也没做.运行此命令后，我物理检查了mydb.db-wal文件，并具有相同的大小.我还检查了设备上的数据库，但数据库中未添加任何内容

This doesn't throw an error but doesn't seem to do anything. After running this, I physically checked my mydb.db-wal file and had the same size. I also checked the db on the device and nothing was added in the database

经过一番挖掘，我发现了这个 [ https://stackoverflow.com/a/30278485/2610933][1] 并尝试了这个:

After some digging around I found this [https://stackoverflow.com/a/30278485/2610933][1] and tried this:

2.

    public Completable flushWalInDB() {
    return Completable.fromAction(new Action() {
        @Override
        public void run() throws Exception {
            getInstance(mContext).getReadableDatabase().execSQL("pragma wal_checkpoint;");
        }
    });
}

运行此命令会引发错误:

When running this it throws an error:

unknown error (code 0): Queries can be performed using SQLiteDatabase query or rawQuery methods only.

并基于此答案[ https://stackoverflow.com/a/19574341/2610933][1] ，我也尝试过抽空数据库，但似乎什么也没发生.

And based on this answer [https://stackoverflow.com/a/19574341/2610933][1] , I also tried to VACUUM the DB but nothing seems to happen.

 public Completable vacuumDb() {
    return Completable.fromAction(new Action() {
        @Override
        public void run() throws Exception {
            getInstance(mContext).getReadableDatabase().execSQL("VACUUM");
        }
    });
} 
}

在创建备份之前刷新数据库中WAL文件的正确方法是什么?

Whats is the correct way of flushing the WAL file in the DB before creating a backup?

谢谢.

推荐答案

PRAGMA wal_checkpoint(2)确实将WAL中的所有数据复制到实际的数据库文件中，但不会删除-wal文件，并且任何并发连接都可以使之后就进行了新的更改.

PRAGMA wal_checkpoint(2) does copy all data from the WAL into the actual database file, but it does not remove the -wal file, and any concurrent connections can make new changes right afterwards.

如果您要确定没有WAL干扰备份，请运行 PRAGMA journal_mode = DELETE . (您可以稍后将其切换回.)

If you want to be really sure that there is no WAL to interfere with your backup, run PRAGMA journal_mode = DELETE. (You can switch it back afterwards.)

这篇关于如何在SQLite android中手动执行检查点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！