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