I have been playing with SQLite recently and toying with the idea of creating some kind of framework upon which I could build persistent data objects, much like the Zend way of doing things in Zend Framework for PHP. That was, until I came across SQLite Persistent Objects…
SQLite Persistent Objects is written by Jeff Lamarche and it is a framework which specifies a single class which you can extend to give SQLite persistent abilities to that object. The basic functionality goes like this:
#import "SQLitePersistentObject.h"
@interface Person : SQLitePersistentObject {
NSString *name;
NSString *email;
}
@property (nonatomic, retain) NSString *name;
@property (nonatomic, retain) NSString *email;
@end
#include "Person.h"
@implementation Person
@synthesize name, email;
- (void)dealloc
{
[name release];
[email release];
[super dealloc];
}
@end
Now, within your application you can use something along these lines:
#include "SQLiteInstanceManager.h" #include "Person.h" ... // Set the database path [[SQLiteInstanceManager sharedManager] setDatabaseFilepath:@"database.sqlite"]; // Create a new person, set it up and save it Person *newPerson = [[Person alloc] init]; newPerson.name = @"Joe Bloggs"; newPerson.email = @"joe.bloggs@example.com"; [newPerson save]; [newPerson release];
Then, SQLite Persistent Objects will create the table if necessary and add the new person to the table. It really is that simple. You can even have multiple objects derived from SQLitePersistenObject which contain references to each other and when you call save on them, they will notice the links and sort things out so that the database tables contain sufficient references to each other to persist that link. Another great feature is the ability of the code to remember what has been loaded previously and keep it in memory so that it doesn’t do unnecessary loading.
If you’re thinking of using SQLite in your iPhone / iPod Touch / Cocoa app, then be sure to check out SQLite Persistent Objects!
Yeah, I’m using it in my current (actually next) app and it’s really great and that easy!
How to create foreign key in your Person model to for example Car model?
I tried to use something like this
@interface Person : SQLitePersistentObject {
Car *car;
// …. your code ….
}
@property (nonatomic, retain) Car *car;
// …. your code ….
@end
However SQLitePersistent doesn’t create any references, just TEXT fields, and store there [car description] as I suppose.
Any ideas how to create real foreign key to another model?
Thanks in advance.
Hi Jack,
One thing to note, in order for SQLLitePersistentObject to save a link to another object, that other object must also be a subclass of SQLLitePersistentObject, or at least some type of object which it knows how to save (Number, Data, String, etc).
Can you check that Car is a subclass of SQLLitePersistentObject as well please?
Thanks,
Matt
Matt, of course Car is the subclass of SQLLitePersistentObject.
In my real app, I have four classes (Application, Country, Currency, Downloads)
Downloads, contains references to Application, Country and Currency, here is example:
@interface Downloads : SQLitePersistentObject {
Application *application;
Country *country;
Currency *currency;
float royalty;
int units;
int isUpdate;
NSDate *date;
NSString *appId;
}
@property (nonatomic, retain) Application *application;
@property (nonatomic, retain) Country *country;
@property (nonatomic, retain) Currency *currency;
@property (assign) float royalty;
@property (assign) int units;
@property (assign) int isUpdate;
@property (nonatomic, retain) NSDate *date;
@property (nonatomic, retain) NSString *appId;
@end
Example of Application class:
@interface Application : SQLitePersistentObject {
NSString *name;
}
@property (nonatomic, retain) NSString *name;
@end
And here is DB Schema created by SQLitePersistent
CREATE TABLE downloads (pk INTEGER PRIMARY KEY, application TEXT, app_id TEXT, is_update INTEGER, royalty REAL, currency TEXT, country TEXT, date REAL, units INTEGER)
application, currency and country are TEXT field, no foreign keys created. What I’ve done wrong?
Thanks in advance.
Hi Jack,
Yes, that’s right. SQLite doesn’t have foreign keys. It will store it as a text field and in that field it will store something which SQLitePersistentObject knows how to find the relevant object in the other table. Does that make sense?
Does it work for you? i.e. can you save and load objects correctly?
Matt
Matt, of course SQLite has foreign keys, as any relational DB.
I asked about foreign keys because if I use them in my particular application, it’ll give me huge advantage (I’ll store only unique data (such as countries, or a currencies) and at the downloads table will be contain only references to them (integers)). It’s more convenient, than just store, all bunch of data.
By the way, i have dropped email to Jeff (author of the SQLitePersistentObject), but looks like he went to the WWDC, I’ll update you with info when I receive reply.
Hi Jack,
I’m not sure if SQLite really does have foreign keys, check out http://www.sqlite.org/omitted.html . There’s lots of people which mention how to do foreign key constraints (properly) using triggers.
That’s a good idea to ask Jeff, but also, take a look in the source code itself (the file of interest is http://code.google.com/p/sqlitepersistentobjects/source/browse/trunk/src/SQLitePersistentObject.m). You’ll notice that it creates a cross reference table to do the “foreign key” behaviour.
I still don’t quite know why you are having issues with it, it will work exactly as you expect by only having to store things once.
Matt
Thanks Matt, I’ll check.
Hi Matt,
I was wondering if you could shed some light on how to setup a has_many relationship using SQlite Persistent Objects. I’ve been looking into this for quite a while and can’t find any good information. I think I understand the belongs_to relationship where you just specify another SLPO as a property but not sure how to get the 1-to-many collection, wether you need to setup your own method to return an array (in which case how to setup the WHERE statement), or if this is somehow already built in (I didn’t see it in the source).
Thanks!
Hi Brian,
Thanks for the comment. I haven’t used SLPO for a while, but I really thought you could just use an NSArray as a property to create a 1-to-many relationship. Have you tried that>
Matt