sqlite - Ship android app with pre populated database

Question

Here is my code,

public DBHelper(Context context) {
    super(context, DB_NAME, null, 2);
    this.context = context;
    DB_PATH = context.getDatabasePath(DB_NAME).getAbsolutePath();
}

@Override
public void onCreate(SQLiteDatabase db) {
    createDataBase();
}

private void createDataBase() {
    boolean dbExist = checkDataBase();
    if (!dbExist) {
        copyDataBase();
    }
}

private boolean checkDataBase() {
    System.out.println("DB_PATH : " + DB_PATH);
    File dbFile = new File(DB_PATH);
    return dbFile.exists();
}

private void copyDataBase() {
    Log.i("Database",
            "New database is being copied to device!");
    byte[] buffer = new byte[1024];
    OutputStream myOutput;
    int length;
    InputStream myInput;
    try {
        myInput = context.getAssets().open(DB_NAME);
        myOutput = new FileOutputStream(DB_PATH);
        while ((length = myInput.read(buffer)) > 0) {
            myOutput.write(buffer, 0, length);
        }
        myOutput.close();
        myOutput.flush();
        myInput.close();
        Log.i("Database",
                "New database has been copied to device!");
    } catch (IOException e) {
        e.printStackTrace();
    }
}

Everything works fine, and I even get the log New database has been copied to device!, but when I try to read the data from db, I am getting no such table exception.

Note: I'm trying to update one of my old app, this same code works in older device versions like 5.0 and below, but when I try to update the app using latest devices its not working.

Answer 1

Assuming that the database you have copied to the assets folder does contain the table(s) then I believe that your issue is that you are instantiating an instance of the DBHelper and then you have implicitly opened the database by either implicitly or explicitly calling getWritableDatabase or getReadableDatabase and are then using the onCreate method to initiate the copy.

If so then the get????ableDatabase will create an empty database, the copy overwrites this but on later versions, Android 9+, the -shm and -wal files are left as they were and when the database is then opened, then due to the -shm and -wal files not matching the original empty database a corruption is detected so a new database is created that is empty, as the SDK code tries to provide a usable database.

• From Android 9+ by default WAL (Write Ahead Logging) is used by default, this is what creates and uses the -shm and -wal files.

There are 3 fixes.

• use the disableWriteAheadLogging method by overriding the onConfigure method of the SQLiteOpenHelper class. This then uses the older journal mode.

• Ensure that getWritableDatabase/getReadableDatabase is not called. This could be done by ensuring the copy is done when instantiating the DBHelper instance.

• Ensuring that the -wal and -shm files, if they exist at the time of the copy, are deleted.

Using the first is perhaps only delaying the inevitable and is not really recommended as it does not take advantage of the benefits of WAL mode.

The following version of your DBHelper incorporates the second fixand also as a precaution the third fix :-

public class DBHelper extends SQLiteOpenHelper {

    public static final  String DB_NAME = "myDBName";
    public static String DB_PATH;

    Context context;

    public DBHelper(Context context) {
        super(context, DB_NAME, null, 2);
        this.context = context;
        //<<<<<<<<<< ADDED (moved from createDatabase) 1st Fix >>>>>>>>>>
        DB_PATH = context.getDatabasePath(DB_NAME).getAbsolutePath();
        if (!checkDataBase()) {
            copyDataBase();
        }
        //<<<<<<<<<< END OF ADDED CODE >>>>>>>>>>
        this.getWritableDatabase(); //<<<<<<<<<< Added to force an open after the copy - not essential
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //createDataBase(); <<<<<<<<<< relying on this was the cause of the issue >>>>>>>>>>
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    //<<<<<<<<<< NOT NEEDED AND SHOULD NOT BE CALLED >>>>>>>>>
    private void createDataBase() {
        boolean dbExist = checkDataBase();
        if (!dbExist) {
            copyDataBase();
        }
    }

    private boolean checkDataBase() {
        System.out.println("DB_PATH : " + DB_PATH);
        File dbFile = new File(DB_PATH);
        if (dbFile.exists()) return true;
        //<<<<<<<<<< ADDED to create the databases directory if it doesn't exist >>>>>>>>>>
        //it may be that getWritableDatabase was used to circumvent the issue that the copy would fail in the databases directory does not exist, hence this fix is included
        if (!new File(dbFile.getParent()).exists()) {
            new File(dbFile.getParent()).mkdirs();
        }
        return false;
    }

    private void copyDataBase() {
        Log.i("Database",
                "New database is being copied to device!");
        byte[] buffer = new byte[1024];
        //<<<<<<<<<< ADDED to delete wal and shm files if they exist (3rd fix) >>>>>>>>>>
        File dbDirectory = new File(new File(DB_PATH).getParent());
        File dbwal = new File(dbDirectory.getPath() + File.separator + DB_NAME + "-wal");
        if (dbwal.exists()) {
            dbwal.delete();
        }
        File dbshm = new File(dbDirectory.getPath() + File.separator + DB_NAME + "-shm");
        if (dbshm.exists()) {
            dbshm.delete();
        }
        //<<<<<<<<<< END OF ADDED CODE >>>>>>>>>>

        OutputStream myOutput;
        int length;
        InputStream myInput;
        try {
            myInput = context.getAssets().open(DB_NAME);
            myOutput = new FileOutputStream(DB_PATH);
            while ((length = myInput.read(buffer)) > 0) {
                myOutput.write(buffer, 0, length);
            }
            myOutput.close();
            myOutput.flush();
            myInput.close();
            Log.i("Database",
                    "New database has been copied to device!");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

This has been tested on both Android 5 and Android 10 using the following code from an activity (along with additional code to dump the schema (note obviously NOT your database rather one that was available )):-

DBHelper mDBHlpr;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
    mDBHlpr = new DBHelper(this);

The result as per the log :-

2019-05-07 06:20:53.148 I/System.out: DB_PATH : /data/user/0/soa.usingyourownsqlitedatabaseblog/databases/myDBName
2019-05-07 06:20:53.148 I/Database: New database is being copied to device!
2019-05-07 06:20:53.149 I/Database: New database has been copied to device!
2019-05-07 06:20:53.168 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@e3fe34f
2019-05-07 06:20:53.169 I/System.out: 0 {
2019-05-07 06:20:53.169 I/System.out:    type=table
2019-05-07 06:20:53.169 I/System.out:    name=Categories
2019-05-07 06:20:53.169 I/System.out:    tbl_name=Categories
2019-05-07 06:20:53.169 I/System.out:    rootpage=2
2019-05-07 06:20:53.169 I/System.out:    sql=CREATE TABLE "Categories" (
2019-05-07 06:20:53.169 I/System.out:   "not_id" integer NOT NULL,
2019-05-07 06:20:53.169 I/System.out:   "CategoryLabel" TEXT,
2019-05-07 06:20:53.169 I/System.out:   "Colour" integer,
2019-05-07 06:20:53.169 I/System.out:   PRIMARY KEY ("not_id")
2019-05-07 06:20:53.169 I/System.out: )
2019-05-07 06:20:53.170 I/System.out: }
2019-05-07 06:20:53.170 I/System.out: 1 {
2019-05-07 06:20:53.170 I/System.out:    type=table
2019-05-07 06:20:53.170 I/System.out:    name=Content
2019-05-07 06:20:53.170 I/System.out:    tbl_name=Content
2019-05-07 06:20:53.170 I/System.out:    rootpage=3
2019-05-07 06:20:53.170 I/System.out:    sql=CREATE TABLE "Content" (
2019-05-07 06:20:53.170 I/System.out:   "again_not_id" INTEGER NOT NULL,
2019-05-07 06:20:53.170 I/System.out:   "Text" TEXT,
2019-05-07 06:20:53.170 I/System.out:   "Source" VARCHAR,
2019-05-07 06:20:53.170 I/System.out:   "Category" integer,
2019-05-07 06:20:53.170 I/System.out:   "VerseOrder" integer,
2019-05-07 06:20:53.170 I/System.out:   PRIMARY KEY ("again_not_id")
2019-05-07 06:20:53.170 I/System.out: )
2019-05-07 06:20:53.170 I/System.out: }
2019-05-07 06:20:53.171 I/System.out: 2 {
2019-05-07 06:20:53.171 I/System.out:    type=table
2019-05-07 06:20:53.171 I/System.out:    name=android_metadata
2019-05-07 06:20:53.171 I/System.out:    tbl_name=android_metadata
2019-05-07 06:20:53.171 I/System.out:    rootpage=4
2019-05-07 06:20:53.171 I/System.out:    sql=CREATE TABLE android_metadata (locale TEXT)
2019-05-07 06:20:53.171 I/System.out: }
2019-05-07 06:20:53.171 I/System.out: <<<<<