func reflectBlacklistedColumns(v interface{}, blacklist []string) []string {
cols := []string{}
for _, tag := range reflectFields(v).Names {
if str.SliceContains(blacklist, tag) {
continue
}
cols = append(cols, tag)
}
return cols
}
// SetBlacklist creates SET clause(s) using a record and blacklist of columns
func (b *UpdateBuilder) SetBlacklist(rec interface{}, columns ...string) *UpdateBuilder {
val := reflect.Indirect(reflect.ValueOf(rec))
vname := val.String()
vtype := val.Type()
if len(columns) == 0 {
panic("SetBlacklist a list of columns names")
}
for i := 0; i < vtype.NumField(); i++ {
f := vtype.Field(i)
dbName := f.Tag.Get("db")
if dbName == "" {
log.Fatalf("%s must have db struct tags for all fields: `db:\"\"`", vname)
}
if !str.SliceContains(columns, dbName) {
value := val.Field(i).Interface()
b.Set(dbName, value)
}
}
return b
}
// SetWhitelist creates SET clause(s) using a record and whitelist of columns.
// To specify all columns, use "*".
func (b *UpdateBuilder) SetWhitelist(rec interface{}, columns ...string) *UpdateBuilder {
val := reflect.Indirect(reflect.ValueOf(rec))
vname := val.String()
vtype := val.Type()
isWildcard := len(columns) == 0 || columns[0] == "*"
for i := 0; i < vtype.NumField(); i++ {
f := vtype.Field(i)
dbName := f.Tag.Get("db")
if dbName == "" {
log.Fatalf("%s must have db struct tags for all fields: `db:\"\"`", vname)
}
value := val.Field(i).Interface()
if isWildcard {
b.Set(dbName, value)
} else if str.SliceContains(columns, dbName) {
b.Set(dbName, value)
}
}
return b
}
// 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{}) {
if len(b.table) == 0 {
panic("no table specified")
}
lenCols := len(b.cols)
lenRecords := len(b.records)
if lenCols == 0 {
panic("no columns specified")
}
if len(b.vals) == 0 && lenRecords == 0 {
panic("no values or records specified")
}
if lenRecords == 0 && b.cols[0] == "*" {
panic(`"*" can only be used in conjunction with Record`)
}
if lenRecords == 0 && b.isBlacklist {
panic(`Blacklist can only be used in conjunction with Record`)
}
// reflect fields removing blacklisted columns
if lenRecords > 0 && b.isBlacklist {
info := reflectFields(b.records[0])
lenFields := len(info.fields)
cols := []string{}
for i := 0; i < lenFields; i++ {
f := info.fields[i]
if str.SliceContains(b.cols, f.dbName) {
continue
}
cols = append(cols, f.dbName)
}
b.cols = cols
}
// reflect all fields
if lenRecords > 0 && b.cols[0] == "*" {
info := reflectFields(b.records[0])
b.cols = info.Columns()
}
var sql bytes.Buffer
var args []interface{}
sql.WriteString("INSERT INTO ")
sql.WriteString(b.table)
sql.WriteString(" (")
for i, c := range b.cols {
if i > 0 {
sql.WriteRune(',')
}
Dialect.WriteIdentifier(&sql, c)
}
sql.WriteString(") VALUES ")
start := 1
// 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(',')
}
buildPlaceholders(&sql, start, len(row))
for _, v := range row {
args = append(args, v)
start++
}
}
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.records {
if i > 0 || anyVals {
sql.WriteRune(',')
}
ind := reflect.Indirect(reflect.ValueOf(rec))
vals, err := ValuesFor(ind.Type(), ind, b.cols)
if err != nil {
panic(err.Error())
}
buildPlaceholders(&sql, start, len(vals))
for _, v := range vals {
args = append(args, v)
start++
}
}
// Go thru the returning clauses
for i, c := range b.returnings {
if i == 0 {
sql.WriteString(" RETURNING ")
} else {
sql.WriteRune(',')
}
Dialect.WriteIdentifier(&sql, c)
}
return sql.String(), args
}
// ToSQL serialized the UpsertBuilder to a SQL string
// It returns the string with placeholders and a slice of query arguments
func (b *UpsertBuilder) ToSQL() (string, []interface{}) {
if len(b.table) == 0 {
panic("no table specified")
}
lenCols := len(b.cols)
if lenCols == 0 {
panic("no columns specified")
}
if len(b.vals) == 0 && b.record == nil {
panic("no values or records specified")
}
if b.record == nil && b.cols[0] == "*" {
panic(`"*" can only be used in conjunction with Record`)
}
if b.record == nil && b.isBlacklist {
panic(`Blacklist can only be used in conjunction with Record`)
}
// build where clause from columns and values
if len(b.whereFragments) == 0 {
panic("where clause required for upsert")
}
// reflect fields removing blacklisted columns
if b.record != nil && b.isBlacklist {
info := reflectFields(b.record)
lenFields := len(info.fields)
cols := []string{}
for i := 0; i < lenFields; i++ {
f := info.fields[i]
if str.SliceContains(b.cols, f.dbName) {
continue
}
cols = append(cols, f.dbName)
}
b.cols = cols
}
// reflect all fields
if b.record != nil && b.cols[0] == "*" {
info := reflectFields(b.record)
b.cols = info.Columns()
}
if len(b.returnings) == 0 {
b.returnings = b.cols
}
/*
END GOAL:
WITH
new_values (id, field1, field2) AS (
values (1, 'A', 'X'),
(2, 'B', 'Y'),
(3, 'C', 'Z')
),
upsert as
(
update mytable m
set field1 = nv.field1,
field2 = nv.field2
FROM new_values nv
WHERE m.id = nv.id
RETURNING m.*
)
INSERT INTO mytable (id, field1, field2)
SELECT id, field1, field2
FROM new_values
WHERE NOT EXISTS (SELECT 1
FROM upsert up
WHERE up.id = new_values.id)
Upsert("table").
Columns("name", "email").
Values("mario", "*****@*****.**").
Where("name = $1", "mario").
Returning("id", "name", "email")
*/
if b.record != nil {
ind := reflect.Indirect(reflect.ValueOf(b.record))
var err error
b.vals, err = ValuesFor(ind.Type(), ind, b.cols)
if err != nil {
panic(err.Error())
}
}
buf := bufPool.Get()
defer bufPool.Put(buf)
/*
WITH
upd as (
update people
set name = $1,
email = $2
WHERE name = $3
RETURNING id, name, email
),
ins AS (
INSERT INTO people (name, email)
SELECT $1, $2
WHERE NOT EXISTS (SELECT 1 FROM upd)
RETURNING id, name, email
)
SELECT * FROM upd
UNION ALL
SELECT * FROM ins
*/
// TODO refactor this, no need to call update
// builder, just need a few more helper functions
var args []interface{}
buf.WriteString("WITH upd AS ( ")
ub := NewUpdateBuilder(b.table)
for i, col := range b.cols {
ub.Set(col, b.vals[i])
}
ub.whereFragments = b.whereFragments
ub.returnings = b.returnings
updateSQL, args := ub.ToSQL()
buf.WriteString(updateSQL)
buf.WriteString("), ins AS (")
buf.WriteString(" INSERT INTO ")
writeIdentifier(buf, b.table)
buf.WriteString("(")
writeIdentifiers(buf, b.cols, ",")
buf.WriteString(") SELECT ")
writePlaceholders(buf, len(b.vals), ",", 1)
buf.WriteString(" WHERE NOT EXISTS (SELECT 1 FROM upd) RETURNING ")
writeIdentifiers(buf, b.returnings, ",")
buf.WriteString(") SELECT * FROM ins UNION ALL SELECT * FROM upd")
return buf.String(), args
}
// ToSQL serialized the InsectBuilder to a SQL string
// It returns the string with placeholders and a slice of query arguments
func (b *InsectBuilder) ToSQL() (string, []interface{}) {
if len(b.table) == 0 {
panic("no table specified")
}
lenCols := len(b.cols)
if lenCols == 0 {
panic("no columns specified")
}
if len(b.vals) == 0 && b.record == nil {
panic("no values or records specified")
}
if b.record == nil && b.cols[0] == "*" {
panic(`"*" can only be used in conjunction with Record`)
}
if b.record == nil && b.isBlacklist {
panic(`Blacklist can only be used in conjunction with Record`)
}
// reflect fields removing blacklisted columns
if b.record != nil && b.isBlacklist {
info := reflectFields(b.record)
lenFields := len(info.fields)
cols := []string{}
for i := 0; i < lenFields; i++ {
f := info.fields[i]
if str.SliceContains(b.cols, f.dbName) {
continue
}
cols = append(cols, f.dbName)
}
b.cols = cols
}
// reflect all fields
if b.record != nil && b.cols[0] == "*" {
info := reflectFields(b.record)
b.cols = info.Columns()
}
whereAdded := false
// build where clause from columns and values
if len(b.whereFragments) == 0 && b.record == nil {
whereAdded = true
for i, column := range b.cols {
b.whereFragments = append(b.whereFragments, newWhereFragment(column+"=$1", b.vals[i:i+1]))
}
}
if len(b.returnings) == 0 {
b.returnings = b.cols
}
/*
END GOAL:
WITH sel AS (
SELECT id, user_name, auth_id, auth_provider
FROM users
WHERE user_name = $1 and auth_id = $2 and auth_provider = $3
), ins AS (
INSERT INTO users (user_name, auth_id, auth_provider)
SELECT $1, $2, $3
WHERE NOT EXISTS (SELECT 1 FROM sel)
RETURNING id, user_name, auth_id, auth_provider
)
SELECT * FROM ins
UNION ALL
SELECT * FROM sel
*/
if b.record != nil {
ind := reflect.Indirect(reflect.ValueOf(b.record))
var err error
b.vals, err = ValuesFor(ind.Type(), ind, b.cols)
if err != nil {
panic(err.Error())
}
}
buf := bufPool.Get()
defer bufPool.Put(buf)
var args []interface{}
var selectSQL string
buf.WriteString("WITH sel AS (")
sb := NewSelectBuilder(b.returnings...).
From(b.table)
sb.whereFragments = b.whereFragments
selectSQL, args = sb.ToSQL()
buf.WriteString(selectSQL)
buf.WriteString("), ins AS (")
buf.WriteString(" INSERT INTO ")
writeIdentifier(buf, b.table)
buf.WriteString("(")
writeIdentifiers(buf, b.cols, ",")
buf.WriteString(") SELECT ")
if whereAdded {
writePlaceholders(buf, len(args), ",", 1)
} else {
writePlaceholders(buf, len(b.vals), ",", len(args)+1)
args = append(args, b.vals...)
}
buf.WriteString(" WHERE NOT EXISTS (SELECT 1 FROM sel) RETURNING ")
writeIdentifiers(buf, b.returnings, ",")
buf.WriteString(") SELECT * FROM ins UNION ALL SELECT * FROM sel")
return buf.String(), args
}
