database asset for unitTest

Issue

For my unit tests I have collected real-world sensor data in a SQLite database. I need this data to create my tests. I tried the two methods below without success.

I am really stumped by this issue, because normally SQLiteOpenHelper loads any database in /data/data/packageName/databases/ flawlessly.

I tried android-sqlite-asset-helper:

val targetContext by lazy { InstrumentationRegistry.getTargetContext() }
val testContext by lazy { InstrumentationRegistry.getContext() }

const val TEST_DB_NAME = "testdb.sqlite"
const val VERSION = 1018

class TestDatabase(context: Context) : 
    SQLiteAssetHelper(context, TEST_DB_NAME, null, null, VERSION) {
    ...  
}

but none of the contexts work. Using testContext it can not write the database to the file system and with targetContext it can't access androidTest/assets/databases/testdb.sqlite.

When I try to copy it manually (as suggested by) with:

const val DB_PATH = "/data/data/de.leo.smartTrigger/databases/"

@Throws(IOException::class)
private fun copyDatabase(testContext: Context) {
    val inputStream = testContext.getAssets().open("databases/$TEST_DB_NAME")
    val outFileName = DB_PATH + TEST_DB_NAME
    val outputStream = FileOutputStream(outFileName)

    val buffer = ByteArray(1024)
    var length: Int = inputStream.read(buffer)
    while (length > 0) {
        outputStream.write(buffer, 0, length)
        length = inputStream.read(buffer)
    }

    outputStream.flush()
    outputStream.close()
    inputStream.close()
}

//after
copyDatabase(testContext)
val db = SQLiteDatabase.openDatabase(DB_PATH + TEST_DB_NAME, null, SQLiteDatabase.CREATE_IF_NECESSARY)
//yields an empty database although 
File(DB_PATH + TEST_DB_NAME).exists() == true // true

How can I load the database from androidTest/assets/databases/testdb.sqlite? Bonus points for in-memory, because I will be discarded after the test any way.

Solution

I found a solution:

Convert the database to input-statements

sqlite testdb.sqlite .dump > testdb.sql

put this file in androidTest/assets/databases/ then create the database:

    class MyDatabaseOpenHelper(val context: Context,
                           version: Int = 1) : SQLiteOpenHelper
                                                  (context,
                                                   null,
                                                   null,
                                                   version,
                                                   null) {
    override fun onCreate(db: SQLiteDatabase?) {
        BufferedReader(InputStreamReader(context.getAssets().open("databases/testdb.sql"))).apply {
            lines().forEach {
                try {
                    db!!.execSQL(it)
                } catch (e: SQLiteException) {
                    Log.w("read test db", e)
                }
            }
            close()
        }
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {
        //noop load from file
    }
}

By not supplying a name we create an in-memory database from the sql statements.

This solution is not perfect, because it requires a manual step when the test database is updated, but it works for me.

Answered By - leonardkraemer