sqlite3 사용 방법

+ (void)initDb {

    if (gTheDb == nil )

        [self openDatabase];

}

+ (void)openDatabase

{

    NSString *dir = [DbManager getDbPath];

    NSString *writableDBPath = [dir stringByAppendingPathComponent:kDbName];

    

    //check file

    NSFileManager *fileManager;

    fileManager = [NSFileManager defaultManager];

    if ([fileManager fileExistsAtPath:writableDBPath] == NO) {

        

        NSError *error;

        NSString *bundleDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:kDbName];

BOOL success = [fileManager copyItemAtPath:bundleDBPath toPath:writableDBPath error:&error];

        if( !success )

        {

            NSLog(@"fail to create writable database into %@.(error=%@)",writableDBPath, error);

            return;

        }

    }

    

    // Open the database. 

    if (sqlite3_open([writableDBPath UTF8String], &gTheDb) != SQLITE_OK) {

        sqlite3_close(gTheDb);

        NSLog(@"Failed to open database. (%s)", sqlite3_errmsg(gTheDb));

        return;

    }

    NSLog(@"Success to open database. (%@)", writableDBPath);

}

+ (void)closeDatabase

{

    if (gTheDb) {

        sqlite3_close(gTheDb);

    }

}

+ (NSString *)getDbPath {

    NSString *path = [NSString stringWithFormat:@"%@/%@",

                      [Util getCachesPath],

                      KPATH_DB];

    // create directory

    [[NSFileManager defaultManager] createDirectoryAtPath:path withIntermediateDirectories:YES attributes:nil error:nil];

    return path;

}

//일반적인 쿼리 방법

    NSString *query = @"SELECT * FROM ZMESSAGE";

    NSString *query = [NSString stringWithFormat:@"SELECT * FROM ZTHREAD WHERE Z_PK=\"%@\"",ZTHREAD];
    const char *query_stmt = [query UTF8String];
    sqlite3_stmt *statement=nil;
    if (sqlite3_prepare_v2(theOldDb, query_stmt, -1, &statement, NULL) == SQLITE_OK)
    {
        NSNumber *ZSELECTEDDEFAULTSKIN = nil;
        NSString *ZSELECTEDSKINFILENAME = @"";
        if (sqlite3_step(statement) == SQLITE_ROW) {
            ZSELECTEDDEFAULTSKIN  = [NSNumber numberWithInt: sqlite3_column_int(statement, 4)];
            if( sqlite3_column_text(statement, 9) != NULL)
            ZSELECTEDSKINFILENAME = [NSString stringWithUTF8String:(const char *) sqlite3_column_text(statement, 9)];
        }
    }
    sqlite3_finalize(statement);

// 특수 문자 입력을 위한 처리 방법

query = [NSString stringWithFormat: @"INSERT INTO tbMessage (msg_kind,mt,call_mdn,message,ret,fc,ft,subject,status,read,port_height,land_height,spam,port_emo_rects,land_emo_rects,recipient,is_multirecipient) \

         VALUES (%d,%d,\'%@\',?4,\'%@\',%d,\'%@\',?8,%d,%d,%f,%f,%d,\'%@\',\'%@\',\'%@\',%d)", \

         msgkind,type,callmdn,/*message,*/receiveTime,attachedFileCount, \

         fileType,/*subject,*/status,read,port_height,land_height,isSpam,portEmoRects,landEmoRects,recipient,is_multirecipient];

query_stmt = [query UTF8String];

if (sqlite3_prepare_v2(gTheDb, query_stmt, -1, &statement, NULL) == SQLITE_OK)

{

    sqlite3_bind_text(statement, 4, [message cStringUsingEncoding:NSUTF8StringEncoding], -1,

                      SQLITE_TRANSIENT);

    sqlite3_bind_text(statement, 8, [subject cStringUsingEncoding:NSUTF8StringEncoding], -1,

                      SQLITE_TRANSIENT);

    if (sqlite3_step(statement) != SQLITE_DONE)

        NSLog(@"[migration]Fail inserting into tbMessage");

}