sql-finder1.0.0Simple library for create clojure.java.jdbc queries dependencies
dev dependencies
| (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)))) | |||||||||||||||
PublicThe 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)))))) | |||||||||||||||