1倔叼、創(chuàng)建路徑柴钻、創(chuàng)建數據庫
static let manager = BaseQueue()
lazy var db: Connection = {
let path = BaseQueue.path()
var db = try? Connection(path)
db?.busyTimeout = 5.0
return db!
}()
private class func path() -> String{
let path = NSHomeDirectory() + "/Documents/Caches/Sqlite"
let file = FileManager.default
if file.fileExists(atPath: path) == false{
do {
try file.createDirectory(atPath: path, withIntermediateDirectories: true, attributes: nil)
debugPrint("create path success")
} catch {
debugPrint(error.localizedDescription)
}
}
return path + "DataBase.sqlite"
}
2府怯、表格字段定義
public let dataBase : Connection = BaseQueue.manager.db//數據庫單利
private let SearchTable = “SearchTable”//表名
//剛開始定義三個字段名或者說三列
private let id = Expression<Int>("primaryId")
private let hotName = Expression<String?>("hotName")
private let updateTime = Expression<TimeInterval>("time")
//后期表結構升級可以用到
private let createTime = Expression<TimeInterval>("createTime”)
3掂榔、創(chuàng)建一個表
private class func addTable() -> Table{
let tables = Table(SearchTable)
try! dataBase.run(tables.create(ifNotExists: true, block: { (table) in
//注意.autoincrement,如果是String必須選擇true,這里定義為自增id
table.column(id,primaryKey: .autoincrement)
table.column(hotName)
table.column(updateTime)
}))
return tables
}
如果不存在表就創(chuàng)建一個表
創(chuàng)建表后創(chuàng)建字段拓劝，id是主鍵
4考赛、判斷表是否有該列一罩，表結構升級需要用到
private class func columns(table :String,column :String) -> Bool{
do {
var columnDatas :[String] = []
let s = try dataBase.prepare("PRAGMA table_info(" + table + ")" )
for row in s { columnDatas.append(row[1]! as! String) }
let list = columnDatas.filter { (item) -> Bool in
return item == column
}
return list.count > 0
}
catch {
return false
}
}
這里可以用更高級的方法判斷
5、判斷表中是否有這個數據
private class func selectCount(keyword :String) ->Int{
let table = addTable()
let haveColumn = columns(table: SearchTable, column: "createTime")
if !haveColumn {//表結構升級 添加一列createTime
do {
try dataBase.run(table.addColumn(createTime,defaultValue:0))
} catch {
debugPrint(error)
}
}
let alice = table.filter(hotName == keyword)
do {
let count = try dataBase.scalar(alice.count)
debugPrint(count)
return count
} catch {
return 0
}
}
這里先判斷表中有沒有createTime這一列灯蝴，沒有則添加一列默認值0,最后才是查詢條數
6恢口、增加數據
public class func insertData(keyword :String,completion:@escaping ((_ success : Bool) -> Void)){
let count = selectCount(keyword: keyword)
if count == 0 {
let table = addTable()
let time : TimeInterval = NSDate ().timeIntervalSince1970
let insertdata = table.insert(hotName <- keyword,updateTime <- time,createTime <- time)
do {
try dataBase.run(insertdata)
completion(true)
} catch {
debugPrint(error)
completion(false)
}
}else{
updateData(keyword: keyword, completion: completion)
}
}
表中有數據則修改，沒數據則添加
7穷躁、修改數據
public class func updateData(keyword :String,completion:@escaping ((_ success : Bool) -> Void)){
let count = selectCount(keyword: keyword)
if count == 0 {
insertData( keyword: keyword, completion: completion)
}else{
let table = addTable().filter(hotName == keyword)
let time : TimeInterval = NSDate ().timeIntervalSince1970
let update = table.update(hotName <- keyword,updateTime <- time)
do {
try dataBase.run(update)
completion(true)
} catch {
debugPrint(error)
completion(false)
}
}
}
表中有數據則修改耕肩，沒數據則添加
8、刪除數據
public class func deleteData(keyword :String,completion:@escaping ((_ success : Bool) -> Void)){
let table = addTable()
do {
let alice = table.filter(hotName == keyword)
try dataBase.run(alice.delete())
completion(true)
} catch {
debugPrint(error)
completion(false)
}
}
public class func deleteData(keywords :[String],completion:@escaping ((_ success : Bool) -> Void)){
do {
try dataBase.transaction {
keywords.forEach { (title) in
let table = addTable()
do {
let alice = table.filter(hotName == title)
try dataBase.run(alice.delete())
} catch {
debugPrint(error)
}
}
}
completion(true)
} catch {
completion(false)
}
}
使用事務來做批量刪除 操作
9问潭、數據查詢
public class func searchData(page : Int,size : Int = 20,completion:@escaping ((_ datas : [String]) ->Void)){
let table = addTable()
do {
let datas = try dataBase.prepare(table.order(updateTime.desc).limit(size, offset: (page - 1) * size))
decodeData(listData: datas,completion: completion)
} catch {
debugPrint(error)
completion([])
}
}
public class func searchData(completion:@escaping ((_ datas : [String]) ->Void)){
let table = addTable()
do {
let datas = try dataBase.prepare(table)
decodeData(listData: datas,completion: completion)
} catch {
debugPrint(error)
completion([])
}
}
private class func decodeData(listData : AnySequence<Row>,completion:@escaping ((_ datas : [String]) ->Void)){
var contentData : [String] = []
listData.forEach { (objc) in
let content :String = objc[hotName] ?? ""
contentData.append(content)
}
completion(contentData)
}
10猿诸、總結
a、一開始我們只創(chuàng)建 id 狡忙、hotName梳虽、updateTime,這三個字段所以我們的創(chuàng)建數據庫是如下
private class func addTable() -> Table{
let tables = Table(SearchTable)
try! dataBase.run(tables.create(ifNotExists: true, block: { (table) in
//注意.autoincrement,如果是String必須選擇true,這里定義為自增id
table.column(id,primaryKey: .autoincrement)
table.column(hotName)
table.column(updateTime)
}))
return tables
}
b、到某個版本的時候灾茁，需要增加一個字段createTime窜觉，這時候表中插入新數據的時候，老版本表就無法兼容北专。
所以老版本的表就需要做一個升級禀挫，就是我們的第5點。
c拓颓、當然如果用戶沒有經歷過a階段语婴，這時候對于我們來說很簡單。要么創(chuàng)建表的時候使用4個字段录粱，要么就是使用升級方案。
