Golang DB.Get方法代码示例

func validateMasterPassword(db *sqlx.DB) {
	c := dbConfig{}

	db.Get(&c, "SELECT `key`, `value` FROM `config` WHERE `key` = 'teststring'")

	if c.Key == "" {
		panic("Could not read the teststring from the config table. Your database is broken.")
	}

	// not yet initialized, so store the ciphertext
	if len(c.Value) == 0 {
		ciphertext, err := Encrypt([]byte(TestString))
		if err != nil {
			panic(err)
		}

		_, err = db.Exec("UPDATE `config` SET `value` = ? WHERE `key` = ?", ciphertext, c.Key)
		if err != nil {
			panic("Could not write initial password marker: " + err.Error())
		}
	} else {
		plaintext, err := Decrypt(c.Value)
		if err != nil {
			panic("The configured password is not usable for the configured database.")
		}

		// this should never happen: a wrong password should always yield an error in Decrypt()
		if TestString != string(plaintext) {
			panic("The configured password is not usable for the configured database.")
		}
	}
}

// addTimeContext adds a time context to a Message if the word "then" is found.
func addTimeContext(db *sqlx.DB, in *dt.Msg) error {
	var addContext bool
	for _, stem := range in.Stems {
		if stem == "then" {
			addContext = true
			break
		}
	}
	if !addContext {
		return nil
	}
	var byt []byte
	var err error
	if in.User.ID > 0 {
		q := `SELECT value FROM states WHERE userid=$1 AND key=$2`
		err = db.Get(&byt, q, in.User.ID, keyContextTime)
	} else {
		q := `SELECT value FROM states
		      WHERE flexid=$1 AND flexidtype=$2 AND key=$3`
		err = db.Get(&byt, q, in.User.FlexID, in.User.FlexIDType,
			keyContextTime)
	}
	if err == sql.ErrNoRows {
		return nil
	}
	if err != nil {
		return err
	}
	var times []time.Time
	if err = json.Unmarshal(byt, &times); err != nil {
		return err
	}
	in.StructuredInput.Times = times
	return nil
}

func InsertAnswer(db *sqlx.DB, userId int, questionId int, message string) (*Answer, error) {
	a := Answer{}
	a.AuthorId = userId
	a.QuestionId = questionId
	a.Message = message
	var err error

	if err = ValidateAnswer(a); err != nil {
		return nil, err
	}

	qry := sq.Insert("answers").
		Columns("author_id", "question_id", "message").
		Values(a.AuthorId, a.QuestionId, a.Message).
		Suffix("RETURNING *").
		PlaceholderFormat(sq.Dollar)

	sql, params, err := qry.ToSql()
	if err != nil {
		return nil, err
	}

	err = db.Get(&a, sql, params...)
	dbErr := dbError(err)
	if dbErr != nil {
		return nil, dbErr
	} else {
		return &a, nil
	}
}

func (p *Post) GetBySlug(db *sqlx.DB, slug string) error {

	err := db.Get(p, "SELECT * FROM posts WHERE status=1 AND slug=$1", slug)
	if err != nil {
		return err
	}
	return nil
}

func (p *Post) GetById(db *sqlx.DB, post_id int) error {

	err := db.Get(p, "SELECT * FROM posts WHERE id=$1", post_id)
	if err != nil {
		return err
	}
	return nil
}

// GetChannelList returns the ChannelList for the given id.
func GetChannelList(db *sqlx.DB, id int64) (models.ChannelList, error) {
	var cl models.ChannelList
	err := db.Get(&cl, "select * from channel_list where id = $1", id)
	if err != nil {
		return cl, fmt.Errorf("get channel-list %d error: %s", id, err)
	}
	return cl, nil
}

// GetNode returns the Node for the given DevEUI.
func GetNode(db *sqlx.DB, devEUI lorawan.EUI64) (models.Node, error) {
	var node models.Node
	err := db.Get(&node, "select * from node where dev_eui = $1", devEUI[:])
	if err != nil {
		return node, fmt.Errorf("get node %s error: %s", devEUI, err)
	}
	return node, nil
}

// GetChannel returns the Channel matching the given id.
func GetChannel(db *sqlx.DB, id int64) (models.Channel, error) {
	var channel models.Channel
	err := db.Get(&channel, "select * from channel where id = $1", id)
	if err != nil {
		return channel, fmt.Errorf("get channel %d error: %s", id, err)
	}
	return channel, nil
}

// getApplication returns the Application for the given AppEUI.
func getApplication(db *sqlx.DB, appEUI lorawan.EUI64) (models.Application, error) {
	var app models.Application
	err := db.Get(&app, "select * from application where app_eui = $1", appEUI[:])
	if err != nil {
		return app, fmt.Errorf("get application %s error: %s", appEUI, err)
	}
	return app, nil
}

// GetLocationBySlug Looks up a location by its Yelp ID
func GetLocationBySlug(db *sqlx.DB, slug string) (*models.Location, error) {
	l := models.Location{}
	err := db.Get(&l, db.Rebind(`SELECT * FROM locations WHERE slug=?`), slug)
	if err != nil {
		return nil, err
	}
	return &l, nil
}

func GetUserData(db *sqlx.DB, userId int64) (*ApiUser, error) {
	var user ApiUser
	var err error

	err = db.Get(&user, "SELECT * FROM tinyplannr_api.user WHERE user_id = $1", userId)
	log.Println(err)

	return &user, err
}

func GetUserByID(db *sqlx.DB, id int) (*models.User, error) {
	u := models.User{}
	err := db.Get(&u, db.Rebind(`SELECT * FROM users WHERE id=?`), id)
	if err != nil {
		log.Printf("Error while getting user by id: %v", err)
		return nil, err
	}
	return &u, nil
}

func FetchToken(db *sqlx.DB, token string, maxAge int) (*Token, error) {
	t := Token{}
	err := db.Get(&t, "select user_name,token,attempts,email from token where token = ? and timestampdiff(SECOND, created_at, now()) <= ?", token, maxAge)
	if err != nil {
		return nil, err
	}

	return &t, nil
}

func FetchAnswer(db *sqlx.DB, uid string) (*SecurityAnswer, error) {
	answer := SecurityAnswer{}
	err := db.Get(&answer, "select a.user_name,a.question_id,q.question,a.answer from security_answer a join security_question q on a.question_id = q.id  where a.user_name = ?", uid)
	if err != nil {
		return nil, err
	}

	return &answer, nil
}

func (q *Query) GetResult(db *sqlx.DB, result interface{}) error {
	sql, vars := q.GetSQL()

	if debugEnabled {
		marshaled, _ := json.Marshal(vars)
		Debug("%s, %s", sql, string(marshaled))
	}

	return db.Get(result, sql, vars...)
}