Squid
5a0e2e630b
Ship the Go/SQLite API and Web UI, Chrome/Brave capture addon, Docker Compose, Pangolin reverse-proxy support, and a user-crontab watchdog so the binary stays running without systemd.
554 lines
16 KiB
Go
554 lines
16 KiB
Go
package db
|
|
|
|
import (
|
|
"context"
|
|
"database/sql"
|
|
"fmt"
|
|
"strings"
|
|
"time"
|
|
|
|
_ "modernc.org/sqlite"
|
|
)
|
|
|
|
// Store wraps the SQLite connection and domain queries.
|
|
type Store struct {
|
|
db *sql.DB
|
|
}
|
|
|
|
// User is a Reddit account reference.
|
|
type User struct {
|
|
ID int64 `json:"id"`
|
|
RedditUserID string `json:"reddit_user_id"`
|
|
Username string `json:"username"`
|
|
}
|
|
|
|
// Post is a Reddit submission ("top post").
|
|
type Post struct {
|
|
ID int64 `json:"id"`
|
|
RedditPostID string `json:"reddit_post_id"`
|
|
Title string `json:"title"`
|
|
Body string `json:"body"`
|
|
AuthorID int64 `json:"author_id"`
|
|
AuthorName string `json:"author_name"`
|
|
Permalink string `json:"permalink"`
|
|
Subreddit string `json:"subreddit"`
|
|
CreatedAt time.Time `json:"created_at"`
|
|
}
|
|
|
|
// Comment is a saved Reddit comment with optional screenshot/Imgur URL.
|
|
type Comment struct {
|
|
ID int64 `json:"id"`
|
|
RedditCommentID string `json:"reddit_comment_id"`
|
|
Body string `json:"body"`
|
|
AuthorID int64 `json:"author_id"`
|
|
AuthorName string `json:"author_name"`
|
|
PostID int64 `json:"post_id"`
|
|
RedditPostID string `json:"reddit_post_id"`
|
|
Permalink string `json:"permalink"`
|
|
ScreenshotPath string `json:"screenshot_path"`
|
|
ImgurURL string `json:"imgur_url"`
|
|
CapturedAt time.Time `json:"captured_at"`
|
|
PostTitle string `json:"post_title"`
|
|
}
|
|
|
|
// SearchResult is a comment row returned by search.
|
|
type SearchResult struct {
|
|
Comment
|
|
}
|
|
|
|
// Open opens (or creates) the SQLite database and runs migrations.
|
|
func Open(path string) (*Store, error) {
|
|
database, err := sql.Open("sqlite", path)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("open sqlite: %w", err)
|
|
}
|
|
database.SetMaxOpenConns(1)
|
|
|
|
store := &Store{db: database}
|
|
if err := store.migrate(); err != nil {
|
|
_ = database.Close()
|
|
return nil, err
|
|
}
|
|
return store, nil
|
|
}
|
|
|
|
// Close closes the database.
|
|
func (s *Store) Close() error {
|
|
return s.db.Close()
|
|
}
|
|
|
|
func (s *Store) migrate() error {
|
|
const schema = `
|
|
PRAGMA foreign_keys = ON;
|
|
PRAGMA journal_mode = WAL;
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
reddit_user_id TEXT NOT NULL UNIQUE,
|
|
username TEXT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS posts (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
reddit_post_id TEXT NOT NULL UNIQUE,
|
|
title TEXT NOT NULL DEFAULT '',
|
|
body TEXT NOT NULL DEFAULT '',
|
|
author_id INTEGER NOT NULL REFERENCES users(id),
|
|
permalink TEXT NOT NULL DEFAULT '',
|
|
subreddit TEXT NOT NULL DEFAULT '',
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS comments (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
reddit_comment_id TEXT NOT NULL UNIQUE,
|
|
body TEXT NOT NULL DEFAULT '',
|
|
author_id INTEGER NOT NULL REFERENCES users(id),
|
|
post_id INTEGER NOT NULL REFERENCES posts(id),
|
|
permalink TEXT NOT NULL DEFAULT '',
|
|
screenshot_path TEXT NOT NULL DEFAULT '',
|
|
imgur_url TEXT NOT NULL DEFAULT '',
|
|
captured_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
|
|
CREATE INDEX IF NOT EXISTS idx_comments_reddit_id ON comments(reddit_comment_id);
|
|
CREATE INDEX IF NOT EXISTS idx_posts_reddit_id ON posts(reddit_post_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS blocked_usernames (
|
|
username TEXT PRIMARY KEY COLLATE NOCASE,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
`
|
|
_, err := s.db.Exec(schema)
|
|
if err != nil {
|
|
return fmt.Errorf("migrate: %w", err)
|
|
}
|
|
return s.seedBlocklistIfEmpty()
|
|
}
|
|
|
|
func (s *Store) seedBlocklistIfEmpty() error {
|
|
var count int
|
|
if err := s.db.QueryRow(`SELECT COUNT(1) FROM blocked_usernames`).Scan(&count); err != nil {
|
|
return err
|
|
}
|
|
if count > 0 {
|
|
return nil
|
|
}
|
|
_, err := s.db.Exec(`INSERT INTO blocked_usernames (username) VALUES (?)`, NormalizeUsername("AutoModerator"))
|
|
return err
|
|
}
|
|
|
|
// NormalizeUsername trims, strips u/, and lower-cases a Reddit username.
|
|
func NormalizeUsername(name string) string {
|
|
name = strings.TrimSpace(name)
|
|
name = strings.TrimPrefix(name, "u/")
|
|
name = strings.TrimPrefix(name, "U/")
|
|
name = strings.TrimPrefix(name, "/u/")
|
|
return strings.ToLower(strings.TrimSpace(name))
|
|
}
|
|
|
|
// ListBlockedUsernames returns all blocked usernames (normalized).
|
|
func (s *Store) ListBlockedUsernames(ctx context.Context) ([]string, error) {
|
|
rows, err := s.db.QueryContext(ctx, `SELECT username FROM blocked_usernames ORDER BY username COLLATE NOCASE`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
var out []string
|
|
for rows.Next() {
|
|
var name string
|
|
if err := rows.Scan(&name); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, name)
|
|
}
|
|
if out == nil {
|
|
out = []string{}
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
// IsUsernameBlocked reports whether the author is on the blocklist.
|
|
func (s *Store) IsUsernameBlocked(ctx context.Context, username string) (bool, error) {
|
|
normalized := NormalizeUsername(username)
|
|
if normalized == "" {
|
|
return false, nil
|
|
}
|
|
var count int
|
|
err := s.db.QueryRowContext(ctx, `SELECT COUNT(1) FROM blocked_usernames WHERE username = ?`, normalized).Scan(&count)
|
|
return count > 0, err
|
|
}
|
|
|
|
// AddBlockedUsername inserts one username (idempotent).
|
|
func (s *Store) AddBlockedUsername(ctx context.Context, username string) (string, error) {
|
|
normalized := NormalizeUsername(username)
|
|
if normalized == "" {
|
|
return "", fmt.Errorf("username required")
|
|
}
|
|
_, err := s.db.ExecContext(ctx, `
|
|
INSERT INTO blocked_usernames (username) VALUES (?)
|
|
ON CONFLICT(username) DO NOTHING
|
|
`, normalized)
|
|
return normalized, err
|
|
}
|
|
|
|
// RemoveBlockedUsername deletes one username.
|
|
func (s *Store) RemoveBlockedUsername(ctx context.Context, username string) error {
|
|
normalized := NormalizeUsername(username)
|
|
if normalized == "" {
|
|
return fmt.Errorf("username required")
|
|
}
|
|
_, err := s.db.ExecContext(ctx, `DELETE FROM blocked_usernames WHERE username = ?`, normalized)
|
|
return err
|
|
}
|
|
|
|
// ReplaceBlockedUsernames replaces the entire blocklist.
|
|
func (s *Store) ReplaceBlockedUsernames(ctx context.Context, usernames []string) ([]string, error) {
|
|
tx, err := s.db.BeginTx(ctx, nil)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer func() { _ = tx.Rollback() }()
|
|
|
|
if _, err := tx.ExecContext(ctx, `DELETE FROM blocked_usernames`); err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
seen := map[string]struct{}{}
|
|
var normalized []string
|
|
for _, name := range usernames {
|
|
n := NormalizeUsername(name)
|
|
if n == "" {
|
|
continue
|
|
}
|
|
if _, ok := seen[n]; ok {
|
|
continue
|
|
}
|
|
seen[n] = struct{}{}
|
|
if _, err := tx.ExecContext(ctx, `INSERT INTO blocked_usernames (username) VALUES (?)`, n); err != nil {
|
|
return nil, err
|
|
}
|
|
normalized = append(normalized, n)
|
|
}
|
|
if err := tx.Commit(); err != nil {
|
|
return nil, err
|
|
}
|
|
if normalized == nil {
|
|
normalized = []string{}
|
|
}
|
|
return normalized, nil
|
|
}
|
|
|
|
// CommentExists reports whether a comment with the given Reddit id is stored.
|
|
func (s *Store) CommentExists(ctx context.Context, redditCommentID string) (bool, error) {
|
|
var count int
|
|
err := s.db.QueryRowContext(ctx, `SELECT COUNT(1) FROM comments WHERE reddit_comment_id = ?`, redditCommentID).Scan(&count)
|
|
if err != nil {
|
|
return false, err
|
|
}
|
|
return count > 0, nil
|
|
}
|
|
|
|
// CommentCaptureStatus reports existence and whether a screenshot is already stored.
|
|
type CommentCaptureStatus struct {
|
|
Exists bool `json:"exists"`
|
|
HasScreenshot bool `json:"has_screenshot"`
|
|
ScreenshotPath string `json:"screenshot_path,omitempty"`
|
|
}
|
|
|
|
// GetCommentCaptureStatus loads capture status for a Reddit comment id.
|
|
func (s *Store) GetCommentCaptureStatus(ctx context.Context, redditCommentID string) (CommentCaptureStatus, error) {
|
|
var path string
|
|
err := s.db.QueryRowContext(ctx, `SELECT screenshot_path FROM comments WHERE reddit_comment_id = ?`, redditCommentID).Scan(&path)
|
|
if err == sql.ErrNoRows {
|
|
return CommentCaptureStatus{Exists: false, HasScreenshot: false}, nil
|
|
}
|
|
if err != nil {
|
|
return CommentCaptureStatus{}, err
|
|
}
|
|
return CommentCaptureStatus{
|
|
Exists: true,
|
|
HasScreenshot: strings.TrimSpace(path) != "",
|
|
ScreenshotPath: path,
|
|
}, nil
|
|
}
|
|
|
|
// SetCommentScreenshot sets screenshot_path only when currently empty.
|
|
func (s *Store) SetCommentScreenshot(ctx context.Context, redditCommentID, screenshotPath string) (bool, error) {
|
|
result, err := s.db.ExecContext(ctx, `
|
|
UPDATE comments
|
|
SET screenshot_path = ?
|
|
WHERE reddit_comment_id = ?
|
|
AND (screenshot_path IS NULL OR screenshot_path = '')
|
|
`, screenshotPath, redditCommentID)
|
|
if err != nil {
|
|
return false, err
|
|
}
|
|
n, err := result.RowsAffected()
|
|
return n > 0, err
|
|
}
|
|
|
|
// PostExists reports whether a post with the given Reddit id is stored.
|
|
func (s *Store) PostExists(ctx context.Context, redditPostID string) (bool, error) {
|
|
var count int
|
|
err := s.db.QueryRowContext(ctx, `SELECT COUNT(1) FROM posts WHERE reddit_post_id = ?`, redditPostID).Scan(&count)
|
|
if err != nil {
|
|
return false, err
|
|
}
|
|
return count > 0, nil
|
|
}
|
|
|
|
// UpsertUser inserts or updates a user by Reddit user id.
|
|
func (s *Store) UpsertUser(ctx context.Context, redditUserID, username string) (int64, error) {
|
|
if redditUserID == "" {
|
|
redditUserID = "unknown:" + username
|
|
}
|
|
if username == "" {
|
|
username = "[deleted]"
|
|
}
|
|
|
|
_, err := s.db.ExecContext(ctx, `
|
|
INSERT INTO users (reddit_user_id, username) VALUES (?, ?)
|
|
ON CONFLICT(reddit_user_id) DO UPDATE SET username = excluded.username
|
|
`, redditUserID, username)
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
|
|
var id int64
|
|
err = s.db.QueryRowContext(ctx, `SELECT id FROM users WHERE reddit_user_id = ?`, redditUserID).Scan(&id)
|
|
return id, err
|
|
}
|
|
|
|
// SavePostInput is the payload for saving a submission.
|
|
type SavePostInput struct {
|
|
RedditPostID string
|
|
Title string
|
|
Body string
|
|
AuthorID string
|
|
AuthorName string
|
|
Permalink string
|
|
Subreddit string
|
|
}
|
|
|
|
// SavePost upserts a post and its author.
|
|
func (s *Store) SavePost(ctx context.Context, input SavePostInput) (int64, error) {
|
|
authorID, err := s.UpsertUser(ctx, input.AuthorID, input.AuthorName)
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
|
|
_, err = s.db.ExecContext(ctx, `
|
|
INSERT INTO posts (reddit_post_id, title, body, author_id, permalink, subreddit, created_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
|
|
ON CONFLICT(reddit_post_id) DO UPDATE SET
|
|
title = excluded.title,
|
|
body = excluded.body,
|
|
author_id = excluded.author_id,
|
|
permalink = excluded.permalink,
|
|
subreddit = excluded.subreddit
|
|
`, input.RedditPostID, input.Title, input.Body, authorID, input.Permalink, input.Subreddit)
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
|
|
var id int64
|
|
err = s.db.QueryRowContext(ctx, `SELECT id FROM posts WHERE reddit_post_id = ?`, input.RedditPostID).Scan(&id)
|
|
return id, err
|
|
}
|
|
|
|
// SaveCommentInput is the payload for saving a comment.
|
|
type SaveCommentInput struct {
|
|
RedditCommentID string
|
|
Body string
|
|
AuthorID string
|
|
AuthorName string
|
|
RedditPostID string
|
|
PostTitle string
|
|
PostBody string
|
|
PostAuthorID string
|
|
PostAuthorName string
|
|
PostPermalink string
|
|
Subreddit string
|
|
Permalink string
|
|
ScreenshotPath string
|
|
}
|
|
|
|
// SaveComment ensures the parent post exists, then inserts the comment (no overwrite of existing).
|
|
func (s *Store) SaveComment(ctx context.Context, input SaveCommentInput) (int64, error) {
|
|
exists, err := s.CommentExists(ctx, input.RedditCommentID)
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
if exists {
|
|
var id int64
|
|
err = s.db.QueryRowContext(ctx, `SELECT id FROM comments WHERE reddit_comment_id = ?`, input.RedditCommentID).Scan(&id)
|
|
return id, err
|
|
}
|
|
|
|
postID, err := s.SavePost(ctx, SavePostInput{
|
|
RedditPostID: input.RedditPostID,
|
|
Title: input.PostTitle,
|
|
Body: input.PostBody,
|
|
AuthorID: input.PostAuthorID,
|
|
AuthorName: input.PostAuthorName,
|
|
Permalink: input.PostPermalink,
|
|
Subreddit: input.Subreddit,
|
|
})
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
|
|
authorID, err := s.UpsertUser(ctx, input.AuthorID, input.AuthorName)
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
|
|
result, err := s.db.ExecContext(ctx, `
|
|
INSERT INTO comments (reddit_comment_id, body, author_id, post_id, permalink, screenshot_path, captured_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, datetime('now'))
|
|
`, input.RedditCommentID, input.Body, authorID, postID, input.Permalink, input.ScreenshotPath)
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
return result.LastInsertId()
|
|
}
|
|
|
|
// GetCommentByRedditID loads a comment by Reddit id.
|
|
func (s *Store) GetCommentByRedditID(ctx context.Context, redditCommentID string) (*Comment, error) {
|
|
row := s.db.QueryRowContext(ctx, `
|
|
SELECT c.id, c.reddit_comment_id, c.body, c.author_id, u.username, c.post_id, p.reddit_post_id,
|
|
c.permalink, c.screenshot_path, c.imgur_url, c.captured_at, p.title
|
|
FROM comments c
|
|
JOIN users u ON u.id = c.author_id
|
|
JOIN posts p ON p.id = c.post_id
|
|
WHERE c.reddit_comment_id = ?
|
|
`, redditCommentID)
|
|
|
|
var comment Comment
|
|
var capturedAt string
|
|
err := row.Scan(
|
|
&comment.ID, &comment.RedditCommentID, &comment.Body, &comment.AuthorID, &comment.AuthorName,
|
|
&comment.PostID, &comment.RedditPostID, &comment.Permalink, &comment.ScreenshotPath,
|
|
&comment.ImgurURL, &capturedAt, &comment.PostTitle,
|
|
)
|
|
if err == sql.ErrNoRows {
|
|
return nil, nil
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
comment.CapturedAt = parseTime(capturedAt)
|
|
return &comment, nil
|
|
}
|
|
|
|
// SetImgurURL stores the Imgur link for a comment.
|
|
func (s *Store) SetImgurURL(ctx context.Context, redditCommentID, imgurURL string) error {
|
|
_, err := s.db.ExecContext(ctx, `UPDATE comments SET imgur_url = ? WHERE reddit_comment_id = ?`, imgurURL, redditCommentID)
|
|
return err
|
|
}
|
|
|
|
func commentSearchFilter(query, username string) (where string, args []any) {
|
|
var clauses []string
|
|
if username != "" {
|
|
clauses = append(clauses, `u.username LIKE ?`)
|
|
args = append(args, username+"%")
|
|
}
|
|
if query != "" {
|
|
pattern := normalizeGlob(query)
|
|
clauses = append(clauses, `(c.body GLOB ? OR p.title GLOB ?)`)
|
|
args = append(args, pattern, pattern)
|
|
}
|
|
if len(clauses) > 0 {
|
|
where = "WHERE " + strings.Join(clauses, " AND ")
|
|
}
|
|
return where, args
|
|
}
|
|
|
|
// CountComments returns how many comments match the optional username / GLOB filters.
|
|
func (s *Store) CountComments(ctx context.Context, query, username string) (int, error) {
|
|
where, args := commentSearchFilter(query, username)
|
|
sqlQuery := fmt.Sprintf(`
|
|
SELECT COUNT(1)
|
|
FROM comments c
|
|
JOIN users u ON u.id = c.author_id
|
|
JOIN posts p ON p.id = c.post_id
|
|
%s
|
|
`, where)
|
|
var total int
|
|
err := s.db.QueryRowContext(ctx, sqlQuery, args...).Scan(&total)
|
|
return total, err
|
|
}
|
|
|
|
// SearchComments finds comments by optional username and GLOB text pattern (newest first).
|
|
func (s *Store) SearchComments(ctx context.Context, query, username string, limit, offset int) ([]SearchResult, error) {
|
|
if limit <= 0 || limit > 500 {
|
|
limit = 100
|
|
}
|
|
if offset < 0 {
|
|
offset = 0
|
|
}
|
|
|
|
where, args := commentSearchFilter(query, username)
|
|
sqlQuery := fmt.Sprintf(`
|
|
SELECT c.id, c.reddit_comment_id, c.body, c.author_id, u.username, c.post_id, p.reddit_post_id,
|
|
c.permalink, c.screenshot_path, c.imgur_url, c.captured_at, p.title
|
|
FROM comments c
|
|
JOIN users u ON u.id = c.author_id
|
|
JOIN posts p ON p.id = c.post_id
|
|
%s
|
|
ORDER BY c.captured_at DESC, c.id DESC
|
|
LIMIT ? OFFSET ?
|
|
`, where)
|
|
args = append(args, limit, offset)
|
|
|
|
rows, err := s.db.QueryContext(ctx, sqlQuery, args...)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
var results []SearchResult
|
|
for rows.Next() {
|
|
var item SearchResult
|
|
var capturedAt string
|
|
if err := rows.Scan(
|
|
&item.ID, &item.RedditCommentID, &item.Body, &item.AuthorID, &item.AuthorName,
|
|
&item.PostID, &item.RedditPostID, &item.Permalink, &item.ScreenshotPath,
|
|
&item.ImgurURL, &capturedAt, &item.PostTitle,
|
|
); err != nil {
|
|
return nil, err
|
|
}
|
|
item.CapturedAt = parseTime(capturedAt)
|
|
results = append(results, item)
|
|
}
|
|
return results, rows.Err()
|
|
}
|
|
|
|
func normalizeGlob(query string) string {
|
|
query = strings.TrimSpace(query)
|
|
if query == "" {
|
|
return "*"
|
|
}
|
|
// If the user did not include wildcards, wrap for substring match.
|
|
if !strings.ContainsAny(query, "*?") {
|
|
return "*" + query + "*"
|
|
}
|
|
return query
|
|
}
|
|
|
|
func parseTime(value string) time.Time {
|
|
layouts := []string{
|
|
time.RFC3339,
|
|
"2006-01-02 15:04:05",
|
|
"2006-01-02T15:04:05Z",
|
|
}
|
|
for _, layout := range layouts {
|
|
if parsed, err := time.Parse(layout, value); err == nil {
|
|
return parsed
|
|
}
|
|
}
|
|
return time.Time{}
|
|
}
|