I am trying to develop an App for Android which uses SQLite to store and manage data (which also includes User generated data). My Question would be how do I update the db file when I update the App? My Understanding is that everything that is in the installation Folder gets overwritten (which makes it impossible to preserve User generated data) and everything in the Private Storage is not updated. I am planning to add some rows to some Tables once in a while without overwrite the db file as I need to preserve User generated data. How do I do that? Is there some way to do something like this: if first_start_after_update do update_db_file(). Or is there any other practical Way? This is my first time developing an App and I would appreciate some Input. (Also I am using kivy /python)
You could mimic/replicate how it's done with Java/Kotlin via a class that extends SQLiteOpenHelper.
With SQLiteOpenHelper when the database is accessed (attempted to be opened/connected) then a check/test is undertaken to determine if the database exists or not.
If not then the database is created and the onCreate method (which must be overidden) is invoked. This is where the tables and other components (indexes, triggers, views etc) can be (should be) created.
If providing a pre-populated database as part of the package, then before obtaining an SQLiteDatabase object (via getWritableDatabase or getReadableDatabase) a check for the existence of the database file should be made and if the database file does not exist then the database should be copied from the asset to it's final location (typically in the location data/data/<the_package_name>/databases/). Noting that the databases folder may not exist, so may need to be created (using mkdir). Thus when it comes to opening/connecting the database then it will exist (if it has successfully been copied from the asset file).
Now to handle the scenario of an update, with the user_version is utilised. This is part of the header, is 4 bytes and is at offset 60 within the first 100 bytes (the header). The value can be manipulated utilising the user_version pragma.
So if you update the user_version of the updated database a relatively inexpensive check of the asset file's user_version can be compared against the existing database's user_version. If the asset file's user_version is greater than the existing database's user_version then an update is pending.
With regard to user v provided data, that needs to be distinguished (e.g. different table, column to flag user/provided, type of data in a column).
You would then copy the updated asset file as a database with a name different to the existing, and either
Although Java not Pyhton here's an answer that covers some of the above that you may find useful.