Download external .db file for read-only use in Kotlin Android application?

I already have a SQLite database (.db) file available for download at a specific URL (let's say https://mywebsite.com/mydb.db). I want to download this file and use it inside my Kotlin Android app. I can download the file to the "Downloads" folder in my device emulator, but I have several questions about where to go from here.

1. Do I need to copy this file to internal storage, or can I create a database directly from the file in the Downloads folder? I want the database data to persist locally, so I don't want to leave the database file in a place where it may get deleted (the Downloads folder).

   • Basically, I'd like the app to be able to read from a persistent, internal .db file

2. Sometimes, I'll need to add more data to the .db file. In the future, when I re-download the .db file and create a database from it, will this overwrite the existing database in the app's internal storage? Do I need to manually delete old iterations of the database?

3. I'd like to download the .db file automatically the first time the app runs, and every time I upload a new version of the .db file to https://mywebsite.com/mydb.db.

In general, I'm very new to Android development, so I'm not sure what the best practices are. The code I've provided successfully downloads my .db file to the "Downloads" folder on a button click. I'm not sure where to go from here in terms of creating a database from the .db file, and automatically replacing the database when I upload a new .db file to my host URL.

private const val PERMISSION_CODE = 1000

override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        downloadButton.setOnClickListener {
            // Checks if version of Android is >= Marshmallow
            if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.M) {
                if (checkSelfPermission(Manifest.permission.WRITE_EXTERNAL_STORAGE) == PackageManager.PERMISSION_DENIED){
                    // request permission if denied
                    requestPermissions(arrayOf(Manifest.permission.WRITE_EXTERNAL_STORAGE), PERMISSION_CODE)
                }
                else {
                    startDownload()
                }
            }
            else {
                // OS is out of date, no permissions are needed
                startDownload()
            }
        }

private fun startDownload() {
        val request = DownloadManager.Request(Uri.parse(getString(R.string.db_URL)))
        request.setAllowedNetworkTypes(DownloadManager.Request.NETWORK_WIFI or DownloadManager.Request.NETWORK_MOBILE)
        request.setTitle("my_database.db")
        request.setDescription("My database is downloading.")
        request.allowScanningByMediaScanner()
        request.setNotificationVisibility(DownloadManager.Request.VISIBILITY_VISIBLE_NOTIFY_COMPLETED)
        request.setDestinationInExternalPublicDir(Environment.DIRECTORY_DOWNLOADS, "mydb_" + "${System.currentTimeMillis()}")

        val manager =  getSystemService(Context.DOWNLOAD_SERVICE) as DownloadManager
        manager.enqueue(request)
}

Do I need to copy this file to internal storage, or can I create a database directly from the file in the Downloads folder?

The file is the database and can therefore be opened from the downloads folder.

I want the database data to persist locally, so I don't want to leave the database file in a place where it may get deleted (the Downloads folder).

You could copy the file from the downloads folder and place it in the default location for the App based upon the Context's getDatabasePath(the_database_name) method to obtain the location that the file will be copied to. You would (at least for the first run) check to see if the database file exists in it's final location and then initiate the copy after which the database could be opened and used.

Alternately you could download it directly to the final destination rather than have an intermediate copy.

Sometimes, I'll need to add more data to the .db file. In the future, when I re-download the .db file and create a database from it, will this overwrite the existing database in the app's internal storage? Do I need to manually delete old iterations of the database?

There are various ways but basically if introducing a new file then you would likely overwrite the existing database file (stored within the App's data) at it's old location (although this isn't the only option). Although, if introducing new data rather than a new schema (the latter would likely need new code so you could utilise the database version to force a recopy).

The issue is how to know if a new file has been introduced as the database file name would, at least at the final destination, likely be the same.

1. Checking the size of the file may not indicate a change when there has been a change and is therefore not a failsafe option.

   • That is the database file is saved as chucks of pages, some of those pages will likely include free space that may be utilised when updating the database and thus the database file size remains unchanged.

2. You could check the last modified date of the file.

3. You could utilise a different file name e.g. mydbv1.db and then mydbv2.db etc.
4. You could utilise the user_version, care would be needed if using a subclass of SQLiteOpenHelper (as is most often the case) as this is utilised by SQLiteOpenHelper (the database version passed as the 4th paramter).
5. You could utilise the application_id (this is similar to user_version but is not utilised by SQLiteOpenHelper).

1-3 would require a means of persistently recording the currently used value, perhaps in a table in the database itself, perhaps an empty file in the same directory.

4 & 5 are actually stored in the database header you access the values via pragma's or programatically extract them (the latter doesn't need the file to be opened as database and would typically be less resource intensive as you only need to read the first 100 bytes to get the complete header).

Once you have detected a new version it's simply a matter of re-doing the download and copy.

The above assumes that the App has multiple users and therefore that a download file always has to exist on the host server (your website) and that you have discounted shipping the App with the database (which could simplify matters as when a new version is available you introduce a new version of the App).

The above also assumes that the database is only read by the end user. If not and the user can update data then a strategy would be needed to cater for preserving the specific user's data.