SHARE
TWEET

Untitled

a guest Apr 24th, 2019 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. //
  2. //  Database.swift
  3. //
  4. //  Created by Robert Ryan on 3/8/19.
  5. //  Copyright © 2019 Robert Ryan. All rights reserved.
  6. //
  7.  
  8. import Foundation
  9. import SQLite3
  10.  
  11. private let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
  12. private let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
  13.  
  14. // MARK: - Database
  15.  
  16. /// Thin wrapper for SQLite C interface
  17.  
  18. public class Database {
  19.     // MARK: - Properties
  20.  
  21.     /// The URL for the database
  22.     let fileURL: URL
  23.    
  24.     /// The `sqlite3_open` options
  25.     public var options: OpenOptions
  26.    
  27.     /// A `DateFormatter` for writing dates to the database
  28.     public static var dateFormatter: DateFormatter = {
  29.         let formatter = DateFormatter()
  30.         formatter.dateFormat = "yyyy-MM-dd'T'HH:mm:ss.SSSX"
  31.         formatter.timeZone = TimeZone(secondsFromGMT: 0)
  32.         formatter.locale = Locale(identifier: "en_US_POSIX")
  33.         return formatter
  34.     }()
  35.    
  36.     /// The SQLite database pointer
  37.     private var database: OpaquePointer?
  38.    
  39.     /// Array of prepared statements that have not yet been finalized
  40.     private var openStatements: [Statement] = []
  41.    
  42.     // MARK: - Initialization
  43.  
  44.     /// Database initializer
  45.     ///
  46.     /// Note: You must still `open` this database before using it.
  47.     public init(fileURL: URL, options: OpenOptions = .default) {
  48.         self.fileURL = fileURL
  49.         self.options = options
  50.     }
  51.    
  52.     /// Deinitializer that will finalize any open statements and then close the database if not already closed.
  53.     deinit {
  54.         finalizeStatements()
  55.         try? close()
  56.     }
  57. }
  58.  
  59. // MARK: - Types
  60.  
  61. public extension Database {
  62.     enum DatabaseError: Error {
  63.         case failed(ReturnCode, String)
  64.         case unknownType(Any)
  65.         case notOpened
  66.         case noStatementPrepared
  67.         case closeFailed
  68.     }
  69.    
  70.     struct OpenOptions: OptionSet {
  71.         public let rawValue: Int32
  72.        
  73.         public static let readOnly     = OpenOptions(rawValue: SQLITE_OPEN_READONLY)
  74.         public static let readWrite    = OpenOptions(rawValue: SQLITE_OPEN_READWRITE)
  75.         public static let create       = OpenOptions(rawValue: SQLITE_OPEN_CREATE)
  76.         public static let noMutex      = OpenOptions(rawValue: SQLITE_OPEN_NOMUTEX)
  77.         public static let fullMutex    = OpenOptions(rawValue: SQLITE_OPEN_FULLMUTEX)
  78.         public static let sharedCache  = OpenOptions(rawValue: SQLITE_OPEN_SHAREDCACHE)
  79.         public static let privateCache = OpenOptions(rawValue: SQLITE_OPEN_PRIVATECACHE)
  80.        
  81.         public static let `default`:  OpenOptions = [.readWrite, .create]
  82.        
  83.         public init(rawValue: Int32) {
  84.             self.rawValue = rawValue
  85.         }
  86.     }
  87.    
  88.     enum ReturnCode: Equatable {
  89.         // non error codes
  90.        
  91.         case ok
  92.         case done
  93.         case row
  94.        
  95.         // error codes
  96.        
  97.         case auth
  98.         case busy
  99.         case cantOpen
  100.         case constraint
  101.         case corrupt
  102.         case empty
  103.         case error
  104.         case fail
  105.         case format
  106.         case full
  107.         case `internal`
  108.         case interrupt
  109.         case ioerr
  110.         case locked
  111.         case mismatch
  112.         case misuse
  113.         case nolfs
  114.         case nomem
  115.         case notadb
  116.         case notfound
  117.         case notice
  118.         case perm
  119.         case `protocol`
  120.         case range
  121.         case readonly
  122.         case schema
  123.         case toobig
  124.         case warning
  125.        
  126.         case unknown(Int32)
  127.        
  128.         static func code(for code: Int32) -> ReturnCode {
  129.             switch code {
  130.             case SQLITE_OK:         return .ok
  131.             case SQLITE_DONE:       return .done
  132.             case SQLITE_ROW:        return .row
  133.                
  134.             case SQLITE_AUTH:       return .auth
  135.             case SQLITE_BUSY:       return .busy
  136.             case SQLITE_CANTOPEN:   return .cantOpen
  137.             case SQLITE_CONSTRAINT: return .constraint
  138.             case SQLITE_CORRUPT:    return .corrupt
  139.             case SQLITE_EMPTY:      return .empty
  140.             case SQLITE_ERROR:      return .error
  141.             case SQLITE_FAIL:       return .fail
  142.             case SQLITE_FORMAT:     return .format
  143.             case SQLITE_FULL:       return .full
  144.             case SQLITE_INTERNAL:   return .internal
  145.             case SQLITE_INTERRUPT:  return .interrupt
  146.             case SQLITE_IOERR:      return .ioerr
  147.             case SQLITE_LOCKED:     return .locked
  148.             case SQLITE_MISMATCH:   return .mismatch
  149.             case SQLITE_MISUSE:     return .misuse
  150.             case SQLITE_NOLFS:      return .nolfs
  151.             case SQLITE_NOMEM:      return .nomem
  152.             case SQLITE_NOTADB:     return .notadb
  153.             case SQLITE_NOTFOUND:   return .notfound
  154.             case SQLITE_NOTICE:     return .notice
  155.             case SQLITE_PERM:       return .perm
  156.             case SQLITE_PROTOCOL:   return .protocol
  157.             case SQLITE_RANGE:      return .range
  158.             case SQLITE_READONLY:   return .readonly
  159.             case SQLITE_SCHEMA:     return .schema
  160.             case SQLITE_TOOBIG:     return .toobig
  161.             case SQLITE_WARNING:    return .warning
  162.                
  163.             default:                return .unknown(code)
  164.             }
  165.         }
  166.     }
  167. }
  168.  
  169. // MARK: - Public methods
  170.  
  171. public extension Database {
  172.     /// Open database
  173.     func open() throws {
  174.         do {
  175.             try call { sqlite3_open_v2(fileURL.path, &database, options.rawValue, nil) }
  176.         } catch {
  177.             try? close()
  178.             throw error
  179.         }
  180.     }
  181.  
  182.     /// Close database
  183.     func close() throws {
  184.         if database == nil { return }
  185.         finalizeStatements()
  186.         try call {
  187.             defer { database = nil }
  188.             return sqlite3_close(database)
  189.         }
  190.     }
  191.    
  192.     /// Execute statement
  193.     ///
  194.     /// - Parameter sql: SQL to be performed.
  195.     /// - Throws: SQLite errors.
  196.     func exec(_ sql: String) throws {
  197.         guard database != nil else { throw DatabaseError.notOpened }
  198.        
  199.         try call { sqlite3_exec(database, sql, nil, nil, nil) }
  200.     }
  201.    
  202.    
  203.     /// Prepare SQL
  204.     ///
  205.     /// - Parameters:
  206.     ///   - sql:        SQL to be prepared
  207.     ///   - parameters: Any parameters to be bound to any `?` in the SQL.
  208.     /// - Returns: The prepared statement.
  209.     /// - Throws: SQLite errors.
  210.     func prepare(_ sql: String, parameters: [DatabaseBindable]? = nil) throws -> Statement {
  211.         guard database != nil else { throw DatabaseError.notOpened }
  212.  
  213.         var stmt: OpaquePointer?
  214.  
  215.         try call { sqlite3_prepare_v2(database, sql, -1, &stmt, nil) }
  216.  
  217.         let statement = Statement(database: self, statement: stmt!)
  218.  
  219.         openStatements.append(statement)
  220.        
  221.         try statement.bind(parameters)
  222.        
  223.         return statement
  224.     }
  225.    
  226.     /// The `rowid` of the last row inserted
  227.     ///
  228.     /// - Returns: The `rowid`.
  229.     func lastRowId() -> Int64 {
  230.         return sqlite3_last_insert_rowid(database)
  231.     }
  232.    
  233.     /// Returns number of rows changed by last `INSERT`, `UPDATE`, or `DELETE` statement.
  234.     ///
  235.     /// - Returns: Number of rows changed.
  236.     func changes() -> Int32 {
  237.         return sqlite3_changes(database)
  238.     }
  239.    
  240.     /// Returns number of rows changed `INSERT`, `UPDATE`, or `DELETE` statements since the database was opened.
  241.     ///
  242.     /// - Returns: Number of rows changed.
  243.     func totalChanges() -> Int32 {
  244.         return sqlite3_total_changes(database)
  245.     }
  246.    
  247.     /// Finalize a previously prepared statement
  248.     ///
  249.     /// - Parameter statement: The previously prepared statement.
  250.     /// - Throws: SQLite error.
  251.     func finalize(_ statement: Statement) throws {
  252.         guard let index = openStatements.firstIndex(where: { $0.sqlite3_stmt == statement.sqlite3_stmt }) else {
  253.             return
  254.         }
  255.        
  256.         openStatements.remove(at: index)
  257.        
  258.         try call {
  259.             defer { statement.sqlite3_stmt = nil }
  260.             return sqlite3_finalize(statement.sqlite3_stmt)
  261.         }
  262.     }
  263.    
  264.     /// The version of SQLite being used.
  265.     ///
  266.     /// - Returns: Version string.
  267.     func version() -> String? {
  268.         guard let cString = sqlite3_libversion() else { return nil }
  269.         return String(cString: cString)
  270.     }
  271. }
  272.  
  273. // MARK: Private methods
  274.  
  275. fileprivate extension Database {
  276.     /// Call block containing SQLite C function
  277.     ///
  278.     /// - Parameter block: Block that returns value from SQLite C function.
  279.     /// - Returns: Returns return value from that C function if it returned `.ok`, `.done`, or `.row`.
  280.     /// - Throws: SQLite error.
  281.     @discardableResult
  282.     func call(block: () -> (Int32)) throws -> Database.ReturnCode {
  283.         let result = Database.ReturnCode.code(for: block())
  284.         switch result {
  285.         case .ok, .done, .row:
  286.             return result
  287.            
  288.         default:
  289.             let message = String(cString: sqlite3_errmsg(database))
  290.             throw DatabaseError.failed(result, message)
  291.         }
  292.     }
  293.    
  294.     /// Finalize all open statements (those prepared but not yet finalized).
  295.     func finalizeStatements() {
  296.         for statement in openStatements {
  297.             try? finalize(statement)
  298.         }
  299.     }
  300. }
  301.  
  302. // MARK: - Statement
  303.  
  304. /// SQLite statement.
  305. public class Statement {
  306.     public fileprivate(set) var sqlite3_stmt: OpaquePointer?
  307.     private weak var database: Database?
  308.    
  309.     init(database: Database, statement: OpaquePointer) {
  310.         self.database = database
  311.         self.sqlite3_stmt = statement
  312.     }
  313.    
  314.     deinit {
  315.         try? database?.finalize(self)
  316.     }
  317. }
  318.  
  319. // MARK: Public methods
  320.  
  321. public extension Statement {
  322.     /// Bind array of parameters to `?` placeholders in SQL
  323.     ///
  324.     /// - Parameter parameters: The array of parameters.
  325.     /// - Throws: SQLite error.
  326.     func bind(_ parameters: [DatabaseBindable]?) throws {
  327.         try parameters?.enumerated().forEach { index, value in
  328.             let offset = Int32(index + 1)
  329.             try database?.call { value.bind(to: self, offset: offset) }
  330.         }
  331.     }
  332.    
  333.     @discardableResult
  334.     /// Perform the prepared statement.
  335.     ///
  336.     /// - Returns: The return code if `.done`, `.row` (or `.ok`, which it never can be).
  337.     /// - Throws: The SQLite error if return code is not one of the aforementioned values.
  338.     func step() throws -> Database.ReturnCode {
  339.         guard let database = database,
  340.             let statement = sqlite3_stmt else { throw Database.DatabaseError.notOpened }
  341.        
  342.         return try database.call { sqlite3_step(statement) }
  343.     }
  344.    
  345.     /// Reset the values bound to this prepared statement.
  346.     ///
  347.     /// Used if you want to bind new values and perform the statement again without re-preparing it.
  348.     ///
  349.     /// - Throws: SQLite error.
  350.     func reset() throws {
  351.         guard let database = database,
  352.             let statement = sqlite3_stmt else { throw Database.DatabaseError.notOpened }
  353.        
  354.         try database.call { sqlite3_reset(statement) }
  355.     }
  356. }
  357.  
  358. // MARK: - Data binding protocol
  359.  
  360. public protocol DatabaseBindable {
  361.     /// Initializer used when returning value from result set of performed SQL `SELECT` statement.
  362.     ///
  363.     /// - Parameters:
  364.     ///   - statement: The prepared and performed SQLite statement.
  365.     ///   - index: The 0-based index for the column being returned.
  366.     init?(from statement: Statement, index: Int32)
  367.    
  368.     /// When binding a value to a prepared (but not yet performed) SQL statement.
  369.     ///
  370.     /// - Parameters:
  371.     ///   - statement: The prepared SQLite statement to be performed.
  372.     ///   - offset: the 1-based index for the column being bound.
  373.     /// - Returns: The SQLite return code.
  374.     func bind(to statement: Statement, offset: Int32) -> Int32
  375. }
  376.  
  377. // MARK: Specific type conformances
  378.  
  379. extension String: DatabaseBindable {
  380.     public init?(from statement: Statement, index: Int32) {
  381.         guard let pointer = sqlite3_column_text(statement.sqlite3_stmt, index) else { return nil }
  382.         self = String(cString: pointer)
  383.     }
  384.    
  385.     public func bind(to statement: Statement, offset: Int32) -> Int32 {
  386.         return sqlite3_bind_text(statement.sqlite3_stmt, offset, cString(using: .utf8), -1, SQLITE_TRANSIENT)
  387.     }
  388. }
  389.  
  390. extension Decimal: DatabaseBindable {
  391.     public init?(from statement: Statement, index: Int32) {
  392.         guard let string = String(from: statement, index: index),
  393.             let value = Decimal(string: string, locale: Locale(identifier: "en_US_POSIX")) else { return nil }
  394.         self = value
  395.     }
  396.    
  397.     public func bind(to statement: Statement, offset: Int32) -> Int32 {
  398.         var value = self
  399.         let string = NSDecimalString(&value, Locale(identifier: "en_US_POSIX"))
  400.         return sqlite3_bind_text(statement.sqlite3_stmt, offset, string.cString(using: .utf8), -1, SQLITE_TRANSIENT)
  401.     }
  402. }
  403.  
  404. extension IntegerLiteralType: DatabaseBindable {
  405.     public init?(from statement: Statement, index: Int32) {
  406.         let value = sqlite3_column_int64(statement.sqlite3_stmt, index)
  407.         self = .init(value)
  408.     }
  409.  
  410.     public func bind(to statement: Statement, offset: Int32) -> Int32 {
  411.         return sqlite3_bind_int64(statement.sqlite3_stmt, offset, Int64(self))
  412.     }
  413. }
  414.  
  415. extension Double: DatabaseBindable {
  416.     public init?(from statement: Statement, index: Int32) {
  417.         self = sqlite3_column_double(statement.sqlite3_stmt, index)
  418.     }
  419.  
  420.     public func bind(to statement: Statement, offset: Int32) -> Int32 {
  421.         return sqlite3_bind_double(statement.sqlite3_stmt, offset, self)
  422.     }
  423. }
  424.  
  425. extension Float: DatabaseBindable {
  426.     public init?(from statement: Statement, index: Int32) {
  427.         self = Float(sqlite3_column_double(statement.sqlite3_stmt, index))
  428.     }
  429.  
  430.     public func bind(to statement: Statement, offset: Int32) -> Int32 {
  431.         return sqlite3_bind_double(statement.sqlite3_stmt, offset, Double(self))
  432.     }
  433. }
  434.  
  435. extension Data: DatabaseBindable {
  436.     public init?(from statement: Statement, index: Int32) {
  437.         let count = sqlite3_column_bytes(statement.sqlite3_stmt, index)
  438.         if count == 0 { return nil }
  439.        
  440.         guard let bytes = sqlite3_column_blob(statement.sqlite3_stmt, index) else { return nil }
  441.         self = Data(bytes: bytes, count: Int(count))
  442.     }
  443.  
  444.     public func bind(to statement: Statement, offset: Int32) -> Int32 {
  445.         return withUnsafeBytes { pointer in
  446.             let bytes = pointer.baseAddress
  447.             return sqlite3_bind_blob(statement.sqlite3_stmt, offset, bytes, Int32(count), SQLITE_TRANSIENT)
  448.         }
  449.     }
  450. }
  451.  
  452. extension Date: DatabaseBindable {
  453.     public init?(from statement: Statement, index: Int32) {
  454.         guard let pointer = sqlite3_column_text(statement.sqlite3_stmt, index) else { return nil }
  455.         let string = String(cString: pointer)
  456.         guard let date = Database.dateFormatter.date(from: string) else { return nil }
  457.  
  458.         self = date
  459.     }
  460.  
  461.     public func bind(to statement: Statement, offset: Int32) -> Int32 {
  462.         let string = Database.dateFormatter.string(from: self)
  463.         return sqlite3_bind_text(statement.sqlite3_stmt, offset, string.cString(using: .utf8), -1, SQLITE_TRANSIENT)
  464.     }
  465. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top