Go-SQLite3: Convert between string and slice

[Mark Bauermeister]

type Country struct {
    ID           int    `json:"id"`
    States string `json:"states"`
}

var Countries []Country

func getAllCountries() {
    rows, err := db.Query("select id, states from countries")
    if err != nil {
        astilog.Error(err)
    }
    defer rows.Close()
    for rows.Next() {
        var id int
        var states string
        err := rows.Scan(&id, &states)
        if err != nil {
            astilog.Error(err)
        }
        Countries = append(Countries, Country{id, states})
    }
}

See (fictional) code above.

This is something I've been struggling with for a while.

In the past, I'd simply accept the string representation of a JSON array and operate on that directly.
However, that's less flexible and certainly less elegant.

What's the correct way of turning the above code into something a bit more canonical?
Since SQLite doesn't know of arrays, I obviously need to somehow cast the array to a string representation somewhere before "rows.Scan". Which is the part I'm struggling with.

The data presentation I'm hoping for looks as follows:

type Country struct {
    ID           int     `json:"id"`
    States []State `json:"states"`
}

type State struct {
    Short        string  `json:"short"`
    Population int `json:"population"`
}

[Marcin Romaszewicz]

Define a type:

type Countries []Country

Then, for that type, implement the sql.Scanner and sql.Valuer interfaces. You could, for example, marshal your state array as a list of comma separated states. You're not casting, but providing customer scanners/readers for your data type which convert it for something that the sql layer knows how to deal with. You could just as well marshal to JSON. There's really not anything you can convert the array into via the type system which SQLite could store. The idiomatic SQL way to handle this, actually, is to use a second table for states, which associates a set of states with each country.

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.