iOS WCDB使用
準備
簡介
WCDB 是基于SQLCipher，而SQLCipher 又是基于SQLite.故是一種關(guān)系型數(shù)據(jù)庫，我們在設(shè)計表的時候，需要滿足數(shù)據(jù)庫設(shè)計的3范式
- ORM：類字段綁定。可以理解為官方提供了一個簡單的方式，OC類屬性到數(shù)據(jù)庫表頭的映射
搭建
使用
初始化
基本操作
- 高度封裝
#import <WCDB/WCTInterface+Convenient.h>著量食用 -
#import <WCDB/WCTInterface+ChainCall.h>- WCTInsert
- WCTSelect
- WCTRowSelect
- WCTUpdate
- WCTDelete
- WCTMultiSelect
調(diào)用方式：[database prepareXXX]
文件管理
- 獲取數(shù)據(jù)大小
__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);
}
- 移動數(shù)據(jù)庫
[database close:^{
WCTError *error = nil;
BOOL ret = [database moveFilesToDirectory:otherDirectory withError:&error];
if (!ret) {
NSLog(@"Move files Error %@", error);
}
}];
- 獲取數(shù)據(jù)庫路徑
NSArray *paths = [database getPaths];
聯(lián)表查詢
- 簡單查詢（只支持內(nèi)聯(lián)）
//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;
}
事務(wù)
//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];
}
});
}
數(shù)據(jù)庫修復(fù)
統(tǒng)計
- 追溯可以統(tǒng)計時間
[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];
數(shù)據(jù)庫設(shè)計的3范式
- 第一范式：當關(guān)系模式R的所有屬性都不能在分解為更基本的數(shù)據(jù)單位時，稱R是滿足第一范式的，簡記為1NF。滿足第一范式是關(guān)系模式規(guī)范化的最低要求，否則，將有很多基本操作在這樣的關(guān)系模式中實現(xiàn)不了。
理解：每一列都保證原子性，不可再分
例如：保存一個地址：可以用一列來保存，正確的做法是，保存為國家、省、市、縣、詳細地址 - 第二范式：如果關(guān)系模式R滿足第一范式，并且R得所有非主屬性都完全依賴于R的每一個候選關(guān)鍵屬性，稱R滿足第二范式，簡記為2NF。
理解：如果存在1對多的關(guān)系,需要單獨建立表來保存關(guān)系
例如：一個老師可以在多個班上上課，就需要把班級信息和老師信息拆分，減少數(shù)據(jù)庫冗余 - 第三范式：設(shè)R是一個滿足第一范式條件的關(guān)系模式，X是R的任意屬性集，如果X非傳遞依賴于R的任意一個候選關(guān)鍵字，稱R滿足第三范式，簡記為3NF.
理解：不存在傳遞依賴，所有的屬性都直接依賴于主鍵
例如：一學(xué)生在某一個班級，那個班級在某一個學(xué)校，就需要建立一個學(xué)生表，班級表，學(xué)校表，進行合理拆表
