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: SearchHistory :many
23SELECT * FROM history
24WHERE url LIKE '%' || ? || '%' OR title LIKE '%' || ? || '%'
25ORDER BY visit_count DESC, last_visited DESC
26LIMIT ?;
27
28-- name: DeleteHistoryByID :exec
29DELETE FROM history WHERE id = ?;
30
31-- name: DeleteHistoryOlderThan :exec
32DELETE FROM history WHERE last_visited < ?;
33
34-- name: DeleteAllHistory :exec
35DELETE FROM history;
36
37-- name: GetHistoryStats :one
38SELECT COUNT(*) as total_entries, COALESCE(SUM(visit_count), 0) as total_visits, COUNT(DISTINCT date(last_visited)) as unique_days FROM history;
39
40-- name: GetDomainStats :many
41SELECT 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 ?;
42
43-- name: GetHourlyDistribution :many
44SELECT CAST(strftime('%H', last_visited) AS INTEGER) as hour, COUNT(*) as visit_count FROM history GROUP BY hour ORDER BY hour;
45
46-- name: GetDailyVisitCount :many
47SELECT 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;
48
49-- name: DeleteHistoryByDomain :exec
50DELETE FROM history WHERE url LIKE '%://' || ? || '/%' OR url LIKE '%://' || ? || '?%' OR url LIKE '%://' || ? || '#%' OR url LIKE '%://' || ?;
51
52-- name: SearchHistoryFTSUrl :many
53SELECT h.id, h.url, h.title, h.favicon_url, h.visit_count, h.last_visited, h.created_at
54FROM history_fts fts
55JOIN history h ON fts.rowid = h.id
56WHERE fts.url MATCH @query
57ORDER BY h.visit_count DESC, h.last_visited DESC
58LIMIT @limit;
59
60-- name: SearchHistoryFTSUrlWithDomainBoost :many
61SELECT h.id, h.url, h.title, h.favicon_url, h.visit_count, h.last_visited, h.created_at,
62 CASE
63 WHEN h.url LIKE '%://' || @term || '.%' THEN 2
64 WHEN h.url LIKE '%://%.' || @term || '.%' THEN 2
65 WHEN h.url LIKE '%://' || @term || '/%' THEN 2
66 WHEN h.url LIKE '%://%.' || @term || '/%' THEN 1
67 ELSE 0
68 END as domain_boost
69FROM history_fts fts
70JOIN history h ON fts.rowid = h.id
71WHERE fts.url MATCH @query
72ORDER BY domain_boost DESC, h.visit_count DESC, h.last_visited DESC
73LIMIT @limit;
74
75-- name: SearchHistoryFTSTitle :many
76SELECT h.id, h.url, h.title, h.favicon_url, h.visit_count, h.last_visited, h.created_at
77FROM history_fts fts
78JOIN history h ON fts.rowid = h.id
79WHERE fts.title MATCH @query
80ORDER BY h.visit_count DESC, h.last_visited DESC
81LIMIT @limit;
82
83-- name: GetRecentHistorySince :many
84SELECT * FROM history
85WHERE last_visited >= datetime('now', ?)
86ORDER BY last_visited DESC;
87
88-- name: GetMostVisited :many
89SELECT * FROM history
90WHERE last_visited >= datetime('now', ?)
91ORDER BY visit_count DESC, last_visited DESC;
92
93-- name: GetAllRecentHistory :many
94SELECT * FROM history
95ORDER BY last_visited DESC;
96
97-- name: GetAllMostVisited :many
98SELECT * FROM history
99ORDER BY visit_count DESC, last_visited DESC;
100
101-- name: CapVisitCount :exec
102UPDATE history SET visit_count = ? WHERE url = ? AND visit_count > ?;