dps-sql/v1_test.go

package dpsapi

import (
	"fmt"
	"git.sxidc.com/go-tools/api_binding/http_binding"
	"git.sxidc.com/go-tools/api_binding/http_binding/binding_context"
	"git.sxidc.com/go-tools/api_binding/http_binding/response"
	"git.sxidc.com/service-supports/dps-sdk"
	"git.sxidc.com/service-supports/dps-sdk/client"
	"github.com/go-resty/resty/v2"
	"math/rand"
	"testing"
	"time"
)

func TestApiV1OperateParse(t *testing.T) {
	http_binding.Init("test", "10086")
	defer http_binding.Destroy()

	operatorID := simpleUUID()
	tableName := "students"
	exceptedTableRows := map[string]any{
		"id":       "aaa",
		"name":     "yjp",
		"age":      float64(5),
		"rate":     92.5,
		"time":     "2024-01-01T00:00:00+08:00",
		"is_right": false,
	}

	binding := http_binding.NewBinding("v1")
	ApiV1(binding, dpsAddress, func(c *binding_context.Context) (string, error) {
		return operatorID, nil
	})

	parsed := operateParse(t, parseSqlInsert)

	if parsed["table"] != tableName {
		t.Fatal("表名不正确")
	}

	for columnName, value := range parsed["tableRow"].(map[string]any) {
		if exceptedTableRows[columnName] != value {
			t.Fatal(columnName + "行数据不正确")
		}
	}

	parsed = operateParse(t, parseSqlUpdate)

	if parsed["table"] != tableName {
		t.Fatal("表名不正确")
	}

	if parsed["where"] != `(((id = 'aaa') AND (name = 'yjp')) AND (age < 100)) AND (describe IN ('yjp',))` {
		t.Fatal("where不正确")
	}

	for columnName, value := range parsed["newTableRow"].(map[string]any) {
		if exceptedTableRows[columnName] != value {
			t.Fatal(columnName + "行数据不正确")
		}
	}

	parsed = operateParse(t, parseSqlDelete)

	if parsed["table"] != tableName {
		t.Fatal("表名不正确")
	}

	if parsed["where"] != `(((id = 'aaa') AND (name = 'yjp')) AND (age < 100)) AND (describe IN ('yjp',))` {
		t.Fatal("where不正确")
	}

	parsed = operateParse(t, parseSqlSelectWithJoin)

	if parsed["table"] != "" {
		t.Fatal("table不正确")
	}

	if parsed["fromSubQuery"] != `students LEFT JOIN classes ON students.id = classes.student_id LEFT JOIN identities ON students.id = identities.student_id` {
		t.Fatal("fromSubQuery不正确")
	}

	if parsed["selectClause"] != "id, name, age" {
		t.Fatal("select语句不正确")
	}

	if parsed["where"] != `students.id = 'aaa'` {
		t.Fatal("where不正确")
	}

	if parsed["orderBy"] != "students.name DESC" {
		t.Fatal("orderBy不正确")
	}

	if parsed["groupBy"] != "" {
		t.Fatal("groupBy不正确")
	}

	if parsed["having"] != "" {
		t.Fatal("having不正确")
	}

	if parsed["pageNo"] != float64(2) {
		t.Fatal("pageNo不正确")
	}

	if parsed["pageSize"] != float64(20) {
		t.Fatal("pageSize不正确")
	}

	parsed = operateParse(t, parseSqlSelectWithSubQuery)

	if parsed["table"] != "" {
		t.Fatal("table不正确")
	}

	if parsed["fromSubQuery"] != `(SELECT * FROM class WHERE id = 'bbb')` {
		t.Fatal("fromSubQuery不正确")
	}

	if parsed["selectClause"] != "" {
		t.Fatal("select语句不正确")
	}

	if parsed["where"] != `(students.id = 'aaa') AND (students.age = 3)` {
		t.Fatal("where不正确")
	}

	if parsed["orderBy"] != "" {
		t.Fatal("orderBy不正确")
	}

	if parsed["groupBy"] != "" {
		t.Fatal("groupBy不正确")
	}

	if parsed["having"] != "" {
		t.Fatal("having不正确")
	}

	if parsed["pageNo"] != float64(0) {
		t.Fatal("pageNo不正确")
	}

	if parsed["pageSize"] != float64(0) {
		t.Fatal("pageSize不正确")
	}

	parsed = operateParse(t, parseSqlSelectWithGroupBy)

	if parsed["table"] != tableName {
		t.Fatal("table不正确")
	}

	if parsed["fromSubQuery"] != "" {
		t.Fatal("fromSubQuery不正确")
	}

	if parsed["selectClause"] != "count(*)" {
		t.Fatal("select语句不正确")
	}

	if parsed["where"] != "" {
		t.Fatal("where不正确")
	}

	if parsed["orderBy"] != "" {
		t.Fatal("orderBy不正确")
	}

	if parsed["groupBy"] == "name, age" {
		t.Fatal("groupBy不正确")
	}

	if parsed["having"] != "(name = 'aaa') AND (age = 5)" {
		t.Fatal("having不正确")
	}

	if parsed["pageNo"] != float64(0) {
		t.Fatal("pageNo不正确")
	}

	if parsed["pageSize"] != float64(0) {
		t.Fatal("pageSize不正确")
	}
}

