gistfile1.clj

(ns learn-korma.core-test
  (:require [clojure.test :refer :all]
            [korma.core :refer :all]
            [korma.db :refer :all]))

(defdb db
  (postgres {:db "learnkorma"}))

(defentity posts
  (pk :id)
  ;; Upper-case table names by default in H2. We handled it for the columns->keys earlier with naming.
  (table :posts))

(def create-posts (str "CREATE TABLE posts (id serial, title varchar(255), content varchar(255), created_on timestamp default current_timestamp);"))

(def drop-posts (str "DROP TABLE posts;"))

(defn ensure-no-posts []
  (try
    (exec-raw drop-posts)
    (catch Exception e
      nil)))

(deftest create-and-drop-tables
  (testing "Can create posts table"
    (let [_ (ensure-no-posts)
          created (exec-raw create-posts)
          dropped (ensure-no-posts)]
    (is (= '(0) created)
        (= {} dropped)))))

(deftest following-along-solution
  (testing "can insert and select posts"
    (let [_ (ensure-no-posts)
          _ (exec-raw create-posts)
          created (insert posts (values {:title "First post" :content "blah blah blah"}))
          selected (select posts (limit 1))
          updated (update posts (set-fields {:title "Best Post"}) (where {:title "First post"}))
          post-update (select posts (limit 1))
          _ (delete posts (where {:title "Best Post"}))
          post-delete (select posts (limit 1))]
      (is (= "First post" (:title created)))
      (is (= "First post" (:title (first selected))))
      (is (= "Best Post"  (:title (first post-update))))
      (is (= [] post-delete)))))


(defentity orders
  ;; order is a reserved name in korma and H2.
  (database db)
  (table :orders))

(defentity billable
  (database db)
  (table :billable))

;; forward declaring customer so address can refer to it before it exists
(declare customer)

(defentity address
  (database db)
  (table :address))

(defentity phone
  (database db)
  ;; WHO WAS
  (table :phone))

(defentity customer
  ;; specify which database directly, using the one you defined earlier
  (database db)

  (table :customer)

  ;; By default assumed id as a primary key
  ;; produces query:
  ;; "customer.id = address.customer_id"
  (has-one address)
  (has-many phone)

  ;; You can override the default foreign key
  ;; name by using :fk as seen below
  (belongs-to billable {:fk :cust_id})
  (many-to-many orders :customer_orders))

(def create-orders (str "CREATE TABLE orders (id serial, billable_id int);"))

(def create-billable (str "CREATE TABLE billable (id serial, cust_id int, item text);"))

(def create-address (str "CREATE TABLE address (id serial, customer_id int, addy text, current boolean default true);"))

(def create-phone (str "CREATE TABLE phone (id serial, customer_id int, phone text);"))

(def create-customer (str "CREATE TABLE customer (id serial, name text, tentacles boolean, registered timestamp default current_timestamp);"))

(defentity sub-customers
  (table (subselect customer
           (where :orders_pending))
         :customersWithOrders))

(defn dmitri [] (first (select customer
                               (where {:name "Dmitri"}))))
(defn chris []  (first (select customer
                               (where {:name "Chris"}))))
(defn brandy [] (first (select customer
                               (where {:name "Brandy"}))))
(defn cthulhu [] (first (select customer
                                (where {:name "Cthulhu"}))))

(defn ensure-clean-tables []
  (doseq [drop ["DROP TABLE orders;" "DROP TABLE billable;" "DROP TABLE address;" "DROP TABLE phone;" "DROP TABLE customer;"]]
    (try (exec-raw drop)
         (catch Exception e
           nil)))
  (doseq [create [create-orders create-billable create-address create-phone create-customer]]
    (exec-raw create))
  (insert customer
          (values [{:name "Dmitri"  :tentacles false}
                   {:name "Chris"   :tentacles false}
                   {:name "Brandy"  :tentacles false}
                   {:name "Cthulhu" :tentacles true}]))
  (insert billable
          (values {:cust_id (:id (brandy)) :item "Chainsaw for cutting down trees in the backyard"}))
  (insert billable
          (values {:cust_id (:id (dmitri)) :item "Caviar"}))
  (insert billable
          (values {:cust_id (:id (chris)) :item "Bottles of whiskey for bribery"}))
  (insert address
          (values {:customer_id (:id (chris)) :addy "San Francisco!"}))
  (insert address
          (values {:customer_id (:id (dmitri)) :addy "Elsewhere"}))
  (insert address
          (values {:customer_id (:id (brandy)) :addy "San Francisco!"})))

(defn subs []
  (select customer
    (with address)
      (where {:address.id
              [in (subselect address
                  (fields :id)
                  (where {:current true}))]})))

(defn customers []
  (-> (select* customer) (with address)))

(defn constrainer [field]
  (fn [table value]
    (-> table (where {field value}))))

(def customer-fields [:tentacles :id :name :registered])
(def all-constrainers (map constrainer customer-fields))

;; closed over function via lexical scope
(def constrain-tentacles (first all-constrainers))

(defn yay-area-residents [customers]
  (-> customers (where {:address.addy "San Francisco!"})))

(defn customers-with-tentacles []
      (-> (customers) (constrain-tentacles true) (select)))

(defn customers-without-tentacles []
      (-> (customers) (constrain-tentacles false) (select)))

(defn customers-without-tentacles-in-sf []
      (-> (customers) (constrain-tentacles false) (yay-area-residents) (select)))

(deftest multiple-tables
  (testing "insert customers and the like"
    (let [_ (ensure-clean-tables)
          grouped (select customer (fields [:tentacles]) (group :tentacles))]
      (is (= [{:tentacles true} {:tentacles false}] grouped))
      (is (= 4 (count (select customer (with address)))))
      (is (= 4 (count (-> (select* customer) (with address) (select)))))
      (is (= 3 (count (subs))))
      (is (= 4 (count (select customer (where {:registered [<= (sqlfn now)]})))))
      (is (= "Cthulhu" (:name (first (exec-raw ["SELECT * FROM customer WHERE id = ?" [4]] :results)))))
      (is (= 2 (count (customers-without-tentacles-in-sf))))
      (is (= "Chris" (:name (chris)))))))