G'day all,
I am building a reporting interface on top of a database with a star
schema. The star schema makes it very easy to restrict queries by various
combinations of dimension. Some combinations are legal; others are not.
Hence core.match. The number of possible combinations is large, and I would
like to provide a simple API for setting up the dimensional constraints
before running a query.
I have in mind to use dynamic variables because in generating reports, a
few separate queries to the database are required - always with the same
dimensional constraints for a particular instance of a report. It is a pain
to pass a large number of parameters down the call stack - all the way from
the interface to the presentation layer, to the mapping layer which
ultimately builds and runs the query against the db. It would be even more
of a pain to update all calls with new parameters every time I add a new
dimension to the database.
So the interface would look something like this:
(binding [*dimension1* ... *dimension2* ... *dimension3* ...]
(get-fact-1-aggregates mandatory-dimension-restriction)
(get-fact-2-aggs mandatory-dim-rest)
....)
Or more concretely if you like a bit of context:
(binding [*drill-name* "DR1" *hole-name* "h1" *from* timestamp *to*
timestamp2]
(do-something-with (drilling-aggregates 123))
(do-something-with (reaming-aggregates 123))
....)
Where 123 would be a company ID set by the user's login credentials and
since it is mandatory and will always be passed I have put it as a formal
parameters.
Here is a rough draft of the code I have in mind.
I would very much appreciate any comments as to the sanity of this
approach, and any other ideas on how to do it better.
Specifically - any better ways of building the query??
Note: I have not populated all the legal combinations of variables so the
match would grow.
Thanks!
David
(ns hummingbird.mappers.core
(:require
[hummingbird.mappers.database :as db]
[clojure.java.jdbc :as sql]
[clojure.core.match :refer [match]]))
(def ^:dynamic *job-name* nil)
(def ^:dynamic *drill-name* nil)
(def ^:dynamic *hole-name* nil)
(def ^:dynamic *from* nil)
(def ^:dynamic *to* nil)
(def !nil? (complement nil?))
(defn build-dr-query
([company-name hole-selection from-selection to-selection]
(format
"SELECT
round((sum(dr.distance)/sum(dr.sample_period))::numeric, 2) as feed_rate
FROM
drilling as dr,
dim_time as t,
dim_hole as h
WHERE
dr.time_key = t.time_key
AND dr.hole_key = h.hole_key
AND h.company_name = '%s' -- mandatory company restriction
%s -- optional hole restriction
%s -- optional time from restriction
%s -- optional time to restriction
" company-name hole-selection from-selection to-selection))
([company-name]
(match [company-name *hole-name* *from* *to*]
[company-name nil nil nil]
(build-dr-query company-name "" "" "")
[company-name (hole-name :guard !nil?) nil nil]
(build-dr-query company-name
(format "AND h.hole_name = '%s'" hole-name)
""
"")
[company-name (hole-name :guard !nil?) (from :guard !nil?) nil]
(build-dr-query company-name
(format "AND h.hole_name = '%s'" hole-name)
(format "AND t.date >= '%s'::timestamp" from)
"")
[company-name (hole-name :guard !nil?) (from :guard !nil?) (to
:guard !nil?)]
(build-dr-query company-name
(format "AND h.hole_name = '%s'" hole-name)
(format "AND t.date >= '%s'::timestamp" from)
(format "AND t.date < '%s'::timestamp" to))
:else (throw (Exception. "Illegal combination")))))
(defn drilling-aggregates [& {:keys [group-by order-by]}]
(sql/with-connection db/osprey
(sql/with-query-results rows [(build-dr-query "ACME")]
(db/only-one rows))))
--
David Jagoe
davidja...@gmail.com
+18053284389
--
You received this message because you are subscribed to the Google
Groups "Clojure" group.
To post to this group, send email to clojure@googlegroups.com
Note that posts from new members are moderated - please be patient with your
first post.
To unsubscribe from this group, send email to
clojure+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/clojure?hl=en
