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
}
