func GetLiveMatch() ([]modal.Modal, error) { var db *sqlx.DB var err error var matches []modal.Modal if db, err = sqlx.Open("mysql", user+":"+password+"@/Dota?parseTime=true"); err != nil { return matches, err } defer db.Close() rows, err := db.Queryx("select * from Live where deleted=?", deletedTime) if err != nil { return matches, err } for rows.Next() { var match modal.Live err = rows.StructScan(&match) if err != nil { return matches, err } log.WithFields(log.Fields{ "radiant id ": match.RadiantID, "dire id ": match.DireID, }).Info("Searching for Details in Score table") err = db.QueryRowx("SELECT * FROM Score where id=? LIMIT 1", match.RadiantID).StructScan(&match.Radiant) if err != nil { return matches, err } _, err = assignDataToPicksAndBans(db, &match) if err != nil { return matches, err } matches = append(matches, match) } return matches, nil }
func GetAbility() ([]modal.Modal, error) { var db *sqlx.DB var err error var abilities []modal.Modal if db, err = sqlx.Open("mysql", user+":"+password+"@/Dota?parseTime=true"); err != nil { return abilities, err } defer db.Close() rows, err := db.Queryx("select * from Ability") if err != nil { return abilities, err } for rows.Next() { var ability modal.Ability err = rows.StructScan(&ability) if err != nil { return abilities, err } abilities = append(abilities, ability) } return abilities, nil }
func GetTeam() ([]modal.Modal, error) { var db *sqlx.DB var err error var teams []modal.Modal if db, err = sqlx.Open("mysql", user+":"+password+"@/Dota?parseTime=true"); err != nil { return teams, err } defer db.Close() rows, err := db.Queryx("select * from Team") if err != nil { return teams, err } for rows.Next() { var team modal.Team err = rows.StructScan(&team) if err != nil { return teams, err } teams = append(teams, team) } return teams, nil }
func GetHero() ([]modal.Modal, error) { var db *sqlx.DB var err error var heroes []modal.Modal if db, err = sqlx.Open("mysql", user+":"+password+"@/Dota?parseTime=true"); err != nil { return heroes, err } defer db.Close() rows, err := db.Queryx("select * from Hero") if err != nil { return heroes, err } for rows.Next() { var hero modal.Hero err = rows.StructScan(&hero) if err != nil { return heroes, err } heroes = append(heroes, hero) } return heroes, nil }
// DropAndCreateTable удаляет таблицу, если она уже существует и создает заново func DropAndCreateTable(schema string, tableName string, db *sqlx.DB) (bool, error) { var err error var rows *sqlx.Rows // Проверяем нет ли такой таблицы в базе rows, err = db.Queryx("SELECT to_regclass('" + tableName + "');") if err != nil { //fmt.Println("Error on check table '"+tableName+"':", err) return false, err } defer rows.Close() // И если есть удаляем rowsCount := 0 for rows.Next() { rowsCount++ } if rowsCount > 0 { _, err = db.Exec("DROP TABLE IF EXISTS " + tableName + ";") if err != nil { //fmt.Println("Error on drop table '"+tableName+"':", err) return false, err } } // Создаем таблицу _, err = db.Exec(schema) if err != nil { //fmt.Println("Error on create table '"+tableName+"':", err) return false, err } return true, nil }
func GetLeague() ([]modal.Modal, error) { var db *sqlx.DB var err error var leagues []modal.Modal if db, err = sqlx.Open("mysql", user+":"+password+"@/Dota?parseTime=true"); err != nil { return leagues, err } defer db.Close() // find league where deleted is 0000-00-00 00:00:00 rows, err := db.Queryx("select * from League where deleted = ?", deletedTime) if err != nil { return leagues, err } for rows.Next() { var league modal.League err = rows.StructScan(&league) if err != nil { return leagues, err } leagues = append(leagues, league) } return leagues, nil }
func Clean(db *sqlx.DB, config *eqemuconfig.Config) (err error) { type ItemList struct { Name string `db:"name"` NpcId int64 `db:"npcid"` LootTableId int64 `db:"loottableid"` LootDropId int64 `db:"lootdropid"` ItemId int64 `db:"itemid"` Price float64 `db:"price"` Chance float64 `db:"chance"` } zone := "wakening" fmt.Println("Adjusting pricing for", zone) rows, err := db.Queryx(`SELECT se.npcID npcid, nt.name name, i.id itemid, lte.loottable_id loottableid, lde.lootdrop_id lootdropid, lde.chance chance, i.name, i.price price FROM spawn2 s2 INNER JOIN spawngroup sg ON sg.id = s2.spawngroupID INNER JOIN spawnentry se ON se.spawngroupID = sg.id INNER JOIN npc_types nt ON nt.id = se.npcID INNER JOIN loottable_entries lte ON lte.loottable_id = nt.loottable_id INNER JOIN lootdrop_entries lde ON lde.lootdrop_id = nt.loottable_id INNER JOIN items i on i.id = lde.item_id INNER JOIN merchantlist ml ON ml.item = i.id WHERE s2.zone = ? AND lde.chance > 0 ORDER BY price desc LIMIT 1 `, zone) if err != nil { fmt.Println("Error initializing", err.Error()) return } //&{Diamond 119111 7741 7741 10037 200000 3.75} //iterate results for rows.Next() { itemList := &ItemList{} err = rows.StructScan(itemList) if err != nil { return } fmt.Println(itemList) newPrice := itemList.Price * (itemList.Chance / 100) fmt.Println("Setting price from", itemList.Price, "to", newPrice) //Set pricing _, err = db.Exec("UPDATE loottable SET mincash = mincash + ?, maxcash = maxcash + ? WHERE id = ?", itemList.Price, itemList.Price, itemList.LootTableId) if err != nil { return } //Remove chance of drop _, err = db.Exec("UPDATE lootdrop_entries SET chance = 0, disabled_chance = ? WHERE lootdrop_id = ? and item_id = ?", itemList.Chance, itemList.LootDropId, itemList.ItemId) if err != nil { return } return } return //&{Diamond 119017 377 10037 200000} }
// Execute a read query (SELECT) on a given SQL database func (q *Query) ExecRead(db *sqlx.DB) (*sqlx.Rows, error) { sql, vars := q.GetSQL() if debugEnabled { marshaled, _ := json.Marshal(vars) Debug("%s, %s", sql, string(marshaled)) } return db.Queryx(sql, vars...) }
//Find all Characters by wildcard name func FindAllByName(db *sqlx.DB, name string) (characters []*CharacterOutput, err error) { rows, err := db.Queryx(`SELECT cd.*, cs.name as class_name, g.name as guild_name, g.id as guild_id, gm.rank as guild_rank FROM character_data cd JOIN class_skill cs ON (cs.class = cd.class) LEFT JOIN guild_members gm ON (gm.char_id = cd.id) LEFT JOIN guilds g ON (g.id = gm.guild_id) WHERE cd.Name LIKE ?`, "%"+name+"%") if err != nil { fmt.Println("Error initial") return } //fmt.Println("Getting Characters by Name", name) for rows.Next() { char := &CharacterOutput{} err = rows.StructScan(char) if err != nil { return } /* lastLogin := time.Unix(char.Last_login, 0) lastLoginDuration := time.Since(lastLogin) if lastLoginDuration.Hours() > 0 { char.LastLoginText += fmt.Sprintf("%9.f hour", lastLoginDuration.Hours()) if lastLoginDuration.Hours() > 0 { char.LastLoginText += "s" } char.LastLoginText += " " } if lastLoginDuration.Minutes() > 0 { if len(char.LastLoginText) > 0 { char.LastLoginText += ", " } char.LastLoginText += fmt.Sprintf("%9.f minute", lastLoginDuration.Minutes()) if lastLoginDuration.Minutes() > 0 { char.LastLoginText += "s" } char.LastLoginText += " " } if lastLoginDuration.Seconds() > 0 { if len(char.LastLoginText) > 0 { char.LastLoginText += ", " } char.LastLoginText += fmt.Sprintf("%9.f second", lastLoginDuration.Seconds()) if lastLoginDuration.Seconds() > 0 { char.LastLoginText += "s" } char.LastLoginText += " " } if len(char.LastLoginText) > 0 { char.LastLoginText += "ago" } */ characters = append(characters, char) } return }
// GetJanusCSV is a test function for better formatting style.... // Keep as a called function to allow use in other code (like ocdBulk) and to future use like gRPC // func TestFuncx(db *sqlx.DB) (*[]AgeModelx, error) { func GetJanusCSV(db *sqlx.DB, sqlstring string) (string, error) { results, err := db.Queryx(sqlstring) if err != nil { log.Printf(`Error with: %s`, err) } defer results.Close() csvdata, _ := ResultsToCSV(results) return csvdata, nil }
func GetSlaveHosts(db *sqlx.DB) map[string]interface{} { rows, err := db.Queryx("SHOW SLAVE HOSTS") if err != nil { log.Fatalln("ERROR: Could not get slave hosts", err) } defer rows.Close() results := make(map[string]interface{}) for rows.Next() { err = rows.MapScan(results) if err != nil { log.Fatal(err) } } return results }
func GetStatusAsInt(db *sqlx.DB) map[string]int64 { type Variable struct { Variable_name string Value int64 } vars := make(map[string]int64) rows, err := db.Queryx("SELECT Variable_name AS variable_name, Variable_Value AS value FROM information_schema.global_status") if err != nil { log.Fatal("ERROR: Could not get status as integer", err) } for rows.Next() { var v Variable rows.Scan(&v.Variable_name, &v.Value) vars[v.Variable_name] = v.Value } return vars }
func exec(n *query.Node, db *sqlx.DB) (interface{}, error) { slct := New(n) rows, err := db.Queryx(slct.String()) if err != nil { return nil, err } var results []map[string]interface{} for rows.Next() { result := make(map[string]interface{}) cols, err := rows.Columns() if err != nil { return nil, err } data, err := rows.SliceScan() for i, col := range cols { switch data[i].(type) { case []uint8: result[col] = string(data[i].([]uint8)) break default: result[col] = data[i] break } } for _, e := range n.Edges { if e.Params != nil { t, _ := query.ApplyContext(&e, result) result[string(e.Name)], err = exec(t, db) } } results = append(results, result) } rows.Close() if len(results) == 1 { return results[0], nil } else { return results, nil } }
// isDBValid checks if the given db is a SIFT DB func isDBValid(db *sqlx.DB) error { for table, numColumns := range numColumnsByTable { q := "PRAGMA table_info(" + table + ")" //rows, err := db.Queryx("PRAGMA table_info(?)", table) rows, err := db.Queryx(q) if err != nil { return fmt.Errorf("error trying to find number of columns in table %v: %v", table, err) } count := 0 for rows.Next() { count++ } if count != numColumns { return fmt.Errorf("number of columns on database (%v) != num expected (%v)", count, numColumns) } } return nil }
//Search for items by name func SearchByName(db *sqlx.DB, name string, limit int64, offset int64) (items []*ItemOutput, err error) { rows, err := db.Queryx(`SELECT * FROM items WHERE Name LIKE ? LIMIT ? OFFSET ?`, "%"+name+"%", limit, offset) if err != nil { fmt.Errorf("Error querying: %s", err.Error()) return } for rows.Next() { item := &ItemOutput{} err = rows.StructScan(&item) if err != nil { return } items = append(items, item) } return }
//Search for items by name func GetGridsByZone(db *sqlx.DB, name string) (grids []*GridOutput, err error) { rows, err := db.Queryx(`SELECT grid_entries.* FROM grid_entries INNER JOIN zone ON zone.id = grid_entries.zoneid WHERE zone.short_name = ?`, name) if err != nil { fmt.Errorf("Error querying: %s", err.Error()) return } for rows.Next() { grid := &GridOutput{} err = rows.StructScan(&grid) if err != nil { return } grids = append(grids, grid) } return }
func GetVariables(db *sqlx.DB) (map[string]string, error) { type Variable struct { Variable_name string Value string } vars := make(map[string]string) rows, err := db.Queryx("SELECT Variable_name AS variable_name, Variable_Value AS value FROM information_schema.global_variables") if err != nil { return vars, err } for rows.Next() { var v Variable err := rows.Scan(&v.Variable_name, &v.Value) if err != nil { return vars, err } vars[v.Variable_name] = v.Value } return vars, err }
func GetStatus(db *sqlx.DB) map[string]string { type Variable struct { Variable_name string Value string } vars := make(map[string]string) rows, err := db.Queryx("SELECT Variable_name AS variable_name, Variable_Value AS value FROM information_schema.global_status") if err != nil { log.Fatalln("ERROR: Could not get status variable", err) } for rows.Next() { var v Variable err := rows.Scan(&v.Variable_name, &v.Value) if err != nil { log.Fatalln("ERROR: Could not get results from status scan", err) } vars[v.Variable_name] = v.Value } return vars }
// Execute takes a database instance, SQL statement, and parameters and executes the query // returning the resulting rows. func Execute(db *sqlx.DB, sql string, params map[string]interface{}) (*Iterator, error) { var ( err error rows *sqlx.Rows ) // Execute the query. if params != nil && len(params) > 0 { rows, err = db.NamedQuery(sql, params) } else { rows, err = db.Queryx(sql) } if err != nil { return nil, err } return &Iterator{ rows: rows, }, nil }
//Search for items by name func GetSpawnsByZone(db *sqlx.DB, name string) (npcs []*SpawnOutput, err error) { rows, err := db.Queryx(`SELECT spawn2.* FROM spawnentry INNER JOIN spawn2 ON spawnentry.spawngroupid = spawn2.spawngroupid WHERE spawn2.zone = ? GROUP BY spawn2.id;`, name) if err != nil { fmt.Errorf("Error querying: %s", err.Error()) return } for rows.Next() { npc := &SpawnOutput{} err = rows.StructScan(&npc) if err != nil { return } npcs = append(npcs, npc) } return }
//Get an Inventory by Character ID func GetByCharacterId(db *sqlx.DB, id string) (items []*InventoryOutput, err error) { //items = &[]InventoryOutput{} rows, err := db.Queryx(`SELECT i.slotid, items.icon, i.itemid, items.Name, items.slots, i.charges, items.bagslots FROM inventory i JOIN items ON (i.itemid = items.id) WHERE i.charid = ? ORDER BY i.slotid ASC`, id) if err != nil { fmt.Errorf("Error querying: %s", err.Error()) return } for rows.Next() { item := &InventoryOutput{} err = rows.StructScan(&item) if err != nil { return } items = append(items, item) } return }
func SelectuInt(dbx *sqlx.DB, testuint uint64) { type userS struct { ID uint64 TimeCreated time.Time `db:"time_created"` } rows, err := dbx.Queryx("SELECT id, time_created from test_table_int WHERE ID=" + strconv.FormatUint(testuint, 10)) for rows.Next() { var user userS err = rows.StructScan(&user) idstr := strconv.FormatUint(user.ID, 10) fmt.Printf("%#v", user.TimeCreated.Location()) if err != nil { fmt.Println(err) return } fmt.Printf("%#v", idstr) } }
func GetJobs(db *sqlx.DB, jobs Jobs) Jobs { query := "SELECT * FROM `message_queue` WHERE `status` = ? ORDER BY `send_priority` DESC LIMIT 100" rows, err := db.Queryx(query, "pending") if err != nil { panic(err) } for rows.Next() { var job Job err = rows.StructScan(&job) if err != nil { panic(err) } jobs.Jobs = append(jobs.Jobs, job) } db.Close() return jobs }
func generateSpawngroups(db *sqlx.DB) (err error) { f, err := os.Create("../../" + zonename + "/spawngroups.go") if err != nil { fmt.Println(err.Error()) return } defer f.Close() rows, err := db.Queryx(fmt.Sprintf("SELECT * FROM spawngroup where id <= %d and id >= %d", zoneid*1000+999, zoneid*1000)) if err != nil { fmt.Println("Error exec select:", err.Error()) os.Exit(1) } outStr := ` package ` + zonename + ` import ( "github.com/xackery/goeq/spawn" "database/sql" ) var spawngroups []spawn.SpawnGroup = []spawn.SpawnGroup{ ` spawngroupCount := 0 spawngroups := []*spawn.SpawnGroup{} for rows.Next() { spawngroupCount++ sg := spawn.SpawnGroup{} if err = rows.StructScan(&sg); err != nil { fmt.Println("Error getting rows", err.Error()) os.Exit(1) } //special exception, CT spawngroup improper. if sg.Name.String == "cshomeAsherah_the_Torch_Bearer00083304628" { continue } m := mapFields(&sg) spawngroups = append(spawngroups, &sg) outStr += "spawn.SpawnGroup{" for k, v := range m { switch v.(type) { case string: outStr = fmt.Sprintf("%s%s: \"%s\", ", outStr, k, v) case sql.NullString: outStr = fmt.Sprintf("%s%s: sql.NullString{String: \"%s\", Valid: true}, ", outStr, k, v.(sql.NullString).String) case int: outStr = fmt.Sprintf("%s%s: %d, ", outStr, k, v) case sql.NullInt64: outStr = fmt.Sprintf("%s%s: sql.NullInt64{Int64: %d, Valid: true}, ", outStr, k, v.(sql.NullInt64).Int64) case float64: outStr = fmt.Sprintf("%s%s: %f, ", outStr, k, v) default: fmt.Println("Unsupported type:", k, v) return } } outStr = outStr[0 : len(outStr)-2] outStr += "},\n" } outStr += "}" if _, err = f.WriteString(outStr); err != nil { fmt.Println(err.Error()) return } fmt.Println("Generated", spawngroupCount, "spawngroups") //build a string for in clause spawngroupstring := "(" for _, sg := range spawngroups { spawngroupstring = fmt.Sprintf("%s%d, ", spawngroupstring, sg.Id.Int64) } spawngroupstring = spawngroupstring[0:len(spawngroupstring)-2] + ")" f, err = os.Create("../../" + zonename + "/spawnentries.go") if err != nil { fmt.Println(err.Error()) return } defer f.Close() //grab spawnentries rows, err = db.Queryx(fmt.Sprintf("SELECT * FROM spawnentry where spawngroupid in %s", spawngroupstring)) if err != nil { fmt.Println("Error exec select:", err.Error()) os.Exit(1) } outStr = ` package ` + zonename + ` import ( "github.com/xackery/goeq/spawn" //"database/sql" ) var spawnentries []spawn.SpawnEntry = []spawn.SpawnEntry{ ` spawngroupCount = 0 for rows.Next() { spawngroupCount++ tse := takspawn.SpawnEntry{} if err = rows.StructScan(&tse); err != nil { fmt.Println("Error getting rows for spawnentry", err.Error()) os.Exit(1) } se := spawn.SpawnEntry{} if err = se.DecodeFromTakp(&tse); err != nil { fmt.Println(err.Error()) return } m := mapFields(&se) outStr += "spawn.SpawnEntry{" for k, v := range m { switch v.(type) { case string: outStr = fmt.Sprintf("%s%s: \"%s\", ", outStr, k, v) case sql.NullString: outStr = fmt.Sprintf("%s%s: sql.NullString{String: \"%s\", Valid: true}, ", outStr, k, v.(sql.NullString).String) case int: outStr = fmt.Sprintf("%s%s: %d, ", outStr, k, v) case sql.NullInt64: outStr = fmt.Sprintf("%s%s: sql.NullInt64{Int64: %d, Valid: true}, ", outStr, k, v.(sql.NullInt64).Int64) case float64: outStr = fmt.Sprintf("%s%s: %f, ", outStr, k, v) default: fmt.Println("Unsupported type:", k, v) return } } outStr = outStr[0 : len(outStr)-2] outStr += "},\n" } outStr += "}" if _, err = f.WriteString(outStr); err != nil { fmt.Println(err.Error()) return } fmt.Println("Generated", spawngroupCount, "spawnentries") f, err = os.Create("../../" + zonename + "/spawn.go") if err != nil { fmt.Println(err.Error()) return } defer f.Close() //grab spawnentries rows, err = db.Queryx(fmt.Sprintf("SELECT * FROM spawn2 where spawngroupid in %s", spawngroupstring)) if err != nil { fmt.Println("Error exec select:", err.Error()) os.Exit(1) } outStr = ` package ` + zonename + ` import ( "github.com/xackery/goeq/spawn" "database/sql" ) var spawns []spawn.Spawn2 = []spawn.Spawn2{ ` spawngroupCount = 0 for rows.Next() { spawngroupCount++ ts := takspawn.Spawn2{} if err = rows.StructScan(&ts); err != nil { fmt.Println("Error getting rows for spawn2", err.Error()) os.Exit(1) } se := spawn.Spawn2{} if err = se.DecodeFromTakp(&ts); err != nil { fmt.Println(err.Error()) return } m := mapFields(&se) if ts.Pathgrid != 0 { isUnique := true for _, gridid := range gridids { if gridid == ts.Pathgrid { isUnique = false break } } if isUnique { gridids = append(gridids, ts.Pathgrid) } } outStr += "spawn.Spawn2{" for k, v := range m { switch v.(type) { case string: outStr = fmt.Sprintf("%s%s: \"%s\", ", outStr, k, v) case sql.NullString: outStr = fmt.Sprintf("%s%s: sql.NullString{String: \"%s\", Valid: true}, ", outStr, k, v.(sql.NullString).String) case int: outStr = fmt.Sprintf("%s%s: %d, ", outStr, k, v) case sql.NullInt64: outStr = fmt.Sprintf("%s%s: sql.NullInt64{Int64: %d, Valid: true}, ", outStr, k, v.(sql.NullInt64).Int64) case float64: outStr = fmt.Sprintf("%s%s: %f, ", outStr, k, v) default: fmt.Println("Unsupported type:", k, v) return } } outStr = outStr[0 : len(outStr)-2] outStr += "},\n" } outStr += "}" if _, err = f.WriteString(outStr); err != nil { fmt.Println(err.Error()) return } fmt.Println("Generated", spawngroupCount, "spawn2") return }
func generateLoot(db *sqlx.DB) (err error) { f, err := os.Create("../../" + zonename + "/loottable.go") if err != nil { fmt.Println(err.Error()) return } defer f.Close() lootstring := "(" for _, lootid := range lootids { lootstring += fmt.Sprintf("%d, ", lootid) } lootstring = lootstring[0:len(lootstring)-2] + ")" rows, err := db.Queryx(fmt.Sprintf("SELECT * FROM loottable where id in %s", lootstring)) if err != nil { fmt.Println("Error exec select:", err.Error()) os.Exit(1) } outStr := ` package ` + zonename + ` import ( "github.com/xackery/goeq/loot" "database/sql" ) var loottables []loot.LootTable = []loot.LootTable{ ` counter := 0 for rows.Next() { counter++ tl := takloot.LootTable{} if err = rows.StructScan(&tl); err != nil { fmt.Println("Error getting rows", err.Error()) os.Exit(1) } l := loot.LootTable{} if err = l.DecodeFromTakp(&tl); err != nil { fmt.Println(err.Error()) return } m := mapFields(&l) outStr += "loot.LootTable{" for k, v := range m { switch v.(type) { case string: outStr = fmt.Sprintf("%s%s: \"%s\", ", outStr, k, v) case sql.NullString: outStr = fmt.Sprintf("%s%s: sql.NullString{String: \"%s\", Valid: true}, ", outStr, k, v.(sql.NullString).String) case int: outStr = fmt.Sprintf("%s%s: %d, ", outStr, k, v) case sql.NullInt64: outStr = fmt.Sprintf("%s%s: sql.NullInt64{Int64: %d, Valid: true}, ", outStr, k, v.(sql.NullInt64).Int64) case float64: outStr = fmt.Sprintf("%s%s: %f, ", outStr, k, v) default: fmt.Println("Unsupported type:", k, v) return } } outStr = outStr[0 : len(outStr)-2] outStr += "},\n" } outStr += "}" if _, err = f.WriteString(outStr); err != nil { fmt.Println(err.Error()) return } fmt.Println("Generated", counter, "loottable") f, err = os.Create("../../" + zonename + "/loottableentries.go") if err != nil { fmt.Println(err.Error()) return } defer f.Close() rows, err = db.Queryx(fmt.Sprintf("SELECT * FROM loottable_entries WHERE loottable_id IN %s", lootstring)) if err != nil { fmt.Println("Error exec select:", err.Error()) os.Exit(1) } outStr = ` package ` + zonename + ` import ( "github.com/xackery/goeq/loot" //"database/sql" ) var loottableentries []loot.LootTableEntries = []loot.LootTableEntries{ ` counter = 0 for rows.Next() { counter++ tl := takloot.LootTableEntries{} if err = rows.StructScan(&tl); err != nil { fmt.Println("Error getting rows", err.Error()) os.Exit(1) } l := loot.LootTableEntries{} if err = l.DecodeFromTakp(&tl); err != nil { fmt.Println(err.Error()) return } m := mapFields(&l) outStr += "loot.LootTableEntries{" for k, v := range m { switch v.(type) { case string: outStr = fmt.Sprintf("%s%s: \"%s\", ", outStr, k, v) case sql.NullString: outStr = fmt.Sprintf("%s%s: sql.NullString{String: \"%s\", Valid: true}, ", outStr, k, v.(sql.NullString).String) case int: outStr = fmt.Sprintf("%s%s: %d, ", outStr, k, v) case sql.NullInt64: outStr = fmt.Sprintf("%s%s: sql.NullInt64{Int64: %d, Valid: true}, ", outStr, k, v.(sql.NullInt64).Int64) case float64: outStr = fmt.Sprintf("%s%s: %f, ", outStr, k, v) default: fmt.Println("Unsupported type:", k, v) return } } outStr = outStr[0 : len(outStr)-2] outStr += "},\n" } outStr += "}" if _, err = f.WriteString(outStr); err != nil { fmt.Println(err.Error()) return } fmt.Println("Generated", counter, "loottableentry") f, err = os.Create("../../" + zonename + "/lootdrop.go") if err != nil { fmt.Println(err.Error()) return } defer f.Close() rows, err = db.Queryx(fmt.Sprintf("SELECT lootdrop.* FROM lootdrop INNER JOIN loottable_entries ON loottable_entries.lootdrop_id = lootdrop.id WHERE loottable_id in %s", lootstring)) if err != nil { fmt.Println("Error exec select:", err.Error()) os.Exit(1) } outStr = ` package ` + zonename + ` import ( "github.com/xackery/goeq/loot" //"database/sql" ) var lootdrops []loot.LootDrop = []loot.LootDrop{ ` counter = 0 for rows.Next() { counter++ l := loot.LootDrop{} if err = rows.StructScan(&l); err != nil { fmt.Println("Error getting rows", err.Error()) os.Exit(1) } m := mapFields(&l) outStr += "loot.LootDrop{" for k, v := range m { switch v.(type) { case string: outStr = fmt.Sprintf("%s%s: \"%s\", ", outStr, k, v) case sql.NullString: outStr = fmt.Sprintf("%s%s: sql.NullString{String: \"%s\", Valid: true}, ", outStr, k, v.(sql.NullString).String) case int: outStr = fmt.Sprintf("%s%s: %d, ", outStr, k, v) case sql.NullInt64: outStr = fmt.Sprintf("%s%s: sql.NullInt64{Int64: %d, Valid: true}, ", outStr, k, v.(sql.NullInt64).Int64) case float64: outStr = fmt.Sprintf("%s%s: %f, ", outStr, k, v) default: fmt.Println("Unsupported type:", k, v) return } } outStr = outStr[0 : len(outStr)-2] outStr += "},\n" } outStr += "}" if _, err = f.WriteString(outStr); err != nil { fmt.Println(err.Error()) return } fmt.Println("Generated", counter, "LootDrop") f, err = os.Create("../../" + zonename + "/lootdropentries.go") if err != nil { fmt.Println(err.Error()) return } defer f.Close() rows, err = db.Queryx(fmt.Sprintf(` SELECT lootdrop_entries.* FROM lootdrop_entries INNER JOIN lootdrop ON lootdrop.id = lootdrop_entries.lootdrop_id INNER JOIN loottable_entries ON loottable_entries.lootdrop_id = lootdrop.id WHERE loottable_id in %s GROUP BY lootdrop_entries.item_id, lootdrop_entries.lootdrop_id`, lootstring)) if err != nil { fmt.Println("Error exec select:", err.Error()) os.Exit(1) } outStr = ` package ` + zonename + ` import ( "github.com/xackery/goeq/loot" //"database/sql" ) var lootdropentries []loot.LootDropEntries = []loot.LootDropEntries{ ` counter = 0 for rows.Next() { counter++ l := loot.LootDropEntries{} if err = rows.StructScan(&l); err != nil { fmt.Println("Error getting rows", err.Error()) os.Exit(1) } m := mapFields(&l) outStr += "loot.LootDropEntries{" for k, v := range m { switch v.(type) { case string: outStr = fmt.Sprintf("%s%s: \"%s\", ", outStr, k, v) case sql.NullString: outStr = fmt.Sprintf("%s%s: sql.NullString{String: \"%s\", Valid: true}, ", outStr, k, v.(sql.NullString).String) case int: outStr = fmt.Sprintf("%s%s: %d, ", outStr, k, v) case sql.NullInt64: outStr = fmt.Sprintf("%s%s: sql.NullInt64{Int64: %d, Valid: true}, ", outStr, k, v.(sql.NullInt64).Int64) case float64: outStr = fmt.Sprintf("%s%s: %f, ", outStr, k, v) default: fmt.Println("Unsupported type:", k, v) return } } outStr = outStr[0 : len(outStr)-2] outStr += "},\n" } outStr += "}" if _, err = f.WriteString(outStr); err != nil { fmt.Println(err.Error()) return } fmt.Println("Generated", counter, "LootDropEntries") return }
/* Run mysql task. */ func (m *MySQLTask) Run(r *http.Request, data map[string]interface{}) (response *Response) { response = NewResponse(http.StatusOK) queries := []*Response{} var ( db *sqlx.DB rows *sqlx.Rows err error ) for _, query := range m.config.Queries { var ( Rows []map[string]interface{} ) args := []interface{}{} qr := NewResponse(http.StatusOK).StripStatusData() var url string if url, err = m.Interpolate(query.URL, data); err != nil { qr.Error(err) goto Append } if m.config.ReturnQueries { qr.AddValue("query", query.Query) } if db, err = sqlx.Open("mysql", url); err != nil { qr.Error(err.Error()) if err, ok := err.(*mysql.MySQLError); ok { qr.AddValue("error_code", err.Number) } goto Append } for _, arg := range query.Args { var a string if a, err = m.Interpolate(arg, data); err != nil { qr.Error(err) goto Append } args = append(args, a) } if m.config.ReturnQueries { qr.AddValue("args", args) } // run query rows, err = db.Queryx(query.Query, args...) if err != nil { qr.Error(err) if err, ok := err.(*mysql.MySQLError); ok { qr.AddValue("error_code", err.Number) } goto Append } Rows = []map[string]interface{}{} for rows.Next() { results := make(map[string]interface{}) err = rows.MapScan(results) if err != nil { qr.Error(err) goto Append } Rows = append(Rows, results) } qr.Result(Rows) Append: queries = append(queries, qr) } // single result if m.config.singleResultIndex != -1 { response.Result(queries[m.config.singleResultIndex]) } else { response.Result(queries) } return }
func generateNPCTypes(db *sqlx.DB) (err error) { f, err := os.Create("../../" + zonename + "/npctypes.go") if err != nil { fmt.Println(err.Error()) return } defer f.Close() rows, err := db.Queryx(fmt.Sprintf("SELECT * FROM npc_types where id <= %d and id >= %d", zoneid*1000+999, zoneid*1000)) if err != nil { fmt.Println("Error exec select:", err.Error()) os.Exit(1) } outStr := ` package ` + zonename + ` import ( "github.com/xackery/goeq/npc" "database/sql" ) var npctypes []npc.NpcTypes = []npc.NpcTypes{ ` npcCount := 0 for rows.Next() { npcCount++ tnpc := taknpc.NpcTypes{} if err = rows.StructScan(&tnpc); err != nil { fmt.Println("Error getting rows", err.Error()) os.Exit(1) } //fmt.Println(tnpc) pnpc := npc.NpcTypes{} if err = pnpc.DecodeFromTakp(&tnpc); err != nil { fmt.Println(err.Error()) return } //Store loot tables for later, when loot gets generated if pnpc.Loottable_id != 0 { isUnique := true for _, lootid := range lootids { if lootid == pnpc.Loottable_id { isUnique = false break } } if isUnique { lootids = append(lootids, pnpc.Loottable_id) } } m := mapFields(&pnpc) outStr += "npc.NpcTypes{" for k, v := range m { switch v.(type) { case string: outStr = fmt.Sprintf("%s%s: \"%s\", ", outStr, k, v) case sql.NullString: outStr = fmt.Sprintf("%s%s: sql.NullString{String: \"%s\", Valid: true}, ", outStr, k, v.(sql.NullString).String) case int: outStr = fmt.Sprintf("%s%s: %d, ", outStr, k, v) case sql.NullInt64: outStr = fmt.Sprintf("%s%s: sql.NullInt64{Int64: %d, Valid: true}, ", outStr, k, v.(sql.NullInt64).Int64) case float64: outStr = fmt.Sprintf("%s%s: %f, ", outStr, k, v) default: fmt.Println("Unsupported type:", k, v) return } } outStr = outStr[0 : len(outStr)-2] outStr += "},\n" } outStr += "}" if _, err = f.WriteString(outStr); err != nil { fmt.Println(err.Error()) return } fmt.Println("Generated", npcCount, "npcs") return }
func generateGrids(db *sqlx.DB) (err error) { gridstring := "(" for _, gridid := range gridids { gridstring += fmt.Sprintf("%d, ", gridid) } gridstring = gridstring[0:len(gridstring)-2] + ")" f, err := os.Create("../../" + zonename + "/grid.go") if err != nil { fmt.Println(err.Error()) return } defer f.Close() rows, err := db.Queryx(fmt.Sprintf("SELECT * FROM grid WHERE zoneid = %d AND id IN %s", zoneid, gridstring)) if err != nil { fmt.Println("Error exec select:", err.Error()) os.Exit(1) } outStr := ` package ` + zonename + ` import ( "github.com/xackery/goeq/grid" ) var grids []grid.Grid = []grid.Grid{ ` count := 0 for rows.Next() { count++ gr := grid.Grid{} if err = rows.StructScan(&gr); err != nil { fmt.Println("Error getting rows", err.Error()) os.Exit(1) } m := mapFields(&gr) outStr += "grid.Grid{" for k, v := range m { switch v.(type) { case string: outStr = fmt.Sprintf("%s%s: \"%s\", ", outStr, k, v) case sql.NullString: outStr = fmt.Sprintf("%s%s: sql.NullString{String: \"%s\", Valid: true}, ", outStr, k, v.(sql.NullString).String) case int: outStr = fmt.Sprintf("%s%s: %d, ", outStr, k, v) case sql.NullInt64: outStr = fmt.Sprintf("%s%s: sql.NullInt64{Int64: %d, Valid: true}, ", outStr, k, v.(sql.NullInt64).Int64) case float64: outStr = fmt.Sprintf("%s%s: %f, ", outStr, k, v) default: fmt.Println("Unsupported type:", k, v) return } } outStr = outStr[0 : len(outStr)-2] outStr += "},\n" } outStr += "}" if _, err = f.WriteString(outStr); err != nil { fmt.Println(err.Error()) return } fmt.Println("Generated", count, "grids") f, err = os.Create("../../" + zonename + "/gridentries.go") if err != nil { fmt.Println(err.Error()) return } defer f.Close() rows, err = db.Queryx(fmt.Sprintf("SELECT * FROM grid_entries WHERE zoneid = %d AND gridid IN %s", zoneid, gridstring)) if err != nil { fmt.Println("Error exec select:", err.Error()) os.Exit(1) } outStr = ` package ` + zonename + ` import ( "github.com/xackery/goeq/grid" ) var gridentries []grid.GridEntries = []grid.GridEntries{ ` count = 0 for rows.Next() { count++ gr := grid.GridEntries{} if err = rows.StructScan(&gr); err != nil { fmt.Println("Error getting rows", err.Error()) os.Exit(1) } m := mapFields(&gr) outStr += "grid.GridEntries{" for k, v := range m { switch v.(type) { case string: outStr = fmt.Sprintf("%s%s: \"%s\", ", outStr, k, v) case sql.NullString: outStr = fmt.Sprintf("%s%s: sql.NullString{String: \"%s\", Valid: true}, ", outStr, k, v.(sql.NullString).String) case int: outStr = fmt.Sprintf("%s%s: %d, ", outStr, k, v) case sql.NullInt64: outStr = fmt.Sprintf("%s%s: sql.NullInt64{Int64: %d, Valid: true}, ", outStr, k, v.(sql.NullInt64).Int64) case float64: outStr = fmt.Sprintf("%s%s: %f, ", outStr, k, v) default: fmt.Println("Unsupported type:", k, v) return } } outStr = outStr[0 : len(outStr)-2] outStr += "},\n" } outStr += "}" if _, err = f.WriteString(outStr); err != nil { fmt.Println(err.Error()) return } fmt.Println("Generated", count, "gridentries") return }
/* Run postgres task */ func (p *PostgresTask) Run(r *http.Request, data map[string]interface{}) (response *Response) { response = NewResponse(http.StatusOK) queryresults := []*Response{} for _, query := range p.config.Queries { qresponse := NewResponse(http.StatusOK).StripStatusData() var ( args []interface{} db *sqlx.DB err error url string rows *sqlx.Rows Rows []map[string]interface{} errq error ) if url, err = p.Interpolate(query.URL, data); err != nil { qresponse.Error(err) goto Append } // interpolate all args args = []interface{}{} for _, arg := range query.Args { interpolated, e := p.Interpolate(arg, data) if e != nil { qresponse.Error(e) goto Append } args = append(args, interpolated) } // add query with args to response? if p.config.ReturnQueries { qresponse.AddValue("query", query.Query).AddValue("args", args) } if db, err = sqlx.Connect("postgres", url); err != nil { if err, ok := err.(*pq.Error); ok { qresponse.AddValue("error_code", err.Code.Name()) } qresponse.Error(err) goto Append } // run query rows, errq = db.Queryx(query.Query, args...) if errq != nil { if errq, ok := errq.(*pq.Error); ok { qresponse.AddValue("error_code", errq.Code.Name()) } qresponse.Error(errq) goto Append } Rows = []map[string]interface{}{} for rows.Next() { results := make(map[string]interface{}) err = rows.MapScan(results) if err != nil { if err, ok := err.(*pq.Error); ok { qresponse.AddValue("error_code", err.Code.Name()) } qresponse.Error(err) goto Append } Rows = append(Rows, results) } qresponse.Result(Rows) Append: queryresults = append(queryresults, qresponse) } // single result if p.config.singleResultIndex != -1 { response.Result(queryresults[p.config.singleResultIndex]) } else { response.Result(queryresults) } return }
func fetchStatDatabase(db *sqlx.DB) (map[string]interface{}, error) { db = db.Unsafe() rows, err := db.Queryx(`SELECT * FROM pg_stat_database`) if err != nil { logger.Errorf("Failed to select pg_stat_database. %s", err) return nil, err } type pgStat struct { XactCommit uint64 `db:"xact_commit"` XactRollback uint64 `db:"xact_rollback"` BlksRead uint64 `db:"blks_read"` BlksHit uint64 `db:"blks_hit"` BlkReadTime *float64 `db:"blk_read_time"` BlkWriteTime *float64 `db:"blk_write_time"` TupReturned uint64 `db:"tup_returned"` TupFetched uint64 `db:"tup_fetched"` TupInserted uint64 `db:"tup_inserted"` TupUpdated uint64 `db:"tup_updated"` TupDeleted uint64 `db:"tup_deleted"` Deadlocks *uint64 `db:"deadlocks"` TempBytes *uint64 `db:"temp_bytes"` } totalStat := pgStat{} for rows.Next() { p := pgStat{} if err := rows.StructScan(&p); err != nil { logger.Warningf("Failed to scan. %s", err) continue } totalStat.XactCommit += p.XactCommit totalStat.XactRollback += p.XactRollback totalStat.BlksRead += p.BlksRead totalStat.BlksHit += p.BlksHit if p.BlkReadTime != nil { if totalStat.BlkReadTime == nil { totalStat.BlkReadTime = p.BlkReadTime } else { *totalStat.BlkReadTime += *p.BlkReadTime } } if p.BlkWriteTime != nil { if totalStat.BlkWriteTime == nil { totalStat.BlkWriteTime = p.BlkWriteTime } else { *totalStat.BlkWriteTime += *p.BlkWriteTime } } totalStat.TupReturned += p.TupReturned totalStat.TupFetched += p.TupFetched totalStat.TupInserted += p.TupInserted totalStat.TupUpdated += p.TupUpdated totalStat.TupDeleted += p.TupDeleted if p.Deadlocks != nil { if totalStat.Deadlocks == nil { totalStat.Deadlocks = p.Deadlocks } else { *totalStat.Deadlocks += *p.Deadlocks } } if p.TempBytes != nil { if totalStat.TempBytes == nil { totalStat.TempBytes = p.TempBytes } else { *totalStat.TempBytes += *p.TempBytes } } } stat := make(map[string]interface{}) stat["xact_commit"] = totalStat.XactCommit stat["xact_rollback"] = totalStat.XactRollback stat["blks_read"] = totalStat.BlksRead stat["blks_hit"] = totalStat.BlksHit if totalStat.BlkReadTime != nil { stat["blk_read_time"] = *totalStat.BlkReadTime } if totalStat.BlkWriteTime != nil { stat["blk_write_time"] = *totalStat.BlkWriteTime } stat["tup_returned"] = totalStat.TupReturned stat["tup_fetched"] = totalStat.TupFetched stat["tup_inserted"] = totalStat.TupInserted stat["tup_updated"] = totalStat.TupUpdated stat["tup_deleted"] = totalStat.TupDeleted if totalStat.Deadlocks != nil { stat["deadlocks"] = *totalStat.Deadlocks } if totalStat.TempBytes != nil { stat["temp_bytes"] = *totalStat.TempBytes } return stat, nil }