ON DUPLICATE KEY UPDATE

[Torwang1]

No description provided.

[huandu]

什么时候有更多的信息呢？ @Torwang1

[WilliamYang1992]

@huandu 哈哈，我刚好也想问这个，什么时候可以加上”on duplicate key update“的支持呢？谢谢

[huandu]

可以考虑直接使用 InsertBuilder 的 SQL() 方法来实现，毕竟一般 update 后面跟的表达式里面也没什么值得 escape 东西，一般都是固定的一个字符串。如果业务上使用觉得麻烦，可以在业务层对 InsertBuilder 做个简单封装。

[Torwang1]

现在的实现方式:

sql, args := sqlbuilder.NewInsertBuilder().
	InsertInto(quoteField(c.Task.Tablename)).
	Cols(columns...).
	Values(values...).
	Build()

sql = fmt.Sprintf("%s ON DUPLICATE KEY UPDATE %s = %v", sql, fieldname, interface{})

毕竟一般 update 后面跟的表达式里面也没什么值得 escape 东西

可能存在两个问题:

1. 注入的问题;
2. time.Time 编码是在 driver 层完成, 业务层处理 time.Time 时会遇到困难;

[Torwang1]

补充采集场景:

instance	attr_1	attr_2	attr_3
ins_1	3	29	100

其中, instance 是唯一标识, attr 来自不同的数据源.

---

需要 ON DUPLICATE KEY UPDATE

[huandu]

我明白你的意思了，不过ON DUPLICATE KEY UPDATE 这个的语法其实还有点麻烦。你这里写的是 MySQL 的语法，而 PostgreSQL 的语法是 ON CONFLICT，SQLite 的语法是 ON CONFLICT(index_column)，差异极大，比较难做成不同风格（flavor）的统一。

暂时没有想到太好的解法，一种可能性是扩展 InsertBuilder#SQL() 方法的能力，允许在里面使用变量，类似现在的 Build() 方法，这需要对底层的机制做一些升级，当然也需要对所有支持 SQL() 方法的 builder 都要进行升级。这样，业务代码自行做 ON DUPLICATE KEY UPDATE 的封装就好了。

[Torwang1]

一种可能性是扩展 InsertBuilder#SQL() 方法的能力

持保留态度, SQL() 提供了过度的灵活性

---

大致浏览了三种数据库文档(只熟悉 MYSQL, 其它两种仅读过文档), 有些想法:

1. API 可以提供 ON CONFLICT IGNORE 和 ON CONFLICT SET;
2. 数据库特有的语法, 通过 flavor 提供;

(其实, 我想表达 : 按场景提供 API & 对应的使用指引 )

[huandu]

持保留态度, SQL() 提供了过度的灵活性
我也是觉得不太好，所以现在我还没想到比较好的方案，没有动手去改。

这个库最初引入 flavor 的设计想法，就是想有限度的抹平不同 SQL 系统之间的语法细微差异，实现通用易读的拼接语法，不过面对现在这个 ON DUPLICATE KEY 已经有点为难了，这是一种非标准（并不在 SQL ISO 标准里）且各家语法都不一样（仔细看差异还很大），要想用 flavor 统一几乎不可能了，如果实现出 ON CONFLICT IGNORE 和 ON CONFLICT SET，那么 MySQL 用户肯定不太能看懂，PostgreSQL 和 SQLite 用户又觉得别扭的状况，两不讨好。

可能一种比较好的方法是让不同 flavor 的 builder 有不同的方法，这需要对 flavor 机制做很大的改动，不能像现在这样实现了，不过可能算是一种一劳永逸的做法吧，这个我会考虑下。

[croconut]

I'm resolving at the application level with an upsert tag on my structs, but this would be a nice addition. I'm not exactly sure whats blocking this, it could be a flavor specific implementation where we'd need to tag the conflict column for postgres and mysql could just tag the upsertable columns. Dunno about sqlite.

[huandu]

@croconut There is nothing blocking us to build such SQL with current API. At the application level, we can call SQL() on SelectBuilder to build any arbitrary SQL including ON DUPLICATE KEY.

I keep this issue open because I cannot find a satisfied solution to define APIs for ON DUPLICATE KEY for all kinds of SQL dialects.

[wangmir]

How about make a function like *ExceptTag similar to *ForTag?

And, we can just ignore key field which is tagged as pk to avoid this.

For the composite key.. I'm not sure.

[huandu]

How about make a function like *ExceptTag similar to *ForTag?

Can you please provide a sample to let me know what the *ExceptTag method will do? I don't quite get your idea.

[wangmir]

@huandu Currently, I'm avoiding to add id from update using tag like this,

type User struct {
	UserID           string    `json:"userID"           db:"user_id"`
	FirstName        string    `json:"firstName"        db:"first_name"        fieldtag:"mutable"`
	LastName         string    `json:"lastName"         db:"last_name"         fieldtag:"mutable"`
	CreationTime     time.Time `json:"creationTime"     db:"creation_time"     fieldtag:"mutable"`
	ModificationTime time.Time `json:"modificationTime" db:"modification_time" fieldtag:"mutable"`
}

With using for tag,

userStruct.UpdateForTag(tableUser, "mutable", userCopy)

But, when we have function like UpdateExceptTag then I can replace above like this,

type User struct {
	UserID           string    `json:"userID"           db:"user_id"        fieldtag:"pk"`
	FirstName        string    `json:"firstName"        db:"first_name"`
	LastName         string    `json:"lastName"         db:"last_name"`
	CreationTime     time.Time `json:"creationTime"     db:"creation_time"`
	ModificationTime time.Time `json:"modificationTime" db:"modification_time"`
}

[huandu]

But, when we have function like UpdateExceptTag then I can replace above like this,

@wangmir
I got your point and add a new method WithoutTag in Struct to address this issue. Code change is ready to get merged in #96. Your feedback on the change is highly appreciated.

[wangmir]

@huandu Thanks! I checked for the code, and it seems very solid. Thanks for reflecting this issue very quickly.

[wangmir]

@huandu In case of ON CONFLICT DO UPDATE,

I realized that insert builder automatically fill the arguments for the update too. Is it ur intended action?? If so, then it will be really good 👍

	builder := fileMetadataStruct.WithoutTag(entity.ImmutableFieldTags...).Update(tableFileMetadata, fileMetadata)
	builder = builder.Where(builder.Equal("file_id", fileMetadata.FileID))

	sql, _ := builder.BuildWithFlavor(sb.PostgreSQL)

	insertBuilder := fileMetadataStruct.InsertInto(tableFileMetadata, fileMetadata)
	insertBuilder.SQL("ON CONFLICT (file_id) DO ")
	insertBuilder.SQL(sql)

	insertSql, insertArgs := insertBuilder.BuildWithFlavor(sb.PostgreSQL)

So, in this code, I firstly thought that, I need to get arguments from update builder, and need to append it,
but I realized that, the insertArgs already contains the arg for the update.

I only passed parsed SQL, not the arg, so I'm shocked.

[wangmir]

@huandu Oh, it seems not, the data of args array is not properly set. So, how can I put the ON CONFLICT manually with combining updater builder? is there any way?