//
// NoteDAOSQLite.swift
// MyNotes
//
// Created by sus001 on 16/5/23.
// Copyright © 2016年 njsus. All rights reserved.
//
import Foundation
class NoteDAOSQLite {
//
let DBILE_NAME = "NotesAppList.sqlites"
// 声明C指针类型变量db
var db:COpaquePointer = nil
class var sharedNoteDAOSQLite:NoteDAOSQLite {
struct Static {
static var instance: NoteDAOSQLite?
static var once: dispatch_once_t = 0
}
dispatch_once(&Static.once){
Static.instance = NoteDAOSQLite()
Static.instance?.createEditableCopyOfDatabaseIfNeeded();
}
return Static.instance!
}
func createEditableCopyOfDatabaseIfNeeded() -> Void {
let writableDBPath = self.applicationDocumentsDirectoryFile()
// 用于将NSString类型转换为C接受的char*类型数据
let cpath = writableDBPath.cStringUsingEncoding(NSUTF8StringEncoding)
// sqlite3_open函数时打开数据库,函数的第一个参数是数据库文件的完整路径,接收的是char*类型的数据,第二个参数为sqlite3指针变量db的地址
if sqlite3_open(cpath,&db) != SQLITE_OK {
sqlite3_close(db)
assert(false, "数据库打开失败")
}else{
// CREATE TABLE IF NOT EXISTS Note() 建表语句,当表不存在时创建,否则不创建
let sql = "CREATE TABLE IF NOT EXISTS Note(cdate TEXT PRIMARY KEY,content TEXT)"
let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding)
// sqlite3_exec(db,cSql!,nil,nil,nil) 该函数的第一个参数时sqlite3指针变量地址,第二个参数时要执行的sql语句,第三个/四个参数时要回调的函数,第五个参数时执行出错的字符串
if sqlite3_exec(db,cSql!,nil,nil,nil) != SQLITE_OK {
sqlite3_close(db)
assert(false, "建表失败!")
}
// 原则上,无论正常结束还说异常介绍,必须使用sqlite_close函数释放资源
sqlite3_close(db)
}
}
func finderById(model: Note) -> Note? {
let path = self.applicationDocumentsDirectoryFile()
let cPath = path.cStringUsingEncoding(NSUTF8StringEncoding)
if sqlite3_open(cPath,&db) != SQLITE_OK {
sqlite3_close(db)
assert(false, "数据库打开失败")
}else{
let sql = "SELECT cdate,content FROM Note where cdate = ?"
let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding)
var statement: COpaquePointer = nil
// 预处理过程
// sqlite3_prepare_v2函数时sql预处理语句,预处理的目的时将sql编译成二进制代码,提高sql语句的执行速度
// 函数的第三个参数代表全部sql字符串的长度,
// 第四个参数是sqlite3_stmt指针的地址,它是语句对象,通过该语句对象可以知晓sql语句
// 第五个参数是sql语句没有执行的部分语句
if sqlite3_prepare_v2(db,cSql!,-1,&statement,nil) == SQLITE_OK {
let dateFormatter: NSDateFormatter = NSDateFormatter()
dateFormatter.dateFormat = "yyyy-MM-dd HH:mm:ss"
let strDate = dateFormatter.stringFromDate(model.date)
let cDate = strDate.cStringUsingEncoding(NSUTF8StringEncoding)
// 绑定参数
// sqlite3_bind_text函数用于绑定sql语句的参数,
// 第一个参数时statement指针,
// 第二个参数为序号(从1开始),
// 第三个参数为字符串值,
// 第四个参数为字符串长度,
// 第五个参数为一个函数指针
sqlite3_bind_text(statement,1,cDate!,-1,nil)
// 执行
// sqlite3_step函数执行sql语句,如何函数的返回值等于SQLITE_ROW,则说明还有其他的行没有遍历
if sqlite3_step(statement) == SQLITE_ROW{
// sqlite3_column_text函数读取字符串类型的字段
// 函数的第二个参数用于知道select字段的索引(从0开始)
// 在swift中 UnsafePointer<Int8> 相当于C语言的char*
let bufDate = UnsafePointer<Int8>(sqlite3_column_text(statement,0))
// 将C字符串转换为NSString类型
let strDate = String.fromCString(bufDate)
let date: NSDate = dateFormatter.dateFromString(strDate!)!
let bufContent = UnsafePointer<Int8>(sqlite3_column_text(statement,1))
let strContent = String.fromCString(bufContent)
let note = Note(date: date, content: strContent!)
sqlite3_close(db)
return note
}
}
// sqlite3_finalize函数释放语句对象statement
sqlite3_finalize(statement)
sqlite3_close(db)
}
return nil
}
// 查询所有数据
func findAll() -> NSMutableArray {
let listData = NSMutableArray()
let path = self.applicationDocumentsDirectoryFile()
let cPath = path.cStringUsingEncoding(NSUTF8StringEncoding)
if sqlite3_open(cPath,&db) != SQLITE_OK {
sqlite3_close(db)
assert(false, "数据打开失败!")
}else{
let sql = "SELECT cdate,content FROM Note"
let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding)
var statement:COpaquePointer = nil
// 预处理过程
if sqlite3_prepare_v2(db,cSql!,-1,&statement,nil) == SQLITE_OK{
let dateFormatter: NSDateFormatter = NSDateFormatter()
dateFormatter.dateFormat = "yyyy-MM-dd HH:mm:ss"
// 执行
while sqlite3_step(statement) == SQLITE_ROW {
let bufDate = UnsafePointer<Int8>(sqlite3_column_text(statement,0))
let strDate = String.fromCString(bufDate)
let date: NSDate = dateFormatter.dateFromString(strDate!)!
let bufContent = UnsafePointer<Int8>(sqlite3_column_text(statement,1))
let strContent = String.fromCString(bufContent)
let note = Note(date: date, content: strContent!)
listData.addObject(note)
}
}
sqlite3_finalize(statement)
sqlite3_close(db)
}
return listData
}
// 创建
func create(model: Note) -> Int8 {
let path = self.applicationDocumentsDirectoryFile()
let cPath = path.cStringUsingEncoding(NSUTF8StringEncoding)
if sqlite3_open(cPath,&db) != SQLITE_OK{
sqlite3_close(db)
assert(false, "数据库打开失败")
}else{
let sql = "INSERT OR REPLACE INTO note(cdate,content) VALUES(?,?)"
let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding)
var statement: COpaquePointer = nil
if sqlite3_prepare_v2(db,cSql!,-1,&statement,nil) == SQLITE_OK {
let dateFormatter: NSDateFormatter = NSDateFormatter()
dateFormatter.dateFormat = "yyyy-MM-dd HH:mm:ss"
let strDate = dateFormatter.stringFromDate(model.date)
let cDate = strDate.cStringUsingEncoding(NSUTF8StringEncoding)
let cContent = model.content?.cStringUsingEncoding(NSUTF8StringEncoding)
sqlite3_bind_text(statement,1,cDate!,-1,nil)
sqlite3_bind_text(statement,2,cContent!,-1,nil)
if sqlite3_step(statement) != SQLITE_DONE {
assert(false, "插入数据失败")
}
}
sqlite3_finalize(statement)
sqlite3_close(db)
}
return 0
}
func remove(model: Note) -> Int {
let path = self.applicationDocumentsDirectoryFile()
let cPath = path.cStringUsingEncoding(NSUTF8StringEncoding)
// 打开数据库
if sqlite3_open(cPath,&db) != SQLITE_OK{
sqlite3_close(db)
assert(false, "数据库打开失败")
}else{
// 准备sql语句
let sql = "DELETE FROM note WHere cdate = ?"
let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding)
// statement语句对象指针变量
var statement: COpaquePointer = nil
// 预处理语句
if sqlite3_prepare_v2(db,cSql!,-1,&statement,nil) == SQLITE_OK {
let dateFormatter:NSDateFormatter = NSDateFormatter()
dateFormatter.dateFormat = "yyyy-MM-dd HH:mm:ss"
// swift数据类型和C语言数据类型转换
let strDate = dateFormatter.stringFromDate(model.date)
let cDate = strDate.cStringUsingEncoding(NSUTF8StringEncoding)
// 绑定参数
sqlite3_bind_text(statement,1,cDate!,-1,nil)
// 执行删除操作
if sqlite3_step(statement) != SQLITE_DONE {
assert(false, "删除数据失败")
}
}
// 释放 语句对象资源
sqlite3_finalize(statement)
// 关闭数据库
sqlite3_close(db)
}
return 0
}
func modify(model: Note) -> Int {
let path = self.applicationDocumentsDirectoryFile()
let cPath = path.cStringUsingEncoding(NSUTF8StringEncoding)
if sqlite3_open(cPath,&db) != SQLITE_OK{
sqlite3_close(db)
assert(false, "数据库打开失败")
}else{
let sql = "UPDATE note SET content = ? WHERE cdate = ?"
let cSql = sql.cStringUsingEncoding(NSUTF8StringEncoding)
var statement:COpaquePointer = nil
if sqlite3_prepare_v2(db,cSql!,-1,&statement,nil) == SQLITE_OK{
let dateFromatter:NSDateFormatter = NSDateFormatter()
dateFromatter.dateFormat = "yyyy-MM-dd HH:mm:SS"
let strDate = dateFromatter.stringFromDate(model.date)
let cDate = strDate.cStringUsingEncoding(NSUTF8StringEncoding)
let cContent = model.content?.cStringUsingEncoding(NSUTF8StringEncoding)
sqlite3_bind_text(statement,1,cDate!,-1,nil)
sqlite3_bind_text(statement,2,cContent!,-1,nil)
if sqlite3_step(statement) != SQLITE_DONE{
assert(false, "修改数据失败")
}
}
// 释放 语句对象资源
sqlite3_finalize(statement)
// 关闭数据库
sqlite3_close(db)
}
return 0
}
func applicationDocumentsDirectoryFile() -> NSString {
let documents = NSSearchPathForDirectoriesInDomains(.DocumentDirectory,.UserDomainMask, true)
return (documents.last)!.stringByAppendingString(DBILE_NAME)
}
}