Skip to content

Instantly share code, notes, and snippets.

@KaQuMiQ
Created April 13, 2021 16:37
Show Gist options
  • Save KaQuMiQ/4bf5fb7a218ad950d0fb35cf7f21968c to your computer and use it in GitHub Desktop.
Save KaQuMiQ/4bf5fb7a218ad950d0fb35cf7f21968c to your computer and use it in GitHub Desktop.
import Foundation
import SQLite3
private final class SQLiteHandle {
fileprivate var handle: OpaquePointer?
fileprivate init(handle: OpaquePointer?) {
self.handle = handle
}
deinit { sqlite3_close(handle) }
}
public struct SQLDatabase {
public var exec: (Statement, Array<Bindable>) -> Result<Void, Error>
public var load: (Statement, Array<Bindable>) -> Result<Array<Row>, Error>
}
public extension SQLDatabase {
enum Error: Swift.Error {
case binding(message: String?)
case statement(message: String?)
case execution(message: String?)
case encoding(message: String?)
case decoding(message: String?)
case migration(message: String?)
case connection(message: String?)
}
}
public extension SQLDatabase {
func execute(
_ statement: Statement,
arguments: Bindable...
) -> Result<Void, Error> {
exec(statement, arguments)
}
func fetch<Value>(
_ statement: Statement,
arguments: Bindable...,
mapping: (Array<Row>) -> Array<Value>
) -> Result<Array<Value>, Error> {
load(statement, arguments)
.map(mapping)
}
}
public extension SQLDatabase {
static func sqlite(
at path: String,
using migrations: Migrations
) -> Result<SQLDatabase, Error> {
var rawHandle: OpaquePointer?
if sqlite3_open_v2(path, &rawHandle, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, nil) == SQLITE_OK {
let db: SQLiteHandle = .init(handle: rawHandle)
func lastErrorMessage() -> String? {
sqlite3_errmsg(db.handle).map(String.init(cString:))
}
func performMigrations() -> Result<Void, Error> {
#warning("TODO: to complete")
//CREATE TABLE IF NOT EXISTS some_table (id INTEGER PRIMARY KEY AUTOINCREMENT, ...);
return .success(Void())
}
return performMigrations()
.map {
SQLDatabase(
exec: { (statement: Statement, arguments: Array<Bindable>) -> Result<Void, Error> in
var statementHandle: OpaquePointer?
defer { sqlite3_finalize(statementHandle) }
guard sqlite3_prepare_v2(db.handle, statement.rawString, -1, &statementHandle, nil) == SQLITE_OK
else { return .failure(.statement(message: lastErrorMessage())) }
guard sqlite3_bind_parameter_count(statementHandle) == arguments.count
else { return .failure(.binding(message: "Bindings count does not match arguments count")) }
for (idx, argument) in arguments.enumerated(){
guard argument.bind(statementHandle, at: Int32(idx + 1))
else { return .failure(.binding(message: lastErrorMessage())) }
}
let result = sqlite3_step(statementHandle)
guard result == SQLITE_ROW || result == SQLITE_DONE
else { return .failure(.execution(message: lastErrorMessage())) }
return .success(Void())
},
load: { (statement: Statement, arguments: Array<Bindable>) -> Result<Array<Row>, Error> in
var statementHandle: OpaquePointer?
guard sqlite3_prepare_v2(db.handle, statement.rawString, -1, &statementHandle, nil) == SQLITE_OK
else { return .failure(.statement(message: lastErrorMessage())) }
guard sqlite3_bind_parameter_count(statementHandle) == arguments.count
else { return .failure(.binding(message: "Bindings count does not match arguments count")) }
for (idx, argument) in arguments.enumerated(){
guard argument.bind(statementHandle, at: Int32(idx + 1))
else { return .failure(.binding(message: lastErrorMessage())) }
}
var rows: Array<Row> = []
// TODO: error handling
while sqlite3_step(statementHandle) == SQLITE_ROW {
rows.append(Row(statementHandle))
}
return .success(rows)
}
)
}
} else {
let errorMessage: String?
if rawHandle != nil {
errorMessage = sqlite3_errmsg(rawHandle).map(String.init(cString:))
sqlite3_close(rawHandle)
} else {
errorMessage = nil
}
return .failure(.connection(message: errorMessage))
}
}
}
public struct Statement {
internal let rawString: String
}
extension Statement: ExpressibleByStringLiteral {
public init(stringLiteral value: StaticString) {
self.init(rawString: value.description)
}
}
public protocol Bindable {
func bind(_ handle: OpaquePointer?, at index: Int32) -> Bool
}
extension Int32: Bindable {
public func bind(_ handle: OpaquePointer?, at index: Int32) -> Bool {
sqlite3_bind_int(handle, index, self) == SQLITE_OK
}
}
extension Int64: Bindable {
public func bind(_ handle: OpaquePointer?, at index: Int32) -> Bool {
sqlite3_bind_int64(handle, index, self) == SQLITE_OK
}
}
let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
extension String: Bindable {
public func bind(_ handle: OpaquePointer?, at index: Int32) -> Bool {
sqlite3_bind_text(handle, index, self, -1, SQLITE_TRANSIENT) == SQLITE_OK
}
}
extension Bool: Bindable {
public func bind(_ handle: OpaquePointer?, at index: Int32) -> Bool {
sqlite3_bind_int(handle, index, self ? 1 : 0) == SQLITE_OK
}
}
extension Double: Bindable {
public func bind(_ handle: OpaquePointer?, at index: Int32) -> Bool {
sqlite3_bind_double(handle, index, self) == SQLITE_OK
}
}
extension Data: Bindable {
public func bind(_ handle: OpaquePointer?, at index: Int32) -> Bool {
sqlite3_bind_blob(handle, index, [UInt8](self), Int32(self.count), SQLITE_TRANSIENT) == SQLITE_OK
}
}
public struct Row {
var values: Dictionary<String, Bindable?>
fileprivate init(_ handle: OpaquePointer?) {
func bindable(at index: Int32) -> Bindable? {
switch sqlite3_column_type(handle, index) {
case SQLITE_BLOB:
if let pointer = sqlite3_column_blob(handle, index) {
let length = Int(sqlite3_column_bytes(handle, index))
return Data(bytes: pointer, count: length)
} else {
return Data()
}
case SQLITE_FLOAT:
return sqlite3_column_double(handle, index)
case SQLITE_INTEGER:
return sqlite3_column_int64(handle, index)
case SQLITE_NULL:
return nil
case SQLITE_TEXT:
return String(cString: UnsafePointer(sqlite3_column_text(handle, index)))
case let type:
fatalError("unsupported column type: \(type)")
}
}
self.values = Dictionary.init(uniqueKeysWithValues:
(0 ..< sqlite3_column_count(handle))
.map {
(key: String(cString: sqlite3_column_name(handle, $0)), value: bindable(at: $0))
}
)
}
}
public struct Migrations {
public var migrations: Array<String>
}
@KaQuMiQ
Copy link
Author

KaQuMiQ commented Apr 13, 2021

Example:

guard case let .success(db) = SQLDatabase.sqlite(at: ":memory:", using: Migrations(migrations: []))
else { fatalError() }

print(db.execute("CREATE TABLE users (id INTEGER NOT NULL, name TEXT NOT NULL, label TEXT, age INTEGER)"))
print(db.execute("INSERT INTO users VALUES (?, ?, ?, ?)", arguments: 1, "Adam", "", 24))
print(db.execute("INSERT INTO users VALUES (?, ?, ?, ?)", arguments: 2, "Ewa", "EV", 22))
print(db.execute("INSERT INTO users VALUES (?, ?, ?, ?)", arguments: 3, "Rafał", "raf", 17))
print(db.fetch("SELECT * FROM users WHERE age > ?", arguments: 18, mapping: { rows in rows }))

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment