eta_api/utils/sql.go

package utils

import (
	"bytes"
	"errors"
	"fmt"
	"reflect"
	"regexp"
	"text/template"
)

type Driver string
type SqlCondition string

const (
	DM            Driver       = "dm"
	MySql         Driver       = "mysql"
	GroupUnit     SqlCondition = "GroupUnit"
	Distinct      SqlCondition = "Distinct"
	Order         SqlCondition = "Order"
	Delimiter     SqlCondition = "Delimiter"
	ConvertColumn SqlCondition = "ConvertColumn"

	ToDate SqlCondition = "ToDate"
)

var TemplateMap = map[SqlCondition]map[Driver]string{
	GroupUnit: {
		MySql: `GROUP_CONCAT({{.Distinct}} {{.Column}} {{.Order}} SEPARATOR '{{.Delimiter}}')`,
		DM:    `LISTAGG({{.Distinct}} {{.Column}},'{{.Delimiter}}') WITHIN GROUP ({{.Order}})`,
	},
	ConvertColumn: {
		MySql: `CONVERT({{.ConvertColumn}} USING gbk )`,
		DM:    `{{.ConvertColumn}}`,
	},
	ToDate: {
		MySql: `DATE({{.Column}})`,
		DM:    `TO_DATE({{.Column}})`,
	},
}

var supportDriverMap = map[string]Driver{
	"mysql": MySql,
	"dm":    DM,
	//"postgres": Postgres,
}

type QueryParam struct {
	Driver        string
	Column        string
	Order         string
	Distinct      string
	Delimiter     string
	OrderField    string
	OrderRule     string
	ConvertColumn string `description:"Convert指令传递的clounm"`
	HasDistinct   bool
}
type SqlParam interface {
	GetParamName() string
	GetFormatConditionStr(param *QueryParam) string
}
type DistinctParam struct {
}

func (distinctParam *DistinctParam) GetParamName() string {
	return "Distinct"
}
func (distinctParam *DistinctParam) GetFormatConditionStr(param *QueryParam) string {
	if param.HasDistinct {
		return "DISTINCT"
	}
	return ""
}

type ToDateParam struct {
}

func (toDateParam *ToDateParam) GetParamName() string {
	return "ToDate"
}
func (toDateParam *ToDateParam) GetFormatConditionStr(param *QueryParam) (sql string) {
	dbDriver, _ := getDriverInstance(param.Driver)
	if param.Column == "" {
		FileLog.Error("聚合字段为空,无法生成聚合sql")
		return
	}
	var templateSqlStr string
	if _, ok := TemplateMap[ToDate][dbDriver]; !ok {
		templateSqlStr = TemplateMap[ToDate][MySql]
	} else {
		templateSqlStr = TemplateMap[ToDate][dbDriver]
	}
	if templateSqlStr == "" {
		FileLog.Error("聚合sql模板不存在,无法生成聚合sql")
		return
	}
	templateSql, err := template.New("ToDate").Parse(templateSqlStr)
	if err != nil {
		FileLog.Error("failed to parse template: %v", err)
		return
	}
	//反射获取结构体的值
	value := reflect.ValueOf(param)
	// 检查是否是指针
	if value.Kind() != reflect.Ptr {
		fmt.Println("请求参数必须是一个结构体")
		return
	}
	// 获取结构体的元素
	elem := value.Elem()
	// 检查是否是结构体
	if elem.Kind() != reflect.Struct {
		fmt.Println("请求参数必须是一个结构体")
		return
	}
	// 获取字段的值
	fieldValue := elem.FieldByName("ConvertColumn")
	// 检查字段是否存在
	if !fieldValue.IsValid() {
		fmt.Printf("Error: field %s not found\n", "ConvertColumn")
		return
	}
	// 检查字段是否可导出
	if !fieldValue.CanSet() {
		fmt.Printf("Error: field %s is not exported and cannot be set\n", "ConvertColumn")
		return
	}
	// 渲染模板
	var buf bytes.Buffer
	err = templateSql.Execute(&buf, param)
	if err != nil {
		fmt.Sprintf("执行模板填充失败: %v", err)
		return
	}
	sql = buf.String()
	fmt.Printf("生成的转换日期语句为:%s\n", sql)
	return sql
}

type ConvertParam struct {
}

