WCDB使用看這篇就夠了
我這兒WCDB使用的版本是2.0.4，我直接貼代碼，可以直接看demo，詳細(xì)用法和封裝都寫在demo里了崔列，我是demo，查看數(shù)據(jù)庫(kù)工具可以用這個(gè)旺遮，我是工具赵讯，提取碼: 7jx7，工具下載下來(lái)可能安裝可能顯示已損壞耿眉，設(shè)置-安全性與隱私-通用 里設(shè)置允許任何來(lái)源边翼，沒那個(gè)選項(xiàng)就命令行搞定，復(fù)制下面語(yǔ)句命令行執(zhí)行再次查看鸣剪。
sudo spctl --master-disable
WCDB增刪改查
/*
* 這兒對(duì)WCDB的操作打開了監(jiān)聽日志的组底，可留意控制臺(tái)打印信息
* 對(duì)WCDB的任何操作都依賴于模型綁定，模型首先要遵循TableCodable協(xié)議筐骇，需要存儲(chǔ)的值需要在enum CodingKeys: String, CodingTableKey{}里面case 記錄债鸡，static let objectRelationalMapping = TableBinding(CodingKeys.self) {}方法里設(shè)置你對(duì)數(shù)據(jù)庫(kù)存儲(chǔ)值的設(shè)定
* WCDB數(shù)據(jù)庫(kù)一般情況下不需要開發(fā)者手動(dòng)調(diào)用開關(guān)。操作時(shí)自動(dòng)打開铛纬，當(dāng)沒有指向 Database 所共享的 Core 時(shí)厌均，數(shù)據(jù)庫(kù)會(huì)自動(dòng)關(guān)閉，并回收內(nèi)存
* 具體更多信息自己閱讀WCDB文檔
*/
//MARK: - 增
private func addMenuItems() -> UIMenu {
let addAction1 = UIAction.init(title: "insert") { _ in
let object = Sample()
object.identifier = 1
object.description = "abc"
//純插入操作告唆，由于設(shè)置了identifier為主鍵棺弊，所以identifier必須唯一峭范，不然插入必失敗并打印錯(cuò)誤
try? DBManager.shared.db.insert(object, intoTable: "\(Sample.self)")
}
let addAction2 = UIAction.init(title: "insertOrReplace") { _ in
let object = Sample()
object.identifier = 1
object.description = "abcd"
//當(dāng)主鍵重復(fù)時(shí)即為更新薄啥，不重復(fù)時(shí)即直接插入數(shù)據(jù)
try? DBManager.shared.db.insertOrReplace(object, intoTable: "\(Sample.self)")
}
let addAction3 = UIAction.init(title: "insertOrIgnore") { _ in
let object = Sample()
object.identifier = 1
object.description = "abcdefg"
//當(dāng)出現(xiàn)主鍵重復(fù)時(shí)晰韵，直接忽略此操作蒲凶，不重復(fù)時(shí)才插入
try? DBManager.shared.db.insertOrIgnore(object, intoTable: "\(Sample.self)")
}
let addAction4 = UIAction.init(title: "帶自定義對(duì)象insert") { _ in
let object = Sample()
//由于設(shè)置了主鍵identifier為自增茄螃，這兒identifier會(huì)視數(shù)據(jù)庫(kù)中的最大值自增
object.description = "自定義對(duì)象存儲(chǔ)"
guard let data = try? JSONEncoder().encode(["variable1": "1","variable2": "2"]) else {return}
let cuModel = Customer(with: Value(data))
object.myClass = cuModel
//這兒的Customer自定義對(duì)象我們使用二進(jìn)制存儲(chǔ)缝驳，存儲(chǔ)自定義對(duì)象只需要遵循ColumnCodable協(xié)議，且實(shí)現(xiàn)init?(with Value)和archivedValue方法归苍，這兩個(gè)方法相當(dāng)于是解歸檔
try? DBManager.shared.db.insert(object, intoTable: "\(Sample.self)")
}
let addAction5 = UIAction.init(title: "Sample設(shè)置了description不為空而傳入空值") { _ in
let object = Sample()
object.identifier = 10
//Sample設(shè)置了description不可為空用狱，這樣插入會(huì)失敗且報(bào)錯(cuò)
try? DBManager.shared.db.insert(object, intoTable: "\(Sample.self)")
}
return UIMenu.init(children: [addAction1,addAction2,addAction3,addAction4,addAction5])
}
//MARK: - 刪
private func deleteMenuItems() -> UIMenu {
let deleteAction1 = UIAction.init(title: "delete") { _ in
//delete(fromTable table: String,where condition: Condition? = nil,orderBy orderList: [OrderBy]? = nil,limit: Limit? = nil,offset: Offset? = nil)，這五個(gè)組合起來(lái)可以理解為：將 table 表內(nèi)拼弃，滿足 condition 的數(shù)據(jù)夏伊，按照 orderList 的方式進(jìn)行排序，然后從頭開始第 offset 行數(shù)據(jù)后的 limit 行數(shù)據(jù)刪除吻氧，各參數(shù)代表的含義：table：表名,condition：條件溺忧，這兒可以多個(gè)條件合并，orderList：排序規(guī)則盯孙，這兒是數(shù)組鲁森，可以有多個(gè)列的排序規(guī)則，limit：無(wú)offset時(shí)理解為前 limit 行振惰，有offset時(shí)理解為后 limit 行歌溉，offset：前 offset 行
try? DBManager.shared.db.delete(fromTable: "\(Sample.self)",
where: Sample.Properties.identifier > 2 && Sample.Properties.description == "abc",
orderBy: [Sample.Properties.identifier.asOrder().order(.descending)],
limit: 2,offset: 3)
}
return UIMenu.init(children: [deleteAction1])
}
//MARK: - 改
private func changeMenuItems() -> UIMenu {
//propertyConvertibleList、condition骑晶、limit 和 offset 前面介紹過了痛垛，兩個(gè)更新不同的在with參數(shù)，on后的參數(shù)代表需要更新的列
let changeAction1 = UIAction.init(title: "基于對(duì)象的更新(update with object)") { _ in
let object = Sample()
object.description = "byObject"
try? DBManager.shared.db.update(table: "\(Sample.self)",
on: [Sample.Properties.description],
with: object,
where: Sample.Properties.identifier > 1)
}
let changeAction2 = UIAction.init(title: "基于值的更新(update with row)") { _ in
let row: [ColumnCodable] = ["byRow"]
//當(dāng)on后的參數(shù)包含myClass而未設(shè)定myClass的值時(shí)桶蛔，會(huì)置空
try? DBManager.shared.db.update(table: "\(Sample.self)",
on: [Sample.Properties.description,Sample.Properties.myClass],
with: row,
where: Sample.Properties.identifier == 10000)
}
return UIMenu.init(children: [changeAction1,changeAction2])
}
//MARK: - 查
/*
* 若是部分列查詢只獲取了 identifier 字段匙头，而沒有獲取 description 的值。這就可能與 Swift 本身存在沖突仔雷。 Swift 規(guī)定了對(duì)象創(chuàng)建時(shí)乾胶，必須初始化所有成員變量。而進(jìn)行對(duì)象部分查詢時(shí)朽寞，則可能出現(xiàn)某部分變量沒有變查詢识窿，因此無(wú)法初始化的情況。因此脑融，對(duì)于可能不被查詢的成員變量喻频，應(yīng)將其類型定義為可選值。 對(duì)于 Sample 類中肘迎，"getObjects" 接口雖然沒有獲取 description 的值甥温，但由于 description 是 String? 類型锻煌，因此不會(huì)出錯(cuò)。 而將`var description: String?` 改為 `var description: String`就會(huì)出錯(cuò),這兒使用部分查詢時(shí)模型綁定里的值一定要都設(shè)置為可選值
* 只有g(shù)etObjects和getObject為對(duì)象查詢姻蚓，其余都是值查詢
* "getRows" 接口獲取整個(gè)矩陣的所有內(nèi)容宋梧，即返回值為二維數(shù)組。
* "getRow" 接口獲取某一橫行的數(shù)據(jù)狰挡，即返回值為一維數(shù)組捂龄。
* "getColumn" 接口獲取某一縱列的數(shù)據(jù)，即返回值為一維數(shù)組加叁。
* "getDistinctColumn" 與 "getColumn" 類似倦沧，但它會(huì)過濾掉重復(fù)的值。
* "getValue" 接口獲取矩陣中某一個(gè)格的內(nèi)容它匕。
* "getDistinctValue" 與 "getValue" 類似展融，但它會(huì)過濾掉重復(fù)的值。
*/
private func checkMenuItems() -> UIMenu {
let checkAction1 = UIAction.init(title: "getObjects(全部列的查詢)") { _ in
do {
let allObjects: [Sample] = try DBManager.shared.db.getObjects(on: Sample.Properties.all, fromTable: "\(Sample.self)")
print("getObjects(全部列的查詢)",allObjects)
} catch {
}
}
let checkAction2 = UIAction.init(title: "getObject(全部列的查詢)") { _ in
do {
//"getObject" 等價(jià)于 limit: 1 時(shí)的 "getObjects" 接口
let object: Sample? = try DBManager.shared.db.getObject(on: Sample.Properties.all, fromTable: "\(Sample.self)")
if let obj = object {
print("getObject(全部列的查詢)",obj.identifier,obj.description,obj.myClass)
} else {
print("getObject(全部列的查詢)obj為空")
}
} catch {
}
}
let checkAction3 = UIAction.init(title: "getObjects(部分列的查詢)") { _ in
do {
let allObjects: [Sample] = try DBManager.shared.db.getObjects(on: [Sample.Properties.identifier,Sample.Properties.myClass], fromTable: "\(Sample.self)")
print("getObjects(部分列的查詢)",allObjects)
} catch {
}
}
let checkAction4 = UIAction.init(title: "getRows(全部列的查詢)") { _ in
do {
let allRows = try DBManager.shared.db.getRows(on: Sample.Properties.all, fromTable: "\(Sample.self)")
print("getRows(全部列的查詢)",allRows)
//row column 代表查詢到的值里面第 row 行 第 column 列
print("getRows(全部列的查詢)",allRows[row: 0, column: 0].int64Value)
} catch {
}
}
let checkAction5 = UIAction.init(title: "getRow(全部列的查詢)") { _ in
do {
//若是帶 offset 參數(shù)表示查詢第 offset+1 行
let row = try DBManager.shared.db.getRow(on: Sample.Properties.all, fromTable: "\(Sample.self)",offset: 1)
print("getRow(全部列的查詢)",row)
print("getRow(第二行第1列值的查詢)",row[1].stringValue)
} catch {
}
}
let checkAction6 = UIAction.init(title: "getColumn(部分列的查詢)") { _ in
do {
//獲取 description 列
let descriptionColumn = try DBManager.shared.db.getColumn(on: Sample.Properties.description, fromTable: "\(Sample.self)")
print("getColumn(description列的查詢)",descriptionColumn)
print("getColumn(description列第0個(gè)的查詢)",descriptionColumn[0].stringValue)
} catch {
}
}
let checkAction7 = UIAction.init(title: "getDistinctColumn(部分列的查詢)") { _ in
do {
//獲取 description 列
let distinctDescriptionColumn = try DBManager.shared.db.getDistinctColumn(on: Sample.Properties.description, fromTable: "\(Sample.self)")
print("getDistinctColumn(description列的查詢)",distinctDescriptionColumn)
print("getDistinctColumn(description列第0個(gè)的查詢)",distinctDescriptionColumn[0].stringValue)
} catch {
}
}
let checkAction8 = UIAction.init(title: "getValue(部分列的查詢)") { _ in
do {
//獲取 description 列豫柬，無(wú)offset參數(shù)代表第一行告希，有即 offset+1行
let value = try DBManager.shared.db.getValue(on: Sample.Properties.description, fromTable: "\(Sample.self)")
print("getValue(description列第一行的查詢)",value)
print("getValue(description列第一行值的查詢)",value.stringValue)
} catch {
}
}
let checkAction9 = UIAction.init(title: "getValue(identifier最大值的查詢)") { _ in
do {
//獲取 identifier最大值
let value = try DBManager.shared.db.getValue(on: Sample.Properties.identifier.max(), fromTable: "\(Sample.self)")
print("getValue(identifier最大值的查詢)",value)
print("getValue(identifier最大值的查詢)",value.int64Value)
} catch {
}
}
let checkAction10 = UIAction.init(title: "getDistinctValue(不重復(fù)的description查詢)") { _ in
do {
//獲取不重復(fù)的description的值
let value = try DBManager.shared.db.getDistinctValue(on: Sample.Properties.description, fromTable: "\(Sample.self)")
print("getDistinctValue(不重復(fù)的description查詢)",value)
print("getDistinctValue(不重復(fù)的description查詢)",value.stringValue)
} catch {
}
}
let checkAction11 = UIAction.init(title: "聯(lián)表查詢prepareMultiSelect（需要先執(zhí)行可中斷事務(wù)，在另一張sampleTable插入些數(shù)據(jù)）") { _ in
do {
//這兒聯(lián)表查詢需要查哪些列的只能單獨(dú)列出來(lái)烧给，不能用.all
let multiSelect = try DBManager.shared.db.prepareMultiSelect(on: [Sample.Properties.identifier.in(table: "\(Sample.self)"),
Sample.Properties.description.in(table: "\(Sample.self)"),
Sample.Properties.identifier.in(table: "sampleTable"),
Sample.Properties.description.in(table: "sampleTable")],
fromTables: ["\(Sample.self)","sampleTable"])
.where(Sample.Properties.identifier.in(table: "\(Sample.self)") == Sample.Properties.identifier.in(table: "sampleTable"))
while let multiObject = try multiSelect.nextMultiObject() {
let sample = multiObject["\(Sample.self)"] as? Sample
let otherSample = multiObject["sampleTable"] as? Sample
print("聯(lián)表查詢",sample,otherSample)
}
} catch {
}
}
return UIMenu.init(children: [checkAction1,checkAction2,checkAction3,checkAction4,checkAction5,checkAction6,checkAction7,checkAction8,checkAction9,checkAction10,checkAction11])
}
//MARK: - 表
/* 表相當(dāng)于指定了表名和模型綁定類的 Database暂雹，其實(shí)質(zhì)只是后者的簡(jiǎn)化版。增刪查改中提到的所有接口Table都具備创夜，而且這些接口調(diào)用時(shí)都不需要再傳表名和 ORM 類型杭跪，因?yàn)閳?zhí)行數(shù)據(jù)讀寫時(shí)Table使用起來(lái)比Database更加簡(jiǎn)潔，而且也有利于以表為單位來(lái)管理數(shù)據(jù)讀寫邏輯驰吓，所以WCDB推薦盡量使用Table來(lái)進(jìn)行數(shù)據(jù)讀寫涧尿。
*/
private func tableMenuItems() -> UIMenu {
//下面增刪改查分別寫個(gè)示例
let table = DBManager.shared.db.getTable(named: "\(Sample.self)",of: Sample.self)
let tableAction1 = UIAction.init(title: "增") { _ in
let object = Sample()
object.identifier = 55
object.description = "增"
try? table.insert(object)
}
let tableAction2 = UIAction.init(title: "刪") { _ in
try? table.delete(limit: 1)
}
let tableAction3 = UIAction.init(title: "改") { _ in
let object = Sample()
object.description = "改"
try? table.update(on: Sample.Properties.description, with: object,where: Sample.Properties.identifier > 55)
}
let tableAction4 = UIAction.init(title: "查") { _ in
do {
let objects:[Sample] = try table.getObjects(on: Sample.Properties.all)
print("表查詢",objects)
} catch {
}
}
return UIMenu.init(children: [tableAction1,tableAction2,tableAction3,tableAction4])
}
//MARK: - 事務(wù)
/*
* 事務(wù)一般用于 提升性能 和 保證數(shù)據(jù)原子性。Database 和 Table 都能直接發(fā)起事務(wù)檬贰，也可以通過 Transaction 更好地控制事務(wù)
* 事務(wù)提升性能的實(shí)質(zhì)是批量處理
* 在多線程下姑廉，刪除操作發(fā)生的時(shí)機(jī)是不確定的。倘若它發(fā)生在 插入完成之后 和 取出數(shù)據(jù)之前 的瞬間翁涤，則 getObjects() 無(wú)法取出剛才插入的數(shù)據(jù)桥言，且這種多線程低概率的 bug 是很難查的。而事務(wù)可以保證一段操作的原子性
*/
private func transactionMenuItems() -> UIMenu {
let table = DBManager.shared.db.getTable(named: "\(Sample.self)",of: Sample.self)
let transAction1 = UIAction.init(title: "多個(gè)對(duì)象單獨(dú)插入") { _ in
let object = Sample()
object.description = "多個(gè)對(duì)象單獨(dú)插入"
let objects = Array(repeating: object, count: 2000)
for object in objects {
try? table.insert(object)
}
}
let transAction2 = UIAction.init(title: "多個(gè)對(duì)象事務(wù)插入") { _ in
let object = Sample()
object.description = "多個(gè)對(duì)象事務(wù)插入"
let objects = Array(repeating: object, count: 2000)
//insert(objects:) 接口內(nèi)置了事務(wù)葵礼，并對(duì)批量數(shù)據(jù)做了針對(duì)性的優(yōu)化号阿，性能更好
try? DBManager.shared.db.run(transaction: { _ in
for object in objects {
try? table.insert(object)
}
})
}
//在多線程下，刪除操作發(fā)生的時(shí)機(jī)是不確定的鸳粉。倘若它發(fā)生在 插入完成之后 和 取出數(shù)據(jù)之前 的瞬間扔涧，則 getObjects() 無(wú)法取出剛才插入的數(shù)據(jù)，且這種多線程低概率的 bug 是很難查的。
let transAction3 = UIAction.init(title: "插入查詢不寫在事務(wù)中") { _ in
DispatchQueue(label: "other thread").async {
try? table.delete()
}
let object = Sample()
object.description = "不寫在事務(wù)中"
try? table.insert(object)
do {
let objects = try table.getObjects(on: Sample.Properties.all)
print("插入查詢不寫在事務(wù)中",objects.count) // 值不固定說明先后順序不確定
} catch {
}
}
let transAction4 = UIAction.init(title: "插入查詢都寫在事務(wù)中") { _ in
DispatchQueue(label: "other thread").async {
try? table.delete()
try? DBManager.shared.db.run(transaction: { _ in
let object = Sample()
object.description = "都寫在事務(wù)中"
try? table.insert(object)
do {
let objects = try table.getObjects(on: Sample.Properties.all)
print("插入查詢都寫在事務(wù)中",objects.count) // 輸出1
} catch {
}
})
}
}
//WCDB Swift 提供了四種事務(wù)枯夜，普通事務(wù)弯汰、可控事務(wù)、嵌入事務(wù)和可中斷事務(wù)
//上面的transAction1和transAction2皆為普通事務(wù)
let transAction5 = UIAction.init(title: "可控事務(wù)") { _ in
let object = Sample()
object.description = "可控事務(wù)"
let objects = Array(repeating: object, count: 10)
//由于事務(wù)是批量處理湖雹，所以i == 3返回 false即回滾了數(shù)據(jù)咏闪，也就一個(gè)數(shù)據(jù)都沒插入
try? DBManager.shared.db.run(controllableTransaction: { _ in
for i in 0...objects.count-1 {
let obj = objects[i]
if i == 3 {
return false
}
try? table.insert(obj)
}
return true
})
}
let transAction6 = UIAction.init(title: "嵌入事務(wù)") { _ in
let object = Sample()
object.description = "嵌入事務(wù)"
let objects = Array(repeating: object, count: 10)
try? DBManager.shared.db.run(transaction: { _ in
try? DBManager.shared.db.run(controllableTransaction: { _ in
for i in 0...objects.count-1 {
let obj = objects[i]
if i == 3 {
return false
}
try? table.insert(obj)
}
return true
})
})
}
//在需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行大量數(shù)據(jù)更新的場(chǎng)景，我們的開發(fā)習(xí)慣一般是將這些更新操作統(tǒng)一到子線程處理摔吏，這樣可以避免阻塞主線程鸽嫂，影響用戶體驗(yàn)。為了解決大事務(wù)會(huì)阻塞主線程的問題舔腾，WCDB 才加入了可中斷事務(wù)溪胶÷Р粒可中斷事務(wù)把一個(gè)流程很長(zhǎng)的事務(wù)過程看成一個(gè)循環(huán)邏輯稳诚，每次循環(huán)執(zhí)行一次短時(shí)間的DB操作。操作之后根據(jù)外部傳入的參數(shù)判斷當(dāng)前事務(wù)是否可以結(jié)束瀑踢，如果可以結(jié)束的話扳还，就直接Commit Transaction，將事務(wù)修改內(nèi)容寫入磁盤橱夭。如果事務(wù)還不可以結(jié)束氨距，再判斷主線程是否因?yàn)楫?dāng)前事務(wù)阻塞，沒有的話就回調(diào)外部邏輯棘劣，繼續(xù)執(zhí)行后面的循環(huán)俏让，直到外部邏輯處理完畢。如果檢測(cè)到主線程因?yàn)楫?dāng)前事務(wù)阻塞茬暇，則會(huì)立即 Commit Transaction首昔，先將部分修改內(nèi)容寫入磁盤，并喚醒主線程執(zhí)行DB操作糙俗。等到主線程的DB操作執(zhí)行完成之后勒奇，在重新開一個(gè)新事務(wù)，讓外部可以繼續(xù)執(zhí)行之前中斷的邏輯巧骚。
let transAction7 = UIAction.init(title: "可中斷事務(wù)") { _ in
var objects: [Sample] = []
for i in 0..<100 {
let obj = Sample()
obj.identifier = i
obj.description = "可中斷事務(wù)\(i)"
objects.append(obj)
}
DispatchQueue(label: "other thread").async {
do {
var index = 0
try DBManager.shared.db.run(pausableTransaction: { handle, stop, isNewTransaction in
// isNewTransaction表示第一次執(zhí)行赊颠，或者事務(wù)在上次循環(huán)結(jié)束之后被中斷提交了
if isNewTransaction {
//新事務(wù)先建一下表，避免事務(wù)被中斷之后劈彪，表已經(jīng)被其他邏輯刪除
try handle.create(table: "sampleTable", of: Sample.self)
}
//寫入一個(gè)對(duì)象竣蹦，這里還可以用PreparedStatement來(lái)減少SQL解析的耗時(shí)
try handle.insert(objects[index], intoTable: "sampleTable")
index += 1
//給stop賦值成true表示事務(wù)結(jié)束
stop = index >= (objects.count-3)
})
} catch {
print("Transaction failed with error: \(error)")
}
}
}
return UIMenu.init(children: [transAction1,transAction2,transAction3,transAction4,transAction5,transAction6,transAction7])
}
//MARK: - 語(yǔ)言集成查詢
/*
* 語(yǔ)言集成查詢使得開發(fā)者能夠通過 Swift 的語(yǔ)法特性去完成 SQL 語(yǔ)句。
*/
private func integratedQueryMenuItems() -> UIMenu {
let integratedQueryAction1 = UIAction.init(title: "增") { _ in
let statementInsert = StatementInsert().insert(intoTable: "\(Sample.self)").columns(Sample.Properties.identifier).values(99)
print(statementInsert.description) // 輸出 "INSERT INTO Sample(identifier) VALUES(99)"
}
let integratedQueryAction2 = UIAction.init(title: "刪") { _ in
let statementInsert = StatementDelete().delete(from: "\(Sample.self)").where(Sample.Properties.identifier > 5)
print(statementInsert.description) // 輸出 "DELETE FROM Sample WHERE id > 5"
}
let integratedQueryAction3 = UIAction.init(title: "改") { _ in
let statementInsert = StatementUpdate().update(table: "\(Sample.self)").where(Sample.Properties.identifier == 1).set(Sample.Properties.description).to("語(yǔ)言集成查詢改值")
print(statementInsert.description) // 輸出 "UPDATE Sample SET description = '語(yǔ)言集成查詢改值' WHERE id == 1"
}
let integratedQueryAction4 = UIAction.init(title: "查") { _ in
let statementInsert = StatementSelect().select(Sample.Properties.description).from("\(Sample.self)").where(Sample.Properties.identifier > 5)
print(statementInsert.description) // 輸出 "SELECT description FROM Sample WHERE id > 5"
}
return UIMenu.init(children: [integratedQueryAction1,integratedQueryAction2,integratedQueryAction3,integratedQueryAction4])
}
//MARK: - Other
/*
* 數(shù)據(jù)庫(kù)升級(jí)
*
* WCDB的數(shù)據(jù)庫(kù)升級(jí)很簡(jiǎn)單沧奴，我們知道不銷毀表的情況下草添，無(wú)法對(duì)列直接進(jìn)行刪除，所以WCDB做了這樣的處理
* 直接在模型綁定里進(jìn)行列的是否存儲(chǔ)操作扼仲，新加列可以在case 里加上远寸，當(dāng)db.create(table:of:)調(diào)用時(shí)會(huì)自動(dòng)在表里新增列抄淑，'刪除'列可在case 里刪除,當(dāng)db.create(table:of:)調(diào)用時(shí)會(huì)自動(dòng)在表里忽略此列，但是表里此列并沒有刪除驰后，且以前此列的值不會(huì)刪除
*/
/*
* 數(shù)據(jù)庫(kù)操作
*
* database.purge()即回收 database 數(shù)據(jù)庫(kù)中暫不使用的內(nèi)存
* Database.purge()即回收所有已創(chuàng)建的數(shù)據(jù)庫(kù)中暫不使用的內(nèi)存
* 在 iOS 平臺(tái)上肆资，當(dāng)內(nèi)存不足、收到系統(tǒng)警告時(shí)灶芝，WCDB Swift 會(huì)自動(dòng)調(diào)用 Database.purge() 接口以減少內(nèi)存占用
* 某些情況下郑原，開發(fā)者需要確保數(shù)據(jù)庫(kù)完全關(guān)閉后才能進(jìn)行操作，如移動(dòng)文件操作
* 可以使用以下方式保證是在WCDB數(shù)據(jù)庫(kù)關(guān)閉后做的操作
* try? database.close(onClosed: {
* try database.moveFiles(toDirectory: otherDirectory)
* })
*/
/*
* 文件與代碼模版
*
* 模型綁定的大部分都是格式固定的代碼夜涕，因此犯犁，WCDB Swift 提供了文件模版和代碼模版兩種方式，以簡(jiǎn)化模型綁定操作女器。 文件和代碼模版都在源代碼的 tools/templates 目錄下
* 這兒我們使用文件模版酸役，首先獲取 WCDB 的 Github 倉(cāng)庫(kù)，在命令行如下操作：
* cd path-to-your-wcdb-dir/tools/templates //進(jìn)入wcdb項(xiàng)目里找到tools->templats目錄驾胆，拖進(jìn)去
* sh install.sh //這兒是把文件工具導(dǎo)入Xcode創(chuàng)建新文件里
* 文件模版安裝完成后涣澡，在 Xcode 的菜單 File -> New -> File... 中創(chuàng)建新文件，通用數(shù)據(jù)模版選擇 TableCodable丧诺。 在彈出的菜單中輸入文件名入桂，并選擇 Language 為 Swift 即可。
* 自定義類型模版選擇 ColumnCodable
*/
WCDB推薦使用表操作驳阎，我有封裝抗愁，可直接使用
class DBManager: NSObject {
private let path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0].appending("/myWCDB.db")
private override init() {
db = Database(at: path)
print("WCDB數(shù)據(jù)庫(kù)地址",path)
//全局性能監(jiān)控
Database.globalTrace { tag, path, handleId, sql, cost in
print("WCDB數(shù)據(jù)庫(kù)性能指標(biāo)： tag \(tag) id \(handleId) at path \(path) takes \(cost) seconds to execute sql \(sql)");
}
//全局錯(cuò)誤監(jiān)控
Database.globalTrace(ofError: { (error: WCDBError) in
#if DEBUG
assert(error.level != .Fatal)
#endif
if error.level == .Ignore {
print("可忽略WCDB數(shù)據(jù)庫(kù)信息",error)
} else {
print("WCDB數(shù)據(jù)庫(kù)錯(cuò)誤",error)
}
})
db.setNotification { corruptedDatabase in
print("Database is corrupted: tag \(corruptedDatabase.tag ?? 0), path \(corruptedDatabase.path)")
//WCDB 檢測(cè)到損壞之后，isAlreadyCorrupted會(huì)始終返回 YES
print("WCDB數(shù)據(jù)庫(kù)有損壞",corruptedDatabase.isAlreadyCorrupted())// 輸出1
}
}
static let shared:DBManager = DBManager.init()
public var db:Database!
//MARK: - 創(chuàng)建表
public func createTable<T:TableCodable>(name:String? = nil, model:T.Type) {
try? db.create(table: name ?? "\(T.self)", of: T.self)
}
//MARK: - 增
public func insertOrReplace<T:TableCodable>(_ objects:T..., tableName:String? = nil, on propertyConvertibleList: [PropertyConvertible]? = nil) {
let table = db.getTable(named: tableName ?? "\(T.self)", of: T.self)
try? table.insertOrReplace(objects, on: propertyConvertibleList)
}
public func insertOrIgnore<T:TableCodable>(_ objects:T..., tableName:String? = nil, on propertyConvertibleList: [PropertyConvertible]? = nil) {
let table = db.getTable(named: tableName ?? "\(T.self)", of: T.self)
try? table.insertOrIgnore(objects, on: propertyConvertibleList)
}
//MARK: - 刪
public func delete<T:TableCodable>(_ model:T.Type,
tableName:String? = nil,
where condition: Condition? = nil,
orderBy orderList: [OrderBy]? = nil,
limit: Limit? = nil,
offset: Offset? = nil) {
let table = db.getTable(named: tableName ?? "\(model.self)",of: model.self)
try? table.delete(where: condition, orderBy: orderList, limit: limit, offset: offset)
}
//MARK: - 改
public func update<T:TableCodable>(tableName:String? = nil,
on propertyConvertibleList: PropertyConvertible...,
with object: T,
where condition: Condition? = nil,
orderBy orderList: [OrderBy]? = nil,
limit: Limit? = nil,
offset: Offset? = nil) {
let table = db.getTable(named: tableName ?? "\(T.self)",of: T.self)
try? table.update(on: propertyConvertibleList, with: object, where: condition, orderBy: orderList, limit: limit, offset: offset)
}
//MARK: - 查
public func getObjects<T:TableCodable>(_ model:T.Type,
tableName:String? = nil,
where condition: Condition? = nil,
orderBy orderList: [OrderBy]? = nil,
limit: Limit? = nil,
offset: Offset? = nil) -> [T] {
let table = db.getTable(named: tableName ?? "\(model.self)",of: model.self)
do {
let objects:[T] = try table.getObjects(on: T.Properties.all, where: condition, orderBy: orderList, limit: limit, offset: offset)
return objects
} catch {
return []
}
}
public func getObject<T:TableCodable>(_ model:T.Type,
tableName:String? = nil,
where condition: Condition? = nil,
orderBy orderList: [OrderBy]? = nil,
offset: Offset? = nil) -> T? {
let table = db.getTable(named: tableName ?? "\(model.self)",of: model.self)
do {
let object:T? = try table.getObject(on: T.Properties.all, where: condition, orderBy: orderList, offset: offset)
return object
} catch {
return nil
}
}
}
WCDB通用模型
/*
* 模型綁定可參照SampleORM呵晚，模型綁定類型可為class蜘腌，struct，enum
*
* 字段映射的類型劣纲，并非所有類型的變量都支持被綁定為字段逢捺。WCDB Swift 內(nèi)建了常用類型的支持，包括：
* 32 位整型 Bool, Int, Int8, Int16, Int32, UInt, UInt8, UInt16, UInt32
* 64 位整型 Int64, UInt64, Date
* 浮點(diǎn)型 Float, Double
* 字符串類型 String, URL
* 二進(jìn)制類型 Data, Array, Dictionary, Set
* 其中 Date 以時(shí)間戳的形式存儲(chǔ), Array癞季、Dictionary劫瞳、Set 以 JSON 的形式存儲(chǔ)。
* 對(duì)于沒有內(nèi)建支持的類型绷柒，可以手動(dòng)為其添加支持志于，參照Customer
*
*/
final class Sample: TableCodable {
//模型綁定類型最好都設(shè)置為可選值，不然部分查詢?nèi)菀壮鲥e(cuò)
var identifier: Int? = nil
var description: String? = nil
var myClass: Customer? = nil
var multiUniquePart1: Int? = nil
var multiUniquePart2: Int? = nil
//這一塊管WCDB在數(shù)據(jù)庫(kù)需要存儲(chǔ)的值
enum CodingKeys: String, CodingTableKey {
typealias Root = Sample
//這樣寫是重命名數(shù)據(jù)庫(kù)中存儲(chǔ)的列名废睦，就是實(shí)際值是id不是identifier
case identifier = "id"
case description
case myClass
case multiUniquePart1
case multiUniquePart2
//這一塊是配置數(shù)據(jù)庫(kù)存儲(chǔ)值的設(shè)定
static let objectRelationalMapping = TableBinding(CodingKeys.self) {
/*
* BindColumnConstraint(
* _ codingKey: CodingTableKeyType,// 對(duì)應(yīng)字段的枚舉
* isPrimary: Bool = false, // 該字段是否為主鍵伺绽。字段約束中只能同時(shí)存在一個(gè)主鍵
* orderBy term: OrderTerm? = nil, // 當(dāng)該字段是主鍵時(shí)，存儲(chǔ)順序是升序還是降序
* isAutoIncrement: Bool = false, // 當(dāng)該字段是主鍵時(shí)，其是否支持自增奈应。只有整型數(shù)據(jù)可以定義為自增澜掩。
* onConflict conflict: Conflict? = nil, // 當(dāng)該字段是主鍵時(shí)，若產(chǎn)生沖突杖挣，應(yīng)如何處理
* isNotNull: Bool = false, // 該字段是否可以為空
* isUnique: Bool = false, // 該字段是否可以具有唯一性
* defaultTo defaultValue: ColumnDef.DefaultType? = nil // 該字段在數(shù)據(jù)庫(kù)內(nèi)使用什么默認(rèn)值)
*/
//設(shè)置列的配置肩榕，WCDB里表主鍵只能設(shè)置一個(gè)
BindColumnConstraint(identifier, isPrimary: true)
//設(shè)置索引，索引名為表名+subfix（即這兒_uniqueIndex）, isUnique：是否唯一（唯一索引就是字段的值不能有重復(fù)的兩行出現(xiàn)）
//列同時(shí)為主鍵和列的唯一索引惩妇，主鍵優(yōu)先級(jí)大于唯一索引
BindIndex(identifier, namedWith: "_uniqueIndex",isUnique: true)
//對(duì)于需要特別指明索引存儲(chǔ)順序的字段株汉，可以通過 asIndex(orderBy:) 函數(shù)指定
BindIndex(description.asIndex(orderBy: .descending), namedWith: "_descendingIndex")
//對(duì)于由多個(gè)字段組成的聯(lián)合索引，BindIndex后面可以指定多個(gè)字段
BindIndex(identifier, description.asIndex(orderBy: .descending), namedWith: "_multiIndex")
//聯(lián)合主鍵約束
// BindMultiPrimary(multiUniquePart1,multiUniquePart2.asIndex(orderBy: .descending))
//聯(lián)合唯一約束
// BindMultiUnique(multiUniquePart1,multiUniquePart2)
//檢查約束歌殃，有此約束乔妈，必須滿足里面條件的數(shù)據(jù)才可插入，否則報(bào)錯(cuò)
// BindChecks {
// multiUniquePart2 > 3
// }
//設(shè)置兩表的聯(lián)合外鍵氓皱，用于聯(lián)表查詢
//點(diǎn)擊了"可中斷事務(wù)"后會(huì)有sampleTable路召，然后可以使用以下sql語(yǔ)句查詢
//select sampleTable.id as sample_id, sampleTable.description as sample_des from sampleTable join Sample on Sample.id = sampleTable.id
// BindForeginKey(identifier, foreignKey: ForeignKey.init().references(with: "sampleTable").columns(Sample.Properties.identifier))
}
}
//當(dāng)主鍵需要自動(dòng)遞增時(shí)需設(shè)置下面兩個(gè)屬性
//用于定義是否使用自增的方式插入
var isAutoIncrement: Bool = false//這兒一定要設(shè)置為false，設(shè)置了初始值false一樣遞增匀泊，若是設(shè)置了true當(dāng)插入相同主鍵時(shí)优训，會(huì)crash
//用于獲取自增插入后的主鍵值
var lastInsertedRowID: Int64 = 0
}
自定義模型
class Customer: ColumnCodable {
var variable1: String? = nil
var variable2: String? = nil
static var columnType: ColumnType {
return .BLOB
}
required init?(with value: Value) {
let data = value.dataValue
guard data.count > 0 else {
return nil
}
guard let dictionary = try? JSONDecoder().decode([String: String].self, from: data) else {
return nil
}
variable1 = dictionary["variable1"] ?? ""
variable2 = dictionary["variable2"] ?? ""
}
func archivedValue() -> Value {
if let data = try? JSONEncoder().encode(["variable1": variable1,"variable2": variable2]) {
return Value(data)
}
return Value(nil)
}
}
