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
}
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}
}
}
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
}
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) {
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
}
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
}
// 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))
}
// 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))
}
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++ {