Simple store/retrieve of blob or data (NSKeyArchive of a class maybe)

Question

Experienced developer here... but somewhat new to Swift and definite newbie with SQLite.swift...

I am trying to get my hands around using SQLite in an iOS app - so I created a simple app to make sure I got the basics down... Everything was going great until I got to the point of storing and retrieving data objects (ie class objects)...

Here is my code:

    //**********************************************************************
    //**
    //** Create/Open database connection
    //**
    //**********************************************************************
    let dbPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!
    let db: Connection = try! Connection("\(dbPath)/stickis.sqlite3")

    let dbTable = Table("testtbl")
    let id = Expression<Int64>("id")
    let boolfld = Expression<Bool>("boolfld")
    let int64fld = Expression<Int64>("int64fld")
    let stringfld = Expression<String>("stringfld")
    let blobfld = Expression<SQLite.Blob?>("blobfld") // optional due to ?

    //**********************************************************************
    //*
    //* Drop table if it exists
    //*
    //**********************************************************************
    try! db.run(dbTable.drop(ifExists: true))

    //**********************************************************************
    //**
    //** Create/Open Table
    //**
    //**********************************************************************
    do
    {
        try db.run((dbTable.create(ifNotExists: true)
        {
            t in
            t.column(id, primaryKey: true)
            t.column(boolfld)
            t.column(int64fld)
            t.column(stringfld)
            t.column(blobfld)
            })
        )
    }
    catch
    {
        print("Failed to create sticki table")
    }

    //**********************************************************************
    //**
    //** Add Record 1
    //**
    //**********************************************************************
    do
    {
        let testblob: Blob = windowcontent()
        let rowid = try db.run(dbTable.insert(boolfld <- true, int64fld <- 1963, stringfld <- "unknown", blobfld <- testblob))
        print("inserted id: \(rowid)")
    }
    catch
    {
        print("insertion failed: \(error)")
    }

    //**********************************************************************
    //**
    //** Add Record 2
    //**
    //**********************************************************************
    do
    {
        let rowid = try db.run(dbTable.insert(boolfld <- true, int64fld <- 1972, stringfld <- "David"))
        print("inserted id: \(rowid)")
    }
    catch
    {
        print("insertion failed: \(error)")
    }

    //**********************************************************************
    //**
    //** Update Record 1
    //**
    //**********************************************************************
    let rec2updt = dbTable.filter(id == 1)
    do
    {
        if try db.run(rec2updt.update(stringfld <- "TJ")) > 0
        {
            print("updated to TJ")
        }
        else
        {
            print("record not found")
        }
    }
    catch
    {
        print("update failed")
    }

    //**********************************************************************
    //**
    //** Query Particular Record using filter
    //**
    //**********************************************************************
    let tjFilter = dbTable.filter(int64fld == 1964)
    for dataRec in try! db.prepare(tjFilter)
    {
        print("id: \(dataRec[id]), stringfld: \(dataRec[stringfld])")
    }

    //**********************************************************************
    //**
    //** Query All Records
    //**
    //**********************************************************************
    for dataRec in try! db.prepare(dbTable)
    {
        print("id: \(dataRec[id]), stringfld: \(dataRec[stringfld])")
    }

    //**********************************************************************
    //**
    //** Delete Records
    //**
    //**********************************************************************
    try! db.run(dbTable.delete())

Everything was working just fine for me.... until I added the following line under the "Add Record 1" comment block...

    let testblob: Blob = windowcontent()

windowcontent() is a class (or could be a struct) that I want to "Archive" and save... The reason I am using a Blob to store it in is that the stored object type could be of several different classes.

I also tried this:

        let testdata: Data = Data()
        let testblob: Blob = testdata as Blob

and get an expected error of unable to convert Data to Blob

Does anyone have a simple example of assigning and retrieving data to/from an SQLite.Blob that they would share with me ?

Also, side topic, is there a good place I can share my "basics" iOS example code once I have all of it running ? Assuming some other newbie may come along and it could make there life easier.

Answer 1

Here is the running code that answers my question. See the section "Query particular record using filter" for where I convert from Blob back to String.

