summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorCarlo Zancanaro <carlo@clearboxsystems.com.au>2013-07-12 13:34:56 +1000
committerCarlo Zancanaro <carlo@clearboxsystems.com.au>2013-07-12 13:34:56 +1000
commit3230be8fa3864d36b1ee6501e44bfdec0b6e2096 (patch)
tree9467e991a704732f143ac4caf19591e6b476f6e7
parent66faf944287ef77715236668236129846d919a89 (diff)
Update the README a bunch
-rw-r--r--README.md210
1 files changed, 144 insertions, 66 deletions
diff --git a/README.md b/README.md
index f95026e..df541f9 100644
--- a/README.md
+++ b/README.md
@@ -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