sql – GoLang,REST,PATCH和构建UPDATE查询

几天之后,我一直在努力研究如何在Go REST API中继续处理PATCH请求,直到我找到了一个已填充的 article about using pointers and omitempty tag并且工作正常.很好,直到我意识到我仍然需要构建一个UPDATE SQL查询.

我的结构看起来像这样：

type Resource struct {
    Name        *string `json:"name,omitempty"        sql:"resource_id"`
    Description *string `json:"description,omitempty" sql:"description"`
}

我期待包含这样一个请求主体的PATCH / resources / {resource-id}请求：

{"description":"Some new description"}

在我的处理程序中,我将以这种方式构建Resource对象(忽略导入,忽略错误处理)：

var resource Resource
resourceID,_ := mux.Vars(r)["resource-id"]

d := json.NewDecoder(r.Body)
d.Decode(&resource)

// at this point our resource object should only contain
// the Description field with the value from JSON in request body

现在,对于正常的UPDATE(PUT请求),我会这样做(简化)：

stmt,_ := db.Prepare(`UPDATE resources SET description = ?,name = ? WHERE resource_id = ?`)
res,_ := stmt.Exec(resource.Description,resource.Name,resourceID)

PATCH和omitempty标记的问题是该对象可能缺少多个属性,因此我不能只使用硬编码字段和占位符来准备语句…我将不得不动态地构建它.

这里有我的问题：我如何动态构建这样的UPDATE查询？在最好的情况下,我需要一些解决方案来识别设置属性,获取他们的SQL字段名称(可能来自标签)然后我应该能够构建UPDATE查询.我知道我可以使用反射来获取对象属性,但不知道如何获取他们的sql标签名称,当然我希望尽可能避免在这里使用反射…或者我可以简单地检查每个属性它不是没有,但在现实生活中,结构比这里提供的示例大得多……

有人可以帮我这个吗？有人已经有必要解决相同/类似的情况吗？

解：

基于这里的答案,我能够提出这个抽象的解决方案. SQLPatches方法从给定的struct构建SQLPatch结构(因此没有特定的具体结构)：

import (
    "fmt"
    "encoding/json"
    "reflect"
    "strings"
)

const tagname = "sql"

type SQLPatch struct {
    Fields []string
    Args   []interface{}
}

func SQLPatches(resource interface{}) SQLPatch {
    var sqlPatch SQLPatch
    rType := reflect.TypeOf(resource)
    rVal := reflect.ValueOf(resource)
    n := rType.NumField()

    sqlPatch.Fields = make([]string,n)
    sqlPatch.Args = make([]interface{},n)

    for i := 0; i < n; i++ {
        fType := rType.Field(i)
        fVal := rVal.Field(i)
        tag := fType.Tag.Get(tagname)

        // skip nil properties (not going to be patched),skip unexported fields,skip fields to be skipped for SQL
        if fVal.IsNil() || fType.PkgPath != "" || tag == "-" {
            continue
        }

        // if no tag is set,use the field name
        if tag == "" {
            tag = fType.Name
        }
        // and make the tag lowercase in the end
        tag = strings.ToLower(tag)

        sqlPatch.Fields = append(sqlPatch.Fields,tag+" = ?")

        var val reflect.Value
        if fVal.Kind() == reflect.Ptr {
            val = fVal.Elem()
        } else {
            val = fVal
        }

        switch val.Kind() {
        case reflect.Int,reflect.Int8,reflect.Int16,reflect.Int32,reflect.Int64:
            sqlPatch.Args = append(sqlPatch.Args,val.Int())
        case reflect.String:
            sqlPatch.Args = append(sqlPatch.Args,val.String())
        case reflect.Bool:
            if val.Bool() {
                sqlPatch.Args = append(sqlPatch.Args,1)
            } else {
                sqlPatch.Args = append(sqlPatch.Args,0)
            }
        }
    }

    return sqlPatch
}

然后我可以简单地这样称呼它：