func (convertParam *ConvertParam) GetParamName() string {
	return "Convert"
}
func (convertParam *ConvertParam) GetFormatConditionStr(param *QueryParam) (sqlStr string) {
	dbDriver, _ := getDriverInstance(param.Driver)
	if param.ConvertColumn == "" {
		FileLog.Error("转换字段为空,无法生成聚合sql")
		return
	}
	var templateSqlStr string
	if _, ok := TemplateMap[ConvertColumn][dbDriver]; !ok {
		templateSqlStr = TemplateMap[ConvertColumn][MySql]
	} else {
		templateSqlStr = TemplateMap[ConvertColumn][dbDriver]
	}
	if templateSqlStr == "" {
		FileLog.Error("转换sql模板不存在,无法生成转换sql")
		return
	}
	templateSql, err := template.New("ConvertColumn").Parse(templateSqlStr)
	if err != nil {
		FileLog.Error("failed to parse template: %v", err)
		return
	}
	//反射获取结构体的值
	value := reflect.ValueOf(param)
	// 检查是否是指针
	if value.Kind() != reflect.Ptr {
		fmt.Println("请求参数必须是一个结构体")
		return
	}
	// 获取结构体的元素
	elem := value.Elem()
	// 检查是否是结构体
	if elem.Kind() != reflect.Struct {
		fmt.Println("请求参数必须是一个结构体")
		return
	}
	// 获取字段的值
	fieldValue := elem.FieldByName("ConvertColumn")
	// 检查字段是否存在
	if !fieldValue.IsValid() {
		fmt.Printf("Error: field %s not found\n", "ConvertColumn")
		return
	}
	// 检查字段是否可导出
	if !fieldValue.CanSet() {
		fmt.Printf("Error: field %s is not exported and cannot be set\n", "ConvertColumn")
		return
	}
	// 渲染模板
	var buf bytes.Buffer
	err = templateSql.Execute(&buf, param)
	if err != nil {
		fmt.Sprintf("执行模板填充失败: %v", err)
		return
	}
	sqlStr = buf.String()
	fmt.Printf("生成的准换编码语句为:%s\n", sqlStr)
	return sqlStr
}

var sqlGeneratorFactory = map[SqlCondition]SqlParam{
	// 添加支持的语法
	GroupUnit: &GroupUnitParam{
		supportGrams: []SqlCondition{
			Distinct,
			Order,
			Delimiter,
		},
	},
	Order:         &OrderParam{},
	Delimiter:     &DelimiterParam{},
	Distinct:      &DistinctParam{},
	ConvertColumn: &ConvertParam{},
	ToDate:        &ToDateParam{},
}

type DelimiterParam struct {
}

func (delimiterParam *DelimiterParam) GetParamName() string {
	return "Delimiter"
}
func (delimiterParam *DelimiterParam) GetFormatConditionStr(param *QueryParam) string {
	if param.Delimiter == "" {
		return ","
	}
	return param.Delimiter
}

type OrderParam struct {
	Field string
	Rule  string
}

func (orderParam *OrderParam) GetParamName() string {
	return "Order"
}

func (orderParam *OrderParam) GetFormatConditionStr(param *QueryParam) string {
	if param.OrderField == "" {
		orderParam.Field = param.Column
	} else {
		orderParam.Field = param.OrderField
	}
	if param.OrderRule != "" {
		if param.OrderRule == "ASC" || param.OrderRule == "DESC" {
			orderParam.Rule = param.OrderRule
		}
	}
	return fmt.Sprintf("ORDER BY %s %s", orderParam.Field, orderParam.Rule)
}

// 高级语法聚合
type GroupUnitParam struct {
	supportGrams []SqlCondition
}

func (groupUnitParam *GroupUnitParam) GetParamName() string {
	return "GroupUnit"
}

