1-- +goose Up
2-- Store normalized history domains for indexed systemview filtering.
3
4ALTER TABLE history ADD COLUMN domain TEXT;
5
6UPDATE history
7SET domain = LOWER(
8 CASE
9 WHEN INSTR(REPLACE(REPLACE(SUBSTR(url, INSTR(url, '://') + 3), '?', '/'), '#', '/'), '/') > 0 THEN
10 SUBSTR(
11 REPLACE(REPLACE(SUBSTR(url, INSTR(url, '://') + 3), '?', '/'), '#', '/'),
12 1,
13 INSTR(REPLACE(REPLACE(SUBSTR(url, INSTR(url, '://') + 3), '?', '/'), '#', '/'), '/') - 1
14 )
15 ELSE REPLACE(REPLACE(SUBSTR(url, INSTR(url, '://') + 3), '?', '/'), '#', '/')
16 END
17)
18WHERE INSTR(url, '://') > 0;
19
20-- Mirror domainurl.CanonicalDomain for legacy rows as closely as SQLite can:
21-- strip userinfo first, then strip the leading www. alias.
22UPDATE history
23SET domain = SUBSTR(domain, INSTR(domain, '@') + 1)
24WHERE INSTR(domain, '@') > 0;
25
26UPDATE history
27SET domain = SUBSTR(domain, 5)
28WHERE domain LIKE 'www.%';
29
30CREATE INDEX IF NOT EXISTS idx_history_domain_last_visited ON history(domain, last_visited DESC);
31
32-- +goose Down
33DROP INDEX IF EXISTS idx_history_domain_last_visited;
34
35-- Rebuild instead of ALTER TABLE DROP COLUMN so rollback works on SQLite
36-- versions before 3.35. Preserve the base history schema and indexes/triggers.
37DROP TRIGGER IF EXISTS history_fts_update;
38DROP TRIGGER IF EXISTS history_fts_delete;
39DROP TRIGGER IF EXISTS history_fts_insert;
40DROP TABLE IF EXISTS history_fts;
41
42CREATE TABLE history_008_down (
43 id INTEGER PRIMARY KEY AUTOINCREMENT,
44 url TEXT NOT NULL UNIQUE,
45 title TEXT,
46 favicon_url TEXT,
47 visit_count INTEGER DEFAULT 1,
48 last_visited DATETIME DEFAULT CURRENT_TIMESTAMP,
49 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
50);
51
52INSERT INTO history_008_down (id, url, title, favicon_url, visit_count, last_visited, created_at)
53SELECT id, url, title, favicon_url, visit_count, last_visited, created_at
54FROM history;
55
56DROP TABLE history;
57ALTER TABLE history_008_down RENAME TO history;
58
59CREATE INDEX IF NOT EXISTS idx_history_url ON history(url);
60CREATE INDEX IF NOT EXISTS idx_history_last_visited ON history(last_visited);
61CREATE INDEX IF NOT EXISTS idx_history_visit_count_last_visited ON history(visit_count DESC, last_visited DESC);
62
63CREATE VIRTUAL TABLE IF NOT EXISTS history_fts USING fts5(
64 url,
65 title,
66 content='history',
67 content_rowid='id'
68);
69
70-- +goose StatementBegin
71CREATE TRIGGER IF NOT EXISTS history_fts_insert AFTER INSERT ON history BEGIN
72 INSERT INTO history_fts(rowid, url, title) VALUES (NEW.id, NEW.url, NEW.title);
73END;
74-- +goose StatementEnd
75
76-- +goose StatementBegin
77CREATE TRIGGER IF NOT EXISTS history_fts_delete AFTER DELETE ON history BEGIN
78 INSERT INTO history_fts(history_fts, rowid, url, title) VALUES('delete', OLD.id, OLD.url, OLD.title);
79END;
80-- +goose StatementEnd
81
82-- +goose StatementBegin
83CREATE TRIGGER IF NOT EXISTS history_fts_update AFTER UPDATE ON history BEGIN
84 INSERT INTO history_fts(history_fts, rowid, url, title) VALUES('delete', OLD.id, OLD.url, OLD.title);
85 INSERT INTO history_fts(rowid, url, title) VALUES (NEW.id, NEW.url, NEW.title);
86END;
87-- +goose StatementEnd
88
89INSERT INTO history_fts(rowid, url, title)
90SELECT id, url, title FROM history;