diff options
| -rw-r--r-- | README.md | 8 | ||||
| -rw-r--r-- | src/clojure_sql/compiler.clj | 75 | ||||
| -rw-r--r-- | src/clojure_sql/core.clj | 35 | ||||
| -rw-r--r-- | src/clojure_sql/dsl.clj | 36 | ||||
| -rw-r--r-- | src/clojure_sql/jdbc.clj | 12 |
5 files changed, 91 insertions, 75 deletions
@@ -20,7 +20,7 @@ allow for data to be inserted, updated and deleted. queries is performed by multimethods which dispatch on an arbitrary (and otherwise unused) `db` parameter. This allows the compilation of queries to be entirely special-cased per database. By default -`clojure-sql` will produce standard ANSI SQL. +`clojure-sql` will produce SQL for PostgreSQL. ## Usage @@ -39,8 +39,10 @@ queries to be entirely special-cased per database. By default '(= :uid :pid)) (s/project [:username])) ; => ["SELECT \"u\".\"username\" FROM \"users\" AS \"u\" JOIN \"people\" AS \"p\" ON (\"u\".\"id\" = \"p\".\"id\") WHERE (\"p\".\"fname\" = ?)" "Henry")] - - (s/use-jdbc! "postgres://user:pass@localhost:5432/db") + + (require '[clojure-sql.jdbc :as jdbc]) + ; => nil + (jdbc/use-jdbc! "postgres://user:pass@localhost:5432/db") ; => nil diff --git a/src/clojure_sql/compiler.clj b/src/clojure_sql/compiler.clj index 5bec649..0cbb808 100644 --- a/src/clojure_sql/compiler.clj +++ b/src/clojure_sql/compiler.clj @@ -21,16 +21,16 @@ ;; DB specific escaping methods ;; ============================================================== -(defmulti field-name (fn [db _] db)) -(defmethod field-name :default [_ field] +(defmulti field-name (fn [db _] db) :default :postgres) +(defmethod field-name :postgres [_ field] (str \" (name field) \")) -(defmulti table-name (fn [db _] db)) -(defmethod table-name :default [_ table] +(defmulti table-name (fn [db _] db) :default :postgres) +(defmethod table-name :postgres [_ table] (str \" (name table) \")) -(defmulti function-name (fn [db _] db)) -(defmethod function-name :default [_ function] +(defmulti function-name (fn [db _] db) :default :postgres) +(defmethod function-name :postgres [_ function] (str \" (name function) \")) @@ -70,15 +70,15 @@ (declare compile-query compile-expression) -(defmulti compile-expression-list (fn [db _] db)) -(defmethod compile-expression-list :default [db ex] +(defmulti compile-expression-list (fn [db _] db) :default :postgres) +(defmethod compile-expression-list :postgres [db ex] (->> (map (partial compile-expression db) ex) (apply sequence) ((p-lift string/join ",")) $add-parentheses)) -(defmulti compile-expression-sequential (fn [db _] db)) -(defmethod compile-expression-sequential :default [db ex] +(defmulti compile-expression-sequential (fn [db _] db) :default :postgres) +(defmethod compile-expression-sequential :postgres [db ex] (let [compile-exprs #(map (partial compile-expression db) %) op-name (operator-name (first ex)) num-args (dec (count ex))] @@ -113,8 +113,8 @@ (add-parentheses (string/join "," vals)))))) $add-parentheses))) -(defmulti compile-expression (fn [db _] db)) -(defmethod compile-expression :default [db ex] +(defmulti compile-expression (fn [db _] db) :default :postgres) +(defmethod compile-expression :postgres [db ex] (condp u/funcall ex boolean? (return (string/upper-case (str ex))) query? ($add-parentheses (compile-query db ex)) @@ -144,8 +144,8 @@ (return " AS ") (return (field-name db alias))))) -(defmulti compile-fields (fn [db _] db)) -(defmethod compile-fields :default [db fields-map] +(defmulti compile-fields (fn [db _] db) :default :postgres) +(defmethod compile-fields :postgres [db fields-map] (if (seq fields-map) (->> (for [[alias field] fields-map] (make-field-name db field alias)) @@ -159,8 +159,8 @@ :full-outer "FULL OUTER" :cross "CROSS"}) -(defmulti compile-tables (fn [db _ _] db)) -(defmethod compile-tables :default [db join tables-map] +(defmulti compile-tables (fn [db _ _] db) :default :postgres) +(defmethod compile-tables :postgres [db join tables-map] (if (vector? join) (->> (for [table-alias join] (make-table-name db (get tables-map table-alias) table-alias)) @@ -183,14 +183,14 @@ (return "TRUE")) (return ")")))))) -(defmulti compile-where (fn [db _] db)) -(defmethod compile-where :default [db expr] +(defmulti compile-where (fn [db _] db) :default :postgres) +(defmethod compile-where :postgres [db expr] (if expr ($str (return " WHERE ") (compile-expression db expr)) (return nil))) -(defmulti compile-sort (fn [db _] db)) -(defmethod compile-sort :default [db fields] +(defmulti compile-sort (fn [db _] db) :default :postgres) +(defmethod compile-sort :postgres [db fields] (if fields (->> (for [[[table field] dir] fields] ($str (make-field-name db [table field]) @@ -200,8 +200,8 @@ ($str (return " ORDER BY "))) (return nil))) -(defmulti compile-group (fn [db _] db)) -(defmethod compile-group :default [db fields] +(defmulti compile-group (fn [db _] db) :default :postgres) +(defmethod compile-group :postgres [db fields] (if fields (->> (for [[table field] fields] (make-field-name db [table field])) @@ -210,14 +210,21 @@ ($str (return " GROUP BY "))) (return nil))) -(defmulti compile-having (fn [db _] db)) -(defmethod compile-having :default [db expr] +(defmulti compile-having (fn [db _] db) :default :postgres) +(defmethod compile-having :postgres [db expr] (if expr ($str (return " HAVING ") (compile-expression db expr)) (return nil))) -(defmulti compile-query (fn [db _] db)) -(defmethod compile-query :default [db {:keys [tables fields joins where sort group having union]}] +(defmulti compile-limit (fn [db _ _] db) :default :postgres) +(defmethod compile-limit :postgres [db take drop] + (return (str (if take + (str " LIMIT " take)) + (if drop + (str " OFFSET " drop))))) + +(defmulti compile-query (fn [db _] db) :default :postgres) +(defmethod compile-query :postgres [db {:keys [tables fields joins where sort group having union take drop]}] (if union (->> union (map (partial compile-query db)) @@ -232,23 +239,19 @@ (compile-where db where) (compile-group db group) (compile-having db having) - (compile-sort db sort)))) + (compile-sort db sort) + (compile-limit db take drop)))) -(defn compile [db query] +(defn compile-select [db query] (let [[sql vars] ((compile-query db query) [])] (vec (cons sql vars)))) -;; Utility functions - -(defn ^:private build-insertion [db fields record] - ) - -(defn insert! [db {:keys [fields tables joins]} & records] +(defn compile-insert [db {:keys [fields tables joins]} & records] (assert (= (count tables) 1) "Cannot insert into a multiple-table query") (let [fields-order (map key fields) wrap #(str "INSERT INTO " @@ -275,7 +278,7 @@ ;; {:uid 10, :name :carlo, :other :stuff} ;; {:uid 1, :name :carl, :other :stuf}) -(defn update! [db {:keys [tables fields where joins]} partial-record] +(defn compile-update [db {:keys [tables fields where joins]} partial-record] (assert (= (count tables) 1) "Cannot delete from a multiple-table query") (assert (seq (set/intersection (set (keys partial-record)) (set (keys fields)))) "At least one field must be being updated") @@ -307,7 +310,7 @@ ;; {:username "carlozancnaro" ;; :blah "blah"}) -(defn delete! [db {:keys [tables where joins]}] +(defn compile-delete [db {:keys [tables where joins]}] (assert (= (count tables) 1) "Cannot delete from a multiple-table query") (let [table (compile-tables db joins tables) where-expression (if where diff --git a/src/clojure_sql/core.clj b/src/clojure_sql/core.clj index ebdc01f..03f992b 100644 --- a/src/clojure_sql/core.clj +++ b/src/clojure_sql/core.clj @@ -1,5 +1,5 @@ (ns clojure-sql.core - (:refer-clojure :exclude [sort]) + (:refer-clojure :exclude [sort take drop]) (:require [clojure.set :as set] [clojure-sql.compiler :as c] [clojure-sql.dsl :as d] @@ -21,7 +21,7 @@ (defmethod print-method clojure_sql.query.Query [query writer] (binding [*out* writer] - (pr (c/compile *database-type* query)))) + (pr (c/compile-select *database-type* query)))) (def table #'d/table) @@ -32,46 +32,27 @@ (def group #'d/group) (def having #'d/having) (def sort #'d/sort) - -;(def take #'d/take) -;(def drop #'d/drop) +(def take #'d/take) +(def drop #'d/drop) (defn run-query [query] (assert *query-executor* "Cannot execute a query without a query executor") - (*query-executor* :query (c/compile *database-type* query))) + (*query-executor* :select (c/compile-select *database-type* query))) (defn insert! [query & records] (assert *query-executor* "Cannot execute a query without a query executor") - (let [compiled (apply c/insert! *database-type* query records)] + (let [compiled (apply c/compile-insert *database-type* query records)] (*query-executor* :insert compiled))) (defn update! [query partial-record] (assert *query-executor* "Cannot execute a query without a query executor") - (let [compiled (c/update! *database-type* query partial-record)] + (let [compiled (c/compile-update *database-type* query partial-record)] (*query-executor* :update compiled))) (defn delete! [query] (assert *query-executor* "Cannot execute a query without a query executor") - (let [compiled (c/delete! *database-type* query)] + (let [compiled (c/compile-delete *database-type* query)] (*query-executor* :delete compiled))) (q/set-query-deref-behaviour! run-query) - - - - - - - -;; FIXME: how to do this better? With some discovery, maybe? - -(require '[clojure.java.jdbc :as jdbc]) -(defn use-jdbc! [connection-string] - (set-query-executor! (fn [type query] - (jdbc/with-connection connection-string - (case type - :query (jdbc/with-query-results results query - (vec results)) - :insert (jdbc/do-prepared-return-keys (first query) (next query)) - (jdbc/do-prepared (first query) (next query))))))) diff --git a/src/clojure_sql/dsl.clj b/src/clojure_sql/dsl.clj index c091d0e..10b1e8f 100644 --- a/src/clojure_sql/dsl.clj +++ b/src/clojure_sql/dsl.clj @@ -1,5 +1,5 @@ (ns clojure-sql.dsl - (:refer-clojure :exclude [sort group]) + (:refer-clojure :exclude [sort group take drop]) (:require [clojure.set :as set] [clojure.walk :as walk] [clojure-sql.query :as q] @@ -159,7 +159,9 @@ (defn ^:private joinable? [query] (and (nil? (:group query)) - (nil? (:having query)))) + (nil? (:having query)) + (nil? (:take query)) + (nil? (:drop query)))) (defn ^:private convert-to-subquery [query] (-> (table query) @@ -168,6 +170,13 @@ (defn ^:private remove-sort [query] (dissoc query :sort)) +(defn ^:private make-join-subquery [query] + (if (joinable? query) + query + (if (or (:take query) (:drop query)) + (convert-to-subquery query) + (convert-to-subquery (remove-sort query))))) + (def ^:private valid-join-type? (comp boolean #{:cross :inner :outer :full-outer})) (defn join [left right & {:keys [on type]}] (if (= type :right) @@ -175,18 +184,14 @@ (let [type (if (= type :left) :outer type) - left (if (joinable? left) - left - (convert-to-subquery (remove-sort left))) - right (if (joinable? right) - right - (convert-to-subquery (remove-sort right))) + left (make-join-subquery left) + right (make-join-subquery right) common-tables (set/intersection (set (keys (:tables left))) (set (keys (:tables right)))) right (reduce (fn [query [alias table]] (rename-table query alias (keyword (gensym (name table))))) right (:tables right)) - ;_ (assert (empty? common-tables) "Cannot join two tables with the same name") + ;;_ (assert (empty? common-tables) "Cannot join two tables with the same name") merged-tables (merge (:tables left) (:tables right)) common-fields (set/intersection (set (keys (:fields left))) (set (keys (:fields right)))) @@ -263,3 +268,16 @@ resolved-expression (process-expression table-name (:fields query) expression) new-having (combine-conjunctions old-having resolved-expression)] (assoc query :having new-having))) + +(defn take [query n] + (if-let [old-take (:take query)] + (assoc query :take (min old-take n)) + (assoc query :take n))) + +(defn drop [query n] + (let [query (if-let [old-take (:take query)] + (assoc query :take (- old-take n)) + query)] + (if-let [old-drop (:drop query)] + (assoc query :drop (+ old-drop n)) + (assoc query :drop n)))) diff --git a/src/clojure_sql/jdbc.clj b/src/clojure_sql/jdbc.clj new file mode 100644 index 0000000..8611336 --- /dev/null +++ b/src/clojure_sql/jdbc.clj @@ -0,0 +1,12 @@ +(ns clojure-sql.jdbc + (:require [clojure.java.jdbc :as jdbc] + [clojure-sql.core :refer [set-query-executor!]])) + +(defn use-jdbc! [connection-string] + (set-query-executor! (fn [type query] + (jdbc/with-connection connection-string + (case type + :select (jdbc/with-query-results results query + (vec results)) + :insert (jdbc/do-prepared-return-keys (first query) (next query)) + (jdbc/do-prepared (first query) (next query))))))) |