在WAL模式下以File/Streams备份sqlite db//通过export/backup sql命令 [英] Backup sqlite db in WAL mode without FileStreams // by export/backup sql commands

问题描述

我在这里搜索了很多答案，但是所有人都使用 FileInputStream 和 FileOutputStream 来备份数据库.

I have searched lots of answers here, but all use FileInputStream and FileOutputStream to backup a database.

当您将 PRAGMA 语句与 journal_mode = WAL (预写日志)，异步模式等一起使用时，文件复制将失败.

A file copy fails in the moment where you use PRAGMA statements with journal_mode=WAL (write-ahead-logging), asynchronous mode, and so on.

如何备份/导出/快照在Android上以WAL模式运行的sqlite数据库?

How can I backup/export/snapshot a sqlite database that runs in WAL mode on android?

文件复制不是正确的方法.在sqlite中必须有一些备份/导出命令(嗯，通过sqlite命令外壳可以使用 ).

A file copy can't be the right way. There must be some backup/export command available in sqlite (well, it is available through the sqlite command shell).

到目前为止，我还没有找到解决方案.任何帮助都将不胜感激.

So far, I didn't find a solution. Any help greatly appreciated.

推荐答案

当您将PRAGMA语句与journal_mode = WAL(预写日志)，异步模式，等等.

A file copy fails in the moment where you use PRAGMA statements with journal_mode=WAL (write-ahead-logging), asynchronous mode, and so on.

简而言之，您需要备份并还原所有三个文件，或者需要确保数据库已完全检查点，备份数据库文件并删除 -wal 和-shm 文件进行还原.

In short, you need to either back and restore all three 3 files or you need to ensure that the database has been fully checkpointed, backup the database file and delete the -wal and -shm files when restoring.

这是更全面的答案.

这是在WAL模式下检查数据库的方法的示例(用于日记模式可以是Android默认值的情况):-

This is an example of a method that checkpoints the database if in WAL mode (this is used where the journal mode could be either as per the Android default) :-

private void checkpointIfWALEnabled(Context context) {
    final String TAG = "WALCHKPNT";
    Cursor csr;
    int wal_busy = -99, wal_log = -99, wal_checkpointed = -99;
    SQLiteDatabase db = SQLiteDatabase.openDatabase(context.getDatabasePath(DBConstants.DATABASE_NAME).getPath(),null,SQLiteDatabase.OPEN_READWRITE);
    csr = db.rawQuery("PRAGMA journal_mode",null);
    if (csr.moveToFirst()) {
        String mode = csr.getString(0);
        //Log.d(TAG, "Mode is " + mode);
        if (mode.toLowerCase().equals("wal")) {
            csr = db.rawQuery("PRAGMA wal_checkpoint",null);
            if (csr.moveToFirst()) {
                wal_busy = csr.getInt(0);
                wal_log = csr.getInt(1);
                wal_checkpointed = csr.getInt(2);
            }
            //Log.d(TAG,"Checkpoint pre checkpointing Busy = " + String.valueOf(wal_busy) + " LOG = " + String.valueOf(wal_log) + " CHECKPOINTED = " + String.valueOf(wal_checkpointed) );
            csr = db.rawQuery("PRAGMA wal_checkpoint(TRUNCATE)",null);
            csr.getCount();
            csr = db.rawQuery("PRAGMA wal_checkpoint",null);
            if (csr.moveToFirst()) {
                wal_busy = csr.getInt(0);
                wal_log = csr.getInt(1);
                wal_checkpointed = csr.getInt(2);
            }
            //Log.d(TAG,"Checkpoint post checkpointing Busy = " + String.valueOf(wal_busy) + " LOG = " + String.valueOf(wal_log) + " CHECKPOINTED = " + String.valueOf(wal_checkpointed) );
        }
    }
    csr.close();
    db.close();
}

• 请注意，上面的数据库名称是通过常量DBConstants.DATABSENAME获取(解析的)，这是一个简单的签名更改，允许传递数据库名称.

这篇关于在WAL模式下以File/Streams备份sqlite db//通过export/backup sql命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！