check-product-feed.clj

;;
;; Read a CSV file and look up the product ids it contains in a
;; database. Report all the products in the CSV that do not exist in
;; the database.
;;
;; Usage: $0 <path-to-csv-file>
;;

(import 'java.io.FileReader 'au.com.bytecode.opencsv.CSVReader)

(use 'clojure.contrib.str-utils)
(use 'clojure.contrib.sql)

;; OpenCSV gives us a List of String[]s... ugh.
(defn read-csv [file-name]
  (with-open [reader (CSVReader. (FileReader. file-name))]
     (rest ;; skip the header row
      (map seq (seq (. reader readAll))))))

;; extract interesting fields from a CSV row
(defn product-from [row]
  {:product-id (nth row  0 "")
   :title      (nth row  1 "")})

;; set up the db connection
(def db {:classname   "org.h2.Driver"
         :subprotocol "h2"
         :subname (str "file:///Users/mrowe/.h2data/mydata")
         :user     "sa"
         :password ""})

(defn sql-query [q]
  (with-query-results res q (doall res)))

(defn count-products [product-id]
  (:count
   (first
    (sql-query ["select count(1) as count from product where id = ?" product-id]))))

(defn exists? [product-id]
   (>= (count-products product-id) 1))

(defn product-missing? [csv-row]
  (let [product (product-from csv-row)]
    (not (exists? (product :product-id)))))

;;;;;;;;;;

(def filename (first *command-line-args*))
(def feed (read-csv filename))

(defn report-product-id [row]
  (let [product (product-from row)]
    (format "Not in product catalog: %s - %s" (product :product-id) (product :title))))

(with-connection db 
  (println (str-join "\n" (map report-product-id (filter product-missing? feed)))))