FMDB 相关

#import "ViewController.h"

#import "FMDBMigrationManager.h"
#import "FMDB.h"

#define PATH_OF_DOCUMENT    [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0]
#define CREATE_TEACHER_TAB @"CREATE TABLE IF NOT EXISTS %@(content text,time text)"
//插入基础元素
#define INSERT_SQL @"insert into %@(content, time) values(?,?)"

@interface ViewController () {
    
    FMDatabaseQueue *dbQueue;
}

@end

@implementation ViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
    
    NSString * doc = PATH_OF_DOCUMENT;
    NSString * path = [doc stringByAppendingPathComponent:@"teacherCache.sqlite"];
    
    if (!dbQueue) {
        
        dbQueue = [FMDatabaseQueue databaseQueueWithPath:path];
    }
    
    [self createTable:@"user"];
}

- (NSString *)databaseFilePath {
    
    NSString * doc = PATH_OF_DOCUMENT;
    NSString * path = [doc stringByAppendingPathComponent:@"teacherCache.sqlite"];
    return path;
}

- (void)didReceiveMemoryWarning {
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

- (BOOL)isTableExist:(NSString *)tableName {
    
    FMDatabase *db = [FMDatabase databaseWithPath:[self databaseFilePath]];
    if ([db open]) {
        
        FMResultSet *rs = [db executeQuery:@"select count(*) as 'count' from sqlite_master where type ='table' and name = ?", tableName];
        while ([rs next]) {
            
            // just print out what we've got in a number of formats.
            NSInteger count = [rs intForColumn:@"count"];
            
            [db close];
            
            if (count <= 0) {
                
                return NO;
            }else {
                
                return YES;
            }
        }
        [db close];
    }else {
        
        NSLog(@"error when open db");
    }
    
    return NO;
}

- (void)createTable:(NSString*)table {
    
    if ([self isTableExist:table]) {
        
        NSLog(@"table exist already");
        
        NSLog(@"alter now");
        FMDatabase *db = [FMDatabase databaseWithPath:[self databaseFilePath]];
        if ([db open]) {
            if (![db columnExists:@"noe" inTableWithName:table]) {
                
                [self alterTable:table];
            }
            
            [db close];
        }
        
        return;
    }
    FMDatabase *db = [FMDatabase databaseWithPath:[self databaseFilePath]];
    if ([db open]) {
        
        NSString *sSQL = [NSString stringWithFormat:CREATE_TEACHER_TAB, table];
        BOOL res = [db executeUpdate:sSQL];
        if (!res) {
            
            NSLog(@"error when creating db table");
        }else {
            
            NSLog(@"succ to creating db table");
            
            [self insertContentTable:table Content:@"ssfdsf" Time:@"2015-04-05"];
        }
        [db close];
        
    }else {
        
        NSLog(@"error when open db");
    }
}

- (void)insertContentTable:(NSString*)table Content:(NSString*)content Time:(NSString *)time
{
    [dbQueue inDatabase:^(FMDatabase *db)
     {
         NSString *sql = [NSString stringWithFormat:INSERT_SQL,table];
         
         BOOL res = [db executeUpdate:sql,content,time];
         if (!res)
         {
             NSLog(@"error when insertContentTable");
         }
         else
         {
             NSLog(@"succ to insertContentTable");
         }
     }];
}

- (void)alterTable:(NSString*)table {
    
    [dbQueue inDatabase:^(FMDatabase *db) {
         
         NSString *sql = [NSString stringWithFormat:@"ALTER TABLE %@ ADD %@ text",table, @"noe"];
         
         BOOL res = [db executeUpdate:sql];
         if (!res)
         {
             NSLog(@"error when alterTable");
         }
         else
         {
             NSLog(@"succ to alterTable");
         }
     }];
}