add json-based data into a sqlite database using swiftyJSON and sqlite.swift

Question

Please bear with me this is my first swift project, I'm not yet up to speed with the syntax/language.

I retrieve data from a web service using AlamoFire and parse it using SwiftyJson. Then I want to insert it into a local SQLite database using SQLite.swift.

So far AlomoFire, SwiftyJson and SQLite.swift are installed and working in my project, things went very smooth to my surprise ;)

Now my question. The data I got is in JSON, in an Array so I want to do this:

let companiesTable = db["companies"] // get the table from our SQLite Database
db.transaction(.Deferred) { txn in
    for (index,company) in data {
        if companiesTable.insert(name <- company["name"]!), shortname <- company["short_name"]).statement.failed {
            return .Rollback
        }
    }
    return .Commit
}

My problem is in the insert. I have to force unwrap using a !, which is ok for name (required column in the database), but not ok for shortname or a dozen other columns (not mentioned in the example above for simplicity) which may be empty/null. Of course only columns with a value should be inserted.

Second question. I found the stuff about transactions here on stackoverflow, is the transaction automatically Committed or Roll-backed when doing the 'return'

Rob Rob · Accepted Answer · 2015-07-26T22:15:35

You ask:

My problem is in the insert. I have to force unwrap using a !, which is ok for name (required column in the database), but not ok for shortname or a dozen other columns (not mentioned in the example above for simplicity) which may be empty/null.

If fields like shortname can be empty/null, then define it accordingly:

let company = db["companies"]
let name = Expression<String>("name")
let shortname = Expression<String?>("short_name")

When you do that, then when you insert the data, if the short_name is present, it uses it, and if it's not, it will be NULL in the database:

let data = [
    (1, ["name": "United States of America", "short_name": "US"]),
    (2, ["name": "United Kingdom",           "short_name": "UK"]),
    (3, ["name": "France"])]

let companiesTable = db["companies"] // get the table from our SQLite Database
db.transaction(.Deferred) { txn in
    for (index,company) in data {
        if companiesTable.insert(name <- company["name"]!, shortname <- company["short_name"]).statement.failed {
            return .Rollback
        }
    }
    return .Commit
}

You then ask:

Second question. I found the stuff about transactions here on stackoverflow, is the transaction automatically Committed or Roll-backed when doing the 'return'

If you're using transaction method, whether it commits or rolls back is a function of what TransactionResult value you return. In this example, it will commit if you return .Commit, and rollback if you return .Rollback.

add json-based data into a sqlite database using swiftyJSON and sqlite.swift

1 Answers