Commits

Anonymous committed 2d05402

complete clause functionality with unit tests
reflect this update in CHANGES file

Comments (0)

Files changed (5)

 ## 0.2 / 2010-Sep-?? (GMT+5:30)
 
 - [TODO] Support large sized query result-sets (by adding lazy loading option)
-- [TODO] Add :group-by, :order-by, :limit and :rest arguments to find-xxx fns
-- [TODO] Optimistic locks using lock fields/time stamps
+- [TODO] Add :group-by and :rest arguments to entity/find-xxx functions
+- [TODO] Optimistic locks using lock-counter field (optional column in meta)
 - [TODO] *show-sql* to be implemented for non-SELECT queries too
+- [TODO] Add support for composite primary keys
+
+- DSL for SQL clauses, Sub-query support
+
+- [????] Provide 'insert' and 'update' functions
 
 ## 0.1 / 2010-Aug-31 (GMT + 5:30)
 

src/main/clj/org/bituf/sqlrat/clause.clj

    Example 2 -- [\" GROUP BY ?\" \"category\"]
    Example 3 -- [\" LIMIT ?, ?\" 50 60]
    Example 4 -- [\"SELECT * FROM emp\"]
-  "
-  (:use org.bituf.sqlrat.entity.internal))
+   Function names in uppercase (AND, OR, E) enclose the emitted SQL."
+  (:use org.bituf.sqlrat.util)
+  (:use org.bituf.sqlrat.clause.internal))
 
 (def *debug* false)
 (defn debug
     (if *debug* (apply println "[DEBUG][" source "]" message data
                   "->" (type data) more))))
 
-(defn bad-arg#
-  [reason & more]
-  (throw (IllegalArgumentException. (apply str reason more))))
 
+;; ========= Validate & Convert as clause =========
 
 (defn as-clause
   "Convert given expression into a clause and return it. Throw exception on
-   invalid input."
+   invalid input. A valid clause is a non-empty vector with first element as
+   the SQL clause followed by optional values for placeholder '?' symbols:
+   [\"id=?\" 45]"
   [clause]
   (debug "as-clause" "Received clause:" clause)
   (let [clause-vec (if (string? clause) [clause] (as-vector clause))
                      (bad-arg# "Clause must not be nil or empty: " clause))
         _          (if (not (string? (first clause-vec)))
                      (bad-arg# "Clause must begin with string: " clause))]
-    clause-vec))
+    (assoc-clause-meta clause-vec)))
 
 
-(defn merge-clauses "Merge one or more clauses into one super-clause"
-  ([many-clauses]
-    (merge-clauses many-clauses \  ))
-  ([many-clauses delim]
-    (debug "merge-clauses" "Received clause:" many-clauses)
-    (let [clauses-vec (if (string? many-clauses) [(as-clause many-clauses)]
-                        (as-vector (map #(as-clause %) many-clauses)))
-          qexpr (apply str (interpose delim (map #(first %) clauses-vec)))
-          qparm (flatten (map #(rest %) clauses-vec))]
-      (into [qexpr] qparm))))
+(defn clause?
+  "Return true if specified object is a clause, false otherwise"
+  [obj]
+  (true? (clause-key (meta obj))))
 
 
-(defn str-col [k] (if (keyword? k) (name k) k))
+(defn assert-clause
+  "Return specified clause if it is really a clause, throw
+   IllegalArgumentException otherwise."
+  [clause]
+  (if (clause? clause) clause
+    (bad-arg# "Not a clause: " clause)
+    ;(throw (IllegalArgumentException. (str "Not a clause: " clause)))
+    ))
+
+
+;; ======== Merge clauses =========
+
+(defn merge-clauses
+  "Merge one or more clauses into one super-clause."
+  [& clauses]
+  (debug "merge-clauses" "Received clauses:" clauses)
+  (let [clauses-vec (as-vector (map #(as-clause %) clauses))
+        qexpr (apply str (map #(first %) clauses-vec))
+        qparm (flatten (map #(rest %) clauses-vec))]
+    (as-clause (into [qexpr] qparm))))
+
+
+;; short hand functions for merging clauses
+
+(defn prefix "Prefix to clause" [clause token] (merge-clauses token clause))
+(defn suffix "Suffix to clause" [clause token] (merge-clauses clause token))
+(defn enclose
+  "Enclose a clause with prefix and suffix"
+  ([clause] (enclose clause (as-clause "(") (as-clause ")")))
+  ([clause prefix suffix] (merge-clauses prefix clause suffix)))
+(def E enclose) ;; short hand for enclose
+
 
 ;; ========== WHERE clause functions ===========
 
 ;;;
-;; functions on 2 operands
+;; functions on 1 operand
+(defn is-null
+  "Is null
+   Example: (is-null :location) >> [\"location IS NULL\"]"
+  [k]
+  (assert k)
+  (as-clause [(str (str-col k) " IS NULL")]))
+
+
+(defn not-null
+  "Not null
+   Example: (not-null :location) >> [\"location NOT NULL\"]"
+  [k]
+  (assert k)
+  (as-clause [(str (str-col k) " NOT NULL")]))
+
+
+;;;
+;; functions on 2 operands: ?-suffixed function names indicate parameterized SQL
 ;; taken from here: http://com.w3schools.com/sql/sql_where.asp
-(defn op2 [op k v] [(str (str-col k) op \?) v])
-(defn =?    "Equals"       [k v] (op2 \=   k v))
-(defn <>?   "Not equal to" [k v] (op2 "<>" k v))
-(defn !=?   "Not equal to" [k v] (op2 "!=" k v))
-(defn >?    "Greater than" [k v] (op2 \>   k v))
-(defn <?    "Less than"    [k v] (op2 \<   k v))
-(defn >=?   "Greater than or equals" [k v] (op2 ">=" k v))
-(defn <=?   "Less than or equals"    [k v] (op2 "<=" k v))
-(defn like?        "Like"        [k v] (op2 " LIKE " k v))
-(defn begins-with? "Begins with" [k v] (op2 " LIKE " k (str v \%)))
-(defn ends-with?   "Ends with"   [k v] (op2 " LIKE " k (str \% v)))
-(defn includes?    "Includes"    [k v] (op2 " LIKE " k (str \% v \%)))
+(defn op2
+  "Join column and value by specified operator. Use to write helper functions.
+   Note: 'op' and 'k' are asserted not to be a logical false in 3-arg version.
+   Example: (op2 \\= :id 45)
+            >> [\"id=?\" 45]
+   Example: (op2 \\= :id (as-clause \"SELECT x FROM y WHERE z=10\"))
+            >> [\"id=(SELECT x FROM y WHERE z=10)\"]
+   Example: (op2 \\= :id nil (fn [k] (str k \" IS NULL\")))
+            >> [\":id IS NULL\"]
+   Example: (op2 \\= :id 697 (fn [k] (str k \" IS NULL\")))
+            >> [\"id=?\" 697]"
+  ([op k v]
+    (assert op)
+    (assert k)
+    (if (clause? v) (merge-clauses (as-clause (str (str-col k) op))
+                      (E v)) ;; sub-query
+      (as-clause [(str (str-col k) op \?) v])))
+  ([op k v ^IFn if-nil-fn1]
+    (if (nil? v) (if-nil-fn1 k)
+      (op2 op k v))))
 
-(defn between? [k v1 v2] [(str (str-col k) " BETWEEN ? AND ?") v1 v2])
 
-(defn in? [k v] (let [v-vec (as-vector v)
-                      v-cnt (count v-vec)
-                      qexpr (apply str (str-col k) " IN ("
-                              (apply str
-                                (interpose ", " (take v-cnt (repeat \?)))) ")")]
-                  (into [qexpr] v-vec)))
+(defn =?
+  "Equals
+   Example: (=? :id 45) >> [\"id=?\" 45]"
+  [k v]
+  (op2 \=   k v is-null))
 
 
+(defn <>?
+  "Not equal to
+   Example: (<>? :id 56) >> [\"id<>?\" 56]"
+  [k v]
+  (op2 "<>" k v not-null))
+
+
+(defn !=?
+  "Not equal to
+   Example: (!=? :id 56) >> [\"id!=?\" 56]"
+  [k v]
+  (op2 "!=" k v not-null))
+
+
+(defn >?
+  "Greater than
+   Example: (>? :c 8.3) >> [\"c>?\" 8.3]"
+  [k v]
+  (assert v)
+  (op2 \>   k v))
+
+
+(defn <?
+  "Less than
+   Example: (<? :qty 5) >> [\"qty<?\" 5]"
+  [k v]
+  (assert v)
+  (op2 \<   k v))
+
+
+(defn >=?
+  "Greater than or equals
+   Example: (>=? :t 9.6) >> [\"t>=?\" 9.6]"
+  [k v]
+  (assert v)
+  (op2 ">=" k v))
+
+
+(def =>? >=?)
+
+
+(defn <=?
+  "Less than or equals
+   Example; (<=? :t 9.7) >> [\"t<=?\" 9.7]"
+  [k v]
+  (assert v)
+  (op2 "<=" k v))
+
+
+(def =<? <=?)
+
+
+(defn like?
+  "Like
+   Example: (like? :flag \"on\") >> [\"flag LIKE ?\" \"on\"]"
+  [k ^String v]
+  (assert v)
+  (op2 " LIKE " k v))
+
+
+(defn begins-with?
+  "Begins with
+   Example: (begins-with? :name \"ram\") >> [\"name LIKE ?\" \"ram%\"]"
+  [k ^String v]
+  (assert v)
+  (like? k (str v \%)))
+
+
+(defn ends-with?
+  "Ends with
+   Example: (ends-with? :name \"reynolds\") >> [\"name LIKE ?\" \"%reynolds\"]"
+  [k ^String v]
+  (assert v)
+  (like? k (str \% v)))
+
+
+(defn includes?
+  "Includes
+   Example: (includes? :name \"matt\") >> [\"name LIKE ?\" \"%matt%\"]"
+  [k ^String v]
+  (assert v)
+  (like? k (str \% v \%)))
+
+
+;;;
+;; BETWEEN expression
+(defn between?
+  "Value between v1 and v2.
+   Example: (between? :p 6 9) >> [\"p BETWEEN ? AND ?\" 6 9]"
+  [k v1 v2]
+  (assert k)
+  (assert v1)
+  (assert v2)
+  (as-clause [(str (str-col k) " BETWEEN ? AND ?") v1 v2]))
+
+
+;;;
+;; IN expression
+(defn- in-g?
+  "Generic IN expression function" 
+  [^String kw k v-coll]
+  (assert k)
+  (assert v-coll)
+  (let [v-vec (as-vector v-coll)
+        _     (if (empty? v-vec)
+                (throw (IllegalArgumentException. "Value collection is empty")))
+        v-cnt (count v-vec)
+        qexpr (apply str (str-col k) kw "("
+                (apply str
+                  (interpose ", " (take v-cnt (repeat \?)))) ")")]
+    (as-clause (into [qexpr] v-vec))))
+
+
+(defn in?
+  "In
+   Example: (in? :c [30 38]) >> [\"c IN (?, ?)\" 30 38]"
+  [k v-coll]
+  (in-g? " IN " k v-coll))
+
+
+(defn not-in?
+  "Not in
+   Example: (not-in? :c [30 38]) >> [\"c NOT IN (?, ?)\" 30 38]"
+  [k v-coll]
+  (in-g? " NOT IN " k v-coll))
+
+
+;;;
+;; IN/Equals expression
+(defn in=?
+  "Either equals or in a set of values.
+   Example: (in=? :qty nil) >> [\"qty IS NULL\"]
+   Example: (in=? :qty 56) >> [\"qty=?\" 56]
+   Example: (in=? :qty [56 78]) >> [\"qty IN (?, ?)\" 56 78]"
+  [k v]
+  (if (coll? v)
+    (in? k v) (=? k v)))
+
+
+;;;
+;; NOT IN/DoesNotEqual expression
+(defn- not-in<>-g?
+  "Generic 'Neither equals nor in a set of values' function"
+  [^IFn ne-fn k v]
+  (if (coll? v)
+    (not-in? k v) (ne-fn k v)))
+
+
+(defn not-in<>?
+  "Not equal, nor in a set of values.
+   Example: (not-in<>? :loc nil)
+            >> [\"loc NOT NULL\"]
+   Example: (not-in<>? :loc \"egypt\")
+            >> [\"loc<>?\" \"egypt\"]
+   Example: (not-in<>? :loc [\"egypt\" \"russia\"])
+            >> [\"loc NOT IN (?, ?)\" \"egypt\" \"russia\"]"
+  [k v]
+  (not-in<>-g? <>? k v))
+
+
+(defn not-in!=?
+  "Not equal, nor in a set of values.
+   Same as 'not-in<>?' but uses '!=' as operator."
+  [k v]
+  (not-in<>-g? !=? k v))
+
+
+;;;
+;; Convenience functions for parameters in a map
 (defn map-to-clauses
-  [^Map data-map operator]
-  (map #(do [(str (first %) operator \? ) (last %)]) (seq data-map)))
+  "Apply function op2fn to a map. Example:
+   (map-to-clauses in=? {:a 10 :b [20 30] :c nil})
+   >> ([\"a=?\" 10] [\"b IN (?, ?)\" 20 30] [\"c IS NULL\"])
+   The function op2fn should take 2 operands and must return a clause."
+  [^IFn op2fn ^Map pairs]
+  (map #(op2fn (first %) (last %)) (seq pairs)))
 
 
-(defn map-or-coll-to-super-clause
-  [enclose-begin delim enclose-end operator & map-or-many-clauses]
-  (debug "map-or-coll-to-super-clause" "Received clauses:" map-or-many-clauses)
-  (let [clause-coll (if (map? map-or-many-clauses)
-                      (map-to-clauses map-or-many-clauses operator)
-                      map-or-many-clauses)
-        merged-coll (merge-clauses clause-coll delim)
-        enclsd-coll (merge-clauses [enclose-begin merged-coll enclose-end] "")]
-    (debug "map-or-coll-to-super-clause" "Merged clause:" merged-coll)
-    enclsd-coll))
+;;;
+;; Convenience functions to join clauses by AND/OR
+(defn AND [& clauses] (E (apply merge-clauses
+                           (interpose (as-clause " AND ") clauses))))
+(defn OR  [& clauses] (E (apply merge-clauses
+                           (interpose (as-clause " OR ")  clauses))))
 
 
-(defn and? [& many-expr]
-  (debug "and?" "Received expressions:" many-expr)
-  (apply map-or-coll-to-super-clause "(" " AND " ")" \= many-expr))
+;; === Comma separated columns (for SELECT columns, GROUP BY, ORDER BY etc) ===
 
+(defn cscols
+  "Return columns in comma separated string form.
+   Example: [:qty :price \"order_date\"] >> [\"qty, price, order_date\"] "
+  [tokens]
+  (let [tokens-vec (as-vector tokens)]
+    (as-clause (apply str
+                 (interpose ", " (map #(str-col %) tokens-vec))))))
 
-(defn or?  [& many-expr]
-  (debug "or?"  "Received expressions:" many-expr)
-  (apply map-or-coll-to-super-clause "(" " OR  " ")" \= many-expr))
 
+;; === The LIMIT clause ===
 
-(defmacro sql "Macro to provide natural operators for SQL expressions"
-  [expr]
-  `(let [~'=  =?
-         ~'<> <>?
-         ~'!= !=?
-         ~'>  >?
-         ~'<  <?
-         ~'>= >=?
-         ~'<= <=?
-         ~'like like?
-         ~'begins-with begins-with?
-         ~'ends-with   ends-with?
-         ~'includes    includes?
-         ~'contains    includes?
-         ~'between     between?
-         ~'in          in?
-         ~'and and?
-         ~'or  or?]
-     ~expr))
-
-;; test: (or* (>=? :id 45) (between? :cnt 67 89))
-;; or say: (sql (or (>= :id 45) (between :cnt 67 89)))
-;(or? (>=? :id 45) (between? :cnt 67 89))
-;(sql (or (>= :id 45) (between :cnt 67 89)))
-
-(binding [*debug* true]
-  (do
-    (println (sql (or (>= :id 45) (between :cnt 67 89))))
-    (println (sql (and (or (>= :id 45) (between :cnt 67 89))
-                    (in :name ["Foo" "Bar" "Baz"]))))))
+(defn limit
+  "The LIMIT clause"
+  ([howmany]
+    (as-clause [" LIMIT ?" howmany]))
+  ([from howmany]
+    (as-clause [" LIMIT ?, ?" from howmany])))

src/main/clj/org/bituf/sqlrat/clause/internal.clj

+(ns org.bituf.sqlrat.clause.internal)
+
+(defn bad-arg#
+  [reason & more]
+  (throw (IllegalArgumentException. (apply str reason more))))
+
+(defn str-col [k] (if (keyword? k) (name k) k))
+
+
+;; ======== Clause metadata ========
+
+(def clause-key :sqlrat-sql-clause?)
+
+
+(def clause-meta {clause-key true})
+
+
+(defn assoc-clause-meta "Add clause meta data to specified object"
+  [obj]
+  (with-meta obj (merge (meta obj) clause-meta)))

src/main/clj/org/bituf/sqlrat/util.clj

+(ns org.bituf.sqlrat.util
+  "Common utility functions")
+
+
+(defn as-vector
+  "Converts anything to vector"
+  [anything]
+  (if (vector? anything) anything
+    (if (or (seq? anything) (set? anything)) (into [] anything)
+      (if (map? anything) (into [] (vals anything))
+        [anything]))))

src/test/script/runtests.clj

 (ns runtests
-  (:use org.bituf.sqlrat.test.dbblog)
+  (:require org.bituf.sqlrat.test.dbblog)
+  (:require org.bituf.sqlrat.test.test-clause)
   (:use clojure.test))
 
 
 (binding [org.bituf.sqlrat.entity/*show-sql* true
           org.bituf.sqlrat.entity/*show-sql-results* true]
   (run-tests
-    'org.bituf.sqlrat.test.dbblog))
+    'org.bituf.sqlrat.test.dbblog
+    'org.bituf.sqlrat.test.test-clause))