// 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
}
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()
}
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
}
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()
}
}
//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
}
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
}
}
// 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)
}
// 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)
}
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
}
// 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)
}
// 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)
}
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
}
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
}
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
}
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
}
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
}
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
}
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
}
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
}
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)
}
}
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
}
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
}
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)
}
}
}
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
}
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
}
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...)
}
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
}
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
}
