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
}
