iOS WCDB使用
准备
简介
WCDB 是基于SQLCipher，而SQLCipher 又是基于SQLite.故是一种关系型数据库，我们在设计表的时候，需要满足数据库设计的3范式
- ORM：类字段绑定。可以理解为官方提供了一个简单的方式，OC类属性到数据库表头的映射
搭建
使用
初始化
基本操作
- 高度封装
#import <WCDB/WCTInterface+Convenient.h>着量食用 -
#import <WCDB/WCTInterface+ChainCall.h>- WCTInsert
- WCTSelect
- WCTRowSelect
- WCTUpdate
- WCTDelete
- WCTMultiSelect
调用方式：[database prepareXXX]
文件管理
- 获取数据大小
__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);
}
- 移动数据库
[database close:^{
WCTError *error = nil;
BOOL ret = [database moveFilesToDirectory:otherDirectory withError:&error];
if (!ret) {
NSLog(@"Move files Error %@", error);
}
}];
- 获取数据库路径
NSArray *paths = [database getPaths];
联表查询
- 简单查询（只支持内联）
//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];
}
}
- 底层方式
- (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];
}
});
}
数据库修复
统计
- 追溯可以统计时间
[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);
}];
- 查看SQL
[WCTStatistics SetGlobalSQLTrace:^(NSString *sql) {
NSLog(@"SQL: %@", sql);
}];
- 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范式
- 第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
理解：每一列都保证原子性，不可再分
例如：保存一个地址：可以用一列来保存，正确的做法是，保存为国家、省、市、县、详细地址 - 第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。
理解：如果存在1对多的关系,需要单独建立表来保存关系
例如：一个老师可以在多个班上上课，就需要把班级信息和老师信息拆分，减少数据库冗余 - 第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.
理解：不存在传递依赖，所有的属性都直接依赖于主键
例如：一学生在某一个班级，那个班级在某一个学校，就需要建立一个学生表，班级表，学校表，进行合理拆表
