Quarter Life Crisis

SQLite¶

694 words on Software

Mac OS X.4 brought us Core Data. Which in my very limited experience is both amazingly cool and a source of endless frustration. On the up-side, it has never been easier to tell the computer the data model you have in mind, to implant that into an application and even get an graphical interface for the data for free. On the down-side, actually wanting to do stuff with your data – which in my case invariably meant wanting to do something outside Apple’s nice and simple examples – brings you to a point where you hit the limits of that very simple approach.

You’ll find yourself cursing Core Data and Bindings because they don’t provide the exact functionality you want and because the documentation isn’t particularly helpful for anything outside the simple stuff. My impression, however, ended up being that for a small project where your data model isn’t complex and you don’t need the benefit of a database in the background, it may just be simpler and less work to chuck in your own code for reading and writing your data as a property list and live happily ever after.

But the power of this database driven bit of magic is in the OS. And I wonder to which extent it is being used! Off the top of my head I don’t know many applications which actually use Core Data. And I know even fewer non-trivial applications using it. One big Core Data application that I had plenty of opportunity to use is Sandvox. It uses Core Data for all its storage needs and as far as I can tell this wasn’t an entirely unproblematic decision. At least I sent plenty of crash logs deep inside what looked like Core Data SQLite code their way. And I imagine that such problems, in such a vital part of an application are a royal pain to debug. Particularly if most of the magic comes from deep within an opaque third party framework. (In addition, storing everything – potentially even media – in a single large database file, won’t make you many friends when Time Machine comes along and slaps 50MB onto your backup drive even when you only changed a single character.)

However, there’s a fun advantage to the Core Data storage. With the database being a standard SQLite database, you can actually edit it outside the application. At least for text that’s quite feasible, for other forms of data it may be more tricky. So if some aspect of the file is broken and needs to be fixed or if you want to do editing that the application doesn’t let you do natively, this is a great workaround. And it’s even feasible for people without a lot of database knowledge.

All it takes is a Terminal window which should be set to have an infinite line buffer and use UTF-8 encoding to avoid breaking non-ASCII characters (at least when I used it, I have no idea whether the encoding can be set on a per-file basis). Then you open the file you want to change with SQLite, using the sqlite3 myfile. Inside the sqlite3 tool you need to issue the .dump command which gives you all the file’s data as text.

My distinctly unsophisticated next step was to simply select all text and copy it, to then paste it into a text editor where the first few and last lines are removed, so the raw SQL remains. Inside that file you can then edit to your heart’s content, save it – taking care of encodings once more – and finally create a new database with the edited data using a command like sqlite3 newfile < editedfile. And that’s already it.

Obviously I would prefer a nice graphical editor for SQLite files here, but luckily this workaround wasn’t too bad. Feel free to add all the needed disclaimers here: yes absolutely, you can break your files like this. Particularly if you have no idea about the database expectations of Core Data. And yes, you are an idiot if you considered it a good idea to do any destructive editing on your original files.

July 31, 2007, 1:12

Tagged as software.