Example #1
0
File: select.go Project: upper/db
func (qs *selector) As(alias string) Selector {
	if qs.table == nil {
		qs.setErr(errors.New("Cannot use As() without a preceding From() expression"))
		return qs
	}
	last := len(qs.table.Columns) - 1
	if raw, ok := qs.table.Columns[last].(*exql.Raw); ok {
		qs.table.Columns[last] = exql.RawValue("(" + raw.Value + ") AS " + exql.ColumnWithName(alias).Compile(qs.stringer.t))
	}
	return qs
}
Example #2
0
File: convert.go Project: upper/db
func (tu *templateWithUtils) PlaceholderValue(in interface{}) (exql.Fragment, []interface{}) {
	switch t := in.(type) {
	case db.RawValue:
		return exql.RawValue(t.String()), t.Arguments()
	case db.Function:
		fnName := t.Name()
		fnArgs := []interface{}{}

		args, _ := toInterfaceArguments(t.Arguments())
		fragments := []string{}
		for i := range args {
			frag, args := tu.PlaceholderValue(args[i])
			fragments = append(fragments, frag.Compile(tu.Template))
			fnArgs = append(fnArgs, args...)
		}
		return exql.RawValue(fnName + `(` + strings.Join(fragments, `, `) + `)`), fnArgs
	default:
		// Value must be escaped.
		return sqlPlaceholder, []interface{}{in}
	}
}
Example #3
0
File: builder.go Project: upper/db
func columnFragments(template *templateWithUtils, columns []interface{}) ([]exql.Fragment, []interface{}, error) {
	l := len(columns)
	f := make([]exql.Fragment, l)
	args := []interface{}{}

	for i := 0; i < l; i++ {
		switch v := columns[i].(type) {
		case *selector:
			expanded, rawArgs := Preprocess(v.statement().Compile(v.stringer.t), v.Arguments())
			f[i] = exql.RawValue(expanded)
			args = append(args, rawArgs...)
		case db.Function:
			fnName, fnArgs := v.Name(), v.Arguments()
			if len(fnArgs) == 0 {
				fnName = fnName + "()"
			} else {
				fnName = fnName + "(?" + strings.Repeat("?, ", len(fnArgs)-1) + ")"
			}
			expanded, fnArgs := Preprocess(fnName, fnArgs)
			f[i] = exql.RawValue(expanded)
			args = append(args, fnArgs...)
		case db.RawValue:
			expanded, rawArgs := Preprocess(v.Raw(), v.Arguments())
			f[i] = exql.RawValue(expanded)
			args = append(args, rawArgs...)
		case exql.Fragment:
			f[i] = v
		case string:
			f[i] = exql.ColumnWithName(v)
		case interface{}:
			f[i] = exql.ColumnWithName(fmt.Sprintf("%v", v))
		default:
			return nil, nil, fmt.Errorf("Unexpected argument type %T for Select() argument.", v)
		}
	}
	return f, args, nil
}
Example #4
0
File: builder.go Project: upper/db
	cursor *sql.Rows // This is the main query cursor. It starts as a nil value.
	err    error
}

type fieldValue struct {
	fields []string
	values []interface{}
}

var (
	reInvisibleChars       = regexp.MustCompile(`[\s\r\n\t]+`)
	reColumnCompareExclude = regexp.MustCompile(`[^a-zA-Z0-9]`)
)

var (
	sqlPlaceholder = exql.RawValue(`?`)
)

type exprDB interface {
	StatementQuery(stmt *exql.Statement, args ...interface{}) (*sql.Rows, error)
	StatementQueryRow(stmt *exql.Statement, args ...interface{}) (*sql.Row, error)
	StatementExec(stmt *exql.Statement, args ...interface{}) (sql.Result, error)
}

type sqlBuilder struct {
	sess exprDB
	t    *templateWithUtils
}

