donniemarko/internal/storage/sqlite.go

package storage

import (
	"database/sql"
	"fmt"
	"time"

	"donniemarko/internal/note"

	_ "modernc.org/sqlite"
)

type SQLiteStorage struct {
	db *sql.DB
}

const sqliteSchema = `
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS notes (
    id TEXT PRIMARY KEY,
    path TEXT NOT NULL UNIQUE,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    updated_at INTEGER NOT NULL,
    size INTEGER NOT NULL,
    published INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS tags (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS note_tags (
    note_id TEXT NOT NULL,
    tag_id INTEGER NOT NULL,
    UNIQUE(note_id, tag_id),
    FOREIGN KEY(note_id) REFERENCES notes(id) ON DELETE CASCADE,
    FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS notes_updated_at_idx ON notes(updated_at);
CREATE INDEX IF NOT EXISTS notes_path_idx ON notes(path);
CREATE INDEX IF NOT EXISTS note_tags_note_idx ON note_tags(note_id);
CREATE INDEX IF NOT EXISTS note_tags_tag_idx ON note_tags(tag_id);
`

func NewSQLiteStorage(dbPath string) (*SQLiteStorage, error) {
	db, err := sql.Open("sqlite", dbPath)
	if err != nil {
		return nil, fmt.Errorf("open sqlite: %w", err)
	}

	if _, err := db.Exec(sqliteSchema); err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("init schema: %w", err)
	}

	return &SQLiteStorage{db: db}, nil
}

func (s *SQLiteStorage) Close() error {
	if s.db == nil {
		return nil
	}
	return s.db.Close()
}

func (s *SQLiteStorage) GetAll() []*note.Note {
	rows, err := s.db.Query(`
        SELECT id, path, title, content, updated_at, size, published
        FROM notes
    `)
	if err != nil {
		return []*note.Note{}
	}
	defer rows.Close()

	var notes []*note.Note
	for rows.Next() {
		n, err := scanNote(rows)
		if err != nil {
			continue
		}
		n.Tags = s.GetTags(n.ID)
		notes = append(notes, n)
	}

	return notes
}

func (s *SQLiteStorage) Get(id string) (*note.Note, error) {
	row := s.db.QueryRow(`
        SELECT id, path, title, content, updated_at, size, published
        FROM notes
        WHERE id = ?
    `, id)

	n, err := scanNote(row)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("No note with id '%s'", id)
		}
		return nil, err
	}

	n.Tags = s.GetTags(n.ID)
	return n, nil
}

func (s *SQLiteStorage) Create(n *note.Note) error {
	_, err := s.db.Exec(`
        INSERT INTO notes (id, path, title, content, updated_at, size, published)
        VALUES (?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(id) DO UPDATE SET
            path = excluded.path,
            title = excluded.title,
            content = excluded.content,
            updated_at = excluded.updated_at,
            size = excluded.size,
            published = excluded.published
        WHERE excluded.updated_at > notes.updated_at
    `,
		n.ID,
		n.Path,
		n.Title,
		n.Content,
		toUnix(n.UpdatedAt),
		n.Size,
		boolToInt(n.Published),
	)
	if err != nil {
		return fmt.Errorf("insert note: %w", err)
	}

	return nil
}

func (s *SQLiteStorage) Delete(id string) {
	_, _ = s.db.Exec(`DELETE FROM notes WHERE id = ?`, id)
}

func (s *SQLiteStorage) DeleteByPath(path string) {
	_, _ = s.db.Exec(`DELETE FROM notes WHERE path = ?`, path)
}

func (s *SQLiteStorage) Update(id string, n *note.Note) {
	_, _ = s.db.Exec(`
        UPDATE notes
        SET path = ?, title = ?, content = ?, updated_at = ?, size = ?, published = ?
        WHERE id = ?
    `,
		n.Path,
		n.Title,
		n.Content,
		toUnix(n.UpdatedAt),
		n.Size,
		boolToInt(n.Published),
		id,
	)
}

