Post

WCDB之多层对象嵌套实现方式及查询(七)

Posted
By 13 min read

参考：

前言

项目中遇到的一些结构化类型是对象里包含对象，甚至是多层对象，而有的子对象的值或孙对象的值还要支持查询、排序

一、所有子对象、顺对象都支持可存储

处理对象包含对象的处理，将所有对象都支持ColumnJSONCodable,枚举类型支持ColumnCodable GroupMemberDBModel->UserInfo-MemberRelation GroupMemberDBModel->UserInfo-UserOnOrOffLine GroupMemberDBModel->UserInfo-MemberGender GroupMemberDBModel->MemberType

二、查询时支持查询子对象的属性或孙对象的属性

本身是没法做到可直接查询，但是可以通过增加一个私有属性作为字段来专门用于查询、排序等作用，这样做相当于储存子节点时顺便拷贝一份数据来存储查询，如下：

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

final class GroupMemberDBModel: TableCodable {
    // 用户基本信息
    var user: UserInfo? {
        didSet {
            self.remarkName = user?.friendRelation.remarkName
            self.isOnline = user?.userOnOrOffline.isOnline
        }
    }
    //--start--这些字段的值查询、排序、分组等作用，真实相通的值存到实际的对象中，相当于重复存储了2遍值
    private var remarkName: String?
    private var isOnline: Bool?
    //--end--这些字段的值专门用于查询、排序、分组等作用，真实相通的值存到实际的对象中，相当于重复存储了2遍值
    // 群ID
    var groupID: Int64?
    // 成员类型
    var type: MemberType?
    
    
    // 表名
    static var tableName: String {
        return "groupMembers"
    }
    
    enum CodingKeys: String, CodingTableKey {
        typealias Root = GroupMemberDBModel
        
        // 默认的限制，不对任何字段做特殊限制
        static let objectRelationalMapping = TableBinding(CodingKeys.self)
        
        case user
        case groupID
        case type
        // 以下两个字段用于储存来做查找条件、排序等功能
        case remarkName
        case isOnline
    }
}

完整demo如下： 1、业务模型

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

import Foundation
// 群成员
struct GroupMemberInfo {
  // 用户基本信息
  var user: UserInfo = UserInfo()
  // 群ID
  var groupID: Int64 = 0
  // 成员类型
  var type: MemberType = .ower
  init() {}
}

// 成员类型
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
    }
  }

}

/// 用户基本信息
struct UserInfo {
  // 用户ID
  var uid: Int64 = 0
  // 昵称
  var name: String = ""
  // 头像
  var pic: String = ""
  // 性别
  var gender: MemberGender = .secrecy
  // 好友关系
  var friendRelation: MemberRelation = MemberRelation()
  // 用户上下线
  var userOnOrOffline: UserOnOrOffLine = UserOnOrOffLine()
  
  init() {}
}

// 性别
enum MemberGender {
  typealias RawValue = Int
  case secrecy // 保密
  case male // 男
  case female // 女
  case other(Int)

  init() {
    self = .secrecy
  }

  init?(rawValue: Int) {
    switch rawValue {
    case 0: self = .secrecy
    case 1: self = .male
    case 2: self = .female
    default: self = .other(rawValue)
    }
  }

  var rawValue: Int {
    switch self {
    case .secrecy: return 0
    case .male: return 1
    case .female: return 2
    case .other(let val): return val
    }
  }

}


// 好友关系信息
struct MemberRelation {
  // 是否好友
  var isFriend: Bool = false
  // 好友备注名
  var remarkName: String = String()

  init() {}
}

