Post

WCDB之自定义数据库字段(二)

Posted
By 19 min read

参考：

前言

WCDB.swift中默认已经支持一些基本类型，但是如果复杂的类型需要自定一，基本原理是转换成这次的基本类型来存储

数据库中的类型Swift中的类型
32 位整型Bool, Int, Int8, Int16, Int32, UInt, UInt8, UInt16, UInt32
64 位整型Int64, UInt64, Date
浮点型Float, Double
字符串类型String, URL
二进制类型Data, Array, Dictionary, Set

如果是基本类型都可以直接存，如果是集合类型，确定好集合类型的具体类型后也可以直接存，比如[Int]、[String: String]

如果不是基本类型，如枚举、对象、泛型集合如[String: Any]，需要自定义实现ColumnCodable

1、自定义枚举

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

//  PeopleModel.swift
enum Gender: Int {
    case male // 男
    case femal // 女
    case other // 其他
}
//  PeopleDBModel.swift
extension Gender: ColumnCodable {
    static var columnType: ColumnType {
        return .integer32
    }
    init?(with value: Value) {
        self.init(rawValue: value.intValue)
    }
    func archivedValue() -> Value {
        return Value(self.rawValue)
    }
}

2、自定义枚举2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

//  PeopleModel.swift
enum SportType {
    typealias RawValue = String
    case football // 足球
    case basketball // 篮球
    case other(String) // 其他
    
    init?(rawValue: String) {
        switch rawValue {
        case "football": self = .football
        case "basketball": self = .basketball
        default: self = .other(rawValue)
        }
    }
    
    var rawValue: String {
        switch self {
        case .football: return "football"
        case .basketball: return "basketball"
        case .other(let i): return i
        }
    }
}

//  PeopleDBModel.swift
extension SportType: ColumnCodable {
    // ===start===由于分类与定义不在同一个文件，需要自定decoder\encoder
    init(from decoder: any Decoder) throws {
        let container = try decoder.singleValueContainer()
        let sportString = try container.decode(String.self)
        switch sportString {
        case "football":
            self = .football
        case "basketball":
            self = .basketball
        default:
            self = .other(sportString)
        }
    }
    
    func encode(to encoder: Encoder) throws {
        var container = encoder.singleValueContainer()
        switch self {
        case .football:
            try container.encode("football")
        case .basketball:
            try container.encode("basketball")
        case .other(let val):
            try container.encode(val)
        }
    }
    // ===end===由于分类与定义不在同一个文件，需要自定decoder\encoder
    
    static var columnType: ColumnType {
        return .text
    }
    init?(with value: Value) {
        self.init(rawValue: value.stringValue)
    }
    func archivedValue() -> Value {
        return Value(self.rawValue)
    }
}

3、简化枚举的写法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

// 成员类型
enum MemberType {
  typealias RawValue = Int
  case ower // 群主
  case admin // 管理员
  case member // 成员
  case other(Int)

  init() {
    self = .ower
  }

  init?(rawValue: Int) {
    switch rawValue {
    case 0: self = .ower
    case 1: self = .admin
    case 2: self = .member
    default: self = .other(rawValue)
    }
  }

  var rawValue: Int {
    switch self {
    case .ower: return 0
    case .admin: return 1
    case .member: return 2
    case .other(let val): return val
    }
  }

}
// 协议实现
// MARK: - 枚举类型实现ColumnCodable
extension MemberType: ColumnCodable {
    // ===start===由于分类与定义不在同一个文件，需要自定decoder\encoder
    init(from decoder: any Decoder) throws {
        let container = try decoder.singleValueContainer()
        let val = try container.decode(RawValue.self)
        self.init(rawValue: val)!
    }
    func encode(to encoder: Encoder) throws {
        var container = encoder.singleValueContainer()
        try container.encode(self.rawValue)
    }
    // ===end===由于分类与定义不在同一个文件，需要自定decoder\encoder
    // ** ColumnCodable -- start **/
    static var columnType: ColumnType {
        return .integer32
    }
    init?(with value: Value) {
        self.init(rawValue: value.intValue)
    }
    func archivedValue() -> Value {
        return Value(self.rawValue)
    }
    // ** ColumnCodable -- end **/
}

