Commits

kumarshantanu  committed de8fdd3

add org.bituf.sqlrat.clause
bump the version to 0.2-SNAPSHOT in pom.xml
update CHANGES for TODO items
minor update to org.bituf.sqlrat.entity.internal

  • Participants
  • Parent commits 6d38a12

Comments (0)

Files changed (4)

 
 # Changes and TODO
 
-## 0.2 / planned
+## 0.2 / 2010-Sep-?? (GMT+5:30)
 
 - [TODO] Support large sized query result-sets (by adding lazy loading option)
-- [TODO] Optimistic locks using time stamps
-- [TODO] DSL for the WHERE clause
-- [????] Add :groupby and :orderby arguments to find-xxx functions
+- [TODO] Add :group-by, :order-by, :limit and :rest arguments to find-xxx fns
+- [TODO] Optimistic locks using lock fields/time stamps
+- [TODO] *show-sql* to be implemented for non-SELECT queries too
 
 ## 0.1 / 2010-Aug-31 (GMT + 5:30)
 
 - Query by COUNT(*) function
 - User can specify columns, WHERE clause in retrieve functions
 - CRUD (Create, Retrieve, Update, Delete) functions for entity-relations
-- Avoid N+1 Select
+- Avoid N+1 Selects
   <groupId>org.bituf</groupId>
   <artifactId>sqlrat</artifactId>
   <packaging>jar</packaging>
-  <version>0.1</version>
+  <version>0.2-SNAPSHOT</version>
   <name>sqlrat</name>
-  <description>SQLRat is a Clojure library to access Relation databases.</description>
+  <description>SQLRat is a Clojure library to access Relational databases.</description>
   <url>http://code.google.com/p/bitumenframework/</url>
   <repositories>
     <repository>

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

+(ns org.bituf.sqlrat.clause
+  "Convenience functions for handling SQL clauses. Clauses contain
+   1. clause expression
+   2. clause parameters (optional)
+   Clause examples:
+   Example 1 -- [\" WHERE emp.id = ?\" 1039]
+   Example 2 -- [\" GROUP BY ?\" \"category\"]
+   Example 3 -- [\" LIMIT ?, ?\" 50 60]
+   Example 4 -- [\"SELECT * FROM emp\"]
+  "
+  (:use org.bituf.sqlrat.entity.internal))
+
+(def *debug* false)
+(defn debug
+  ([source message]
+    (if *debug* (apply println "[DEBUG][" source "]" message)))
+  ([source message data & more]
+    (if *debug* (apply println "[DEBUG][" source "]" message data
+                  "->" (type data) more))))
+
+(defn bad-arg#
+  [reason & more]
+  (throw (IllegalArgumentException. (apply str reason more))))
+
+
+(defn as-clause
+  "Convert given expression into a clause and return it. Throw exception on
+   invalid input."
+  [clause]
+  (debug "as-clause" "Received clause:" clause)
+  (let [clause-vec (if (string? clause) [clause] (as-vector clause))
+        _          (if (or (nil? clause) (empty? clause-vec))
+                     (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))
+
+
+(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 str-col [k] (if (keyword? k) (name k) k))
+
+;; ========== WHERE clause functions ===========
+
+;;;
+;; functions on 2 operands
+;; 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 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 map-to-clauses
+  [^Map data-map operator]
+  (map #(do [(str (first %) operator \? ) (last %)]) (seq data-map)))
+
+
+(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))
+
+
+(defn and? [& many-expr]
+  (debug "and?" "Received expressions:" many-expr)
+  (apply map-or-coll-to-super-clause "(" " AND " ")" \= many-expr))
+
+
+(defn or?  [& many-expr]
+  (debug "or?"  "Received expressions:" many-expr)
+  (apply map-or-coll-to-super-clause "(" " OR  " ")" \= many-expr))
+
+
+(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"]))))))

File src/main/clj/org/bituf/sqlrat/entity/internal.clj

     (pprint obj))
   ([title obj]
     (println title)
-    (pprint obj)))
+    (pprint obj))
+  ([title obj & more]
+    (println title)
+    (pprint obj)
+    (doseq [each more] (pprint each))))
 
 
-(defn as-vector [anything]
+(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))
        (binding [sql/do-prepared do-prepared-insert]
          (sql/insert-values table (keys record) (vals record)))
        result))))
+
+(defn str-clause-key
+  "Converts a clause keyword to string.
+   :where becomes WHERE
+   :order-by becomes ORDER BY
+   and so on."
+  [^Keyword clause-key]
+  (.replace
+    (str \  (name clause-key) \  )
+    \- \  ))
+
+(defn merge-clause
+  "Merge clauses. For example:
+   [:where \"id = ?\" 45]
+   [:order-by :name]
+   [:order-by :city :name]
+   [:limit 34 89]
+"
+  [^Vector clauses]
+  )