sqlite.playground.swift

import UIKit
class Statement{
    var db :OpaquePointer?
    var Statement: OpaquePointer?
    init(_ db :OpaquePointer?) {
        self.db = db
    }
    func prepare(_ sql : String) throws{
        let presult = sqlite3_prepare_v2(db, sql, -1, &Statement, nil)
        if presult != SQLITE_OK{
            let errorMessage = String(cString: sqlite3_errmsg(db))
            throw DbError.prepare(msg: errorMessage)
        }
    }
    func finalize() -> Void{
        sqlite3_finalize(Statement)
    }
    func exec()->Bool{
        return sqlite3_step(Statement) == SQLITE_DONE
    }
    func exec(_ sql : String)->Bool{
        do{
            try prepare(sql)
            print("\nSuccessfully exec.")
            return exec()
        }catch DbError.prepare(let msg){
            print(msg)
        }catch {
            print("Unexpected error: \(error).")
        }
        return false
    }
    func step()->Bool{
        return sqlite3_step(Statement) == SQLITE_ROW
    }
    deinit {
        finalize()
    }
    func bindInt(_ index : Int32,_ value : Int32){
        sqlite3_bind_int(Statement, index, value)
    }
    func bindString(_ index : Int32,_ value : NSString){
        sqlite3_bind_text(Statement, index, value.utf8String, -1, nil)
    }
    func columnInt(_ index : Int32)->Int32{
        return sqlite3_column_int(Statement, index)
    }
    func columnText(_ index : Int32)->String{
        guard let queryResultCol1 = sqlite3_column_text(Statement, index) else {
            return ""
        }
        return String(cString: queryResultCol1)
    }
}
struct Cell{
    var str : String;
    var b : Bool
}
enum DbError: Error {
    case prepare(msg: String)
}
import SQLite3
class Sql{
    var part1DbPath : String?="db2"
    var db: OpaquePointer?
    func openDatabase() -> OpaquePointer? {
        guard let part1DbPath = part1DbPath else {
            print("part1DbPath is nil.")
            return nil
        }
        if sqlite3_open(part1DbPath, &db) == SQLITE_OK {
            print("Successfully opened connection to database at \(part1DbPath)")
            return db
        } else {
            print("Unable to open database.")
            //    PlaygroundPage.current.finishExecution()
            return nil
        }
    }
    func close()->Void{
        sqlite3_close(db)
    }
    
}
class biz{
    var db: OpaquePointer?
    init(db: OpaquePointer?) {
        self.db = db
    }
    let createTableString = """
CREATE TABLE alarm(
str CHAR(255),
b int);
"""
    func createTable() {
        let sm = Statement(db)
        sm.exec(createTableString)
    }
    let insertStatementString = "INSERT INTO alarm (str, b) VALUES (?, ?);"
    
    func insert(_ p1 : NSString,_ p2 : Int32) {
        let sm = Statement(db)
        do{
            try sm.prepare(insertStatementString)
            sm.bindInt(2,p2)
            sm.bindString(1, p1)
            sm.exec()
            print("\nSuccessfully inserted row.")
        }catch DbError.prepare(let msg){
            print(msg)
        }catch {
            print("Unexpected error: \(error).")
        }
    }
    func q1() -> Void{
        let cells = query()
        for cell in cells{
            print("\(cell.str) | \(cell.b)")
        }
    }
    let queryStatementString = "SELECT * FROM alarm;"
    func query() -> [Cell] {
        var r : [Cell] = []
        let sm = Statement(db)
        do{
            try sm.prepare(queryStatementString)
            while sm.step(){
                let b = sm.columnInt(1)
                let str = sm.columnText(0)
                r.append(Cell(str: str, b: b == 1))
            }
        }catch DbError.prepare(let msg){
            print(msg)
        }catch {
            print("Unexpected error: \(error).")
        }
        return r
    }
    let updateStatementString = "UPDATE alarm SET b = ? WHERE str = ?;"
    func update(_ p1 : NSString,_ p2 : Int32) {
        let sm = Statement(db)
        do{
            try sm.prepare(updateStatementString)
            sm.bindInt(1, p2)
            sm.bindString(2, p1)
            sm.exec()
            print("\nSuccessfully updated row.")
        }catch DbError.prepare(let msg){
            print(msg)
        }catch {
            print("Unexpected error: \(error).")
        }
    }
    let deleteStatementString = "DELETE FROM alarm WHERE str = ?;"
    func delete(_ p1 : NSString) {
        let sm = Statement(db)
        do{
            try sm.prepare(deleteStatementString)
            sm.bindString(1, p1)
            sm.exec()
            print("\nSuccessfully deleted row.")
        }catch DbError.prepare(let msg){
            print(msg)
        }catch {
            print("Unexpected error: \(error).")
        }
    }
    let deleteAllString = "DELETE FROM alarm;"
    func deleteAll() {
        let sm = Statement(db)
        sm.exec(deleteAllString)
    }
    let malformedQueryString = "SELECT Stuff from Things WHERE Whatever;"
    
    func prepareMalformedQuery() {
        let sm = Statement(db)
        do{
            try sm.prepare(malformedQueryString)
            sm.exec()
            print("\nSuccessfully")
        }catch DbError.prepare(let msg){
            print(msg)
        }catch {
            print("prepareMalformedQuery error: \(error).")
        }
    }
}
var b = Sql()
let db = b.openDatabase()
var s = biz(db:db)
s.createTable()
s.insert("21:36",1)
s.insert("22:36",1)
s.q1()
s.update("21:36",0)
s.q1()

s.delete("21:36")
s.deleteAll()
s.prepareMalformedQuery()
//b.close()
//s.query()