Thursday, May 1, 2008

SQLite Adventures

With the release of OS X 10.4 (Tiger), Apple gave us developers something pretty cool called "Core Data". Core Data is an ORM tool based (at least in concept, if not in actual code) on Enterprise Objects Framework (or EOF) which was NeXT's revolutionary tool for persisting object-oriented data to a database: revolutionary, that is, back in 1993.

Now, I don't mean to slight EOF or Core Data in that statement. Both are cool tools, and are vastly superior to embedding SQL calls to a procedural API in your object-oriented code. In 1993, strongly-typed languages ruled the desktop development roost and "web development" was yet to even really exist as a defined form of programming since, for all practical purposes, 1993 was the year the web was born1.

 C and C++ were the indisputed champions of desktop application development and the arrival of Java two years later would cement the dominance of strongly-typed languages for quite some time. In 1993, weakly-typed languages had been around for a while but, for a number of reasons, were largely considered ill-suited to writing "serious" applications. NeXTStep's use of Objective-C was definitely an odd-man out in terms of desktop application development.

Things have changed quite a lot since 1993, though. Computers have gotten faster, dynamic languages like PERL, Ruby, and Python have become increasingly popular, and even most of the statically typed languages — including C++ — have gained some level of runtime introspection capability.

Probably one of the coolest approaches to object-relational mapping is the ActiveRecord design pattern. A good ActiveRecord implementation requires a dynamic language with robust type introspection which allows the object to dynamically add variables and methods to itself based on the contents of a database table.

But ActiveRecord, as cool as it is, is not ideally suited for desktop development with an embedded database because it is database table-driven. What would be ideal for embedded work is a sort-of "reverse ActiveRecord" where tables are created based on the existing attributes of objects. So, if you added a string variable to your object, it would know to add a varchar column to the table and would also know to save the contents of the variable in that column.  If there is a name for this approach, please let me know it; I've never heard one, but I suspect one exists.

Anyway, I do have a point here, and it has to do with the iPhone and SQLite. I realized yesterday while writing an article proposal on SQLite that Objective-C's dynamic nature would allow this theoretical "reverse ActiveRecord" idea to be implemented and, in fact, that it could probably be encapsulated almost entirely in a single abstract super class. Well, not technically abstract, because Objective-C doesn't support abstract classes, but that's neither here nor there.

Yesterday, I wrote a proof-of-concept version of just such a class. It has methods to create tables, insert, update, and delete data from the database as well as rollback to the last values stored in the database. It's capable of using foreign key relationships for one-to-one associations between objects and cross-reference tables for one-to-many associations represented in Objective-C by the use of arrays and dictionaries.

My code is still very rough around the edges and is definitely not production ready, but I think the concept is sound and with Core Data not being available on the iPhone, I think it's a great approach to avoid having to write tons of custom embedded SQL for each object. In fact, all you do is create a subclass, add instance variables and property declarations and you're done. You don't actually have to write any SQL to handle the persistence and there's even a mechanism to specify indices for your object's table.

I will clean up my code a little bit and put it out there for comments and feedback in the next day or so.

1 - Yes, I know that, in theory the web dates back to 1989 and in implementation dates back to 1990, but CERN announced on April 30, 1993 that the World Wide Web would be free for everyone to use, and that date (in my mind) marks the real beginning of the web as we know it.




5 comments:

stevebclues said...

That sounds really awesome. I will gladly look over it and give you some input when you post it. -jonc

Adrian Kosmaczewski said...

I arrived to your blog thanks to the message you sent to cocoadev last friday about the WWDC schedules :) Thanks for the link!

Then I got stuck at this post, because it's funny, I'm working on my MSc thesis and one of the things I've created for it is... an ActiveRecord implementation in C++ (you can browse the code at http://code.google.com/p/remproject/source/browse/trunk/src/storage/ActiveRecord.h ) which uses template metaprogramming, multiple inheritance and the Poco::Any type (based on the boost::any class).

You might find it interesting for your own project (of course is MUCH more rigid than Objective-C) There are a couple of tests in http://code.google.com/p/remproject/source/browse/trunk/src/tests/ActiveRecordTest.cpp that show how to use the class (for the moment it only supports one-to-many relationships, and it uses SQLite as you might have guessed). It is GPL'd so feel free to use and enjoy! :)

BTW, see you at WWDC!

thaoula said...

Hi Jeff,

Just wondering if you got around to finishing your implementation.

Are you still looking to post your code as it sounds really useful.

Kind Regards,
Tarek

JeansPilot said...

JeansPilot offers the chance to buy a large variety of men’s and women’s jeans clothing from the world famous Italian Brands.
Online jeans clothing store looks for original fashion clothing sales and clearances of worldwide known designers. We participate in fashion auctions to get the lowest possible price for Top quality Clothes, Shoes and Accessories.
Buy Jeans

h4ns said...

What youre saying is completely true. I know that everybody must say the same thing, but I just think that you put it in a way that everyone can understand. I also love the images you put in here. They fit so well with what youre trying to say. Im sure youll reach so many people with what youve got to say.

Arsenal vs Huddersfield Town live streaming
Arsenal vs Huddersfield Town live streaming
Wolverhampton Wanderers vs Stoke City Live Streaming
Wolverhampton Wanderers vs Stoke City Live Streaming
Notts County vs Manchester City Live Streaming
Notts County vs Manchester City Live Streaming
Bologna vs AS Roma Live Streaming
Bologna vs AS Roma Live Streaming
Juventus vs Udinese Live Streaming
Juventus vs Udinese Live Streaming
Napoli vs Sampdoria Live Streaming
Napoli vs Sampdoria Live Streaming
Fulham vs Tottenham Hotspur Live Streaming
Fulham vs Tottenham Hotspur Live Streaming
AS Monaco vs Marseille Live Streaming
AS Monaco vs Marseille Live Streaming
Alajuelense vs Perez Zeledon Live Streaming
Alajuelense vs Perez Zeledon Live Streaming
Technology News | News Today | Live Streaming TV Channels