October 14, 2014

Swift, Structs, and Data Storage/Retrieval with sqlite

Posted in Uncategorized at 8:52 pm by tetontech

A data storage and interaction problem exits in Swift. If someone uses structs instead of objects in an attempt to take a more functional approach in coding their application, CoreData won’t work. It only interacts with objects, not structs. While Apple may solve this in the future, that possibility doesn’t help us now. To resolve this issue I decided to create two libraries that would behave like an ORM (Object Relational Mapper) but work with structs (STRUM?).

The first library runs all interactions with sqlite3 on background threads so we don’t have to deal with threading. The sqlite3 interaction is SQL based and generates and uses prepared statements, transactions, and rollbacks. I call this first library SwiftlyDB and its source code is available on my github repository. It is MIT licensed and the main purpose of this posting is discussing its API. Later postings will cover Swift lessons learned during SwiftlyDB’s creation.

The second library, currently in the design stage, will work with structs much like any ORM works with objects. It will translate between the results returned by SwiftlyDB queries and create the appropriate structs. It will also generate the SQL required to store struct information using SwiftlyDB. This means that this second library, which I’m currently calling SwiftlyStore, will make it possible to safely store, update, retrieve, and delete from storage Swift structs in an intelligent and parallel way.

Now let’s get down to how to use SwiftlyDB. SwiftlyDB is functional, parallel, light weight (less than 350 lines of code), flexible, and easy to use. Its API consists of two structs, SwiftlyDb and DBAccessError, and four functions; setupSwiftly, discardSwiftly, swiftlyTransact, and swiftlyTransactAll. The first two functions setup and disconnect from a sqlite3 instance for you and the last two execute SQL.

To get started, use setupSwiftly to generate an instance of SwiftlyDB. You do this by passing it the name of an sqlite file. This example uses ‘test.sqlite.’

let (error,aSwiftlyDB) = setupSwiftly(“test.sqlite”)

If the sqlite file exists in your application’s bundle, setupSwiftly will copy it, only once, to your application’s documents directory so it can be used. If you choose not to ship a database with your app setupSwiftly will create the sqlite file in the documents directory for you. Either way, you end up with a usable sqlite database file for your app.

In the example line of code above, aSwiftlyDB is of type SwiftlyDB optional and error is of type DBAccessError optional. When your file is copied and the sqlite3 database is opened, error will be nil and aSwiftlyDB will not.

‘test.sqlite’ used in this example has only one table, the ‘dog’ table with id:text, age:integer, and height:double fields. To add a single dog use the swiftlyTransact function, swiftlyTransact(aSwiftlyDB:SwiftlyDB, sql:String, parameters:Array<Storable>?, resultHandler:(DBAccessError?, Any?) ->()).

The example below shows how to add a dog without using prepared statements which, by the way, is not usually a good idea. An SQL string is assembled to insert the dog and the string, aSwiftlyDB returned from the call to setupSwiftly, and a closure containing code to execute after the insertion is done are passed in as parameters. As is possible in Swift, the closure is placed after the closing parenthesis to aid in readability.

let insertString = "INSERT INTO dog VALUES (\"\(NSUUID.UUID().UUIDString)\", 5, 3.2)"
swiftlyTransact(aSwiftlyDB!, insertString, nil){(error:DBAccessError?, data:Any?) ->() in
    println("inserted")
    if let theErrorDescription = error?.description{
        println("oops. got an insertion error. \(theErrorDescription)")
        return
    }
    else{
        println("updated \(data!) records")
   }
}

After the insertion is done using the sqlite3 C library, the result of the insertion operation is passed to the resultHandler closure. In this case, the data parameter is a Int representing how many records were changed (1). I’ve shown some simple error handling made possible by having both an error and the transaction’s data as parameters to the resultHandler.

Now that data is in the database how do we get it out? A second call to swiftlyTransact will do that for us. In the code below, all of the dogs in the table are requested and printed to the console. You could present this to the user via your app’s user interface but I’m trying to keep the example simple.

let selectString = "SELECT * FROM dog"
swiftlyTransact(aSwiftlyDB!, selectString, nil){(error:DBAccessError?, data:Any?) ->() in
    println("selection")

    if let theErrorDescription = error?.errorDescription{
        println("oops. got a selection error. \(theErrorDescription)")
        return
    }
    else if let dogs = data as? Array<Dictionary<String,String>>{
        println("found \(dogs.count) in dogs")
        for dog in dogs{
            for fieldName in dog.keys{
                println("\t\(fieldName) : \(dog[fieldName]!)")
            }
       }
       println("done with dogs")
    }
}

swiftlyTransact also works well for single prepared statements. This example shows how to add a single dog like the previous insert example.

let preparedStatementString = "INSERT INTO dog VALUES (?,?,?)"
swiftlyTransact(aSwiftlyDB!, preparedStatementString, [NSUUID().UUIDString, 5, 3.2]){(error:DBAccessError?, data:Any?) ->() in
    println("inserted")
    if let theErrorDescription = error?.errorDescription{
        println("oops. got an insertion error. \(theErrorDescription)")
        return
    }
    else{
        println("updated \(data!) records")
    }
}

Any library that forced you to execute a function call for every SQL statement would be poorly designed. SwiftlyDB uses swiftlyTransactAll to fill the roll of executing ‘sql scripts’, swiftlyTransactAll(aSwiftlyDB:SwiftlyDB, tasks:Array<Dictionary<String,Array<Storable>?>>,resultHandler:((DBAccessError?, [Any]?) ->())?) -> ().

swiftlyTransactAll has three parameters, an instance of SwiftlyDB to work with, a list of tasks to do, and a closure to execute once the list of tasks is complete.

This example adds 10 random dogs to the dog table using swiftlyTransactAll.

var taskList = Array<Dictionary<String,Array<Storable>?>>()
for index in 0..<10{
    let parameters:Array<Storable>? = [NSUUID().UUIDString,Int(arc4random()%12),Double(arc4random()%100)/10]
    let aTask = [preparedStatementString:parameters]
    taskList.append(aTask)
}

swiftlyTransactAll(aSwiftlyDB!,taskList){(error:DBAccessError?, data:[AnyO]?) -> () in
    println("inserted")
    if let theErrorDescription = error?.description{
        println("oops. got an insertion error. \(theErrorDescription)")
        return
    }
    else{
        println("updated \(data!.count) records")
    }
}

The last function in the API closes the sqlite database for you and cleans up.

Example:

discardSwiftly(aSwiftlyDB)

So that’s it. The entire API. Most of the code exists in the SwiftlyDB.swift file, but one item of interest exists in the Storable.swift file. It would be good to limit the parameters to types of things that make database sense; Strings, Ints, and Doubles. To this end String, Int, and Double have been extended to implement a custom Storable protocol found in the Storable.swift file. This way types of things that don’t make database sense such as user interface elements, controllers, delegates, etc. can not be accidentally sent to one of the swiftlyTransact functions.

SwiftlyDB is available for your use. It is functional in nature. It handles sqlite database transactions and rollbacks for you. It executes all database interactions in parallel to your code and does this safely. Take it and use it in any way you want.

My next postings will look at Swift lessons learned as I implemented this library.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: