Copying an existing database to device memory from the assets folder

Question

I am trying to copy a database file from the assets folder to the device /data/data folder for reference by the app. This copy process keeps failing.

I have used a copyDatabase() method found in the answers to similar problems on stackoverflow. These methods use an InputStream variable set by context().getAssets().open(databaseName); however, in my case, this assignment fails to populate a database. A subsequent method that queries the db throws an exception, since the database has no table 'main' as expected.

This is my copyDatabase() method:

 String DATABASE_NAME = "birdsDBase3.db"

       private void copyDatabase(String DATABASE_PATH){
            Log.i(TAG, "now in copyDatabase()");
            try {
                Log.i(TAG, "trying to copy database.");
                InputStream assetDB = context.getAssets().open(DATABASE_NAME);
                Log.i(TAG, assetDB.toString());
                OutputStream appDataBase = new FileOutputStream(DATABASE_PATH, false);

                byte[] buffer = new byte[1024];
                int length;
                while ((length = assetDB.read(buffer)) > 0){
                    appDataBase.write(buffer, 0, length);
                }
                appDataBase.flush();
                appDataBase.close();
                assetDB.close();
            } catch(IOException e){
                e.printStackTrace();
            }
        }
    }

Error from log: "SQLiteLog: (1) no such table: category"

I want the database to copy over to the device so that another method can send a query for distinct tuples in one column. Instead, this method fails due to the absence of a 'main' table in the database. I know that the query is well formed because I have run it within the SQLite Browser and got the expected result, and the method has been checked in a different thread on stackoverflow.

Answer 1

Assuming that the issue is simple that the database hasn't been correctly saved (not a rare occurrence so check and double check that the file copied into the assets folder is populated accordingly) then the likely issue is as per :-

Since the advent of Android 9 many of the older methods of copying the database from the assets folder now fail. This due to using the getWritableDatabase method to create the databases folder into which the database is copied.

That is when an App is installed the folders data/data/package are created for the App's data. SQlite databases are be default stored in /data/data/package/databases, the copy will fail with a NOENT if the databases folder doesn't exist.

Using the SQLiteOpenHelper's getWritableDatabase will create the databases folder when it creates the database, whihc is typically the same name/file as the database to be copied from the assets folder and the final database name.

Before Android 9 the default logging used was journal mode. This mode uses a file named the same as the database name but suffixed with -journal. The file contains transactions and can be used to roll back the transaction from the actual database.

With Android 9 Write-Ahead Logging (WAL) is used be default. This writes the actual transaction to a file -wal, (which is effectively part of the database and looked at first) and when a checkpoint occurs the database file is updated and the -wal file cleared.

The -wal file is therefore instrinsic, if it exists it MUST belong to the database for which it was created.

So what happens is that getWritableDatabase creates a database, and some transactions, such as creating the android_metadata table are written to the -wal file. The database is copied from the assets file overwriting the newly created database but the -wal and the -shm files (a shared memory file used by WAL) remain. When the database is opened the error results in an unusable database, due to the -wal and -shm files not belonging to the copied database, and the SDK/API creates a usable new database and hence no tables that existed in the asset file that was copied.

The solutions can be :-

1. Disable WAL by calling the SQLiteDatabase's disableWriteAheadLogging method before the database has been used.

   • If using a subclass of SQLiteOpenHelper, generally by overriding the onConfigure method of the SQLiteOpenHelper.
   • Not recommended as the benefits of WAL are lost.

2. Using a different database name to create the databases folder (never known that to be done).

   • Awkward, never tried and thus not considered.

3. Deleting or renaming the -wal and -shm files before the copied database is opened.

   • The best option so far but...

4. Create the databases folder using standard/basic File handling i.e. by using the_databases_parent_file.mkdirs();

   • The best and recommended option as
   • it does not have the overheads of opening the database file
   • doing the checks
   • creating the android_metadata table
   • creating and writing the -wal and -shm files
   • overwriting the intermediate database file
   • deleting the -wal and -shm files
   • affords the advantages of WAL

An example that uses this technique when checking for the existence of the database file is :-

private boolean checkDataBase(Context context, String databaseName) {
    /**
     * Does not open the database instead checks to see if the file exists
     * also creates the databases directory if it does not exists
     * (the real reason why the database is opened, which appears to result in issues)
     */

    File db = new File(context.getDatabasePath(databaseName).getPath()); //Get the file name of the database
    Log.d("DBPATH","DB Path is " + db.getPath()); //TODO remove for Live 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;
}

To implemnt the above your would use the above to check the database and do away with the getWritableDatabase that is called between checking the database and calling the copyDatbase method.

Answer 2

You need to implement

SqliteAssetHelper

Search over the web and try it.