columnTypes behavior is confusing

[CodingDoug]

This might be a sqld/hrana issue, but I'm reporting this from the observations from the TS client as seen in https://github.com/libsql/libsql-client-ts/pull/87

The values incolumnTypes have inconsistent (and, in my opinion, not very useful) behavior. The values are be based on the underlying table types given at the time of creation, but follow strange rules.

• Given type cloB becomes columnType cloB (note the exact reproduction of case)
• Given type text becomes columnType TEXT

As far as I can tell, the rules for deriving columnTypes are like this:

• If one of the main SQLite types in any case (TEXT, BLOB, INTEGER, REAL), convert to uppercase
• Otherwise, give back verbatim what was specified when creating the table, including any size limit (e.g. "VaRcHaR(255)" is also reported verbatim)

Additionally, the affinity "NUMERIC" can't be discerned at all by looking at columnTypes. When a column has numeric affinity, the columnType won't tell you what kind of value is actually in each row (it could be a JS string or number - you still have to examine each value itself to know for sure what you've got).

I'm not sure how useful all this is. Client code can break if the type simply changes case in the table and can't easily discern affinity. It would be more useful if the type was always provided in SQLite type affinity form in as a constant string (regardless how it was provided when creating the table). That would give a more reliable indicator of what's in each row value, and easy to switch on.

[penberg]

This is the behaviour you get with SQLite (including all the libraries) so I don't know what we can do here. We would essentially have to implement SQLite type affinity rules, which doesn't seem to make much sense.

[CodingDoug]

@penberg is there something in SQLite that documents this inherited behavior, or is this all just assumed to be understood?