Best way to bundles photos with app: files or in sqlite database?

Question

Lets say that I have an app that lets you browse through a listing of cars found in a Sqlite database. When you click on a car in the listing, it'll open up a view with the description of the car and a photo of the car.

My question is: should I keep the photo in the database as a binary data column in the row for this specific car, or should I have the photo somewhere in the resources directory? Which is better to do? Are there any limitations of Sqlite in terms of how big a binary data column can be?

The database will be pretty much be read only and bundled with the app (so the user wouldn't be inserting any cars and their photos).

Answer 1

The default MAX SQLite size for a BLOB or String is 2³¹-1 bytes. That value also applies to the maximum bytes to be stored in a ROW.

As to which method is better, I do not know. What I would do in your case is test both methods and monitor the memory usage and its effect on battery life. Maybe you'll find the filesystem method has a clear advantage over the other in that area and the ease of storing it in SQLite is not worth it for your use case, or maybe you'll find the opposite.

Answer 2

This is a decision which is discussed quite a lot. And in my opinion, it's a matter of personal taste. Pretty much like vim/emacs, windows/linux kind of debates. Not that heated though.

Both sides have their advantages and disadvantages. When you store them in a database, you don't need to worry much about filename and location. Management is also easier (you delete the row(s) containing the BLOBs, and that's it). But the files are also harder to access and you may need to write wrapper code in one way or the other (f.ex. some of those "download.php" links).

On the other hand, if the binary data is stored as a separate file, management is more complicated (You need to open the proper file from the disk by constructing the filename first). On large data sets you may run into filesystem bottlenecks when the number of files in one directory grows very large (but this can be prevented by creating subdirs easily). But then, if the data is stored as files, replacing them becomes so much easier. Other people can also access it without the need to know the internals (imagine for example a user who takes fun in customizing his/her UI).

I'm certain that there are other points to be raised, but I don't want to write too much now...

I would say: Think about what operations you would like to do with the photos (and the limitations of both storage methods), and from there take an informed decision. There's not much that can go wrong.

TX-Log and FS-Journal

On further investigation I found some more info:

• SQLite uses a Transaction Log
• Android uses YAFFS on the system mount points and VFAT on the SD-Card. Both are (to the best of my knowlege) unjournaled.

I don't know the exact implementation of SQLite's TX-Log, but it is to be expected that each INSERT/UPDATE operation will perform two writes on the disk. I may be mistaken (it largely depends on the implementation of transactions), but frankly, I couldn't be bothered to skim through the SQLite source code. It feels to me like we start splitting hairs here (premature optimization anyone?)...

As both file systems (YAFFS and VFAT) are not journalled, you have no additional "hidden" write operations.

These two points speak in favour of the file system.

Note that this info is to be taken with a grain of salt. I only skimmed over the Google results of YAFFS journaling and sqlite transaction log. I may have missed some details.