Compojure and SQLite

Continuing with learning Clojure and Compojure better, I decided I'd wade through some basic DB access. Eric Lavigne has a great post with all the basics. I decided to write a small web app that allows a database to be viewer and (eventually) modified. Again, fairly simple so I don't confuse myself. The full clj file is include at the bottom of the post.

The app starts with some basic definitions:
(ns db-edit
(:require [clojure.contrib.sql :as sql])
(:use clojure.contrib.json.write)
(:use compojure))

Creates a name space for the app called "db-edit", and imports the clojure sql and json libs, as well as the compojure stuff. I'm still not quite sure of the full meaning of clojure name spaces, or the syntax used, but it works.

Next, define the stuff for creating a database connection:
(def db-params {:classname "org.sqlite.JDBC"
:subprotocol "sqlite"
:subname "./database.sqlite"})

The :classname is obviously the class to use for database access. The :subprotocol is the second part of the standard Connection.getConnection url parameter (the form of that url is "jdbc:subprotocol:subname") and is DB driver dependent. The :subname, is of course the third part of the url parameter.

Following the DB definition are some handy definitions for HTTP errors:
(def http-error-map
{400 {:name "Bad Request"
:long "The request could not be understood by the server due to malformed syntax."}
401 {:name "Unauthorized"
:long "The request requires user authentication."} ; Note WWW-Authenticate header should be added
404 {:name "Not Found"
:long "The server has not found anything matching the Request-URI."}})

It's just a map from HTTP error codes to an English name and a longer description of it. Followed by:
(defn error [err]
(if-let [errMap (get http-error-map err)]
{:status err
:headers {}
:body (str "HTTP error " err ": " (get errMap :name) "\n" (get errMap :long))}
{:status err
:headers {}
:body (str "HTTP Error " err " encountered")}))

The error function returns a compojure response for the error. It sets the HTTP status to the error code, and the gives a body with the name and description.

Since there's going to be some static file serving and some user submitted data going into the db, two functions are defined to do a cheap ass sanitization of strings. Although the path sanitizer is probably good enough, the sql one is probably not something you'd in a real production app. Of course, in a real production app you wouldn't be getting table names from the user, so it may be a moot point.
(defn sanitize-sql
"Cleans the string of nasty SQL related special chars"
[s]
(. s (replaceAll "/[\\s'\";#%]/" "")))

(defn cleanPath
"Sanitize the incoming string for '..'"
[p]
(.. p (replaceAll "/\\.+" "/") (replaceAll "/+" "/")))


Now, I'm going to go out of order. Here are the routes the app is going to handle:

• /columns/TableName will return a JSON array with the column names of the table
  
• /table/TableName will return a JSON array of objects. Each object is a row of the table
  
• /tables will return a JSON array with the names of all tables in the database
  
• Anything else is searched for in the ./static directory and served up as a regular file
  

(defroutes my-routes
(ANY "/columns/:table" (get-column-meta (params :table) column-name))
(ANY "/table/:table" (get-table (params :table)))
(ANY "/tables" (get-table-meta table-name))
(ANY "/*" (or (serve-file "./static" (cleanPath (params :*))) :next))
(ANY "/*" (error 400)))

(run-server {:port 8080}
"/*" (servlet my-routes))


The serve-file line is taken pretty much directly out of one of the wikibooks pages. The error line, doesn't do anything unless the file serve fails (that's the point of the :next).

Now, into the database stuff. First up: getting the list of table names:
(defn get-table-meta
"Demonstrate getting table info"
[f]
(sql/with-connection db-params
(json-str (map f
(resultset-seq
(-> (sql/connection)
(.getMetaData)
(.getTables nil nil nil nil)))))))

(defn table-name
"Given a mapstruct from the get-table-meta function, give back a string"
[t]
(:table_name t))

The get-table-meta is pretty much taken directly from the sql contrib docs but you also give it a function to extract only the meta information you need. The getTables method returns (in clojure) a sequence of structmaps. The table-name function just retrieves the table name from the structmap. I'm still not sure what that -> operator is doing... I think it's creating a function, but I'm not sure why the example code did it that way.

