eta_api/utils/sql.go

package utils

import (
	"errors"
	"fmt"
	"regexp"
)

type Driver string

const (
	DM    Driver = "dm"
	MySql Driver = "mysql"
)

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

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 GroupUnitDistinctFunc(driver string, column, delimiter string) (sqlStr string) {
	dbDriver, _ := getDriverInstance(driver)
	if delimiter == "" {
		delimiter = ","
	}
	if column == "" {
		column = "[UNKNOWN COLUMN]"
	}
	//if tableAlia != "" {
	//	column = fmt.Sprintf("%s.%s", tableAlia, column)
	//}
	switch dbDriver {
	case MySql:
		sqlStr = fmt.Sprintf("GROUP_CONCAT(DISTINCT %s SEPARATOR '%s')", column, delimiter)
	case DM:
		sqlStr = fmt.Sprintf("LISTAGG(DISTINCT %s, '%s') WITHIN GROUP (ORDER BY %s)", column, delimiter, column)
	default:
		sqlStr = fmt.Sprintf("GROUP_CONCAT(DISTINCT  %s SEPARATOR '%s')", column, delimiter) // 默认使用 MySQL 的语法
	}
	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 NeedDateOrTimeFormat(driver string) bool {
	var dbDriver Driver
	if driver == "" {
		dbDriver = supportDriverMap[DbDriverName]
	} else {
		dbDriver, _ = getDriverInstance(driver)
	}
	if dbDriver == DM {
		return true
	}
	return false
}

func ReplaceDriverKeywords(driver string, sql string) string {
	dbDriver, _ := getDriverInstance(driver)
	rules := map[Driver]map[string]string{
		DM: {
			"admin":    `"admin"`,
			"value":    `"value"`,
			"exchange": `"exchange"`,
		},
	}
	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
}