;;  Copyright (c) Stephen C. Gilardi. All rights reserved.  The use and

 ;;  distribution terms for this software are covered by the Eclipse Public

 ;;  License 1.0 (http://opensource.org/licenses/eclipse-1.0.php) which can

 ;;  be found in the file epl-v10.html 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.test-sql

   (:use [clojure.contrib.sql :as sql :only ()]))

 

 (def db {:classname "org.apache.derby.jdbc.EmbeddedDriver"

          :subprotocol "derby"

          :subname "/tmp/clojure.contrib.sql.test.db"

          :create true})

 

 (defn create-fruit

   "Create a table"

   []

   (sql/create-table

    :fruit

    [:name "varchar(32)" "PRIMARY KEY"]

    [:appearance "varchar(32)"]

    [:cost :int]

    [:grade :real]))

 

 (defn drop-fruit

   "Drop a table"

   []

   (try

    (sql/drop-table :fruit)

    (catch Exception _)))

 

 (defn insert-rows-fruit

   "Insert complete rows"

   []

   (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

   "Insert rows with values for only specific columns"

   []

   (sql/insert-values

    :fruit

    [:name :cost]

    ["Mango" 722]

    ["Feijoa" 441]))

 

 (defn insert-records-fruit

   "Insert records, maps from keys specifying columns to values"

   []

   (sql/insert-records

    :fruit

    {:name "Pomegranate" :appearance "fresh" :cost 585}

    {:name "Kiwifruit" :grade 93}))

 

 (defn db-write

   "Write initial values to the database as a transaction"

   []

   (sql/with-connection db

     (sql/transaction

      (drop-fruit)

      (create-fruit)

      (insert-rows-fruit)

      (insert-values-fruit)

      (insert-records-fruit)))

   nil)

 

 (defn db-read

   "Read the entire fruit table"

   []

   (sql/with-connection db

     (sql/with-query-results res

       ["SELECT * FROM fruit"]

       (doseq [rec res]

         (println rec)))))

 

 (defn db-update-appearance-cost

   "Update the appearance and cost of the named fruit"

   [name appearance cost]

   (sql/update-values

    :fruit

    ["name=?" name]

    {:appearance appearance :cost cost}))

 

 (defn db-update

   "Update two fruits as a transaction"

   []

   (sql/with-connection db

     (sql/transaction

      (db-update-appearance-cost "Banana" "bruised" 14)

      (db-update-appearance-cost "Feijoa" "green" 400)))

   nil)

 

 (defn db-update-or-insert

   "Updates or inserts a fruit"

   [record]

   (sql/with-connection db

     (sql/update-or-insert-values

      :fruit

      ["name=?" (:name record)]

      record)))

 

 (defn db-read-all

   "Return all the rows of the fruit table as a vector"

   []

   (sql/with-connection db

     (sql/with-query-results res

       ["SELECT * FROM fruit"]

       (into [] res))))

 

 (defn db-grade-range

   "Print rows describing fruit that are within a grade range"

   [min max]

   (sql/with-connection db

     (sql/with-query-results res

       [(str "SELECT name, cost, grade "

             "FROM fruit "

             "WHERE grade >= ? AND grade <= ?")

        min max]

       (doseq [rec res]

         (println rec)))))

 

 (defn db-grade-a 

   "Print rows describing all grade a fruit (grade between 90 and 100)"

   []

   (db-grade-range 90 100))

 

 (defn db-get-tables

   "Demonstrate getting table info"

   []

   (sql/with-connection db

     (into []

           (resultset-seq

            (-> (sql/connection)

                (.getMetaData)

                (.getTables nil nil nil (into-array ["TABLE" "VIEW"])))))))

 

 (defn db-exception

   "Demonstrate rolling back a partially completed transaction on exception"

   []

   (sql/with-connection db

     (sql/transaction

      (sql/insert-values

       :fruit

       [:name :appearance]

       ["Grape" "yummy"]

       ["Pear" "bruised"])

      ;; at this point the insert-values call is complete, but the transaction

      ;; is not. the exception will cause it to roll back leaving the database

      ;; untouched.

      (throw (Exception. "sql/test exception")))))

 

 (defn db-sql-exception

   "Demonstrate an sql exception"

   []

   (sql/with-connection db

     (sql/transaction

      (sql/insert-values

       :fruit

       [:name :appearance]

       ["Grape" "yummy"]

       ["Pear" "bruised"]

       ["Apple" "strange" "whoops"]))))

 

 (defn db-batchupdate-exception

   "Demonstrate a batch update exception"

   []

   (sql/with-connection db

     (sql/transaction

      (sql/do-commands

       "DROP TABLE fruit"

       "DROP TABLE fruit"))))

 

 (defn db-rollback

   "Demonstrate a rollback-only trasaction"

   []

   (sql/with-connection db

     (sql/transaction

      (prn "is-rollback-only" (sql/is-rollback-only))

      (sql/set-rollback-only)

      (sql/insert-values

       :fruit

       [:name :appearance]

       ["Grape" "yummy"]

       ["Pear" "bruised"])

      (prn "is-rollback-only" (sql/is-rollback-only))

      (sql/with-query-results res

        ["SELECT * FROM fruit"]

        (doseq [rec res]

          (println rec))))

     (prn)

     (sql/with-query-results res

       ["SELECT * FROM fruit"]

       (doseq [rec res]

         (println rec)))))