SQLite 入门
Why SQLite
- 集成在 iOS 系统中,不会影响包体积
- 广泛使用和测试
- 开源
- 查询语言与主流数据库类似
- 跨平台
The C API
Opening a Connection
在进行进一步的操作之前,首先要建立一个数据库的连接
func openDatabase() -> OpaquePointer? {
var db: OpaquePointer? = nil
if sqlite3_open(part1DbPath, &db) == SQLITE_OK {
print("Successfully opened connection to database at \(part1DbPath)")
return db
} else {
print("Unable to open database. Verify that you created the directory described " +
"in the Getting Started section.")
PlaygroundPage.current.finishExecution()
}
}
Sqlite3_open
方法会打开或者新建一个数据库文件。如果成功返回一个 OpaquePointer
类型,数据库的操作者需要持有这个 point 来和数据库进行交互。
OpaquePoint
是一个 Swift 类型,用来表示不能直接用 Swift 类型直接表示的 C 语言指针
许多 SQLite 方法都会返回一个 Int32
类型的结果码。这些结果码几乎都有一个 SQLite 中定义好的常量表示,比如 SQLITE_OK
代表结果码 0
,结果码的列表可以在这里找到。
Creating a Table
let createTableString = """
CREATE TABLE Contact(
Id INT PRIMARY KEY NOT NULL,
Name CHAR(255));
"""
func createTable() {
// 1
var createTableStatement: OpaquePointer? = nil
// 2
if sqlite3_prepare_v2(db, createTableString, -1, &createTableStatement, nil) == SQLITE_OK {
// 3
if sqlite3_step(createTableStatement) == SQLITE_DONE {
print("Contact table created.")
} else {
print("Contact table could not be created.")
}
} else {
print("CREATE TABLE statement could not be prepared.")
}
// 4
sqlite3_finalize(createTableStatement)
}
- 创建一个后续步骤要使用的指针
- 调用
sqlite3_prepare_v2()
方法将 SQL 语句编译成字节码并返回结果码 - 在执行一个随意写的 SQL 语句之前非常重要的步骤。更多信息查看这里。检查一下返回的结果码来确保 SQL 语句被正确编译。 - 调用
sqlite3_step()
方法来执行编译好的 SQL 语句。可以调用一次或者多次 - 最后必须调用
sqlite3_finalize()
方法,参数传入接收 SQL 语句编译结果的指针来删除它,防止资源泄露,当 编译结果指针被删除后,不可再使用它
Insert
// 0
let insertStatementString = "INSERT INTO Contact (Id, Name) VALUES (?, ?);"
func insert() {
var insertStatement: OpaquePointer? = nil
// 1
if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
let id: Int32 = 1
let name: NSString = "Ray"
// 2
sqlite3_bind_int(insertStatement, 1, id)
// 3
sqlite3_bind_text(insertStatement, 2, name.utf8String, -1, nil)
// 4
if sqlite3_step(insertStatement) == SQLITE_DONE {
print("Successfully inserted row.")
} else {
print("Could not insert row.")
}
} else {
print("INSERT statement could not be prepared.")
}
// 5
sqlite3_finalize(insertStatement)
}
- 插入的 SQL 语句,问号为占位符,在调用
prepare
方法进行 compile 后,可调用bind
方法设置实际的值,compile 的结果可以多次使用,传入不同的值 - compile
- 替换问号占位符的位置为实际的值,
bind
方法的第二个参数为问号的位置,位置从 1 开始,第三个参数为实际的值 - 与第二步一样的作用,
bind_text
增加了两个参数,更多信息查看这里 - 执行语句
- 释放,如果要插入多条记录,可以持有编译后的语句重用之
Multiple Inserts
func multiInsert() {
var insertStatement: OpaquePointer? = nil
// 1
let names: [NSString] = ["Ray", "Chris", "Martha", "Danielle"]
if sqlite3_prepare_v2(db, insertStatementString, -1, &insertStatement, nil) == SQLITE_OK {
// 2
for (index, name) in names.enumerated() {
// 3
let id = Int32(index + 1)
sqlite3_bind_int(insertStatement, 1, id)
sqlite3_bind_text(insertStatement, 2, name.utf8String, -1, nil)
if sqlite3_step(insertStatement) == SQLITE_DONE {
print("Successfully inserted row.")
} else {
print("Could not insert row.")
}
// 4
sqlite3_reset(insertStatement)
}
sqlite3_finalize(insertStatement)
} else {
print("INSERT statement could not be prepared.")
}
}
在再次执行语句之前需要调用
sqlite3_reset()
方法重置状态
Query
// 0
let queryStatementString = "SELECT * FROM Contact;"
func query() {
var queryStatement: OpaquePointer? = nil
// 1
if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
// 2
if sqlite3_step(queryStatement) == SQLITE_ROW {
// 3
let id = sqlite3_column_int(queryStatement, 0)
// 4
let queryResultCol1 = sqlite3_column_text(queryStatement, 1)
let name = String(cString: queryResultCol1!)
// 5
print("Query Result:")
print("\(id) | \(name)")
} else {
print("Query returned no results")
}
} else {
print("SELECT statement could not be prepared")
}
// 6
sqlite3_finalize(queryStatement)
}
- 查询语句
- 编译语句
- 检查执行的结果是否是
SQLITE_ROW
, 代表返回一条记录 - 获取第一个字段,
sqlite3_column_int
的第二个参数为字段的索引值,从 0 开始 - 获取第二个字段的值并转换为 Swift 类型
- 打印结果
- 结束
Printing Every Row
func query() {
var queryStatement: OpaquePointer? = nil
if sqlite3_prepare_v2(db, queryStatementString, -1, &queryStatement, nil) == SQLITE_OK {
while (sqlite3_step(queryStatement) == SQLITE_ROW) {
let id = sqlite3_column_int(queryStatement, 0)
let queryResultCol1 = sqlite3_column_text(queryStatement, 1)
let name = String(cString: queryResultCol1!)
print("Query Result:")
print("\(id) | \(name)")
}
} else {
print("SELECT statement could not be prepared")
}
sqlite3_finalize(queryStatement)
}
在 while 循环中获取所有的记录,当获取最后一条记录后,sqlite3_step
会返回 SQLITE_DONE
标识结束。
Update
// 0
let updateStatementString = "UPDATE Contact SET Name = 'Chris' WHERE Id = 1;"
func update() {
var updateStatement: OpaquePointer? = nil
if sqlite3_prepare_v2(db, updateStatementString, -1, &updateStatement, nil) == SQLITE_OK {
if sqlite3_step(updateStatement) == SQLITE_DONE {
print("Successfully updated row.")
} else {
print("Could not update row.")
}
} else {
print("UPDATE statement could not be prepared")
}
sqlite3_finalize(updateStatement)
}
- 更新语句,更新一条的时候直接写死,否则应该用问号占位
- 依次是 prepare、step、finalize
Delete
let deleteStatementStirng = "DELETE FROM Contact WHERE Id = 1;"
func delete() {
var deleteStatement: OpaquePointer? = nil
if sqlite3_prepare_v2(db, deleteStatementStirng, -1, &deleteStatement, nil) == SQLITE_OK {
if sqlite3_step(deleteStatement) == SQLITE_DONE {
print("Successfully deleted row.")
} else {
print("Could not delete row.")
}
} else {
print("DELETE statement could not be prepared")
}
sqlite3_finalize(deleteStatement)
}
相同的套路,准备 SQL 语句、prepare、step、finalize
Handling Errors
合适的错误处理可以节约开发时间,返回更有意义的错误信息。
let malformedQueryString = "SELECT Stuff from Things WHERE Whatever;"
func prepareMalformedQuery() {
var malformedStatement: OpaquePointer? = nil
// 1
if sqlite3_prepare_v2(db, malformedQueryString, -1, &malformedStatement, nil) == SQLITE_OK {
print("This should not have happened.")
} else {
// 2
let errorMessage = String.init(cString: sqlite3_errmsg(db))
print("Query could not be prepared! \(errorMessage)")
}
// 3
sqlite3_finalize(malformedStatement)
}
// 输出: Query could not be prepared! no such table: Things
调用 sqlite3_errmsg(db)
方法获取最近一次错误的文本描述
Close the Database Connection
调用 sqlite3_close(db)
方法关闭连接,返回 SQLITE_OK
关闭成功。关闭需要的准备查看这里
SQLite with Swift
Wrap Errors
enum SQLiteError: Error {
case OpenDatabase(message: String)
case Prepare(message: String)
case Step(message: String)
case Bind(message: String)
}
封装错误类型,关联值记录错误信息
将数据库的操作封装为可以 throw error 的方法
Wrap the Database Connection
原始的方法中使用到了 OpaquePointer
类型,可以做 Swift 化的封装
class SQLiteDatabase {
fileprivate let dbPointer: OpaquePointer?
fileprivate init(dbPointer: OpaquePointer?) {
self.dbPointer = dbPointer
}
deinit {
sqlite3_close(dbPointer)
}
fileprivate var errorMessage: String {
if let errorPointer = sqlite3_errmsg(dbPointer) {
let errorMessage = String(cString: errorPointer)
return errorMessage
} else {
return "No error message provided from sqlite."
}
}
}
let db: SQLiteDatabase
do {
db = try SQLiteDatabase.open(path: part2DbPath)
print("Successfully opened connection to database.")
} catch SQLiteError.OpenDatabase(let message) {
print("Unable to open database: \(message)")
PlaygroundPage.current.finishExecution()
}
Wrap the Prepare Call
extension SQLiteDatabase {
func prepareStatement(sql: String) throws -> OpaquePointer? {
var statement: OpaquePointer? = nil
guard sqlite3_prepare_v2(dbPointer, sql, -1, &statement, nil) == SQLITE_OK else {
throw SQLiteError.Prepare(message: errorMessage)
}
return statement
}
}
Wrap the Table Creation
struct Contact {
let id: Int32
let name: NSString
}
protocol SQLTable {
static var createStatement: String { get }
}
extension Contact: SQLTable {
static var createStatement: String {
return """
CREATE TABLE Contact(
Id INT PRIMARY KEY NOT NULL,
Name CHAR(255)
);
"""
}
}
extension SQLiteDatabase {
func createTable(table: SQLTable.Type) throws {
// 1
let createTableStatement = try prepareStatement(sql: table.createStatement)
// 2
defer {
sqlite3_finalize(createTableStatement)
}
// 3
guard sqlite3_step(createTableStatement) == SQLITE_DONE else {
throw SQLiteError.Step(message: errorMessage)
}
print("\(table) table created.")
}
}
参数是 SQLTable.Type 而不是 SQLTable 是因为 createStatement 为 static 修饰
do {
try db.createTable(table: Contact.self)
} catch {
print(db.errorMessage)
}
Wrap Insertions
extension SQLiteDatabase {
func insertContact(contact: Contact) throws {
let insertSql = "INSERT INTO Contact (Id, Name) VALUES (?, ?);"
let insertStatement = try prepareStatement(sql: insertSql)
defer {
sqlite3_finalize(insertStatement)
}
let name: NSString = contact.name as NSString
guard sqlite3_bind_int(insertStatement, 1, contact.id) == SQLITE_OK,
sqlite3_bind_text(insertStatement, 2, name.utf8String, -1, nil) == SQLITE_OK else {
throw SQLiteError.Bind(message: errorMessage)
}
guard sqlite3_step(insertStatement) == SQLITE_DONE else {
throw SQLiteError.Step(message: errorMessage)
}
print("Successfully inserted row.")
}
}
do {
try db.insertContact(contact: Contact(id: 1, name: "Ray"))
} catch {
print(db.errorMessage)
}
Wrap Reads
extension SQLiteDatabase {
func contact(id: Int32) -> Contact? {
let querySql = "SELECT * FROM Contact WHERE Id = ?;"
guard let queryStatement = try? prepareStatement(sql: querySql) else {
return nil
}
defer {
sqlite3_finalize(queryStatement)
}
guard sqlite3_bind_int(queryStatement, 1, id) == SQLITE_OK else {
return nil
}
guard sqlite3_step(queryStatement) == SQLITE_ROW else {
return nil
}
let id = sqlite3_column_int(queryStatement, 0)
let queryResultCol1 = sqlite3_column_text(queryStatement, 1)
let name = String(cString: queryResultCol1!)
return Contact(id: id, name: name)
}
}
小结
- SQLite 的使用过程,打开数据库、写 SQL 语句、编译语句、[填充语句中的占位值]、执行、[多次使用重置语句,填充占位置,执行]、结束语句、关闭数据库
- SQLITE_ROW 用在查询结果的记录,SQLITE_DONE 用于增删改查的结束,SQLITE_OK 用在一般语句的结果判断
Reference
- 不完整翻译自 raywenderlich
- demo
- SQLite.swift
留下评论