summaryrefslogtreecommitdiffhomepage
path: root/backend/db
diff options
context:
space:
mode:
Diffstat (limited to 'backend/db')
-rw-r--r--backend/db/articles.sql.go70
-rw-r--r--backend/db/feeds.sql.go25
-rw-r--r--backend/db/models.go9
-rw-r--r--backend/db/queries/articles.sql10
-rw-r--r--backend/db/queries/feeds.sql15
-rw-r--r--backend/db/schema.sql9
6 files changed, 85 insertions, 53 deletions
diff --git a/backend/db/articles.sql.go b/backend/db/articles.sql.go
index 9e60cb4..7492598 100644
--- a/backend/db/articles.sql.go
+++ b/backend/db/articles.sql.go
@@ -75,22 +75,23 @@ func (q *Queries) DeleteArticlesByFeed(ctx context.Context, feedID int64) error
const getArticle = `-- name: GetArticle :one
SELECT
a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
- f.id as feed_id_2, f.url as feed_url, f.title as feed_title
+ f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
FROM articles AS a
INNER JOIN feeds AS f ON a.feed_id = f.id
WHERE a.id = ?
`
type GetArticleRow struct {
- ID int64
- FeedID int64
- Guid string
- Title string
- Url string
- IsRead int64
- FeedID2 int64
- FeedUrl string
- FeedTitle string
+ ID int64
+ FeedID int64
+ Guid string
+ Title string
+ Url string
+ IsRead int64
+ FeedID2 int64
+ FeedUrl string
+ FeedTitle string
+ FeedIsSubscribed int64
}
func (q *Queries) GetArticle(ctx context.Context, id int64) (GetArticleRow, error) {
@@ -106,6 +107,7 @@ func (q *Queries) GetArticle(ctx context.Context, id int64) (GetArticleRow, erro
&i.FeedID2,
&i.FeedUrl,
&i.FeedTitle,
+ &i.FeedIsSubscribed,
)
return i, err
}
@@ -179,24 +181,25 @@ func (q *Queries) GetArticlesByFeed(ctx context.Context, feedID int64) ([]Articl
const getReadArticles = `-- name: GetReadArticles :many
SELECT
a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
- f.id as feed_id_2, f.url as feed_url, f.title as feed_title
+ f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
FROM articles AS a
INNER JOIN feeds AS f ON a.feed_id = f.id
-WHERE a.is_read = 1
+WHERE a.is_read = 1 AND f.is_subscribed = 1
ORDER BY a.id DESC
LIMIT 100
`
type GetReadArticlesRow struct {
- ID int64
- FeedID int64
- Guid string
- Title string
- Url string
- IsRead int64
- FeedID2 int64
- FeedUrl string
- FeedTitle string
+ ID int64
+ FeedID int64
+ Guid string
+ Title string
+ Url string
+ IsRead int64
+ FeedID2 int64
+ FeedUrl string
+ FeedTitle string
+ FeedIsSubscribed int64
}
func (q *Queries) GetReadArticles(ctx context.Context) ([]GetReadArticlesRow, error) {
@@ -218,6 +221,7 @@ func (q *Queries) GetReadArticles(ctx context.Context) ([]GetReadArticlesRow, er
&i.FeedID2,
&i.FeedUrl,
&i.FeedTitle,
+ &i.FeedIsSubscribed,
); err != nil {
return nil, err
}
@@ -235,24 +239,25 @@ func (q *Queries) GetReadArticles(ctx context.Context) ([]GetReadArticlesRow, er
const getUnreadArticles = `-- name: GetUnreadArticles :many
SELECT
a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
- f.id as feed_id_2, f.url as feed_url, f.title as feed_title
+ f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
FROM articles AS a
INNER JOIN feeds AS f ON a.feed_id = f.id
-WHERE a.is_read = 0
+WHERE a.is_read = 0 AND f.is_subscribed = 1
ORDER BY a.id DESC
LIMIT 100
`
type GetUnreadArticlesRow struct {
- ID int64
- FeedID int64
- Guid string
- Title string
- Url string
- IsRead int64
- FeedID2 int64
- FeedUrl string
- FeedTitle string
+ ID int64
+ FeedID int64
+ Guid string
+ Title string
+ Url string
+ IsRead int64
+ FeedID2 int64
+ FeedUrl string
+ FeedTitle string
+ FeedIsSubscribed int64
}
func (q *Queries) GetUnreadArticles(ctx context.Context) ([]GetUnreadArticlesRow, error) {
@@ -274,6 +279,7 @@ func (q *Queries) GetUnreadArticles(ctx context.Context) ([]GetUnreadArticlesRow
&i.FeedID2,
&i.FeedUrl,
&i.FeedTitle,
+ &i.FeedIsSubscribed,
); err != nil {
return nil, err
}
diff --git a/backend/db/feeds.sql.go b/backend/db/feeds.sql.go
index 4db84af..29b26ca 100644
--- a/backend/db/feeds.sql.go
+++ b/backend/db/feeds.sql.go
@@ -12,7 +12,7 @@ import (
const createFeed = `-- name: CreateFeed :one
INSERT INTO feeds (url, title, fetched_at)
VALUES (?, ?, ?)
-RETURNING id, url, title, fetched_at
+RETURNING id, url, title, fetched_at, is_subscribed
`
type CreateFeedParams struct {
@@ -29,6 +29,7 @@ func (q *Queries) CreateFeed(ctx context.Context, arg CreateFeedParams) (Feed, e
&i.Url,
&i.Title,
&i.FetchedAt,
+ &i.IsSubscribed,
)
return i, err
}
@@ -44,7 +45,7 @@ func (q *Queries) DeleteFeed(ctx context.Context, id int64) error {
}
const getFeed = `-- name: GetFeed :one
-SELECT id, url, title, fetched_at
+SELECT id, url, title, fetched_at, is_subscribed
FROM feeds
WHERE id = ?
`
@@ -57,12 +58,13 @@ func (q *Queries) GetFeed(ctx context.Context, id int64) (Feed, error) {
&i.Url,
&i.Title,
&i.FetchedAt,
+ &i.IsSubscribed,
)
return i, err
}
const getFeedByURL = `-- name: GetFeedByURL :one
-SELECT id, url, title, fetched_at
+SELECT id, url, title, fetched_at, is_subscribed
FROM feeds
WHERE url = ?
`
@@ -75,13 +77,15 @@ func (q *Queries) GetFeedByURL(ctx context.Context, url string) (Feed, error) {
&i.Url,
&i.Title,
&i.FetchedAt,
+ &i.IsSubscribed,
)
return i, err
}
const getFeeds = `-- name: GetFeeds :many
-SELECT id, url, title, fetched_at
+SELECT id, url, title, fetched_at, is_subscribed
FROM feeds
+WHERE is_subscribed = 1
ORDER BY id
`
@@ -99,6 +103,7 @@ func (q *Queries) GetFeeds(ctx context.Context) ([]Feed, error) {
&i.Url,
&i.Title,
&i.FetchedAt,
+ &i.IsSubscribed,
); err != nil {
return nil, err
}
@@ -116,6 +121,7 @@ func (q *Queries) GetFeeds(ctx context.Context) ([]Feed, error) {
const getFeedsToFetch = `-- name: GetFeedsToFetch :many
SELECT id, url, fetched_at
FROM feeds
+WHERE is_subscribed = 1
`
type GetFeedsToFetchRow struct {
@@ -147,6 +153,17 @@ func (q *Queries) GetFeedsToFetch(ctx context.Context) ([]GetFeedsToFetchRow, er
return items, nil
}
+const unsubscribeFeed = `-- name: UnsubscribeFeed :exec
+UPDATE feeds
+SET is_subscribed = 0
+WHERE id = ?
+`
+
+func (q *Queries) UnsubscribeFeed(ctx context.Context, id int64) error {
+ _, err := q.db.ExecContext(ctx, unsubscribeFeed, id)
+ return err
+}
+
const updateFeedMetadata = `-- name: UpdateFeedMetadata :exec
UPDATE feeds
SET title = ?, fetched_at = ?
diff --git a/backend/db/models.go b/backend/db/models.go
index 2f36cb4..94ab9c0 100644
--- a/backend/db/models.go
+++ b/backend/db/models.go
@@ -14,8 +14,9 @@ type Article struct {
}
type Feed struct {
- ID int64
- Url string
- Title string
- FetchedAt string
+ ID int64
+ Url string
+ Title string
+ FetchedAt string
+ IsSubscribed int64
}
diff --git a/backend/db/queries/articles.sql b/backend/db/queries/articles.sql
index 3f1590a..c1feaae 100644
--- a/backend/db/queries/articles.sql
+++ b/backend/db/queries/articles.sql
@@ -1,7 +1,7 @@
-- name: GetArticle :one
SELECT
a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
- f.id as feed_id_2, f.url as feed_url, f.title as feed_title
+ f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
FROM articles AS a
INNER JOIN feeds AS f ON a.feed_id = f.id
WHERE a.id = ?;
@@ -9,20 +9,20 @@ WHERE a.id = ?;
-- name: GetUnreadArticles :many
SELECT
a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
- f.id as feed_id_2, f.url as feed_url, f.title as feed_title
+ f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
FROM articles AS a
INNER JOIN feeds AS f ON a.feed_id = f.id
-WHERE a.is_read = 0
+WHERE a.is_read = 0 AND f.is_subscribed = 1
ORDER BY a.id DESC
LIMIT 100;
-- name: GetReadArticles :many
SELECT
a.id, a.feed_id, a.guid, a.title, a.url, a.is_read,
- f.id as feed_id_2, f.url as feed_url, f.title as feed_title
+ f.id as feed_id_2, f.url as feed_url, f.title as feed_title, f.is_subscribed as feed_is_subscribed
FROM articles AS a
INNER JOIN feeds AS f ON a.feed_id = f.id
-WHERE a.is_read = 1
+WHERE a.is_read = 1 AND f.is_subscribed = 1
ORDER BY a.id DESC
LIMIT 100;
diff --git a/backend/db/queries/feeds.sql b/backend/db/queries/feeds.sql
index 6d4d172..8445532 100644
--- a/backend/db/queries/feeds.sql
+++ b/backend/db/queries/feeds.sql
@@ -1,11 +1,12 @@
-- name: GetFeed :one
-SELECT id, url, title, fetched_at
+SELECT id, url, title, fetched_at, is_subscribed
FROM feeds
WHERE id = ?;
-- name: GetFeeds :many
-SELECT id, url, title, fetched_at
+SELECT id, url, title, fetched_at, is_subscribed
FROM feeds
+WHERE is_subscribed = 1
ORDER BY id;
-- name: CreateFeed :one
@@ -23,10 +24,16 @@ DELETE FROM feeds
WHERE id = ?;
-- name: GetFeedByURL :one
-SELECT id, url, title, fetched_at
+SELECT id, url, title, fetched_at, is_subscribed
FROM feeds
WHERE url = ?;
-- name: GetFeedsToFetch :many
SELECT id, url, fetched_at
-FROM feeds;
+FROM feeds
+WHERE is_subscribed = 1;
+
+-- name: UnsubscribeFeed :exec
+UPDATE feeds
+SET is_subscribed = 0
+WHERE id = ?;
diff --git a/backend/db/schema.sql b/backend/db/schema.sql
index 5c2bf48..eb40dea 100644
--- a/backend/db/schema.sql
+++ b/backend/db/schema.sql
@@ -1,9 +1,10 @@
-- Feeds
CREATE TABLE IF NOT EXISTS feeds (
- id INTEGER PRIMARY KEY AUTOINCREMENT,
- url TEXT NOT NULL,
- title TEXT NOT NULL,
- fetched_at TEXT NOT NULL
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ url TEXT NOT NULL,
+ title TEXT NOT NULL,
+ fetched_at TEXT NOT NULL,
+ is_subscribed INTEGER NOT NULL DEFAULT 1
);
-- Articles
generated by cgit v1.2.3-70-g09d2 (git 2.46.0) at 2025-12-01 03:51:53 +0000