1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
|
# 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.
|
