baize/test/database_test.go

package test

import (
	"git.sxidc.com/go-framework/baize/framework/core/infrastructure/database"
	"git.sxidc.com/go-framework/baize/framework/core/infrastructure/database/data_service"
	"git.sxidc.com/go-framework/baize/framework/core/infrastructure/database/operations"
	"git.sxidc.com/go-framework/baize/framework/core/infrastructure/database/sql"
	"git.sxidc.com/go-tools/utils/strutils"
	"github.com/pkg/errors"
	"math/rand"
	"testing"
	"time"
)

func TestOperations(t *testing.T) {
	dbOperations, err := operations.NewOperations(&operations.Config{
		UserName:           "test",
		Password:           "123456",
		Address:            "localhost",
		Port:               "30432",
		Database:           "test",
		MaxConnections:     40,
		MaxIdleConnections: 10,
		LogLevel:           "error",
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	testDBExecutor(t, dbOperations)
}

func TestDataService(t *testing.T) {
	dataService, err := data_service.NewExecutor(&data_service.Config{
		Token:          "8qe+uPgpQ2JWxu3lSyOx5EWC24/c+zJOxvFhvRLRTzU=",
		Address:        "localhost",
		HttpPort:       "10000",
		GrpcPort:       "10001",
		Namespace:      "baize",
		DataSource:     "baize",
		HttpTimeoutSec: 10,
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	testDBExecutor(t, dataService)
}

func TestDatabase(t *testing.T) {
	dbOperations, err := operations.NewOperations(&operations.Config{
		UserName:           "test",
		Password:           "123456",
		Address:            "localhost",
		Port:               "30432",
		Database:           "test",
		MaxConnections:     40,
		MaxIdleConnections: 10,
		LogLevel:           "error",
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	dataService, err := data_service.NewExecutor(&data_service.Config{
		Token:          "8qe+uPgpQ2JWxu3lSyOx5EWC24/c+zJOxvFhvRLRTzU=",
		Address:        "localhost",
		HttpPort:       "10000",
		GrpcPort:       "10001",
		Namespace:      "baize",
		DataSource:     "baize",
		HttpTimeoutSec: 10,
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	testDatabaseEntity(t, dbOperations, dbOperations)
	testDatabase(t, dbOperations, dbOperations)
	testDatabaseEntity(t, dataService, dataService)
	testDatabase(t, dataService, dataService)

	err = database.Transaction(dbOperations, func(tx database.Executor) error {
		testDatabaseEntity(t, dbOperations, tx)
		testDatabase(t, dbOperations, tx)
		return nil
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	err = database.Transaction(dbOperations, func(tx database.Executor) error {
		testDatabaseEntity(t, dataService, tx)
		testDatabase(t, dataService, tx)
		return nil
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}
}

func testDBExecutor(t *testing.T, dbExecutor database.Executor) {
	id := strutils.SimpleUUID()
	name := strutils.SimpleUUID()
	age := rand.Int31()
	enterTime := time.Now().Local()

	_, err := dbExecutor.ExecuteRawSql(`
INSERT INTO
	test.students (id, name, age, enter_time)
VALUES 
    (?, ?, ?, ?)
`, id, name, age, enterTime)
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	results, err := dbExecutor.ExecuteRawSql(`
SELECT 
    *
FROM
	test.students
WHERE 
    id = ?
`, id)
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if results[0].ColumnValueString("id") != id {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			id, results[0].ColumnValueString("id")))
	}

	if results[0].ColumnValueString("name") != name {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			name, results[0].ColumnValueString("name")))
	}

	if results[0].ColumnValueInt32("age") != age {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			age, results[0].ColumnValueInt32("age")))
	}

	if results[0].ColumnValueTime("enter_time").Format(time.DateTime) != enterTime.Format(time.DateTime) {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			enterTime, results[0].ColumnValueTime("enter_time")))
	}

	_, err = dbExecutor.ExecuteRawSql(`
DELETE FROM
	test.students
WHERE 
    id = ?
`, id)
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	results, err = dbExecutor.ExecuteRawSql(`
SELECT 
    *
FROM
	test.students
WHERE 
    id = ?
`, id)
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if len(results) != 0 {
		t.Fatalf("Delete Error: %+v\n", results)
	}

	_, err = dbExecutor.ExecuteRawSqlTemplate(`
INSERT INTO
	{{ .table_name }} (id, name, age, enter_time)
VALUES 
    (?, ?, ?, ?)
`, map[string]any{"table_name": "test.students"}, id, name, age, enterTime)
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	results, err = dbExecutor.ExecuteRawSqlTemplate(`
SELECT 
    *
FROM
	{{ .table_name }}
WHERE 
    id = ?
`, map[string]any{"table_name": "test.students"}, id)
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if results[0].ColumnValueString("id") != id {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			id, results[0].ColumnValueString("id")))
	}

	if results[0].ColumnValueString("name") != name {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			name, results[0].ColumnValueString("name")))
	}

	if results[0].ColumnValueInt32("age") != age {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			age, results[0].ColumnValueInt32("age")))
	}

	if results[0].ColumnValueTime("enter_time").Format(time.DateTime) != enterTime.Format(time.DateTime) {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			enterTime, results[0].ColumnValueTime("enter_time")))
	}

	_, err = dbExecutor.ExecuteRawSqlTemplate(`
DELETE FROM
	{{ .table_name }}
WHERE 
    id = ?
`, map[string]any{"table_name": "test.students"}, id)
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	results, err = dbExecutor.ExecuteRawSqlTemplate(`
SELECT 
    *
FROM
	{{ .table_name }}
WHERE 
    id = ?
`, map[string]any{"table_name": "test.students"}, id)
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if len(results) != 0 {
		t.Fatalf("Delete Error: %+v\n", results)
	}
}

