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
}
Beispiel #2
0
// 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
}
Beispiel #3
0
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
}
Beispiel #5
0
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
}
Beispiel #6
0
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
}
Beispiel #7
0
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()
}
Beispiel #8
0
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
}
Beispiel #9
0
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
}
Beispiel #11
0
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
}
Beispiel #12
0
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
}
Beispiel #13
0
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
}
Beispiel #14
0
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
}
Beispiel #15
0
// 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
}
func fetchDatabaseSize(db *sqlx.DB) (map[string]interface{}, error) {
	rows, err := db.Query("select sum(pg_database_size(datname)) as dbsize from pg_database")
	if err != nil {
		logger.Errorf("Failed to select pg_database_size. %s", err)
		return nil, err
	}

	var totalSize float64
	for rows.Next() {
		var dbsize float64
		if err := rows.Scan(&dbsize); err != nil {
			logger.Warningf("Failed to scan %s", err)
			continue
		}
		totalSize += dbsize
	}

	return map[string]interface{}{
		"total_size": totalSize,
	}, nil
}
func queryWhere(db *sqlx.DB) {

	log.Println("Query with Where...")
	log.Println("================")
	var subject string

	rows, err := db.Query("select subject from todos where id = $1", 1)
	if err != nil {
		log.Fatal(err)
	}
	for rows.Next() {
		if err = rows.Scan(&subject); err != nil {
			log.Fatal(err)
		}
		log.Printf("Subject is %s\n", subject)
	}

	if err = rows.Err(); err != nil {
		log.Fatal(err)
	}
}
Beispiel #18
0
// writeProcedureBody reads the body of the SP with the given name from the database and writes it
// to the given Writer.
func writeProcedureBody(db *sqlx.DB, name string, w io.Writer) (err error) {
	rows, err := db.Query("EXEC sp_helptext ?", name)
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var text string
		err = rows.Scan(&text)
		if err != nil {
			return errors.New(fmt.Sprintf("%v: %v", name, err.Error()))
		}

		_, err = w.Write([]byte(text))
		if err != nil {
			return errors.New(fmt.Sprintf("%v: %v", name, err.Error()))
		}
	}
	return nil
}
Beispiel #19
0
func RemoveNPCByZone(db *sqlx.DB, zone string) (totalRemoved int64, err error) {

	var zoneid int64
	var rows *sql.Rows

	if rows, err = db.Query("SELECT zoneidnumber from zone where short_name = ?", zone); err != nil {
		err = fmt.Errorf("Could not find zone %s: %s", zone, err.Error())
		return
	}
	for rows.Next() {
		if err = rows.Scan(&zoneid); err != nil {
			err = fmt.Errorf("Error getting zone id: %s", err.Error())
			return
		}
	}

	if zoneid < 1 {
		err = fmt.Errorf("Error getting zone id")
		return
	}

	var result sql.Result
	var affect int64
	//Remove from spawngroup

	if result, err = db.Exec("DELETE FROM npc_types WHERE id <= ? AND id >= ?", zoneid*1000+999, zoneid*1000); err != nil {
		err = fmt.Errorf("Error deleting npc_types: %s", err.Error())
		return
	}

	if affect, err = result.RowsAffected(); err != nil {
		err = fmt.Errorf("Error gettign rows affected for npc_type: %s", err.Error())
		return
	}
	totalRemoved += affect
	return
}