From b9901f220007bda328427db8dcbe822bb89c99bc Mon Sep 17 00:00:00 2001 From: Oscar Najera 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