diff options
| -rw-r--r-- | webstats/queries.sql | 15 | ||||
| -rw-r--r-- | webstats/server.lisp | 19 |
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) |