// 用户在线情况
struct UserOnOrOffLine {
  // 用户ID
  var uid: Int64 = 0
  // 是否在线
  var isOnline: Bool = false
  // 上下线时间
  var onlineOrOfflineTime: Int64 = 0
  // 是否显示在线状态
  var isShowOnLineStatus: Bool = false
  init() {}
}
// MARK: -
extension GroupMemberInfo {
    // 业务数据存入数据库时使用
    var dbModel: GroupMemberDBModel {
        let dbInfo = GroupMemberDBModel()
        dbInfo.user = user
        dbInfo.groupID = groupID
        dbInfo.type = type
        return dbInfo
    }
    mutating func bizModel(dbModel: GroupMemberDBModel) -> GroupMemberInfo {
        if let val = dbModel.user {
            user = val
        }
        if let val = dbModel.groupID {
            groupID = val
        }
        if let val = dbModel.type {
            type = val
        }
        return self
    }
    static func bizModel(_ dbModel: GroupMemberDBModel) -> GroupMemberInfo {
        var val = GroupMemberInfo()
        return val.bizModel(dbModel: dbModel)
    }
}
extension GroupMemberDBModel {
    // 数据库数据库解析成业务数据使用
    var bizModel: GroupMemberInfo {
        return GroupMemberInfo.bizModel(self)
    }
    
}

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
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

import Foundation
import WCDBSwift

final class GroupMemberDBModel: TableCodable {
    // 用户基本信息
    var user: UserInfo? {
        didSet {
            self.isFriend = user?.friendRelation.isFriend
            self.remarkName = user?.friendRelation.remarkName
            self.isOnline = user?.userOnOrOffline.isOnline
        }
    }
    //--start--这些字段的值查询、排序、分组等作用，真实相通的值存到实际的对象中，相当于重复存储了2遍值
    private var isFriend: Bool?
    private var remarkName: String?
    private var isOnline: Bool?
    //--end--这些字段的值专门用于查询、排序、分组等作用，真实相通的值存到实际的对象中，相当于重复存储了2遍值
    // 群ID
    var groupID: Int64?
    // 成员类型
    var type: MemberType?
    
    
    // 表名
    static var tableName: String {
        return "groupMembers"
    }
    
    enum CodingKeys: String, CodingTableKey {
        typealias Root = GroupMemberDBModel
        
        // 默认的限制，不对任何字段做特殊限制
        static let objectRelationalMapping = TableBinding(CodingKeys.self)
        
        case user
        case groupID
        case type
        case isFriend
        case remarkName
        case isOnline
    }
}

// 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 **/
}
extension MemberGender: 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 **/
}
// MARK: - 对象类型实现ColumnCodable
extension MemberRelation: ColumnJSONCodable {
    // ** 由于class或struct的定义，与extension不在同一个文件里才需要 -- start **/
    enum CodingKeys: String, CodingKey {
        case isFriend
        case remarkName
    }
    func encode(to encoder: any Encoder) throws {
        var container = encoder.container(keyedBy: CodingKeys.self)
        try container.encode(isFriend, forKey: .isFriend)
        try container.encode(remarkName, forKey: .remarkName)
    }
    
    init(from decoder: any Decoder) throws {
        let container = try decoder.container(keyedBy: CodingKeys.self)
        isFriend = try container.decode(Bool.self, forKey: .isFriend)
        remarkName = try container.decode(String.self, forKey: .remarkName)

    }
}

// MARK: - 对象类型实现ColumnCodable
extension UserOnOrOffLine: ColumnJSONCodable {
    // ** 由于class或struct的定义，与extension不在同一个文件里才需要 -- start **/
    enum CodingKeys: String, CodingKey {
        case uid
        case isOnline
        case onlineOrOfflineTime
        case isShowOnLineStatus
    }
    func encode(to encoder: any Encoder) throws {
        var container = encoder.container(keyedBy: CodingKeys.self)
        try container.encode(uid, forKey: .uid)
        try container.encode(isOnline, forKey: .isOnline)
        try container.encode(onlineOrOfflineTime, forKey: .onlineOrOfflineTime)
        try container.encode(isShowOnLineStatus, forKey: .isShowOnLineStatus)
    }
    
