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: SearchHistoryFTSTitle :many
61SELECT h.id, h.url, h.title, h.favicon_url, h.visit_count, h.last_visited, h.created_at
62FROM history_fts fts
63JOIN history h ON fts.rowid = h.id
64WHERE fts.title MATCH @query
65ORDER BY h.visit_count DESC, h.last_visited DESC
66LIMIT @limit;
67
68-- name: GetRecentHistorySince :many
69SELECT * FROM history
70WHERE last_visited >= datetime('now', ?)
71ORDER BY last_visited DESC;
72
73-- name: GetMostVisited :many
74SELECT * FROM history
75WHERE last_visited >= datetime('now', ?)
76ORDER BY visit_count DESC, last_visited DESC;
77
78-- name: GetAllRecentHistory :many
79SELECT * FROM history
80ORDER BY last_visited DESC;
81
82-- name: GetAllMostVisited :many
83SELECT * FROM history
84ORDER BY visit_count DESC, last_visited DESC;