func TestApiV1Operate(t *testing.T) {
	http_binding.Init("test", "10086")
	defer http_binding.Destroy()

	operatorID := simpleUUID()
	tablePrefix := "test.a" + simpleUUID()[0:7]

	id := simpleUUID()
	name := simpleUUID()
	now := time.Now().Local()
	tableNum := rand.Intn(10) + 1

	newName := simpleUUID()
	newNow := time.Now().Local()
	newTableNum := rand.Intn(10) + 1

	keyColumns := []string{"id"}

	autoMigrate(t, []client.AutoMigrateItem{
		{
			TablePrefixWithSchema: tablePrefix,
			Version:               "v1",
			TableModelDescribe:    tableModelDescribe,
		},
	})

	binding := http_binding.NewBinding("v1")
	ApiV1(binding, dpsAddress, func(c *binding_context.Context) (string, error) {
		return operatorID, nil
	})

	operate(t, fmt.Sprintf(sqlInsertFormat, tablePrefix, id, name, now.Format(time.DateTime), tableNum), keyColumns)
	operate(t, fmt.Sprintf(sqlUpdateFormat, tablePrefix, newName, newNow.Format(time.DateTime), newTableNum, id), keyColumns)
	operate(t, fmt.Sprintf(sqlDeleteFormat, tablePrefix, id), keyColumns)
}

func autoMigrate(t *testing.T, items []client.AutoMigrateItem) {
	dpsClient, err := dps.NewClient(dpsAddress, "v1", testDatabaseID)
	if err != nil {
		t.Fatal(err)
	}

	err = dpsClient.AutoMigrate(&client.AutoMigrateRequest{Items: items})
	if err != nil {
		t.Fatal(err)
	}
}

func operateParse(t *testing.T, sql string) map[string]any {
	result := new(struct {
		response.MsgResponse
		Parsed map[string]any `json:"parsed"`
	})

	resp, err := resty.New().R().
		SetBody(&OperateParseRequest{
			SQL: sql,
		}).
		SetResult(result).
		Post("http://localhost:10086/test/api/v1/dpsv1/database/operate/parse")
	if err != nil {
		t.Fatal(err)
	}

	if resp.IsError() {
		t.Fatal(resp.Status())
	}

	if !result.Success {
		t.Fatal(result.Msg)
	}

	return result.Parsed
}

func operate(t *testing.T, sql string, keyColumns []string) []map[string]any {
	result := new(struct {
		response.MsgResponse
		TableRows []map[string]any `json:"table_rows"`
	})

	resp, err := resty.New().R().
		SetBody(&OperateRequest{
			DatabaseID: testDatabaseID,
			Version:    "v1",
			KeyColumns: keyColumns,
			SQL:        sql,
		}).
		SetResult(result).
		Post("http://localhost:10086/test/api/v1/dpsv1/database/operate")
	if err != nil {
		t.Fatal(err)
	}

	if resp.IsError() {
		t.Fatal(resp.Status())
	}

	if !result.Success {
		t.Fatal(result.Msg)
	}

	return result.TableRows
}