Add #column_types and #column_type(index : Int32) to DB::ResultSet

Crystal Contrib
1

Hi, I am working on an app that does a lot of code generation. One part of it generates code for SQL queries. While I plan to allow the end-user to specify the types of columns explicitly, I really also need a way to have a default. But I don’t see a way to get the database column type from the ResultSet in order to decide what that default should be. At first I wondered maybe it is not be possible, but I checked the SQLite docs and it is (and in fact has to be for the API to be useful/work).

From Result Values From A Query

The sqlite3_column_type() routine returns the datatype code for the initial data type of the result column. The returned value is one of SQLITE_INTEGER, SQLITE_FLOAT, SQLITE_TEXT, SQLITE_BLOB, or SQLITE_NULL.

I can check, but I imagine other supported databases are going to have a way to get this info too.

So I propose adding #column_types and #column_type(index : Int32) to DB::ResultSet. Can we add these abstract methods, so that maintainers of the various backends can proceed to implement?

To do this, I suppose we will need a standardized Enum for the variety of possible types supported across various databases, basically these.

And ideally we probably should add a method for getting the size of the column too, for column types such as VARCHAR, BLOB, etc.

2

I would post an issue on the crystal-db issue tracker for this, but here are my initial thoughts:

Looks like Postgres can. It passes the oid information for each column into the ResultSet. oids are Postgres type ids (stands for “object identifiers”), equivalent to the SQLite datatype code.

An enum won’t work here, at least not for all DB implementations because the sets of supported data types aren’t the same across all of them. Especially when you take into account types provided by DB plugins/extensions.

3

For reference: Add #column_types and #column_type(index : Int32) to DB::ResultSet · Issue #204 · crystal-lang/crystal-db · GitHub