sql_helper.go

package database

import (
	"context"
	"database/sql"

	sq "github.com/Masterminds/squirrel"
	"github.com/rs/zerolog"
)

// NewSQLHelper creates a SQLHelper. It must not outlive the given context and sql executor
func NewSQLHelper(ctx context.Context, sqle SQLExecutor, log zerolog.Logger) SQLHelper {
	return SQLHelper{ctx, sqle, log}
}

// SQLHelper is a short lived helper to easily get/select Mapped structures
type SQLHelper struct {
	ctx  context.Context
	sqle SQLExecutor
	log  zerolog.Logger
}

// Get loads a mapped structure
func (h *SQLHelper) Get(obj interface{}, query sq.Sqlizer) error {
	return GetContext(h.ctx, h.sqle, obj, query, &h.log)
}

// GetByPKey loads a mapped structure
func (h *SQLHelper) GetByPKey(obj Mapped, value interface{}) error {
	return h.GetBy(obj, obj.PKeyColumn(), value)
}

// GetBy ...
func (h *SQLHelper) GetBy(obj Mapped, column string, value interface{}) error {
	return h.GetWhere(obj, sq.Eq{column: value})
}

// GetWhere loads a mapped structure
func (h *SQLHelper) GetWhere(obj Mapped, pred interface{}, args ...interface{}) error {
	query := SQ.
		Select(obj.Columns(true)...).
		From(obj.Table()).
		Where(pred, args...)

	return h.Get(obj, query)
}

// Select loads a structure list
func (h *SQLHelper) Select(obj interface{}, query sq.Sqlizer) error {
	return SelectContext(h.ctx, h.sqle, obj, query, &h.log)
}

// Exec executes a query
func (h *SQLHelper) Exec(query sq.Sqlizer) (sql.Result, error) {
	return ExecContext(h.ctx, h.sqle, query, &h.log)
}

// Insert inserts a Mapped into the db
func (h *SQLHelper) Insert(instances ...Mapped) (sql.Result, error) {
	query := SQLInsert(instances...)
	return h.Exec(query)
}

// Upsert upserts a Mapped into the db
func (h *SQLHelper) Upsert(instances ...Mapped) error {
	for _, instance := range instances {
		query := SQLUpsert(instance)
		if _, err := h.Exec(query); err != nil {
			return err
		}
	}
	return nil
}

// SyncRelationStrings ...
func (h *SQLHelper) SyncRelationStrings(
	table string, colFrom string, colTo string,
	colFromValue string, colToValues []string,
) error {
	// Upsert ts les couples
	q := sq.Insert(table).
		Columns(colFrom, colTo)
	for _, value := range colToValues {
		q = q.Values(colFromValue, value)
	}
	q = q.Suffix("ON CONFLICT DO NOTHING")
	if _, err := h.Exec(q); err != nil {
		return err
	}

	filter := sq.And{sq.Eq{colFrom: colFromValue}}
	for _, value := range colToValues {
		filter = append(filter, sq.NotEq{colTo: value})
	}
	// Delete les valeurs non voulues
	if _, err := h.Exec(sq.Delete(table).Where(filter)); err != nil {
		return err
	}
	return nil
}