iOS WCDB 使用

iOS WCDB使用

准备

简介

WCDB 是基于SQLCipher，而SQLCipher 又是基于SQLite.故是一种关系型数据库，我们在设计表的时候，需要满足数据库设计的3范式

• ORM：类字段绑定。可以理解为官方提供了一个简单的方式，OC类属性到数据库表头的映射

搭建

使用

初始化

基本操作

1. 高度封装#import <WCDB/WCTInterface+Convenient.h> 着量食用
2. #import <WCDB/WCTInterface+ChainCall.h>
   • WCTInsert
   • WCTSelect
   • WCTRowSelect
   • WCTUpdate
   • WCTDelete
   • WCTMultiSelect
     调用方式：[database prepareXXX]

文件管理

1. 获取数据大小

  __block WCTError *error = nil;
        __block size_t fileSize;
        [database close:^{
          //you can call [getFilesSizeWithError:] for an unclosed database, but you will an inaccurate result and a warning
          fileSize = [database getFilesSizeWithError:&error];
        }];
        if (error) {
            NSLog(@"Get file size Error %@", error);
        }

2. 移动数据库

[database close:^{
          WCTError *error = nil;
          BOOL ret = [database moveFilesToDirectory:otherDirectory withError:&error];
          if (!ret) {
              NSLog(@"Move files Error %@", error);
          }
        }];

3. 获取数据库路径

NSArray *paths = [database getPaths];

联表查询

1. 简单查询（只支持内联）

    //Multi select
    {
        WCTMultiSelect *select = [[database prepareSelectMultiObjectsOnResults:{
                                                                                   WCTSampleAdvance.intValue.inTable(tableName),
                                                                                   WCTSampleAdvanceMulti.intValue.inTable(tableName2)}
                                                                    fromTables:@[ tableName, tableName2 ]] where:WCTSampleAdvance.intValue.inTable(tableName) == WCTSampleAdvanceMulti.intValue.inTable(tableName2)];
        NSArray<WCTMultiObject *> *multiObjects = select.allMultiObjects;
        for (WCTMultiObject *multiObjects : multiObjects) {
            WCTSampleAdvance *object1 = (WCTSampleAdvance *) [multiObjects objectForKey:tableName];
            WCTSampleAdvanceMulti *object2 = (WCTSampleAdvanceMulti *) [multiObjects objectForKey:tableName2];
        }
    }

2. 底层方式

- (NSArray<BCUserModel *> *)p_sqlAssembleUser:(WCTExpr)expr containSession:(BOOL)containSession order:(WCTOrderByList)orderByList{
    NSString *userTab = [BCUserModel tableName];
    NSString *sessionTab = [BCSession tableName];
    NSString *contactTab = [BCContactModel tableName];
    WCTResultList resultList;
    if(containSession){
        resultList = {
            BCSession.AllProperties.inTable(sessionTab),
            BCUserModel.AllProperties.inTable(userTab),
            BCContactModel.AllProperties.inTable(contactTab),
        };
    }else{
        resultList = {
            BCUserModel.AllProperties.inTable(userTab),
            BCContactModel.AllProperties.inTable(contactTab),
        };
    }
    WCDB::JoinClause joinClause = WCDB::JoinClause(userTab.UTF8String)
    .join(contactTab.UTF8String, WCDB::JoinClause::Type::LeftOuter)
    .on(BCUserModel.ID.inTable(userTab)  == BCContactModel.targetUserId.inTable(contactTab));
    if(containSession){
        joinClause = joinClause.join(sessionTab.UTF8String, WCDB::JoinClause::Type::LeftOuter)
        .on(BCUserModel.ID.inTable(userTab) == BCSession.userID.inTable(sessionTab));
        expr = expr && (BCSession.type.inTable([BCSession tableName]) == ChatTypePrivate || BCSession.type.inTable([BCSession tableName]).isNull());
    }
    WCDB::StatementSelect statementSelect = WCDB::StatementSelect().select(resultList).from(joinClause).where(expr).orderBy(orderByList);
    WCTError *error;
    WCTStatement *statement = [self.dataBase prepare:statementSelect withError:&error];
    NSMutableArray *users = [NSMutableArray array];
    if (statement) {
        while ([statement step]) {
            
            BCSession *session = [[BCSession alloc] init];
            BCUserModel *user = [[BCUserModel alloc] init];
            BCContactModel *contact = [[BCContactModel alloc] init];
            for (int i = 0; i < [statement getColumnCount]; ++i) {
                NSString *tableName = [statement getTableNameAtIndex:i];
                NSString *columnName = [statement getColumnNameAtIndex:i];
                WCTValue *value = [statement getValueAtIndex:i];
                if ([tableName isEqualToString:sessionTab]) {
                    if (value != NULL) [session setValue:value forKey:columnName];
                }
                if ([tableName isEqualToString:userTab]) {
                    if (value != NULL) [user setValue:value forKey:columnName];
                }
                if([tableName isEqualToString:contactTab]){
                    if(value != NULL)
                        [contact setValue:value forKey:columnName];
                }
            }
            user.session = session.ID.length ? session : nil;
            user.contactModel = contact.channelId.length ? contact : nil;
            [users addObject:user];
        }
        error = [statement getError];
        if (error) {
            NSLog(@"Error %@", error);
        }
    } else {
        NSLog(@"Error %@", error);
    }
    return users;
}

事务

