github.com/bingoohuang/gg@v0.0.0-20240325092523-45da7dee9335/pkg/sqx/sqx_test.go (about)

     1  package sqx_test
     2  
     3  import (
     4  	"fmt"
     5  	"testing"
     6  
     7  	"github.com/bingoohuang/gg/pkg/sqx"
     8  	"github.com/stretchr/testify/assert"
     9  )
    10  
    11  func ExampleCondition() {
    12  	type Cond struct {
    13  		Name string `cond:"name like ?" modifier:"%v%"` // like的示例
    14  		Addr string `cond:"addr = ?"`
    15  		Code int    `cond:"code > ?" zero:"-1"` // Code == -1时，忽略本字段的条件
    16  		Nooo string `cond:"-"`                  // 忽略本字段作为条件
    17  	}
    18  
    19  	ret, err := sqx.CreateSQL(`select code, name, addr, email from person order by code`, Cond{
    20  		Name: "天问一号",
    21  		Addr: "火星基地",
    22  		Code: -1,
    23  	})
    24  	fmt.Println(fmt.Sprintf("%+v", ret), err)
    25  
    26  	ret, err = sqx.CreateSQL(`select code, name, addr, email from person order by code`, Cond{
    27  		Name: "嫦娥",
    28  		Addr: "广寒宫",
    29  		Code: 100,
    30  	})
    31  	fmt.Println(fmt.Sprintf("%+v", ret), err)
    32  
    33  	ret.Append("limit ?, ?", 1, 10)
    34  	fmt.Println(fmt.Sprintf("%+v", ret), err)
    35  
    36  	// Output:
    37  	// &{Name: Q:select code, name, addr, email from person where name like ? and addr = ? order by code Vars:[%天问一号% 火星基地] Ctx:<nil> NoLog:false Timeout:0s Limit:0 ConvertOptions:[] adapted:false} <nil>
    38  	// &{Name: Q:select code, name, addr, email from person where name like ? and addr = ? and code > ? order by code Vars:[%嫦娥% 广寒宫 100] Ctx:<nil> NoLog:false Timeout:0s Limit:0 ConvertOptions:[] adapted:false} <nil>
    39  	// &{Name: Q:select code, name, addr, email from person where name like ? and addr = ? and code > ? order by code limit ?, ? Vars:[%嫦娥% 广寒宫 100 1 10] Ctx:<nil> NoLog:false Timeout:0s Limit:0 ConvertOptions:[] adapted:false} <nil>
    40  }
    41  
    42  func TestEmbeddedCondition(t *testing.T) {
    43  	type Cond1 struct {
    44  		BigName string
    45  		C       int
    46  		D       int
    47  	}
    48  
    49  	type Cond2 struct {
    50  		Cond1
    51  		E string
    52  	}
    53  
    54  	x, err := sqx.CreateSQL(`select a,b,c from t`, Cond2{Cond1: Cond1{BigName: "bb"}, E: "ee"})
    55  	assert.Nil(t, err)
    56  	assert.Equal(t, `select a, b, c from t where big_name = ? and e = ?`, x.Q)
    57  	assert.Equal(t, []interface{}{"bb", "ee"}, x.Vars)
    58  }
    59  
    60  func TestModifier(t *testing.T) {
    61  	type Cond2 struct {
    62  		E string `cond:"e like ?" modifier:"%v%"`
    63  	}
    64  
    65  	x, err := sqx.CreateSQL(`select a,b,c from t`, Cond2{E: "ee"})
    66  	assert.Nil(t, err)
    67  	assert.Equal(t, `select a, b, c from t where e like ?`, x.Q)
    68  	assert.Equal(t, []interface{}{"%ee%"}, x.Vars)
    69  }
    70  
    71  func TestManual(t *testing.T) {
    72  	s := sqx.SQL{Q: `select * from warn_template_rule`}
    73  	s.And(`source_type = ?`, "x")
    74  	s.And(`temp_source_id = ?`, "y")
    75  	s.And(`id in (?)`, []string{"a", "b", "c"})
    76  	s.Append(`order by id desc`)
    77  	assert.Equal(t, `select * from warn_template_rule where source_type = ? and temp_source_id = ? and id in (?,?,?) order by id desc`, s.Q)
    78  	assert.Equal(t, []interface{}{"x", "y", "a", "b", "c"}, s.Vars)
    79  }
    80  
    81  func TestCondition(t *testing.T) {
    82  	type cond struct {
    83  		B    string // will generate `b = ?` when B is not zero.
    84  		C    int    `cond:"c0 > ?"`           // use cond tag directly.
    85  		D    int    `cond:"c2 > ?" zero:"-1"` // use cond tag directly when B is not specified zero.
    86  		E    string `zero:"null"`
    87  		Mark string `cond:"-"` // ignore this field as condition.
    88  	}
    89  
    90  	x, err := sqx.CreateSQL(`select a,b,c from t`, cond{})
    91  	assert.Nil(t, err)
    92  	assert.Equal(t, `select a, b, c from t where c2 > ? and e = ?`, x.Q)
    93  	assert.Equal(t, []interface{}{0, ""}, x.Vars)
    94  
    95  	x, err = sqx.CreateSQL(`select a,b,c from t`, cond{E: "null"})
    96  	assert.Nil(t, err)
    97  	assert.Equal(t, `select a, b, c from t where c2 > ?`, x.Q)
    98  	assert.Equal(t, []interface{}{0}, x.Vars)
    99  
   100  	x, err = sqx.CreateSQL(`select a,b,c from t order by a`, &cond{E: "null"})
   101  	assert.Nil(t, err)
   102  	assert.Equal(t, `select a, b, c from t where c2 > ? order by a`, x.Q)
   103  	assert.Equal(t, []interface{}{0}, x.Vars)
   104  
   105  	x, err = sqx.CreateSQL(`select a,b,c from t order by a`, cond{D: -1, E: "null"})
   106  	assert.Nil(t, err)
   107  	assert.Equal(t, `select a,b,c from t order by a`, x.Q)
   108  
   109  	x, err = sqx.CreateSQL(`select a,b,c from t order by a`, cond{B: "bb", D: -1, E: "null"})
   110  	assert.Nil(t, err)
   111  	assert.Equal(t, `select a, b, c from t where b = ? order by a`, x.Q)
   112  	assert.Equal(t, []interface{}{"bb"}, x.Vars)
   113  
   114  	x, err = sqx.CreateSQL(`select a,b,c from t where a = 1 order by a`, cond{B: "bb", D: -1, E: "null"})
   115  	assert.Nil(t, err)
   116  	assert.Equal(t, `select a, b, c from t where a = 1 and b = ? order by a`, x.Q)
   117  	assert.Equal(t, []interface{}{"bb"}, x.Vars)
   118  
   119  	x, err = sqx.CreateSQL(`select a,b,c from t where a = 1 or a = 2 order by a`, cond{B: "bb", D: -1, E: "null"})
   120  	assert.Nil(t, err)
   121  	assert.Equal(t, `select a, b, c from t where (a = 1 or a = 2) and b = ? order by a`, x.Q)
   122  	assert.Equal(t, []interface{}{"bb"}, x.Vars)
   123  
   124  	x, err = sqx.CreateSQL(`select a,b,c from t where a = 1 or a = 2 order by a`, cond{C: 10, D: -1, E: "null"})
   125  	assert.Nil(t, err)
   126  	assert.Equal(t, `select a, b, c from t where (a = 1 or a = 2) and c0 > ? order by a`, x.Q)
   127  	assert.Equal(t, []interface{}{10}, x.Vars)
   128  
   129  	x.Append(`limit ?,?`, 0, 100)
   130  	assert.Equal(t, `select a, b, c from t where (a = 1 or a = 2) and c0 > ? order by a limit ?,?`, x.Q)
   131  	assert.Equal(t, []interface{}{10, 0, 100}, x.Vars)
   132  }