[PROP] database/sql api extension: Add a way to get column type information

[Arne Hormann]

I'd like to access column types in sql.Rows, but I can only get column names and prefer not to descend into "unsafe" teritory.

In my case it's for a codegenerator, but it would also be very useful for user-specified queries, generated queries or queries on unknown tables.

The type information should already be known to the driver, I just can't get it out.

What's missing is a way to unwrap it from the sql structs (Row and Rows) to get the full data provided by the driver (Rows.rowsi).

If a driver can access its own data given a *sql.Rows, it can provide column type information.

I imagine something like this would work:

database/sql/driver - new exported interface:

type Inspector interface {

SetInspector(inspect func(sqlStruct interface{}) (interface{}, error))

}

database/sql - new func:

func inspect(sqlStruct interface{}) (interface{}, error) {

switch unwrapped := sqlStruct.(type) {

case *Row:

return unwrapped.rows.rowsi, nil

case *Rows:

return unwrapped.rowsi, nil

}

return nil, errors.New("sqlStruct could not be inspected")

}

database/sql - in Register after error checking:

if inspector, ok := driver.(driver.Inspector); ok {

driver.SetInspector(inspect)

}

If required, cases for *Tx, *DB and *Stmt could be added to inspect.

The proposal gives drivers a way to access their own data in the wrapped structs and exports only one interface in the driver package.

The driver only has to store the inspect function it receives on registration.

Only the driver can access this information, it's free to expose it in a safe way.

The sql package keeps responsibility for unwrapping should the internal structs change.

Regarding the driver, it only has to store the function it receives after Registration.

Functions to use it could look like this (example for github.com/go-sql-driver/mysql):

func Fields(rows interface{}) ([]Field, error) {

nativeRows, err := inspect(rows)

if err != nil {

return nil, err

}

myRows, ok := nativeRows.(*mysqlRows)

if !ok {

return nil, errors.New("rows must be a mysql rows object")

}

fields := make([]Field, len(myRows.columns))

for i, f := range myRows.columns {

fields[i] = &f

}

return fields, nil

}

type Field interface {

Name() string

MysqlType() string

MysqlDeclaration(params ...interface{}) (string, error)

ReflectType() (reflect.Type, error)

IsNumber() bool

IsInteger() bool

IsFloatingPoint() bool

IsDecimal() bool

IsText() bool

IsBlob() bool

IsTime() bool

IsPrimaryKey() bool

IsUniqueKey() bool

IsMultipleKey() bool

IsNotNull() bool

IsUnsigned() bool

IsZerofill() bool

IsBinary() bool

IsAutoIncrement() bool

}

And a user would call fields, err := mysql.Fields(rows) after a query.

What do you think?

[Daniel Theophanes]

For a while the sql package wasn't able to get column names. Now it can which is good. I do think it would be good to be able to get the schema information, but I'm not sure where that should best live. Your Field interface in incredibly MySql specific and very long. It seems like if this was exposed from the sql driver, you would want it to return a very small type, maybe interface{} and then type assert it to a driver type. Is a generic database/sql Field going to handle arbitrary PostgreSQL types? Serial types? GIS types? IP types? Arrays? Ranges? Cubrid and SQL Server types? Differentiate between Date, Timestamp, DateTime, DateTime2 DateTimeOffset, Time? UUID? var bit? Set, Multiset, List?

Types are not a thing that can be exposed generically, though they may still be able to be exposed in some manner.

To proceed, I would open an issue at http://golang.org/issue/new , link your two messages from this group to it, and describe your use case and motivation for inclusion with much detail.

-Daniel

[Arne Hormann]

Am Freitag, 31. Mai 2013 18:33:41 UTC+2 schrieb Daniel Theophanes:

For a while the sql package wasn't able to get column names. Now it can which is good. I do think it would be good to be able to get the schema information, but I'm not sure where that should best live. Your Field interface in incredibly MySql specific and very long. It seems like if this was exposed from the sql driver, you would want it to return a very small type, maybe interface{} and then type assert it to a driver type. Is a generic database/sql Field going to handle arbitrary PostgreSQL types? Serial types? GIS types? IP types? Arrays? Ranges? Cubrid and SQL Server types? Differentiate between Date, Timestamp, DateTime, DateTime2 DateTimeOffset, Time? UUID? var bit? Set, Multiset, List?

