Exemplo n.º 1
0
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
}
Exemplo n.º 2
0
// 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
}
Exemplo n.º 3
0
// 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
}
Exemplo n.º 4
0
// 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
}
Exemplo n.º 5
0
// 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
}
Exemplo n.º 6
0
// 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
}