Support for SQLite's flexible data types?

[justinclift]

At present with go-sqlite3, there doesn't seem to be a way of retrieving the data type of a result set per row.

This is a problem when the data in a result set has different types in a column than declared by the table definition.

eg if a table says a column is of type (say) integer, and instead one of the rows has a (say) string, then retrieving the row barfs.

I think it's due to using sqlite3_column_decltype() in the DeclTypes() function, which only returns the table level declared column types.

At present I'm using gwenn's SQLite driver, as that has a per row (of the result set) function to retrieve the appropriate types:

    https://github.com/gwenn/gosqlite/blob/0f8d5237cc9841b45604b1c9e4ecbe78866bc6c7/stmt.go#L550-L555

@gwenn's focus these days is on Rust instead of Go, so it's probably a good idea (from my PoV) to see if this can be added in your driver which is much more widely used.

Note, I opened an issue ages ago with some background info for this on @gwenn's repo, if that helps: gwenn/gosqlite#5.

The use case is when working with user generated SQLite databases, for example:

    https://dbhub.io/justinclift/DB4S%20download%20stats.sqlite

Thoughts? 😄

[gjrtimmer]

@justinclift sorry for the delay. This looks like something worth looking in to. Could you do some research and look into the code from gwenn to see if you can locate the code responsible for this ?

[justinclift]

No worries. We all have priorities. 😄

With the code that does it, I think the link to gwenn's repo in the above is the bit you're after. That seems to determine the data type of the actual value for a field, which lets the flexible data typing work.

[gjrtimmer]

@justinclift think we should definitely look into this and either make an project for it or a simple PR which will incorporate this functionality.

[ricardofandrade]

@gjrtimmer I've done some independent research while looking at conversion for uuid.UUID and I think I found a way to implement this.
First, the interface sql.Scanner will always pass the driver's underlying type to Scan(interface{}).
This process happens for each row so a go type capable to dealing with different types would be capable of reading columns with different types in each row.
I would consider this part of retrieving data to be done in the current driver.

Second, the interface sql.Valuer is poor in this aspect because Value() takes no context from the driver so all it can do is returning always the same type broadly compatible with sql for all drivers.
However, the driver can implement driver.NamedValueChecker which takes for each parameter in the query.
The NamedValue in CheckNamedValue(*driver.NamedValue) has enough information to make a decision: the go value, the column name or index.
The driver already has a lot of Register... functions, if we associate the NamedValueChecker implementation with a custom-registered function one can freely convert types before they get inserted / used as parameters.