add new visit row with foreign key previewwebstats

2 files changed, 24 insertions, 10 deletions

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
diff --git a/webstats/server.lisp b/webstats/server.lisp
index 3fe237a..5df77b3 100644
--- a/webstats/server.lisp
+++ b/webstats/server.lisp
@@ -29,13 +29,18 @@
 ;;   (create-stats-table db))
 
 ;; (sqlite:with-open-database (db "test.db")
-;;   (insert db
-;;           :click nil
-;;           :page "ho"
-;;           :referer "ref"
-;;           :ip "13"
-;;           :user-agent "sly"
-;;           :title "try"))
+;;   (sqlite:with-transaction db
+;;     (let ((ip "122.0.0.1")
+;;           (agent "sly"))
+;;       (add-ip db :ip ip)
+;;       (add-user-agent db :user-agent agent)
+;;       (insert db
+;;               :click nil
+;;               :page "ho"
+;;               :referer "ref"
+;;               :ip ip
+;;               :user-agent agent
+;;               :title "try"))))
 
 (hunchentoot:define-easy-handler
     (visit :uri "/visit" :default-request-type :both)