aboutsummaryrefslogtreecommitdiffhomepage
path: root/backend/db
diff options
context:
space:
mode:
Diffstat (limited to 'backend/db')
-rw-r--r--backend/db/feeds.sql.go41
-rw-r--r--backend/db/migrations.go2
-rw-r--r--backend/db/migrations/007_add_feeds_fetch_interval_seconds.sql1
-rw-r--r--backend/db/models.go13
-rw-r--r--backend/db/queries/feeds.sql16
-rw-r--r--backend/db/schema.sql13
6 files changed, 59 insertions, 27 deletions
diff --git a/backend/db/feeds.sql.go b/backend/db/feeds.sql.go
index 0226a7d..9fcc43e 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, user_id)
VALUES (?, ?, ?, ?)
-RETURNING id, url, title, fetched_at, is_subscribed, user_id
+RETURNING id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
`
type CreateFeedParams struct {
@@ -37,6 +37,7 @@ func (q *Queries) CreateFeed(ctx context.Context, arg CreateFeedParams) (Feed, e
&i.FetchedAt,
&i.IsSubscribed,
&i.UserID,
+ &i.FetchIntervalSeconds,
)
return i, err
}
@@ -52,7 +53,7 @@ func (q *Queries) DeleteFeed(ctx context.Context, id int64) error {
}
const getFeed = `-- name: GetFeed :one
-SELECT id, url, title, fetched_at, is_subscribed, user_id
+SELECT id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
FROM feeds
WHERE id = ?
`
@@ -67,12 +68,13 @@ func (q *Queries) GetFeed(ctx context.Context, id int64) (Feed, error) {
&i.FetchedAt,
&i.IsSubscribed,
&i.UserID,
+ &i.FetchIntervalSeconds,
)
return i, err
}
const getFeedByURL = `-- name: GetFeedByURL :one
-SELECT id, url, title, fetched_at, is_subscribed, user_id
+SELECT id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
FROM feeds
WHERE url = ? AND user_id = ?
`
@@ -92,6 +94,7 @@ func (q *Queries) GetFeedByURL(ctx context.Context, arg GetFeedByURLParams) (Fee
&i.FetchedAt,
&i.IsSubscribed,
&i.UserID,
+ &i.FetchIntervalSeconds,
)
return i, err
}
@@ -133,7 +136,7 @@ func (q *Queries) GetFeedUnreadCounts(ctx context.Context, userID int64) ([]GetF
}
const getFeeds = `-- name: GetFeeds :many
-SELECT id, url, title, fetched_at, is_subscribed, user_id
+SELECT id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
FROM feeds
WHERE is_subscribed = 1 AND user_id = ?
ORDER BY id
@@ -155,6 +158,7 @@ func (q *Queries) GetFeeds(ctx context.Context, userID int64) ([]Feed, error) {
&i.FetchedAt,
&i.IsSubscribed,
&i.UserID,
+ &i.FetchIntervalSeconds,
); err != nil {
return nil, err
}
@@ -170,16 +174,18 @@ func (q *Queries) GetFeeds(ctx context.Context, userID int64) ([]Feed, error) {
}
const getFeedsToFetch = `-- name: GetFeedsToFetch :many
-SELECT id, url, fetched_at, user_id
+SELECT id, url, fetched_at, user_id, fetch_interval_seconds
FROM feeds
WHERE is_subscribed = 1
+ AND datetime(fetched_at, '+' || fetch_interval_seconds || ' seconds') <= datetime('now')
`
type GetFeedsToFetchRow struct {
- ID int64
- Url string
- FetchedAt string
- UserID int64
+ ID int64
+ Url string
+ FetchedAt string
+ UserID int64
+ FetchIntervalSeconds int64
}
func (q *Queries) GetFeedsToFetch(ctx context.Context) ([]GetFeedsToFetchRow, error) {
@@ -196,6 +202,7 @@ func (q *Queries) GetFeedsToFetch(ctx context.Context) ([]GetFeedsToFetchRow, er
&i.Url,
&i.FetchedAt,
&i.UserID,
+ &i.FetchIntervalSeconds,
); err != nil {
return nil, err
}
@@ -221,6 +228,22 @@ func (q *Queries) UnsubscribeFeed(ctx context.Context, id int64) error {
return err
}
+const updateFeedFetchInterval = `-- name: UpdateFeedFetchInterval :exec
+UPDATE feeds
+SET fetch_interval_seconds = ?
+WHERE id = ?
+`
+
+type UpdateFeedFetchIntervalParams struct {
+ FetchIntervalSeconds int64
+ ID int64
+}
+
+func (q *Queries) UpdateFeedFetchInterval(ctx context.Context, arg UpdateFeedFetchIntervalParams) error {
+ _, err := q.db.ExecContext(ctx, updateFeedFetchInterval, arg.FetchIntervalSeconds, arg.ID)
+ return err
+}
+
const updateFeedMetadata = `-- name: UpdateFeedMetadata :exec
UPDATE feeds
SET title = ?, fetched_at = ?
diff --git a/backend/db/migrations.go b/backend/db/migrations.go
index 4ccfba9..28a608f 100644
--- a/backend/db/migrations.go
+++ b/backend/db/migrations.go
@@ -14,7 +14,7 @@ import (
//go:embed migrations/*.sql
var migrationsFS embed.FS
-const EXPECTED_SCHEMA_VERSION = 4
+const EXPECTED_SCHEMA_VERSION = 7
type Migration struct {
Version int
diff --git a/backend/db/migrations/007_add_feeds_fetch_interval_seconds.sql b/backend/db/migrations/007_add_feeds_fetch_interval_seconds.sql
new file mode 100644
index 0000000..102a555
--- /dev/null
+++ b/backend/db/migrations/007_add_feeds_fetch_interval_seconds.sql
@@ -0,0 +1 @@
+ALTER TABLE feeds ADD COLUMN fetch_interval_seconds INTEGER NOT NULL DEFAULT 3600;
diff --git a/backend/db/models.go b/backend/db/models.go
index e397102..342d6a0 100644
--- a/backend/db/models.go
+++ b/backend/db/models.go
@@ -14,12 +14,13 @@ type Article struct {
}
type Feed struct {
- ID int64
- Url string
- Title string
- FetchedAt string
- IsSubscribed int64
- UserID int64
+ ID int64
+ Url string
+ Title string
+ FetchedAt string
+ IsSubscribed int64
+ UserID int64
+ FetchIntervalSeconds int64
}
type User struct {
diff --git a/backend/db/queries/feeds.sql b/backend/db/queries/feeds.sql
index 094a0f8..678f7cc 100644
--- a/backend/db/queries/feeds.sql
+++ b/backend/db/queries/feeds.sql
@@ -1,10 +1,10 @@
-- name: GetFeed :one
-SELECT id, url, title, fetched_at, is_subscribed, user_id
+SELECT id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
FROM feeds
WHERE id = ?;
-- name: GetFeeds :many
-SELECT id, url, title, fetched_at, is_subscribed, user_id
+SELECT id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
FROM feeds
WHERE is_subscribed = 1 AND user_id = ?
ORDER BY id;
@@ -24,14 +24,20 @@ DELETE FROM feeds
WHERE id = ?;
-- name: GetFeedByURL :one
-SELECT id, url, title, fetched_at, is_subscribed, user_id
+SELECT id, url, title, fetched_at, is_subscribed, user_id, fetch_interval_seconds
FROM feeds
WHERE url = ? AND user_id = ?;
-- name: GetFeedsToFetch :many
-SELECT id, url, fetched_at, user_id
+SELECT id, url, fetched_at, user_id, fetch_interval_seconds
FROM feeds
-WHERE is_subscribed = 1;
+WHERE is_subscribed = 1
+ AND datetime(fetched_at, '+' || fetch_interval_seconds || ' seconds') <= datetime('now');
+
+-- name: UpdateFeedFetchInterval :exec
+UPDATE feeds
+SET fetch_interval_seconds = ?
+WHERE id = ?;
-- name: UnsubscribeFeed :exec
UPDATE feeds
diff --git a/backend/db/schema.sql b/backend/db/schema.sql
index 2596c84..85f39d1 100644
--- a/backend/db/schema.sql
+++ b/backend/db/schema.sql
@@ -8,12 +8,13 @@ CREATE TABLE IF NOT EXISTS users (
-- 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,
- is_subscribed INTEGER NOT NULL DEFAULT 1,
- user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE
+ 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,
+ user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
+ fetch_interval_seconds INTEGER NOT NULL DEFAULT 3600
);
-- Articles
generated by cgit v1.3.1 (git 2.54.0) at 2026-06-01 19:39:04 +0000