ds-sdk/sdk/raw_sql_tpl/raw_sql_tpl.go

package raw_sql_tpl

import (
	"errors"
	"git.sxidc.com/go-tools/utils/strutils"
	"github.com/fatih/structs"
	"reflect"
	"strconv"
	"time"
)

type TableRow struct {
	Column      string `structs:"column"`
	Value       any    `structs:"-"`
	ParsedValue string `structs:"value"`
}

func (tableRow *TableRow) parse() error {
	if strutils.IsStringEmpty(tableRow.Column) {
		return errors.New("没有传递列名")
	}

	if tableRow.Value == nil {
		return errors.New("没有传递列值")
	}

	parsedValue, err := parseValue(tableRow.Value)
	if err != nil {
		return err
	}

	tableRow.ParsedValue = parsedValue

	return nil
}

type Condition struct {
	Column      string `structs:"column"`
	Operator    string `structs:"operator"`
	Value       any    `structs:"-"`
	ParsedValue string `structs:"value"`
}

func (cond *Condition) parse() error {
	if strutils.IsStringEmpty(cond.Column) {
		return errors.New("没有传递列名")
	}

	if cond.Value == nil {
		return errors.New("没有传递条件值")
	}

	parsedValue, err := parseValue(cond.Value)
	if err != nil {
		return err
	}

	cond.ParsedValue = parsedValue

	return nil
}

const InsertTpl = `
INSERT INTO
    {{ .table_name }} ({{ .columns | join "," }})
VALUES
	({{ .values | join "," }})
`

type InsertExecuteParams struct {
	TableName string
	TableRows []*TableRow
}

func (params InsertExecuteParams) check() error {
	if strutils.IsStringEmpty(params.TableName) {
		return errors.New("没有传递表名")
	}

	if params.TableRows == nil || len(params.TableRows) == 0 {
		return errors.New("没有传递行数据")
	}

	for _, tableRow := range params.TableRows {
		if err := tableRow.parse(); err != nil {
			return err
		}
	}

	return nil
}

func (params InsertExecuteParams) Map() (map[string]any, error) {
	if err := params.check(); err != nil {
		return nil, err
	}

	columns := make([]string, 0)
	values := make([]any, 0)
	for _, tableRow := range params.TableRows {
		columns = append(columns, tableRow.Column)
		values = append(values, tableRow.ParsedValue)
	}

	return map[string]any{
		"table_name": params.TableName,
		"columns":    columns,
		"values":     values,
	}, nil
}

const DeleteTpl = `
DELETE FROM
	{{ .table_name }}
WHERE
	{{ range .conditions }} {{ .column }} {{ if .operator }}{{ .operator }}{{ else }}={{ end }} {{ .value }} AND {{ end }} 1 = 1
`

type DeleteExecuteParams struct {
	TableName  string       `structs:"table_name"`
	Conditions []*Condition `structs:"conditions"`
}

func (params DeleteExecuteParams) check() error {
	if strutils.IsStringEmpty(params.TableName) {
		return errors.New("没有传递表名")
	}

	if params.Conditions == nil || len(params.Conditions) == 0 {
		return errors.New("没有传递条件")
	}

	for _, condition := range params.Conditions {
		if err := condition.parse(); err != nil {
			return err
		}
	}

	return nil
}

func (params DeleteExecuteParams) Map() (map[string]any, error) {
	if err := params.check(); err != nil {
		return nil, err
	}

	return structs.Map(params), nil
}

const UpdateTpl = `
{{- $setList := list -}}
{{- range .table_rows -}}
{{- $set := printf "%s = %s" .column .value -}}
{{- $setList = append $setList $set -}}
{{- end }}

UPDATE
	{{ .table_name }}
SET
	{{ $setList | join "," }}
WHERE
	{{ range .conditions }} {{ .column }} {{ if .operator }}{{ .operator }}{{ else }}={{ end }} {{ .value }} AND {{ end }} 1 = 1
`

type UpdateExecuteParams struct {
	TableName  string       `structs:"table_name"`
	TableRows  []*TableRow  `structs:"table_rows"`
	Conditions []*Condition `structs:"conditions"`
}

