java-jdbc/dsl

Basic DSL for SQL/DDL generation formerly part of org.clojure/java.jdbc (0.3.0).

This DSL was grown here in this repository as an experiment for inclusion in the java.jdbc contrib library but community feedback indicated that adding a DSL to java.jdbc caused confusion and was unnecessary. In particular, this DSL wasn't very sophisticated and I had no plans to make it sophisticated. Projects like HoneySQL and SQLingvo are always going to be better DSLs.

Because this DSL is being removed from org.clojure/java.jdbc, the java.jdbc.sql and java.jdbc.ddl namespaces have been moved to this external project so that anyone using the DSL can continue doing so by simply switching to the java-jdbc.sql and java-jdbc.ddl namespaces from this project instead.

Releases and Dependency Information

Latest stable release: 0.1.3:

• All Released Versions

Leiningen dependency information:

[java-jdbc/dsl "0.1.3"]

Maven dependency information:

<dependency>
  <groupId>java-jdbc</groupId>
  <artifactId>dsl</artifactId>
  <version>0.1.3</version>
</dependency>

Usage

Generate SQL and parameters that can be used in query and execute expressions:

(select * :person)
;;=> ("SELECT * FROM person")
(select [:id :name] :person
  (where {:email "user@domain.com" :sex ["M", "F"]}))
;;=> ("SELECT id,name FROM person WHERE email = ? AND sex IN (?, ?)" "user@domain.com" "M", "F")
(select [{:p.id :userid} :p.name :a.city] {:person :p}
  (join {:address :a} {:p.id :a.personid})
  (where {:p.email "user@domain.com"}))
;;=> ("SELECT p.id AS userid,p.name,a.city FROM person p JOIN address a ON p.id = a.personid
;;     WHERE p.email = ?" "user@domain.com")
(select [:id :name] :person
  (order-by :name))
;;=> ("SELECT id,name FROM person ORDER BY name ASC")
(update :person {:status "active"})
;;=> ("UPDATE person SET status = ?" "active")
(update :person {:status "suspended"}
  (where {:country "NG"}))
;;=> ("UPDATE person SET status = ? WHERE country = ?" "suspended" "NG")
(delete :person (where {:id 42}))
;;=> ("DELETE FROM person WHERE id = ?" 42)

select expects a column-spec, a table-spec, optional join-clauses (as strings), an optional where-clause (sequence of SQL conditions as a string followed by parameters). It returns a sequence whose first element is a string containing a SQL SELECT statement and whose remaining elements are the values to be substituted for parameters (?) in that string.

A column-spec may be *, a single column or a sequence of columns. A column may be a string or keyword, or a map (of a single key to a single value, that specifies a column alias).

A table-spec may be a string or a keyword, or a map (of a single key to a single value, that specifies a table alias).

A join-clause is just a string containing a SQL JOIN clause. It can be generated by the join function.

A where-clause is a sequence whose first element is a string containing SQL conditions and whose remaining elements are the values to be substituted for parameters (?) in that string. It can be generated by the where function.

delete expects a table-spec and a where-clause (which is not optional). It returns a sequence whose first element is a string containing a SQL DELETE statement and whose remaining elements are the values to be substituted for parameters (?) in that string.

insert expects a table-spec and either column-names followed by values or value-maps representing rows.

join expects a table-spec and a join-map which is used to generate the ON clause. It returns a string containing a SQL JOIN/ON clause.

A join-map is a map whose keys and values represent columns in the two tables being joined.

where expects a value-map which is used to generate a string that contains the conditional part of a WHERE clause and the values to be substituted for parameters (?) within that string. It returns a sequence whose first element is the string and whose remaining elements are the parameter values.

order-by expects a sequence of col-sorts. A col-sort is either string or keyword representing a column to sort on (ascending), or a map with one key / value pair that specifies a column name and a sort direction (:asc, :desc).

update expects a table-spec, an update-map and an optional where-clause. It returns a sequence whose first element is a string containing a SQL UPDATE statement and whose remaining elements are the values to be substituted for parameters (?) in that string.

An update-map is a map whose keys represent columns to be set to the corresponding values.

All functions that generate SQL may have an optional :entities keyword argument (after the specified arguments) which specifies an identifier naming convention, e.g., (quoted \)** which would cause SQL entities to be quoted with **\ (:id would become `id`). Other built-in naming conventions as as-is and lower-case.

The entities macro can be used to apply an identifier naming convention to a DSL expression. It expects a function representing the naming convention and a DSL expression. It post-walks the DSL expression and inserts the :entities keyword argument and naming convention at the end of each expression.

Changees

• 0.1.3 2015-06-10 Update to support Clojure 1.7.0-RC1 (conflict with update).
• 0.1.2 2015-04-13 Add support for more WHERE clauses (@kofrasa).
• 0.1.1 2014-11-27 Add support for IN (@dryewo); bump base Clojure version to 1.6.0
• 0.1.0 2013-11-24 Initial version extracted from java.jdbc

License

Copyright (c) 2012-2015 Sean Corfield

Distributed under the Eclipse Public License, the same as Clojure.