// Preprocess takes an SQL string with placeholders and a list of arguments to
// replace them with. It returns a blank string and error if the number of placeholders
// does not match the number of arguments.
func Preprocess(sql string, vals []interface{}) (string, error) {
// Get the number of arguments to add to this query
if sql == "" {
if len(vals) != 0 {
return "", ErrArgumentMismatch
}
return "", nil
}
curVal := 0
var buf = bufferpool.Get()
defer bufferpool.Put(buf)
pos := 0
for pos < len(sql) {
r, w := utf8.DecodeRuneInString(sql[pos:])
pos += w
switch {
case r == '?':
if curVal >= len(vals) {
return "", ErrArgumentMismatch
}
if err := interpolate(buf, vals[curVal]); err != nil {
return "", err
}
curVal++
case r == '`', r == '\'', r == '"':
p := strings.IndexRune(sql[pos:], r)
if p == -1 {
return "", ErrInvalidSyntax
}
if r == '"' {
r = '\''
}
buf.WriteRune(r)
buf.WriteString(sql[pos : pos+p])
buf.WriteRune(r)
pos += p + 1
case r == '[':
w := strings.IndexRune(sql[pos:], ']')
col := sql[pos : pos+w]
D.EscapeIdent(buf, col)
pos += w + 1 // size of ']'
default:
buf.WriteRune(r)
}
}
if curVal != len(vals) {
return "", ErrArgumentMismatch
}
return buf.String(), nil
}
// MapToSql serialized the InsertBuilder to a SQL string
// It goes through the Maps param and combined its keys/values into the SQL query string
// It returns the string with placeholders and a slice of query arguments
func (b *InsertBuilder) MapToSql(sql *bytes.Buffer) (string, []interface{}, error) {
keys := make([]string, len(b.Maps))
vals := make([]interface{}, len(b.Maps))
i := 0
for k, v := range b.Maps {
keys[i] = k
if dbVal, ok := v.(driver.Valuer); ok {
if val, err := dbVal.Value(); err == nil {
vals[i] = val
} else {
return "", nil, errgo.Mask(err)
}
} else {
vals[i] = v
}
i++
}
var args []interface{}
var placeholder = bufferpool.Get() // Build the placeholder like "(?,?,?)"
defer bufferpool.Put(placeholder)
placeholder.WriteRune('(')
for i, c := range keys {
if i > 0 {
sql.WriteRune(',')
placeholder.WriteRune(',')
}
Quoter.writeQuotedColumn(c, sql)
placeholder.WriteRune('?')
}
sql.WriteString(") VALUES ")
placeholder.WriteRune(')')
sql.WriteString(placeholder.String())
for _, row := range vals {
args = append(args, row)
}
return sql.String(), args, nil
}
// ToSql serialized the DeleteBuilder to a SQL string
// It returns the string with placeholders and a slice of query arguments
func (b *DeleteBuilder) ToSql() (string, []interface{}, error) {
if len(b.From) == 0 {
return "", nil, ErrMissingTable
}
var sql = bufferpool.Get()
defer bufferpool.Put(sql)
var args []interface{}
sql.WriteString("DELETE FROM ")
sql.WriteString(b.From)
// Write WHERE clause if we have any fragments
if len(b.WhereFragments) > 0 {
sql.WriteString(" WHERE ")
writeWhereFragmentsToSql(b.WhereFragments, sql, &args)
}
// Ordering and limiting
if len(b.OrderBys) > 0 {
sql.WriteString(" ORDER BY ")
for i, s := range b.OrderBys {
if i > 0 {
sql.WriteString(", ")
}
sql.WriteString(s)
}
}
if b.LimitValid {
sql.WriteString(" LIMIT ")
fmt.Fprint(sql, b.LimitCount)
}
if b.OffsetValid {
sql.WriteString(" OFFSET ")
fmt.Fprint(sql, b.OffsetCount)
}
return sql.String(), args, nil
}
// ToSql serialized the UpdateBuilder to a SQL string
// It returns the string with placeholders and a slice of query arguments
func (b *UpdateBuilder) ToSql() (string, []interface{}, error) {
if b.RawFullSql != "" {
return b.RawFullSql, b.RawArguments, nil
}
if len(b.Table) == 0 {
return "", nil, ErrMissingTable
}
if len(b.SetClauses) == 0 {
return "", nil, ErrMissingSet
}
var sql = bufferpool.Get()
defer bufferpool.Put(sql)
var args []interface{}
sql.WriteString("UPDATE ")
sql.WriteString(b.Table)
sql.WriteString(" SET ")
// Build SET clause SQL with placeholders and add values to args
for i, c := range b.SetClauses {
if i > 0 {
sql.WriteString(", ")
}
Quoter.writeQuotedColumn(c.column, sql)
if e, ok := c.value.(*expr); ok {
sql.WriteString(" = ")
sql.WriteString(e.Sql)
args = append(args, e.Values...)
} else {
sql.WriteString(" = ?")
args = append(args, c.value)
}
}
// Write WHERE clause if we have any fragments
if len(b.WhereFragments) > 0 {
sql.WriteString(" WHERE ")
writeWhereFragmentsToSql(b.WhereFragments, sql, &args)
}
// Ordering and limiting
if len(b.OrderBys) > 0 {
sql.WriteString(" ORDER BY ")
for i, s := range b.OrderBys {
if i > 0 {
sql.WriteString(", ")
}
sql.WriteString(s)
}
}
if b.LimitValid {
sql.WriteString(" LIMIT ")
fmt.Fprint(sql, b.LimitCount)
}
if b.OffsetValid {
sql.WriteString(" OFFSET ")
fmt.Fprint(sql, b.OffsetCount)
}
return sql.String(), args, nil
}
func interpolate(w QueryWriter, v interface{}) error {
valuer, ok := v.(driver.Valuer)
if ok {
val, err := valuer.Value()
if err != nil {
return err
}
v = val
}
valueOfV := reflect.ValueOf(v)
kindOfV := valueOfV.Kind()
switch {
case v == nil:
w.WriteString("NULL")
case isInt(kindOfV):
var ival = valueOfV.Int()
w.WriteString(strconv.FormatInt(ival, 10))
case isUint(kindOfV):
var uival = valueOfV.Uint()
w.WriteString(strconv.FormatUint(uival, 10))
case kindOfV == reflect.String:
var str = valueOfV.String()
if !utf8.ValidString(str) {
return ErrNotUTF8
}
D.EscapeString(w, str)
case isFloat(kindOfV):
var fval = valueOfV.Float()
w.WriteString(strconv.FormatFloat(fval, 'f', -1, 64))
case kindOfV == reflect.Bool:
D.EscapeBool(w, valueOfV.Bool())
case kindOfV == reflect.Struct:
if typeOfV := valueOfV.Type(); typeOfV == typeOfTime {
t := valueOfV.Interface().(time.Time)
D.EscapeTime(w, t)
} else {
return ErrInvalidValue
}
case kindOfV == reflect.Slice:
typeOfV := reflect.TypeOf(v)
subtype := typeOfV.Elem()
kindOfSubtype := subtype.Kind()
sliceLen := valueOfV.Len()
stringSlice := make([]string, 0, sliceLen)
switch {
case sliceLen == 0:
return ErrInvalidSliceLength
case isInt(kindOfSubtype):
for i := 0; i < sliceLen; i++ {
var ival = valueOfV.Index(i).Int()
stringSlice = append(stringSlice, strconv.FormatInt(ival, 10))
}
case isUint(kindOfSubtype):
for i := 0; i < sliceLen; i++ {
var uival = valueOfV.Index(i).Uint()
stringSlice = append(stringSlice, strconv.FormatUint(uival, 10))
}
case kindOfSubtype == reflect.String:
for i := 0; i < sliceLen; i++ {
var str = valueOfV.Index(i).String()
if !utf8.ValidString(str) {
return ErrNotUTF8
}
var buf = bufferpool.Get()
D.EscapeString(buf, str)
stringSlice = append(stringSlice, buf.String())
bufferpool.Put(buf)
}
default:
return ErrInvalidSliceValue
}
w.WriteRune('(')
w.WriteString(strings.Join(stringSlice, ","))
w.WriteRune(')')
default:
return ErrInvalidValue
}
return nil
}
// ToSql serialized the SelectBuilder to a SQL string
// It returns the string with placeholders and a slice of query arguments
func (b *SelectBuilder) ToSql() (string, []interface{}, error) {
if b.RawFullSql != "" {
return b.RawFullSql, b.RawArguments, nil
}
if len(b.JoinFragments) > 0 {
for _, f := range b.JoinFragments {
if f.columnsAdded == false && len(f.columns) > 0 {
for _, c := range f.columns {
if c != "" {
b.Columns = append(b.Columns, c)
}
}
f.columnsAdded = true
}
}
}
if len(b.Columns) == 0 {
panic("no columns specified")
}
if len(b.FromTable) == 0 {
panic("no table specified")
}
var sql = bufferpool.Get()
defer bufferpool.Put(sql)
var args []interface{}
sql.WriteString("SELECT ")
if b.IsDistinct {
sql.WriteString("DISTINCT ")
}
for i, s := range b.Columns {
if i > 0 {
sql.WriteString(", ")
}
sql.WriteString(s)
}
sql.WriteString(" FROM ")
sql.WriteString(b.FromTable)
if len(b.JoinFragments) > 0 {
for _, f := range b.JoinFragments {
sql.WriteString(" " + f.joinType + " JOIN " + f.table + " ON ")
writeWhereFragmentsToSql(f.onConditions, sql, &args)
}
}
if len(b.WhereFragments) > 0 {
sql.WriteString(" WHERE ")
writeWhereFragmentsToSql(b.WhereFragments, sql, &args)
}
if len(b.GroupBys) > 0 {
sql.WriteString(" GROUP BY ")
for i, s := range b.GroupBys {
if i > 0 {
sql.WriteString(", ")
}
sql.WriteString(s)
}
}
if len(b.HavingFragments) > 0 {
sql.WriteString(" HAVING ")
writeWhereFragmentsToSql(b.HavingFragments, sql, &args)
}
if len(b.OrderBys) > 0 {
sql.WriteString(" ORDER BY ")
for i, s := range b.OrderBys {
if i > 0 {
sql.WriteString(", ")
}
sql.WriteString(s)
}
}
if b.LimitValid {
sql.WriteString(" LIMIT ")
fmt.Fprint(sql, b.LimitCount)
}
if b.OffsetValid {
sql.WriteString(" OFFSET ")
fmt.Fprint(sql, b.OffsetCount)
}
return sql.String(), args, nil
}
// ToSql serialized the InsertBuilder to a SQL string
// It returns the string with placeholders and a slice of query arguments
func (b *InsertBuilder) ToSql() (string, []interface{}, error) {
if len(b.Into) == 0 {
return "", nil, ErrMissingTable
}
if len(b.Cols) == 0 && len(b.Maps) == 0 {
panic("no columns or map specified")
} else if len(b.Maps) == 0 {
if len(b.Vals) == 0 && len(b.Recs) == 0 {
panic("no values or records specified")
}
if len(b.Cols) == 0 && (len(b.Vals) > 0 || len(b.Recs) > 0) {
panic("no columns specified")
}
}
var sql = bufferpool.Get()
defer bufferpool.Put(sql)
sql.WriteString("INSERT INTO ")
sql.WriteString(b.Into)
sql.WriteString(" (")
if len(b.Maps) != 0 {
return b.MapToSql(sql)
}
var args []interface{}
var placeholder = bufferpool.Get() // Build the placeholder like "(?,?,?)"
defer bufferpool.Put(placeholder)
// Simulataneously write the cols to the sql buffer, and build a placeholder
placeholder.WriteRune('(')
for i, c := range b.Cols {
if i > 0 {
sql.WriteRune(',')
placeholder.WriteRune(',')
}
Quoter.writeQuotedColumn(c, sql)
placeholder.WriteRune('?')
}
sql.WriteString(") VALUES ")
placeholder.WriteRune(')')
placeholderStr := placeholder.String()
// Go thru each value we want to insert. Write the placeholders, and collect args
for i, row := range b.Vals {
if i > 0 {
sql.WriteRune(',')
}
sql.WriteString(placeholderStr)
for _, v := range row {
args = append(args, v)
}
}
anyVals := len(b.Vals) > 0
// Go thru the records. Write the placeholders, and do reflection on the records to extract args
for i, rec := range b.Recs {
if i > 0 || anyVals {
sql.WriteRune(',')
}
sql.WriteString(placeholderStr)
ind := reflect.Indirect(reflect.ValueOf(rec))
vals, err := b.valuesFor(ind.Type(), ind, b.Cols)
if err != nil {
panic(err.Error())
}
for _, v := range vals {
args = append(args, v)
}
}
return sql.String(), args, nil
}