sqimple/select.go

package sqimple

import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	"code.aneur.in/go/record"
)

// https://www.sqlite.org/lang_select.html
type SelectQuery struct {
	ctx context.Context
	db  *sql.DB

	queryFunc    func(Query) (*sql.Rows, error)
	queryRowFunc func(Query) *sql.Row

	distinct bool

	columns []Column

	tables []SelectTable

	where []Where
	args  []any

	// group // TODO
	// having // TODO

	orderBy []OrderBy

	limit  int
	offset int
}

func (q *SelectQuery) Args() []any {
	return q.args
}

func (q *SelectQuery) Columns(defs ...string) *SelectQuery {
	if q.columns == nil {
		q.columns = []Column{}
	}

	for _, def := range defs {
		q.columns = append(q.columns, Column(def))
	}

	return q
}

func (q *SelectQuery) Ctx(ctx context.Context) *SelectQuery {
	q.ctx = ctx
	return q
}

func (q *SelectQuery) DB(db *sql.DB) *SelectQuery {
	q.db = db
	return q
}

func (q *SelectQuery) Distinct(distinct bool) *SelectQuery {
	q.distinct = distinct

	return q
}

func (q *SelectQuery) IsValid() bool {
	for _, c := range q.columns {
		if !c.IsValid() {
			return false
		}
	}

	for _, t := range q.tables {
		if !t.IsValid() {
			return false
		}
	}

	for _, w := range q.where {
		if !w.IsValid() {
			return false
		}
	}

	return false
}

func (q *SelectQuery) LeftJoin(def string) *SelectQuery {
	q.tables = append(q.tables, LeftJoin(def))
	return q
}

func (q *SelectQuery) Limit(limit int) *SelectQuery {
	q.limit = limit
	return q
}

func (q *SelectQuery) InnerJoin(def string) *SelectQuery {
	q.tables = append(q.tables, InnerJoin(def))
	return q
}

func (q *SelectQuery) RightJoin(def string) *SelectQuery {
	q.tables = append(q.tables, RightJoin(def))
	return q
}

func (q *SelectQuery) Offset(offset int) *SelectQuery {
	q.offset = offset
	return q
}

func (q *SelectQuery) OrderBy(defs ...string) *SelectQuery {
	if q.orderBy == nil {
		q.orderBy = []OrderBy{}
	}

	for _, def := range defs {
		q.orderBy = append(q.orderBy, OrderBy(def))
	}

	return q
}

func (q *SelectQuery) Query() (*sql.Rows, error) {
	if q.queryFunc != nil {
		return q.queryFunc(q)
	}

	return nil, nil
}

func (q *SelectQuery) QueryFunc(f func(Query) (*sql.Rows, error)) *SelectQuery {
	q.queryFunc = f
	return q
}

func (q *SelectQuery) QueryRecord() (record.Record, error) {
	rows, err := q.Query()
	if err != nil {
		return nil, err
	}

	return ScanOneRecord(rows)
}

func (q *SelectQuery) QueryRecords() ([]record.Record, error) {
	rows, err := q.Query()
	if err != nil {
		return nil, err
	}

	return ScanRecords(rows)
}

func (q *SelectQuery) QueryRow() *sql.Row {
	if q.queryRowFunc != nil {
		return q.queryRowFunc(q)
	}

	return nil
}

func (q *SelectQuery) QueryRowFunc(f func(Query) *sql.Row) *SelectQuery {
	q.queryRowFunc = f
	return q
}

func (q *SelectQuery) String() string {
	strs := []string{"select"}

	if q.distinct {
		strs = append(strs, "distinct")
	}

	if len(q.columns) > 0 {
		for i, column := range q.columns {
			if i < len(q.columns)-1 {
				strs = append(strs, fmt.Sprintf("%s,", column))
			} else {
				strs = append(strs, column.String())
			}
		}
	} else {
		strs = append(strs, "*")
	}

	for _, table := range q.tables {
		strs = append(strs, table.String())
	}

	if len(q.where) > 0 {
		strs = append(strs, "where")

		for _, w := range q.where {
			strs = append(strs, w.String())
		}
	}

	if len(q.orderBy) > 0 {
		strs = append(strs, "order by")

		for i, o := range q.orderBy {
			if i < len(q.orderBy)-1 {
				strs = append(strs, fmt.Sprintf("%s,", o))
			} else {
				strs = append(strs, o.String())
			}
		}
	}

	if q.limit > 0 {
		if q.offset > 0 {
			strs = append(strs, fmt.Sprintf("limit %d offset %d", q.limit, q.offset))
		} else {
			strs = append(strs, fmt.Sprintf("limit %d", q.limit))
		}
	}

	return strings.Join(strs, " ")
}

func (q *SelectQuery) Where(def string, args ...any) *SelectQuery {
	if q.where == nil {
		q.where = []Where{}
	}

	q.where = append(q.where, Where(def))

	if len(args) > 0 {
		if q.args == nil {
			q.args = []any{}
		}

		q.args = append(q.args, args...)
	}

	return q
}

func Select(from string) *SelectQuery {
	q := &SelectQuery{
		tables: []SelectTable{From(from)},
	}

	return q
}