// GetAgeModelData does the search and modifies the results struct pointer // Not placed in the above function to allow this to be called by stand along apps like ocdBulk func GetAgeModelData(db *sqlx.DB, sqlstring string, results **[]AgeModel) { db.MapperFunc(strings.ToUpper) err := db.Select(*results, sqlstring) if err != nil { log.Printf(`Error v2 with: %s`, err) } }
func queryWhereToStructTX(db *sqlx.DB) { log.Println("Query with Where Map to Struct TX...") log.Println("====================================") tx := db.MustBegin() now := time.Now() t := Todo{ Subject: sql.NullString{String: "Mow Lawn"}, Description: sql.NullString{String: "Yuck!"}, CreatedAt: now, UpdatedAt: now, } tx.Exec("Insert into todos(subject, description, created_at, updated_at) values ($1, $2, $3, $4)", t.Subject, t.Description, t.CreatedAt, t.UpdatedAt) tx.Commit() todos := []Todo{} err := db.Select(&todos, "select * from todos") if err != nil { log.Fatal(err) } for _, todo := range todos { log.Printf("Subject is %s\n", todo.CreatedAt) } }
func KillThreads(db *sqlx.DB) { var ids []int db.Select(&ids, "SELECT Id FROM information_schema.PROCESSLIST WHERE Command != 'binlog dump' AND User != 'system user' AND Id != CONNECTION_ID()") for _, id := range ids { db.Exec("KILL ?", id) } }
func (s *Slugs) GetAllUrls(db *sqlx.DB) { err := db.Select(s, "SELECT slug,modified,status FROM posts WHERE status=1 ORDER BY created") if err != nil { panic(err) } }
func (p *Posts) GetAll(db *sqlx.DB) { err := db.Select(p, "SELECT * FROM posts ORDER BY created DESC") if err != nil { panic(err) } }
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 } }
func contentServersSection(cdnName string, ccrDomain string, db *sqlx.DB) (map[string]ContentServer, error) { csQuery := "select * from content_servers where cdn='" + cdnName + "'" cServers := []CrContentServer{} err := db.Select(&cServers, csQuery) if err != nil { log.Println(err) err = fmt.Errorf("contentServersSection error selecting content_servers: %v", err) return nil, err } dsServerQuery := "select * from cr_deliveryservice_server" dsServers := []CrDeliveryserviceServer{} err = db.Select(&dsServers, dsServerQuery) if err != nil { log.Println("ERROR: >> ", err) err = fmt.Errorf("contentServersSection error selecting cr_deliveryservice_server: %v", err) return nil, err } dsMap := make(map[string]ContentServerDsMap) for _, row := range dsServers { if dsMap[row.ServerName] == nil { dsMap[row.ServerName] = make(ContentServerDsMap) } // if dsMap[row.ServerName][row.Name] == nil { // dsMap[row.ServerName][row.Name] = make(ContentServerDomainList, 0, 10) // } pattern := row.Pattern if strings.HasSuffix(pattern, "\\..*") { pattern = strings.Replace(pattern, ".*\\.", "", 1) pattern = strings.Replace(pattern, "\\..*", "", 1) if strings.HasPrefix(row.DsType, "HTTP") { pattern = row.ServerName + "." + pattern + "." + ccrDomain } else { pattern = "edge." + pattern + "." + ccrDomain } } dsMap[row.ServerName][row.Name] = append(dsMap[row.ServerName][row.Name], pattern) } retMap := make(map[string]ContentServer) for _, row := range cServers { hCount, _ := strconv.Atoi(row.HashCount) hCount = hCount * 1000 // TODO JvD retMap[row.HostName] = ContentServer{ Fqdn: row.Fqdn, HashCount: hCount, HashID: row.HostName, InterfaceName: row.InterfaceName, IP: row.Ip, IP6: row.Ip6, LocationID: row.CacheGroup, Port: row.Port, Profile: row.Profile, Status: row.Status, Type: row.Status, DeliveryServices: dsMap[row.HostName], } } return retMap, nil }
func search(db *sqlx.DB, query string) ([]person, error) { people := []person{} tokens := strings.Split(query, " ") cryptoTokens := []string{} for _, token := range tokens { token = strings.TrimSpace(strings.ToLower(token)) crypted := fmt.Sprintf("%x", sha256.Sum256([]byte(token))) cryptoTokens = append(cryptoTokens, crypted) } q, args, err := sqlx.In(`SELECT DISTINCT p.id, p.name FROM people p JOIN hashes_people hp ON hp.person_id = p.id JOIN hashes h ON h.id = hp.hash_id AND h.hash IN (?) GROUP BY p.name`, cryptoTokens) if err != nil { return people, err } err = db.Select(&people, q, args...) if err != nil { return people, err } return people, err }
func GetProcesslist(db *sqlx.DB) []Processlist { pl := []Processlist{} err := db.Select(&pl, "SELECT id, user, host, `db` AS `database`, command, time_ms as time, state FROM INFORMATION_SCHEMA.PROCESSLIST") if err != nil { log.Fatalln("ERROR: Could not get processlist", err) } return pl }
// GetChannelLists returns a list of ChannelList items. func GetChannelLists(db *sqlx.DB, limit, offset int) ([]models.ChannelList, error) { var channelLists []models.ChannelList err := db.Select(&channelLists, "select * from channel_list order by name limit $1 offset $2", limit, offset) if err != nil { return nil, fmt.Errorf("get channel-list list error: %s", err) } return channelLists, nil }
// GetChannelsForChannelList returns the Channels for the given ChannelList id. func GetChannelsForChannelList(db *sqlx.DB, channelListID int64) ([]models.Channel, error) { var channels []models.Channel err := db.Select(&channels, "select * from channel where channel_list_id = $1 order by channel", channelListID) if err != nil { return nil, fmt.Errorf("get channels for channel-list %d error: %s", channelListID, err) } return channels, nil }
// GetVehiclesByID returns all vehicles with a given ID func GetVehiclesByID(db *sqlx.DB, id string) ([]*muni.Vehicle, error) { v := []*muni.Vehicle{} err := db.Select(&v, `SELECT * FROM vehicles WHERE vehicle_id=$1`, id) if err != nil { return nil, err } return v, nil }
func GetSlaveHostsArray(db *sqlx.DB) []SlaveHosts { sh := []SlaveHosts{} err := db.Select(&sh, "SHOW SLAVE HOSTS") if err != nil { log.Fatalln("ERROR: Could not get slave hosts array", err) } return sh }
// GetVehiclesAfterTime returns all vehicles logged after limit func GetVehiclesAfterTime(db *sqlx.DB, limit time.Time) ([]*muni.Vehicle, error) { v := []*muni.Vehicle{} err := db.Select(&v, `SELECT * FROM vehicles WHERE time_received >= $1 ORDER BY time_received DESC`, limit) if err != nil { return nil, err } return v, nil }
// GetNodesForAppEUI returns a slice of nodes, sorted by DevEUI, for the given AppEUI. func GetNodesForAppEUI(db *sqlx.DB, appEUI lorawan.EUI64, limit, offset int) ([]models.Node, error) { var nodes []models.Node err := db.Select(&nodes, "select * from node where app_eui = $1 order by dev_eui limit $2 offset $3", appEUI[:], limit, offset) if err != nil { return nodes, fmt.Errorf("get nodes error: %s", err) } return nodes, nil }
// GetNodes returns a slice of nodes, sorted by DevEUI. func GetNodes(db *sqlx.DB, limit, offset int) ([]models.Node, error) { var nodes []models.Node err := db.Select(&nodes, "select * from node order by dev_eui limit $1 offset $2", limit, offset) if err != nil { return nodes, fmt.Errorf("get nodes error: %s", err) } return nodes, nil }
// GetVehiclesBeforeTime returns all vehicles logged after limit func GetVehiclesBeforeTime(db *sqlx.DB, limit time.Time) ([]*muni.Vehicle, error) { v := []*muni.Vehicle{} err := db.Select(&v, `SELECT * FROM vehicles WHERE time_received < $1`, limit) if err != nil { return nil, err } return v, nil }
// getApplications returns a slice of applications. func getApplications(db *sqlx.DB, limit, offset int) ([]models.Application, error) { var apps []models.Application err := db.Select(&apps, "select * from application order by app_eui limit $1 offset $2", limit, offset) if err != nil { return apps, fmt.Errorf("get applications error: %s", err) } return apps, nil }
func (l *List) QueryResources(db *sqlx.DB) error { var movies []*Movie err := db.Select(&movies, "SELECT * FROM lists WHERE list_id=$1", l.ID) if err != nil { return err } l.Movies = movies return nil }
func longQueriesCheck(db *sqlx.DB) Check { checkTitle := "Long Queries" var results []longQueriesResult err := db.Select(&results, longQueriesSQL) if err != nil { return makeErrorCheck(checkTitle, err) } return Check{checkTitle, longQueriesStatus(results), results} }
func connCountCheck(db *sqlx.DB, limit int) Check { checkTitle := "Connection Count" var result []connCountResult err := db.Select(&result, "SELECT count(*) FROM pg_stat_activity where usename = current_user") if err != nil { return makeErrorCheck(checkTitle, err) } return Check{checkTitle, connCountStuats(result[0].Count, limit), result} }
func blockingCheck(db *sqlx.DB) Check { checkTitle := "Blocking Queries" var results []blockingResult err := db.Select(&results, blockingSQL) if err != nil { return makeErrorCheck(checkTitle, err) } return Check{checkTitle, blockingStatus(results), results} }
func hitRateCheck(db *sqlx.DB) Check { checkTitle := "Hit Rate" var results []hitRateResult err := db.Select(&results, hitRateSQL) if err != nil { return makeErrorCheck(checkTitle, err) } return Check{checkTitle, hitRateStatus(results), results} }
func bloatCheck(db *sqlx.DB) Check { checkTitle := "Bloat" var results []bloatResult err := db.Select(&results, bloatSQL) if err != nil { return makeErrorCheck(checkTitle, err) } return Check{checkTitle, bloatStatus(results), results} }
func unusedIndexesCheck(db *sqlx.DB) Check { checkTitle := "Indexes" var results []unusedIndexesResult err := db.Select(&results, unusedIndexesSQL) if err != nil { return makeErrorCheck(checkTitle, err) } return Check{checkTitle, unusedIndexesStatus(results), results} }
func idleQueriesCheck(db *sqlx.DB) Check { checkTitle := "Idle in Transaction" var results []idleQueriesResult err := db.Select(&results, idleQueriesSQL) if err != nil { return makeErrorCheck(checkTitle, err) } return Check{checkTitle, idleQueriesStatus(results), results} }
func FetchQuestions(db *sqlx.DB) ([]*SecurityQuestion, error) { questions := []*SecurityQuestion{} err := db.Select(&questions, "select id,question from security_question") if err != nil { return nil, err } return questions, nil }
func GetSlaveHostsDiscovery(db *sqlx.DB) []string { slaveList := []string{} /* This method does not return the server ports, so we cannot rely on it for the time being. */ err := db.Select(&slaveList, "select host from information_schema.processlist where command ='binlog dump'") if err != nil { log.Fatalln("ERROR: Could not get slave hosts from the processlist", err) } return slaveList }
func listBooks(db *sqlx.DB) func(c *echo.Context) error { return func(c *echo.Context) error { var books []Book err := db.Select(&books, "SELECT * FROM books") if err != nil { return c.NoContent(http.StatusBadRequest) } return c.JSON(http.StatusOK, books) } }
// @Title getGooseDbVersions // @Description retrieves the goose_db_version information for a certain id // @Accept application/json // @Success 200 {array} GooseDbVersion // @Resource /api/2.0 // @Router /api/2.0/goose_db_version [get] func getGooseDbVersions(db *sqlx.DB) (interface{}, error) { ret := []GooseDbVersion{} queryStr := "select * from goose_db_version" err := db.Select(&ret, queryStr) if err != nil { log.Println(err) return nil, err } return ret, nil }