4、struct、class类型实现ColumnCodable协议定义类型一

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

//  PeopleModel.swift
struct Pet {
    enum PetType: Int {
        case cat
        case dog
    }
    var name: String
    var age: Int
    var type: PetType
}
//  PeopleDBModel.swift
// MARK: 对象类型实现ColumnCodable
extension Pet: ColumnCodable {
    // ** 由于class或struct的定义，与extension不在同一个文件里才需要 -- start **/
    enum CodingKeys: String, CodingKey {
        case name
        case age
        case type
    }
    func encode(to encoder: any Encoder) throws {
        var container = encoder.container(keyedBy: CodingKeys.self)
        try container.encode(name, forKey: .name)
        try container.encode(age, forKey: .age)
        try container.encode(type.rawValue, forKey: .type)
    }
    
    init(from decoder: any Decoder) throws {
        
        let container = try decoder.container(keyedBy: CodingKeys.self)
        let name = try container.decode(String.self, forKey: .name)
        let age = try container.decode(Int.self, forKey: .age)
        let typeVal = try container.decode(Int.self, forKey: .type)
        guard let type = PetType(rawValue: typeVal) else {
            throw NSError(domain: "type: \(typeVal)", code: 0)
        }
        self.init(name: name, age: age, type: type)
    }
    // ** 由于class或struct的定义，与extension不在同一个文件里才需要 -- end **/
    // 数据库存储的类型
    
    // ** ColumnCodable -- start **/
    static var columnType: ColumnType {
        return .BLOB
    }
    // 从数据库里解析成Model
    init?(with value: Value) {
        let data = value.dataValue
        guard data.count > 0,
              let jsonDict = try? JSONSerialization.jsonObject(with: data, options: []) as? [String: Any] else {
            return nil
        }
        guard let name = jsonDict["name"] as? String else {
            return nil
        }
        guard let age = jsonDict["age"] as? Int else {
            return nil
        }
        guard let typeVal = jsonDict["type"] as? Int,
           let type = Pet.PetType(rawValue: typeVal) else {
            return nil
        }
        self.init(name: name, age: age, type: type)
        // ** ColumnCodable -- end **/
    }

    // Model存入数据库时调用
    func archivedValue() -> Value {
        var jsonDict: [String: Any] = [:]
        jsonDict["name"] = name
        jsonDict["age"] = age
        jsonDict["type"] = type.rawValue
        guard let data = try? JSONSerialization.data(withJSONObject: (jsonDict as NSDictionary), options: []) else {
            return Value(nil)
        }
        return Value(data)
    }
}

4、struct、class类型实现ColumnJSONCodable协议定义类型二（简化版）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

//  PeopleModel.swift
struct Car {
    var name: String
    var price: Double
    var isNew: Bool
}
//  PeopleDBModel.swift
// MARK: - 对象类型实现ColumnJSONCodable
extension Car: ColumnJSONCodable {
    enum CodingKeys: String, CodingKey {
        case name
        case price
        case isNew
    }
    func encode(to encoder: any Encoder) throws {
        var container = encoder.container(keyedBy: CodingKeys.self)
        try container.encode(name, forKey: .name)
        try container.encode(price, forKey: .price)
        try container.encode(isNew, forKey: .isNew)
    }
    
    init(from decoder: any Decoder) throws {
        
        let container = try decoder.container(keyedBy: CodingKeys.self)
        name = try container.decode(String.self, forKey: .name)
        price = try container.decode(Double.self, forKey: .price)
        isNew = try container.decode(Bool.self, forKey: .isNew)

    }
}

完整demo：

一、定义业务Model

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115

import Foundation

