jkk/honeysql — pull request #26 by Menthalion (2014-08-18 21:11:07+00:00)
Title: Support for T-SQL style DELETE join and ANSI OVER clause

I recently started to use HoneySQL and love its simplicity and composability. For a project I'm currently working on I needed some extra clauses and after getting them working I thought it might be nice to contribute back. This is my first Github pull request, but I did my best to implement all formatters, helpers and builders that were needed.

First commit is simply support for a `delete` clause that can take a `from` clause. Microsoft SQL supports a special syntax for `update` and `delete` for joined tables by giving in several table/alias pairs in the `from` clause, referencing one of the aliases in the `update` or `delete`, and performing the join in the `where` clause. The `update` syntax can quite easily be used in standard HoneySQL, but `:delete-from` only accepts one table. A separate `delete` that takes a `from` clause mirrors `update`s existing flexibility in this.

The second and third commit are slightly more involved and adds support for the [ANSI '99 `over` clause][1]. It adds support for windowed aggregates over (ordered) partitions. For example
```SQL
SELECT
person.id as source_id,
person.name,
count(*) OVER (PARTITION BY name) AS double_count,
rank() OVER (PARTITION BY name ORDER BY date) AS ranking,
first_value(id) OVER (PARTITION BY name ORDER BY date) AS target_id
FROM person
```
This can be created by using the following syntax
```clj
{:select [[{:aggregate [:%first_value.id]
:over {:partition-by [:name] :order-by [:date]}} :target-id]]}
```
The `:aggregate` clause was added because creating formatters, helpers and builders were getting too complex to support `{:over [:%first_value.id {partition-by :name order-by :creation_date}]}`. With `:aggregate` everything seemed to fit existing code better.

Here's an example with helper functions
```clj
(honeysql.core/format (select (-> (over (-> (spartition-by :name)
(order-by :date)))
(aggregate (honeysql.core/call :first_value :id)))))
```

Notes:
* `:partition-by` helper name was changed to `spartition-by` to prevent name clash with the core fn. Perhaps `merge-partition-by` should match that?
* `aggregate` helper function forces first over the function parameter to prevent aliasing.

Suggestions:
* The hardest part in adding these clauses was figuring out the destructuring signatures of `defmethod format-clause`, `defhelper` and `defmethod build-clause`. Annotating them in code or readme might help people get up to speed faster and contribute clauses more easily.
* Another tough part was the interaction between `defhelper`, `defmethod build clause` and additional `defn *clausename*`.

[1]: http://msdn.microsoft.com/en-us/library/ms189461.aspx