sql - GoLang、REST、PATCH 和构建 UPDATE 查询

Question

几天以来，我一直在努力解决如何在 Go REST API 中处理 PATCH 请求，直到找到 article about using pointers and omitempty tag我已经填充并且工作正常。很好，直到我意识到我仍然需要构建一个 UPDATE SQL 查询。

我的 struct 看起来像这样:

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 查询。我知道我可以使用 reflection 来获取对象属性，但不知道如何获取它们的 sql 标记名称，当然我想尽可能避免在此处使用反射...或者我可以简单地检查每个属性是否不是 nil，但在现实生活中，结构比此处提供的示例大得多...

有人可以帮我解决这个问题吗？是否有人已经必须解决相同/相似的情况？

解决方案:

根据此处的答案，我能够提出这个抽象的解决方案。 SQLPatches 方法从给定的结构构建 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, 0, n)
    sqlPatch.Args = make([]interface{}, 0, 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("%s\n", data)
}

您可以在 Go Playground 上查看.我在这里看到的唯一问题是，我为两个 slice 分配了传递的结构中的字段数量，可能是 10，即使我最终可能只想修补一个属性，从而导致分配比需要更多的内存。 . 知道如何避免这种情况吗？

Answer 1

我最近遇到了同样的问题。关于 PATCH 并环顾四周发现 this article .它还引用了 RFC 5789它说:

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", "path": "/a/b/c", "value": [ "foo", "bar" ] },
    { "op": "replace", "path": "/a/b/c", "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 关于 Data structures and Allocation 的指南:

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

带make(Type, Length, Capacity (optional))

考虑以下示例:

// 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, 0, 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, 0, n)
sqlPatch.Args = make([]interface{}, 0, 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