Copying SQLite Database on Oreo

Question

In one of my Android apps, I use a prefilled database which I copy at first use. This works well on all Android versions so far, but with API 28 it fails. It does copy the database and it can connect to the database afterward, but for some reason, none of the tables can be accessed. I get the error that the table doesn't exist.

I have checked the database by downloading it from the emulator and there is no problem with the database. Another app where I create the database in code works fine (database is created and tables can be found after).

The code I use to copy the database:

public void copyDatabase() throws IOException{
    InputStream myInput = mContext.getAssets().open(mDbSource);

    // Path to the just created empty db
    String outFileName = getDbPath() + mDbName;

    //Open the empty db as the output stream
    OutputStream myOutput = new FileOutputStream(outFileName);

    //transfer bytes from the inputfile to the outputfile
    byte[] buffer = new byte[1024];
    int length;
    while ((length = myInput.read(buffer))>0){
        myOutput.write(buffer, 0, length);
    }

    //Close the streams
    myOutput.flush();
    myOutput.close();
    myInput.close();
}

private String getDbPath(){
    return "/data/data/"+mContext.getResources().getString(R.string.package_name)+"/databases/";
}

Has anything changed in API 28 that makes this fail for some reason? What could be the cause of this?

Answer 1

The issue with API 28 on is that WAL (Write-Ahead Logging) is turned on by default. If, as is often the case, the database is opened, often done when checking to see if the database exists, then two files the -wal and -shm files are created.

e.g. in your code this appears to indicate that this is what you are doing :-

// Path to the just created empty db
String outFileName = getDbPath() + mDbName;

When the now existing database is overridden by the copy then the -wal and -shm files remain.

When the database is eventually attempted to be opened as an SQLiteDatabase then a mismatch between the database and the -wal and -shm files is raised. The underlying SQLiteDatabase open then decides that the database cannot be opened and so, as to provide a usable database, deletes and recreates the database effectively emptying the database and hence the situation arises.

There are three ways around this

1. Override the onConfigure method and force it to use the old journal mode ( using the [disableWriteAheadLogging](https://developer.android.com/reference/android/arch/persistence/db/SupportSQLiteDatabase#disablewriteaheadlogging method)).
2. Use an SQLiteDatabase open method to check to see if the database exists but to check to see if the File exists.
3. As part of the copy delete the -wal and -shm files.

I'd suggest that 2 or 3 are the better ways as :-

• There are no overheads of unnecessarily opening (and creating) the database, just to check for it's existence and to create the directories.
• The App will likely be improved due to the advantages of a WAL over Journal logging.

The above is all covered in Ship android app with pre populated database.

The solution code actually incorporates both 2 and 3 (although fix 3 should not be required (as fix 2 does not open the database as an SQLiteDatabase)).

Saying that I believe that the following code for checking if the database exists (fix 2) is better than it's equivalent in the answer above:-

/**
 * Check if the database already exists. NOTE will create the databases folder is it doesn't exist
 * @return true if it exists, false if it doesn't
 */
public static boolean checkDataBase(Context context, String dbname) {

    File db = new File(context.getDatabasePath(dbname).getPath()); //Get the file name of the database
    Log.d("DBPATH","DB Path is " + db.getPath()); //TODO remove if publish App
    if (db.exists()) return true; // If it exists then return doing nothing

    // Get the parent (directory in which the database file would be)
    File dbdir = db.getParentFile();
    // If the directory does not exits then make the directory (and higher level directories)
    if (!dbdir.exists()) {
        db.getParentFile().mkdirs();
        dbdir.mkdirs();
    }
    return false;
}

Answer 2

Yes, with android 9 there are two additional files that are part of the SQLite database, and if you copy a database overlaying an existing database, and don't delete those two other files first, then the database will consider itself corrupted. See this answer: Ship android app with pre populated database

But @MikeT should get the credit, as the linked answer was his to begin with!!

Answer 3

I cannot be sure until I see the logcat. However, I think you might have some permission missing while accessing your database.

I would like to ask checking if you have the following provider in your AndroidManifest.xml file for giving the authority to your application for accessing the database. Here's a sample manifest file having the provider.

<provider
    android:name=".DatabaseContentProvider"
    android:authorities="your.application.package.name"
    android:exported="false" />

And you need a DatabaseContentProvider class like the following.

import android.content.ContentProvider;
import android.content.ContentValues;
import android.database.Cursor;
import android.net.Uri;
import android.support.annotation.NonNull;
import android.support.annotation.Nullable;

public class DatabaseContentProvider extends ContentProvider {

    @Nullable
    @Override
    public Uri insert(@NonNull Uri uri, @Nullable ContentValues values) {
        return null;
    }

    @Nullable
    @Override
    public Cursor query(@NonNull Uri uri,
                        @Nullable String[] projection,
                        @Nullable String selection,
                        @Nullable String[] selectionArgs,
                        @Nullable String sortOrder) {
        return null;
    }

    @Override
    public boolean onCreate() {
        return true;
    }

    @Override
    public int update(@NonNull Uri uri, @Nullable ContentValues values,
                      @Nullable String selection, @Nullable String[] selectionArgs) {
        return 0;
    }

    @Override
    public int delete(@NonNull Uri uri, @Nullable String selection, @Nullable String[] selectionArgs) {
        return 0;
    }

    @Nullable
    @Override
    public String getType(@NonNull Uri uri) {
        return null;
    }
}

I have a sample code in Github for basic database operations in Android where you might also take a look.