fields: add function to return expression for selecting all columns of a given struct.

[henvic]

Hi,

I'm sending this for the sake of giving back upstream, feel free to use it as you wish.

We've created a function to help us use scany more productively on some experiments we're running with regards to:

• Primary: being able to add new fields on a database (if we use SELECT * this is not possible)
• Secondary: naming fields explicitly instead of using * mean we've greater performance thanks to any gains related to indices, reduced network communication, etc.

By the way,

• I'm not sure if the name Fields() and Wildcard() conveys nicely what it does. You might also want to consider Columns() instead.
• Maybe the caching layer is overkill.

Thanks.

[georgysavva]

Hello! This feature might be a good improvement to scany capabilities actually.
I need to think of a proper place where to put these new functions to keep the API consistent. Your feature might be also related in terms of API to another functional that I would like to implement, see this thread: https://github.com/georgysavva/scany/issues/12#issuecomment-679303703.

BTW, The first thing that comes to my mind about your current implementation is how Wildcard() function should handle nested structs? Consider the following example:

type Place struct {
    Name string
   Address string
}

type User struct {
    Place *Place 
}

If we call .Fields() function on the User type it returns: []string{"place.name", "place.address"} which is expected. Then we call .Wildcard() it will return: place.name, place.address and I see a problem here because in most databases it means that the result column name in the SELECT statement will be "name" and "address" and you need an explicit name alias: place.name AS "place.name", place.address AS "place.address". Otherwise, you won't be able to scany into User type with scany capabilities and I believe this is something you want to achieve!

The caching is good, but it should be rather inside the GetColumnToFieldIndexMap() function. This is something I would also like to add in the future, see: https://github.com/georgysavva/scany/issues/25#issuecomment-756498314

[henvic]

Hi Georgy,

Funnily enough, today we identified more problems with handling nested structures (panic, and part of what you said). I'm fixing it on my end and will get back once I've it working properly.

Also, sometimes you have a JSON or JSONB data type on your table and this would generate a list of each individual field mapped, so maybe an option called json akin to encoding/json's omitempty tag option is necessary to make this work as intended.

Possible usage:

type Table struct {
	Column      Nested `db:"column,json"`
}

or

type Table struct {
	Column      Nested `db:",json"`
}

/cc @Dalot (kudos for identifying the issues).

[henvic]

Hi @georgysavva,

I've done the improvements discussed above, and separated a global cache layer prototype in a second commit.

[georgysavva]

Hi @henvic. Thank you so much for working on this feature. It took me a while to get back to you. Unfortunately, I've decided not to include that feature in scany since there are some design complications I am wary of.

Again, deeply sorry for not accepting your work.

[henvic]

Hey Georgy, no worries! Thank you for scany, regardless!