type Student struct {
	ID        string    `sqlmapping:"column:id" sqlresult:"column:id"`
	Name      string    `sqlmapping:"column:name" sqlresult:"column:name"`
	Age       int32     `sqlmapping:"column:age" sqlresult:"column:age"`
	EnterTime time.Time `sqlmapping:"column:enter_time" sqlresult:"column:enter_time"`
}

func testDatabaseEntity(t *testing.T, writeDBExecutor database.Executor, readDBExecutor database.Executor) {
	tableName := "test.students"

	id1 := strutils.SimpleUUID()
	name1 := "1" + strutils.SimpleUUID()
	age1 := rand.Int31()
	enterTime1 := time.Now().Local()

	id2 := strutils.SimpleUUID()
	name2 := "2" + strutils.SimpleUUID()
	age2 := rand.Int31()
	enterTime2 := time.Now().Local()

	student1 := &Student{
		ID:        id1,
		Name:      name1,
		Age:       age1,
		EnterTime: enterTime1,
	}

	newStudent1 := &Student{
		ID:        id1,
		Name:      name2,
		Age:       age2,
		EnterTime: enterTime2,
	}

	student2 := &Student{
		ID:        id2,
		Name:      name2,
		Age:       age2,
		EnterTime: enterTime2,
	}

	err := database.InsertEntity(writeDBExecutor, tableName, student1)
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	result, err := database.QueryOne(readDBExecutor, &sql.QueryOneExecuteParams{
		TableName: tableName,
		Conditions: sql.NewConditions().
			Equal("id", id1),
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if result.ColumnValueString("id") != id1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			id1, result.ColumnValueString("id")))
	}

	if result.ColumnValueString("name") != name1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			name1, result.ColumnValueString("name")))
	}

	if result.ColumnValueInt32("age") != age1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			age1, result.ColumnValueInt32("age")))
	}

	if result.ColumnValueTime("enter_time").Format(time.DateTime) != enterTime1.Format(time.DateTime) {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			enterTime1, result.ColumnValueTime("enter_time")))
	}

	err = database.UpdateEntity(writeDBExecutor, tableName, newStudent1)
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	result, err = database.QueryOne(readDBExecutor, &sql.QueryOneExecuteParams{
		TableName: tableName,
		Conditions: sql.NewConditions().
			Equal("id", id1),
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if result.ColumnValueString("id") != id1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			id1, result.ColumnValueString("id")))
	}

	if result.ColumnValueString("name") != name2 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			name2, result.ColumnValueString("name")))
	}

	if result.ColumnValueInt32("age") != age2 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			age2, result.ColumnValueInt32("age")))
	}

	if result.ColumnValueTime("enter_time").Format(time.DateTime) != enterTime2.Format(time.DateTime) {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			enterTime2, result.ColumnValueTime("enter_time")))
	}

	err = database.DeleteEntity(writeDBExecutor, tableName, newStudent1)
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	exist, err := database.CheckExist(readDBExecutor, &sql.CheckExistExecuteParams{
		TableName: tableName,
		Conditions: sql.NewConditions().
			Equal("id", id1),
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if exist {
		t.Fatalf("%+v\n", errors.New("Delete Error"))
	}

	err = database.InsertEntity(writeDBExecutor, tableName, []any{student1, student2})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	results, totalCount, err := database.Query(readDBExecutor, &sql.QueryExecuteParams{
		TableName:  tableName,
		Conditions: sql.NewConditions().In("id", []string{id1, id2}),
		OrderBy:    "name ASC",
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if len(results) != 2 || totalCount != 2 {
		t.Fatalf("%+v\n", errors.Errorf("Insert Batch Error: %v", results))
	}

	if results[0].ColumnValueString("id") != id1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			id1, result.ColumnValueString("id")))
	}

	if results[0].ColumnValueString("name") != name1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			name1, result.ColumnValueString("name")))
	}

	if results[0].ColumnValueInt32("age") != age1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			age1, result.ColumnValueInt32("age")))
	}

	if results[0].ColumnValueTime("enter_time").Format(time.DateTime) != enterTime1.Format(time.DateTime) {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			enterTime1, result.ColumnValueTime("enter_time")))
	}

	if results[1].ColumnValueString("id") != id2 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			id2, result.ColumnValueString("id")))
	}

	if results[1].ColumnValueString("name") != name2 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			name2, result.ColumnValueString("name")))
	}

	if results[1].ColumnValueInt32("age") != age2 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			age2, result.ColumnValueInt32("age")))
	}

	if results[1].ColumnValueTime("enter_time").Format(time.DateTime) != enterTime2.Format(time.DateTime) {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			enterTime2, result.ColumnValueTime("enter_time")))
	}

	hasOnlyOne, err := database.CheckHasOnlyOne(readDBExecutor, &sql.CheckHasOnlyOneExecuteParams{
		TableName: tableName,
		Conditions: sql.NewConditions().
			Equal("id", id1),
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if !hasOnlyOne {
		t.Fatalf("%+v\n", errors.New("Check HasOnlyOne Error"))
	}

	count, err := database.Count(readDBExecutor, &sql.CountExecuteParams{
		TableName: tableName,
		Conditions: sql.NewConditions().
			Equal("id", id2),
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if count != 1 {
		t.Fatalf("%+v\n", errors.New("Count Error"))
	}
}

func testDatabase(t *testing.T, writeDBExecutor database.Executor, readDBExecutor database.Executor) {
	tableName := "test.students"

	id1 := strutils.SimpleUUID()
	name1 := "1" + strutils.SimpleUUID()
	age1 := rand.Int31()
	enterTime1 := time.Now().Local()

	id2 := strutils.SimpleUUID()
	name2 := "2" + strutils.SimpleUUID()
	age2 := rand.Int31()
	enterTime2 := time.Now().Local()

	tableRow1 := sql.NewTableRow().
		Add("id", id1).
		Add("name", name1).
		Add("age", age1).
		Add("enter_time", enterTime1)

	newTableRow1 := sql.NewTableRow().
		Add("name", name2).
		Add("age", age2).
		Add("enter_time", enterTime2)

	tableRow2 := sql.NewTableRow().
		Add("id", id2).
		Add("name", name2).
		Add("age", age2).
		Add("enter_time", enterTime2)

	err := database.Insert(writeDBExecutor, &sql.InsertExecuteParams{
		TableName: tableName,
		TableRow:  tableRow1,
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	result, err := database.QueryOne(readDBExecutor, &sql.QueryOneExecuteParams{
		TableName: tableName,
		Conditions: sql.NewConditions().
			Equal("id", id1),
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if result.ColumnValueString("id") != id1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			id1, result.ColumnValueString("id")))
	}

	if result.ColumnValueString("name") != name1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			name1, result.ColumnValueString("name")))
	}

	if result.ColumnValueInt32("age") != age1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			age1, result.ColumnValueInt32("age")))
	}

	if result.ColumnValueTime("enter_time").Format(time.DateTime) != enterTime1.Format(time.DateTime) {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			enterTime1, result.ColumnValueTime("enter_time")))
	}

	err = database.Update(writeDBExecutor, &sql.UpdateExecuteParams{
		TableName:  tableName,
		TableRow:   newTableRow1,
		Conditions: sql.NewConditions().Equal("id", id1),
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	result, err = database.QueryOne(readDBExecutor, &sql.QueryOneExecuteParams{
		TableName: tableName,
		Conditions: sql.NewConditions().
			Equal("id", id1),
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if result.ColumnValueString("id") != id1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			id1, result.ColumnValueString("id")))
	}

	if result.ColumnValueString("name") != name2 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			name2, result.ColumnValueString("name")))
	}

	if result.ColumnValueInt32("age") != age2 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			age2, result.ColumnValueInt32("age")))
	}

	if result.ColumnValueTime("enter_time").Format(time.DateTime) != enterTime2.Format(time.DateTime) {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			enterTime2, result.ColumnValueTime("enter_time")))
	}

	err = database.Delete(writeDBExecutor, &sql.DeleteExecuteParams{
		TableName:  tableName,
		Conditions: sql.NewConditions().Equal("id", id1),
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	exist, err := database.CheckExist(readDBExecutor, &sql.CheckExistExecuteParams{
		TableName: tableName,
		Conditions: sql.NewConditions().
			Equal("id", id1),
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if exist {
		t.Fatalf("%+v\n", errors.New("Delete Error"))
	}

	err = database.InsertBatch(writeDBExecutor, &sql.InsertBatchExecuteParams{
		TableName:     tableName,
		TableRowBatch: []sql.TableRow{*tableRow1, *tableRow2},
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	results, totalCount, err := database.Query(readDBExecutor, &sql.QueryExecuteParams{
		TableName:  tableName,
		Conditions: sql.NewConditions().In("id", []string{id1, id2}),
		OrderBy:    "name ASC",
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if len(results) != 2 || totalCount != 2 {
		t.Fatalf("%+v\n", errors.Errorf("Insert Batch Error: %v", results))
	}

	if results[0].ColumnValueString("id") != id1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			id1, result.ColumnValueString("id")))
	}

	if results[0].ColumnValueString("name") != name1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			name1, result.ColumnValueString("name")))
	}

	if results[0].ColumnValueInt32("age") != age1 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			age1, result.ColumnValueInt32("age")))
	}

	if results[0].ColumnValueTime("enter_time").Format(time.DateTime) != enterTime1.Format(time.DateTime) {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			enterTime1, result.ColumnValueTime("enter_time")))
	}

	if results[1].ColumnValueString("id") != id2 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			id2, result.ColumnValueString("id")))
	}

	if results[1].ColumnValueString("name") != name2 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			name2, result.ColumnValueString("name")))
	}

	if results[1].ColumnValueInt32("age") != age2 {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			age2, result.ColumnValueInt32("age")))
	}

	if results[1].ColumnValueTime("enter_time").Format(time.DateTime) != enterTime2.Format(time.DateTime) {
		t.Fatalf("%+v\n", errors.Errorf("Result Error: except: %v, actual: %v",
			enterTime2, result.ColumnValueTime("enter_time")))
	}

	hasOnlyOne, err := database.CheckHasOnlyOne(readDBExecutor, &sql.CheckHasOnlyOneExecuteParams{
		TableName: tableName,
		Conditions: sql.NewConditions().
			Equal("id", id1),
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if !hasOnlyOne {
		t.Fatalf("%+v\n", errors.New("Check HasOnlyOne Error"))
	}

	count, err := database.Count(readDBExecutor, &sql.CountExecuteParams{
		TableName: tableName,
		Conditions: sql.NewConditions().
			Equal("id", id2),
	})
	if err != nil {
		t.Fatalf("%+v\n", err)
	}

	if count != 1 {
		t.Fatalf("%+v\n", errors.New("Count Error"))
	}
}