github.com/bingoohuang/gg@v0.0.0-20240325092523-45da7dee9335/pkg/sqlparse/sqlparser/dbtype_test.go (about)

     1  package sqlparser
     2  
     3  import (
     4  	"errors"
     5  	"testing"
     6  
     7  	_ "github.com/go-sql-driver/mysql"
     8  	_ "github.com/jackc/pgx/v4/stdlib"
     9  	"github.com/stretchr/testify/assert"
    10  )
    11  
    12  func TestConvertDBType(t *testing.T) {
    13  	const q0 = "select `name` from `user` where `min` >= ? and name like 'abc?' order by age"
    14  	const q1 = `select "name" from "user" where "min" >= $1 and "name" like 'abc?' order by "age"`
    15  	const q2 = `select count(*) as "cnt" from "user" where "min" >= $1 and "name" like 'abc?'`
    16  
    17  	r, err := Kingbase.Convert(q0)
    18  	assert.Nil(t, err)
    19  	assert.NotNil(t, r)
    20  	assert.Equal(t, q1, r.ConvertQuery())
    21  
    22  	const q00 = `select * from user where name in (?, ?)`
    23  	r, err = Kingbase.Convert(q00)
    24  	assert.Nil(t, err)
    25  	q, _ := r.PickArgs([]interface{}{"a", "b", "c"})
    26  	assert.Equal(t, `select * from "user" where "name" in ($1, $2, $3)`, q)
    27  
    28  	v := &Paging{PageSeq: 1, PageSize: 20, RowsCount: CreateCountingQuery}
    29  	r, err = Kingbase.Convert(q0, WithPaging(v))
    30  	assert.Nil(t, err)
    31  	assert.Equal(t, q1+" limit $2 offset $3", r.ConvertQuery())
    32  	assert.Equal(t, q2, r.CountingQuery)
    33  	assert.Equal(t, []interface{}{20, 0}, r.ExtraArgs)
    34  
    35  	const q3 = `insert into t (a, b, c, d, e, f,g) values(?, ?, ?, ?, ?, ?,?) on duplicate key update a=values(a), b=values(b), c=values(c),d=values(c)`
    36  
    37  	r, err = Kingbase.Convert(q3)
    38  	assert.True(t, errors.Is(err, ErrSyntax))
    39  
    40  	// 在占位符数量与字段数量不匹配时，自动调整占位符数量
    41  	const q40 = `insert into t (a, b, c) values(?,?)`
    42  	const q41 = `insert into "t"("a", "b", "c") values ($1, $2, $3)`
    43  	r, err = Kingbase.Convert(q40)
    44  	assert.Nil(t, err)
    45  	assert.Equal(t, q41, r.ConvertQuery())
    46  	// 在占位符数量与字段数量不匹配时，自动调整占位符数量
    47  	const q50 = `insert into t (a, b, c) values(?,?,?,?)`
    48  	r, err = Kingbase.Convert(q50)
    49  	assert.Nil(t, err)
    50  	assert.Equal(t, ByPlaceholder, r.BindMode)
    51  	assert.Equal(t, q41, r.ConvertQuery())
    52  
    53  	const q60 = `insert into t (a, b, c) values(':a', ':b', ':c')`
    54  	const q62 = `insert into t (a, b, c) values(:a, :b, :c)`
    55  	const q63 = `insert into t (a, b, c) values(:?)`
    56  	const q61 = `insert into "t"("a", "b", "c") values ($1, $2, $3)`
    57  	r, err = Kingbase.Convert(q60)
    58  	assert.Nil(t, err)
    59  	assert.Equal(t, []string{"a", "b", "c"}, r.VarNames)
    60  	assert.Equal(t, q61, r.ConvertQuery())
    61  
    62  	r, err = Kingbase.Convert(q62)
    63  	assert.Nil(t, err)
    64  	assert.Equal(t, []string{"a", "b", "c"}, r.VarNames)
    65  	assert.Equal(t, ByName, r.BindMode)
    66  	assert.Equal(t, q61, r.ConvertQuery())
    67  
    68  	r, err = Kingbase.Convert(q63)
    69  	assert.Nil(t, err)
    70  	assert.Equal(t, []string{"a", "b", "c"}, r.VarNames)
    71  	assert.Equal(t, ByName, r.BindMode)
    72  	assert.Equal(t, q61, r.ConvertQuery())
    73  
    74  	const q70 = `insert into t (a, b, c) values(':2', ':1', ':3')` // b,a,c
    75  	r, err = Kingbase.Convert(q70)
    76  	assert.Nil(t, err)
    77  	assert.Equal(t, []int{2, 1, 3}, r.VarPoses)
    78  	assert.Equal(t, q61, r.ConvertQuery())
    79  
    80  	const q71 = `insert into t (a, b, c) values(:1, :2, :3)`
    81  	r, err = Kingbase.Convert(q71)
    82  	assert.Nil(t, err)
    83  	assert.Equal(t, []int{1, 2, 3}, r.VarPoses)
    84  	assert.Equal(t, BySeq, r.BindMode)
    85  	assert.Equal(t, q61, r.ConvertQuery())
    86  
    87  	const q72 = `insert into t (a, b, c) values(:?, :?, :?)`
    88  	r, err = Kingbase.Convert(q72)
    89  	assert.Nil(t, err)
    90  	assert.Equal(t, []string{"a", "b", "c"}, r.VarNames)
    91  	assert.Equal(t, ByName, r.BindMode)
    92  	assert.Equal(t, q61, r.ConvertQuery())
    93  
    94  	const q73 = `update t set a = :?, b = :? where c > :? and d = :?`
    95  	const q74 = `update "t" set "a" = $1, "b" = $2 where "c" > $3 and "d" = $4`
    96  	r, err = Kingbase.Convert(q73)
    97  	assert.Nil(t, err)
    98  	assert.Equal(t, []string{"a", "b", "c", "d"}, r.VarNames)
    99  	assert.Equal(t, ByName, r.BindMode)
   100  	assert.Equal(t, q74, r.ConvertQuery())
   101  }