Example #1
1
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
}
Example #2
0
File: main.go Project: xrstf/raziel
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.")
		}
	}
}
Example #3
0
// 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
}
Example #4
0
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
	}
}
Example #5
0
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
}
Example #6
0
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
}
Example #7
0
// 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
}
Example #8
0
// 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
}
Example #9
0
// 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
}
Example #10
0
// 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
}
Example #11
0
// 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
}
Example #12
0
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
}
Example #13
0
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
}
Example #14
0
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
}
Example #15
0
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
}
Example #16
0
// 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
}
Example #17
0
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
}
Example #18
0
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
}
Example #19
0
// 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
}
Example #20
0
// 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
}
Example #21
0
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...)
}
Example #22
0
// 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
}
Example #23
0
// 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
}
Example #24
0
// 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
}
Example #25
0
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
	}
}
Example #26
0
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
}
Example #27
0
// 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
}
Example #28
0
File: token.go Project: jhbsz/cloud
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
}
Example #29
0
File: hub.go Project: jhbsz/cloud
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
}
Example #30
0
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
}