commit cc4249f287ba6c604f11e99f79bc03f4264125c3
parent 57c2c3a99a69e71c8852e6885262d003230560fd
Author: David Eisinger <[email protected]>
Date: Mon, 6 Oct 2025 13:40:35 -0400
fulltext search
Diffstat:
5 files changed, 192 insertions(+), 11 deletions(-)
diff --git a/README.md b/README.md
@@ -64,11 +64,12 @@ curl -i -X POST http://localhost:8080/bookmarks \
### GET /bookmarks
-List bookmarks in reverse chronological order. Supports basic pagination.
+List bookmarks in reverse chronological order, or search with full-text.
- Query params:
- `limit` (optional, default 50, max 200)
- `offset` (optional, default 0)
+ - `q` (optional) full-text query across title, url, description, and archived content
- Responses:
- `200 OK` with JSON array of bookmarks
- `400 Bad Request` for invalid `limit` or `offset`
@@ -77,13 +78,14 @@ List bookmarks in reverse chronological order. Supports basic pagination.
```
curl -s http://localhost:8080/bookmarks | jq
curl -s 'http://localhost:8080/bookmarks?limit=20&offset=20' | jq
+curl -s 'http://localhost:8080/bookmarks?q=example' | jq
```
## Data Model
Table: `bookmarks`
- `id` INTEGER PRIMARY KEY AUTOINCREMENT (starts at 1)
-- `url` TEXT NOT NULL
+- `url` TEXT NOT NULL UNIQUE
- `title` TEXT NOT NULL
- `description` TEXT NULL
- `created_at` TEXT NOT NULL (RFC3339)
diff --git a/cmd/marky.go b/cmd/marky.go
@@ -97,22 +97,52 @@ func main() {
http.Error(w, "failed to archive URL", http.StatusBadGateway)
return
}
- created, err := db.CreateBookmarkWithArchive(r.Context(), req.URL, req.Title, req.Description, txt)
- if err != nil {
- log.Printf("create bookmark with archive error: %v", err)
+ // If URL exists, re-archive and touch updated_at; else create
+ if existing, err := db.GetBookmarkByURL(r.Context(), req.URL); err != nil {
+ log.Printf("lookup by url error: %v", err)
http.Error(w, "internal server error", http.StatusInternalServerError)
return
+ } else if existing != nil {
+ if err := db.SaveArchive(r.Context(), existing.ID, txt); err != nil {
+ log.Printf("save archive error: %v", err)
+ http.Error(w, "internal server error", http.StatusInternalServerError)
+ return
+ }
+ if err := db.TouchBookmark(r.Context(), existing.ID); err != nil {
+ log.Printf("touch bookmark error: %v", err)
+ http.Error(w, "internal server error", http.StatusInternalServerError)
+ return
+ }
+ updated, err := db.GetBookmark(r.Context(), existing.ID)
+ if err != nil || updated == nil {
+ if err != nil {
+ log.Printf("refetch bookmark error: %v", err)
+ }
+ http.Error(w, "internal server error", http.StatusInternalServerError)
+ return
+ }
+ w.Header().Set("Content-Type", "application/json")
+ // 200 OK since we updated existing bookmark
+ _ = json.NewEncoder(w).Encode(toResp(updated))
+ return
+ } else {
+ created, err := db.CreateBookmarkWithArchive(r.Context(), req.URL, req.Title, req.Description, txt)
+ if err != nil {
+ log.Printf("create bookmark with archive error: %v", err)
+ http.Error(w, "internal server error", http.StatusInternalServerError)
+ return
+ }
+ w.Header().Set("Content-Type", "application/json")
+ w.WriteHeader(http.StatusCreated)
+ _ = json.NewEncoder(w).Encode(toResp(created))
}
- w.Header().Set("Content-Type", "application/json")
- w.WriteHeader(http.StatusCreated)
- _ = json.NewEncoder(w).Encode(toResp(created))
-
case http.MethodGet:
- // Optional pagination: limit and offset
+ // Optional pagination and search: limit, offset, q
q := r.URL.Query()
limit := 50
offset := 0
+ query := strings.TrimSpace(q.Get("q"))
if s := q.Get("limit"); s != "" {
if v, err := strconv.Atoi(s); err == nil && v > 0 {
if v > 200 {
@@ -133,7 +163,13 @@ func main() {
}
}
- items, err := db.ListBookmarks(r.Context(), limit, offset)
+ var items []dbpkg.Bookmark
+ var err error
+ if query != "" {
+ items, err = db.SearchBookmarks(r.Context(), query, limit, offset)
+ } else {
+ items, err = db.ListBookmarks(r.Context(), limit, offset)
+ }
if err != nil {
log.Printf("list bookmarks error: %v", err)
http.Error(w, "internal server error", http.StatusInternalServerError)
diff --git a/internal/database.go b/internal/database.go
@@ -35,6 +35,11 @@ type DB struct {
dsn string
}
+// execer abstracts *sql.DB and *sql.Tx for executing SQL with context.
+type execer interface {
+ ExecContext(context.Context, string, ...any) (sql.Result, error)
+}
+
// DefaultPath returns the default SQLite file path: ~/.marky/bookmarks.sql
func DefaultPath() (string, error) {
home, err := os.UserHomeDir()
@@ -175,6 +180,14 @@ func (db *DB) CreateBookmark(ctx context.Context, url, title string, description
if err != nil {
return nil, fmt.Errorf("last insert id: %w", err)
}
+ // Replace FTS row (empty content until archive exists)
+ ftsContent := ""
+ if c, _, ok, _ := db.GetArchive(ctx, id); ok {
+ ftsContent = c
+ }
+ if err := ftsReplace(ctx, db.sql, id, title, url, nullString(desc), ftsContent); err != nil {
+ return nil, fmt.Errorf("fts replace after create: %w", err)
+ }
return &Bookmark{
ID: id,
URL: url,
@@ -208,6 +221,29 @@ func (db *DB) GetBookmark(ctx context.Context, id int64) (*Bookmark, error) {
return &b, nil
}
+// GetBookmarkByURL returns a bookmark by exact URL.
+func (db *DB) GetBookmarkByURL(ctx context.Context, u string) (*Bookmark, error) {
+ var b Bookmark
+ var created, updated string
+ if err := db.sql.QueryRowContext(ctx, `
+ SELECT id, url, title, description, created_at, updated_at
+ FROM bookmarks WHERE url = ?
+ `, u).Scan(&b.ID, &b.URL, &b.Title, &b.Description, &created, &updated); err != nil {
+ if errors.Is(err, sql.ErrNoRows) {
+ return nil, nil
+ }
+ return nil, fmt.Errorf("select bookmark by url: %w", err)
+ }
+ var err error
+ if b.CreatedAt, err = time.Parse(time.RFC3339Nano, created); err != nil {
+ return nil, fmt.Errorf("parse created_at: %w", err)
+ }
+ if b.UpdatedAt, err = time.Parse(time.RFC3339Nano, updated); err != nil {
+ return nil, fmt.Errorf("parse updated_at: %w", err)
+ }
+ return &b, nil
+}
+
// ListBookmarks returns bookmarks ordered by created_at desc.
func (db *DB) ListBookmarks(ctx context.Context, limit, offset int) ([]Bookmark, error) {
if limit <= 0 {
@@ -269,9 +305,30 @@ func (db *DB) UpdateBookmark(ctx context.Context, id int64, url, title string, d
if n == 0 {
return nil, nil
}
+ // Update FTS metadata while preserving content
+ var descForFTS sql.NullString
+ if description != nil {
+ descForFTS = sql.NullString{String: *description, Valid: true}
+ }
+ ftsContent := ""
+ if c, _, ok, _ := db.GetArchive(ctx, id); ok {
+ ftsContent = c
+ }
+ if err := ftsReplace(ctx, db.sql, id, title, url, nullString(descForFTS), ftsContent); err != nil {
+ return nil, fmt.Errorf("fts replace after update: %w", err)
+ }
return db.GetBookmark(ctx, id)
}
+// TouchBookmark updates only the updated_at timestamp.
+func (db *DB) TouchBookmark(ctx context.Context, id int64) error {
+ _, err := db.sql.ExecContext(ctx, `UPDATE bookmarks SET updated_at = ? WHERE id = ?`, time.Now().UTC().Format(time.RFC3339Nano), id)
+ if err != nil {
+ return fmt.Errorf("touch bookmark: %w", err)
+ }
+ return nil
+}
+
// DeleteBookmark deletes a bookmark by ID. Returns true if a row was deleted.
func (db *DB) DeleteBookmark(ctx context.Context, id int64) (bool, error) {
res, err := db.sql.ExecContext(ctx, `DELETE FROM bookmarks WHERE id = ?`, id)
@@ -282,6 +339,11 @@ func (db *DB) DeleteBookmark(ctx context.Context, id int64) (bool, error) {
if err != nil {
return false, fmt.Errorf("rows affected: %w", err)
}
+ if n > 0 {
+ if _, err := db.sql.ExecContext(ctx, `DELETE FROM bookmarks_fts WHERE rowid = ?`, id); err != nil {
+ return false, fmt.Errorf("delete fts row: %w", err)
+ }
+ }
return n > 0, nil
}
@@ -328,6 +390,11 @@ func (db *DB) CreateBookmarkWithArchive(ctx context.Context, url, title string,
_ = tx.Rollback()
return nil, fmt.Errorf("insert archive: %w", err)
}
+ // Replace FTS row within the same transaction
+ if err := ftsReplace(ctx, tx, id, title, url, nullString(desc), archiveContent); err != nil {
+ _ = tx.Rollback()
+ return nil, fmt.Errorf("insert fts row: %w", err)
+ }
if err := tx.Commit(); err != nil {
return nil, fmt.Errorf("commit: %w", err)
}
@@ -354,6 +421,16 @@ func (db *DB) SaveArchive(ctx context.Context, bookmarkID int64, content string)
if err != nil {
return fmt.Errorf("save archive: %w", err)
}
+ // Replace into FTS with latest content and metadata
+ b, err := db.GetBookmark(ctx, bookmarkID)
+ if err != nil {
+ return fmt.Errorf("fetch bookmark for fts: %w", err)
+ }
+ if b != nil {
+ if err := ftsReplace(ctx, db.sql, bookmarkID, b.Title, b.URL, nullString(b.Description), content); err != nil {
+ return fmt.Errorf("fts replace after archive: %w", err)
+ }
+ }
return nil
}
@@ -376,3 +453,55 @@ func (db *DB) GetArchive(ctx context.Context, bookmarkID int64) (content string,
}
return content, at, true, nil
}
+
+// SearchBookmarks performs a full-text search using FTS5 and returns matching bookmarks
+// ordered by a simple relevance score derived from offsets() length.
+func (db *DB) SearchBookmarks(ctx context.Context, query string, limit, offset int) ([]Bookmark, error) {
+ if limit <= 0 {
+ limit = 50
+ }
+ rows, err := db.sql.QueryContext(ctx, `
+ SELECT b.id, b.url, b.title, b.description, b.created_at, b.updated_at
+ FROM bookmarks_fts AS f
+ JOIN bookmarks AS b ON b.id = f.rowid
+ WHERE bookmarks_fts MATCH ?
+ ORDER BY bm25(bookmarks_fts)
+ LIMIT ? OFFSET ?
+ `, query, limit, offset)
+ if err != nil {
+ return nil, fmt.Errorf("fts query: %w", err)
+ }
+ defer rows.Close()
+ out := make([]Bookmark, 0, limit)
+ for rows.Next() {
+ var b Bookmark
+ var created, updated string
+ if err := rows.Scan(&b.ID, &b.URL, &b.Title, &b.Description, &created, &updated); err != nil {
+ return nil, fmt.Errorf("scan fts row: %w", err)
+ }
+ if b.CreatedAt, err = time.Parse(time.RFC3339Nano, created); err != nil {
+ return nil, fmt.Errorf("parse created_at: %w", err)
+ }
+ if b.UpdatedAt, err = time.Parse(time.RFC3339Nano, updated); err != nil {
+ return nil, fmt.Errorf("parse updated_at: %w", err)
+ }
+ out = append(out, b)
+ }
+ if err := rows.Err(); err != nil {
+ return nil, fmt.Errorf("iterate fts: %w", err)
+ }
+ return out, nil
+}
+
+// ftsReplace replaces a row in the contentless FTS5 table using DELETE + INSERT,
+// because UPSERT/REPLACE is not implemented for virtual tables.
+func ftsReplace(ctx context.Context, ex execer, rowid int64, title, url string, description any, content string) error {
+ if _, err := ex.ExecContext(ctx, `DELETE FROM bookmarks_fts WHERE rowid = ?`, rowid); err != nil {
+ return err
+ }
+ _, err := ex.ExecContext(ctx, `
+ INSERT INTO bookmarks_fts(rowid, title, url, description, content)
+ VALUES(?, ?, ?, ?, ?)
+ `, rowid, title, url, description, content)
+ return err
+}
diff --git a/internal/migrations/0003_fts.sql b/internal/migrations/0003_fts.sql
@@ -0,0 +1,10 @@
+-- FTS5 virtual table for full-text search across bookmark metadata and archived content.
+-- We use a contentless FTS5 table and manage rows explicitly from application code.
+CREATE VIRTUAL TABLE IF NOT EXISTS bookmarks_fts USING fts5(
+ title,
+ url,
+ description,
+ content,
+ content=''
+);
+
diff --git a/internal/migrations/0004_unique_url.sql b/internal/migrations/0004_unique_url.sql
@@ -0,0 +1,4 @@
+-- Ensure URL uniqueness.
+DROP INDEX IF EXISTS idx_bookmarks_url;
+CREATE UNIQUE INDEX IF NOT EXISTS idx_bookmarks_url_unique ON bookmarks(url);
+
