Tuesday, August 19, 2008

SQLite Persistent Objects

Wouldn't it be nice if your Objective-C data objects just knew how to save and load themselves? It'd be nice if you could just call "save" and trust that your object would save itself properly somewhere, and that when you wanted to load it back in, you could just call a class method to retrieve the object or objects you wanted?

Well, now you can. I've been talking about this project for a while, but it got put on hold for the book. I have now finished the first draft of the code. This is very much a beta release, but I am interested in feedback from people, so am releasing it. In the next release, I will provide more sample code and unit tests, and other niceties, but for this release, you just get the source code files and a little bit of information about how to use them.

What does it do?

It lets you create Objective-C classes that know how to persist themselves into a SQLite database. Not only that, but it completely hides the implementation details from you - you do not need to create the database, create the tables, or do anything else except work with your ojbects. you simply subclass SQLitePersistentObject and create Objective-C 2.0 properties for every data element you want persisted. When you create an instance of this object and send it the save message, it will get saved into the database.

How does it work

Every subclass of SQLitePersistentObject gets its own table in the database. Every property that's not a collection class (NSDictionary, NSArray, NSSet or mutable variants) will get persisted into a column in the database. Properties that are pointers to other objects that are also subclasses of SQLitePersistentObject will get stored as a reference to the right row in that object's corresponding table. Collection classes gets stored as child tables, and are capable of storing either a foreign key reference (when the object they hold is a subclass of SQLitePersistentObject) or in a field on the child table.

Can all properties be stored?

No. But most can. This currently does not support properties that are c-strings, void pointers, structs, or unions. All scalars (ints, floats, etc) get saved into appropriate fields. When it comes to Cocoa objects, any class that conforms to NSCoding can be stored in a column. It is also possible to provide support for specific classes by adding a category on the class you wish to support that tells the system how to store and retrieve that object from a column's data. There are provided categories for NSDate, NSString, NSData, NSMutableData, NSNumber, and (of course) NSObject. Creating new ones to let other objects be persisted is relatively easy - just look at one of the included categories and implement the same methods. The methods are documented in NSObject-SQLitePersistence.h.

Classes that don't have direct support (the ones listed above or any that you add), will use NSObject's persistence mechanism, which archives the object into a BLOB using an NSKeyedArchiver. This is inefficient for some objects because you can't search or compare on these fields, but at least most object can be persisted. Some classes like NSImage, this method actually works quite well and there's probably no reason to add a specific category.

How do I use it?

Add all the files from the zip file to your project, link in libsqlite3.dylib. Then, declare data objects like this:

#import <foundation/foundation.h>
#import "SQLitePersistentObject.h"