func (s *SQLiteStorage) Search(query string) []*note.Note {
	pattern := "%" + query + "%"
	rows, err := s.db.Query(`
        SELECT id, path, title, content, updated_at, size, published
        FROM notes
        WHERE lower(content) LIKE lower(?)
        OR id IN (
            SELECT nt.note_id
            FROM note_tags nt
            JOIN tags t ON t.id = nt.tag_id
            WHERE lower(t.name) LIKE lower(?)
        )
    `, pattern, pattern)
	if err != nil {
		return []*note.Note{}
	}
	defer rows.Close()

	var notes []*note.Note
	for rows.Next() {
		n, err := scanNote(rows)
		if err != nil {
			continue
		}
		n.Tags = s.GetTags(n.ID)
		notes = append(notes, n)
	}

	return notes
}

func (s *SQLiteStorage) Count() int {
	row := s.db.QueryRow(`SELECT COUNT(*) FROM notes`)
	var count int
	if err := row.Scan(&count); err != nil {
		return 0
	}
	return count
}

func (s *SQLiteStorage) AddTag(noteID, tag string) error {
	tx, err := s.db.Begin()
	if err != nil {
		return fmt.Errorf("begin tx: %w", err)
	}

	if _, err := tx.Exec(`INSERT OR IGNORE INTO tags (name) VALUES (?)`, tag); err != nil {
		_ = tx.Rollback()
		return fmt.Errorf("insert tag: %w", err)
	}

	var tagID int64
	if err := tx.QueryRow(`SELECT id FROM tags WHERE name = ?`, tag).Scan(&tagID); err != nil {
		_ = tx.Rollback()
		return fmt.Errorf("lookup tag: %w", err)
	}

	if _, err := tx.Exec(`INSERT OR IGNORE INTO note_tags (note_id, tag_id) VALUES (?, ?)`, noteID, tagID); err != nil {
		_ = tx.Rollback()
		return fmt.Errorf("attach tag: %w", err)
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit: %w", err)
	}

	return nil
}

func (s *SQLiteStorage) RemoveTag(noteID, tag string) error {
	tx, err := s.db.Begin()
	if err != nil {
		return fmt.Errorf("begin tx: %w", err)
	}

	var tagID int64
	err = tx.QueryRow(`SELECT id FROM tags WHERE name = ?`, tag).Scan(&tagID)
	if err != nil {
		if err == sql.ErrNoRows {
			_ = tx.Rollback()
			return nil
		}
		_ = tx.Rollback()
		return fmt.Errorf("lookup tag: %w", err)
	}

	if _, err := tx.Exec(`DELETE FROM note_tags WHERE note_id = ? AND tag_id = ?`, noteID, tagID); err != nil {
		_ = tx.Rollback()
		return fmt.Errorf("detach tag: %w", err)
	}

	if _, err := tx.Exec(`DELETE FROM tags WHERE id = ? AND NOT EXISTS (SELECT 1 FROM note_tags WHERE tag_id = ?)`, tagID, tagID); err != nil {
		_ = tx.Rollback()
		return fmt.Errorf("cleanup tag: %w", err)
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit: %w", err)
	}

	return nil
}

func (s *SQLiteStorage) GetTags(noteID string) []string {
	rows, err := s.db.Query(`
        SELECT t.name
        FROM tags t
        JOIN note_tags nt ON nt.tag_id = t.id
        WHERE nt.note_id = ?
        ORDER BY t.name
    `, noteID)
	if err != nil {
		return []string{}
	}
	defer rows.Close()

	var tags []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			continue
		}
		tags = append(tags, name)
	}

	return tags
}

type scanner interface {
	Scan(dest ...interface{}) error
}

func scanNote(row scanner) (*note.Note, error) {
	var (
		id        string
		path      string
		title     string
		content   string
		updated   int64
		size      int64
		published int
	)

	if err := row.Scan(&id, &path, &title, &content, &updated, &size, &published); err != nil {
		return nil, err
	}

	return &note.Note{
		ID:        id,
		Path:      path,
		Title:     title,
		Content:   content,
		UpdatedAt: fromUnix(updated),
		Size:      size,
		Published: published != 0,
	}, nil
}

func toUnix(t time.Time) int64 {
	if t.IsZero() {
		return 0
	}
	return t.UTC().Unix()
}

func fromUnix(v int64) time.Time {
	if v == 0 {
		return time.Time{}
	}
	return time.Unix(v, 0).UTC()
}

func boolToInt(v bool) int {
	if v {
		return 1
	}
	return 0
}