func (p *Posts) GetPostsLimit(db *sqlx.DB, offset, limit int) int { var total_posts int err := db.Get(&total_posts, "SELECT count(id) FROM posts WHERE status=1 AND created::date<=CURRENT_TIMESTAMP::date") if err != nil { panic(err) } err = db.Select(p, "SELECT * FROM posts WHERE status=1 AND created::date<=CURRENT_TIMESTAMP::date ORDER BY created DESC LIMIT $1 OFFSET $2", limit, offset) if err != nil { panic(err) } return total_posts }
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, ×); 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 }
// 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 }
// 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 }
// 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 }
// 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 }
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 }
// GetNodesCount returns the total number of nodes. func GetNodesCount(db *sqlx.DB) (int, error) { var count struct { Count int } err := db.Get(&count, "select count(*) as count from node") if err != nil { return 0, fmt.Errorf("get nodes count error: %s", err) } return count.Count, nil }
func CountPublishedPosts(db *sqlx.DB) int { var total_posts int err := db.Get(&total_posts, "SELECT count(id) FROM posts WHERE status=1 AND created::date<=CURRENT_TIMESTAMP::date") if err != nil { panic(err) } return total_posts }
func FindUserByID(db *sqlx.DB, uid int64) (*User, error) { user := User{} // query := "SELECT * FROM users WHERE username=$1" err := db.Get(&user, "SELECT * FROM users WHERE id=$1", uid) err = user.QueryResources(db) if err != nil { log.Println(err) } return &user, err }
// GetApplicationsCount returns the number of applications. func GetApplicationsCount(db *sqlx.DB) (int, error) { var count struct { Count int } err := db.Get(&count, "select count(*) as count from application") if err != nil { return 0, fmt.Errorf("get applications count error: %s", err) } return count.Count, nil }
// GetMsg returns a message for a given message ID. func GetMsg(db *sqlx.DB, id uint64) (*Msg, error) { q := `SELECT id, sentence, abotsent FROM messages WHERE id=$1` m := &Msg{} if err := db.Get(m, q, id); err != nil { return nil, err } return m, 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...) }
// GetChannelListsCount returns the total number of channel-lists. func GetChannelListsCount(db *sqlx.DB) (int, error) { var count struct { Count int } err := db.Get(&count, "select count(*) as count from channel_list") if err != nil { return 0, fmt.Errorf("get channel-list count error: %s", err) } return count.Count, nil }
// New method func (p *Post) New(db *sqlx.DB) int { var lastInsertId int err := db.Get(&lastInsertId, "INSERT INTO posts (title,shortmessage,body,status,slug,created,modified) VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING id", p.Title, p.Shortmessage, p.Body, p.Status, p.Slug, p.Created, p.Modified) if err != nil { panic(err) } return lastInsertId }
// GetNodesForAppEUICount returns the total number of nodes given an AppEUI. func GetNodesForAppEUICount(db *sqlx.DB, appEUI lorawan.EUI64) (int, error) { var count struct { Count int } err := db.Get(&count, "select count(*) as count from node where app_eui = $1", appEUI[:]) if err != nil { return 0, fmt.Errorf("get nodes count for app_eui=%s error: %s", appEUI, err) } return count.Count, nil }
func getPage(db *sqlx.DB, link string) (*Page, error) { page := Page{} err := db.Get(&page, "SELECT * FROM page where link=$1", link) // fmt.Printf("%#v\n", page) if err != nil { log.Println(err) return &Page{}, err } else { return &page, nil } }
func Login(db *sqlx.DB, email string, password string) (*models.User, error) { user := models.User{} err := db.Get(&user, "SELECT * FROM users where email=lower($1)", email) if err != nil { return nil, err } err = bcrypt.CompareHashAndPassword(user.Password, []byte(password)) if err != nil { return nil, err } return &user, nil }
// CreateChannelList creates the given ChannelList. func CreateChannelList(db *sqlx.DB, cl *models.ChannelList) error { err := db.Get(&cl.ID, "insert into channel_list (name) values ($1) returning id", cl.Name, ) if err != nil { return fmt.Errorf("create channel-list '%s' error: %s", cl.Name, err) } log.WithFields(log.Fields{ "id": cl.ID, "name": cl.Name, }).Info("channel-list created") return nil }
func (t *Token) Get(db *sqlx.DB, id int64) error { err := db.Get(t, "SELECT * FROM tokens WHERE id = $1 LIMIT 1;", id) if err, ok := err.(*pq.Error); ok { switch err.Code.Name() { default: return &Error{err.Code.Name(), "pq error"} } } if err == sql.ErrNoRows { return &Error{"record_not_found", "token not found"} } return err }
func (h *Hub) Get(db *sqlx.DB, slug string) error { err := db.Get(h, "SELECT user_id FROM hubs WHERE slug = $1;", slug) if err, ok := err.(*pq.Error); ok { switch err.Code.Name() { default: return &Error{err.Code.Name(), "pq error"} } } if err == sql.ErrNoRows { return &Error{"record_not_found", "hub not found"} } return err }
func (p *Posts) GetAllPublished(db *sqlx.DB) int { var total_posts int err := db.Get(&total_posts, "SELECT count(id) FROM posts WHERE status=1 AND created::date<=CURRENT_TIMESTAMP::date") if err != nil { panic(err) } err = db.Select(p, "SELECT * FROM posts WHERE status=1 AND created::date<=CURRENT_TIMESTAMP::date ORDER BY created DESC") if err != nil { panic(err) } return total_posts }