    init(from decoder: any Decoder) throws {
        
        let container = try decoder.container(keyedBy: CodingKeys.self)
        uid = try container.decode(Int64.self, forKey: .uid)
        isOnline = try container.decode(Bool.self, forKey: .isOnline)
        onlineOrOfflineTime = try container.decode(Int64.self, forKey: .onlineOrOfflineTime)
        isShowOnLineStatus = try container.decode(Bool.self, forKey: .isShowOnLineStatus)

    }
    // ** 由于class或struct的定义，与extension不在同一个文件里才需要 -- end **/
    // 数据库存储的类型
}
// MARK: - 对象类型实现ColumnJSONCodable
extension UserInfo: ColumnJSONCodable {
    enum CodingKeys: String, CodingKey {
        case uid
        case name
        case pic
        case gender
        case friendRelation
        case userOnOrOffline
    }
    func encode(to encoder: any Encoder) throws {
        var container = encoder.container(keyedBy: CodingKeys.self)
        try container.encode(uid, forKey: .uid)
        try container.encode(name, forKey: .name)
        try container.encode(pic, forKey: .pic)
        try container.encode(gender, forKey: .gender)
        try container.encode(friendRelation, forKey: .friendRelation)
        try container.encode(userOnOrOffline, forKey: .userOnOrOffline)
    }
    
    init(from decoder: any Decoder) throws {
        
        let container = try decoder.container(keyedBy: CodingKeys.self)
        uid = try container.decode(Int64.self, forKey: .uid)
        name = try container.decode(String.self, forKey: .name)
        pic = try container.decode(String.self, forKey: .pic)
        gender = try container.decode(MemberGender.self, forKey: .gender)
        friendRelation = try container.decode(MemberRelation.self, forKey: .friendRelation)
        userOnOrOffline = try container.decode(UserOnOrOffLine.self, forKey: .userOnOrOffline)

    }
}

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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77

import Foundation
import WCDBSwift

class MemberDBWorker {
    let dbPath = "\(NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0])/test.db"
    var database: Database? = nil
    
    // MARK: 初始化数据库和表
    init() {
        // 初始化数据库对象
        database = Database(at: dbPath)
        do {
            //监控单个数据库
            database?.trace(ofPerformance: { (tag, path, handleId, sql, info) in
                print(path)
                print("\(info.costInNanoseconds/1000000) millseconds \(info.costInNanoseconds%1000000) nanoseconds to execute sql \(sql)");
            })
            // 要初始化建表（没有创建过该表就建表，已经建过表了有字段变更就做响应处理）
            try database?.create(table: GroupMemberDBModel.tableName, of: GroupMemberDBModel.self)
        } catch {
            print(error)
        }
    }
    
    // MARK: 插入数据
    func addMembers(list: [GroupMemberInfo]) -> Bool {
        do {
            let dbList = list.map{$0.dbModel}
            try database?.insert(dbList, on: GroupMemberDBModel.Properties.all, intoTable: GroupMemberDBModel.tableName)
        } catch {
            print(error)
            return false
        }
        return true
    }
    
    // MARK: 查询数据
    func allMembers() -> [GroupMemberInfo]? {
        do {
            let list: [GroupMemberDBModel]? = try database?.getObjects(on: GroupMemberDBModel.Properties.all, fromTable: GroupMemberDBModel.tableName)
            return list?.map{ $0.bizModel }
        } catch {
            print(error)
            return nil
        }
    }
    func searchMember(remarkName: String) -> [GroupMemberInfo]? {
        do {
            let list: [GroupMemberDBModel]? = try database?.getObjects(on: GroupMemberDBModel.Properties.all, fromTable: GroupMemberDBModel.tableName, where: GroupMemberDBModel.Properties.remarkName.like("%\(remarkName)%"))
            return list?.map{ $0.bizModel }
        } catch {
            print(error)
            return nil
        }
    }
    @discardableResult
    func deleteAll() -> Bool {
        do {
            try database?.delete(fromTable: GroupMemberDBModel.tableName)
            return true
        } catch {
            print(error)
            return false
        }
    }
    func update(info: GroupMemberInfo) -> Bool {
        do {
            try database?.update(table: GroupMemberDBModel.tableName, on: GroupMemberDBModel.Properties.all, with: info.dbModel)
            return true
        } catch {
            print(error)
            return false
        }
    }

}
db, wcdb
db wcdb
This post is licensed under CC BY 4.0 by the author.