Skip to content

Instantly share code, notes, and snippets.

@randallmlough
Last active November 5, 2019 21:53
Show Gist options
  • Save randallmlough/cec18fb52ed9622ddb7cbf995803c969 to your computer and use it in GitHub Desktop.
Save randallmlough/cec18fb52ed9622ddb7cbf995803c969 to your computer and use it in GitHub Desktop.
Struct with tags to map to SQL statement and values with tests
// struct tags
// db:"something" - first tag will always be the name
// db:"-" - will skip the field and move on
// db:",follow" - will inspect the struct without using it's current fieldname as a column name. Useful when you want to embed a type/struct without using it's fieldname as a column
// db:",required" – if a fieldvalue is of type pointer and it is empty, it will return an error
// db:",asIs" – doesn't do anything further mapping or reflection. Takes it's fieldname and value and adds to list. Useful for json columns or []string, etc.
// db:",omitempty" - if the pointer value is nil it will not include it in the final map
// example: `db:"column_name,follow,required"`
// example: `db:"column_name,asIs,omitempty"`
// note: if a "follow" or "asIs" is present, an embeded struct will be dot notated. Ie. "something"."level_one_field"."level_two_field" etc.
type Object struct {
Name string `db:"name"`
Slice []string `db:"slice,asIs"`
Json CustomType `db:"json,asIs"`
AsIsButOmitempty *CustomType `db:"as_is_but_omitempty,asIs,omitempty"`
JsonColToMap *CustomType `db:"json_col_to_map,omitempty"`
FollowCustomType *CustomType `db:"follow_custom_type,follow,omitempty"`
NilJsonCol *CustomType `db:"nil_json_col,asIs,omitempty"`
Map map[string]interface{} `db:"map,asIs,omitempty"`
}
type CustomType struct {
Name string `json:"name"`
Age int `json:"age"`
Address Address `json:"address" db:"address,asIs"`
}
type Address struct {
Street string `json:"street"`
}
func insertQueryTest(req *Object) (stmt string, args []interface{}, err error) {
var insertObject = struct {
*Object `db:",follow,required"`
UUID string `db:"uuid"`
}{
Object: req,
UUID: "XXX-XXX-XXXX",
}
row, err := toMap(insertObject)
if err != nil {
return "", nil, err
}
cols, args := columnsValues(row)
stmt = fmt.Sprintf(`INSERT INTO %q (%s) VALUES (%s) RETURNING *`, "test", cols, cols.Placeholders())
return
}
func updateQueryTest(req *Object) (stmt string, args []interface{}, err error) {
var insertObject = struct {
*Object `db:",follow,required"`
UUID string `db:"uuid"`
}{
Object: req,
UUID: "XXX-XXX-XXXX",
}
row, err := toMap(insertObject)
if err != nil {
return "", nil, err
}
cols, args := columnsValues(row)
stmt = fmt.Sprintf(`UPDATE %q SET %s RETURNING *`, "test", cols.WithPlaceholders())
return
}
func toMap(i interface{}) (map[string]interface{}, error) {
mr := mapRow{}
if err := mr.reduce(i); err != nil {
return nil, err
}
return mr, nil
}
type mapRow map[string]interface{}
var (
ErrRequiredFieldMissing = errors.New("missing required field")
ErrUnrecognizedType = errors.New("unrecognized type")
)
func (mr mapRow) reduce(i interface{}) error {
v := reflect.Indirect(reflect.ValueOf(i))
fmt.Println(v.Kind())
switch v.Kind() {
case reflect.Ptr:
fmt.Println("is ptr type")
v = v.Elem()
if err := mr.reduce(i); err != nil {
return err
}
case reflect.Struct:
for i := 0; i < v.NumField(); i++ {
val := v.Field(i)
tf := v.Type().Field(i)
queryTag := tag.New("db", tf.Tag)
if !queryTag.Contains("-") && val.CanInterface() {
colName := tf.Name
if !queryTag.IsEmpty() && queryTag.Values()[0] != "" {
colName = queryTag.Values()[0]
}
switch val.Kind() {
case reflect.Ptr:
if !val.IsNil() {
switch val.Elem().Kind() {
case reflect.Struct:
if queryTag.Contains("follow") {
if err := mr.reduce(val.Elem().Interface()); err != nil {
return err
}
} else if queryTag.Contains("asIs") {
mr[colName] = val.Interface()
} else {
deepMap := make(mapRow)
if err := deepMap.reduce(val.Elem().Interface()); err != nil {
return err
}
for key, value := range deepMap {
mr[colName+"."+key] = value
}
}
default:
mr[colName] = val.Interface()
}
} else if queryTag.Contains("required") {
return ErrRequiredFieldMissing
} else if !queryTag.Contains("omitempty") {
mr[colName] = nil
}
case reflect.Slice, reflect.Map:
if !val.IsNil() {
mr[colName] = val.Interface()
} else if !queryTag.Contains("omitempty") {
mr[colName] = nil
}
case reflect.Struct:
if queryTag.Contains("follow") {
if err := mr.reduce(val.Interface()); err != nil {
return err
}
} else if queryTag.Contains("asIs") {
mr[colName] = val.Interface()
} else {
deepMap := make(mapRow)
if err := deepMap.reduce(val.Interface()); err != nil {
return err
}
for key, value := range deepMap {
mr[colName+"."+key] = value
}
}
default:
mr[colName] = val.Interface()
}
}
}
default:
return ErrUnrecognizedType
}
return nil
}
func columnsValues(row map[string]interface{}) (cols cols, args []interface{}) {
for key, value := range row {
tmp := strings.Split(key, ".")
quotedKeys := make([]string, len(tmp), len(tmp))
for i, dotKey := range tmp {
quotedKeys[i] = fmt.Sprintf("%q", dotKey)
}
cols = append(cols, strings.Join(quotedKeys, "."))
args = append(args, value)
}
return
}
type cols []string
func (c cols) String() string {
return strings.Join(c, ",")
}
func (c cols) WithPlaceholders() cols {
for idx := 1; idx <= len(c); idx++ {
c[idx-1] += fmt.Sprintf("=$%d", idx)
}
return c
}
func (c cols) Placeholders() placeholders {
var placeholders []string
if len(c) == 0 {
return placeholders
}
for idx := 1; idx <= len(c); idx++ {
placeholders = append(placeholders, fmt.Sprintf("$%d", idx))
}
return placeholders
}
type placeholders []string
func (p placeholders) String() string {
return strings.Join(p, ",")
}
func Test_insertQueryTest(t *testing.T) {
pointerType := &CustomType{
Name: "I'm a pointer",
Age: 999999999999,
Address: Address{
Street: "000 the clouds",
},
}
type args struct {
req *Object
}
tests := []struct {
name string
args args
wantStmt string
wantArgs []interface{}
wantErr bool
}{
{
name: "basic",
args: args{
req: &Object{
Name: "basic",
Slice: []string{"one", "two", "three"},
Json: CustomType{
Name: "steve",
Age: 30,
Address: Address{
Street: "555 fake street",
},
},
Map: nil, // will not appear in statement
},
},
wantStmt: `INSERT INTO "test" ("name","slice","json","uuid") VALUES ($1,$2,$3,$4) RETURNING *`,
wantArgs: []interface{}{
"basic",
[]string{"one", "two", "three"},
CustomType{
Name: "steve",
Age: 30,
Address: Address{
Street: "555 fake street",
},
},
"XXX-XXX-XXXX", // UUID from function
},
wantErr: false,
},
{
name: "a little more complex",
args: args{
req: &Object{
Name: "bigger struct",
Slice: nil,
Json: CustomType{
Name: "steve",
Age: 30,
Address: Address{
Street: "555 fake street",
},
},
AsIsButOmitempty: pointerType,
JsonColToMap: &CustomType{
Name: "johnny",
Age: 40,
Address: Address{
Street: "222 another fake street",
},
},
FollowCustomType: &CustomType{
Name: "bob", // two "names" will appear in final statement. A lower cased "name" and title cased "Name"
Age: 50,
Address: Address{
Street: "111 no address",
},
},
NilJsonCol: nil,
Map: map[string]interface{}{"fake data": 34234234},
},
},
wantStmt: `INSERT INTO "test" ("name","as_is_but_omitempty","json_col_to_map"."Age","Name","Age","uuid","slice","json","json_col_to_map"."Name","json_col_to_map"."address","address","map") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) RETURNING *`,
wantArgs: []interface{}{
"bigger struct",
pointerType,
40,
"bob",
50,
"XXX-XXX-XXXX",
nil,
CustomType{
Name: "steve",
Age: 30,
Address: Address{
Street: "555 fake street",
},
},
"johnny",
Address{
Street: "222 another fake street",
},
Address{
Street: "111 no address",
},
map[string]interface{}{"fake data": 34234234},
},
wantErr: false,
},
{
name: "error because of missing request",
args: args{
req: nil,
},
wantErr: true,
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
gotStmt, gotArgs, err := insertQueryTest(tt.args.req)
if (err != nil) != tt.wantErr {
t.Errorf("insertQueryTest() error = %v, wantErr %v", err, tt.wantErr)
return
}
fmt.Println("stmt:",gotStmt)
fmt.Println("args:",gotArgs)
// The exact sequencing may differ from what "wantStmt" since GO doesnt guarantee order.
// however, the key/value pair from stmt to arguments is correct.
//if gotStmt != tt.wantStmt {
// t.Errorf("insertQueryTest() gotStmt = %v, want %v", gotStmt, tt.wantStmt)
//}
//if !reflect.DeepEqual(gotArgs, tt.wantArgs) {
// t.Errorf("insertQueryTest() gotArgs = %v, want %v", gotArgs, tt.wantArgs)
//}
})
}
}
func Test_updateQueryTest(t *testing.T) {
type args struct {
req *Object
}
tests := []struct {
name string
args args
wantStmt string
wantArgs []interface{}
wantErr bool
}{
{
name: "basic",
args: args{
req: &Object{
Name: "basic",
Slice: []string{"one", "two", "three"},
Json: CustomType{
Name: "steve",
Age: 30,
Address: Address{
Street: "555 fake street",
},
},
Map: nil, // will not appear in statement
},
},
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
gotStmt, gotArgs, err := updateQueryTest(tt.args.req)
if (err != nil) != tt.wantErr {
t.Errorf("updateQueryTest() error = %v, wantErr %v", err, tt.wantErr)
return
}
fmt.Println("stmt:",gotStmt)
fmt.Println("args:",gotArgs)
// The exact sequencing may differ from what "wantStmt" since GO doesnt guarantee order.
// however, the key/value pair from stmt to arguments is correct.
})
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment