1、创建路径、创建数据库
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、创建一个表
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
}
如果不存在表就创建一个表
创建表后创建字段，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、一开始我们只创建 id 、hotName、updateTime,这三个字段所以我们的创建数据库是如下
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阶段，这时候对于我们来说很简单。要么创建表的时候使用4个字段，要么就是使用升级方案。
