lxutils/lxDb/sql.go

package lxDb

import (
	"errors"
	"fmt"
	"git.listensoft.net/tool/lxutils/lxUtil"
	"gorm.io/gorm"
	"strings"
)

// 带事务的, 和不带事务的 说明:
// 如果需要支持事务请调aaaTx方法, 并传开启事务的DB

// OneByIdTx 通过ID查询一条
func OneById(tx *gorm.DB, m interface{}, id uint) (err error) {
	if id == 0 {
		return errors.New("主键ID不能为空")
	}
	if tx.Take(m, id).Error != nil { // or First ?
		return errors.New("未找到记录")
	}
	return nil
}

// OneTx 查询一条 使用m的有值属性作为条件, m必须是Model结构体. 条件为空时报错.
func One(tx *gorm.DB, m interface{}) error {
	return oneTx(tx, m, "")
}

func oneTx(tx *gorm.DB, m interface{}, Type string) (err error) {
	//reflectVal := reflect.ValueOf(m)
	//t := reflect.Indirect(reflectVal).Type()
	//newObj := reflect.New(t)
	if lxUtil.IsZeroOfUnderlyingType(m) {
		return errors.New("条件不能为空")
	}

	// 这里有一个特别的情况, 如果m.id有值, 生成sql的where里id条件出现两次, 但是不影响效果
	if Type == "first" { // 第一个
		err = tx.Where(m).First(m).Error
	} else if Type == "last" { // 最后一个
		err = tx.Where(m).Last(m).Error
	} else { // 就是一个
		err = tx.Where(m).Take(m).Error
	}
	if err != nil {
		return err
	}
	return nil
}

// FirstTx 查询第一条 使用m的有值属性作为条件, m必须是Model结构体. 条件为空时报错.
func First(tx *gorm.DB, m interface{}) error {
	return oneTx(tx, m, "first")
}

// LastTx 查询最后一条 使用m的有值属性作为条件, m必须是Model结构体. 条件为空时报错.
func Last(tx *gorm.DB, m interface{}) error {
	return oneTx(tx, m, "last")
}

// One 查询一条. 这种方式时不行的, 实际上对应的表是 base_model
//func (m *BaseModel) One() (err error) {
//	//if DB.Where(m).First(one).RecordNotFound() {
//	if DB.Where(m).First(m).Error != nil {
//		return errors.New("resource is not found")
//	}
//	return nil
//}

// CreateTx 带事务的, 新增
func Create(tx *gorm.DB, m interface{}) error {
	return tx.Create(m).Error
}

// UpdateTx 带事务的, 更新一条数据的单个字段, m.ID必须有值
func Update(tx *gorm.DB, m interface{}, field string, value interface{}) error {
	db := tx.Model(m).Update(field, value)
	if err := db.Error; err != nil {
		return err
	}
	if db.RowsAffected != 1 {
		return errors.New("id is invalid and resource is not found")
	}
	return nil
}

// UpdatesTx 带事务的, 更新一条数据的多个字段, m.ID必须有值
func Updates(tx *gorm.DB, m interface{}, fields interface{}) error {
	db := tx.Model(m).Updates(fields)
	if err := db.Error; err != nil {
		return err
	}
	if db.RowsAffected != 1 {
		return errors.New("id is invalid and resource is not found")
	}
	return nil
}

// DeleteTx 删除, m.ID必须有值. tx不为空就是带事务的
func Delete(tx *gorm.DB, m interface{}) error {
	//func DeleteTx(tx *gorm.DB, m interface{}, conds ...interface{}) error {
	db := tx.Delete(m)
	//db := tx.Delete(m, conds...)
	if err := db.Error; err != nil {
		return err
	}
	if db.RowsAffected != 1 {
		return errors.New("未找到要删除的数据")
	}
	return nil
}

// ListTx 查询数据列表, m是库表结构体, m的有值属性会作为查询条件, 且必须有条件, list里个体的类型可以与m的类型不同
func List(tx *gorm.DB, m interface{}, list interface{}) (err error) {
	if lxUtil.IsZeroOfUnderlyingType(m) {
		return errors.New("条件不能为空")
	}
	if tx.Model(m).Where(m).Find(list).Error != nil {
		// if tx.Where(m).Find(list).Error != nil {
		return errors.New("查询出现错误")
	}
	return nil
}

// ListAllTx 查询所有数据, m是库表结构体, m的有值属性不会作为查询条件, list里个体的类型可以与m的类型不同
func ListAll(tx *gorm.DB, m interface{}, list interface{}) (err error) {
	if tx.Model(m).Find(list).Error != nil {
		// if tx.Where(m).Find(list).Error != nil {
		return errors.New("查询出现错误")
	}
	return nil
}

