FMDB数据库中的一些操作

#pragma mark - 数据库的操作

- (BOOL)judgeModel:(TaskResourceModel *)model isInArray:(NSArray *)shopArray

{

    for (TaskResourceModel *shopModel in shopArray) {

        if ([shopModel.resourceId isEqualToString:model.resourceId]) {

            return YES;

        }

    }

    return NO;

}

 

//初始化数据库数据

- (void)initTableDatas

{

    //创建table

    [self creatSqliteTable];

    

    NSMutableArray *purchaceArray = [self selectPurchaceSqliteOfAllEntity];

    for (TaskResourceModel *model in purchaceArray) {

        NSMutableArray *shopArray = [self selectSqliteOfAllEntity];

        if (![self judgeModel:model isInArray:shopArray]) {

            [self insertEntityOfSourceStr:[model toJSONString] andSourceId:model.resourceId];

        }

    }

}

 

- (NSString *)sqlitePath

{

    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];

    NSString * doc = documentPath;

    

    NSString * path;

    

    User *user = [[User alloc] getUserInformation];

    switch ([self.productCategoryId integerValue]) {

        case 69:

        {

            NSString *userSqlitePath = [NSString stringWithFormat:@"%@_%@",user.userAccountId,@"shopTaskResource_wc.sqlite"];

            path = [doc stringByAppendingPathComponent:userSqlitePath];

            break;

        }

        case 68:

        {

            NSString *userSqlitePath = [NSString stringWithFormat:@"%@_%@",user.userAccountId,@"shopTaskResource_sina.sqlite"];

            path = [doc stringByAppendingPathComponent:userSqlitePath];

            break;

        }

        case 88:

        {

            NSString *userSqlitePath = [NSString stringWithFormat:@"%@_%@",user.userAccountId,@"shopTaskResource_qq.sqlite"];

            path = [doc stringByAppendingPathComponent:userSqlitePath];

            break;

        }

        default:

            break;

    }

    

    return path;

}

 

- (NSString *)purchaseSqlitePath

{

    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];

    NSString * doc = documentPath;

    NSString * path = [doc stringByAppendingPathComponent:@"taskResource.sqlite"];

    return path;

}

 

//插

- (void)insertEntityOfSourceStr:(NSString *)sourceStr andSourceId:(NSString *)sourceId

{

    NSFileManager * fileManager = [NSFileManager defaultManager];

    [fileManager fileExistsAtPath:[self sqlitePath]];

//    if (![fileManager fileExistsAtPath:[self sqlitePath]]){

        [self creatSqliteTable];

//    }

    

    //    'sourceId' TEXT, 'sourceModelStr'

    FMDatabase * db = [FMDatabase databaseWithPath:[self sqlitePath]];

    if ([db open]) {

        NSString *insertSql1= [NSString stringWithFormat:

                               @"INSERT INTO 'TaskSource' ('%@', '%@') VALUES ('%@', '%@')",

                               @"sourceId", @"sourceModelStr", sourceId, sourceStr];

        BOOL res = [db executeUpdate:insertSql1];

        if (!res) {

            NSLog(@"error when insert db table");

        } else {

            NSLog(@"success to insert db table");

        }

        [db close];

    }

}

 

//查所有的

- (NSMutableArray *)selectPurchaceSqliteOfAllEntity

{

    NSMutableArray *array = [NSMutableArray array];

    FMDatabase * db = [FMDatabase databaseWithPath:[self purchaseSqlitePath]];

    if ([db open]) {

        NSString *sql = @"SELECT * FROM TaskSource";

        //        NSString * sql = @"select * from user";

        FMResultSet * rs = [db executeQuery:sql];

        while ([rs next]) {

            NSString *json = [rs stringForColumn:@"sourceModelStr"];

            

            TaskResourceModel *model = [[TaskResourceModel alloc] initWithString:json error:nil];

            [array addObject:model];

        }

        [db close];

    }

    return array;

}

 

//查所有的

- (NSMutableArray *)selectSqliteOfAllEntity

{

    NSMutableArray *array = [NSMutableArray array];

    FMDatabase * db = [FMDatabase databaseWithPath:[self sqlitePath]];

    if ([db open]) {

        NSString *sql = @"SELECT * FROM TaskSource";

        //        NSString * sql = @"select * from user";

        FMResultSet * rs = [db executeQuery:sql];

        while ([rs next]) {

            NSString *json = [rs stringForColumn:@"sourceModelStr"];

            

            TaskResourceModel *model = [[TaskResourceModel alloc] initWithString:json error:nil];

            [array addObject:model];

        }

        [db close];

    }

    return array;

}

 

//删除

- (void)deletePurchaceSqliteOfSourceId:(NSString *)sourceId

{

    FMDatabase * db = [FMDatabase databaseWithPath:[self purchaseSqlitePath]];

    if ([db open]) {

        NSString *sql = [NSString stringWithFormat:@"delete from TaskSource where sourceId = '%@'",sourceId];

        //        NSString * sql = @"select * from user";

        BOOL res = [db executeUpdate:sql];

        if (!res) {

            NSLog(@"error when delete db table");

        } else {

            NSLog(@"success to delete db table");

        }

        [db close];

    }

}

 

//删除

- (void)deleteSqliteOfSourceId:(NSString *)sourceId

{

    FMDatabase * db = [FMDatabase databaseWithPath:[self sqlitePath]];

    if ([db open]) {

        NSString *sql = [NSString stringWithFormat:@"delete from TaskSource where sourceId = '%@'",sourceId];

        //        NSString * sql = @"select * from user";

        BOOL res = [db executeUpdate:sql];

        if (!res) {

            NSLog(@"error when delete db table");

        } else {

            NSLog(@"success to delete db table");

        }

        [db close];

    }

}

 