enum Gender: Int {
    case male // 男
    case femal // 女
    case other // 其他
}

enum SportType {
    typealias RawValue = String
    case football // 足球
    case basketball // 篮球
    case other(String) // 其他
    
    init?(rawValue: String) {
        switch rawValue {
        case "football": self = .football
        case "basketball": self = .basketball
        default: self = .other(rawValue)
        }
    }
    
    var rawValue: String {
        switch self {
        case .football: return "football"
        case .basketball: return "basketball"
        case .other(let i): return i
        }
    }
}

struct Pet {
    enum PetType: Int {
        case cat
        case dog
    }
    var name: String
    var age: Int
    var type: PetType
}

struct Car {
    var name: String
    var price: Double
    var isNew: Bool
}

// 不直接使用dbmodel作为业务model使用时为了方便一些自定义字段的使用等
// PeopleModel - DBWorker(WCDB) - PeopleDBModel
class PeopleModel {
    var name: String? // 姓名-字符串-可以直接存DB
    var age: Int? // 年龄-整数-可以直接存DB
    var height: Double? // 身高-小数-可以直接存DB
    var isMarried: Bool? // 是否已婚-布尔值-可以直接存DB
    var birthDate: Date? // 时间-可以直接存DB
    var loveBooks: [String]? // 基本类型的组合-可以直接存
    var gender: Gender? // int类型枚举-需要转换才能存
    var loveSport: SportType? // String的枚举-需要转换才能存
    var pet: Pet?// 对象类型-需要转换才能存
    var car: Car? // 对象类型-需要转换才能存
    var extra: [String: Any]?// 字典类型-需要转换才能存,由于不能直接存，所以使用另一个计算属性来中转
    var extraData: Data? {
        get {
            if let jsonDict = extra,
                  let data = try? JSONSerialization.data(withJSONObject: (jsonDict as NSDictionary), options: []) {
                return data
            } else {
                return nil
            }
        } set {
            if let data = newValue,
                  let jsonDict = try? JSONSerialization.jsonObject(with: data, options: []) as? [String: Any] {
                extra = jsonDict
            }
        }
    }
}

extension PeopleModel {
    // 业务数据存入数据库时使用
    var dbModel: PeopleDBModel {
        let dbInfo = PeopleDBModel()
        dbInfo.name = name
        dbInfo.age = age
        dbInfo.height = height
        dbInfo.isMarried = isMarried
        dbInfo.birthDate = birthDate
        dbInfo.loveBooks = loveBooks
        dbInfo.gender = gender
        dbInfo.loveSport = loveSport
        dbInfo.pet = pet
        dbInfo.car = car
        dbInfo.extraData = extraData
        return dbInfo
    }
}
extension PeopleDBModel {
    // 数据库数据库解析成业务数据使用
    var bizModel: PeopleModel {
        let bizModel = PeopleModel()
        bizModel.name = name
        bizModel.age = age
        bizModel.height = height
        bizModel.isMarried = isMarried
        bizModel.birthDate = birthDate
        bizModel.loveBooks = loveBooks
        bizModel.gender = gender
        bizModel.loveSport = loveSport
        bizModel.pet = pet
        bizModel.car = car
        bizModel.extraData = extraData
        return bizModel
    }
}

二、定义数据库表对应的DBModel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181

import Foundation
import WCDBSwift

final class PeopleDBModel: TableCodable {
    var name: String? // 字符串-可以直接存DB
    var age: Int? // 整数-可以直接存DB
    var height: Double? // 小数-可以直接存DB
    var isMarried: Bool? // 枚举值-可以直接存DB
    var birthDate: Date?
    var loveBooks: [String]? // 基本类型的组合-可以直接存
    var gender: Gender? // int类型枚举-需要转换才能存
    var loveSport: SportType? // String的枚举-需要转换才能存
    var pet: Pet?// 对象类型-需要转换才能存
    var car: Car? // 对象类型-需要转换才能存
    var extraData: Data?// 字典类型[String: Any]-需要转换成Data才能存
    
