Golang DB.Query方法代码示例

func fetchConnections(db *sqlx.DB) (map[string]interface{}, error) {
	rows, err := db.Query(`
		select count(*), waiting from pg_stat_activity group by waiting
	`)
	if err != nil {
		logger.Errorf("Failed to select pg_stat_activity. %s", err)
		return nil, err
	}

	var totalActive, totalWaiting float64
	for rows.Next() {
		var count float64
		var waiting string
		if err := rows.Scan(&count, &waiting); err != nil {
			logger.Warningf("Failed to scan %s", err)
			continue
		}
		if waiting != "" {
			totalActive += count
		} else {
			totalWaiting += count
		}
	}

	return map[string]interface{}{
		"active":  totalActive,
		"waiting": totalWaiting,
	}, nil
}

// ExtractCities efficiently from a user's message.
func ExtractCities(db *sqlx.DB, in *dt.Msg) ([]dt.City, error) {
	// Interface type is used to expand the args in db.Select below.
	// Although we're only storing strings, []string{} doesn't work.
	var args []interface{}

	// Look for "at", "in", "on" prepositions to signal that locations
	// follow, skipping everything before
	var start int
	for i := range in.Stems {
		switch in.Stems[i] {
		case "at", "in", "on":
			start = i
			break
		}
	}

	// Prepare sentence for iteration
	tmp := regexNonWords.ReplaceAllString(in.Sentence, "")
	words := strings.Fields(strings.Title(tmp))

	// Iterate through words and bigrams to assemble a DB query
	for i := start; i < len(words); i++ {
		args = append(args, words[i])
	}
	bgs := bigrams(words, start)
	for i := 0; i < len(bgs); i++ {
		args = append(args, bgs[i])
	}

	cities := []dt.City{}
	q := `SELECT name, countrycode FROM cities
	      WHERE countrycode='US' AND name IN (?)
	      ORDER BY LENGTH(name) DESC`
	query, arguments, err := sqlx.In(q, args)
	query = db.Rebind(query)
	rows, err := db.Query(query, arguments...)
	if err != nil {
		return nil, err
	}
	defer func() {
		if err = rows.Close(); err != nil {
			log.Info("failed to close db rows.", err)
		}
	}()
	for rows.Next() {
		city := dt.City{}
		if err = rows.Scan(&city.Name, &city.CountryCode); err != nil {
			return nil, err
		}
		cities = append(cities, city)
	}
	if err = rows.Err(); err != nil {
		return nil, err
	}
	if len(cities) == 0 {
		return nil, ErrNotFound
	}
	return cities, nil
}

func RemoveVehiclesOlderThan(db *sqlx.DB, limit time.Time) error {
	rows, err := db.Query(`DELETE FROM vehicles WHERE time_received < $1`, limit)
	if err != nil {
		return err
	}
	rows.Close()
	return nil
}

func GetEnProceso(db *sqlx.DB, id int) ([]*Activity, error) {
	var (
		Activitys []*Activity
		rows      *sql.Rows
	)

	if id <= 0 {
		var (
			err error
		)
		rows, err = db.Query(sqlGetActivityEnProcesoALL)
		if err != nil {
			return nil, err
		}

	} else {
		var (
			err error
		)
		rows, err = db.Query(sqlGetActivityEnProcesoByID, id)

		if err != nil {
			panic(err)
		}

	}

	for rows.Next() {

		a := &Activity{}
		err := rows.Scan(
			&a.ID,
			&a.Title,
			&a.Description,
			&a.ExpireDates,
			&a.User,
			&a.Estate,
			&a.Adjuntos,
			&a.Pioridad,
		)

		if err != nil {
			if err == sql.ErrNoRows {
				return Activitys, nil
			}
			panic(err)
		}
		Activitys = append(Activitys, a)
	}

	if err := rows.Err(); err != nil {
		panic(err)
	}

	return Activitys, nil
}

func getAllIdleEntries(db *sqlx.DB) (idleEntries, error) {
	rows, err := db.Query(`select id, nick, max(lastSeen) from downtime
	group by nick`)
	if err != nil {
		return nil, err
	}
	entries := idleEntries{}
	for rows.Next() {
		var e idleEntry
		err := rows.Scan(&e.id, &e.nick, &e.lastSeen)
		if err != nil {
			return nil, err
		}
		entries = append(entries, &e)
	}
	return entries, nil
}

func getVersion(db *sqlx.DB, appName string) string {
	rows, err := db.Query(fmt.Sprintf("select version from %s_meta;", appName))
	if err != nil {
		log.Println(err)
		return "-1"
	}
	defer rows.Close()

	rows.Next()
	var version string
	err = rows.Scan(&version)
	if err != nil {
		log.Println(err)
		return "-1"
	}
	return version
}

func getCrconfigSnapshot(cdn string, db *sqlx.DB) (string, error) {
	queryStr := `select snapshot from crconfig_snapshots where cdn = $1 and created_at = (select max(created_at) created_at from crconfig_snapshots where cdn = $1);`
	rows, err := db.Query(queryStr, cdn)
	if err != nil {
		return "", fmt.Errorf("getCrconfigSnapshot query error: %v", err)
	}
	defer rows.Close()

	if !rows.Next() {
		return "", fmt.Errorf("No Snapshot Found")
	}

	var snapshot string
	if err := rows.Scan(&snapshot); err != nil {
		return "", fmt.Errorf("getCrconfigSnapshot row error: %v", err)
	}
	return snapshot, rows.Err()
}

