construct-query.go

package db_pg_contact

import (
	"infra/not-sure/dbtypes"
	"domains/data/contact"

	"fmt"
	"time"
	// log "github.com/Sirupsen/logrus"
)

// appendToQuery appends the given piece with its args to an existing query.
//
// - `conditions` is a list of WHERE conditions which are assumed to be joined
// with `AND`.
// - `args` is a list of query arguments (so, every `$1` in a query has a matching
// element in `args`).
// - `i` is a total number of query arguments (it's used to determine if we are
// `$1` or `$2` or `$3` or more).
// - `cond` is the new condition to be appended to `conditions` with `arg`
// being its arguments. It's expected to be a fmt-formatted string with
// placeholders for the digits, and their number is expected to match
// `arg` count. For example, if `cond` is `date IS BETWEEN $%v AND $%v`
// we'd expect exactly 2 arguments in `arg`.
func appendToQuery(conditions *[]string, args *[]interface{}, i *uint64, cond string, arg ...interface{}) {
	nums := make([]interface{}, 0, len(arg))
	for _ = range arg {
		*i++
		nums = append(nums, *i)
	}

	*conditions = append(*conditions, fmt.Sprintf(`(`+cond+`)`, nums...))
	*args = append(*args, arg...)
}

// limitToQuery constructs a WHERE clause from the given limiter.
//
// This code should be reasonably fast, but here's an idea how to make it even
// faster: store not conditions but the whole query in array and join it
// with `strings.Join()`.
func limitToQuery(l contact.Limiter, loc *time.Location, t time.Time) (ret string, args []interface{}) {
	conditions := make([]string, 0, 30)
	args = make([]interface{}, 0, 30)
	i := uint64(0)

	if l.Ids != nil {
		appendToQuery(&conditions, &args, &i, `id = ANY($%v::bigint[])`, l.Ids.ToQueryString())
	}
	if l.IdsNot != nil {
		appendToQuery(&conditions, &args, &i, `NOT( id = ANY($%v::bigint[]) )`, l.IdsNot.ToQueryString())
	}

	if l.ProjectID != 0 {
		appendToQuery(&conditions, &args, &i, `project = $%v`, l.ProjectID)
	}
	if l.Projects != nil {
		appendToQuery(&conditions, &args, &i, `project = ANY($%v::integer[])`, l.Projects.ToQueryString())
	}

	if l.RedialAfter != nil {
		appendToQuery(&conditions, &args, &i, `redial_at >= $%v`, *l.RedialAfter)
	}
	if l.RedialBefore != nil {
		appendToQuery(&conditions, &args, &i, `redial_at <= $%v`, *l.RedialBefore)
	}
	if l.RedialEmpty == true {
		appendToQuery(&conditions, &args, &i, `redial_at IS NULL`)
	}

	if l.Quotas != nil {
		appendToQuery(&conditions, &args, &i, `quotas @> $%v::bigint[]`, l.Quotas.ToQueryString())
	}

	if l.DeprecatedState != contact.StatusUnknown {
		l.States = dbtypes.UintSlice{uint64(l.DeprecatedState)}
	}
	if l.States != nil && len(l.States) > 0 {
		appendToQuery(&conditions, &args, &i, `state = ANY($%v::bigint[])`, l.States.ToQueryString())
	}
	if l.Statuses != nil && len(l.Statuses) > 0 {
		appendToQuery(&conditions, &args, &i, `status = ANY($%v::bigint[])`, l.Statuses.ToQueryString())
	}

	if l.TzMin != nil {
		appendToQuery(&conditions, &args, &i, `tz_offset >= $%v`, *l.TzMin)
	}
	if l.TzMax != nil {
		appendToQuery(&conditions, &args, &i, `tz_offset <= $%v`, *l.TzMax)
	}

	if l.LocaltimeMin != nil || l.LocaltimeMax != nil {
		// default 00:00-24:00
		var limmin = uint(0)
		var limmax = uint(1440)

		if l.LocaltimeMin != nil {
			limmin = *l.LocaltimeMin
		}
		if l.LocaltimeMax != nil {
			limmax = *l.LocaltimeMax
		}

		res := timeLim(limmin, limmax, t.In(loc))

		tq := ""
		tzArgs := make([]interface{}, 0, 4)
		for x := 0; x < len(res); x += 2 {
			if x != 0 {
				tq += ` OR `
			}

			tq += `(tz_offset BETWEEN $%v AND $%v)`
			tzArgs = append(tzArgs, res[x])
			tzArgs = append(tzArgs, res[x+1])
		}

		appendToQuery(&conditions, &args, &i, tq, tzArgs...)
	}

	if l.FieldsAny != nil {
		appendToQuery(&conditions, &args, &i, `fields && $%v::integer[]`, l.FieldsAny.ToQueryString())
	}
	if l.FieldsAll != nil {
		appendToQuery(&conditions, &args, &i, `fields @> $%v::integer[]`, l.FieldsAll.ToQueryString())
	}

	// limit by tries count
	if l.TriesMin != nil {
		appendToQuery(&conditions, &args, &i, `tries >= $%v`, *l.TriesMin)
	}
	if l.TriesMax != nil {
		appendToQuery(&conditions, &args, &i, `tries <= $%v`, *l.TriesMax)
	}

	// limit by latest call time
	if l.LatestCallFrom != nil {
		appendToQuery(&conditions, &args, &i, `latest_call_time >= $%v`, *l.LatestCallFrom)
	}
	if l.LatestCallTo != nil {
		appendToQuery(&conditions, &args, &i, `latest_call_time < $%v`, *l.LatestCallTo)
	}

	// Finalize

	for idx, cond := range conditions {
		if idx == 0 {
			ret += " WHERE "
		} else {
			ret += " AND "
		}
		ret += cond
	}

	if l.Limit != 0 {
		i++
		ret += fmt.Sprintf(" LIMIT $%v", i)
		args = append(args, l.Limit)
	}

	// log.WithField("sql", ret).WithField("args", args).Warn("Composed query")
	return
}

// timeLim retrieves border timings for contacts' local time
// (in minutes) and returns real limiting tuples.
// accepts time at TZ at which the clients' timezones are stored.
func timeLim(lower, upper uint, now time.Time) []int {
	var nowMins int
	{
		nowMins = (now.Hour()*60 + now.Minute())
	}

	lima := int(lower) - nowMins
	limb := int(upper) - nowMins

	// <|--|  |--time----|>
	if limb > 720 {
		return []int{-720, (limb - 1440), lima, 720}
	}

	// <|--|   time |-----|>
	if lima < -720 {
		return []int{-720, limb, (1440 + lima), 720}
	}

	// overlapping lims {{{
	if limb < -720 {
		return []int{lima, (1440 + limb)}
	}

	if lima > 720 {
		return []int{(lima - 1440), limb}
	}

	if lima > limb {
		if lima < 0 || limb < 0 {
			return []int{-720, limb, lima, 720}
		}
		return []int{-720, limb, (720 - lima), 720}
	}
	// }}}

	// <    |---time---|  >
	return []int{lima, limb}
}