Source

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

kumarshantanu de8fdd3 








shantanuk e3573f1 
shantanuk 2d05402 
shantanuk 46b09cb 

kumarshantanu de8fdd3 









shantanuk 2d05402 
kumarshantanu de8fdd3 
shantanuk 55cc10a 





kumarshantanu de8fdd3 

shantanuk 5a47290 



shantanuk 12a2dc0 



shantanuk 2e10a96 
shantanuk 5a47290 
kumarshantanu de8fdd3 

shantanuk 55cc10a 
shantanuk 5a47290 


shantanuk 2e10a96 
shantanuk 55cc10a 
shantanuk 4998ade 
shantanuk 55cc10a 
shantanuk 2e10a96 

shantanuk 55cc10a 
kumarshantanu de8fdd3 

shantanuk 5a47290 






shantanuk 8d76f97 





shantanuk 5a47290 
shantanuk 8d76f97 







shantanuk 5a47290 







shantanuk 55cc10a 
shantanuk 5a47290 
shantanuk 55cc10a 
kumarshantanu de8fdd3 

shantanuk 55cc10a 
shantanuk 2d05402 
shantanuk 4998ade 
shantanuk 55cc10a 

shantanuk 2d05402 













shantanuk 8538d1e 
shantanuk 12a2dc0 
shantanuk 8538d1e 
shantanuk 12a2dc0 



shantanuk 5a47290 

shantanuk 8538d1e 



shantanuk e3573f1 



shantanuk 312bf36 
shantanuk e3573f1 
shantanuk 312bf36 


shantanuk 2d05402 


shantanuk e3573f1 



shantanuk 2d05402 
shantanuk 312bf36 

shantanuk e3573f1 
shantanuk 312bf36 
shantanuk e3573f1 
shantanuk 312bf36 
shantanuk e3573f1 
shantanuk 312bf36 
shantanuk e3573f1 
shantanuk 312bf36 
shantanuk 5a47290 

shantanuk 312bf36 
kumarshantanu de8fdd3 



shantanuk 2d05402 


shantanuk 12a2dc0 
shantanuk 2d05402 

shantanuk 312bf36 
shantanuk 2d05402 



shantanuk 12a2dc0 
shantanuk 2d05402 

shantanuk 312bf36 
shantanuk 2d05402 



kumarshantanu de8fdd3 
shantanuk 2d05402 



shantanuk 12a2dc0 
shantanuk 2d05402 
shantanuk 12a2dc0 
shantanuk 2d05402 
shantanuk 12a2dc0 
shantanuk 2d05402 
shantanuk 12a2dc0 
shantanuk 2d05402 


shantanuk 312bf36 
shantanuk 46b09cb 
shantanuk 312bf36 
shantanuk 2d05402 


kumarshantanu de8fdd3 

shantanuk 2d05402 




kumarshantanu de8fdd3 

shantanuk 2d05402 




























































































shantanuk 312bf36 
shantanuk 2d05402 








shantanuk 8d76f97 









shantanuk 2d05402 

























































kumarshantanu de8fdd3 
shantanuk 2d05402 





kumarshantanu de8fdd3 

shantanuk 46b09cb 
shantanuk 8cdcf8a 
shantanuk 8d76f97 

shantanuk 8cdcf8a 
















shantanuk 8d76f97 


shantanuk 46b09cb 
shantanuk 8d76f97 


shantanuk 46b09cb 


shantanuk 2d05402 
shantanuk e3573f1 










shantanuk 2e10a96 




shantanuk e3573f1 



shantanuk 2e10a96 




kumarshantanu de8fdd3 

shantanuk 312bf36 
kumarshantanu de8fdd3 
shantanuk 312bf36 
shantanuk 2d05402 


shantanuk 55cc10a 

shantanuk 5a47290 


kumarshantanu de8fdd3 

shantanuk 2d05402 
kumarshantanu de8fdd3 
shantanuk 2d05402 





shantanuk 5a47290 





shantanuk e3573f1 

shantanuk 5a47290 










shantanuk e3573f1 

shantanuk 5a47290 




shantanuk e3573f1 

shantanuk 5a47290 
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
(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\"]
   Function names in uppercase (AND, OR, FN) enclose the emitted SQL."
  (:use org.bituf.sqlrat.util)
  (:use org.bituf.sqlrat.clause.internal)
  (:import [clojure.lang IFn IPersistentCollection]))