//Run blocked transaction
    {
        BOOL committed = [database runTransaction:^BOOL {
          WCTSampleTransaction *object = [[WCTSampleTransaction alloc] init];
          BOOL ret = [database insertObject:object
                                       into:tableName];
          //return YES to do a commit and return NO to do a rollback
          if (ret) {
              return YES;
          }
          return NO;
        }
            event:^(WCTTransactionEvent event) {
              NSLog(@"Event %d", event);
            }];
    }

    //Run threaded transaction
    {
        //[beginTransaction], [commitTransaction], [rollbackTransaction] and all interfaces inside this transaction should run in same thread
        BOOL ret = [database beginTransaction];
        WCTSampleTransaction *object = [[WCTSampleTransaction alloc] init];
        ret = [database insertObject:object
                                into:tableName];
        if (ret) {
            ret = [database commitTransaction];
        } else {
            ret = [database rollbackTransaction];
        }
    }

    //Transaction using WCTTransaction
    {
        //You can do a transaction in different threads using WCTTransaction.
        //But it's better to run serially, or an inner thread mutex will guarantee this.
        WCTTransaction *transaction = [database getTransaction];
        BOOL ret = [transaction begin];
        dispatch_async(dispatch_queue_create("other thread", DISPATCH_QUEUE_SERIAL), ^{
          WCTSampleTransaction *object = [[WCTSampleTransaction alloc] init];
          BOOL ret = [transaction insertObject:object
                                          into:tableName];
          if (ret) {
              [transaction commit];
          } else {
              [transaction rollback];
          }
        });
    }

数据库修复

统计

1. 追溯可以统计时间

[WCTStatistics SetGlobalPerformanceTrace:^(WCTTag tag, NSDictionary<NSString *, NSNumber *> *sqls, NSInteger cost) {
      NSLog(@"Tag: %d", tag);
      [sqls enumerateKeysAndObjectsUsingBlock:^(NSString *sql, NSNumber *count, BOOL *) {
        NSLog(@"SQL: %@ Count: %d", sql, count.intValue);
      }];
      NSLog(@"Total cost %ld nanoseconds", (long) cost);
    }];

2. 查看SQL

[WCTStatistics SetGlobalSQLTrace:^(NSString *sql) {
      NSLog(@"SQL: %@", sql);
    }];

3. ERROR

[WCTStatistics SetGlobalErrorReport:^(WCTError *error) {
      NSLog(@"[Error] %@", error);
    }];

扩展

利用OC 自动生成ORM

+(void)bc_generateWCDBCategory:(Class)cls{
    NSString *clsName = NSStringFromClass(cls);
    unsigned int outCount = 0;
    objc_property_t * properties = class_copyPropertyList(cls, &outCount);
    NSLog(@"*********打印WCDB的.h文件****************\n");
    NSMutableString * strH = [[NSMutableString alloc] init];
    [strH appendString:@"\n\n\n"];
    for (unsigned int i = 0; i < outCount; i ++) {
        objc_property_t property = properties[i];
        //属性名
        NSString *name =  [NSString stringWithUTF8String:property_getName(property)];
        [strH appendFormat:@"WCDB_PROPERTY(%@);\n",name];
    }
    [strH appendString:@"\n\n\n 1"];
    NSLog(@"%@",strH);
    NSLog(@"*********打印WCDB的.m文件****************\n");
    NSMutableString * strM = [[NSMutableString alloc] init];
    [strM appendString:@"\n\n\n"];
    [strM appendFormat:@"\nWCDB_IMPLEMENTATION(%@)\n",clsName];
    [strM appendString:@"\n"];
    for (unsigned int i = 0; i < outCount; i ++) {
        objc_property_t property = properties[i];
        //属性名
        NSString *name =  [NSString stringWithUTF8String:property_getName(property)];
        [strM appendFormat:@"WCDB_SYNTHESIZE(%@, %@);\n",clsName,name];
    }
    [strM appendString:@"\n"];
    [strM appendFormat:@"WCDB_PRIMARY(%@,ID);\n",clsName];
    [strM appendFormat:@"WCDB_UNIQUE(%@, ID);\n",clsName];
    [strM appendFormat:@"WCDB_NOT_NULL(%@, ID);\n",clsName];
    [strM appendFormat:@"WCDB_INDEX(%@, \"_index\", userID);\n",clsName];
    [strM appendString:@"\n"];
    [strM appendFormat:@"+(NSString *)tableName{\n return @\"%@\";\n }",clsName];
    [strM appendString:@"\n\n\n 1"];
    NSLog(@"%@",strM);
    free(properties);
}

加密

WCTDatabase *database = [[WCTDatabase alloc] initWithPath:path];
NSData *password = [@"MyPassword" dataUsingEncoding:NSASCIIStringEncoding];
[database setCipherKey:password];

数据库设计的3范式

1. 第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
   理解：每一列都保证原子性，不可再分
   例如：保存一个地址：可以用一列来保存，正确的做法是，保存为国家、省、市、县、详细地址
2. 第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。
   理解：如果存在1对多的关系,需要单独建立表来保存关系
   例如：一个老师可以在多个班上上课，就需要把班级信息和老师信息拆分，减少数据库冗余
3. 第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.
   理解：不存在传递依赖，所有的属性都直接依赖于主键
   例如：一学生在某一个班级，那个班级在某一个学校，就需要建立一个学生表，班级表，学校表，进行合理拆表

引用

• 官方地址