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