func (groupUnitParam *GroupUnitParam) GetFormatConditionStr(param *QueryParam) (sqlStr string) {
	dbDriver, _ := getDriverInstance(param.Driver)
	if param.Column == "" {
		FileLog.Error("聚合字段为空,无法生成聚合sql")
		return
	}
	var templateSqlStr string
	if _, ok := TemplateMap[GroupUnit][dbDriver]; !ok {
		templateSqlStr = TemplateMap[GroupUnit][MySql]
	} else {
		templateSqlStr = TemplateMap[GroupUnit][dbDriver]
	}
	if templateSqlStr == "" {
		FileLog.Error("聚合sql模板不存在,无法生成聚合sql")
		return
	}
	templateSql, err := template.New("GroupUnit").Parse(templateSqlStr)
	if err != nil {
		FileLog.Error("failed to parse template: %v", err)
		return
	}
	//反射获取结构体的值
	value := reflect.ValueOf(param)
	// 检查是否是指针
	if value.Kind() != reflect.Ptr {
		fmt.Println("请求参数必须是一个结构体")
		return
	}
	// 获取结构体的元素
	elem := value.Elem()
	// 检查是否是结构体
	if elem.Kind() != reflect.Struct {
		fmt.Println("请求参数必须是一个结构体")
		return
	}
	for _, supportGram := range groupUnitParam.supportGrams {
		// 获取字段的值
		fieldValue := elem.FieldByName(string(supportGram))
		// 检查字段是否存在
		if !fieldValue.IsValid() {
			fmt.Printf("Error: field %s not found\n", supportGram)
			continue
		}
		// 检查字段是否可导出
		if !fieldValue.CanSet() {
			fmt.Printf("Error: field %s is not exported and cannot be set\n", supportGram)
			continue
		}
		newValue := sqlGeneratorFactory[supportGram].GetFormatConditionStr(param)
		// 检查新值的类型是否匹配
		newValueValue := reflect.ValueOf(newValue)
		if fieldValue.Type() != newValueValue.Type() {
			fmt.Printf("字段%s类型不匹配 : 期望的类型是 %s, 传入的类型为: %s", supportGram, fieldValue.Type(), newValueValue.Type())
			return
		}
		// 设置字段的值
		fieldValue.Set(newValueValue)
	}
	// 渲染模板
	var buf bytes.Buffer
	err = templateSql.Execute(&buf, param)
	if err != nil {
		fmt.Sprintf("执行模板填充失败: %v", err)
		return
	}
	sqlStr = buf.String()
	fmt.Printf("生成的聚合语句为:%s\n", sqlStr)
	return sqlStr
}

func GroupUnitFunc(driver string, column, delimiter string, columnOrder bool, orderField, orderRule string, hasDistinct bool) (sqlStr string) {
	dbDriver, _ := getDriverInstance(driver)
	if delimiter == "" {
		delimiter = ","
	}
	if column == "" {
		column = "[UNKNOWN COLUMN]"
	}
	//if tableAlia != "" {
	//	column = fmt.Sprintf("%s.%s", tableAlia, column)
	//}
	var sqlOrder, strDistinct string
	if hasDistinct {
		strDistinct = "DISTINCT"
	}
	if columnOrder && orderField == "" {
		orderField = column
	}
	switch dbDriver {
	case DM:
		if columnOrder {
			sqlOrder = fmt.Sprintf("WITHIN GROUP (ORDER BY %s %s)", orderField, orderRule)
		}
		sqlStr = fmt.Sprintf("LISTAGG(%s %s, '%s') %s", strDistinct, column, delimiter, sqlOrder)
	default:
		// 默认使用MySQL的语法
		if columnOrder {
			sqlOrder = fmt.Sprintf("ORDER BY %s %s", orderField, orderRule)
		}
		sqlStr = fmt.Sprintf("GROUP_CONCAT(%s %s %s SEPARATOR '%s')", strDistinct, column, sqlOrder, delimiter)
	}
	return sqlStr
}

func getDriverInstance(driver string) (dbDriver Driver, err error) {
	if driver == "" {
		dbDriver = supportDriverMap[DbDriverName]
	}
	if currentDriver, ok := supportDriverMap[driver]; !ok {
		err = errors.New("不支持的数据库驱动类型")
		return
	} else {
		dbDriver = currentDriver
	}
	return
}
func ReplaceDriverKeywords(driver string, sql string) string {
	dbDriver, _ := getDriverInstance(driver)
	rules := map[Driver]map[string]string{
		DM: {
			"admin":    `"admin"`,
			"value":    `"value"`,
			"exchange": `"exchange"`,
			"size":     `"size"`,
			// 查询的from可能会有个别地方是小写, 所以这里不加而是把字段的from统一改为*.from处理
			"a.from": `a."from"`,
			"b.from": `b."from"`,
			"g.from": `g."from"`,
		},
	}
	replacements, ok := rules[dbDriver]
	if !ok {
		return sql
	}
	for keyword, replace := range replacements {
		// 仅替换单独的单词，复合单词含关键词不管
		pattern := fmt.Sprintf(`\b%s\b`, regexp.QuoteMeta(keyword))
		re := regexp.MustCompile(pattern)
		sql = re.ReplaceAllString(sql, replace)
	}
	return sql
}

func GenerateQuerySql(gram SqlCondition, param *QueryParam) (sqlGenerator string) {
	if exec, ok := sqlGeneratorFactory[gram]; !ok {
		FileLog.Error("不支持的语法:%s", gram)
		return
	} else {
		sqlGenerator = exec.GetFormatConditionStr(param)
	}
	return
}