aboutsummaryrefslogtreecommitdiffhomepage
path: root/backend/db
diff options
context:
space:
mode:
Diffstat (limited to 'backend/db')
-rw-r--r--backend/db/feeds.sql.go23
-rw-r--r--backend/db/migrations.go2
-rw-r--r--backend/db/migrations/003_add_users_table.sql15
-rw-r--r--backend/db/models.go12
-rw-r--r--backend/db/queries/feeds.sql8
-rw-r--r--backend/db/schema.sql13
6 files changed, 61 insertions, 12 deletions
diff --git a/backend/db/feeds.sql.go b/backend/db/feeds.sql.go
index 29b26ca..9d6c5c1 100644
--- a/backend/db/feeds.sql.go
+++ b/backend/db/feeds.sql.go
@@ -7,12 +7,13 @@ package db
import (
"context"
+ "database/sql"
)
const createFeed = `-- name: CreateFeed :one
INSERT INTO feeds (url, title, fetched_at)
VALUES (?, ?, ?)
-RETURNING id, url, title, fetched_at, is_subscribed
+RETURNING id, url, title, fetched_at, is_subscribed, user_id
`
type CreateFeedParams struct {
@@ -30,6 +31,7 @@ func (q *Queries) CreateFeed(ctx context.Context, arg CreateFeedParams) (Feed, e
&i.Title,
&i.FetchedAt,
&i.IsSubscribed,
+ &i.UserID,
)
return i, err
}
@@ -45,7 +47,7 @@ func (q *Queries) DeleteFeed(ctx context.Context, id int64) error {
}
const getFeed = `-- name: GetFeed :one
-SELECT id, url, title, fetched_at, is_subscribed
+SELECT id, url, title, fetched_at, is_subscribed, user_id
FROM feeds
WHERE id = ?
`
@@ -59,12 +61,13 @@ func (q *Queries) GetFeed(ctx context.Context, id int64) (Feed, error) {
&i.Title,
&i.FetchedAt,
&i.IsSubscribed,
+ &i.UserID,
)
return i, err
}
const getFeedByURL = `-- name: GetFeedByURL :one
-SELECT id, url, title, fetched_at, is_subscribed
+SELECT id, url, title, fetched_at, is_subscribed, user_id
FROM feeds
WHERE url = ?
`
@@ -78,12 +81,13 @@ func (q *Queries) GetFeedByURL(ctx context.Context, url string) (Feed, error) {
&i.Title,
&i.FetchedAt,
&i.IsSubscribed,
+ &i.UserID,
)
return i, err
}
const getFeeds = `-- name: GetFeeds :many
-SELECT id, url, title, fetched_at, is_subscribed
+SELECT id, url, title, fetched_at, is_subscribed, user_id
FROM feeds
WHERE is_subscribed = 1
ORDER BY id
@@ -104,6 +108,7 @@ func (q *Queries) GetFeeds(ctx context.Context) ([]Feed, error) {
&i.Title,
&i.FetchedAt,
&i.IsSubscribed,
+ &i.UserID,
); err != nil {
return nil, err
}
@@ -119,7 +124,7 @@ func (q *Queries) GetFeeds(ctx context.Context) ([]Feed, error) {
}
const getFeedsToFetch = `-- name: GetFeedsToFetch :many
-SELECT id, url, fetched_at
+SELECT id, url, fetched_at, user_id
FROM feeds
WHERE is_subscribed = 1
`
@@ -128,6 +133,7 @@ type GetFeedsToFetchRow struct {
ID int64
Url string
FetchedAt string
+ UserID sql.NullInt64
}
func (q *Queries) GetFeedsToFetch(ctx context.Context) ([]GetFeedsToFetchRow, error) {
@@ -139,7 +145,12 @@ func (q *Queries) GetFeedsToFetch(ctx context.Context) ([]GetFeedsToFetchRow, er
items := []GetFeedsToFetchRow{}
for rows.Next() {
var i GetFeedsToFetchRow
- if err := rows.Scan(&i.ID, &i.Url, &i.FetchedAt); err != nil {
+ if err := rows.Scan(
+ &i.ID,
+ &i.Url,
+ &i.FetchedAt,
+ &i.UserID,
+ ); err != nil {
return nil, err
}
items = append(items, i)
diff --git a/backend/db/migrations.go b/backend/db/migrations.go
index 9a146a1..41c837c 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 = 2
+const EXPECTED_SCHEMA_VERSION = 3
type Migration struct {
Version int
diff --git a/backend/db/migrations/003_add_users_table.sql b/backend/db/migrations/003_add_users_table.sql
new file mode 100644
index 0000000..4cf963b
--- /dev/null
+++ b/backend/db/migrations/003_add_users_table.sql
@@ -0,0 +1,15 @@
+-- Add users table and user_id column to feeds table.
+
+-- Users
+CREATE TABLE IF NOT EXISTS users (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ username TEXT NOT NULL UNIQUE,
+ password_hash TEXT NOT NULL,
+ created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+
+-- Add user_id to feeds table
+ALTER TABLE feeds ADD COLUMN user_id INTEGER REFERENCES users(id) ON DELETE CASCADE;
+
+-- Index for feeds.user_id
+CREATE INDEX IF NOT EXISTS idx_feeds_user_id ON feeds(user_id);
diff --git a/backend/db/models.go b/backend/db/models.go
index 94ab9c0..81fcd0c 100644
--- a/backend/db/models.go
+++ b/backend/db/models.go
@@ -4,6 +4,10 @@
package db
+import (
+ "database/sql"
+)
+
type Article struct {
ID int64
FeedID int64
@@ -19,4 +23,12 @@ type Feed struct {
Title string
FetchedAt string
IsSubscribed int64
+ UserID sql.NullInt64
+}
+
+type User struct {
+ ID int64
+ Username string
+ PasswordHash string
+ CreatedAt string
}
diff --git a/backend/db/queries/feeds.sql b/backend/db/queries/feeds.sql
index 8445532..3b5b882 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
+SELECT id, url, title, fetched_at, is_subscribed, user_id
FROM feeds
WHERE id = ?;
-- name: GetFeeds :many
-SELECT id, url, title, fetched_at, is_subscribed
+SELECT id, url, title, fetched_at, is_subscribed, user_id
FROM feeds
WHERE is_subscribed = 1
ORDER BY id;
@@ -24,12 +24,12 @@ DELETE FROM feeds
WHERE id = ?;
-- name: GetFeedByURL :one
-SELECT id, url, title, fetched_at, is_subscribed
+SELECT id, url, title, fetched_at, is_subscribed, user_id
FROM feeds
WHERE url = ?;
-- name: GetFeedsToFetch :many
-SELECT id, url, fetched_at
+SELECT id, url, fetched_at, user_id
FROM feeds
WHERE is_subscribed = 1;
diff --git a/backend/db/schema.sql b/backend/db/schema.sql
index eb40dea..e1260b0 100644
--- a/backend/db/schema.sql
+++ b/backend/db/schema.sql
@@ -1,10 +1,19 @@
+-- Users
+CREATE TABLE IF NOT EXISTS users (
+ id INTEGER PRIMARY KEY AUTOINCREMENT,
+ username TEXT NOT NULL UNIQUE,
+ password_hash TEXT NOT NULL,
+ created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
+);
+
-- 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
+ is_subscribed INTEGER NOT NULL DEFAULT 1,
+ user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);
-- Articles
@@ -24,3 +33,5 @@ CREATE INDEX IF NOT EXISTS idx_articles_feed_id ON articles(feed_id);
CREATE INDEX IF NOT EXISTS idx_articles_feed_guid ON articles(feed_id, guid);
CREATE INDEX IF NOT EXISTS idx_articles_is_read ON articles(is_read);
+
+CREATE INDEX IF NOT EXISTS idx_feeds_user_id ON feeds(user_id);
generated by cgit v1.2.3-70-g09d2 (git 2.46.0) at 2025-11-30 22:41:53 +0000