func GetSpawnGroupIdsByClass(db *sqlx.DB, class int64) (ids []int64, err error) {

	rows, err := db.Query("SELECT sg.id as id FROM npc_types nt INNER JOIN spawnentry se ON se.npcid = nt.id INNER JOIN spawngroup sg ON sg.id = se.spawngroupid WHERE nt.class = ?", class)
	if err != nil {
		return
	}

	//iterate results
	for rows.Next() {
		id := int64(0)
		err = rows.Scan(&id)
		if err != nil {
			return
		}
		ids = append(ids, id)
	}
	return
}

func findRelatedSubmits(db *sqlx.DB, sub *scannedSubmit) ([]*scannedSubmit, error) {
	rows, err := db.Query(relatedSubmitsQuery, sub.Contest, sub.Task, sub.Team, sub.ID)
	if err != nil {
		log.Printf("Error scanning for related submits: %s", err)
		return nil, err
	}

	result := make([]*scannedSubmit, 0)

	for rows.Next() {
		s, _ := scanSubmit(rows)
		if s != nil {
			result = append(result, s)
		}
	}

	return result, nil
}

func Get(db *sqlx.DB, id int) ([]*Activity, error) {
	var (
		Activitys []*Activity
	)

	if id <= 0 {
		return nil, ErrNoIDSent
	}

	rows, err := db.Query(sqlGetActivityByID, id)

	if err != nil {
		panic(err)
	}

	for rows.Next() {

		a := &Activity{}
		err := rows.Scan(
			&a.ID,
			&a.Title,
			&a.Description,
			&a.ExpireDates,
			&a.User,
			&a.Estate,
			&a.Adjuntos,
			&a.Pioridad,
		)

		if err != nil {
			if err == sql.ErrNoRows {
				return Activitys, nil
			}
			panic(err)
		}
		Activitys = append(Activitys, a)
	}

	if err := rows.Err(); err != nil {
		panic(err)
	}

	return Activitys, nil
}

func GetSpawnGroupIdsByNameWildcard(db *sqlx.DB, wildcard string) (ids []int64, err error) {

	rows, err := db.Query("SELECT sg.id as id FROM npc_types nt INNER JOIN spawnentry se ON se.npcid = nt.id INNER JOIN spawngroup sg ON sg.id = se.spawngroupid WHERE nt.name LIKE ?", "%"+wildcard+"%")
	if err != nil {
		fmt.Println("Error initial")
		return
	}

	//iterate results
	for rows.Next() {
		id := int64(0)
		err = rows.Scan(&id)
		if err != nil {
			return
		}
		ids = append(ids, id)
	}
	return
}

func getLinkedSQL(db *sqlx.DB, table, policy string) ([]string, error) {
	urns := []string{}
	rows, err := db.Query(db.Rebind(fmt.Sprintf("SELECT template FROM %s WHERE policy=?", table)), policy)
	if err == sql.ErrNoRows {
		return nil, errors.Wrap(pkg.ErrNotFound, "")
	} else if err != nil {
		return nil, errors.Wrap(err, "")
	}

	defer rows.Close()
	for rows.Next() {
		var urn string
		if err = rows.Scan(&urn); err != nil {
			return []string{}, errors.Wrap(err, "")
		}
		urns = append(urns, urn)
	}
	return urns, nil
}

func getFacts(db *sqlx.DB, fact string, tidbit string) ([]*factoid, error) {
	var fs []*factoid
	query := `select
			id,
			fact,
			tidbit,
			verb,
			owner,
			created,
			accessed,
			count
		from factoid
		where fact like ?
		and tidbit like ?;`
	rows, err := db.Query(query,
		"%"+fact+"%", "%"+tidbit+"%")
	if err != nil {
		log.Printf("Error regexping for facts: %s", err)
		return nil, err
	}
	for rows.Next() {
		var f factoid
		var tmpCreated int64
		var tmpAccessed int64
		err := rows.Scan(
			&f.id,
			&f.Fact,
			&f.Tidbit,
			&f.Verb,
			&f.Owner,
			&tmpCreated,
			&tmpAccessed,
			&f.Count,
		)
		if err != nil {
			return nil, err
		}
		f.created = time.Unix(tmpCreated, 0)
		f.accessed = time.Unix(tmpAccessed, 0)
		fs = append(fs, &f)
	}
	return fs, err
}

func GetSpawnGroupIdsByEmptyMerchant(db *sqlx.DB) (ids []int64, err error) {

	rows, err := db.Query("SELECT sg.id as id FROM npc_types nt INNER JOIN spawnentry se ON se.npcid = nt.id INNER JOIN spawngroup sg ON sg.id = se.spawngroupid WHERE nt.merchant_id > 0 AND nt.merchant_id not in (select merchantid from merchantlist)")
	if err != nil {
		fmt.Println("Error initial")
		return
	}

	//iterate results
	for rows.Next() {
		id := int64(0)
		err = rows.Scan(&id)
		if err != nil {
			return
		}
		ids = append(ids, id)
	}
	return
}

// this who string isn't escaped, just sooo, you know.
func getMarkovChain(db *sqlx.DB, who string) (*babbler, error) {
	query := fmt.Sprintf(`select tidbit from factoid where fact like '%s quotes';`, who)
	rows, err := db.Query(query)
	if err != nil {
		return nil, err
	}

	babble := newBabbler()

	for rows.Next() {

		var tidbit string
		err := rows.Scan(&tidbit)
		if err != nil {
			return nil, err
		}

		addToMarkovChain(babble, tidbit)
	}
	return babble, nil
}