// QueryTx 带事务的查询数据列表. tx为空就是不带事务, 否则认为是开启了事务. 你应该避免使用次方法, 而是使用Query
func Query(tx *gorm.DB, m interface{}, list interface{}, q *PaginationQuery) (err error) {
	// !! 关于会话 新的Statement实例 及复用 ref: https://gorm.io/zh_CN/docs/method_chaining.html

	if q == nil {
		err = errors.New("paginationQuery不可为nil")
		return
	}

	tx = tx.Model(m)

	// 注意: count, 查询list, summary的顺序不能变.

	tx = q.Build(tx) // 构造查询条件

	// 记录条数
	if needDoCount(q) {
		var total int64
		tx = tx.Count(&total)
		q.Total = int(total)
		if total == 0 { // 如果查了记录条数并且是0, 就不需要查记录和汇总了
			return
		}
	}

	if q.OrderBy != "" {
		tx = tx.Order(lxUtil.FieldToColumn(q.OrderBy)) // TODO: q.OrderBy是字符串,可能多个字段 会有问题吗
		//tx = tx.Order(q.OrderBy)
	}

	if q.Offset > 0 {
		tx = tx.Offset((q.Offset - 1) * q.Limit)
	}
	if q.Limit > 0 {
		tx = tx.Limit(q.Limit)
	}

	// 获取查询值
	err = tx.Find(list).Error

	// 获取汇总信息, 如果不需要查记录条数就不再查汇总
	if needDoCount(q) {
		if q.Summary != "" && len(q.SummarySql) == 0 {
			q.SummarySql = fieldsToSumSql(q.Summary)
		}
		if len(q.Summary) != 0 {
			tx = tx.Offset(-1) // 需要去除offset, 否则结果可能为空, 注意: 设置0不起作用.
			var summary = make(map[string]interface{})
			//tx.Order("") // FIXME: 怎么去掉order by, sum是不需要order by的, 影响性能.
			tx.Select(q.SummarySql).Take(&summary)

			// []byte 转 string. 不太合理, 应该返回int或float
			for k, v := range summary {
				if bs, ok := v.([]byte); ok {
					summary[k] = string(bs)
				}
			}
			q.SummaryResult = summary
		}
	}

	return
}

//// SqlOne 原生SQL查询一个
//func SqlOne(sql string, m interface{}) {
//	DB.Raw(sql, m)
//}
//
//// SqlList 原生SQL查询列表
//func SqlList() {
//
//}

func SqlQuery(tx *gorm.DB, sql string, list interface{}, q *PaginationQuery, params ...interface{}) (err error) {
	var builder strings.Builder
	builder.WriteString(sql)

	if params == nil {
		params = make([]interface{}, 0)
	}

	// 条件字段
	if q != nil {
		where, args := q.BuildRawWhere()
		if hasWhere(sql) { // 原SQL已有WHERE子句
			builder.WriteString(where) // 去掉where 前头的and or ..
		} else { // 原SQL没有WHERE子句
			if strings.HasPrefix(where, " AND ") {
				where = strings.Replace(where, " AND ", " WHERE ", 1)
				builder.WriteString(where)
			} else if strings.HasPrefix(where, " OR ") {
				where = strings.Replace(where, " OR ", " WHERE ", 1)
				builder.WriteString(where)
			} else {
				builder.WriteString(where) // "" 或者 " GROUP BY ..."
			}
		}
		if len(args) > 0 {
			params = append(params, args...)
		}

		// 半成品 sql 用于查询其他信息
		var sql2 = builder.String()

		// 记录条数
		if needDoCount(q) {
			var total int64
			//tx = tx.Count(&total)
			//tx.Raw("SELECT COUNT(*) as total FROM ("+sql2+") aaaa", params...).Take(&total)
			//todo lcs 优化速度 start
			// 替换 SELECT 和 FROM 之间的部分，并去掉 GROUP BY
			replacedSQL := replaceSelectAndRemoveGroupBy(sql2)
			tx.Raw(replacedSQL, params...).Take(&total)
			//todo end
			q.Total = int(total)
			if total == 0 { // 如果查了记录条数并且是0, 就不需要查记录和汇总了
				return
			}

			// 获取汇总信息 // TODO: 汇总应该放到查询列表的后面
			if q.Summary != "" && len(q.SummarySql) == 0 {
				q.SummarySql = fieldsToSumSql(q.Summary)
			}
			if len(q.Summary) != 0 {
				tx = tx.Offset(-1) // 需要去除offset, 否则结果可能为空, 注意: 设置0不起作用.
				var summary = make(map[string]interface{})
				//tx.Order("") // FIXME: 怎么去掉order by, sum是不需要order by的, 影响性能.
				//tx.Select(q.SummarySql).Take(&summary) // 不适合rawsql?

				tx.Raw("SELECT "+strings.Join(q.SummarySql, ", ")+" FROM ("+sql2+") ssss", params...).Take(&summary)

				// []byte 转 string. 不太合理, 应该返回int或float
				for k, v := range summary {
					if bs, ok := v.([]byte); ok {
						summary[k] = string(bs)
					}
				}
				q.SummaryResult = summary
			}
		}

		// 排序处理
		if q.OrderBy != "" {
			s := fmt.Sprintf(" ORDER BY %s", lxUtil.FieldToColumn(q.OrderBy)) // TODO: q.OrderBy是字符串,可能多个字段 会有问题吗
			builder.WriteString(s)
		}

		// 偏移量处理
		if q.Limit > 0 {
			if q.Offset > 0 {
				offset := (q.Offset - 1) * q.Limit
				s := fmt.Sprintf(" LIMIT %d, %d", offset, q.Limit)
				builder.WriteString(s)
			} else {
				s := fmt.Sprintf(" LIMIT %d", q.Limit)
				builder.WriteString(s)
			}
		}
	}

	//tx.Raw(builder.String(), params...).Scan(list) // FIXME: unsupported data type: &[] why?
	tx.Raw(builder.String(), params...).Find(list) // Find与Scan区别: list传入[]时, 查询为空的情况下, Find返回的是[], 而Scan返回的是nil.
	// ref: What is the difference between Find and Scan: https://github.com/go-gorm/gorm/issues/4218

	return
}

