golib/infra/om/querybuilder.go

package om

import (
	"fmt"
	"reflect"
	"strings"

	"golib/v3/features/sdb"
)

type Builder struct {
	table   string
	query   []Condition
	limit   int64
	offset  int64
	orders  []string
	groupBy string
}

func (b *Builder) Table(table string) {
	b.table = table
}

func (b *Builder) Query(params Params) error {
	for k, v := range params {
		if err := b.addQueryCondition(k, v); err != nil {
			return err
		}
	}
	return nil
}

func (b *Builder) GroupBy(groupBy string) {
	b.groupBy = groupBy
}

func (b *Builder) Limit(params LimitParams) {
	b.limit = params.Limit
	b.offset = params.Offset
}

func (b *Builder) OrderBy(orderBy OrderBy) {
	for k, v := range orderBy {
		b.orders = append(b.orders, k+" "+string(v))
	}
}

func (b *Builder) addQueryCondition(key string, value any) error {
	switch key[:1] {
	case "-":
		b.query = append(b.query, NewCondition(key[1:], value, Like))
	case "%":
		if v, ok := value.(string); ok {
			b.query = append(b.query, NewCondition(key[1:], "%"+v+"%", Like))
		} else {
			return fmt.Errorf("addQueryCondition: add filter err: startswith not string key: %s val: %v", key, value)
		}
	case ">":
		b.query = append(b.query, NewCondition(key[1:], value, Ge))
	case "<":
		b.query = append(b.query, NewCondition(key[1:], value, Le))
	case "|":
		// only slice/array params supported
		rvk := reflect.ValueOf(value).Kind()
		if rvk != reflect.Slice && rvk != reflect.Array {
			return fmt.Errorf("addQueryCondition: only slice/array params supported: key: %s val: %v", key, value)
		}
		b.query = append(b.query, NewCondition(key[1:], value, Equ))
	case "!":
		// single or slice/array params supported
		b.query = append(b.query, NewCondition(key[1:], value, UnEqu))
	default:
		b.query = append(b.query, NewCondition(key, value))
	}
	return nil
}

func (b *Builder) GetConditionSQLs() string {
	var sql string
	if len(b.query) > 0 {
		for _, cond := range b.query {
			if len(sql) > 0 {
				sql = sql + AND + " "
			}
			rv := reflect.ValueOf(cond.Value)

			switch rv.Kind() {
			case reflect.Slice, reflect.Array:
				sql = fmt.Sprintf("%s ( %s %s ? ", sql, cond.FieldName, cond.Opt)
				// start with 1
				for i := 1; i < rv.Len(); i++ {
					sql = fmt.Sprintf("%s OR %s %s ? ", sql, cond.FieldName, cond.Opt)
				}
				sql = sql + ")" + " "
			default:
				// sql + AND table.sec opt ?
				sql = sql + cond.FieldName + " " + cond.Opt + " ? "
			}
		}
	}
	return sql
}

func (b *Builder) GetCountSQL() string {
	sql := fmt.Sprintf("SELECT COUNT(sn) as count FROM %s ", b.table)
	if len(b.query) > 0 {
		sql = sql + "WHERE " + b.GetConditionSQLs()
	}
	if b.groupBy != "" {
		sql = sql + " GROUP BY " + b.groupBy
	}
	return sql
}

func (b *Builder) GetSumSQL() string {
	sql := fmt.Sprintf("SELECT ROUND(SUM(%s),2) FROM %s ", b.groupBy, b.table)
	if len(b.query) > 0 {
		sql = sql + "WHERE " + b.GetConditionSQLs()
	}
	return sql
}

func (b *Builder) GetDeleteSQL() string {
	sql := fmt.Sprintf("DELETE FROM %s ", b.table)
	if len(b.query) > 0 {
		sql = sql + "WHERE " + b.GetConditionSQLs()
		return sql
	}
	return b.GetCustomerSQL(sql)
}

func (b *Builder) GetSelectSQL() string {
	sql := fmt.Sprintf("SELECT * FROM %s ", b.table)
	return b.GetCustomerSQL(sql)
}

func (b *Builder) GetCustomerSQL(sql string) string {
	if !strings.HasSuffix(sql, " ") {
		sql = sql + " "
	}
	if len(b.query) > 0 {
		if strings.Contains(strings.ToUpper(sql), "WHERE") {
			sql = sql + "AND "
		} else {
			sql = sql + "WHERE "
		}
		sql = sql + b.GetConditionSQLs()
	}
	if b.groupBy != "" {
		sql = sql + " GROUP BY " + b.groupBy + " "
	}
	if len(b.orders) > 0 {
		sql = sql + "ORDER BY "
		for idx, v := range b.orders {
			if idx > 0 {
				sql = sql + ", "
			}
			sql = sql + v + " "
		}
	}
	if b.limit > 0 {
		sql = sql + fmt.Sprintf("LIMIT %d ", b.limit)
	}
	if b.offset > 0 {
		if b.limit == 0 {
			sql = sql + "LIMIT -1 " // SQLte3 also requires Limit to exist if OFFSET exists
		}
		sql = sql + fmt.Sprintf("OFFSET %d ", b.offset)
	}
	return sql
}

func (b *Builder) GetValues() []any {
	values := make([]any, 0)
	for _, cond := range b.query {
		rv := reflect.ValueOf(cond.Value)
		switch rv.Kind() {
		case reflect.Slice, reflect.Array:
			for i := 0; i < rv.Len(); i++ {
				values = append(values, rv.Index(i).Interface())
			}
		default:
			values = append(values, cond.Value)
		}
	}
	return values
}

func NewBuilder() *Builder {
	o := &Builder{}
	return o
}

func CreateUpdateSql(table string, valueFields []string, idFields ...string) string {
	sep := fmt.Sprintf("%s = ?, %s", Q, Q)
	columns := strings.Join(valueFields, sep)
	sql := fmt.Sprintf("UPDATE %s%s%s SET %s%s%s = ?", Q, table, Q, Q, columns, Q)

	if len(idFields) > 0 {
		idColumns := strings.Join(idFields, " = ? AND ")
		sql = fmt.Sprintf("%s WHERE %s = ?", sql, idColumns)
	} else {
		// 如果不存在更新条件, 则更新所有数据
		// realIdFields = []string{defaultQueryField}
	}
	return sql
}

func CreateInsertSQL(table string, cols []string) string {
	mark := make([]string, len(cols))
	for i := range mark {
		mark[i] = "?"
	}
	sep := fmt.Sprintf("%s, %s", Q, Q)
	columns := strings.Join(cols, sep)
	qMarks := strings.Join(mark, ", ")
	return fmt.Sprintf(`INSERT INTO '%s' ('%s') VALUES (%s)`, table, columns, qMarks)
}

func CreateInsertSqlWithNum(table string, cols []string, max int) string {
	mark := make([]string, len(cols))
	for i := range mark {
		mark[i] = "?"
	}
	sep := fmt.Sprintf("%s, %s", Q, Q)
	qMarks := strings.Join(mark, ", ")
	columns := strings.Join(cols, sep)

	header := fmt.Sprintf(`INSERT INTO '%s' ('%s') `, table, columns)

	vl := make([]string, max)
	for i := 0; i < max; i++ {
		vl[i] = fmt.Sprintf("(%s)", qMarks)
	}

	header += fmt.Sprintf("VALUES %s", strings.Join(vl, ", "))
	return header
}

type TableColumn struct {
	Key     string
	Type    string
	Default any
	Notnull bool
	Unique  bool
}

func (t TableColumn) SQL() string {
	notNull := func() string {
		if t.Notnull {
			return "NOT NULL "
		}
		return "NULL "
	}
	value := func() string {
		if t.Default == nil {
			return ""
		}
		switch t.Type {
		case sdb.TypeINTEGER, sdb.TypeREAL, sdb.TypeUINT:
			return fmt.Sprintf(`DEFAULT %v `, t.Default)
		case sdb.TypeTEXT:
			return fmt.Sprintf(`DEFAULT '%v' `, t.Default)
		case sdb.TypeBOOLEAN:
			if t.Default == true {
				return `DEFAULT 1 `
			} else {
				return `DEFAULT 0 `
			}
		default:
			return ""
		}
	}
	unique := func() string {
		if t.Unique {
			return "UNIQUE "
		}
		return ""
	}
	return fmt.Sprintf(`%s %s %s%s%s`, t.Key, t.Type, notNull(), unique(), value())
}

func CreateTableSQL(name string, column []TableColumn) string {
	column = append(column,
		TableColumn{Key: "sn", Type: sdb.TypeTEXT, Notnull: true, Unique: true},
	)
	str := make([]string, len(column))
	for i, col := range column {
		str[i] = col.SQL()
	}
	sql := `CREATE TABLE %s (id INTEGER PRIMARY KEY Autoincrement NOT NULL, %s, creationTime INTEGER DEFAULT CURRENT_TIMESTAMP)`
	return fmt.Sprintf(sql, name, strings.Join(str, ", "))
}