package test import ( "git.sxidc.com/go-framework/baize/framework/core/infrastructure/database/sql" "git.sxidc.com/go-tools/utils/strutils" "git.sxidc.com/go-tools/utils/template" "github.com/pkg/errors" "math/rand" "testing" "time" ) func TestDatabaseSqlConditions(t *testing.T) { conditions := sql.NewConditions(). AddCondition("name1 = ?", "foo"). Equal("age", 20). Like("name2", "%foo%"). In("id1", []string{"111", "222"}). NotIn("id2", []string{"33", "444"}). Not("id3", "555"). LessThan("num1", 10). LessThanAndEqual("num2", 20). GreaterThan("num3", 30). GreaterThanAndEqual("num4", 40) exceptQueries := []string{ `name1 = ?`, `"age" = ?`, `"name2" LIKE ?`, `"id1" IN ?`, `"id2" NOT IN ?`, `"id3" != ?`, `"num1" < ?`, `"num2" <= ?`, `"num3" > ?`, `"num4" >= ?`, } exceptArgs := []any{ "foo", 20, "%foo%", []string{"111", "222"}, []string{"33", "444"}, "555", 10, 20, 30, 40, } exceptAnd := `name1 = ? AND "age" = ? AND "name2" LIKE ? AND "id1" IN ? AND "id2" NOT IN ? AND "id3" != ? AND "num1" < ? AND "num2" <= ? AND "num3" > ? AND "num4" >= ?` for i, query := range conditions.Queries() { if exceptQueries[i] != query { t.Fatalf("%+v\n", errors.Errorf("Query Error: except %v, actural %v", exceptQueries[i], query)) } } for i, arg := range conditions.Args() { argStringSlice, ok := arg.([]string) if ok { for j, stringArg := range argStringSlice { exceptArg, ok := exceptArgs[i].([]string) if !ok { t.Fatalf("%+v\n", errors.New("参数类型不匹配")) } if exceptArg[j] != stringArg { t.Fatalf("%+v\n", errors.Errorf("Args Error: except %v, actural %v", exceptQueries[i], stringArg)) } } continue } if exceptArgs[i] != arg { t.Fatalf("%+v\n", errors.Errorf("Args Error: except %v, actural %v", exceptQueries[i], arg)) } } if conditions.And() != exceptAnd { t.Fatalf("%+v\n", errors.Errorf("And Error:\nexcept %v\nactural %v\n", exceptAnd, conditions.And())) } } func TestDatabaseSqlTableRow(t *testing.T) { tableRow := sql.NewTableRow(). Add("name", "test"). Add("age", 20) exceptColumns := []string{ `"name"`, `"age"`, } exceptValues := []any{ "test", 20, } for i, column := range tableRow.Columns() { if exceptColumns[i] != column { t.Fatalf("%+v\n", errors.Errorf("Column Error: except %v, actural %v", exceptColumns[i], column)) } } for i, value := range tableRow.Values() { if exceptValues[i] != value { t.Fatalf("%+v\n", errors.Errorf("Value Error: except %v, actural %v", exceptValues[i], value)) } } } func TestDatabaseSqlResult(t *testing.T) { timeResult := time.Now().Local() timeStringResult := time.Now().Local().Format("2006-01-02T15:04:05") + "+08:00" stringResult := strutils.SimpleUUID() boolResult := rand.Intn(2) == 0 intResult := rand.Int() int8Result := int8(rand.Int()) int16Result := int16(rand.Int()) int32Result := int32(rand.Int()) int64Result := int64(rand.Int()) uintResult := uint(rand.Int()) uint8Result := uint8(rand.Int()) uint16Result := uint16(rand.Int()) uint32Result := uint32(rand.Int()) uint64Result := uint64(rand.Int()) float32Result := rand.Float32() float64Result := rand.Float64() result := sql.Result{ "timeStr": timeStringResult, "time": timeResult, "string": stringResult, "bool": boolResult, "int": intResult, "int8": int8Result, "int16": int16Result, "int32": int32Result, "int64": int64Result, "uint": uintResult, "uint8": uint8Result, "uint16": uint16Result, "uint32": uint32Result, "uint64": uint64Result, "float32": float32Result, "float64": float64Result, } if result.ColumnValueTime("timeStr").Format("2006-01-02T15:04:05")+"+08:00" != timeStringResult { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", timeStringResult, result.ColumnValueTime("timeStr"))) } if !result.ColumnValueTime("time").Equal(timeResult) { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", timeResult, result.ColumnValueTime("time"))) } if result.ColumnValueString("string") != stringResult { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", stringResult, result.ColumnValueString("string"))) } if result.ColumnValueBool("bool") != boolResult { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", boolResult, result.ColumnValueBool("bool"))) } if result.ColumnValueInt("int") != intResult { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", intResult, result.ColumnValueInt("int"))) } if result.ColumnValueInt8("int8") != int8Result { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", int8Result, result.ColumnValueInt8("int8"))) } if result.ColumnValueInt16("int16") != int16Result { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", int16Result, result.ColumnValueInt16("int16"))) } if result.ColumnValueInt32("int32") != int32Result { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", int32Result, result.ColumnValueInt32("int32"))) } if result.ColumnValueInt64("int64") != int64Result { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", int64Result, result.ColumnValueInt64("int64"))) } if result.ColumnValueUint("uint") != uintResult { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", uintResult, result.ColumnValueUint("uint"))) } if result.ColumnValueUint8("uint8") != uint8Result { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", uint8Result, result.ColumnValueUint8("uint8"))) } if result.ColumnValueUint16("uint16") != uint16Result { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", uint16Result, result.ColumnValueUint16("uint16"))) } if result.ColumnValueUint32("uint32") != uint32Result { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", uint32Result, result.ColumnValueUint32("uint32"))) } if result.ColumnValueUint64("uint64") != uint64Result { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", uint64Result, result.ColumnValueUint64("uint64"))) } if result.ColumnValueFloat32("float32") != float32Result { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", float32Result, result.ColumnValueFloat32("float32"))) } if result.ColumnValueFloat64("float64") != float64Result { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", float64Result, result.ColumnValueFloat64("float64"))) } } type Info struct { ID string `sqlresult:"column:id"` Name string `sqlresult:"column:name"` Age int32 `sqlresult:"column:age"` EnterTime time.Time `sqlresult:"column:enter_time"` EnterTimeStr string `sqlresult:"column:enter_time"` } func TestDatabaseParseSqlResult(t *testing.T) { id1 := strutils.SimpleUUID() name1 := strutils.SimpleUUID() age1 := rand.Int31() enterTime1 := time.Now().Local() id2 := strutils.SimpleUUID() name2 := strutils.SimpleUUID() age2 := rand.Int31() enterTime2 := time.Now().Local() result1 := sql.Result{ "id": id1, "name": name1, "age": age1, "enter_time": enterTime1, } result2 := sql.Result{ "id": id2, "name": name2, "age": age2, "enter_time": enterTime2, } results := []sql.Result{result1, result2} var nameResult string nameResults := make([]string, 0) err := sql.ParseSqlResultWithColumn(result1, &nameResult, "name") if err != nil { t.Fatalf("%+v\n", err) } if nameResult != name1 { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", name1, nameResult)) } err = sql.ParseSqlResultWithColumn(results, &nameResults, "name") if err != nil { t.Fatalf("%+v\n", err) } if len(nameResults) != 2 { t.Fatalf("%+v\n", errors.Errorf("Result Error: %v", nameResults)) } for _, r := range nameResults { if r != name1 && r != name2 { t.Fatalf("%+v\n", errors.Errorf("Result Error: %v", r)) } } info := new(Info) infos := make([]Info, 0) err = sql.ParseSqlResult(result2, info) if err != nil { t.Fatalf("%+v\n", err) } if info.ID != id2 { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", id2, info.ID)) } if info.Name != name2 { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", name2, info.Name)) } if info.Age != age2 { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", age2, info.Age)) } if info.EnterTime.Format(time.DateTime) != enterTime2.Format(time.DateTime) { t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v", enterTime2, info.EnterTime)) } err = sql.ParseSqlResult(results, &infos) if err != nil { t.Fatalf("%+v\n", err) } for _, i := range infos { if i.ID != id1 && i.ID != id2 { t.Fatalf("%+v\n", errors.Errorf("Result Error: %v", i.ID)) } if i.Name != name1 && i.Name != name2 { t.Fatalf("%+v\n", errors.Errorf("Result Error: %v", i.Name)) } if i.Age != age1 && i.Age != age2 { t.Fatalf("%+v\n", errors.Errorf("Result Error: %v", i.Age)) } if i.EnterTime.Format(time.DateTime) != enterTime1.Format(time.DateTime) && i.EnterTime.Format(time.DateTime) != enterTime2.Format(time.DateTime) { t.Fatalf("%+v\n", errors.Errorf("Result Error: %v", i.EnterTime)) } } } func TestDatabaseSqlTemplate(t *testing.T) { tableName := "test.students" insertMap, err := sql.InsertExecuteParams{ TableName: tableName, TableRow: sql.NewTableRow(). Add("id", ""). Add("name", ""). Add("age", ""). Add("enter_time", ""), }.Map() if err != nil { t.Fatalf("%+v\n", err) } checkDatabaseSqlClause(t, sql.InsertTpl, insertMap, ` INSERT INTO test.students ("id","name","age","enter_time") VALUES (?, ?, ?, ?) `) insertBatchMap, err := sql.InsertBatchExecuteParams{ TableName: tableName, TableRowBatch: []sql.TableRow{ *sql.NewTableRow(). Add("id", ""). Add("name", ""). Add("age", ""). Add("enter_time", ""), *sql.NewTableRow(). Add("id", ""). Add("name", ""). Add("age", ""). Add("enter_time", ""), }, }.Map() if err != nil { t.Fatalf("%+v\n", err) } checkDatabaseSqlClause(t, sql.InsertTpl, insertBatchMap, ` INSERT INTO test.students ("id","name","age","enter_time") VALUES (?, ?, ?, ?),(?, ?, ?, ?) `) deleteMap, err := sql.DeleteExecuteParams{ TableName: tableName, Conditions: sql.NewConditions(). Equal("id", ""), }.Map() if err != nil { t.Fatalf("%+v\n", err) } checkDatabaseSqlClause(t, sql.DeleteTpl, deleteMap, ` DELETE FROM test.students WHERE "id" = ? AND 1 = 1 `) updateMap, err := sql.UpdateExecuteParams{ TableName: tableName, TableRow: sql.NewTableRow(). Add("name", ""). Add("age", ""). Add("enter_time", ""), Conditions: sql.NewConditions(). Equal("id", ""), }.Map() if err != nil { t.Fatalf("%+v\n", err) } checkDatabaseSqlClause(t, sql.UpdateTpl, updateMap, ` UPDATE test.students SET "name" = ?, "age" = ?, "enter_time" = ? WHERE "id" = ? AND 1 = 1 `) queryMap, err := sql.QueryExecuteParams{ TableName: tableName, SelectClauses: []string{"name", "age"}, Conditions: sql.NewConditions(). Equal("name", ""). Equal("age", ""), OrderBy: "name DESC", PageNo: 1, PageSize: 10, }.Map() if err != nil { t.Fatalf("%+v\n", err) } checkDatabaseSqlClause(t, sql.QueryTpl, queryMap, ` SELECT name, age FROM test.students WHERE "name" = ? AND "age" = ? AND 1 = 1 ORDER BY name DESC LIMIT 10 OFFSET 0 `) queryOneMap, err := sql.QueryOneExecuteParams{ TableName: tableName, SelectClauses: []string{"name", "age"}, Conditions: sql.NewConditions(). Equal("id", ""), }.Map() if err != nil { t.Fatalf("%+v\n", err) } checkDatabaseSqlClause(t, sql.QueryTpl, queryOneMap, ` SELECT name, age FROM test.students WHERE "id" = ? AND 1 = 1 `) countMap, err := sql.CountExecuteParams{ TableName: tableName, Conditions: sql.NewConditions(). Equal("name", ""). Equal("age", ""), }.Map() if err != nil { t.Fatalf("%+v\n", err) } checkDatabaseSqlClause(t, sql.CountTpl, countMap, ` SELECT COUNT(*) FROM test.students WHERE "name" = ? AND "age" = ? AND 1 = 1 `) checkExistMap, err := sql.CheckExistExecuteParams{ TableName: tableName, Conditions: sql.NewConditions(). Equal("name", ""). Equal("age", ""), }.Map() if err != nil { t.Fatalf("%+v\n", err) } checkDatabaseSqlClause(t, sql.CountTpl, checkExistMap, ` SELECT COUNT(*) FROM test.students WHERE "name" = ? AND "age" = ? AND 1 = 1 `) checkHasOnlyOneMap, err := sql.CheckHasOnlyOneExecuteParams{ TableName: tableName, Conditions: sql.NewConditions(). Equal("name", ""). Equal("age", ""), }.Map() if err != nil { t.Fatalf("%+v\n", err) } checkDatabaseSqlClause(t, sql.CountTpl, checkHasOnlyOneMap, ` SELECT COUNT(*) FROM test.students WHERE "name" = ? AND "age" = ? AND 1 = 1 `) } func checkDatabaseSqlClause(t *testing.T, sqlStr string, templateMap map[string]any, clause string) { parsedSql, err := template.ParseTemplateStringToString(sqlStr, templateMap) if err != nil { t.Fatalf("%+v\n", errors.New(err.Error())) } if parsedSql != clause { t.Fatalf("%+v\n", errors.Errorf("Clause Error:\nexcept: %v\nactural: %v\n", clause, parsedSql)) } }