WCDB

October 23, 2017

iOS/MacOS中使用wcdb

集成

pod 'WCDB'

使用

Since WCDB is an Objective-C++ framework, for those files in your project that includes WCDB, you should rename their extension .m to .mm.

https://github.com/tencent/wcdb/wiki/iOS+macOS使用教程

类字段绑定（ORM）

定义该类遵循WCTTableCoding协议。
可以在类声明上定义，也可以通过文件模版在category内定义(好处是避免暴露#import <WCDB/WCDB.h>,一些引用此model的类就不用改后缀为.mm了)

//.h
#import <Foundation/Foundation.h>
#import <WCDB/WCDB.h>

@interface Dog : NSObject<WCTTableCoding>

@property(nonatomic,assign)NSInteger localID;
@property(nonatomic,copy)NSString *name;
@property(nonatomic,strong)NSDate *birthDay;//联系电话

WCDB_PROPERTY(localID)
WCDB_PROPERTY(name)
WCDB_PROPERTY(birthDay)

@end

//.mm
#import "Dog.h"

@implementation Dog

// 使用 WCDB_IMPLEMENTATIO 宏在类文件定义绑定到数据库表的类
WCDB_IMPLEMENTATION(Dog)

// 使用 WCDB_SYNTHESIZE 宏在类文件定义需要绑定到数据库表的字段
WCDB_SYNTHESIZE(Dog, localID)
WCDB_SYNTHESIZE(Dog, name)
WCDB_SYNTHESIZE(Dog, birthDay)

//定义自增主键(插入数据需要设置`model.isAutoIncrement = YES;`)
WCDB_PRIMARY_AUTO_INCREMENT(Dog, localID)
@end

或者使用模板文件(模版的安装脚本集成在WCDB的编译脚本中，只需编译一次WCDB，就会自动安装文件模版.重启Xcode后可以看到):

#import "Dog+WCTTableCoding.h"
#import <WCDB/WCDB.h>

@interface Dog (WCTTableCoding) <WCTTableCoding>
WCDB_PROPERTY(localID)
WCDB_PROPERTY(name)
WCDB_PROPERTY(birthDay)
@end

增删改查

1). 创建表

    Dog *dog = [[Dog alloc]init];
    dog.name = @"小白";
    dog.birthDay = [NSDate date];
    //自增
    dog.isAutoIncrement = YES;
    
    WCTDatabase *database = [[WCTDatabase alloc] initWithPath:kDataBasePath];
    BOOL result = [database createTableAndIndexesOfName:kDogTable //表名
                                              withClass:Dog.class];
    NSLog(@"创建表  = %d", result);

2). 插入

//插入
BOOL r = [database insertObject:dog  into:kDogTable];
    
NSLog(@"插入  = %d", result);

3). 删除

r = [database deleteObjectsFromTable:kDogTable
                               where:Dog.localID == 2];
NSLog(@"删除  = %d", r);

4). 修改

    
    Dog *tarDog = [[Dog alloc]init];
    tarDog.name = @"小黑";
    //更新全部数据的`name`字段
    r = [database updateAllRowsInTable:kDogTable
                          onProperties:Dog.name
                            withObject:tarDog];
    NSLog(@"修改  = %d", r);
    
    //修改propetises
    Dog *tarDog2 = [[Dog alloc]init];
    tarDog2.name = @"小王";
    tarDog2.birthDay = [NSDate dateWithTimeIntervalSinceNow:1000];
    tarDog2.localID = 9;
    r = [database updateRowsInTable:kDogTable
                       onProperties:{Dog.localID, Dog.birthDay , Dog.name}
                         withObject:tarDog2
                          where:Dog.localID == tarDog2.localID];

    NSLog(@"修改2  = %d", r);

5). 查询

    NSArray<Dog *> *dogs = [database getObjectsOfClass:Dog.class
                                             fromTable:kDogTable
                                               orderBy:Dog.birthDay.order()];
    for (Dog *dog in dogs) {
        NSLog(@"%@ %@",dog.name, dog.birthDay);
    }
    NSLog(@"查询  = %d", r);