override func viewDidLoad()
{
    super.viewDidLoad()
    // Do any additional setup after loading the view.


    //**********************************************************************
    //**
    //** Create/Open database connection
    //**
    //**********************************************************************

    // dbpath is where we want the sqlite file to reside... in this case we are using the documents directory
    let dbPath = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true).first!

    // the filename we are giving our database is "testdb.sqlite3" - it can be what ever you like.  If the file exists, it opens it as a database, if it does not exist, it will create it and then open it.
    let db: Connection = try! Connection("\(dbPath)/testdb.sqlite3")

    // defining the name of the SQLite table we want to use as well as the field names we want to create in the table.  In this case, we are creating one of each major type of fields - Bool, Long Int, String, Blob
    let dbTable = Table("testtbl")
    let id = Expression<Int64>("id")
    let boolfld = Expression<Bool>("boolfld")
    let int64fld = Expression<Int64>("int64fld")
    let stringfld = Expression<String>("stringfld")
    let blobfld = Expression<SQLite.Blob?>("blobfld") // Blob field is optional due to SQLite.Blob? - remove the ? to make it require "Not Null"

    //**********************************************************************
    //*
    //* Drop table if it exists
    //*
    //**********************************************************************
    // Deleting the table if it exists - this is simply to allow the program to be rerun and start by  creating a new table
    try! db.run(dbTable.drop(ifExists: true))

    //**********************************************************************
    //**
    //** Create/Open Table
    //**
    //**********************************************************************
    do
    {
        // create the table with the following fields...
        try db.run((dbTable.create(ifNotExists: true)
        {
            t in
            // the fields in our table...
            t.column(id, primaryKey: true)
            t.column(boolfld)
            t.column(int64fld)
            t.column(stringfld)
            t.column(blobfld)
            })
        )
    }
    catch
    {
        // should not get to this, but if it does, you can expect the remainder of the app to fail also.
        print("Failed to create sticki table")
    }

    //**********************************************************************
    //**
    //** Add Record 1
    //**
    //**********************************************************************
    do
    {
        // setup a Data var and then save it as an SQLite.Blob
        let testdata: Data = "foo".data(using: .utf8)!  //Data()
        let testblob: Blob = Blob(bytes: [UInt8](testdata))

        // insert a new record into the database... the function will return the rowID of the newly inserted record.
        let rowid = try db.run(dbTable.insert(boolfld <- true, int64fld <- 1963, stringfld <- "unknown", blobfld <- testblob))

        print("inserted id: \(rowid)")
    }
    catch
    {
        print("insertion failed: \(error)")
    }

    //**********************************************************************
    //**
    //** Add Record 2
    //**
    //**********************************************************************
    do
    {
        // Adding a 2nd record to the database - no Blob field this time
        let rowid = try db.run(dbTable.insert(boolfld <- true, int64fld <- 1972, stringfld <- "David"))
        print("inserted id: \(rowid)")
    }
    catch
    {
        print("insertion failed: \(error)")
    }

    //**********************************************************************
    //**
    //** Update Record 1
    //**
    //**********************************************************************

    // Setup filter to get record "WHERE id == 1"
    let rec2updt = dbTable.filter(id == 1)
    do
    {
        // db.run will return the # of records updated - must be > 0 (actually, == 1)
        if try db.run(rec2updt.update(stringfld <- "TJ")) > 0
        {
            print("updated to TJ")
        }
        else
        {
            print("record not found")
        }
    }
    catch
    {
        print("update failed")
    }

    //**********************************************************************
    //**
    //** Query Particular Record using filter
    //**
    //**********************************************************************

    // Setup filter to get record "WHERE int64fld == 1963"
    let tjFilter = dbTable.filter(int64fld == 1963)

    // Run using filter and print out results (should only be 1 rec in our case)
    for dataRec in try! db.prepare(tjFilter)
    {
        // Convert Blob back to String
        let tmpStr: String = String(bytes: dataRec[blobfld]!.bytes, encoding: .utf8)!
        print("id: \(dataRec[id]), stringfld: \(dataRec[stringfld]), blobfld: \(tmpStr)")
    }

    //**********************************************************************
    //**
    //** Query All Records
    //**
    //**********************************************************************

    // Prints all records in database
    for dataRec in try! db.prepare(dbTable)
    {
        print("id: \(dataRec[id]), stringfld: \(dataRec[stringfld])")
    }

    //**********************************************************************
    //**
    //** Delete Records
    //**
    //**********************************************************************

    // Deletes ALL records in the table (use a filter to delete individual records)
    try! db.run(dbTable.delete())

}

override func didReceiveMemoryWarning()
{
    super.didReceiveMemoryWarning()
    // Dispose of any resources that can be recreated.
}