Golang DB.QueryRowx方法代码示例

func CheckLongRunningWrites(db *sqlx.DB, thresh int) int {
	var count int
	err := db.QueryRowx("select count(*) from information_schema.processlist where command = 'Query' and time >= ? and info not like 'select%'", thresh).Scan(&count)
	if err != nil {
		log.Println("ERROR: Could not check long running writes", err)
	}
	return count
}

func getUserById(db *sqlx.DB, id int) (*User, error) {
	var user User
	err := db.QueryRowx("SELECT * FROM users WHERE id = $1", id).StructScan(&user)
	if err == sql.ErrNoRows {
		return nil, ErrNotFound
	}
	return &user, dbError(err)
}

func getUserByName(db *sqlx.DB, name string) (*User, error) {
	var user User
	err := db.QueryRowx("SELECT * FROM users WHERE name = $1", name).StructScan(&user)
	if err == sql.ErrNoRows {
		return nil, ErrNotFound
	}
	return &user, dbError(err)
}

func getQuestionById(db *sqlx.DB, id int) (*Question, error) {
	var q Question
	err := db.QueryRowx("SELECT * FROM questions WHERE id = $1", id).StructScan(&q)
	if err == sql.ErrNoRows {
		return nil, ErrNotFound
	}
	return &q, dbError(err)
}

func GetVariableByName(db *sqlx.DB, name string) string {
	var value string
	err := db.QueryRowx("SELECT Variable_Value AS Value FROM information_schema.global_variables WHERE Variable_Name = ?", name).Scan(&value)
	if err != nil {
		log.Println("ERROR: Could not get variable by name", err)
	}
	return value
}

func insertUser(db *sqlx.DB, name string, email string, password string) (*User, error) {
	var user User
	err := db.QueryRowx("INSERT INTO users (name, email, password_hash) VALUES ($1, $2, $3) RETURNING *", name, email, password).StructScan(&user)
	if dbErr := dbError(err); dbErr != nil {
		return nil, dbErr
	} else {
		return &user, nil
	}
}

func insertQuestion(db *sqlx.DB, authorId int, title string, question string) (*Question, error) {
	var q Question
	err := db.QueryRowx("INSERT INTO questions (author_id, title, question) VALUES ($1, $2, $3) RETURNING *", authorId, title, question).StructScan(&q)
	if dbErr := dbError(err); dbErr != nil {
		return nil, dbErr
	} else {
		return &q, nil
	}
}

//Get a character by ID
func GetById(db *sqlx.DB, id string) (character *CharacterOutput, err error) {
	character = &CharacterOutput{}
	err = db.QueryRowx(`SELECT cd.*, cs.name as class_name 
		FROM character_data cd 
		JOIN class_skill cs ON (cs.class = cd.class) 
		WHERE cd.id = ?`, id).StructScan(character)
	if err != nil {
		fmt.Errorf("Error querying: %s", err.Error())
		return
	}
	return
}

// Save a message to the database, updating the message ID.
func (m *Msg) Save(db *sqlx.DB) error {
	var pluginName string
	if m.Plugin != nil {
		pluginName = m.Plugin.Config.Name
	}
	q := `INSERT INTO messages
	      (userid, sentence, plugin, route, abotsent, needstraining, flexid,
		flexidtype, trained)
	      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING id`
	row := db.QueryRowx(q, m.User.ID, m.Sentence, pluginName, m.Route,
		m.AbotSent, m.NeedsTraining, m.User.FlexID, m.User.FlexIDType,
		m.Trained)
	if err := row.Scan(&m.ID); err != nil {
		return err
	}
	return nil
}

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 NewUser(db *sqlx.DB, u User) (int64, error) {
	u.Created_At = time.Now()
	u.Updated_At = time.Now()
	query, args, err := ParseQueryStruct(QNewUser, u, db)

	if err != nil {
		return 0, err
	}

	// Postgres does not return last inserted row id in sql.Result
	// so we have to do this
	var id int64
	row := db.QueryRowx(query, args...)
	row.Scan(&id)

	if row.Err() != nil {
		return 0, row.Err()
	}

	return id, nil
}

func assignDataToPicksAndBans(db *sqlx.DB, match *modal.Live) (matches []modal.Modal, err error) {
	var rpicks []modal.Pick
	err = db.Select(&rpicks, "select t2.dotaid, t2.name from Pick t1, Hero t2 where t1.heroid = t2.dotaid and t1.scoreid=?", match.RadiantID)
	if err != nil {
		return matches, err
	}
	match.Radiant.Picks = append(match.Radiant.Picks, rpicks...)

	var rbans []modal.Ban
	err = db.Select(&rbans, "select t2.dotaid, t2.name from Ban t1, Hero t2 where t1.heroid = t2.dotaid and t1.scoreid=?", match.RadiantID)
	if err != nil {
		return matches, err
	}
	match.Radiant.Bans = append(match.Radiant.Bans, rbans...)

	err = db.QueryRowx("SELECT * FROM Score  where id=? LIMIT 1", match.DireID).StructScan(&match.Dire)
	if err != nil {
		return matches, err
	}

	var dpicks []modal.Pick
	err = db.Select(&dpicks, "select t2.dotaid, t2.name from Pick t1, Hero t2 where t1.heroid = t2.dotaid and t1.scoreid=?", match.DireID)
	if err != nil {
		return matches, err
	}
	match.Dire.Picks = append(match.Dire.Picks, dpicks...)

	var dbans []modal.Ban
	err = db.Select(&dbans, "select t2.dotaid, t2.name from Ban t1, Hero t2 where t1.heroid = t2.dotaid and t1.scoreid=?", match.DireID)
	if err != nil {
		return matches, err
	}
	match.Dire.Bans = append(match.Dire.Bans, dbans...)

	return matches, nil
}