func (d *insertData) ToSql() (sqlStr string, args []interface{}, err error) {
if len(d.Into) == 0 {
err = fmt.Errorf("insert statements must specify a table")
return
}
if len(d.Values) == 0 {
err = fmt.Errorf("insert statements must have at least one set of values")
return
}
sql := &bytes.Buffer{}
if len(d.Prefixes) > 0 {
args, _ = d.Prefixes.AppendToSql(sql, " ", args)
sql.WriteString(" ")
}
sql.WriteString("INSERT INTO ")
sql.WriteString(d.Into)
sql.WriteString(" ")
if len(d.Columns) > 0 {
sql.WriteString("(")
sql.WriteString(strings.Join(d.Columns, ","))
sql.WriteString(") ")
}
sql.WriteString("VALUES ")
valuesStrings := make([]string, len(d.Values))
for r, row := range d.Values {
valueStrings := make([]string, len(row))
for v, val := range row {
e, isExpr := val.(expr)
if isExpr {
valueStrings[v] = e.sql
args = append(args, e.args...)
} else {
valueStrings[v] = "?"
args = append(args, val)
}
}
valuesStrings[r] = fmt.Sprintf("(%s)", strings.Join(valueStrings, ","))
}
sql.WriteString(strings.Join(valuesStrings, ","))
if len(d.Suffixes) > 0 {
sql.WriteString(" ")
args, _ = d.Suffixes.AppendToSql(sql, " ", args)
}
sqlStr, err = d.PlaceholderFormat.ReplacePlaceholders(sql.String())
return
}
// 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
}
func (d *updateData) ToSql() (sqlStr string, args []interface{}, err error) {
if len(d.Table) == 0 {
err = fmt.Errorf("update statements must specify a table")
return
}
if len(d.SetClauses) == 0 {
err = fmt.Errorf("update statements must have at least one Set clause")
return
}
sql := &bytes.Buffer{}
if len(d.Prefixes) > 0 {
args, _ = d.Prefixes.AppendToSql(sql, " ", args)
sql.WriteString(" ")
}
sql.WriteString("UPDATE ")
sql.WriteString(d.Table)
sql.WriteString(" SET ")
setSqls := make([]string, len(d.SetClauses))
for i, setClause := range d.SetClauses {
var valSql string
e, isExpr := setClause.value.(expr)
if isExpr {
valSql = e.sql
args = append(args, e.args...)
} else {
valSql = "?"
args = append(args, setClause.value)
}
setSqls[i] = fmt.Sprintf("%s = %s", setClause.column, valSql)
}
sql.WriteString(strings.Join(setSqls, ", "))
if len(d.WhereParts) > 0 {
sql.WriteString(" WHERE ")
args, err = appendToSql(d.WhereParts, sql, " AND ", args)
if err != nil {
return
}
}
if len(d.OrderBys) > 0 {
sql.WriteString(" ORDER BY ")
sql.WriteString(strings.Join(d.OrderBys, ", "))
}
if len(d.Limit) > 0 {
sql.WriteString(" LIMIT ")
sql.WriteString(d.Limit)
}
if len(d.Offset) > 0 {
sql.WriteString(" OFFSET ")
sql.WriteString(d.Offset)
}
if len(d.Suffixes) > 0 {
sql.WriteString(" ")
args, _ = d.Suffixes.AppendToSql(sql, " ", args)
}
sqlStr, err = d.PlaceholderFormat.ReplacePlaceholders(sql.String())
return
}
func (d *deleteData) ToSql() (sqlStr string, args []interface{}, err error) {
if len(d.From) == 0 {
err = fmt.Errorf("delete statements must specify a From table")
return
}
sql := &bytes.Buffer{}
if len(d.Prefixes) > 0 {
args, _ = d.Prefixes.AppendToSql(sql, " ", args)
sql.WriteString(" ")
}
sql.WriteString("DELETE FROM ")
sql.WriteString(d.From)
if len(d.WhereParts) > 0 {
sql.WriteString(" WHERE ")
args, err = d.WhereParts.AppendToSql(sql, " AND ", args)
if err != nil {
return
}
}
if len(d.OrderBys) > 0 {
sql.WriteString(" ORDER BY ")
sql.WriteString(strings.Join(d.OrderBys, ", "))
}
if len(d.Limit) > 0 {
sql.WriteString(" LIMIT ")
sql.WriteString(d.Limit)
}
if len(d.Offset) > 0 {
sql.WriteString(" OFFSET ")
sql.WriteString(d.Offset)
}
if len(d.Suffixes) > 0 {
sql.WriteString(" ")
args, _ = d.Suffixes.AppendToSql(sql, " ", args)
}
sqlStr, err = d.PlaceholderFormat.ReplacePlaceholders(sql.String())
return
}
// 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 {
return "", nil, errgo.New("no columns or map specified")
} else if len(b.Maps) == 0 {
if len(b.Vals) == 0 && len(b.Recs) == 0 {
return "", nil, errgo.New("no values or records specified")
}
if len(b.Cols) == 0 && (len(b.Vals) > 0 || len(b.Recs) > 0) {
return "", nil, errgo.New("no columns specified")
}
}
var sql = bufferpool.Get()
sql.WriteString("INSERT INTO ")
sql.WriteString(b.Into)
sql.WriteString(" (")
if len(b.Maps) != 0 {
return b.MapToSql(sql)
}
defer bufferpool.Put(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 {
return "", nil, errgo.Mask(err)
}
for _, v := range vals {
args = append(args, v)
}
}
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.Expression) == 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.QuoteAs())
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 (d *selectData) ToSql() (sqlStr string, args []interface{}, err error) {
if len(d.Columns) == 0 {
err = fmt.Errorf("select statements must have at least one result column")
return
}
sql := &bytes.Buffer{}
if len(d.Prefixes) > 0 {
args, _ = d.Prefixes.AppendToSql(sql, " ", args)
sql.WriteString(" ")
}
sql.WriteString("SELECT ")
if d.Distinct {
sql.WriteString("DISTINCT ")
}
if len(d.Columns) > 0 {
args, err = appendToSql(d.Columns, sql, ", ", args)
if err != nil {
return
}
}
if len(d.From) > 0 {
sql.WriteString(" FROM ")
sql.WriteString(d.From)
}
if len(d.Joins) > 0 {
sql.WriteString(" ")
sql.WriteString(strings.Join(d.Joins, " "))
}
if len(d.WhereParts) > 0 {
sql.WriteString(" WHERE ")
args, err = appendToSql(d.WhereParts, sql, " AND ", args)
if err != nil {
return
}
}
if len(d.GroupBys) > 0 {
sql.WriteString(" GROUP BY ")
sql.WriteString(strings.Join(d.GroupBys, ", "))
}
if len(d.HavingParts) > 0 {
sql.WriteString(" HAVING ")
args, err = appendToSql(d.HavingParts, sql, " AND ", args)
if err != nil {
return
}
}
if len(d.OrderBys) > 0 {
sql.WriteString(" ORDER BY ")
sql.WriteString(strings.Join(d.OrderBys, ", "))
}
if len(d.Limit) > 0 {
sql.WriteString(" LIMIT ")
sql.WriteString(d.Limit)
}
if len(d.Offset) > 0 {
sql.WriteString(" OFFSET ")
sql.WriteString(d.Offset)
}
if len(d.Suffixes) > 0 {
sql.WriteString(" ")
args, _ = d.Suffixes.AppendToSql(sql, " ", args)
}
sqlStr, err = d.PlaceholderFormat.ReplacePlaceholders(sql.String())
return
}