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 := `WHERE 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().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{
		"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.ColumnValueStringAsTime("time").Format("2006-01-02T15:04:05")+"+08:00" != timeResult {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except %v, actural %v",
			timeResult, result.ColumnValueStringAsTime("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")))
	}
}

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