Quarter Life Crisis

The world according to Sven-S. Porst

« Fresh and YoungMainJuly Films »

SQLite

694 words on

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.

Comments

Comment by Fred: User icon

You can edit the database at the command line without dumping if you’re handy with SELECT — but I prefer to do it in a graphical browser, and I’ve never seen an OS X one, though I’ve used a few X windows ones that weren’t bad.

There are a number of Apple apps that use SQLite, if not the Core Data bindings. iTunes is the biggest one (and can’t use the straight bindings because of Windows-portable carbonness). Mail.app uses it for the Envelope Index, it’s really all over the place.

July 31, 2007, 8:19

Comment by ssp: User icon

I’d still like to see some real-world non-trivial applications that use Core Data (and not just SQLite directly). And non-Apple ones at that.

I’m sure they do exist, but there aren’t loads of them.

July 31, 2007, 9:49

Comment by Fred: User icon

It depends on what definition of trivial you use — nearly every collection/library/organizer app written in the past year and a half is all Core Data. There are hundreds of them in pretty wide use.

August 1, 2007, 7:11

Comment by ssp: User icon

I have to admit that I haven’t used any of these applications (but I’ve heard about Core Data plans from tools like BibDesk or even big contenders like Delicious Library). But these are mere plans in both cases rather than software that exists in a released version.

Do you know other examples? In a way using Core Data for a ‘library’ type application which essentially is nothing but a pretty UI for a database seems to be the most obvious and least challenging use of it. Any other examples. Possibly even concrete ones? Perhaps I just haven’t run across such applications. I don’t exactly download each new bit of software that starts existing.

August 1, 2007, 9:26

Add your comment

« Fresh and YoungMainJuly Films »

Comments on

Photos

Categories

Me

This page

Out & About

pinboard Links

People

Ego-Linking