在Android上备份SQLITE数据库的最佳方法是什么，以使数据停留在用户的Google驱动器中？我希望理想地进行增量备份，而不是一直备份整个数据。
当前我使用的策略是备份所有三个数据库文件{db_name}.db，{db_name}-wal.db和{db_name}-shm.db每天一次备份到用户的Google驱动器中，然后在用户登录新设备时为用户还原所有这些文件。这是我正在用于备份的工人代码的一部分： -
File databaseFile = new File(context.getDatabasePath("db1").getAbsolutePath());
File databaseWalFile = new File(context.getDatabasePath("db1-wal").getAbsolutePath());
File databaseShmFile = new File(context.getDatabasePath("db1-shm").getAbsolutePath());
if (!databaseFile.exists() || !databaseWalFile.exists() || !databaseShmFile.exists()) {
return Result.SUCCESS;
}
long totalBackupSize = databaseFile.length() + databaseWalFile.length() + databaseShmFile.length();
GoogleSignInAccount account = GoogleSignIn.getLastSignedInAccount(getApplicationContext());
DriveResourceClient driveResourceClient = Drive.getDriveResourceClient(getApplicationContext(), account);
final Task<DriveFolder> appFolderTask = driveResourceClient.getAppFolder();
final Task<DriveContents> createContentsDbTask = driveResourceClient.createContents();
final Task<DriveContents> createContentsDbWalTask = driveResourceClient.createContents();
final Task<DriveContents> createContentsDbShmTask = driveResourceClient.createContents();
Tasks.whenAll(appFolderTask, createContentsDbTask, createContentsDbWalTask, createContentsDbShmTask)
.continueWithTask(task -> {
DriveFolder parent = appFolderTask.getResult();
DriveContents dbContents = createContentsDbTask.getResult();
DriveContents dbWalContents = createContentsDbWalTask.getResult();
DriveContents dbShmContents = createContentsDbShmTask.getResult();
if (dbContents != null && dbWalContents != null && dbShmContents != null) {
Utility.copy(new FileInputStream(databaseFile), dbContents.getOutputStream());
Utility.copy(new FileInputStream(databaseWalFile), dbWalContents.getOutputStream());
Utility.copy(new FileInputStream(databaseShmFile), dbShmContents.getOutputStream());
MetadataChangeSet changeSet = new MetadataChangeSet.Builder()
.setTitle(AppConstants.GoogleDriveBackupDbFileName)
.setMimeType(AppConstants.DatabaseMimeType)
.build();
MetadataChangeSet changeWalSet = new MetadataChangeSet.Builder()
.setTitle(AppConstants.GoogleDriveBackupDbWalFileName)
.setMimeType(AppConstants.DatabaseMimeType)
.build();
MetadataChangeSet changeShmSet = new MetadataChangeSet.Builder()
.setTitle(AppConstants.GoogleDriveBackupDbShmFileName)
.setMimeType(AppConstants.DatabaseMimeType)
.build();
return Tasks.whenAll(driveResourceClient.createFile(parent, changeSet, dbContents),
driveResourceClient.createFile(parent, changeWalSet, dbWalContents),
driveResourceClient.createFile(parent, changeShmSet, dbShmContents));
}
TaskCompletionSource<DriveFile> tcs = new TaskCompletionSource<>();
tcs.setException(new Exception("Failed to copy database file"));
return Tasks.whenAll(tcs.getTask());
}).addOnSuccessListener(driveFile -> {
SharedPreferences.Editor editor = context.getSharedPreferences(PreferenceConstants.INSTANCE.getPreferenceName(), Context.MODE_PRIVATE).edit();
editor.putLong(PreferenceConstants.INSTANCE.getLastGoogleDriveBackupTime(), new Date().getTime());
editor.putLong(PreferenceConstants.INSTANCE.getFileSize(), totalBackupSize);
editor.apply();
Log.v("BackupWorker", "Backup successful");
result[0] = Result.SUCCESS;
countDownLatch.countDown();
}).addOnFailureListener(e -> {
Log.e("BackupWorker", "Unable to create file", e);
result[0] = Result.FAILURE;
countDownLatch.countDown();
});
使用上述策略备份和还原，但我的担心是： -
- 如果将来内部实施发生变化，我们需要在WAL和SHM之外备份某些文件，该怎么办？简单地备份主数据库文件对我不起作用。
- 此策略效率低下，因为它要求我一直备份整个数据库。是否可以在这里进行增量备份？
我考虑将备份存储在CSV文件中，然后将其存储在Google Drive上。然后，我可以为每个后续备份写入CSV文件的更改。但是，我觉得这会增加开发人员的努力。有更好的解决方案吗？
简单地备份主数据库文件不适用于 我。
如果在执行备份之前，请使用截短选项正确检查数据库，则不需要备份-wal和-shm文件(因为它们为空(。
同样，如果您关闭所有连接，则还应完成检查点。例如，请考虑以下内容(logDBSizeInfo输出文件大小，addsomeData和deleteSomeData如他们说的那样(
mDBHlpr = new DBHelper(this);
logDBSizeInfo(this,DBHelper.DBNAME);
if (DatabaseUtils.queryNumEntries(mDBHlpr.getWritableDatabase(),"table1") < 1) {
addSomeData(100000);
deleteSomeData(1000);
} else {
addSomeData(1000);
deleteSomeData(100);
}
logDBSizeInfo(this,DBHelper.DBNAME);
mDBHlpr.close();
//checkpointIfWALEnabled(this,DBHelper.DBNAME);
logDBSizeInfo(this,DBHelper.DBNAME);
导致： -
05-24 20:25:47.195 D/DBSIZEINFO: Database Size is 34312192 05-24 20:25:47.195 D/DBSIZEINFO: Database -wal file Size is 0 path is /data/user/0/aso.so56286308backupwal/databases/mydb-wal 05-24 20:25:47.196 D/DBSIZEINFO: Database -shm file Size is 32768 path is /data/user/0/aso.so56286308backupwal/databases/mydb-shm 05-24 20:25:47.199 D/ADDSOMEDATA: Adding about 1000 rows 05-24 20:25:47.365 D/ADDSOMEDATA: Deleting about 100 rows 05-24 20:25:47.397 D/DBSIZEINFO: Database Size is 34656256 05-24 20:25:47.397 D/DBSIZEINFO: Database -wal file Size is 420272 path is /data/user/0/aso.so56286308backupwal/databases/mydb-wal 05-24 20:25:47.397 D/DBSIZEINFO: Database -shm file Size is 32768 path is /data/user/0/aso.so56286308backupwal/databases/mydb-shm <<<<<<<<<<After Close>>>>>>>>>> 05-24 20:25:47.400 D/DBSIZEINFO: Database Size is 34656256 05-24 20:25:47.400 D/DBSIZEINFO: Database -wal file does not exist 05-24 20:25:47.400 D/DBSIZEINFO: Database file -shm does not exist
我使用以下内容来确保在备份之前检查文件(仅数据库文件(： -
private void checkpointIfWALEnabled(Context context, String databaseName) {
Cursor csr;
int wal_busy = -99, wal_log = -99, wal_checkpointed = -99;
SQLiteDatabase db = SQLiteDatabase.openDatabase(context.getDatabasePath(databaseName).getPath(),null,SQLiteDatabase.OPEN_READWRITE);
csr = db.rawQuery("PRAGMA journal_mode",null);
if (csr.moveToFirst()) {
String mode = csr.getString(0);
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);
}
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);
}
}
}
csr.close();
db.close();
}
可以减小备份大小的另一个选项是将真空吸入到中。我没有尝试过这种方法。但是，使用日志记录，因此之后也可能是明智的检查(然后，您不仅可以使用该文件作为备份，还可以替换原始文件(。
数据库的增量备份可能被证明是不切实际的，而且更耗时，除非您基本上已经建立了交易，否则可能不会问这个问题。通常，增量备份系统由相对容易检测到的更改驱动。