// 是否需要查询记录条数
func needDoCount(q *PaginationQuery) bool {
	if q.NoTotal {
		return false
	}

	if q.Limit == 0 { // 不限制条数, 等同于查所有记录, 这时候就不需要查记录条数
		return false
	}

	//return q.Offset <= 1 // todo lcs 为什么要这样写 第二页都没了
	return true
}

// utils -----------------

// "inAmt,inCount" -> ["SUM(int_amt) AS inAmt", "SUM(int_count) AS inCount"]
func fieldsToSumSql(fields string) (sumSqls []string) {
	strs := strings.Split(strings.TrimSpace(fields), ",")
	for _, str := range strs {
		field := strings.TrimSpace(str)
		if field != "" {
			sumSqls = append(sumSqls, "SUM("+lxUtil.FieldToColumn(field)+") AS "+field+"")
		}
	}
	return
}

// SELECT...FROM...[WHERE] 句式的 SQL 中是否存在 WHERE 子句
func hasWhere(sql string) bool {
	deep := 0 // 括号嵌套层数
	step := 0 // "where" 匹配进度

	// 遍历 sql 忽略 ' ( ` 判断是否存在 where
	for i := 0; i < len(sql); i++ {
		switch sql[i] {
		case '(':
			deep++
		case ')':
			deep--
		case 96: // "`"
			// 下一个 "`" 的下标
			// 忽略其他字符
			for i = i + 1; i < len(sql); i++ {
				if sql[i] == 96 {
					break
				}
			}
		case 39: // "'"
			// 下一个 "'" 的下标
			// 忽略其他字符
			for i = i + 1; i < len(sql); i++ {
				if sql[i] == 39 {
					break
				}
			}
		default:
			if deep != 0 {
				continue
			}
			if step == 5 {
				return true
			}
			if sql[i] == where[step][0] || sql[i] == where[step][1] {
				step++
			} else {
				step = 0
			}
		}
	}
	return false
}

var where = []string{
	"Ww",
	"Hh",
	"Ee",
	"Rr",
	"Ee",
}

// Transaction 同时开两个事务的方法
func Transaction(txItem, txMain *gorm.DB, fun func(txItem, txMain *gorm.DB) (err error)) (err error) {
	tx1 := txItem.Begin()
	tx2 := txMain.Begin()
	err = fun(tx1, tx2)
	if err != nil {
		tx1.Rollback()
		tx2.Rollback()
		return err
	}
	err = tx1.Commit().Error
	if err != nil {
		return
	}
	err = tx2.Commit().Error
	if err != nil {
		return
	}
	return
}

// replaceSelectAndRemoveGroupBy 替换 SELECT 和 FROM 之间的部分，并去掉 GROUP BY
func replaceSelectAndRemoveGroupBy(sql string) string {
	// 找到 SELECT 和 FROM 的位置
	selectIndex := strings.Index(strings.ToUpper(sql), "SELECT")
	fromIndex := strings.Index(strings.ToUpper(sql), "FROM")

	if selectIndex == -1 || fromIndex == -1 {
		return sql // 如果没有找到 SELECT 或 FROM，返回原始 SQL
	}

	// 替换 SELECT 和 FROM 之间的部分
	newSelectClause := "COUNT(DISTINCT ***) AS total"
	replacedSQL := sql[:selectIndex+6] + " " + newSelectClause + " " + sql[fromIndex:]

	// 去掉 GROUP BY 子句
	groupByIndex := strings.Index(strings.ToUpper(replacedSQL), "GROUP BY")
	if groupByIndex != -1 {
		c := replacedSQL[groupByIndex+8:]
		c = strings.TrimSpace(c)
		c = strings.ReplaceAll(c, ";", "")
		c = strings.Split(c, ",")[0]
		replacedSQL = replacedSQL[:groupByIndex]
		replacedSQL = strings.ReplaceAll(replacedSQL, "DISTINCT ***", "DISTINCT "+c)
	} else {
		//没有group by 的
		replacedSQL = strings.ReplaceAll(replacedSQL, "DISTINCT ***", "*")
	}

	return replacedSQL
}