feat: mssql and pg merge query

[bgdnxt]

closed #428

[vmihailenco]

Thanks! This looks pretty good already and is rather important given that Postgres 15 also has support for MERGE statements.

Could you try to add some tests to query_test.go? This is how we make sure the generated queries stay stable with different dialects.

[vmihailenco]

I guess in future we may want to extend the API to something like this and try to make it work the same in different RDMS:

db.NewMerge().
    WhenUpdate("MATCHED", func(q *bun.UpdateQuery) *bun.UpdateQuery {
        return q.Set("models.value = _data.value")
    }).
    WhenInsert("NOT MATCHED", func(q *bun.InsertQuery) *bun.InsertQuery {
        return q.Column("name", "value")
    }).
    WhenDelete("...")

@bgdnxt Have you considered that already by any chance?

[bgdnxt]

haven't look into pg15 yet, will try tomorrow.

[bgdnxt]

the merge query syntax between MSSQL and PG are very similar. so it is easy to add supported.

[bgdnxt]

I guess in future we may want to extend the API to something like this and try to make it work the same in different RDMS:

db.NewMerge().
    WhenUpdate("MATCHED", func(q *bun.UpdateQuery) *bun.UpdateQuery {
        return q.Set("models.value = _data.value")
    }).
    WhenInsert("NOT MATCHED", func(q *bun.InsertQuery) *bun.InsertQuery {
        return q.Column("name", "value")
    }).
    WhenDelete("...")

@bgdnxt Have you considered that already by any chance?

The update/insert query within when clause are quit different from original. could not benefit from applying a full update/inert query statement. so i just pull out the Set for updating, and Value for inserting.

now the merge query could be use as:

db.NewMerge().
    Model(&Model{}).
    With("_data", DBM.NewValues(&newModels)).
    Using("_data").
    On("?TableAlias.name = _data.name").
    WhenUpdate("MATCHED", func(q *bun.MergeQuery) *bun.MergeQuery {
          return q.Set("value = _data.value")
    }).
    WhenInsert("NOT MATCHED", func(q *bun.MergeQuery) *bun.MergeQuery {
          return q.Value("name", "_data.name").Value("value", "_data.value")
    }).
    Returning("$action")

and i still keep the func WhenExpr for writing raw expressions.

db.NewMerge().
    Model(&Model{}).
    With("_data", DBM.NewValues(&newModels)).
    Using("_data").
    On("?TableAlias.name = _data.name").
    WhenDelete("MATCHED").
    WhenExpr("NOT MATCHED THEN INSERT (name, value) VALUES (_data.name, _data.value)").
    Returning("$action")

[vmihailenco]

and i still keep the func WhenExpr for writing raw expressions.

Agreed. I would even keep the old name When.

could not benefit from applying a full update/inert query statement

Yes, but we don't have to support all options from those queries. Just use the fields you need, .e.g. q.set from UpdateQuery and q.columns from InsertQuery. That is a bit wasteful, but the API makes more sense.

The problem with current implementation is that you can't have multiple when clauses like:

    WhenInsert("NOT MATCHED AND cond1", func(q *bun.MergeQuery) *bun.MergeQuery {
          return q.Value("name", "_data.name").Value("value", "_data.value")
    }).
    WhenInsert("NOT MATCHED AND cond2", func(q *bun.MergeQuery) *bun.MergeQuery {
          return q.Value("name", "_data.name").Value("value", "_data.value")
    }).

Also the order of When/WhenUpdate/WhenInsert is not preserved which might be a problem.

I suggest to try something like this:

type MergeQuery struct {
    ...
    when []schema.QueryAppender
}

func (q *MergeQuery) When(query string, args ...interface{}) *MergeQuery {
    q.when = append(q.when, schema.SafeQuery(query, args))
    return q
}

func (q *MergeQuery) WhenInsert(query string, cb func (q *InsertQuery) *InsertQuery) *MergeQuery {
    q.when = append(q.when, whenInsert{query: query, cb: cb})
    return q
}

Then implement QueryAppender for whenInsert struct. What do you think?

[bgdnxt]

@vmihailenco thanks for the feedback, now the code it more clean.

[vmihailenco]

Perhaps we can also accept args ...interface{} as the last argument? It is a bit awkward, but would allow something like:

q.WhenInsert("MATCHED AND foo = ?", func() {...}, 123)

[vmihailenco]

@bgdnxt excellent, thank you!