查询单条可以用getOneObjectOfClass:

WINQ

WINQ的接口包括但不限于：

一元操作符：+、-、!等
二元操作符：||、&&、+、-、*、/、|、&、<<、>>、<、<=、==、!=、>、>=等
范围比较：IN、BETWEEN等
字符串匹配：LIKE、GLOB、MATCH、REGEXP等
聚合函数：AVG、COUNT、MAX、MIN、SUM等

...

凡是SQLite支持的语法规则，WINQ基本都有其对应的接口。且接口名称与SQLite的语法规则基本保持一致。

WCDB-swift的使用

WCDBSwift是基于 Swift 4.0 的 Codable 协议实现的。

下面是一个模板:

import Foundation
import WCDBSwift

class SaleORM: TableCodable {
    //Your own properties
    let variable1: Int = 0
    var variable2: String? // Optional if it would be nil in some WCDB selection
    var variable3: Double? // Optional if it would be nil in some WCDB selection
    let unbound: Date? = nil

    enum CodingKeys: String, CodingTableKey {
        typealias Root = SaleORM

        //List the properties which should be bound to table
        case variable1 = "custom_name"
        case variable2
        case variable3

        static let objectRelationalMapping = TableBinding(CodingKeys.self)

        //Column constraints for primary key, unique, not null, default value and so on. It is optional.
        //static var columnConstraintBindings: [CodingKeys: ColumnConstraintBinding]? {
        //    return [
        //        .variable: ColumnConstraintBinding(isPrimary: true, isAutoIncrement: true),
        //        .variable2: ColumnConstraintBinding(isUnique: true)
        //    ]
        //}

        //Index bindings. It is optional.
        //static var indexBindings: [IndexBinding.Subfix: IndexBinding]? {
        //    return [
        //        "_index": IndexBinding(indexesBy: CodingKeys.variable2)
        //    ]
        //}

        //Table constraints for multi-primary, multi-unique and so on. It is optional.
        //static var tableConstraintBindings: [TableConstraintBinding.Name: TableConstraintBinding]? {
        //    return [
        //        "MultiPrimaryConstraint": MultiPrimaryBinding(indexesBy: variable2.asIndex(orderBy: .descending), variable3.primaryKeyPart2)
        //    ]
        //}

        //Virtual table binding for FTS and so on. It is optional.
        //static var virtualTableBinding: VirtualTableBinding? {
        //    return VirtualTableBinding(with: .fts3, and: ModuleArgument(with: .WCDB))
        //}
    }

    //Properties below are needed only the primary key is auto-incremental
    //var isAutoIncrement: Bool = false
    //var lastInsertedRowID: Int64 = 0
}

1). 需要在类中定义CodingKeys枚举类型,并遵循 String 和 CodingTableKey。 2). 枚举列举每一个需要定义的字段。 3). 对于变量名与表的字段名不一样的情况，可以使用别名进行映射，如case variable1 = "custom_name" 4). 对于不需要写入数据库的字段，则不需要在 CodingKeys 内定义 5). 对于变量名与 SQLite 的保留关键字冲突的字段，同样可以使用别名进行映射，如 offset 是 SQLite 的关键字

字段约束

class Animal: TableCodable {

    //动物的id,设置为主键
    var identifier: Int? = nil
    //名字,`唯一`:映射到`animal_name`
    var name: String?
    //给默认值,默认12岁
    var age:Int? = nil
 
    enum CodingKeys: String, CodingTableKey {
        typealias Root = Animal

        //List the properties which should be bound to table
        case identifier
        case name = "animal_name"
        case age
        
        static let objectRelationalMapping = TableBinding(CodingKeys.self)

