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"
)

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

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

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

	return nil
}

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

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

	if strutils.IsStringEmpty(cond.Value) {
		return errors.New("没有传递列值")
	}

	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.check(); 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([]string, 0)
	for _, tableRow := range params.TableRows {
		columns = append(columns, tableRow.Column)
		values = append(values, tableRow.Value)
	}

	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.check(); 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.check(); err != nil {
			return err
		}
	}

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

	for _, condition := range params.Conditions {
		if err := condition.check(); 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("没有传递表名")
	}

	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("没有传递表名")
	}

	return nil
}

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

	return structs.Map(params), nil
}