From b9901f220007bda328427db8dcbe822bb89c99bc Mon Sep 17 00:00:00 2001
From: Oscar Najera <hi@oscarnajera.com>
Date: Mon, 10 Feb 2025 13:29:17 +0100
Subject: add new visit row with foreign key preview

---
 webstats/queries.sql | 15 ++++++++++++---
 1 file changed, 12 insertions(+), 3 deletions(-)

(limited to 'webstats/queries.sql')

diff --git a/webstats/queries.sql b/webstats/queries.sql
index 5a8a0ae..383ae22 100644
--- a/webstats/queries.sql
+++ b/webstats/queries.sql
@@ -15,11 +15,20 @@ CREATE TABLE IF NOT EXISTS STATS (
 -- Removes table tracking visits
 DROP TABLE IF EXISTS STATS
 
+-- name: add-ip
+INSERT OR IGNORE INTO source (ip)
+    VALUES (:ip)
+
+-- name: add-user-agent
+INSERT OR IGNORE INTO useragents (name)
+    VALUES (:user_agent)
+
 -- name: insert @last-id
 -- Insert a data point
-INSERT INTO STATS (click, page, referrer, ip, user_agent, title)
-    VALUES (:click, :page, :referer, :ip, :user_agent, :title)
-
+INSERT INTO STATS (click, page, referrer, title, ipid, agentid)
+        VALUES (:click, :page, :referer, :title,
+        (SELECT id FROM source WHERE ip = :ip),
+        (SELECT id FROM useragents WHERE name = :user_agent))
 
 -- name: activity-stats
 -- timeseries of activity
-- 
cgit v1.2.3

