Lightweight and fast SQL builder for Go language
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

657 lines
13 KiB

// Copyright 2016 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 (
"testing"
"github.com/stretchr/testify/assert"
)
type MyInt int
func TestBuilderCond(t *testing.T) {
var cases = []struct {
cond Cond
sql string
args []interface{}
}{
{
Eq{"a": 1}.And(Like{"b", "c"}).Or(Eq{"a": 2}.And(Like{"b", "g"})),
"(a=? AND b LIKE ?) OR (a=? AND b LIKE ?)",
[]interface{}{1, "%c%", 2, "%g%"},
},
{
Eq{"a": 1}.Or(Like{"b", "c"}).And(Eq{"a": 2}.Or(Like{"b", "g"})),
"(a=? OR b LIKE ?) AND (a=? OR b LIKE ?)",
[]interface{}{1, "%c%", 2, "%g%"},
},
{
Eq{"d": []string{"e", "f"}},
"d IN (?,?)",
[]interface{}{"e", "f"},
},
{
Eq{"e": Select("id").From("f").Where(Eq{"g": 1})},
"e=(SELECT id FROM f WHERE g=?)",
[]interface{}{1},
},
{
Eq{"e": Expr("SELECT id FROM f WHERE g=?", 1)},
"e=(SELECT id FROM f WHERE g=?)",
[]interface{}{1},
},
{
Like{"a", "%1"}.And(Like{"b", "%2"}),
"a LIKE ? AND b LIKE ?",
[]interface{}{"%1", "%2"},
},
{
Like{"a", "%1"}.Or(Like{"b", "%2"}),
"a LIKE ? OR b LIKE ?",
[]interface{}{"%1", "%2"},
},
{
Neq{"d": "e"}.Or(Neq{"f": "g"}),
"d<>? OR f<>?",
[]interface{}{"e", "g"},
},
{
Neq{"d": []string{"e", "f"}},
"d NOT IN (?,?)",
[]interface{}{"e", "f"},
},
{
Neq{"e": Select("id").From("f").Where(Eq{"g": 1})},
"e<>(SELECT id FROM f WHERE g=?)",
[]interface{}{1},
},
{
Neq{"e": Expr("SELECT id FROM f WHERE g=?", 1)},
"e<>(SELECT id FROM f WHERE g=?)",
[]interface{}{1},
},
{
Lt{"d": 3},
"d<?",
[]interface{}{3},
},
{
Lt{"d": 3}.And(Lt{"e": 4}),
"d<? AND e<?",
[]interface{}{3, 4},
},
{
Lt{"d": 3}.Or(Lt{"e": 4}),
"d<? OR e<?",
[]interface{}{3, 4},
},
{
Lt{"e": Select("id").From("f").Where(Eq{"g": 1})},
"e<(SELECT id FROM f WHERE g=?)",
[]interface{}{1},
},
{
Lt{"e": Expr("SELECT id FROM f WHERE g=?", 1)},
"e<(SELECT id FROM f WHERE g=?)",
[]interface{}{1},
},
{
Lte{"d": 3},
"d<=?",
[]interface{}{3},
},
{
Lte{"d": 3}.And(Lte{"e": 4}),
"d<=? AND e<=?",
[]interface{}{3, 4},
},
{
Lte{"d": 3}.Or(Lte{"e": 4}),
"d<=? OR e<=?",
[]interface{}{3, 4},
},
{
Lte{"e": Select("id").From("f").Where(Eq{"g": 1})},
"e<=(SELECT id FROM f WHERE g=?)",
[]interface{}{1},
},
{
Lte{"e": Expr("SELECT id FROM f WHERE g=?", 1)},
"e<=(SELECT id FROM f WHERE g=?)",
[]interface{}{1},
},
{
Gt{"d": 3},
"d>?",
[]interface{}{3},
},
{
Gt{"d": 3}.And(Gt{"e": 4}),
"d>? AND e>?",
[]interface{}{3, 4},
},
{
Gt{"d": 3}.Or(Gt{"e": 4}),
"d>? OR e>?",
[]interface{}{3, 4},
},
{
Gt{"e": Select("id").From("f").Where(Eq{"g": 1})},
"e>(SELECT id FROM f WHERE g=?)",
[]interface{}{1},
},
{
Gt{"e": Expr("SELECT id FROM f WHERE g=?", 1)},
"e>(SELECT id FROM f WHERE g=?)",
[]interface{}{1},
},
{
Gte{"d": 3},
"d>=?",
[]interface{}{3},
},
{
Gte{"d": 3}.And(Gte{"e": 4}),
"d>=? AND e>=?",
[]interface{}{3, 4},
},
{
Gte{"d": 3}.Or(Gte{"e": 4}),
"d>=? OR e>=?",
[]interface{}{3, 4},
},
{
Gte{"e": Select("id").From("f").Where(Eq{"g": 1})},
"e>=(SELECT id FROM f WHERE g=?)",
[]interface{}{1},
},
{
Gte{"e": Expr("SELECT id FROM f WHERE g=?", 1)},
"e>=(SELECT id FROM f WHERE g=?)",
[]interface{}{1},
},
{
Between{"d", 0, 2},
"d BETWEEN ? AND ?",
[]interface{}{0, 2},
},
{
Between{"d", 0, Expr("CAST('2003-01-01' AS DATE)")},
"d BETWEEN ? AND CAST('2003-01-01' AS DATE)",
[]interface{}{0},
},
{
Between{"d", Expr("CAST('2003-01-01' AS DATE)"), 2},
"d BETWEEN CAST('2003-01-01' AS DATE) AND ?",
[]interface{}{2},
},
{
Between{"d", Expr("CAST('2003-01-01' AS DATE)"), Expr("CAST('2003-01-01' AS DATE)")},
"d BETWEEN CAST('2003-01-01' AS DATE) AND CAST('2003-01-01' AS DATE)",
[]interface{}{},
},
{
Between{"d", 0, 2}.And(Between{"e", 3, 4}),
"d BETWEEN ? AND ? AND e BETWEEN ? AND ?",
[]interface{}{0, 2, 3, 4},
},
{
Between{"d", 0, 2}.Or(Between{"e", 3, 4}),
"d BETWEEN ? AND ? OR e BETWEEN ? AND ?",
[]interface{}{0, 2, 3, 4},
},
{
Expr("a < ?", 1),
"a < ?",
[]interface{}{1},
},
{
Expr("a < ?", 1).And(Eq{"b": 2}),
"(a < ?) AND b=?",
[]interface{}{1, 2},
},
{
Expr("a < ?", 1).Or(Neq{"b": 2}),
"(a < ?) OR b<>?",
[]interface{}{1, 2},
},
{
IsNull{"d"},
"d IS NULL",
[]interface{}{},
},
{
IsNull{"d"}.And(IsNull{"e"}),
"d IS NULL AND e IS NULL",
[]interface{}{},
},
{
IsNull{"d"}.Or(IsNull{"e"}),
"d IS NULL OR e IS NULL",
[]interface{}{},
},
{
NotNull{"d"},
"d IS NOT NULL",
[]interface{}{},
},
{
NotNull{"d"}.And(NotNull{"e"}),
"d IS NOT NULL AND e IS NOT NULL",
[]interface{}{},
},
{
NotNull{"d"}.Or(NotNull{"e"}),
"d IS NOT NULL OR e IS NOT NULL",
[]interface{}{},
},
{
NotIn("a", 1, 2).And(NotIn("b", "c", "d")),
"a NOT IN (?,?) AND b NOT IN (?,?)",
[]interface{}{1, 2, "c", "d"},
},
{
In("a", 1, 2).Or(In("b", "c", "d")),
"a IN (?,?) OR b IN (?,?)",
[]interface{}{1, 2, "c", "d"},
},
{
In("a", []int{1, 2}).Or(In("b", []string{"c", "d"})),
"a IN (?,?) OR b IN (?,?)",
[]interface{}{1, 2, "c", "d"},
},
{
In("a", Expr("select id from x where name > ?", "b")),
"a IN (select id from x where name > ?)",
[]interface{}{"b"},
},
{
In("a", []MyInt{1, 2}).Or(In("b", []string{"c", "d"})),
"a IN (?,?) OR b IN (?,?)",
[]interface{}{MyInt(1), MyInt(2), "c", "d"},
},
{
In("a", []int{}),
"0=1",
[]interface{}{},
},
{
In("a", []int{1}),
"a IN (?)",
[]interface{}{1},
},
{
In("a", []int8{}),
"0=1",
[]interface{}{},
},
{
In("a", []int8{1}),
"a IN (?)",
[]interface{}{1},
},
{
In("a", []int16{}),
"0=1",
[]interface{}{},
},
{
In("a", []int16{1}),
"a IN (?)",
[]interface{}{1},
},
{
In("a", []int32{}),
"0=1",
[]interface{}{},
},
{
In("a", []int32{1}),
"a IN (?)",
[]interface{}{1},
},
{
In("a", []int64{}),
"0=1",
[]interface{}{},
},
{
In("a", []int64{1}),
"a IN (?)",
[]interface{}{1},
},
{
In("a", []uint{}),
"0=1",
[]interface{}{},
},
{
In("a", []uint{1}),
"a IN (?)",
[]interface{}{1},
},
{
In("a", []uint8{}),
"0=1",
[]interface{}{},
},
{
In("a", []uint8{1}),
"a IN (?)",
[]interface{}{1},
},
{
In("a", []uint16{}),
"0=1",
[]interface{}{},
},
{
In("a", []uint16{1}),
"a IN (?)",
[]interface{}{1},
},
{
In("a", []uint32{}),
"0=1",
[]interface{}{},
},
{
In("a", []uint32{1}),
"a IN (?)",
[]interface{}{1},
},
{
In("a", []uint64{}),
"0=1",
[]interface{}{},
},
{
In("a", []uint64{1}),
"a IN (?)",
[]interface{}{1},
},
{
In("a", []string{}),
"0=1",
[]interface{}{},
},
{
In("a", []interface{}{}),
"0=1",
[]interface{}{},
},
{
In("a", []MyInt{}),
"0=1",
[]interface{}{},
},
{
In("a", []interface{}{1, 2, 3}).And(Eq{"b": "c"}),
"a IN (?,?,?) AND b=?",
[]interface{}{1, 2, 3, "c"},
},
{
In("a", Select("id").From("b").Where(Eq{"c": 1})),
"a IN (SELECT id FROM b WHERE c=?)",
[]interface{}{1},
},
{
NotIn("a", Expr("select id from x where name > ?", "b")),
"a NOT IN (select id from x where name > ?)",
[]interface{}{"b"},
},
{
NotIn("a", []int{}),
"0=0",
[]interface{}{},
},
{
NotIn("a", []int{1}),
"a NOT IN (?)",
[]interface{}{1},
},
{
NotIn("a", []int8{}),
"0=0",
[]interface{}{},
},
{
NotIn("a", []int8{1}),
"a NOT IN (?)",
[]interface{}{1},
},
{
NotIn("a", []int16{}),
"0=0",
[]interface{}{},
},
{
NotIn("a", []int16{1}),
"a NOT IN (?)",
[]interface{}{1},
},
{
NotIn("a", []int32{}),
"0=0",
[]interface{}{},
},
{
NotIn("a", []int32{1}),
"a NOT IN (?)",
[]interface{}{1},
},
{
NotIn("a", []int64{}),
"0=0",
[]interface{}{},
},
{
NotIn("a", []int64{1}),
"a NOT IN (?)",
[]interface{}{1},
},
{
NotIn("a", []uint{}),
"0=0",
[]interface{}{},
},
{
NotIn("a", []uint{1}),
"a NOT IN (?)",
[]interface{}{1},
},
{
NotIn("a", []uint8{}),
"0=0",
[]interface{}{},
},
{
NotIn("a", []uint8{1}),
"a NOT IN (?)",
[]interface{}{1},
},
{
NotIn("a", []uint16{}),
"0=0",
[]interface{}{},
},
{
NotIn("a", []uint16{1}),
"a NOT IN (?)",
[]interface{}{1},
},
{
NotIn("a", []uint32{}),
"0=0",
[]interface{}{},
},
{
NotIn("a", []uint32{1}),
"a NOT IN (?)",
[]interface{}{1},
},
{
NotIn("a", []uint64{}),
"0=0",
[]interface{}{},
},
{
NotIn("a", []uint64{1}),
"a NOT IN (?)",
[]interface{}{1},
},
{
NotIn("a", []interface{}{}),
"0=0",
[]interface{}{},
},
{
NotIn("a", []string{}),
"0=0",
[]interface{}{},
},
{
NotIn("a", []MyInt{}),
"0=0",
[]interface{}{},
},
{
NotIn("a", []MyInt{1, 2}),
"a NOT IN (?,?)",
[]interface{}{1, 2},
},
{
NotIn("a", []interface{}{1, 2, 3}).And(Eq{"b": "c"}),
"a NOT IN (?,?,?) AND b=?",
[]interface{}{1, 2, 3, "c"},
},
{
NotIn("a", []interface{}{1, 2, 3}).Or(Eq{"b": "c"}),
"a NOT IN (?,?,?) OR b=?",
[]interface{}{1, 2, 3, "c"},
},
{
NotIn("a", Select("id").From("b").Where(Eq{"c": 1})),
"a NOT IN (SELECT id FROM b WHERE c=?)",
[]interface{}{1},
},
{
Or(Eq{"a": 1, "b": 2}, Eq{"c": 3, "d": 4}),
"(a=? AND b=?) OR (c=? AND d=?)",
[]interface{}{1, 2, 3, 4},
},
{
Not{Eq{"a": 1, "b": 2}},
"NOT (a=? AND b=?)",
[]interface{}{1, 2},
},
{
Not{Neq{"a": 1, "b": 2}},
"NOT (a<>? AND b<>?)",
[]interface{}{1, 2},
},
{
Not{Eq{"a": 1}.And(Eq{"b": 2})},
"NOT (a=? AND b=?)",
[]interface{}{1, 2},
},
{
Not{Neq{"a": 1}.And(Neq{"b": 2})},
"NOT (a<>? AND b<>?)",
[]interface{}{1, 2},
},
{
Not{Eq{"a": 1}}.And(Neq{"b": 2}),
"NOT a=? AND b<>?",
[]interface{}{1, 2},
},
{
Not{Eq{"a": 1}}.Or(Neq{"b": 2}),
"NOT a=? OR b<>?",
[]interface{}{1, 2},
},
}
for _, k := range cases {
sql, args, err := ToSQL(k.cond)
assert.NoError(t, err)
assert.EqualValues(t, k.sql, sql)
for i := 0; i < 10; i++ {
sql2, _, err := ToSQL(k.cond)
assert.NoError(t, err)
assert.EqualValues(t, sql, sql2)
}
assert.EqualValues(t, len(args), len(k.args))
if len(args) > 0 {
for i := 0; i < len(args); i++ {
assert.EqualValues(t, k.args[i], args[i])
}
}
}
}
func TestSubquery(t *testing.T) {
subb := Select("id").From("table_b").Where(Eq{"b": "a"})
b := Select("a, b").From("table_a").Where(
Eq{
"b_id": subb,
"id": 23,
},
)
sql, args, err := b.ToSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT a, b FROM table_a WHERE b_id=(SELECT id FROM table_b WHERE b=?) AND id=?", sql)
assert.EqualValues(t, []interface{}{"a", 23}, args)
}
// https://github.com/go-xorm/xorm/issues/820
func TestExprCond(t *testing.T) {
b := Select("id").From("table1").Where(expr{sql: "a=? OR b=?", args: []interface{}{1, 2}}).Where(Or(Eq{"c": 3}, Eq{"d": 4}))
sql, args, err := b.ToSQL()
assert.NoError(t, err)
assert.EqualValues(t, "table1", b.TableName())
assert.EqualValues(t, "SELECT id FROM table1 WHERE (a=? OR b=?) AND (c=? OR d=?)", sql)
assert.EqualValues(t, []interface{}{1, 2, 3, 4}, args)
}
func TestBuilder_ToBoundSQL(t *testing.T) {
newSQL, err := Select("id").From("table").Where(In("a", 1, 2)).ToBoundSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT id FROM table WHERE a IN (1,2)", newSQL)
}
func TestBuilder_From2(t *testing.T) {
b := Select("id").From("table_b", "tb").Where(Eq{"b": "a"})
sql, args, err := b.ToSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT id FROM table_b tb WHERE b=?", sql)
assert.EqualValues(t, []interface{}{"a"}, args)
b = Select().From("table_b", "tb").Where(Eq{"b": "a"})
sql, args, err = b.ToSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT * FROM table_b tb WHERE b=?", sql)
assert.EqualValues(t, []interface{}{"a"}, args)
}
func TestBuilder_And(t *testing.T) {
b := Select("id").From("table_b", "tb").Where(Eq{"b": "a"}).And(Neq{"c": "d"})
sql, args, err := b.ToSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT id FROM table_b tb WHERE b=? AND c<>?", sql)
assert.EqualValues(t, []interface{}{"a", "d"}, args)
}
func TestBuilder_Or(t *testing.T) {
b := Select("id").From("table_b", "tb").Where(Eq{"b": "a"}).Or(Neq{"c": "d"})
sql, args, err := b.ToSQL()
assert.NoError(t, err)
assert.EqualValues(t, "SELECT id FROM table_b tb WHERE b=? OR c<>?", sql)
assert.EqualValues(t, []interface{}{"a", "d"}, args)
}