dtcyv3985
2018-01-19 13:19
浏览 33

在Postgresql数据库中存储递归go结构

I have two structs (Person and Tenant) that reference each other recursively.

I have no experience with 'SQL' and Im trying to use the https://github.com/jmoiron/sqlx library to store these structs in a way that they keep referencing each other, so that I can retrieve them again as structs.

I don't know which type should the tables be created with or how I am supposed to insert the objects to get it work.

Also If there is any other go library that can handle this case easily i'm open to any suggestions.

Thanks in advance.

type Tenant struct {
    Id     int      `db:"id"`
    Name   string   `db:"name"`
    Person []Person `db:"person"`
}
type Person struct {
    Id       int       `db:"id"`
    Username string    `db:"username"`
    Tenants  *[]Tenant `db:"tenants"`
}
func main() {

    var schema = `
                       CREATE TABLE IF NOT EXISTS person (
                           id int,
                           username text
                           tenants []text //-> type????
                       );

                       CREATE TABLE IF NOT EXISTS tenant (
                           id int,
                           name text,
                           person []text //-> type????
                       )`

    psqlInfo := fmt.Sprintf("host=%s port=%d user=%s "+
        "password=%s dbname=%s sslmode=%s",
        host, port, user, password, dbname, sslmode)

    db, err := sqlx.Open("postgres", psqlInfo)
    if err != nil {
        fmt.Println(err)
        return
    }
    defer db.Close()

    err = db.Ping()
    if err != nil {
        panic(err)
    }

    fmt.Println("Successfully connected!")

    db.MustExec(schema)

    var tenant1 Tenant
    var person1 Person
    tenant1 = Tenant{1, "newtenant", []Person{person1}}
    person1 = Person{1, "newuser", &[]Tenant{tenant1}}

    tx := db.MustBegin()

    tx.NamedExec("INSERT INTO tenant (id,name,person) VALUES (:id,:name, :person)", &tenant1)
    tx.Commit()
    out := []Tenant{}
    db.Select(&out, "SELECT * FROM tenant ORDER BY name ASC")

    fmt.Println(out)
}
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongwei3151 2018-01-19 13:49
    已采纳

    NOTE: This is not a real answer, just a longer comment on the SQL part of the question. Unfortunately I have no experience with sqlx so I cannot help you with that.


    What you have there seems to be a many-to-many relationship. A Person can belong to multiple Tenants and a Tenant can have multiple Persons.

    In SQL this is usually handled by, what's sometimes called, a linking or junction table.

    -- postgresql flavor of SQL
    
    CREATE TABLE IF NOT EXISTS person (
        id serial PRIMARY KEY,
        username text NOT NULL
    );
    
    CREATE TABLE IF NOT EXISTS tenant (
        id serial PRIMARY KEY,
        name text NOT NULL
    );
    
    -- the linking table
    CREATE TABLE IF NOT EXISTS person_tenant (
        person_id integer NOT NULL REFERENCES person (id),
        tenant_id integer NOT NULL REFERENCES tenant (id),
        PRIMARY KEY(person_id, tenant_id)
    );
    
    已采纳该答案
    打赏 评论

相关推荐 更多相似问题