func Subscribe(g pg.Getter, subscriber int64, link string) (*Subscription, error) {
now := time.Now()
var sub Subscription
err := g.Get(&sub.Source, `
INSERT INTO sources (link, created)
VALUES ($1, $2)
ON CONFLICT DO NOTHING
RETURNING *
`, link, now)
if err != nil {
return nil, pg.CastErr(err)
}
err = g.Get(&sub, `
INSERT INTO subscriptions (subscriber_id, source_id, created)
VALUES ($1, $2, $3)
ON CONFLICT DO NOTHING
RETURNING *
`, subscriber, sub.Source.SourceID, now)
if err != nil {
return nil, pg.CastErr(err)
}
return &sub, err
}
func AccountByLogin(g pg.Getter, login, provider string) (*Account, error) {
var a Account
err := g.Get(&a, `
SELECT * FROM accounts WHERE login = $1 AND provider = $2
LIMIT 1
`, login, provider)
return &a, pg.CastErr(err)
}
func AccountByID(g pg.Getter, accountID int) (*Account, error) {
var a Account
err := g.Get(&a, `
SELECT * FROM accounts WHERE account_id = $1
LIMIT 1
`, accountID)
return &a, pg.CastErr(err)
}
func PasteByID(g pg.Getter, pid int64) (*Paste, error) {
var p Paste
err := g.Get(&p, `
SELECT * FROM pastes
WHERE id = $1
LIMIT 1
`, pid)
return &p, pg.CastErr(err)
}
func CreatePaste(g pg.Getter, p Paste) (*Paste, error) {
now := time.Now()
err := g.Get(&p, `
INSERT INTO pastes (content, created, updated)
VALUES ($1, $2, $3)
RETURNING *
`, p.Content, now, now)
return &p, pg.CastErr(err)
}
func UpdateNote(g pg.Getter, note Note) (*Note, error) {
err := g.Get(¬e, `
UPDATE notes
SET content = $1, is_public = $2, expire_at = $3
WHERE note_id = $4
RETURNING *
`, note.Content, note.IsPublic, note.ExpireAt, note.NoteID)
return ¬e, pg.CastErr(err)
}
func NoteByID(g pg.Getter, noteID int) (*Note, error) {
var n Note
err := g.Get(&n, `
SELECT * FROM notes
WHERE note_id = $1 AND (expire_at < $2 OR expire_at IS NULL)
LIMIT 1
`, noteID, time.Now())
return &n, pg.CastErr(err)
}
func CreateNote(g pg.Getter, n Note) (*Note, error) {
n.CreatedAt = time.Now()
err := g.Get(&n.NoteID, `
INSERT INTO notes (owner_id, content, expire_at, created_at, is_public)
VALUES ($1, $2, $3, $4, $5)
RETURNING note_id
`, n.OwnerID, n.Content, n.ExpireAt, time.Now(), n.IsPublic)
return &n, pg.CastErr(err)
}
func UpdatePaste(g pg.Getter, p Paste) (*Paste, error) {
now := time.Now()
err := g.Get(&p, `
UPDATE pastes
SET content = $1, updated = $2
WHERE id = $3
RETURNING *
`, p.Content, now, p.ID)
return &p, pg.CastErr(err)
}
func AccessToken(g pg.Getter, accountID int) (string, error) {
var token string
err := g.Get(&token, `
SELECT access_token FROM sessions
WHERE account = $1
ORDER BY created DESC
LIMIT 1
`, accountID)
return token, pg.CastErr(err)
}
func TopicByID(g pg.Getter, topicID int64) (*Topic, error) {
var t Topic
err := g.Get(&t, `
SELECT * FROM topics WHERE topic_id = $1 LIMIT 1
`, topicID)
if err != nil {
return &t, pg.CastErr(err)
}
return &t, nil
}
func NotesByOwner(s pg.Selector, ownerID, limit, offset int) ([]*Note, error) {
var notes []*Note
err := s.Select(¬es, `
SELECT * FROM notes
WHERE owner_id = $1 AND (expire_at < $2 OR expire_at IS NULL)
ORDER BY created_at DESC
LIMIT $3 OFFSET $4
`, ownerID, time.Now(), limit, offset)
return notes, pg.CastErr(err)
}
func SessionAccount(g pg.Getter, key string) (*AccountWithScopes, error) {
var a AccountWithScopes
err := g.Get(&a, `
SELECT a.account_id, a.login, a.created, s.scopes
FROM accounts a INNER JOIN sessions s ON s.account = a.account_id
WHERE s.key = $1
LIMIT 1
`, key)
return &a, pg.CastErr(err)
}
func IsNoteOwner(g pg.Getter, noteID, ownerID int) (bool, error) {
var isOwner bool
err := g.Get(&isOwner, `
SELECT COALESCE(sub.is_owner, false) FROM (
SELECT true AS is_owner FROM notes
WHERE note_id = $1 AND owner_id = $2
LIMIT 1
) sub
`, noteID, ownerID)
return isOwner, pg.CastErr(err)
}
func Pastes(s pg.Selector, limit, offset int64) ([]*Paste, error) {
var pastes []*Paste
err := s.Select(&pastes, `
SELECT
id, created, updated,
substring(content from 0 for 500) AS content
FROM pastes
ORDER BY updated DESC
LIMIT $1 OFFSET $2
`, limit, offset)
return pastes, pg.CastErr(err)
}
func DeleteNote(e pg.Execer, noteID int) error {
res, err := e.Exec(`
DELETE FROM notes WHERE note_id = $1
`, noteID)
if err != nil {
return pg.CastErr(err)
}
if n, _ := res.RowsAffected(); n != 1 {
return pg.ErrNotFound
}
return nil
}
func Subscriptions(s pg.Selector, subscriber int64, limit, offset int64) ([]*Subscription, error) {
var subs []*Subscription
err := s.Select(&subs, `
SELECT sub.*, src.*
FROM subscriptions sub
INNER JOIN sources src ON sub.source_id = src.source_id
WHERE
sub.subscriber_id = $1
ORDER BY sub.created DESC
LIMIT $2 OFFSET $3
`, subscriber, limit, offset)
return subs, pg.CastErr(err)
}
func Entries(s pg.Selector, subscriber int64, limit, offset int64) ([]*Entry, error) {
var entries []*Entry
err := s.Select(&entries, `
SELECT e.*, src.*
FROM entries e
OUTER JOIN sources src ON src.source_id = e.source_id
OUTER JOIN subscriptions sub ON sub.source_id = src.source_id
WHERE
sub.subscriber_id = $1
ORDER BY e.created DESC
LIMIT $2 OFFSET $3
`, subscriber, limit, offset)
return entries, pg.CastErr(err)
}
// Comments return slice of comments that match given query criteria.
func Comments(s pg.Selector, o CommentsOpts) ([]*Comment, error) {
if o.Offset < 0 {
o.Offset = 0
}
q := qb.Q("SELECT * FROM comments").Limit(o.Limit, o.Offset).OrderBy("created DESC")
if o.TopicID != 0 {
q.Where("topic_id = ?", o.TopicID)
}
if o.AuthorID != "" {
q.Where("author_id = ?", o.AuthorID)
}
query, args := q.Build()
query = sqlx.Rebind(sqlx.DOLLAR, query)
var comments []*Comment
if err := s.Select(&comments, query, args...); err != nil {
return nil, pg.CastErr(err)
}
return comments, nil
}
func CreateTopic(g pg.Getter, t Topic) (*Topic, error) {
now := time.Now()
if t.Created.IsZero() {
t.Created = now
}
t.Updated = now
var tid int64
err := g.Get(&tid, `
INSERT INTO topics (author_id, title, tags, created, updated)
VALUES ($1, $2, $3, $4, $5)
RETURNING topic_id
`, t.AuthorID, t.Title, t.Tags, t.Created, t.Updated)
if err != nil {
return nil, pg.CastErr(err)
}
t.TopicID = tid
return &t, nil
}
func CreateComment(g pg.Getter, c Comment) (*Comment, error) {
now := time.Now()
if c.Created.IsZero() {
c.Created = now
}
c.Updated = now
var cid int64
err := g.Get(&cid, `
INSERT INTO comments (topic_id, author_id, content, created, updated)
VALUES ($1, $2, $3, $4, $5)
RETURNING comment_id
`, c.TopicID, c.AuthorID, c.Content, c.Created, c.Updated)
if err != nil {
return nil, pg.CastErr(err)
}
c.CommentID = cid
return &c, nil
}
func CreateSession(
g pg.Getter,
account int,
key string,
accessToken string,
scopes []string,
) (string, error) {
var ok bool
err := g.Get(&ok, `
INSERT INTO sessions (key, account, created, access_token, provider, scopes)
SELECT $1, $2, $3, $4, a.provider, $5
FROM accounts a WHERE a.account_id = $2 LIMIT 1
RETURNING true
`, key, account, time.Now(), accessToken, strings.Join(scopes, " "))
if err != nil {
return "", pg.CastErr(err)
}
if !ok {
return "", pg.ErrNotFound
}
return key, nil
}
// Topics return slice of topics that match given query criteria.
func Topics(s pg.Selector, o TopicsOpts) ([]*TopicWithAuthor, error) {
if o.Offset < 0 {
o.Offset = 0
}
q := qb.Q(`
SELECT t.*, a.*
FROM topics t LEFT JOIN accounts a ON t.author_id = a.account_id
`).Limit(o.Limit, o.Offset).OrderBy("t.created ASC")
if len(o.Tags) > 0 {
// TODO
}
if !o.OlderThan.IsZero() {
q.Where("t.created < ?", o.OlderThan)
}
query, args := q.Build()
query = sqlx.Rebind(sqlx.DOLLAR, query)
var topics []*TopicWithAuthor
if err := s.Select(&topics, query, args...); err != nil {
return nil, pg.CastErr(err)
}
return topics, nil
}