I think you misunderstand me. The Field stuff is an example specific to the mysql driver and would live there.

I don't propose to add a generic way to access schema information (I think that's impossible), I propose to add a way for a driver to get an unwrapped representation of its own data.

What it does with it and how it exposes it is left to the driver.

[Arne Hormann]

I added Issue 5606.

[Arne Hormann]

For an overview of the kinds of hoops one currently has to jump through to get column metadata (in this case for mysql):

http://play.golang.org/p/Rsw-k10VbB - and that's probably still missing some error handling.

At least I got by without directly using unsafe. This is the most convoluted Go code I ever wrote and I had to duplicate quite some stuff from mysql.

It would be easier if it were integrated into the driver, but then the driver would depend on internal database/sql structures, which is a bad idea.

[Carlos Castillo]

Couldn't rows, and also statements have a function that returns the underlying driver value (although possibly through a very restricted interface like interface{}). This would be analogous to how the os.FileInfo interface works, where you need to know which OS you're on to access the value of os.FileInfo.Sys().

IE: if the sql.Rows object had a "DriverRows() interface{}" method, you'd have to be inside the driver to cast it to a meaningful value (assuming the driver doesn't export it's rows object), and can then use it for the driver specific type inspection.

Calling it would then be mysql.InspectRows(rows.DriverRows()), and it would panic/error if the argument isn't the mysql driver's row type.

On Thursday, May 30, 2013 6:06:17 AM UTC-7, Arne Hormann wrote:

[Arne Hormann]

That should work, too. Still, here's why I prefer my proposal:

• the dependency of a specific driver and specific Go version is entirely optional
• exported changes are limited to database/sql/driver
• it can never be called by a user, as it requires the driver object to get the callback and the driver does not have to export it

Those properties were very important to me when I designed it. My first idea was a simple "func Unwrap(rowOrRows interface{}) (interface{}, error)" in database/sql, but I really didn't like the polution of the user visible api with a driver specific function and the possibility of misuse and irritation that could cause.

My proposal has some similarity to the inclusion of the Queryer interface for faster driver implementations. It's optional, if the mechanism doesn't exist in a Go version older than the driver (the callback doesn't get set in Register), the callback is nil or a default implementation causing an error, so the driver can respond to a Fields() call by returning an error. If the Go version includes this but the driver doesn't, the type assertion in Register will fail and the callback won't be set.

If database/sql.Rows changes, every project using this functionality will have to create different versions or use buildtags so it doesn't break with a version after tip. And that's a lot more code than doing so in the driver, as database/sql.Rows is probably one of the most used structs for anyone using database/sql.

I also think your proposal requires more and deeper changes than mine, but I only evaluated mine, so I don't know for sure.

All in all, I don't see a clear benefit. In what way does it improve what I proposed?

[Arne Hormann]

For anyone following this thread: It somehow got split, which wasn't my intention. Part of the discussion happened in another thread in which I tried to get help with building a workaround.

I wrote about my use case and motivation there:

I'm interested in getting the closest matching go type from the driver and causing the smallest amount of internal type conversions that is possible.

Today, there's just no way to get there. If I use dynamic queries (specified by the user) or aggregated queries or anything synthetic (like SHOW in MySQL), I can't get the type from the database.

Ok, maybe there is a way, but that means replicating a lot of infrastructure across projects to determine the column types and provide guesses for all synthetic stuff not using a table directly. Maybe there could be a companion support library for each driver, but who wants to write and maintain them? 

Additionaly, using []byte every time causes a lot of allocations and inspecting the table structure causes some queries and a slower startup, which in some cases can't be prevented by caching, the database may have changed. If I use SHOW COLUMNS or maybe parse the sql to get that info, I still have to construct something to store it and have to remember the matches between Go and the database types. Moving that to the driver makes life easier for everyone who wants to efficiently serialize rows in the smallest possible binary form, create ORM tools, build code generators (my own use case here), build tools for any database instead of the one one her own server, ...

Having the ability to know the types would be a gift to everyone building tools. Maybe not the users with their own database and existing tables they already know, but I guess they won't complain when better tools are created. And saying better I also mean causing less queries and putting less strain on the GC. Or not having to implement tricks like a lazy buffer in every driver (which I didn't even know about until I saw it in a pull request from bradfitz).