baize/test/database_sql_test.go

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))
	}
}