little draft on visit stats

2 files changed, 94 insertions, 0 deletions

diff --git a/webstats/queries.sql b/webstats/queries.sql
new file mode 100644
index 0000000..2a3f762
--- /dev/null
+++ b/webstats/queries.sql
@@ -0,0 +1,21 @@
+#lang cl-yesql/sqlite
+
+-- name: create-stats-table
+-- Creates table for tracking visits
+CREATE TABLE IF NOT EXISTS STATS (
+    timestamp int DEFAULT (unixepoch()) NOT NULL,
+    click text,
+    page text,
+    referrer text,
+    ip text,
+    user_agent text,
+    title text)
+
+-- name: drop-stats-table
+-- Removes table tracking visits
+DROP TABLE IF EXISTS STATS
+
+-- 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)
diff --git a/webstats/server.lisp b/webstats/server.lisp
new file mode 100644
index 0000000..cff23f7
--- /dev/null
+++ b/webstats/server.lisp
@@ -0,0 +1,73 @@
+(ql:quickload '(hunchentoot spinneret cl-yesql cl-yesql/sqlite))
+
+(defpackage :webstats
+  (:use :common-lisp :hunchentoot :spinneret))
+
+(in-package :webstats)
+
+(overlord:set-package-base "/home/titan/dev/scratch/webstats/")
+
+(yesql:import log-queries
+  :from "/home/titan/dev/scratch/webstats/queries.sql"
+  :as :cl-yesql/sqlite
+  :binding :all-functions)
+
+;; (sqlite:with-open-database (db "test.db")
+;;   (drop-stats-table db)
+;;   (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"))
+
+
+(defvar *acceptor* (make-instance 'hunchentoot:easy-acceptor
+                                  :port 4252))
+
+(hunchentoot:start *acceptor*)
+
+(hunchentoot:define-easy-handler (vars-disp :uri "/vars") ()
+  (setf (hunchentoot:content-type*) "text/plain")
+  (format nil "Hey ~{~A ~% ~}~%"
+          (list
+           (remote-addr*)
+           (header-in* :x-forwarded-for)
+           (authorization)
+           (hunchentoot::iso-time)
+           (request-method*)
+           (script-name*)
+           (query-string*)
+           (server-protocol*)
+           (return-code*)
+           (content-length*)
+           (referer)
+           (user-agent))))
+
+(hunchentoot:define-easy-handler (visit :uri "/visit") ()
+  (sqlite:with-open-database (db "test.db")
+    (format nil "you are our visit ~d"
+            (insert db
+                    :click nil
+                    :page (script-name*)
+                    :referer (referer)
+                    :ip (remote-addr*)
+                    :user-agent (user-agent)
+                    :title "try"))
+    ))
+
+
+(hunchentoot:define-easy-handler (link :uri "/link") ()
+  (with-html-string
+    (:doctype)
+    (:html
+     (:header (:title "hu yu ipi"))
+     (:body
+      (:p "learning "
+          (:a :href "vars" "some explodiert")
+          (:a :href "visit" "count visit"))))
+    ))