One global sqlite connection instead of per request

2 files changed, 20 insertions, 14 deletions

diff --git a/webstats/readme.org b/webstats/readme.org
index a720673..9c85733 100644
--- a/webstats/readme.org
+++ b/webstats/readme.org
@@ -1,5 +1,10 @@
 #+title: Track website visit statistics
 
+* change SQLite database to WAL mode
+This is a property of the database not the connection.
+#+begin_src bash
+sqlite3 test.db 'PRAGMA journal_mode=WAL;'
+#+end_src
 * Setup lighttpd
 On the config file append the proxy to the running lisp server
 #+begin_src conf
diff --git a/webstats/server.lisp b/webstats/server.lisp
index b0b18d0..a253b91 100644
--- a/webstats/server.lisp
+++ b/webstats/server.lisp
@@ -40,27 +40,25 @@
 (hunchentoot:define-easy-handler
     (visit :uri "/visit" :default-request-type :both)
     (title page referer click)
-  (sqlite:with-open-database (db "test.db")
-    (format nil "you are our visit ~d"
-            (insert db
-                    :click click
-                    :page page
-                    :referer referer
-                    :ip (remote-addr*)
-                    :user-agent (user-agent)
-                    :title title))))
+  (format nil "you are our visit ~d"
+          (insert *sqlite*
+                  :click click
+                  :page page
+                  :referer referer
+                  :ip (remote-addr*)
+                  :user-agent (user-agent)
+                  :title title)))
 
 
 (hunchentoot:define-easy-handler (stat-js :uri "/stats.js") ()
   (setf (hunchentoot:content-type*) "text/javascript")
   (ps:ps-compile-file "stats.paren"))
 
-(hunchentoot:define-easy-handler (metric :uri "/metric.json") (q)
+(hunchentoot:define-easy-handler (metric :uri "/metric.json") ()
   (setf (hunchentoot:content-type*) "application/json")
-  (sqlite:with-open-database (db "test.db")
-    (let ((series (activity-stats db)))
-      (cl-json:encode-json-to-string
-       (apply #'mapcar #'list series)))))
+  (let ((series (activity-stats *sqlite*)))
+    (cl-json:encode-json-to-string
+     (apply #'mapcar #'list series))))
 
 (hunchentoot:define-easy-handler (graphs :uri "/graphs") ()
   (with-html-string
@@ -86,10 +84,12 @@
                        (then #'plot)))))))))))
 
 (defvar *acceptor*)
+(defvar *sqlite*)
 
 (defun start-server (port)
   (setf *acceptor*
         (make-instance 'hunchentoot:easy-acceptor :port port))
+  (setf *sqlite* (sqlite:connect "test.db"))
   (hunchentoot:start *acceptor*))
 
 (defun main ()
@@ -110,6 +110,7 @@
        () (progn
             (format *error-output* "Aborting.~&")
             (hunchentoot:stop *acceptor*)
+            (sqlite:disconnect *sqlite*)
             (uiop:quit)))
       (error (c) (format t "Woops, an unknown error occured:~&~a~&" c)))))