Best Practice to go from sphinx db to somewhat complex golang struct

Question

I am trying to convert a sphinx query via MySQL into a golang struct and am having a hard time. It seems like this should be a common sort of problem, but so far I'm stuck converting it to a map or parsing output myself. Specifically, I have a schema in sphinx that looks like {Source: {ID:string, subId:string, Campaigns:[]{CampaignID:string, Status:string}}}

I've tried using the simple rows.scan but that doesn't help me parse the repeating field, I just get it as an unparsed string. In sphinx, the key's aren't in quotation marks, so JSON.unmarshal doesn't seem to help at all. And using sqlx, I've tried to build up the following struct

type CampaignStatus struct {
    CampaignId string
    Status string
}
type Source struct {
    Id               string
    SubId    string
    StatusByCampaign []CampaignStatus
}
type Status struct {
    Source
}

and passing in a Status struct to Row.ScanStruct() and I get back either a "Missing destination Name Source" error or if I name the source member in Status, I get "sql: Scan error on column index 0: unsupported Scan, storing driver.Value type []uint8 into type *v1.Source". Full disclosure, the sphinx schema has a few other columns as peers with StatusByCampaign, they come after it and I don't care about them in my use case.

This seems like something that has to have been come across before, but I can't seem to find a solution outside of writing my own parser, which I am loath to do.

score 1 · Answer 1 · answered Apr 13 '17 at 18:04

I found out the legacy system I'm working with was using an ASP.Net library called ServiceStack to serialize/deserialize my sphinx db, and it was using it's own custom JSV format, which the author of ServiceStack say as an optimized mix of JSON and CSV, but hasn't been adopted anywhere else as far as I can tell.

So it looks like I'm going to either rewrite that library in Go, or more likely change the indexing algorithm to use JSON.

reticentroot · Answer 2 · 2017-04-10T18:53:16.883

0

I think it might be the way you are defining your structs. Given the json you provided {Source: {ID:string, subId:string, Campaigns:[]{CampaignID:string, Status:string}}} your struct should look like something close to whats below.

type object struct {
    Source struct {
        ID        string     `db:"id"`
        SubId     string     `db:"sub_id"`
        Campaigns []compaign `db:"compaigns"`
    } `json:"source"`
}

type compaign struct {
    CampaignID string `db:"compaign_id"`
    Status     string `db:"status"`
}

Given the comments below. Try obj := make(map[string]interaface{}) instead of the defining a struct.

Also taking a look at https://github.com/jmoiron/sqlx the struct should be using the db flag instead of json, so I changed it. You shouldn't have to marshal the data

example db.Select(&people, "SELECT * FROM person ORDER BY first_name ASC") Where &people is a reference to a struct. E.g var people PeopleStruct

Now I'm not famailar with your driver, but it should be the same, in that you pass a reference to a struct or interface with your query and it looks the data into that reference. You don't normally have to unmarshall in the struct after the query.

edited Apr 10 '17 at 18:53

answered Apr 08 '17 at 02:46

reticentroot

3,612
2
22
39

I wish that would work, but the string I get back from my sphinx database doesn't seem to be JSON, as there are no quotes around that values. When trying to unmarshal with Json I get the error "invalid character 'S' looking for beginning of object key string" because the Source in {Source:... is not in quotes in my returned string. Is there a way around this with JSON or a different way to scan the object? – Matt C Apr 10 '17 at 18:32
Have you tried offloading it to a map[string]interface{} instead of a custom struct ... it wont be as picky with data, but youll have to use reflection on the data to access and change it. Another option is to write a parser.. if its string data. Also I don't know how you're scanning the object, but there is plenty on stack about scanning and data formatting, example http://stackoverflow.com/questions/17265463/how-do-i-convert-a-database-row-into-a-struct-in-go – reticentroot Apr 10 '17 at 18:43
Yeah, I have implemented a custom parser that is working for my problem right now, but doing your own is fragile, and I don't know if it will continue to work as my problem set gets bigger. All I really want is to generically be able to pass in a struct type, and have a MySQL reader be able to fill out that type with a given query if the schema's match. Its surprising and dissapointing to me that there isn't a library/built in solution to this. Your link shows explicitely having to put the structure of the data right into the scan method, making me have to re-write the same code over again – Matt C Apr 10 '17 at 19:59
Most Go db drivers work by passing the struct in as part of the query. The driver is responsible for marshaling the data correctly in the struct. It might be in your best interest, as that is a best practice, less likely to break, and scalable. Once your using the drivers as designed then the worries you should have are, what should my query be, and what should my struct look like. – reticentroot Apr 10 '17 at 20:14
I also suggest you give this a read. Its extremely insightful on the best ways to use sql and go together http://go-database-sql.org/overview.html – reticentroot Apr 10 '17 at 20:40
Apreciate that, but I've been trying that and failed. I would like to use JSON and be done with it, but I'm working with a legacy system that has a lot built up into sphinx indexes and they don't seem to be working with the built in DB stuff. I can get the following string out of it via just a rows.Scan(&stringVal) "{Id:1,MarketplaceId:1,StatusByCampaign:[{CampaignId:20,Status:Active},{CampaignId:21,Status:Active}],LastUpdatedOn:0001-01-01,IsDeleted:False}" but am having the hardest time putting that string into a struct. – Matt C Apr 10 '17 at 21:57
Want to add a bit more of source code i or someone else can run. To produce your error. Might be faster if we can see how exactly you trying to parse thar string into a struct, in that way you dont have to rewrite or mod the legacy system drastically – reticentroot Apr 10 '17 at 22:00
Also as im sure you're aware. The string your having trouble with is having trouble because that isnt proper json. You can put all the interger based data into a struct, but the things that look like string (date for instance) arent strings or anything that resemble a data type, which is why it cant be unmarshaled not even into an interface. Unless you parse/format the data or update how you use the driver.. your going to have issues abd likely will have to parse everything. – reticentroot Apr 10 '17 at 23:34
I was thinking of a solution for you. If the issue is the lack of quotes. You might be better with a general purpose regex like the one here http://stackoverflow.com/q/24175802/4639336 that will fornat the string. This may be more reliable then a parser. The link above is an example not a solution, but a poasible alternative. – reticentroot Apr 11 '17 at 01:00

Best Practice to go from sphinx db to somewhat complex golang struct

2 Answers2