My updated approach is now like this:
(defn find-uuids [query a2p-id]
(sql/with-connection sqlserver
(sql/with-query-results rs
[query a2p-id]
(:uuid (first rs)))))
(defn find-records [{query :main-query from-date :from-date to-date
:to-date sub-query-fn :sub-query-fn}]
(sql/with-connection db2
(sql/with-query-results rs
[query from-date to-date]
(doall (map #(hash-map :a2p-id (:id %) :dh-uuid (sub-query-fn (:id
%))) rs)))))
; from / to will be supplied by user so I will factor it out here for now
(def user-from "2013-05-24")
(def user-to "2013-05-25")
(def query-parts #{{:entity "Project"
:main-query "select project_id as id from project where
last_updated > ? and last_updated < ?"
:from-date user-from
:to-date user-to
:sub-query-fn (partial find-uuids "select project_uuid as
uuid from project where a2p-id = ?")
}
{:entity "Model"
:main-query "select model_id as id from model where
last_updated > ? and last_updated < ?"
:from-date user-from
:to-date user-to
:sub-query-fn (partial find-uuids "select model_uuid
as uuid from model where a2p-id = ?")
}
})
(map find-records query-parts)
Looking at the set of maps it's clear that there may be room for further
compression but I want to wait until I get all of the queries and odd cases
out of the way first.
Thanks again for your help.
Ray
On Friday, 24 May 2013 23:20:11 UTC+2, Mond Ray wrote:
>
> Fantastic answer Marc - I had been fiddling about with map and hash maps
> and could quite get the parens right but now its sorted. Brilliant - thanks
> again for the help.
>
> Ray
>
> On Friday, 24 May 2013 18:56:54 UTC+2, mlimotte wrote:
>>
>> Hi Ray,
>>
>> First, I'd remove the print(ln)s from the functions. No need for
>> side-effects there. Just have the functions return the result value and
>> then when you call a2p-records-by-date you can wrap it in a println. Then
>> you want to create a Map as the return value of the inner loop
>> in a2p-records-by-date
>>
>> (defn a2p-records-by-date [query from-date to-date dh-sub-query]
>> (sql/with-connection db2
>> (sql/with-query-results rs [query from-date to-date]
>> (doall (map #(hashmap :a2pid (:project_id %) :uuid (dh-sub-query
>> (:project_id %))) rs)))))
>> ; the doall is needed so the rs is completely consumed before
>> (sql/with-query-results
>> …) closes the connection
>>
>> (defn dh-records-by-a2p-id [query a2p-id]
>> (sql/with-connection mssql
>> (sql/with-query-results rs [query a2p-id]
>> (:project_uuid (first rs)))))
>> ; I'm assuming there's only zero or one :project_uuid for a given a2p-id.
>> ; In the zero case, rs is an empty Seq, so (first rs) returns nil and
>> (:project_uuid nil) is nil
>>
>> (defn dh-sub-query [a2p-id] (dh-records-by-a2p-id "select PROJECT_UUID
>> from PROJECT where A2P_PROJECT_ID = ?" a2p-id))
>>
>> (println (a2p-records-by-date "select project_id from PROJECT where
>> timestamp > ? and timestamp < ?" "2012-03-02" "2012-03-07" dh-sub-query))
>>
>>
>> Of course, I haven't tested any of this, so plenty of opportunity for
>> mistakes.
>>
>> Marc
>>
>>
>> On Fri, May 24, 2013 at 10:55 AM, Mond Ray <[email protected]> wrote:
>>
>>> I am starting out to use Clojure to combine and verify data between DB2
>>> on a Mainframe, SQL Server and an Atom Feed. Yes, it's such fun working in
>>> a start-up ;-)
>>>
>>> Database wise, all is connecting OK after some Leiningen shenanigans and
>>> I am now stuck on the mapping part ;-)
>>>
>>> The code is below. I want to generate a set of maps that show the
>>> following:
>>>
>>> #({:a2pid 269, :uuid nil}
>>> {:a2pid 270, :uuid nil}
>>> {:a2pid 258, :uuid nil}
>>> {:a2pid 261, :uuid nil}
>>> {:a2pid 251, :uuid E7D4262C-62B3-4129-9CE4-B342DC1C39FC})
>>>
>>> The idea is to have a list of maps that can show where there are gaps
>>> between the two DBs and, coming next, the Atom feed.
>>>
>>> It is essentially a join operation - and maybe that's what I need but I
>>> cannot figure out where to plonk the magic words.
>>>
>>> (defn a2p-records-by-date [query from-date to-date dh-sub-query]
>>> (sql/with-connection db2
>>> (sql/with-query-results rs [query from-date to-date]
>>> (doseq [row rs] (println (str " " (:project_id row)
>>> (dh-sub-query (:project_id row))))))))
>>>
>>> (defn dh-records-by-a2p-id [query a2p-id]
>>> (sql/with-connection mssql
>>> (sql/with-query-results rs [query a2p-id]
>>> (dorun (map #(print (:project_uuid %)) rs)))))
>>>
>>> (defn dh-sub-query [a2p-id] (dh-records-by-a2p-id "select PROJECT_UUID
>>> from PROJECT where A2P_PROJECT_ID = ?" a2p-id))
>>>
>>> (a2p-records-by-date "select project_id from PROJECT where timestamp > ?
>>> and timestamp < ?" "2012-03-02" "2012-03-07" dh-sub-query)
>>>
>>> The output looks like this, so I am close!
>>>
>>> 269
>>> 270
>>> 258
>>> 261
>>> E7D4262C-62B3-4129-9CE4-B342DC1C39FC 251
>>>
>>> Can anyone help me out about how to generate the maps?
>>>
>>> This is just the start as I have many queries to run for many entities,
>>> so doubtless I will be back again ;-)
>>>
>>> Thanks in advance
>>>
>>> Ray
>>>
>>> --
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "Clojure" group.
>>> To post to this group, send email to [email protected]
>>> Note that posts from new members are moderated - please be patient with
>>> your first post.
>>> To unsubscribe from this group, send email to
>>> [email protected]
>>> For more options, visit this group at
>>> http://groups.google.com/group/clojure?hl=en
>>> ---
>>> You received this message because you are subscribed to the Google
>>> Groups "Clojure" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to [email protected].
>>> For more options, visit https://groups.google.com/groups/opt_out.
>>>
>>>
>>>
>>
>>
--
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to [email protected]
Note that posts from new members are moderated - please be patient with your
first post.
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
---
You received this message because you are subscribed to the Google Groups
"Clojure" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
