1-- name: GetRecentHistory :many
2SELECT * FROM history ORDER BY last_visited DESC LIMIT ? OFFSET ?;
3
4-- name: GetHistoryByURL :one
5SELECT * FROM history WHERE url = ? LIMIT 1;
6
7-- name: UpsertHistory :exec
8INSERT INTO history (url, title, favicon_url)
9VALUES (?, ?, ?)
10ON CONFLICT(url)
11DO UPDATE SET
12 visit_count = visit_count + 1,
13 last_visited = CURRENT_TIMESTAMP,
14 title = COALESCE(EXCLUDED.title, history.title),
15 favicon_url = COALESCE(EXCLUDED.favicon_url, history.favicon_url);
16
17-- name: IncrementVisitCount :exec
18UPDATE history
19SET visit_count = visit_count + 1, last_visited = CURRENT_TIMESTAMP
20WHERE url = ?;
21
22-- name: IncrementVisitCountByDelta :exec
23UPDATE history
24SET visit_count = visit_count + ?, last_visited = CURRENT_TIMESTAMP
25WHERE url = ?;
26
27-- name: SearchHistory :many
28SELECT * FROM history
29WHERE url LIKE '%' || ? || '%' OR title LIKE '%' || ? || '%'
30ORDER BY visit_count DESC, last_visited DESC
31LIMIT ?;
32
33-- name: DeleteHistoryByID :exec
34DELETE FROM history WHERE id = ?;
35
36-- name: DeleteHistoryOlderThan :exec
37DELETE FROM history WHERE last_visited < ?;
38
39-- name: DeleteAllHistory :exec
40DELETE FROM history;
41
42-- name: GetHistoryStats :one
43SELECT COUNT(*) as total_entries, COALESCE(SUM(visit_count), 0) as total_visits, COUNT(DISTINCT date(last_visited)) as unique_days FROM history;
44
45-- name: GetDomainStats :many
46SELECT SUBSTR(SUBSTR(url, INSTR(url, '://') + 3), 1, CASE WHEN INSTR(SUBSTR(url, INSTR(url, '://') + 3), '/') > 0 THEN INSTR(SUBSTR(url, INSTR(url, '://') + 3), '/') - 1 ELSE LENGTH(SUBSTR(url, INSTR(url, '://') + 3)) END) as domain, COUNT(*) as page_count, SUM(visit_count) as total_visits, MAX(last_visited) as last_visit FROM history GROUP BY domain ORDER BY total_visits DESC LIMIT ?;
47
48-- name: GetHourlyDistribution :many
49SELECT CAST(strftime('%H', last_visited) AS INTEGER) as hour, COUNT(*) as visit_count FROM history GROUP BY hour ORDER BY hour;
50
51-- name: GetDailyVisitCount :many
52SELECT date(last_visited) as day, COUNT(*) as entries, SUM(visit_count) as visits FROM history WHERE last_visited >= date('now', ?) GROUP BY day ORDER BY day ASC;
53
54-- name: DeleteHistoryByDomain :exec
55DELETE FROM history WHERE url LIKE '%://' || ? || '/%' OR url LIKE '%://' || ? || '?%' OR url LIKE '%://' || ? || '#%' OR url LIKE '%://' || ?;
56
57-- name: SearchHistoryFTSUrl :many
58SELECT h.id, h.url, h.title, h.favicon_url, h.visit_count, h.last_visited, h.created_at
59FROM history_fts fts
60JOIN history h ON fts.rowid = h.id
61WHERE fts.url MATCH @query
62ORDER BY h.visit_count DESC, h.last_visited DESC
63LIMIT @limit;
64
65-- name: SearchHistoryFTSUrlWithDomainBoost :many
66SELECT h.id, h.url, h.title, h.favicon_url, h.visit_count, h.last_visited, h.created_at,
67 CASE
68 WHEN h.url LIKE '%://' || @term || '.%' THEN 2
69 WHEN h.url LIKE '%://%.' || @term || '.%' THEN 2
70 WHEN h.url LIKE '%://' || @term || '/%' THEN 2
71 WHEN h.url LIKE '%://%.' || @term || '/%' THEN 1
72 ELSE 0
73 END as domain_boost
74FROM history_fts fts
75JOIN history h ON fts.rowid = h.id
76WHERE fts.url MATCH @query
77ORDER BY domain_boost DESC, h.visit_count DESC, h.last_visited DESC
78LIMIT @limit;
79
80-- name: SearchHistoryFTSTitle :many
81SELECT h.id, h.url, h.title, h.favicon_url, h.visit_count, h.last_visited, h.created_at
82FROM history_fts fts
83JOIN history h ON fts.rowid = h.id
84WHERE fts.title MATCH @query
85ORDER BY h.visit_count DESC, h.last_visited DESC
86LIMIT @limit;
87
88-- name: GetRecentHistorySince :many
89SELECT * FROM history
90WHERE last_visited >= datetime('now', ?)
91ORDER BY last_visited DESC;
92
93-- name: GetMostVisited :many
94SELECT * FROM history
95WHERE last_visited >= datetime('now', ?)
96ORDER BY visit_count DESC, last_visited DESC;
97
98-- name: GetAllRecentHistory :many
99SELECT * FROM history
100ORDER BY last_visited DESC;
101
102-- name: GetAllMostVisited :many
103SELECT * FROM history
104ORDER BY visit_count DESC, last_visited DESC;
105
106-- name: CapVisitCount :exec
107UPDATE history SET visit_count = ? WHERE url = ? AND visit_count > ?;