Is it better to create the db/tables programmatically or through a .db file in assets

Question

Hi,
does Android have a "best practices" guideline on creating & populating the db/tables programmatically vs. deploying a .db file in assets?

What are the pros/cons of both approaches?

I have a db with big long strings in several columns, and about 50 rows, so writing the insert statements alone would take quite some space. It seems a waste.

Thoughts?

Thanks!
llappall

Answer 1

I've just started using SQLite on Android.

The same kind of factors apply in your decisions on this platform as any other.

Scripting and programmatically populating:

• Allows over the wire updates, makes auto updaters easier.
• Scripting your changes makes them easily reproducable
• Takes more initial effort (if you can't generate the creation script)
• Could lead to versioning issues (not knowing which scripted changes have been applied) and failed scripted updates.

Prepopulating the database and deploying

• Similar level of time required to create and populate but easier through a GUI interface.
• Fixed database versions (though the oness is on you to keep snapshot versions).
• Larger db file sizes to be pulled down over the wire for updates, rather than small scripts.
• Deployment is copy / paste vs script execution.

It's all preference really, after all, there's nothing to say you can't start with a prepopulated database and have it update over the wire via scripts.

Answer 2

The differences between the two methods you mentioned are minimal with a database size of 50 rows. I just finished an app that had roughly 8K rows. In this case, programatically loading the data when the app is first started took over 5 minutes on the emulator alone. Changing the app to load the database from assets or raw directories loaded the database almost immediately.

Programatically loading the database:

• Easily modifiable
• Becomes slow as size increases
• Duplicates data in the app

Copying from assets:

• Difficult to modify (Requires modification of a database instead of a text file)
• Quickly loads large databases on initialization
• Duplicates data in the app

In both of these cases the data is duplicated. The text file or database in the assets directory cannot be cleaned up after installation. So not only does that copy exist but the data now also gets populated in the database. To avoid duplicating the data a third solution exists. When the app first starts and is being initialized it can fetch the database from an internet address. It can download the database and copy the contents to the database (similar to copying from assets).