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