type Resource struct {
    Description *string `json:"description,omitempty"`
    Name *string `json:"name,omitempty"`
}

func main() {
    var r Resource

    json.Unmarshal([]byte(`{"description": "new description"}`),&r)
    sqlPatch := SQLPatches(r)

    data,_ := json.Marshal(sqlPatch)
    fmt.Printf("%sn",data)
}

你可以在Go Playground检查它.我看到的唯一问题是我分配了传递结构中字段数量的两个切片,可能是10,即使我可能只想在最后修补一个属性导致分配比需要更多的内存……任何想法如何避免这种情况？

解决方法

The difference between the PUT and PATCH requests is reflected in the way the server processes the enclosed entity to modify the resource identified by the Request-URI. In a PUT request,the enclosed entity is considered to be a modified version of the resource stored on the origin server,and the client is requesting that the stored version be replaced. With PATCH,however,the enclosed entity contains a set of instructions describing how a resource currently residing on the origin server should be modified to produce a new version. The PATCH method affects the resource identified by the Request-URI,and it also MAY have side effects on other resources; i.e.,new resources may be created,or existing ones modified,by the application of a PATCH.

例如：

[
    { "op": "test","path": "/a/b/c","value": "foo" },{ "op": "remove","path": "/a/b/c" },{ "op": "add","value": [ "foo","bar" ] },{ "op": "replace","value": 42 },{ "op": "move","from": "/a/b/c","path": "/a/b/d" },{ "op": "copy","from": "/a/b/d","path": "/a/b/e" }
]

这组指令应该可以更轻松地构建更新查询.

编辑

这就是obtain sql tags的方法,但你必须使用反射：

type Resource struct {
        Name        *string `json:"name,omitempty"        sql:"resource_id"`
        Description *string `json:"description,omitempty" sql:"description"`
}

sp := "sort of string"
r := Resource{Description: &sp}
rt := reflect.TypeOf(r) // reflect.Type
rv := reflect.ValueOf(r) // reflect.Value

for i := 0; i < rv.NumField(); i++ { // Iterate over all the fields
    if !rv.Field(i).IsNil() { // Check it is not nil

        // Here you would do what you want to having the sql tag.
        // Creating the query would be easy,however
        // not sure you would execute the statement

        fmt.Println(rt.Field(i).Tag.Get("sql")) // Output: description
    }
}

我知道你不想使用反射,但是当你评论状态时,这可能是比前一个更好的答案.

编辑2：

关于分配 – 阅读Effective Go about Data structures and Allocation的本指南：

// Here you are allocating an slice of 0 length with a capacity of n
sqlPatch.Fields = make([]string,n)
sqlPatch.Args = make([]interface{},n)

使用make(类型,长度,容量(可选))

请考虑以下示例：

// newly allocated zeroed value with Composite Literal 
// length: 0
// capacity: 0
testSlice := []int{}
fmt.Println(len(testSlice),cap(testSlice)) // 0 0
fmt.Println(testSlice) // []

// newly allocated non zeroed value with make   
// length: 0
// capacity: 10
testSlice = make([]int,10)
fmt.Println(len(testSlice),cap(testSlice)) // 0 10
fmt.Println(testSlice) // []

// newly allocated non zeroed value with make   
// length: 2
// capacity: 4
testSlice = make([]int,2,4)
fmt.Println(len(testSlice),cap(testSlice)) // 2 4
fmt.Println(testSlice) // [0 0]

在您的情况下,可能需要以下内容：

// Replace this
sqlPatch.Fields = make([]string,n)

// With this or simple omit the capacity in make above
sqlPatch.Fields = []string{}
sqlPatch.Args = []interface{}{}

// The allocation will go as follow: length - capacity
testSlice := []int{} // 0 - 0
testSlice = append(testSlice,1) // 1 - 2
testSlice = append(testSlice,1) // 2 - 2   
testSlice = append(testSlice,1) // 3 - 4   
testSlice = append(testSlice,1) // 4 - 4   
testSlice = append(testSlice,1) // 5 - 8

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!