    enum CodingKeys: String, CodingTableKey {
        typealias Root = PeopleDBModel

        static let objectRelationalMapping = TableBinding(CodingKeys.self)
        
        case name
        case age
        case height
        case isMarried
        case birthDate
        case loveBooks
        case gender
        case loveSport
        case pet
        case car
        case extraData
    }
    
    static var tableName: String {
        return "peoples"
    }
}

// MARK: - 枚举类型实现ColumnCodable
extension Gender: ColumnCodable {
    static var columnType: ColumnType {
        return .integer32
    }
    init?(with value: Value) {
        self.init(rawValue: value.intValue)
    }
    func archivedValue() -> Value {
        return Value(self.rawValue)
    }
}
extension SportType: ColumnCodable {
    // ===start===由于分类与定义不在同一个文件，需要自定decoder\encoder
    init(from decoder: any Decoder) throws {
        let container = try decoder.singleValueContainer()
        let sportString = try container.decode(String.self)
        switch sportString {
        case "football":
            self = .football
        case "basketball":
            self = .basketball
        default:
            self = .other(sportString)
        }
    }
    
    func encode(to encoder: Encoder) throws {
        var container = encoder.singleValueContainer()
        switch self {
        case .football:
            try container.encode("football")
        case .basketball:
            try container.encode("basketball")
        case .other(let val):
            try container.encode(val)
        }
    }
    // ===end===由于分类与定义不在同一个文件，需要自定decoder\encoder
    
    static var columnType: ColumnType {
        return .text
    }
    init?(with value: Value) {
        self.init(rawValue: value.stringValue)
    }
    func archivedValue() -> Value {
        return Value(self.rawValue)
    }
}
// MARK: - 对象类型实现ColumnCodable
extension Pet: ColumnCodable {
    // ** 由于class或struct的定义，与extension不在同一个文件里才需要 -- start **/
    enum CodingKeys: String, CodingKey {
        case name
        case age
        case type
    }
    func encode(to encoder: any Encoder) throws {
        var container = encoder.container(keyedBy: CodingKeys.self)
        try container.encode(name, forKey: .name)
        try container.encode(age, forKey: .age)
        try container.encode(type.rawValue, forKey: .type)
    }
    
    init(from decoder: any Decoder) throws {
        
        let container = try decoder.container(keyedBy: CodingKeys.self)
        let name = try container.decode(String.self, forKey: .name)
        let age = try container.decode(Int.self, forKey: .age)
        let typeVal = try container.decode(Int.self, forKey: .type)
        guard let type = PetType(rawValue: typeVal) else {
            throw NSError(domain: "type: \(typeVal)", code: 0)
        }
        self.init(name: name, age: age, type: type)
    }
    // ** 由于class或struct的定义，与extension不在同一个文件里才需要 -- end **/
    // 数据库存储的类型
    
    // ** ColumnCodable -- start **/
    static var columnType: ColumnType {
        return .BLOB
    }
    // 从数据库里解析成Model
    init?(with value: Value) {
        let data = value.dataValue
        guard data.count > 0,
              let jsonDict = try? JSONSerialization.jsonObject(with: data, options: []) as? [String: Any] else {
            return nil
        }
        guard let name = jsonDict["name"] as? String else {
            return nil
        }
        guard let age = jsonDict["age"] as? Int else {
            return nil
        }
        guard let typeVal = jsonDict["type"] as? Int,
           let type = Pet.PetType(rawValue: typeVal) else {
            return nil
        }
        self.init(name: name, age: age, type: type)
        // ** ColumnCodable -- end **/
    }

    // Model存入数据库时调用
    func archivedValue() -> Value {
        var jsonDict: [String: Any] = [:]
        jsonDict["name"] = name
        jsonDict["age"] = age
        jsonDict["type"] = type.rawValue
        guard let data = try? JSONSerialization.data(withJSONObject: (jsonDict as NSDictionary), options: []) else {
            return Value(nil)
        }
        return Value(data)
    }
}
// MARK: - 对象类型实现ColumnJSONCodable
extension Car: ColumnJSONCodable {
    enum CodingKeys: String, CodingKey {
        case name
        case price
        case isNew
    }
    func encode(to encoder: any Encoder) throws {
        var container = encoder.container(keyedBy: CodingKeys.self)
        try container.encode(name, forKey: .name)
        try container.encode(price, forKey: .price)
        try container.encode(isNew, forKey: .isNew)
    }
    
    init(from decoder: any Decoder) throws {
        
        let container = try decoder.container(keyedBy: CodingKeys.self)
        name = try container.decode(String.self, forKey: .name)
        price = try container.decode(Double.self, forKey: .price)
        isNew = try container.decode(Bool.self, forKey: .isNew)

    }
}

三、定义操作数据库的工具类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

import Foundation
import WCDBSwift

class PeopleDBWorker {
    let dbPath = "\(NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0])/test.db"
    var database: Database? = nil
    
    // MARK: 初始化数据库和表
    init() {
        // 初始化数据库对象
        database = Database(at: dbPath)
        do {
            // 要初始化建表（没有创建过该表就建表，已经建过表了有字段变更就做响应处理）
            try database?.create(table: PeopleDBModel.tableName, of: PeopleDBModel.self)
        } catch {
            print(error)
        }
    }
    
    // MARK: 插入数据
    func addPeoples(list: [PeopleModel]) -> Bool {
        do {
            try database?.insert(list.map{$0.dbModel}, on: PeopleDBModel.Properties.all, intoTable: PeopleDBModel.tableName)
        } catch {
            print(error)
            return false
        }
        return true
    }
    
    // MARK: 查询数据
    func selectPeoples() -> [PeopleModel]? {
        do {
            let list: [PeopleDBModel]? = try database?.getObjects(on: PeopleDBModel.Properties.all, fromTable: PeopleDBModel.tableName)
            return list?.map{ $0.bizModel }
        } catch {
            print(error)
            return nil
        }
    }

}

四、调用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

class ViewController: UIViewController {

    override func viewDidLoad() {
        super.viewDidLoad()
        view.backgroundColor = .white
        testPeople()

    }
    func testPeople() {
        let worker = PeopleDBWorker()
        // 设置数据
        let datas = [
            ["name": "李白",
             "age": 1000,
             "height": 184.0,
             "isMarried": false,
             "birthDate": Date(),
             "loveBooks": ["史记","论语"],
             "gender": 0,
             "loveSport": "basketball",
             "pet": ["name": "xiaowang","age": 5, "type": 1],
             "extra": ["fater":"who","pic":"http://www.baidu.com"]
            ]
        ]
        let models = datas.map {
            let model = PeopleModel()
            model.name = $0["name"] as? String
            model.age = $0["age"] as? Int
            model.height = $0["height"] as? Double
            model.isMarried = $0["isMarried"] as? Bool
            model.birthDate = $0["birthDate"] as? Date
            model.loveBooks = $0["loveBooks"] as? [String]
            if let val = $0["gender"] as? Int {
                model.gender = Gender(rawValue:  val)
            }
            if let val = $0["loveSport"] as? String {
                model.loveSport = SportType(rawValue:  val)
            }
            if let val = $0["pet"] as? [String: Any] {
                if let name = val["name"] as? String,
                   let age = val["age"] as? Int,
                   let type = val["type"] as? Int,
                   let petType = Pet.PetType(rawValue: type) {
                    model.pet = Pet(name: name, age: age, type: petType)
                }
            }
            model.extra = $0["extra"] as? [String: Any]
            return model
        }
        let result = worker.addPeoples(list: models)
        let resultList = worker.selectPeoples()
        print(result, resultList)
    }
}
db, wcdb
db wcdb
This post is licensed under CC BY 4.0 by the author.