SQLite.swift

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>
}

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 }))