Slice Support

[go-gorp]

Slice Support

[coopernurse]

Hi there!

I haven't thought about slices yet. Postgres supports array columns it looks like. Is that a SQL standard, or a Postgres extension? I'm curious of how to handle it in the general case. Open to suggestions on this.

cheers

-- James

[alecloudenback]

I'm a self-taught programmer, who's especially new to databases. But what I think would be causing the issue is the structure of slices, which consist of of a pointer to the array, the length of the segment, and its capacity. I would suppose that if the slice's array was sent as an argument to the DB, it would work.

One could detect the type of the Go struct field and decide whether or not it was a slice via the reflect package. I will attempt to make inroads into this strategy, but if this adds insight to you I can almost assure you that someone else could turn this around better and faster than myself.

[coopernurse]

Hi there. I'm not too familiar with how these Postgresql. ARRAY types work. SQLite and MySQL don't support ARRAYs as far as I know, so this feature would only be available for Postgresql.

A first step that would help would be to write a .go file (perhaps as a gist) that just does a simple INSERT / UPDATE / SELECT sequence against a table that has an ARRAY field using the pql driver directly (i.e. no gorp). That example would help me understand how this data type works and would prove that the go postgres driver supports them.

Once we have that example I suspect we could figure out how to bolt it into the SQL generator in gorp.

What do you think?

[adharris]

I took a stab at implementing some of the array functionality using the driver:
https://gist.github.com/4163702

let me know if there is anything I can add there

[coopernurse]

Sorry I've been quiet on this ticket. @adharris, thanks for the example. I just pushed a mechanism that allows you to write custom type converters for struct fields. I wonder if this would be a good way to handle slices.

If anyone has a moment, take a look at the gorp_test.go testTypeConverter type, and related TestTypeConversionExample()

[cy137]

Does anyone know if this has been worked on?

Thanks!

[coopernurse]

Not to my knowledge.

[lenw]

The type converter interface is happening at the wrong level afaics for this to work.

In pg (http://www.postgresql.org/docs/9.3/static/arrays.html) this sort of insert would need to be generated :

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);


INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

I guess Dialect.BindVar(x) would need to do the work there based on the column type ?

[siliconsenthil]

Any update on this? Am I missing something. Is there a way to register a custom mapper to convert to value string, otherwise? Or any other workaround?

[GeertJohan]

We'll have to pick this up in v2-phase3 (see #270). @siliconsenthil

[schmorrison]

I am new to Go and programming in general but could we, instead of defining an array column type, create a related table and include a reference key from the parent table. For example:

Parent struct {
  Id int
  Name String
  Children []Child
}

Child struct {
  Id int
  Name String
}

dbMap.AddTableWithName(Parent{}, "parent").SetKeys(true, "Id")
dbMap.AddColMap("Id")
dbMap.AddColMap("Name")
dbMap.AddColMap("Children").SetChild(true)

dbMap.AddTableWithName(Child{}, "child_of_parent").SetParentStruct(true, "parent")
dbMap.AddColMap("Id")
dbMap.AddColMap("Name")
dbMap.AddColMap("ParentRef").SetParentRef(true)

list := &Parent{}
dbMap.Select(list, "select * from parent where Name = ?", "testParent")

I dont know anything about the bolts underpinning gorp, but using the field names of the Parent structure, and just create a new table called child_of_parent with a column that has the reference id of the Parent. Whenever select parent is called gorp knows that the []child is fulfilled from the table child_of_parent.

Or, optionally, because that would require each Child entry to be directly related to one Parent, including a matching parameter that pulls all children that have a value that matches in a particular column and fill the internal slice with that.

list := &Parent{}
dbMap.SetSelectChild(list.Children, "select * from child_of_parent where Name = ?", "testChild")
dbMap.Select(list, "select * from parent where Name = ?", "testParent")

Though that would be quite like a normal two line anyway.

Not sure how gorp would implement that idiomatically, just spit balling. Not very hard to write a helper to do that for me and just SetTransient(true) on the parent structure.

schmorrison

[nelsam]

@schmorrison That's a different issue. This issue is specifically about slices of database-supported values, which are generally accepted as SQL arrays.

The issue of supporting first-class joins is something I've implemented in a fork, but the code is very complex, and I don't trust directly merging it in. I need to find some time to really sit down and work on it to make sure it's fully tested.