Skip to content

Instantly share code, notes, and snippets.

@mootoh
Created March 4, 2009 16:40
Show Gist options
  • Save mootoh/73900 to your computer and use it in GitHub Desktop.
Save mootoh/73900 to your computer and use it in GitHub Desktop.
/*
* simple SQLite3 wrapper for Objective-C.
*
* @author mootoh
*/
#import <sqlite3.h>
@implementation LocalCache
-(id) init
{
if (self = [super init]) {
path_ = [[self databasePath] retain];
if (SQLITE_OK == sqlite3_open([path_ UTF8String], &handle_)) {
[self migrate];
}
}
return self;
}
- (void) dealloc
{
[path_ release];
sqlite3_close(handle_);
[super dealloc];
}
- (NSArray *) select:(NSDictionary *)dict from:(NSString *)table
{
return [self select:dict from:table option:nil];
}
- (NSArray *) select:(NSDictionary *)dict from:(NSString *)table option:(NSDictionary *)option
{
sqlite3_stmt *stmt = nil;
NSString *keys = @"";
for (NSString *key in dict)
keys = [keys stringByAppendingFormat:@"%@, ", key];
keys = [keys substringToIndex:keys.length-2]; // cut last ', '
NSString *sql = [NSString stringWithFormat:@"SELECT %@ from %@", keys, table];
if (option) {
// TODO
}
if (sqlite3_prepare_v2(handle_, [sql UTF8String], -1, &stmt, NULL) != SQLITE_OK) {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(handle_));
}
NSMutableArray *results = [NSMutableArray array];
while (sqlite3_step(stmt) == SQLITE_ROW) {
NSMutableDictionary *result = [NSMutableDictionary dictionary];
int i = 0;
for (NSString *key in dict) {
Class klass = [dict objectForKey:key];
if (klass == [NSNumber class]) {
NSNumber *num = [NSNumber numberWithInt:sqlite3_column_int(stmt, i)];
[result setObject:num forKey:key];
} else if (klass == [NSString class]) {
char *chs = (char *)sqlite3_column_text(stmt, i);
NSString *str = chs ? [NSString stringWithUTF8String:chs] : @"";
[result setObject:str forKey:key];
} else {
NSAssert(NO, @"not reach here!");
}
i++;
}
[results addObject:result];
}
return [results retain];
}
- (void) insert:(NSDictionary *)dict into:(NSString *)table
{
sqlite3_stmt *stmt = nil;
NSString *keys = @"";
NSString *vals = @"";
for (NSString *key in dict) {
keys = [keys stringByAppendingFormat:@"%@, ", key];
id v = [dict objectForKey:key];
NSString *val = nil;
if ([v isKindOfClass:[NSString class]]) {
val = [NSString stringWithFormat:@"'%@'", (NSString *)v];
} else if ([v isKindOfClass:[NSNumber class]]) {
val = [(NSNumber *)v stringValue];
} else {
NSAssert(NO, @"not reach here");
}
vals = [vals stringByAppendingFormat:@"%@, ", val];
}
// cut last ', '
keys = [keys substringToIndex:keys.length-2];
vals = [vals substringToIndex:vals.length-2];
NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES (%@);", table, keys, vals];
if (sqlite3_prepare_v2(handle_, [sql UTF8String], -1, &stmt, NULL) != SQLITE_OK) {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(handle_));
}
int i = 1;
for (NSString *key in dict) {
id v = [dict objectForKey:key];
if ([v isKindOfClass:[NSString class]]) {
sqlite3_bind_text(stmt, i, [(NSString *)v UTF8String], -1, SQLITE_TRANSIENT);
} else if ([v isKindOfClass:[NSNumber class]]) {
sqlite3_bind_int(stmt, i, [(NSNumber *)v intValue]);
} else {
NSAssert(NO, @"not reach here");
}
i++;
}
if (SQLITE_ERROR == sqlite3_step(stmt)) {
[[NSException
exceptionWithName:@"LocalCacheException"
reason:[NSString stringWithFormat:@"Failed to INSERT INTO LocalCache: msg='%s'", sqlite3_errmsg(handle_)]
userInfo:nil] raise];
}
sqlite3_finalize(stmt);
}
- (void) update:(NSDictionary *)dict table:(NSString *)table condition:(NSString *)cond
{
sqlite3_stmt *stmt = nil;
NSString *sets = @"";
for (NSString *key in dict) {
id v = [dict objectForKey:key];
NSString *val = nil;
if ([v isKindOfClass:[NSString class]]) {
val = [NSString stringWithFormat:@"'%@'", (NSString *)v];
} else if ([v isKindOfClass:[NSNumber class]]) {
val = [(NSNumber *)v stringValue];
} else {
NSAssert(NO, @"not reach here");
}
sets = [sets stringByAppendingFormat:@"%@=%@, ", key, val];
}
// cut last ', '
sets = [sets substringToIndex:sets.length-2];
NSString *sql = [NSString stringWithFormat:@"UPDATE %@ SET %@", table, sets];
sql = [sql stringByAppendingFormat:@" %@;", cond ? cond : @""];
NSLog(@"update sql = %@", sql);
if (sqlite3_prepare_v2(handle_, [sql UTF8String], -1, &stmt, NULL) != SQLITE_OK) {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(handle_));
}
int i = 1;
for (NSString *key in dict) {
id v = [dict objectForKey:key];
if ([v isKindOfClass:[NSString class]]) {
sqlite3_bind_text(stmt, i, [(NSString *)v UTF8String], -1, SQLITE_TRANSIENT);
} else if ([v isKindOfClass:[NSNumber class]]) {
sqlite3_bind_int(stmt, i, [(NSNumber *)v intValue]);
} else {
NSAssert(NO, @"not reach here");
}
i++;
}
if (SQLITE_ERROR == sqlite3_step(stmt)) {
[[NSException
exceptionWithName:@"LocalCacheException"
reason:[NSString stringWithFormat:@"Failed to UPDATE LocalCache: msg='%s'", sqlite3_errmsg(handle_)]
userInfo:nil] raise];
}
sqlite3_finalize(stmt);
}
- (void) delete:(NSString *)table condition:(NSString *)cond
{
sqlite3_stmt *stmt = nil;
NSString *sql = [NSString stringWithFormat:@"DELETE FROM %@ %@;", table, cond ? cond : @""];
NSLog(@"delete sql = %@", sql);
if (sqlite3_prepare_v2(handle_, [sql UTF8String], -1, &stmt, NULL) != SQLITE_OK) {
NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(handle_));
}
if (SQLITE_ERROR == sqlite3_step(stmt)) {
[[NSException
exceptionWithName:@"LocalCacheException"
reason:[NSString stringWithFormat:@"Failed to DELETE FROM LocalCache: msg='%s'", sqlite3_errmsg(handle_)]
userInfo:nil] raise];
}
sqlite3_finalize(stmt);
}
static LocalCache *s_local_cache = nil;
+ (LocalCache *) sharedLocalCache
{
if (s_local_cache == nil) {
s_local_cache = [[LocalCache alloc] init];
}
return s_local_cache;
}
@end // LocalCache
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment