smutok/internal/store/sqlite_articles.go(view raw)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
package store
import (
"context"
"fmt"
"strings"
)
func (s *Sqlite) UpsertArticle(
ctx context.Context,
timestampUsec, feedID, title, content, author, href string,
publishedAt int,
) error {
tx, err := s.db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
if _, err = tx.ExecContext(ctx,
`insert or ignore into articles (id, feed_id, title, content, author, href, published_at) values (?, ?, ?, ?, ?, ?, ?)`,
timestampUsec, feedID, title, content, author, href, publishedAt); err != nil {
return err
}
if _, err = tx.ExecContext(ctx, `insert or ignore into article_statuses (article_id) values (?)`, timestampUsec); err != nil {
return err
}
return tx.Commit()
}
func (s *Sqlite) SyncReadStatus(ctx context.Context, ids []string) error {
placeholders, args := buildPlaceholdersAndArgs(ids)
query := fmt.Sprintf(`--sql
update article_statuses
set is_read = case when article_id in (%s)
then false
else true
end`, placeholders)
_, err := s.db.ExecContext(ctx, query, args...)
return err
}
func (s *Sqlite) SyncStarredStatus(ctx context.Context, ids []string) error {
placeholders, args := buildPlaceholdersAndArgs(ids)
query := fmt.Sprintf(`--sql
update article_statuses
set is_starred = case when article_id in (%s)
then true
else false
end`, placeholders)
_, err := s.db.ExecContext(ctx, query, args...)
return err
}
type Article struct {
ID string
Title string
Href string
Content string
Author string
IsRead bool
IsStarred bool
FeedID string
FeedTitle string
PublishedAt int64
}
func (s *Sqlite) GetArticles(ctx context.Context) ([]Article, error) {
query := `--sql
select a.id, a.title, a.href, a.content, a.author, s.is_read, s.is_starred, a.feed_id, f.title feed_name, a.published_at
from articles a
join article_statuses s on a.id = s.article_id
join feeds f on f.id = a.feed_id
where s.is_read = false
order by a.published_at desc`
rows, err := s.db.QueryContext(ctx, query)
if err != nil {
return nil, err
}
defer rows.Close()
var res []Article
for rows.Next() {
var a Article
if serr := rows.Scan(&a.ID, &a.Title, &a.Href, &a.Content, &a.Author, &a.IsRead, &a.IsStarred, &a.FeedID, &a.FeedTitle, &a.PublishedAt); serr != nil {
return res, serr
}
res = append(res, a)
}
if err = rows.Err(); err != nil {
return res, err
}
return res, nil
}
func buildPlaceholdersAndArgs(in []string, prefixArgs ...any) (placeholders string, args []any) {
placeholders = strings.Repeat("?,", len(in))
placeholders = placeholders[:len(placeholders)-1] // trim trailing comma
args = make([]any, len(prefixArgs)+len(in))
copy(args, prefixArgs)
for i, v := range in {
args[len(prefixArgs)+i] = v
}
return placeholders, args
}
|