//查特定sourceid

- (TaskResourceModel *)selectSqliteOfSourceId:(NSString *)sourceId

{

    FMDatabase * db = [FMDatabase databaseWithPath:[self sqlitePath]];

    NSString *json;

    if ([db open]) {

        json = [db stringForQuery:@"SELECT sourceModelStr FROM TaskSource WHERE sourceId = ?",sourceId];

        ////        NSString * sql = @"select * from user";

        //        FMResultSet * rs = [db executeQuery:sql];

        //        while ([rs next]) {

        //            NSString *json = [rs stringForColumn:@"sourceModelStr"];

        //

        //            model = [[TaskResourceModel alloc] initWithString:json error:nil];

        ////            return

        //        }

        [db close];

    }

    TaskResourceModel *model = [[TaskResourceModel alloc] initWithString:json error:nil];

    return model;

}

 

- (void)creatSqliteTable

{

    NSFileManager * fileManager = [NSFileManager defaultManager];

    [fileManager fileExistsAtPath:[self sqlitePath]];

    

//    if (flag == NO) {

        // create it

        FMDatabase * db = [FMDatabase databaseWithPath:[self sqlitePath]];

        if ([db open]) {

            //            NSString *sqlCreateTable =  [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS '%@' ('%@' INTEGER PRIMARY KEY AUTOINCREMENT, '%@' TEXT, '%@' INTEGER, '%@' TEXT)",TABLENAME,ID,NAME,AGE,ADDRESS];

            //            BOOL res = [db executeUpdate:sqlCreateTable];

            

            NSString * sql = @"CREATE TABLE IF NOT EXISTS 'TaskSource' ('sourceId' TEXT, 'sourceModelStr' TEXT)";

            BOOL res = [db executeUpdate:sql];

            if (!res) {

                //                debugLog(@"error when creating db table");

            } else {

                //                debugLog(@"succ to creating db table");

            }

            [db close];

        } else {

            //            debugLog(@"error when open db");

        }

//    }

}

 

+ (void)deleteAllSqliteData

{

    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];

    NSString * doc = documentPath;

    

//    NSString *userSqlitePath = [NSString stringWithFormat:@"%@_%@",user.userAccountId,@"shopTaskResource_qq.sqlite"];

//    User *user = [[User alloc] getUserInformation];

//    NSString * wcPath = [doc stringByAppendingPathComponent:[NSString stringWithFormat:@"%@_%@",user.userAccountId,@"shopTaskResource_wc.sqlite"]];

//    NSString * sinaPath = [doc stringByAppendingPathComponent:[NSString stringWithFormat:@"%@_%@",user.userAccountId,@"shopTaskResource_sina.sqlite"]];

//    NSString * qqPath = [doc stringByAppendingPathComponent:[NSString stringWithFormat:@"%@_%@",user.userAccountId,@"shopTaskResource_qq.sqlite"]];

    

//    FMDatabase * db = [FMDatabase databaseWithPath:wcPath];

//    if ([db open]) {

//        NSString *sql = [NSString stringWithFormat:@"delete from TaskSource "];

//        //        NSString * sql = @"select * from user";

//        BOOL res = [db executeUpdate:sql];

//        if (!res) {

//            NSLog(@"error when delete db table");

//        } else {

//            NSLog(@"success to delete db table");

//        }

//        [db close];

//    }

//    

//    FMDatabase * sinaDb = [FMDatabase databaseWithPath:sinaPath];

//    if ([sinaDb open]) {

//        NSString *sql = [NSString stringWithFormat:@"delete from TaskSource "];

//        //        NSString * sql = @"select * from user";

//        BOOL res = [sinaDb executeUpdate:sql];

//        if (!res) {

//            NSLog(@"error when delete db table");

//        } else {

//            NSLog(@"success to delete db table");

//        }

//        [sinaDb close];

//    }

//    

//    FMDatabase * qqDb = [FMDatabase databaseWithPath:qqPath];

//    if ([qqDb open]) {

//        NSString *sql = [NSString stringWithFormat:@"delete from TaskSource "];

//        //        NSString * sql = @"select * from user";

//        BOOL res = [qqDb executeUpdate:sql];

//        if (!res) {

//            NSLog(@"error when delete db table");

//        } else {

//            NSLog(@"success to delete db table");

//        }

//        [qqDb close];

//    }

//    

//    NSString *purchasePath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) objectAtIndex:0];

//    NSString * doc = documentPath;

    NSString * purchasePath = [documentPath stringByAppendingPathComponent:@"taskResource.sqlite"];

//    FMDatabase * purchaseDb = [FMDatabase databaseWithPath:purchasePath];

//    if ([purchaseDb open]) {

//        NSString *sql = [NSString stringWithFormat:@"delete from TaskSource "];

//        //        NSString * sql = @"select * from user";

//        BOOL res = [purchaseDb executeUpdate:sql];

//        if (!res) {

//            NSLog(@"error when delete db table");

//        } else {

//            NSLog(@"success to delete db table");

//        }

//        [purchaseDb close];

//    }

    

    NSFileManager * fileManager = [NSFileManager defaultManager];

////    if ([fileManager fileExistsAtPath:wcPath]){

//        NSError *error;

//        [fileManager removeItemAtPath:wcPath error:&error];

////        NSLog(@"-------:%@",error);

////    }

////    if ([fileManager fileExistsAtPath:sinaPath]){

//        [fileManager removeItemAtPath:sinaPath error:nil];

////    }

////    if ([fileManager fileExistsAtPath:qqPath]){

//        [fileManager removeItemAtPath:qqPath error:nil];

////    }

////    if ([fileManager fileExistsAtPath:purchasePath]){

        [fileManager removeItemAtPath:purchasePath error:nil];

//    }

}