mirror of
https://github.com/groue/GRDB.swift
synced 2026-11-22 04:00:39 +00:00
400 lines
17 KiB
Swift
400 lines
17 KiB
Swift
import Foundation
|
|
|
|
extension Database {
|
|
|
|
// MARK: - Statements
|
|
|
|
/// Returns a new prepared statement that can be reused.
|
|
///
|
|
/// let statement = try db.makeSelectStatement(sql: "SELECT * FROM player WHERE id = ?")
|
|
/// let player1 = try Player.fetchOne(statement, arguments: [1])!
|
|
/// let player2 = try Player.fetchOne(statement, arguments: [2])!
|
|
///
|
|
/// - parameter sql: An SQL query.
|
|
/// - returns: A SelectStatement.
|
|
/// - throws: A DatabaseError whenever SQLite could not parse the sql query.
|
|
public func makeSelectStatement(sql: String) throws -> SelectStatement {
|
|
try makeSelectStatement(sql: sql, prepFlags: 0)
|
|
}
|
|
|
|
/// Returns a new prepared statement that can be reused.
|
|
///
|
|
/// let statement = try db.makeSelectStatement(sql: "SELECT COUNT(*) FROM player WHERE score > ?", prepFlags: 0)
|
|
/// let moreThanTwentyCount = try Int.fetchOne(statement, arguments: [20])!
|
|
/// let moreThanThirtyCount = try Int.fetchOne(statement, arguments: [30])!
|
|
///
|
|
/// - parameter sql: An SQL query.
|
|
/// - parameter prepFlags: Flags for sqlite3_prepare_v3 (available from
|
|
/// SQLite 3.20.0, see http://www.sqlite.org/c3ref/prepare.html)
|
|
/// - returns: A SelectStatement.
|
|
/// - throws: A DatabaseError whenever SQLite could not parse the sql query.
|
|
func makeSelectStatement(sql: String, prepFlags: Int32) throws -> SelectStatement {
|
|
try SelectStatement.prepare(self, sql: sql, prepFlags: prepFlags)
|
|
}
|
|
|
|
/// Returns a prepared statement that can be reused.
|
|
///
|
|
/// let statement = try db.cachedSelectStatement(sql: "SELECT COUNT(*) FROM player WHERE score > ?")
|
|
/// let moreThanTwentyCount = try Int.fetchOne(statement, arguments: [20])!
|
|
/// let moreThanThirtyCount = try Int.fetchOne(statement, arguments: [30])!
|
|
///
|
|
/// The returned statement may have already been used: it may or may not
|
|
/// contain values for its eventual arguments.
|
|
///
|
|
/// - parameter sql: An SQL query.
|
|
/// - returns: An UpdateStatement.
|
|
/// - throws: A DatabaseError whenever SQLite could not parse the sql query.
|
|
public func cachedSelectStatement(sql: String) throws -> SelectStatement {
|
|
try publicStatementCache.selectStatement(sql)
|
|
}
|
|
|
|
/// Returns a cached statement that does not conflict with user's cached statements.
|
|
func internalCachedSelectStatement(sql: String) throws -> SelectStatement {
|
|
try internalStatementCache.selectStatement(sql)
|
|
}
|
|
|
|
/// Returns a new prepared statement that can be reused.
|
|
///
|
|
/// let statement = try db.makeUpdateStatement(sql: "INSERT INTO player (name) VALUES (?)")
|
|
/// try statement.execute(arguments: ["Arthur"])
|
|
/// try statement.execute(arguments: ["Barbara"])
|
|
///
|
|
/// - parameter sql: An SQL query.
|
|
/// - returns: An UpdateStatement.
|
|
/// - throws: A DatabaseError whenever SQLite could not parse the sql query.
|
|
public func makeUpdateStatement(sql: String) throws -> UpdateStatement {
|
|
try makeUpdateStatement(sql: sql, prepFlags: 0)
|
|
}
|
|
|
|
/// Returns a new prepared statement that can be reused.
|
|
///
|
|
/// let statement = try db.makeUpdateStatement(sql: "INSERT INTO player (name) VALUES (?)", prepFlags: 0)
|
|
/// try statement.execute(arguments: ["Arthur"])
|
|
/// try statement.execute(arguments: ["Barbara"])
|
|
///
|
|
/// - parameter sql: An SQL query.
|
|
/// - parameter prepFlags: Flags for sqlite3_prepare_v3 (available from
|
|
/// SQLite 3.20.0, see http://www.sqlite.org/c3ref/prepare.html)
|
|
/// - returns: An UpdateStatement.
|
|
/// - throws: A DatabaseError whenever SQLite could not parse the sql query.
|
|
func makeUpdateStatement(sql: String, prepFlags: Int32) throws -> UpdateStatement {
|
|
try UpdateStatement.prepare(self, sql: sql, prepFlags: prepFlags)
|
|
}
|
|
|
|
/// Returns a prepared statement that can be reused.
|
|
///
|
|
/// let statement = try db.cachedUpdateStatement(sql: "INSERT INTO player (name) VALUES (?)")
|
|
/// try statement.execute(arguments: ["Arthur"])
|
|
/// try statement.execute(arguments: ["Barbara"])
|
|
///
|
|
/// The returned statement may have already been used: it may or may not
|
|
/// contain values for its eventual arguments.
|
|
///
|
|
/// - parameter sql: An SQL query.
|
|
/// - returns: An UpdateStatement.
|
|
/// - throws: A DatabaseError whenever SQLite could not parse the sql query.
|
|
public func cachedUpdateStatement(sql: String) throws -> UpdateStatement {
|
|
try publicStatementCache.updateStatement(sql)
|
|
}
|
|
|
|
/// Returns a cached statement that does not conflict with user's cached statements.
|
|
func internalCachedUpdateStatement(sql: String) throws -> UpdateStatement {
|
|
try internalStatementCache.updateStatement(sql)
|
|
}
|
|
|
|
/// Executes one or several SQL statements, separated by semi-colons.
|
|
///
|
|
/// try db.execute(
|
|
/// sql: "INSERT INTO player (name) VALUES (:name)",
|
|
/// arguments: ["name": "Arthur"])
|
|
///
|
|
/// try db.execute(sql: """
|
|
/// INSERT INTO player (name) VALUES (?);
|
|
/// INSERT INTO player (name) VALUES (?);
|
|
/// INSERT INTO player (name) VALUES (?);
|
|
/// """, arguments: ["Arthur", "Barbara", "O'Brien"])
|
|
///
|
|
/// This method may throw a DatabaseError.
|
|
///
|
|
/// - parameters:
|
|
/// - sql: An SQL query.
|
|
/// - arguments: Statement arguments.
|
|
/// - throws: A DatabaseError whenever an SQLite error occurs.
|
|
public func execute(sql: String, arguments: StatementArguments = StatementArguments()) throws {
|
|
try execute(literal: SQLLiteral(sql: sql, arguments: arguments))
|
|
}
|
|
|
|
/// Executes one or several SQL statements, separated by semi-colons.
|
|
///
|
|
/// try db.execute(literal: SQLLiteral(
|
|
/// sql: "INSERT INTO player (name) VALUES (:name)",
|
|
/// arguments: ["name": "Arthur"]))
|
|
///
|
|
/// try db.execute(literal: SQLLiteral(sql: """
|
|
/// INSERT INTO player (name) VALUES (?);
|
|
/// INSERT INTO player (name) VALUES (?);
|
|
/// INSERT INTO player (name) VALUES (?);
|
|
/// """, arguments: ["Arthur", "Barbara", "O'Brien"]))
|
|
///
|
|
/// With Swift 5, you can safely embed raw values in your SQL queries,
|
|
/// without any risk of syntax errors or SQL injection:
|
|
///
|
|
/// try db.execute(literal: """
|
|
/// INSERT INTO player (name) VALUES (\("Arthur"));
|
|
/// INSERT INTO player (name) VALUES (\("Barbara"));
|
|
/// INSERT INTO player (name) VALUES (\("O'Brien"));
|
|
/// """)
|
|
///
|
|
/// This method may throw a DatabaseError.
|
|
///
|
|
/// - parameter sqlLiteral: An SQLLiteral.
|
|
/// - throws: A DatabaseError whenever an SQLite error occurs.
|
|
public func execute(literal sqlLiteral: SQLLiteral) throws {
|
|
// This method is like sqlite3_exec (https://www.sqlite.org/c3ref/exec.html)
|
|
// It adds support for arguments, and the tricky part is to consume
|
|
// arguments as statements are executed.
|
|
//
|
|
// This job is performed by StatementArguments.extractBindings(forStatement:allowingRemainingValues:)
|
|
//
|
|
// And before we return, we'll check that all arguments were consumed.
|
|
|
|
let context = SQLGenerationContext(self)
|
|
let sql = try sqlLiteral.sql(context)
|
|
var arguments = context.arguments
|
|
let initialValuesCount = arguments.values.count
|
|
|
|
// Build a C string (SQLite wants that), and execute SQL statements one
|
|
// after the other.
|
|
try sql.utf8CString.withUnsafeBufferPointer { buffer in
|
|
guard let sqlStart = buffer.baseAddress else { return }
|
|
let sqlEnd = sqlStart + buffer.count // past \0
|
|
var statementStart = sqlStart
|
|
while statementStart < sqlEnd {
|
|
var statementEnd: UnsafePointer<Int8>? = nil
|
|
let nextStatement: UpdateStatement?
|
|
|
|
// Compile
|
|
do {
|
|
let authorizer = StatementCompilationAuthorizer()
|
|
nextStatement = try withAuthorizer(authorizer) {
|
|
try UpdateStatement(
|
|
database: self,
|
|
statementStart: statementStart,
|
|
statementEnd: &statementEnd,
|
|
prepFlags: 0,
|
|
authorizer: authorizer)
|
|
}
|
|
}
|
|
|
|
guard let statement = nextStatement else {
|
|
// End of SQL string
|
|
break
|
|
}
|
|
|
|
// Extract statement arguments
|
|
let bindings = try arguments.extractBindings(forStatement: statement, allowingRemainingValues: true)
|
|
// unsafe is OK because we just extracted the correct number of arguments
|
|
statement.setUncheckedArguments(StatementArguments(bindings))
|
|
|
|
// Execute
|
|
try statement.execute()
|
|
|
|
// Next
|
|
statementStart = statementEnd!
|
|
}
|
|
}
|
|
|
|
// Check that all arguments were consumed: it is a programmer error to
|
|
// provide arguments that do not match the statement.
|
|
if arguments.values.isEmpty == false {
|
|
throw DatabaseError(
|
|
resultCode: .SQLITE_MISUSE,
|
|
message: "wrong number of statement arguments: \(initialValuesCount)")
|
|
}
|
|
}
|
|
}
|
|
|
|
extension Database {
|
|
func executeUpdateStatement(_ statement: UpdateStatement) throws {
|
|
// Two things must prevent the statement from executing: aborted
|
|
// transactions, and database suspension.
|
|
try checkForAbortedTransaction(sql: statement.sql, arguments: statement.arguments)
|
|
try checkForSuspensionViolation(from: statement)
|
|
|
|
if _isRecordingSelectedRegion {
|
|
_selectedRegion.formUnion(statement.databaseRegion)
|
|
}
|
|
|
|
let authorizer = observationBroker.updateStatementWillExecute(statement)
|
|
let sqliteStatement = statement.sqliteStatement
|
|
var code: Int32 = SQLITE_OK
|
|
withAuthorizer(authorizer) {
|
|
while true {
|
|
code = sqlite3_step(sqliteStatement)
|
|
if code == SQLITE_ROW {
|
|
// Statement returns a row, but the user ignores the
|
|
// content of this row:
|
|
//
|
|
// try db.execute(sql: "SELECT ...")
|
|
//
|
|
// That's OK: maybe the selected rows perform side effects.
|
|
// For example:
|
|
//
|
|
// try db.execute(sql: "SELECT sqlcipher_export(...)")
|
|
//
|
|
// Or maybe the user doesn't know that the executed statement
|
|
// return rows (https://github.com/groue/GRDB.swift/issues/15);
|
|
//
|
|
// try db.execute(sql: "PRAGMA journal_mode=WAL")
|
|
//
|
|
// It is thus important that we consume *all* rows.
|
|
continue
|
|
} else {
|
|
break
|
|
}
|
|
}
|
|
}
|
|
|
|
// Statement has been fully executed, and authorizer has been reset.
|
|
// We can now move on further tasks.
|
|
|
|
if code == SQLITE_DONE {
|
|
try updateStatementDidExecute(statement)
|
|
} else {
|
|
assert(code != SQLITE_ROW)
|
|
try updateStatementDidFail(statement)
|
|
throw DatabaseError(
|
|
resultCode: code,
|
|
message: lastErrorMessage,
|
|
sql: statement.sql,
|
|
arguments: statement.arguments)
|
|
}
|
|
}
|
|
|
|
private func updateStatementDidExecute(_ statement: UpdateStatement) throws {
|
|
if statement.invalidatesDatabaseSchemaCache {
|
|
clearSchemaCache()
|
|
}
|
|
|
|
try observationBroker.updateStatementDidExecute(statement)
|
|
}
|
|
|
|
private func updateStatementDidFail(_ statement: UpdateStatement) throws {
|
|
// Failed statements can not be reused, because sqlite3_reset won't
|
|
// be able to restore the statement to its initial state:
|
|
// https://www.sqlite.org/c3ref/reset.html
|
|
//
|
|
// So make sure we clear this statement from the cache.
|
|
internalStatementCache.remove(statement)
|
|
publicStatementCache.remove(statement)
|
|
|
|
try observationBroker.updateStatementDidFail(statement)
|
|
}
|
|
|
|
@inline(__always)
|
|
func selectStatementWillExecute(_ statement: SelectStatement) throws {
|
|
// Two things must prevent the statement from executing: aborted
|
|
// transactions, and database suspension.
|
|
try checkForAbortedTransaction(sql: statement.sql, arguments: statement.arguments)
|
|
try checkForSuspensionViolation(from: statement)
|
|
|
|
if _isRecordingSelectedRegion {
|
|
_selectedRegion.formUnion(statement.databaseRegion)
|
|
}
|
|
}
|
|
|
|
func selectStatementDidFail(_ statement: SelectStatement) {
|
|
// Failed statements can not be reused, because sqlite3_reset won't
|
|
// be able to restore the statement to its initial state:
|
|
// https://www.sqlite.org/c3ref/reset.html
|
|
//
|
|
// So make sure we clear this statement from the cache.
|
|
internalStatementCache.remove(statement)
|
|
publicStatementCache.remove(statement)
|
|
}
|
|
}
|
|
|
|
/// A thread-unsafe statement cache
|
|
struct StatementCache {
|
|
unowned let db: Database
|
|
private var selectStatements: [String: SelectStatement] = [:]
|
|
private var updateStatements: [String: UpdateStatement] = [:]
|
|
|
|
init(database: Database) {
|
|
self.db = database
|
|
}
|
|
|
|
mutating func selectStatement(_ sql: String) throws -> SelectStatement {
|
|
if let statement = selectStatements[sql] {
|
|
return statement
|
|
}
|
|
|
|
// http://www.sqlite.org/c3ref/c_prepare_persistent.html#sqlitepreparepersistent
|
|
// > The SQLITE_PREPARE_PERSISTENT flag is a hint to the query
|
|
// > planner that the prepared statement will be retained for a long
|
|
// > time and probably reused many times.
|
|
//
|
|
// This looks like a perfect match for cached statements.
|
|
//
|
|
// However SQLITE_PREPARE_PERSISTENT was only introduced in
|
|
// SQLite 3.20.0 http://www.sqlite.org/changes.html#version_3_20
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
let statement = try db.makeSelectStatement(sql: sql, prepFlags: SQLITE_PREPARE_PERSISTENT)
|
|
#else
|
|
let statement: SelectStatement
|
|
if #available(iOS 12.0, OSX 10.14, watchOS 5.0, *) {
|
|
// SQLite 3.24.0 or more
|
|
statement = try db.makeSelectStatement(sql: sql, prepFlags: SQLITE_PREPARE_PERSISTENT)
|
|
} else {
|
|
// SQLite 3.19.3 or less
|
|
statement = try db.makeSelectStatement(sql: sql)
|
|
}
|
|
#endif
|
|
selectStatements[sql] = statement
|
|
return statement
|
|
}
|
|
|
|
mutating func updateStatement(_ sql: String) throws -> UpdateStatement {
|
|
if let statement = updateStatements[sql] {
|
|
return statement
|
|
}
|
|
|
|
// http://www.sqlite.org/c3ref/c_prepare_persistent.html#sqlitepreparepersistent
|
|
// > The SQLITE_PREPARE_PERSISTENT flag is a hint to the query
|
|
// > planner that the prepared statement will be retained for a long
|
|
// > time and probably reused many times.
|
|
//
|
|
// This looks like a perfect match for cached statements.
|
|
//
|
|
// However SQLITE_PREPARE_PERSISTENT was only introduced in
|
|
// SQLite 3.20.0 http://www.sqlite.org/changes.html#version_3_20
|
|
#if GRDBCUSTOMSQLITE || GRDBCIPHER
|
|
let statement = try db.makeUpdateStatement(sql: sql, prepFlags: SQLITE_PREPARE_PERSISTENT)
|
|
#else
|
|
let statement: UpdateStatement
|
|
if #available(iOS 12.0, OSX 10.14, watchOS 5.0, *) {
|
|
// SQLite 3.24.0 or more
|
|
statement = try db.makeUpdateStatement(sql: sql, prepFlags: SQLITE_PREPARE_PERSISTENT)
|
|
} else {
|
|
// SQLite 3.19.3 or less
|
|
statement = try db.makeUpdateStatement(sql: sql)
|
|
}
|
|
#endif
|
|
updateStatements[sql] = statement
|
|
return statement
|
|
}
|
|
|
|
mutating func clear() {
|
|
updateStatements = [:]
|
|
selectStatements = [:]
|
|
}
|
|
|
|
mutating func remove(_ statement: SelectStatement) {
|
|
selectStatements.removeFirst { $0.value === statement }
|
|
}
|
|
|
|
mutating func remove(_ statement: UpdateStatement) {
|
|
updateStatements.removeFirst { $0.value === statement }
|
|
}
|
|
}
|