How to update or insert with SQLite.swift
In this example, the user dictionary stores words that are typed on a custom keyboard. If the word is already in the dictionary, then that word's frequency count is incremented by 1. But if the word hasn't been entered before, then a new row is inserted with a default frequency of 1.
The table was created with the following schema:
let userDictionary = Table("user_dictionary")
let wordId = Expression<Int64>("id")
let word = Expression<String>("word")
let frequency = Expression<Int64>("frequency")
// ...
let _ = try db.run( userDictionary.create(ifNotExists: true) {t in
t.column(wordId, primaryKey: true)
t.column(word, unique: true)
t.column(frequency, defaultValue: 1)
})
Taken from the question, this is what we want to do:
- Begin a transaction
- Do the update
- Check the rowcount
- If it is 0 do the insert
- Commit
Here is how the code would look.
let wordToUpdate = "hello"
// ...
// 1. wrap everything in a transaction
try db.transaction {
// scope the update statement (any row in the word column that equals "hello")
let filteredTable = userDictionary.filter(word == wordToUpdate)
// 2. try to update
if try db.run(filteredTable.update(frequency += 1)) > 0 { // 3. check the rowcount
print("updated word frequency")
} else { // update returned 0 because there was no match
// 4. insert the word
let rowid = try db.run(userDictionary.insert(word <- wordToUpdate))
print("inserted id: \(rowid)")
}
} // 5. if successful, transaction is commited
See the SQLite.swift documentation for more help.