Android database approach for future updates

Question

What is the best way to work with the sqlite database in android?

The sqlite database file (copy it for the first time into the application environment)

OR

Creating the tables in code (in database helper's onCreate())

My database has 6 tables and it is empty for the first time. I ask this because I want to update my database in the future and would want to know the best approach for this.

Thank you!

Answer 1

If you don't expect to have your db updated by user interaction, the file might be the best option, especially in the case that you have a lot of data to insert (file copying vs a bunch of inserts).

On the other hand, if you expect to have some data altered or added by the user, the file approach will work only in the first release.

Any time you will need to update your schema or add new data (releasing an upgrade), you will need to consider that the existing data might be changed or enriched by some stuff that the users will expect to find AFTER the upgrade.

So replacing the file is not an option anymore.

In case you need to use the sqllite helper approach, I'd love to hear some feedbacks on my sqllite helper code generator that you can find here: github

Answer 2

You should create (in code) it the first time it is used. Android offers the SQLiteOpenHelper class that should be used for it. SQLiteOpenHelper defines the following methods:

onCreate(SQLiteDatabase db): invoked when the database is created, this is where you can create tables and columns to them, create views or triggers.

onUpgrade(SQLiteDatabse db, int oldVersion, int newVersion): Invoked if the used database is older than the current version. Handle here the upgrade stuff (data migration, table creation/deletion)

See here for a good tutorial: http://www.codeproject.com/KB/android/AndroidSQLite.aspx

Answer 3

For large amounts of data you might want to consider this kind of solution: Create an empty database in code and provide an activity which responds to an intent with this action: android.intent.action.SEND. Respond by parsing the sent file and populate the database with the contents. Design a format which can be easily parsed (XML is not needed for everything ;-) so the code to parse the file and fill the database is small (my binary for this including an UI to show progress (which is the larger part of the activity) is less than 12 kB in size).

The file may be distributed separately (extra apk, download, whatever). The benefit of this approach is that you do not need to store your initial database content within the apk and thus the data is only stored once on the device (after the file has been deleted). Otherwise you have the data in the database plus the source code or asset in the apk.

Answer 4

Not specific to SQLLite or android, however I have worked on a Windows trading application where users could save down Xml 'documents' - ie: a custom view saving their reporting preferences and various other flags which could then be shared around the team. On startup a user's profile was loaded and their documents parsed to customize the UI.

The application was to have a release every 3 weeks and existing documents needed to work with the new application. This was a problem as occasionally the XML schema changed resulting in new or deleted fields.

The solution we came up with was to create an abstract type called Patcher. Each release could have one or more DerivedPatcher types with it which were run on the first load after an update. The Patcher would have an abstract method to patch the XMl documents. Meaning an XML document would be loaded in with the old schema and upgraded, saved back in-place using the new schema. The Patcher would also have a rollback method to allow unrolling if an error occurred.

The same approach could be applied to tables in a database. Basically if you can create a patcher or PatchManager to serialize key tables to XML in memory, then apply the DB changes and write the data back, you can achieve database migration in a generic, re-usable way.

A key benefit of this method is it can be tested on developer PCs before deployment, so long as you have access to some sample SQLLite data. Knowing how your users use your application is key here.