Support for columns with variable type

[ahti]

I'm working on building a prototype reimplementation of an apps model layer using sqlite-data (just the local db part), aiming for compatibility with the existing schema. It's going rather well overall, except for one column in one table: This column does not have a type annotation in the schema, and the type of the value varies per row between String, Int and Double based on runtime configuration.

The current version of the app uses (through some abstraction) sqlite3_column_type and switches over the result to determine what type to decode, so the type just becomes whatever was bound when the row was inserted, put in Int, get back an Int, put in String, get back a String, no matter how number-y the string might look or whatever.

With sqlite-data/structured-queries, there doesn't seem to be a way to replicate this behavior, or at least I can't seem to find one.

Would you be open to accepting a PR adding some kind of support for this? I opened the discussion here since sqlite-data is what i use, but I'm not quite sure how it would slot in nicely. Adding a new requirement to QueryDecoder in structured-queries feels kinda wrong since this is rather SQLite specific. GRDB has the DatabaseValue type which fits, I guess conforming that to QueryDecodable and casting the QueryDecoder in init(decoder:) to then call a sqlite-specific func could work.

[stephencelis]

@ahti Thanks for flagging this. After mulling it over we think that our decoding is overly lenient. We've pushed a PR exploring a "strict" mode here, which will throw on type mismatch, allowing you to wrap your init(decoder:) logic with try–catch to achieve what you want:

#489

One thing to consider is that this is only as flexible as SQLite's very limited type affinity, so you wouldn't be able to use this trick to distinguish a Bool from an Int, or a Double from a Float for example.

Wanna take it for a spin and let us know what you think?

[stephencelis]

For the typeMismatch error case, I wonder how much including the type in the value costs from a performance perspective

If you want to try benchmarking and reporting your findings, we are definitely down to reconsider things, but I imagine a stack value with a pointer to a metatype location is rather cheap. Definitely open to providing something closer to the metal, but in the case you suggested earlier it would still be a cast to get to a SQLiteQueryDecoder or something like it, which might be even more expensive.

Otoh, the type included in the error value is the expected type, so it doesn't really add any new information

This is admittedly an undercooked 0.x API that sort of matches Swift's built-in DecodingError.typeMismatch, but without the DecodingError.Context—and maybe it's this context-like part of the error that could allow you to get access to the underlying SQLite database pointer…ideas welcome 😄

Also, when decoding a Double, the abstraction our app is currently using allows (apart from the column with variable types) reading both SQLITE_FLOAT as well as SQLITE_INTEGER.

Technically this is possible with this branch via:

do {
  self.double = try Double(decoder: decoder)
} catch {
  self.double = Double(try Int(decoder: decoder))
}

In general I think it's worth noting that the author of SQLite is staunchly in favor of the flexible type system (as might be expected from its creator 😄, although they did end up adding STRICT tables), so being this strict about types could maybe be called not-super-idiomatic for an SQLite tool/library.

That being said, when I wrote our current abstraction I did end up going the strict route as well (for columns other than the one that prompted this post), because I felt it fit better with Swifts type system. But maybe the strict behavior could be surprising for some and/or be problematic for some existing DBs that might have mixed types because whatever tool was used to create them didn't care about being specific with types.

I love SQLite, but I find that article not particularly moving. I got started with dynamic languages and have felt both the joy and pain of large-scale applications built in languages like Ruby (my rebuttal to argument 4.1). Types, like tests, are just another tool for proving correctness, and the quality of proof is only as good as the quality of the pudding.

Point-Free is all about analyzing trade-offs and writing real-world code to vet how things should be. That means we're happy to field examples of where this stricter typing could be problematic, but in applying it to our own code bases, we surfaced several correctness issues in our own libraries and test schemas (rebuttal to 4.2, though they are minor bugs that are probably not a big deal).

