Exemplo n.º 1
0
// Get executes the query, returning any found results
func (q LedgerStateQuery) Select(ctx context.Context, dest interface{}) error {
	hSql := sq.
		Select("MAX(sequence) as horizonsequence").
		From("history_ledgers")

	scSql := sq.
		Select("MAX(ledgerseq) as stellarcoresequence").
		From("ledgerheaders")

	var result LedgerState

	err := q.Horizon.Get(ctx, hSql, &result)

	if err != nil {
		return err
	}

	err = q.Core.Get(ctx, scSql, &result)

	if err != nil {
		return err
	}

	setOn([]LedgerState{result}, dest)
	return nil
}
Exemplo n.º 2
0
func Query(src interface{}, tableName string, values url.Values) (sql string, args []interface{}, err error) {
	builder := squirrel.Select("*").From(tableName)

	// add where clause
	for key, value := range values {
		nBuilder, err := whereValueForKey(builder, src, key, value[0])
		if err != nil {
			return "", []interface{}{}, err
		}
		builder = nBuilder
	}

	// add order if it exists
	if oVal := values.Get(KeyOrder); oVal != "" {
		field, order, err := orderFromValue(src, oVal)
		if err != nil {
			return "", []interface{}{}, err
		}
		builder = builder.OrderBy(field + " " + string(order))
	}

	// add limit and offset
	builder = builder.Limit(uintFromKey(values, KeyLimit, 1000))
	builder = builder.Offset(uintFromKey(values, KeyOffset, 0))

	return builder.ToSql()
}
Exemplo n.º 3
0
func SelectAllEquipmentInfo(dbm *gorp.DbMap) ([]EquipmentInfo, error) {
	var results []EquipmentInfo

	q := sq.Select(
		`"EquipmentInfo"."ID"`,
		`"EquipmentInfo"."CertifiedName"`,
		`"RadioAccessTechnology"."Description" AS EquipmentType`,
		`"EquipmentInfo"."Model"`,
		`"EquipmentInfo"."AuthNumber"`,
		`"EquipmentInfo"."RadioType"`,
		`"EquipmentInfo"."IsApplied1421"`,
		`"EquipmentInfo"."AuthDate"`,
		`"EquipmentInfo"."Note"`,
		`"EquipmentInfo"."File"`,
	).From(
		"EquipmentInfo",
	).LeftJoin(
		`"RadioAccessTechnology" ON "EquipmentInfo"."EquipmentType" = "RadioAccessTechnology"."EquipmentType"`,
	)

	sql, _, err := q.ToSql()
	if err != nil {
		return results, err
	}

	if _, err := dbm.Select(&results, sql); err != nil {
		return nil, err
	}

	return results, nil
}
Exemplo n.º 4
0
func (p *Place) Read(tx *sql.Tx) error {
	row := sq.Select("*").Where(sq.Eq{"id": p.ID}).RunWith(tx).QueryRow()
	if err := row.Scan(p.Row()...); err != nil {
		return err
	}
	return nil
}
func BenchmarkSquirrelBuilderComplex(b *testing.B) {
	arg_eq1 := squirrel.Eq{"f": 2, "x": "hi"}
	arg_eq2 := map[string]interface{}{"g": 3}
	arg_eq3 := squirrel.Eq{"h": []int{1, 2, 3}}

	b.ResetTimer()
	for n := 0; n < b.N; n++ {
		squirrel.Select("a", "b", "z", "y", "x").
			Distinct().
			From("c").
			Where("d = ? OR e = ?", 1, "wat").
			Where(arg_eq1).
			Where(arg_eq2).
			Where(arg_eq3).
			GroupBy("i").
			GroupBy("ii").
			GroupBy("iii").
			Having("j = k").
			Having("jj = ?", 1).
			Having("jjj = ?", 2).
			OrderBy("l").
			OrderBy("l").
			OrderBy("l").
			Limit(7).
			Offset(8).
			ToSql()
	}
}
Exemplo n.º 6
0
Arquivo: sql.go Projeto: vozhyk-/gohan
//StateFetch fetches the state of the specified resource
func (tx *Transaction) StateFetch(s *schema.Schema, filter transaction.Filter) (state transaction.ResourceState, err error) {
	if !s.StateVersioning() {
		err = fmt.Errorf("Schema %s does not support state versioning.", s.ID)
		return
	}
	cols := makeStateColumns(s)
	q := sq.Select(cols...).From(quote(s.GetDbTableName()))
	q, _ = addFilterToQuery(s, q, filter, true)
	sql, args, err := q.ToSql()
	if err != nil {
		return
	}
	logQuery(sql, args...)
	rows, err := tx.transaction.Queryx(sql, args...)
	if err != nil {
		return
	}
	defer rows.Close()
	if !rows.Next() {
		err = fmt.Errorf("No resource found")
		return
	}
	data := map[string]interface{}{}
	rows.MapScan(data)
	err = decodeState(data, &state)
	return
}
Exemplo n.º 7
0
func QueryQuestions(db *sqlx.DB, authorId int, query string, offset int) ([]Question, error) {
	qs := []Question{}
	qry := sq.Select("*").From("questions")

	if authorId != 0 {
		qry = qry.Where("author_id = ?", authorId)
	}
	if query != "" {
		word := fmt.Sprint("%", query, "%")
		qry = qry.Where("(title LIKE ? OR question LIKE ?)", word, word)
	}
	if offset > 0 {
		qry = qry.Offset(uint64(offset))
	}

	qry = qry.OrderBy("created_at DESC")
	qry = qry.PlaceholderFormat(sq.Dollar)
	sql, params, err := qry.ToSql()

	if err != nil {
		return qs, err
	} else {
		err := db.Select(&qs, sql, params...)
		dbErr := dbError(err)
		return qs, dbErr
	}
}
Exemplo n.º 8
0
// TransactionsByLedger is a query that loads all rows from `txhistory` where
// ledgerseq matches `Sequence.`
func (q *Q) TransactionsByLedger(dest interface{}, seq int32) error {
	sql := sq.Select("ctxh.*").
		From("txhistory ctxh").
		OrderBy("ctxh.txindex ASC").
		Where("ctxh.ledgerseq = ?", seq)

	return q.Select(dest, sql)
}
func (q SequenceByAddressQuery) Select(ctx context.Context, dest interface{}) error {
	sql := sq.
		Select("seqnum as sequence", "accountid as address").
		From("accounts").
		Where(sq.Eq{"accountid": q.Addresses})

	return q.SqlQuery.Select(ctx, sql, dest)
}
Exemplo n.º 10
0
// LedgerHeaderBySequence is a query that loads a single row from the
// `ledgerheaders` table.
func (q *Q) LedgerHeaderBySequence(dest interface{}, seq int32) error {
	sql := sq.Select("clh.*").
		From("ledgerheaders clh").
		Limit(1).
		Where("clh.ledgerseq = ?", seq)

	return q.Get(dest, sql)
}
Exemplo n.º 11
0
func (r *DaoResource) builderApplication() *sq.SelectBuilder {
	builder := sq.Select(
		"id", "token", "provider_id", "platform", "provider",
		"name", "attributes", "created_at", "updated_at").
		From(model.TableApplications)

	return &builder
}
Exemplo n.º 12
0
// SequencesForAddresses loads the current sequence number for every accountid
// specified in `addys`
func (q *Q) SequencesForAddresses(dest interface{}, addys []string) error {
	sql := sq.
		Select("seqnum as sequence", "accountid as address").
		From("accounts").
		Where(sq.Eq{"accountid": addys})

	return q.Select(dest, sql)
}
Exemplo n.º 13
0
// TransactionByHash is a query that loads a single row from the `txhistory`.
func (q *Q) TransactionByHash(dest interface{}, hash string) error {
	sql := sq.Select("ctxh.*").
		From("txhistory ctxh").
		Limit(1).
		Where("ctxh.txid = ?", hash)

	return q.Get(dest, sql)
}
Exemplo n.º 14
0
func ValidateToken(s gorp.SqlExecutor, memberID int64, token string) error {
	query := squirrel.Select("*").From(TableNameToken).
		Where(squirrel.Eq{"MemberID": memberID, "Value": token})
	tokens := []*Token{}
	sqlutil.Select(s, query, &tokens)
	if len(tokens) == 0 {
		return fmt.Errorf("token not found")
	}
	return nil
}
Exemplo n.º 15
0
func GetID(dbmap *gorp.DbMap, m CrudResource, id interface{}) error {
	query := squirrel.Select("*").
		From(m.TableName()).
		Where(squirrel.Eq{"ID": id})
	if err := sqlutil.SelectOne(dbmap, query, m); err != nil {
		message := fmt.Sprintf("Could not find %s.", m.TableName())
		return httperr.New(http.StatusNotFound, message, err)
	}
	return nil
}
Exemplo n.º 16
0
func (r *DaoResource) GetEndpointByToken(token string) (*model.Endpoint, error) {
	var entity model.Endpoint

	q := sq.Select("id", "token", "provider_id", "application_id", "attributes", "created_at", "updated_at", "enabled").
		From(model.TableEndpoints).
		Where("token = ?", token)

	err := r.Storage.SelectOne(&entity, &q)
	return &entity, err
}
Exemplo n.º 17
0
Arquivo: user.go Projeto: haste/tiers
func GetAllProfiles(user_id int, timestamp int64) []profile.Profile {
	query := squirrel.Select(`
		id, user_id, timestamp,
		agent, level, ap,
		unique_portals_visited, portals_discovered, xm_collected,
		hacks, resonators_deployed, links_created, control_fields_created, mind_units_captured,
		longest_link_ever_created, largest_control_field, xm_recharged, portals_captured,
		unique_portals_captured, mods_deployed,
		resonators_destroyed, portals_neutralized, enemy_links_destroyed, enemy_control_fields_destroyed,
		distance_walked,
		max_time_portal_held, max_time_link_maintained, max_link_length_x_days, max_time_field_held,
		largest_field_mus_x_days,
		glyph_hack_points, consecutive_days_hacking,
		unique_missions_completed,
		agents_successfully_recruited,
		innovator
	`).From("tiers_profiles").OrderBy("timestamp ASC")

	query = query.Where(
		squirrel.And{
			squirrel.Eq{"user_id": user_id},
			squirrel.Expr("timestamp >= ?", timestamp),
		},
	)

	// XXX: Handle errors.
	rows, _ := query.RunWith(db).Query()
	defer rows.Close()

	var profiles []profile.Profile
	for rows.Next() {
		var p profile.Profile

		rows.Scan(
			&p.Id, &p.UserId, &p.Timestamp,
			&p.Nick, &p.Level, &p.AP,
			&p.UniquePortalsVisited, &p.PortalsDiscovered, &p.XMCollected,
			&p.Hacks, &p.ResonatorsDeployed, &p.LinksCreated, &p.ControlFieldsCreated, &p.MindUnitsCaptured,
			&p.LongestLinkEverCreated, &p.LargestControlField, &p.XMRecharged, &p.PortalsCaptured,
			&p.UniquePortalsCaptured, &p.ModsDeployed,
			&p.ResonatorsDestroyed, &p.PortalsNeutralized, &p.EnemyLinksDestroyed, &p.EnemyControlFieldsDestroyed,
			&p.DistanceWalked,
			&p.MaxTimePortalHeld, &p.MaxTimeLinkMaintained, &p.MaxLinkLengthXDays, &p.MaxTimeFieldHeld,
			&p.LargestFieldMUsXDays,
			&p.GlyphHackPoints, &p.ConsecutiveDaysHacking,
			&p.UniqueMissionsCompleted,
			&p.AgentsSuccessfullyRecruited,
			&p.InnovatorLevel,
		)

		profiles = append(profiles, p)
	}

	return profiles
}
Exemplo n.º 18
0
func (q ConnectedAssetsQuery) Select(ctx context.Context, dest interface{}) error {

	assets, ok := dest.(*[]xdr.Asset)
	if !ok {
		return errors.New("dest is not *[]xdr.Asset")
	}

	var (
		t xdr.AssetType
		c string
		i string
	)

	err := q.SellingAsset.Extract(&t, &c, &i)
	if err != nil {
		return err
	}

	sql := sq.Select(
		"buyingassettype AS type",
		"coalesce(buyingassetcode, '') AS code",
		"coalesce(buyingissuer, '') AS issuer").
		From("offers").
		Where(sq.Eq{"sellingassettype": t}).
		GroupBy("buyingassettype", "buyingassetcode", "buyingissuer")

	if t != xdr.AssetTypeAssetTypeNative {
		sql = sql.Where(sq.Eq{"sellingassetcode": c, "sellingissuer": i})
	}

	var rows []struct {
		Type   xdr.AssetType
		Code   string
		Issuer string
	}

	err = q.SqlQuery.Select(ctx, sql, &rows)

	if err != nil {
		return err
	}

	results := make([]xdr.Asset, len(rows))
	*assets = results

	for i, r := range rows {
		results[i], err = assetFromDB(r.Type, r.Code, r.Issuer)
		if err != nil {
			return err
		}
	}

	return nil
}
Exemplo n.º 19
0
func FindUsers() ([]User, error) {
	qb := squirrel.Select("*").From("user")

	var users []User
	err := db.LoadCollection(&qb, &users)
	if err != nil {
		return nil, err
	}

	return users, nil
}
Exemplo n.º 20
0
func (*Loader) FindByIdInTable(tableName string, id int, buf interface{}) error {
	qb := squirrel.Select("*").
		From(tableName).
		Where("id = ?", id)

	err := LoadStruct(&qb, buf)
	if err != nil {
		return err
	}

	return nil
}
Exemplo n.º 21
0
func (r *DaoResource) GetPublishByToken(token string) (*model.Publish, error) {
	var entity model.Publish

	q := sq.Select(
		"id", "token", "provider_id", "message", "data", "status", "created_at", "updated_at",
		"messages_count", "success_count", "failed_count", "").
		From(model.TableEndpoints).
		Where("token = ?", token)

	err := r.Storage.SelectOne(&entity, &q)
	return &entity, err
}
Exemplo n.º 22
0
func TestSelect__Squirrel(t *testing.T) {
	q := sq.Select("id", "name").From("user").Where(sq.Eq{"name": "hogehoge"})
	query, args, err := q.ToSql()
	if err != nil {
		t.Fatal("unexpected error:", err)
	}
	if query != "SELECT id, name FROM user WHERE name = ?" {
		t.Fatal("unexpected query:", query)
	}
	if !reflect.DeepEqual(args, []interface{}{"hogehoge"}) {
		t.Fatal("unexpected args:", args)
	}
}
Exemplo n.º 23
0
func ListenToFeeds(s gorp.SqlExecutor) error {
	feeds := []*Feed{}
	query := squirrel.Select("*").From(TableNameFeed).
		Where(squirrel.Eq{"Deleted": false})
	if err := sqlutil.Select(s, query, &feeds); err != nil {
		return err
	}
	for _, feed := range feeds {
		if err := feed.UpdateStories(s); err != nil {
			return err
		}
	}
	return nil
}
Exemplo n.º 24
0
func (this *Wigo) SearchLogs(probe string, hostname string, group string, limit uint64, offset uint64) []*Log {

	// Lock
	LocalWigo.sqlLiteLock.Lock()
	defer LocalWigo.sqlLiteLock.Unlock()

	// Construct SQL Query
	logs := make([]*Log, 0)
	logsQuery := squirrel.Select("date,level,grp,host,probe,message").From("logs")

	if probe != "" {
		logsQuery = logsQuery.Where(squirrel.Eq{"probe": probe})
	}
	if hostname != "" {
		logsQuery = logsQuery.Where(squirrel.Eq{"host": hostname})
	}
	if group != "" {
		logsQuery = logsQuery.Where(squirrel.Eq{"grp": group})
	}

	// Index && Offset
	logsQuery = logsQuery.OrderBy("id DESC")
	logsQuery = logsQuery.Limit(limit)
	logsQuery = logsQuery.Offset(offset)

	// Execute
	rows, err := logsQuery.RunWith(LocalWigo.sqlLiteConn).Query()
	if err != nil {
		log.Printf("Fail to exec query to fetch logs : %s", err)
		return logs
	}

	// Instanciate
	for rows.Next() {
		l := new(Log)
		t := new(time.Time)

		if err := rows.Scan(&t, &l.Level, &l.Group, &l.Host, &l.Probe, &l.Message); err != nil {
			return logs
		}

		l.Timestamp = t.Unix()
		l.Date = t.Format(dateLayout)

		logs = append(logs, l)
	}

	return logs
}
Exemplo n.º 25
0
func BenchmarkSqrl(b *testing.B) {
	db := test.db

	var t1, t2 string

	for i := 0; i < b.N; i++ {
		rows, err := squirrel.Select("*").From("users").RunWith(db).Query()
		panicIfErr(err)

		for rows.Next() {
			err := rows.Scan(&t1, &t2)
			panicIfErr(err)
		}
	}
}
Exemplo n.º 26
0
func FindMember(s gorp.SqlExecutor, email string) (*Member, error) {
	query := squirrel.Select("*").From(TableNameMember).
		Where(squirrel.Eq{"email": email})
	members := []*Member{}
	if err := sqlutil.Select(s, query, &members); err != nil {
		return nil, err
	}

	err := fmt.Errorf("no user for email")
	if len(members) == 0 {
		return nil, err
	}

	return members[0], nil
}
Exemplo n.º 27
0
func (gorpdb *GorpDb) ReadMany(in interface{}, opts *db.Filter) ([]interface{}, error) {
	t := reflect.TypeOf(in)
	//sliceType := reflect.SliceOf(t)
	//sliceValue := reflect.MakeSlice(sliceType, 0, 0)

	tmap, e := gorpdb.db.TableFor(t, false)
	if e != nil {
		return nil, e
	}
	tname := tmap.TableName
	query := squirrel.Select("*").From(tname)

	var (
		limit  = opts.Limit
		offset = opts.Offset
		page   = opts.Page
	)

	if page != 0 && limit != 0 {
		offset = (page - 1) * limit
	}

	if limit != 0 {
		query = query.Limit(limit)
	}
	if offset != 0 {
		query = query.Offset(offset)
	}
	if opts.Order != "" {
		query = query.OrderBy(opts.Order)
	}
	if opts.WhereEq != nil {
		for _, v := range opts.WhereEq {
			query = query.Where(v)
		}
	}
	qstring, args, err := query.ToSql()
	if err != nil {
		return nil, err
	}

	//typeSliceInterface := reflect.ValueOf([]interface{}{}).Type()
	//slIface := sliceValue.Convert(typeSliceInterface).Interface().([]interface{})
	//var re []interface{}

	res, err := gorpdb.db.Select(in, qstring, args...)
	return res, err
}
Exemplo n.º 28
0
func IsImportedEquipmentInfo(file string, dbm *gorp.DbMap) (int64, error) {
	q := sq.Select(
		"count(*)",
	).From(
		"EquipmentInfo",
	).Where(
		sq.Eq{"File": file},
	)

	sql, args, err := q.ToSql()
	if err != nil {
		return -1, err
	}

	return dbm.SelectInt(sql, args...)
}
Exemplo n.º 29
0
func (this *User) Get(id int) (result map[string]interface{}, err error) {

	user := &User{}
	qb := squirrel.Select("*").
		From("user").
		Where("id = ?", id)
	err = db.LoadStruct(&qb, user)
	if err != nil {
		return nil, err
	}

	result = map[string]interface{}{
		"email": user.Email,
	}
	return result, nil
}
Exemplo n.º 30
0
Arquivo: queue.go Projeto: haste/tiers
func GetAllUserQueues() *sql.Rows {
	query := squirrel.
		Select("q.user_id, MAX(q.timestamp) AS latest, COUNT(q.id) AS count").
		From("tiers_queues q").
		Join("tiers_users u ON u.id = q.user_id").
		GroupBy("q.user_id")

	// XXX: Handle errors.
	rows, err := query.RunWith(db).Query()

	if err != nil {
		log.Fatal(err)
	}

	return rows
}