ds-sdk/sql/sql_tpl/sql_tpl.go

package sql_tpl

import "errors"

const InsertTpl = `
INSERT INTO
    {{ .table_name }} ({{ .columns | join "," }})
VALUES
{{- $valuesClauses := list }}
{{- range .values_list }}
{{- $valuesClause := printf "(%s)" ( . | join "," ) }}
{{- $valuesClauses = append $valuesClauses $valuesClause }}
{{- end }}
    {{ $valuesClauses | join "," }}
`

type InsertExecuteParams struct {
	TableName string
	*TableRow
}

func (params InsertExecuteParams) Map() (map[string]any, error) {
	if params.TableRow == nil {
		return nil, nil
	}

	if params.TableRow.err != nil {
		return nil, params.TableRow.err
	}

	columns := make([]string, 0)
	values := make([]any, 0)

	for _, cv := range params.TableRow.columnValues {
		columns = append(columns, cv.column)
		values = append(values, cv.value)
	}

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

type InsertBatchExecuteParams struct {
	TableName     string
	TableRowBatch []TableRow
}

func (params InsertBatchExecuteParams) Map() (map[string]any, error) {
	if params.TableRowBatch == nil || len(params.TableRowBatch) == 0 {
		return nil, nil
	}

	columns := make([]string, 0)
	for _, cv := range params.TableRowBatch[0].columnValues {
		columns = append(columns, cv.column)
	}

	valuesList := make([]any, 0)

	for _, tableRow := range params.TableRowBatch {
		if tableRow.err != nil {
			return nil, tableRow.err
		}

		if len(columns) != len(tableRow.columnValues) {
			return nil, errors.New("列数不匹配，保证每个TableRow的Add数量一致")
		}

		columnAndValueMap := make(map[string]any, 0)

		for _, cv := range tableRow.columnValues {
			columnAndValueMap[cv.column] = cv.value
		}

		values := make([]any, len(columnAndValueMap))
		for _, column := range columns {
			values = append(values, columnAndValueMap[column])
		}

		valuesList = append(valuesList, values)
	}

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

const DeleteTpl = `
DELETE FROM
    {{ .table_name }}
WHERE
    {{ range .conditions }} {{ . }} AND {{ end }} 1 = 1
`

type DeleteExecuteParams struct {
	TableName string
	*Conditions
}

func (params DeleteExecuteParams) Map() (map[string]any, error) {
	if params.Conditions == nil {
		return nil, errors.New("没有传递删除条件")
	}

	if params.Conditions.err != nil {
		return nil, params.Conditions.err
	}

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

const UpdateTpl = `
UPDATE
    {{ .table_name }}
SET
    {{ .set_list | join "," }}
WHERE
    {{ range .conditions }} {{ . }} AND {{ end }} 1 = 1
`

type UpdateExecuteParams struct {
	TableName string
	*TableRow
	*Conditions
}

func (params UpdateExecuteParams) Map() (map[string]any, error) {
	if params.TableRow == nil {
		return nil, nil
	}

	if params.TableRow.err != nil {
		return nil, params.TableRow.err
	}

	setList := make([]string, 0)
	for _, cv := range params.TableRow.columnValues {
		setList = append(setList, cv.column+" = "+cv.value)
	}

	conditions := make([]string, 0)
	if params.Conditions != nil {
		if params.Conditions.err != nil {
			return nil, params.Conditions.err
		}

		conditions = params.Conditions.Conditions
	}

	return map[string]any{
		"table_name": params.TableName,
		"set_list":   setList,
		"conditions": conditions,
	}, nil
}

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

type QueryExecuteParams struct {
	TableName     string
	SelectColumns []string
	*Conditions
	PageNo   int
	PageSize int
}

func (params QueryExecuteParams) Map() (map[string]any, error) {
	var limit int
	var offset int

	if params.PageNo != 0 && params.PageSize != 0 {
		limit = params.PageSize
		offset = (params.PageNo - 1) * params.PageSize
	}

	conditions := make([]string, 0)
	if params.Conditions != nil {
		if params.Conditions.err != nil {
			return nil, params.Conditions.err
		}

		conditions = params.Conditions.Conditions
	}

	return map[string]any{
		"table_name":     params.TableName,
		"select_columns": params.SelectColumns,
		"conditions":     conditions,
		"limit":          limit,
		"offset":         offset,
	}, nil
}

type QueryOneExecuteParams struct {
	TableName     string
	SelectColumns []string
	*Conditions
}

func (params QueryOneExecuteParams) Map() (map[string]any, error) {
	conditions := make([]string, 0)
	if params.Conditions != nil {
		if params.Conditions.err != nil {
			return nil, params.Conditions.err
		}

		conditions = params.Conditions.Conditions
	}

	return map[string]any{
		"table_name":     params.TableName,
		"select_columns": params.SelectColumns,
		"conditions":     conditions,
	}, nil
}

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

type CountExecuteParams struct {
	TableName string
	*Conditions
}

func (params CountExecuteParams) Map() (map[string]any, error) {
	conditions := make([]string, 0)
	if params.Conditions != nil {
		if params.Conditions.err != nil {
			return nil, params.Conditions.err
		}

		conditions = params.Conditions.Conditions
	}

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

type CheckExistExecuteParams struct {
	TableName string
	*Conditions
}

func (params CheckExistExecuteParams) Map() (map[string]any, error) {
	conditions := make([]string, 0)
	if params.Conditions != nil {
		if params.Conditions.err != nil {
			return nil, params.Conditions.err
		}

		conditions = params.Conditions.Conditions
	}

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

type CheckHasOnlyOneExecuteParams struct {
	TableName string
	*Conditions
}

func (params CheckHasOnlyOneExecuteParams) Map() (map[string]any, error) {
	conditions := make([]string, 0)
	if params.Conditions != nil {
		if params.Conditions.err != nil {
			return nil, params.Conditions.err
		}

		conditions = params.Conditions.Conditions
	}

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