1-- +goose Up
2-- History tracking for visited URLs
3
4CREATE TABLE IF NOT EXISTS history (
5 id INTEGER PRIMARY KEY AUTOINCREMENT,
6 url TEXT NOT NULL UNIQUE,
7 title TEXT,
8 favicon_url TEXT,
9 visit_count INTEGER DEFAULT 1,
10 last_visited DATETIME DEFAULT CURRENT_TIMESTAMP,
11 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
12);
13
14CREATE INDEX IF NOT EXISTS idx_history_url ON history(url);
15CREATE INDEX IF NOT EXISTS idx_history_last_visited ON history(last_visited);
16CREATE INDEX IF NOT EXISTS idx_history_visit_count_last_visited ON history(visit_count DESC, last_visited DESC);
17
18-- FTS5 for history search
19CREATE VIRTUAL TABLE IF NOT EXISTS history_fts USING fts5(
20 url,
21 title,
22 content='history',
23 content_rowid='id'
24);
25
26-- Triggers to keep FTS in sync
27-- +goose StatementBegin
28CREATE TRIGGER IF NOT EXISTS history_fts_insert AFTER INSERT ON history BEGIN
29 INSERT INTO history_fts(rowid, url, title) VALUES (NEW.id, NEW.url, NEW.title);
30END;
31-- +goose StatementEnd
32
33-- +goose StatementBegin
34CREATE TRIGGER IF NOT EXISTS history_fts_delete AFTER DELETE ON history BEGIN
35 INSERT INTO history_fts(history_fts, rowid, url, title) VALUES('delete', OLD.id, OLD.url, OLD.title);
36END;
37-- +goose StatementEnd
38
39-- +goose StatementBegin
40CREATE TRIGGER IF NOT EXISTS history_fts_update AFTER UPDATE ON history BEGIN
41 INSERT INTO history_fts(history_fts, rowid, url, title) VALUES('delete', OLD.id, OLD.url, OLD.title);
42 INSERT INTO history_fts(rowid, url, title) VALUES (NEW.id, NEW.url, NEW.title);
43END;
44-- +goose StatementEnd
45
46-- +goose Down
47DROP TRIGGER IF EXISTS history_fts_update;
48DROP TRIGGER IF EXISTS history_fts_delete;
49DROP TRIGGER IF EXISTS history_fts_insert;
50DROP TABLE IF EXISTS history_fts;
51DROP TABLE IF EXISTS history;