Finding the column names of a table is nearly the same:
(defn get-column-meta
"Given a table name, return a collection of the field names"
[t f]
(sql/with-connection db-params
(json-str (map f
(resultset-seq
(-> (sql/connection)
(.getMetaData)
(.getColumns nil nil t nil)))))))

(defn column-name
"Given a mapstruct from the table-col-names, give back the column name"
[c]
(:column_name c))

The main function takes the table name and the filter function (I only column-name is used at the moment), and returns a JSON array of column names. The filter function could return a structmap which includes the type as well and the JSON would be an array of objects.

The final chunk is the function to return an entire table as an array of json objects:
(defn get-table
"Given a table name, return a collection of structmaps for all the data in the table"
[t]
(json-str (sql/with-connection
db-params
(sql/with-query-results
res
[(str "SELECT * FROM " (sanitize-sql t))]
(into [] res)))))

You can't parametrize the table name of an SQL statement, so concatenating the user given table name to the select is a rather unsafe operation. The sanitize-sql is used, but it's not exactly industrial strength security.

That's all there was to it. I'm still not up to speed on Clojure idioms so I still have to look into certain things to see how they work. But putting it together was relatively painless considering I haven't written Java for several years and I'm still new to Clojure. I'm still pleased with Compojure, the goal of the program was quite simple so I hoped the code would be equally simple and I was not disappointed. The code is short and fairly clear, without too many magic chunks.

Next time, a basic UI and probably inserting rows.

Full code:
(ns db-edit
(:require [clojure.contrib.sql :as sql])
(:use clojure.contrib.json.write)
(:use compojure))

; Information about the SQLite database
(def db-params {:classname "org.sqlite.JDBC"
:subprotocol "sqlite"
:subname "./database.sqlite"})

(def http-error-map
{400 {:name "Bad Request"
:long "The request could not be understood by the server due to malformed syntax."}
401 {:name "Unauthorized"
:long "The request requires user authentication."} ; Note WWW-Authenticate header should be added
404 {:name "Not Found"
:long "The server has not found anything matching the Request-URI."}})

(defn error [err]
(if-let [errMap (get http-error-map err)]
{:status err
:headers {}
:body (str "HTTP error " err ": " (get errMap :name) "\n" (get errMap :long))}
{:status err
:headers {}
:body (str "HTTP Error " err " encountered")}))

(defn sanitize-sql
"Cleans the string of nasty SQL related special chars"
[s]
(. s (replaceAll "/[\\s'\";#%]/" "")))

(defn cleanPath
"Sanitize the incoming string for '..'"
[p]
(.. p (replaceAll "/\\.+" "/") (replaceAll "/+" "/")))

(defn get-table-meta
"Demonstrate getting table info"
[f]
(sql/with-connection db-params
(json-str (map f
(resultset-seq
(-> (sql/connection)
(.getMetaData)
(.getTables nil nil nil nil)))))))

(defn get-column-meta
"Given a table name, return a collection of the field names"
[t f]
(sql/with-connection db-params
(json-str (map f
(resultset-seq
(-> (sql/connection)
(.getMetaData)
(.getColumns nil nil t nil)))))))

(defn get-table
"Given a table name, return a collection of structmaps for all the data in the table"
[t]
(json-str (sql/with-connection
db-params
(sql/with-query-results
res
[(str "SELECT * FROM " (sanitize-sql t))]
(into [] res)))))

(defn table-name
"Given a mapstruct from the get-table-meta function, give back a string"
[t]
(:table_name t))

(defn column-name
"Given a mapstruct from the table-col-names, give back the column name"
[c]
(:column_name c))


(defn insert-row [table params] (error 404))

(defroutes my-routes
(POST "/insert/:table" (insert-row (params :table) params))
(ANY "/columns/:table" (get-column-meta (params :table) column-name))
(ANY "/table/:table" (get-table (params :table)))
(ANY "/tables" (get-table-meta table-name))
(ANY "/*" (or (serve-file "./static" (cleanPath (params :*))) :next))
(ANY "/*" (error 400)))

(run-server {:port 8080}
"/*" (servlet my-routes))


Update: The second part includes inserting data, decorators, and middleware.