// ApplyTo returns a new SelectBuilder after applying the paging effects of
// `p` to `sql`. This method provides the default case for paging: int64
// cursor-based paging by an id column.
func (p PageQuery) ApplyTo(
sql sq.SelectBuilder,
col string,
) (sq.SelectBuilder, error) {
sql = sql.Limit(p.Limit)
cursor, err := p.CursorInt64()
if err != nil {
return sql, err
}
switch p.Order {
case "asc":
sql = sql.
Where(fmt.Sprintf("%s > ?", col), cursor).
OrderBy(fmt.Sprintf("%s asc", col))
case "desc":
sql = sql.
Where(fmt.Sprintf("%s < ?", col), cursor).
OrderBy(fmt.Sprintf("%s desc", col))
default:
return sql, errors.Errorf("invalid order: %s", p.Order)
}
return sql, nil
}
func addFilterToQuery(s *schema.Schema, q sq.SelectBuilder, filter map[string]interface{}, join bool) sq.SelectBuilder {
if filter == nil {
return q
}
for key, value := range filter {
property, err := s.GetPropertyByID(key)
var column string
if join {
column = makeColumn(s, *property)
} else {
column = quote(key)
}
if err != nil {
log.Notice(err.Error())
continue
}
if property.Type == "boolean" {
v := make([]bool, len(value.([]string)))
for i, j := range value.([]string) {
v[i], _ = strconv.ParseBool(j)
}
q = q.Where(sq.Eq{column: v})
} else {
q = q.Where(sq.Eq{column: value})
}
}
return q
}
func addFilterToQuery(s *schema.Schema, q sq.SelectBuilder, filter map[string]interface{}, join bool) (sq.SelectBuilder, error) {
if filter == nil {
return q, nil
}
for key, value := range filter {
property, err := s.GetPropertyByID(key)
if err != nil {
return q, err
}
var column string
if join {
column = makeColumn(s.GetDbTableName(), *property)
} else {
column = quote(key)
}
queryValues, ok := value.([]string)
if ok && property.Type == "boolean" {
v := make([]bool, len(queryValues))
for i, j := range queryValues {
v[i], _ = strconv.ParseBool(j)
}
q = q.Where(sq.Eq{column: v})
} else {
q = q.Where(sq.Eq{column: value})
}
}
return q, nil
}
func SelectOne(s gorp.SqlExecutor, builder squirrel.SelectBuilder, src interface{}) error {
sql, args, err := builder.ToSql()
if err != nil {
return err
}
return s.SelectOne(src, sql, args...)
}
// Get gets a single row returned by the provided sql builder into the provided dest.
// dest must be a non-slice value of the correct record type.
func (q SqlQuery) Get(ctx context.Context, sql sq.SelectBuilder, dest interface{}) error {
sql = sql.PlaceholderFormat(sq.Dollar)
query, args, err := sql.ToSql()
if err != nil {
return errors.Wrap(err, 1)
}
return q.GetRaw(ctx, query, args, dest)
}
func (q SqlQuery) Query(ctx context.Context, sql sq.SelectBuilder) (*sqlx.Rows, error) {
sql = sql.PlaceholderFormat(sq.Dollar)
query, args, err := sql.ToSql()
if err != nil {
return nil, errors.Wrap(err, 1)
}
return q.QueryRaw(ctx, query, args)
}
func (q SqlQuery) Get(ctx context.Context, sql sq.SelectBuilder, dest interface{}) error {
db := sqlx.NewDb(q.DB, "postgres")
sql = sql.PlaceholderFormat(sq.Dollar)
query, args, err := sql.ToSql()
if err != nil {
return err
}
return db.Get(dest, query, args...)
}
func (q SqlQuery) Select(ctx context.Context, sql sq.SelectBuilder, dest interface{}) error {
db := sqlx.NewDb(q.DB, "postgres")
sql = sql.PlaceholderFormat(sq.Dollar)
query, args, err := sql.ToSql()
if err != nil {
return err
}
log.WithField(ctx, "sql", query).Info("Executing query")
return db.Select(dest, query, args...)
}
func makeJoin(s *schema.Schema, q sq.SelectBuilder) sq.SelectBuilder {
manager := schema.GetManager()
for _, property := range s.Properties {
if property.RelationProperty == "" {
continue
}
relatedSchema, _ := manager.Schema(property.Relation)
q = q.LeftJoin(
quote(relatedSchema.GetDbTableName()) + fmt.Sprintf(" on %s.%s = %s.id", s.GetDbTableName(), property.ID, relatedSchema.GetDbTableName()))
q = makeJoin(relatedSchema, q)
}
return q
}
// loadValue uses QueryBuilder to run SELECT query and fetch single value from database,
// use it for getting ID or counter
func LoadValue(qb *squirrel.SelectBuilder, buf interface{}) error {
query, args, err := qb.ToSql()
if err != nil {
return err
}
err = components.App.Db.Unsafe().QueryRow(query, args...).Scan(buf)
if err != nil && err != sql.ErrNoRows {
return err
}
return nil
}
func LoadStruct(qb *squirrel.SelectBuilder, buf interface{}) error {
query, args, err := qb.ToSql()
if err != nil {
return err
}
err = components.App.Db.Unsafe().Get(buf, query, args...)
if err != nil {
return err
}
return nil
}
func makeJoin(s *schema.Schema, tableName string, q sq.SelectBuilder) sq.SelectBuilder {
manager := schema.GetManager()
for _, property := range s.Properties {
if property.RelationProperty == "" {
continue
}
relatedSchema, _ := manager.Schema(property.Relation)
aliasTableName := makeAliasTableName(tableName, property)
q = q.LeftJoin(
fmt.Sprintf("%s as %s on %s.%s = %s.id", quote(relatedSchema.GetDbTableName()), quote(aliasTableName),
quote(tableName), quote(property.ID), quote(aliasTableName)))
q = makeJoin(relatedSchema, aliasTableName, q)
}
return q
}
func (f *EffectLedgerFilter) Apply(ctx context.Context, sql sq.SelectBuilder) (sq.SelectBuilder, error) {
start := TotalOrderID{LedgerSequence: f.LedgerSequence}
end := TotalOrderID{LedgerSequence: f.LedgerSequence + 1}
return sql.Where(
"(heff.history_operation_id >= ? AND heff.history_operation_id < ?)",
start.ToInt64(),
end.ToInt64(),
), nil
}
func (f *EffectOperationFilter) Apply(ctx context.Context, sql sq.SelectBuilder) (sq.SelectBuilder, error) {
start := ParseTotalOrderID(f.OperationID)
end := start
end.IncOperationOrder()
return sql.Where(
"(heff.history_operation_id >= ? AND heff.history_operation_id < ?)",
start.ToInt64(),
end.ToInt64(),
), nil
}
func (f *EffectAccountFilter) Apply(ctx context.Context, sql sq.SelectBuilder) (sq.SelectBuilder, error) {
var account HistoryAccountRecord
err := Get(ctx, HistoryAccountByAddressQuery{f.SqlQuery, f.AccountAddress}, &account)
if err != nil {
return sql, err
}
return sql.Where("heff.history_account_id = ?", account.Id), nil
}
func whereValueForKey(builder squirrel.SelectBuilder, src interface{}, key, value string) (squirrel.SelectBuilder, error) {
keyParts := strings.Split(key, "-")
sqlString := ""
switch len(keyParts) {
case 1:
if !structHasField(src, keyParts[0]) {
return builder, nil
}
sqlString = keyParts[0] + " = ?"
case 2:
op := Operator(keyParts[0])
field := keyParts[1]
if !structHasField(src, field) {
return builder, nil
}
switch op {
case Lt:
sqlString = field + " < ?"
case Lte:
sqlString = field + " <= ?"
case Gt:
sqlString = field + " > ?"
case Gte:
sqlString = field + " >= ?"
case In:
values := strings.Split(value, ",")
builder = builder.Where(squirrel.Eq{field: values})
return builder, nil
default:
return builder, nil
}
default:
return builder, nil
}
builder = builder.Where(sqlString, value)
return builder, nil
}
func (f *EffectTransactionFilter) Apply(ctx context.Context, sql sq.SelectBuilder) (sq.SelectBuilder, error) {
var tx TransactionRecord
err := Get(ctx, TransactionByHashQuery{f.SqlQuery, f.TransactionHash}, &tx)
if err != nil {
return sql, nil
}
start := ParseTotalOrderID(tx.Id)
end := start
end.TransactionOrder++
return sql.Where(
"(heff.history_operation_id >= ? AND heff.history_operation_id < ?)",
start.ToInt64(),
end.ToInt64(),
), nil
}
func (f *EffectTypeFilter) Apply(ctx context.Context, sql sq.SelectBuilder) (sq.SelectBuilder, error) {
return sql.Where("heff.type = ?", f.Type), nil
}
