feat(backend): introduce sqlc

2 files changed, 105 insertions, 0 deletions

diff --git a/backend/db/queries/articles.sql b/backend/db/queries/articles.sql
new file mode 100644
index 0000000..3f1590a
--- /dev/null
+++ b/backend/db/queries/articles.sql
@@ -0,0 +1,73 @@
+-- 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
+FROM articles AS a
+INNER JOIN feeds AS f ON a.feed_id = f.id
+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
+FROM articles AS a
+INNER JOIN feeds AS f ON a.feed_id = f.id
+WHERE a.is_read = 0
+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
+FROM articles AS a
+INNER JOIN feeds AS f ON a.feed_id = f.id
+WHERE a.is_read = 1
+ORDER BY a.id DESC
+LIMIT 100;
+
+-- name: GetArticlesByFeed :many
+SELECT id, feed_id, guid, title, url, is_read
+FROM articles
+WHERE feed_id = ?
+ORDER BY id DESC;
+
+-- name: GetArticleGUIDsByFeed :many
+SELECT guid
+FROM articles
+WHERE feed_id = ?;
+
+-- name: CreateArticle :one
+INSERT INTO articles (feed_id, guid, title, url, is_read)
+VALUES (?, ?, ?, ?, ?)
+RETURNING *;
+
+-- name: UpdateArticle :exec
+UPDATE articles
+SET title = ?, url = ?
+WHERE feed_id = ? AND guid = ?;
+
+-- name: UpdateArticleReadStatus :exec
+UPDATE articles
+SET is_read = ?
+WHERE id = ?;
+
+-- name: MarkFeedArticlesRead :exec
+UPDATE articles
+SET is_read = 1
+WHERE feed_id = ?;
+
+-- name: MarkFeedArticlesUnread :exec
+UPDATE articles
+SET is_read = 0
+WHERE feed_id = ?;
+
+-- name: DeleteArticlesByFeed :exec
+DELETE FROM articles
+WHERE feed_id = ?;
+
+-- name: CheckArticleExists :one
+SELECT EXISTS(
+    SELECT 1 FROM articles
+    WHERE feed_id = ? AND guid = ?
+) as article_exists;
diff --git a/backend/db/queries/feeds.sql b/backend/db/queries/feeds.sql
new file mode 100644
index 0000000..6d4d172
--- /dev/null
+++ b/backend/db/queries/feeds.sql
@@ -0,0 +1,32 @@
+-- name: GetFeed :one
+SELECT id, url, title, fetched_at
+FROM feeds
+WHERE id = ?;
+
+-- name: GetFeeds :many
+SELECT id, url, title, fetched_at
+FROM feeds
+ORDER BY id;
+
+-- name: CreateFeed :one
+INSERT INTO feeds (url, title, fetched_at)
+VALUES (?, ?, ?)
+RETURNING *;
+
+-- name: UpdateFeedMetadata :exec
+UPDATE feeds
+SET title = ?, fetched_at = ?
+WHERE id = ?;
+
+-- name: DeleteFeed :exec
+DELETE FROM feeds
+WHERE id = ?;
+
+-- name: GetFeedByURL :one
+SELECT id, url, title, fetched_at
+FROM feeds
+WHERE url = ?;
+
+-- name: GetFeedsToFetch :many
+SELECT id, url, fetched_at
+FROM feeds;