@interface PersistablePerson : SQLitePersistentObject {
NSString *lastName;
NSString *firstName;
@property (nonatomic, retain) NSString * lastName;
@property (nonatomic, retain) NSString * firstName;

Once you've done that, you can just create your objects as usual:

PersistablePerson *person = [[PersistablePerson alloc] init];
person.firstName = @"Joe";
person.lastName = @"Smith";

Now, you can save it to the database (which will be created if necessary) like so:

[person save];

Loading it back in is almost as easy. Any persistable object gets dynamic class methods added to it to allow you to search. So, we could retrieve all the PersistablePerson objects that had a last name of "Smith" like so:

NSArray *people = [PersistablePerson findByLastName:@"Smith"]

Or, you could specify the exact criteria like this:

PeristablePerson *joeSmith = [PersistablePerson findFirstByCriteria:@"WHERE last_name = 'Smith' AND first_name = 'Joe'];

Notice that the camel case word divisions (marked by capital letters) got changed into an underscore, so if you want to use findByCriteria: or findFirstByCriteria: you have to make sure you get the column names correct. I plan on adding additional dynamic class methods to allow searching based on multiple criteria, but for now, if you want to search on more than one field you have to manually specify the criteria. Don't worry, it's not hard.

Can I create Indexes?

Yep. Just need to override a class method

+(NSArray *)indices;

You should return an NSArray of NSArrays. Each array contained in the returned array represents one index, and should contain the name of the properties to build the index on. Use the property names - although, in some cases, the names are changed, this method should return the actual property names, not the database column names. Here is an example implementation of indices

+(NSArray *)indices
NSArray *index1 = [NSArray arrayWithObject:@"lastName"];
NSArray *index2 = [NSArray arrayWithObjects:@"lastName", @"firstName", nil];
NSArray *index3 = [NSArray arrayWithObjects:@"age", @"lastName", @"firstName", nil];
return [NSArray arrayWithObjects:index1, index2, index3, nil];

Assuming the strings passed back all describe valid properties, the table that holds this class' data will have three indices. Easy enough, right?

What happens if I load the same object in multiple times?

These classes maintain a map of all persistable objects in memory. If you load one that's already in memory, it returns a reference to that instead of going back to the database. If you need to be able to make a copy of the object, you'll have to implement NSCopying, as SQLitePersistableObject does not currently conform to NSCopying, though it probably will before I consider this "done".

How is this Licensed

It's a very liberal license. You can use it however you want, without attribution, in any software commercial or otherwise. You are not required to contribute back your changes (although they are certainly welcome), nor are you required to distribute your changed version. The only restriction that I place on this is that if you distribute the source code, modified or unmodified, that you leave my original comments, copyright notice, and contact information, and ask that you comment your changes.

Is it fully functional?

Close to it. As I said earlier, I want to add more robust methods for finding objects. I also haven't implemented rollback. Because this maintains a memory map, it's actually difficult right now to go back to the way an object was when it was last saved because you have to make sure that every object that has a reference to that object releases it, make sure that it is gone, then re-load it from the database. Rollback is high on my priority list. Because this is an early release, there are bound to be bugs and I'm sure you can come up with enhancements that could make it better.

Does it use private or undocumented libraries?

No. It does not. It does make extensive use of the Objective-C runtime, but that is fully documented and open to developers. There is also nothing in here covered by the NDA. It was developed under Cocoa for OS X, but since it only uses foundation objects, it should, in theory, work just as well on the iPhone. Because of the NDA, I'm not actually going to say it works on the iPhone... I can neither confirm or deny that it works on said platform. But it should.

So, Where is it Already?

You can get it right here.

You should check out the file SQLitePersistentObject.h, which contains fairly extensive documentation in headerdoc format.


craig t mackenzie said...

So is this basically an ORM or am i missing something? the dynamic class thing reminds me of ActiveRecord.

I've just started playing with ActiveRecord for OBJC, have you seen it: http://code.google.com/p/activerecord/

How would you say this differs from AR?

Just curious, as i haven't yet decided on a hard and fast route for DB management.

Jeff LaMarche said...

It's very similar to ActiveRecord. I tend to think of it as "reverse ActiveRecord" because in ActiveRecord, the object's structure is created dynamically from the database structure, here it's the opposite, with the database structure being created based on the properties of the class.

But the concept is similar and the implementation take some inspiration from Ruby's ActiveRecord implementation, modified as necessary for the differences between Ruby and Obj-C.

Jeff LaMarche said...

Oh, and, yes, I saw the ActiveRecord,even worked on porting it to the iPhone for a bit, but after getting familiar with it, I decided I just didn't like the approach they had taken. I wanted something that would get out of the way more than it did, something that was more in line with the design patterns of Objective-C.

DadGuy said...

I have to say that I agree with your assessment, if you can ignore having to specify the code details of reading/writing things in and out to disk, a number of coding tasks get far simpler. Looking forward to playing around with this, thanks for writing it!

Branden Russell said...

ug. This seems to be exactly what I needed and spent many hours writing myself.
Wish I had seen this earlier!

Might still be a helpful replacement or addition to what I have, so thanks a ton for doing this!

realberen said...

Jeff, I have to say, this is a great project! I'll be sure to use it a lot and hopefully provide a lot of useful feedback :) Thank you soo much :)

Mike Huntington said...

This is a great project. I would just like to know one thing... How can I return all rows in the table like "Select *"??

jan said...

Firstly want to thank Jeff for the briliant project.

A comment on Mike's question regarding returning all rows from a table. Would it not make sense to use the findByCriteria:(NSString *)criteriaString, method with a blank NSString as the criteria?

Tested it and seems to be working well.

Zbój said...

what is the difference to Entropy (http://entropydb.googlecode.com)? It seems to me that the databases are very similar.

Andre said...

entropydb has a different license that is more restrictive.... i am going to take a look at SQLite Persistent Objects because its license is more open, it puts a sense of trust in me that i might contribute back which i appreciate and plan to do just that...

Jesse said...

I'm looking forward to valueForUndefinedKey: implementation.

Overall it's a great project.

Jesse said...

[NSArray *people = [PersistablePerson findByLastName:@"Smith"];

I'm not sure how you implement the dynamic class method, and how to use it ?

I did have to implement the "findByCriteria:(NSString*) myself to do something like that.

Besides, can you explain why you can store NSInteger which is an int, but you can't store a BOOL ?

supreme said...

It says the file does not exist the zip file to download ... ?

matt said...


Baron said...

Hi Jeff, this looks really great so thanks for putting in the effort! One question I did not see answered in the Readme or in the main header is with regard to inheritance. Does your package handle a class hierarchy like:



Doug Alcorn said...

What's the strategy to handle database schema changes? You run some code for a while and then decide you want to add an age attribute to your person object. Will that new column get created on the fly?

Andres said...


I am having trouble setting up a relationship between two tables.

I have the following sub-classes of SQLitePersistentObject;

@interface Students : SQLitePersistentObject
NSString *firstName;
NSString *lastName;
@property (nonatomic, retain) NSString *firstName;
@property (nonatomic, retain) NSString *lastName;

@interface Courses : SQLitePersistentObject
NSString *courseName;
@property (nonatomic, retain) NSString *courseName;

How would I make a relationship between these two tables so I can find, eg, all students who are taking a specific course?

This is what I tried:
I added "NSMutableArray *courses" to the Students class and "NSMutableArray *students" to the Courses class but when I inspect the DB (after creating instances of each class and assigning values to the properties and saving each) the column values for courseName were NIL instead of the values I set in code. I hadn't yet added anything to the mutable arrays.

Finally, once the relationships are set up correctly how would I use SQLitePersistentObject's +(NSArray *)findByCriteria: to find the set of students who are taking a specific course?


Zosden89 said...

Has anyone had memory issues with the persistent objects. It works fine for one class I use but for another it seems to take a long time and usually crashes on large arrays of objects being saved. I have four different objects that don't have any correlation is there something I'm suppose to do for this or what. The objects that are taking a long time is about 100 objects with 9 different strings in each. I pretty much used the same style has the example. This by the way is running on the iphone.

ran6110 said...


Would like to try it but it seems the link is broken...

Guess I'll keep looking...

Maskell said...

We are looking for experienced iPhone App Developers with many open projects requiring development.

If you are interested, please vist the followign site:


sb said...

The code is here now:

Edwin said...

scrub m65 kamagra attorney lawyer body scrub field jacket lovegra marijuana attorney injury lawyer

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