forked from xorm/builder
119 lines
4.1 KiB
Go
119 lines
4.1 KiB
Go
// Copyright 2018 The Xorm Authors. All rights reserved.
|
|
// Use of this source code is governed by a BSD-style
|
|
// license that can be found in the LICENSE file.
|
|
|
|
package builder
|
|
|
|
import (
|
|
"errors"
|
|
"testing"
|
|
|
|
"github.com/stretchr/testify/assert"
|
|
)
|
|
|
|
func TestBuilder_OnConflict4Oracle(t *testing.T) {
|
|
|
|
}
|
|
|
|
func TestBuilder_OnConflict4Mysql(t *testing.T) {
|
|
// should fail to build
|
|
sql, err := Dialect(MYSQL).Insert(Eq{
|
|
"a": "value", "b": 1, "c": 10,
|
|
}).Into("table1").OnConflict(Eq{}, []string{"id"}).ToBoundSQL()
|
|
assert.Error(t, err)
|
|
assert.True(t, errors.Is(err, ErrInvalidOnConflictSupport))
|
|
|
|
// should fail to build
|
|
sql, err = Dialect(MYSQL).Insert(Eq{
|
|
"a": "value", "b": 1, "c": 10,
|
|
}).Into("table1").OnConflict(Eq{}, "id").ToBoundSQL()
|
|
assert.Error(t, err)
|
|
assert.True(t, errors.Is(err, ErrInvalidOnConflictSupport))
|
|
|
|
// simple
|
|
sql, err = Dialect(MYSQL).Insert(Eq{
|
|
"a": "value", "b": 1, "c": 10,
|
|
}).Into("table1").OnConflict(Eq{
|
|
"a": "value", "b": 1,
|
|
}).ToBoundSQL()
|
|
assert.NoError(t, err)
|
|
assert.EqualValues(t, "INSERT INTO table1 (a,b,c) VALUES ('value',1,10) ON DUPLICATE KEY UPDATE a='value',b=1", sql)
|
|
|
|
// use expressions
|
|
sql, err = Dialect(MYSQL).Insert(Eq{
|
|
"a": "value", "b": 1, "c": 10,
|
|
}).Into("table1").OnConflict(Eq{
|
|
"a": "value", "b": Expr("max(c) + ?", 1), "f": Incr(2),
|
|
}).ToBoundSQL()
|
|
assert.NoError(t, err)
|
|
assert.EqualValues(t, "INSERT INTO table1 (a,b,c) VALUES ('value',1,10) ON DUPLICATE KEY UPDATE a='value',b=(max(c) + 1),f=f+2", sql)
|
|
|
|
// use overwrite
|
|
sql, err = Dialect(MYSQL).Insert(Eq{
|
|
"a": "value", "b": 1, "c": 10,
|
|
}).Into("table1").OnConflict(Eq{
|
|
"a": ConflictOverwrite("a"), "b": ConflictOverwrite("a"), "f": Incr(2),
|
|
}).ToBoundSQL()
|
|
assert.NoError(t, err)
|
|
assert.EqualValues(t, "INSERT INTO table1 (a,b,c) VALUES ('value',1,10) ON DUPLICATE KEY UPDATE a=VALUES(a),b=VALUES(a),f=f+2", sql)
|
|
|
|
// use sub-select
|
|
sql, err = Dialect(MYSQL).Insert("a,b,c").Into("table1").
|
|
OnConflict(Eq{
|
|
"a": "value", "b": Expr("max(c) + ?", 1), "f": Incr(2),
|
|
}).Select("a", "b", "max(c)").From("table2").GroupBy("c").ToBoundSQL()
|
|
assert.NoError(t, err)
|
|
assert.EqualValues(t, "INSERT INTO table1 (a,b,c) SELECT a,b,max(c) FROM table2 GROUP BY c ON DUPLICATE KEY UPDATE a='value',b=(max(c) + 1),f=f+2", sql)
|
|
}
|
|
|
|
func TestBuilder_OnConflict4PostgresLike(t *testing.T) {
|
|
// simple
|
|
sql, err := Dialect(POSTGRES).Insert(Eq{
|
|
"a": "value", "b": 1, "c": 10,
|
|
}).Into("table1").OnConflict(Eq{
|
|
"a": "value", "b": 1,
|
|
}).ToBoundSQL()
|
|
assert.NoError(t, err)
|
|
assert.EqualValues(t, "INSERT INTO table1 (a,b,c) VALUES ('value',1,10) ON CONFLICT DO UPDATE SET a='value',b=1", sql)
|
|
|
|
// simple with specific columns
|
|
sql, err = Dialect(POSTGRES).Insert(Eq{
|
|
"a": "value", "b": 1, "c": 10,
|
|
}).Into("table1").OnConflict(Eq{
|
|
"a": "value", "b": 1,
|
|
}, "id").ToBoundSQL()
|
|
assert.NoError(t, err)
|
|
assert.EqualValues(t, "INSERT INTO table1 (a,b,c) VALUES ('value',1,10) ON CONFLICT (id) DO UPDATE SET a='value',b=1", sql)
|
|
|
|
// use expressions
|
|
sql, err = Dialect(POSTGRES).Insert(Eq{
|
|
"a": "value", "b": 1, "c": 10,
|
|
}).Into("table1").OnConflict(Eq{
|
|
"a": "value", "b": Expr("max(c) + ?", 1), "f": Incr(2),
|
|
}).ToBoundSQL()
|
|
assert.NoError(t, err)
|
|
assert.EqualValues(t, "INSERT INTO table1 (a,b,c) VALUES ('value',1,10) ON CONFLICT DO UPDATE SET a='value',b=(max(c) + 1),f=f+2", sql)
|
|
|
|
// use overwrite
|
|
sql, err = Dialect(POSTGRES).Insert(Eq{
|
|
"a": "value", "b": 1, "c": 10,
|
|
}).Into("table1").OnConflict(Eq{
|
|
"a": ConflictOverwrite("a"), "b": ConflictOverwrite("a"), "f": Incr(2),
|
|
}).ToBoundSQL()
|
|
assert.NoError(t, err)
|
|
assert.EqualValues(t, "INSERT INTO table1 (a,b,c) VALUES ('value',1,10) ON CONFLICT DO UPDATE SET a=EXCLUDED.a,b=EXCLUDED.a,f=f+2", sql)
|
|
|
|
// use sub-select
|
|
sql, err = Dialect(POSTGRES).Insert("a,b,c").Into("table1").
|
|
OnConflict(Eq{
|
|
"a": "value", "b": Expr("max(c) + ?", 1), "f": Incr(2),
|
|
}).Select("a", "b", "max(c)").From("table2").GroupBy("c").ToBoundSQL()
|
|
assert.NoError(t, err)
|
|
assert.EqualValues(t, "INSERT INTO table1 (a,b,c) SELECT a,b,max(c) FROM table2 GROUP BY c ON CONFLICT DO UPDATE SET a='value',b=(max(c) + 1),f=f+2", sql)
|
|
|
|
}
|
|
|
|
func TestBuilder_OnConflict4Mssql(t *testing.T) {
|
|
|
|
}
|