sql-finder

1.0.0


Simple library for create clojure.java.jdbc queries

dependencies

org.clojure/clojure
1.3.0
midje
1.4.0

dev dependencies

lein-midje
1.0.9
lein-clojars
0.9.0
lein-marginalia
0.7.1



(this space intentionally left almost blank)
 

The core namespace exposes the main API functions for creating queries. It pulls in the functionality for building the different parts of the query from the other project namespaces.

(ns 
  finder.core
  (require [finder.opts :as opts]
           [finder.where :as where]
           [finder.params :as params]))

Query wraps up all the query building and is called by the main API functions. It takes a table name, some parameters, and some options. Then returns a vector that can be used in a JDBC query.

(defn- 
  query [tbl params options]
  (let [where (where/get-where params)
        args (params/get-params params)
        opts (opts/get-options options)
        sql (format " select * from %s%s%s " (name tbl) where opts)]
    (apply vector
      (concat [sql] args))))

Public

The API consists mainly of the 'where' function, with a few others that provide a slightly more meaningful syntax for particular queries, like finding by an ID.

The general structure for the functions is to take a table name, a series of parameters as the second argument, and then some options like ordering for the last parameter.

Find all records from table matching the parameters. The parameters should be a map or vector of maps. Last argument is the options map.

(defn 
  where
  ([tbl params] (where tbl params {}))
  ([tbl params opts] (query tbl params opts)))

Find records matching a single field and value. Can also take query options for order, limit and offset.

(defn 
  by
  ([tbl fld id] (by tbl fld id {}))
  ([tbl fld id opts] (where tbl (hash-map fld id) opts)))

Does a simple find on a table by the 'id' column.

(defn 
  by-id
  [tbl id]
  (by tbl "id" id))

Does a find all on a table to return every result by default, but can be given the standard options to order, limit and offset too.

(defn 
  all
  ([tbl] (all tbl {}))
  ([tbl opts] (where tbl {} opts)))
 

This namespace deals with the query options like order, limit and offset

(ns 
  finder.opts
  (:require [clojure.string :as string]))

Destructures a name/value vector and returns a single order clause as a string. ie. 'name desc'

(defn- 
  to-order-clause [[col dir]]
  (format "%s %s" (name col)
                  (name dir)))

Takes an order by column, or series of columns and builds the order by statement as a string to return.

(defn- 
  get-order [order]
  (let [orders (if (map? order) order 
                   (hash-map order :desc))]
    (str " order by "
      (string/join ", "
        (map to-order-clause orders)))))

Checks the options data to see if an option is present, and if it is then passes it to its handler function, returning the result.

(defn- 
  to-option [opts [opt func]]
  (if-let [data (get opts opt)]
    (func data) ""))

Public

Takes the 'options' map of information about ordering, limit and offset. Then returns a string for those parts of the SQL query, in the correct order.

(defn 
  get-options [opts]
  (let [clause #(format " %s %d" %1 %2)]
    (string/join ""
      (map (partial to-option opts)         
           {:order get-order
            :limit (partial clause "limit")
            :offset (partial clause "offset")}))))
 

This namespace handles extracting all the values from the query parameters that need to be bound by the prepared statement in JDBC before the query is executed.

(ns 
  finder.params)

Extract the value for an individual parameter, that could be a value, a set of values, or a vector comparator

(defn- 
  to-param [[_ value]]
  (cond
    (set? value) (apply vector value)
    (vector? value) (second value)
    :else value))

Public

Fetch all the parameter values from the specified parameters. These parameters could be values, sets, or comparator vectors. Returns an ordered vector of the parameters as they are specified.

(defn 
  get-params [params]
  (let [param-vec (if (vector? params) params
                      (vector params))
        to-params #(concat %1 (map to-param %2))]
    (flatten
      (reduce to-params [] param-vec))))
 

This namespace handles turning the query parameters into the 'where' part of the SQL query.

(ns 
  finder.where
  (:require [clojure.string :as string]))
(declare to-where-params)

Creates a pseudo 'where in' clause using a series of comparisons for each value instead (JDBC driver does not support binding values to 'in')

(defn- 
  to-where-in [fld value]
  (format "(%s)"
    (to-where-params
      (map (partial hash-map fld) value))))

Formats a single where clause, using = as the default operator, or the one specified by the value vector. eg. ['< 12]

(defn- 
  to-where [fld value]
  (let [operator (if (vector? value)
                     (first value)
                     "=")]
    (format "%s %s ?" (name fld) operator)))

Formats a single where clause part, which could either be a single fld/value parameter, or a 'where in' clause using a set.

(defn-  
  to-where-clause [[fld value]]
    (cond
      (set? value) (to-where-in fld value)
      :else (to-where fld value)))

Takes a bunch of param groups, joins them with ORs.

(defn- 
  to-where-params [groups]
  (let [to-group #(format "(%s)"
                   (string/join " and "
                     (map to-where-clause %)))]
    (string/join " or "
      (map to-group groups))))

Public

Fetch the 'where' sql clause and return it as a string.

(defn 
  get-where [params]
  (let [to-vector #(if (vector? %) % (vector %))]
    (if (empty? params) ""
        (format " where %s"
                (to-where-params (to-vector params))))))