1-- name: GetRecentHistory :many
2SELECT * FROM history
3ORDER BY last_visited DESC, id DESC
4LIMIT ? OFFSET ?;
5
6-- name: GetRecentHistorySinceCutoff :many
7-- Bounded window variant: the direct comparison keeps the last_visited index
8-- usable. Rows with a NULL last_visited are intentionally excluded.
9SELECT * FROM history
10WHERE last_visited >= @cutoff
11ORDER BY last_visited DESC, id DESC
12LIMIT @limit OFFSET @offset;
13
14-- name: GetRecentHistoryByDomain :many
15SELECT * FROM history
16WHERE domain = @domain
17ORDER BY last_visited DESC, id DESC
18LIMIT @limit OFFSET @offset;
19
20-- name: GetAllRecentHistoryByDomain :many
21SELECT * FROM history
22WHERE domain = @domain
23ORDER BY last_visited DESC, id DESC;
24
25-- name: GetRecentHistoryWindow :many
26SELECT * FROM history
27WHERE last_visited < @before OR (last_visited = @before AND id < @before_id)
28ORDER BY last_visited DESC, id DESC
29LIMIT @limit;
30
31-- name: GetRecentHistoryWindowByDomain :many
32SELECT * FROM history
33WHERE domain = @domain AND (last_visited < @before OR (last_visited = @before AND id < @before_id))
34ORDER BY last_visited DESC, id DESC
35LIMIT @limit;
36
37-- name: GetHistoryByURL :one
38SELECT * FROM history WHERE url = ? LIMIT 1;
39
40-- name: UpsertHistory :exec
41INSERT INTO history (url, title, favicon_url, domain)
42VALUES (?, ?, ?, ?)
43ON CONFLICT(url)
44DO UPDATE SET
45 visit_count = visit_count + 1,
46 last_visited = CURRENT_TIMESTAMP,
47 title = COALESCE(EXCLUDED.title, history.title),
48 favicon_url = COALESCE(EXCLUDED.favicon_url, history.favicon_url),
49 domain = COALESCE(EXCLUDED.domain, history.domain);
50
51-- name: IncrementVisitCount :exec
52UPDATE history
53SET visit_count = visit_count + 1, last_visited = CURRENT_TIMESTAMP
54WHERE url = ?;
55
56-- name: IncrementVisitCountByDelta :exec
57UPDATE history
58SET visit_count = visit_count + ?, last_visited = CURRENT_TIMESTAMP
59WHERE url = ?;
60
61-- name: SearchHistory :many
62SELECT * FROM history
63WHERE url LIKE '%' || ? || '%' OR title LIKE '%' || ? || '%'
64ORDER BY visit_count DESC, last_visited DESC
65LIMIT ?;
66
67-- name: DeleteHistoryByID :exec
68DELETE FROM history WHERE id = ?;
69
70-- name: DeleteHistoryOlderThan :exec
71DELETE FROM history WHERE last_visited < ?;
72
73-- name: DeleteHistorySince :exec
74DELETE FROM history WHERE last_visited >= ?;
75
76-- name: DeleteAllHistory :exec
77DELETE FROM history;
78
79-- name: GetHistoryStats :one
80SELECT
81 COUNT(*) as total_entries,
82 COALESCE(SUM(visit_count), 0) as total_visits,
83 COUNT(DISTINCT date(last_visited)) as unique_days
84FROM history;
85
86-- name: GetDomainStats :many
87SELECT
88 COALESCE(domain, '') as domain,
89 COUNT(*) as page_count,
90 SUM(visit_count) as total_visits,
91 MAX(last_visited) as last_visit
92FROM history
93WHERE domain IS NOT NULL AND domain != ''
94GROUP BY domain
95ORDER BY total_visits DESC
96LIMIT ?;
97
98-- name: GetHourlyDistribution :many
99SELECT
100 CAST(strftime('%H', last_visited) AS INTEGER) as hour,
101 COUNT(*) as visit_count
102FROM history
103GROUP BY hour
104ORDER BY hour;
105
106-- name: GetDailyVisitCount :many
107SELECT
108 date(last_visited) as day,
109 COUNT(*) as entries,
110 SUM(visit_count) as visits
111FROM history
112WHERE last_visited >= date('now', ?)
113GROUP BY day
114ORDER BY day ASC;
115
116-- name: DeleteHistoryByDomain :exec
117DELETE FROM history WHERE domain = @domain;
118
119-- name: SearchHistoryFTSUrlWithDomainBoost :many
120SELECT h.id, h.url, h.title, h.favicon_url, h.visit_count, h.last_visited, h.created_at,
121 CASE
122 WHEN h.url LIKE '%://' || @term || '.%' THEN 2
123 WHEN h.url LIKE '%://%.' || @term || '.%' THEN 2
124 WHEN h.url LIKE '%://' || @term || '/%' THEN 2
125 WHEN h.url LIKE '%://%.' || @term || '/%' THEN 1
126 ELSE 0
127 END as domain_boost
128FROM history_fts fts
129JOIN history h ON fts.rowid = h.id
130WHERE fts.url MATCH @query
131 AND (@cutoff IS NULL OR h.last_visited >= @cutoff)
132ORDER BY domain_boost DESC, h.visit_count DESC, h.last_visited DESC
133LIMIT @limit;
134
135-- name: SearchHistoryFTSTitle :many
136SELECT h.id, h.url, h.title, h.favicon_url, h.visit_count, h.last_visited, h.created_at, h.domain
137FROM history_fts fts
138JOIN history h ON fts.rowid = h.id
139WHERE fts.title MATCH @query
140 AND (@cutoff IS NULL OR h.last_visited >= @cutoff)
141ORDER BY h.visit_count DESC, h.last_visited DESC
142LIMIT @limit;
143
144-- name: GetRecentHistorySince :many
145SELECT * FROM history
146WHERE last_visited >= datetime('now', ?)
147ORDER BY last_visited DESC;
148
149-- name: GetMostVisited :many
150SELECT * FROM history
151WHERE last_visited >= datetime('now', ?)
152ORDER BY visit_count DESC, last_visited DESC;
153
154-- name: GetAllRecentHistory :many
155SELECT * FROM history
156ORDER BY last_visited DESC, id DESC;
157
158-- name: GetAllMostVisited :many
159SELECT * FROM history
160ORDER BY visit_count DESC, last_visited DESC;
161
162-- name: GetMostVisitedHistory :many
163SELECT * FROM history
164ORDER BY visit_count DESC, last_visited DESC
165LIMIT @limit;
166
167-- name: GetMostVisitedHistorySinceCutoff :many
168-- Bounded window variant: the direct comparison keeps the last_visited index
169-- usable. Rows with a NULL last_visited are intentionally excluded.
170SELECT * FROM history
171WHERE last_visited >= @cutoff
172ORDER BY visit_count DESC, last_visited DESC
173LIMIT @limit;
174
175-- name: CapVisitCount :exec
176UPDATE history SET visit_count = ? WHERE url = ? AND visit_count > ?;