# clojure-sql

`clojure-sql` is a DSL for the creation of SQL queries in [Clojure][1]
code. It has a strong emphasis on *composability* and *abstraction*.

[1]: http://clojure.org/

## Release

Latest release: `0.1.0`

[Leiningen][2] dependency information: 

    :::clojure
    [clojure-sql "0.1.0"]

[2]: https://github.com/technomancy/leiningen

## Example Usage

    :::clojure
    (require '[clojure-sql.core :as s])

    (s/table :users)
    ;; => ["SELECT * FROM \"users\" AS \"users1907\""]

    (-> (s/table :users)
        (s/project [:id :username]))
    ;; => ["SELECT \"users1910\".\"id\" AS \"id\", \"users1910\".\"username\" AS \"username\" FROM \"users\" AS \"users1910\""]

    (require '[clojure-sql.jdbc :as jdbc])
    (jdbc/use-jdbc! "postgres://user:pass@localhost:5432/db")
    
    (-> (s/table :users)
        (s/project [:id :username])
        deref)
    ;; => [{:id 5, :username "username"}]

## Relational Algebra

The constructs and operations available in `clojure-sql` are designed
around those of [Relational Algebra][3]. The majority of operations
take one (or more) queries as arguments and will return a new query as
a result. In this way any transformation can be applied to any query
in a manner which can be easily reasoned about.

A basic query on a table is constructed with the `table` function:
`(table :tablename)`.

[3]: http://en.wikipedia.org/wiki/Relational_Algebra

### Operations

The primary operations available in `clojure-sql` are the following:

* `select`ion: selecting rows which match a condition

        :::clojure
        (s/select (s/table :users) `(= :username "mange"))

    The second argument is a `clojure-sql` expression. This is expressed
    as a quoted Clojure form where the following two assumptions are made:

    * Clojure keywords represent *column names*
    * Clojure symbols represent *SQL functions* and *SQL operators*

    Some more examples of `clojure-sql` expressions and their
    "equivalent" SQL:

        :::clojure
        (let [user-id 5] `(= :user-id ~user-id))
        ;; => "(\"user-id\" = 5)"
        `(and (= :user-id 5) (= :username "mange"))
        ;; => "((\"user-id\" = 5) AND (\"username\" = 'mange'))"
        `(or (= :user-id 10) (= :username "mange"))
        ;; => "((\"user-id\" = 10) OR (\"username\" = 'mange'))"
        `(and (= :name "Barry") (= (length :username) 10))
        ;; => "((\"name\" = 'Barry') AND (\"length\"(\"username\") = 10))

* `project`ion: setting the fields which are exposed by the query

        :::clojure
        (s/project (s/table :users) [:id :username])
        (s/project (s/table :users) {:id :uid, :username :uname}) ;; project and rename in one operation

* `rename`ing: giving an existing field (or fields) a new name

        :::clojure
        (-> (s/table :users)
            (s/project [:id :username])
            (s/rename {:id :uid}))

    Rename can also be given a function which will do the
    transformation. This can be particularly handy when combined with
    the helper functions `as-subobject` and `prefix-names`:

        :::clojure
        (-> (s/table :users)
            (s/project [:id :username])
            (s/rename (s/as-subobject :user)))

    In the final result map fields with a dot will be transformed into
    nested maps. `as-subobject` will put all fields from a query into
    a specified key in the result map. As an example, the above query
    will result in a result map like:

        :::clojure
        {:user {:id 5, :username "mange"}}

* `join`ing: combine two queries into one query

        :::clojure
        (s/join (-> (s/table :users)
                    (s/project [:id :username :person-id]))
                (-> (s/table :people)
                    (s/project [:id :name])
                    (s/rename (s/as-subobject :person)))
                :on `(= :person.id :person-id))

    If a join between two queries has common fields then it is assumed
    to be a natural join (that is: an inner join on common fields).

        :::clojure
        (s/join (-> (s/table :users)
                    (s/project [:id :username :person,id]))
                (-> (s/table :people)
                    (s/project [:id :name])
                    (s/rename (s/as-subobject :person))))

    With joins the composability of `clojure-sql` becomes much more
    useful:

        :::clojure
        (def users (-> (s/table :users)
                       (s/project [:id :username :person])))
                       
        (def people (-> (s/table :people)
                        (s/project [:id :name])))
                        
        (s/join (s/rename users (s/as-subobject :user))
                (s/rename people (s/as-subobject :person))
                :on `(= :user.person :person.id))

#### Set operations

At present `clojure-sql` supports two set operations. These operations
have a very strict requirement that all the sets being operated on
must expose *exactly the same* fields.

* `union`ing
* `intersection`

#### Extra-relational operations

A number of extra-relational operations are also available. They are
equally composable as the above relational operators, but they may
introduce subqueries under some circumstances. (The docstrings should
explain those circumstances for each function.)

These operations will not be explained in detail here, but are
hopefully self explanatory.

* `group`ing/`having`
* `sort`ing
* `take`ing/`drop`ing

## Database support

`clojure-sql` aims to have an extensible compiler. Compilation of
queries is performed by multimethods which dispatch on an arbitrary
(and otherwise unused) `db` parameter. This allows the compilation of
queries to be special-cased per database. By default `clojure-sql`
will produce SQL for PostgreSQL (work on other databases may be
undertaken in future).

### clojure.java.jdbc

[`clojure.java.jdbc`][4] support is provided, but must be included
explicitly. `clojure-sql` does not depend on `clojure.java.jdbc`.

    :::clojure
    (require '[clojure-sql.jdbc :as jdbc])

    (jdbc/use-jdbc! "postgres://user:pass@localhost:5432/db")
    ;; => nil

    (deref (-> (s/table :users)
               (s/project [:id :username])))
    ;; => [{:id 5, :username "username"}]

Results are returned from queries in an eager vector.

[4]: https://github.com/clojure/java.jdbc

### Other query executors

If `clojure.java.jdbc` support is not what you're looking for (or if
you're looking for more control of how `clojure.java.jdbc` is
executed) then you can register a query executor with
`clojure-sql.core/set-query-executor!`. A query executor is a function
which is used to run a query when it is executed.

When a query is executed the executor is passed two things: the type
of query being executed, and the compiled query (as vector of
`[query-string & query-args]`). The query executor's return value will
be the return value of the associated query function call (`deref`,
`insert!`, `update!` or `delete!`).

## License

Copyright © 2013 Carlo Zancanaro

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