        //Column constraints for primary key, unique, not null, default value and so on. It is optional.
        static var columnConstraintBindings: [CodingKeys: ColumnConstraintBinding]? {
            return [
                .identifier: ColumnConstraintBinding(isPrimary: true, isAutoIncrement: true),
                //.name: ColumnConstraintBinding(isUnique: true)
                .name: ColumnConstraintBinding(isNotNull: true, defaultTo: "旺财")
            ]
        }
    }

    //Properties below are needed only the primary key is auto-incremental
    var isAutoIncrement: Bool = true  // 用于定义是否使用自增的方式插入
    var lastInsertedRowID: Int64 = 0  // 插入，完成自增后会将主键复制给`lastInsertedRowID`
}

增删改查

1). 插入

插入操作有 "insert" 和 "insertOrReplace" 两个接口。故名思义，前者只是单纯的插入数据，当数据出现冲突时会失败，而后者在主键一致时，新数据会覆盖旧数据。

let database = Database(withPath: "/Users/tyrad/Desktop/sample.db")
try database.create(table: "animalTable", of: Animal.self)
  
let animal = Animal.init()
animal.age = 10
animal.name = "小嘿"
  
animal.identifier = 5
  
//try database.insert(objects: animal, intoTable: "animalTable")
//后者在主键一致时，新数据会覆盖旧数据。
try database.insertOrReplace(objects: animal, intoTable: "animalTable")

2). 删除

删除操作只有一个接口，其函数原型为：

func delete(fromTable table: String, // 表名
            where condition: Condition? = nil, // 符合删除的条件
            orderBy orderList: [OrderBy]? = nil, // 排序的方式
            limit: Limit? = nil, // 删除的个数
            offset: Offset? = nil // 从第几个开始删除
) throws

清空表数据(保留表结构)

try database.delete(fromTable: "animalTable")

举例:

// 删除 age not null，按 identifier 升序排列后的前 3 行的后 2 行数据
try Sample.createDatabase().delete(fromTable: "animalTable", where:Animal.Properties.age.isNotNull(), orderBy: [Animal.Properties.identifier.asOrder(by: .ascending)], limit: 2, offset: 3)

3). 更新操作

更新操作有 "update with object" 和 "update with row" 两个接口。它们的原型分别

func update<Object: TableEncodable>(
    table: String,
    on propertyConvertibleList: [PropertyConvertible],
    with object: Object,
    where condition: Condition? = nil,
    orderBy orderList: [OrderBy]? = nil,
    limit: Limit? = nil,
    offset: Offset? = nil) throws

func update(
    table: String,
    on propertyConvertibleList: [PropertyConvertible],
    with row: [ColumnEncodableBase],
    where condition: Condition? = nil,
    orderBy orderList: [OrderBy]? = nil,
    limit: Limit? = nil,
    offset: Offset? = nil) throws

举例:

let newAnimal = Animal.init()
newAnimal.age = 100
newAnimal.name = "小花"
  
try database.update(table: "animalTable",
             on: [Animal.Properties.age,Animal.Properties.name],
             with: newAnimal)

4). 查询

查找接口对应的操作有 8 个，分别为

getObjects
getObject
getRows
getRow
getColumn
getDistinctColumn
getValue
getDistinctValue

虽然接口较多，但大部分都是为了简化操作而提供的便捷接口。实现上其实与 update 类似，只有 "object" 和 "row" 两种方式。

func getObjects<Object: TableDecodable>(
    on propertyConvertibleList: [PropertyConvertible],
    fromTable table: String,
    where condition: Condition? = nil,
    orderBy orderList: [OrderBy]? = nil,
    limit: Limit? = nil,
    offset: Offset? = nil) throws -> [Object]

func getObject<Object: TableDecodable>(i
    on propertyConvertibleList: [PropertyConvertible],
    fromTable table: String,
    where condition: Condition? = nil,
    orderBy orderList: [OrderBy]? = nil,
    offset: Offset? = nil) throws -> Object?

其他

事务、索引、数据库修复等。