Sample Database

database.h

//
//  Database.h
//  ChanelVIP
//
//  Created by Mike Han on 1/12/12.
//  Copyright (c) 2012 Comwerks Interactive. All rights reserved.
//

#import <Foundation/Foundation.h>

#import "Environment.h"
#import <sqlite3.h>

@interface Database : NSObject

@property (nonatomic, strong) NSString *databaseName, *databasePath;

- (void) checkAndCreateDatabase;
- (void) createTablesIfNotExists;
- (BOOL) saveSysConfig:(NSArray *) data;

- (NSDictionary *) getStoreData: (NSString *) storeCode;
- (NSDictionary *) authenticateStaff: (NSString *) staffCode withPassword: (NSString *) password;

- (BOOL) saveToTable:(NSString *)tableName withData:(NSArray *)data;

+ (Database *) sharedInstance;

@end

database.m

//
//  Database.m
//  ChanelVIP
//
//  Created by Mike Han on 1/12/12.
//  Copyright (c) 2012 Comwerks Interactive. All rights reserved.
//

#import "Database.h"

@implementation Database

static Database *sharedInstance = nil;

@synthesize databaseName = _databaseName;
@synthesize databasePath = _databasePath;

- (id) init{
    self = [super init];
    if(self){
        //customized constructor.
        
        //get database name
        self.databaseName = (NSString *)[[Environment sharedInstance].environment objectForKey:@"database_name"];
        NSLog(@"database name is %@", self.databaseName);
        
        //get database path
        NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
        NSString *documents = [documentPaths objectAtIndex:0];
        self.databasePath = [documents stringByAppendingPathComponent:self.databaseName];
        NSLog(@"Database path is %@", self.databasePath);
        
        [self checkAndCreateDatabase];
    }
    return self;
}

- (void) checkAndCreateDatabase{
    BOOL success;
    
    NSFileManager *fm = [NSFileManager defaultManager];
    success = [fm fileExistsAtPath:self.databasePath];
    
    if (success){
        [self createTablesIfNotExists];
        return;
    }
    
    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:self.databaseName];
    
    [fm copyItemAtPath:databasePathFromApp toPath:self.databasePath error:nil];
    [self createTablesIfNotExists];
}

- (void) createTablesIfNotExists{
    sqlite3 *database;
    if (sqlite3_open([self.databasePath UTF8String], &database) == SQLITE_OK) {
        
        //create tables if not exists
        char *errMsg;
        if(sqlite3_exec(database, "BEGIN TRANSACTION;", 
                        NULL, NULL, &errMsg) != SQLITE_OK){   
            NSLog(@"Can't begin transaction: \r%@", [NSString stringWithCString:errMsg encoding:[NSString defaultCStringEncoding]]);
        }
        
        //Create table queries.
        NSDictionary *tables = [NSDictionary dictionaryWithObjectsAndKeys:
                                @"CREATE TABLE IF NOT EXISTS sysconfig (s_configname TEXT, s_configvalue TEXT, s_remark TEXT)", @"sysconfig",
                                
                                @"CREATE TABLE IF NOT EXISTS store (s_storecode TEXT, s_name TEXT, s_country TEXT, s_address1 TEXT, s_address2 TEXT, s_address3 TEXT, s_address4 TEXT, s_telephone TEXT, s_fax TEXT, s_locationsegment TEXT);",
                                    @"store",
                                
                                @"CREATE TABLE IF NOT EXISTS staff (s_staffcode TEXT, s_name TEXT, s_password TEXT);",
                                    @"staff",
                                
                                nil];
        
        for (NSString *tableName in tables) {
            if(sqlite3_exec(database, 
                            [[tables objectForKey:tableName] cStringUsingEncoding:[NSString defaultCStringEncoding]], 
                            NULL, NULL, &errMsg) != SQLITE_OK){   
                NSLog(@"Can't create %@: \r%@",tableName, [NSString stringWithCString:errMsg encoding:[NSString defaultCStringEncoding]]);
            }
        }
        
        if(sqlite3_exec(database, "COMMIT;", 
                        NULL, NULL, &errMsg) != SQLITE_OK){   
            NSLog(@"Can't be commited: \r%@", [NSString stringWithCString:errMsg encoding:[NSString defaultCStringEncoding]]);
        }else{
            NSLog(@"Created all tables if it is required.");
        }
    }else{
        NSLog(@"Dammit - can't create tables");
    }
}

#pragma mark - Saving data

- (BOOL) saveSysConfig: (NSArray *) data{
    return [self saveToTable:@"sysconfig" withData:data];
}



