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 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 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 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 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 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 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 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 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 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 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 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
}