Golang DB.Queryx方法代码示例

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

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
}

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

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
}