java - Database Import and Export not working in Android Pie

Question

Below is the working method to Import and Export SQLite database. Its Working just fine in all android versions excluding Android Pie. When I am trying to import in Android pie, it shows successful toast but database is not being restored. Can anybody help me workaround in Android Pie(API 28).

private void importDB() {

    try {
        File sd = Environment.getExternalStorageDirectory();
        File cur_db_pat = new File(this.getDatabasePath(DATABASE_NAME).getAbsolutePath());

        if (sd.canWrite()) {
            String backupDBPath = bac_dir_nam +"/" + DATABASE_NAME;
            File currentDB = new File(sd, backupDBPath);

            FileChannel src = new FileInputStream(currentDB).getChannel();
            FileChannel dst = new FileOutputStream(cur_db_pat).getChannel();
            dst.transferFrom(src, 0, src.size());
            src.close();
            dst.close();
            Toast.makeText(getBaseContext(), cur_db_pat.toString(),
                    Toast.LENGTH_LONG).show();
        }
    } catch (Exception e) {

        Toast.makeText(getBaseContext(), e.toString(), Toast.LENGTH_LONG)
                .show();

    }
}

private void exportDB() {

    try {
        File sd = Environment.getExternalStorageDirectory();
        File cur_db_pat = new File(this.getDatabasePath(DATABASE_NAME).getAbsolutePath());

        if (sd.canWrite()) {
            String backupDBPath = bac_dir_nam+"/" + DATABASE_NAME;
            File backupDB = new File(sd, backupDBPath);

            FileChannel src = new FileInputStream(cur_db_pat).getChannel();
            FileChannel dst = new FileOutputStream(backupDB).getChannel();
            dst.transferFrom(src, 0, src.size());
            src.close();
            dst.close();
            Toast.makeText(getBaseContext(), backupDB.toString(),
                    Toast.LENGTH_LONG).show();

        }
    } catch (Exception e) {

        Toast.makeText(getBaseContext(), e.toString(), Toast.LENGTH_LONG)
                .show();

    }
}

I don't have much experience with file system. So an example would help a lot.

Answer 1

In Android Pie+ SQLite has changed to default to using the generally more efficient Write Ahead Logging (WAL) instead of Journal mode.

As such there will be two files with the same name as the database but suffixed with -shm (shared memory file) and -wal (write ahead log) and their presence is what I believe causes the issue(s). Temporary Files Used By SQLite (see 2.2 and 2.3)

One fix would be to disable Write Ahead Logging using use the SQliteDatabase disableWriteAheadLogging method and the previous method would work as before but with the less efficient journal mode.

• (if using a subclass of SQliteOpenHelper then override the onConfigure method to invoke this method. ) disableWriteAheadLogging.

Another fix is to delete these two files when restoring. To avoid the potential for corruption you have to ensure that the database was adequately checkpointed before making the backup. see PRAGMA checkpoint;

The following is a snippet that deletes these two files when restoring (noting that the backup is assumed to have been taken with adequate checkpointing):-

                    // Added for Android 9+ to delete shm and wal file if they exist
                    File dbshm = new File(dbfile.getPath() + "-shm");
                    File dbwal = new File(dbfile.getPath()+ "-wal");
                    if (dbshm.exists()) {
                        dbshm.delete();
                    }
                    if (dbwal.exists()) {
                        dbwal.delete();
                    }

Another fix would be to additionally backup and subsequently restore the -shm and -wal files.

You may also wish considering the potential benefits of renaming the original files when importing/restoring, checking the new files after they have been copied (e.g. using PRAGMA integrity_check;) if the results indicat no issues then delete the renamed original files, otherwise delete the imported files and rename the original files to their original name, indicating that the import failed.