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
|
# clojure-sql
A DSL for [Clojure][1] to compose queries using the constructs of
[Relational Algebra][2]. The RA constructs are then compiled into an
SQL query to be run on a DBMS. `clojure-sql` doesn't connect to a
database itself, but can be configured to perform an action when a
query is to be executed.
`clojure-sql` provides some utility functions beyond strict RA to
allow for data to be inserted, updated and deleted.
`clojure-sql` provides no mechanism to create database schemas.
[1]: http://clojure.org/
[2]: http://en.wikipedia.org/wiki/Relational_Algebra
## Leiningen information
Just add the following to your `project.clj` to get started with
`clojure-sql`.
[clojure-sql "0.1.0"]
## 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).
## Usage
In `clojure-sql` expressions it is generally assumed that:
* Clojure keywords represent *column names* (exception: the `table`
function)
* Clojure symbols represent *SQL functions* and *SQL operators*
* Clojure strings represent *literal strings*
* Clojure numbers represent *literal numbers*
Thus: `(and (= :name "Barry") (= (length :username) 10))` compiles
into an SQL expression equivalent to `("name" = 'Barry' AND
"length"("username") = 10)`.
A few more query examples:
(require '[clojure-sql.core :as s])
;; this is only shown as a demonstration
;; a query like this is not advised for general use
(s/table :users)
;; => ["SELECT * FROM \"users\" AS \"users1715\""]
;; it is recommended that a query be projected first to provide
;; clojure-sql with field information
(-> (s/table :users) ;; tables are automatically given internal aliases
(s/project [:id :username]))
;; => ["SELECT \"users3205\".\"id\" AS \"id\", \"users3205\".\"username\" AS \"username\" FROM \"users\" AS \"users3205\""]
(-> (s/table :users)
(s/project [:id :username])
(s/rename {:id :uid})
(s/join (-> (s/table :people)
(s/project {:id :pid, :fname :first})
(s/select '(= :first "Henry")))
:on '(= :uid :pid))
(s/project [:username]))
;; => ["SELECT \"users3430\".\"username\" AS \"username\" FROM (\"users\" AS \"users3430\" INNER JOIN \"people\" AS \"people3432\" ON (\"users3430\".\"id\" = \"people3432\".\"id\")) WHERE (\"people3432\".\"fname\" = ?)" "Henry"]
### clojure.java.jdbc
[`clojure.java.jdbc`][3] support is provided, but must be included
explicitly. `clojure-sql` does not depend on `clojure.java.jdbc`.
(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.
[3]: 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!`).
## Generated queries
In general, `clojure-sql` will try to avoid creating subqueries. For
any of the RA primitives (`select`, `join`, `project`, `rename`) no
subqueries will be introduced. The use of non-RA operations (`group`,
`sort`, `take`/`drop`) may introduce a subquery.
The particular operations which will create a subquery are:
* `join`ing a query which has been `group`ed
* `sort`ing a query which has been `take`n or `drop`ped from
* `group`ing a query which has been previously `group`ed
## License
Copyright © 2013 Carlo Zancanaro
Distributed under the Eclipse Public License, the same as Clojure.
|