// WithSession returns a query builder that is bound to the given database session.
func WithSession(sess interface{}, t *exql.Template) (Builder, error) {
Example #5
0
File: select.go Project: upper/db
func (qs *selector) OrderBy(columns ...interface{}) Selector {
	var sortColumns exql.SortColumns

	for i := range columns {
		var sort *exql.SortColumn

		switch value := columns[i].(type) {
		case db.RawValue:
			col, args := Preprocess(value.Raw(), value.Arguments())
			sort = &exql.SortColumn{
				Column: exql.RawValue(col),
			}
			qs.mu.Lock()
			qs.orderByArgs = append(qs.orderByArgs, args...)
			qs.mu.Unlock()
		case db.Function:
			fnName, fnArgs := value.Name(), value.Arguments()
			if len(fnArgs) == 0 {
				fnName = fnName + "()"
			} else {
				fnName = fnName + "(?" + strings.Repeat("?, ", len(fnArgs)-1) + ")"
			}
			expanded, fnArgs := Preprocess(fnName, fnArgs)
			sort = &exql.SortColumn{
				Column: exql.RawValue(expanded),
			}
			qs.mu.Lock()
			qs.orderByArgs = append(qs.orderByArgs, fnArgs...)
			qs.mu.Unlock()
		case string:
			if strings.HasPrefix(value, "-") {
				sort = &exql.SortColumn{
					Column: exql.ColumnWithName(value[1:]),
					Order:  exql.Descendent,
				}
			} else {
				chunks := strings.SplitN(value, " ", 2)

				order := exql.Ascendent
				if len(chunks) > 1 && strings.ToUpper(chunks[1]) == "DESC" {
					order = exql.Descendent
				}

				sort = &exql.SortColumn{
					Column: exql.ColumnWithName(chunks[0]),
					Order:  order,
				}
			}
		default:
			qs.setErr(fmt.Errorf("Can't sort by type %T", value))
			return qs
		}
		sortColumns.Columns = append(sortColumns.Columns, sort)
	}

	qs.mu.Lock()
	qs.orderBy = &exql.OrderBy{
		SortColumns: &sortColumns,
	}
	qs.mu.Unlock()

	return qs
}
Example #6
0
File: insert.go Project: upper/db
func (qi *inserter) processValues() (values []*exql.Values, arguments []interface{}) {
	// TODO: simplify with immutable queries
	var insertNils bool

	for _, enqueuedValue := range qi.enqueuedValues {
		if len(enqueuedValue) == 1 {
			ff, vv, err := Map(enqueuedValue[0], nil)
			if err == nil {
				columns, vals, args, _ := qi.builder.t.ToColumnsValuesAndArguments(ff, vv)

				values, arguments = append(values, vals), append(arguments, args...)

				if len(qi.columns) == 0 {
					for _, c := range columns.Columns {
						qi.columns = append(qi.columns, c)
					}
				} else {
					if len(qi.columns) != len(columns.Columns) {
						insertNils = true
						break
					}
				}
				continue
			}
		}

		if len(qi.columns) == 0 || len(enqueuedValue) == len(qi.columns) {
			arguments = append(arguments, enqueuedValue...)

			l := len(enqueuedValue)
			placeholders := make([]exql.Fragment, l)
			for i := 0; i < l; i++ {
				placeholders[i] = exql.RawValue(`?`)
			}
			values = append(values, exql.NewValueGroup(placeholders...))
		}
	}

	if insertNils {
		values, arguments = values[0:0], arguments[0:0]

		for _, enqueuedValue := range qi.enqueuedValues {
			if len(enqueuedValue) == 1 {
				ff, vv, err := Map(enqueuedValue[0], &MapOptions{IncludeZeroed: true, IncludeNil: true})
				if err == nil {
					columns, vals, args, _ := qi.builder.t.ToColumnsValuesAndArguments(ff, vv)
					values, arguments = append(values, vals), append(arguments, args...)

					if len(qi.columns) != len(columns.Columns) {
						qi.columns = qi.columns[0:0]
						for _, c := range columns.Columns {
							qi.columns = append(qi.columns, c)
						}
					}
				}
				continue
			}
		}
	}

	return
}
Example #7
0
File: convert.go Project: upper/db
// ToWhereWithArguments converts the given parameters into a exql.Where
// value.
func (tu *templateWithUtils) ToWhereWithArguments(term interface{}) (where exql.Where, args []interface{}) {
	args = []interface{}{}

	switch t := term.(type) {
	case []interface{}:
		if len(t) > 0 {
			if s, ok := t[0].(string); ok {
				if strings.ContainsAny(s, "?") || len(t) == 1 {
					s, args = Preprocess(s, t[1:])
					where.Conditions = []exql.Fragment{exql.RawValue(s)}
				} else {
					var val interface{}
					key := s
					if len(t) > 2 {
						val = t[1:]
					} else {
						val = t[1]
					}
					cv, v := tu.ToColumnValues(db.NewConstraint(key, val))
					args = append(args, v...)
					for i := range cv.ColumnValues {
						where.Conditions = append(where.Conditions, cv.ColumnValues[i])
					}
				}
				return
			}
		}
		for i := range t {
			w, v := tu.ToWhereWithArguments(t[i])
			if len(w.Conditions) == 0 {
				continue
			}
			args = append(args, v...)
			where.Conditions = append(where.Conditions, w.Conditions...)
		}
		return
	case db.RawValue:
		r, v := Preprocess(t.Raw(), t.Arguments())
		where.Conditions = []exql.Fragment{exql.RawValue(r)}
		args = append(args, v...)
		return
	case db.Constraints:
		for _, c := range t.Constraints() {
			w, v := tu.ToWhereWithArguments(c)
			if len(w.Conditions) == 0 {
				continue
			}
			args = append(args, v...)
			where.Conditions = append(where.Conditions, w.Conditions...)
		}
		return
	case db.Compound:
		var cond exql.Where

		for _, c := range t.Sentences() {
			w, v := tu.ToWhereWithArguments(c)
			if len(w.Conditions) == 0 {
				continue
			}
			args = append(args, v...)
			cond.Conditions = append(cond.Conditions, w.Conditions...)
		}

		if len(cond.Conditions) > 0 {
			var frag exql.Fragment
			switch t.Operator() {
			case db.OperatorNone, db.OperatorAnd:
				q := exql.And(cond)
				frag = &q
			case db.OperatorOr:
				q := exql.Or(cond)
				frag = &q
			default:
				panic(fmt.Sprintf("Unknown type %T", t))
			}
			where.Conditions = append(where.Conditions, frag)
		}

		return
	case db.Constraint:
		cv, v := tu.ToColumnValues(t)
		args = append(args, v...)
		where.Conditions = append(where.Conditions, cv.ColumnValues...)
		return where, args
	}

	panic(fmt.Sprintf("Unknown condition type %T", term))
}
Example #8
0
File: convert.go Project: upper/db
// ToColumnValues converts the given conditions into a exql.ColumnValues struct.
func (tu *templateWithUtils) ToColumnValues(term interface{}) (cv exql.ColumnValues, args []interface{}) {
	args = []interface{}{}

	switch t := term.(type) {
	case []interface{}:
		l := len(t)
		for i := 0; i < l; i++ {
			column, ok := t[i].(string)

			if !ok {
				p, q := tu.ToColumnValues(t[i])
				cv.ColumnValues = append(cv.ColumnValues, p.ColumnValues...)
				args = append(args, q...)
				continue
			}

			if !strings.ContainsAny(column, "=") {
				column = fmt.Sprintf("%s = ?", column)
			}

			chunks := strings.SplitN(column, "=", 2)

			column = chunks[0]
			format := strings.TrimSpace(chunks[1])

			columnValue := exql.ColumnValue{
				Column:   exql.ColumnWithName(column),
				Operator: "=",
				Value:    exql.RawValue(format),
			}

			ps := strings.Count(format, "?")
			if i+ps < l {
				for j := 0; j < ps; j++ {
					args = append(args, t[i+j+1])
				}
				i = i + ps
			} else {
				panic(fmt.Sprintf("Format string %q has more placeholders than given arguments.", format))
			}

			cv.ColumnValues = append(cv.ColumnValues, &columnValue)
		}
		return cv, args
	case db.Constraint:
		columnValue := exql.ColumnValue{}

		// Guessing operator from input, or using a default one.
		if column, ok := t.Key().(string); ok {
			chunks := strings.SplitN(strings.TrimSpace(column), ` `, 2)
			columnValue.Column = exql.ColumnWithName(chunks[0])
			if len(chunks) > 1 {
				columnValue.Operator = chunks[1]
			}
		} else {
			if rawValue, ok := t.Key().(db.RawValue); ok {
				columnValue.Column = exql.RawValue(rawValue.Raw())
				args = append(args, rawValue.Arguments()...)
			} else {
				columnValue.Column = exql.RawValue(fmt.Sprintf("%v", t.Key()))
			}
		}

		switch value := t.Value().(type) {
		case db.Function:
			fnName, fnArgs := value.Name(), value.Arguments()
			if len(fnArgs) == 0 {
				// A function with no arguments.
				fnName = fnName + "()"
			} else {
				// A function with one or more arguments.
				fnName = fnName + "(?" + strings.Repeat("?, ", len(fnArgs)-1) + ")"
			}
			expanded, fnArgs := Preprocess(fnName, fnArgs)
			columnValue.Value = exql.RawValue(expanded)
			args = append(args, fnArgs...)
		case db.RawValue:
			expanded, rawArgs := Preprocess(value.Raw(), value.Arguments())
			columnValue.Value = exql.RawValue(expanded)
			args = append(args, rawArgs...)
		default:
			v, isSlice := toInterfaceArguments(value)

			if isSlice {
				if columnValue.Operator == "" {
					columnValue.Operator = sqlInOperator
				}
				if len(v) > 0 {
					// Array value given.
					columnValue.Value = exql.RawValue(fmt.Sprintf(`(?%s)`, strings.Repeat(`, ?`, len(v)-1)))
				} else {
					// Single value given.
					columnValue.Value = exql.RawValue(`(NULL)`)
				}
				args = append(args, v...)
			} else {
				if v == nil {
					// Nil value given.
					columnValue.Value = sqlNull
					if columnValue.Operator == "" {
						columnValue.Operator = sqlIsOperator
					}
				} else {
					columnValue.Value = sqlPlaceholder
					args = append(args, v...)
				}
			}

		}

		// Using guessed operator if no operator was given.
		if columnValue.Operator == "" {
			if tu.DefaultOperator != "" {
				columnValue.Operator = tu.DefaultOperator
			} else {
				columnValue.Operator = sqlDefaultOperator
			}
		}

		cv.ColumnValues = append(cv.ColumnValues, &columnValue)

		return cv, args
	case db.RawValue:
		columnValue := exql.ColumnValue{}
		p, q := Preprocess(t.Raw(), t.Arguments())

		columnValue.Column = exql.RawValue(p)
		args = append(args, q...)

		cv.ColumnValues = append(cv.ColumnValues, &columnValue)
		return cv, args
	case db.Constraints:
		for _, c := range t.Constraints() {
			p, q := tu.ToColumnValues(c)
			cv.ColumnValues = append(cv.ColumnValues, p.ColumnValues...)
			args = append(args, q...)
		}
		return cv, args
	}

	panic(fmt.Sprintf("Unknown term type %T.", term))
}
Example #9
0
File: convert.go Project: upper/db
package sqlbuilder

import (
	"database/sql/driver"
	"fmt"
	"reflect"
	"strings"

	"upper.io/db.v2"
	"upper.io/db.v2/internal/sqladapter/exql"
)

var (
	sqlNull            = exql.RawValue(`NULL`)
	sqlIsOperator      = `IS`
	sqlInOperator      = `IN`
	sqlDefaultOperator = `=`
)

type templateWithUtils struct {
	*exql.Template
}

func newTemplateWithUtils(template *exql.Template) *templateWithUtils {
	return &templateWithUtils{template}
}

func expandQuery(in string, args []interface{}, fn func(interface{}) (string, []interface{})) (string, []interface{}) {
	argn := 0
	argx := make([]interface{}, 0, len(args))
	for i := 0; i < len(in); i++ {