In one application I use sqlite to store JPEG thumbnails. That's it; there's nothing else in there. It's super handy; I specifically needed to reduce the number of files that I open and close because that's slow on Windows/NTFS. SQLite made this trivial. I could have managed a binary pack format on my own but I didn't have to.
Another alternative that I've seen used is a zip or tar with no compression if you are just appending files and reading but only rarely updating or deleting.
But sqlite is still better, it is more reliable, a bad write on that end of zip index destroys the whole zip archive and sqlite also gives you a lot more potential flexibility later on if you need to add metadata or something else. It is better in terms of inserts and deletes, although you will still need to vacuum.
A bad write on a zip file destroys the O(1) seek time, but it doesn't destroy the zip. That goes back to PKZip trying to work on floppies and over modems. You can still do an O(n) seek on a particular file, or expand and recompress the file to recover whatever isn't truncated.
For this situation it does matter, but it is recoverable.
That property came back into use when we crossed the 2GB and then 4GB threshold for archives, because the TOC uses a 32 bit integer to reference backward into the file. A lot of implementations used a signed int, and even when they fixed it, organic growth in bandwidth and thus content size ran you into the wall in another couple of years. We had collectively mostly supported UTF-8 filenames around that same time period, so it was going from one problem to the next (or choosing between implementations that had fixed one but not the other).
For the 32 bit address problem, you could read forward from the front, and as long as no entry was over 4GB, you could still read the file. If the file count was low enough you could cache all of the entry objects, and if reads dominated opens, then you were functionally back to O(1) access (but O(n) startup).
There was a 64 bit extension going around, but when I stopped working with zip files every day I stopped tracking the progress.
I'm aware; I mentioned this at the end of my post. This is still easier. I want to clearly express how absolutely trivial it was to use SQLite for this. I didn't really have to think about anything.
Depends on the indexing needs, right? Something like IFF should be fine if you need a sequence of images to store that you don’t need to actually query for individually.
Always, but I find it really hard to see how not using SQLite in this use case is the better option. Maybe if you need every single percentile of performance? In 99% of other cases just go with SQLite.
Yes easier, as in time to integrate and get working correctly. I’ve used SQLite extensively over the last 10 years and yes it’s a good solution, but not a replacement for fopen. A flat file would be easier to integrate, test, and harden over SQLite. Would take about the same time as integrating SQLite into a system for the same purpose and would be easily extendable to support features as the system grows.
> Would take about the same time as integrating SQLite into a system for the same purpose and would be easily extendable to support features as the system grows.
I find it much easier to add features to my post-2007 projects (when I started using SQLite) for the specific reason that I can open the SQLite file in a GUI and pretty quickly see what’s going on with data organization (schema) and how the customer uses the software I wrote (ie by what columns they actually use/misuse).
Prior to that, there’s various versions of my b-tree library and lots of zips, or linear text indexes, or any combination of whatever fit the need. Data storage implementation needs to be reasoned about in detail for each pre-2007 revisit in ways that don’t happen with SQLite projects.
At client site to debug issues and deal with things like bulk product name changes https://sqlitebrowser.org/
For more involved work that I do at my shop or on my laptop, DataGrip by Jet Brains is great. Before I got fed up with Apple, I used https://menial.co.uk/base/
DataGrip’s benefit to me is mainly reworking a customer DB at the ER diagram level, then I manually update my code to match.
By the way, SQLite started out as a TCL extension. It works really well in TCL and good old TCL/TK/SQLite gets CRUD done fast. I almost feel bad looking so heroic.