(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))))


;; ========= Validate & Convert as clause =========

(defn clause?
  "Return true if specified object is a clause, false otherwise"
  [obj]
  (true? (clause-key (meta obj))))


(defn as-clause
  "Convert given expression into a clause and return it. Throw exception on
  invalid input. A strictly valid clause is a non-empty vector with the first
  element as the string SQL clause followed by optional values for placeholder
  '?' symbols: [\"id=?\" 45]
  The following clauses are permitted:
  Keyword    - :id       => becomes clause => [\"id\"]
  String     - \"id\"    => becomes clause => [\"id\"]
  Clause     - [\"id\"]  => remains clause => [\"id\"]
  Collection - '(\"id\") => becomes clause => [\"id\"]
  Any value  - 479       => becomes clause => [\"479\"]
  You got the drift."
  [clause]
  (debug "as-clause" "Received clause:" clause)
  (if (clause? clause) clause
    (let [clause-vec (if (string? clause) [clause]
                       (if (keyword? clause) [(name clause)]
                         (if (coll? clause) (as-vector clause)
                           [(str 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))
          ]
      (assoc-clause-meta clause-vec))))


(defn as-value-clause
  "Converts a value, or a set of values into a value clause. Value clauses look
  like these:
  [\"?, ?, ?\" 10 20 30]
  [\"?\" \"Anthony Pereira\"]
  etc"
  [value]
  (if *assert-args* (try
                      (assert (not (clause? value)))
                      (assert (not (nil?    value)))
                      (catch AssertionError e
                        (bad-arg!
                          "Invalid input: nil and clauses are not allowed"))))
  (let [values (as-vector value)
        _      (if *assert-args* (doseq [each values]
                                   (try
                                     (assert (not (nil?  each)))
                                     (assert (not (coll? each)))
                                     (assert (not (fn?   each)))
                                     (catch AssertionError e
                                       (bad-arg! "Invalid input: nil, collection and function are not allowed as clause elements"
                                         )))))
        vcount (count values)
        cl-str (apply str
                 (interpose ", "
                   (take vcount
                     (repeat \? ))))]
    (as-clause (into [cl-str] values))))


(defn empty-clause
  []
  (as-clause ""))


(defn empty-clause?
  [clause]
  (assert (clause? clause))
  (empty? (first 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))))


(defn query
  "Merge clauses with interposed space. Useful to construct free-form clauses.
  Example: (query :SELECT :* :FROM :emp :WHERE (=? :id 56))
           => [\"SELECT * FROM emp WHERE id=?\" 56]
  See also: merge-clauses"
  [& clauses]
  (apply merge-clauses (interpose (as-clause " ") clauses)))


(def ^{:doc "Alias for the 'query' function"}
      >> query)


(defn merge-key-clauses
  [prefix ^Keyword k ^IPersistentVector clause & more]
  (let [clauses (into [clause] more)
        sclause (apply merge-clauses clauses)] ; sclause = super-clause
    (if (empty-clause? sclause) (empty-clause)
      (merge-clauses (str-clause-key k prefix)
        sclause))))


;; short hand functions for merging clauses

(defn enclose
  "Enclose a clause with '(' and ')' characters"
  [^IPersistentVector clause]
  (merge-clauses (as-clause "(")
    clause (as-clause ")")))

;; short hand

(defn SELECT  [^Vector clause & more] (apply merge-key-clauses nil :select
                                        (into [clause] more)))
(defn FROM    [^Vector clause & more] (apply merge-key-clauses " " :from
                                        (into [clause] more)))
(defn WHERE   [^Vector clause & more] (apply merge-key-clauses " " :where
                                        (into [clause] more)))
(defn GROUPBY [^Vector clause & more] (apply merge-key-clauses " " :group-by
                                        (into [clause] more)))
(def ^{:doc "Alias for GROUPBY"}
      GROUP-BY GROUPBY)


;; ========== WHERE clause functions ===========

;;;
;; functions on 1 operand
(defn is-null
  "Is null
   Example: (is-null :location) => [\"location IS NULL\"]"
  [k]
  (assert k)
  (as-clause [(str (str-name k) " IS NULL")]))


(defn not-null
  "Not null
   Example: (not-null :location) => [\"location NOT NULL\"]"
  [k]
  (assert k)
  (as-clause [(str (str-name 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
  "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-name k) op))
                      (enclose v)) ;; sub-query
      (as-clause [(str (str-name k) op \?) v])))
  ([op k v ^IFn if-nil-fn1]
    (if (nil? v) (if-nil-fn1 k)
      (op2 op k v))))


(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-name k) " BETWEEN ? AND ?") v1 v2]))


;;;
;; IN expression
(defn- in-g?
  "Generic IN expression function" 
  [^String kw k v-coll]
  (assert k)
  (assert v-coll)
  (if (clause? v-coll) (merge-clauses (str-name k) kw
                         (enclose v-coll)) ; sub-query
    (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-name 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
  "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 ?
  "Smart value-parameter function. If you pass a map, a key=value pattern is
  applied. For other collections and single-value a parameterized value clause
  is generated. 
  Arguments:
    coll  (Collection) of data. If it is a map then equivalent to (? in=? coll)
          else generates a parameterized SQL value clause
    op2fn (Function) accepts two arguments, returns parameterized SQL clause
    m     (Map) map of colnames versus values
  Example:
    user=> (? [1 2 3])
    [\"?, ?, ?\" 1 2 3]
    user=> (? #{10 nil 55})
    [\"?, ?, ?\" nil 10 55]
    user=> (? 34)
    [\"?\" 34]
    user=> (? {:a 10 :b \"hello\" :c [10 20 30] :d nil})
    ([\"a=?\" 10] [\"b=?\" \"hello\"]
     [\"c IN (?, ?, ?)\" 10 20 30] [\"d IS NULL\"])
  See also:
    as-value-clause, map-to-clauses"
  ([value]
    (if (map? value) (? in=? value)
      (as-value-clause value)))
  ([^IFn op2fn ^IPersistentMap m]
    (if *assert-args*
      (assert (fn? op2fn))
      (assert (map? m)))
    (map-to-clauses op2fn m)))


;;;
;; Convenience functions to join clauses by a delimiter

(defn enclose-interpose
  [sep-clause clause-1 clause-2 & more]
  (let [clauses (into [clause-1 clause-2] more)]
    (enclose (apply merge-clauses
               (interpose sep-clause clauses)))))


(defn AND
  "Interpose clauses with \" AND \" and enclose the result"
  ([clauses]
    (if *assert-args* (assert (coll? clauses)))
    (apply AND clauses))
  ([clause-1 clause-2 & more]
    (apply enclose-interpose " AND " clause-1 clause-2 more)))


(defn OR
  "Interpose clauses with \" OR \" and enclose the result"
  ([clauses]
    (if *assert-args* (assert (coll? clauses)))
    (apply OR clauses))
  ([clause-1 clause-2 & more]
    (apply enclose-interpose " OR " clause-1 clause-2 more)))


;; === Comma separated names (for SELECT columns, GROUP BY, ORDER BY etc) ===

(defn csnames
  "Return columns in comma separated string form.
   Example: [:qty :price \"order_date\"] >> [\"qty, price, order_date\"] "
  [tokens]
  (if (empty? tokens) (empty-clause)
    (let [tokens-vec (as-vector tokens)]
      (apply merge-clauses
        (interpose (as-clause ", ")
          (map as-clause tokens-vec))))))


;; === The LIMIT clause ===

(defn limit
  "The LIMIT clause"
  ([howmany]
    (as-clause [" LIMIT ?" howmany]))
  ([from howmany]
    (as-clause [" LIMIT ?, ?" from howmany])))


;; === Functions and Columns ===


(defn sqlfn
  "Example: user=> (sqlfn :AVG :subjectid)
            [\"AVG(subjectid)\"]"
  [^Keyword fn-name & args]
  (merge-clauses
    (apply merge-clauses
      (as-clause (str (str-name fn-name) "("))
      (map as-clause args))
    (as-clause ")")))

(def ^{:doc "Alias for 'sqlfn' function"}
      FN sqlfn)

(defn as
  "Example: user=> (as :avgscore \"AVG(score)\")
            [\"AVG(score) AS avgscore\"]"
  [^Keyword new-colname expr]
  (apply merge-clauses
    (map as-clause [expr " AS " new-colname])))

(defn asfn
  "Example: user=> (asfn :avgscore :AVG :score)
            [\"AVG(score) AS avgscore\"]"
  [^Keyword new-colname ^Keyword fn-name & args]
  (as new-colname (apply sqlfn fn-name args)))
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.