(For full coverage, 4.3 seems to admit there is a problem but you can query to correct it, and extends it with a vague notion of "flexible" typing making "egregious" errors easier to spot, without any evidence or example. And 4.4 tries to turn the argument around on itself with a symmetry that doesn't exist---it would be far more honest to just admit the trade-offs. And at the end of the day the very notion of the positivity "flexible" vs. the negativity of "rigid" shows that there is a real bias here 😄)

[ahti]

Okay I've tried your branch in my prototype, and have also implemented what i had in mind initially. I then ran some benchmarks using ordo-one/benchmark on our real dataset and two derived variants.

Turns out including the type in the error or not didn't make a significant difference, but it does seem the throw/catch cycle does take a bit of time as well as quite a lot of malloc traffic. When using a chain of try-catch the distribution of types and order in which the types are tried matter quite a bit.

Overall tho it's within roughly the same order of magnitude and I never need to decode anywhere near 550k rows at once in practice, so I think I could work with either solution.

Would be interesting to see how these results would look on a machine with worse memory bandwidth, and also try if typed throws might improve things somehow, but this is all I have time for today :D

Detailed Results

The benchmarks were run on a 14-core M4 Pro. All databases are in-memory.

The results marked "try chain" are using an enum with int, string and double cases, with this init, with the order the types are tried in varied according to the title.

init(decoder: inout some QueryDecoder) throws {
    if let s = try? String(decoder: &decoder) {
        self = .string(s)
    } else if let i = try? Int64(decoder: &decoder) {
        self = .int(i)
    } else if let d = try? Double(decoder: &decoder) {
        self = .double(d)
    } else {
        throw ValueDecodingError.unexpectedType
    }
}

The results for DatabaseValue are using the implementation here (mixed with a bunch of changes for running the tests on linux ^^).

Benchmarks grouped by dataset:

Live

This is a copy of from a live database from our app. The Values table has 550k rows, types distributed as follows:

sqlite> select typeof(value), count(typeof(value)) from Values group by typeof(value);

typeof(value)  count(typeof(value))
-------------  --------------------
integer        227167
real           23
text           327567

DatabaseValue

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	18	18	18	18	18	18	18	3228
Time (wall clock) (ms) *	36	37	37	37	38	38	40	3228

try chain String -> Int -> Double

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	246	246	246	246	246	246	246	2933
Time (wall clock) (ms) *	40	41	41	41	41	42	44	2933

try chain Int -> String -> Double

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	346	346	346	346	346	346	346	2614
Time (wall clock) (ms) *	45	46	46	46	46	47	50	2614

try chain Double -> Int -> String

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	901	901	901	901	901	901	901	1755
Time (wall clock) (ms) *	67	68	68	69	69	72	79	1755

mixed

same database, with all values replace with result of

CASE abs(random()) % 12
  WHEN 0 THEN 'apple' WHEN 1 THEN 'banana' WHEN 2 THEN 'cherry' WHEN 3 THEN 'pie'
  WHEN 4 THEN 1 WHEN 5 THEN 42 WHEN 6 THEN -7 WHEN 7 THEN 999
  WHEN 8 THEN 3.14159 WHEN 9 THEN -2.5 WHEN 10 THEN 1.0e100 ELSE 1234.5678
END

DatabaseValue

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) *	89	89	89	89	89	89	89	3184
Time (wall clock) (ms) *	36	37	38	38	38	39	41	3184

Suspiciously low malloc count here, note the missing K 🤔

try chain String -> Int -> Double

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	554	554	554	554	554	554	554	2184
Time (wall clock) (ms) *	53	55	55	55	56	57	60	2184

string only

Same database again, with all values replaced with CAST(value AS TEXT).

DatabaseValue

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	18	18	18	18	18	18	18	3461
Time (wall clock) (ms) *	34	34	35	35	35	36	38	3461

try chain String -> Int -> Double

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	18	18	18	18	18	18	18	4001
Time (wall clock) (ms) *	29	30	30	30	30	31	34	4001

try chain Double -> Int -> String, string values only

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	1128	1128	1128	1128	1128	1128	1128	1662
Time (wall clock) (ms) *	69	72	72	73	73	75	83	1662

[stephencelis]

Thanks for sharing! It does seem like the benchmarks are reasonable enough to move forward, though we're in no rush and can let the PR simmer for a bit. We'd also be happy to make improvements that bring allocations and wall clock time down, so I'll try to find time to mess with Instruments a bit before merging.

[ahti]

I just gave typed throws a go and that makes all the difference :D I added a case other(Error) to QueryDecodingError, and changed the decode functions on SQLiteQueryDecoder (just the implementation, not the protocol) to throws(QueryDecodingError), wrapping the other errors where needed, and that brings the performance to pretty much dead even.

Detailed Results

Live

DatabaseValue

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	18	18	18	18	18	18	18	3215
Time (wall clock) (ms) *	36	37	37	38	38	38	41	3215

try chain String -> Int -> Double

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	18	18	18	18	18	18	18	3558
Time (wall clock) (ms) *	33	34	34	34	34	35	37	3558

try chain Int -> String -> Double

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	18	18	18	18	18	18	18	3421
Time (wall clock) (ms) *	34	35	35	35	35	36	38	3421

try chain Double -> Int -> String

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	18	18	18	18	18	18	18	3056
Time (wall clock) (ms) *	38	39	39	40	40	41	43	3056

mixed

DatabaseValue, evenly mixed values

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) *	89	89	89	89	89	89	89	3127
Time (wall clock) (ms) *	37	38	38	39	39	40	61	3127

try chain String -> Int -> Double, evenly mixed values

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) *	89	89	89	89	89	89	89	3265
Time (wall clock) (ms) *	35	36	37	37	37	38	41	3265

string only

DatabaseValue, string values only

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	18	18	18	18	18	18	18	3352
Time (wall clock) (ms) *	35	36	36	36	36	37	40	3352

try chain String -> Int -> Double, string values only

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	18	18	18	18	18	18	18	4000
Time (wall clock) (ms) *	29	30	30	30	30	31	33	4000

try chain Double -> Int -> String, string values only

Metric	p0	p25	p50	p75	p90	p99	p100	Samples
Malloc (total) (K) *	18	18	18	18	18	18	18	3045
Time (wall clock) (ms) *	38	39	39	40	40	41	42	3045

[stephencelis]

@ahti Good find! Want to PR your changes into the open PR?