diff options
| -rw-r--r-- | README.md | 210 |
1 files changed, 144 insertions, 66 deletions
@@ -1,77 +1,168 @@ # 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. +`clojure-sql` is a DSL for the creation of SQL queries in [Clojure][1] +code. It emphasises *composability* primarily. [1]: http://clojure.org/ -[2]: http://en.wikipedia.org/wiki/Relational_Algebra -## Leiningen information +## Release -Just add the following to your `project.clj` to get started with -`clojure-sql`. +Latest release: `0.1.0` + +[Leiningen][2] dependency information: [clojure-sql "0.1.0"] -## Database support +## Example Usage -`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). + (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\""] -## Usage + (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"}] -In `clojure-sql` expressions it is generally assumed that: +## Relational Algebra -* 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* +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. -Thus: `(and (= :name "Barry") (= (length :username) 10))` compiles -into an SQL expression equivalent to `("name" = 'Barry' AND -"length"("username") = 10)`. +A basic query on a table is constructed with the `table` function: +`(table :tablename)`. -A few more query examples: +[3]: http://en.wikipedia.org/wiki/Relational_Algebra - (require '[clojure-sql.core :as s]) +### Operations - ;; 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\""] +The primary operations available in `clojure-sql` are the following: - ;; 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\""] +* `select`ion: selecting rows which match a condition - (-> (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"] + (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: + + (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 + + (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 + + (-> (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`: + + (-> (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: + + {:user {:id 5, :username "mange"}} + +* `join`ing: combine two queries into one query + + (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). + + (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: + + (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`][3] support is provided, but must be included +[`clojure.java.jdbc`][4] support is provided, but must be included explicitly. `clojure-sql` does not depend on `clojure.java.jdbc`. (require '[clojure-sql.jdbc :as jdbc]) @@ -85,7 +176,7 @@ explicitly. `clojure-sql` does not depend on `clojure.java.jdbc`. Results are returned from queries in an eager vector. -[3]: https://github.com/clojure/java.jdbc +[4]: https://github.com/clojure/java.jdbc ### Other query executors @@ -101,19 +192,6 @@ of query being executed, and the compiled query (as vector of 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 |