Something went wrong. Try again.
A guide to self-host your app on your local hardware (e.g. Raspberry Pi) with custom domain and SSL pi.arjunkrishna.dev
Something went wrong. Try again.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546-- name: initCREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value INTEGER);INSERT OR IGNORE INTO meta (key, value) VALUES ('total_views', 0);
CREATE TABLE IF NOT EXISTS seen_ips (ip TEXT PRIMARY KEY);
CREATE TABLE IF NOT EXISTS visits ( id INTEGER PRIMARY KEY AUTOINCREMENT, time TEXT NOT NULL, ip TEXT NOT NULL, user_agent TEXT, referrer TEXT, device TEXT, screen TEXT, language TEXT);
-- name: insertVisitINSERT INTO visits (time, ip, user_agent, referrer, device, screen, language)VALUES (?, ?, ?, ?, ?, ?, ?);
-- name: incTotalViewsUPDATE meta SET value = value + 1 WHERE key = 'total_views';
-- name: addSeenIpINSERT OR IGNORE INTO seen_ips (ip) VALUES (?);
-- name: trimVisitsDELETE FROM visits WHERE id NOT IN (SELECT id FROM visits ORDER BY id DESC LIMIT ?);
-- name: getTotalViewsSELECT value FROM meta WHERE key = 'total_views';
-- name: getUniqueVisitorsSELECT COUNT(*) AS n FROM seen_ips;
-- name: getRecentVisitsSELECT id, time, ip, user_agent AS userAgent, referrer, device, screen, languageFROM visits ORDER BY id DESC LIMIT ? OFFSET ?;
-- name: getIpCountsSELECT ip, COUNT(*) AS n FROM visits GROUP BY ip;
-- name: getVisitCountSELECT COUNT(*) AS n FROM visits;