From 148f752b5f48707dc3d7fe448d1faf33d5cd0228 Mon Sep 17 00:00:00 2001 From: Carlo Zancanaro Date: Thu, 13 Jun 2013 18:24:05 +1000 Subject: Starting a re-write of the DSL, to be followed by the compiler. Flip around field/table aliases, do joins a bit differently. They're my main aims at the moment! I'll also add a preprocessor for the compiler to massage it into a nicer form there. I discovered that joins can be done with a pretty sweet syntax in SQL: (tableA LEFT JOIN tableB) RIGHT JOIN tableC This is pretty much perfect for my purposes. Flipping alias maps just makes more sense and removes a whole bunch of `flip-map` calls that would be unnecessary if the aliases were the other way around. The user-facing side of the DSL will be left unchanged, though. The user provides an `{old-name new-name}` map and internally we convert that into `{new-name old-name}`. Like magic. I'm also adding a bunch more tests. Hopefully that will make things more likely to work for long periods of time. Peace out! --- .midje.clj | 1 + project.clj | 2 +- src/clojure_sql/compiler.clj | 2 +- src/clojure_sql/core.clj | 22 +++--- src/clojure_sql/dsl.clj | 159 +++++++++++++++++++------------------- src/clojure_sql/util.clj | 15 +++- test/clojure_sql/core_test.clj | 63 ++++++++++------ test/clojure_sql/dsl_test.clj | 168 +++++++++++++++++++++++++++++++++++++++++ 8 files changed, 317 insertions(+), 115 deletions(-) create mode 100644 .midje.clj create mode 100644 test/clojure_sql/dsl_test.clj diff --git a/.midje.clj b/.midje.clj new file mode 100644 index 0000000..788127a --- /dev/null +++ b/.midje.clj @@ -0,0 +1 @@ +(change-defaults :partial-prerequisites true) diff --git a/project.clj b/project.clj index a568c1d..30932b7 100644 --- a/project.clj +++ b/project.clj @@ -4,4 +4,4 @@ :license {:name "Eclipse Public License" :url "http://www.eclipse.org/legal/epl-v10.html"} :dependencies [[org.clojure/clojure "1.5.1"] - [midje "1.5.0"]]) + [midje "1.5.1"]]) diff --git a/src/clojure_sql/compiler.clj b/src/clojure_sql/compiler.clj index 7ded112..ecd7bd6 100644 --- a/src/clojure_sql/compiler.clj +++ b/src/clojure_sql/compiler.clj @@ -211,7 +211,7 @@ (defn insert! [db query & records] {:pre [(empty? (:joins query))]} ;; some code here - ($str (return "Carlo")) + #_($str (return "Carlo")) ) (defn update! [db query & partial-records] diff --git a/src/clojure_sql/core.clj b/src/clojure_sql/core.clj index 4f88a53..4e3db34 100644 --- a/src/clojure_sql/core.clj +++ b/src/clojure_sql/core.clj @@ -17,16 +17,18 @@ (binding [*out* writer] (pr (c/compile nil query)))) -(def table #'d/table) -(def project #'d/project) -(def rename #'d/rename) -(def join #'d/join) -(def select #'d/select) -(def sort-by #'d/sort-by) - -(def insert! #'c/insert!) -(def update! #'c/update!) -(def delete! #'c/delete!) +(comment + + (def table #'d/table) + (def project #'d/project) + (def rename #'d/rename) + (def join #'d/join) + (def select #'d/select) + (def sort-by #'d/sort-by) + + (def insert! #'c/insert!) + (def update! #'c/update!) + (def delete! #'c/delete!)) diff --git a/src/clojure_sql/dsl.clj b/src/clojure_sql/dsl.clj index 8c9c31d..68446d1 100644 --- a/src/clojure_sql/dsl.clj +++ b/src/clojure_sql/dsl.clj @@ -19,9 +19,9 @@ ;; -SORTING- ;; { -;; :table => tablename -> table_alias, +;; :tables => tablename -> table_alias ;; :fields => (table_alias, fieldname) -> field_alias -;; :joins => [tablename -> (type, table_alias, on)] +;; :joins => [left-table type right-table on?] ;; :where => expression ;; :sort-by => [(field, direction)] ;; } @@ -29,8 +29,8 @@ (defn table [arg] (into (q/->Query) (if (map? arg) - {:table arg} - {:table {arg arg}}))) + {:tables (u/flip-map arg)} + {:tables {arg arg}}))) (defn ^:private ambiguous-error [field & [query]] (throw (ex-info (str "Ambiguous field " field " in query with more than one table") @@ -38,7 +38,7 @@ :query query}))) (defn ^:private resolve-field [table aliases field] - (let [field-alias-lookup (u/flip-map aliases)] + (let [field-alias-lookup aliases] (or (field-alias-lookup field) (if table [table field] @@ -51,25 +51,27 @@ :else expression)) (defn project [query fields] - (let [table (if-not (:joins query) - (-> query :table first val)) - alias-lookup (u/flip-map (:fields query)) - original-name #(resolve-fields table (:fields query) %)] + (let [table (if (= (count (:tables query)) 1) + (-> query :tables first key)) + alias-lookup (or (:fields query) {}) + original-name #(resolve-fields table alias-lookup %)] (assoc query :fields (->> (for [[key val] (if (map? fields) fields (zipmap fields fields))] - [(original-name key) val]) + [val (original-name key)]) (into {}))))) (defn rename [query field-renames] {:pre [(map? field-renames) ;; the intersection of the new aliases with the old aliases NOT renamed by this operation (empty? (set/intersection (set (vals field-renames)) - (set/difference (set (vals (:fields query))) + (set/difference (set (keys (:fields query))) (set (keys field-renames)))))]} (let [fields (:fields query) - alias-lookup (u/flip-map (:fields query)) + alias-lookup (or (:fields query) {}) + fields-to-remove (set/difference (set (keys field-renames)) + (set (vals field-renames))) original-name (fn [field] (cond (vector? field) field (contains? alias-lookup field) (get alias-lookup field) @@ -77,72 +79,75 @@ {:field field :query query :renames field-renames}))) - (get alias-lookup field))] - (update-in query - [:fields] #(->> (for [[key val] field-renames] - ;(if (contains? val (:fields query))) - [(original-name key) val]) - (into %))))) + (get alias-lookup field)) + ;; do the renaming we want here: + fields (->> (for [[key val] field-renames] + [val (original-name key)]) + (into fields)) + ;; remove the things we no longer have here: + fields (->> (remove (comp fields-to-remove key) fields) + (into {}))] + (assoc query :fields fields))) -(defn ^:private combine-wheres [& wheres] - (reduce (fn [acc where] - (cond (nil? acc) where - (nil? where) acc - :else (or (if (and (sequential? where) - (= (name (first where)) "and")) - `(and ~acc ~@(next where))) - (if (and (sequential? acc) - (= (name (first acc)) "and")) - `(and ~@(next acc) ~where)) - `(and ~acc ~where)))) - nil wheres)) +;; (defn ^:private combine-wheres [& wheres] +;; (reduce (fn [acc where] +;; (cond (nil? acc) where +;; (nil? where) acc +;; :else (or (if (and (sequential? where) +;; (= (name (first where)) "and")) +;; `(and ~acc ~@(next where))) +;; (if (and (sequential? acc) +;; (= (name (first acc)) "and")) +;; `(and ~@(next acc) ~where)) +;; `(and ~acc ~where)))) +;; nil wheres)) -(defn join [left right & {:keys [on type]}] - (let [joins-vector (or (:join left) []) - common-fields (set/intersection (set (vals (:fields left))) - (set (vals (:fields right)))) - joined-fields (if (:type :right) - (merge (->> (:fields left) - (filter (comp not common-fields val)) - (into {})) - (:fields right)) - (merge (:fields left) - (->> (:fields right) - (filter (comp not common-fields val)) - (into {})))) - implicit-on (if (seq common-fields) - (map (fn [field] - `(= ~(resolve-field (:table left) (:fields left) field) - ~(resolve-field (:table right) (:fields right) field))) - common-fields)) - on (if on - [(resolve-fields nil joined-fields on)]) - join-condition (if-let [condition (seq (concat implicit-on on))] - `(and ~@condition) - true)] - (-> left - (assoc :fields joined-fields) - (assoc :joins (into (conj joins-vector - [(or type :inner) (:table right) join-condition]) - (:joins right))) - (assoc :where (combine-wheres (:where left) (:where right)))))) +;; (defn join [left right & {:keys [on type]}] +;; (let [joins-vector (or (:join left) []) +;; common-fields (set/intersection (set (vals (:fields left))) +;; (set (vals (:fields right)))) +;; joined-fields (if (= type :right) +;; (merge (->> (:fields left) +;; (filter (comp not common-fields val)) +;; (into {})) +;; (:fields right)) +;; (merge (:fields left) +;; (->> (:fields right) +;; (filter (comp not common-fields val)) +;; (into {})))) +;; implicit-on (if (seq common-fields) +;; (map (fn [field] +;; `(= ~(resolve-field (:table left) (:fields left) field) +;; ~(resolve-field (:table right) (:fields right) field))) +;; common-fields)) +;; on (if on +;; [(resolve-fields nil joined-fields on)]) +;; join-condition (if-let [condition (seq (concat implicit-on on))] +;; `(and ~@condition) +;; true)] +;; (-> left +;; (assoc :fields joined-fields) +;; (assoc :joins (into (conj joins-vector +;; [(or type :inner) (:table right) join-condition]) +;; (:joins left))) +;; (assoc :where (combine-wheres (:where left) (:where right)))))) -(defn select [query expression] - (let [table-name (if-not (:joins query) - (-> query :table first val)) - old-where (:where query) - resolved-expression (resolve-fields table-name (:fields query) expression) - new-where (combine-wheres old-where resolved-expression)] - (assoc query :where new-where))) +;; (defn select [query expression] +;; (let [table-name (if-not (:joins query) +;; (-> query :table first val)) +;; old-where (:where query) +;; resolved-expression (resolve-fields table-name (:fields query) expression) +;; new-where (combine-wheres old-where resolved-expression)] +;; (assoc query :where new-where))) -(defn sort-by [query fields] - (let [table-name (if-not (:joins query) - (-> query :table first val)) - fields-seq (if (sequential? fields) - fields - [fields])] - (assoc query - :sort-by (for [field fields-seq] - (if (vector? field) - (resolve-field table-name (:fields query) field) - [(resolve-field table-name (:fields query) field) :asc]))))) +;; (defn sort-by [query fields] +;; (let [table-name (if-not (:joins query) +;; (-> query :table first val)) +;; fields-seq (if (sequential? fields) +;; fields +;; [fields])] +;; (assoc query +;; :sort-by (for [field fields-seq] +;; (if (vector? field) +;; (resolve-field table-name (:fields query) field) +;; [(resolve-field table-name (:fields query) field) :asc]))))) diff --git a/src/clojure_sql/util.clj b/src/clojure_sql/util.clj index cd8e8b6..f2edf30 100644 --- a/src/clojure_sql/util.clj +++ b/src/clojure_sql/util.clj @@ -1,4 +1,5 @@ -(ns clojure-sql.util) +(ns clojure-sql.util + (:require [clojure.set :as set])) (defn funcall [f & args] (apply f args)) @@ -11,3 +12,15 @@ (->> m (map (comp vec reverse)) (into {}))) + +(defn map-kv [f & maps] + (let [all-keys (into #{} (mapcat keys maps))] + (->> all-keys + (map (fn [key] + (apply f key (map #(get % key) maps)))) + (into {})))) + +(defn map-vals [f & maps] + (apply map-kv (fn [k & vs] + [k (apply f vs)]) + maps)) diff --git a/test/clojure_sql/core_test.clj b/test/clojure_sql/core_test.clj index 236e421..6a12698 100644 --- a/test/clojure_sql/core_test.clj +++ b/test/clojure_sql/core_test.clj @@ -3,28 +3,41 @@ (:require [clojure-sql.core :refer :all] [midje.sweet :refer :all])) -(fact - @(table :user) - => ["SELECT * FROM \"user\""] - - @(-> (table :user) (select '(= :username "george"))) - => ["SELECT * FROM \"user\" WHERE (\"user\".\"username\" = ?)" "george"] - - @(-> (table :user) (project {:username :u})) - => ["SELECT \"user\".\"username\" AS \"u\" FROM \"user\""] - - @(-> (table :user) (project {'(+ :username :password) :u})) - => ["SELECT (\"user\".\"username\" + \"user\".\"password\") AS \"u\" FROM \"user\""]) - -(into {} (-> (table :user) (project '{(+ :username :password) :u}))) - -(-> (table :users) - (project '[:username (+ 1 2 3)]) - (rename '{(+ 1 2 3) :x}) - (select `(exists ~(-> (table :users) - (select '(= 10 :username))))) - println) - -(-> (table :users) - (project {:username :un}) - (select '(= :username 10))) +(comment + + (fact + @(table :user) + => ["SELECT * FROM \"user\""] + + @(-> (table :user) (project [:username])) + => ["SELECT \"user\".\"username\" AS \"username\" FROM \"user\""] + + @(-> (table :user) (select '(= :username "george"))) + => ["SELECT * FROM \"user\" WHERE (\"user\".\"username\" = ?)" "george"] + + @(-> (table :user) (project {:username :u})) + => ["SELECT \"user\".\"username\" AS \"u\" FROM \"user\""] + + @(-> (table :user) (project {'(+ :age :modifier) :u})) + => ["SELECT (\"user\".\"age\" + \"user\".\"modifier\") AS \"u\" FROM \"user\""] + + @(-> (table :user) + (project [:id]) + (join (-> (table :x) + (project [:id]) + (join (-> (table :y) + (project [:id])) + :type :left)))) + => ["SELECT \"user\".\"id\" AS \"id\" FROM \"user\" INNER JOIN \"x\" ON ((\"user\".\"id\" = \"x\".\"id\"))"]) + + (into {} (-> (table :user) (project '{(+ :username :password) :u}))) + + (-> (table :users) + (project '[:username (+ 1 2 3)]) + (rename '{(+ 1 2 3) :x}) + (select `(exists ~(-> (table :users) + (select '(= 10 :username)))))) + + (-> (table :users) + (project {:username :un}) + (select '(= :username 10)))) diff --git a/test/clojure_sql/dsl_test.clj b/test/clojure_sql/dsl_test.clj new file mode 100644 index 0000000..182749e --- /dev/null +++ b/test/clojure_sql/dsl_test.clj @@ -0,0 +1,168 @@ +(ns clojure-sql.dsl-test + (:refer-clojure :exclude [sort-by]) + (:require [clojure-sql.dsl :refer :all] + [midje.sweet :refer :all])) + +(unfinished join) + +(fact "Table creates basic queries on tables" + + (table ..name..) + => {:tables {..name.. ..name..}} + ;(provided (keyword? ..name..) => true) + + (table {..name.. ..alias..}) + => {:tables {..alias.. ..name..}} + + (table {..name.. ..alias.., ..name2.. ..alias2..}) + => {:tables {..alias.. ..name.., + ..alias2.. ..name2..}}) + + +(fact "Project restricts (and/or adds) fields of a query" + + (prerequisites (keyword? ..table-alias..) => true + (keyword? ..table..) => true + (keyword? ..field..) => true + (keyword? ..field-alias..) => true + (keyword? ..field2..) => true + (keyword? ..field2-alias..) => true) + + (fact "projecting onto a single field from one table" + (-> (table {..table.. ..table-alias..}) + (project [..field..])) + => {:tables {..table-alias.. ..table..} + :fields {..field.. [..table-alias.. ..field..]}} + + (-> (table {..table.. ..table-alias..}) + (project {..field.. ..field-alias..})) + => {:tables {..table-alias.. ..table..} + :fields {..field-alias.. [..table-alias.. ..field..]}}) + + + (fact "projecting onto multiple fields from one table" + (-> (table {..table.. ..table-alias..}) + (project [..field.. ..field2..])) + => {:tables {..table-alias.. ..table..} + :fields {..field.. [..table-alias.. ..field..] + ..field2.. [..table-alias.. ..field2..]}} + + (-> (table {..table.. ..table-alias..}) + (project {..field.. ..field-alias.., ..field2.. ..field2-alias..})) + => {:tables {..table-alias.. ..table..} + :fields {..field-alias.. [..table-alias.. ..field..] + ..field2-alias.. [..table-alias.. ..field2..]}}) + + (fact "projecting one a field from multiple tables" + (prerequisites ..tables.. =contains=> {:tables {..table.. ..table.., ..table2.. ..table2..}}) + + (project ..tables.. [..field..]) + => (throws clojure.lang.ExceptionInfo) + + (project ..tables.. {..field.. ..field-alias..}) + => (throws clojure.lang.ExceptionInfo)) + + (fact "projecting a subset of the current projection from one table" + ;; Note that these two facts are retaining the ..table.. in the + ;; fields rather than using the ..table-alias.. they would have + ;; used otherwise. This shows they are filtering the existing + ;; fields rather than removing and re-adding the fields. + (project {:tables {..table-alias.. ..table..} + :fields {..field.. [..table.. ..field..] + ..field2.. [..table.. ..field2..]}} + [..field..]) + => {:tables {..table-alias.. ..table..} + :fields {..field.. [..table.. ..field..]}} + + (project {:tables {..table-alias.. ..table..} + :fields {..field.. [..table.. ..field..] + ..field2.. [..table.. ..field2..]}} + {..field.. ..field-alias..}) + => {:tables {..table-alias.. ..table..} + :fields {..field-alias.. [..table.. ..field..]}}) + + (fact "projecting a disjoint set from the current projection from one table" + (project {:tables {..table-alias.. ..table..} + :fields {[..table.. ..field..] ..field..}} + [..field2..]) + => {:tables {..table-alias.. ..table..} + :fields {..field2.. [..table-alias.. ..field2..]}}) + + (fact "projecting a superset from the current projection from one table" + (project {:tables {..table.. ..table..} + :fields {..field.. [..table.. ..field..]}} + [..field.. ..field2..]) + => {:tables {..table.. ..table..} + :fields {..field.. [..table.. ..field..] + ..field2.. [..table.. ..field2..]}} + + ;; once again - note the table aliases + (project {:tables {..table-alias.. ..table..} + :fields {..field.. [..table.. ..field..]}} + [..field.. ..field2..]) + => {:tables {..table-alias.. ..table..} + :fields {..field.. [..table.. ..field..] + ..field2.. [..table-alias.. ..field2..]}}) + + + + (fact "projecting a subset of the current projection from two table" + (prerequisites ..tables.. =contains=> {:tables {..table.. ..table.., ..table2.. ..table2..}}) + + (project {:tables ..tables.. + :fields {..field.. [..table.. ..field..] + ..field2.. [..table.. ..field2..]}} + [..field..]) + => {:tables ..tables.. + :fields {..field.. [..table.. ..field..]}} + + (project {:tables ..tables.. + :fields {..field.. [..table.. ..field..] + ..field2.. [..table.. ..field2..]}} + {..field.. ..field-alias..}) + => {:tables ..tables.. + :fields {..field-alias.. [..table.. ..field..]}}) + + (fact "projecting a disjoint set from the current projection from two tables" + (project {:tables {..table-alias.. ..table.., ..table2-alias.. ..table2..} + :fields {[..table.. ..field..] ..field..}} + [..field2..]) + => (throws clojure.lang.ExceptionInfo)) + + (fact "projecting a superset from the current projection from two tables" + (project {:tables {..table-alias.. ..table.., ..table2-alias.. ..table2..} + :fields {[..table.. ..field..] ..field..}} + [..field.. ..field2..]) + => (throws clojure.lang.ExceptionInfo))) + + + +(fact "renaming fields does what you'd expect (renames them, removes the old alias)" + (prerequisites (keyword? ..table..) => true + (keyword? ..field..) => true + (keyword? ..field2..) => true + (keyword? ..field-alias..) => true) + + (-> (table ..table..) + (project [..field..]) + (rename {..field.. ..field-alias..})) + => {:tables {..table.. ..table..} + :fields {..field-alias.. [..table.. ..field..]}} + + (-> (table ..table..) + (project [..field.. ..field2..]) + (rename {..field.. ..field-alias..})) + => {:tables {..table.. ..table..} + :fields {..field-alias.. [..table.. ..field..] + ..field2.. [..table.. ..field2..]}} + + (-> {:tables {..table.. ..table.., + ..table2.. ..table2..} + :fields {..field.. [..table.. ..field..] + ..field2.. [..table.. ..field2..]}} + (rename {..field.. ..field-alias..})) + => {:tables {..table.. ..table.., ..table2.. ..table2..} + :fields {..field-alias.. [..table.. ..field..] + ..field2.. [..table.. ..field2..]}}) + + -- cgit v1.2.3