{{$def:=.}}package {{.Pkgname}} /* This file was created by mkdb-client. The intention is not to modify thils file, but you may extend the struct {{$def.Structname}} in a seperate file (so that you can regenerate this one from time to time) */ /* PRIMARY KEY: {{id_field}} */ /* postgres: create sequence {{$def.TableName}}_seq; Main Table: CREATE TABLE {{$def.TableName}} ({{id_col}} integer primary key default nextval('{{$def.TableName}}_seq') {{- range cols_no_id}},{{.}} {{col_sqltype .}} not null {{col_extraopts .}} {{end}}); Alter statements: {{range cols_no_id}}ALTER TABLE {{$def.TableName}} ADD COLUMN {{.}} {{col_sqltype .}} not null{{col_extraopts .}} default {{col_sqldef .}}; {{end}} Archive Table: (structs can be moved from main to archive using Archive() function) CREATE TABLE {{$def.TableName}}_archive ({{id_col}} integer unique not null {{- range cols_no_id}},{{.}} {{col_sqltype .}} not null{{end}}); */ import ( savepb "{{$def.Def.ImportPath}}" "golang.conradwood.net/go-easyops/sql" gosql "database/sql" "fmt" "context" ) type {{$def.Structname}} struct { DB *sql.DB SQLTablename string SQLArchivetablename string } func New{{$def.Structname}}(db *sql.DB) *{{$def.Structname}} { foo := {{$def.Structname}}{DB: db} foo.SQLTablename = "{{$def.TableName}}" foo.SQLArchivetablename = "{{$def.TableName}}_archive" return &foo } // archive. It is NOT transactionally save. func (a *{{$def.Structname}}) Archive(ctx context.Context,id {{field_gotype id_field}}) (error) { // load it p,err:=a.By{{id_field}}(ctx,id) if err != nil { return err } // now save it to archive: _,e := a.DB.ExecContext(ctx,"archive_{{$def.Structname}}","insert into "+a.SQLArchivetablename+"+ ({{id_col}}, {{- range $i,$f := cols_no_id}}{{deli $i ", "}}{{.}}{{end}}) values ($1, {{- range $i,$f := cols_no_id}}{{deli $i ", "}}${{inc2 $i}}{{end}}) ",p.{{id_field}} {{- range $i,$f := fieldvalues_no_id}}, p.{{.}}{{end}}) if e !=nil { return e } // now delete it. a.DeleteBy{{id_field}}(ctx,id) return nil } // Save (and use database default ID generation) func (a *{{$def.Structname}}) Save(ctx context.Context, p *savepb.{{$def.Def.Name}}) ({{field_gotype id_field}},error) { qn := "{{$def.Structname}}_Save" rows,e := a.DB.QueryContext(ctx,qn, "insert into "+a.SQLTablename+" ( {{- range $i,$f := cols_no_id}}{{deli $i ", "}}{{.}}{{end}}) values ( {{- range $i,$f := cols_no_id}}{{deli $i ", "}}${{inc $i}}{{end}}) returning {{id_col}}" {{- range $i,$f := fieldvalues_no_id}}, p.{{.}}{{end}}) if e !=nil { return {{id_null}},a.Error(ctx,qn,e) } defer rows.Close() if !rows.Next() { return {{id_null}},a.Error(ctx,qn,fmt.Errorf("No rows after insert")) } var id {{field_gotype id_field}} e = rows.Scan(&id) if e != nil { return {{id_null}},a.Error(ctx,qn,fmt.Errorf("failed to scan id after insert: %s",e)) } p.{{id_field}} = id return id,nil } // Save using the ID specified func (a *{{$def.Structname}}) SaveWithID(ctx context.Context, p *savepb.{{$def.Def.Name}}) error { qn := "insert_{{$def.Structname}}" _,e := a.DB.ExecContext(ctx,qn,"insert into "+a.SQLTablename+" ({{id_col}}, {{- range $i,$f := cols_no_id}}{{deli $i ", "}}{{.}}{{end}}) values ($1, {{- range $i,$f := cols_no_id}}{{deli $i ", "}}${{inc2 $i}}{{end}}) ",p.{{id_field}} {{- range $i,$f := fieldvalues_no_id}}, p.{{.}}{{end}}) return a.Error(ctx,qn,e) } func (a *{{$def.Structname}}) Update(ctx context.Context, p *savepb.{{$def.Def.Name}}) error { qn := "{{$def.Structname}}_Update" _,e := a.DB.ExecContext(ctx,qn,"update "+a.SQLTablename+" set {{range $i,$f := cols_no_id}}{{deli $i ", "}}{{.}}=${{inc $i}}{{end}} where {{id_col}} = ${{field_count}}" {{- range $i,$f := fieldvalues_no_id}},p.{{.}}{{end}},p.{{id_field}}) return a.Error(ctx,qn,e) } // delete by id field func (a *{{$def.Structname}}) DeleteBy{{id_field}}(ctx context.Context,p {{field_gotype id_field}}) (error) { qn := "delete{{$def.Structname}}_By{{id_field}}" _,e := a.DB.ExecContext(ctx,qn,"delete from "+a.SQLTablename+" where {{id_col}} = $1",p) return a.Error(ctx,qn,e) } // get it by primary id func (a *{{$def.Structname}}) By{{id_field}}(ctx context.Context,p {{field_gotype id_field}}) (*savepb.{{$def.Def.Name}},error) { qn := "{{$def.Structname}}_By{{id_field}}" rows,e := a.DB.QueryContext(ctx,qn,"select {{id_col}},{{range $i,$f := cols_no_id}}{{deli $i ", "}}{{.}}{{end}} from "+a.SQLTablename+" where {{id_col}} = $1",p) if e != nil { return nil,a.Error(ctx,qn,fmt.Errorf("By{{id_field}}: error querying (%s)",e)) } defer rows.Close() l,e := a.FromRows(ctx,rows) if e != nil { return nil,a.Error(ctx,qn,fmt.Errorf("By{{id_field}}: error scanning (%s)",e)) } if len(l) == 0 { return nil,a.Error(ctx,qn,fmt.Errorf("No {{$def.Def.Name}} with id %d",p)) } if len(l) != 1 { return nil,a.Error(ctx,qn,fmt.Errorf("Multiple (%d) {{$def.Def.Name}} with id %d",len(l),p)) } return l[0],nil } // get all rows func (a *{{$def.Structname}}) All(ctx context.Context) ([]*savepb.{{$def.Def.Name}},error) { qn := "{{$def.Structname}}_all" rows,e := a.DB.QueryContext(ctx,qn,"select {{id_col}},{{range $i,$f := cols_no_id}}{{deli $i ", "}}{{.}}{{end}} from "+a.SQLTablename+" order by {{id_col}}") if e != nil { return nil,a.Error(ctx,qn,fmt.Errorf("All: error querying (%s)",e)) } defer rows.Close() l,e := a.FromRows(ctx,rows) if e != nil { return nil,fmt.Errorf("All: error scanning (%s)",e) } return l,nil } /********************************************************************** * GetBy[FIELD] functions **********************************************************************/ {{range fields_no_id}} // get all "{{$def.Structname}}" rows with matching {{.}} func (a *{{$def.Structname}}) By{{.}}(ctx context.Context, p {{field_gotype .}}) ([]*savepb.{{$def.Def.Name}},error) { qn := "{{$def.Structname}}_By{{.}}" rows,e := a.DB.QueryContext(ctx,qn,"select {{id_col}},{{range $i,$f := cols_no_id}}{{deli $i ", "}}{{.}}{{end}} from "+a.SQLTablename+" where {{col_name .}} = $1",p) if e != nil { return nil,a.Error(ctx,qn,fmt.Errorf("By{{.}}: error querying (%s)",e)) } defer rows.Close() l,e := a.FromRows(ctx,rows) if e != nil { return nil,a.Error(ctx,qn,fmt.Errorf("By{{.}}: error scanning (%s)",e)) } return l,nil } // the 'like' lookup func (a *{{$def.Structname}}) ByLike{{.}}(ctx context.Context, p {{field_gotype .}}) ([]*savepb.{{$def.Def.Name}},error) { qn := "{{$def.Structname}}_ByLike{{.}}" rows,e := a.DB.QueryContext(ctx,qn,"select {{id_col}},{{range $i,$f := cols_no_id}}{{deli $i ", "}}{{.}}{{end}} from "+a.SQLTablename+" where {{col_name .}} ilike $1",p) if e != nil { return nil,a.Error(ctx,qn,fmt.Errorf("By{{.}}: error querying (%s)",e)) } defer rows.Close() l,e := a.FromRows(ctx,rows) if e != nil { return nil,a.Error(ctx,qn,fmt.Errorf("By{{.}}: error scanning (%s)",e)) } return l,nil } {{end}} /********************************************************************** * DeteleBy[FIELD] functions **********************************************************************/ {{range fields_no_id}} // delete all "{{$def.Structname}}" rows with matching {{.}} func (a *{{$def.Structname}}) DeleteBy{{.}}(ctx context.Context,p {{field_gotype .}}) (error) { qn := "delete{{$def.Structname}}_By{{.}}" _,e := a.DB.ExecContext(ctx,qn,"delete from "+a.SQLTablename+" where {{.}} = $1",p) return a.Error(ctx,qn,e) } {{ end }} /********************************************************************** * Helper to convert from an SQL Query **********************************************************************/ // from a query snippet (the part after WHERE) func (a *{{$def.Structname}}) FromQuery(ctx context.Context, query_where string,args...interface{}) ([]*savepb.{{$def.Def.Name}},error) { rows,err := a.DB.QueryContext(ctx,"custom_query_"+a.Tablename(),"select "+a.SelectCols()+" from "+a.Tablename()+" where "+query_where,args...) if err != nil { return nil,err } return a.FromRows(ctx,rows) } /********************************************************************** * Helper to convert from an SQL Row to struct **********************************************************************/ func (a *{{$def.Structname}}) Tablename() string { return a.SQLTablename } func (a *{{$def.Structname}}) SelectCols() string { return "{{id_col}},{{range $i,$f := cols_no_id}}{{deli $i ", "}}{{.}}{{end}}" } func (a *{{$def.Structname}}) SelectColsQualified() string { return ""+a.SQLTablename+".{{id_col}},{{range $i,$f := cols_no_id}}{{deli $i ", "}}"+a.SQLTablename+".{{.}}{{end}}" } func (a *{{$def.Structname}}) FromRows(ctx context.Context, rows *gosql.Rows) ([]*savepb.{{$def.Def.Name}},error) { var res []*savepb.{{$def.Def.Name}} for rows.Next() { foo := savepb.{{$def.Def.Name}}{ {{msgInitializers}} } err := rows.Scan(&foo.{{id_field}}{{range fieldvalues_no_id}},&foo.{{.}}{{end}}) if err != nil { return nil,a.Error(ctx,"fromrow-scan",err) } res = append(res,&foo) } return res,nil } /********************************************************************** * Helper to create table and columns **********************************************************************/ func (a *{{$def.Structname}}) CreateTable(ctx context.Context) error { csql := []string{ `create sequence if not exists `+a.SQLTablename+`_seq;`, `CREATE TABLE if not exists `+a.SQLTablename+` ({{id_col}} integer primary key default nextval('`+a.SQLTablename+`_seq') {{- range cols_no_id}},{{.}} {{col_sqltype .}} not null {{col_extraopts .}} {{end}});`, `CREATE TABLE if not exists `+a.SQLTablename+`_archive ({{id_col}} integer primary key default nextval('`+a.SQLTablename+`_seq') {{- range cols_no_id}},{{.}} {{col_sqltype .}} not null {{col_extraopts .}} {{end}});`, } for i,c:=range csql { _,e := a.DB.ExecContext(ctx,fmt.Sprintf("create_"+a.SQLTablename+"_%d",i),c) if e != nil { return e } } return nil } /********************************************************************** * Helper to meaningful errors **********************************************************************/ func (a *{{$def.Structname}}) Error(ctx context.Context, q string, e error) error { if e == nil { return nil } return fmt.Errorf("[table="+a.SQLTablename+", query=%s] Error: %s",q,e) }