func (params UpdateExecuteParams) check() error {
	if strutils.IsStringEmpty(params.TableName) {
		return errors.New("没有传递表名")
	}

	if params.TableRows == nil || len(params.TableRows) == 0 {
		return errors.New("没有传递表行")
	}

	for _, tableRow := range params.TableRows {
		if err := tableRow.parse(); err != nil {
			return err
		}
	}

	if params.Conditions == nil || len(params.Conditions) == 0 {
		return errors.New("没有传递条件")
	}

	for _, condition := range params.Conditions {
		if err := condition.parse(); err != nil {
			return err
		}
	}

	return nil
}

func (params UpdateExecuteParams) Map() (map[string]any, error) {
	if err := params.check(); err != nil {
		return nil, err
	}

	return structs.Map(params), nil
}

const QueryTpl = `
SELECT
	{{ if .select_columns }}{{ .select_columns | join "," }}{{ else }}*{{ end }} 
FROM
	{{ .table_name }}
WHERE
	{{ range .conditions }} {{ .column }} {{ if .operator }}{{ .operator }}{{ else }}={{ end }} {{ .value }} AND {{ end }} 1 = 1 
{{ if .limit }}LIMIT {{ .limit }}{{ end }}
{{ if .offset }}OFFSET {{ .offset }}{{ end }}
`

type QueryExecuteParams struct {
	TableName     string       `structs:"table_name"`
	SelectColumns []string     `structs:"select_columns"`
	Conditions    []*Condition `structs:"conditions"`
	Limit         int          `structs:"limit"`
	Offset        int          `structs:"offset"`
}

func (params QueryExecuteParams) check() error {
	if strutils.IsStringEmpty(params.TableName) {
		return errors.New("没有传递表名")
	}

	if params.Conditions != nil && len(params.Conditions) != 0 {
		for _, condition := range params.Conditions {
			if err := condition.parse(); err != nil {
				return err
			}
		}
	}

	return nil
}

func (params QueryExecuteParams) Map() (map[string]any, error) {
	if err := params.check(); err != nil {
		return nil, err
	}

	return structs.Map(params), nil
}

const CountTpl = `
SELECT
	COUNT(*)
FROM
	{{ .table_name }}
WHERE
	{{ range .conditions }} {{ .column }} {{ if .operator }}{{ .operator }}{{ else }}={{ end }} {{ .value }} AND {{ end }} 1 = 1`

type CountExecuteParams struct {
	TableName  string       `structs:"table_name"`
	Conditions []*Condition `structs:"conditions"`
}

func (params CountExecuteParams) check() error {
	if strutils.IsStringEmpty(params.TableName) {
		return errors.New("没有传递表名")
	}

	if params.Conditions != nil && len(params.Conditions) != 0 {
		for _, condition := range params.Conditions {
			if err := condition.parse(); err != nil {
				return err
			}
		}
	}

	return nil
}

func (params CountExecuteParams) Map() (map[string]any, error) {
	if err := params.check(); err != nil {
		return nil, err
	}

	return structs.Map(params), nil
}

const (
	timeWriteFormat = time.DateTime + ".000000 +08:00"
)

func parseValue(value any) (string, error) {
	valueValue := reflect.ValueOf(value)

	if !valueValue.IsValid() {
		return "", errors.New("无效值")
	}

	if valueValue.Kind() == reflect.Ptr && valueValue.IsNil() {
		return "", errors.New("空值")
	}

	if valueValue.Kind() == reflect.Ptr {
		valueValue = valueValue.Elem()
	}

	switch v := valueValue.Interface().(type) {
	case string:
		return "'" + v + "'", nil
	case bool:
		return strconv.FormatBool(v), nil
	case time.Time:
		return "'" + v.Format(timeWriteFormat) + "'", nil
	case int:
		return strconv.Itoa(v), nil
	case int8:
		return strconv.FormatInt(int64(v), 10), nil
	case int16:
		return strconv.FormatInt(int64(v), 10), nil
	case int32:
		return strconv.FormatInt(int64(v), 10), nil
	case int64:
		return strconv.FormatInt(v, 10), nil
	case uint:
		return strconv.FormatUint(uint64(v), 10), nil
	case uint8:
		return strconv.FormatUint(uint64(v), 10), nil
	case uint16:
		return strconv.FormatUint(uint64(v), 10), nil
	case uint32:
		return strconv.FormatUint(uint64(v), 10), nil
	case uint64:
		return strconv.FormatUint(v, 10), nil
	default:
		return "", errors.New("不支持的类型")
	}
}