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

[trans]

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 the capability to specify the types of columns explicitly, I also have to have a way to use 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 via sqlite3_column_type() (see docs), and in fact has to be for the API to be useful/work. It is also true for Postgres using oid. I suspect it will true for just about every possible backend.

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, however, I suppose we will need to come up with a standardized Enum for the variety of possible types supported across various databases, mostly these. It has been suggested to me that this might be infeasible given the variety of supported types across all DB implementations (and possible plugins/extensions). How do we handle this? I suppose we either support as many as reasonably possible, or just support a small set of general types along the lines of SQLite. Or would a free-form Symbol be a better solution, if we standardize a set of symbols for the very common types, but allow not standards ones too?

Ideally we should probably also add a method columns_size(index : Int32) for getting the size for column types such as VARCHAR, BLOB, etc.

I hope this can be worked out. My current project is highly dependent on finding a way to get access to these types.

(Note: I originally posted this on the forum.)

[straight-shoota]

Types are very specific to the driver, so I think this could only work in a way that each driver provides a type that describes a column in a way that makes sense for that specific driver.

The generic API would be very minimal, maybe just the methods on ResultSet and a stub for a return type which drivers would inherit from for their implementation. The generic type might not have any specific properties at all. Or maybe some common fields could be standardized.

Some inspiration could be the PQ::Field and MySQL::ColumnSpec types.

[Blacksmoke16]

Iv'e thought about a related topic in the past. Mainly is how far should crystal-db go in regards to providing a common DB API?

The PHP world has https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/introduction.html which does similar things, abstracting out the connection/query/result to allow using the same API no matter the underlying platform. However, it also goes a bit further abstracting out the types: https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html. Together these allow for it to have a SchemaManager that provides an API for doing basically exactly what OP is looking for.

[trans]

@straight-shoota MySQL::ColumnSpec is good inspiration!

The simple solution is to just have the API return the Crystal type the driver deems appropriate. That could be standard type or a specialized one implemented by the driver.