;;  Copyright (c) Stephen C. Gilardi. All rights reserved. The use and
;;  distribution terms for this software are covered by the Common Public
;;  License 1.0 (http://opensource.org/licenses/cpl.php) which can be found
;;  in the file CPL.TXT at the root of this distribution. By using this
;;  software in any fashion, you are agreeing to be bound by the terms of
;;  this license. You must not remove this notice, or any other, from this
;;  software.
;;
;;  test.clj
;;
;;  test/example for clojure.contrib.sql
;;
;;  scgilardi (gmail)
;;  Created 13 September 2008

(ns clojure.contrib.sql.test
  (:require [clojure.contrib.sql :as sql]))

(def db {:classname "org.apache.derby.jdbc.EmbeddedDriver"
         :subprotocol "derby"
         :subname "/tmp/clojure.contrib.sql.test.db"
         :create true})

(defn drop-fruit []
  (try
   (sql/drop-table :fruit)
   (catch Exception e)))

(defn create-fruit []
  (sql/create-table :fruit
    [:name "varchar(32)" "NOT NULL"]
    [:appearance "varchar(32)"]
    [:cost :int]
    [:grade :real]))

(defn insert-rows-fruit []
  (sql/insert-rows :fruit
    ["Apple" "red" 59 87]
    ["Banana" "yellow" 29 92.2]
    ["Peach" "fuzzy" 139 90.0]
    ["Orange" "juicy" 89 88.6]))

(defn insert-values-fruit []
  (sql/insert-values :fruit
    [:name :cost]
    ["Mango" 722]
    ["Feijoa" 441]))

(defn db-write []
  (sql/with-connection db
    (sql/transaction
     (drop-fruit)
     (create-fruit)
     (insert-rows-fruit)
     (insert-values-fruit)))
  nil)

(defn db-read []
  (sql/with-connection db
    (sql/with-results res
     "select * from fruit"
      (doseq [rec res]
        (println rec)))))

(defn db-read-all []
  (sql/with-connection db
    (sql/with-results res
     "select * from fruit"
      (into [] res))))

(defn db-grade-a []
  (sql/with-connection db
    (sql/with-results res
     "select name, cost from fruit where grade >= 90"
      (doseq [rec res]
        (println rec)))))

(defn db-exception []
  (sql/with-connection db
    (sql/transaction
      (sql/insert-values :fruit
        [:name :appearance]
        ["Grape" "yummy"]
        ["Pear" "bruised"])
      (throw (Exception. "an exception")))))