- (NSDictionary *) getStoreData: (NSString *) storeCode{
    sqlite3 *database;
    sqlite3_stmt *stmt;
    
    NSDictionary* result;
    
    if(sqlite3_open([self.databasePath UTF8String], &database) == SQLITE_OK &&
       sqlite3_prepare_v2(
                          database, 
                          [[NSString stringWithFormat:@"SELECT s_storecode, s_name, s_country FROM store WHERE s_storecode = \"%@\"", storeCode] UTF8String], 
                          -1, 
                          &stmt, 
                          NULL) == SQLITE_OK
       ){
        if(sqlite3_step(stmt) == SQLITE_ROW){
            result = [[NSDictionary alloc] initWithObjectsAndKeys:
                      [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(stmt, 0)],
                      @"s_storecode",
                      [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(stmt, 1)],
                      @"s_name",
                      [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(stmt, 2)],
                      @"s_country",
                      nil];
        }else{
            result = nil;
        }
    }
    
    sqlite3_finalize(stmt);
    sqlite3_close(database);
    
    return result;
}

- (NSDictionary *) authenticateStaff: (NSString *) staffCode withPassword: (NSString *) password{
    
    sqlite3 *database;
    sqlite3_stmt *stmt;
    
    NSDictionary* staff = nil;
    
    if(sqlite3_open([self.databasePath UTF8String], &database) == SQLITE_OK &&
       sqlite3_prepare_v2(database, 
                          [[NSString stringWithFormat:@"SELECT s_staffcode, s_name, s_password FROM staff WHERE s_staffcode = \"%@\";", staffCode, password] UTF8String], 
                          -1, &stmt, NULL) == SQLITE_OK
    ){
        if(sqlite3_step(stmt) == SQLITE_ROW){
           staff = [[NSDictionary alloc] initWithObjectsAndKeys:
                    [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(stmt, 0)],
                    @"s_staffcode",
                    [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(stmt, 1)],
                    @"s_name",
                    [[NSString alloc] initWithUTF8String:(const char *) sqlite3_column_text(stmt, 2)],
                     @"s_password", nil];
            if (![password isEqualToString:[staff objectForKey:@"s_password"]]) {
                NSLog(@"Password don't match");
                NSLog(@"Staff is : %@", staff);
                staff = nil;
            }
        }
    }
    
    return staff;
}

- (BOOL) saveToTable:(NSString *)tableName withData:(NSArray *)data{
    
    sqlite3 *database;
    
    NSMutableArray *values = [[NSMutableArray alloc] init];
    
    for (int i = 0; i < [data count]; i++) {
        NSDictionary *dict = (NSDictionary *)[data objectAtIndex:i];
        NSMutableString *fields = [[NSMutableString alloc] initWithString:@""];
        NSMutableString *vals = [[NSMutableString alloc] initWithString:@""];
        
        for (NSString *key in dict) {
            if ([fields length] == 0) {
                [fields appendString:key];
            }else{
                [fields appendFormat:@", %@", key];
            }
            if ([vals length] == 0){
                [vals appendFormat:@"\"%@\"", [dict objectForKey:key]];
            }else{
                [vals appendFormat:@", \"%@\"", [dict objectForKey:key]];
            }
        }
        
        [values addObject:[NSString stringWithFormat:@"INSERT INTO %@ (%@) VALUES (%@);\r", tableName, fields, vals]];
    }
    
    
    BOOL status = YES;
    
    char *errMsg;
    
    const char *deleteFrom = [[NSString stringWithFormat:@"DELETE FROM %@", tableName] 
                             UTF8String];
    
    //open database and start transaction
    if (sqlite3_open([self.databasePath UTF8String], &database) == SQLITE_OK &&
        sqlite3_exec(database, "BEGIN TRANSACTION;", NULL, NULL, &errMsg) == SQLITE_OK &&
        sqlite3_exec(database, deleteFrom, NULL, NULL, &errMsg) == SQLITE_OK){
        
        int i;
        
        for (i = 0; i < [values count]; i++) {
            NSString *sql = [values objectAtIndex:i];
            //NSLog(@"SQL : \r%@", sql);
            
            
            if (sqlite3_exec(database, [sql UTF8String], NULL, NULL, &errMsg) != SQLITE_OK) {
                NSLog(@"Failed to insert: \r%@", 
                      [NSString stringWithCString:errMsg encoding:[NSString defaultCStringEncoding]]);
                status = NO;
                if(sqlite3_exec(database, "ROLLBACK;", NULL, NULL, &errMsg) != SQLITE_OK) NSLog(@"Can't rollback the transaction. :( ");
                break;
            }else{
                NSLog(@"Inserted a row into %@", tableName);
            }
        }
        
        if(sqlite3_exec(database, "COMMIT;", NULL, NULL, &errMsg) == SQLITE_OK){
            status = YES;
            NSLog(@"Successfully inserted %i rows into %@", i, tableName);
        }else{
            NSLog(@"Can't commit transaction for %@. \r%@", tableName, [NSString stringWithCString:errMsg encoding:[NSString defaultCStringEncoding]]);
            status = NO;
        }
    }else{
        NSLog(@"Can't start Transaction/Delete from %@ :\r%@", tableName, [NSString stringWithCString:errMsg encoding:[NSString defaultCStringEncoding]]);
        status = NO;
    }
    sqlite3_close(database);
    return status;
}

+ (Database *) sharedInstance{
    @synchronized(self){
        if(sharedInstance == nil)
            sharedInstance = [[Database alloc] init];
        return sharedInstance;
    }
}

@end