export.clj

export.clj

(require '[clojure.java.io :as io])
(require '[clojure.data.xml :as xml])
(require '[next.jdbc.sql :as sql])
(require '[next.jdbc.result-set :as rs])

(def db-spec
  "Database connection"
  {:dbtype "oracle:thin"
   :host "localhost"
   :user "system"
   :password "oracle"
   :dbname "xe"})

(defn query
  "Execute an SQL query"
  ([sql] (query sql []))
  ([sql params]
   (sql/query db-spec (cons sql params) {:builder-fn rs/as-unqualified-kebab-maps})))

(defn in
  "in construction for SQL queries"
  [coll]
  (let [marks (str/join ", " (take (count coll) (repeat "?")))]
    (str "(" marks ")")))

(defn find-restaurants []
  (query "SELECT * FROM Restaurants"))

(defn find-likes [restaurant-ids]
  (query (str "SELECT * FROM Likes WHERE fk_rest IN" (in restaurant-ids)) restaurant-ids))

(defn to-xml [data]
  (mapcat
   (fn [[tag rows]]
     (for [row rows]
       ;; ensure all attrs and tags are uppercased
       (xml/sexp-as-element [(str/upper-case tag) (update-keys row (comp str/upper-case name))])))
   data))

(defn to-file [data filename]
  (with-open [file (io/writer filename)]
    (xml/emit data file)))

(let [restaurants (find-restaurants)            ;; find all restaurants
      restaurant-ids (mapv :numero restaurants) ;; extract the ids from the restaurants
      likes (find-likes restaurant-ids)]        ;; retrieve the likes based on the restaurant-ids
  (-> [["RESTAURANTS" restaurants]              ;; add metadata `RESTAURANTS` (XML Tag) to the `restaurants`
       ["LIKES" likes]]                         ;; add metadata `LIKES` (XML Tag) to the `likes`
      (to-xml)                                  ;; encode the data as XML
      (to-file "/tmp/export.xml")))             ;; export to a file