From fb946311da111e2a422a938b0b8720c3ecf3341c Mon Sep 17 00:00:00 2001
From: Carlo Zancanaro <carlo@clearboxsystems.com.au>
Date: Fri, 21 Jun 2013 10:42:56 +1000
Subject: Add take/drop, move jdbc stuff, document default of postgres

Add take and drop functionality to the queries, so now you can use the take and
drop functions in a similar way to how they work on seqs in clojure.

Move jdbc interface stuff into clojure-sql.jdbc, so if you're using jdbc you
can include it yourself. (If you're not using jdbc then it shouldn't bother
you).

Given the default compilation target is actually postgres, document that.
---
 src/clojure_sql/compiler.clj | 75 +++++++++++++++++++++++---------------------
 src/clojure_sql/core.clj     | 35 +++++----------------
 src/clojure_sql/dsl.clj      | 36 +++++++++++++++------
 src/clojure_sql/jdbc.clj     | 12 +++++++
 4 files changed, 86 insertions(+), 72 deletions(-)
 create mode 100644 src/clojure_sql/jdbc.clj

(limited to 'src')

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)))))))
-- 
cgit v1.2.3