;; | |
;; 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))))) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment