expression.go

package dbx

import (
	"fmt"
	"reflect"
	"sort"
	"strings"
)

// Expression can build SQL and bind parameters.
type Expression interface {
	Build(db *DB, params Params) string
}

type rawExp struct {
	sql    string
	params Params
}

// Raw builds SQL expression from raw SQL with optional params.
func Raw(sql string, params ...Params) Expression {
	out := Params{}
	if len(params) > 0 {
		for k, v := range params[0] {
			out[k] = v
		}
	}
	return rawExp{sql: sql, params: out}
}

func (e rawExp) Build(_ *DB, params Params) string {
	for k, v := range e.params {
		params[k] = v
	}
	return e.sql
}

// HashExp builds conjunction: col1=val1 AND col2=val2...
type HashExp map[string]any

func (e HashExp) Build(db *DB, params Params) string {
	if len(e) == 0 {
		return ""
	}
	keys := make([]string, 0, len(e))
	for k := range e {
		keys = append(keys, k)
	}
	sort.Strings(keys)

	parts := make([]string, 0, len(keys))
	for _, k := range keys {
		v := e[k]
		col := db.QuoteColumnName(k)
		switch {
		case v == nil:
			parts = append(parts, col+" IS NULL")
		case isSlice(v):
			parts = append(parts, buildIn(db, params, k, v, false))
		case isExpression(v):
			parts = append(parts, col+"=("+v.(Expression).Build(db, params)+")")
		default:
			p := addParam(params, v)
			parts = append(parts, col+"="+p)
		}
	}
	return strings.Join(parts, " AND ")
}

type opExp struct {
	op   string
	args []Expression
}

// And joins expressions with AND.
func And(exprs ...Expression) Expression {
	return opExp{op: "AND", args: exprs}
}

// Or joins expressions with OR.
func Or(exprs ...Expression) Expression {
	return opExp{op: "OR", args: exprs}
}

func (e opExp) Build(db *DB, params Params) string {
	parts := make([]string, 0, len(e.args))
	for _, a := range e.args {
		if a == nil {
			continue
		}
		s := strings.TrimSpace(a.Build(db, params))
		if s == "" {
			continue
		}
		parts = append(parts, "("+s+")")
	}
	return strings.Join(parts, " "+e.op+" ")
}

type notExp struct {
	e Expression
}

// Not negates expression.
func Not(e Expression) Expression {
	return notExp{e: e}
}

func (e notExp) Build(db *DB, params Params) string {
	if e.e == nil {
		return ""
	}
	s := e.e.Build(db, params)
	if strings.TrimSpace(s) == "" {
		return ""
	}
	return "NOT (" + s + ")"
}

type likeExp struct {
	col   string
	value any
}

// Like creates column LIKE '%value%' expression.
func Like(col string, value any) Expression {
	return likeExp{col: col, value: value}
}

func (e likeExp) Build(db *DB, params Params) string {
	p := addParam(params, "%"+fmt.Sprint(e.value)+"%")
	return db.QuoteColumnName(e.col) + " LIKE " + p
}

type inExp struct {
	col    string
	values any
	not    bool
}

// In creates column IN (...) expression.
func In(col string, values any) Expression {
	return inExp{col: col, values: values}
}

// NotIn creates column NOT IN (...) expression.
func NotIn(col string, values any) Expression {
	return inExp{col: col, values: values, not: true}
}

func (e inExp) Build(db *DB, params Params) string {
	return buildIn(db, params, e.col, e.values, e.not)
}

type betweenExp struct {
	col     string
	start   any
	end     any
	negated bool
}

// Between creates BETWEEN expression.
func Between(col string, start, end any) Expression {
	return betweenExp{col: col, start: start, end: end}
}

func (e betweenExp) Build(db *DB, params Params) string {
	p1 := addParam(params, e.start)
	p2 := addParam(params, e.end)
	if e.negated {
		return db.QuoteColumnName(e.col) + " NOT BETWEEN " + p1 + " AND " + p2
	}
	return db.QuoteColumnName(e.col) + " BETWEEN " + p1 + " AND " + p2
}

type existsExp struct {
	query *SelectQuery
	not   bool
}

// Exists creates EXISTS (subquery).
func Exists(q *SelectQuery) Expression {
	return existsExp{query: q}
}

func (e existsExp) Build(_ *DB, params Params) string {
	if e.query == nil {
		return ""
	}
	sql, p := e.query.build()
	for k, v := range p {
		params[k] = v
	}
	if e.not {
		return "NOT EXISTS (" + sql + ")"
	}
	return "EXISTS (" + sql + ")"
}

func buildIn(db *DB, params Params, col string, values any, negated bool) string {
	v := reflect.ValueOf(values)
	if !v.IsValid() || (v.Kind() == reflect.Slice && v.Len() == 0) {
		if negated {
			return "1=1"
		}
		return "1=0"
	}
	if v.Kind() != reflect.Slice && v.Kind() != reflect.Array {
		p := addParam(params, values)
		if negated {
			return db.QuoteColumnName(col) + "!=" + p
		}
		return db.QuoteColumnName(col) + "=" + p
	}
	holders := make([]string, 0, v.Len())
	for i := 0; i < v.Len(); i++ {
		holders = append(holders, addParam(params, v.Index(i).Interface()))
	}
	op := " IN "
	if negated {
		op = " NOT IN "
	}
	return db.QuoteColumnName(col) + op + "(" + strings.Join(holders, ", ") + ")"
}

func addParam(params Params, v any) string {
	if params == nil {
		panic("params cannot be nil")
	}
	for i := len(params) + 1; ; i++ {
		name := fmt.Sprintf("p%d", i)
		if _, ok := params[name]; ok {
			continue
		}
		params[name] = v
		return "{:" + name + "}"
	}
}

func isSlice(v any) bool {
	rv := reflect.ValueOf(v)
	return rv.IsValid() && (rv.Kind() == reflect.Slice || rv.Kind() == reflect.Array)
}

func isExpression(v any) bool {
	_, ok := v.(Expression)
	return ok
}