Skip to content

Instantly share code, notes, and snippets.

@mchirico
Created December 5, 2020 16:43
Show Gist options
  • Save mchirico/e6841490ea1354bcd6be73a903d313f9 to your computer and use it in GitHub Desktop.
Save mchirico/e6841490ea1354bcd6be73a903d313f9 to your computer and use it in GitHub Desktop.
//
// SqliteBroker.swift
// tv
//
// Created by Michael Chirico on 9/29/18.
// Copyright © 2018 Michael Chirico. All rights reserved.
//
/*
References:
https://www.raywenderlich.com/385-sqlite-with-swift-tutorial-getting-started
*/
import Foundation
import UIKit
import SQLite3
import HealthKit
public class SQLiteTest {
var tableNames: [String]=[]
func checkFile(file: String) -> Bool {
var db: OpaquePointer?
let documents = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
let fileURL = documents.appendingPathComponent(file)
if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
print("error opening database")
return false
}
var statement: OpaquePointer?
if sqlite3_prepare_v2(db, "SELECT name FROM sqlite_master WHERE type='table'", -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error preparing select: \(errmsg)")
}
let cols = sqlite3_column_count(statement)
while sqlite3_step(statement) == SQLITE_ROW {
tableNames.removeAll()
for i in 0..<cols {
let queryResultColi = sqlite3_column_text(statement, i)
if queryResultColi != nil {
let result = String(cString: queryResultColi!)
tableNames.append(result)
}
}
print(tableNames)
}
if sqlite3_finalize(statement) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error finalizing prepared statement: \(errmsg)")
}
statement = nil
return true
}
}
public class SqliteBroker {
internal let SQLITE_STATIC = unsafeBitCast(0, to: sqlite3_destructor_type.self)
internal let SQLITE_TRANSIENT = unsafeBitCast(-1, to: sqlite3_destructor_type.self)
// open database
var db: OpaquePointer?
var dbP2: OpaquePointer?
var pBackup: OpaquePointer?
public init() {}
public func open(_ file: String = "test.sqlite") -> OpaquePointer {
let documents = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
let fileURL = documents.appendingPathComponent(file)
if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
print("error opening database")
}
return db!
}
public func close(_ db: OpaquePointer? = nil) {
var db = db
if db == nil {
db = self.db
if db == nil { return }
}
if sqlite3_close(db) != SQLITE_OK {
print("error closing database")
}
db = nil
}
/*
2016-10-19 06:39:53,58.0
2016-10-19 06:56:57,52.0
2016-10-19 07:03:30,51.0
2016-10-19 07:06:17,52.0
*/
public func exFileSQL(file: String, sql: String) {
var db: OpaquePointer?
let documents = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
let fileURL = documents.appendingPathComponent(file)
if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
print("error opening database")
}
if sqlite3_exec(db,
sql, nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("Error executing statement: \(errmsg)")
}
self.close(db)
}
public func heartAdd(heartRateSamples: [HKQuantitySample]) {
var db: OpaquePointer?
let documents = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
let fileURL = documents.appendingPathComponent("hr.sqlite")
if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
print("error opening database")
}
if sqlite3_exec(db,
"create table if not exists hr (d datetime, hr integer);", nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error creating table: \(errmsg)")
}
sqlite3_exec(db, "BEGIN TRANSACTION;", nil, nil, nil)
let dfmt = DateFormatter()
dfmt.dateFormat = "yyyy-MM-dd HH:mm:ss"
for r in heartRateSamples {
let result = r as HKQuantitySample
let quantity = result.quantity
let count = quantity.doubleValue(for: HKUnit(from: "count/min"))
let sd = result.startDate
let s = "insert into hr (d,hr) values('\(dfmt.string(from: sd ))',\(count));"
if sqlite3_exec(db, s, nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("sqlite3_exec: \(errmsg)")
}
}
sqlite3_exec(db, "END TRANSACTION;", nil, nil, nil)
self.close(db)
}
// MARK: TESTING
public func testAttach() {
var db: OpaquePointer?
var db2: OpaquePointer?
if sqlite3_open(":memory:", &db) != SQLITE_OK {
print("error opening database")
}
if sqlite3_exec(db,
"create table if not exists test (id integer primary key autoincrement, name text);", nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error creating table: \(errmsg)")
}
if sqlite3_exec(db,
"create table if not exists blobtest (des varchar(80),b blob);", nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error creating table: \(errmsg)")
}
if sqlite3_exec(db, "insert into test (name) values ('EXCELLENT!');", nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("sqlite3_exec: \(errmsg)")
}
if sqlite3_exec(db, "insert into test (id,name) values (30,'Add!');", nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("sqlite3_exec: \(errmsg)")
}
let documents = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
let fileURL = documents.appendingPathComponent("test.sqlite")
if sqlite3_open(fileURL.path, &db2) != SQLITE_OK {
print("error opening database")
}
if sqlite3_exec(db, "attach database '\(fileURL)' as f;", nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error attaching: \(errmsg)")
}
var msql = "insert into f.test select a.id, a.name from test a left join f.test b on a.id=b.id where b.id is NULL order by +a.rowid;"
_ = self.sql(msql, db: db)
msql = "update f.test set name = (select name from test b where b.id=f.test.id and b.name != f.test.name) where id = (select id from test b where b.id=f.test.id and b.name != f.test.name);"
_ = self.sql(msql, db: db)
msql = "insert into f.junk2 (a) values (30);"
_ = self.sql(msql, db: db)
msql = "insert into f.junk2 (a) values (33);"
_ = self.sql(msql, db: db)
// if sqlite3_exec(db2, ".database", nil, nil, nil) != SQLITE_OK {
// let errmsg = String.fromCString(sqlite3_errmsg(db))
// print(".database: \(errmsg)")
// }
let k = self.sql("PRAGMA database_list;", db: db)
for i in k {
print(i)
}
self.close(db)
}
// MARK: working with blobs
public func insertBlob(_ d: String, n: Data) {
// sqlite3_bind_blob(yourSavingSQLStatement, 2, [dataForImage bytes], [dataForImage length], SQLITE_TRANSIENT);
_ = open("test.sqlite")
var statement: OpaquePointer?
if sqlite3_prepare_v2(db, "insert into blobtest (des,b) values (?,?)", -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error preparing insert: \(errmsg)")
}
if sqlite3_bind_text(statement, 1, d, -1, SQLITE_TRANSIENT) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("failure binding foo: \(errmsg)")
}
if sqlite3_bind_blob(statement, 2, (n as NSData).bytes, Int32(n.count), SQLITE_TRANSIENT) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("failure binding foo: \(errmsg)")
}
if sqlite3_step(statement) != SQLITE_DONE {
let errmsg = String(cString: sqlite3_errmsg(db))
print("failure inserting foo: \(errmsg)")
}
if sqlite3_finalize(statement) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error finalizing prepared statement: \(errmsg)")
}
close()
print("end of insert blob")
}
public func memory() {
if sqlite3_open(":memory:", &db) != SQLITE_OK {
print("error opening database")
}
if sqlite3_exec(db, "create table if not exists test (id integer primary key autoincrement, name text)", nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error creating table: \(errmsg)")
}
if sqlite3_exec(db,
"create table if not exists blobtest (des varchar(80),b blob);", nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error creating table: \(errmsg)")
}
var statement: OpaquePointer?
if sqlite3_prepare_v2(db, "insert into test (name) values (?)", -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error preparing insert: \(errmsg)")
}
if sqlite3_bind_text(statement, 1, "This was Memory", -1, SQLITE_TRANSIENT) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("failure binding foo: \(errmsg)")
}
if sqlite3_step(statement) != SQLITE_DONE {
let errmsg = String(cString: sqlite3_errmsg(db))
print("failure inserting foo: \(errmsg)")
} else {
print("inserted")
}
if sqlite3_finalize(statement) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error finalizing prepared statement: \(errmsg)")
}
statement = nil
if sqlite3_prepare_v2(db, "select id, name from test", -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error preparing select: \(errmsg)")
}
while sqlite3_step(statement) == SQLITE_ROW {
let id = sqlite3_column_int64(statement, 0)
print("id = \(id); ", terminator: "")
let name = sqlite3_column_text(statement, 1)
if name != nil {
let nameString = String(cString: name!)
// let nameString = String(cString: UnsafePointer<Int8>(name!))
print("name = \(nameString)")
} else {
print("name not found")
}
}
if sqlite3_finalize(statement) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error finalizing prepared statement: \(errmsg)")
}
statement = nil
}
public func insertRec(_ sql: String) {
if sqlite3_exec(db, sql, nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error in insert: \(errmsg)")
}
listAll()
}
public func grabDatabase() -> Data? {
let documents = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
let fileURL = documents.appendingPathComponent("test.sqlite")
// Maybe do with a guard
do {
let data = try Data(contentsOf: fileURL, options: .mappedIfSafe)
return data
} catch _ {
print("Could not get data")
}
return nil
}
public func listAll() {
print("listAll()")
print("\n\n\n\n\n\n\n")
var statement: OpaquePointer?
if sqlite3_prepare_v2(db, "select id, name from test", -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error preparing select: \(errmsg)")
}
let cols = sqlite3_column_count(statement)
while sqlite3_step(statement) == SQLITE_ROW {
var row: [String]=[]
for i in 0..<cols {
let name = sqlite3_column_text(statement, i)
if name != nil {
let rawPointer = UnsafeRawPointer(name!)
let pointer = rawPointer.assumingMemoryBound(to: UInt8.self)
let nameString = String(pointer.pointee)
//let nameString = String(cString: UnsafePointer<Int8>(name!))
row.append(nameString)
}
}
print(row)
}
if sqlite3_finalize(statement) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error finalizing prepared statement: \(errmsg)")
}
statement = nil
}
public func sqlBlob() -> [String: UIImage] {
_ = self.open()
var statement: OpaquePointer?
if sqlite3_prepare_v2(db, "select des,b from blobtest;", -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error preparing select: \(errmsg)")
}
var result: [String: UIImage]=[:]
var rowsD: String?
var rowsB: Data?
while sqlite3_step(statement) == SQLITE_ROW {
let des = sqlite3_column_text(statement, 0)
let len = sqlite3_column_bytes(statement, 1)
let blob = sqlite3_column_blob(statement, 1)
if des != nil {
let rawPointer = UnsafeRawPointer(des!)
let pointer = rawPointer.assumingMemoryBound(to: UInt8.self)
let descString = String(pointer.pointee)
if blob != nil {
rowsD = descString
let rawPointer = UnsafeRawPointer(blob!)
let pointer = rawPointer.assumingMemoryBound(to: UInt8.self)
//rowsB = Bytes(pointer.pointee)
rowsB = Data(bytes: pointer, count: Int(len))
//rowsB = Data(bytes: UnsafePointer<UInt8>(blob), count: Int(len))
result[rowsD!] = UIImage(data: rowsB!)
}
}
}
if sqlite3_finalize(statement) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error finalizing prepared statement: \(errmsg)")
}
statement = nil
self.close()
return result
}
public func sql(_ sql: String, db: OpaquePointer? = nil) -> [[String]] {
var db = db
if db == nil { db = self.db }
var statement: OpaquePointer?
if sqlite3_prepare_v2(db, sql, -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error preparing select: \(errmsg)")
}
let cols = sqlite3_column_count(statement)
var rows: [[String]]=[[]]
while sqlite3_step(statement) == SQLITE_ROW {
var row: [String]=[]
for i in 0..<cols {
let name = sqlite3_column_text(statement, i)
if name != nil {
let rawPointer = UnsafeRawPointer(name!)
let pointer = rawPointer.assumingMemoryBound(to: UInt8.self)
let nameString = String(pointer.pointee)
//let nameString = String(cString: UnsafePointer<Int8>(name!))
row.append(nameString)
}
}
rows.append(row)
}
if sqlite3_finalize(statement) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error finalizing prepared statement: \(errmsg)")
}
statement = nil
return rows
}
public func writeDisk() {
let documents = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
let fileURL = documents.appendingPathComponent("test.sqlite")
if sqlite3_open(fileURL.path, &dbP2) != SQLITE_OK {
print("error opening database")
}
pBackup = sqlite3_backup_init(dbP2, "main", db, "main")
sqlite3_backup_step(pBackup, -1)
sqlite3_backup_finish(pBackup)
}
/*
Example Usage:
see: sqliteTests.swift
*/
public func sqlExe(file: String, stmt: String) {
let documents = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
let fileURL = documents.appendingPathComponent(file)
if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
print("error opening database")
}
if sqlite3_exec(db, stmt, nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error on sqlite3_exec: \(errmsg)")
print("stmt: \(stmt)")
}
if sqlite3_close(db) != SQLITE_OK {
print("error closing database")
}
db = nil
}
struct Result {
var id: Int
var msg: String
var row: Int
var timeStamp: String
}
func sqlQuery(file: String, stmt: String) -> [Result] {
var statement: OpaquePointer?
let documents = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
let fileURL = documents.appendingPathComponent(file)
if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
print("error opening database")
}
statement = nil
if sqlite3_prepare_v2(db, stmt, -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error preparing select: \(errmsg)")
}
var r = [Result]()
while sqlite3_step(statement) == SQLITE_ROW {
let id = sqlite3_column_int64(statement, 0)
let msg = sqlite3_column_text(statement, 1)
let row = sqlite3_column_int64(statement, 2)
print("id = \(row); ", terminator: "")
let timeStamp = sqlite3_column_text(statement, 3)
if msg != nil && timeStamp != nil {
let msgString = String(cString: msg!)
let timeStampS = String(cString: timeStamp!)
r.append(Result(id: Int(id), msg: msgString, row: Int(row), timeStamp: timeStampS))
print("msg = \(msgString)")
print("timeStamp = \(timeStampS)")
} else {
print("name not found")
}
}
if sqlite3_finalize(statement) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error finalizing prepared statement: \(errmsg)")
}
statement = nil
if sqlite3_close(db) != SQLITE_OK {
print("error closing database")
}
db = nil
return r
}
public func myStart() {
print("\n\n\nmyStart()\n\n\n\n")
let documents = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: true)
let fileURL = documents.appendingPathComponent("test.sqlite")
if sqlite3_open(fileURL.path, &db) != SQLITE_OK {
print("error opening database")
}
if sqlite3_exec(db, "create table if not exists test (id integer primary key autoincrement, name text)", nil, nil, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error creating table: \(errmsg)")
}
var statement: OpaquePointer?
if sqlite3_prepare_v2(db, "insert into test (name) values (?)", -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error preparing insert: \(errmsg)")
}
if sqlite3_bind_text(statement, 1, "This was disk", -1, SQLITE_TRANSIENT) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("failure binding foo: \(errmsg)")
}
if sqlite3_step(statement) != SQLITE_DONE {
let errmsg = String(cString: sqlite3_errmsg(db))
print("failure inserting foo: \(errmsg)")
}
if sqlite3_finalize(statement) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error finalizing prepared statement: \(errmsg)")
}
statement = nil
if sqlite3_prepare_v2(db, "select id, name from test", -1, &statement, nil) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error preparing select: \(errmsg)")
}
while sqlite3_step(statement) == SQLITE_ROW {
let id = sqlite3_column_int64(statement, 0)
print("id = \(id); ", terminator: "")
let name = sqlite3_column_text(statement, 1)
if name != nil {
let nameString = String(cString: name!)
//let nameString = String(cString: UnsafePointer<Int8>(name!))
print("name = \(nameString)")
} else {
print("name not found")
}
}
if sqlite3_finalize(statement) != SQLITE_OK {
let errmsg = String(cString: sqlite3_errmsg(db))
print("error finalizing prepared statement: \(errmsg)")
}
statement = nil
if sqlite3_close(db) != SQLITE_OK {
print("error closing database")
}
db = nil
}
public func getDatabaseFileURL(database: String) -> URL {
let documents = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
let fileURL = documents.appendingPathComponent(database)
return fileURL
}
public func getDatabaseFileURL() -> URL {
let documents = try! FileManager.default.url(for: .documentDirectory, in: .userDomainMask, appropriateFor: nil, create: false)
let fileURL = documents.appendingPathComponent("test.sqlite")
return fileURL
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment