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.

Answer 2

In your class for Db WorkHelper ovverride onOpen() method and set disableWriteAheadLogging then call onOpen() standard, if version of android sdk 28 , sure then old version remain old modality.

@Override
public void onOpen(SQLiteDatabase database) {
    super.onOpen(database);
    if(Build.VERSION.SDK_INT >= 28)
    {
        database.disableWriteAheadLogging();
    }
}

In my case WORK perfect.

Answer 3

Unlike what the other commenters have suggested, you can't rely on the database consisting of a single file after write-ahead logging is disabled, and you can't assume that the -shl and -wal filenames remain correct. This is all an implementation detail of sqlite3 / Android and therefore subject to change at any time (just like the old code broke).

One way of doing this that I expect to continue working is to use sqlite3's .dump command to convert the database into SQL that can later be executed in order to recreate the database.

I haven't tested the following, but expect that something similar to it should work:

// Copyright 2021 Google LLC.
// SPDX-License-Identifier: Apache-2.0

// Untested:
    private byte[] exportDatabase(SQLiteDatabase database) throws IOException {
        Process process = new ProcessBuilder()
                .command("/system/bin/sqlite3", database.getPath(), ".dump")
                .redirectOutput(ProcessBuilder.Redirect.PIPE)
                .start();
        try (InputStream inputStream = process.getInputStream()) {
            // [read the full contents of inputStream and save them somewhere]
            return ByteStreams.toByteArray(inputStream);
        } finally {
            waitForProcess(process);
        }
    }

    private void importDatabase(String databasePath, InputStream backedUpData) throws IOException {
        // restore the database:
        Process process = new ProcessBuilder()
                .command("/system/bin/sqlite3", databasePath)
                .redirectInput(ProcessBuilder.Redirect.PIPE)
                .start();
        try (OutputStream outputStream = process.getOutputStream()) {
            // now write the backed-up contents back to outputStream
            ByteStreams.copy(backedUpData, outputStream);
        } 
        waitForProcess(process);
    }

    private static void waitForProcess(Process process) {
        try {
            process.waitFor();
        } catch (InterruptedException e) {
            // ignore interruption, restore interrupt flag
            Thread.currentThread().interrupt();
        }
    }

Obviously, you'll have to ensure that:

• The database that you're backing up isn't currently open.
• The database that you're restoring doesn't already exist.