I'm using SQLite with Swift to store some JSON in a table column. I convert a Codable object into a JSON String and store it in the "json" column in my "data" table.
id
name
json
1
item1
{"status" : true}
2
item2
{"status" : false}
struct Item : Codable {
let status : Bool
}
//Decode with:
let decoder = JSONDecoder()
let item = try decoder.decode(Item.self, from: someStrigData)
The issue arises when I try to modify that stored JSON String using the SQLite JavaScript functions. SQLites uses 1 and 0 instead true/false and as a result, the JSON String fails to Decode.
To illustrate, the following SQL Query:
UPDATE data SET json = json_set(json, '$.status', false) WHERE name = "item1"
Will change the table the following way:
id
name
json
1
item1
{"status" : 0}
2
item2
{"status" : false}
I even tried to extract and use the Boolean value from hardcoded JSON String.
UPDATE data SET json = json_set(json, '$.status', json_extract('{"a": false}', '$.a') ) WHERE name = "item1"
But it did not help:
id
name
json
1
item1
{"status" : 0}
2
item2
{"status" : false}
I need a way to to force SQLite to use true/false instead of 0 and 1 OR I need a way for Swift to recognise 0 and 1 as true/false and correctly decode the JSON String into a Swift object.
I would like to note that the data here is for illustrative purposes and I actually use generics that conform to Codable, which means I can't implement custom encode/decode for each type because I want this to work for any object that conforms to Codable protocol.
If you're forced to deal with mixed types in the DB column, then you don't have any really good options directly via Codable. One possible solution is to use the JSONSerialization class to deserialize the incoming JSON into a dictionary, modify the appropriate leaf values to use boolean values consistently, re-serialize the modified data, then Swift-decode via Codable.
(You don't need the reverse transformation in this case, because you are apparently allowed to store true/false values in the DB as-is.)
If you can choose to adopt numeric values consistently in the DB column, you could perhaps use a computed property to work around the problem, something like:
struct Item : Codable {
private let status : Int
var itemStatus : Bool {
status != 0
}
init(itemStatus: Bool) {
self.status = itemStatus ? 1 : 0
}
}
Hmm good ideas but I want to be able to use arbitrary Codable to store/retrieve/modify in the database without modifying each and every object type that I will store.
So I have an idea to use string operations in the data base, for example I can put a unique placeholder instead of true/false and then replace that placeholder together with the quotes using substring replacement functions to achieve the desired effect but it would be much nicer if there is way to solve it in Swift.
It appears that encoding/decoding needs a bit more flexibility.
If (and only if) it's not possible to fix the problem on the other side (so it always writes correct true / false), I'd do this:
struct JSBool: Codable {
var value: Bool
init(from decoder: Decoder) throws {
let container = try decoder.singleValueContainer()
if let value = try? container.decode(Bool.self) {
self.value = value
} else if let value = try? container.decode(Int.self) {
self.value = value != 0 // treats any <> 0 as true
} else {
fatalError("Other case") // like "true", or "yes", etc
}
}
}
struct Item: Codable {
var status: JSBool
var boolStatus: Bool {
get { status.value }
set { status.value = newValue }
}
}
Note you'll have to change the use sites to "item.boolStatus" instead of "item.status".
