feat(backend): introduce sqlc

7 files changed, 697 insertions, 0 deletions

diff --git a/backend/db/articles.sql.go b/backend/db/articles.sql.go
new file mode 100644
index 0000000..9e60cb4
--- /dev/null
+++ b/backend/db/articles.sql.go
@@ -0,0 +1,350 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+// source: articles.sql
+
+package db
+
+import (
+	"context"
+)
+
+const checkArticleExists = `-- name: CheckArticleExists :one
+SELECT EXISTS(
+    SELECT 1 FROM articles
+    WHERE feed_id = ? AND guid = ?
+) as article_exists
+`
+
+type CheckArticleExistsParams struct {
+	FeedID int64
+	Guid   string
+}
+
+func (q *Queries) CheckArticleExists(ctx context.Context, arg CheckArticleExistsParams) (int64, error) {
+	row := q.db.QueryRowContext(ctx, checkArticleExists, arg.FeedID, arg.Guid)
+	var article_exists int64
+	err := row.Scan(&article_exists)
+	return article_exists, err
+}
+
+const createArticle = `-- name: CreateArticle :one
+INSERT INTO articles (feed_id, guid, title, url, is_read)
+VALUES (?, ?, ?, ?, ?)
+RETURNING id, feed_id, guid, title, url, is_read
+`
+
+type CreateArticleParams struct {
+	FeedID int64
+	Guid   string
+	Title  string
+	Url    string
+	IsRead int64
+}
+
+func (q *Queries) CreateArticle(ctx context.Context, arg CreateArticleParams) (Article, error) {
+	row := q.db.QueryRowContext(ctx, createArticle,
+		arg.FeedID,
+		arg.Guid,
+		arg.Title,
+		arg.Url,
+		arg.IsRead,
+	)
+	var i Article
+	err := row.Scan(
+		&i.ID,
+		&i.FeedID,
+		&i.Guid,
+		&i.Title,
+		&i.Url,
+		&i.IsRead,
+	)
+	return i, err
+}
+
+const deleteArticlesByFeed = `-- name: DeleteArticlesByFeed :exec
+DELETE FROM articles
+WHERE feed_id = ?
+`
+
+func (q *Queries) DeleteArticlesByFeed(ctx context.Context, feedID int64) error {
+	_, err := q.db.ExecContext(ctx, deleteArticlesByFeed, feedID)
+	return err
+}
+
+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
+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
+}
+
+func (q *Queries) GetArticle(ctx context.Context, id int64) (GetArticleRow, error) {
+	row := q.db.QueryRowContext(ctx, getArticle, id)
+	var i GetArticleRow
+	err := row.Scan(
+		&i.ID,
+		&i.FeedID,
+		&i.Guid,
+		&i.Title,
+		&i.Url,
+		&i.IsRead,
+		&i.FeedID2,
+		&i.FeedUrl,
+		&i.FeedTitle,
+	)
+	return i, err
+}
+
+const getArticleGUIDsByFeed = `-- name: GetArticleGUIDsByFeed :many
+SELECT guid
+FROM articles
+WHERE feed_id = ?
+`
+
+func (q *Queries) GetArticleGUIDsByFeed(ctx context.Context, feedID int64) ([]string, error) {
+	rows, err := q.db.QueryContext(ctx, getArticleGUIDsByFeed, feedID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []string{}
+	for rows.Next() {
+		var guid string
+		if err := rows.Scan(&guid); err != nil {
+			return nil, err
+		}
+		items = append(items, guid)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const getArticlesByFeed = `-- name: GetArticlesByFeed :many
+SELECT id, feed_id, guid, title, url, is_read
+FROM articles
+WHERE feed_id = ?
+ORDER BY id DESC
+`
+
+func (q *Queries) GetArticlesByFeed(ctx context.Context, feedID int64) ([]Article, error) {
+	rows, err := q.db.QueryContext(ctx, getArticlesByFeed, feedID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []Article{}
+	for rows.Next() {
+		var i Article
+		if err := rows.Scan(
+			&i.ID,
+			&i.FeedID,
+			&i.Guid,
+			&i.Title,
+			&i.Url,
+			&i.IsRead,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+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
+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
+`
+
+type GetReadArticlesRow struct {
+	ID        int64
+	FeedID    int64
+	Guid      string
+	Title     string
+	Url       string
+	IsRead    int64
+	FeedID2   int64
+	FeedUrl   string
+	FeedTitle string
+}
+
+func (q *Queries) GetReadArticles(ctx context.Context) ([]GetReadArticlesRow, error) {
+	rows, err := q.db.QueryContext(ctx, getReadArticles)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []GetReadArticlesRow{}
+	for rows.Next() {
+		var i GetReadArticlesRow
+		if err := rows.Scan(
+			&i.ID,
+			&i.FeedID,
+			&i.Guid,
+			&i.Title,
+			&i.Url,
+			&i.IsRead,
+			&i.FeedID2,
+			&i.FeedUrl,
+			&i.FeedTitle,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+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
+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
+`
+
+type GetUnreadArticlesRow struct {
+	ID        int64
+	FeedID    int64
+	Guid      string
+	Title     string
+	Url       string
+	IsRead    int64
+	FeedID2   int64
+	FeedUrl   string
+	FeedTitle string
+}
+
+func (q *Queries) GetUnreadArticles(ctx context.Context) ([]GetUnreadArticlesRow, error) {
+	rows, err := q.db.QueryContext(ctx, getUnreadArticles)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []GetUnreadArticlesRow{}
+	for rows.Next() {
+		var i GetUnreadArticlesRow
+		if err := rows.Scan(
+			&i.ID,
+			&i.FeedID,
+			&i.Guid,
+			&i.Title,
+			&i.Url,
+			&i.IsRead,
+			&i.FeedID2,
+			&i.FeedUrl,
+			&i.FeedTitle,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const markFeedArticlesRead = `-- name: MarkFeedArticlesRead :exec
+UPDATE articles
+SET is_read = 1
+WHERE feed_id = ?
+`
+
+func (q *Queries) MarkFeedArticlesRead(ctx context.Context, feedID int64) error {
+	_, err := q.db.ExecContext(ctx, markFeedArticlesRead, feedID)
+	return err
+}
+
+const markFeedArticlesUnread = `-- name: MarkFeedArticlesUnread :exec
+UPDATE articles
+SET is_read = 0
+WHERE feed_id = ?
+`
+
+func (q *Queries) MarkFeedArticlesUnread(ctx context.Context, feedID int64) error {
+	_, err := q.db.ExecContext(ctx, markFeedArticlesUnread, feedID)
+	return err
+}
+
+const updateArticle = `-- name: UpdateArticle :exec
+UPDATE articles
+SET title = ?, url = ?
+WHERE feed_id = ? AND guid = ?
+`
+
+type UpdateArticleParams struct {
+	Title  string
+	Url    string
+	FeedID int64
+	Guid   string
+}
+
+func (q *Queries) UpdateArticle(ctx context.Context, arg UpdateArticleParams) error {
+	_, err := q.db.ExecContext(ctx, updateArticle,
+		arg.Title,
+		arg.Url,
+		arg.FeedID,
+		arg.Guid,
+	)
+	return err
+}
+
+const updateArticleReadStatus = `-- name: UpdateArticleReadStatus :exec
+UPDATE articles
+SET is_read = ?
+WHERE id = ?
+`
+
+type UpdateArticleReadStatusParams struct {
+	IsRead int64
+	ID     int64
+}
+
+func (q *Queries) UpdateArticleReadStatus(ctx context.Context, arg UpdateArticleReadStatusParams) error {
+	_, err := q.db.ExecContext(ctx, updateArticleReadStatus, arg.IsRead, arg.ID)
+	return err
+}
diff --git a/backend/db/db.go b/backend/db/db.go
new file mode 100644
index 0000000..0c56c2b
--- /dev/null
+++ b/backend/db/db.go
@@ -0,0 +1,31 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+
+package db
+
+import (
+	"context"
+	"database/sql"
+)
+
+type DBTX interface {
+	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
+	PrepareContext(context.Context, string) (*sql.Stmt, error)
+	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
+	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
+}
+
+func New(db DBTX) *Queries {
+	return &Queries{db: db}
+}
+
+type Queries struct {
+	db DBTX
+}
+
+func (q *Queries) WithTx(tx *sql.Tx) *Queries {
+	return &Queries{
+		db: tx,
+	}
+}
diff --git a/backend/db/feeds.sql.go b/backend/db/feeds.sql.go
new file mode 100644
index 0000000..4db84af
--- /dev/null
+++ b/backend/db/feeds.sql.go
@@ -0,0 +1,165 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+// source: feeds.sql
+
+package db
+
+import (
+	"context"
+)
+
+const createFeed = `-- name: CreateFeed :one
+INSERT INTO feeds (url, title, fetched_at)
+VALUES (?, ?, ?)
+RETURNING id, url, title, fetched_at
+`
+
+type CreateFeedParams struct {
+	Url       string
+	Title     string
+	FetchedAt string
+}
+
+func (q *Queries) CreateFeed(ctx context.Context, arg CreateFeedParams) (Feed, error) {
+	row := q.db.QueryRowContext(ctx, createFeed, arg.Url, arg.Title, arg.FetchedAt)
+	var i Feed
+	err := row.Scan(
+		&i.ID,
+		&i.Url,
+		&i.Title,
+		&i.FetchedAt,
+	)
+	return i, err
+}
+
+const deleteFeed = `-- name: DeleteFeed :exec
+DELETE FROM feeds
+WHERE id = ?
+`
+
+func (q *Queries) DeleteFeed(ctx context.Context, id int64) error {
+	_, err := q.db.ExecContext(ctx, deleteFeed, id)
+	return err
+}
+
+const getFeed = `-- name: GetFeed :one
+SELECT id, url, title, fetched_at
+FROM feeds
+WHERE id = ?
+`
+
+func (q *Queries) GetFeed(ctx context.Context, id int64) (Feed, error) {
+	row := q.db.QueryRowContext(ctx, getFeed, id)
+	var i Feed
+	err := row.Scan(
+		&i.ID,
+		&i.Url,
+		&i.Title,
+		&i.FetchedAt,
+	)
+	return i, err
+}
+
+const getFeedByURL = `-- name: GetFeedByURL :one
+SELECT id, url, title, fetched_at
+FROM feeds
+WHERE url = ?
+`
+
+func (q *Queries) GetFeedByURL(ctx context.Context, url string) (Feed, error) {
+	row := q.db.QueryRowContext(ctx, getFeedByURL, url)
+	var i Feed
+	err := row.Scan(
+		&i.ID,
+		&i.Url,
+		&i.Title,
+		&i.FetchedAt,
+	)
+	return i, err
+}
+
+const getFeeds = `-- name: GetFeeds :many
+SELECT id, url, title, fetched_at
+FROM feeds
+ORDER BY id
+`
+
+func (q *Queries) GetFeeds(ctx context.Context) ([]Feed, error) {
+	rows, err := q.db.QueryContext(ctx, getFeeds)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []Feed{}
+	for rows.Next() {
+		var i Feed
+		if err := rows.Scan(
+			&i.ID,
+			&i.Url,
+			&i.Title,
+			&i.FetchedAt,
+		); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const getFeedsToFetch = `-- name: GetFeedsToFetch :many
+SELECT id, url, fetched_at
+FROM feeds
+`
+
+type GetFeedsToFetchRow struct {
+	ID        int64
+	Url       string
+	FetchedAt string
+}
+
+func (q *Queries) GetFeedsToFetch(ctx context.Context) ([]GetFeedsToFetchRow, error) {
+	rows, err := q.db.QueryContext(ctx, getFeedsToFetch)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+	items := []GetFeedsToFetchRow{}
+	for rows.Next() {
+		var i GetFeedsToFetchRow
+		if err := rows.Scan(&i.ID, &i.Url, &i.FetchedAt); err != nil {
+			return nil, err
+		}
+		items = append(items, i)
+	}
+	if err := rows.Close(); err != nil {
+		return nil, err
+	}
+	if err := rows.Err(); err != nil {
+		return nil, err
+	}
+	return items, nil
+}
+
+const updateFeedMetadata = `-- name: UpdateFeedMetadata :exec
+UPDATE feeds
+SET title = ?, fetched_at = ?
+WHERE id = ?
+`
+
+type UpdateFeedMetadataParams struct {
+	Title     string
+	FetchedAt string
+	ID        int64
+}
+
+func (q *Queries) UpdateFeedMetadata(ctx context.Context, arg UpdateFeedMetadataParams) error {
+	_, err := q.db.ExecContext(ctx, updateFeedMetadata, arg.Title, arg.FetchedAt, arg.ID)
+	return err
+}
diff --git a/backend/db/models.go b/backend/db/models.go
new file mode 100644
index 0000000..2f36cb4
--- /dev/null
+++ b/backend/db/models.go
@@ -0,0 +1,21 @@
+// Code generated by sqlc. DO NOT EDIT.
+// versions:
+//   sqlc v1.29.0
+
+package db
+
+type Article struct {
+	ID     int64
+	FeedID int64
+	Guid   string
+	Title  string
+	Url    string
+	IsRead int64
+}
+
+type Feed struct {
+	ID        int64
+	Url       string
+	Title     string
+	FetchedAt string
+}
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;
diff --git a/backend/db/schema.sql b/backend/db/schema.sql
new file mode 100644
index 0000000..5c2bf48
--- /dev/null
+++ b/backend/db/schema.sql
@@ -0,0 +1,25 @@
+-- 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
+);
+
+-- Articles
+CREATE TABLE IF NOT EXISTS articles (
+    id      INTEGER PRIMARY KEY AUTOINCREMENT,
+    feed_id INTEGER NOT NULL,
+    guid    TEXT NOT NULL,
+    title   TEXT NOT NULL,
+    url     TEXT NOT NULL,
+    is_read INTEGER NOT NULL DEFAULT 0,
+    FOREIGN KEY (feed_id) REFERENCES feeds(id) ON DELETE CASCADE
+);
+
+-- Indice
+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);