baize/framework/core/infrastructure/database/sql/sql_template.go

package sql

import (
	"github.com/pkg/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 "," }}
`

// InsertExecuteParams 插入参数
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
	}

	if (params.TableRow.columns == nil || len(params.TableRow.columns) == 0) ||
		(params.TableRow.values == nil || len(params.TableRow.values) == 0) {
		return nil, errors.New("没有传递列和值")
	}

	values := make([]any, 0)
	for i := 0; i < len(params.TableRow.values); i++ {
		values = append(values, "?")
	}

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

// InsertBatchExecuteParams 批量插入参数
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
	}

	values := make([]any, 0)
	for i := 0; i < len(params.TableRowBatch[0].values); i++ {
		values = append(values, "?")
	}

	valuesList := make([]any, 0)
	for _, tableRow := range params.TableRowBatch {
		if tableRow.err != nil {
			return nil, tableRow.err
		}

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

		valuesList = append(valuesList, values)
	}

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

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

// DeleteExecuteParams 删除参数
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,
		"queries":    params.queries,
	}, nil
}

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

// UpdateExecuteParams 更新参数
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 _, column := range params.TableRow.columns {
		setList = append(setList, column+" = ?")
	}

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

const QueryTpl = `
SELECT
    {{ if .select_columns }}{{ .select_columns | join ", " }}{{ else }}*{{ end }} 
FROM
    {{ .table_name }}
WHERE
    {{ range .queries -}}{{ . }} AND {{ end -}}1 = 1
{{- if .order_by }}
ORDER BY {{ .order_by }}
{{- end }}
{{- if not (eq .limit -1) }}
LIMIT {{ .limit }}
{{- end }}
{{- if not (eq .offset -1) }}
OFFSET {{ .offset }}
{{- end }}
`

// QueryExecuteParams 查询参数
type QueryExecuteParams struct {
	TableName     string
	SelectClauses []string
	*Conditions
	OrderBy  string
	PageNo   int
	PageSize int
}

func (params QueryExecuteParams) Map() (map[string]any, error) {
	limit := -1
	offset := -1

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

	return map[string]any{
		"table_name":     params.TableName,
		"select_columns": params.SelectClauses,
		"queries":        params.Conditions.queries,
		"limit":          limit,
		"offset":         offset,
		"order_by":       params.OrderBy,
	}, nil
}

// QueryOneExecuteParams 单查询参数
type QueryOneExecuteParams struct {
	TableName     string
	SelectClauses []string
	*Conditions
}

func (params QueryOneExecuteParams) Map() (map[string]any, error) {
	return map[string]any{
		"table_name":     params.TableName,
		"select_columns": params.SelectClauses,
		"queries":        params.Conditions.queries,
		"limit":          -1,
		"offset":         -1,
	}, nil
}

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

// CountExecuteParams 计数参数
type CountExecuteParams struct {
	TableName string
	*Conditions
}

func (params CountExecuteParams) Map() (map[string]any, error) {
	return map[string]any{
		"table_name": params.TableName,
		"queries":    params.Conditions.queries,
	}, nil
}

// CheckExistExecuteParams 存在性校验参数
type CheckExistExecuteParams struct {
	TableName string
	*Conditions
}

func (params CheckExistExecuteParams) Map() (map[string]any, error) {
	return map[string]any{
		"table_name": params.TableName,
		"queries":    params.Conditions.queries,
	}, nil
}

// CheckHasOnlyOneExecuteParams 唯一性校验参数
type CheckHasOnlyOneExecuteParams struct {
	TableName string
	*Conditions
}

func (params CheckHasOnlyOneExecuteParams) Map() (map[string]any, error) {
	return map[string]any{
		"table_name": params.TableName,
		"queries":    params.Conditions.queries,
	}, nil
}