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